Re: [HACKERS] postmaster recovery and automatic restart suppression
Hi, On Fri, Jun 5, 2009 at 1:02 AM, Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: Hi, in case of a serious failure of a backend or an auxiliary process the postmaster performs a crash recovery and restarts the db automatically. Is there a possibility to deactivate the restart and to force the postmaster to simply exit at the end ? Good point. I also think that this makes a handling of failover more complicated. In other words, clusterware cannot determine whether to do failover when it detects the death of the primary postgres. A wrong decision might cause split brain syndrome. How about new GUC parameter to determine whether to restart postmaster automatically when it fails abnormally? This would be useful for various failover system. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] PostgreSQL Developer meeting minutes up
Hi, Quoting Ron Mayer rm...@cheapcomplexdevices.com: Seems you'd want to do is create a new branch as close to the point where the bug was introduced - and then merge that forward into each of the branches. Thank you for pointing this out. As a fan of monotone I certainly know and like that way. However, for people who are used to CVS, lots of branching and merging quickly sound dangerous and messy. So I'd like to keep things as simple as possible while still keeping possibilities open for the future. Note that a requirement for daggy fixes is that the bug is fixed close to the point where it was introduced. So fixing it on the oldest stable branch that introduced a bug instead of fixing it on HEAD and then back-porting would certainly be a step into the right direction. And I think it would be sufficient in most cases. If not, we can still enhance that and used daggy fixes later on (as long as we have a conversion that allows merging, that is). Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Simple, safe hot backup and recovery
Hi, all I posted this message to the pgsql-general mailing list, however there was no response. So, I repost the mail to pgsql-hackers. I want to achieve a simple, safe hot backup and recovery using PostgreSQL 8.3 or later. The standalone hot backup script listed in 24.3.5.1. Standalone hot backups (http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html) seems to be very helpful to me because it's simple and it matches my needs. I don't need the timeline feature provided by PITR. However, the recovery procedure is somewhat complex, as the documentation shows. So, I want to rely on the PostgreSQL's crush recovery mechanism. Is this a bad idea? I wrote a prototype script for that reason. The script's first part is based on the standalone hot backup script taken from the documentation. The last part is my idea. The archived WAL segment files are stored into the backup's pg_xlog/ and remake the backup file. The script works for me, but I want to know whether this approach is really safe or not. If it's not safe, I want to know the reason. Anybody has good idea? Is there another solution? Thanks in advance, -- Yoshinori Sano yoshinori.s...@gmail.com hotbackup.sh Description: Bourne shell script -- 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] Synchronous replication: status of standby side
Hi, On Thu, Jun 4, 2009 at 7:53 PM, Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: We are planning to use the HotStandby feature too. That would require to have the correct state information since we have to control the DB clients when and how they can connect or reconnect. But also in general, from our platform POV it's an important feature to have the right view on the system at any point of time (e.g. in case of update avoid switchover if standby is out of sync, ...). I understand that the feature to check the status of replication is useful, too. On the other hand, I hesitate to make the size of synch rep patch bigger. Big patch increases the burden on the reviewers. Current patch is already big. So, I think that this feature should be postponed at least until core of synch rep will be committed. BTW. Which kind of status should be detectable when combining replication with Hot Standby? There are several statuses. For example, the last commit transaction in the primary server has been; 1) not sent to the standby server yet. 2) sent to the standby, but not written there yet. 3) sent and written, but not fsynced yet. 4) sent, written and fsynced, but not applied yet. 5) etc.. And, which server should we ask the status, the primary or the standby server? In addition, can we expect that a replication break will be logged as an important event ? Yes, the termination of replication is logged as LOG message in the patch. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Simple, safe hot backup and recovery
Hi, On Fri, Jun 5, 2009 at 4:18 PM, Yoshinori Sano yoshinori.s...@gmail.com wrote: Hi, all I posted this message to the pgsql-general mailing list, however there was no response. So, I repost the mail to pgsql-hackers. I want to achieve a simple, safe hot backup and recovery using PostgreSQL 8.3 or later. The standalone hot backup script listed in 24.3.5.1. Standalone hot backups (http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html) seems to be very helpful to me because it's simple and it matches my needs. I don't need the timeline feature provided by PITR. However, the recovery procedure is somewhat complex, as the documentation shows. So, I want to rely on the PostgreSQL's crush recovery mechanism. Is this a bad idea? I wrote a prototype script for that reason. The script's first part is based on the standalone hot backup script taken from the documentation. The last part is my idea. The archived WAL segment files are stored into the backup's pg_xlog/ and remake the backup file. The script works for me, but I want to know whether this approach is really safe or not. If it's not safe, I want to know the reason. Anybody has good idea? Is there another solution? A crash recovery from standalone hot backup might not redo the latest transaction (generated after backup). It seems to be only guaranteed that a database is recovered up to the state just after pg_stop_backup. Does this meet your requirements? psql $DB_NAME -c SELECT pg_stop_backup(); sleep 10 # Why we need this? rm /var/lib/pgsql/backup_in_progress tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/ Since all WAL files generated during backup have to be added into backup.tar, I guess that sleep 10 waits until they are archived. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] It's June 1; do you know where your release is?
On Thu, Jun 4, 2009 at 5:23 PM, Andrew Dunstan and...@dunslane.net wrote: Yeah. The patch below should be far more future-proof (and past-proof, for that matter). Dave, any chance you can test it? Doesn't work here, presumably because there is a PerlEz.lib in the same directory as Perl510.lib, causing the glob to match more than 1 file. -- Dave Page EnterpriseDB UK: 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] Simple, safe hot backup and recovery
Hi,
On Fri, Jun 5, 2009 at 5:10 PM, Fujii Masao masao.fu...@gmail.com wrote:
psql $DB_NAME -c SELECT pg_stop_backup();
sleep 10 # Why we need this?
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
Since all WAL files generated during backup have to be added into backup.tar,
I guess that sleep 10 waits until they are archived.
In v8.4, pg_stop_backup waits until all WAL files used during backup
are archived.
So, sleep is already unnecessary for standalone hot backup.
Here is one-line patch to get rid of the description of that needless
operation from
backup.sgml.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Index: doc/src/sgml/backup.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.127
diff -c -r2.127 backup.sgml
*** doc/src/sgml/backup.sgml 27 May 2009 12:28:55 - 2.127
--- doc/src/sgml/backup.sgml 5 Jun 2009 09:09:36 -
***
*** 1335,1341
psql -c select pg_start_backup('hot_backup');
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c select pg_stop_backup();
- sleep 20
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
/programlisting
--- 1335,1340
--
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] It's June 1; do you know where your release is?
Dave Page wrote:
On Thu, Jun 4, 2009 at 5:23 PM, Andrew Dunstan and...@dunslane.net wrote:
Yeah. The patch below should be far more future-proof (and past-proof, for
that matter).
Dave, any chance you can test it?
Doesn't work here, presumably because there is a PerlEz.lib in the
same directory as Perl510.lib, causing the glob to match more than 1
file.
OK. try with this please:
my @perl_libs = grep { /perl\d+.lib$/ }
glob($solution-{options}-{perl} . '\lib\CORE\perl*.lib');
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] Synchronous replication: status of standby side
Em Sex, 2009-06-05 às 16:19 +0900, Fujii Masao escreveu: BTW. Which kind of status should be detectable when combining replication with Hot Standby? There are several statuses. For example, the last commit transaction in the primary server has been; 1) not sent to the standby server yet. 2) sent to the standby, but not written there yet. 3) sent and written, but not fsynced yet. 4) sent, written and fsynced, but not applied yet. 5) etc.. We could have some kind of table of replication status code and a last status code or a history of status codes, which is used by clients (psql, pgadmin, etc) to shows the replication status. And, which server should we ask the status, the primary or the standby server? Is expected that this information resides in both? For example, take a status code like 2) sent to the standby, but not written there yet., if we expect this in primary, in the stand by must have something like 2) received from primary, but not written here yet. as status code? I guess that would be a good way to check whether and how replication is working in both servers. I agree with you that this feature should be postponed. Best Regards, -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br http://www.rnp.br/keyserver/pks/lookup?search=0x8F3E3C06D428D10A signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
Re: [HACKERS] 8.4b2 tsearch2 strange error
I poked around in the dump for awhile. I still can't reproduce the failure from a standing start. It looks to me like Tatsuo's database was possibly produced from separate schema and data load steps, followed by some update operations. It would be nice to have a full script for reproducing the state of the database. Sorry, I don't have full SQL logs. Here are DML used for the database: INSERT INTO msginfo(file_size, file_mtime, msg_date, flags, hdr_from, hdr_to, hdr_cc, hdr_newsgroups, hdr_subject, hdr_msgid, hdr_inreplyto, hdr_references, body_text, body_index) VALUES(%u, E'%s', E'%s', %u, E'%s', E'%s', E'%s', E'%s', E'%s', E'%s', E'%s', E'%s', E'%s', to_tsvector(E'%s')) DELETE FROM msginfo WHERE msg_sid = %lu DELETE FROM msginfo WHERE msg_sid IN (SELECT msg_sid FROM msginfo EXCEPT SELECT msg_sid FROM msg_folderinfo) So no exotic things appear... -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] postmaster recovery and automatic restart suppression
Hi, -Original Message- From: ext Fujii Masao [mailto:masao.fu...@gmail.com] Sent: Friday, June 05, 2009 8:14 AM To: Kolb, Harald (NSN - DE/Munich) Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] postmaster recovery and automatic restart suppression Hi, On Fri, Jun 5, 2009 at 1:02 AM, Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: Hi, in case of a serious failure of a backend or an auxiliary process the postmaster performs a crash recovery and restarts the db automatically. Is there a possibility to deactivate the restart and to force the postmaster to simply exit at the end ? Good point. I also think that this makes a handling of failover more complicated. In other words, clusterware cannot determine whether to do failover when it detects the death of the primary postgres. A wrong decision might cause split brain syndrome. Mh, I cannot follow your reflections. Could you explain a little bit more ? How about new GUC parameter to determine whether to restart postmaster automatically when it fails abnormally? This would be useful for various failover system. A new GUC parameter would be the optimal solution. Since I'm new to the community, what's the usual way to make this happen ? Regards, Harald. -- 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] Improving the ngettext() patch
(Grrr, declension, not declination.) Plural-Forms: nplurals=3; plural=n%10==1 n%100!=11 ? 0 :n%10=2 n%10=4 (n%10010 ||n%100=20) ? 1 : 2;\n Thanks. The above (ignoring backslash-EOL) is the form recommended for Russian (inter alia(s)) in the Texinfo manual for gettext (info gettext). FWIW this might be an alternative: Plural-Forms: nplurals=3; plural=((n - 1) % 10) = (5-1) || (((n - 1) % 100) = (14-1) ((n - 1) % 100) = (11 - 1)) ? 2 : ((n - 1) % 10) == (1 - 1) ? 0 : 1;\n David Hudson
Re: [HACKERS] PostgreSQL Developer meeting minutes up
Markus Wanner mar...@bluegap.ch writes: Note that a requirement for daggy fixes is that the bug is fixed close to the point where it was introduced. So fixing it on the oldest stable branch that introduced a bug instead of fixing it on HEAD and then back-porting would certainly be a step into the right direction. I think it's already been made crystal clear that the people who actually do this work don't do it that way, and are uninterested in allowing their tools to force them to do it that way. Patching from HEAD back works better for us for a number of reasons, the main one being that HEAD is the version of the code that's most swapped into our awareness. However, so long as we can have a separate working copy per branch, I see no problem with preparing all the versions of a patch and then committing them back-to-front. What I'm not clear about is the mechanics for doing that. Would someone explain exactly what the steps should be to produce the nicest-looking git history? 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] Simple, safe hot backup and recovery
Fujii Masao wrote: Hi, On Fri, Jun 5, 2009 at 5:10 PM, Fujii Masao masao.fu...@gmail.com wrote: psql $DB_NAME -c SELECT pg_stop_backup(); sleep 10 # Why we need this? rm /var/lib/pgsql/backup_in_progress tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/ Since all WAL files generated during backup have to be added into backup.tar, I guess that sleep 10 waits until they are archived. In v8.4, pg_stop_backup waits until all WAL files used during backup are archived. So, sleep is already unnecessary for standalone hot backup. Here is one-line patch to get rid of the description of that needless operation from backup.sgml. Good point, applied. Thanks. -- Bruce Momjian br...@momjian.ushttp://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] Re: [COMMITTERS] pgsql: Initialise perl library as documented in perl API.
Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: That's what we have a buildfarm for ;-) There's a failure on FBSD too by the look of it. I'll dig some more to see what I can find. I see this when building HEAD on Fedora 10: plperl.c: In function 'plperl_init_interp': plperl.c:450: warning: null argument where non-null required (argument 3) Seems like a good hint ... Yeah. I didn't get that. But the odd thing is that on 5.8 especially it shouldn't matter. perl 5.8.8's perl.h has: #ifndef PERL_SYS_INIT3 # define PERL_SYS_INIT3(argvp,argcp,envp) PERL_SYS_INIT(argvp,argcp) #endif and the only place it's defined elsewhere that I can see is for OS2 (for anyone still running it!). These two Unixware machines have 5.8.8. and the FBSD machine has 5.8.7. So surely it can't be that, unless I'm missing something badly. The unixish.h file has this on 5.8: # define PERL_SYS_INIT(c,v) MALLOC_CHECK_TAINT2(*c,*v) PERL_FPU_INIT MALLOC_INIT I'm guessing the problem is actually somewhere in there. [hours of digging later] On FBSD at least, this is failing in the function Perl_do_taint(). I can't see anything too terrible in the source for this, but to dig further I'd have to build another perl with debugging turned on. Anyway, it turns out that this actually isn't called at all if Perl is configured to use its own malloc() routines instead of those supplied by the system, as is the case on Fedora, for example, but not FBSD. So we have a bit of a dilemma. We know that this initialization is required by the Perl API, and we know that some platforms fail without it, and we also know that this fails on some platforms which use the system's malloc() for perl. I think we need to float a bug upstream to the perl guys on this, but as a holding position I suggest that we alter the #ifdef test to avoid calling PERL_SYS_INIT3() where MYMALLOC is defined. It's ugly, but I can't think of another simple way around it (and we've done worse things to accommodate platform weirdness ;-) ) BTW, it's not caused by passing NULL as the third argument of PERL_SYS_INIT3() - changing that was the first thing I tried. 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] PostgreSQL Developer meeting minutes up
Tom Lane wrote: Markus Wanner mar...@bluegap.ch writes: Note that a requirement for daggy fixes is that the bug is fixed close to the point where it was introduced. So fixing it on the oldest stable branch that introduced a bug instead of fixing it on HEAD and then back-porting would certainly be a step into the right direction. I think it's already been made crystal clear that the people who actually do this work don't do it that way, and are uninterested in allowing their tools to force them to do it that way. Patching from HEAD back works better for us for a number of reasons, the main one being that HEAD is the version of the code that's most swapped into our awareness. Yeah, a requirement to work from the back branch forward is quite unacceptable IMNSHO. It's also quite unreasonable. The tool is there to help, not to force an unnatural work pattern on us. 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] Re: [COMMITTERS] pgsql: Initialise perl library as documented in perl API.
Andrew Dunstan wrote: [hours of digging later] On FBSD at least, this is failing in the function Perl_do_taint(). I can't see anything too terrible in the source for this, but to dig further I'd have to build another perl with debugging turned on. Anyway, it turns out that this actually isn't called at all if Perl is configured to use its own malloc() routines instead of those supplied by the system, as is the case on Fedora, for example, but not FBSD. So we have a bit of a dilemma. We know that this initialization is required by the Perl API, and we know that some platforms fail without it, and we also know that this fails on some platforms which use the system's malloc() for perl. I think we need to float a bug upstream to the perl guys on this, but as a holding position I suggest that we alter the #ifdef test to avoid calling PERL_SYS_INIT3() where MYMALLOC is defined. It's ugly, but I can't think of another simple way around it (and we've done worse things to accommodate platform weirdness ;-) ) BTW, it's not caused by passing NULL as the third argument of PERL_SYS_INIT3() - changing that was the first thing I tried. I think with an extensive C comment that is acceptable at this point, at least until we hear from the perl guys. I would think projects would have hit this issue too. -- Bruce Momjian br...@momjian.ushttp://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] Re: [COMMITTERS] pgsql: Initialise perl library as documented in perl API.
Andrew Dunstan and...@dunslane.net writes: I think we need to float a bug upstream to the perl guys on this, but as a holding position I suggest that we alter the #ifdef test to avoid calling PERL_SYS_INIT3() where MYMALLOC is defined. +1 on both. BTW, it's not caused by passing NULL as the third argument of PERL_SYS_INIT3() - changing that was the first thing I tried. Nonetheless, that compiler warning is pretty ugly; please do something about it. I concur with your feeling that setting up a dummy empty environment array is probably the safest thing to do. 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] 8.4 release schedule: RC1 next week
After some discussion the core committee has agreed that it's time to wind down the 8.4 beta cycle. We will be shooting for: RC1 wrap Thursday 6/11 for release Monday 6/15. RC2 (if needed) the following Thursday. 8.4.0 wrap Thursday 6/25 for release Monday 6/29. This is optimistic, of course, but barring any new complex bug reports it seems meetable. Let's get those open items taken care of! 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] Synchronous replication: status of standby side
Hi, -Original Message- From: ext Fujii Masao [mailto:masao.fu...@gmail.com] Sent: Friday, June 05, 2009 9:19 AM To: Kolb, Harald (NSN - DE/Munich) Cc: pgsql-hackers@postgresql.org; Czichy, Thoralf (NSN - FI/Helsinki) Subject: Re: [HACKERS] Synchronous replication: status of standby side Hi, On Thu, Jun 4, 2009 at 7:53 PM, Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: We are planning to use the HotStandby feature too. That would require to have the correct state information since we have to control the DB clients when and how they can connect or reconnect. But also in general, from our platform POV it's an important feature to have the right view on the system at any point of time (e.g. in case of update avoid switchover if standby is out of sync, ...). I understand that the feature to check the status of replication is useful, too. On the other hand, I hesitate to make the size of synch rep patch bigger. Big patch increases the burden on the reviewers. Current patch is already big. So, I think that this feature should be postponed at least until core of synch rep will be committed. BTW. Which kind of status should be detectable when combining replication with Hot Standby? There are several statuses. For example, the last commit transaction in the primary server has been; 1) not sent to the standby server yet. This would be the standalone mode, otherwise this would mean that the commit is not yet finished. So this situation wouldn't be of interest. 2) sent to the standby, but not written there yet. 3) sent and written, but not fsynced yet. 4) sent, written and fsynced, but not applied yet. 5) etc.. Mh, all these situations mean a not finished commit, so I cannot see the problem. I meant more global states, like - replication off - replication on - replication starting (secondary currently synchronizing with primary) - replication broken (currently in recovery mode (or sth. similar)) And, which server should we ask the status, the primary or the standby server? Both should have the same view and the same state, so it should be possible to ask both of them. Normally it would be sufficient to ask the primary, but if the primary side crashes, it might be necessary to ask the secondary which is perhaps not yet triggered to switch to primary mode. What will happen on the secondary side when this side detects that the replication breaks down. Will there be any active activity to solve the problem or does it keep itsself passive and waits that either the primary recovers or the trigger file arises ? In addition, can we expect that a replication break will be logged as an important event ? Yes, the termination of replication is logged as LOG message in the patch. Ah, sorry for the question, I saw the variuos ereports in the patch now (and also the replication_broken events from primary and secondary side). Thanks. Regards, Harald. -- 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] It's June 1; do you know where your release is?
On Fri, Jun 5, 2009 at 1:19 PM, Andrew Dunstan and...@dunslane.net wrote:
OK. try with this please:
my @perl_libs = grep { /perl\d+.lib$/ }
glob($solution-{options}-{perl} . '\lib\CORE\perl*.lib');
That looks good.
--
Dave Page
EnterpriseDB UK: 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] PostgreSQL Developer meeting minutes up
On Fri, Jun 5, 2009 at 9:38 AM, Tom Lanet...@sss.pgh.pa.us wrote: Markus Wanner mar...@bluegap.ch writes: Note that a requirement for daggy fixes is that the bug is fixed close to the point where it was introduced. So fixing it on the oldest stable branch that introduced a bug instead of fixing it on HEAD and then back-porting would certainly be a step into the right direction. I think it's already been made crystal clear that the people who actually do this work don't do it that way, and are uninterested in allowing their tools to force them to do it that way. Patching from HEAD back works better for us for a number of reasons, the main one being that HEAD is the version of the code that's most swapped into our awareness. However, so long as we can have a separate working copy per branch, I see no problem with preparing all the versions of a patch and then committing them back-to-front. What I'm not clear about is the mechanics for doing that. Would someone explain exactly what the steps should be to produce the nicest-looking git history? I'm sure someone is going to come in here and again recommend merging, but I'm going to again recommend not merging. Cherry-picking is the way to go here. Or just commit to each branch completely separately with the same commit message; cherry-pick at least IMO is just a convenience to help you attempt to apply the patch to a different branch. The way you're using commit messages to construct the release notes really puts a limits on what the history has to look like. I think it would be good to find a better way to generate release notes that isn't quite so dependent on having a very tight history, but even if we do that I think in this particular situation cherry-picking is going to be less work for the committers than any of the other options that have been proposed. ...Robert -- 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] PostgreSQL Developer meeting minutes up
Robert Haas robertmh...@gmail.com writes: I'm sure someone is going to come in here and again recommend merging, but I'm going to again recommend not merging. Cherry-picking is the way to go here. Or just commit to each branch completely separately with the same commit message; cherry-pick at least IMO is just a convenience to help you attempt to apply the patch to a different branch. Commit to each branch separately is surely the closest analog to what we have done historically. What I'm trying to understand is whether there's an easy variant on that that'd expose the related-ness of the patch versions in a way git understands, hopefully giving us more ability to leverage git's capabilities in future. However, given that we don't do any real development on the back branches, it might be that trying to be smart about this is a waste of time anyway. Surely only the HEAD version of the patch is going to be something that other developers care about merging with. 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] PostgreSQL Developer meeting minutes up
On Fri, Jun 5, 2009 at 11:37 AM, Tom Lanet...@sss.pgh.pa.us wrote: However, given that we don't do any real development on the back branches, it might be that trying to be smart about this is a waste of time anyway. Surely only the HEAD version of the patch is going to be something that other developers care about merging with. I think that's about right. I think there would be some benefit in developning better tools - release notes seem to be the main issue - so that, for example, if I develop a complex feature and you think my code is great (ok, now I'm dreaming), you could actually merge my commits rather than flattening them. The EXPLAIN stuff I'm working on right now is a good example where it's a lot easier to review the changes piece by piece rather than as a big unit, but I know you won't want to commit it that way because (1) with CVS, it would be a lot more work to do that, and (2) it would suck a lot of extra commits into the data you use to generate release notes, thereby making that process more complex. I'm actually going to the trouble of trying to make sure that each of my commits does one and only one thing that can be separately checked, tested, and either accepted (hopefully) or rejected (hopefully not). Hopefully, that will still help with reviewing, but then if you commit it, it'll probably go in as one stomping commit that changes the world, or at most as two or three commits that are all still pretty big. There are certainly cases where big stomping commits are good (I have them in my own projects, too, and branches with long histories of little dumb commits regularly get squashed and rebased before merging) but I think it would be nice to have other options. (As a side benefit, if one of my little micro-commits turns out to have a bug, you can easily revert *just that commit*, without having to manually sort out exactly which pieces related to that change.) ...Robert -- 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] PostgreSQL Developer meeting minutes up
Robert Haas robertmh...@gmail.com writes: [ about micro commits ] (As a side benefit, if one of my little micro-commits turns out to have a bug, you can easily revert *just that commit*, without having to manually sort out exactly which pieces related to that change.) I don't actually have a lot of faith in such an approach. My experience is that bugs arise from unforeseen interactions of changes, and that backing out just one isn't a useful thing to do, even if none of the later parts of the patch directly depend on it. So, yeah, presenting a patch as a series of edits can be useful for review purposes, but I'm not at all excited about cluttering the long-term project history with a zillion micro-commits. One of the things I find most annoying about reviewing the current commit history is that Bruce has taken a micro-commit approach to managing the TODO list --- I was seldom so happy as the day that disappeared from CVS, because of the ensuing reduction in noise level. 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] PostgreSQL Developer meeting minutes up
On Fri, Jun 5, 2009 at 12:15 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: [ about micro commits ] (As a side benefit, if one of my little micro-commits turns out to have a bug, you can easily revert *just that commit*, without having to manually sort out exactly which pieces related to that change.) I don't actually have a lot of faith in such an approach. My experience is that bugs arise from unforeseen interactions of changes, and that backing out just one isn't a useful thing to do, even if none of the later parts of the patch directly depend on it. So, yeah, presenting a patch as a series of edits can be useful for review purposes, but I'm not at all excited about cluttering the long-term project history with a zillion micro-commits. One of the things I find most annoying about reviewing the current commit history is that Bruce has taken a micro-commit approach to managing the TODO list --- I was seldom so happy as the day that disappeared from CVS, because of the ensuing reduction in noise level. I've never even noticed that noise, even when reviewing older history. The power of git log to get you exactly the commits you care about is not to be underestimated. With regard to micro-commits, I don't have hugely strong feelings on the issue. I like them in certain situations, and I think that git makes it feasible to use them that way if you want to; but if you don't want to, I don't think that's a disaster either. ...Robert -- 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] PostgreSQL Developer meeting minutes up
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: [ about micro commits ] (As a side benefit, if one of my little micro-commits turns out to have a bug, you can easily revert *just that commit*, without having to manually sort out exactly which pieces related to that change.) I don't actually have a lot of faith in such an approach. My experience is that bugs arise from unforeseen interactions of changes, and that backing out just one isn't a useful thing to do, even if none of the later parts of the patch directly depend on it. So, yeah, presenting a patch as a series of edits can be useful for review purposes, but I'm not at all excited about cluttering the long-term project history with a zillion micro-commits. One of the things I find most annoying about reviewing the current commit history is that Bruce has taken a micro-commit approach to managing the TODO list --- I was seldom so happy as the day that disappeared from CVS, because of the ensuing reduction in noise level. Yea, that was a problem that is now fixed. -- Bruce Momjian br...@momjian.ushttp://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
[HACKERS] pg_migrator mention in documentation
Now that pg_migrator is in beta (http://pgfoundry.org/frs/?group_id=1000235), I was wondering if we want to mention pg_migrator anywhere in our docs or release notes? Josh Berkus is already mentioning it in the draft press release. I expect a final pg_migrator 8.4 release by the end of next week, before we release Postgres RC1. -- Bruce Momjian br...@momjian.ushttp://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
[HACKERS] movavg
Folks,
Here's Harada-san's moving average windowing function, expressed as a
contrib module. It has tests, etc.
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/Makefile b/contrib/Makefile
index 738a28a..e9fc2f9 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -23,6 +23,7 @@ WANTED_DIRS = \
isn \
lo \
ltree \
+ movavg \
oid2name\
pageinspect \
pg_buffercache \
diff --git a/contrib/movavg/Makefile b/contrib/movavg/Makefile
new file mode 100644
index 000..b295575
--- /dev/null
+++ b/contrib/movavg/Makefile
@@ -0,0 +1,17 @@
+# $PostgreSQL$
+
+MODULES = movavg
+DATA_built = movavg.sql
+DATA = uninstall_movavg.sql
+REGRESS = install_movavg one_sliding
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/movavg
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/movavg/data/one_sliding.data
b/contrib/movavg/data/one_sliding.data
new file mode 100644
index 000..9811557
--- /dev/null
+++ b/contrib/movavg/data/one_sliding.data
@@ -0,0 +1,11 @@
+develop10 52002007/08/01
+sales 1 50002006/10/01
+personnel 5 35002007/12/10
+sales 4 48002007/08/08
+sales 6 55002007/01/02
+personnel 2 39002006/12/23
+develop7 42002008/01/01
+develop9 45002008/01/01
+sales 3 48002007/08/01
+develop8 60002006/10/01
+develop11 52002007/08/15
diff --git a/contrib/movavg/expected/install_movavg.out
b/contrib/movavg/expected/install_movavg.out
new file mode 100644
index 000..43f11fa
--- /dev/null
+++ b/contrib/movavg/expected/install_movavg.out
@@ -0,0 +1,3 @@
+SET client_min_messages = warning;
+\set ECHO none
+RESET client_min_messages;
diff --git a/contrib/movavg/expected/one_sliding.out
b/contrib/movavg/expected/one_sliding.out
new file mode 100644
index 000..e398e4f
--- /dev/null
+++ b/contrib/movavg/expected/one_sliding.out
@@ -0,0 +1,32 @@
+CREATE TABLE empsalary(
+ depname varchar,
+ empno bigint,
+ salary int,
+ enroll_date date
+);
+\COPY empsalary FROM 'data/one_sliding.data'
+SELECT
+depname,
+salary,
+movavg(salary::float8, 1) OVER w
+FROM empsalary
+WINDOW w AS (
+ PARTITION BY depname
+ ORDER BY salary
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+);
+ depname | salary | movavg
+---++--
+ develop | 4200 | 4350
+ develop | 4500 | 4633.333
+ develop | 5200 | 4966.667
+ develop | 5200 | 5466.667
+ develop | 6000 | 5600
+ personnel | 3500 | 3700
+ personnel | 3900 | 3700
+ sales | 4800 | 4800
+ sales | 4800 | 4866.667
+ sales | 5000 | 5100
+ sales | 5500 | 5250
+(11 rows)
+
diff --git a/contrib/movavg/movavg.c b/contrib/movavg/movavg.c
new file mode 100644
index 000..b5e28ca
--- /dev/null
+++ b/contrib/movavg/movavg.c
@@ -0,0 +1,125 @@
+#include postgres.h
+#include fmgr.h
+#include windowapi.h
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(movavg);
+
+Datum movavg(PG_FUNCTION_ARGS);
+
+typedef struct {
+ int64 exiting;/* absolute position of exiting row */
+ int64 entering; /* absolute position of entering row */
+ float8 svalue; /* trans value */
+ int count; /* the number of accumlated values */
+} movavg_data;
+
+Datum
+movavg(PG_FUNCTION_ARGS)
+{
+ WindowObjectwinobj = PG_WINDOW_OBJECT();
+ boolisnull, isout;
+ int64 cur = WinGetCurrentPosition(winobj);
+ int32 range;
+ Datum value;
+ int i;
+ boolconst_range;
+
+ /* current row +/- range rows are the targets */
+ range = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, isnull));
+ if (isnull || range 0)
+ {
+ elog(ERROR, invalid range);
+ }
+ /* if it's stable Const value or not */
+ const_range = get_fn_expr_arg_stable(fcinfo-flinfo, 1);
+
+ /*
+* For variable range arguments, we only calculate exact
+* average for all the target rows. Otherwise we can
+* optimze it by subtract/add method.
+*/
+ if (!const_range)
+ {
+
Re: [HACKERS] Postgres delays function returning large set of data
Pavel Stehule wrote:
Hello
can you send source code? There are two types of C SRF functions. One
returns row ro by row, second store returned rows and returns block.
What did you use?
I had originally used the style that returns row by row, but after reading
the page i created a new function which returns a block. This version runs
slightly faster (12.5 seconds to run my test case) but it is still far
slower than expected.
A few notes on the code:
getdata function returns an array with length 2*size, the first size
elements are one colum the other size elements are the next column.
I have timed the call getdata and determined it consumes on average around
30ms of my test case's run time.
code
PG_FUNCTION_INFO_V1(getTableFastHDF5);
Datum
getTableFastHDF5(PG_FUNCTION_ARGS)
{
/*{{{*/
/* Locals */
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo-resultinfo;
FuncCallContext *fcc;
TupleDesc tupdesc;
Tuplestorestate *tupstore;
MemoryContext per_query_ctx;
MemoryContext oldcontext;
AttInMetadata *attinmeta;
int *data;
int size;
int i;
if (!connections)
ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(No
connections open, use openHDF5 to open a file first)));
data = getdata(textToStr(PG_GETARG_TEXT_P(0)), PG_GETARG_INT32(1),
PG_GETARG_INT32(2), size, TEMPORARY_CONTEXT);
per_query_ctx = rsinfo-econtext-ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);
tupdesc = rsinfo-expectedDesc;
tupstore = tuplestore_begin_heap(true, false, SortMem);
if (data == NULL)
ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(Could not
find specified data in file)));
for (i = 0; i size; i++)
{
/*{{{*/
Datum val[2];
bool nulls[2];
HeapTuple tup;
MemoryContextSwitchTo(oldcontext);
MemSet(nulls, false, 2 * sizeof(bool));
/* fill strings to be turned into tuple */
val[0] = Int32GetDatum(data[i]);
val[1] = Int32GetDatum(data[i + size]);
/* make tuple */
tup = heap_form_tuple(tupdesc, val, nulls);
/* make tuple to datum so it can be returned */
MemoryContextSwitchTo(per_query_ctx);
tuplestore_puttuple(tupstore, tup);
/* return, but there is more to send */
/*}}}*/
}
/* return and finish sending */
tuplestore_donestoring(tupstore);
MemoryContextSwitchTo(oldcontext);
rsinfo-returnMode = SFRM_Materialize;
rsinfo-setResult = tupstore;
rsinfo-setDesc = tupdesc;
return (Datum) 0;
/*}}}*/
}
/code
--
View this message in context:
http://www.nabble.com/Postgres-delays-function-returning-large-set-of-data-tp23853886p23891972.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Postgres delays function returning large set of data
flippo00110001 dde...@umich.edu writes: tupstore = tuplestore_begin_heap(true, false, SortMem); SortMem? What are you testing, 7.4? But anyway, the question I was going to ask is whether you made sure the data set you're trying to return fits in memory. If the tuplestore spills to disk then it's certainly going to be a lot slower than the function itself is. I note that you seem to be leaking copies of all the data in the inner loop --- it'd probably be advisable to do heap_freetuple(tup) to avoid bloating executor memory with still a third copy of the dataset. It might also be advisable to rethink your coding boundaries: if getdata were to return a prefilled tuplestore instead of an array that has to be converted into a tuplestore, you would at least save memory space and perhaps some time too. 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] PostgreSQL Developer meeting minutes up
* Andrew Dunstan and...@dunslane.net [090605 13:55]: Yeah, a requirement to work from the back branch forward is quite unacceptable IMNSHO. It's also quite unreasonable. The tool is there to help, not to force an unnatural work pattern on us. Again, just to make it clear, git isn't going to *force* anyone to drastically change their workflow. For people who want to keep a separate working directory per branch, and just work on them as independently as they do with CVS, *nothing* is going to have to change, except the possible git push step required to actually publish your committed changes... But, if you want, you could just also have a post-commit hook that will do that push for you too, and you just don't commit until you're sure (a-la-cvs-style): cvs update === git stash save git pull git stash apply cvs commit === git commit -a git push The git stash is because git won't pull/merge remote work into a dirty workdir... This is the classic conflict CVS mess that git avoids, and then allows you to use all it's powerful merge machinery to merge any of your stashed local changes back into what you've just pulled. But I have a feeling that as people (specifically the comitters) get slowly introduced and exposed to some of the more advanced things git lets you do, and as you get comfortable with using it, people will *want* to start altering how they do thing, simply because they start to find out that git really allows them to do what they really want, rather than what they have thought they want because they've been so brainwashed by CVS... ;-) -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Developer meeting minutes up
Aidan Van Dyk wrote: * Andrew Dunstan and...@dunslane.net [090605 13:55]: Yeah, a requirement to work from the back branch forward is quite unacceptable IMNSHO. It's also quite unreasonable. The tool is there to help, not to force an unnatural work pattern on us. Again, just to make it clear, git isn't going to *force* anyone to drastically change their workflow. My reaction was against someone saying in effect don't work that way, work this way. So make your argument to that person ;-) [...] I have a feeling that as people (specifically the comitters) get slowly introduced and exposed to some of the more advanced things git lets you do, and as you get comfortable with using it, people will *want* to start altering how they do thing, simply because they start to find out that git really allows them to do what they really want, rather than what they have thought they want because they've been so brainwashed by CVS... The whole point is that we want something better *that suits our work patterns*. Almost all the backpatching that gets done is by the committers. So we have a bunch of concerns that are not relevant to that vast majority of developers. In particular, it would be nice to be able to make a bunch of changes on different branches and then commit it all in one hit. If that's possible, then well and good. If it's not, that's a pity. 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] PostgreSQL Developer meeting minutes up
* Andrew Dunstan and...@dunslane.net [090605 14:41]: The whole point is that we want something better *that suits our work patterns*. Almost all the backpatching that gets done is by the committers. So we have a bunch of concerns that are not relevant to that vast majority of developers. In particular, it would be nice to be able to make a bunch of changes on different branches and then commit it all in one hit. If that's possible, then well and good. If it's not, that's a pity. My only concern is that I am seeing 2 requirements emerge: 1) Everything has to work as it currently does with CVS 2) We want better information about how patches relate for possible future stuff Unfortunately, those 2 requirements are conflicting... If you (not anyone personally, but the more general PostgreSQL committer) want the repository to properly track the fixes and show their relationship, and extra through all the branches than you really do want the branch-to-fix and merge the fix forward into all your STABLE/master branches, like the daggy type thing mentioned elsewhere... But notice, that is *very* different from the current work patterns based on the CVS model where everything is completely independent (save the commit message), and it's a huge change to the way developers work. If you want to stay with the current CVS style, then you aren't going to get any closer than commit messages matching (or possibly a reference to another commit as an extra line) that we currently have with CVS. My suggestion is to keep it simple. Just work independently, like you currently do. You don't want every committer to have to completely learn the advanced features of a new tool just to use it... You can use it as you use the less feature-full tool as you learn all the features... But as people start to use the new tool, and start to use it's more advanced features, then it's natural that their results will start to be reflected the main repository. But insisting that people currently comfortable and proficient in the current work patterns *have* to learn completely new ones for a flag-day type switch and start using them immediately is going to: * Piss them off * Create great ill-will against the tool And neither of those will be the fault of the tool itself, but of the way a new process was forced in conjunction with a new tool... I don't want to see the PG project trying to *force* a radical change in the way the development/branches currently work at the same time as a switch to git. Replace the tool, and allow the current processes and work-flows to gradually improve. The process and work-flow improvements will be an iterative and collaborative process, just like the actual code improvements, where huge radical patches are generally frowned upon. I've used git for a long time, on many different projects. I do know how radically it *can* change the process, and how much more efficient and natural the improved processes can be. But the change is not an overnight change. And it's not going to happen unless the people needing to change *see* it's benefits. And that's going to take time and experience with the new tool... Anyways, I said previously that I was over with this thread, but now I mean it ;-) If someone want specific git information or help, I'm available. a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
[HACKERS] pg_migrator issue with contrib
I've been kicking the tires on this a bit, and I've found an issue when dealing with contrib/ (specifically dblink, although I haven't looked around anymore). dblink_current_query() is not in the 8.4 version - when I run pg_migrator on an 8.3 cluster that has dblink installed, I get the following: Restoring database schema psql:/home/postgres/pg_migrator_dump_db.sql:271: ERROR: could not find function dblink_current_query in file /opt/dbs/pgsql84-beta2/lib/dblink.so There were problems executing /opt/dbs/pgsql84-beta2/bin/psql --set ON_ERROR_STOP=on --port 5432 -f /home/postgres/pg_migrator_dump_db.sql --dbname template1 /dev/null pg_migrator exits leaving me with a corrupted 8.3 instance. At the very least, a mention in the documentation of incompatible contrib module(s) would be nice. Even better would be a sanity check added to prevent this. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- 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_migrator issue with contrib
Brad Nicholson wrote: I've been kicking the tires on this a bit, and I've found an issue when dealing with contrib/ (specifically dblink, although I haven't looked around anymore). dblink_current_query() is not in the 8.4 version - when I run pg_migrator on an 8.3 cluster that has dblink installed, I get the following: Restoring database schema psql:/home/postgres/pg_migrator_dump_db.sql:271: ERROR: could not find function dblink_current_query in file /opt/dbs/pgsql84-beta2/lib/dblink.so There were problems executing /opt/dbs/pgsql84-beta2/bin/psql --set ON_ERROR_STOP=on --port 5432 -f /home/postgres/pg_migrator_dump_db.sql --dbname template1 /dev/null Yep, pg_migrator will exit on any restore error. This really relates to the problem of how we handle /contrib schema migration from one release to the other. Good thing you posted to hackers because that is really the group that can address this. pg_migrator is really just restoring the schema that pg_dump is producing. pg_migrator exits leaving me with a corrupted 8.3 instance. When you say corrupted, I assume you mean you have remove the _old suffixes to restart your 8.3 instance, right? I hope that is the only corruption issue --- please confirm. At the very least, a mention in the documentation of incompatible contrib module(s) would be nice. Even better would be a sanity check added to prevent this. OK, I am looking to the hackers group for recommentations on this. I wonder if I should recommend uninstalling /contrib modules before the upgrade, but that is not possible for custom data types that have columns already defined in the old cluster. -- Bruce Momjian br...@momjian.ushttp://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_migrator issue with contrib
Bruce Momjian wrote: At the very least, a mention in the documentation of incompatible contrib module(s) would be nice. Even better would be a sanity check added to prevent this. OK, I am looking to the hackers group for recommentations on this. I wonder if I should recommend uninstalling /contrib modules before the upgrade, but that is not possible for custom data types that have columns already defined in the old cluster. There is no nice answer to this. It goes way beyond data types: you could be using the module stuff in indexes, functions, views etc. You can't just drop the stuff. The best I have been able to do in similar cases is to install the updated module in the database before restoring, and ignore any restoration errors about foo already exists or foo not found in .so file. Not sure how well that translates to pg_migrator, though. 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] pg_migrator issue with contrib
On Fri, 2009-06-05 at 15:50 -0400, Bruce Momjian wrote: Brad Nicholson wrote: I've been kicking the tires on this a bit, and I've found an issue when dealing with contrib/ (specifically dblink, although I haven't looked around anymore). dblink_current_query() is not in the 8.4 version - when I run pg_migrator on an 8.3 cluster that has dblink installed, I get the following: Restoring database schema psql:/home/postgres/pg_migrator_dump_db.sql:271: ERROR: could not find function dblink_current_query in file /opt/dbs/pgsql84-beta2/lib/dblink.so There were problems executing /opt/dbs/pgsql84-beta2/bin/psql --set ON_ERROR_STOP=on --port 5432 -f /home/postgres/pg_migrator_dump_db.sql --dbname template1 /dev/null Yep, pg_migrator will exit on any restore error. This really relates to the problem of how we handle /contrib schema migration from one release to the other. Good thing you posted to hackers because that is really the group that can address this. pg_migrator is really just restoring the schema that pg_dump is producing. pg_migrator exits leaving me with a corrupted 8.3 instance. When you say corrupted, I assume you mean you have remove the _old suffixes to restart your 8.3 instance, right? I hope that is the only corruption issue --- please confirm. Unfortunately no - when I try and start the old version, I get: pg_ctl -D pgsql83/ start postgres: could not find the database system Expected to find it in the directory /opt/DATA/pgsql83, but could not open file /opt/DATA/pgsql83/global/pg_control: No such file or directory I am specifying both new and old paths to pg_migrator, as I don't have my data directories in standard locations. Test case is pretty straight forward. Create an 8.3 instance Add dblink follow through the pg_migrator upgrade path try and start your 8.3 instance after the failure -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- 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_migrator issue with contrib
Bruce Momjian br...@momjian.us writes: Brad Nicholson wrote: At the very least, a mention in the documentation of incompatible contrib module(s) would be nice. Even better would be a sanity check added to prevent this. OK, I am looking to the hackers group for recommentations on this. One thing I was going to suggest is that pg_migrator check that all the .so's in the old installation also exist in the new one. However that could be overkill since you don't know for sure if the old .so is referenced anywhere in the database. As for the specific problem at hand, it might've been a mistake to replace dblink_current_query() with a SQL function instead of changing the internal implementation of the C function. We could still fix 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] pg_migrator issue with contrib
Brad Nicholson wrote: When you say corrupted, I assume you mean you have remove the _old suffixes to restart your 8.3 instance, right? I hope that is the only corruption issue --- please confirm. Unfortunately no - when I try and start the old version, I get: pg_ctl -D pgsql83/ start postgres: could not find the database system Expected to find it in the directory /opt/DATA/pgsql83, but could not open file /opt/DATA/pgsql83/global/pg_control: No such file or directory I am specifying both new and old paths to pg_migrator, as I don't have my data directories in standard locations. Test case is pretty straight forward. Create an 8.3 instance Add dblink follow through the pg_migrator upgrade path try and start your 8.3 instance after the failure Uh, I assume you read all of the INSTALL instructions, including the last item: 10. Reverting to old cluster ... -- If you ran pg_migrator _without_ --link or did not start the new server, the old cluster was not modified except that an .old suffix was appended to $PGDATA/global/pg_control and tablespaces directories. To reuse the old cluster, remove the tablespace directories created by the new cluster and remove the .old suffix from the old cluster tablespace directory names and $PGDATA/global/pg_control; then you can restart the old cluster. I just modified the arrow text to be clearer. The rename of pg_controldata is done to prevent accidental starting of the old cluster in case of migration success. -- Bruce Momjian br...@momjian.ushttp://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_migrator issue with contrib
Andrew Dunstan wrote: Bruce Momjian wrote: At the very least, a mention in the documentation of incompatible contrib module(s) would be nice. Even better would be a sanity check added to prevent this. OK, I am looking to the hackers group for recommentations on this. I wonder if I should recommend uninstalling /contrib modules before the upgrade, but that is not possible for custom data types that have columns already defined in the old cluster. There is no nice answer to this. It goes way beyond data types: you could be using the module stuff in indexes, functions, views etc. You can't just drop the stuff. The best I have been able to do in similar cases is to install the updated module in the database before restoring, and ignore any restoration errors about foo already exists or foo not found in .so file. Not sure how well that translates to pg_migrator, though. I suspected this answer but figured I would get a definative answer rather than guessing. Based on the way pg_migrator works I am going to suggest that if /contrib restore generates an error that they uninstall the /contrib from the old cluster and retry. -- Bruce Momjian br...@momjian.ushttp://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_migrator issue with contrib
Bruce Momjian wrote: There is no nice answer to this. It goes way beyond data types: you could be using the module stuff in indexes, functions, views etc. You can't just drop the stuff. The best I have been able to do in similar cases is to install the updated module in the database before restoring, and ignore any restoration errors about foo already exists or foo not found in .so file. Not sure how well that translates to pg_migrator, though. I suspected this answer but figured I would get a definative answer rather than guessing. Based on the way pg_migrator works I am going to suggest that if /contrib restore generates an error that they uninstall the /contrib from the old cluster and retry. I have added the following paragraph to the pg_migrator INSTALL docs: If an error occurs while restoring the database schema, pg_migrator will exit and you will have to revert to the old cluster as outlined in step #10 below. To try pg_migrator again, you will need to modify the old cluster so the pg_migrator schema restore succeeds. If the problem is a /contrib module, you might need to uninstall the /contrib module from the old cluster and install it in the new cluster after migration. The only other idea I have would be to add a switch that allows schema restore errors to be ignored, but I would like to see if the above text is enough first. -- Bruce Momjian br...@momjian.ushttp://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_migrator issue with contrib
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Brad Nicholson wrote: At the very least, a mention in the documentation of incompatible contrib module(s) would be nice. Even better would be a sanity check added to prevent this. OK, I am looking to the hackers group for recommentations on this. One thing I was going to suggest is that pg_migrator check that all the .so's in the old installation also exist in the new one. However that could be overkill since you don't know for sure if the old .so is referenced anywhere in the database. Or in this case that the new *.so has the same functions. As for the specific problem at hand, it might've been a mistake to replace dblink_current_query() with a SQL function instead of changing the internal implementation of the C function. We could still fix that. I am afraid /contrib is going to be a mine field for this type of problem so I am going to recommend uninstaling the /contrib module if possible and retry the migration. That should work in this case. -- Bruce Momjian br...@momjian.ushttp://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
[HACKERS] blocking referencing system catalogs in 8.4 breaks my code
In testing my software on postgresql-8.4beta[12] I got ERROR: 42501: permission denied: pg_namespace is a system catalog LOCATION: ATAddForeignKeyConstraint, tablecmds.c:4599 from my code which has a column schema_name name NOT NULL REFERENCES pg_namespace ON DELETE CASCADE this despite PostgreSQL happily accepting: GRANT REFERENCES ON pg_namespace TO greg; I found the change in tablecmds.c and a mention of it in PostgreSQL Weekly News - May 17 2009, yet I don't understand why referencing a system table in this manner should be a problem - is it? I'm trying to have certain rows of my tables go away if certain schemas are dropped. Is there a preferred way to do this? If this regression is retained, it needs to be documented in the reference manual AND I think that an attempt to GRANT REFERENCES privileges to a system table should not be allowed. BTW, I'm loving most of what I see in 8.4, _Greg J. Greg Davidson -- 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] Documentation: GiST extension implementation
Hi, I've been working some more on this documentation: Le 20 mai 09 à 18:10, Dimitri Fontaine a écrit : - consistent signature update for 8.4 (recheck arg) - compress/decompress non void example This means the multi-column support part is still missing, as proper english review too. But I think it should not stop us to consider the patch either for 8.4 (documentation patch I believe are accepted this late in the game) or for 8.5, in which case I'll put it on the CommitFest page. Even without touching the multi-column aspect of GiST custom support, I think it improves this part of the documentation a lot. why care? Please consider it: it's not coming from nowhere. All examples are based on real working code, prefix.c (to be found on pgfoundry, served more than 70 millions GiST lookups already, is used in several telco companies) and gistproc.c, and I enjoyed Teodor's and Andrew Gierth's advices and comments. / Regards, -- dim gist.sgml.patch Description: Binary data -- 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] blocking referencing system catalogs in 8.4 breaks my code
J. Greg Davidson j...@well.com writes: from my code which has a column schema_name name NOT NULL REFERENCES pg_namespace ON DELETE CASCADE You can't do that; it never has worked and is not likely to work in the foreseeable future, because we don't support triggers on system catalogs. Perhaps the system failed to alert you to the fact before, but if it does so now, that's not a regression. 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_migrator issue with contrib
Hi, Ah, we need module/extension/package/plugin so badly... Le 5 juin 09 à 22:19, Bruce Momjian a écrit : I am afraid /contrib is going to be a mine field for this type of problem so I am going to recommend uninstaling the /contrib module if possible and retry the migration. That should work in this case. You can't seriously recommend that, I'm afraid. As Andrew (Dunstan) was saying up-thread, the faulty module (from contrib or pgfoundry) could hold some indexes (btree, gist, gin) and/ or data types in the cluster relations. So if you uninstall the module (drop type cascade, drop operator class, ...) you lose data. Some example modules that I can think of and are wildspread in the field, as far as I know, are ip4r (data type and indexes), orafce (functions, views, tables), and some less spread are prefix (data type and indexes) or temporal (period data type, indexes). Please do not recommend people to lose their precious data to be able to upgrade. You could tell them pg_migrator isn't an option in their case, though. At least we're left with a faster (multi-threaded) pg_restore :) Regards, -- dim -- 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_migrator issue with contrib
Bruce, Assuming a contrib module *hasn't* changed its API, does pg_migrator link against the 8.4 version of the module's .so, or the old 8.3 version? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] blocking referencing system catalogs in 8.4 breaks my code
Greg, this despite PostgreSQL happily accepting: GRANT REFERENCES ON pg_namespace TO greg; We should probably error out on this. I found the change in tablecmds.c and a mention of it in PostgreSQL Weekly News - May 17 2009, yet I don't understand why referencing a system table in this manner should be a problem - is it? System tables have special properties which can make modifications to them behave non-transactionally. So it's never safe to have a trigger on a system table. I'm frankly surprised that this worked for you before. I'm trying to have certain rows of my tables go away if certain schemas are dropped. Is there a preferred way to do this? I can't off the top of my head think of a preferred way. If this regression is retained, it needs to be documented in the reference manual AND I think that an attempt to GRANT REFERENCES privileges to a system table should not be allowed. I agree that GRANT REFERENCES should error. Do you have a suggestion where in the manual would be a good place to warn people that FKs against the system tables are a bad idea? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] 8.4b2 tsearch2 strange error
I wrote:
I poked around in the dump for awhile. I still can't reproduce the
failure from a standing start.
I've had a bit of success with reproducing the failure from scratch.
After loading the SQL dump that Tatsuo previously supplied into a UTF8,
C-locale database, generate a data-only dump of it, along the lines of
pg_dump -a ... ishii-data.sql
Then run this psql script in the database (the function is designed to
exercise every entry in the GIN index):
create table msgstats as
select * from ts_stat('select body_index from msginfo');
create language plpgsql;
create function testit () returns int as $$
declare r record;
ifail int := 0;
begin
for r in select * from msgstats loop
begin
perform count(*) from msginfo where plainto_tsquery(r.word) @@ body_index;
exception when others then
raise notice 'failed for %: %', r.word, sqlerrm;
ifail := ifail + 1;
end;
end loop;
return ifail;
end $$ language plpgsql;
delete from msginfo;
truncate msg_folderinfo;
\i ishii-data.sql
reset client_min_messages;
select testit();
The results are not very stable, but I fairly often get tuple offset out
of range errors, and my last try resulted in an Assert failure in
autovacuum. What I think is happening is that there's some inadequately
locked page-changing logic in there, and if you can get autovacuum to
happen on msginfo concurrently with the data reload step, it'll mess up.
Given that we've not seen reports of this on 8.3, I'll bet lunch that
the bug is somewhere in the fast insert code. I haven't found it
yet, though.
BTW, I also did more analysis of Tatsuo's binary dump using this
function. Of some 38000 distinct lexemes in the body_index column,
about 1500 trigger an error in testit(); the errors are either
tuple offset out of range or something like could not open segment 1
of relation base/16384/16497 (target block 955254869): No such file or
directory. There are about 440 distinct messages that show up,
suggesting that approximately that many pages of the index contain
corrupted TIDs. Manual examination of the index is consistent with
this, and also indicates that all of the corrupt pages are in the second
half of the table. That's not too hard to square with the
concurrent-update theory --- autovac might have already done about half
the index before a large data load started. However it means that
corruption is *very* probable if data insertion is happening
concurrently with a vacuum, else not such a large number of pages would
have gotten messed up.
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] Unique namespace per session for temp tables?
Hi all, Following the issue with temp tables and 2PC, we thought that each session in Postgres was supposed to generate a unique namespace for its temporary tables. But if we execute the following scenario: * First session*. BEGIN; CREATE TEMP TABLE foo (x int) ON COMMIT DROP; PREPARE TRANSACTION 't1'; *Disconnect*. *Wait* (at least until backend process for first session exits). *Reconnect new session*. CREATE TEMP TABLE foo (x int); -- blocks until t1 commits The issue we see is that a totally unrelated session (which should have a unique temporary schema), reuses the temporary schema that is locked away in the prepared transaction. Isn't that breaking the assumption that each session should have a unique schema for its temp tables? Thanks in advance for your insights. Emmanuel -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com
Re: [HACKERS] Unique namespace per session for temp tables?
Emmanuel Cecchet m...@asterdata.com writes: Following the issue with temp tables and 2PC, we thought that each session in Postgres was supposed to generate a unique namespace for its temporary tables. You made that up out of sheer wishful thinking. There is one pg_temp schema per backendid slot. 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_migrator issue with contrib
On Fri, Jun 5, 2009 at 6:11 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Some example modules that I can think of and are wildspread in the field, as far as I know, are ip4r (data type and indexes), orafce (functions, views, tables), and some less spread are prefix (data type and indexes) or temporal (period data type, indexes). And hstore... David Blewett -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] explain refactoring v3
Complete list of changes included in this patch:
- Remove unnecesary parameters nkeys, keycols, and qlabel from
show_sort_keys(), and completely unused parameter scanrelid from
show_scan_qual().
- Include the StringInfo buffer used as a staging area for EXPLAIN
output in the ExplainState object, rather than passing it around
separately.
- Refactor some duplicated logic out of
show_scan_qual()/show_upper_qual() into new function show_qual().
- Refactor various bits of duplicated logic from explain_outNode()
into new functions ExplainSubNodes(), ExplainMemberNodes(), and
ExplainScanTarget().
- For consistency with the naming of other functions in this module
and elsewhere, rename explain_outNode() to ExplainNode().
- Instead of having ExplainNode() indent all of the lines of its
output except the first, and making it the caller's responsibility to
properly indent the first line, make ExplainNode() indent the first
line too.
- Instead of having appendStringInfoSpaces() in ruleutils.c and
separate logic to do the same thing in multiple places in explain.c,
implement a single, efficient version of this logic in stringinfo.c.
I'm planning to submit one or more follow-on patches to implement
support for machine-readable EXPLAIN output, but it seems better to
have this part as a separate patch, since I think all of these changes
pretty much stand on their own, and this way it doesn't confuse the
issue of what is going on in the main patch. One of the important
parts of this tightening is that it substantially reduces the number
of different places that write directly to the output buffer, which is
good because essentially all of the remaining places will need to be
changed to support XML/JSON output.
...Robert
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***
*** 42,47 explain_get_index_name_hook_type explain_get_index_name_hook = NULL;
--- 42,48
typedef struct ExplainState
{
+ StringInfo str; /* output buffer */
/* options */
bool printTList; /* print plan targetlists */
bool printAnalyze; /* print actual times */
***
*** 56,78 static void ExplainOneQuery(Query *query, ExplainStmt *stmt,
static void report_triggers(ResultRelInfo *rInfo, bool show_relname,
StringInfo buf);
static double elapsed_time(instr_time *starttime);
! static void explain_outNode(StringInfo str,
! Plan *plan, PlanState *planstate,
! Plan *outer_plan,
! int indent, ExplainState *es);
! static void show_plan_tlist(Plan *plan,
! StringInfo str, int indent, ExplainState *es);
static void show_scan_qual(List *qual, const char *qlabel,
! int scanrelid, Plan *scan_plan, Plan *outer_plan,
! StringInfo str, int indent, ExplainState *es);
static void show_upper_qual(List *qual, const char *qlabel, Plan *plan,
! StringInfo str, int indent, ExplainState *es);
! static void show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
! const char *qlabel,
! StringInfo str, int indent, ExplainState *es);
! static void show_sort_info(SortState *sortstate,
! StringInfo str, int indent, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);
/*
--- 57,79
static void report_triggers(ResultRelInfo *rInfo, bool show_relname,
StringInfo buf);
static double elapsed_time(instr_time *starttime);
! static void ExplainNode(Plan *plan, PlanState *planstate,
! Plan *outer_plan, int indent, ExplainState *es);
! static void show_plan_tlist(Plan *plan, int indent, ExplainState *es);
! static void show_qual(List *qual, const char *qlabel, Plan *plan,
! Plan *outer_plan, int indent, bool useprefix, ExplainState *es);
static void show_scan_qual(List *qual, const char *qlabel,
! Plan *scan_plan, Plan *outer_plan,
! int indent, ExplainState *es);
static void show_upper_qual(List *qual, const char *qlabel, Plan *plan,
! int indent, ExplainState *es);
! static void show_sort_keys(Plan *sortplan, int indent, ExplainState *es);
! static void show_sort_info(SortState *sortstate, int indent, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);
+ static void ExplainScanTarget(Scan *plan, ExplainState *es);
+ static void ExplainMemberNodes(List *plans, PlanState **planstate,
+ Plan *outer_plan, int indent, ExplainState *es);
+ static void ExplainSubNodes(List *plans, int indent, ExplainState *es);
/*
***
*** 347,359 ExplainPrintPlan(StringInfo str, QueryDesc *queryDesc,
Assert(queryDesc-plannedstmt != NULL);
memset(es, 0, sizeof(es));
es.printTList = verbose;
es.printAnalyze = analyze;
es.pstmt = queryDesc-plannedstmt;
es.rtable = queryDesc-plannedstmt-rtable;
! explain_outNode(str,
! queryDesc-plannedstmt-planTree, queryDesc-planstate,
NULL, 0, es);
}
--- 348,360
Assert(queryDesc-plannedstmt != NULL);
memset(es, 0, sizeof(es));
+ es.str =
Re: [HACKERS] pg_migrator issue with contrib
Josh Berkus wrote: Bruce, Assuming a contrib module *hasn't* changed its API, does pg_migrator link against the 8.4 version of the module's .so, or the old 8.3 version? 8.4 version, or whatever is in the 8.4 lib, which should be 8.4. -- Bruce Momjian br...@momjian.ushttp://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_migrator issue with contrib
Dimitri Fontaine wrote: Hi, Ah, we need module/extension/package/plugin so badly... Le 5 juin 09 ? 22:19, Bruce Momjian a ?crit : I am afraid /contrib is going to be a mine field for this type of problem so I am going to recommend uninstaling the /contrib module if possible and retry the migration. That should work in this case. You can't seriously recommend that, I'm afraid. As Andrew (Dunstan) was saying up-thread, the faulty module (from contrib or pgfoundry) could hold some indexes (btree, gist, gin) and/ or data types in the cluster relations. So if you uninstall the module (drop type cascade, drop operator class, ...) you lose data. Some example modules that I can think of and are wildspread in the field, as far as I know, are ip4r (data type and indexes), orafce (functions, views, tables), and some less spread are prefix (data type and indexes) or temporal (period data type, indexes). Please do not recommend people to lose their precious data to be able to upgrade. You could tell them pg_migrator isn't an option in their case, though. At least we're left with a faster (multi-threaded) pg_restore :) Very good point, and something I had not considered. I tried uninstalling hstore and it gladly dropped any hstore columns! I have updated the INSTALL instructions: If an error occurs while restoring the database schema, pg_migrator will exit and you will have to revert to the old cluster as outlined in step #10 below. To try pg_migrator again, you will need to modify the old cluster so the pg_migrator schema restore succeeds. If the problem is a /contrib module, you might need to uninstall the /contrib module from the old cluster and install it in the new cluster after the migration, assuming the module is not being used to store user data. -- Bruce Momjian br...@momjian.ushttp://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] 8.4b2 tsearch2 strange error
I wrote: ... What I think is happening is that there's some inadequately locked page-changing logic in there, and if you can get autovacuum to happen on msginfo concurrently with the data reload step, it'll mess up. Given that we've not seen reports of this on 8.3, I'll bet lunch that the bug is somewhere in the fast insert code. I haven't found it yet, though. Well, I found at least part of the problem: http://archives.postgresql.org/message-id/20090606023940.bd4b8753...@cvs.postgresql.org This is in perfectly sequential code. The reason it has nondeterministic effects is that (so far as I can tell) the only non-crash case where there would be duplicate TIDs to eliminate is if two backends are concurrently flushing an index's pending-inserts list. The code is designed to let that happen and suppress the duplicates at the very end of the process, in addItemPointersToTuple(); but the duplicate-removal logic was broken and allowed extra garbage TIDs to creep in. So at least in the case I'm testing, this happens when autovacuum fires on the table concurrently with a large insertion. Please update to CVS HEAD, reindex that index, and then see if you see any more strange behavior. I'm not entirely convinced that this is the only problem ... 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] 8.4b2 tsearch2 strange error
Well, I found at least part of the problem: http://archives.postgresql.org/message-id/20090606023940.bd4b8753...@cvs.postgresql.org This is in perfectly sequential code. The reason it has nondeterministic effects is that (so far as I can tell) the only non-crash case where there would be duplicate TIDs to eliminate is if two backends are concurrently flushing an index's pending-inserts list. The code is designed to let that happen and suppress the duplicates at the very end of the process, in addItemPointersToTuple(); but the duplicate-removal logic was broken and allowed extra garbage TIDs to creep in. So at least in the case I'm testing, this happens when autovacuum fires on the table concurrently with a large insertion. Please update to CVS HEAD, reindex that index, and then see if you see any more strange behavior. I'm not entirely convinced that this is the only problem ... Thanks for investigating the problem. Using CVS HEAD and reindexing has solved the problems I reported. On Monday I will ask my engineers try the CVS HEAD and do more operations to see if any strange thing happen... -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] blocking referencing system catalogs in 8.4 breaks my code
Hi Josh, I think that any special features common to all on system tables would go nicely near the top of 44.1 System Catalogs Overview. That plus a specific error message, not just a PERMISSION DENIED, so that someone like me can put the error message in the PostgreSQL.org search box to find relevant documentation. If referencing system tables was always unreliable, I'm glad that it is now forbidden! I've now worked around the problem by creating my own schemas table along with functions create_schema and drop_schema which insert and delete a referenceable row in my own table as they create and drop a system schema. I've been delighted to see a number of irregularities and exceptions disappear as PostgreSQL evolves, leaving the system simpler as well as better. Perhaps at some point someone more knowledgeable than me will see a way to eliminate this limitation. Information wants to be referenceable. Thanks for the help, _Greg On Fri, 2009-06-05 at 15:34 -0700, Josh Berkus wrote: Greg, this despite PostgreSQL happily accepting: GRANT REFERENCES ON pg_namespace TO greg; We should probably error out on this. I found the change in tablecmds.c and a mention of it in PostgreSQL Weekly News - May 17 2009, yet I don't understand why referencing a system table in this manner should be a problem - is it? System tables have special properties which can make modifications to them behave non-transactionally. So it's never safe to have a trigger on a system table. I'm frankly surprised that this worked for you before. I'm trying to have certain rows of my tables go away if certain schemas are dropped. Is there a preferred way to do this? I can't off the top of my head think of a preferred way. If this regression is retained, it needs to be documented in the reference manual AND I think that an attempt to GRANT REFERENCES privileges to a system table should not be allowed. I agree that GRANT REFERENCES should error. Do you have a suggestion where in the manual would be a good place to warn people that FKs against the system tables are a bad idea? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
