Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-21 Thread Gregory Stark
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> What if we only applied >> HOT to primary-key indexes, so that there was certainly not more than >> one index per table that the property applies to? > > The main objective of HOT is to enable retail vacuum of HOT-updated tuple

Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_class attributes and race conditions ?)

2007-03-19 Thread Heikki Linnakangas
Pavan Deolasee wrote: Heikki Linnakangas wrote: > Pavan Deolasee wrote: > We would only need the extra byte in HOT-updated tuples. Alternatively, we could use the bits we have free in infomask2. There's currently 5 bits free, using just 2 or 3 of those would get us quite far. Or just one, wh

Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_class attributes and race conditions ?)

2007-03-19 Thread Pavan Deolasee
Heikki Linnakangas wrote: > Pavan Deolasee wrote: >> 2. Heikki suggested an approach where we add a byte >> to tuple header and track HOT-ness of different indexes. >> The idea looks good but had a downside of increasing tuple >> header and complexity. > > We would only need the extra byte in HOT-

Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_class attributes and race conditions ?)

2007-03-19 Thread Heikki Linnakangas
Pavan Deolasee wrote: 2. Heikki suggested an approach where we add a byte to tuple header and track HOT-ness of different indexes. The idea looks good but had a downside of increasing tuple header and complexity. We would only need the extra byte in HOT-updated tuples. Alternatively, we could

Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_class attributes and race conditions ?)

2007-03-17 Thread Pavan Deolasee
Tom Lane wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: >> While creating an index, if a HEAP_ONLY tuple is found, >> CREATE INDEX [CONCURRENTLY] fails with an error and the >> user needs to SET HOT OFF and then try again. While turning >> HOT off, the entire table is CHILLed, holding Acces

Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_class attributes and race conditions ?)

2007-03-17 Thread Tom Lane
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > While creating an index, if a HEAP_ONLY tuple is found, > CREATE INDEX [CONCURRENTLY] fails with an error and the > user needs to SET HOT OFF and then try again. While turning > HOT off, the entire table is CHILLed, holding AccessExclusive > lock on th

CREATE INDEX and HOT (was [HACKERS] Question: pg_class attributes and race conditions ?)

2007-03-16 Thread Pavan Deolasee
How do we move forward with the CREATE INDEX issue with HOT ? There are quite a few suggestions and objections. Can we please discuss and decide on the plan ? I am very comfortable with the current state of HOT, the results are encouraging and I hope this issue does not become a showstopper. Her

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Simon Riggs
On Fri, 2007-03-16 at 16:59 -0400, Alvaro Herrera wrote: > Here's is a very simple, low-tech idea. How about checking whether the > new index requires chilling tuples; if it does, then elog(ERROR) until > all the indexes have been manually chilled, which would be done with an > "ALTER INDEX ... C

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Alvaro Herrera
Simon Riggs wrote: > > What if we only applied > > HOT to primary-key indexes, so that there was certainly not more than > > one index per table that the property applies to? > > On its own, I don't think this is a sufficiently wide use-case. > > Perhaps we should do this PLUS make HOT-semantics

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Joshua D. Drake
Gregory Stark wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> Just to throw my two bits in here :). If we do that, how does that >> effect the idea that most people in the web world use (id serial primary >> key), even though that is not what they are searching on? > > "affect". But I

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Simon Riggs
On Fri, 2007-03-16 at 12:40 -0400, Tom Lane wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > > Any thoughts on the overall approach ? > > Fragile and full of race conditions :-(. I thought from the beginning > that CREATE INDEX might be a showstopper for the whole HOT concept, > and it's s

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Pavan Deolasee
Heikki Linnakangas wrote: > Tom Lane wrote: >> What if we only applied >> HOT to primary-key indexes, so that there was certainly not more than >> one index per table that the property applies to? > > The main objective of HOT is to enable retail vacuum of HOT-updated > tuples. Doing the above wou

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Gregory Stark
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Just to throw my two bits in here :). If we do that, how does that > effect the idea that most people in the web world use (id serial primary > key), even though that is not what they are searching on? "affect". But I think you're right that genera

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Simon Riggs
On Fri, 2007-03-16 at 21:56 +0530, Pavan Deolasee wrote: > Any thoughts on the overall approach ? Any suggestions to > simplify things or any alternate designs ? Well your design is very different from what we discussed, so I think I should post my proposed design alongside this, for further dis

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Pavan Deolasee
Tom Lane wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: >> Any thoughts on the overall approach ? > > Fragile and full of race conditions :-(. > Yes, it looks a bit complex. But IMHO we can get around that. Do you have any ideas in mind about doing that ? > I thought from the beginning >

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Heikki Linnakangas
Tom Lane wrote: What if we only applied HOT to primary-key indexes, so that there was certainly not more than one index per table that the property applies to? The main objective of HOT is to enable retail vacuum of HOT-updated tuples. Doing the above would make it useless for that purpose, at

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Joshua D. Drake
Tom Lane wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: >> Any thoughts on the overall approach ? > > Fragile and full of race conditions :-(. I thought from the beginning > that CREATE INDEX might be a showstopper for the whole HOT concept, > and it's starting to look like that's the case

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Tom Lane
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > Any thoughts on the overall approach ? Fragile and full of race conditions :-(. I thought from the beginning that CREATE INDEX might be a showstopper for the whole HOT concept, and it's starting to look like that's the case. I think what we need to

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Pavan Deolasee
Tom Lane wrote: > > In what context are you proposing to do that, and won't this > high-strength lock in itself lead to deadlocks? > > The whole thing sounds exceedingly ugly anyway --- for example > what happens if the backend doing the CREATE INDEX fails and > is therefore unable to clear the fl

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Tom Lane
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > My understanding is that the backend which sets this attribute > must first acquire a lock on the heap relation of sufficient > strength so as to ensure that there are no concurrent UPDATErs, > update the pg_class row and then release the lock on the r

Re: [HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Alvaro Herrera
Pavan Deolasee wrote: > > > What is the safest way to access/modify the pg_class attribute > and still avoid any race conditions with the other backends ? > > A specific example is: To solve the CREATE INDEX problem with > HOT, I am thinking of adding (along with other things) a pg_class > boole

[HACKERS] Question: pg_class attributes and race conditions ?

2007-03-16 Thread Pavan Deolasee
What is the safest way to access/modify the pg_class attribute and still avoid any race conditions with the other backends ? A specific example is: To solve the CREATE INDEX problem with HOT, I am thinking of adding (along with other things) a pg_class boolean attribute, say hot_update_enable.