Re: [HACKERS] Suggestion: Which Binary?
David Wheeler <[EMAIL PROTECTED]> writes: > On Mar 31, 2006, at 16:01, Tom Lane wrote: >> It seems rather pointless to document two instances of what is in fact >> a generic autoconf-script behavior ... > I'm sorry to be such a moron about this, but what exactly is that > behavior? That you can specify an environment variable for whatever * > is in --with-*? The generic autoconf documentation says You can give `configure' initial values for configuration parameters by setting variables in the command line or in the environment. Here is an example: ./configure CC=c89 CFLAGS=-O2 LIBS=-lposix This isn't super helpful, of course, since it doesn't say exactly which variables any particular autoconf script responds to. But pretty much all of the programs that a configure script searches for are reflected as variables. A quick grep through our configure script for the phrase "Let the user override" finds a couple dozen hits, and that's just for programs, never mind non-program variables. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] listen not schema-aware
Neil Conway <[EMAIL PROTECTED]> writes: > i.e. the LISTEN/NOTIFY argument is not the name of a relation, so it > wouldn't make much sense to schema-qualify it. I'm not entirely sure why we even have the grammar allowing qualified names in these statements. It's not documented that you can do that. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] listen not schema-aware
On Fri, 2006-03-31 at 20:27 -0500, Agent M wrote: > Why is the schema ignored entirely when using listen/notify? Per the docs: Commonly, the notification name is the same as the name of some table in the database, and the notify event essentially means, "I changed this table, take a look at it to see what's new". But no such association is enforced by the NOTIFY and LISTEN commands. i.e. the LISTEN/NOTIFY argument is not the name of a relation, so it wouldn't make much sense to schema-qualify it. -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] First Aggregate Funtion?
On 3/31/06, Martijn van Oosterhout wrote: > On Fri, Mar 31, 2006 at 03:02:47PM -0600, Tony Caduto wrote: > > Has there ever been any talk of adding a first aggregate function? > > It would make porting from Oracle and Access much easier. > > > > Or is there something in the contrib modules that I might have missed? > > There are several oracle compatability modules: > > http://pgfoundry.org/projects/oracompat/ > http://pgfoundry.org/projects/orafce/ > > I'm sure there's many more if you look... If all you want is FIRST() and LAST() then: -- Create a function that always returns the first non-NULL item CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END; $$ LANGUAGE SQL STABLE; -- And then wrap an aggreagate around it CREATE AGGREGATE public.first ( sfunc= public.first_agg, basetype = anyelement, stype= anyelement ); -- Create a function that always returns the last non-NULL item CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ SELECT $2; $$ LANGUAGE SQL STABLE; -- And then wrap an aggreagate around it CREATE AGGREGATE public.last ( sfunc= public.last_agg, basetype = anyelement, stype= anyelement ); Hope that helps! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: Which Binary?
On Fri, Mar 31, 2006 at 06:52:51PM -0500, Tom Lane wrote: > David Wheeler <[EMAIL PROTECTED]> writes: > > But that's a PITA. I'd much rather have been able to tell configure > > *which* perl to use: > > >./configure --with-perl=/usr/bin/perl5.8.6 > > The more usual way to handle this sort of thing is to put each > version of perl in a different directory, and then you can alter > PATH while running configure to pick which one you want. I've got > several versions of perl on this machine that I select that way ... > it doesn't require any special smarts on the part of the perl-using > program, and it scales to handle multiple versions of other things > like Tcl, too. You mean something more like this? PATH=/path/to/perl:/usr/bin:/bin ./configure ... Sounds good, except when the perl people have in mind is on the same path as other perls. How would changing $PATH help with a situation like that? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] listen not schema-aware
Why is the schema ignored entirely when using listen/notify? I couldn't find any mention of this in the documentation. Ideally, it should support schemas (and store any string it takes) but it should at least throw an error when a schema is prepended. I guess the workaround is to simply delete the period. client 1: listen schema1.msg; client 2: notify schema1.msg; notify schema2.msg; client 1: Asynchronous notification "msg" received from server process with PID X. Asynchronous notification "msg" received from server process with PID X. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 16:01, Tom Lane wrote: It seems rather pointless to document two instances of what is in fact a generic autoconf-script behavior ... I'm sorry to be such a moron about this, but what exactly is that behavior? That you can specify an environment variable for whatever * is in --with-*? Thanks, David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 15:52, Tom Lane wrote: The more usual way to handle this sort of thing is to put each version of perl in a different directory, and then you can alter PATH while running configure to pick which one you want. I've got several versions of perl on this machine that I select that way ... it doesn't require any special smarts on the part of the perl-using program, and it scales to handle multiple versions of other things like Tcl, too. I would normally do that, as well, but in this case, I wanted my self- compiled Perl to always be what runs (as a general rule), so I had it hard link itself in /usr/bin as well as /usr/local/bin. It is only in this one case where I need the stock Perl to be found that things get wonky for me. :-) Best, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestion: Which Binary?
David Wheeler <[EMAIL PROTECTED]> writes: > Yes, but even the environment variables get me what I want. I > therefore respectfully submit the attached patch to document them in > the INSTALL file. It seems rather pointless to document two instances of what is in fact a generic autoconf-script behavior ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Suggestion: Which Binary?
David Wheeler <[EMAIL PROTECTED]> writes: > But that's a PITA. I'd much rather have been able to tell configure > *which* perl to use: >./configure --with-perl=/usr/bin/perl5.8.6 The more usual way to handle this sort of thing is to put each version of perl in a different directory, and then you can alter PATH while running configure to pick which one you want. I've got several versions of perl on this machine that I select that way ... it doesn't require any special smarts on the part of the perl-using program, and it scales to handle multiple versions of other things like Tcl, too. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 12:40, Josh Berkus wrote: In support of David's suggestion, I'll point out that most other OSS software configuration scripts (Apache, PHP, etc.) I deal with supports the above syntax. Yes, but even the environment variables get me what I want. I therefore respectfully submit the attached patch to document them in the INSTALL file. Best, David Index: doc/src/sgml/installation.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/installation.sgml,v retrieving revision 1.252 diff -u -r1.252 installation.sgml --- doc/src/sgml/installation.sgml 5 Jan 2006 03:01:32 - 1.252 +++ doc/src/sgml/installation.sgml 31 Mar 2006 22:08:07 - @@ -182,6 +182,14 @@ PL/Perl you need a full Perl installation, including the libperl library and the header files. + configure will collect this information + from whatever perl is in your PATH; + if you'd like it to use an alternate perl, simply + specify its location via the PERL environment + variable, e.g., PERL=/usr/bin/per5.8.6 + + + Since PL/Perl will be a shared library, the libperl libperl library must be a shared library @@ -219,6 +227,11 @@ Python 1.6 and later; users of earlier versions of Python will need to install it. + configure will collect this information + from whatever python is in your PATH; + if you'd like it to use an alternate python, simply + specify its location via the PYTHON environment + variable, e.g., PYTHON=/usr/bin/per5.8.6 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] First Aggregate Funtion?
On Fri, Mar 31, 2006 at 03:02:47PM -0600, Tony Caduto wrote: > Has there ever been any talk of adding a first aggregate function? > It would make porting from Oracle and Access much easier. > > Or is there something in the contrib modules that I might have missed? There are several oracle compatability modules: http://pgfoundry.org/projects/oracompat/ http://pgfoundry.org/projects/orafce/ I'm sure there's many more if you look... -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[HACKERS] First Aggregate Funtion?
Has there ever been any talk of adding a first aggregate function? It would make porting from Oracle and Access much easier. Or is there something in the contrib modules that I might have missed? Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Suggestion: Which Binary?
People: >./configure --with-perl=/usr/bin/perl5.8.6 In support of David's suggestion, I'll point out that most other OSS software configuration scripts (Apache, PHP, etc.) I deal with supports the above syntax. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 12:05, Seneca Cunningham wrote: Like passing PERL=/usr/bin/perl5.8.6 to configure? Is that currently supported? Because, if so, it's documented AFAICT. Best, David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: Which Binary?
David Wheeler wrote: But that's a PITA. I'd much rather have been able to tell configure *which* perl to use: ./configure --with-perl=/usr/bin/perl5.8.6 Would it be possible to add support for an optional argument to the PL/* options (--with-perl,--with-python, --with-tcl) so that we can get it to use the correct binary without having to resort to any shenanigans? Like passing PERL=/usr/bin/perl5.8.6 to configure? -- Seneca Cunningham [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Suggestion: Which Binary?
Dear PostgreSQL Hackers, I recently ran into an issue where I was having trouble compiling PostgreSQL with PL/Perl. Although Mac OS X 10.4 comes with a dynamic Perl, I long ago compiled my own Perl, which is static. So /usr/bin/ perl was my static Perl, and /usr/bin/perl5.8.6 is the stock Perl. But of course, PostgreSQL's configure script was just executing 'perl' and finding it in the path, thus getting my static Perl which, of course, wouldn't work. I got 'round this by temporarily moving things around: rm /usr/bin/perl ln /usr/bin/perl5.8.6 /usr/bin/perl ./configure --with-perl rm /usr/bin/perlo ln /usr/local/bin/perl5.8.8 /usr/bin/perl But that's a PITA. I'd much rather have been able to tell configure *which* perl to use: ./configure --with-perl=/usr/bin/perl5.8.6 Would it be possible to add support for an optional argument to the PL/* options (--with-perl,--with-python, --with-tcl) so that we can get it to use the correct binary without having to resort to any shenanigans? Just an idea. Thanks! David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL dirty-buffer management bug
I wrote: > I'm thinking we should change the code and the README to specify that > you must mark the buffer dirty before you can END_CRIT_SECTION(). While looking at this I realized that in fact we need to, and do, mark the buffer dirty even earlier than that: look at bufmgr.c LockBuffer and SyncOneBuffer comments. LockBuffer is marking the buffer dirty before we even start the critical section. Because of that, there is no actual bug here at present. But what we've got is confusing, klugy, inefficient code (it's inefficient because it sometimes marks buffers dirty without changing them). I think it's time to clean this up. The correct sequence of operations is really pin and lock buffer(s) START_CRIT_SECTION() apply change to buffer(s), and mark them dirty emit XLOG record END_CRIT_SECTION() unlock and unpin buffer(s) I think we ought to rename WriteNoReleaseBuffer to MarkBufferDirty to convey its true function, and use it in the third step of this sequence. We could get rid of the klugy force-dirty in LockBuffer, and get rid of the poorly named WriteBuffer altogether --- the unpin operation would now always just be ReleaseBuffer. Any objections? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_class catalog question...
On 3/31/06, Tom Lane <[EMAIL PROTECTED]> wrote: > This argument falls flat when you consider that the width of a CHAR > entry is measured in characters, not bytes, and therefore its physical > size is not fixed even if its logical width is. Gotta love multibyte :) -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Index vacuum improvements
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > On Wed, 29 Mar 2006, Tom Lane wrote: >> That loses the ability to reflect tuple deadness back into LP_DELETE >> flags, no? > At first glance, it doesn't look so hard. index_getmulti could mark > those tids that are dead, and btgetmulti would rescan the index page and > set LP_DELETE on all tuples that are still there. > We don't have to care about splits; if the index tuple is no longer where > it used to be, just ignore it. Right, no? True --- as long as there's even a reasonable probability of the tuple getting marked, we'll get the performance benefit. I don't see a way to make it work for bitmap indexscans though --- by the time we visit the heap, the index has long since forgotten where those index entries were. I think this may be worth doing even disregarding any possible vacuum speedup, simply because it'll reduce the number of index page lock/unlock cycles needed during a regular indexscan. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] WAL dirty-buffer management bug
On Fri, 2006-03-31 at 09:36 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Problem for indexes only. heap xlrecs don't specify exact insert points > > Sure they do. They had better, else (for example) the associated index > insertions will be wrong. Yep, you're right. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_class catalog question...
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > About the only reason I use CHAR in other databases systems is when I > know that the field will always contain the same amount of data, ie: > storing a SHA1. In these cases it's silly to have a 4 byte overhead to > store length. I really wish CHAR in PostgreSQL worked this way, so it > would be a welcome addition to have a type that did work this way. In > fact, I'd argue that CHAR should be made to work that way, and what's > currently called CHAR should be renamed for those who wish to use it. This argument falls flat when you consider that the width of a CHAR entry is measured in characters, not bytes, and therefore its physical size is not fixed even if its logical width is. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Slony-I for circular replication
On Fri, 31 Mar 2006 09:35:31 -0600 "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > On Thu, Mar 30, 2006 at 10:15:21AM -0500, D'Arcy J.M. Cain wrote: > > I had to do multi-master replication for a major project and we wound > > up writing our own replication system. The problem is that this sort of > > thing really has to be based on your own business rules. There is no > > way to make it generic. At least, that was the conclusion we came to. > > Maybe true, but certainly a lot of the framework should be generic, no? Possibly. Unfortunately I did the work under NDA. I should check and see how much I can talk about. I may at least be able to present a paper discussing the basic ideas. Hmm. Maybe for the Toronto conference? > There's *a lot* of people who ask about multi-master replication. Yah, we were one of them. :-) -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_class catalog question...
On Fri, Mar 31, 2006 at 10:45:15AM -0500, Jonah H. Harris wrote: > On 3/31/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > What are you using a char[64] for anyway? You should probably consider > > using NameData, if you want to store an identifier. > > It's just a fixed length string that will never change in size and as > such, I'd like not to add the overhead of any variable-length > handling. What about creating a fixed-size general purpose type? About the only reason I use CHAR in other databases systems is when I know that the field will always contain the same amount of data, ie: storing a SHA1. In these cases it's silly to have a 4 byte overhead to store length. I really wish CHAR in PostgreSQL worked this way, so it would be a welcome addition to have a type that did work this way. In fact, I'd argue that CHAR should be made to work that way, and what's currently called CHAR should be renamed for those who wish to use it. I've yet to run across a use for CHAR where you might actually have a variable amount of data stored and just want to enforce a certain number of space padding. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_class catalog question...
On 3/31/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > What are you using a char[64] for anyway? You should probably consider > using NameData, if you want to store an identifier. It's just a fixed length string that will never change in size and as such, I'd like not to add the overhead of any variable-length handling. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_class catalog question...
Jonah H. Harris wrote: > On 3/31/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote: > > What if you put your char[64] before relhassubclass, then you > > don't change CLASS_TUPLE_SIZE. > > Thought about that... but it would be an ugly place for this column. > I know I could get around it by renumbering the attribute, but that's > just a kludge. What are you using a char[64] for anyway? You should probably consider using NameData, if you want to store an identifier. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_class catalog question...
On 3/31/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote: > What if you put your char[64] before relhassubclass, then you > don't change CLASS_TUPLE_SIZE. Thought about that... but it would be an ugly place for this column. I know I could get around it by renumbering the attribute, but that's just a kludge. Now that I've had some sleep, I'm sure I'll get it working :) -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Slony-I for circular replication
On Thu, Mar 30, 2006 at 10:15:21AM -0500, D'Arcy J.M. Cain wrote: > I had to do multi-master replication for a major project and we wound > up writing our own replication system. The problem is that this sort of > thing really has to be based on your own business rules. There is no > way to make it generic. At least, that was the conclusion we came to. Maybe true, but certainly a lot of the framework should be generic, no? There's *a lot* of people who ask about multi-master replication. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] WAL dirty-buffer management bug
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > It may be not good but not harmful either. On step2, the transaction will > abort and leave a page that has been changed but not marked dirty. There are > two situtations could happen after that. One is step 3, the other is the > page is still in the buffer pool and another transaction will write on it > (no problem, the tuple slot is already marked used). For step 3, yes, we > will see two WAL records trying to insert to the same tuple slot, but the > 2nd one will cover the 1st one -- no problem. Well, no, see the code in PageAddItem: if (ItemIdIsUsed(itemId) || ItemIdGetLength(itemId) != 0) { elog(WARNING, "will not overwrite a used ItemId"); return InvalidOffsetNumber; } So during WAL replay the second insert will fail, leading to elog(PANIC, "heap_insert_redo: failed to add tuple"); Removing that error check in PageAddItem doesn't strike me as a good idea, either ;-) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] WAL dirty-buffer management bug
Simon Riggs <[EMAIL PROTECTED]> writes: > Problem for indexes only. heap xlrecs don't specify exact insert points Sure they do. They had better, else (for example) the associated index insertions will be wrong. > Accesses to local buffers don't need to be critical sections either. True, but in most places it would uglify the code quite a bit to make it like that, because START/END_CRIT_SECTION are bracketing code that is shared between both cases. And I'm not seeing where we'd get any particular reliability gain from it: if the code has any significant risk of elog'ing during the critical section, it's broken anyway ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] WAL dirty-buffer management bug
On Thu, 2006-03-30 at 13:51 -0500, Tom Lane wrote: > This is pretty much what heapam and btree currently do, but on looking > at it I think it's got a problem: we really ought to mark the buffer > dirty before releasing the critical section. Otherwise, if there's an > elog(ERROR) before the WriteBuffer call is reached, the backend would go > on about its business, and we'd have changes in a disk buffer that isn't > marked dirty. The changes would be uncommitted, presumably, because of > the error --- but nonetheless this could result in inconsistency down > the road. One example scenario is: > 1. We insert a tuple at, say, index 3 on a page. > 2. elog after making the XLOG entry, but before WriteBuffer. > 3. page is later discarded from shared buffers; since it's not > marked dirty, it'll just be dropped without writing it. > 4. Later we need to insert another tuple in same table, and > we again choose index 3 on this page as the place to put it. > 5. system crash leads to replay from WAL. > Now we'll have two different WAL records trying to insert tuple 3. > Not good. Agreed. Problem for indexes only. heap xlrecs don't specify exact insert points so they'd replay just fine even if they were not originally inserted like that. > I'm thinking we should change the code and the README to specify that > you must mark the buffer dirty before you can END_CRIT_SECTION(). > Comments? Couple thoughts... Should we just do this for indexes only? (Or any structure that requires an exact physical position to be recorded in WAL). Accesses to local buffers don't need to be critical sections either. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_class catalog question...
""Jonah H. Harris"" <[EMAIL PROTECTED]> wrote > > Yeah, I noticed that one. How would you suggest setting > CLASS_TUPLE_SIZE in that case? > What if you put your char[64] before relhassubclass, then you don't change CLASS_TUPLE_SIZE. Regards, Qingqing ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] WAL dirty-buffer management bug
"Tom Lane" <[EMAIL PROTECTED]> wrote > > This is pretty much what heapam and btree currently do, but on looking > at it I think it's got a problem: we really ought to mark the buffer > dirty before releasing the critical section. Otherwise, if there's an > elog(ERROR) before the WriteBuffer call is reached, the backend would go > on about its business, and we'd have changes in a disk buffer that isn't > marked dirty. The changes would be uncommitted, presumably, because of > the error --- but nonetheless this could result in inconsistency down > the road. One example scenario is: > 1. We insert a tuple at, say, index 3 on a page. > 2. elog after making the XLOG entry, but before WriteBuffer. > 3. page is later discarded from shared buffers; since it's not >marked dirty, it'll just be dropped without writing it. > 4. Later we need to insert another tuple in same table, and >we again choose index 3 on this page as the place to put it. > 5. system crash leads to replay from WAL. > Now we'll have two different WAL records trying to insert tuple 3. > Not good. > It may be not good but not harmful either. On step2, the transaction will abort and leave a page that has been changed but not marked dirty. There are two situtations could happen after that. One is step 3, the other is the page is still in the buffer pool and another transaction will write on it (no problem, the tuple slot is already marked used). For step 3, yes, we will see two WAL records trying to insert to the same tuple slot, but the 2nd one will cover the 1st one -- no problem. If the 2nd one will not cover the 1st one (say that WAL record is broken), also no prolbem since the tuple header will gaurantee that tuple is invisible. Can you give an example that this will lead data corruption? Regards, Qingqing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org