Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Tom Lane
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> But we've already had a couple of cases of interesting failures going >> unnoticed because of the noise level. Between duplicate reports about >> busted patches and transient problems on particular build machines >> (out of disk space

Re: [HACKERS] [COMMITTERS] pgsql: Bumping catversion due to changes to pg_trigger and pg_rewrite.

2007-03-19 Thread Tom Lane
Neil Conway <[EMAIL PROTECTED]> writes: > Jan Wieck wrote: >> BTW, the comment in this file says that we hope we never have more than >> 10 catversion changes per day, but to even make this possible we should >> start counting at zero, shouldn't we? > The comment says "hopefully we'll never commit

[HACKERS] Re: [COMMITTERS] pgsql: Changes pg_trigger and extend pg_rewrite in order to allow

2007-03-19 Thread Jan Wieck
On 3/19/2007 8:51 PM, Tom Lane wrote: [EMAIL PROTECTED] (Jan Wieck) writes: Changes pg_trigger and extend pg_rewrite in order to allow triggers and rules to be defined with different, per session controllable, behaviors for replication purposes. Surely this patch should've included a catversi

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Andrew Dunstan
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Tom Lane wrote: Actually what I *really* want is something closer to "show me all the unexplained failures", but unless Andrew is willing to support some way of tagging failures in the master database, I suppose that won't happe

Re: [HACKERS] Stats for multi-column indexes

2007-03-19 Thread Mark Kirkwood
Jeff Davis wrote: I know we can't keep stats on every combination of columns. My initial idea would be to only keep stats about a multi-column index (and probably optional for those, too). Maybe you would want to keep single column indexes too, so that (more) accurate estimates for bitmap-an

Re: [HACKERS] Stats for multi-column indexes

2007-03-19 Thread Jeff Davis
On Mon, 2007-03-19 at 21:24 -0400, Tom Lane wrote: > Jeff Davis <[EMAIL PROTECTED]> writes: > > We can already keep stats for a functional index. Is there a reason we > > can't keep stats for a multi-column index? > > The questions that need to be answered are (1) what stats are you gonna > collec

Re: [HACKERS] buildfarm not reporting xml flag correctly?

2007-03-19 Thread Andrew Dunstan
Tom Lane wrote: I just noticed that the buildfarm status page shows an "xml" flag in the legend, but not one of the HEAD machines is flagged as building xml; which is most certainly wrong. I see that - and I see the cause. It will be fixed when I overcome some permissions problems

[HACKERS] modifying the table function

2007-03-19 Thread Islam Hegazy
Hi there I am trying to modify the execution of the table function to work in iterator fashion instead of materializing the output. I have been digging the Postgresql code source for about a month now and I can figure out where the execution of the table function works. I will be very grateful

Re: [HACKERS] initdb fails - postgresql does not support leap seconds

2007-03-19 Thread Eric
Can't remember if it was in this forum, but a few people asked why I was downloading source and not binary. The reason is that I am experimenting with a different indexing (multi-dimensional) method. I looked hard at GiST, but it doesn't look like GiST supports multi- dimensional indexing method

Re: [HACKERS] Stats for multi-column indexes

2007-03-19 Thread Tom Lane
Jeff Davis <[EMAIL PROTECTED]> writes: > We can already keep stats for a functional index. Is there a reason we > can't keep stats for a multi-column index? The questions that need to be answered are (1) what stats are you gonna collect, and (2) exactly what are you going to do with them when you

[HACKERS] buildfarm not reporting xml flag correctly?

2007-03-19 Thread Tom Lane
I just noticed that the buildfarm status page shows an "xml" flag in the legend, but not one of the HEAD machines is flagged as building xml; which is most certainly wrong. regards, tom lane ---(end of broadcast)--- TIP 9: In

[HACKERS] Stats for multi-column indexes

2007-03-19 Thread Jeff Davis
I know the idea has come up a few times to do cross-column statistics to improve plans when the data distributions are dependent. I found a couple references in the archives: http://archives.postgresql.org/pgsql-hackers/2006-09/msg02118.php http://archives.postgresql.org/pgsql-hackers/2006-08/msg0

Re: [HACKERS] [COMMITTERS] pgsql: Changes pg_trigger and extend pg_rewrite in order to allow

2007-03-19 Thread Tom Lane
[EMAIL PROTECTED] (Jan Wieck) writes: > Changes pg_trigger and extend pg_rewrite in order to allow triggers and > rules to be defined with different, per session controllable, behaviors > for replication purposes. Surely this patch should've included a catversion bump. r

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Tom Lane
"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > There might be trouble if a second function has to be executed with > the same PL as an already running (but currently "stopped") > function. This would only work for PL that is thread-safe in some way. Seems a bit iffy. It strikes me that at least

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Tom Lane
"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > Here is a paper about portable userspace threads that I just googled. > http://www.gnu.org/software/pth/rse-pmt.ps I'm not impressed --- the list of unsolved problems is a bit too long. One that seems a showstopper for our purposes is lack of abilit

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Florian G. Pflug
Gregory Stark wrote: "Florian G. Pflug" <[EMAIL PROTECTED]> writes: Since context switching would occur only at two well-defined places (Some return_next_row function that PLs call when a SFR returns a row, and in the executor if no more previously returned rows from that SFR are available), th

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Florian G. Pflug
Andrew Dunstan wrote: Florian G. Pflug wrote: Just a thought - I believe that there are portable user-space thread implementations that contain little or no machine-specific code. What if postgres used one of those to switch from the PL into the executor and back after, say, 1000 rows were retu

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Tom Lane
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Actually what I *really* want is something closer to "show me all the >> unexplained failures", but unless Andrew is willing to support some way >> of tagging failures in the master database, I suppose that won't happen. > Who would d

[HACKERS] Make TIMESTAMP + TIME in the source code

2007-03-19 Thread Luis D. García
Hello, I'm writinng again because I need to make some validation for the incoming data (VALUES... on the INSERT-Stmt). This validation is based on adding a TIMESTAMP and a TIME kind of data. From the Shell I can make: template1=# select timestamp '02/03/85 7:00pm'+'00:00:02.3450' as "TIMESTAMP

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Andrew Dunstan
I wrote: 2. I was annoyed repeatedly that some buildfarm members weren't reporting log_archive_filenames entries, which forced going the long way round in the process I was using. Seems like we need some more proactive means for getting buildfarm owners to keep their script versions up-to-date.

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Andrew Dunstan
Tom Lane wrote: I think what would be nice is some way to view all the failures for a given branch, extending back not-sure-how-far. Right now the only way to see past failures is to look at individual machines' histories, which is not real satisfactory when you want a broader view. Actually wh

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Gregory Stark
"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > Since context switching would occur only at two well-defined places > (Some return_next_row function that PLs call when a SFR returns a row, > and in the executor if no more previously returned rows from that SFR > are available), this wouldn't intr

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Joe Conway
Andrew Dunstan wrote: Joe Conway wrote: Andrew Dunstan wrote: Are we really sure that this isn't a solution in search of a problem? The need for value-per-call is real (examples mentioned down-thread) and was anticipated from day one of the SRF implementation (in fact the first patch I wrote

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Andrew Dunstan
Joe Conway wrote: Andrew Dunstan wrote: Are we really sure that this isn't a solution in search of a problem? The need for value-per-call is real (examples mentioned down-thread) and was anticipated from day one of the SRF implementation (in fact the first patch I wrote was value-per-call,

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Joe Conway
Andrew Dunstan wrote: Are we really sure that this isn't a solution in search of a problem? The need for value-per-call is real (examples mentioned down-thread) and was anticipated from day one of the SRF implementation (in fact the first patch I wrote was value-per-call, not materialize). B

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Islam Hegazy
So, I understood from all those opinions that much of the work is to be done in the interface language interpreter not postgresql code itself. Am I right or I missed something? Regards Islam Hegazy - Original Message - From: "Andrew Dunstan" <[EMAIL PROTECTED]> To: "Florian G. Pflug"

Re: [HACKERS] CLUSTER and MVCC

2007-03-19 Thread Bruce Momjian
Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > We wouldn't clean up tuples that are visible to a transaction, but if > > you have one long-running transaction like pg_dump in a database with > > otherwise short transaction, you'll have a lot of tuples that are not > > vacuu

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Andrew Dunstan
Florian G. Pflug wrote: Just a thought - I believe that there are portable user-space thread implementations that contain little or no machine-specific code. What if postgres used one of those to switch from the PL into the executor and back after, say, 1000 rows were returned by the SFR? What

Re: [HACKERS] alter operator class

2007-03-19 Thread Tom Lane
Teodor Sigaev <[EMAIL PROTECTED]> writes: > 'alter operator class' command isn't "dumpable": > postgres=# ALTER OPERATOR CLASS name_pattern_ops USING btree RENAME TO > namep_ops; Sure it's dumpable. The point here is that name_pattern_ops is a built-in object and so pg_dump doesn't think it sho

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Florian G. Pflug
Gregory Stark wrote: "Andrew Dunstan" <[EMAIL PROTECTED]> writes: Yes, more or less. That's what made me think of it. OTOH, before we rush out and do it someone needs to show that it's a net win. I agree with Tom that making tuplestore faster would probably be a much better investment of time.

[HACKERS] alter operator class

2007-03-19 Thread Teodor Sigaev
'alter operator class' command isn't "dumpable": postgres=# ALTER OPERATOR CLASS name_pattern_ops USING btree RENAME TO namep_ops; postgres=# create table n (a name); postgres=# create index ni on n (a namep_ops); % pg_dump postgres | psql wow ERROR: operator class "namep_ops" does not ex

Re: [HACKERS] who gets paid for this

2007-03-19 Thread Bruce Momjian
Dave Page wrote: > Joshua D. Drake wrote: > > Christian Bird wrote: > >> Hi all, > >> > >> I'm a grad student at UC Davis studying the postgres community and I > >> wanted to know if some on this list could help me out. I'm studying > >> the factors that affect people "graduating" from being maili

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Gregory Stark
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > Yes, more or less. That's what made me think of it. > > OTOH, before we rush out and do it someone needs to show that it's a net win. > I > agree with Tom that making tuplestore faster would probably be a much better > investment of time. I don't t

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Tom Lane
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> The current buildfarm webpages make it easy to see when a branch tip >> is seriously broken, but it's not very easy to investigate transient >> failures, such as a regression test race condition that only >> materializes once in awh

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

2007-03-19 Thread Merlin Moncure
On 3/19/07, Pavan Deolasee <[EMAIL PROTECTED]> wrote: Yeah, I think CREATE INDEX CONCURRENTLY is much easier to solve. Though I am not completely convinced that we can do that without much changes to CREATE INDEX CONCURRENTLY logic. For example, I believe we still need to lock out HOT-updates bef

Re: [HACKERS] Patch license update to developer's FAQ

2007-03-19 Thread Bruce Momjian
Zeugswetter Andreas ADI SD wrote: > > > I don't think it is common. I didn't add that part, so if > > you also think it is rare, I will remove that distinction. New text: > > > > PostgreSQL is licensed under a BSD license. By posting a > patch > > to the public PostgreSQL mailling lis

Re: [HACKERS] Grouped Index Tuples / Clustered Indexes

2007-03-19 Thread Bruce Momjian
Simon Riggs wrote: > On Sun, 2007-03-11 at 19:06 +0100, Florian G. Pflug wrote: > > Heikki Linnakangas wrote: > > > There's a third related term in use as well. When you issue CLUSTER, the > > > table will be clustered on an index. And that index is then the "index > > > the table is clustered on

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Tom Lane
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Richard Huxton wrote: >> Is this not what we do with aggregate functions at present? > Yes, more or less. That's what made me think of it. > OTOH, before we rush out and do it someone needs to show that it's a net > win. Yeah, because this isn't doin

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Andrew Dunstan
Richard Huxton wrote: Andrew Dunstan wrote: Actually, I think we could teach the PLs to do it - just not transparently, so we'd need to mark which functions used the new protocol. Such functions would get a state object as an implied first argument, so in plperl it might work like this (for

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Richard Huxton
Andrew Dunstan wrote: Actually, I think we could teach the PLs to do it - just not transparently, so we'd need to mark which functions used the new protocol. Such functions would get a state object as an implied first argument, so in plperl it might work like this (for a generate_series-like

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

2007-03-19 Thread Simon Riggs
On Mon, 2007-03-19 at 10:29 -0500, Merlin Moncure wrote: > On 3/17/07, Simon Riggs <[EMAIL PROTECTED]> wrote: > > I'm very comfortable with the idea that HOT can be turned on/off for a > > table. That gives us a workaround to bugs. Previously, changing things > > like WITHOUT OIDS was done over two

Re: [HACKERS] getTypeIOParam is wrong for domains over array types?

2007-03-19 Thread Tom Lane
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > On 3/19/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> I think probably the right solution is to adjust getTypeIOParam so that >> it only examines typelem for base types (typtype = 'b'). Can anyone >> see a downside to that? > will that skip intermediate

Re: [HACKERS] getTypeIOParam is wrong for domains over array types?

2007-03-19 Thread Merlin Moncure
On 3/19/07, Tom Lane <[EMAIL PROTECTED]> wrote: In lsyscache.c we have /* * Array types get their typelem as parameter; everybody else gets their * own type OID as parameter. (This is a change from 8.0, in which only * composite types got their own OID as parameter.) */

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

2007-03-19 Thread Merlin Moncure
On 3/17/07, Simon Riggs <[EMAIL PROTECTED]> wrote: I'm very comfortable with the idea that HOT can be turned on/off for a table. That gives us a workaround to bugs. Previously, changing things like WITHOUT OIDS was done over two releases, so I'd suggest the same thing here. Add the option now, di

[HACKERS] getTypeIOParam is wrong for domains over array types?

2007-03-19 Thread Tom Lane
In lsyscache.c we have /* * Array types get their typelem as parameter; everybody else gets their * own type OID as parameter. (This is a change from 8.0, in which only * composite types got their own OID as parameter.) */ if (OidIsValid(typeStruct->typelem)) r

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

2007-03-19 Thread Heikki Linnakangas
Hannu Krosing wrote: Ühel kenal päeval, E, 2007-03-19 kell 12:05, kirjutas Simon Riggs: On Mon, 2007-03-19 at 10:51 +, Heikki Linnakangas wrote: Pavan Deolasee wrote: We've already used three of those, two for tracking HEAP_ONLY and HOT_UPDATED tuples and one for tracking fragmented tuple.

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Teodor Sigaev
Given that rechecking requires Expr and state structures, maybe it would be easier to make the operators RECHECK so the planner does the right thing now, but make a flag that tells the index scan *not* to recheck this tuple. That would seem slightly less work and fit better with the existing code.

[HACKERS] Back from vacation

2007-03-19 Thread Bruce Momjian
I am back from vacation and reading email again. I should be caught up in less than a week (2.6k emails), and easily ready for feature freeze April 1. For the curious, I took a 10-day family vacation in Costa Rica. A private tour guide took us to three areas of Costa Rica. We saw an active volc

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

2007-03-19 Thread Hannu Krosing
Ühel kenal päeval, E, 2007-03-19 kell 12:05, kirjutas Simon Riggs: > On Mon, 2007-03-19 at 10:51 +, Heikki Linnakangas wrote: > > Pavan Deolasee wrote: > > > Heikki Linnakangas wrote: > > > > Pavan Deolasee wrote: > > > > We would only need the extra byte in HOT-updated tuples. > > > Alterna

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Teodor Sigaev
Interesting. So we'd add a flag to the index tuples in GiST indicating if the tuple is lossily compressed or not. The compress-function would set that flag when it performs lossy compression, and gistgettuple would return it to the caller. Keys in GiST index may be another type than column on

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Andrew Dunstan
Heikki Linnakangas wrote: Tom Lane wrote: Neil Conway <[EMAIL PROTECTED]> writes: Returning control to the backend for every row returned would likely be excessive, but you could return once every k rows and get most of the benefits of both approaches (k might be on the order of 1000). Howev

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Gregory Stark
"Gregory Stark" <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> writes: > >> row-ordering discrepancy in rowtypes test| >> 2007-02-10 03:00:02 | 3 > > Is this because the test is fixed or unfixable? If not shouldn't the test get > an ORDER BY clause

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Tom Lane
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Martijn van Oosterhout wrote: >> IIRC indexes can already ask to have the system recheck conditions on >> returned tuples. For example GiST can return more tuples than actually >> match. That's what the amopreqcheck column is for in pg_amop. > Right

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Martijn van Oosterhout
On Mon, Mar 19, 2007 at 04:40:52PM +0300, Teodor Sigaev wrote: > >Right, except that flag is per operator in operator class, and what I'm > >proposing is that the index could pass a flag per tuple in the scan. > > That might make sense even for GiST. Sometimes complex compressions is used > in

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Tom Lane
Gregory Stark <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> writes: >> missing BYTE_ORDER definition for Solaris| >> 2007-01-10 14:18:23 | 1 > What is this BYTE_ORDER macro? Should I be using it instead of the > AC_C_BIGENDIAN test in configure for t

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Heikki Linnakangas
Teodor Sigaev wrote: Right, except that flag is per operator in operator class, and what I'm proposing is that the index could pass a flag per tuple in the scan. That might make sense even for GiST. Sometimes complex compressions is used in GiST opclasses. If indexing value is rather small th

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Andrew Dunstan
Tom Lane wrote: BTW, before I forget, this little project turned up a couple of small improvements for the current buildfarm infrastructure: 1. There are half a dozen entries with obviously bogus timestamps: bfarm=# select sysname,snapshot,branch from mfailures where snapshot < '2004-01-01';

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Teodor Sigaev
Right, except that flag is per operator in operator class, and what I'm proposing is that the index could pass a flag per tuple in the scan. That might make sense even for GiST. Sometimes complex compressions is used in GiST opclasses. If indexing value is rather small then it's stored in inde

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Stefan Kaltenbrunner
Gregory Stark wrote: "Tom Lane" <[EMAIL PROTECTED]> writes: Also, for completeness, the causes I wrote off as not interesting (anymore, in some cases): missing BYTE_ORDER definition for Solaris| 2007-01-10 14:18:23 | 1 What is this BYTE_ORDER macro? Should I

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Heikki Linnakangas
Martijn van Oosterhout wrote: On Mon, Mar 19, 2007 at 12:23:01PM +, Heikki Linnakangas wrote: Currently amgettuple returns one matching tuple at a time, in index order. I'm proposing two changes to add support for - candidate matches IIRC indexes can already ask to have the system recheck

Re: [HACKERS] Buildfarm feature request: some way to track/classify failures

2007-03-19 Thread Gregory Stark
"Tom Lane" <[EMAIL PROTECTED]> writes: > Also, for completeness, the causes I wrote off as not interesting > (anymore, in some cases): > > missing BYTE_ORDER definition for Solaris| > 2007-01-10 14:18:23 | 1 What is this BYTE_ORDER macro? Should I be using it in

Re: [HACKERS] Indexam interface proposal

2007-03-19 Thread Martijn van Oosterhout
On Mon, Mar 19, 2007 at 12:23:01PM +, Heikki Linnakangas wrote: > Currently amgettuple returns one matching tuple at a time, in index > order. I'm proposing two changes to add support for > - candidate matches IIRC indexes can already ask to have the system recheck conditions on returned tupl

Re: [HACKERS] Bug in UTF8-Validation Code?

2007-03-19 Thread Mario Weilguni
Am Sonntag, 18. März 2007 12:36 schrieb Martijn van Oosterhout: > > It seems to me that the easiest solution would be to forbid \x?? escape > sequences where it's greater than \x7F for UTF-8 server encodings. > Instead introduce a \u escape for specifying the unicode character > directly. Under the

[HACKERS] Indexam interface proposal

2007-03-19 Thread Heikki Linnakangas
Currently amgettuple returns one matching tuple at a time, in index order. I'm proposing two changes to add support for - candidate matches - partial ordering Those two features are essential to make clustered indexes work, and in the future, binned bitmap indexes that don't have a bitmap for e

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

2007-03-19 Thread Simon Riggs
On Mon, 2007-03-19 at 10:51 +, Heikki Linnakangas wrote: > 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

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

2007-03-19 Thread Simon Riggs
On Mon, 2007-03-19 at 16:06 +0530, Pavan Deolasee wrote: > Simon Riggs wrote: > > > > This problem is solved by moving the wait (for all transactions in > > reference snapshot to finish) so that it is now between the first and > > second scans, as described. > > > > During the second Vscan we

Re: [HACKERS] modifying the tbale function

2007-03-19 Thread Heikki Linnakangas
Tom Lane wrote: Neil Conway <[EMAIL PROTECTED]> writes: Returning control to the backend for every row returned would likely be excessive, but you could return once every k rows and get most of the benefits of both approaches (k might be on the order of 1000). However, this still leaves us wi

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_classattributes and race conditions ?)

2007-03-19 Thread Pavan Deolasee
Simon Riggs wrote: > > This problem is solved by moving the wait (for all transactions in > reference snapshot to finish) so that it is now between the first and > second scans, as described. > > During the second Vscan we would prune each block, so the only remaining > tuple in the block when the

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: [HACKERS] [PATCHES] Bitmapscan changes

2007-03-19 Thread Heikki Linnakangas
Grzegorz Jaskiewicz wrote: On Mar 16, 2007, at 10:12 PM, Heikki Linnakangas wrote: You'll obviously need to run it with the patch applied. I'd suggest to enable stats_block_level to see the effect on buffer cache hit/miss ratio. groupeditems-42-pghead.patch.gz is enough, or it needs maintai

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: [HACKERS] ILIKE and indexes

2007-03-19 Thread Guillaume Smet
On 3/19/07, Tom Lane <[EMAIL PROTECTED]> wrote: Not if you have an index on lower(col) which one supposes you'd have anyway for such an application. Or are you running an ancient PG release? Yes, you're right. Looking at my history I can't find what my error was - I analyzed the table several

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

2007-03-19 Thread Simon Riggs
On Mon, 2007-03-19 at 09:28 +, Simon Riggs wrote: > On Mon, 2007-03-19 at 14:33 +0530, Pavan Deolasee wrote: > > Simon Riggs wrote: > > > > > > > > > We *must* make CREATE INDEX CONCURRENTLY work with HOT. The good news is > > > I think we can without significant difficulty. > > > > > > >

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

2007-03-19 Thread Simon Riggs
On Mon, 2007-03-19 at 14:33 +0530, Pavan Deolasee wrote: > Simon Riggs wrote: > > > > > > We *must* make CREATE INDEX CONCURRENTLY work with HOT. The good news is > > I think we can without significant difficulty. > > > > Yeah, I think CREATE INDEX CONCURRENTLY is much easier to solve. Though

[HACKERS] CREATE INDEX and HOT - revised design

2007-03-19 Thread Pavan Deolasee
There are few things I realized over the weekend while going through the code: 1. It looks like a bad idea to use ALTER TABLE .. to chill a table becuase ALTER TABLE takes AccessExclusive lock on the table. But it would still be a good idea to have ALTER TABLE .. to turn HOT-updates ON/OFF.

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

2007-03-19 Thread Pavan Deolasee
Simon Riggs wrote: > > > We *must* make CREATE INDEX CONCURRENTLY work with HOT. The good news is > I think we can without significant difficulty. > Yeah, I think CREATE INDEX CONCURRENTLY is much easier to solve. Though I am not completely convinced that we can do that without much changes to CR