Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-22 Thread Zeugswetter Andreas ADI SD
I very much like Hannu's idea, but it does present some issues. I too liked Hannu's idea initially, but Tom raised a valid concern that it does not address the basic issue of root tuples. According to the idea, a DEAD root tuple can be used for a subsequent update of the same row.

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-22 Thread Pavan Deolasee
On 2/22/07, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: I very much like Hannu's idea, but it does present some issues. I too liked Hannu's idea initially, but Tom raised a valid concern that it does not address the basic issue of root tuples. According to the idea, a DEAD

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-22 Thread Zeugswetter Andreas ADI SD
Imho we should follow the swing idea. Yes, thats one option. Though given a choice I would waste four bytes in the heap-page than inserting a new index entry. No question about that. My point was, that it would mean wasting the 2 (2 must be enough for a slot pointer) bytes on every

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-22 Thread Pavan Deolasee
On 2/22/07, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: Yes, thats one option. Though given a choice I would waste four bytes in the heap-page than inserting a new index entry. No question about that. My point was, that it would mean wasting the 2 (2 must be enough for a slot

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-22 Thread Zeugswetter Andreas ADI SD
Yes, thats one option. Though given a choice I would waste four bytes in the heap-page than inserting a new index entry. No question about that. My point was, that it would mean wasting the 2 (2 must be enough for a slot pointer) bytes on every heap tuple, hot or not. And then

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-22 Thread Pavan Deolasee
On 2/22/07, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: I think you are still misunderstanding me, sorry if I am not beeing clear enough. When the row is hot-updated it is too late. You do not have room in the root for the line pointer. I think the word line pointer is causing

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-22 Thread Zeugswetter Andreas ADI SD
I think the word line pointer is causing some confusion here. Let me explain the idea again: Each page has a set of line pointers OR item-ids as they are referred in the code (I shall use the word item-id here after). The item-id stores the offset(15 bits), length (15 bits) and two

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-21 Thread Simon Riggs
On Mon, 2007-02-12 at 09:24 +0530, Pavan Deolasee wrote: On 2/12/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Hannu Krosing wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: What if we

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-21 Thread Pavan Deolasee
On 2/21/07, Simon Riggs [EMAIL PROTECTED] wrote: I very much like Hannu's idea, but it does present some issues. I too liked Hannu's idea initially, but Tom raised a valid concern that it does not address the basic issue of root tuples. According to the idea, a DEAD root tuple can be used

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-21 Thread Simon Riggs
On Thu, 2007-02-22 at 00:00 +0530, Pavan Deolasee wrote: On 2/21/07, Simon Riggs [EMAIL PROTECTED] wrote: I very much like Hannu's idea, but it does present some issues. I too liked Hannu's idea initially, but Tom raised a valid concern that it does not

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-16 Thread Hannu Krosing
Ühel kenal päeval, K, 2007-02-14 kell 10:41, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: OTOH, for same page HOT tuples, we have the command and trx ids stored twice first as cmax,xmax of the old tuple and as cmin,xmin of the updated tuple. One of these could probably be used

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-16 Thread Pavan Deolasee
On 2/16/07, Hannu Krosing [EMAIL PROTECTED] wrote: Ühel kenal päeval, K, 2007-02-14 kell 10:41, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: OTOH, for same page HOT tuples, we have the command and trx ids stored twice first as cmax,xmax of the old tuple and as cmin,xmin of

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-16 Thread Zeugswetter Andreas ADI SD
As described, you've made that problem worse because you're trying to say we don't know which of the chain entries is pointed at. There should be a flag, say HOT_CHAIN_ENTRY for the tuple the it's called HEAP_UPDATE_ROOT index(es) point at. And this should be the preferred CTID for

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-16 Thread Pavan Deolasee
On 2/16/07, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: As described, you've made that problem worse because you're trying to say we don't know which of the chain entries is pointed at. There should be a flag, say HOT_CHAIN_ENTRY for the tuple the it's called HEAP_UPDATE_ROOT

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-16 Thread Zeugswetter Andreas ADI SD
Just to avoid any confusion with the patch I sent out this week, we are setting HEAP_UPDATE_ROOT on all tuples which are HOT-updated. We set HEAP_ONLY_TUPLE for all tuples which does not have index reference. So may be combination of (HEAP_UPDATE_ROOT ~HEAP_ONLY_TUPLE) can be used to

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-16 Thread Pavan Deolasee
On 2/16/07, Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: Oh sorry. Thanks for the clarification. Imho HEAP_UPDATE_ROOT should be renamed for this meaning then (or what does ROOT mean here ?). Maybe HEAP_UPDATE_CHAIN ? Yes, you are right. There is some disconnect between what Simon

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-14 Thread Hannu Krosing
Ühel kenal päeval, T, 2007-02-13 kell 09:38, kirjutas Tom Lane: Heikki Linnakangas [EMAIL PROTECTED] writes: Hannu Krosing wrote: Are we actually doing that ? I.E are null bitmaps really allocated in 1 byte steps nowadays ? Yes. Not really; we still have to MAXALIGN at the end of the

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-14 Thread Pavan Deolasee
On 2/14/07, Hannu Krosing [EMAIL PROTECTED] wrote: OTOH, for same page HOT tuples, we have the command and trx ids stored twice first as cmax,xmax of the old tuple and as cmin,xmin of the updated tuple. One of these could probably be used for in-page HOT tuple pointer. I think we recently

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-14 Thread Tom Lane
Hannu Krosing [EMAIL PROTECTED] writes: Ühel kenal päeval, T, 2007-02-13 kell 09:38, kirjutas Tom Lane: It's all moot anyway since 8 bits isn't enough for a pointer ... With 8k pages and MAXALIGN=8 we just barely can, as with current page structure (tuple headers together with data) the

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-13 Thread Heikki Linnakangas
Hannu Krosing wrote: But actually that 1 free byte in the header is not currently just waste of space. If you have any nulls in your tuple, there's going to be a null bitmap in addition to the header. 1 byte is conveniently enough to store the null bitmap for a table with max 8 columns, Are

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-13 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: Hannu Krosing wrote: Are we actually doing that ? I.E are null bitmaps really allocated in 1 byte steps nowadays ? Yes. Not really; we still have to MAXALIGN at the end of the bitmap. The point is that you can get 8 bits in there before paying

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-13 Thread Pavan Deolasee
On 2/13/07, Tom Lane [EMAIL PROTECTED] wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Hannu Krosing wrote: Are we actually doing that ? I.E are null bitmaps really allocated in 1 byte steps nowadays ? Yes. Not really; we still have to MAXALIGN at the end of the bitmap. The point is

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-12 Thread Zeugswetter Andreas ADI SD
What are the problems with just shuffling the last (and only visible) tuple to replace the HOT-hain root and be done with it ? ctid stops being a reliable identifier. A backend selecting the row by ctid would need to take one step to the root slot to get at the tuple. This does seem

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-12 Thread mark
On Mon, Feb 12, 2007 at 12:48:06AM -0500, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: How about adding a new 2-byte field to header for in-page c_tid poiner for HOT ? We just finished sweating blood to get the tuple header size down to 23 bytes from 27 (which saves 8 bytes not 4

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-12 Thread Heikki Linnakangas
[EMAIL PROTECTED] wrote: On Mon, Feb 12, 2007 at 12:48:06AM -0500, Tom Lane wrote: We just finished sweating blood to get the tuple header size down to 23 bytes from 27 (which saves 8 bytes not 4 if MAXALIGN=8). We are not going to blow that again on HOT. I haven't had enough time to follow

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-12 Thread Hannu Krosing
Ühel kenal päeval, E, 2007-02-12 kell 17:23, kirjutas Heikki Linnakangas: [EMAIL PROTECTED] wrote: On Mon, Feb 12, 2007 at 12:48:06AM -0500, Tom Lane wrote: We just finished sweating blood to get the tuple header size down to 23 bytes from 27 (which saves 8 bytes not 4 if MAXALIGN=8). We

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Hannu Krosing
Ühel kenal päeval, K, 2007-02-07 kell 17:38, kirjutas Simon Riggs: When we try to UPDATE a tuple and the new tuple version doesn't fit on the block, we get the BufferCleanupLock if possible and then perform a single-block VACUUM. Any tuple that is both HEAP_DEAD HEAP_ONLY_TUPLE can be

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Tom Lane
Hannu Krosing [EMAIL PROTECTED] writes: What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a cycle of ctid pointers, which changes the lookup process from 'follow ctid chaint until the end' to 'follow the tid chain until you reach the

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Hannu Krosing
Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a cycle of ctid pointers, which changes the lookup process from 'follow ctid

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Pavan Deolasee
On 2/11/07, Hannu Krosing [EMAIL PROTECTED] wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a cycle of ctid pointers,

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Heikki Linnakangas
Pavan Deolasee wrote: On 2/11/07, Hannu Krosing [EMAIL PROTECTED] wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Heikki Linnakangas
Hannu Krosing wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a cycle of ctid pointers, which changes the lookup process from

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Pavan Deolasee
On 2/12/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Hannu Krosing wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-11 Thread Tom Lane
Hannu Krosing [EMAIL PROTECTED] writes: How about adding a new 2-byte field to header for in-page c_tid poiner for HOT ? We just finished sweating blood to get the tuple header size down to 23 bytes from 27 (which saves 8 bytes not 4 if MAXALIGN=8). We are not going to blow that again on HOT.

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Pavan Deolasee
On 2/9/07, Simon Riggs [EMAIL PROTECTED] wrote: On Wed, 2007-02-07 at 14:17 -0500, Tom Lane wrote: ISTM we could fix that by extending the index VACUUM interface to include two concepts: aside from remove these TIDs when you find them, there could be replace these TIDs with those TIDs when

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Simon Riggs
On Thu, 2007-02-08 at 14:47 +, Heikki Linnakangas wrote: However, the easiest solution would be to make CREATE INDEX wait until the old tuple is dead. That should be ok at least for concurrent CREATE INDEX, because it already has that kind of a wait between 1st and 2nd phase. I'm not

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Heikki Linnakangas
Tom Lane wrote: ISTM we could fix that by extending the index VACUUM interface to include two concepts: aside from remove these TIDs when you find them, there could be replace these TIDs with those TIDs when you find them. This would allow pointer-swinging to one of the child tuples, after which

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Teodor Sigaev
Implementing the replace these TIDs operation atomically would be simple, except for the new bitmap index am. It should be possible there That isn't simple (may be, even possible) from GIN. -- Teodor Sigaev E-mail: [EMAIL PROTECTED]

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Tom Lane
Teodor Sigaev [EMAIL PROTECTED] writes: Implementing the replace these TIDs operation atomically would be simple, except for the new bitmap index am. It should be possible there That isn't simple (may be, even possible) from GIN. I suspect that those pushing this idea only care about btrees

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Simon Riggs
On Fri, 2007-02-09 at 10:17 -0500, Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: Implementing the replace these TIDs operation atomically would be simple, except for the new bitmap index am. It should be possible there That isn't simple (may be, even possible) from GIN. I

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Simon Riggs
On Fri, 2007-02-09 at 13:39 +, Heikki Linnakangas wrote: Tom Lane wrote: ISTM we could fix that by extending the index VACUUM interface to include two concepts: aside from remove these TIDs when you find them, there could be replace these TIDs with those TIDs when you find them. This

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Heikki Linnakangas
Simon Riggs wrote: On Fri, 2007-02-09 at 13:39 +, Heikki Linnakangas wrote: Tom Lane wrote: ISTM we could fix that by extending the index VACUUM interface to include two concepts: aside from remove these TIDs when you find them, there could be replace these TIDs with those TIDs when you

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Hannu Krosing
Ühel kenal päeval, R, 2007-02-09 kell 13:39, kirjutas Heikki Linnakangas: Tom Lane wrote: ISTM we could fix that by extending the index VACUUM interface to include two concepts: aside from remove these TIDs when you find them, there could be replace these TIDs with those TIDs when you find

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Tom Lane
Hannu Krosing [EMAIL PROTECTED] writes: What are the problems with just shuffling the last (and only visible) tuple to replace the HOT-hain root and be done with it ? ctid stops being a reliable identifier. regards, tom lane ---(end of

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Simon Riggs
On Fri, 2007-02-09 at 13:47 -0500, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: What are the problems with just shuffling the last (and only visible) tuple to replace the HOT-hain root and be done with it ? ctid stops being a reliable identifier. Yes, that sums it up. The

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Simon Riggs
On Fri, 2007-02-09 at 13:16 +0530, Pavan Deolasee wrote: The second problem of concurrent index scans seems a bit more complex. We need a mechanism so that no tuples are missed or tuples are not returned twice. Since CHILLing of a tuple adds a new access path to the tuple from the index, a

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Bruce Momjian
Tom Lane wrote: Removing the root tuple will require a VACUUM *FULL*. That seems unacceptable ... it won't take too long for your table to fill up with stubs, and we don't want to return to the bad old days when periodic VACUUM FULL was unavoidable. ISTM we could fix that by extending

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Simon Riggs
On Fri, 2007-02-09 at 18:10 -0500, Bruce Momjian wrote: Tom Lane wrote: Removing the root tuple will require a VACUUM *FULL*. That seems unacceptable ... it won't take too long for your table to fill up with stubs, and we don't want to return to the bad old days when periodic VACUUM

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-09 Thread Bruce Momjian
Simon Riggs wrote: I need clarification here. Is removing dead heap tuple always going to require an index scan, or was this just for chilling a row (adding an index)? We can remove a tupled marked HEAP_ONLY_TUPLE when it is status HEAPTUPLE_DEAD. The HEAP_UPDATE_ROOT tuple can be

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-08 Thread Heikki Linnakangas
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The basic idea is that when a tuple is UPDATEd we can, in certain circumstances, avoid inserting index tuples for a tuple. Such tuples are marked HEAP_ONLY_TUPLE, but their storage is otherwise identical to other tuples. What is VACUUM

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-08 Thread Simon Riggs
On Wed, 2007-02-07 at 14:17 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The basic idea is that when a tuple is UPDATEd we can, in certain circumstances, avoid inserting index tuples for a tuple. Such tuples are marked HEAP_ONLY_TUPLE, but their storage is otherwise

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-07 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: The basic idea is that when a tuple is UPDATEd we can, in certain circumstances, avoid inserting index tuples for a tuple. Such tuples are marked HEAP_ONLY_TUPLE, but their storage is otherwise identical to other tuples. What is VACUUM FULL going to do

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-07 Thread Simon Riggs
On Wed, 2007-02-07 at 14:17 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The basic idea is that when a tuple is UPDATEd we can, in certain circumstances, avoid inserting index tuples for a tuple. Such tuples are marked HEAP_ONLY_TUPLE, but their storage is otherwise

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-07 Thread Joshua D. Drake
Simon Riggs wrote: Heap Only Tuples (HOT) is a simplification of earlier proposals for improving the way the server handles frequent updates, based upon what's been learned and feedback received. Heap Only Tuples The basic idea is that when a tuple is UPDATEd we can, in

Re: [HACKERS] HOT for PostgreSQL 8.3

2007-02-07 Thread Merlin Moncure
On 2/7/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Simon Riggs wrote: Heap Only Tuples (HOT) is a simplification of earlier proposals for improving the way the server handles frequent updates, based upon what's been learned and feedback received. Uhmmm... how often is that the case? Don't