Thread: allow LIMIT in UPDATE and DELETE
Hi all, Currently the LIMIT clause is not allowed in UPDATE or DELETE statements. I wonder how easy it would be to allow it, and what people think about it ? For our application it would help a lot when processing things chunk-wise to avoid long running queries. The fact that the actual rows processed would be unpredictable does not make it less useful for us. We actually don't care which rows are processed, we process them all anyway, we just want to make sure it is a limited number at a time. A lot of our processes do take large amounts of time (hours up to days), and we cannot allow that to be in one transaction, the system does on-line processing too... I guess the low-level infrastructure is already there (from what I understood from earlier postings, but I may be wrong), and the question is more if this feature is worth to be included or not... and the syntax must be adjusted of course if yes. All comments welcome... Thanks, Csaba.
On 5/19/06, Csaba Nagy <nagy@ecircle-ag.com> wrote: > Hi all, > > Currently the LIMIT clause is not allowed in UPDATE or DELETE > statements. I wonder how easy it would be to allow it, and what people > think about it ? For our application it would help a lot when processing > things chunk-wise to avoid long running queries. I asked that question a while ago.. http://archives.postgresql.org/pgsql-general/2005-04/msg00386.php and got this response: http://archives.postgresql.org/pgsql-general/2005-04/msg00387.php Works quite well :) -- Postgresql & php tutorials http://www.designmagick.com/
On 5/19/06, Csaba Nagy <nagy@ecircle-ag.com> wrote:
> Hi all,
>
> Currently the LIMIT clause is not allowed in UPDATE or DELETE
> statements. I wonder how easy it would be to allow it, and what people
> think about it ? For our application it would help a lot when processing
> things chunk-wise to avoid long running queries.
>
> The fact that the actual rows processed would be unpredictable does not
> make it less useful for us. We actually don't care which rows are
> processed, we process them all anyway, we just want to make sure it is a
> limited number at a time. A lot of our processes do take large amounts
> of time (hours up to days), and we cannot allow that to be in one
> transaction, the system does on-line processing too...
>
> I guess the low-level infrastructure is already there (from what I
> understood from earlier postings, but I may be wrong), and the question
> is more if this feature is worth to be included or not... and the syntax
> must be adjusted of course if yes.
-- sample data
CREATE TEMP TABLE tab (id serial primary key, n int, t text);
INSERT INTO tab(n) SELECT * FROM generate_series(1,1000);
-- say, you want such an update:
UPDATE tab SET t = 'aqq' WHERE n > 10;
-- but with limit:
UPDATE tab SET t = 'aqq' WHERE id in (SELECT id FROM tab WHERE n > 10
LIMIT 100);
-- or this way (join):
UPDATE tab SET t = 'aqq' FROM (SELECT id FROM tab WHERE n > 10 LIMIT
100) AS tab_ids WHERE tab.id = tab_ids.id;
...this of course assumes that you have a primary key you can use
to "target" the update.
Then again, there are places where there is no primary key, like:
CREATE TABLE foo (t text);
INSERT INTO foo VALUES('aaa');
INSERT INTO foo VALUES('aaa');
...and you want to update first 'aaa' to 'bbb'.
But you can handle it this way:
CREATE TEMP SEQUENCE aaa_temp_seq;
UPDATE foo SET t='bbb' WHERE t='aaa' AND nextval('aaa_temp_seq') <= 1;
-- LIMIT 1
...this of course will suck for big queries (all matching rows will be
searched, but not updated);
Reagrds,
Dawid
> I asked that question a while ago.. > > http://archives.postgresql.org/pgsql-general/2005-04/msg00386.php > > and got this response: > > http://archives.postgresql.org/pgsql-general/2005-04/msg00387.php > > Works quite well :) I don't doubt that it works well, in fact that's what I plan to use myself... I was not asking for a workaround, that I can figure out myself :-) Regarding the arguments of the post you linked: "It isn't in the SQL standard" - neither LIMIT on SELECT, still it is a very useful feature; "it would have undefined behavior" - so does LIMIT on SELECT without order by, and it is again still useful when you don't care about which entry you get, you just want one of them. It certainly provides a planning advantage in some cases where ordering would mean a sort; The subquery delete will definitely have more involved plan than a delete with limit. On some of my tables that would make a difference, even if not that big one due to caching effects (the same rows are deleted/updated which were already visited by the subquery). I can't say for sure how big is the penalty of doing the subqery, but the plans I have seen involve something like: db=# prepare test_001(bigint, bigint, smallint) as db-# DELETE FROM big_table db-# WHERE (col1, col2) IN db-# (SELECT col1, col2 FROM big_table db(# WHERE col1=$2 db(# AND col3 IS NOT NULL db(# AND col4 =$3 db(# AND col5 <> 'o' db(# LIMIT 1000); PREPARE db=# explain execute test_001(1,1,1); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=2166.10..2282.99 rows=1 width=6) -> HashAggregate (cost=2166.10..2166.10 rows=29 width=16) -> Subquery Scan "IN_subquery" (cost=0.00..2165.95 rows=29 width=16) -> Limit (cost=0.00..2165.66 rows=29 width=16) -> Index Scan using idx_big_table_col3 on big_table (cost=0.00..2165.66 rows=29 width=16) Index Cond: (col1 = $2) Filter: ((col3 IS NOT NULL) AND (col4 = $3) AND ("col5" <> 'o'::bpchar)) -> Index Scan using pk_big_table on big_table (cost=0.00..4.02 rows=1 width=22) Index Cond: ((big_table.col1 = "outer".col1) AND (big_table.col2 = "outer".col2)) (9 rows) idx_big_table_col3 - is a partial index where col3 is not null, which means ~ 10% of the total rows. The estimates are a generic estimate, the worst case is that there are a few 100K rows selected by the subselect before the limit. So I guess the worst case can have as much as double cost than a plan for DELETE with LIMIT would have. With the LIMIT allowed on DELETE it would be something like: -> Limit (cost=0.00..2165.66 rows=29 width=16) -> Index Scan using idx_big_table_col3 on big_table (cost=0.00..2165.66 rows=29 width=16) Index Cond: (col1 = $2) Filter: ((col3 IS NOT NULL) AND (col4 = $3) AND ("col5" <> 'o'::bpchar)) That would spare a HashAggregate and an index scan. The index scan would very likely not be a problem, as the same index entries are visited in the subquery and likely are cached, and the HashAggregate should be also fast for the max 1000 rows it has to handle, but they are still completely unnecessary for my purpose, so I still think the LIMIT on DELETE and UPDATE would make perfect sense. Cheers, Csaba.
> -- sample data
> CREATE TEMP TABLE tab (id serial primary key, n int, t text);
> INSERT INTO tab(n) SELECT * FROM generate_series(1,1000);
> -- say, you want such an update:
> UPDATE tab SET t = 'aqq' WHERE n > 10;
> -- but with limit:
> UPDATE tab SET t = 'aqq' WHERE id in (SELECT id FROM tab WHERE n > 10
> LIMIT 100);
> -- or this way (join):
> UPDATE tab SET t = 'aqq' FROM (SELECT id FROM tab WHERE n > 10 LIMIT
> 100) AS tab_ids WHERE tab.id = tab_ids.id;
>
> ...this of course assumes that you have a primary key you can use
> to "target" the update.
>
> Then again, there are places where there is no primary key, like:
>
> CREATE TABLE foo (t text);
> INSERT INTO foo VALUES('aaa');
> INSERT INTO foo VALUES('aaa');
> ...and you want to update first 'aaa' to 'bbb'.
>
> But you can handle it this way:
>
> CREATE TEMP SEQUENCE aaa_temp_seq;
> UPDATE foo SET t='bbb' WHERE t='aaa' AND nextval('aaa_temp_seq') <= 1;
> -- LIMIT 1
> ...this of course will suck for big queries (all matching rows will be
> searched, but not updated);
>
> Reagrds,
> Dawid
Like I said in a previous post, I can figure out the workarounds, but it
would be nice not to need it, not to mention the LIMIT would work
faster.
I specifically seek for opinions about the LIMIT on DELETE/UPDATE
feature, not workarounds.
Cheers,
Csaba.
> That would spare a HashAggregate and an index scan. The index scan would > very likely not be a problem, as the same index entries are visited in > the subquery and likely are cached, and the HashAggregate should be also > fast for the max 1000 rows it has to handle, but they are still > completely unnecessary for my purpose, so I still think the LIMIT on > DELETE and UPDATE would make perfect sense. Oh, it just occured to me: the subquery is using a different index than the outer loop, so we can forget about caching. Considering that the outer loop uses an index 10x bigger than the subquery, and that means ~50million entries, and the typical situation where this would be used mostly selects 1000 rows indeed, the LIMIT on DELETE for this case would mean for sure ~ 50% speedup. Cheers, Csaba.
Csaba Nagy <nagy@ecircle-ag.com> writes:
> Currently the LIMIT clause is not allowed in UPDATE or DELETE
> statements. I wonder how easy it would be to allow it, and what people
> think about it ?
This has been proposed before, and rejected before, and the arguments
against are just as strong as they were before. See the archives.
regards, tom lane
On Fri, 2006-05-19 at 15:51, Tom Lane wrote: > Csaba Nagy <nagy@ecircle-ag.com> writes: > > Currently the LIMIT clause is not allowed in UPDATE or DELETE > > statements. I wonder how easy it would be to allow it, and what people > > think about it ? > > This has been proposed before, and rejected before, and the arguments > against are just as strong as they were before. See the archives. Tom, I guess you refer to the following: http://archives.postgresql.org/pgsql-patches/2002-09/msg00256.php Well, let me fight your arguments. The first one here is not the first in your mail, but the most important I guess: "Just because MySQL is willing to implement nonstandard bad ideas doesn't mean we are. In any case the idea that this might provide some amount of compatibility is illusory: the odds are good that we'd delete or update a different tuple than they do, because of implementation differences. An application that actually depends on MySQL's behavior would surely be broken." Well, first of all, you're not competing here with MySQL in this case, but with Oracle. Our application does this using Oracle'sROWNUM trick and it works perfectly fine. Now I guess you think Oracle's ROWNUM is also stupid in this case, butit certainly helps us writing cleaner SQL, and a missing postgres alternative which is easy to use won't help you in attractingOracle users. Regarding the compatibility, I do not expect that the rows deleted/updated will be the same as on Oracle or other DB, I simplyexpect that only a limited number of rows will be processed at a time... the rest will be processed in the next runs,on all DBs... Is this so outrageous ? Regarding non-standard, LIMIT is non-standard in SELECTS as well, and I don't see how much more non-standard would it bein DELETE/UPDATE. Regarding "bad ideas", "depending on MySQLs behavior", I think you're simply overreacting here... for me LIMIT in DELETE/UPDATEwould have saved a few days of compatibility work on our application WITH ORACLE, not MySQL, and a few contrivedqueries. "And how exactly do you control *which* tuple(s) get deleted or updated, if the WHERE clause selects more than the limit?" I DO NOT CARE about which rows are deleted. The fact that it is nondeterministic can be very clearly specified in the documentationif you think it is such a bad thing, but nondeterministic is perfectly fine sometimes. There are lots of nondeterminismsin the data base world, starting with the ordering of selects if you don't use order by, then why don't weforce everybody using order by ? Why don't you force to use order by on a select with limit ? Why there it is enough tosay it in the docs that it WILL BE NON_DETERMINISTIC ? "Then use ctid." For the problem at hand in your post it is a good solution, except that it will cause a full table scan cause I guess fewpeople have indexes on ctid. Or you have to write your queries really contrived, by duplicating most of your query conditionsso that it can use some indexes. I'm not sure if you'll get away without at least 2 full table scans if using ctidand no indexes, one for the subquery and one for the delete itself... not to mention the need for something like a HashAggregateon the subquery results... all this is speculation, but for sure you'll spend 10x the time for optimizing thesubquery then you would writing a simple DELETE with LIMIT. "Have you got any evidence that there's a meaningful speedup?" No, but from speculating the query plans I see it would mean up to 50% speedup for my use case. "We have a zero-tolerance policy on yacc warnings." "This just seems like a really bad idea ..." You simply have prejudices against this feature. If you wouldn't be so against it I'm sure the problems could be solved.You're one of the most influent person on where postgres is going, and it's a pity when you're so against somethingyou don't like based on gut feelings... Cheers, Csaba.
Csaba Nagy <nagy@ecircle-ag.com> writes:
> I DO NOT CARE about which rows are deleted.
You can't possibly think that that holds true in general.
> The fact that it is
> nondeterministic can be very clearly specified in the documentation if
> you think it is such a bad thing, but nondeterministic is perfectly
> fine sometimes. There are lots of nondeterminisms in the data base
> world, starting with the ordering of selects if you don't use order
> by, then why don't we force everybody using order by ? Why don't you
> force to use order by on a select with limit ? Why there it is enough
> to say it in the docs that it WILL BE NON_DETERMINISTIC ?
I can tolerate nondeterminism in SELECT because it doesn't change the
data. If you get it wrong you can always do it over. UPDATE/DELETE
need to have higher standards though.
regards, tom lane
On Fri, 2006-05-19 at 16:31, Tom Lane wrote: > Csaba Nagy <nagy@ecircle-ag.com> writes: > > I DO NOT CARE about which rows are deleted. > > You can't possibly think that that holds true in general. I agree that it is not true in the general case, but then I also don't want to use DELETE with LIMIT in the general case. I only want to use it in the very specific cases where it makes sense, and it results in cleaner SQL, and it would likely result in a better execution plan. > I can tolerate nondeterminism in SELECT because it doesn't change the > data. If you get it wrong you can always do it over. UPDATE/DELETE > need to have higher standards though. Please Tom, there are so many ways you can shoot your feet already in there... I don't see why this one would be a bigger foot-gun then the subquery stile. It is functionally equivalent. It's only easier to write... if somebody wants to shoot himself, he can do it one way or the other. Placing a big warning on the docs should be enough... <rant> except if postgres is really targeting the MySql users instead of the Oracle folks. Those guys already have this foot-gun readily loaded... where's the American spirit where you are allowed to carry guns and expected to act responsible ?</rant> Cheers, Csaba.
>----- Original Message ---- >From: Tom Lane <tgl@sss.pgh.pa.us> >To: Csaba Nagy <nagy@ecircle-ag.com> >Cc: Postgres general mailing list <pgsql-general@postgresql.org> >Sent: Friday, May 19, 2006 9:31:24 AM >Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE > >You can't possibly think that that holds true in general. > >I can tolerate nondeterminism in SELECT because it doesn't change the >data. If you get it wrong you can always do it over. UPDATE/DELETE >need to have higher standards though. > > regards, tom lane The usage Csaba is referring to seems to be pretty common practice in the world of Oracle. If I need to purge 5-10 millionrows from a non-partitioned table on a regular basis (e.g: archiving) I'm going to use delete in conjunction withan appropriate where clause (typically something like less than some sequence number or date) and tack a "rownum<X" (whereX is some fairly large constant) on the end so that the delete is done in chunks. I'll commit immediately afterwardsand loop until sql%rowcount<X indicating that I'm finsihed. Now the question... why would you do that instead of doing everything in one big transaction on Oracle? I guess performanceis one reason. Oracle's (at least with 8/8i) performance seems to tank very quickly on deletes as you increasethe number of records you delete in a single transaction. The other (at least with my understanding of Oracle internals)is that using smaller transactions will mean less rollback segment space used which reduces the likelyhood of yourtransaction getting killed due to Oracle running out of rollback space on a database that has heavy usage. Since Postgresql's MVCC system doesn't require rollback segments I guess the performance question is the more important ofthe two. If there could be a performance gain in Postgresql with "delete from X where Y limit Z" vs "delete from X whereY in (some subselect limit Z)" I'd think Csaba suggestion has some merit. Regards, Shelby Cain
> Now the question... why would you do that instead of doing everything in one big transaction on Oracle? I guess performanceis one reason. Oracle's (at least with 8/8i) performance seems to tank very quickly on deletes as you increasethe number of records you delete in a single transaction. The other (at least with my understanding of Oracle internals)is that using smaller transactions will mean less rollback segment space used which reduces the likelyhood of yourtransaction getting killed due to Oracle running out of rollback space on a database that has heavy usage. Running out of rollback segments is the answer in our case. It happened more than once... > Since Postgresql's MVCC system doesn't require rollback segments I guess the performance question is the more importantof the two. If there could be a performance gain in Postgresql with "delete from X where Y limit Z" vs "deletefrom X where Y in (some subselect limit Z)" I'd think Csaba suggestion has some merit. But postgres has in turn the problem of not functional vacuum when you have long running transactions. That is a problem for heavily recycled tables like queue tables. Now recently I have solved the vacuum problem by regularly CLUSTER-ing our most heavily used queue table, so long running transactions are not anymore such a huge problem for us, but we still have the case of some user triggered operations which time out on the web before finishing on the DB. Some of those would make perfect sense to be done partially and the user then can restart the operation from where it left... now we often have 5 minutes of updates rolled back and leaving nothing useful but only dead rows. And yes, not everything we do must be perfectly transactional... sometimes the partial work worth more than partial work rolled back. Cheers, Csaba.
On Fri, May 19, 2006 at 04:22:42PM +0200, Csaba Nagy wrote:
> "Then use ctid."
>
> For the problem at hand in your post it is a good solution, except
> that it will cause a full table scan cause I guess few people have
> indexes on ctid. Or you have to write your queries really contrived,
> by duplicating most of your query conditions so that it can use some
> indexes. I'm not sure if you'll get away without at least 2 full
> table scans if using ctid and no indexes, one for the subquery and
> one for the delete itself... not to mention the need for something
> like a HashAggregate on the subquery results... all this is
> speculation, but for sure you'll spend 10x the time for optimizing
> the subquery then you would writing a simple DELETE with LIMIT.
Err, you don't need an index on ctid because the ctid represents that
physical location of the tuple on disk. ctids are what indexes use to
refer to tuples...
# explain DELETE FROM t WHERE ctid=(SELECT ctid FROM t WHERE pronargs=1 LIMIT 1);
QUERY PLAN
----------------------------------------------------------------
Tid Scan on t (cost=3.75..7.76 rows=2 width=6)
Filter: (ctid = $0)
InitPlan
-> Limit (cost=0.00..3.75 rows=1 width=6)
-> Seq Scan on t (cost=0.00..22.50 rows=6 width=6)
Filter: (pronargs = 1)
(6 rows)
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment
> Err, you don't need an index on ctid because the ctid represents that
> physical location of the tuple on disk. ctids are what indexes use to
> refer to tuples...
OK, then how you explain this:
db=# prepare test_001(bigint, bigint, smallint) as
db-# DELETE FROM big_table
db-# WHERE ctid IN
db-# (SELECT ctid FROM big_table
db(# WHERE col1=$2
db(# AND col2 IS NOT NULL
db(# AND col3 =$3
db(# AND col4 <> 'o'
db(# LIMIT 1000);
PREPARE
db=# explain execute test_001(1,1,1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=2165.98..24570725.13 rows=29 width=6)
Join Filter: ("outer".ctid = "inner".ctid)
-> Seq Scan on big_table (cost=0.00..1037188.04 rows=36063404
width=6)
-> Materialize (cost=2165.98..2166.27 rows=29 width=6)
-> Subquery Scan "IN_subquery" (cost=0.00..2165.95 rows=29
width=6)
-> Limit (cost=0.00..2165.66 rows=29 width=6)
-> Index Scan using idx_big_table_col2 on
big_table (cost=0.00..2165.66 rows=29 width=6)
Index Cond: (col1 = $2)
Filter: ((col2 IS NOT NULL) AND (col3 = $3)
AND ("col4" <> 'o'::bpchar))
(9 rows)
Cheers,
Csaba.
Regarding Oracle's ROWNUM - since they have to use that instead of OFFSET
and LIMIT, that isn't much of an argument for the Oracle way. When
converting queries into Oracle SQL, I always _really_ miss OFFSET and
LIMIT. They are much easier to use than ROWNUM, especially with ORDER BY.
I think that more databases support OFFSET and LIMIT than ROWNUM (the
Oracle way).
Personally, I have never wanted a DELETE or UPDATE with LIMIT. The one
time I did something similar in Oracle, I used partitions, and just dropped
or truncated the partition containing the old data.
Susan
Csaba Nagy
<nagy@ecircle-ag.com> To: Postgres general mailing list
<pgsql-general@postgresql.org>
Sent by: cc:
Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE
pgsql-general-owner@pos |-------------------|
tgresql.org | [ ] Expand Groups |
|-------------------|
05/19/2006 07:22
AM
Well, first of all, you're not competing here with MySQL in this case, but
with Oracle. Our application does this using Oracle's ROWNUM trick and it
works perfectly fine. Now I guess you think Oracle's ROWNUM is also stupid
in this case, but it certainly helps us writing cleaner SQL, and a missing
postgres alternative which is easy to use won't help you in attracting
Oracle users.
Cheers,
Csaba.
----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------
On Fri, 2006-05-19 at 17:43, SCassidy@overlandstorage.com wrote: > Regarding Oracle's ROWNUM - since they have to use that instead of OFFSET > and LIMIT, that isn't much of an argument for the Oracle way. When > converting queries into Oracle SQL, I always _really_ miss OFFSET and > LIMIT. They are much easier to use than ROWNUM, especially with ORDER BY. I would agree here, but for my purpose would have been a lot easier to parameterize the limit syntax only than to completely rewrite the query for postgres. > I think that more databases support OFFSET and LIMIT than ROWNUM (the > Oracle way). I actually don't care what's the syntax as long I can create a query with the syntactically equivalent parts in the same place of the query and the same number and order of parameters. Then it's fairly easy to parameterize it and have the same code handle it... otherwise I have to write special code for each data base. Sometimes I have to do that anyway for different reasons, but it is a PITA to maintain it, so I try to minimize the number of places where I have to do it. > Personally, I have never wanted a DELETE or UPDATE with LIMIT. The one > time I did something similar in Oracle, I used partitions, and just dropped > or truncated the partition containing the old data. Perfectly fine, I also don't use many of the features available :-) That doesn't mean it is not useful for others... Cheers, Csaba.
>----- Original Message ---- >From: SCassidy@overlandstorage.com >To: Csaba Nagy <nagy@ecircle-ag.com> >Cc: Postgres general mailing list <pgsql-general@postgresql.org>; >pgsql-general-owner@postgresql.org >Sent: Friday, May 19, 2006 10:43:43 AM >Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE > >Personally, I have never wanted a DELETE or UPDATE with LIMIT. The one >time I did something similar in Oracle, I used partitions, and just dropped >or truncated the partition containing the old data. > Yeah, that’s the proper way to handle the issue assuming that sufficient forethought was put into the design of the database. It becomes trivial to drop a partition part of your weekly/monthly maintenance. Unfortunately, when dealing with legacy database systems that have grown in a very organic way over a span of 5-10 yearsyou don't have much control over the schema and there is significant inertia present to leave things as they are. Asan example, the DBA group at the client I'm currently working has their databases managed by IBM global services and theywill push back on altering a table definition to use partitioning as it costs the organization money to have those changesput into production. Having a internal developer use a script to perform such maintenance as a batch process is considered"free" so you can guess how many such processes have been created over the years. Regards, Shelby Cain
> >Personally, I have never wanted a DELETE or UPDATE with LIMIT. The one > >time I did something similar in Oracle, I used partitions, and just dropped > >or truncated the partition containing the old data. > > > > Yeah, that’s the proper way to handle the issue assuming that sufficient forethought was put into the design of the database. It becomes trivial to drop a partition part of your weekly/monthly maintenance. Well, sometimes it's not that easy. How would you handle a batch processing system which stores the incoming requests in a queue table in the data base, and then periodically processes a batch of it, with the additional constraint that it is allowed to process at most 1000 at a time so it won't produce a too long running transaction ? Suppose the processing is quite costly, and the queue can have bursts of incoming requests which then have to be slowly processed... the requests are coming from the web and must be processed asynchronously, the insert into the data base must be very fast. Partitioning would be able to solve this kind of problem I guess, if one processing chunk is one partition, and it is dropped after processed, but it needs a whole lot of setup and I'm not sure how well it would work with largely variable bursts of data... the number of partitions could grow indefinitely, and there would be a race condition when there's low traffic and we need to process an incomplete chunk (after a maximum sleep timeout for e.g.) while there are still some incoming requests we don't want to loose. So what I'm talking about is not maintenance, but on-line operation... Cheers, Csaba.
Martijn van Oosterhout <kleptog@svana.org> writes:
> Err, you don't need an index on ctid because the ctid represents that
> physical location of the tuple on disk. ctids are what indexes use to
> refer to tuples...
> # explain DELETE FROM t WHERE ctid=(SELECT ctid FROM t WHERE pronargs=1 LIMIT 1);
This doesn't currently work for more than one tuple, though: the natural
locution would be "WHERE ctid IN (SELECT returning more than one tid)"
but the planner/executor don't cope with doing that efficiently. Might
be worth trying to fix that.
regards, tom lane
----- Original Message ---- >From: Csaba Nagy <nagy@ecircle-ag.com> >To: Shelby Cain <alyandon@yahoo.com> >Cc: SCassidy@overlandstorage.com; Postgres general mailing list ><pgsql-general@postgresql.org>; pgsql-general-owner@postgresql.org >Sent: Friday, May 19, 2006 11:46:42 AM >Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE > >Well, sometimes it's not that easy. How would you handle a batch >processing system which stores the incoming requests in a queue table in >the data base, and then periodically processes a batch of it, with the >additional constraint that it is allowed to process at most 1000 at a >time so it won't produce a too long running transaction ? Suppose the >processing is quite costly, and the queue can have bursts of incoming >requests which then have to be slowly processed... the requests are >coming from the web and must be processed asynchronously, the insert >into the data base must be very fast. I can't imagine a case where a properly tuned Postgresql installation with appropriate hardware backing it couldn't handlethat particular kind of workload pattern. However, I usually work with Oracle so tables used as queues don't havethe same performance issues you'd run into with Postgresql. Regardless, this type of queue problem can also be tackled by having your data layer persisting the input from the web inmemory (which maintains a low perceived response time to the client) and posting to the table as fast as the database allows. > >So what I'm talking about is not maintenance, but on-line operation... Different problems will always require different solutions. In the case you present I don't really think partitioning isthe answer. Regards, Shelby Cain
On Fri, May 19, 2006 at 05:19:37PM +0200, Csaba Nagy wrote: > > Since Postgresql's MVCC system doesn't require rollback segments I guess the performance question is the more importantof the two. If there could be a performance gain in Postgresql with "delete from X where Y limit Z" vs "deletefrom X where Y in (some subselect limit Z)" I'd think Csaba suggestion has some merit. > > But postgres has in turn the problem of not functional vacuum when you > have long running transactions. That is a problem for heavily recycled > tables like queue tables. I'd rather see time invested in coming up with a way to seperate long running and short running transactions from an MVCC standpoint than relying on the ability to shorten transactions lengths. But, there is another advantage to doing deletes or even updates in chunks; it means fewer dead rows in the table being modified, since autovacuum can go to work any time after the first long-running update/delete finishes. > Now recently I have solved the vacuum problem by regularly CLUSTER-ing > our most heavily used queue table, so long running transactions are not > anymore such a huge problem for us, but we still have the case of some > user triggered operations which time out on the web before finishing on > the DB. Some of those would make perfect sense to be done partially and > the user then can restart the operation from where it left... now we > often have 5 minutes of updates rolled back and leaving nothing useful > but only dead rows. BTW, there's a bug/issue with CLUSTER that makes it not entirely transaction safe. > And yes, not everything we do must be perfectly transactional... > sometimes the partial work worth more than partial work rolled back. Yes, something that some people in the community have a hard time accepting. A great example is storing session data for websites. It's convenient to do it in the database, but it can cause big performance problems with MVCC. But it's session data; if you have to wipe the table after a database crash, who cares? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, May 19, 2006 at 10:25:19AM -0700, Shelby Cain wrote: > ----- Original Message ---- > >From: Csaba Nagy <nagy@ecircle-ag.com> > >To: Shelby Cain <alyandon@yahoo.com> > >Cc: SCassidy@overlandstorage.com; Postgres general mailing list ><pgsql-general@postgresql.org>; pgsql-general-owner@postgresql.org > >Sent: Friday, May 19, 2006 11:46:42 AM > >Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE > > > >Well, sometimes it's not that easy. How would you handle a batch > >processing system which stores the incoming requests in a queue table in > >the data base, and then periodically processes a batch of it, with the > >additional constraint that it is allowed to process at most 1000 at a > >time so it won't produce a too long running transaction ? Suppose the > >processing is quite costly, and the queue can have bursts of incoming > >requests which then have to be slowly processed... the requests are > >coming from the web and must be processed asynchronously, the insert > >into the data base must be very fast. > > I can't imagine a case where a properly tuned Postgresql installation with appropriate hardware backing it couldn't handlethat particular kind of workload pattern. However, I usually work with Oracle so tables used as queues don't havethe same performance issues you'd run into with Postgresql. Just try and do (what should stay) a small queue table in the same database as long-running reporting transactions. As long as a long-running report is going you might as well suspend all vacuuming on that queue table, because it won't do you any good; the report transaction means that vacuum can't remove anything. I've seen a case where a queue table should always fit into a single database page; 2 at most. But because some transactions will run for a minute or two, that table is normally about 40 pages, almost entirely all dead space. Of course the same problem affects all the indexes on that table as well. I can't imagine how bad this would be if the database actually had hour-long reports that had to run... and luckily the system is quiet at night when pg_dump runs. > Regardless, this type of queue problem can also be tackled by having your data layer persisting the input from the webin memory (which maintains a low perceived response time to the client) and posting to the table as fast as the databaseallows. Uh, and just what happens when your web front-end crashes then?? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
----- Original Message ---- >From: Jim C. Nasby <jnasby@pervasive.com> >To: Shelby Cain <alyandon@yahoo.com> >Cc: Csaba Nagy <nagy@ecircle-ag.com>; SCassidy@overlandstorage.com; >Postgres general mailing list <pgsql-general@postgresql.org>;>pgsql-general-owner@postgresql.org >Sent: Monday, May 22, 2006 5:04:51 PM >Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE > >Just try and do (what should stay) a small queue table in the same >database as long-running reporting transactions. As long as a >long-running report is going you might as well suspend all vacuuming on >that queue table, because it won't do you any good; the report >transaction means that vacuum can't remove anything. > >I've seen a case where a queue table should always fit into a single >database page; 2 at most. But because some transactions will run for a >minute or two, that table is normally about 40 pages, almost entirely >all dead space. Of course the same problem affects all the indexes on >that table as well. > >I can't imagine how bad this would be if the database actually had >hour-long reports that had to run... and luckily the system is quiet at >night when pg_dump runs. My experience with job queues comes from clients that mostly use Oracle as the backend. However, even with Oracle a queuetable should be storing information about a job and not have records unnecessarily locked simply because they are being"worked on" by another hypothetical "job runner" process... by this I mean that the status of a job should be updatedto a distinct state at any given moment in time (eg: unprocessed, processing, processed). In the case I present above,if you are using Postgresql you wouldnt have any open long-running transactions on that table and vacuuming shouldwork... or am I misunderstanding the issue? >> Regardless, this type of queue problem can also be tackled by having your data >>layer persisting the input from the webin memory (which maintains a low >>perceived response time to the client) and posting to the table as fast as the >>databaseallows. > >Uh, and just what happens when your web front-end crashes then?? Sorry, I probably should have said middle tier instead of data layer. Obviously, no single design choice will cover 100%of circumstances. If your middle tier is located on the web server and you have a physical crash then obviously you are going to miss the jobsthere were being held in memory waiting to be posted to the database and you'll have to resubmit (if possible) on restart. Having sane restart requirements/procedures thought out beforehand also goes a long way to making recover from anunexpected failure easier. If your middle tier lives on another machine then you'd miss (at most) the one job that the web application was in the processof handing off to your middle tier. If your application is such that a missed job submission is a highly criticalproblem then you'd want a redundant solution for your middle tier to help avoid the problem or perhaps you mightwant to rethink posting asynchronously from your web UI and buffering the submissions. Regards, Shelby Cain
On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote: > BTW, there's a bug/issue with CLUSTER that makes it not entirely > transaction safe. For God's sake, don't fix that one ! I rely on it... now seriously, until there's a way to tell the DB that an exclusive lock is enough to vacuum all dead rows, even if they would be visible by other transactions, CLUSTER is the only thing I can use to circumvent the long running transaction syndrome. Cheers, Csaba.
On Tue, 2006-05-23 at 00:04, Jim C. Nasby wrote: > I can't imagine how bad this would be if the database actually had > hour-long reports that had to run... and luckily the system is quiet at > night when pg_dump runs. BTW, pg_dump is the only thing that stays in my way with the CLUSTER strategy against long running transactions, because it locks (in shared mode, but that's just enough) all dumped tables at the beginning. It would be nice to have an "exclude-table" option on it. I actually started working on a patch to allow that, I will make it just good enough for my purpose (very poor C skills here). Would that be interesting for others ? > > Regardless, this type of queue problem can also be tackled by having your data layer persisting the input from the webin memory (which maintains a low perceived response time to the client) and posting to the table as fast as the databaseallows. > > Uh, and just what happens when your web front-end crashes then?? Well, actually we do have some processes where we use a buffer in the web layer which collects ~1000 entries (or 5 minutes worth, whichever comes first) in memory and then flushes them to the DB. But the processing still happens in the DB, as we have a sizable cluster of web machines and it is still more efficient to handle the processing centrally than by each machine itself. So we still insert a batch of raw entries and bulk process them on the DB using stored procedures. If the web machine crashes, we loose some entries. But in those cases we have bigger problems too... We do have however a job table where the whole cluster puts tasks and the machine which has free resources takes some to process. We first used JMS for this purpose, but that was just not flexible enough to accommodate our constraints and rules of load distribution across the cluster. This is the real bitchy queue table. Cheers, Csaba.
On Tue, May 23, 2006 at 10:30:35AM +0200, Csaba Nagy wrote: > On Tue, 2006-05-23 at 00:04, Jim C. Nasby wrote: > > I can't imagine how bad this would be if the database actually had > > hour-long reports that had to run... and luckily the system is quiet at > > night when pg_dump runs. > > BTW, pg_dump is the only thing that stays in my way with the CLUSTER > strategy against long running transactions, because it locks (in shared > mode, but that's just enough) all dumped tables at the beginning. It > would be nice to have an "exclude-table" option on it. I actually > started working on a patch to allow that, I will make it just good > enough for my purpose (very poor C skills here). Would that be > interesting for others ? Well, being able to have finer control over what you're dumping is on the TODO list, and I think there was even consensus reached on -hackers as to how the syntax should work. But that's only a partial fix, because generally you'd want a complete dump of your database anyway. What would be better is if pg_dump could release locks as it no longer needs them, namely as it dumps each table. Though this might require pg_dump remembering some state information about each object since certain things are dumped after all the COPY commands, such as RI. This also doesn't address the issue of long-running transactions preventing dead rows from being vacuumed. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, May 23, 2006 at 10:19:25AM +0200, Csaba Nagy wrote: > On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote: > > BTW, there's a bug/issue with CLUSTER that makes it not entirely > > transaction safe. > > For God's sake, don't fix that one ! I rely on it... now seriously, > until there's a way to tell the DB that an exclusive lock is enough to > vacuum all dead rows, even if they would be visible by other > transactions, CLUSTER is the only thing I can use to circumvent the long > running transaction syndrome. I believe it's already fixed in HEAD/8.2. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote:
> On Tue, May 23, 2006 at 10:19:25AM +0200, Csaba Nagy wrote:
> > On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote:
> > > BTW, there's a bug/issue with CLUSTER that makes it not entirely
> > > transaction safe.
> >
> > For God's sake, don't fix that one ! I rely on it... now seriously,
> > until there's a way to tell the DB that an exclusive lock is enough to
> > vacuum all dead rows, even if they would be visible by other
> > transactions, CLUSTER is the only thing I can use to circumvent the long
> > running transaction syndrome.
>
> I believe it's already fixed in HEAD/8.2.
If it is, TODO doesn't reflect that:
* Make CLUSTER preserve recently-dead tuples per MVCC
requirements
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Mon, May 22, 2006 at 03:49:01PM -0700, Shelby Cain wrote: > My experience with job queues comes from clients that mostly use Oracle as the backend. However, even with Oracle a queuetable should be storing information about a job and not have records unnecessarily locked simply because they are being"worked on" by another hypothetical "job runner" process... by this I mean that the status of a job should be updatedto a distinct state at any given moment in time (eg: unprocessed, processing, processed). In the case I present above,if you are using Postgresql you wouldnt have any open long-running transactions on that table and vacuuming shouldwork... or am I misunderstanding the issue? The issue is that vacuum has to base it's decisions not on the oldest running transaction that has locks on a table, but on the oldest running transaction in the entire database, because that transaction could start reading any table at any time. Until that changes, long-running transactions of any kind and heavy-update tables simply won't mix well at all in a single database. I recently proposed a way around this [1], but it didn't get much traction. [1] http://archives.postgresql.org/pgsql-general/2006-05/msg00184.php -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
----- Original Message ---- >From: Jim C. Nasby <jnasby@pervasive.com> >To: Shelby Cain <alyandon@yahoo.com> >Cc: Csaba Nagy <nagy@ecircle-ag.com>; SCassidy@overlandstorage.com; Postgres >general mailing list <pgsql-general@postgresql.org>;pgsql-general-owner@postgresql.org >Sent: Tuesday, May 23, 2006 5:19:58 PM >Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE > >The issue is that vacuum has to base it's decisions not on the oldest >running transaction that has locks on a table, but on the oldest running >transaction in the entire database, because that transaction could start >reading any table at any time. Until that changes, long-running >transactions of any kind and heavy-update tables simply won't mix well >at all in a single database. Now I understand... thanks for the clarification. Regards, Shelby Cain
> > would be nice to have an "exclude-table" option on it. I actually > > started working on a patch to allow that, I will make it just good > > enough for my purpose (very poor C skills here). Would that be > > interesting for others ? > > Well, being able to have finer control over what you're dumping is on > the TODO list, and I think there was even consensus reached on -hackers > as to how the syntax should work. > I actually managed to do that, I have a pg_dump which accepts "-e table_name" multiple times, so I can exclude tables from the dump. It's nice to have the source at hand :-) For the tables I do exclude, works fine for me, might have problems in the generic case, e.g. when you have large object references in the excluded table, the referred large objects will still be transferred, and I don't know what happens if there are other tables depending on the excluded ones... in my case that's not the case. > But that's only a partial fix, because generally you'd want a complete > dump of your database anyway. What would be better is if pg_dump could > release locks as it no longer needs them, namely as it dumps each table. > Though this might require pg_dump remembering some state information > about each object since certain things are dumped after all the COPY > commands, such as RI. For my purpose that wouldn't help much, the queue tables are anyway useless in the dump (their job might have been already executed, with external side-effects as emails, and that can't be rolled back, and shouldn't be executed again either). So the queue tables are out of context anyway by the time I load the dump, so I can dump/reload them separately if needed, and I have to filter their content anyway. > This also doesn't address the issue of long-running transactions > preventing dead rows from being vacuumed. But it works fine with CLUSTER, at least as currently implemented. Cheers, Csaba.
On Wed, 2006-05-24 at 00:15, Bruce Momjian wrote: > Jim C. Nasby wrote: > > On Tue, May 23, 2006 at 10:19:25AM +0200, Csaba Nagy wrote: > > > On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote: > > > > BTW, there's a bug/issue with CLUSTER that makes it not entirely > > > > transaction safe. > > > > > > For God's sake, don't fix that one ! I rely on it... now seriously, > > > until there's a way to tell the DB that an exclusive lock is enough to > > > vacuum all dead rows, even if they would be visible by other > > > transactions, CLUSTER is the only thing I can use to circumvent the long > > > running transaction syndrome. > > > > I believe it's already fixed in HEAD/8.2. > > If it is, TODO doesn't reflect that: > > * Make CLUSTER preserve recently-dead tuples per MVCC > requirements Can I ask to leave in some kind of switch (configuration, syntax, I don't care) so that the old behavior is still available if needed ? Thanks, Csaba.