hot update doesn't work?
in [PgSql]
|
Prev: [HACKERS] hot update doesn't work?
Next: [COMMITTERS] pgsql: Add PGFILEDESC description to Makefiles for all /contrib
From: "Kevin Grittner" on 12 May 2010 11:34 Pavel Stehule <pavel.stehule(a)gmail.com> wrote: > I would to repeatably update non indexed column of temp table. I > expected cheap operation, but it isn't true. You're updating the row 100000 times within a single transaction. I don't *think* HOT will reclaim a version of a row until the transaction which completed it is done and no other transactions can see that version any longer. It does raise the question, though -- couldn't a HOT update of a tuple *which was written by the same transaction* do an "update in place"? I mean, the updating transaction doesn't need to see the old row after this, and other transactions shouldn't see it either. I suspect that somewhere in the subtransaction or referential integrity areas there may be some issues with that, but it would be a clever optimization if it could be pulled off. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 12 May 2010 11:39 On Wed, May 12, 2010 at 11:34 AM, Kevin Grittner <Kevin.Grittner(a)wicourts.gov> wrote: > Pavel Stehule <pavel.stehule(a)gmail.com> wrote: > >> I would to repeatably update non indexed column of temp table. I >> expected cheap operation, but it isn't true. > > You're updating the row 100000 times within a single transaction. I > don't *think* HOT will reclaim a version of a row until the > transaction which completed it is done and no other transactions can > see that version any longer. It does raise the question, though -- > couldn't a HOT update of a tuple *which was written by the same > transaction* do an "update in place"? I mean, the updating > transaction doesn't need to see the old row after this, and other > transactions shouldn't see it either. > > I suspect that somewhere in the subtransaction or referential > integrity areas there may be some issues with that, but it would be > a clever optimization if it could be pulled off. scripting this outside of transaction does not exhibit the behavior -- even with autovac off relation size tops out arond 57k. vacuuming as it goes seems to top out around 200 row versions before hot catches them. I guess a good way to think of hot is a page level vacuum. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 12 May 2010 11:47 "Kevin Grittner" <Kevin.Grittner(a)wicourts.gov> writes: > You're updating the row 100000 times within a single transaction. I > don't *think* HOT will reclaim a version of a row until the > transaction which completed it is done and no other transactions can > see that version any longer. It does raise the question, though -- > couldn't a HOT update of a tuple *which was written by the same > transaction* do an "update in place"? Well ... in the first place there is not, ever, any such thing as "update in place". The correct question to ask is whether we could vacuum away the older elements of the HOT chain on the grounds that they are no longer of interest. What we would see is tuples with xmin equal to xmax and cmin different from cmax. The problem then is to determine whether there are any live snapshots with curcid between cmin and cmax. There is 0 hope of doing that from outside the originating backend. Now if heap_page_prune() is being run by the same backend that generated the in-doubt tuples, which I will agree is likely in a case like this, in principle we could do it. Not sure if it's really worth the trouble and nonorthogonal behavior. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Heikki Linnakangas on 12 May 2010 11:55 Tom Lane wrote: > The correct question to ask is whether we could > vacuum away the older elements of the HOT chain on the grounds that they > are no longer of interest. What we would see is tuples with xmin equal > to xmax and cmin different from cmax. The problem then is to determine > whether there are any live snapshots with curcid between cmin and cmax. > There is 0 hope of doing that from outside the originating backend. > Now if heap_page_prune() is being run by the same backend that generated > the in-doubt tuples, which I will agree is likely in a case like this, > in principle we could do it. There's an extra hurdle in the way: If you remove tuples in the middle of an update chain (not necessarily a HOT update chain), the latest tuple becomes inaccessible to other transactions running in read committed mode that might need to find the latest version of the row by following the ctid pointers. That's not an issue if the row was created in the same transaction too, but we don't know that in HeapTupleSatisfiesVacuum. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 12 May 2010 11:57
On Wed, May 12, 2010 at 11:47 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner(a)wicourts.gov> writes: >> You're updating the row 100000 times within a single transaction. I >> don't *think* HOT will reclaim a version of a row until the >> transaction which completed it is done and no other transactions can >> see that version any longer. It does raise the question, though -- >> couldn't a HOT update of a tuple *which was written by the same >> transaction* do an "update in place"? > > Well ... in the first place there is not, ever, any such thing as > "update in place". The correct question to ask is whether we could > vacuum away the older elements of the HOT chain on the grounds that they > are no longer of interest. What we would see is tuples with xmin equal > to xmax and cmin different from cmax. The problem then is to determine > whether there are any live snapshots with curcid between cmin and cmax. > There is 0 hope of doing that from outside the originating backend. > Now if heap_page_prune() is being run by the same backend that generated > the in-doubt tuples, which I will agree is likely in a case like this, > in principle we could do it. Not sure if it's really worth the trouble > and nonorthogonal behavior. update of same row in a single transaction is not going to come up that much and there are a number of simple work arounds to get better performance. isn't it possible to skip the snapshot check for temp tables though? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |