Re: [HACKERS] psql with GSS can crash
Magnus Hagander píše v čt 25. 02. 2010 v 15:17 +0100: On Thu, Feb 25, 2010 at 15:04, Zdenek Kotala zdenek.kot...@sun.com wrote: Hi all, I got following stack: fd7ffed14b70 strlen () + 40 fd7ffed71665 snprintf () + e5 fd7fff36d088 pg_GSS_startup () + 88 fd7fff36d43a pg_fe_sendauth () + 15a fd7fff36e557 PQconnectPoll () + 3b7 fd7fff36e152 connectDBComplete () + a2 fd7fff36dc32 PQsetdbLogin () + 1b2 0041e96d main () + 30d 0041302c () It seems that connection is not fully configured and krbsrvname or pghost is not filled. Following code in fe-auth.c pg_GSS_startup() causes a crash: 440 maxlen = NI_MAXHOST + strlen(conn-krbsrvname) + 2; 441 temp_gbuf.value = (char *) malloc(maxlen); 442 snprintf(temp_gbuf.value, maxlen, %...@%s, 443 conn-krbsrvname, conn-pghost); 444 temp_gbuf.length = strlen(temp_gbuf.value); And following code in fe-connect.c fillPGconn() fill NULL value. 571 tmp = conninfo_getval(connOptions, krbsrvname); 572 conn-krbsrvname = tmp ? strdup(tmp) : NULL; I think that pg_GSS_startup should sanity the input. How did you get NULL in there? :-) There's a default set for that one that's PG_KRB_SRVNAM, so it really should never come out as NULL, I think... Yeah, you are right. conn-krbsrvname is postgres and conn-pghost is null As for pghost, that certainly seems to be a bug. We check that one in krb5 and SSPI, but for some reason we seem to be missing it in GSSAPI. Yes. The check should be in GSSAPI too. However what I see in pg_hba.conf is following line: local all all gss Gss is used on local unix socket which probably cause a problem that conn-pghost is not filled when psql tries to connect. thanks Zdenek Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] C libpq frontend library fetchsize
Yeb Havinga yebhavi...@gmail.com wrote: I'm wondering if there would be community support for adding using the execute message with a rownum 0 in the c libpq client library, as it is used by the jdbc driver with setFetchSize. The setFetchSize for libpq is difficult because of the interface mismatch -- libpq uses array-based APIs (PGresult) and JDBC uses a cursor-like API (ResultSet). Instead, you can use CURSOR and FETCH commands to retrieve rows in separated PGresult objects. 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] Testing of parallel restore with current snapshot
On 27/02/2010 07:52, Gokulakannan Somasundaram wrote: Tom, I just took the patch, but it seems to be in binary format. Can you send me the patch to me? gunzip shuould do the trick Thanks, Gokul. On Sat, May 30, 2009 at 3:12 AM, Tom Lanet...@sss.pgh.pa.us wrote: Josh Berkusj...@agliodbs.com writes: Tom, Is anyone interested enough to try it if I code it? If you're patient for results, sure. I seem to be doing a customer migration or upgrade every week now, so it wouldn't take me long to have a test subject with a fairly complex database. -- 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] C libpq frontend library fetchsize
Takahiro Itagaki wrote: Yeb Havinga yebhavi...@gmail.com wrote I'm wondering if there would be community support for adding using the execute message with a rownum 0 in the c libpq client library, as it is used by the jdbc driver with setFetchSize. The setFetchSize for libpq is difficult because of the interface mismatch -- libpq uses array-based APIs (PGresult) and JDBC uses a cursor-like API (ResultSet). Instead, you can use CURSOR and FETCH commands to retrieve rows in separated PGresult objects. Hello Takahiro, Thank you for your reply. Yes there is a large overlap with SQL's declare cursor together with fetch, however intuitively it feels strange that the application needs to be changed for something that could be kept internal to the communication protocol. The application is forced to start an application explicitly, and also I tried to declare a cursor with parameters from sql but did not succeed, nor could I declare a cursors in combination with a prepared statement. Building fetchsize into libpq seems like a general solution that could work for all pgresult fetching. Together with a college of mine I worked on this subject some time ago, and we thought it might be interesting enough for others as well. In short this is what we did: - add a boolean 'complete' to the PGresult object - to indicate if the portalrun was complete - add PQresultComplete api call that returns the boolean above. - add PQsendSync - in the query guts routines, do not send sync messages - call pqsendsync when a complete pqresult is received, or if result fetching is stopped before fetching the last result. - the fetchsize was a defined constant in our application, but would be trivial to replace with a PQsetFetchsize. There are some intricacies with reading with libpq with an asynchronous event handler. One was that parseinput does not 'eat' the whole message buffer, i.e. there can be another complete message in the buffer but a new read event might not be triggered because no more data arrives into the buffer. For this purpose we also added - PQmsgAvail - returns true if the event handler may fire again immediately (to process more results) The biggest challenge was when to send sync messages. We have that covered currently but it is done by our application outside of libpq (hence the pqsendsync visible in the api as well as pqresultcomplete). It would be better if that all could be kept inside libpq itself. In the end we believe this could provide useful functions for callers of the libpq api such as PHP. 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] [GENERAL] trouble with to_char('L')
Bruce Momjian wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Where are we on this issue? Oops I forgot it completely. I have a little improved version and would post it tonight. Ah, very good. Thanks. Attached is an improved version. I spent many hours on this patch and am attaching an updated version. I have restructured the code and added many comments, but this is the main one: * Ideally, the server encoding and locale settings would * always match. Unfortunately, WIN32 does not support UTF-8 * values for setlocale(), even though PostgreSQL runs fine with * a UTF-8 encoding on Windows: * * http://msdn.microsoft.com/en-us/library/x99tb11d.aspx * * Therefore, we must set LC_CTYPE to match LC_NUMERIC and * LC_MONETARY, call localeconv(), and use mbstowcs() to * convert the locale-aware string, e.g. Euro symbol, which * is not in UTF-8 to the server encoding. I need someone with WIN32 experience to review and test this patch. I don't understand why cache_locale_time() works on Windows. It sets the LC_CTYPE but does not do any encoding coversion. Doesn't strftime_win32 do the conversion? Do month and day-of-week names not work either, or do they work and the encoding conversion for numeric/money, e.g. Euro, it not necessary? db_strdup does the conversion. regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Polyplanner (was Re: [HACKERS] Avoiding bad prepared-statement plans.)
How about a totally different approach? What if all queries and plans of all queries, simple and prepared, were pre-planned and cached always, persistent? For prepared statements with = 1 parameters, histogram and mcv information could be used to search the plan space for interesting plans. Maybe with some heuristics to cut down on search space (i.e. when operator is '=' and there is a unique index, skip that clause / parameter from the search space). Since processors keep getting more and more cores, and most database activity is IO bound, why not keep one core busy with query analysis? good: - with the several hooks available it could be implemented as optional contrib - if offers plan stability - nice info for management user interface - might be a solution for prepared queries - for queries with large joins, plans might be considered with exhaustive search, so also here there could be an improvement. - it might even be possible to 'test' plans during low-usage hours bad: - unknown how big space for cached plans should be - if big cached plan space doesn't fit in memory, actual planning probably better than fetching from disk, ~= 5 to 10ms. 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] Linux start script updates
On tor, 2009-08-20 at 10:31 -0500, Kevin Grittner wrote: (2) It doesn't exit with zero for a missing executable unless the request is stop. It uses 5, which means program is not installed. Using 5 is correct, but special-casing stop is kind of useless. Every other init script I have ever seen that attempts to handle this, doesn't bother. -- 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On mån, 2010-02-22 at 10:32 -0500, Bruce Momjian wrote: Simon Riggs wrote: On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Is there a good reason for $subject, other than that the code is entangled with other ALTER TABLE code? I think it could be lower, but it would take nontrivial restructuring of the ALTER TABLE support. In particular, consider what happens when you have a list of subcommands that don't all require the same lock level. I think you'd need to scan the list and find the highest required lock level before starting ... IIRC there was a patch from Simon to address this issue, but it had some holes which he didn't have time to close, so it sank. Maybe this can be resurrected and fixed. I was intending to finish that patch in this release cycle. Since you're busy with Hot Standby, any chance you could pass it on? If you'd like. It's mostly finished, just one last thing to finish: atomic changes to pg_class via an already agreed API. I assume this did not get done for 9.0. Do we want a TODO item? Yes. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening
This isn't working. The Windows ports are all saying permission denied but apparently that's not because errno is set to EPERM. Anyone know how to detect permission denied errors from open() on windows? On Mon, Mar 1, 2010 at 12:04 AM, Greg Stark st...@postgresql.org wrote: Log Message: --- add EPERM to the list of return codes to expect from opening directories based on Vista results Modified Files: -- pgsql/src/port: copydir.c (r1.34 - r1.35) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/port/copydir.c?r1=1.34r2=1.35) -- Sent via pgsql-committers mailing list (pgsql-committ...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers -- greg -- 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: pgsql: add EPERM to the list of return codes to expect from opening
You might want to look at _dosmaperror() in src/port/win32error.c - it contains the different win32 error ccodes that we match to EACCESS. I don't see us mapping *anything* to EPERM. Actually, I think that may be your problem - you are testing against EPERM instead of EACCESS. On my linux manpage, EPERM isn't even a valid return code from open(). //Magnus 2010/3/1 Greg Stark gsst...@mit.edu: This isn't working. The Windows ports are all saying permission denied but apparently that's not because errno is set to EPERM. Anyone know how to detect permission denied errors from open() on windows? On Mon, Mar 1, 2010 at 12:04 AM, Greg Stark st...@postgresql.org wrote: Log Message: --- add EPERM to the list of return codes to expect from opening directories based on Vista results Modified Files: -- pgsql/src/port: copydir.c (r1.34 - r1.35) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/port/copydir.c?r1=1.34r2=1.35) -- Sent via pgsql-committers mailing list (pgsql-committ...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] remove contrib/xml2
Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Tom Lane wrote:
... The reason for that behavior is that xpath_table runs through
the XPATH_NODESET results generated by the various XPaths and dumps the
k'th one of each into the k'th output row generated for the current
input row.
ISTM the missing piece is really in our API. We need to be able to
specify a nodeset to iterate over, and then for each node take the first
value produced by each xpath expression. So the example above would look
something like:
SELECT * FROM xpath_table('id', 't', 'xpath_test',
'/rowlist/row', '@a|@b', 'true') as t(id int4, a text, b text);
Hm. It seems like that still leaves you open to the possibility of
out-of-sync results. If you consider the current behavior as what
you'd get with an empty root nodeset spec, then restricting it to
produce only the first output row doesn't help at all -- it would still
associate 1 with oops. In general if the nodeset spec doesn't
select a unique subnode then you're at risk of bogus answers.
Maybe that could be defined as user error but it sure seems like it
would be error-prone to use.
Well, I think that's going to be hard or impossible to avoid in the
general case. My suggestion was intended to give the user a much better
chance of avoiding it, however.
Arbitrary XML (or JSON or YAML or any artbitrarilly tree structured data
markup) doesn't map well to a rectangular structure, and this is always
likely to cause problems like this IMO.
I guess in the end the user could preprocess the XML with XSLT to remove
the irregularities before passing to to xpath_table.
We certainly need to put some warnings in the docs about it.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening
Magnus Hagander mag...@hagander.net writes: Actually, I think that may be your problem - you are testing against EPERM instead of EACCESS. On my linux manpage, EPERM isn't even a valid return code from open(). Yeah, I had just come to the same conclusion upon seeing the buildfarm still pink this morning. On my old HPUX box these codes are defined as /usr/include/sys/errno.h:#defineEPERM 1 /* Not super-user */ /usr/include/sys/errno.h:#defineEACCES 13 /* Permission denied*/ so EPERM seems like the wrong mapping for such an error anyway. BTW, in case anyone with admin privileges is paying attention, the buildfarm (a) is about two hours off on its system clock again, and (b) hasn't sent out a daily status-change summary email since Friday. 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] Linux start script updates
Peter Eisentraut pete...@gmx.net wrote: On tor, 2009-08-20 at 10:31 -0500, Kevin Grittner wrote: (2) It doesn't exit with zero for a missing executable unless the request is stop. It uses 5, which means program is not installed. Using 5 is correct, but special-casing stop is kind of useless. Every other init script I have ever seen that attempts to handle this, doesn't bother. I can't see a clear case either way. I know I *have* seen scripts which took the trouble to special-case it, but I just poked around and found that it seems much less common than unconditionally using exit 5. Does anyone know of an environment where it matters? -Kevin -- 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: pgsql: add EPERM to the list of return codes to expect from opening
So fwiw Narwhal says EACCESS is working. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] contrib/xml2 regression tests vs no-libxslt build option
In yet another demonstration that no good deed goes unpunished, I see that my addition of regression tests to contrib/xml2 is still a few bricks shy of a load. Buildfarm member pika is failing on it, and the reason is clear upon inspection: pika is configured --with-libxml but not --with-libxslt, so the xslt_process calls fail. The obvious thing to do about it is add a variant expected file, but even that won't quite fix things, because of this part of the diff: SET client_min_messages = warning; \set ECHO none + psql:pgxml.sql:79: ERROR: could not find function xslt_process in file /home/pgbuildfarm/workdir/HEAD/inst/lib/postgresql/pgxml.so + psql:pgxml.sql:86: ERROR: could not find function xslt_process in file /home/pgbuildfarm/workdir/HEAD/inst/lib/postgresql/pgxml.so RESET client_min_messages; There's no way for a variant file to deal with the installation-specific file path in those messages. I could crank the SET client_min_messages up to PANIC to hide those messages, but that risks losing important information when an unexpected failure happens. So it seems that the only really viable fix is to rearrange the code so that when libxslt isn't available, the xslt_process() function is still defined and available to CREATE FUNCTION; we can make it throw a runtime error instead of just not being present. This seems like a good idea anyway to make the module's ABI more stable: right now, if you dump and restore from an installation with xslt support to one without, or vice versa, you'll have problems with either missing or unloadable SQL function definitions. More work than I had really wanted to spend on xml2, but the alternative is backing out those regression tests :-( 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] psql with GSS can crash
2010/3/1 Zdenek Kotala zdenek.kot...@sun.com: Magnus Hagander píše v čt 25. 02. 2010 v 15:17 +0100: On Thu, Feb 25, 2010 at 15:04, Zdenek Kotala zdenek.kot...@sun.com wrote: Hi all, I got following stack: fd7ffed14b70 strlen () + 40 fd7ffed71665 snprintf () + e5 fd7fff36d088 pg_GSS_startup () + 88 fd7fff36d43a pg_fe_sendauth () + 15a fd7fff36e557 PQconnectPoll () + 3b7 fd7fff36e152 connectDBComplete () + a2 fd7fff36dc32 PQsetdbLogin () + 1b2 0041e96d main () + 30d 0041302c () It seems that connection is not fully configured and krbsrvname or pghost is not filled. Following code in fe-auth.c pg_GSS_startup() causes a crash: 440 maxlen = NI_MAXHOST + strlen(conn-krbsrvname) + 2; 441 temp_gbuf.value = (char *) malloc(maxlen); 442 snprintf(temp_gbuf.value, maxlen, %...@%s, 443 conn-krbsrvname, conn-pghost); 444 temp_gbuf.length = strlen(temp_gbuf.value); And following code in fe-connect.c fillPGconn() fill NULL value. 571 tmp = conninfo_getval(connOptions, krbsrvname); 572 conn-krbsrvname = tmp ? strdup(tmp) : NULL; I think that pg_GSS_startup should sanity the input. How did you get NULL in there? :-) There's a default set for that one that's PG_KRB_SRVNAM, so it really should never come out as NULL, I think... Yeah, you are right. conn-krbsrvname is postgres and conn-pghost is null Ah, good. We should defentd against that then. As for pghost, that certainly seems to be a bug. We check that one in krb5 and SSPI, but for some reason we seem to be missing it in GSSAPI. Yes. The check should be in GSSAPI too. However what I see in pg_hba.conf is following line: local all all gss Gss is used on local unix socket which probably cause a problem that conn-pghost is not filled when psql tries to connect. So there are really two errors - because we should disallow that. See attached patch - can you confirm it removes the crash with just the client side applied, and then that it properly rejects GSS with the server side applied as well? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ gss_nohost.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] contrib/xml2 regression tests vs no-libxslt build option
On Mon, Mar 1, 2010 at 10:32 AM, Tom Lane t...@sss.pgh.pa.us wrote: In yet another demonstration that no good deed goes unpunished, I see that my addition of regression tests to contrib/xml2 is still a few bricks shy of a load. Buildfarm member pika is failing on it, and the reason is clear upon inspection: pika is configured --with-libxml but not --with-libxslt, so the xslt_process calls fail. I thought the contrib makefile was set up to ignore xml2 if libxslt wasn't being used. ...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] contrib/xml2 regression tests vs no-libxslt build option
Robert Haas robertmh...@gmail.com writes: I thought the contrib makefile was set up to ignore xml2 if libxslt wasn't being used. No, it ignores it if libxml2 isn't available. It can be built with or without libxslt though. 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: pgsql: add EPERM to the list of return codes to expect from opening
On Mon, Mar 1, 2010 at 10:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, in case anyone with admin privileges is paying attention, the buildfarm (a) is about two hours off on its system clock again, and (b) hasn't sent out a daily status-change summary email since Friday. Eh? The buildfarm sends out a daily status-change summary email? To where? ...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] Linux start script updates
Kevin Grittner kevin.gritt...@wicourts.gov writes: I can't see a clear case either way. I know I *have* seen scripts which took the trouble to special-case it, but I just poked around and found that it seems much less common than unconditionally using exit 5. Does anyone know of an environment where it matters? Probably not. You might find it entertaining to read the current Fedora guidelines for init scripts: https://fedoraproject.org/wiki/Packaging:SysVInitScript The skeleton shown there only bothers to throw exit 5 when the program is missing at start time. I think though that the answer to Peter's question is that stop has to be special cased to some extent, because it is not supposed to be an error to stop a service that's not running. If it's not even installed, then a fortiori it's not running, so the exit code *must* be 0 not 5 in that case. I've even been told that you should get 0 if you run service foo stop on a non-running service as a non-superuser, ie, a case where you *would* get a failure (no permissions) if the service were running. I'm not sure I believe that last bit myself, but Red Hat has got some test scripts that think this. 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] contrib/xml2 regression tests vs no-libxslt build option
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I thought the contrib makefile was set up to ignore xml2 if libxslt wasn't being used. No, it ignores it if libxml2 isn't available. It can be built with or without libxslt though. ugh. Maybe we need to set up a dummy function or two if not building with xslt, like we do with the XML functions if not building with libxml. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening
Robert Haas wrote: On Mon, Mar 1, 2010 at 10:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, in case anyone with admin privileges is paying attention, the buildfarm (a) is about two hours off on its system clock again, and (b) hasn't sent out a daily status-change summary email since Friday. Eh? The buildfarm sends out a daily status-change summary email? To where? To subscribers of the relevant mailing lists. See http://pgfoundry.org/mail/?group_id=140 cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening
Robert Haas robertmh...@gmail.com writes: On Mon, Mar 1, 2010 at 10:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, in case anyone with admin privileges is paying attention, the buildfarm (a) is about two hours off on its system clock again, and (b) hasn't sent out a daily status-change summary email since Friday. Eh? The buildfarm sends out a daily status-change summary email? To where? See http://pgfoundry.org/mail/?group_id=140 I'm subscribed to pgbuildfarm-status-green ... and the archives for it match my local log, which says there hasn't been a message since Friday. That's definitely not for lack of changes. Now that I look at the archives, it looks like all the status lists stopped getting mail around that time. 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] contrib/xml2 regression tests vs no-libxslt build option
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: No, it ignores it if libxml2 isn't available. It can be built with or without libxslt though. ugh. Maybe we need to set up a dummy function or two if not building with xslt, like we do with the XML functions if not building with libxml. Right, that was exactly my proposal. The function should be there always, but throw a run-time error if no xslt support. 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: pgsql: add EPERM to the list of return codes to expect from opening
Tom Lane wrote: Eh? The buildfarm sends out a daily status-change summary email? To where? See http://pgfoundry.org/mail/?group_id=140 I'm subscribed to pgbuildfarm-status-green ... and the archives for it match my local log, which says there hasn't been a message since Friday. That's definitely not for lack of changes. Now that I look at the archives, it looks like all the status lists stopped getting mail around that time. There is a DNS failure on the server causing a huge backlog of status messages. I have sent a message to the admins list about it. (I can see the list of errors on the machine by running mailq, but I can't do anything about it.) 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] Linux start script updates
Tom Lane t...@sss.pgh.pa.us wrote: I think though that the answer to Peter's question is that stop has to be special cased to some extent, because it is not supposed to be an error to stop a service that's not running. If it's not even installed, then a fortiori it's not running, so the exit code *must* be 0 not 5 in that case. Exactly. With Fedora respecting the standard in this regard, I'm convinced we should, too. In reviewing things based on Peter's question, I did start to have doubts about *not* special-casing status -- it has its own set of values and 5 is not assigned, so using it seems wrong. It seems like it should be 3 (program is not running). Agreed? -Kevin -- 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] Linux start script updates
Kevin Grittner kevin.gritt...@wicourts.gov writes: Exactly. With Fedora respecting the standard in this regard, I'm convinced we should, too. In reviewing things based on Peter's question, I did start to have doubts about *not* special-casing status -- it has its own set of values and 5 is not assigned, so using it seems wrong. It seems like it should be 3 (program is not running). Agreed? Probably. I think that in practice most scripts are not very tense about this --- as long as the exit code is 0 or not-0 per spec, which not-0 value is reported is not so exciting to most people. 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: Hot Standby query cancellation and Streaming Replication integration
On 2/28/10 7:00 PM, Greg Smith wrote: The main problem with setting vacuum_defer_cleanup_age high isn't showing it works, it's a pretty simple bit of code. It's when you recognize that it penalizes all cleanup all the time, whether or not the standby is actually executing a long-running query or not, that you note the second level of pain in increasing it. Returning to the idea of how is this different from a site already in production?, it may very well be the case that a site that sets vacuum_defer_cleanup_age high enough to support off-peak batch reporting cannot tolerate how that will impact vacuums during their peak time of day. The XID export implementation sidesteps that issue by only making the vacuum delay increase when queries that require it are running, turning this back into a standard what's the best time of day to run my big reports? issue that people understand how to cope with already. I don't think that defer_cleanup_age is a long-term solution. But we need *a* solution which does not involve delaying 9.0. And I think we can measure bloat in a pgbench test, no? When I get a chance, I'll run one for a couple hours and see the difference that cleanup_age makes. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: And I think we can measure bloat in a pgbench test, no? When I get a chance, I'll run one for a couple hours and see the difference that cleanup_age makes. The test case I attached at the start of this thread runs just the UPDATE to the tellers table. Running something similar that focuses just on UPDATEs to the pgbench_accounts table, without the rest of the steps done by the standard test, is the fastest route to bloat. The standard test will do it too, just does a lot of extra stuff too that doesn't impact results (SELECT, INSERT) so it wastes some resources compared to a targeted bloater script. -- 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] Re: pgsql: add EPERM to the list of return codes to expect from opening
Greg Stark wrote: So fwiw Narwhal says EACCESS is working. dawn_bat is also working. Both of these build using Mingw/gcc, not MSVC. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Make plpgsql throw error for SELECT ... INTO rowtypevar , ... ?
Anybody have an opinion about whether to try to improve the error response exhibited in bug #5352? http://archives.postgresql.org/message-id/201003010922.o219m9lk016...@wwwmaster.postgresql.org Currently, if the first variable named after INTO is a rowtype variable, we just stop parsing the INTO clause right there. Bug #5352 is not the first case we've seen of people expecting to be able to write additional INTO targets after that. While I'm not interested right now in trying to define or implement what it would mean to do that, it would be a pretty trivial change to look ahead for a comma, and if one is seen to throw an error along the line of INTO can have only one target variable if the target is a row or record variable. It seems just barely possible that this could break functions that work now, in which the INTO clause is located just ahead of a comma that does actually belong to the surrounding SELECT's syntax. However the user could always work around it by relocating the INTO clause to someplace else --- like say the places that we recommend putting INTO. Aside from giving a less confusing message, making this change would help to forestall future compatibility problems when and if we do generalize INTO to accept multiple targets in such cases. If we've been throwing an error for that syntax for a release or three, it'll be much less likely to bite people in the rear when it suddenly starts doing something different. So I'm inclined to make the change, but only in HEAD --- if there is anyone whose function gets broken, they'd want to see that happen in a major release not a minor update. Comments? 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] Re: Hot Standby query cancellation and Streaming Replication integration
On Mon, Mar 1, 2010 at 5:50 PM, Josh Berkus j...@agliodbs.com wrote: I don't think that defer_cleanup_age is a long-term solution. But we need *a* solution which does not involve delaying 9.0. So I think the primary solution currently is to raise max_standby_age. However there is a concern with max_standby_age. If you set it to, say, 300s. Then run a 300s query on the slave which causes the slave to fall 299s behind. Now you start a new query on the slave -- it gets a snapshot based on the point in time that the slave is currently at. If it hits a conflict it will only have 1s to finish before the conflict causes the query to be cancelled. In short in the current setup I think there is no safe value of max_standby_age which will prevent query cancellations short of -1. If the slave has a constant stream of queries and always has at least one concurrent query running then it's possible that the slave will run continuously max_standby_age-epsilon behind the master and cancel queries left and right, regardless of how large max_standby_age is. To resolve this I think you would have to introduce some chance for the slave to catch up. Something like refusing to use a snapshot older than max_standby_age/2 and instead wait until the existing queries finish and the slave gets a chance to catch up and see a more recent snapshot. The problem is that this would result in very unpredictable and variable response times from the slave. A single long-lived query could cause replay to pause for a big chunk of max_standby_age and prevent any new query from starting. Does anyone see any way to guarantee that the slave gets a chance to replay and new snapshots will become visible without freezing out new queries for extended periods of time? -- greg -- 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] Anyone know if Alvaro is OK?
Marc G. Fournier wrote: Is there a higher then normal amount of earthquakes happening recently? haiti, japan just had one for 6.9, there was apparently one in illinos a few weeks back, one on the Russia/China/N.Korean border and now Chile? Random events come in bunches - something I always stop to remind myself of whenever there is a sudden bunch of quakes, celebrity deaths, plane crashes, etc. Especially with relatively unusual events like great-quakes and plane crashes, it can be tough to see if there is any signal in the noise - a job I have to leave to experienced statisticians. The world averages one great (8+) earthquake/year which, of course, means some years like 2008 have none but 2007 had four. 7-7.9 like Haiti or our own Loma Prieta quake are far more common averaging ~17/year. Haiti is a catastrophe not because the quake was of unusual size (it barely made it into the 7-7.9 category and released less that 1/15 the energy of the Chile quake) but because the hypocenter was both shallow and fairly close to Port-au-Prince combined with terrible construction standards and virtually non-existent emergency-response capabilities in Haiti. Some general quake stats/facts are here: http://earthquake.usgs.gov/earthquakes/eqarchives/year/eqstats.php Cheers, Steve -- 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: Hot Standby query cancellation and Streaming Replication integration
So I think the primary solution currently is to raise max_standby_age. However there is a concern with max_standby_age. If you set it to, say, 300s. Then run a 300s query on the slave which causes the slave to fall 299s behind. Now you start a new query on the slave -- it gets a snapshot based on the point in time that the slave is currently at. If it hits a conflict it will only have 1s to finish before the conflict causes the query to be cancelled. Completely aside from that, how many users are going to be happy with a slave server which is constantly 5 minutes behind? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make plpgsql throw error for SELECT ... INTO rowtypevar , ... ?
On Mon, Mar 1, 2010 at 1:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Anybody have an opinion about whether to try to improve the error response exhibited in bug #5352? http://archives.postgresql.org/message-id/201003010922.o219m9lk016...@wwwmaster.postgresql.org Currently, if the first variable named after INTO is a rowtype variable, we just stop parsing the INTO clause right there. Bug #5352 is not the first case we've seen of people expecting to be able to write additional INTO targets after that. While I'm not interested right now in trying to define or implement what it would mean to do that, it would be a pretty trivial change to look ahead for a comma, and if one is seen to throw an error along the line of INTO can have only one target variable if the target is a row or record variable. It seems just barely possible that this could break functions that work now, in which the INTO clause is located just ahead of a comma that does actually belong to the surrounding SELECT's syntax. However the user could always work around it by relocating the INTO clause to someplace else --- like say the places that we recommend putting INTO. Aside from giving a less confusing message, making this change would help to forestall future compatibility problems when and if we do generalize INTO to accept multiple targets in such cases. If we've been throwing an error for that syntax for a release or three, it'll be much less likely to bite people in the rear when it suddenly starts doing something different. So I'm inclined to make the change, but only in HEAD --- if there is anyone whose function gets broken, they'd want to see that happen in a major release not a minor update. Comments? It seems like a reasonable thing to do, I guess. The whole idea that INTO can be placed absolutely anywhere is really pretty strange, and this is only chipping away at the edges of the weirdness. portal=# create or replace function f() returns integer as $$declare x record; begin select 1 into x + 1 as v; return x.v; end $$ language plpgsql; CREATE FUNCTION portal=# select f(); f --- 2 (1 row) ...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: Hot Standby query cancellation and Streaming Replication integration
On Mon, Mar 1, 2010 at 7:21 PM, Josh Berkus j...@agliodbs.com wrote: Completely aside from that, how many users are going to be happy with a slave server which is constantly 5 minutes behind? Uhm, well all the ones who are happy with our current warm standby setup for one? And all the ones who are looking for a standby reporting server rather than a high availability DR site. For what it's worth Oracle has an option to have your standby intentionally hold back n minutes behind and I've seen that set to 5 minutes. -- greg -- 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: Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: On Mon, Mar 1, 2010 at 7:21 PM, Josh Berkus j...@agliodbs.com wrote: Completely aside from that, how many users are going to be happy with a slave server which is constantly 5 minutes behind? Uhm, well all the ones who are happy with our current warm standby setup for one? And all the ones who are looking for a standby reporting server rather than a high availability DR site. For what it's worth Oracle has an option to have your standby intentionally hold back n minutes behind and I've seen that set to 5 minutes. yeah a lot of people are doing that intentionally... Stefan -- 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: Hot Standby query cancellation and Streaming Replication integration
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: Greg Stark wrote: For what it's worth Oracle has an option to have your standby intentionally hold back n minutes behind and I've seen that set to 5 minutes. yeah a lot of people are doing that intentionally... It's the old DBA screwup safety valve ... drop the main accounts table, you have five minutes to stop replication before it's dropped on the standby. Speaking of which, does the current HS+SR code have a provision to force the standby to stop tracking WAL and come up live, even when there's more WAL available? Because that's what you'd need in order for such a thing to be helpful in that scenario. 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] USE_LIBXSLT in MSVC builds
BTW, it looks like the MSVC build scripts don't bother to make sure that USE_LIBXSLT is defined (or not) correctly --- at least I can't see any reference to that symbol in the Windows-specific files. This is now necessary to avoid disabling the xslt functionality in contrib/xml2. I'm not too sure what to poke there, any help? 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] function side effects
On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: There may be some value in inventing a has no side effects marker, but that should not be confused with IMMUTABLE/STABLE. a READONLY function? SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Notice also that this is separate from deterministic characteristic ::= DETERMINISTIC | NOT DETERMINISTIC which is the SQL standard's variant of volatility. So someone has already had the idea that these two should exist separately. -- 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: Hot Standby query cancellation and Streaming Replication integration
On 3/1/10 11:43 AM, Tom Lane wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: Greg Stark wrote: For what it's worth Oracle has an option to have your standby intentionally hold back n minutes behind and I've seen that set to 5 minutes. yeah a lot of people are doing that intentionally... It's the old DBA screwup safety valve ... drop the main accounts table, you have five minutes to stop replication before it's dropped on the standby. Speaking of which, does the current HS+SR code have a provision to force the standby to stop tracking WAL and come up live, even when there's more WAL available? Because that's what you'd need in order for such a thing to be helpful in that scenario. the fast recovery option should do this. You'd need some fast reaction times, though. However, this leaves aside Greg's point about snapshot age and successive queries; does anyone dispute his analysis? Simon? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries
On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote: * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope so I understand SQL/PSM well so I am able to write production quality implementation. If you like, I can integrate it to core. It can share about 40-50% code with plpgpsm. The behave of plpgpsm is same as plpgsql - without some plpgsql's historical issues (about FOUND, about NULL and record type). SQL/PSM is litlle bit richer language. Now we have not any wide used runtime so I don't thinking about rewriting. Maybe we can rewrite these PL language for parrot or lua runtime in future. But this step isn't necessary - people hasn't performance problems with PL based on PL runtime. While having a cleaner variant of PL/pgSQL available might be desirable for some (but compare discussion on plpython3), given that you label this SQL/PSM, I suppose you are also working on this from a standards-compliance perspective. According to my reading, the part of the SQL standard that is named SQL/PSM does not, however, describe a procedural language in the PostgreSQL sense of the term. It describes server-side modules and an extension to the SQL language (that is, it is activated by CREATE FUNCTION ... LANGUAGE SQL). It remains to be decided which parts of these are ultimately useful and desirable, but I suggest that there be some discussion on the exact strategy in this area first, lest we end up with a plpgsql3. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] double and numeric conversion
Hello all, I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value? Best regards, Theo -- Theo Schlossnagle http://omniti.com/is/theo-schlossnagle -- 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] function side effects
On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: There may be some value in inventing a has no side effects marker, but that should not be confused with IMMUTABLE/STABLE. a READONLY function? SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA good! Notice also that this is separate from deterministic characteristic ::= DETERMINISTIC | NOT DETERMINISTIC so IMMUTABLE = DETERMINISTIC NO SQL, STABLE = DETERMINISTIC READS SQL DATA VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA which is the SQL standard's variant of volatility. So someone has already had the idea that these two should exist separately. seems something we should implement -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries
2010/3/1 Peter Eisentraut pete...@gmx.net: On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote: * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope so I understand SQL/PSM well so I am able to write production quality implementation. If you like, I can integrate it to core. It can share about 40-50% code with plpgpsm. The behave of plpgpsm is same as plpgsql - without some plpgsql's historical issues (about FOUND, about NULL and record type). SQL/PSM is litlle bit richer language. Now we have not any wide used runtime so I don't thinking about rewriting. Maybe we can rewrite these PL language for parrot or lua runtime in future. But this step isn't necessary - people hasn't performance problems with PL based on PL runtime. While having a cleaner variant of PL/pgSQL available might be desirable for some (but compare discussion on plpython3), given that you label this SQL/PSM, I suppose you are also working on this from a standards-compliance perspective. According to my reading, the part of the SQL standard that is named SQL/PSM does not, however, describe a procedural language in the PostgreSQL sense of the term. It describes server-side modules and an extension to the SQL language (that is, it is activated by CREATE FUNCTION ... LANGUAGE SQL). It remains to be decided which parts of these are ultimately useful and desirable, but I suggest that there be some discussion on the exact strategy in this area first, lest we end up with a plpgsql3. I invite any discussion. I hope so my implementation will be clean and fast. Still I am learning this language and have to understand to core. I afraid so implementation SQL/PSM will be little bit modified - postgresql use little bit different concept of warnings, and mainly we have not procedures. So there will be some new limits. My goal is creating some prototype now. I am sure so we can better integrate PL to main parser - but it hasn't be a first step. Pavel -- 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] double and numeric conversion
Theo Schlossnagle wrote: Hello all, I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value? float8_numeric() ? Although it uses sprintf too, by the look of it. 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] function side effects
Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? Seems like that would have to be the same as the last one, or maybe the next-to-last one if you're prepared to assume it's read-only SQL. 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] double and numeric conversion
Theo Schlossnagle je...@omniti.com writes: I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value? If you're worried about micro-optimization, why are you using NUMERIC at all? It's no speed demon. Although you might be able to shave some cycles with a dedicated code path for this conversion, binary to decimal is fundamentally not cheap. 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] function side effects
Jaime Casanova írta: On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: There may be some value in inventing a has no side effects marker, but that should not be confused with IMMUTABLE/STABLE. a READONLY function? SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA good! Notice also that this is separate from deterministic characteristic ::= DETERMINISTIC | NOT DETERMINISTIC so IMMUTABLE = DETERMINISTIC NO SQL, STABLE = DETERMINISTIC READS SQL DATA VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA which is the SQL standard's variant of volatility. So someone has already had the idea that these two should exist separately. seems something we should implement At least the combinations to recognize the current IMMUTABLE/STABLE/VOLATILE features. By definition, READS SQL DATA and MODIFIES SQL DATA cannot be DETERMINISTIC. But I can imagine some C and PL/Perl functions that are NOT DETERMINISTIC NO SQL. And what does CONTAINS SQL mean? Is it distinct from the other two READS/MODIFIES SQL DATA markers? SELECT CURRENT_TIMESTAMP may be an example but it doesn't seem to be significantly different from $$SELECT $1 || $2;$$ LANGUAGE SQL or the same written in PL/Perl or C. 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] scheduler in core
On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? See also: http://www.postgresql.org/docs/current/static/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS ...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] scheduler in core
2010/3/1 Robert Haas robertmh...@gmail.com: On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? I thing so it is very hard restriction based on using and architecture of our SPI interface. Our stored procedures are executed inside one SELECT statement - it is reason for limit. There cannot be two or more outer transactions. Different implementations has different place of runtime - it is more near to top of pipeline. Pavel See also: http://www.postgresql.org/docs/current/static/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS ...Robert -- 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] Re: Hot Standby query cancellation and Streaming Replication integration
On 2/28/10 7:12 PM, Robert Haas wrote: However, I'd still like to hear from someone with the requisite technical knowledge whether capturing and retrying the current query in a query cancel is even possible. I'm not sure who you want to hear from here, but I think that's a dead end. dead end as in too hard to implement? Or for some other reason? It's undeniable that auto-retry would be better from a user's perspective than a user-visible cancel. So if it's *reasonable* to implement, I think we should be working on it. I'm also very puzzled as to why nobody else wants to even discuss it; it's like some wierd blackout. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Mon, Mar 1, 2010 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? Seems like that would have to be the same as the last one i guess the safer asumption is: treat it as MODIFIES SQL DATA -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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: Hot Standby query cancellation and Streaming Replication integration
Josh Berkus j...@agliodbs.com wrote: It's undeniable that auto-retry would be better from a user's perspective than a user-visible cancel. So if it's *reasonable* to implement, I think we should be working on it. I'm also very puzzled as to why nobody else wants to even discuss it; it's like some wierd blackout. Well, at least for serializable transactions past the first statement, you'd need to have the complete *logic* for the transaction in order to do a retry. Not that this is a bad idea -- our application framework does this automatically -- but unless you only support this for a transaction which is wrapped up as a function, I don't see how the database itself could handle it. It might be *possible* to do it outside of a single-function transaction in a read committed transaction, but you'd have to be careful about locks. I remember suggesting automatic query retry (rather than continuing in a mixed-snapshot mode) for update conflicts in read committed mode and Tom had objections; you might want to check the archives for that. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [Feature request] variable declaration of anonymous composite data type in PL/pgSQL
Hello,
Not sure whether it's appropriate list for feature requests though..
Would it be suitable to implement such variable declarations in PL/pgSQL so
that following (or similar) constructs would be possible?
DECLARE
tmpStruct (name varchar, foo integer, bar boolean)[] := array[
('somename', 1, true),
('someothername', 2, false),
('yetothername', 3, true)
];
BEGIN
...
Or maybe it is possible already? (I know there are temporary tables but it's
not quite the same). The goal to have temporary local random access data
structures (like lookup tables), similar to those in C.
--
regards
MM
--
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] function side effects
Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? Seems like that would have to be the same as the last one, or maybe the next-to-last one if you're prepared to assume it's read-only SQL. On a quick search of the spec, the best I was able to tell was that you are required to use CONTAINS SQL if the language is SQL. Perhaps it figures that the database engine can determine the read/write behavior directly if the language is SQL, and you tell it what it does if you're coding in some other language. -Kevin -- 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] scheduler in core
On Mon, Mar 1, 2010 at 4:43 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? yeah...that's what I meant. plpgsql exceptions are no help because there are many cases where you simply don't want the whole sequence of operations to run in a single transaction. loading lots of data to many tables is one. any operation that depends on transaction commit to do something (like notifications) and then hook on the results is another. you always have the heavy hitting administrative functions like vacuum, etc. another case is if you want a procedure to simply run forever...trivially done in a procedure, impossible in a function. The way people do this stuff now is to involve an 1) external scheduler such as cron and 2) .sql scripts for relatively simple things and/or a external scripting language like bash/perl. The external scheduler has a couple of annoying issues...completely not portable to code against and scheduling sub minute accuracy is a big headache. Also, adjusting the scheduling based on database events is, while not impossible, more difficult than it should be. External .sql scripts are portable but extremely limited. Involving something like perl just so I can jump outside the database to do manual transaction management is fine but ISTM these type of things are much better when done inside the database IMNSHO. Another factor here is that a sizable percentage of our user base is bargain hunters coming in from other systems like oracle and ms sql and having to rely in o/s scheduler is very distasteful to them. It's a hole, one of the last remaining IMO, in postgres being able to provide a complete server side development environment without having to deal with the o/s at all. I stand by my statements earlier. Any moderate level and up complexity database has all kinds of scheduling and scripting going on supporting it. These things really should be part of the database, dump with it, and run in a regular way irregardless of platform and server environment etc. With that, 90% of the code I have to write outside of the database goes away. 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] [Feature request] variable declaration of anonymous composite data type in PL/pgSQL
Maciej Mrozowski wrote:
Hello,
Not sure whether it's appropriate list for feature requests though..
Would it be suitable to implement such variable declarations in PL/pgSQL so
that following (or similar) constructs would be possible?
DECLARE
tmpStruct (name varchar, foo integer, bar boolean)[] := array[
('somename', 1, true),
('someothername', 2, false),
('yetothername', 3, true)
];
BEGIN
...
Or maybe it is possible already? (I know there are temporary tables but it's
not quite the same). The goal to have temporary local random access data
structures (like lookup tables), similar to those in C.
There have certainly been time I could have used this. You can get close
using VALUES:
create or replace function foo()
returns void
language plpgsql as
$$
declare
rec record;
begin
for rec in
select *
from (values
(1::int,'a'::text,'2009-08-06'::date),
(5,'wxy','1998-12-23'))
as x (a , b , c )
loop
raise notice 'a: %, b: %, c: %',
rec.a + 1,
length(rec.b),
rec.c + interval '1 day';
end loop;
end;
$$;
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] scheduler in core
Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? There is lots and lots of discussion of that in the archives. It's fundamentally impossible for PL functions done in the current style to start or commit transactions, unless you resort to dblink-style kluges. What's been discussed is some sort of structure that would allow a chunk of PL code to execute outside a transaction and thus issue its own begin and commit commands. This idea is what Merlin is calling a stored procedure, though personally I dislike that terminology. Anyway, nothing's got past the arm-waving stage as yet. 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] Re: Hot Standby query cancellation and Streaming Replication integration
josh, nobody is talking about it because it doesn't make sense. you could only retry if it was the first query in the transaction and only if you could prove there were no side-effects outside the database and then you would have no reason to think the retry would be any more likely to work. greg On 1 Mar 2010 22:32, Josh Berkus j...@agliodbs.com wrote: On 2/28/10 7:12 PM, Robert Haas wrote: However, I'd still like to hear from someone with the requ... dead end as in too hard to implement? Or for some other reason? It's undeniable that auto-retry would be better from a user's perspective than a user-visible cancel. So if it's *reasonable* to implement, I think we should be working on it. I'm also very puzzled as to why nobody else wants to even discuss it; it's like some wierd blackout. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subs...
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Greg Stark st...@mit.edu writes: josh, nobody is talking about it because it doesn't make sense. you could only retry if it was the first query in the transaction and only if you could prove there were no side-effects outside the database and then you would have no reason to think the retry would be any more likely to work. But it's hot standby, so there are no data-modifying transactions. Volatile functions could be a problem, though. A bigger problem is we might have already shipped partial query results to the client. I agree it ain't easy, but it might not be completely out of the question. Definitely won't be happening for 9.0 though. 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: Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: However, this leaves aside Greg's point about snapshot age and successive queries; does anyone dispute his analysis? Simon? There's already a note on the Hot Standby TODO about unexpectly bad max_standby_delay behavior being possible on an idle system, with no suggested resolution for it besides better SR integration. The issue Greg Stark has noted is another variation on that theme. It's already on my list of theorized pathological but as yet undemonstrated concerns that Simon and I identified, the one I'm working through creating a test cases to prove/disprove. I'm past it's possible... talks at this point though as not to spook anyone unnecessarily, and am only raising things I can show concrete examples of in action. White box testing at some point does require pausing one's investigation of what's in the box and getting on with the actual testing instead. The only real spot where my opinion diverges here that I have yet to find any situation where 'max_standby_delay=-1' makes any sense to me. When I try running my test cases with that setting, the whole system just reacts far too strangely. My first patch here is probably going to be adding more visibility into the situation when queries are blocking replication forever, because I think the times I find myself at why is the system hung right now? are when that happens and it's not obvious as an admin what's going on. Also, the idea that a long running query on the standby could cause an unbounded delay in replication is so foreign to my sensibilities that I don't ever include it in the list of useful solutions to the problems I'm worried about. The option is there, not disputing that it makes sense for some people because there seems some demand for it, just can't see how it fits into any of the use-cases I'm concerned about. I haven't said anything about query retry mainly because I can't imagine any way it's possible to build it in time for this release, so whether it's eventually feasible or not doesn't enter into what I'm worried about right now. In any case, I would prioritize that behind work on preventing the most common situations that cause cancellations in the first place, until those are handled so well that retry is the most effective improvement left to consider. -- 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] Re: Hot Standby query cancellation and Streaming Replication integration
On Mon, Mar 1, 2010 at 5:32 PM, Josh Berkus j...@agliodbs.com wrote: On 2/28/10 7:12 PM, Robert Haas wrote: However, I'd still like to hear from someone with the requisite technical knowledge whether capturing and retrying the current query in a query cancel is even possible. I'm not sure who you want to hear from here, but I think that's a dead end. dead end as in too hard to implement? Or for some other reason? I think it's probably too hard to implement for the extremely limited set of circumstances in which it can work. See the other responses for some of the problems. There are others, too. Suppose that the plan for some particular query is to read a table with a hundred million records, sort it, and then do whatever with the results. After reading the first 99 million records, the transaction is cancelled and we have to start over. Maybe someone will say, fine, no problem - but it's certainly going to be user-visible. Especially if we retry more than once. I think we should focus our efforts initially on reducing the frequency of spurious cancels. What we're essentially trying to do here is refute the proposition the WAL record I just replayed might change the result of this query. It's possibly equivalent to the halting problem (and certainly impossibly hard) to refute this proposition in every case where it is in fact false, but it sounds like what we have in place right now doesn't come close to doing as well as can be done. I just read through the current documentation and it doesn't really seem to explain very much about how HS decides which queries to kill. Can someone try to flesh that out a bit? It also uses the term buffer cleanup lock, which doesn't seem to be used anywhere else in the documentation (though it does appear in the source tree, including README.HOT). ...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: Hot Standby query cancellation and Streaming Replication integration
* Tom Lane t...@sss.pgh.pa.us [100301 20:04]: Greg Stark st...@mit.edu writes: josh, nobody is talking about it because it doesn't make sense. you could only retry if it was the first query in the transaction and only if you could prove there were no side-effects outside the database and then you would have no reason to think the retry would be any more likely to work. But it's hot standby, so there are no data-modifying transactions. Volatile functions could be a problem, though. A bigger problem is we might have already shipped partial query results to the client. But, since we know its a slave and that the reason the query was cancelled was because it's got a backlog of updates to apply, it's very likely that the data that the earlier parts of the transaction would be different... And then you have no idea if just blindly replaying all statements of the transaction successively is a good idea... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] [GENERAL] trouble with to_char('L')
Hiroshi Inoue wrote: Bruce Momjian wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Where are we on this issue? Oops I forgot it completely. I have a little improved version and would post it tonight. Ah, very good. Thanks. Attached is an improved version. I spent many hours on this patch and am attaching an updated version. I have restructured the code and added many comments, but this is the main one: * Ideally, the server encoding and locale settings would * always match. Unfortunately, WIN32 does not support UTF-8 * values for setlocale(), even though PostgreSQL runs fine with * a UTF-8 encoding on Windows: * * http://msdn.microsoft.com/en-us/library/x99tb11d.aspx * * Therefore, we must set LC_CTYPE to match LC_NUMERIC and * LC_MONETARY, call localeconv(), and use mbstowcs() to * convert the locale-aware string, e.g. Euro symbol, which * is not in UTF-8 to the server encoding. I need someone with WIN32 experience to review and test this patch. I don't understand why cache_locale_time() works on Windows. It sets the LC_CTYPE but does not do any encoding coversion. Doesn't strftime_win32 do the conversion? Oh, I now see strftime is redefined as a macro in that C files. Thanks. Do month and day-of-week names not work either, or do they work and the encoding conversion for numeric/money, e.g. Euro, it not necessary? db_strdup does the conversion. Should we pull the encoding conversion into a separate function and have strftime_win32() and db_strdup() both call it? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] [GENERAL] Hung postmaster (8.3.9)
Ed L. pg...@bluepolka.net writes: On Monday 01 March 2010 @ 17:57, Tom Lane wrote: BTW, there seems to be some other contributing factor here besides the weird username, because I don't see any looping when I try CREATE USER @. What's your platform exactly, and what type of filesystem is $PGDATA on? This is CentOS 5.2, Linux 2.6.18-92.1.22.el5 #1 SMP x86_64 GNU/Linux. Ah. I can reproduce it on my Fedora box. The infinite loop is because feof never returns 1 when reading from a directory. I think this is a glibc bug and have filed it accordingly: https://bugzilla.redhat.com/show_bug.cgi?id=569697 but IME the glibc boys can be pretty stubborn about acknowledging that corner cases in their code are actually bugs. We shall see. In the meantime, it seems like we ought to take two defensive steps: prevent a quoted @ from being considered as an include introducer, and prevent @ with no additional text from being considered as an inclusion reference no matter what. What the current code is doing is seeing @ as an include file reference with empty include name, and by the time canonicalize_file is done with it this ends up as a reference to the $PGDATA/global directory itself. Which Fedora allows us to open and read, but it reads as an infinite sequence of EOF characters because feof never succeeds. 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: Hot Standby query cancellation and Streaming Replication integration
Joachim Wieland wrote: 1) With the current implementation they will see better performance on the master and more aggressive vacuum (!), since they have less long-running queries now on the master and autovacuum can kick in and clean up with less delay than before. On the other hand their queries on the standby might fail and they will start thinking that this HS+SR feature is not as convincing as they thought it was... Next step for them is to take the documentation and study it for a few days to learn all about vacuum, different delays, transaction ids and age parameters and experiment a few weeks until no more queries fail - for a while... But they can never be sure... In the end they might also modify the parameters in the wrong direction or overshoot because of lack of time to experiment and lose another important property without noticing (like being as close as possible to the master). I assumed they would set max_standby_delay = -1 and be happy. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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: Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: HS+SR is still a tremendous improvement over the options available previously. We never thought it was going to work for everyone everywhere, and shouldn't let our project's OCD tendencies run away from us. OCD (Obsessive-Compulsive Disorder) --- good one. :-) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A thought on Index Organized Tables
a) We are already going from table to index to do unique checks. This is the same thing, which we will do to go and update the snapshot in the indexes. No, it is not the same thing. Updating index snapshots requires being able to *re-find* a previously made index entry for the current row. And it has to be done 100% reliably. The worst that happens if an index entry is not found when it should be during a uniqueness check is that the uniqueness constraint is not enforced properly; which is bad but it doesn't lead to internally-inconsistent data structures. Tom, We are also going to indexes to maintain the referential integrity constraints like foreign keys. Say there are constraints like 'On Delete Cascade' and 'On Delete Restrict', they are maintained through the indexes and if we say that indexes can return wrong results, then the referential integrity is lost and we no longer are ACID compliant. Thanks, Gokul.
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: Joachim Wieland wrote: 1) With the current implementation they will see better performance on the master and more aggressive vacuum (!), since they have less long-running queries now on the master and autovacuum can kick in and clean up with less delay than before. On the other hand their queries on the standby might fail and they will start thinking that this HS+SR feature is not as convincing as they thought it was... I assumed they would set max_standby_delay = -1 and be happy. The admin in this situation might be happy until the first time the primary fails and a failover is forced, at which point there is an unbounded amount of recovery data to apply that was stuck waiting behind whatever long-running queries were active. I don't know if you've ever watched what happens to a pre-8.2 cold standby when you start it up with hundreds or thousands of backed up WAL files to process before the server can start, but it's not a fast process. I watched a production 8.1 standby get 4000 files behind once due to an archive_command bug, and it's not something I'd like to ever chew my nails off to again. If your goal was HA and you're trying to bring up the standby, the server is down the whole time that's going on. This is why no admin who prioritizes HA would consider 'max_standby_delay = -1' a reasonable setting, and those are the sort of users Joachim's example was discussing. Only takes one rogue query that runs for a long time to make the standby so far behind it's useless for HA purposes. And you also have to ask yourself if recovery is halted while waiting for this query to run, how stale is the data on the standby getting?. That's true for any large setting for this parameter, but using -1 for the unlimited setting also gives the maximum possible potential for such staleness. 'max_standby_delay = -1' is really only a reasonable idea if you are absolutely certain all queries are going to be short, which we can't dismiss as an unfounded use case so it has value. I would expect you have to also combine it with a matching reasonable statement_timeout to enforce that expectation to make that situation safer. In any of the offload batch queries to the failover standby situations, it's unlikely an unlimited value for this setting will be practical. Perhaps you set max_standby_delay to some number of hours, to match your expected worst-case query run time and reduce the chance of cancellation. Not putting a limit on it at all is a situation no DBA with healthy paranoia is going to be happy with the potential downside of in a HA environment, given that both unbounded staleness and recovery time are then both possible. The potential of a failed long-running query is much less risky than either of those. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [HACKERS] pg_stop_backup does not complete
On Fri, Feb 26, 2010 at 2:47 AM, Bruce Momjian br...@momjian.us wrote: Postgres 9.0 will be the first release to mention /bin/true as a way of turning off archiving in extraordinary circumstances: http://developer.postgresql.org/pgdocs/postgres/runtime-config-wal.html Setting archive_mode to a command that does nothing but return true, e.g. /bin/true, return true seems ambiguous for me. How about writing clearly return a zero exit status instead? 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: Hot Standby query cancellation and Streaming Replication integration
Robert Haas wrote: I just read through the current documentation and it doesn't really seem to explain very much about how HS decides which queries to kill. Can someone try to flesh that out a bit? I believe it just launches on a mass killing spree once things like max_standby_delay expire. This I want to confirm via testing (can simulate with a mix of long and short running pgbench queries) and then intend to update the docs to clarify. It also uses the term buffer cleanup lock, which doesn't seem to be used anywhere else in the documentation (though it does appear in the source tree, including README.HOT). This loose end was already noted in my last docs update. I wrote an initial description, but Bruce and I decided to leave out until something more thorough could be put together. This is also on my docs cleanup list, will get to it somewhere along the beta timeline. -- 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] pg_stop_backup does not complete
On Fri, Feb 26, 2010 at 10:00 PM, Greg Stark gsst...@mit.edu wrote: Secondarily, the message printed at this time and when the process is finished doesn't actually give the user any information on how much longer to expect the process to take. It would be nice to say what the target archive log we're waiting on is and then periodically print out what the last archived log file was. +1 We would be easily able to calculate the last archived log file from the existence of archive status files. 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] pg_stop_backup does not complete
Fujii Masao wrote: On Fri, Feb 26, 2010 at 2:47 AM, Bruce Momjian br...@momjian.us wrote: Postgres 9.0 will be the first release to mention /bin/true as a way of turning off archiving in extraordinary circumstances: http://developer.postgresql.org/pgdocs/postgres/runtime-config-wal.html Setting archive_mode to a command that does nothing but return true, e.g. /bin/true, return true seems ambiguous for me. How about writing clearly return a zero exit status instead? This is a good catch, and I have a work in progress update to that doc section that fixes that wording, as well as rearranging the recent additions a bit. Really that whole /bin/true big needs to go after the example. A very brief intro to what exit status means on various platforms might be in order too. I'm adjusting all that to read better, once I'm happy with it I'll submit a doc patch in the next week or two with the final result. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [HACKERS] pg_stop_backup does not complete
Fujii Masao wrote: We would be easily able to calculate the last archived log file from the existence of archive status files. Right, but you have to actually scan the whole archive directory to figure that out, and I'd rather not see that code get duplicated somewhere else when it's already inside the archive_command logic. If it just shared that info with the rest of the system instead this would be trivial to discover. -- 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] plpgsql: numeric assignment to an integer variable errors out
Hi, Now it is true that a lot of the uses for that were subsumed when we added coerce-via-IO to the native cast capabilities; but I'm still quite scared of what this would break, and I don't see any field demand for a change. Well, we have had one of our EDB connectors facing issues because of this existing conversion mechanism. I think this is a small patch which tries to do the right thing, no? Regards, Nikhils -- http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication and pg_xlogfile_name()
Sorry for the delay. On Fri, Feb 26, 2010 at 6:26 AM, Erik Rijkers e...@xs4all.nl wrote: With this patch the standby compiles, tests, installs OK. I wanted to check with you if the following is expected. Thanks for the test and bug report! With standby (correctly) as follows : LOG: redo starts at 0/120 LOG: consistent recovery state reached at 0/200 LOG: database system is ready to accept read only connections This is OK. However, initially (even after the above 'ready' message) the timeline value as reported by pg_xlogfile_name_offset(pg_last_xlog_replay_location()) is zero. When we try to read the WAL record discontinuously (e.g., the REDO starting record and the last applied record), the lastPageTLI is always reset. If that record is not in the buffer, it's read from the disk and the lastPageTLI is set to the right timeline. Otherwise, the lastPageTLI remains at zero wrongly. This is the cause of the problem that you reported. I revised the patch so that the lastPageTLI is always set correctly. Please try this new patch. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** *** 13199,13204 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); --- 13199,13208 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. + These functions also accept as a parameter the string that consists of timeline and + location, separated by a slash. In this case a transaction log file name is computed + by using the given timeline. On the other hand, if timeline is not supplied, the + current timeline is used for the computation. /para para *** *** 13245,13257 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); literalfunctionpg_last_xlog_receive_location/function()/literal /entry entrytypetext/type/entry !entryGet last transaction log location received and synced to disk during ! streaming recovery. If streaming recovery is still in progress this will increase monotonically. If streaming recovery has completed then this value will remain static at the value of the last WAL record received and synced to disk during that recovery. When the server has been started without a streaming recovery then the return value will be ! InvalidXLogRecPtr (0/0). /entry /row row --- 13249,13263 literalfunctionpg_last_xlog_receive_location/function()/literal /entry entrytypetext/type/entry !entryGet timeline and location of last transaction log received and synced ! to disk during streaming recovery. The return string is separated by a slash, ! the first value indicates the timeline and the other the location. ! If streaming recovery is still in progress this will increase monotonically. If streaming recovery has completed then this value will remain static at the value of the last WAL record received and synced to disk during that recovery. When the server has been started without a streaming recovery then the return value will be ! literal0/0/0/. /entry /row row *** *** 13259,13270 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); literalfunctionpg_last_xlog_replay_location/function()/literal /entry entrytypetext/type/entry !entryGet last transaction log location replayed during recovery. If recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last WAL record applied during that recovery. When the server has been started normally without a recovery ! then the return value will be InvalidXLogRecPtr (0/0). /entry /row /tbody --- 13265,13278 literalfunctionpg_last_xlog_replay_location/function()/literal /entry entrytypetext/type/entry !entryGet timeline and location of last transaction log replayed during ! recovery. The return string is separated by a slash, the first value ! indicates the timeline and the other the location. If recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last WAL record applied during that recovery. When the server has been started normally without a recovery ! then the return value will be literal0/0/0/. /entry /row /tbody ***
Re: [HACKERS] pg_stop_backup does not complete
Greg Smith wrote: Fujii Masao wrote: We would be easily able to calculate the last archived log file from the existence of archive status files. Right, but you have to actually scan the whole archive directory to figure that out, and I'd rather not see that code get duplicated somewhere else when it's already inside the archive_command logic. If it just shared that info with the rest of the system instead this would be trivial to discover. The archiver process is not connected to shared memory, so scanning the directory is the way to do it. -- 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
