Re: [HACKERS] Streaming replication and unfit messages
On Thursday 18 February 2010 06:17:06 Fujii Masao wrote: [2460]: LOG: could not receive data from client: No connection could be made because the target machine actively refused it. [2460]: FATAL: invalid standby closing message type 4 [2460]: LOG: could not send data to client: No connection could be made because the target machine actively refused it. Also the walsender wrongly tries to send the FATAL message to the standby even though the connection has already been closed, and then gets the following LOG message after the FATAL one. This FATAL message is suitable, but output of the LOG message looks messy, too. We should use COMMERROR instead of FATAL and then just call proc_exit() in order to prevent a message from being sent? Or hope for my idle query cancellation prelim. patch to get applied so you can do ereport(FATAL | LOG_NO_CLIENT, ...) ;-) Andres -- 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::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 17/02/10 18:30, David E. Wheeler wrote: On Feb 17, 2010, at 4:28 AM, Tim Bunce wrote: Umm, perhaps F-funcname(@args), or PG-funcname(@args), or ... ? Anyone got any better suggestions? PG is good. Or maybe DB? It's a module whose only use is embedded in a DB called PG - not sure those carry any extra info. It also treads on the toes of PG-not_a_function should such a beast be needed. I like F-funcname or FN-funcname myself. -- Richard Huxton Archonet Ltd -- 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] codlin_month is up and complain - PL/Python crash
Dne 17.02.10 18:39, Peter Eisentraut napsal(a): On ons, 2010-02-17 at 11:26 -0500, Tom Lane wrote: But the behavior gcc appears to exhibit is that it won't warn about variables that are only assigned once before the PG_TRY is entered, and that seems reasonable to me since such a variable ought to have the correct value either way. FWIW, this is a Sun Studio build that is complaining here. Yes It is SS12. I add volatile keyword and problem disappears. The code difference is following: PLy_spi_execute+0x742: 83 ec 0c subl $0xc,%esp PLy_spi_execute+0x745: ff b5 b8 f9 ff ff pushl 0xf9b8(%ebp) PLy_spi_execute+0x74b: e8 fc ff ff ff call MemoryContextSwitch PLy_spi_execute+0x742: 8b 85 cc f9 ff ff movl 0xf9cc(%ebp),%eax PLy_spi_execute+0x748: 83 ec 0c subl $0xc,%esp PLy_spi_execute+0x74b: 50 pushl %eax PLy_spi_execute+0x74c: e8 fc ff ff ff call MemoryContextSwitch Good to mention that SS inline PLy_spi_execute_query inside PLy_spi_execute(), because it is only one caller. 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] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Wed, Feb 17, 2010 at 10:30:03AM -0800, David E. Wheeler wrote: On Feb 17, 2010, at 4:28 AM, Tim Bunce wrote: Yes, but if it's a variadic function, I suspect that it won't often be called with the same number of args. So you'd potentially end up caching a lot of extra stuff that would never be used again. Potentially. Patches welcome! GitHub. ;-P http://github.com/timbunce/posgtresql-plperl-call Umm, perhaps F-funcname(@args), or PG-funcname(@args), or ... ? Anyone got any better suggestions? PG is good. Or maybe DB? On Thu, Feb 18, 2010 at 08:26:51AM +, Richard Huxton wrote: It's a module whose only use is embedded in a DB called PG - not sure those carry any extra info. It also treads on the toes of PG-not_a_function should such a beast be needed. I like F-funcname or FN-funcname myself. Thanks. I quite like FN. Anybody else want to express an opinion on the color if this bikeshed before I repaint it? Tim. -- 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] A thought: should we run pgindent now?
2010/2/18 Tom Lane t...@sss.pgh.pa.us: In connection with the recent discussion of changing SearchSysCache call format, Robert espoused the view that right now is the time when there are a minimal number of outstanding patches that would suffer merge problems from an invasive change. That seems correct to me --- although ideally everyone should be thinking beta test for the next few months, we all know there will be some development going on in people's private trees. Which leads me to the thought that rather than postponing running pgindent until late beta, maybe we should run it *now*, and get the bulk of its work done for the new code in 9.0. Then people would have a solid base to patch against, rather than having to expect a major merge hassle at the end of beta. We'd probably still want to run pgindent again at the traditional point in the cycle, but if we did one now then the final run would only be fixing sloppiness in beta-period fixes, and it should make relatively few changes. I have a personal interest in this because I'm hoping to spend time over the next few weeks reading all of the HS/SR code in detail, and it will be nicer to look at if it's formatted to project standards; which quite a lot of it is not at the moment. Comments? I think it's a good idea in general. There are of course people out there with patches *already* that will have problems with this, but they'll have the problem eventually anyway. The only real stopper there is if someone (Simon would be the most likelyi I guess?) has a big fixup change queued up or so - but if someone does, we can just postpone until right after that one... The followup question is of course, what do we do with fixup patches that land *after* this? Do we run pgindent once more later in the cycle? That should be a fairly small run in that case, so it might be worth doing it that way? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Streaming replication and unfit messages
Fujii Masao wrote: Hi, When the replication connection is closed unexpectedly, walsender might emit the following unfit messages. IOW, the loss of the connection might be wrongly regarded as an arrival of invalid message by the walsender. This looks messy. We should get rid of that unfit FATAL message, emit a COMMERROR message and just call proc_exit() when the loss of the connection is found? [2460]: LOG: could not receive data from client: No connection could be made because the target machine actively refused it. [2460]: FATAL: invalid standby closing message type 4 [2460]: LOG: could not send data to client: No connection could be made because the target machine actively refused it. Also the walsender wrongly tries to send the FATAL message to the standby even though the connection has already been closed, and then gets the following LOG message after the FATAL one. This FATAL message is suitable, but output of the LOG message looks messy, too. We should use COMMERROR instead of FATAL and then just call proc_exit() in order to prevent a message from being sent? [12586] FATAL: unexpected EOF on standby connection [12586] LOG: could not send data to client: Broken pipe The attached patch fixes those unfit messages. Actually the pg_getbyte_if_available() function is a bit confused. The comment above it claims that it returns 0 if no data is available without blocking, but it actually returns -1 with errno==EWOULDBLOCK. That stems from confusion in secure_read(); with SSL it returns 0 if it would block, but without SSL it returns -1 and EWOULDBLOCK. We should fix that so that secure_read() behaves consistently and so that pq_getbyte_if_available() behaves like e.g pq_getbytes(). Patch attached for that. pq_getbyte_if_available() now reports any errors with COMMERROR level, and returns EOF if the connection is closed cleanly. If no data is available without blocking it now really returns 0 as the comment said. Walsender reports any unexpected EOF to the log at COMMERROR level, similar to what normal backends do. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ? aaa.patch ? postgres ? access/common/.deps ? access/gin/.deps ? access/gist/.deps ? access/hash/.deps ? access/heap/.deps ? access/index/.deps ? access/nbtree/.deps ? access/transam/.deps ? bootstrap/.deps ? catalog/.deps ? commands/.deps ? executor/.deps ? foreign/.deps ? foreign/dummy/.deps ? foreign/postgresql/.deps ? lib/.deps ? libpq/.deps ? main/.deps ? nodes/.deps ? optimizer/geqo/.deps ? optimizer/path/.deps ? optimizer/plan/.deps ? optimizer/prep/.deps ? optimizer/util/.deps ? parser/.deps ? po/af.mo ? po/cs.mo ? po/hr.mo ? po/hu.mo ? po/it.mo ? po/ko.mo ? po/nb.mo ? po/nl.mo ? po/pl.mo ? po/ro.mo ? po/ru.mo ? po/sk.mo ? po/sl.mo ? po/sv.mo ? po/zh_CN.mo ? po/zh_TW.mo ? port/.deps ? postmaster/.deps ? regex/.deps ? replication/.deps ? replication/libpqwalreceiver/.deps ? rewrite/.deps ? snowball/.deps ? snowball/snowball_create.sql ? storage/buffer/.deps ? storage/file/.deps ? storage/freespace/.deps ? storage/ipc/.deps ? storage/large_object/.deps ? storage/lmgr/.deps ? storage/page/.deps ? storage/smgr/.deps ? tcop/.deps ? tsearch/.deps ? utils/.deps ? utils/probes.h ? utils/adt/.deps ? utils/cache/.deps ? utils/error/.deps ? utils/fmgr/.deps ? utils/hash/.deps ? utils/init/.deps ? utils/mb/.deps ? utils/mb/Unicode/BIG5.TXT ? utils/mb/Unicode/CP950.TXT ? utils/mb/conversion_procs/conversion_create.sql ? utils/mb/conversion_procs/ascii_and_mic/.deps ? utils/mb/conversion_procs/cyrillic_and_mic/.deps ? utils/mb/conversion_procs/euc2004_sjis2004/.deps ? utils/mb/conversion_procs/euc_cn_and_mic/.deps ? utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/.deps ? utils/mb/conversion_procs/euc_jp_and_sjis/.deps ? utils/mb/conversion_procs/euc_kr_and_mic/.deps ? utils/mb/conversion_procs/euc_tw_and_big5/.deps ? utils/mb/conversion_procs/latin2_and_win1250/.deps ? utils/mb/conversion_procs/latin_and_mic/.deps ? utils/mb/conversion_procs/utf8_and_ascii/.deps ? utils/mb/conversion_procs/utf8_and_big5/.deps ? utils/mb/conversion_procs/utf8_and_cyrillic/.deps ? utils/mb/conversion_procs/utf8_and_euc2004/.deps ? utils/mb/conversion_procs/utf8_and_euc_cn/.deps ? utils/mb/conversion_procs/utf8_and_euc_jis_2004/.deps ? utils/mb/conversion_procs/utf8_and_euc_jp/.deps ? utils/mb/conversion_procs/utf8_and_euc_kr/.deps ? utils/mb/conversion_procs/utf8_and_euc_tw/.deps ? utils/mb/conversion_procs/utf8_and_gb18030/.deps ? utils/mb/conversion_procs/utf8_and_gbk/.deps ? utils/mb/conversion_procs/utf8_and_iso8859/.deps ? utils/mb/conversion_procs/utf8_and_iso8859_1/.deps ? utils/mb/conversion_procs/utf8_and_johab/.deps ? utils/mb/conversion_procs/utf8_and_shift_jis_2004/.deps ? utils/mb/conversion_procs/utf8_and_sjis/.deps ? utils/mb/conversion_procs/utf8_and_sjis2004/.deps ? utils/mb/conversion_procs/utf8_and_uhc/.deps ? utils/mb/conversion_procs/utf8_and_win/.deps ? utils/misc/.deps ?
Re: [HACKERS] Streaming replication and unfit messages
2010/2/18 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Fujii Masao wrote: Hi, When the replication connection is closed unexpectedly, walsender might emit the following unfit messages. IOW, the loss of the connection might be wrongly regarded as an arrival of invalid message by the walsender. This looks messy. We should get rid of that unfit FATAL message, emit a COMMERROR message and just call proc_exit() when the loss of the connection is found? [2460]: LOG: could not receive data from client: No connection could be made because the target machine actively refused it. [2460]: FATAL: invalid standby closing message type 4 [2460]: LOG: could not send data to client: No connection could be made because the target machine actively refused it. Also the walsender wrongly tries to send the FATAL message to the standby even though the connection has already been closed, and then gets the following LOG message after the FATAL one. This FATAL message is suitable, but output of the LOG message looks messy, too. We should use COMMERROR instead of FATAL and then just call proc_exit() in order to prevent a message from being sent? [12586] FATAL: unexpected EOF on standby connection [12586] LOG: could not send data to client: Broken pipe The attached patch fixes those unfit messages. Actually the pg_getbyte_if_available() function is a bit confused. The comment above it claims that it returns 0 if no data is available without blocking, but it actually returns -1 with errno==EWOULDBLOCK. That stems from confusion in secure_read(); with SSL it returns 0 if it would block, but without SSL it returns -1 and EWOULDBLOCK. We should fix that so that secure_read() behaves consistently and so that pq_getbyte_if_available() behaves like e.g pq_getbytes(). Patch attached for that. pq_getbyte_if_available() now reports any errors with COMMERROR level, and returns EOF if the connection is closed cleanly. If no data is available without blocking it now really returns 0 as the comment said. Walsender reports any unexpected EOF to the log at COMMERROR level, similar to what normal backends do. This cannot possibly be correct: + if (errno == EAGAIN || EWOULDBLOCK || errno == EINTR) The middle argument is missing the errno== part. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Streaming replication on win32, still broken
2010/2/18 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Fujii Masao wrote: On Thu, Feb 18, 2010 at 5:28 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: If I'm reading the patch correctly, when wal_sync_method is 'open_sync', walreceiver nevertheless opens the WAL file without the O_DIRECT flag. When it later flushes it in XLogWalRcvFlush() by issue_xlog_fsync(), issue_xlog_fsync() will do nothing because it assumes the write() synced it already. So the data written isn't being forced to disk at all. When 'open_sync' is chosen, the WAL file is opened with O_SYNC or O_FSYNC flag. So I think that write() flushes the data to disk even if O_DIRECT flag is not given. Am I missing something? Ah, ok, you're right. Yes, I believe the difference is that with O_DIRECT it bypasses the cache completely. Without it, we still sync it out, but it also goes into the cache. O_DIRECT helps us when we're not going to read the file again, because we don't waste cache on it. If we are, which is the case here, it should be really bad for performance, since we actually have to do a physical read. Incidentally, that should also apply to general WAL when archive_mdoe is on. Do we optimize for that? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] A thought: should we run pgindent now?
Magnus Hagander wrote: 2010/2/18 Tom Lane t...@sss.pgh.pa.us: Which leads me to the thought that rather than postponing running pgindent until late beta, maybe we should run it *now*, and get the bulk of its work done for the new code in 9.0. Then people would have a solid base to patch against, rather than having to expect a major merge hassle at the end of beta. ... I think it's a good idea in general. Yep, +1 for running pgindent now. There are of course people out there with patches *already* that will have problems with this, but they'll have the problem eventually anyway. The only real stopper there is if someone (Simon would be the most likelyi I guess?) has a big fixup change queued up or so - but if someone does, we can just postpone until right after that one... It's worth noting that any patches that bit-rot because of pgindent run can be fixed with the following procedure: 1. check out the source tree just before pgindent. 2. Apply patch 3. Run pgindent 4. Diff against source tree just after pgindent. The followup question is of course, what do we do with fixup patches that land *after* this? Do we run pgindent once more later in the cycle? That should be a fairly small run in that case, so it might be worth doing it that way? Yeah, that was Tom's plan. -- 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] Streaming replication on win32, still broken
Magnus Hagander wrote: O_DIRECT helps us when we're not going to read the file again, because we don't waste cache on it. If we are, which is the case here, it should be really bad for performance, since we actually have to do a physical read. Incidentally, that should also apply to general WAL when archive_mdoe is on. Do we optimize for that? Hmm, no we don't. We do take that into account so that we refrain from issuing posix_fadvice(DONTNEED) if archive_mode is on, but we don't disable O_DIRECT. Maybe we should.. -- 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] Streaming replication and unfit messages
Magnus Hagander wrote: This cannot possibly be correct: + if (errno == EAGAIN || EWOULDBLOCK || errno == EINTR) The middle argument is missing the errno== part. Ahh, rats. Yeah it clearly is. Thanks. -- 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] A thought: should we run pgindent now?
2010/2/18 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Magnus Hagander wrote: There are of course people out there with patches *already* that will have problems with this, but they'll have the problem eventually anyway. The only real stopper there is if someone (Simon would be the most likelyi I guess?) has a big fixup change queued up or so - but if someone does, we can just postpone until right after that one... It's worth noting that any patches that bit-rot because of pgindent run can be fixed with the following procedure: 1. check out the source tree just before pgindent. 2. Apply patch 3. Run pgindent 4. Diff against source tree just after pgindent. Doesn't that require that all pgindent runs produce the same output? Which they generally don't due to different sets of typedefs and stuff? It's a solvable problem of course, but not quite as simple as you make it sound :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] A thought: should we run pgindent now?
Magnus Hagander wrote: 2010/2/18 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: It's worth noting that any patches that bit-rot because of pgindent run can be fixed with the following procedure: 1. check out the source tree just before pgindent. 2. Apply patch 3. Run pgindent 4. Diff against source tree just after pgindent. Doesn't that require that all pgindent runs produce the same output? Which they generally don't due to different sets of typedefs and stuff? It's a solvable problem of course, but not quite as simple as you make it sound :-) True. So everyone will have to send their patches to Bruce for bit-rot fixing ;-) -- 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] Streaming replication and unfit messages
On Thu, Feb 18, 2010 at 7:05 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Magnus Hagander wrote: This cannot possibly be correct: + if (errno == EAGAIN || EWOULDBLOCK || errno == EINTR) The middle argument is missing the errno== part. Ahh, rats. Yeah it clearly is. Thanks. Thanks for the patch! This seems nicer than mine. * The received byte is stored in *c. Returns 1 if a byte was read, 0 if ! * if no data was available, or EOF if trouble. Typo. 'if' is repeated. + ereport(COMMERROR, + (errcode_for_socket_access(), +errmsg(could not receive data from client: %m))); + return EOF; We should use r = EOF instead of return EOF as well as other cases? In WalSndHandshake(), when pq_getbyte() returns EOF, the COMMERROR message unexpected EOF on standby connection is emitted doubly. How about removing first COMMERROR message? r = pq_getbyte_if_available(firstchar); if (r 0) { ! /* unexpected error */ ereport(COMMERROR, ! (errcode(ERRCODE_PROTOCOL_VIOLATION), !errmsg(unexpected EOF on standby connection))); ! proc_exit(0); } Since pq_getbyte_if_available() returns EOF if trouble, r == EOF should be used instead of r 0? 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::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Tim Bunce tim.bu...@pobox.com writes: I like F-funcname or FN-funcname myself. Thanks. I quite like FN. Anybody else want to express an opinion on the color if this bikeshed before I repaint it? I wouldn't have, but since you ask... What about reusing the internal name, you seem to be emulating the fmgr in plperl. My proposal is thus FMGR. 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] Streaming replication on win32, still broken
On Thu, Feb 18, 2010 at 7:04 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Magnus Hagander wrote: O_DIRECT helps us when we're not going to read the file again, because we don't waste cache on it. If we are, which is the case here, it should be really bad for performance, since we actually have to do a physical read. Incidentally, that should also apply to general WAL when archive_mdoe is on. Do we optimize for that? Hmm, no we don't. We do take that into account so that we refrain from issuing posix_fadvice(DONTNEED) if archive_mode is on, but we don't disable O_DIRECT. Maybe we should.. Since the performance of WAL write is more important than that of WAL archiving in general, that optimization might offer little benefit. 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] Streaming replication and unfit messages
Fujii Masao wrote: * The received byte is stored in *c. Returns 1 if a byte was read, 0 if ! * if no data was available, or EOF if trouble. Typo. 'if' is repeated. + ereport(COMMERROR, + (errcode_for_socket_access(), + errmsg(could not receive data from client: %m))); + return EOF; We should use r = EOF instead of return EOF as well as other cases? Yep. In WalSndHandshake(), when pq_getbyte() returns EOF, the COMMERROR message unexpected EOF on standby connection is emitted doubly. How about removing first COMMERROR message? Yep. r = pq_getbyte_if_available(firstchar); if (r 0) { ! /* unexpected error */ ereport(COMMERROR, ! (errcode(ERRCODE_PROTOCOL_VIOLATION), ! errmsg(unexpected EOF on standby connection))); ! proc_exit(0); } Since pq_getbyte_if_available() returns EOF if trouble, r == EOF should be used instead of r 0? Yep. Committed with all those fixes. -- 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] Streaming replication on win32, still broken
2010/2/18 Fujii Masao masao.fu...@gmail.com: On Thu, Feb 18, 2010 at 7:04 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Magnus Hagander wrote: O_DIRECT helps us when we're not going to read the file again, because we don't waste cache on it. If we are, which is the case here, it should be really bad for performance, since we actually have to do a physical read. Incidentally, that should also apply to general WAL when archive_mdoe is on. Do we optimize for that? Hmm, no we don't. We do take that into account so that we refrain from issuing posix_fadvice(DONTNEED) if archive_mode is on, but we don't disable O_DIRECT. Maybe we should.. Since the performance of WAL write is more important than that of WAL archiving in general, that optimization might offer little benefit. Well, it's going to make the process that reads the WAL cause actual physical I/O... That'll take a chunk out of your total available I/O, which is likely to push you to the limit of your I/O capacity much quicker. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Fri, Feb 12, 2010 at 2:29 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So the only major feature we're missing is the ability to clean up old files. I found another missing feature in new file-based log shipping (i.e., standby_mode is enabled and 'cp' is used as restore_command). After the trigger file is found, the startup process with pg_standby tries to replay all of the WAL files in both pg_xlog and the archive. So, when the primary fails, if the latest WAL file in pg_xlog of the primary can be read, we can prevent the data loss by copying it to pg_xlog of the standby before creating the trigger file. On the other hand, the startup process with standby mode doesn't replay the WAL files in pg_xlog after the trigger file is found. So failover always causes the data loss even if the latest WAL file can be read from the primary. And if the latest WAL file is copied to the archive instead, it can be replayed but a PANIC error would happen because it's not filled. We should remove this restriction? 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] A thought: should we run pgindent now?
On Feb 17, 2010, at 11:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Which leads me to the thought that rather than postponing running pgindent until late beta, maybe we should run it *now*, and get the bulk of its work done for the new code in 9.0. Then people would have a solid base to patch against, rather than having to expect a major merge hassle at the end of beta. I was going to write an email proposing something along these lines, too - so +1 from me. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Introduce WAL records to log reuse of btree pages, allowing
Simon Riggs wrote: Introduce WAL records to log reuse of btree pages, allowing conflict resolution during Hot Standby. Page reuse interlock requested by Tom. Analysis and patch by me. There's still a theoretical possibility for this to happen: 1. A page is marked as deleted by VACUUM, setting xact field in the opaque 2. Master crashes. WAL replay replays the XLOG_BTREE_DELETE_PAGE record. It resets the xact field to FrozenTransactionId 3. The page is recycled. This writes a XLOG_BTREE_REUSE_PAGE record with FrozenTransactionId as latestRemovedXid When the standby replays that, it will call ResolveRecoveryConflictWithSnapshot with FrozenTransactionid, not the original xid that was used in the master when the page was deleted. A straightforward way to fix that is to WAL-log the real xid in the XLOG_BTREE_DELETE_PAGE records, instead of resetting it to FrozenTransactionId. -- 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] Streaming replication on win32, still broken
Magnus Hagander wrote: 2010/2/18 Fujii Masao masao.fu...@gmail.com: On Thu, Feb 18, 2010 at 7:04 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Magnus Hagander wrote: O_DIRECT helps us when we're not going to read the file again, because we don't waste cache on it. If we are, which is the case here, it should be really bad for performance, since we actually have to do a physical read. Incidentally, that should also apply to general WAL when archive_mdoe is on. Do we optimize for that? Hmm, no we don't. We do take that into account so that we refrain from issuing posix_fadvice(DONTNEED) if archive_mode is on, but we don't disable O_DIRECT. Maybe we should.. Since the performance of WAL write is more important than that of WAL archiving in general, that optimization might offer little benefit. Well, it's going to make the process that reads the WAL cause actual physical I/O... That'll take a chunk out of your total available I/O, which is likely to push you to the limit of your I/O capacity much quicker. Right, doesn't seem sensible, though it would be nice to see a benchmark on that. Here's a patch to disable O_DIRECT when archiving or streaming is enabled. This is pretty hard to test, so any extra eyeballs would be nice.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ? GNUmakefile ? a.patch ? b ? backend ? config.log ? config.status ? config.status.lineno ? configure.lineno ? gin-splay-1.patch ? gin-splay-2.patch ? gin-splay-3.patch ? include ? libpq ? log_unlogged_op_0115.patch ? md-1.c ? md-1.patch ? replication ? temp-file-resowner-2.patch ? contrib/pgbench/.deps ? contrib/pgbench/fsynctest ? contrib/pgbench/fsynctest.c ? contrib/pgbench/fsynctestfile ? contrib/pgbench/pgbench ? contrib/spi/.deps ? doc/src/sgml/HTML.index ? doc/src/sgml/bookindex.sgml ? doc/src/sgml/features-supported.sgml ? doc/src/sgml/features-unsupported.sgml ? doc/src/sgml/version.sgml ? src/Makefile.global ? src/backend/aaa.patch ? src/backend/postgres ? src/backend/access/common/.deps ? src/backend/access/gin/.deps ? src/backend/access/gist/.deps ? src/backend/access/hash/.deps ? src/backend/access/heap/.deps ? src/backend/access/index/.deps ? src/backend/access/nbtree/.deps ? src/backend/access/transam/.deps ? src/backend/bootstrap/.deps ? src/backend/catalog/.deps ? src/backend/commands/.deps ? src/backend/executor/.deps ? src/backend/foreign/.deps ? src/backend/foreign/dummy/.deps ? src/backend/foreign/postgresql/.deps ? src/backend/lib/.deps ? src/backend/libpq/.deps ? src/backend/main/.deps ? src/backend/nodes/.deps ? src/backend/optimizer/geqo/.deps ? src/backend/optimizer/path/.deps ? src/backend/optimizer/plan/.deps ? src/backend/optimizer/prep/.deps ? src/backend/optimizer/util/.deps ? src/backend/parser/.deps ? src/backend/po/af.mo ? src/backend/po/cs.mo ? src/backend/po/hr.mo ? src/backend/po/hu.mo ? src/backend/po/it.mo ? src/backend/po/ko.mo ? src/backend/po/nb.mo ? src/backend/po/nl.mo ? src/backend/po/pl.mo ? src/backend/po/ro.mo ? src/backend/po/ru.mo ? src/backend/po/sk.mo ? src/backend/po/sl.mo ? src/backend/po/sv.mo ? src/backend/po/zh_CN.mo ? src/backend/po/zh_TW.mo ? src/backend/port/.deps ? src/backend/postmaster/.deps ? src/backend/regex/.deps ? src/backend/replication/.deps ? src/backend/replication/libpqwalreceiver/.deps ? src/backend/rewrite/.deps ? src/backend/snowball/.deps ? src/backend/snowball/snowball_create.sql ? src/backend/storage/buffer/.deps ? src/backend/storage/file/.deps ? src/backend/storage/freespace/.deps ? src/backend/storage/ipc/.deps ? src/backend/storage/large_object/.deps ? src/backend/storage/lmgr/.deps ? src/backend/storage/page/.deps ? src/backend/storage/smgr/.deps ? src/backend/tcop/.deps ? src/backend/tsearch/.deps ? src/backend/utils/.deps ? src/backend/utils/probes.h ? src/backend/utils/adt/.deps ? src/backend/utils/cache/.deps ? src/backend/utils/error/.deps ? src/backend/utils/fmgr/.deps ? src/backend/utils/hash/.deps ? src/backend/utils/init/.deps ? src/backend/utils/mb/.deps ? src/backend/utils/mb/Unicode/BIG5.TXT ? src/backend/utils/mb/Unicode/CP950.TXT ? src/backend/utils/mb/conversion_procs/conversion_create.sql ? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps ? src/backend/utils/mb/conversion_procs/euc2004_sjis2004/.deps ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps ? src/backend/utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/.deps ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps ? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps ?
Re: [HACKERS] remove contrib/xml2
M Z escribió: Hi Alvaro, I followed your instruction but put the patch on 8.4.2 as I found it crashes. It looks like the server still crash in the same way. Can you and anyone give me some ideas how to fix this bug? See src/backend/utils/adt/xml.c. Note the comment at the top: /* * Notes on memory management: * * Sometimes libxml allocates global structures in the hope that it can reuse * them later on. This makes it impractical to change the xmlMemSetup * functions on-the-fly; that is likely to lead to trying to pfree() chunks * allocated with malloc() or vice versa. Since libxml might be used by * loadable modules, eg libperl, our only safe choices are to change the * functions at postmaster/backend launch or not at all. Since we'd rather * not activate libxml in sessions that might never use it, the latter choice * is the preferred one. However, for debugging purposes it can be awfully * handy to constrain libxml's allocations to be done in a specific palloc * context, where they're easy to track. Therefore there is code here that * can be enabled in debug builds to redirect libxml's allocations into a * special context LibxmlContext. It's not recommended to turn this on in * a production build because of the possibility of bad interactions with * external modules. */ /* #define USE_LIBXMLCONTEXT */ Then if you look at xpath.c in contrib/xml2 you notice that it's doing exactly the thing that the core module says it's unreliable: using palloc and friends in xmlMemSetup. So to fix the bug what's needed is that the xmlMemSetup call in contrib is removed altogether, and all memory is tracked and released by hand. It's rather tedious, and it's also difficult to plug all resulting memory leaks. But AFAIUI doing that would fix (some of?) the crashes. Not sure if your crash is in this category. -- 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] A thought: should we run pgindent now?
Magnus Hagander wrote: Doesn't that require that all pgindent runs produce the same output? Which they generally don't due to different sets of typedefs and stuff? It's a solvable problem of course, but not quite as simple as you make it sound :-) The typedef file emitted by the buildfarm is supposed to be rather static, no? -- 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] A thought: should we run pgindent now?
Tom Lane wrote: Which leads me to the thought that rather than postponing running pgindent until late beta, maybe we should run it *now*, and get the bulk of its work done for the new code in 9.0. Then people would have a solid base to patch against, rather than having to expect a major merge hassle at the end of beta. (BTW, by now I don't mean *today* --- seems better to wait till alpha4 has been wrapped. But as soon as that's done.) I have a TODO on fixing some of the typedef finding. But I can generate an up to date version of the list Bruce last used in a day or two, and then get this better whacked into shape for another run at the more traditional time. 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] A thought: should we run pgindent now?
Andrew Dunstan wrote: Tom Lane wrote: Which leads me to the thought that rather than postponing running pgindent until late beta, maybe we should run it *now*, and get the bulk of its work done for the new code in 9.0. Then people would have a solid base to patch against, rather than having to expect a major merge hassle at the end of beta. (BTW, by now I don't mean *today* --- seems better to wait till alpha4 has been wrapped. But as soon as that's done.) I have a TODO on fixing some of the typedef finding. But I can generate an up to date version of the list Bruce last used in a day or two, and then get this better whacked into shape for another run at the more traditional time. I am ready to run pgindent whenever requested. -- 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] A thought: should we run pgindent now?
Alvaro Herrera wrote: Magnus Hagander wrote: Doesn't that require that all pgindent runs produce the same output? Which they generally don't due to different sets of typedefs and stuff? It's a solvable problem of course, but not quite as simple as you make it sound :-) The typedef file emitted by the buildfarm is supposed to be rather static, no? Umm, static in what sense? Clearly if we add things to the code that can involve extra typedefs being found. The buildfarm's list is the union of all the typedefs found by the contributing members at the time they do their runs. 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] Avoiding bad prepared-statement plans.
On Tue, 16 Feb 2010 15:22:00 +0100, Greg Stark gsst...@mit.edu wrote: There's a second problem though. We don't actually know how long any given query is going to take to plan or execute. We could just remember how long it took to plan and execute last time or how long it took to plan last time and the average execution time since we cached that plan. Perhaps we should track the stddev of the execution plan, or the max execution time of the plan? Ie there are still unanswered questions about the precise heuristic to use but I bet we can come up with something reasonable. This could be an occasion to implement plan caching... Web 2.0 = AJAX means less need for heavy webpage reloads with (usually) lots of queries, and more small simple queries like selects returning 1 or a few rows every time the user clicks on something. See benchmark here : (PG 8.4.2, MYSQL 5.1.37) http://purity.bobfuck.net/posts/postgres/2010-02-Prep/ If prepared statements are used, MySQL is not faster for small, simple selects... However, when not using prepared statements, most of the postmaster CPU time is spent parsing planning. Problem with prepared statements is they're a chore to use in web apps, especially PHP, since after grabbing a connection from the pool, you don't know if it has prepared plans in it or not. The postmaster could keep a hash of already prepared plans, using the $-parameterized query as a hash key, and when it receives parse+bind message, look up in this cache and fetch plans for the query, avoiding planning entirely. This could be done by the connection pooler too, but it doesn't have the information to decide wether it's wise to cache a plan or not. Of course all the subtility is to determine if the plan is reusable with other parameters... - after planning and executing the query, only cache it if the plan time is a significant part of the query time (as said previously). - only simple queries should be automatically cached like this - perhaps some measure of plan volatility ? For the examples I give in the link above, it's quite easy at least in 2 of the cases : searching UNIQUE columns can't return more than 1 row, so volatility is zero. It only depends on the table size. -- 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] A thought: should we run pgindent now?
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Magnus Hagander wrote: Doesn't that require that all pgindent runs produce the same output? True. So everyone will have to send their patches to Bruce for bit-rot fixing ;-) I think Bruce ought to publish the specific typedef list he uses for each run (maybe commit it in src/tools/pgindent/). In principle, at least, that's the only variable that couldn't be reproduced by someone else. 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] A thought: should we run pgindent now?
Andrew Dunstan wrote: Alvaro Herrera wrote: Magnus Hagander wrote: Doesn't that require that all pgindent runs produce the same output? Which they generally don't due to different sets of typedefs and stuff? It's a solvable problem of course, but not quite as simple as you make it sound :-) The typedef file emitted by the buildfarm is supposed to be rather static, no? Umm, static in what sense? Clearly if we add things to the code that can involve extra typedefs being found. The buildfarm's list is the union of all the typedefs found by the contributing members at the time they do their runs. Yeah, but most typedefs are already there; I mean we're not likely to change even 5% of the current list. -- 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] Avoiding bad prepared-statement plans.
Pierre C li...@peufeu.com writes: Problem with prepared statements is they're a chore to use in web apps, especially PHP, since after grabbing a connection from the pool, you don't know if it has prepared plans in it or not. Have you met preprepare yet? http://preprepare.projects.postgresql.org/README.html http://packages.debian.org/source/sid/preprepare 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
[HACKERS] SR: pseudo replication database of the primary ...
I'm reading through the Wiki docs, and one thing isn't clear: 3. Set up connections and authentication so that the standby server can successfully connect to the pseudo replication database of the primary. I've searched Google, to see if its mentioned anywhere else, but nadda ... can't connect to it using psql, which makes it confusing as to why I'm configuring access to it via pg_hba.conf ... So, what am I missing that should be obvious to me ... ? Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- 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] remove contrib/xml2
Alvaro Herrera alvhe...@commandprompt.com writes: Then if you look at xpath.c in contrib/xml2 you notice that it's doing exactly the thing that the core module says it's unreliable: using palloc and friends in xmlMemSetup. So to fix the bug what's needed is that the xmlMemSetup call in contrib is removed altogether, and all memory is tracked and released by hand. It's rather tedious, and it's also difficult to plug all resulting memory leaks. But AFAIUI doing that would fix (some of?) the crashes. Not sure if your crash is in this category. FWIW, the core xml code seems to have been pretty stable since we gave up on trying to redirect libxml's memory allocations to palloc. So what you basically need to do to xpath.c is something like this: http://archives.postgresql.org/pgsql-committers/2009-05/msg00229.php 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] remove contrib/xml2
Tom Lane t...@sss.pgh.pa.us writes: FWIW, the core xml code seems to have been pretty stable since we gave up on trying to redirect libxml's memory allocations to palloc. So what you basically need to do to xpath.c is something like this: http://archives.postgresql.org/pgsql-committers/2009-05/msg00229.php Which translates to this git URL, for lazy browsers such as me: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f8059d7f8ae8bfac840fbda3c8efbc0f7c09b123 -- 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] Avoiding bad prepared-statement plans.
On Thu, 18 Feb 2010 16:09:42 +0100, Dimitri Fontaine dfonta...@hi-media.com wrote: Pierre C li...@peufeu.com writes: Problem with prepared statements is they're a chore to use in web apps, especially PHP, since after grabbing a connection from the pool, you don't know if it has prepared plans in it or not. Have you met preprepare yet? http://preprepare.projects.postgresql.org/README.html http://packages.debian.org/source/sid/preprepare Regards, Hey, this thing is nice. How hard would it be to put a hook in pg so that, instead of raising an error and cancelling the txn when EXECUTing a statement that is not prepared, it would call a user function (of the user's choice) which would, if possible, prepare said statement, or if not, raise the error ? -- 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] Avoiding bad prepared-statement plans.
Pierre C li...@peufeu.com writes: On Thu, 18 Feb 2010 16:09:42 +0100, Dimitri Fontaine dfonta...@hi-media.com wrote: http://preprepare.projects.postgresql.org/README.html http://packages.debian.org/source/sid/preprepare Hey, this thing is nice. Thanks :) How hard would it be to put a hook in pg so that, instead of raising an error and cancelling the txn when EXECUTing a statement that is not prepared, it would call a user function (of the user's choice) which would, if possible, prepare said statement, or if not, raise the error ? What about catching the error in the application and INSERT'ing into the current preprepare.relation table? The aim would be to do that in dev or in pre-prod environments, then copy the table content in production. Notice you can also SET preprepare.relation TO 'any.table_or_view', so that you can have more than one table for different applications or parts. I wonder how far you'd get using a VIEW instead and calling a user function there, but arranging preprepare to support a function instead is indeed possible. I'll try and see about it this evening, maybe tomorrow afternoon. 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
[HACKERS] Re: [COMMITTERS] pgsql: Introduce WAL records to log reuse of btree pages, allowing
On Thu, 2010-02-18 at 14:23 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Introduce WAL records to log reuse of btree pages, allowing conflict resolution during Hot Standby. Page reuse interlock requested by Tom. Analysis and patch by me. There's still a theoretical possibility for this to happen: 1. A page is marked as deleted by VACUUM, setting xact field in the opaque 2. Master crashes. WAL replay replays the XLOG_BTREE_DELETE_PAGE record. It resets the xact field to FrozenTransactionId 3. The page is recycled. This writes a XLOG_BTREE_REUSE_PAGE record with FrozenTransactionId as latestRemovedXid When the standby replays that, it will call ResolveRecoveryConflictWithSnapshot with FrozenTransactionid, not the original xid that was used in the master when the page was deleted. A straightforward way to fix that is to WAL-log the real xid in the XLOG_BTREE_DELETE_PAGE records, instead of resetting it to FrozenTransactionId. An even simpler way would be to reset the value to latestCompletedXid during btree_xlog_delete_page(). That touches less code. I doubt it will make much difference to conflict recovery, since if pages are being deleted then btree delete records are likely to be frequent and will have already killed long running queries. -- Simon Riggs www.2ndQuadrant.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::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 18, 2010, at 1:35 AM, Tim Bunce wrote: http://github.com/timbunce/posgtresql-plperl-call Thanks, forked. Thanks. I quite like FN. Anybody else want to express an opinion on the color if this bikeshed before I repaint it? I like PG because it lets you know that you're calling a function that's specific to the database. FN could mean anything. I wouldn't have, but since you ask... What about reusing the internal name, you seem to be emulating the fmgr in plperl. My proposal is thus FMGR. Meh. Too long. David -- 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] Listen / Notify - what to do when the queue is full
On Mon, 2010-02-15 at 15:00 -0500, Tom Lane wrote: Joachim Wieland j...@mcknight.de writes: We could probably fake this on the Hot Standby in the following way: We introduce a commit record for every notifying transaction and write it into the queue itself. So right before writing anything else, we write an entry which informs readers that the following records are not yet committed. Then we write the actual notifications and commit. In post-commit we return back to the commit record and flip its status. This doesn't seem likely to work --- it essentially makes commit non atomic. There has to be one and only one authoritative reference as to whether transaction X committed. I thought a bit more about this and don't really understand why we need an xid at all. When we discussed this before the role of a NOTIFY was to remind us to refresh a cache, not as a way of delivering a transactional payload. If the cache refresh use case is still the objective why does it matter whether we commit or not when we issue a NOTIFY? Surely, the rare case where we actually abort right at the end of the transaction will just cause an unnecessary cache refresh. I think that having HS slave sessions issue notifies is a fairly silly idea anyway. They can't write the database, so exactly what condition are they going to be notifying others about? Agreed What *would* be useful is for HS slaves to be able to listen for notify messages issued by writing sessions on the master. This patch gets rid of the need for LISTEN to change on-disk state, so in principle we can do it. The only bit we seem to lack is WAL transmission of the messages (plus of course synchronization in case a slave session is too slow about picking up messages). Definitely a 9.1 project at this point though. OK -- Simon Riggs www.2ndQuadrant.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] Listen / Notify - what to do when the queue is full
On 2/18/10 9:58 AM, Simon Riggs wrote: I thought a bit more about this and don't really understand why we need an xid at all. When we discussed this before the role of a NOTIFY was to remind us to refresh a cache, not as a way of delivering a transactional payload. If the cache refresh use case is still the objective why does it matter whether we commit or not when we issue a NOTIFY? Surely, the rare case where we actually abort right at the end of the transaction will just cause an unnecessary cache refresh. Actually, even for that use, it doesn't wash to have notifies being sent for transactions which have not yet committed. Think of cases (and I have two applications) where data is being pushed into an external non-transactional cache or queue (like Memcached, Redis or ApacheMQ) from PostgreSQL on the backend. If the transaction fails, it's important that the data not get pushed. I guess I'm not following why HS would be different from a single server in this regard, though? Mind you, this is all 9.1 discussion, no? --Josh Berkus -- 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] Listen / Notify - what to do when the queue is full
On Thu, Feb 18, 2010 at 12:58 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, 2010-02-15 at 15:00 -0500, Tom Lane wrote: Joachim Wieland j...@mcknight.de writes: We could probably fake this on the Hot Standby in the following way: We introduce a commit record for every notifying transaction and write it into the queue itself. So right before writing anything else, we write an entry which informs readers that the following records are not yet committed. Then we write the actual notifications and commit. In post-commit we return back to the commit record and flip its status. This doesn't seem likely to work --- it essentially makes commit non atomic. There has to be one and only one authoritative reference as to whether transaction X committed. I thought a bit more about this and don't really understand why we need an xid at all. When we discussed this before the role of a NOTIFY was to remind us to refresh a cache, not as a way of delivering a transactional payload. If the cache refresh use case is still the objective why does it matter whether we commit or not when we issue a NOTIFY? Surely, the rare case where we actually abort right at the end of the transaction will just cause an unnecessary cache refresh. notifications serve many more purposes than cache refreshes...it's a generic 'wake up and do something' to the client. For example, one of those things could be for the client to shut down. If the server errors out of the transaction that set up the client to shut down, you probably wouldn't want the client to shut down. I don't think that's a big deal really, but it conflicts with the old behavior. However, being able to send notifications immediately (not at end of transaction) would be exceptionally useful in some cases. This happens when the notifying backend is waiting on some sort of response from the notified client. If you could NOTIFY IMMEDIATELY, then you could ping the client and get the response in a single transaction without using dblink based hacks. merlin -- 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] Listen / Notify - what to do when the queue is full
Merlin Moncure mmonc...@gmail.com writes: On Thu, Feb 18, 2010 at 12:58 PM, Simon Riggs si...@2ndquadrant.com wrote: I thought a bit more about this and don't really understand why we need an xid at all. When we discussed this before the role of a NOTIFY was to remind us to refresh a cache, not as a way of delivering a transactional payload. If the cache refresh use case is still the objective why does it matter whether we commit or not when we issue a NOTIFY? Surely, the rare case where we actually abort right at the end of the transaction will just cause an unnecessary cache refresh. notifications serve many more purposes than cache refreshes...it's a generic 'wake up and do something' to the client. The point to my mind is that the previous implementation guaranteed that failed transactions would not send notifies. I don't think we can just drop that semantic consistency statement and not break applications. Also, as Josh notes, even for cache refresh uses it is *critical* that the notifies not be delivered to listeners till after the sender commits; else you have race conditions where the listeners look for changes before they can see them. So it's difficult to make it much simpler than this anyhow. 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] Re: [COMMITTERS] pgsql: Introduce WAL records to log reuse of btree pages, allowing
Simon Riggs si...@2ndquadrant.com writes: On Thu, 2010-02-18 at 14:23 +0200, Heikki Linnakangas wrote: A straightforward way to fix that is to WAL-log the real xid in the XLOG_BTREE_DELETE_PAGE records, instead of resetting it to FrozenTransactionId. An even simpler way would be to reset the value to latestCompletedXid during btree_xlog_delete_page(). That touches less code. I doubt it will make much difference to conflict recovery, since if pages are being deleted then btree delete records are likely to be frequent and will have already killed long running queries. I'm a bit concerned about XID wraparound if the value doesn't get reset to FrozenTransactionId. There's no guarantee the page will get reused promptly ... 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] Avoiding bad prepared-statement plans.
What about catching the error in the application and INSERT'ing into the current preprepare.relation table? The aim would be to do that in dev or in pre-prod environments, then copy the table content in production. Yep, but it's a bit awkward and time-consuming, and not quite suited to ORM-generated requests since you got to generate all the plan names, when the SQL query itself would be the most convenient unique identifier... A cool hack would be something like that : pg_execute( SELECT ..., arguments... ) By inserting a hook which calls a user-specified function on non-existing plan instead of raising an error, this could work. However, this wouldn't work as-is since the plan name must be = NAMEDATALEN, but you get the idea ;) -- 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] Listen/Notify payload and interfaces
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Is the payload decoded to utf8 when pg_enable_utf8 is true? No - there's a note in the code to look into that at some point. So stick to ASCII for the moment. I'm sure the great DBD::Pg utf-8 overhaul (where we get rid of the pg_enable_utf8 hack) will encompass payloads as well. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201002181600 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkt9qn4ACgkQvJuQZxSWSsiM0ACgnjJAfKJOfIFzHBvBmXI3j/ZT lEMAoIV6KwN186oOKqMYa6IQjYPHprzC =AwK+ -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
When accidentally running pg_dump from an 8.4.2 instance into a 9.0devel (cvs as of 2010.02.17) slave as below, this causes a PANIC. The slave can be restarted. localhost:55432 = 8.4.2 instance (ssh tunnel) /tmp:7575 = a 9.0devel standby time pg_dump -h localhost -p 55432 -t public.tab_jobs --clean --no-owner --no-privileges ms | psql -q -h /tmp -p 7575 -d replicas ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only PANIC: cannot make new WAL entries during recovery server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost real0m5.569s After re-starting the slave, its logfile shows this: [...] STATEMENT: ALTER TABLE ONLY tab_jobs ALTER COLUMN db SET STATISTICS 1; ERROR: transaction is read-only STATEMENT: CREATE SEQUENCE tab_jobs_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ERROR: transaction is read-only STATEMENT: ALTER SEQUENCE tab_jobs_id_seq OWNED BY tab_jobs.id; PANIC: cannot make new WAL entries during recovery STATEMENT: SELECT pg_catalog.setval('tab_jobs_id_seq', 31907, true); LOG: server process (PID 25753) was terminated by signal 6: Aborted LOG: terminating any other active server processes LOG: database system was interrupted while in recovery at log time 2010-02-18 16:38:39 CET HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. cp: cannot stat `/var/data1/pg_stuff/dump/hotslave/replication_archive/0001008800E7': No such file or directory LOG: entering standby mode LOG: redo starts at 88/E500E4E8 LOG: consistent recovery state reached at 89/2C80 LOG: database system is ready to accept read only connections [...] It doesn't happen with just any table (I tried that), so maybe it has some connection with this specific table, sequence, or sequence name. There is another (500 GB) replication connection running from a 9.0devel primary to this same 9.0devel slave; the slave seems to pick up that work OK after the restart. I want to mention that here, but I don't think it causes/influences this panic. And to be clear: I realize that one cannot expect a pg_dump at a slave to work: I only did this accidentally. Nevertheless, ISTM it shouldn't PANIC... hth, Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Folks, While hacking on PL/Parrot, I ran across an issue where when trying to load PL/pgsql, it's done unconditionally and fails. How do we fix pg_regress to be a little more subtle about this? 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 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Listen/Notify payload and interfaces
On Feb 18, 2010, at 1:01 PM, Greg Sabino Mullane wrote: Is the payload decoded to utf8 when pg_enable_utf8 is true? No - there's a note in the code to look into that at some point. So stick to ASCII for the moment. I'm sure the great DBD::Pg utf-8 overhaul (where we get rid of the pg_enable_utf8 hack) will encompass payloads as well. Is that immanent? Best, David -- 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] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
Erik Rijkers wrote: And to be clear: I realize that one cannot expect a pg_dump at a slave to work: Hmm, why not? -- 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] Listen/Notify payload and interfaces
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 No - there's a note in the code to look into that at some point. So stick to ASCII for the moment. I'm sure the great DBD::Pg utf-8 overhaul (where we get rid of the pg_enable_utf8 hack) will encompass payloads as well. Is that immanent? Barring sudden free time, funding, and/or a client request, no. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201002181711 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkt9u0AACgkQvJuQZxSWSsj/8wCcD6qwNvnRoQ+UJWzDuAIM1vh/ 7pQAn1yRdO1bKL1qLpX8OeggS4iCl+1j =sOJR -END PGP SIGNATURE- -- 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] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
On Thu, February 18, 2010 23:08, Alvaro Herrera wrote: Erik Rijkers wrote: And to be clear: I realize that one cannot expect a pg_dump at a slave to work: Hmm, why not? Because the slave is readonly. My case was: pg_dump $pg8.4.2 | psql $slave I ran this by accident. I suppose I should have written more clearly: one cannot expect throwing a pg_dump at a slave to work Erik Rijkers -- 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] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
On Thursday 18 February 2010 22:25:35 Erik Rijkers wrote: When accidentally running pg_dump from an 8.4.2 instance into a 9.0devel (cvs as of 2010.02.17) slave as below, this causes a PANIC. The slave can be restarted. localhost:55432 = 8.4.2 instance (ssh tunnel) /tmp:7575 = a 9.0devel standby time pg_dump -h localhost -p 55432 -t public.tab_jobs --clean --no-owner --no-privileges ms | psql -q -h /tmp -p 7575 -d replicas ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only ERROR: transaction is read-only PANIC: cannot make new WAL entries during recovery server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost real0m5.569s After re-starting the slave, its logfile shows this: [...] STATEMENT: ALTER TABLE ONLY tab_jobs ALTER COLUMN db SET STATISTICS 1; ERROR: transaction is read-only STATEMENT: CREATE SEQUENCE tab_jobs_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ERROR: transaction is read-only STATEMENT: ALTER SEQUENCE tab_jobs_id_seq OWNED BY tab_jobs.id; PANIC: cannot make new WAL entries during recovery STATEMENT: SELECT pg_catalog.setval('tab_jobs_id_seq', 31907, true); Hm, yea. setval() simply misses a check there (it was added for nextval though). I wonder if there are other functions bypassing the layers like setval/nextval? Trivial patch attached. Andres diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index 0f9dcfe..d696203 100644 *** a/src/backend/commands/sequence.c --- b/src/backend/commands/sequence.c *** do_setval(Oid relid, int64 next, bool is *** 738,743 --- 738,746 Buffer buf; Form_pg_sequence seq; + /* setval() writes to database and must be prevented during recovery */ + PreventCommandDuringRecovery(); + /* open and AccessShareLock sequence */ init_sequence(relid, elm, seqrel); -- 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] auto_explain causes regression failures
Andrew Dunstan and...@dunslane.net writes: With the following settings custom_variable_classes = 'auto_explain' auto_explain.log_min_duration = 0 auto_explain.log_format = 'xml' auto_explain.log_analyze = on auto_explain.log_verbose = on shared_preload_libraries = 'auto_explain' I am getting regression failures on the rowtypes, transactions and arrays tests. This seems to be fixed now in HEAD. Somebody should try 8.4 as well. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David Fetter da...@fetter.org writes: While hacking on PL/Parrot, I ran across an issue where when trying to load PL/pgsql, it's done unconditionally and fails. How do we fix pg_regress to be a little more subtle about this? Why exactly would we want it to not fail? Regression tests are not about papering over problems. 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] codlin_month is up and complain - PL/Python crash
Zdenek Kotala zdenek.kot...@sun.com writes: Dne 17.02.10 18:39, Peter Eisentraut napsal(a): FWIW, this is a Sun Studio build that is complaining here. Yes It is SS12. I add volatile keyword and problem disappears. OK, I've applied that change in CVS. Please change codlin_moth back to the higher optimization setting. 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 18, 2010, at 3:27 PM, Tom Lane wrote: While hacking on PL/Parrot, I ran across an issue where when trying to load PL/pgsql, it's done unconditionally and fails. How do we fix pg_regress to be a little more subtle about this? Why exactly would we want it to not fail? Regression tests are not about papering over problems. pg_regress needs to not install plpgsql into the data database on 9.0 when passed `--load-language=plpgsql`, because plpgsql will of course already be installed. Unless you want all the third-party modules that depend on plpgsql for tests to somehow detect that they're going to run on 8.5a3 or later and not pass that option. But that'd be kind of a PITA. Much easier if pg_regress knows it doesn't need to install plpgsql. Best, David -- 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] alpha4 timing
On ons, 2010-02-17 at 15:59 -0800, Josh Berkus wrote: On 2/17/10 3:05 PM, Peter Eisentraut wrote: On ons, 2010-02-17 at 14:58 -0800, Josh Berkus wrote: On 2/17/10 2:08 PM, Peter Eisentraut wrote: FYI: 9.0alpha4 will be wrapped tomorrow (Thursday) and released Friday'ish. Can you get me release notes RSN so that I can edit them? I don't have any release notes yet. Feel free to pitch in ... Gah, I won't have any serious writing time until tommorrow evening (US time). Committed. Edit away. -- 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] Thread safety and libxml2
Peter Eisentraut wrote: On ons, 2009-12-30 at 12:55 -0500, Greg Smith wrote: Basically, configure failed on their OpenBSD system because thread safety is on but the libxml2 wasn't compiled with threaded support: http://xmlsoft.org/threads.html Disabling either feature (no --with-libxml or --disable-thread-safety) gives a working build. This could perhaps be fixed by excluding libxml when running the thread test. The thread result is only used in the client libraries and libxml is only used in the backend, so those two shouldn't meet each other in practice. The attached patch removes -lxml2 from the link line of the thread test program. Comments? Can anyone test this fixes the OpenBSD problem? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: configure === RCS file: /cvsroot/pgsql/configure,v retrieving revision 1.671 diff -c -c -r1.671 configure *** configure 17 Feb 2010 04:19:36 - 1.671 --- configure 19 Feb 2010 00:40:05 - *** *** 28593,28599 _CFLAGS=$CFLAGS _LIBS=$LIBS CFLAGS=$CFLAGS $PTHREAD_CFLAGS -DIN_CONFIGURE ! LIBS=$LIBS $PTHREAD_LIBS if test $cross_compiling = yes; then { $as_echo $as_me:$LINENO: result: maybe 5 $as_echo maybe 6; } --- 28593,28601 _CFLAGS=$CFLAGS _LIBS=$LIBS CFLAGS=$CFLAGS $PTHREAD_CFLAGS -DIN_CONFIGURE ! # On OpenBSD, libxml2 is not thread-safe, but it is not used in the backend ! # 2010-02-18 ! LIBS=`echo $LIBS | sed 's/-lxml2 //'`$PTHREAD_LIBS if test $cross_compiling = yes; then { $as_echo $as_me:$LINENO: result: maybe 5 $as_echo maybe 6; } Index: configure.in === RCS file: /cvsroot/pgsql/configure.in,v retrieving revision 1.623 diff -c -c -r1.623 configure.in *** configure.in 17 Feb 2010 04:19:37 - 1.623 --- configure.in 19 Feb 2010 00:40:10 - *** *** 1746,1752 _CFLAGS=$CFLAGS _LIBS=$LIBS CFLAGS=$CFLAGS $PTHREAD_CFLAGS -DIN_CONFIGURE ! LIBS=$LIBS $PTHREAD_LIBS AC_TRY_RUN([#include $srcdir/src/test/thread/thread_test.c], [AC_MSG_RESULT(yes)], [AC_MSG_RESULT(no) --- 1746,1754 _CFLAGS=$CFLAGS _LIBS=$LIBS CFLAGS=$CFLAGS $PTHREAD_CFLAGS -DIN_CONFIGURE ! # On OpenBSD, libxml2 is not thread-safe, but it is not used in the backend ! # 2010-02-18 ! LIBS=`echo $LIBS | sed 's/-lxml2 //'`$PTHREAD_LIBS AC_TRY_RUN([#include $srcdir/src/test/thread/thread_test.c], [AC_MSG_RESULT(yes)], [AC_MSG_RESULT(no) -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Thu, Feb 18, 2010 at 06:27:30PM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: While hacking on PL/Parrot, I ran across an issue where when trying to load PL/pgsql, it's done unconditionally and fails. How do we fix pg_regress to be a little more subtle about this? Why exactly would we want it to not fail? Regression tests are not about papering over problems. OK, I know it's late, but having PL/pgsql on by default has caused an unforeseen need: --require-language. Please find enclosed a patch which implements this. 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 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c index 991bb17..fbf821d 100644 --- a/src/test/regress/pg_regress.c +++ b/src/test/regress/pg_regress.c @@ -85,6 +85,7 @@ char *inputdir = .; char *outputdir = .; char *psqldir = PGBINDIR; static _stringlist *loadlanguage = NULL; +static _stringlist *requirelanguage = NULL; static int max_connections = 0; static char *encoding = NULL; static _stringlist *schedulelist = NULL; @@ -1798,6 +1799,24 @@ create_database(const char *dbname) header(_(installing %s), sl-str); psql_command(dbname, CREATE LANGUAGE \%s\, sl-str); } + + /* +* Make sure any required procedural languages are installed. +*/ + for (sl = requirelanguage; sl != NULL; sl = sl-next) + { + header(_(making sure %s is installed), sl-str); + psql_command(dbname, + DO LANGUAGE plpgsql $$ + BEGIN + IF NOT EXISTS + (SELECT 1 FROM pg_catalog.pg_language WHERE lanname='%s') + THEN + CREATE LANGUAGE %s; + END IF; + END + $$, sl-str, sl-str); + } } static void @@ -1860,6 +1879,8 @@ help(void) printf(_( --inputdir=DIRtake input files from DIR (default \.\)\n)); printf(_( --load-language=lang load the named language before running the\n)); printf(_(tests; can appear multiple times\n)); + printf(_( --require-language=lang make sure the named language is loaded before\n)); + printf(_(running the tests; can appear multiple times\n)); printf(_( --create-role=ROLEcreate the specified role before testing\n)); printf(_( --max-connections=N maximum number of concurrent connections\n)); printf(_((default is 0 meaning unlimited)\n)); @@ -1920,6 +1941,7 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc {create-role, required_argument, NULL, 18}, {temp-config, required_argument, NULL, 19}, {use-existing, no_argument, NULL, 20}, + {require-language, required_argument, NULL, 21}, {NULL, 0, NULL, 0} }; @@ -2013,6 +2035,9 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc case 20: use_existing = true; break; + case 21: + add_stringlist_item(requirelanguage, optarg); + break; default: /* getopt_long already emitted a complaint */ fprintf(stderr, _(\nTry \%s -h\ for more information.\n), -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David Fetter escreveu: OK, I know it's late, but having PL/pgsql on by default has caused an unforeseen need: --require-language. Why? IMHO pg_regress should be used with the same postgres version it was built with. So any tests that use --load-language=plpgsql should be fixed. Besides we could patch pg_regress.c to ignore loading plpgsql language into the database (instead of adding another parameter -- we already have too many options). -- Euler Taveira de Oliveira http://www.timbira.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] Avoiding bad prepared-statement plans.
On Feb 18, 2010, at 2:19 PM, Pierre C wrote: What about catching the error in the application and INSERT'ing into the current preprepare.relation table? The aim would be to do that in dev or in pre-prod environments, then copy the table content in production. Yep, but it's a bit awkward and time-consuming, and not quite suited to ORM-generated requests since you got to generate all the plan names, when the SQL query itself would be the most convenient unique identifier... A cool hack would be something like that : pg_execute( SELECT ..., arguments... ) By inserting a hook which calls a user-specified function on non- existing plan instead of raising an error, this could work. However, this wouldn't work as-is since the plan name must be = NAMEDATALEN, but you get the idea ;) How about the SHA1 hash of the query? Hey, it works for git... :-) Regards, David -- David Christensen End Point Corporation da...@endpoint.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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Fri, Feb 19, 2010 at 12:26:29AM -0200, Euler Taveira de Oliveira wrote: David Fetter escreveu: OK, I know it's late, but having PL/pgsql on by default has caused an unforeseen need: --require-language. Why? IMHO pg_regress should be used with the same postgres version it was built with. So any tests that use --load-language=plpgsql should be fixed. Besides we could patch pg_regress.c to ignore loading plpgsql language into the database (instead of adding another parameter -- we already have too many options). Any external projects will fail on this if they're attempting to support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has suggested that we special-case PL/pgsql for 9.0 and greater, as it's in template0, where those tests are based. Cheers, Another 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 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] LATERAL
Robert Haas wrote: On Sat, Dec 19, 2009 at 11:01 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: I believe the correct approach is probably to treat values that need to be propagated into the inner side as executor parameters. ?This could replace the existing, rather crocky, management of values passed into a nestloop inner indexscan. What is the best place to look for the existing, rather crocky code? Follow the second argument of ExecReScan from nodeNestloop to nodeIndexscan. Yeah, this is grotty. ?It appears that the comment introducing ExecReScan() is somewhat incorrect. ?It asserts that exprCtxt is used only Sigh. ...is used only for index scans. However, it's actually also used for bitmap scans (both heap and index) and TID scans. Also, there appears to be an effort by nodes that don't use exprCtxt directly to propagate down through the node tree, which doesn't seem to make much sense if this is only intended to be used on the inner side of a nestloop. Does some comment need to be updated? -- 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] SR: pseudo replication database of the primary ...
On Fri, Feb 19, 2010 at 12:25 AM, Marc G. Fournier scra...@hub.org wrote: I'm reading through the Wiki docs, and one thing isn't clear: 3. Set up connections and authentication so that the standby server can successfully connect to the pseudo replication database of the primary. I've searched Google, to see if its mentioned anywhere else, but nadda ... can't connect to it using psql, which makes it confusing as to why I'm configuring access to it via pg_hba.conf ... Yeah, since there is no 'replication' database unless user explicitly creates it, psql would be unable to connect to it. 'replication' is the keyword of pg_hba.conf only for authenticating the standby server. This keyword needs to be specified in the 'database' field of pg_hba.conf. So I expressed it as the pseudo replication database. Please see the document for details. http://developer.postgresql.org/pgdocs/postgres/streaming-replication.html#STREAMING-REPLICATION-AUTHENTICATION http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html If that expression is confusing, please feel free to modify it in the wiki and doc ;) 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] SR: pseudo replication database of the primary ...
Fujii Masao wrote: On Fri, Feb 19, 2010 at 12:25 AM, Marc G. Fournier scra...@hub.org wrote: I'm reading through the Wiki docs, and one thing isn't clear: 3. Set up connections and authentication so that the standby server can successfully connect to the pseudo replication database of the primary. I've searched Google, to see if its mentioned anywhere else, but nadda ... can't connect to it using psql, which makes it confusing as to why I'm configuring access to it via pg_hba.conf ... Yeah, since there is no 'replication' database unless user explicitly creates it, psql would be unable to connect to it. 'replication' is the keyword of pg_hba.conf only for authenticating the standby server. This keyword needs to be specified in the 'database' field of pg_hba.conf. So I expressed it as the pseudo replication database. Please see the document for details. http://developer.postgresql.org/pgdocs/postgres/streaming-replication.html#STREAMING-REPLICATION-AUTHENTICATION http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html If that expression is confusing, please feel free to modify it in the wiki and doc ;) FYI, I have modified the SGML docs to call it a replication pseudo-database. -- 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] SR: pseudo replication database of the primary ...
On Fri, Feb 19, 2010 at 12:47 PM, Bruce Momjian br...@momjian.us wrote: FYI, I have modified the SGML docs to call it a replication pseudo-database. Thanks! I also changed the expression on wiki so. 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] SR: pseudo replication database of the primary ...
My only thought would be to change 'pseudo' to 'virtual' ... my initial read, I was looking for a system database like template1/template0 being created ... Thx ... On Fri, 19 Feb 2010, Fujii Masao wrote: On Fri, Feb 19, 2010 at 12:25 AM, Marc G. Fournier scra...@hub.org wrote: I'm reading through the Wiki docs, and one thing isn't clear: 3. Set up connections and authentication so that the standby server can successfully connect to the pseudo replication database of the primary. I've searched Google, to see if its mentioned anywhere else, but nadda ... can't connect to it using psql, which makes it confusing as to why I'm configuring access to it via pg_hba.conf ... Yeah, since there is no 'replication' database unless user explicitly creates it, psql would be unable to connect to it. 'replication' is the keyword of pg_hba.conf only for authenticating the standby server. This keyword needs to be specified in the 'database' field of pg_hba.conf. So I expressed it as the pseudo replication database. Please see the document for details. http://developer.postgresql.org/pgdocs/postgres/streaming-replication.html#STREAMING-REPLICATION-AUTHENTICATION http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html If that expression is confusing, please feel free to modify it in the wiki and doc ;) Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
David Fetter da...@fetter.org wrote: Any external projects will fail on this if they're attempting to support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has suggested that we special-case PL/pgsql for 9.0 and greater, as it's in template0, where those tests are based. +1 for the CREATE LANGUAGE IF NOT EXISTS behavior. The regression test in the core is targeting only its version, but some external projects have version-independent tests. I hope --load-language works as ensure the language is installed rather than always install the language. Regards, --- Takahiro Itagaki 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes: David Fetter da...@fetter.org wrote: support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has suggested that we special-case PL/pgsql for 9.0 and greater, as it's in template0, where those tests are based. +1 for the CREATE LANGUAGE IF NOT EXISTS behavior. The regression test in the core is targeting only its version, but some external projects have version-independent tests. I think it's more like are under the fond illusion that their tests are version-independent. Are we going to back out the next incompatible change we choose to make as soon as somebody notices that it breaks a third-party test case? I don't think so. Let me point out that choosing to install plpgsql by default has already broken --single restore of practically every pg_dump out there. Nobody batted an eye about that. Why are we suddenly so concerned about its effects on unnamed test suites? 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] A thought: should we run pgindent now?
Bruce Momjian wrote: I have a TODO on fixing some of the typedef finding. But I can generate an up to date version of the list Bruce last used in a day or two, and then get this better whacked into shape for another run at the more traditional time. I am ready to run pgindent whenever requested. There is an updated typedefs list at http://www.pgbuildfarm.org/cgi-bin/typedefs.pl 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] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
Andres Freund wrote: Trivial patch attached. Thanks, committed. -- 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] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 18, 2010, at 8:38 PM, Tom Lane wrote: The regression test in the core is targeting only its version, but some external projects have version-independent tests. I think it's more like are under the fond illusion that their tests are version-independent. Are we going to back out the next incompatible change we choose to make as soon as somebody notices that it breaks a third-party test case? I don't think so. Let me point out that choosing to install plpgsql by default has already broken --single restore of practically every pg_dump out there. Nobody batted an eye about that. Why are we suddenly so concerned about its effects on unnamed test suites? Because it's a lot easier for `pg_regress --load-language=plpgsql` to mean ensure the language is installed than it is for 3rd-party test suites to detect what version they're being installed against. Really, all that has to happen is pg_regress in 8.5a4+ needs to just ignore `--load-language=plpgsql`. That's it. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers