Do we need LIMIT clause in UPDATE and DELETE statements for PostgreSQL?

Was working with MySQL recently. Noticed that UPDATE command of it has special LIMIT clause which is really useful as for me, e.g.

UPDATE `Product_download` SET `version`='9.2.1' WHERE `product_download_id`=367 LIMIT 1;

About these ads

Nerd.

Posted in Polls, PostgreSQL
9 comments on “Do we need LIMIT clause in UPDATE and DELETE statements for PostgreSQL?
  1. Thom Brown says:

    I proposed this functionality before, but for a different reason. The example you provided should probably have an index on product_download_id if speed is becoming an issue with such a statement.

    But the scenario I was thinking of was where you want to clear out a huge amount of old data from a massive table, but wanted to do it in batches. Of course this is possible using plenty of other methods, but nothing would be as simple as: DELETE FROM huge_table WHERE data_date < '2012-01-01' LIMIT 1000000;

    This could be run every now and then to minimise load on the system. We wouldn't care which records were deleted in particular, just as long as a lot that met the criteria had been deleted. And we wouldn't require the acquisition of exclusive row-level locks for every candidate row.

    • pashagolub says:

      Well, yes. My example does not reflect the total convenience. Thanks for the great use case!

    • hartmut says:

      On MySQL Cluster, which has hard upper limits for the number of uncommitted row changes, this is even essential to be able to perform certain operations …

      Whether this is an argument for DELETE…LIMIT or against MySQL Cluster is left as an excercise to the reader … ;)

    • Pierre says:

      Well, do you know math ?

      Instead of rolling a dice to guess whether your replicates will drop the same rows as your master, you could just use a modulo to delete X% of your rows at once…

      DELETE FROM huge_table WHETE data_date < '2012-01-01' AND id % 100 = 0;
      — Do that 100 time, with modulo increasing

      The MySQL UPDATE/DELETE with LIMIT is one of their best tool to disrupt the query-based replication results.

  2. Alexander says:

    If you are going to have “limit”, you will need “order by”, otherwise the order will be undefined. Either way, why not simply use a subselect? Like so:

    update users set verified = true where id in (select id from users limit 100)

    Or:

    delete users where id in (select id from users limit 100)

  3. Federico says:

    This could be very useful from a DBA point of view of course, as large bulk operations can be split in smaller chunks.

    +1 for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: