Re: [HACKERS] Proposal: Add JSON support
Dne 6.4.2010 7:57, Joseph Adams napsal(a): On Tue, Apr 6, 2010 at 1:00 AM, Petr Jelinekpjmo...@pjmodos.net wrote: Not really sure about this myself, but keep in mind that NULL has special meaning in SQL. To me, the most logical approach is to do the obvious thing: make JSON's 'null' be SQL's NULL. For instance, SELECTing on a table with NULLs in it and converting the result set to JSON would yield a structure with 'null's in it. 'null'::JSON would yield NULL. I'm not sure what startling results would come of this approach, but I'm guessing this would be most intuitive and useful. +1 Just a note, but PostgreSQL has some UTF-8 validation code, you might want to look at it maybe, at least once you start the actual integration into core, so that you are not reinventing too many wheels. I can see how your own code is good thing for general library which this can (and I am sure will be) used as, but for the datatype itself, it might be better idea to use what's already there, unless it's somehow incompatible of course. Indeed. My plan is to first get a strong standalone JSON library written and tested so it can be used as a general-purpose library. As the JSON code is merged into PostgreSQL, it can be adapted. Part of this adaptation would most likely be removing the UTF-8 validation function I wrote and using PostgreSQL's Unicode support code instead. There are probably other bits that could be PostgreSQLified as well. I wonder if I should consider leveraging PostgreSQL's regex support or if it would be a bad fit/waste of time/slower/not worth it. Regex ? What for ? You certainly don't need it for parsing, you have good parser IMHO and regex would probably be all of the above. -- Regards Petr Jelinek (PJMODOS) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Quoting in recovery.conf
To follow up on the discussion here: http://archives.postgresql.org/pgsql-docs/2010-02/msg00039.php It seems like a big oversight that there's no way to insert quotes in strings in recovery.conf. In the long run, the parsing should be done the same way as postgresql.conf, or the two files be merged altogether, but right now I think we should just add support for escaping quotes. I propose two quotes '' to mean a quote mark in the string, like in strings in SQL queries. -- 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] Compile fail, alpha5 gcc 4.3.3 in elog.c
On Tue, Apr 6, 2010 at 07:29, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Tom Lane t...@sss.pgh.pa.us wrote: - cygwin_conv_to_full_win32_path(cmdLine, buf); + cygwin_conv_path(CCP_POSIX_TO_WIN_A, cmdLine, buf, sizeof(buf)); Buildfarm member brown_bat didn't like this. Seeing that that's the *only* active cygwin buildfarm member, that's not a good percentage. Hmmm, but avoiding deprecated APIs would be good on the lastest cygwin. How about checking the version with #ifdef? #ifdef __CYGWIN__ /* need to convert to windows path */ +#if CYGWIN_VERSION_DLL_MAJOR = 1007 cygwin_conv_path(CCP_POSIX_TO_WIN_A, cmdLine, buf, sizeof(buf)); +#else + cygwin_conv_to_full_win32_path(cmdLine, buf); +#endif strcpy(cmdLine, buf); #endif That seems like the way to do it. Or if it's used in many places, use a #define from one to the other - we don't want those #ifdef's all over the place. Seems cygwin may have deprecated that API a bit early :-), but there's nothing we can do about that. If it's deprecated, they'll eventually delete it... -- 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] Quoting in recovery.conf
On Tue, Apr 6, 2010 at 3:47 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: To follow up on the discussion here: http://archives.postgresql.org/pgsql-docs/2010-02/msg00039.php It seems like a big oversight that there's no way to insert quotes in strings in recovery.conf. In the long run, the parsing should be done the same way as postgresql.conf, or the two files be merged altogether, but right now I think we should just add support for escaping quotes. I propose two quotes '' to mean a quote mark in the string, like in strings in SQL queries. Agreed. This would be useful for users to specify the application_name containing a space in the primary_conninfo, for example. 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
[HACKERS] Remaining Streaming Replication Open Items
I triaged the list of open items on the Streaming Replication wiki page. I propose that we drop the ones I've marked as Drop below, and move the remaining items to the main Open Items page for better visibility. And of course try to resolve them as quickly as possible. * Walsender and dblink are not interruptible on win32. - related thread I'd actually be happy to just leave it for 9.0, but it seems like consensus has been reached on how to fix it, and Fujii is working on a patch, so let's follow that through. * Add the GUC parameter to specify the maximum number of log file segments held in pg_xlog directory to send to the standby server. Which is useful to avoid disk full in the primary. Not only to avoid disk full in primary but also to make it feasible to use streaming replication without archiving. It's a small change, we should do it. * pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a backend cannot know the actual timeline which is related to the location. Drop. It's not clear which timeline those functions should return in boundary cases, when replaying records from a log file where the timeline-switch occurs. * The documentation needs to be improved. I've done as much as I can on my own, what we need now is feedback on what needs to be improved. So I'd like to drop this, but let's add new more specific items about what needs to be improved, as people speak up. * Redefine smart shutdown in standby mode? Drop. Too big a change at this point. * Quotes can't be escaped in recovery.conf Under discussion. Not specific to streaming replication, and it's a pre-existing issue, but should be fixed IMHO. * Change the standby mode name. Bikeshedding without consensus. I like the standby mode the best as discussed on that thread, better than any of the proposed alternatives. Drop this item. * Fix things so that any such variables inherited from the server environment are intentionally *NOT* used for making SR connections. Drop. Besides, we have the same problem with dblink, and I don't recall anyone complaining. * If standby_mode is enabled, and neither primary_conninfo nor restore_command are set, the standby would get stuck. It's not really stuck, it will replay any WAL files you drop into pg_xlog. I concur with Robert Haas though that it shouldn't print the message to the log every few seconds. It should print a message the first time it hits the end of WAL, but subsequent messages should be suppressed until some progress has been made. * Remove the unnecessary section about HS from recovery.conf.sample Yeah, let's do it. * The replication connections consume superuser_reserved_connections slots. I'd still like to change this slightly, per my suggestion on that thread, but I don't feel strongly about it. It doesn't seem like a very big change to me, but Tom felt otherwise. * Add missing description about WAL-logging. Small documentation change. Needs to be done I guess. -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Simon Riggs wrote: On Mon, 2010-04-05 at 19:29 +0100, Simon Riggs wrote: Looking through the code some more I note that their are two timing related parameters that are hardcoded into XLogPageRead(). At the very least such things should be #defines, though one of them was previously configurable using pg_standby, so I would like to see them both accessible to user tuning. ...the code says we want to react quickly when the next WAL record arrives and then sleeps for 100ms. The comment continues , so sleep only a bit. That's in comparison to the 5 s wait when polling the archive. -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Fujii Masao wrote: On Tue, Apr 6, 2010 at 3:29 AM, Simon Riggs si...@2ndquadrant.com wrote: I was also surprised to note that the Startup process is not signaled by WALReceiver when new WAL is received, so it will continue sleeping even though it has work to do. I don't think this is so useful in 9.0 since synchronous replication (i.e., transaction commit wait for WAL to be replayed by the standby) is not supported. Well, it would still be useful, as it would shorten the delay. But yeah, there's a delay in asynchronous replication anyway, so we decided to keep it simple and just poll. It's not ideal and definitely needs to be revisited for synchronous mode in the future. Same for walsenders. -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, 2010-04-06 at 10:19 +0300, Heikki Linnakangas wrote: Fujii Masao wrote: On Tue, Apr 6, 2010 at 3:29 AM, Simon Riggs si...@2ndquadrant.com wrote: I was also surprised to note that the Startup process is not signaled by WALReceiver when new WAL is received, so it will continue sleeping even though it has work to do. I don't think this is so useful in 9.0 since synchronous replication (i.e., transaction commit wait for WAL to be replayed by the standby) is not supported. Well, it would still be useful, as it would shorten the delay. But yeah, there's a delay in asynchronous replication anyway, so we decided to keep it simple and just poll. It's not ideal and definitely needs to be revisited for synchronous mode in the future. Same for walsenders. A signal seems fairly straightforward to me, the archiver did this in 8.0 and it was not considered complex then. Quite why it would be complex here is not clear. I'm not happy that it waits, nor that the wait is non-tunable. I would like to see a new parameter added for this. -- 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
[HACKERS] Question about WAL and XID
Hi, am I right that an XID is global across the whole DB cluster under the same $PGDATA? I am asking because in the WAL record, the first thing sent is an XLogRecord which contains TransactionId xl_xid; and as the comment in access/xlog.h says: /* * The overall layout of an XLOG record is: * Fixed-size header (XLogRecord struct) * rmgr-specific data * BkpBlock * backup block data * BkpBlock * backup block data * ... And the BkpBlock structure contains the RelFileNode info, the triplet for tablespace/database/relation. Or is it completely backwards? I am asking this because I need to check TransactionIdDidCommit(XLogRecord-xl_xid) from the walreciver. Can I expect it to work on any xl_xid that walreceiver encounters? Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] Prepared query parsing much slower in 9.0?
David E. Wheeler wrote: When I run the Bricolage test suite against 8.4 (12,700 assertions), it takes 45-50s on my MacBook Pro. When I run them against 9.0, it takes 530-540s! Is there anything in the tree that has debugging turned on or something? I'm not at all sure that what Josh has found can account for this 10x difference, can it (I ran the tests several times). Looking at the process table, postgresql never goes over 25% CPU on 8.4, but hovers at 90-95% on 9.0. To judge by the way test output is emitted, writes are particularly slow, but I can see some substantial delays on reads, too. This needs to be profiled. Otherwise we'd just be speculating on possible causes with no real hard data. (This is where tools like oprofile come in handy, but I have no idea if something similar is available on OSX.) 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] Question about WAL and XID
Boszormenyi Zoltan wrote: am I right that an XID is global across the whole DB cluster under the same $PGDATA? Yes. I am asking this because I need to check TransactionIdDidCommit(XLogRecord-xl_xid) from the walreciver. Can I expect it to work on any xl_xid that walreceiver encounters? Walreceiver is only responsible for receiving the WAL from the master server, and write to disk. It doesn't apply the log, it doesn't look into the contents at all. So TransactionIdDidCommit(XLogRecord-xl_xid) always returns false on WAL it has received, because it hasn't been applied yet. What are you trying to do? -- 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] Question about WAL and XID
Hi, Heikki Linnakangas írta: Boszormenyi Zoltan wrote: am I right that an XID is global across the whole DB cluster under the same $PGDATA? Yes. I am asking this because I need to check TransactionIdDidCommit(XLogRecord-xl_xid) from the walreciver. Can I expect it to work on any xl_xid that walreceiver encounters? Walreceiver is only responsible for receiving the WAL from the master server, and write to disk. It doesn't apply the log, it doesn't look into the contents at all. So TransactionIdDidCommit(XLogRecord-xl_xid) always returns false on WAL it has received, because it hasn't been applied yet. I guessed so, I intended to collect the xl_xid values in a cache array and check periodically. What are you trying to do? Synchronous replication. :-) Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby: Startup at shutdown checkpoint
Initial patch. I will be testing over next day. No commit before at
least midday on Wed 7 Apr.
The existing call to PrescanPreparedTransactions() looks correct to me
but the comment is wrong. I will change that also, if we agree.
--
Simon Riggs www.2ndQuadrant.com
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index e2566a4..365cd17 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -1719,6 +1719,88 @@ PrescanPreparedTransactions(TransactionId **xids_p, int *nxids_p)
}
/*
+ * StandbyRecoverPreparedTransactions
+ *
+ * Scan the pg_twophase directory and setup all the required information to
+ * allow standby queries to treat prepared transactions as still active.
+ * This is never called at the end of recovery - we use
+ * RecoverPreparedTransactions() at that point.
+ *
+ * Currently we simply call SubTransSetParent() for any subxids of prepared
+ * transactions.
+ */
+void
+StandbyRecoverPreparedTransactions(bool can_overwrite)
+{
+ DIR *cldir;
+ struct dirent *clde;
+
+ cldir = AllocateDir(TWOPHASE_DIR);
+ while ((clde = ReadDir(cldir, TWOPHASE_DIR)) != NULL)
+ {
+ if (strlen(clde-d_name) == 8
+ strspn(clde-d_name, 0123456789ABCDEF) == 8)
+ {
+ TransactionId xid;
+ char *buf;
+ TwoPhaseFileHeader *hdr;
+ TransactionId *subxids;
+ int i;
+
+ xid = (TransactionId) strtoul(clde-d_name, NULL, 16);
+
+ /* Already processed? */
+ if (TransactionIdDidCommit(xid) || TransactionIdDidAbort(xid))
+ {
+ereport(WARNING,
+ (errmsg(removing stale two-phase state file \%s\,
+clde-d_name)));
+RemoveTwoPhaseFile(xid, true);
+continue;
+ }
+
+ /* Read and validate file */
+ buf = ReadTwoPhaseFile(xid, true);
+ if (buf == NULL)
+ {
+ereport(WARNING,
+ (errmsg(removing corrupt two-phase state file \%s\,
+ clde-d_name)));
+RemoveTwoPhaseFile(xid, true);
+continue;
+ }
+
+ /* Deconstruct header */
+ hdr = (TwoPhaseFileHeader *) buf;
+ if (!TransactionIdEquals(hdr-xid, xid))
+ {
+ereport(WARNING,
+ (errmsg(removing corrupt two-phase state file \%s\,
+ clde-d_name)));
+RemoveTwoPhaseFile(xid, true);
+pfree(buf);
+continue;
+ }
+
+ /*
+ * Examine subtransaction XIDs ... they should all follow main
+ * XID, and they may force us to advance nextXid.
+ */
+ subxids = (TransactionId *)
+(buf + MAXALIGN(sizeof(TwoPhaseFileHeader)));
+ for (i = 0; i hdr-nsubxacts; i++)
+ {
+TransactionId subxid = subxids[i];
+
+Assert(TransactionIdFollows(subxid, xid));
+SubTransSetParent(xid, subxid, can_overwrite);
+ }
+ }
+ }
+ FreeDir(cldir);
+}
+
+/*
* RecoverPreparedTransactions
*
* Scan the pg_twophase directory and reload shared-memory state for each
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index abdf4d8..08b4cf8 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -5808,6 +5808,33 @@ StartupXLOG(void)
StartupMultiXact();
ProcArrayInitRecoveryInfo(oldestActiveXID);
+
+ /*
+ * If we're beginning at a shutdown checkpoint, we know that
+ * nothing was running on the master at this point. So fake-up
+ * an empty running-xacts record and use that here and now.
+ * Recover additional standby state for prepared transactions.
+ */
+ if (wasShutdown)
+ {
+RunningTransactionsData running;
+
+/*
+ * Construct a RunningTransactions snapshot representing a shut
+ * down server, with only prepared transactions still alive.
+ * We're never overflowed at this point because all subxids
+ * are listed with their parent prepared transactions.
+ */
+running.xcnt = nxids;
+running.subxid_overflow = false;
+running.nextXid = checkPoint.nextXid;
+running.oldestRunningXid = oldestActiveXID;
+running.xids = xids;
+
+ProcArrayApplyRecoveryInfo(running);
+
+StandbyRecoverPreparedTransactions(false);
+ }
}
/* Initialize resource managers */
@@ -7520,13 +7547,34 @@ xlog_redo(XLogRecPtr lsn, XLogRecord *record)
if (standbyState != STANDBY_DISABLED)
CheckRequiredParameterValues(checkPoint);
+ /*
+ * If we're beginning at a shutdown checkpoint, we know that
+ * nothing was running on the master at this point. So fake-up
+ * an empty running-xacts record and use that here and now.
+ * Recover additional standby state for prepared transactions.
+ */
if (standbyState = STANDBY_INITIALIZED)
{
+ TransactionId *xids;
+ int nxids;
+ TransactionId oldestActiveXID = PrescanPreparedTransactions(xids, nxids);
+ RunningTransactionsData running;
+
/*
- * Remove stale transactions, if any.
+ * Construct a RunningTransactions snapshot representing a shut
+ * down server, with only prepared transactions still alive.
+ * We're never overflowed at this point because all subxids
+ *
Re: [HACKERS] Remaining Streaming Replication Open Items
On Tue, Apr 6, 2010 at 4:09 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I triaged the list of open items on the Streaming Replication wiki page. I propose that we drop the ones I've marked as Drop below, and move the remaining items to the main Open Items page for better visibility. And of course try to resolve them as quickly as possible. Thanks so much!! * Walsender and dblink are not interruptible on win32. - related thread I'd actually be happy to just leave it for 9.0, but it seems like consensus has been reached on how to fix it, and Fujii is working on a patch, so let's follow that through. Yeah, I'm reworking the patch, but I'd like to take aim at only walreceiver because the change for dblink might become too big at this point. Since no one has complained about the long-term problem of dblink, I'm no sure it really should be fixed right now. * Add the GUC parameter to specify the maximum number of log file segments held in pg_xlog directory to send to the standby server. Which is useful to avoid disk full in the primary. Not only to avoid disk full in primary but also to make it feasible to use streaming replication without archiving. It's a small change, we should do it. Yep. * pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a backend cannot know the actual timeline which is related to the location. Drop. It's not clear which timeline those functions should return in boundary cases, when replaying records from a log file where the timeline-switch occurs. OK, but we need to add the note about that confusing behavior. How about?: diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 57163da..da3253f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -13206,6 +13206,8 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); This is usually the desired behavior for managing transaction log archiving behavior, since the preceding file is the last one that currently needs to be archived. +Note that functionpg_xlogfile_name/ and functionpg_xlogfile_name_offset/ +always return an inaccurate result during recovery. /para para @@ -13279,6 +13281,11 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); /table para +Note that functionpg_xlogfile_name/ and functionpg_xlogfile_name_offset/ +always return an inaccurate result from any of the above locations. + /para + + para The functions shown in xref linkend=functions-admin-dbsize calculate the disk space usage of database objects. /para * The documentation needs to be improved. I've done as much as I can on my own, what we need now is feedback on what needs to be improved. So I'd like to drop this, but let's add new more specific items about what needs to be improved, as people speak up. Yep. * Redefine smart shutdown in standby mode? Drop. Too big a change at this point. I don't think that it's too big, but OK. And, ISTM we need to add the note about the longstanding confusing behavior if it's dropped. How about?: diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index 594bd7d..f8899e4 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1339,6 +1339,7 @@ echo -17 /proc/self/oom_adj active, new connections will still be allowed, but only to superusers (this exception allows a superuser to connect to terminate online backup mode). + If the server is in recovery, it additionally waits for recovery to end. /para /listitem /varlistentry * Quotes can't be escaped in recovery.conf Under discussion. Not specific to streaming replication, and it's a pre-existing issue, but should be fixed IMHO. Yep. * Change the standby mode name. Bikeshedding without consensus. I like the standby mode the best as discussed on that thread, better than any of the proposed alternatives. Drop this item. Yep. * Fix things so that any such variables inherited from the server environment are intentionally *NOT* used for making SR connections. Drop. Besides, we have the same problem with dblink, and I don't recall anyone complaining. Yep, but I don't think that dblink has the same issue because it's often used to connect to another database on the same postgres instance, which seems proper method. The problem is that walreceiver might wrongly connect to *its* server and get stuck because no WAL records arrive for ever. Since currently we don't allow the standby to accept the replication connection, the problem will not happen in 9.0, and ISTM we don't need to address it right now. So I agree to drop. * If standby_mode is enabled, and neither primary_conninfo nor restore_command are set, the standby would get stuck. It's not really stuck, it will replay any WAL files you drop into pg_xlog. I concur
Re: [HACKERS] SELECT constant; takes 15x longer on 9.0?
Josh Berkus j...@agliodbs.com wrote: SELECT 'DBD::Pg ping test'; In our test, which does 5801 of these pings during the test, they take an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ). Any clue why this would be? Did you use the same configure options between them? For example, --enable-debug or --enable-cassert. 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] message clarifications
On Sat, 2010-04-03 at 11:00 +0300, Peter Eisentraut wrote: The following messages from the postgres catalog either appear to be internal errors that should be marked differently, or they are in my estimation unintelligible to users and should be rephrased. #: storage/ipc/procarray.c:2224 msgid record known xact %u latestObservedXid %u #: storage/ipc/procarray.c:2257 msgid recording unobserved xid %u (latestObservedXid %u) #: storage/ipc/procarray.c:2379 msgid too many KnownAssignedXids #: storage/ipc/standby.c:861 msgid snapshot of %u running transactions overflowed (lsn %X/%X oldest xid %u next xid %u) #: storage/ipc/standby.c:868 msgid snapshot of %u running transaction ids (lsn %X/%X oldest xid %u next xid %u) These are all DEBUG messages. Can you explain marked differently so I can do that for you? -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Simon Riggs wrote: On Tue, 2010-04-06 at 10:19 +0300, Heikki Linnakangas wrote: Fujii Masao wrote: On Tue, Apr 6, 2010 at 3:29 AM, Simon Riggs si...@2ndquadrant.com wrote: I was also surprised to note that the Startup process is not signaled by WALReceiver when new WAL is received, so it will continue sleeping even though it has work to do. I don't think this is so useful in 9.0 since synchronous replication (i.e., transaction commit wait for WAL to be replayed by the standby) is not supported. Well, it would still be useful, as it would shorten the delay. But yeah, there's a delay in asynchronous replication anyway, so we decided to keep it simple and just poll. It's not ideal and definitely needs to be revisited for synchronous mode in the future. Same for walsenders. A signal seems fairly straightforward to me, the archiver did this in 8.0 and it was not considered complex then. Quite why it would be complex here is not clear. The other side of the problem is that walsender polls too. Eliminating the delay from walreceiver doesn't buy you much unless you eliminate the delay from the walsender too. And things get complicated there. Do you signal the walsenders at every commit? That would be a lot of volume, and adds more work for every normal transaction in the primary. Maybe not much, but it would be one more thing to worry about and test. I'm not happy that it waits, nor that the wait is non-tunable. I would like to see a new parameter added for this. I wanted to keep it simple for users, but feel free to add a parameter if you feel it must be configurable. -- 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] message clarifications
On tis, 2010-04-06 at 10:30 +0100, Simon Riggs wrote: These are all DEBUG messages. Can you explain marked differently so I can do that for you? Then it would be better to convert them to use elog() instead of ereport(). -- 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] message clarifications
On Tue, 2010-04-06 at 12:44 +0300, Peter Eisentraut wrote: On tis, 2010-04-06 at 10:30 +0100, Simon Riggs wrote: These are all DEBUG messages. Can you explain marked differently so I can do that for you? Then it would be better to convert them to use elog() instead of ereport(). Will do -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, 2010-04-06 at 12:38 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: On Tue, 2010-04-06 at 10:19 +0300, Heikki Linnakangas wrote: Fujii Masao wrote: On Tue, Apr 6, 2010 at 3:29 AM, Simon Riggs si...@2ndquadrant.com wrote: I was also surprised to note that the Startup process is not signaled by WALReceiver when new WAL is received, so it will continue sleeping even though it has work to do. I don't think this is so useful in 9.0 since synchronous replication (i.e., transaction commit wait for WAL to be replayed by the standby) is not supported. Well, it would still be useful, as it would shorten the delay. But yeah, there's a delay in asynchronous replication anyway, so we decided to keep it simple and just poll. It's not ideal and definitely needs to be revisited for synchronous mode in the future. Same for walsenders. A signal seems fairly straightforward to me, the archiver did this in 8.0 and it was not considered complex then. Quite why it would be complex here is not clear. The other side of the problem is that walsender polls too. Eliminating the delay from walreceiver doesn't buy you much unless you eliminate the delay from the walsender too. And things get complicated there. Do you signal the walsenders at every commit? That would be a lot of volume, and adds more work for every normal transaction in the primary. Maybe not much, but it would be one more thing to worry about and test. You are trying to connect two unrelated things. We can argue that the WALSender's delay allows it to build up a good sized batch of work to transfer. Having the Startup process wait does not buy us anything at all. Currently if the Startup process finishes more quickly than the WALreceiver it will wait for 100ms. I am surprised at your arguments for simplicity. With Hot Standby you have insisted that everything should be in place. With SR you seem to have just stopped at a barely working, poorly documented implementation. We both know you can fix these things easily and quickly. Please do so. Not because I say so, but because everybody else will soon notice that you could have and did not. -- 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: pending patch: Re: [HACKERS] Streaming replication and pg_xlogfile_name()
Fujii Masao wrote: On Fri, Apr 2, 2010 at 2:22 AM, Robert Haas robertmh...@gmail.com wrote: Can someone explain to me in plain language what problem this is trying to fix? I'm having trouble figuring it out. The problem is that pg_xlogfile_name(pg_last_xlog_receive_location()) and pg_xlogfile_name(pg_last_xlog_replay_location()) might report an inaccurate WAL file name because currently pg_xlogfile_name() always uses the current timeline to calculate the WAL file name. For example, even though the last applied WAL file is 00010002, the standby wrongly reports that 0002 has been applied last. Should we throw an error in pg_xlogfile_name() if called during recovery? It's not doing anything useful as it is. -- 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: pending patch: Re: [HACKERS] Streaming replication and pg_xlogfile_name()
On Tue, Apr 6, 2010 at 7:25 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Fujii Masao wrote: On Fri, Apr 2, 2010 at 2:22 AM, Robert Haas robertmh...@gmail.com wrote: Can someone explain to me in plain language what problem this is trying to fix? I'm having trouble figuring it out. The problem is that pg_xlogfile_name(pg_last_xlog_receive_location()) and pg_xlogfile_name(pg_last_xlog_replay_location()) might report an inaccurate WAL file name because currently pg_xlogfile_name() always uses the current timeline to calculate the WAL file name. For example, even though the last applied WAL file is 00010002, the standby wrongly reports that 0002 has been applied last. Should we throw an error in pg_xlogfile_name() if called during recovery? It's not doing anything useful as it is. I have no objection for now. 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Simon Riggs wrote: I am surprised at your arguments for simplicity. With Hot Standby you have insisted that everything should be in place. With SR you seem to have just stopped at a barely working, poorly documented implementation. That's opposite to my recollection of the hot standby development. I simplified and ripped out a lot of stuff from the original patch. If you insist, I'll work out a patch to send a signal to startup process after every fsync(), but it really doesn't seem very important given that there's always a delay there anyway. We both know you can fix these things easily and quickly. Please do so. That's a plural form. What's the other thing you're referring to? Not because I say so, but because everybody else will soon notice that you could have and did not. Bollocks. -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Well, it would still be useful, as it would shorten the delay. But yeah, there's a delay in asynchronous replication anyway, so we decided to keep it simple and just poll. It's not ideal and definitely needs to be revisited for synchronous mode in the future. Same for walsenders. Stop me if I misunderstood the case at hand, but while waiting some more for having a sizeable batch to send makes a lot of sense to me, waiting on the receiver side when there's some work to do will only forbids a slow slave to keep up with the load, increasing lag artificially. I'm used to asynchronous replication where you're never allowed to rest if some batch is ready for you to process. 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, Apr 6, 2010 at 8:06 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: If you insist, I'll work out a patch to send a signal to startup process after every fsync(), but it really doesn't seem very important given that there's always a delay there anyway. Agreed. Even if we get rid of the delay of startup process, it would still take time until the committed transaction has become visible in the standby. 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Dimitri Fontaine wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Well, it would still be useful, as it would shorten the delay. But yeah, there's a delay in asynchronous replication anyway, so we decided to keep it simple and just poll. It's not ideal and definitely needs to be revisited for synchronous mode in the future. Same for walsenders. Stop me if I misunderstood the case at hand, but while waiting some more for having a sizeable batch to send makes a lot of sense to me, waiting on the receiver side when there's some work to do will only forbids a slow slave to keep up with the load, increasing lag artificially. I'm used to asynchronous replication where you're never allowed to rest if some batch is ready for you to process. When the startup process wakes up after sleep to replay WAL, it does replay all the WAL streamed that far. And if more WAL if streamed during the replay, it's replayed too before the next sleep. But when it does reach the end of already-streamed WAL, it sleeps for 100ms, and doesn't wake up earlier if a WAL record arrives during the sleep. So, it does increase the lag artificially, but it will keep up with the volume just fine. -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, 2010-04-06 at 14:06 +0300, Heikki Linnakangas wrote: If you insist, I'll work out a patch to send a signal to startup process after every fsync(), but it really doesn't seem very important given that there's always a delay there anyway. We both know you can fix these things easily and quickly. Please do so. That's a plural form. What's the other thing you're referring to? I mentioned 3 things right here: * signal * parameter to control delay (2 separate delays) * docs Many other things have been mentioned on other posts and I am unhappy with the answer lets defer everything til 9.1. Yes, some things need to be deferred, but not everything. I feel for you both as developers, and apologise if you don't like what I say, but we need to get things into a better state for 9.0. Please. -- 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] recovery.conf.sample
On Wed, 2010-03-03 at 21:51 +0900, Fujii Masao wrote: The attached patch removes the unnecessary section about HS from recovery.conf.sample. Also it changes the grouping of parameters in recovery.conf.sample as mentioned above. I think that comment block is useful for people to remind them that some relevant parameters need to be set in postgresql.conf. They might otherwise try to put them in recovery.conf and be surprised by the result. -- 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, Apr 6, 2010 at 7:06 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Simon Riggs wrote: I am surprised at your arguments for simplicity. With Hot Standby you have insisted that everything should be in place. With SR you seem to have just stopped at a barely working, poorly documented implementation. That's opposite to my recollection of the hot standby development. I simplified and ripped out a lot of stuff from the original patch. If you insist, I'll work out a patch to send a signal to startup process after every fsync(), but it really doesn't seem very important given that there's always a delay there anyway. I would like to vote strongly against doing this. We all know that Streaming Replication in 9.0 is not going to be as mature as we'd like it to be; but we should be putting our time into fixing things that are broken rather than tinkering with the avoidance of 100 ms waits. We both know you can fix these things easily and quickly. Please do so. That's a plural form. What's the other thing you're referring to? Not because I say so, but because everybody else will soon notice that you could have and did not. Bollocks. I've been thinking that the reason we weren't going to beta was because of the SR open items, but I'm starting to think there's not much left that really needs to be dealt with. The ones from that list I think we should fix yet are: - Walreceiver and dblink are not interruptible on win32. - The documentation needs to be improved (if there's still more to do) - Redefine smart shutdown in standby mode? (i'm working on this) - The replication connections consume superuser_reserved_connections slots. The other stuff strikes me as all window dressing. I also think we need to deal with the shutdown checkpoint issue, which is HS rather than SR. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quoting in recovery.conf
On Tue, 2010-04-06 at 16:07 +0900, Fujii Masao wrote: On Tue, Apr 6, 2010 at 3:47 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: To follow up on the discussion here: http://archives.postgresql.org/pgsql-docs/2010-02/msg00039.php It seems like a big oversight that there's no way to insert quotes in strings in recovery.conf. In the long run, the parsing should be done the same way as postgresql.conf, or the two files be merged altogether, but right now I think we should just add support for escaping quotes. I propose two quotes '' to mean a quote mark in the string, like in strings in SQL queries. Agreed. This would be useful for users to specify the application_name containing a space in the primary_conninfo, for example. +1 -- 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: pending patch: Re: [HACKERS] Streaming replication and pg_xlogfile_name()
On Tue, Apr 6, 2010 at 8:02 PM, Fujii Masao masao.fu...@gmail.com wrote: Should we throw an error in pg_xlogfile_name() if called during recovery? It's not doing anything useful as it is. I have no objection for now. Here is the patch. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center forbid_pg_xlogfile_name_during_recovery_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, 2010-04-06 at 07:47 -0400, Robert Haas wrote: I've been thinking that the reason we weren't going to beta was because of the SR open items, but I'm starting to think there's not much left that really needs to be dealt with. The ones from that list I think we should fix yet are: - Walreceiver and dblink are not interruptible on win32. - The documentation needs to be improved (if there's still more to do) - Redefine smart shutdown in standby mode? (i'm working on this) - The replication connections consume superuser_reserved_connections slots. The other stuff strikes me as all window dressing. I'm not happy that other stuff just gets punted. Things should definitely not be removed from Open Items list when there is still discussion/objection on them. The purpose of discussion on hackers is so that we take note of those items, not just shrug and walk away from them because some weeks have passed since they were mentioned. I shouldn't have to keep a personal list of things I've objected to, so I can refute what other people say. -- 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] Remaining Streaming Replication Open Items
I wrote my previous email before reading this. On Tue, Apr 6, 2010 at 3:09 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I triaged the list of open items on the Streaming Replication wiki page. I propose that we drop the ones I've marked as Drop below, and move the remaining items to the main Open Items page for better visibility. And of course try to resolve them as quickly as possible. * Walsender and dblink are not interruptible on win32. - related thread I'd actually be happy to just leave it for 9.0, but it seems like consensus has been reached on how to fix it, and Fujii is working on a patch, so let's follow that through. Agree. * Add the GUC parameter to specify the maximum number of log file segments held in pg_xlog directory to send to the standby server. Which is useful to avoid disk full in the primary. Not only to avoid disk full in primary but also to make it feasible to use streaming replication without archiving. It's a small change, we should do it. Do we have a working patch? * pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a backend cannot know the actual timeline which is related to the location. Drop. It's not clear which timeline those functions should return in boundary cases, when replaying records from a log file where the timeline-switch occurs. Agree. * The documentation needs to be improved. I've done as much as I can on my own, what we need now is feedback on what needs to be improved. So I'd like to drop this, but let's add new more specific items about what needs to be improved, as people speak up. Agree. It's hard to think of this as a beta-blocker without more specific feedback. * Redefine smart shutdown in standby mode? Drop. Too big a change at this point. We have a working patch for this - I want to commit it. I don't think it's a big change, and the current behavior is extremely pathological. * Quotes can't be escaped in recovery.conf Under discussion. Not specific to streaming replication, and it's a pre-existing issue, but should be fixed IMHO. Fine with me. * Change the standby mode name. Bikeshedding without consensus. I like the standby mode the best as discussed on that thread, better than any of the proposed alternatives. Drop this item. OK. * Fix things so that any such variables inherited from the server environment are intentionally *NOT* used for making SR connections. Drop. Besides, we have the same problem with dblink, and I don't recall anyone complaining. Agree. I think that whole issue is bikeshedding. * If standby_mode is enabled, and neither primary_conninfo nor restore_command are set, the standby would get stuck. It's not really stuck, it will replay any WAL files you drop into pg_xlog. I concur with Robert Haas though that it shouldn't print the message to the log every few seconds. It should print a message the first time it hits the end of WAL, but subsequent messages should be suppressed until some progress has been made. Any idea how to implement this? * Remove the unnecessary section about HS from recovery.conf.sample Yeah, let's do it. Don't care. * The replication connections consume superuser_reserved_connections slots. I'd still like to change this slightly, per my suggestion on that thread, but I don't feel strongly about it. It doesn't seem like a very big change to me, but Tom felt otherwise. Agree, we should fix it. * Add missing description about WAL-logging. Small documentation change. Needs to be done I guess. No strong feelings. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, Apr 6, 2010 at 8:01 AM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-04-06 at 07:47 -0400, Robert Haas wrote: I've been thinking that the reason we weren't going to beta was because of the SR open items, but I'm starting to think there's not much left that really needs to be dealt with. The ones from that list I think we should fix yet are: - Walreceiver and dblink are not interruptible on win32. - The documentation needs to be improved (if there's still more to do) - Redefine smart shutdown in standby mode? (i'm working on this) - The replication connections consume superuser_reserved_connections slots. The other stuff strikes me as all window dressing. I'm not happy that other stuff just gets punted. Things should definitely not be removed from Open Items list when there is still discussion/objection on them. The purpose of discussion on hackers is so that we take note of those items, not just shrug and walk away from them because some weeks have passed since they were mentioned. I shouldn't have to keep a personal list of things I've objected to, so I can refute what other people say. If we never removed anything upon which there wasn't 100% consensus, we would never release. But no one is talking about removing items without discussing them. We are talking about discussing the possibility of removing some of them. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] walreceiver is uninterruptible on win32
On Mon, Apr 5, 2010 at 3:18 PM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Apr 2, 2010 at 11:11 PM, Magnus Hagander mag...@hagander.net wrote: More to the point, I'm not sure I like the creation of yet another DLL to deal with this. The reason this isn't just exported from the main backend is the same reason we created the libpqwalreceiver library I'm sure - bt that means we already have one. How about we just use this same source file, but compile and link it directly into both dblink and libpqwalreceiver? That'd leave us with one DLL less, making life easier. ISTM that we cannot compile dblink using USE_PGXS=1, if that DLL doesn't exist in the installation directory. No? I might have misinterpreted your point. You mean that the same source file defining something like pgwin32_PQexec should be placed in both contrib/dblink and src/backend/replication/libpqwalreceiver? If so, we can compile dblink using USE_PGXS without the DLL. 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: When the startup process wakes up after sleep to replay WAL, it does replay all the WAL streamed that far. And if more WAL if streamed during the replay, it's replayed too before the next sleep. But when it does reach the end of already-streamed WAL, it sleeps for 100ms, and doesn't wake up earlier if a WAL record arrives during the sleep. Thanks for the clear picture. It then works the same as PGQ based consumers, including londiste. I'm now happy ☻ So, it does increase the lag artificially, but it will keep up with the volume just fine. Great. No further complain from me there. I guess it now entirely depends on how easy it is to wake up before the end of the 100ms: it might be that it's easier to code the signaling than to add a GUC for the value? 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] Autonomous transaction
Loïc Vaumerel she...@gmail.com writes: All solutions I found are working the same way : they use dblink. I consider these solution more as handiwork than a clean solution. I am a little bit concerned about side effects as dblink were not intially designed for this. See plproxy which is designed for this kind of work. Or about… Is there a way to use real and clean autonomous transactions in PostgreSQL yet ? None that I know of. If no, is it planned to do so ? When ? We get demands quite often, it seems it's one of the big tickets we're still missing. I don't remember any development effort proposal, though. 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Simon Riggs wrote:
On Tue, 2010-04-06 at 12:38 +0300, Heikki Linnakangas wrote:
Simon Riggs wrote:
On Tue, 2010-04-06 at 10:19 +0300, Heikki Linnakangas wrote:
Fujii Masao wrote:
On Tue, Apr 6, 2010 at 3:29 AM, Simon Riggs si...@2ndquadrant.com wrote:
I was also surprised to note that the Startup process is not signaled by
WALReceiver when new WAL is received, so it will continue sleeping even
though it has work to do.
I don't think this is so useful in 9.0 since synchronous replication
(i.e., transaction commit wait for WAL to be replayed by the standby)
is not supported.
Well, it would still be useful, as it would shorten the delay. But yeah,
there's a delay in asynchronous replication anyway, so we decided to
keep it simple and just poll. It's not ideal and definitely needs to be
revisited for synchronous mode in the future. Same for walsenders.
A signal seems fairly straightforward to me, the archiver did this in
8.0 and it was not considered complex then. Quite why it would be
complex here is not clear.
The other side of the problem is that walsender polls too. Eliminating
the delay from walreceiver doesn't buy you much unless you eliminate the
delay from the walsender too. And things get complicated there. Do you
signal the walsenders at every commit? That would be a lot of volume,
and adds more work for every normal transaction in the primary. Maybe
not much, but it would be one more thing to worry about and test.
You are trying to connect two unrelated things.
We can argue that the WALSender's delay allows it to build up a good
sized batch of work to transfer.
Having the Startup process wait does not buy us anything at all.
Currently if the Startup process finishes more quickly than the
WALreceiver it will wait for 100ms.
Ok, here's a patch to add signaling between walreceiver and startup
process. It indeed isn't much code, and seems pretty safe, so if no-one
objects strongly, I'll commit. It won't help on platforms where
pg_usleep() isn't interrupted by signals, though, but we can live with that.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index abdf4d8..47cd6e9 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -8682,6 +8682,16 @@ StartupProcShutdownHandler(SIGNAL_ARGS)
shutdown_requested = true;
}
+/*
+ * SIGUSR1: do nothing, but receiving the signal will wake us up if we're
+ * sleeping (on platforms where usleep() is interrupted by sleep, on other
+ * platforms this is useless).
+ */
+static void
+DoNothingHandler(SIGNAL_ARGS)
+{
+}
+
/* Handle SIGHUP and SIGTERM signals of startup process */
void
HandleStartupProcInterrupts(void)
@@ -8735,7 +8745,7 @@ StartupProcessMain(void)
else
pqsignal(SIGALRM, SIG_IGN);
pqsignal(SIGPIPE, SIG_IGN);
- pqsignal(SIGUSR1, SIG_IGN);
+ pqsignal(SIGUSR1, DoNothingHandler); /* WAL record has been streamed */
pqsignal(SIGUSR2, SIG_IGN);
/*
@@ -8859,8 +8869,10 @@ retry:
goto triggered;
/*
- * When streaming is active, we want to react quickly when
- * the next WAL record arrives, so sleep only a bit.
+ * Sleep until the next WAL record arrives, or
+ * walreceiver dies. On some platforms, signals don't
+ * interrupt sleep, so poll every 100 ms to ensure we
+ * respond promptly on such platforms.
*/
pg_usleep(10L); /* 100ms */
}
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 98ab484..39d8fb9 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -205,8 +205,8 @@ bool enable_bonjour = false;
char *bonjour_name;
/* PIDs of special child processes; 0 when not running */
-static pid_t StartupPID = 0,
- BgWriterPID = 0,
+pid_t StartupPID = 0;
+static pid_t BgWriterPID = 0,
WalWriterPID = 0,
WalReceiverPID = 0,
AutoVacPID = 0,
@@ -433,6 +433,7 @@ typedef struct
PMSignalData *PMSignalState;
InheritableSocket pgStatSock;
pid_t PostmasterPid;
+ pid_t StartupPid;
TimestampTz PgStartTime;
TimestampTz PgReloadTime;
bool redirection_done;
@@ -4595,6 +4596,7 @@ save_backend_variables(BackendParameters *param, Port *port,
return false;
param-PostmasterPid = PostmasterPid;
+ param-StartupPid = StartupPid;
param-PgStartTime = PgStartTime;
param-PgReloadTime = PgReloadTime;
@@ -4809,6 +4811,7 @@ restore_backend_variables(BackendParameters *param, Port *port)
read_inheritable_socket(pgStatSock, param-pgStatSock);
PostmasterPid = param-PostmasterPid;
+ StartupPid = param-StartupPid;
PgStartTime = param-PgStartTime;
PgReloadTime = param-PgReloadTime;
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index c0e7e0b..c4b4614 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -41,6 +41,7
Re: [HACKERS] SELECT constant; takes 15x longer on 9.0?
On Tue, Apr 6, 2010 at 1:47 AM, Josh Berkus j...@agliodbs.com wrote: Hackers, Continuing the performance test: DBD, like a number of monitoring systems, does pings on the database which look like this: SELECT 'DBD::Pg ping test'; In our test, which does 5801 of these pings during the test, they take an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ). did your pings change? on my machine the query ';' completes in about 0.05ms but any select takes 0.19 - 0.25ms. 0.77 is awfully high -- there has to be an explanation. 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
On Tue, 2010-04-06 at 16:01 +0300, Heikki Linnakangas wrote: Having the Startup process wait does not buy us anything at all. Currently if the Startup process finishes more quickly than the WALreceiver it will wait for 100ms. Ok, here's a patch to add signaling between walreceiver and startup process. It indeed isn't much code, and seems pretty safe, so if no-one objects strongly, I'll commit. It won't help on platforms where pg_usleep() isn't interrupted by signals, though, but we can live with that. Looks good. There is also the fixed 5 sec wait when polling the archive. I would like to make that a parameter, since that was previously controllable with pg_standby. -- 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] Autonomous transaction
It would be useful to have a relation such that all dirtied buffers got written out even for failed transactions (barring a crash) and such that read-any-undeleted were easy to do, despite the non-ACIDity. The overhead of a side transaction seems overkill for such things as logs or advisory relations, and non-DB files would be harder to tie in efficiently to DB activity. A side transaction would still have to be committed in order to be useful; either you're committing frequently (ouch!), or you risk failing to commit just as you would the main transaction. David Hudson -Original Message- From: Loïc Vaumerel [mailto:she...@gmail.com] Sent: Sunday, April 4, 2010 10:26 AM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Autonomous transaction Hi, I have an application project based on a database. I am really interested in using PostgreSQL. I have only one issue, I want to use autonomous transactions to put in place a debug / logging functionality. To do so, I insert messages in a debug table. The problem is, if the main transaction / process rollback, my debug message insert will be rolled back too. This is not the behavior I wish. I need a functionality with the same behavior than the Oracle PRAGMA AUTONOMOUS_TRANSACTION one. I have searched for it in the documentation and on the net, unfortunately nothing. (maybe I missed something) I just found some posts regarding this : http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php https://labs.omniti.com/trac/pgtreats/browser/trunk/autonomous_logging_tool ... and some others ... All solutions I found are working the same way : they use dblink. I consider these solution more as handiwork than a clean solution. I am a little bit concerned about side effects as dblink were not intially designed for this. So my questions : Is there a way to use real and clean autonomous transactions in PostgreSQL yet ? If no, is it planned to do so ? When ? Thanks in advance Best regards Shefla
Re: [HACKERS] message clarifications
Peter Eisentraut pete...@gmx.net writes: On tis, 2010-04-06 at 10:30 +0100, Simon Riggs wrote: These are all DEBUG messages. Can you explain marked differently so I can do that for you? Then it would be better to convert them to use elog() instead of ereport(). Or use errmsg_internal instead of errmsg. 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: Check compulsory parameters in recovery.conf in standby_mode, per
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Ok, here's a patch to add signaling between walreceiver and startup process. It indeed isn't much code, and seems pretty safe, so if no-one objects strongly, I'll commit. I object --- this seems like a large change to be sticking in at this point with no testing. I'm concerned about exactly how often the signal will happen (ie, how much overhead is being added). I'm also concerned about the fact that the startup process will now be receiving a constant storm of no-op signals, an operational behavior that is completely untested. If there's even one place that is failing to deal with EINTR retry, for instance, we'll have a problem. Plus I don't care for the platform dependency of the fix. Being interruptable by signals is not part of the defined API for pg_usleep. I agree with the previous opinion that trying to get rid of that delay is an entirely inappropriate task at this point. Leave it for 9.1. 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] message clarifications
On Tue, 2010-04-06 at 09:57 -0400, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On tis, 2010-04-06 at 10:30 +0100, Simon Riggs wrote: These are all DEBUG messages. Can you explain marked differently so I can do that for you? Then it would be better to convert them to use elog() instead of ereport(). Or use errmsg_internal instead of errmsg. I've changed them to elog() before you said this. Would you like me to change them to errmsg_internal or do you mean ...as an option in the future? -- 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] message clarifications
Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-04-06 at 09:57 -0400, Tom Lane wrote: Or use errmsg_internal instead of errmsg. I've changed them to elog() before you said this. Would you like me to change them to errmsg_internal or do you mean ...as an option in the future? It's just a different option. elog for debug messages is a well established practice, but if you wanted something that looked more like ereport, you could hide the messages from translation with the above. 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] SELECT constant; takes 15x longer on 9.0?
Josh Berkus j...@agliodbs.com writes:
Continuing the performance test:
DBD, like a number of monitoring systems, does pings on the database
which look like this:
SELECT 'DBD::Pg ping test';
In our test, which does 5801 of these pings during the test, they take
an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ).
There's something wrong with your test setup. Or, if you'd like me to
think that there isn't, provide a self-contained test case. I ran a
small program that does
for (i = 0; i 1; i++)
{
res = PQexec(conn, SELECT 'DBD::Pg ping test');
PQclear(res);
}
and I only see a few percent difference between HEAD and 8.4.3,
on two different machines. (It does appear that HEAD is a bit slower
for this, which might or might not be something to worry about.)
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] Remaining Streaming Replication Open Items
Robert Haas wrote: On Tue, Apr 6, 2010 at 3:09 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: * Add the GUC parameter to specify the maximum number of log file segments held in pg_xlog directory to send to the standby server. Which is useful to avoid disk full in the primary. Not only to avoid disk full in primary but also to make it feasible to use streaming replication without archiving. It's a small change, we should do it. Do we have a working patch? No. * Redefine smart shutdown in standby mode? Drop. Too big a change at this point. We have a working patch for this - I want to commit it. I don't think it's a big change, and the current behavior is extremely pathological. Oh, ok. I didn't look at the latest patch, if it looks good to you, fine with me. * If standby_mode is enabled, and neither primary_conninfo nor restore_command are set, the standby would get stuck. It's not really stuck, it will replay any WAL files you drop into pg_xlog. I concur with Robert Haas though that it shouldn't print the message to the log every few seconds. It should print a message the first time it hits the end of WAL, but subsequent messages should be suppressed until some progress has been made. Any idea how to implement this? I'll take a look. It shouldn't be too hard. -- 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: pending patch: Re: [HACKERS] Streaming replication and pg_xlogfile_name()
Fujii Masao wrote: On Tue, Apr 6, 2010 at 8:02 PM, Fujii Masao masao.fu...@gmail.com wrote: Should we throw an error in pg_xlogfile_name() if called during recovery? It's not doing anything useful as it is. I have no objection for now. Here is the patch. ... + if (RecoveryInProgress()) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg(recovery is in progress), + errhint(WAL control functions cannot be executed during recovery.))); + The hint is a bit confusing for pg_xlogfile_name(). pg_xlogfile_name() is hardly a WAL control function like pg_start/stop_backup() are. How about pg_xlogfile_name() cannot be executed during recovery.? -- 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] recovery.conf.sample
Fujii Masao wrote: On Tue, Feb 23, 2010 at 1:44 PM, Fujii Masao masao.fu...@gmail.com wrote: recovery.conf.sample has the following section for Hot Standby. Is this still required? #--- # HOT STANDBY PARAMETERS #--- # # If you want to enable read-only connections during recovery, enable # recovery_connections in postgresql.conf # #--- Heikki classified the recovery options into the following three groups, in the document. 26.1. Archive recovery settings 26.2. Recovery target settings 26.3. Standby server settings OTOH, recovery.conf.sample has classified them into the following two groups. This is inconsistent with the document, and looks confusing. How about modifying recovery.conf.sample to make the grouping the same? ARCHIVE RECOVERY PARAMETERS LOG-STREAMING REPLICATION PARAMETERS The attached patch removes the unnecessary section about HS from recovery.conf.sample. Also it changes the grouping of parameters in recovery.conf.sample as mentioned above. I committed the LOG-STREAMING REPLICATION PARAMETERS - STANDBY SERVER PARAMETERS rename. Simon added the RECOVERY TARGET PARAMETERS heading already, and he wants to keep the Hot Standby section. -- 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] Proposal: Add JSON support
Joseph Adams escribió: http://constellationmedia.com/~funsite/static/json-0.0.2.tar.bz2 My json.c is now 1161 lines long, so I can't quite call it small anymore. Just noticed you don't check the return value of malloc and friends. How do you intend to handle that? There are various places that would simply dump core with the 0.0.2 code. Within Postgres it's easy -- a failed palloc aborts the transaction and doesn't continue running your code. But in a standalone library that's probably not acceptable. If we were to import this there are some lines that could be ripped out, like 60 lines in the string buffer stuff and 130 lines for Unicode. That brings your code just under 1000 lines. -- 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] Remaining Streaming Replication Open Items
On Tue, Apr 6, 2010 at 10:36 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: On Tue, Apr 6, 2010 at 3:09 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: * Add the GUC parameter to specify the maximum number of log file segments held in pg_xlog directory to send to the standby server. Which is useful to avoid disk full in the primary. Not only to avoid disk full in primary but also to make it feasible to use streaming replication without archiving. It's a small change, we should do it. Do we have a working patch? No. :-( * Redefine smart shutdown in standby mode? Drop. Too big a change at this point. We have a working patch for this - I want to commit it. I don't think it's a big change, and the current behavior is extremely pathological. Oh, ok. I didn't look at the latest patch, if it looks good to you, fine with me. I'll commit it tonight. * If standby_mode is enabled, and neither primary_conninfo nor restore_command are set, the standby would get stuck. It's not really stuck, it will replay any WAL files you drop into pg_xlog. I concur with Robert Haas though that it shouldn't print the message to the log every few seconds. It should print a message the first time it hits the end of WAL, but subsequent messages should be suppressed until some progress has been made. Any idea how to implement this? I'll take a look. It shouldn't be too hard. The tricky part, I believe, is that there's more than one message that can potentially be emitted, and you don't want ANY of them to repeat every 2 s, so some thought needs to be given to where to hook in the logic. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared query parsing much slower in 9.0?
On Apr 6, 2010, at 1:59 AM, Andrew Dunstan wrote: This needs to be profiled. Otherwise we'd just be speculating on possible causes with no real hard data. (This is where tools like oprofile come in handy, but I have no idea if something similar is available on OSX.) I think OS X has dtrace…but I'd be interested to see if this issue appears on other platforms, too. 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] Proposal: Add JSON support
Petr Jelinek pjmo...@pjmodos.net writes:
Dne 6.4.2010 7:57, Joseph Adams napsal(a):
To me, the most logical approach is to do the obvious thing: make
JSON's 'null' be SQL's NULL. For instance, SELECTing on a table with
NULLs in it and converting the result set to JSON would yield a
structure with 'null's in it. 'null'::JSON would yield NULL. I'm not
sure what startling results would come of this approach, but I'm
guessing this would be most intuitive and useful.
+1
I think it's a pretty bad idea for 'null'::JSON to yield NULL. AFAIR
there is no other standard datatype for which the input converter can
yield NULL from a non-null input string, and I'm not even sure that the
InputFunctionCall protocol allows it. (In fact a quick look indicates
that it doesn't...)
To me, what this throws into question is not so much whether JSON null
should equate to SQL NULL (it should), but whether it's sane to accept
atomic values. If I understood the beginning of this discussion, that's
not strictly legal. I think it would be better for strict input mode
to reject this, and permissive mode to convert it to a non-atomic value.
Thus jsonify('null') wouldn't yield NULL but a structure containing a
null.
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] Remaining Streaming Replication Open Items
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I triaged the list of open items on the Streaming Replication wiki page. I propose that we drop the ones I've marked as Drop below, and move the remaining items to the main Open Items page for better visibility. By drop do you mean move to TODO? At least some of these issues should be addressed in 9.1 or later. Perhaps some can really be dropped, but it's not clear which. 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 2:32 AM, Takahiro Itagaki wrote: In our test, which does 5801 of these pings during the test, they take an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ). Any clue why this would be? Did you use the same configure options between them? Yes. For example, --enable-debug or --enable-cassert. No. 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 6:07 AM, Merlin Moncure wrote: In our test, which does 5801 of these pings during the test, they take an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ). did your pings change? No. 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] Re: [COMMITTERS] pgsql: Check compulsory parameters in recovery.conf in standby_mode, per
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Ok, here's a patch to add signaling between walreceiver and startup process. It indeed isn't much code, and seems pretty safe Great news! Thanks, -- 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] walreceiver is uninterruptible on win32
On Tue, Apr 6, 2010 at 2:25 PM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Apr 5, 2010 at 3:18 PM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Apr 2, 2010 at 11:11 PM, Magnus Hagander mag...@hagander.net wrote: More to the point, I'm not sure I like the creation of yet another DLL to deal with this. The reason this isn't just exported from the main backend is the same reason we created the libpqwalreceiver library I'm sure - bt that means we already have one. How about we just use this same source file, but compile and link it directly into both dblink and libpqwalreceiver? That'd leave us with one DLL less, making life easier. ISTM that we cannot compile dblink using USE_PGXS=1, if that DLL doesn't exist in the installation directory. No? I might have misinterpreted your point. You mean that the same source file defining something like pgwin32_PQexec should be placed in both contrib/dblink and src/backend/replication/libpqwalreceiver? If so, we can compile dblink using USE_PGXS without the DLL. No, I don't mean that. I mean store it in one place, and copy/link it into where it's used. Look at for example how crypt.c and getaddrinfo.c are handled in libpq. Not sure how that will play with PGXS, though, but I'm not entirely sure we care if it can be built that way? If it does, there should be some way to get PGXS to execute that rule as well, I'm sure. Also note that per Tom's comments this is not a win32 only fix, so it shouldn't be called pgwin32_*(). -- 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] Quoting in recovery.conf
Simon Riggs wrote:
On Tue, 2010-04-06 at 16:07 +0900, Fujii Masao wrote:
On Tue, Apr 6, 2010 at 3:47 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
To follow up on the discussion here:
http://archives.postgresql.org/pgsql-docs/2010-02/msg00039.php
It seems like a big oversight that there's no way to insert quotes in
strings in recovery.conf. In the long run, the parsing should be done
the same way as postgresql.conf, or the two files be merged altogether,
but right now I think we should just add support for escaping quotes. I
propose two quotes '' to mean a quote mark in the string, like in
strings in SQL queries.
Agreed. This would be useful for users to specify the application_name
containing a space in the primary_conninfo, for example.
+1
Ok, here's what I came up with.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index abdf4d8..73ef0f9 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -4894,6 +4894,100 @@ str_time(pg_time_t tnow)
}
/*
+ * Parse one line from recovery.conf. 'cmdline' is the raw line from the
+ * file. If the line is parsed successfully, returns true, false indicates
+ * syntax error. On success, *key_p and *value_p are set to the parameter
+ * name and value on the line, respectively. If the line is an empty line,
+ * consisting entirely of whitespace and comments, function returns true
+ * and *keyp_p and *value_p are set to NULL.
+ *
+ * The pointers returned in *key_p and *value_p point to an internal buffer
+ * that is valid only until the next call of parseRecoveryCommandFile().
+ */
+static bool
+parseRecoveryCommandFileLine(char *cmdline, char **key_p, char **value_p)
+{
+ char *ptr;
+ char *bufp;
+ char *key;
+ char *value;
+ static char *buf = NULL;
+
+ *key_p = *value_p = NULL;
+
+ /*
+ * Allocate the buffer on first use. It's used to hold both the
+ * parameter name and value.
+ */
+ if (buf == NULL)
+ buf = malloc(MAXPGPATH + 1);
+ bufp = buf;
+
+ /* Skip any whitespace at the beginning of line */
+ for (ptr = cmdline; *ptr; ptr++)
+ {
+ if (!isspace((unsigned char) *ptr))
+ break;
+ }
+ /* Ignore empty lines */
+ if (*ptr == '\0' || *ptr == '#')
+ return true;
+
+ /* Read the parameter name */
+ key = bufp;
+ while (*ptr !isspace((unsigned char) *ptr)
+ *ptr != '=' *ptr != '\'')
+ *(bufp++) = *(ptr++);
+ *(bufp++) = '\0';
+
+ /* Skip to the beginning quote of the parameter value */
+ ptr = strchr(ptr, '\'');
+ if (!ptr)
+ return false;
+ ptr++;
+
+ /* Read the parameter value to *bufp. Collapse any '' escapes as we go. */
+ value = bufp;
+ for (;;)
+ {
+ if (*ptr == '\'')
+ {
+ ptr++;
+ if (*ptr == '\'')
+*(bufp++) = '\'';
+ else
+ {
+/* end of parameter */
+*bufp = '\0';
+break;
+ }
+ }
+ else if (*ptr == '\0')
+ return false; /* unterminated quoted string */
+ else
+ *(bufp++) = *ptr;
+
+ ptr++;
+ }
+ *(bufp++) = '\0';
+
+ /* Check that there's no garbage after the value */
+ while (*ptr)
+ {
+ if (*ptr == '#')
+ break;
+ if (!isspace((unsigned char) *ptr))
+ return false;
+ ptr++;
+ }
+
+ /* Success! */
+ *key_p = key;
+ *value_p = value;
+ return true;
+}
+
+/*
* See if there is a recovery command file (recovery.conf), and if so
* read in parameters for archive recovery and XLOG streaming.
*
@@ -4926,39 +5020,16 @@ readRecoveryCommandFile(void)
*/
while (fgets(cmdline, sizeof(cmdline), fd) != NULL)
{
- /* skip leading whitespace and check for # comment */
- char *ptr;
char *tok1;
char *tok2;
- for (ptr = cmdline; *ptr; ptr++)
- {
- if (!isspace((unsigned char) *ptr))
-break;
- }
- if (*ptr == '\0' || *ptr == '#')
- continue;
-
- /* identify the quoted parameter value */
- tok1 = strtok(ptr, ');
- if (!tok1)
- {
- syntaxError = true;
- break;
- }
- tok2 = strtok(NULL, ');
- if (!tok2)
- {
- syntaxError = true;
- break;
- }
- /* reparse to get just the parameter name */
- tok1 = strtok(ptr, \t=);
- if (!tok1)
+ if (!parseRecoveryCommandFileLine(cmdline, tok1, tok2))
{
syntaxError = true;
break;
}
+ if (tok1 == NULL)
+ continue;
if (strcmp(tok1, restore_command) == 0)
{
--
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] Remaining Streaming Replication Open Items
Fujii Masao masao.fu...@gmail.com writes: On Tue, Apr 6, 2010 at 4:09 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: * Fix things so that any such variables inherited from the server environment are intentionally *NOT* used for making SR connections. Drop. Besides, we have the same problem with dblink, and I don't recall anyone complaining. Yep, but I don't think that dblink has the same issue because it's often used to connect to another database on the same postgres instance, which seems proper method. Yes, dblink is a poor precedent to cite because self-connections are a sane behavior in its case. The problem is that walreceiver might wrongly connect to *its* server and get stuck because no WAL records arrive for ever. Since currently we don't allow the standby to accept the replication connection, the problem will not happen in 9.0, and ISTM we don't need to address it right now. So I agree to drop. Agreed, this can be put off until we support relay replication. I think it will be an issue then, however. 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 7:28 AM, Tom Lane wrote:
There's something wrong with your test setup. Or, if you'd like me to
think that there isn't, provide a self-contained test case. I ran a
small program that does
for (i = 0; i 1; i++)
{
res = PQexec(conn, SELECT 'DBD::Pg ping test');
PQclear(res);
}
and I only see a few percent difference between HEAD and 8.4.3,
on two different machines. (It does appear that HEAD is a bit slower
for this, which might or might not be something to worry about.)
I'm going to see if I can replicate it on a second box today.
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] Proposal: Add JSON support
On Tue, Apr 6, 2010 at 11:05 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Joseph Adams escribió: http://constellationmedia.com/~funsite/static/json-0.0.2.tar.bz2 My json.c is now 1161 lines long, so I can't quite call it small anymore. Just noticed you don't check the return value of malloc and friends. How do you intend to handle that? There are various places that would simply dump core with the 0.0.2 code. Within Postgres it's easy -- a failed palloc aborts the transaction and doesn't continue running your code. But in a standalone library that's probably not acceptable. If we were to import this there are some lines that could be ripped out, like 60 lines in the string buffer stuff and 130 lines for Unicode. That brings your code just under 1000 lines. Let me be the first to suggest putting this code under the PostgreSQL license. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT constant; takes 15x longer on 9.0?
Tom Lane t...@sss.pgh.pa.us writes:
Josh Berkus j...@agliodbs.com writes:
Continuing the performance test:
DBD, like a number of monitoring systems, does pings on the database
which look like this:
SELECT 'DBD::Pg ping test';
In our test, which does 5801 of these pings during the test, they take
an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ).
There's something wrong with your test setup. Or, if you'd like me to
think that there isn't, provide a self-contained test case.
I did some comparisons and oprofile work against this test case:
for (i = 0; i [lots]; i++)
{
res = PQexec(conn, SELECT 'DBD::Pg ping test');
PQclear(res);
}
In assert-enabled builds, HEAD seems about 10% slower than 8.4 branch
tip, but as far as I can tell this is all debug overhead associated with
a slightly larger number of catcache entries that are present
immediately after startup. In non-assert-enabled builds there's a
difference of a percent or so, which appears to be due to increased
lexer overhead; oprofile shows these top routines in HEAD:
samples %image name symbol name
49787 7.0533 postgres base_yyparse
35510 5.0307 postgres AllocSetAlloc
29135 4.1275 postgres hash_search_with_hash_value
24541 3.4767 postgres core_yylex
15231 2.1578 postgres PostgresMain
14710 2.0840 postgres hash_seq_search
14340 2.0315 postgres LockReleaseAll
13878 1.9661 postgres MemoryContextAllocZeroAligned
10047 1.4234 postgres ScanKeywordLookup
9866 1.3977 postgres LWLockAcquire
9434 1.3365 postgres LockAcquireExtended
8347 1.1825 postgres hash_any
7954 1.1268 postgres ExecInitExpr
7326 1.0379 postgres MemoryContextAlloc
7243 1.0261 postgres AllocSetFree
6787 0.9615 postgres MemoryContextAllocZero
6501 0.9210 postgres internal_flush
5956 0.8438 postgres LWLockRelease
versus these in 8.4:
samples %image name symbol name
51795 7.2589 postgres AllocSetAlloc
37742 5.2894 postgres base_yyparse
32558 4.5629 postgres hash_search_with_hash_value
17250 2.4175 postgres hash_seq_search
14933 2.0928 postgres AllocSetFree
14902 2.0885 postgres MemoryContextAllocZeroAligned
13219 1.8526 postgres LockReleaseAll
12974 1.8183 postgres SearchCatCache
10885 1.5255 postgres PostgresMain
10592 1.4844 postgres ResourceOwnerReleaseInternal
10462 1.4662 postgres base_yylex
10007 1.4025 postgres hash_any
9553 1.3388 postgres MemoryContextAllocZero
8758 1.2274 postgres LWLockAcquire
8237 1.1544 postgres exec_simple_query
7410 1.0385 postgres LockAcquire
7315 1.0252 postgres MemoryContextCreate
7262 1.0177 postgres MemoryContextAlloc
7220 1.0119 postgres LWLockRelease
The only thing that seems to have changed by more than the noise level
is that core_yylex (formerly base_yylex) got slower. I suppose this is
due to changing over to a re-entrant scanner. The flex manual claims
that %option reentrant doesn't cost any performance --- so I suspect
that what we are seeing here is additional per-call overhead and not a
slowdown that would be important for lexing long queries. I don't think
there's anything to worry about there for nontrivial queries.
I also tried reconnecting to the server for each query. In that
situation HEAD seems to be about 10% slower than 8.4 even without
asserts, which might be an artifact of the changes to eliminate the
flat authentication files. I'm not particularly concerned about that
either, since if you're looking for performance, reconnecting to issue a
trivial query is not what you should be doing.
So I'm not sure where your 15x is coming from, but I don't see it.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Remaining Streaming Replication Open Items
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I triaged the list of open items on the Streaming Replication wiki page. I propose that we drop the ones I've marked as Drop below, and move the remaining items to the main Open Items page for better visibility. By drop do you mean move to TODO? At least some of these issues should be addressed in 9.1 or later. Perhaps some can really be dropped, but it's not clear which. Umm, yes, honestly speaking I hadn't even thought about that. I've added the ones that should be addressed in the future to the TODO list. I added a new subsection for standby server and streaming replication related items: http://wiki.postgresql.org/wiki/Todo#Standby_server_mode -- 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] Proposal: Add JSON support
On Tue, Apr 6, 2010 at 12:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Petr Jelinek pjmo...@pjmodos.net writes:
Dne 6.4.2010 7:57, Joseph Adams napsal(a):
To me, the most logical approach is to do the obvious thing: make
JSON's 'null' be SQL's NULL. For instance, SELECTing on a table with
NULLs in it and converting the result set to JSON would yield a
structure with 'null's in it. 'null'::JSON would yield NULL. I'm not
sure what startling results would come of this approach, but I'm
guessing this would be most intuitive and useful.
+1
I think it's a pretty bad idea for 'null'::JSON to yield NULL. AFAIR
there is no other standard datatype for which the input converter can
yield NULL from a non-null input string, and I'm not even sure that the
InputFunctionCall protocol allows it. (In fact a quick look indicates
that it doesn't...)
Oh. I missed this aspect of the proposal. I agree - that's a bad idea.
To me, what this throws into question is not so much whether JSON null
should equate to SQL NULL (it should), but whether it's sane to accept
atomic values.
With this, I disagree. I see no reason to suppose that a JSON NULL
and an SQL NULL are the same thing.
If I understood the beginning of this discussion, that's
not strictly legal. I think it would be better for strict input mode
to reject this, and permissive mode to convert it to a non-atomic value.
Thus jsonify('null') wouldn't yield NULL but a structure containing a
null.
There's no obvious structure to convert this into.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Add JSON support
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 6, 2010 at 12:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: To me, what this throws into question is not so much whether JSON null should equate to SQL NULL (it should), but whether it's sane to accept atomic values. With this, I disagree. I see no reason to suppose that a JSON NULL and an SQL NULL are the same thing. Oh. If they're not the same, then the problem is easily dodged, but then what *is* a JSON null? 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] [BUGS] BUG #5394: invalid declspec for PG_MODULE_MAGIC
On Mon, Mar 29, 2010 at 11:47 AM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Vladimir Barzionov snego.bar...@gmail.com wrote: Same problem was already discussed for example here http://dbaspot.com/forums/postgresql/393683-re-general-custom-c-function-palloc-broken.html Looks like the simplest way for correcting the issue is declaring additional macro (something like PGMODULEEXPORT) Sure, I agree it is a longstanding bug in PostgreSQL. Developers who use MSVC (not mingw) always encounter the bug; machines in the buildfarm can build Windows binaries just because they have non-standard equipments. A patch attached. The name of PGMODULEEXPORT might be arguable. I agree with this in principle, but won't this break every single add-on module out there that supports Win32? -- 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] SELECT constant; takes 15x longer on 9.0?
On Tue, Apr 6, 2010 at 12:08 PM, David E. Wheeler da...@kineticode.com wrote: On Apr 6, 2010, at 2:32 AM, Takahiro Itagaki wrote: In our test, which does 5801 of these pings during the test, they take an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ). Any clue why this would be? Did you use the same configure options between them? Yes. For example, --enable-debug or --enable-cassert. hmm. ssl? (I don't see any interesting difference in time either btw). can you log in w/psql and confirm the difference there w/timing switch? 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 9:08 AM, David E. Wheeler wrote: For example, --enable-debug or --enable-cassert. No. Oh FFS! I was looking at the wrong build script. It was indeed built with --enable-cassert --enable-debug. Grrr. Well, that's likely the culprit right there. I'm rebuilding without those now and hopefully my tests will be back down to 45s. Many apologies for the noise and wasted time. 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 10:17 AM, Tom Lane wrote: So I'm not sure where your 15x is coming from, but I don't see it. By stupidly having configured with --enable-cassert --enable-debug without realizing it. I've just rebuilt without them and run the tests again using the default postgresql.conf and I'm back down to 57s and 46s over two runs. Sorry for the wasted time. I knew there had to a be a simple answer. 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 10:52 AM, David E. Wheeler wrote: Oh FFS! I was looking at the wrong build script. It was indeed built with --enable-cassert --enable-debug. Grrr. Well, that's likely the culprit right there. I'm rebuilding without those now and hopefully my tests will be back down to 45s. Many apologies for the noise and wasted time. And just to close out this thread, I rebuilt without `--enable-cassert --enable-debug` and now the tests pass in 57s and 46s over two runs, just like on 8.4 (though with just the default postgresql.conf, unlike my 8.4 install). Phew! Knew it had to be somehting stup^H^H^Himple. 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] Proposal: Add JSON support
On Tue, Apr 6, 2010 at 1:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 6, 2010 at 12:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: To me, what this throws into question is not so much whether JSON null should equate to SQL NULL (it should), but whether it's sane to accept atomic values. With this, I disagree. I see no reason to suppose that a JSON NULL and an SQL NULL are the same thing. Oh. If they're not the same, then the problem is easily dodged, but then what *is* a JSON null? I assume we're going to treat JSON much like XML: basically text, but with some validation (and perhaps canonicalization) under the hood. So a JSON null will be null, just a JSON boolean true value will be true. It would be pretty weird if storing true or false or 4 or [3,1,4,1,5,9] into a json column and then reading it back returned the input string; but at the same time storing null into the column returned a SQL NULL. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT constant; takes 15x longer on 9.0?
David E. Wheeler da...@kineticode.com writes: On Apr 6, 2010, at 10:17 AM, Tom Lane wrote: So I'm not sure where your 15x is coming from, but I don't see it. By stupidly having configured with --enable-cassert --enable-debug without realizing it. I've just rebuilt without them and run the tests again using the default postgresql.conf and I'm back down to 57s and 46s over two runs. Huh. I'm still curious, because in my test the overhead of those options seemed to be about 3x. So there's still something considerably different between what you did and what I did. Are you testing a separate connection per ping query? I think I neglected to compare that case with and without assert overhead. 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] Proposal: Add JSON support
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 6, 2010 at 1:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh. If they're not the same, then the problem is easily dodged, but then what *is* a JSON null? I assume we're going to treat JSON much like XML: basically text, but with some validation (and perhaps canonicalization) under the hood. So a JSON null will be null, just a JSON boolean true value will be true. It would be pretty weird if storing true or false or 4 or [3,1,4,1,5,9] into a json column and then reading it back returned the input string; but at the same time storing null into the column returned a SQL NULL. Hmm. So the idea is that all JSON atomic values are considered to be text strings, even when they look like something else (like bools or numbers)? That would simplify matters I guess, but I'm not sure about the usability. In particular I'd want to have something that dequotes the value so that I can get foo not foo when converting to SQL text. (I'm assuming that quotes would be there normally, so as not to lose the distinction between 3 and 3 in the JSON representation.) 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] Proposal: Add JSON support
On Tue, Apr 6, 2010 at 2:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 6, 2010 at 1:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh. If they're not the same, then the problem is easily dodged, but then what *is* a JSON null? I assume we're going to treat JSON much like XML: basically text, but with some validation (and perhaps canonicalization) under the hood. So a JSON null will be null, just a JSON boolean true value will be true. It would be pretty weird if storing true or false or 4 or [3,1,4,1,5,9] into a json column and then reading it back returned the input string; but at the same time storing null into the column returned a SQL NULL. Hmm. So the idea is that all JSON atomic values are considered to be text strings, even when they look like something else (like bools or numbers)? That would simplify matters I guess, but I'm not sure about the usability. I'm not sure what the other option is. If you do SELECT col FROM table, I'm not aware that you can return differently-typed values for different rows... In particular I'd want to have something that dequotes the value so that I can get foo not foo when converting to SQL text. (I'm assuming that quotes would be there normally, so as not to lose the distinction between 3 and 3 in the JSON representation.) Yes, that seems like a useful support function. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Remaining Streaming Replication Open Items
On Tue, April 6, 2010 19:29, Heikki Linnakangas wrote: [...] I've added the ones that should be addressed in the future to the TODO list. I added a new subsection for standby server and streaming replication related items: http://wiki.postgresql.org/wiki/Todo#Standby_server_mode I reported Assertion failure twophase.c a few times; see: http://search.postgresql.org/search?m=1q=Assertion+failure+twophase.cl=d=s= Btw, it has now also happened once without the postbio package installed - (which was unlikely to be the cause anyway, I think). I don't see it mentioned in the TODO, but maybe it's just deemed too elusive to be assigned a todo entry. Was the issue eventually found/solved? thanks, 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] SELECT constant; takes 15x longer on 9.0?
David E. Wheeler wrote: By stupidly having configured with --enable-cassert --enable-debug without realizing it. I've just rebuilt without them and run the tests again using the default postgresql.conf and I'm back down to 57s and 46s over two runs. Every performance test I run, regardless of where the binaries come from or how I thought they were built, starts like this: postgres=# show debug_assertions; debug_assertions -- off (1 row) It's a really good habit to get into, or even enforce in your testing script if practical. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 11:15 AM, Tom Lane wrote: By stupidly having configured with --enable-cassert --enable-debug without realizing it. I've just rebuilt without them and run the tests again using the default postgresql.conf and I'm back down to 57s and 46s over two runs. Huh. I'm still curious, because in my test the overhead of those options seemed to be about 3x. So there's still something considerably different between what you did and what I did. Are you doing this on a Mac? Are you testing a separate connection per ping query? I think I neglected to compare that case with and without assert overhead. No, should be one connection for the entire test suite. 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] Remaining Streaming Replication Open Items
On Tue, 2010-04-06 at 20:27 +0200, Erik Rijkers wrote: Was the issue eventually found/solved? We think so, but the event was not conclusively traceable. -- 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] Remaining Streaming Replication Open Items
On Tue, 2010-04-06 at 11:06 -0400, Robert Haas wrote: * Redefine smart shutdown in standby mode? Drop. Too big a change at this point. We have a working patch for this - I want to commit it. I don't think it's a big change, and the current behavior is extremely pathological. Oh, ok. I didn't look at the latest patch, if it looks good to you, fine with me. I'll commit it tonight. I don't see this on hackers. Have you posted it? I'd like to see what you do before it gets committed. Thanks. -- 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] Proposal: Add JSON support
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: With this, I disagree. I see no reason to suppose that a JSON NULL and an SQL NULL are the same thing. Oh. If they're not the same, then the problem is easily dodged, but then what *is* a JSON null? Probably the same as the javascript null. regards, Yeb Havinga -- 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] SELECT constant; takes 15x longer on 9.0?
David E. Wheeler da...@kineticode.com writes: On Apr 6, 2010, at 11:15 AM, Tom Lane wrote: Huh. I'm still curious, because in my test the overhead of those options seemed to be about 3x. So there's still something considerably different between what you did and what I did. Are you doing this on a Mac? I hadn't, but since you mention it: 10 iterations take about 7.5sec with non-assert CVS HEAD and 15sec with asserts, on a 2008 Macbook Pro. Color me still confused. 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] SELECT constant; takes 15x longer on 9.0?
On Apr 6, 2010, at 12:50 PM, Tom Lane wrote: I hadn't, but since you mention it: 10 iterations take about 7.5sec with non-assert CVS HEAD and 15sec with asserts, on a 2008 Macbook Pro. Color me still confused. Well it's not just pings that the bricolage tests were running, of course. Josh, might you have got the numbers wrong when trying to match up query runtimes to their queries in the CSVLOG output? That might explain it. Most of the queries were BINDs. 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] [BUGS] BUG #5394: invalid declspec for PG_MODULE_MAGIC
Magnus Hagander mag...@hagander.net writes: On Mon, Mar 29, 2010 at 11:47 AM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: A patch attached. The name of PGMODULEEXPORT might be arguable. I agree with this in principle, but won't this break every single add-on module out there that supports Win32? The patch didn't touch the contrib modules, so why would it break third-party sources? 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] SELECT constant; takes 15x longer on 9.0?
2010/4/6 Tom Lane t...@sss.pgh.pa.us: David E. Wheeler da...@kineticode.com writes: On Apr 6, 2010, at 11:15 AM, Tom Lane wrote: Huh. I'm still curious, because in my test the overhead of those options seemed to be about 3x. So there's still something considerably different between what you did and what I did. Are you doing this on a Mac? I hadn't, but since you mention it: 10 iterations take about 7.5sec with non-assert CVS HEAD and 15sec with asserts, on a 2008 Macbook Pro. Color me still confused. it is little bit offtopic. Can we add info about assertation to version() output? like postgres=# select version(); version ─── PostgreSQL 9.0alpha4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.4.3 2010 0127 (Red Hat 4.4.3-4), 32-bit with enabled assertation (1 row) Regards Pavel Stehule 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 -- 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] SELECT constant; takes 15x longer on 9.0?
Pavel Stehule pavel.steh...@gmail.com writes: it is little bit offtopic. Can we add info about assertation to version() output? Greg has the right idea: show debug_assertions. 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] Proposal: Add JSON support
On Tue, Apr 6, 2010 at 12:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Petr Jelinek pjmo...@pjmodos.net writes:
Dne 6.4.2010 7:57, Joseph Adams napsal(a):
To me, the most logical approach is to do the obvious thing: make
JSON's 'null' be SQL's NULL. For instance, SELECTing on a table with
NULLs in it and converting the result set to JSON would yield a
structure with 'null's in it. 'null'::JSON would yield NULL. I'm not
sure what startling results would come of this approach, but I'm
guessing this would be most intuitive and useful.
+1
I think it's a pretty bad idea for 'null'::JSON to yield NULL. AFAIR
there is no other standard datatype for which the input converter can
yield NULL from a non-null input string, and I'm not even sure that the
InputFunctionCall protocol allows it. (In fact a quick look indicates
that it doesn't...)
To me, what this throws into question is not so much whether JSON null
should equate to SQL NULL (it should), but whether it's sane to accept
atomic values. If I understood the beginning of this discussion, that's
not strictly legal. I think it would be better for strict input mode
to reject this, and permissive mode to convert it to a non-atomic value.
Thus jsonify('null') wouldn't yield NULL but a structure containing a
null.
regards, tom lane
Actually, I kind of made a zany mistake here. If 'null'::JSON yielded
NULL, that would mean some type of automatic conversion was going on.
Likewise, '3.14159'::JSON shouldn't magically turn into a FLOAT.
I think the JSON datatype should behave more like TEXT. 'null'::JSON
would yield a JSON fragment containing 'null'. 'null'::JSON::TEXT
would yield the literal text 'null'. However, '3.14159'::JSON::FLOAT
should probably not be allowed as a precaution, as
'hello'::JSON::TEXT would yield 'hello', not 'hello'. In other
words, casting to the target type directly isn't the same as parsing
JSON and extracting a value.
Perhaps there could be conversion functions. E.g.:
json_to_string('hello') yields 'hello'
json_to_number('3.14159') yields '3.14159' as text
(it is up to the user to cast it to the number type s/he wants)
json_to_bool('true') yields TRUE
json_to_null('null') yields NULL, json_null('nonsense') fails
string_to_json('hello') yields 'hello' as JSON
number_to_json(3.14159) yields '3.14159' as JSON
bool_to_json(TRUE) yields 'true' as JSON
null_to_json(NULL) yields 'null' as JSON (kinda useless)
I wonder if these could all be reduced to two generic functions, like
json_to_value and value_to_json.
--
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] Remaining Streaming Replication Open Items
On Tue, 2010-04-06 at 10:09 +0300, Heikki Linnakangas wrote: * Walsender and dblink are not interruptible on win32. - related thread I'd actually be happy to just leave it for 9.0, but it seems like consensus has been reached on how to fix it, and Fujii is working on a patch, so let's follow that through. That one is a must, for me. I would put relaying easily above any of the other stuff. That is a truly useful feature that we are very close to being able to have in this release. Adding things like quotes is not moving us forwards in any important sense. -- 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] SELECT constant; takes 15x longer on 9.0?
Josh, might you have got the numbers wrong when trying to match up query runtimes to their queries in the CSVLOG output? That might explain it. Most of the queries were BINDs. I swept up some DEALLOCATEs by acccident, but those don't appreciably affect the final numbers. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT constant; takes 15x longer on 9.0?
On Tue, Apr 6, 2010 at 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Pavel Stehule pavel.steh...@gmail.com writes: it is little bit offtopic. Can we add info about assertation to version() output? Greg has the right idea: show debug_assertions. why not the entire set of configure options? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Win32 timezone matching
When diagnosing a problem for a guy in the german channel (with Stefan as mediator :P), we've found a case where the timezone information in the registry seem to be empty for some timezones. The current timezone matching code will abort scanning when it comes across one of these, thus claiming it can't match the timezone, and reverting to GMT. We've seen some reports prevously that looked like this, but never really managed to get it diagnosed. Having checked the registry on this machine, it appears to simply be that Std and Dlt are missing from some entries. The attach patch changes our scan to skip to the next timezone when this happens, instead of aborting. The only downtime I can see from this is that in case there are a *lot* of broken timezones (like all of them), well log a lot of warnings. But it will only happen on server startup, so I think it's ok. Comments? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ win32_tzskip.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Missing win32 timezones
When debugging the other timezone issue, I've come across a bunch of timezones that are defined in Windows now (my check is 2003R2, which is the newest one I have readily available) that aren't in our list. This is because our list is based on Windows XP, and Microsoft have pushed timezone updates since. Attached patch updates the list of timezones. Any reason not to backpatch this? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ win32_missing_timezones.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT constant; takes 15x longer on 9.0?
Merlin Moncure mmonc...@gmail.com writes: On Tue, Apr 6, 2010 at 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg has the right idea: show debug_assertions. why not the entire set of configure options? We've discussed that before. pg_config already provides that info, and there was some concern about security risks of exposing it inside the database. (In particular, we currently go to some lengths to not expose any file path information to non-superusers.) If there's a reason to expose *individual* configuration options that aren't already easily checkable, we could discuss that. I would be against sticking it into version() output in any case. That function's already overloaded beyond any sane interpretation of its purpose, to the point where it's difficult to make use of the output programmatically. 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] Win32 timezone matching
Magnus Hagander mag...@hagander.net writes: When diagnosing a problem for a guy in the german channel (with Stefan as mediator :P), we've found a case where the timezone information in the registry seem to be empty for some timezones. The current timezone matching code will abort scanning when it comes across one of these, thus claiming it can't match the timezone, and reverting to GMT. We've seen some reports prevously that looked like this, but never really managed to get it diagnosed. Having checked the registry on this machine, it appears to simply be that Std and Dlt are missing from some entries. The attach patch changes our scan to skip to the next timezone when this happens, instead of aborting. The only downtime I can see from this is that in case there are a *lot* of broken timezones (like all of them), well log a lot of warnings. But it will only happen on server startup, so I think it's ok. I'm not clear on this. Would this patch fix a real seen-in-the-field condition, or is it speculative? In particular, if the loop had kept going in the complainant's machine, would it have found another entry that worked better? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_filedump strangeness
Hi, I'm chasing an apparent index corruption problem, and I came across something I can't quite explain in pg_filedump. Say I dump a non-leaf btree index page: *** * PostgreSQL File/Block Formatted Dump Utility - Version 8.3.0 * * File: 31141 * Options used: -fi -x -R 6246 * * Dump created on: Tue Apr 6 17:40:28 2010 *** Block 6246 Header - Block Offset: 0x030cc000 Offsets: Lower 36 (0x0024) Block: Size 8192 Version4Upper8120 (0x1fb8) LSN: logid 1077 recoff 0x45c8b660 Special 8176 (0x1ff0) Items:3 Free Space: 8084 TLI: 0x0001 Prune XID: 0x Flags: 0x () Length (including item array): 36 : 3504 60b6c845 0100 2400b81f 5...`..E$... 0010: f01f0420 d89f3000 d09f1000 ... ..0. 0020: b89f3000 ..0. Data -- Item 1 -- Length: 24 Offset: 8152 (0x1fd8) Flags: NORMAL Block Id: 6232 linp Index: 1 Size: 24 Has Nulls: 32768 Has Varwidths: 0 1fd8: 5818 01001880 0100 ..X. 1fe8: 80bcc57d 74230100...}t#.. Item 2 -- Length:8 Offset: 8144 (0x1fd0) Flags: NORMAL Block Id: 2756 linp Index: 1 Size: 8 Has Nulls: 0 Has Varwidths: 0 1fd0: c40a 01000800 Item 3 -- Length: 24 Offset: 8120 (0x1fb8) Flags: NORMAL Block Id: 6231 linp Index: 1 Size: 24 Has Nulls: 32768 Has Varwidths: 0 1fb8: 5718 01001880 0100 ..W. 1fc8: 4009cc7f 73230100@...s#.. Special Section - BTree Index Section: Flags: 0x () Blocks: Previous (6109) Next (6305) Level (1) CycleId (0) 1ff0: dd17 a118 0100 *** End of Requested Range Encountered. Last Block Read: 6246 *** Notice how item 2 has size 8, but regular entries have size 24. I know this is related to the high key of this page, but I can't quite figure out why the short entry is 2 not 1. Is item 2 just assumed to be greater than the previous' page high key? Page's 6109 high key is: Item 1 -- Length: 24 Offset: 8152 (0x1fd8) Flags: NORMAL Block Id: 6101 linp Index: 1 Size: 24 Has Nulls: 32768 Has Varwidths: 0 1fd8: d517 01001880 0100 1fe8: 8004f17d 6f230100...}o#.. Note that the data values are integer timestamp without time zone in little endian byte order. (The Has Nulls bit is somewhat bogus -- it displays 32768 when the 0x8000 bit is on, which is rather surprising. I'd expect it to display 1). -- 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] Win32 timezone matching
On Tue, Apr 6, 2010 at 23:44, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: When diagnosing a problem for a guy in the german channel (with Stefan as mediator :P), we've found a case where the timezone information in the registry seem to be empty for some timezones. The current timezone matching code will abort scanning when it comes across one of these, thus claiming it can't match the timezone, and reverting to GMT. We've seen some reports prevously that looked like this, but never really managed to get it diagnosed. Having checked the registry on this machine, it appears to simply be that Std and Dlt are missing from some entries. The attach patch changes our scan to skip to the next timezone when this happens, instead of aborting. The only downtime I can see from this is that in case there are a *lot* of broken timezones (like all of them), well log a lot of warnings. But it will only happen on server startup, so I think it's ok. I'm not clear on this. Would this patch fix a real seen-in-the-field condition, or is it speculative? In particular, if the loop had kept going in the complainant's machine, would it have found another entry that worked better? Real, seen-in-the-field. It would proceed and eventually find the guys Berlin timezone (Central European, which comes after Central Brazilian which is the one that was missing the entries). It does, however, turn out that the issue was fixed when he re-applied the latest timezone update hotfix from microsoft, which appears to rewrite that entire subkey. Not sure how well we can trust that though - it's not like we're working off a documented key... -- 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] Win32 timezone matching
Magnus Hagander mag...@hagander.net writes: On Tue, Apr 6, 2010 at 23:44, Tom Lane t...@sss.pgh.pa.us wrote: I'm not clear on this. Would this patch fix a real seen-in-the-field condition, or is it speculative? In particular, if the loop had kept going in the complainant's machine, would it have found another entry that worked better? Real, seen-in-the-field. It would proceed and eventually find the guys Berlin timezone (Central European, which comes after Central Brazilian which is the one that was missing the entries). Ah, of course. People who actually wanted the Central Brazilian zone are screwed, but they're screwed anyway, and there's no need to also screw people who want a zone that happens to come later in the list. +1 for the patch then. It does, however, turn out that the issue was fixed when he re-applied the latest timezone update hotfix from microsoft, which appears to rewrite that entire subkey. Not sure how well we can trust that though - it's not like we're working off a documented key... In any case, we might as well make things smoother for people working with unpatched systems, if it's such a small change. 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] Win32 timezone matching
Oh, another thought here: what is the effect of the combination of this with your other proposal to add more timezones to the list? In particular, what happens if we use the extended list in an unpatched system that doesn't know about those new zone names? I'm wondering if we *have* to make this change for that to behave sanely. We might also need to consider whether we want any log chatter in such a case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_filedump strangeness
Alvaro Herrera alvhe...@commandprompt.com writes: I'm chasing an apparent index corruption problem, and I came across something I can't quite explain in pg_filedump. Say I dump a non-leaf btree index page: I think this is actually OK. Remember that in a non-rightmost page, item 1 is the high key not a data entry. On the other hand, in a non-leaf page, we don't bother to store the key for the first downlink entry, since the associated key is really minus infinity. Cf nbtree/README: On a non-leaf page, the data items are down-links to child pages with bounding keys. The key in each data item is the *lower* bound for keys on that child page, so logically the key is to the left of that downlink. The high key (if present) is the upper bound for the last downlink. The first data item on each such page has no lower bound --- or lower bound of minus infinity, if you prefer. The comparison routines must treat it accordingly. The actual key stored in the item is irrelevant, and need not be stored at all. This arrangement corresponds to the fact that an LY non-leaf page has one more pointer than key. So item 2 doesn't have a key in it. The other two items have null keys, which means they need a null bitmap. I don't however understand why there seems to be data as well as a null bitmap in there --- is this perhaps a two-column index? (The Has Nulls bit is somewhat bogus -- it displays 32768 when the 0x8000 bit is on, which is rather surprising. I'd expect it to display 1). Yeah, I noticed that too. Made a note to myself to fix it in the next revision of pg_filedump, which I suppose I'd better get on with producing... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Set LC_COLLATE to de_DE_phoneb
How to set the collation of a database to the german phone book sort order? I did ask this on several places. Finally the moderator of pg-forum.de recommended to ask here. See the discussion there: http://www.pg-forum.de/konfiguration/4308-sortierfolge-de_de_phoneb.html Environment: PostgreSQL 8.4.3 build 1400, 32 Bit, Windows XP The usual german collation is German_Germany.1252. This corresponds to the windows language setting de_DE an in the registry (HKEY_CURRENT_USER \control\Panel\International\Locale) to the value 0407. The german phone book order has the windows language setting de_DE_phoneb an the value 00010407 in the registry. Unfortunately I was not able to find a corresponding string for the LC_COLLATE setting. I searched the sources of postgresql an found the function IsoLocaleName(...) in src/backend/utils/adt/pg_locale.c. I guess this should be the place for further investigations. Or am I wrong? I'm not quite well in C and without some knowledge of the libraries behind I make no progress. Can anyone help me out? Is there anywhere a documentation or a translation table for the different representations of the language settings between the postgresql- and the windows-world? Background: I moved an old application from a borland paradox database to postgesql. The speed gain is great but the sorting order isn't the usual to the user. I can't change the order by clauses of the select statements because they are generated by the borland database engine. Thanks in advance Frank Jagusch -- http://www.jagusch-online.de/cdlfj -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
