Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT
On Thu, 27 Mar 2008 00:13:42 -0400 Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I suspect that the size reported by stat() is a little delayed here, but the file system is keeping proper track of it, so the lseek that tries to extend the file fails at the right spot. Hmm. If it really works that way, one would hope Microsoft would've documented that someplace. Can anyone find a statement that Windows' stat() is not current? I'm not in a position to test it myself now (doing training, and then I'll be off to pg-east...), but it'd be interesting to see if it acts the same way with GetFileSize(), or if it's just stat()... /Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: improve shutdown during online backup
Greg Smith wrote: 1) On pg_ctl stop|restart -m smart, check if online backup is in progress and do not shutdown in this case (treat the online backup like an open connection). As long as you give a warning as to the cause. While you're in there, I think more output in general about the reason why a smart shutdown failed would be nice as well. I'll look what I can do. 2) On pg_ctl stop|restart -m fast, remove backup_label after the server has been brought down successfully. And you need a warning here as well about this fact. I think the actual details associated with that label should be both printed and put into the logs at this time, so you know which backup you just hosed. Sounds right. Maybe the label file could get renamed instead? I agree. There are three options here for how -m fast could handle things: 1) Warning, remove backup label. I prefer that. Thanks for the feedback, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT
Andrew Dunstan wrote: I'm working on this (thank goodness for junctions). Maybe we shopuld look at providing a config setting for pg_xlog. I hope you mean an initdb switch -- otherwise it is way too easy to misuse. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Problem identifying constraints which should not be inherited
Hi, On Thu, Mar 20, 2008 at 7:36 PM, Tom Lane [EMAIL PROTECTED] wrote: More to the point, it takes a capability away from the user without actually solving the problem we need to solve, namely to guarantee consistency between parent and child constraints. You can be sure that there is someone out there who will complain that we've broken his application when we disallow this, and we need to be able to point to some positive benefit we got from it. Agreed. So I think we need to implement the whole enchilada or nothing at all. We need to do the following for this: * Add logic to disallow ADD CONSTRAINT ONLY to parent of an inheritance hierarchy * Add logic to mark inherited constraints in the children: This can be achieved by introducing a new bool coninherited attribute in pg_constraint. This will be set to true on only those check constraints that are added to children via the inheritance mechanism * Add logic to disallow dropping inherited constraints directly on children Obviously they will get dropped if a DROP CONSTRAINT is fired on the parent. with recurse set to true (this is the default behaviour) * Modify the pg_dump logic to use the new pg_constraint based attribute logic for version 80300 (or should it be PG_VERSION_NUM 80400?) onwards. Infact the current logic to determine if a check constraint is inherited is to compare its name with the parent constraint name and the comment already mentions that we should make changes in pg_constraint to avoid this rudimentary way of determing the inheritance :). Am important decision here is about adding a new attribute to pg_constraint as it is the only sane way of determining inherited constraints, but that will require an initdb. Comments? Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] [BUGS] Problem identifying constraints which should not be inherited
NikhilS wrote: Am important decision here is about adding a new attribute to pg_constraint as it is the only sane way of determining inherited constraints, but that will require an initdb. Comments? There's no problem forcing an initdb at this point in the release cycle. We will do that for sure many times before we reach beta stage. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Script binaries renaming
Marc G. Fournier napsal(a): -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Wednesday, March 26, 2008 12:58:41 +0100 Zdeněk Kotala [EMAIL PROTECTED] wrote: Minimal me :-) and Solaris Architect committee have complain. Question is also how many users really use these commands. For example vacuumdb is not too important now when we have autovacuum. Huh? I run a vacuumdb once a week on all my databases, even with autovacuum turned on Thanks for correction. I don't have yet PG8.3 on my production server and I was convinced with good autovacuum marketing that is ultimate solution. :-) Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT
Maybe this helps: It is not an error to set a file pointer to a position beyond the end of the file. The size of the file does not increase until you call the SetEndOfFile, WriteFile, or WriteFileEx function. A write operation increases the size of the file to the file pointer position plus the size of the buffer written, which results in the intervening bytes uninitialized. http://msdn2.microsoft.com/en-us/library/aa365541(VS.85).aspx According to Windows' lseek implementation (attached) SetEndOfFile() isn't called for this case. Thanks, Sergey Zubkovsky -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2008 7:14 AM To: Andrew Dunstan Cc: Alvaro Herrera; Gregory Stark; Zubkovsky, Sergey; pgsql-hackers@postgresql.org; Magnus Hagander Subject: Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT Andrew Dunstan [EMAIL PROTECTED] writes: I suspect that the size reported by stat() is a little delayed here, but the file system is keeping proper track of it, so the lseek that tries to extend the file fails at the right spot. Hmm. If it really works that way, one would hope Microsoft would've documented that someplace. Can anyone find a statement that Windows' stat() is not current? regards, tom lane lseek.c Description: lseek.c -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT
Zubkovsky, Sergey wrote: Maybe this helps: It is not an error to set a file pointer to a position beyond the end of the file. The size of the file does not increase until you call the SetEndOfFile, WriteFile, or WriteFileEx function. A write operation increases the size of the file to the file pointer position plus the size of the buffer written, which results in the intervening bytes uninitialized. http://msdn2.microsoft.com/en-us/library/aa365541(VS.85).aspx According to Windows' lseek implementation (attached) SetEndOfFile() isn't called for this case. Yes, but we immediately follow the lseek bye a write(). See src/backend/storage/smgr/md.c:mdextend() . cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Script binaries renaming
Zdenek Kotala wrote: Thanks for correction. I don't have yet PG8.3 on my production server and I was convinced with good autovacuum marketing that is ultimate solution. :-) It is not perfect yet. It's improving -- keep in mind it's rather new. However, I doubt vacuumdb -a is the thing to use when autovac is not good enough, because in those cases what typically happens is that there are huge tables, or tables with high update rates, so a simple vacuum-all-tables-in-all-databases would be similarly useless. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT
Alvaro Herrera [EMAIL PROTECTED] writes: Andrew Dunstan wrote: I'm working on this (thank goodness for junctions). Maybe we shopuld look at providing a config setting for pg_xlog. I hope you mean an initdb switch -- otherwise it is way too easy to misuse. There's one already .. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Andrew Dunstan wrote: I'm working on this (thank goodness for junctions). Maybe we shopuld look at providing a config setting for pg_xlog. I hope you mean an initdb switch -- otherwise it is way too easy to misuse. There's one already .. heh, the things that creep up on you when you're not looking ... cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch queue permenent URLs
I have found a way to have permanent URLs that stay permanent even if the email is moved from the patches queue to the patches_hold queue. The trick is to use base to specify the base directory in the html. The new URLs look like: http://momjian.us/mhonarc/message-id/[EMAIL PROTECTED] The new URLs appear now. The old permanent will also remain active until the next commit fest. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Problem identifying constraints which should not be inherited
NikhilS [EMAIL PROTECTED] writes: ... * Add logic to mark inherited constraints in the children: This can be achieved by introducing a new bool coninherited attribute in pg_constraint. This will be set to true on only those check constraints that are added to children via the inheritance mechanism It probably needs to be a count not a bool. You can/should use the handling of inherited attributes as a pattern to follow --- look at attislocal and attinhcount. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch queue permenent URLs
On Thu, 2008-03-27 at 11:18 -0400, Bruce Momjian wrote: The new URLs look like: http://momjian.us/mhonarc/message-id/[EMAIL PROTECTED] The new URLs appear now. The old permanent will also remain active until the next commit fest. If they are going to be permanent then they should reference a PostgreSQL project domain rather than a personal domain and a company one. Without disrespect to either, our emails should not rely on external domains. That way the project is in control, not the other way around. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)
* Brendan Jurd [EMAIL PROTECTED] [080326 10:19]: On 27/03/2008, Gurjeet Singh [EMAIL PROTECTED] wrote: Is the rsync daemon on anoncvs down? Is everyone else able to do rsync? Possibly related; the Postgres git repository at http://repo.or.cz/w/PostgreSQL.git is showing the last commit at 25 hours ago. It's usually a bit more spry than that. Oops - no, that's just me... I was recently made aware of this: http://repo.or.cz/w/PostgreSQL.git?a=commit;h=69db64c737012a8d2d6fbcce3ace7136cb2bc85f I started digging around to figure this out on Tuesday. It appears as if the rsync mirror of CVS is not always good. It seems like long running CVS operations (like I'm guessing a full tree tag of REL8_3_STABLE) aren't mirrored atomically. Of course, CVS isn't atomic, so we can't really blame it. What appears to have happened is that my rsync caught the rsync mirror when the tree was not all tagged, so when the fromcvs went about making the new branch on the first appearance of REL8_3_STABLE, it had to remove a bunch of files from the branch because they were *not* tagged with that symbol in CVS (or at least, not presently tagged with that symbol in the rsync mirror of CVS)... I would guess that any incremental CVS mirror/conversion is going to hit this at some random time too. Of course, the risk of hitting it goes up as the frequency of your rsync updates go up. And I just forgot to re-enable my cron after I finished looking at it. BTW, anybody following the GIT mirror, the REL8_3_STABLE branch has been re-wound, you you'll probably have to force update it (git fetch -f) if you only accept fast forward updates on fetches (the default). And if you have patches based on REL8_3_STABLE, you'll need to rebase them too. Of course, seeing as the tree in REL8_3_STABLE mirror was broken, I suspect the set of people using it was 0. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Patch queue permenent URLs
On Thu, Mar 27, 2008 at 3:44 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Thu, 2008-03-27 at 11:18 -0400, Bruce Momjian wrote: The new URLs look like: http://momjian.us/mhonarc/message-id/[EMAIL PROTECTED] The new URLs appear now. The old permanent will also remain active until the next commit fest. If they are going to be permanent then they should reference a PostgreSQL project domain rather than a personal domain and a company one. Without disrespect to either, our emails should not rely on external domains. That way the project is in control, not the other way around. The company domain is from the message id by the looks of it - it should not be changed under any circumstances. -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch queue permenent URLs
Simon Riggs wrote: On Thu, 2008-03-27 at 11:18 -0400, Bruce Momjian wrote: The new URLs look like: http://momjian.us/mhonarc/message-id/[EMAIL PROTECTED] The new URLs appear now. The old permanent will also remain active until the next commit fest. If they are going to be permanent then they should reference a PostgreSQL project domain rather than a personal domain and a company one. Without disrespect to either, our emails should not rely on external domains. That way the project is in control, not the other way around. Well, the patch queue maintained by Bruce has always been in his domain. And regarding the company domain, I note that that string is part of a Message-Id, generated by the patch submitter's machine, so entirely out of Bruce's (or anyone else's) control. Note that we will have permanent URLs by Message-Id in our archives soon too: if I have my way, they will look like http://archives.postgresql.org/msgid/[EMAIL PROTECTED] or something similar. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch queue permenent URLs
Dave Page wrote: On Thu, Mar 27, 2008 at 3:44 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Thu, 2008-03-27 at 11:18 -0400, Bruce Momjian wrote: The new URLs look like: http://momjian.us/mhonarc/message-id/[EMAIL PROTECTED] The new URLs appear now. The old permanent will also remain active until the next commit fest. If they are going to be permanent then they should reference a PostgreSQL project domain rather than a personal domain and a company one. Without disrespect to either, our emails should not rely on external domains. That way the project is in control, not the other way around. The company domain is from the message id by the looks of it - it should not be changed under any circumstances. Yep, the only way we can get rid of the company is to use an MD5 hash (which is what I used at first) instead of the message id, but people didn't like that. I have updated the permanent URL to be in the postgresql.org domain: http://momjian.postgresql.org/mhonarc/message-id/[EMAIL PROTECTED] Of course, momjian.us works too. (I am redirecting momjian.postgresql.org to momjian.us because the comments are bound to the domain name momjian.us.) -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows shared_buffers limitations
Greg Smith wrote: Was working on some documentation today and I realized that I've taken for granted the lore about not using large values for shared_buffers in Windows without ever understanding why. Can someone explain what the underlying mechanism that causes that limitation is? From poking the archives I got the impression it was some page mapping issue but details are elusive. All I can offer is Magnus' explanation: All evidence I've seen points to that you should have shared_buffers as *small* as possible on win32, because memory access there is slow. And leave more of the caching up to the OS. http://archives.postgresql.org/pgsql-general/2007-10/msg01115.php Heikki said something similar here: http://archives.postgresql.org/pgsql-performance/2007-10/msg00242.php Rainer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql and named pipes
I was under the impression that I could start a psql -f pipe and then feed it commands through the pipe using echo, and expect it to hang from one command to the next. Of course, this doesn't work -- my guess is that echo sends an EOF after the line I send, so psql sees the EOF in the pipe and terminates. Does anyone have an idea how to go about this? I was expecting to be able to drive two psql sessions in parallel in a shell script -- sort of poor man's concurrent psql :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql and named pipes
* Alvaro Herrera [EMAIL PROTECTED] [080327 12:58]: I was under the impression that I could start a psql -f pipe and then feed it commands through the pipe using echo, and expect it to hang from one command to the next. Of course, this doesn't work -- my guess is that echo sends an EOF after the line I send, so psql sees the EOF in the pipe and terminates. Does anyone have an idea how to go about this? I was expecting to be able to drive two psql sessions in parallel in a shell script -- sort of poor man's concurrent psql :-( I've had to use: while (true); do cat pipe; done | psql The trick is that pipes EOFs everytime the cleint closes it. (Not strictly true, but it appears that way to basic read()ers). You can see a more compilcated setup I use to echo commands to a pipe going to a psql here: http://www.highrise.ca/aidan/postgresql/watchsql -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Windows shared_buffers limitations
Rainer Bauer [EMAIL PROTECTED] writes: Greg Smith wrote: Was working on some documentation today and I realized that I've taken for granted the lore about not using large values for shared_buffers in Windows without ever understanding why. Can someone explain what the underlying mechanism that causes that limitation is? From poking the archives I got the impression it was some page mapping issue but details are elusive. All I can offer is Magnus' explanation: All evidence I've seen points to that you should have shared_buffers as *small* as possible on win32, because memory access there is slow. And leave more of the caching up to the OS. http://archives.postgresql.org/pgsql-general/2007-10/msg01115.php Details are elusive because we have no idea *why* memory access should be slow. If the pages are all in core and mapped in the memory map then really accessing them should just be a hardware issue. The OS only gets involved with a page needs to be paged in from swap or other backing store. One thing which comes to mind is that it's possible Windows is swapping out shared memory making having large shared memory segments dangerous on that front. Of course it's also possible something more subtle is going on. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Minor changes to Recovery related code
Follow-up during March 2008 CommitFest On Thu, 2007-06-07 at 21:53 +0100, Simon Riggs wrote: On Sat, 2007-03-31 at 00:51 +0200, Florian G. Pflug wrote: Simon Riggs wrote: On Fri, 2007-03-30 at 16:34 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: 2. pg_stop_backup() should wait until all archive files are safely archived before returning Not sure I agree with that one. If it fails, you can't tell whether the action is done and it failed while waiting for the archiver, or if you need to redo it. There's a slight delay between pg_stop_backup() completing and the archiver doing its stuff. Currently if somebody does a -m fast straight after the pg_stop_backup() the backup may be unusable. We need a way to plug that small hole. I suggest that pg_stop_backup() polls once per second until pg_xlog/archive_status/LOG.ready disappears, in which case it ends successfully. If it does this for more than 60 seconds it ends successfully but produces a WARNING. I fear that ending sucessfully despite having not archived all wals will make this feature less worthwile. If a dba knows what he is doing, he can code a perfectly safe backup script using 8.2 too. He'll just have to check the current wal position after pg_stop_backup(), (There is a function for that, right?), and wait until the corresponding wal was archived. In realitly, however, I feare that most people will just create a script that does 'echo select pg_stop_backup | psql' or something similar. If they're a bit more carefull, they will enable ON_ERROR_STOP, and check the return value of pgsql. I believe that those are the people who would really benefit from a pg_stop_backup() that waits for archiving to complete. But they probably won't check for WARNINGs. Maybe doing it the other way round would be an option? pg_stop_backup() could wait for the archiver to complete forever, but spit out a warning every 60 seconds or so WARNING: Still waiting for wal archiving of wal ??? to complete. If someone really wants a 60-second timeout, he can just use statement_timeout. I've just come up against this problem again, so I think it is a must fix for this release. Other problems exist also, mentioned on separate threads. We have a number of problems surrounding pg_stop_backup/shutdown: 1. pg_stop_backup() currently returns before the WAL file containing the last change is correctly archived. That is a small hole, but one that is exposed when people write test scripts that immediately shutdown the database after issuing pg_stop_backup(). It doesn't make much sense to shutdown immediately after a hot backup, but it should still work sensibly. 2. We've also had problems caused by making the archiver wait until all WAL files are archived. If there is a backlog for some reason and the DBA issues a restart (i.e. stop and immediate restart) then making the archiver loop while it tries (and possibly fails) to archive all files would cause an outage. Avoiding this is why we do the current get-out-fast approach. There are some sub scenarios: a) there is a backlog of WAL files, but no error has occurred on the *last* file (we might have just fixed a problem). b) there is a backlog of WAL files, but an error is causing a retry of the last file. My proposal is for us to record somewhere other than the logs that a failure to archive has occurred and is being retried. Failure to archive will be recorded in the archive_status directory as an additional file called archive_error, which will be deleted in the case of archive success and created in the case of archive error. This maintains archiver's lack of attachment to shared memory and general simplicity of design. - pg_stop_backup() will wait until the WAL file that ends the backup is safely archived, even if a failure to archive occurs. This is a change to current behaviour, but since it implements the originally *expected* behaviour IMHO it should be the default. The most straightforward thing is to make pg_stop_backup() wait as described above. If people want it to timeout, they can use a statement_timeout as suggested by Florian. This can be implemented by having the function poll in an infinite loop for archive_status/LOG.done. Also, as Florian suggests, we should have it output a WARNING message every 60 seconds. I'll write a patch now. - new function: pg_stop_backup_nowait() return immediately without waiting for archive, the same as the current pg_stop_backup() - new function: pg_stop_backup_wait(int seconds) wait until either an archival fails or the ending WAL file is archived, with a max wait as specified. wait=0 means wait until archive errors are resolved. So I won't do these. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list
Re: [HACKERS] psql and named pipes
Aidan Van Dyk wrote: I've had to use: while (true); do cat pipe; done | psql The trick is that pipes EOFs everytime the cleint closes it. (Not strictly true, but it appears that way to basic read()ers). Ah! Yeah, I knew that and forgot :-) It's easier than that actually -- you just need to keep the pipe open in another process. So I can do this: first open a terminal with $ psql -f foo And then, in another terminal, $ cat foo [1] 29155 [1]+ Stopped cat foo $ echo begin; foo $ echo create table a (a int); foo $ echo insert into a values (1); foo $ echo insert into a values (2); foo $ echo insert into a values (3); foo $ echo commit; foo $ echo select * from a; foo $ kill %1 -bash: echo: write error: Appel système interrompu [1]+ Complété cat foo And while this is going on, the other terminal shows the output being produced by psql. Thanks for the reminder :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql and named pipes
* Alvaro Herrera [EMAIL PROTECTED] [080327 13:51]: Ah! Yeah, I knew that and forgot :-) It's easier than that actually -- you just need to keep the pipe open in another process. So I can do this: first open a terminal with $ psql -f foo And then, in another terminal, $ cat foo [1] 29155 [1]+ Stopped cat foo $ echo begin; foo $ echo create table a (a int); foo $ echo insert into a values (1); foo $ echo insert into a values (2); foo $ echo insert into a values (3); foo $ echo commit; foo $ echo select * from a; foo $ kill %1 -bash: echo: write error: Appel système interrompu [1]+ Complété cat foo And while this is going on, the other terminal shows the output being produced by psql. Thanks for the reminder :-) And thanks for the any open writer trick. Makes it even easier for me to keep using named pipes with psql. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] CopyReadLineText optimization
Heikki Linnakangas wrote: 1. CopyReadLineText is all about finding the the next end of line; splitting to fields is done later. We therefore only care about quotes and escapes when they affect the end of line detection. In text mode, we only need to care about a backslash that precedes a LF/CR. Therefore, we could search for the next LF/CR character with memchr(), and check if the preceding character is a backslash (and if it is, check if there's yet another backslash behind it, and so forth until we hit a non-backslash character). While looking into this, I realized that we also need to detect the end-of-copy marker, backslash+period+EOL. In CSV mode, we only honor the end-of-copy marker if it's on a line of it's own, as \. can occur in data, but in text mode we accept it at any point. Does anyone object to changing that so that we only accept \. on a line of its own in text mode as well? That way we wouldn't need to care about backslashes in CopyReadLineText. AFAIK our tools have always output the \. like that, so this would only affect custom applications that use COPY and the \. marker. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_standby for 8.2 (with last restart point)
Hi all,
For PG versions 8.3 (specifically 8.2) I wanted the %r parameter to be
substituted by the last restart point, just as the recovery code does in
8.3. I assumed there would be objections to it (else it would have already
been there in 8.2.x), so started looking for workarounds. After a few ideas,
I settled with using the output of pg_controldata.
Here's what I have done: I execute pg_controldata and parse it's output
to extract the same information as xlog.c provides for %r in versions 8.3.
Then I rebuild the XLog filename, just like xlog.c, and emit it from the
script. All this is done in a perl script (at the end of this mail).
My next step is: use this script in the restore_command to provide the
%r parameter to pg_standby, like so:
restore_command = 'pg_standby -c -d -s 5 -w 0 -t
/tmp/pg_standby.trigger.5433 ../wal_archive/ %f %p `perl
/home/gurjeet/dev/last_restart_point.pl` 2 pg_standby.log'
I have tested this script using the following restore_command, on a HEAD
version:
restore_command = 'echo before `perl
/home/gurjeet/dev/last_restart_point.pl` pg_standby.log pg_standby -c
-d -s 5 -w 0 -t /tmp/pg_standby.trigger.5433 ../wal_archive/ %f %p %r 2
pg_standby.log echo after `perl /home/gurjeet/dev/last_restart_point.pl`
pg_standby.log'
Using the above restore_command, I can see that my script is able to
detect the change in the restart point (%r) just as soon as the server
updates it. Here's a snippet:
snip
...
Keep archive history: 000100010021 and later
running restore : OK
after 000100010021
before 000100010045
Trigger file: /tmp/pg_standby.trigger.5433
Waiting for WAL file: 000100010047
WAL file path : ../wal_archive//000100010047
Restoring to... : pg_xlog/RECOVERYXLOG
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : cp ../wal_archive//000100010047
pg_xlog/RECOVERYXLOG
Keep archive history: 000100010045 and later
running restore : OK
removing ../wal_archive//000100010025
removing ../wal_archive//00010001002D
removing ../wal_archive//000100010031
...
./snip
So, is this a safe way of extracting the last restart point for PG 8.3?
Or would it be possible to make PG8.3 provide this %r through some patch?
Best regards,
Gurjeet.
Here's the perl script:
script
my @text = `pg_controldata .`; # here . represents the PGDATA, since the
server is executing here.
my $line;
my $time_line_id;
my $redo_log_id;
my $redo_rec_off;
my $wal_seg_bytes;
my $redo_log_seg;
foreach $line ( @text )
{
$line = mychomp( $line );
if( $line =~ m/Latest checkpoint's TimeLineID:\s*(([0-9])+)/ )
{
# decimal number
$time_line_id = 0 + $1;
}
if( $line =~ m/Latest checkpoint's REDO
location:\s*(([0-9]|[A-F])+)\/(([0-9]|[A-F])+)/ )
{
# hexadecimal numbers
$redo_log_id = $1;
$redo_rec_off = $3;
}
if( $line =~ m/Bytes per WAL segment:\s*([0-9]+)/ )
{
# decimal number
$wal_seg_bytes = $1;
}
}
$redo_log_seg = sprintf( %d, hex( $redo_rec_off ) / $wal_seg_bytes );
print . sprintf( %08X%08X%08X, $time_line_id, $redo_log_id,
$redo_log_seg ) . \n;
# Wrapper around Perl's chomp function
sub mychomp
{
my ( $tmp ) = @_;
chomp( $tmp );
return $tmp;
}
/script
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
[HACKERS] Indexing for Expression type data using GIST
Hi All, I am trying to implement indexing mechanism for the Expression type data using GIST. For that purpose I need to store some extra information in the Meta-Data of the GIST Index. The information is entered when the index is created on the table. Can any body give me some suggestion how to do that. I'll be really grateful. Thanks in advance Salman _ Test your Star IQ http://club.live.com/red_carpet_reveal.aspx?icid=redcarpet_HMTAGMAR
Re: [HACKERS] Indexing for Expression type data using GIST
On Thu, Mar 27, 2008 at 02:03:09PM -0500, Wizard Shah wrote: Hi All, I am trying to implement indexing mechanism for the Expression type data using GIST. For that purpose I need to store some extra information in the Meta-Data of the GIST Index. There is no real provision for this but, depending on what it is there may be other options. For example perhaps you can use the typmod or something like that. The information is entered when the index is created on the table. Can any body give me some suggestion how to do that. I'll be really grateful. If you provide some more information about what the information represents we might be able to give you a better idea. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] psql and named pipes
Alvaro Herrera [EMAIL PROTECTED] writes: I was under the impression that I could start a psql -f pipe and then feed it commands through the pipe using echo, and expect it to hang from one command to the next. Of course, this doesn't work -- my guess is that echo sends an EOF after the line I send, so psql sees the EOF in the pipe and terminates. Right. You need some (other?) process holding the write end of the pipe open continuously until you're done with the session. There isn't really any such concept as sending an EOF here. The read side of the pipe reports EOF if there are no processes holding the write side open. More data could be sent by a new writer, but of course psql has no idea about that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Commit fest status
On Mon, 2008-03-24 at 17:50 -0400, Bruce Momjian wrote: FYI, we started the commit fest with 2k emails. We now have 787 emails left to process, and many are done but waiting for me to add TODO items or just delete them. Just finished reviewing the remaining items on the queue that I can comment on. My personal todo list from that is * Refine doc patch for Incomplete docs for restore_command... * pg_stop_backup patch for Minor changes for Recovery... * Complete testing of pl/tcl, pl/python etc for Truncate Triggers * new version of COPY bulk insert patch (v3 in progress) Please nudge me if you think there's anything else. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows shared_buffers limitations
Gregory Stark [EMAIL PROTECTED] writes: One thing which comes to mind is that it's possible Windows is swapping out shared memory making having large shared memory segments dangerous on that front. This is a hazard on most Unixen as well. Windows may just be a bit more aggressive about it. Now that larger shared memory blocks are usually a win, we need to put more effort into telling the kernel to lock the shared memory block into RAM ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Hi, It looks like most of the hard yards will be in getting some form of consensus about what should be done for this TODO. I can't see a reason not to get started on the design now. If a decision is not able to be made after 4 years since the original discussion, is it worth removing the TODO or letting it sit for another 4? But to the actual issue at hand. Andrew Dunstan attempted to summarize the original 2004 thread http://archives.postgresql.org/pgsql-hackers/2006-10/msg01545.php; -- There was some discussion a couple of years ago on the -hackers list about it, so you might like to review the archives. The consensus seemed to be that behaviour would need to be set no later than createdb time. The options I thought of were: 1. current postgres behaviour (we need to do this for legacy reasons, of course, as well as to keep happy the legions who hate using upper case for anything) 2. strictly spec compliant (same as current behaviour, but folding to upper case for unquoted identifiers rather than lower) 3. fully case sensitive even for unquoted identifiers (not spec compliant at all, but nevertheless possibly attractive especially for people migrating from MS SQLServer, where it is an option, IIRC). -- Supporting all 3 of these behaviours at initdb time is not too invasive or complicated from my initial investigation. The steps appear to be; 1. parser has to parse incoming identifiers with the correct casing changes. (currently downcase_truncate_identifier) 2. The output quoting needs to quote identifiers using the same rules as the parser. (currently quote_identifier) 3. the client needs to know what quote rules are in place. (libpq: PQfname, PQfnumber) 4. psql needs to \ commands to be taught about the fact that case can mean different things to different servers. 5. bootstrap needs to correctly case the tables and insert values when bootstrapping at initdb time. This is only really an issue for upper case folding. Many people appear advocate a 4th option to only want the column names to be case preserved or upper cased. They expect other identifiers will behave as they do now. This doesn't really bring us any closer to the spec, it takes us away from it as Tom has suggested in the past. It also appears to increase the complexity and invasiveness of a patch. Being able to support case preservation/sensitivity for all identifiers at initdb time appears to be no extra work than supporting the upper and lower folding versions. The discussions around having a name as supplied and a quoted version allow lots of flexibility, probably even down to the session level. However I personally am struggling to get my head around the corner cases for that approach. If this needs to be at createdb time, I think we add at least the following complexities; 1. all relations cases must be altered when copied from the template database or quoted when copied. We have no idea what a template database might look like, all views and functions would need to be parsed to ensure they point to valid tables. 2. shared relations must be able to be accessed using different names in different databases, eg PG_DATABASE, pg_database. 3. The data in shared relations appears different to the same users in different databases. eg my unquoted username is MrRuss, in db1 (upper): MRRUSS, db2 (case sensitive): MrRuss, db3 (lower): mrruss My guts tells me that's going to lead to user confusion. Dumping and restoring databases to different foldings can/will present an interesting challenge and I'm not sure how to support that. I don't even know if we want to support that officially. I'm leaning towards initdb time, mainly because I think a patch can be produced that isn't to invasive and is much easier to review and actually get applied. I also think that adding the createdb time flags will push this task beyond my ability to write up a patch. Either way though, consensus on what implementation we actually want going forward will enable some more skilled developer to do this without the pain of having to flesh out the design. In light of this email and the other comments Tom and Andrew have made, it's very easy to say 'too hard, we can't get agreement'. I would have thought that standards compliance would have been one good reason to push forward with at least the upper case folding ability. Both of the previous threads on this issue raised lots of questions about possible options but there never seemed to be any knocking the ideas around and getting consensus phase. I would like to at least nail down some of the requirement, if not all. I have put forward my personal opinion, but I expect that is not of significant value as there are many others with much more experience than I. Regards Russell Smith -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:
Re: [HACKERS] Indexing for Expression type data using GIST
Hi,
The information is a simple structure with a string and integer. It basically
represents what attributes of the expression are going to be part of the index
struct ExpIndexInfo
{
int count;
char* ExpIndex;
};
e.g we have an expression data like price 1300 AND color = blue AND mileage
2 and the index will include only the price and mileage attributes. The
string ExpIndex will contain the attributes to be indexed (price and mileage)
and integer count maintains the max number of attributes to be indexed on
the expression. This is all to be specified at the index creation time and
stored in the Meta-Data to be changed or retrieved later
Regards
Salman
Date: Thu, 27 Mar 2008 20:34:04 +0100
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Indexing for Expression type data using GIST
On Thu, Mar 27, 2008 at 02:03:09PM -0500, Wizard Shah wrote:
Hi All,
I am trying to implement indexing mechanism for the Expression type
data using GIST. For that purpose I need to store some extra
information in the Meta-Data of the GIST Index.
There is no real provision for this but, depending on what it is there
may be other options. For example perhaps you can use the typmod or
something like that.
The information is entered when the index is created on the table.
Can any body give me some suggestion how to do that. I'll be really
grateful.
If you provide some more information about what the information
represents we might be able to give you a better idea.
Have a nice day,
--
Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
_
How well do you know your celebrity gossip?
http://originals.msn.com/thebigdebate?ocid=T002MSN03N0707A
Re: [HACKERS] Commit fest status
Simon Riggs wrote: On Mon, 2008-03-24 at 17:50 -0400, Bruce Momjian wrote: FYI, we started the commit fest with 2k emails. We now have 787 emails left to process, and many are done but waiting for me to add TODO items or just delete them. Just finished reviewing the remaining items on the queue that I can comment on. My personal todo list from that is * Refine doc patch for Incomplete docs for restore_command... * pg_stop_backup patch for Minor changes for Recovery... * Complete testing of pl/tcl, pl/python etc for Truncate Triggers * new version of COPY bulk insert patch (v3 in progress) Great. I assume you left comments on each item. Thanks. FYI, the patch queue is down to 580 emails, so we are making good progress after starting at 2k emails. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)
On 28/03/2008, Aidan Van Dyk [EMAIL PROTECTED] wrote: And I just forgot to re-enable my cron after I finished looking at it. Ah, the old post-maintenance-disabled-cron gaff. One of my personal favourites. =) I'm not sure that the git repos has fully recovered. There seems to a block of commits missing, between 2008-03-25 13:09 and 2008-03-27 17:24 UTC. Looking at the CVS logs, there was definitely commit action in that timeframe, but none of it is showing up on the git shortlog. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Russell Smith [EMAIL PROTECTED] writes: The options I thought of were: ... 3. fully case sensitive even for unquoted identifiers (not spec compliant at all, but nevertheless possibly attractive especially for people migrating from MS SQLServer, where it is an option, IIRC). Actually, I think most of the complainers wish that we'd duplicate mysql's behavior ... although some experimentation suggests that that behavior is impossibly inconsistent. It looks to me like, at least for myisam tables, table names are fully case-sensitive and column names are fully not (but are stored and reported in the originally entered casing). Function names also seem case-insensitive. I'm afraid to check whether other table handlers might behave differently still :-( Supporting all 3 of these behaviours at initdb time is not too invasive or complicated from my initial investigation. You are deliberately ignoring all the hard problems. The issue here is not whether we can make the parser fold identifiers in different ways. The issue is how we keep everything from breaking afterwards. The problems mostly are faced by clients --- psql's \d commands, pg_dump, etc. Consider as an example pg_dump's quote_ident function, which has to decide if an identifier requires double-quoting or not. If it doesn't know what case-folding rule will be used to read the identifier, how can it make that decision? Restricting the case folding choice to be frozen at initdb would eliminate some of these problems, but hardly all of them. IMHO this area bears a whole lot of similarity to the backslashes-in-string-literals problem. We are moving extremely slowly towards spec compliance in that area, but we all know that when we throw the switch by making standard_conforming_strings default to ON, we are going to hear howls of anguish from everywhere. Exposing apps to different possible case-folding rules is going to be at least as painful. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sorting Improvements for 8.4
Added to TODO: * Consider being smarter about memory and external files used during sorts http://archives.postgresql.org/pgsql-hackers/2007-11/msg01101.php http://archives.postgresql.org/pgsql-hackers/2007-12/msg00045.php --- Simon Riggs wrote: Just wanted to review a few thoughts and ideas around improving external sorts, as recently encouraged to do by Jim Nasby. Current issues/opportunities are these: ISSUES a) Memory is always in short supply, so using what we have more effectively is going to be welcome. b) Heap sort has a reasonably strong anti-memory effect, meaning that there is an optimum amount of memory for any sort. This shows itself with the CPU time increasing during run forming, making this stage of the sort CPU bound. c) Many sorts are performed prior to aggregation. It might be possible to aggregate prior to writing to disk, as a way of reducing the overall I/O cost. Benefit would occur when the total CPU cost was same no matter when aggregation occurred; that would not apply in all cases, so we would need to sense when benefit was possible. d) Generally reducing the I/O cost of sorting may help the merging stages of a sort. SOLUTIONS The ideas that Greg Stark, Jim Nasby, Heikki and myself have discussed to date were the following: 1. Sort I/O Compression 2. Aggregation during Sort 3. Memory Pools 4. Dynamic Heap Management 5. Dynamic Run Handling I've added (5) to the list as well, which hasn't yet been discussed. 1. SORT I/O COMPRESSION This idea is not dead yet, it just needs a full set of tests to confirm that there is benefit in all cases. If there's not benefit in all cases, we may be able to work out which cases those are, so we know when to use it. 2. AGGREGATION DURING SORT Many sorts are preliminary steps before aggregation. Aggregation during run forming would potentially reduce size of heap and reduce number of comparisons. For many types of aggregate this would not theoretically increase the number of ops since sum(), avg(), min(), max() are all commutative according to their inputs. We would probably need to add another option to Aggregate Functions to indicate the possibility of calculating the aggregate in this way, since some aggregates might rely on the current situation that they expect all their inputs at once in sorted order. (Windowed aggregates are unlikely to be this way). 3. MEMORY POOLS Solving a) could be done by sensible management and allocation of resources. Discussed before, so not rehashed here. 4. DYNAMIC HEAP MANAGEMENT The size of the active heap required to produce the fewest number of runs varies as the sort progresses. For example, sorting an already sorted input needs a trivial heap size. Larger heap sizes simply avoid forming more runs, which is not necessarily a bad thing. More runs only become bad things when we go beyond our ability to perform a single final merge (see Dynamic Run Handling below). Smaller heap sizes reduce the number of comparisons required, plus increase the L2+ cache efficiencies. Those two things are the cause of the anti-memory effect. Because of b), optimising the size of the heap could potentially be a good thing. This can make a considerable difference for nearly sorted data (measurements required...). When we have M amount of memory available to us, we don't start by using it all. We start with m memory and only increase up to M if required. Runs are built with memory set at m. If a tuple arrives that would force the formation of a new run we assess i) do we care if another run is formed? Use our knowledge of the likely amount of data coming our way, compared with number of runs formed so far and see if we really care. If we don't care, allow the new run to be formed and carry on with just heap size of m. (see Dynamic Run Handling later). ii) if we do care about number of runs, then allow the heap to grow by increments up to the full size of M. Increments would be at least x2 and possibly x4. That way we always have work space to rearrange the heap. All of this dances too cleverly around the exact technique and potential costs of rearranging the heap. That is not to be ignored and is the next task in evaluating and accepting/dismissing this potential technique. In combination with memory pooling this technique might also allow memory to be better distributed to other users. 5. DYNAMIC RUN HANDLING (in Final Merge) Another way of addressing a) is to simply make better use of memory itself. Let's look at that in more detail: Number of runs that can be merged at once is currently fixed, based upon available memory. This has the underlying assumption that all runs will be concurrently active during final merging, which may not always be true. If we have random data then almost all runs
Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
On Sunday 16 March 2008 22:18, Tom Lane wrote:
Log Message:
---
Fix TransactionIdIsCurrentTransactionId() to use binary search instead of
linear search when checking child-transaction XIDs. This makes for an
important speedup in transactions that have large numbers of children,
as in a recent example from Craig Ringer. We can also get rid of an
ugly kluge that represented lists of TransactionIds as lists of OIDs.
Are there any plans to backpatch this into REL8_3_STABLE? It looks like I am
hitting a pretty serious performance regression on 8.3 with a stored
procedure that grabs a pretty big recordset, and loops through doing
insertupdate on unique failures. The procedure get progressivly slower
the more records involved... and dbx shows me stuck in
TransactionIdIsCurrentTransactionId(). I can provide provide more details if
needed (lmk what your looking for) but it certainly looks like the issue
discussed here:
http://archives.postgresql.org/pgsql-performance/2008-03/msg00191.php
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Robert Treat [EMAIL PROTECTED] writes: On Sunday 16 March 2008 22:18, Tom Lane wrote: Fix TransactionIdIsCurrentTransactionId() to use binary search instead of linear search when checking child-transaction XIDs. Are there any plans to backpatch this into REL8_3_STABLE? No. It looks like I am hitting a pretty serious performance regression on 8.3 with a stored procedure that grabs a pretty big recordset, and loops through doing insertupdate on unique failures. The procedure get progressivly slower the more records involved... and dbx shows me stuck in TransactionIdIsCurrentTransactionId(). If you can convince me it's a regression I might reconsider, but I rather doubt that 8.2 was better, regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PostgreSQL Replication with read-only access to standby DB
This is probably better answered by the PostgreSQL developer team, so I am posting/moving my discussion to this thread. My apologies if there was a better avenue to pursue this requested feature. This is exactly what we are after. Log based replication built into the core database that would also allow for read-only queries on the slave server. Trigger based / application layer based replication is not a good option for our environment. Neither is a solution that cannot easily handle DDL replication or multiple DBs per PostgreSQL instance. We are using WAL log shipping in production through a series of scripts I wrote in order to have a hot-standby server, which has been working quite well since last September. The request is to now allow that standby server to be more useful than just a hot spare by way of read-only queries for reporting purposes. From a Google Code posting: Title Implementing support for read-only queries on PITR slaves Student Florian G. Pflug Mentor Simon Riggs The support for PITR (Point-In-Time-Recovery) in postgres can be used to build a simple form a master-slave replication. Currently, no queries can be executed on the slave, though - it only replays WAL (Write-Ahead-Log) segments it receives from the master. I want to implement support for running read-only queries on such a PITR slave, making PITR useful not only for disaster recovery, but also for load-balancing. So, what would it take to get this read-only server feature implemented in PostgreSQL? I have been working with PG / Open Source projects for only a year and need some direction on how to propose having this development effort undertaken by the PG development group. My prior experience has been all closed source from Oracle, Informix, IBM, etc. If funding for this specific development effort would help this is an option that we could explore as well. Thanks, Keaton Adams MX Logic, Inc. On 3/26/08 11:48 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Chris Browne wrote: I seem to recall there being a relevant Google Summer of Code project about this, last year. I do not recall how far it got. It obviously didn't make it into 8.3 ;-)! Some parts of it did -- for example we got read-only transactions which were a step towards that goal. (The point here is that a hot standby needs to be able to execute readonly transactions.) against. People who are using the current warm-standby code are already grappling with issues like how to coordinate master/slave failover (including my second favorite acronym, STONITH for shoot the other node in the head). I don't expect handling that sort of thing will ever be integrated into the PostgreSQL database core. Note that most other database products don't integrate it in their core either. They package separate tools for it, and sell it as a single system, but you can often buy the separate tools independently. Oracle's RAC is an exception, but it also works completely differently than any of this. A I know very little about postgreSQL internals but it would be great if: - WAL files could be applied while the standby server is operational / allow read-only queries This is the part that requires modifying PostgreSQL, and that progress was made toward by Florian's GSoC project last summer. - Allow master server to send WAL files to standby servers / * WAL traffic to be streamed to another server - Allow master server to send list of all known standby servers - Allow standby server to check if master server is alive and promote itself as master (would need to ask / make sure other standby servers do not try promote themselves at the same time) These parts you could build right now, except that there's not too much value to more than one standby if you're not using them to execute queries against. People who are using the current warm-standby code are already grappling with issues like how to coordinate master/slave failover (including my second favorite acronym, STONITH for shoot the other node in the head). I don't expect handling that sort of thing will ever be integrated into the PostgreSQL database core. What is happening instead is that the appropriate interfaces to allow building higher-level tools are being designed and made available. I'm in the same boat, looking for master-slave replication for 1 master 2 'standby' read-only servers (one would get promoted to master in case of failure). I recently read about WAL here: http://developer.postgresql.org/pgdocs/postgres/warm-standby.html The standby server is not available for access, since it is continually performing recovery processing. PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby system and then the standby database server. Many such tools exist and are well integrated with other aspects required for successful failover, such as IP address migration. In short there's not much
Re: Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)
* Brendan Jurd [EMAIL PROTECTED] [080327 16:08]: I'm not sure that the git repos has fully recovered. There seems to a block of commits missing, between 2008-03-25 13:09 and 2008-03-27 17:24 UTC. Looking at the CVS logs, there was definitely commit action in that timeframe, but none of it is showing up on the git shortlog. OK, I'll take another look at it tonight. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] pg_standby for 8.2 (with last restart point)
On Fri, 28 Mar 2008, Gurjeet Singh wrote: For PG versions 8.3 (specifically 8.2) I wanted the %r parameter to be substituted by the last restart point, just as the recovery code does in 8.3. I assumed there would be objections to it (else it would have already been there in 8.2.x) The idea to add this feature didn't show up before 8.2 was released, it came up during the 8.3 development cycle. This project doesn't make functional changes to stable releases, that's the reason why 8.2 will never get patched to add the %r feature. Cute script though. I know people have asked about simulating this behavior, and I don't recall a good sample solution being presented before yours. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
On Fri, Mar 28, 2008 at 03:30:21AM +1100, Russell Smith wrote:
3. the client needs to know what quote rules are in place. (libpq:
PQfname, PQfnumber)
The question I want to see answered, is how something like DBD::Pg will
handle this. If I wrote code like this in Perl:
my %hash = $res-get_row_as_hash();
print $hash{mycolumn};
Will this change break my code? Perl hashes are case-sensetive, you
can't change that. And it seems impossibly complex to fix the above
code to work with all the possible combinations... Which starts leading
you down the path of folding in some places and not others, which is
madness.
Have a nice day,
--
Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
signature.asc
Description: Digital signature
Re: Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)
BTW, anybody following the GIT mirror, the REL8_3_STABLE branch has been re-wound, you you'll probably have to force update it (git fetch -f) if you only accept fast forward updates on fetches (the default). Thanks! now i can ditch the git.or.cz mirror And if you have patches based on REL8_3_STABLE, you'll need to rebase them too. Of course, seeing as the tree in REL8_3_STABLE mirror was broken, I suspect the set of people using it was 0. a. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)
On Thu, Mar 27, 2008 at 5:34 PM, Alex Hunsaker [EMAIL PROTECTED] wrote: BTW, anybody following the GIT mirror, the REL8_3_STABLE branch has been re-wound, you you'll probably have to force update it (git fetch -f) if you only accept fast forward updates on fetches (the default). Thanks! now i can ditch the git.or.cz mirror Err oops I was confused, i was talking about git.postgresql.org where REL8_3_STABLE is about 6 weeks old -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Martijn van Oosterhout [EMAIL PROTECTED] writes:
On Fri, Mar 28, 2008 at 03:30:21AM +1100, Russell Smith wrote:
3. the client needs to know what quote rules are in place. (libpq:
PQfname, PQfnumber)
The question I want to see answered, is how something like DBD::Pg will
handle this. If I wrote code like this in Perl:
my %hash = $res-get_row_as_hash();
print $hash{mycolumn};
Will this change break my code? Perl hashes are case-sensetive, you
can't change that. And it seems impossibly complex to fix the above
code to work with all the possible combinations... Which starts leading
you down the path of folding in some places and not others, which is
madness.
Well, DBI already has to deal with this anyways because it tries to provide a
database-independent interface. So you can instruct DBI to upcase, downcase,
or leave the identifiers as the database provides them by setting this
property on your database connection:
FetchHashKeyName (string, inherited)
The FetchHashKeyName attribute is used to specify whether the
fetchrow_hashref() method should perform case conversion on the field
names used for the hash keys. For historical reasons it defaults to
'NAME' but it is recommended to set it to 'NAME_lc' (convert to lower
case) or 'NAME_uc' (convert to upper case) according to your preference.
It can only be set for driver and database handles. For statement handles
the value is frozen when prepare() is called.
So if you've always been using unquoted identifiers you can set
FetchHashKeyName to NAME_lc and it would continue to work. If you've been
using a mixture of quoted and unquoted identifiers things would be trickier
though.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)
* Alex Hunsaker [EMAIL PROTECTED] [080327 19:38]: On Thu, Mar 27, 2008 at 5:34 PM, Alex Hunsaker [EMAIL PROTECTED] wrote: BTW, anybody following the GIT mirror, the REL8_3_STABLE branch has been re-wound, you you'll probably have to force update it (git fetch -f) if you only accept fast forward updates on fetches (the default). Thanks! now i can ditch the git.or.cz mirror Err oops I was confused, i was talking about git.postgresql.org where REL8_3_STABLE is about 6 weeks old Ya, Peter E asked me to help look at that for a while ago too. I couldn't figure out why it's not updating either. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers
Gregory Stark [EMAIL PROTECTED] writes: Martijn van Oosterhout [EMAIL PROTECTED] writes: Will this change break my code? Well, DBI already has to deal with this anyways because it tries to provide a database-independent interface. So you can instruct DBI to upcase, downcase, or leave the identifiers as the database provides them by setting this property on your database connection: That's not a solution, that's a kluge with very obvious failure modes. Now admittedly it's probably not *likely* that someone would use identifiers differing only in case in a single table definition. But it's legal, and in fact we're required by spec to support it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_standby for 8.2 (with last restart point)
On Fri, Mar 28, 2008 at 3:56 AM, Greg Smith [EMAIL PROTECTED] wrote: On Fri, 28 Mar 2008, Gurjeet Singh wrote: For PG versions 8.3 (specifically 8.2) I wanted the %r parameter to be substituted by the last restart point, just as the recovery code does in 8.3. I assumed there would be objections to it (else it would have already been there in 8.2.x) The idea to add this feature didn't show up before 8.2 was released, it came up during the 8.3 development cycle. This project doesn't make functional changes to stable releases, that's the reason why 8.2 will never get patched to add the %r feature. I completely understand that, but still was hoping that we'd change that. Cute script though. I know people have asked about simulating this behavior, and I don't recall a good sample solution being presented before yours. Thanks. Is there anybody who sees any problem with this? Specifically, internals wise, does 8.2 also give the same guarantee 8.3 does w.r.t restart point? And consequently, is it safe to go ahead with this script in a production environment? Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
It seems there is already a project on pgfoundry but there are no files: http://pgfoundry.org/projects/clearxlogtail/ Should this be on pgfoundry or in the Postgres distribution. It seems it might be tied enough to the WAL format to be in the Postgres distribution. --- Kevin Grittner wrote: On Thu, Sep 6, 2007 at 7:31 PM, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: On Thu, Sep 6, 2007 at 7:03 PM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: I think ... there's still room for a simple tool that can zero out the meaningless data in a partially-used WAL segment before compression. It seems reasonable to me, so long as you keep archive_timeout at something reasonably high. If nothing else, people that already have a collection of archived WAL segments would then be able to compact them. That would be a *very* useful tool for us, particularly if it could work against our existing collection of old WAL files. Management here has decided that it would be such a useful tool for our organization that, if nobody else is working on it yet, it is something I should be working on this week. Obviously, I would much prefer to do it in a way which would be useful to the rest of the PostgreSQL community, so I'm looking for advice, direction, and suggestions before I get started. I was planning on a stand-alone executable which could be run against a list of files to update them in-place, or to handle as single file as a stream. The former would be useful for dealing with the accumulation of files we've already got, the latter would be used in our archive script, just ahead of gzip in the pipe. Any suggestions on an existing executable to use as a model for best practices are welcome, as are suggestions for the safest and most robust techniques for identifying the portion of the WAL file which should be set to zero. Finally, I assume that I should put this on pgfoundry? -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction Snapshot Cloning
Added to TODO: * Allow one transaction to see tuples using the snapshot of another transaction This would assist multiple backends in working together. http://archives.postgresql.org/pgsql-hackers/2008-01/msg00400.php --- Chris Browne wrote: [EMAIL PROTECTED] (Simon Riggs) writes: On Fri, 2008-01-11 at 20:39 +, Simon Riggs wrote: On Fri, 2008-01-11 at 15:05 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: If we had a function replace_serializable_snapshot(master_xid, txid_snapshot) this would allow us to use the txid_snapshot values to replace our transaction's serializable snapshot. ... whereupon we'd get wrong answers. Certainly you could not allow transaction xmin to go backwards, and I'm not sure what other restrictions there would be, but the whole thing gives me the willies. Sorry, forgot to add - global xmin isn't going backwards - neither is latest completed xid The xmin of the transaction will go backwards, but as long as we don't do anything prior to the setting of the cloned snapshot, what can go wrong? :-) Note that we required that the provider transaction have the attributes IsXactIsoLevelSerializable and XactReadOnly both being true, so we have the mandates that the resultant backend process: a) Is in read only mode, and b) Is in serializable mode. That's a pair of (possibly stretching-wide!) suspenders worth of support from Evil... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://linuxfinances.info/info/internet.html Trying to be happy is like trying to build a machine for which the only specification is that it should run noiselessly. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal for 8.4: PL/pgSQL - statement CASE
Added to TODO: o Add CASE capability to language (already in SQL) http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php --- Pavel Stehule wrote: Hello I found so PL/SQL support CASE statement http://download-east.oracle.com/docs/cd/B10500_01/appdev.920/a96624/04_struc.htm#484 I propose add this statement to PL/pgSQL too. Reasons: a) it's useful construct, b) this statement is defined in SQL/PSM - better conformance with ANSI Implementation: This statement is implemented in PL/pgPSM, so implementation will be backported to PL/pgSQL. Any ideas? Regards Pavel Stehule ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_standby for 8.2 (with last restart point)
On Fri, 28 Mar 2008, Gurjeet Singh wrote: This project doesn't make functional changes to stable releases, that's the reason why 8.2 will never get patched to add the %r feature. I completely understand that, but still was hoping that we'd change that. Well, then you really don't understand this at all then, so let's work on that for a bit. http://www.postgresql.org/support/versioning is the official statement, perhaps some examples will help clarify where and why the line is where it is. One of the first patches I ever submitted made a minor change to a contrib utility used solely for benchmarking (pgbench) that added a useful feature, only if you passed it the right parameter. That was considered for a tiny bit before being rejected as a feature change too large to put into a stable branch. That was a small change in a utility that should never be run on a production system. You're trying to get a change made to the code path people rely on for their *backups*. Good luck with that. The parable I enjoy pulling out in support of this policy is MySQL bug #31001: http://www.mysqlperformanceblog.com/2007/10/04/mysql-quality-of-old-and-new-features/ where they added a seemingly minor optimization to something and accidentally broke the ability to sort in some cases. There's always a small risk that comes with any code change, and this is why you don't ever touch working production code unless you're fixing a bug that's more troublesome than that risk. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_standby for 8.2 (with last restart point)
On Fri, Mar 28, 2008 at 9:47 AM, Greg Smith [EMAIL PROTECTED] wrote: On Fri, 28 Mar 2008, Gurjeet Singh wrote: This project doesn't make functional changes to stable releases, that's the reason why 8.2 will never get patched to add the %r feature. I completely understand that, but still was hoping that we'd change that. Well, then you really don't understand this at all then, so let's work on that for a bit. http://www.postgresql.org/support/versioning is the official statement, perhaps some examples will help clarify where and why the line is where it is. One of the first patches I ever submitted made a minor change to a contrib utility used solely for benchmarking (pgbench) that added a useful feature, only if you passed it the right parameter. That was considered for a tiny bit before being rejected as a feature change too large to put into a stable branch. That was a small change in a utility that should never be run on a production system. You're trying to get a change made to the code path people rely on for their *backups*. Good luck with that. The parable I enjoy pulling out in support of this policy is MySQL bug #31001: http://www.mysqlperformanceblog.com/2007/10/04/mysql-quality-of-old-and-new-features/ where they added a seemingly minor optimization to something and accidentally broke the ability to sort in some cases. There's always a small risk that comes with any code change, and this is why you don't ever touch working production code unless you're fixing a bug that's more troublesome than that risk. Point well taken. And when I said 'I completely understand that', I meant I understood Postgres' policy for patching older releases. And thanks for the links; it feels good to know that there's an official stand on this topic in Postgres, rather than 'no known serious bugs'. :) I am still looking for comments on the correctness of this script and above mentioned procedure for running it on an 8.2.x release. Thanks and best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [HACKERS] pg_standby for 8.2 (with last restart point)
Greg Smith [EMAIL PROTECTED] writes: ... That was a small change in a utility that should never be run on a production system. You're trying to get a change made to the code path people rely on for their *backups*. Good luck with that. While I quite agree with Greg's comments about not changing stable release branches unnecessarily, it seems that there's another consideration in this case. If we don't back-patch %r then users will have to rely on hacky scripts like the one posted upthread. Is that really a net gain in reliability? (I'm honestly not sure of the answer; I'm just thinking it might be open to debate. In particular I don't remember how complicated the patch to add %r was.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Script binaries renaming
On Wednesday 26 March 2008 12:17, Andrew Dunstan wrote:
Zdenek Kotala wrote:
Tom Lane napsal(a):
Zdenek Kotala [EMAIL PROTECTED] writes:
Why we have pg_dump and pg_dumpall? Or I think pg_resetxlog has same
output like pg_controldata. I think we can merge these commands.
Now we're into change for the sake of change? Those programs don't
have any naming problem.
yes, but they are redundant
Really? How so? They have overlapping functionality, but neither has a
subset of the other's functionality.
Possibly we should merge them, but that's a different issue, and in
particular has nothing to do with renaming, so it doesn't belong in this
thread.
Actually it does belong in this thread, at least in so much that we should
probably think about if we really want to do a bunch of command renaming when
there is a good chance we might want to change these names further in
subsequent releases to address real problems. (I'd be tempted to hold the
cosmetic changes untill we bump to 9.0 anyway, when backward
incompatabilities will make more sense)
One example of the above would be changing binaries to address the current
sub-par support for multiple versions of postgres on a single machine,
something like what debian/ubuntu have done with pg_lsclusters,
pg_initcluster, pg_ctlcluster, etc... istm a bad idea to rename initdb to
pg_init in the next release for what are mostly cosmetic reasons if in the
next 2 or 3 releases down the line we need to change it for more pratical
reasons.
(Side note: I know some people hate the debian changes to the various command
utilities because of the confusion it creates when trying to help people with
postgres; consider that at least those changes solve a class of problems, the
proposed changes will cause far more problems for end-users / helpers, and
for far less of a valid reason)
As for the problem faced by Sun, if they really have an issue with the naming
system, theres no reason they can't rename the binaries themselves to match
thier own naming standards since they control their own packages. I use
Solaris and this wouldn't bother me at all.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_standby for 8.2 (with last restart point)
On Fri, 28 Mar 2008, Tom Lane wrote: While I quite agree with Greg's comments about not changing stable release branches unnecessarily, it seems that there's another consideration in this case. I was just trying to set Gurjeet's expectations appropriately while taking the suggestion seriously anyway. This is one of those cases where you could argue that since there is no good way to find out what to do here, it's an operational bug serious enough to patch. In particular I don't remember how complicated the patch to add %r was Unfortunately it was mixed in with the archive_mode GUC and log_startpoints changes so the diff is more complicated than just that: http://repo.or.cz/w/PostgreSQL.git?a=commit;h=a14266760bd403abd38be499de1619a825e0438b A quick glance suggests this part might only be 14 lines added to xlog.c though (the 11 lines starting with case 'r' and the 3 new variable definitions just above it that uses). -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
On Thursday 27 March 2008 17:11, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
On Sunday 16 March 2008 22:18, Tom Lane wrote:
Fix TransactionIdIsCurrentTransactionId() to use binary search instead
of linear search when checking child-transaction XIDs.
Are there any plans to backpatch this into REL8_3_STABLE?
No.
It looks like I am
hitting a pretty serious performance regression on 8.3 with a stored
procedure that grabs a pretty big recordset, and loops through doing
insertupdate on unique failures. The procedure get progressivly
slower the more records involved... and dbx shows me stuck in
TransactionIdIsCurrentTransactionId().
If you can convince me it's a regression I might reconsider, but I
rather doubt that 8.2 was better,
Well, I can't speak for 8.2, but I have a second system crunching the same
data using the same function on 8.1 (on lesser hardware in fact), and it
doesn't have these type of issues. Looking over the past week, the 8.3 box
averages about 19 minutes to complete each run, and the 8.1 box averages 15
minutes (sample size is over 100 iterations of both). Of course this is when
it completes, the 8.3 box often does not complete, as it falls farther behind
during the day and eventually cannot finish (it does periodic intra-day
summing, so there's a limited time frame it has to run, and it's jobs end up
taking hours to complete).
I am open to the idea that there is some other issue going on here, but
whenever I look at it, it seems stuck in
TransactionIdIsCurrentTransactionId(), progress for the function does get
increasingly slower as it progresses, and I can watch the process consuming
more and more memory as it goes on... I can probably get some dtrace output
tommorrow if you want.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Robert Treat [EMAIL PROTECTED] writes: If you can convince me it's a regression I might reconsider, but I rather doubt that 8.2 was better, Well, I can't speak for 8.2, but I have a second system crunching the same data using the same function on 8.1 (on lesser hardware in fact), and it doesn't have these type of issues. If you can condense it to a test case that is worse on 8.3 than 8.1, I'm willing to listen... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
