[HACKERS] ToDo: plpgsql plugin for query and expression verification
Hello I thinking about more restrictive query and expression checking than now. Used parser checking isn't enough - so some possible bugs can be detected in production stage. Other problem is using any expression as SELECT expr. The request on validation can be different and it is probably for more advanced users - so it could be wrapped to some plugin. So users can exactly set an level for checking that is the best for they. postgres=# set check_function_bodies to on;SET postgres=# create or replace function foo(a varchar, b varchar) returns varchar as $$ begin a = current_date; -- result type is different then target type return a || b || c;-- simple expression has unknown symbol end; $$ language plpgsql; CREATE FUNCTION I think, so these problem have to be identified in compile stage - but it can be too strict for all (and can slow down production) - it is reason for plugin. What do you think about this idea? Regards Pavel Stehule -- Sent 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] libecpg versions and libecpg_compat
Rob Newton írta: Hi Hackers, I posted this to the GENERAL list a while back, but got no repies. Perhaps someone here can help... I've been building ECPG (embedded SQL/C) programs on a system with Pg version 8.0 installed. When I tried to run them recently on version 8.4 I found that there was a libecpg library incompatibility: v8.0 uses libecpg.so.5, whereas 8.4 uses libecpg.so.6. Then I noticed libecpg_compat in the lib area. What is this used for? compat suggests compatibility between different versions? But Pg 8.0 has libecpg_compat.so.2, whereas Pg 8.4 has libecpg_compat.so.3. libecpg_compat.so is for Informix compatibility. It contains the Informix-specific calls. Is there some way of building with Pg v8.0 ECPG lib and running on a system with Pg v8.4 ECPG lib? or vice versa? and is libecpg_compat intended for that purpose? You can build the src/interfaces/libpq and src/interfaces/ecpg/ecpglib directories in the old version and install them side by side with the new version. The 8.4 server works happily with the old ECPG client. 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] LISTEN/NOTIFY and notification timing guarantees
On Tue, Feb 16, 2010 at 6:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Another possibility is to force a ProcessIncomingNotifies scan to occur before we reach ReadyForQuery if we sent any notifies in the just-finished transaction --- but that won't help if there are uncommitted messages in front of ours. What about dealing with self-notifies in memory? i.e. copy them into a subcontext of TopMemoryContext in precommit and commit as usual. Then as a first step in ProcessIncomingNotifies() deliver whatever is in memory and then delete the context. While reading the queue, ignore all self-notifies there. If we abort for some reason, delete the context in AtAbort_Notify(). Would that work? Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf Thanks, tried it. There is nothing in the log - the actual vacuum/analyze commands are not run (as there is no query activity). I suspect that autovacuum is checking each database if it should run - and decides not to run. See the randomly catch process in ps output/pg_stat_activity mentioned in earlier mail. I suspect that this checking generates the load. Is it possible? With this many databases and this high of a statistics target I've changed the default_statistics_target back to its default (100). No change, still stats collector generates load. You're really pushing what you can do in a VM with this many databases of this size. Yes, it's a VM but on our dedicated hardware - there are few other containers running but they are not generating any load. What's puzzling me is that there is no database activity (queries, connections) and stats collector is still eating CPU. Kuba Dne 16.2.2010 8:29, Greg Smith napsal(a): Jakub Ouhrabka wrote: I've found similar reports but with older versions of postgres: http://old.nabble.com/100--of-CPU-utilization-postgres-process-tt27302021.html Those all looked like a FreeBSD issue, doubt it's related to yours. The pgstat.stat is ~20MB. There are 650 databases, 140GB total. default_statistics_target = 1000 The system is running Proxmox linux distribution. PostgreSQL is in OpenVZ container. With this many databases and this high of a statistics target, running in a VM, suspecting autovacuum seems reasonable. You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf, restarting or signalling (pg_ctl reload) the server, and watching just what it's doing. You might need to reduce how aggressively that runs, or limit the higher target to only the tables that need it, to get this under control. You're really pushing what you can do in a VM with this many databases of this size. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: preload for fulltext dictionary
2010/2/16 Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp: Pavel Stehule pavel.steh...@gmail.com wrote: The dictionary data could be shared or minimally dictionary could be preloaded like some PL language. What do you think about this? Surely preloading is the most realistic approach, but I hope we would support dynamic allocation of shared memory, and load dictionaries in the area and share it with backends. We should avoid additonal calls of shmget() or mmap() in the additional shared memory allocation, but we can shrink shared buffers and reuse the area for general purposes. We often have serveral GB of shared buffers nowadays, so dividing some MB of buffers will not be problem. I am for all solution, but using a shared memory isn't realistic now, and can be a problem on MS Windows. More - it can be difficult with some special custom non ro dictionaries. Regards Pavel 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
[HACKERS] OpenVMS?
Folks, Would it be worthwhile to light up some buildfarm animals on OpenVMS? http://www.openvms.org/stories.php?story=10/02/09/2319162 Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] libecpg versions and libecpg_compat
On Tue, Feb 16, 2010 at 09:44:26AM +1000, Rob Newton wrote: I posted this to the GENERAL list a while back, but got no repies. Perhaps someone here can help... Didn't see it there, sorry. Then I noticed libecpg_compat in the lib area. What is this used for? compat suggests compatibility between different versions? No, compat means compatibility towards different database systems. In this case it more or less only means compatibility to Informix. Is there some way of building with Pg v8.0 ECPG lib and running on a system with Pg v8.4 ECPG lib? or vice versa? and is libecpg_compat intended for that purpose? You can link the static library in so you're independant from the dynamic libraries. However, I wonder if that's a good idea. ECPG's parser is build to be in sync with the backend parser, i.e. ecpg 8.0 accepts all SQL statements that the 8.0 backend understands which is is different set than what 8.4 understands. Also if my memory serves well, libpq has had a major version update between 8.0 and 8.4, so if it works at all you might find that you have to recompile the libraries too. Is there any reason why you cannot simply recompile your programs against 8.4? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xpath improvement V2
Arie Bikker wrote: Hi all, I've combined the review suggestions of Jan Urbański, Scott Bailey, and others. This was a lot harder, then I had foreseen; and I took my time to do it the right way (hope you agree!). Hi, I see the patch has been marked as Returned with Feedback on the 6th of February, I assume on grounds of prolonged silence about it. I confess it was partly my fault, because soon after posting the review I suddenly had to focus on other things. I won't be able to review the new version in the next few days, and this commitfest is closing anyway... However I would hate to see that patch just disappear, as I think it's useful and you obviously invested some work in it. At this stage I would suggest moving it to the first 9.1 commitfest, since it's a nice feature, but not one we should burden the committers with this late in the development cycle. Arie, care to add that last version of the patch to the 2010-Next commitfest? Cheers, Jan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] bug? autovacuum is not launched even if autovacuum_freeze_max_age is reached
Hi, In HEAD, when autovacuum is disabled, autovacuum process is not launched forcibly to prevent XID wraparound even if we go through autovacuum_freeze_max_age. This seems to be because ShmemVariableCache-xidVacLimit is not initialized (i.e., SetTransactionIdLimit() is not called) until VACUUM is performed. OTOH, in older version, ShmemVariableCache-xidVacLimit is always initialized when backend updates the flat database file, and then autovacuum process seems to be launched forcibly as expected. Is this bug? I guess that it derives from the changes around flag file done a while ago. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication on win32, still broken
On Tue, Feb 16, 2010 at 12:37 AM, Magnus Hagander mag...@hagander.net wrote: With the libpq fixes, I get further (more on that fix later, btw), but now I get stuck in this. When I do something on the master that generates WAL, such as insert a record, and then try to query this on the slave, the walreceiver process crashes with: PANIC: XX000: could not write to log file 0, segment 9 at offset 0, length 160: Invalid argument LOCATION: XLogWalRcvWrite, .\src\backend\replication\walreceiver.c:487 I'll keep digging at the details, but if somebody has a good idea here.. ;) Yeah, this problem was reproduced in my (very slow :-( ) MinGW environment, too. Though I've not idenfied the cause yet, I guess that it derives from wrong use of the type of local variables in XLogWalRcvWrite(). I'll continue investigation of it. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication on win32, still broken
2010/2/16 Fujii Masao masao.fu...@gmail.com: On Tue, Feb 16, 2010 at 12:37 AM, Magnus Hagander mag...@hagander.net wrote: With the libpq fixes, I get further (more on that fix later, btw), but now I get stuck in this. When I do something on the master that generates WAL, such as insert a record, and then try to query this on the slave, the walreceiver process crashes with: PANIC: XX000: could not write to log file 0, segment 9 at offset 0, length 160: Invalid argument LOCATION: XLogWalRcvWrite, .\src\backend\replication\walreceiver.c:487 I'll keep digging at the details, but if somebody has a good idea here.. ;) Yeah, this problem was reproduced in my (very slow :-( ) MinGW environment, too. Though I've not idenfied the cause yet, I guess that it derives from wrong use of the type of local variables in XLogWalRcvWrite(). I'll continue investigation of it. Thanks! I will be somewhat spottily available over the next two days due to on-site work with clients. Let me know if you would be helped by some details of how to get a (somewhat faster) EC2 image up and running with MSVC to test on :-) -- 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] Explain buffers display units.
On Tue, Feb 16, 2010 at 2:48 AM, Robert Haas robertmh...@gmail.com wrote: Multiplying by the block size makes it sound as if all the memory was read or used, which is simply not the case - especially for things like buffer hits, which don't actually read or allocate any memory at all. In which case it represents how much data would have had to have been read if it wasn't in the buffer cache which is a perfectly reasonable measurement. It's exactly what a cache profiler should be measuring. These are figures that users have to compare with their buffer cache size and with the output of iostat or other tools. Presenting them in arbitrary internal units makes that difficult. We certainly do that for GUCs, and in that context it seems to me to make sense. If you set your shared buffers to a gigabyte, PG will use an additional GB of memory. But if you hit a gigabyte of shared buffers, you may be examining anywhere from one 8K block over and over again all the way up to a full GB of memory. Block hits and reads just don't add in the same way that actual memory allocations do. Accessing the same 8kB of memory 100,1000 times is 1GB of memory bandwidth. The output of explain doesn't give you enough information to distinguish that from accessing 1GB of different data which is too bad but there's a limit to how much information we can fit in a reasonable amount of space. But 1GB of memory bandwidth is still an interesting figure even if it's the same 8kB a hundred thousand times. I think it's a lot more meaningful for a human reader than 131072. And at any rate, what we DON'T do for GUCs is produce differing output format for the same parameter based on the magnitude of the output value, as you've done here. No, that's *exactly* what we do: postgres=# set work_mem = 64; SET postgres=# show work_mem; work_mem -- 64kB (1 row) postgres=# set work_mem = 1024; SET postgres=# show work_mem; work_mem -- 1MB (1 row) postgres=# set work_mem = 1048576; SET postgres=# show work_mem; work_mem -- 1GB (1 row) We accept input in several different formats, but there is only one canonical output formal for any particular GUC, which is furthermore always chosen in such a way that the exact value of the setting is preserved (again, unlike what you've done here). I don't think the use case for GUCs is the same as for empirical measurements. Empirical results are never going to come out as a round number of megabytes so only using larger units in that case would be useless. In the case of GUCs I assume the argument was that someone should be able to copy the output into another postgresql.conf and get the same value, something which is irrelevant for empirical measurements. In any case the machine-readable form of GUC settings is not this one canonical format you describe for SHOW: postgres=# select name,setting,unit,min_val,max_val,boot_val,reset_val from pg_settings where name = 'work_mem'; name | setting | unit | min_val | max_val | boot_val | reset_val --+-+--+-+-+--+--- work_mem | 1048576 | kB | 64 | 2097151 | 1024 | 1024 (1 row) This is similar to how I think the XML output should work. It should have the raw internal values with enough meta data in it that a tool can figure out how to display it or work with it. So, you're saying we shouldn't look at the way that the pg_stat functions format the output because somebody might write a view over it that formats it in some different way that may or may not match what you've done for the EXPLAIN output? What makes you think that people don't just look at the raw numbers? I certainly have, and there's no suggestion in the documentation that users should do anything else. I'm not sure users need suggestions that they should format the data in whatever way they want. We still have to document the programmatic interface they use to get the raw data. pg_stat_statements doesn't do what you're suggesting either; it, too, presents raw numbers, and lets the user make of it what they will. They might, for example, want to compute a hit ratio, as in the example provided in the docs. In the case of EXPLAIN of an index scan, they might want to estimate the number of seeks, on the theory that an inner-indexscan is going to be all random IO. You can compute the hit ratio just fine from measurements with units. And if you're doing it in an automated way you'll want to use machine-readable output, rather than parsing the formatted text. This doesn't seem to be a very carefully thought out proposal, because you haven't explained how it would work for JSON or YAML output. A format-neutral solution which we've already used for sort and hash information (and for GUCs) is to include the unit designator in the output.. But I generally think that trying to make the EXPLAIN output self-documenting to the point where programs don't need any specific
Re: [HACKERS] NaN/Inf fix for ECPG
Hi,
buildfarm member pika fails the NaN test.
Does FreeBSD/MIPS really return true for isinf(NaN)?
Anyway, the attached patch tries to fix the test case
by testing isnan() first and doesn't check isinf()
if isnan() returned true.
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/
diff -durpN pgsql.orig/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c pgsql/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c
--- pgsql.orig/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c 2010-02-09 11:43:57.0 +0100
+++ pgsql/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c 2010-02-16 12:10:55.0 +0100
@@ -104,10 +104,10 @@ if (sqlca.sqlcode 0) sqlprint ( );}
if (sqlca.sqlcode)
break;
- if (isinf(d))
- printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
if (isnan(d))
printf(%d NaN '%s'\n, id, val);
+ else if (isinf(d))
+ printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, insert into nantest1 ( id , d ) values ( $1 + 3 , $2 ),
ECPGt_int,(id),(long)1,(long)1,sizeof(int),
diff -durpN pgsql.orig/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc pgsql/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc
--- pgsql.orig/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc 2010-02-09 11:43:57.0 +0100
+++ pgsql/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc 2010-02-16 12:04:59.0 +0100
@@ -37,10 +37,10 @@ main(void)
exec sql fetch from cur into :id, :d, :val;
if (sqlca.sqlcode)
break;
- if (isinf(d))
- printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
if (isnan(d))
printf(%d NaN '%s'\n, id, val);
+ else if (isinf(d))
+ printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
exec sql insert into nantest1 (id, d) values (:id + 3, :d);
exec sql insert into nantest1 (id, d) values (:id + 6, :val);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NaN/Inf fix for ECPG
Boszormenyi Zoltan írta:
Hi,
buildfarm member pika fails the NaN test.
Does FreeBSD/MIPS really return true for isinf(NaN)?
Anyway, the attached patch tries to fix the test case
by testing isnan() first and doesn't check isinf()
if isnan() returned true.
I lied in the patch name, it wasn't a context diff.
Also, the same remedy seems to be needed in ecpglib/execute.c, too.
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/
diff -dcrpN pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c pgsql/src/interfaces/ecpg/ecpglib/execute.c
*** pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c 2010-02-04 11:10:03.0 +0100
--- pgsql/src/interfaces/ecpg/ecpglib/execute.c 2010-02-16 12:19:38.0 +0100
*** ecpg_store_result(const PGresult *result
*** 468,482
static void
sprintf_double_value(char *ptr, double value, const char *delim)
{
! if (isinf(value))
{
if (value 0)
sprintf(ptr, %s%s, -Infinity, delim);
else
sprintf(ptr, %s%s, Infinity, delim);
}
- else if (isnan(value))
- sprintf(ptr, %s%s, NaN, delim);
else
sprintf(ptr, %.14g%s, value, delim);
}
--- 468,482
static void
sprintf_double_value(char *ptr, double value, const char *delim)
{
! if (isnan(value))
! sprintf(ptr, %s%s, NaN, delim);
! else if (isinf(value))
{
if (value 0)
sprintf(ptr, %s%s, -Infinity, delim);
else
sprintf(ptr, %s%s, Infinity, delim);
}
else
sprintf(ptr, %.14g%s, value, delim);
}
*** sprintf_double_value(char *ptr, double v
*** 484,498
static void
sprintf_float_value(char *ptr, float value, const char *delim)
{
! if (isinf(value))
{
if (value 0)
sprintf(ptr, %s%s, -Infinity, delim);
else
sprintf(ptr, %s%s, Infinity, delim);
}
- else if (isnan(value))
- sprintf(ptr, %s%s, NaN, delim);
else
sprintf(ptr, %.14g%s, value, delim);
}
--- 484,498
static void
sprintf_float_value(char *ptr, float value, const char *delim)
{
! if (isnan(value))
! sprintf(ptr, %s%s, NaN, delim);
! else if (isinf(value))
{
if (value 0)
sprintf(ptr, %s%s, -Infinity, delim);
else
sprintf(ptr, %s%s, Infinity, delim);
}
else
sprintf(ptr, %.14g%s, value, delim);
}
diff -dcrpN pgsql.orig/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c pgsql/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c
*** pgsql.orig/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c 2010-02-09 11:43:57.0 +0100
--- pgsql/src/interfaces/ecpg/test/expected/pgtypeslib-nan_test.c 2010-02-16 12:10:55.0 +0100
*** if (sqlca.sqlcode 0) sqlprint ( );}
*** 104,113
if (sqlca.sqlcode)
break;
- if (isinf(d))
- printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
if (isnan(d))
printf(%d NaN '%s'\n, id, val);
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, insert into nantest1 ( id , d ) values ( $1 + 3 , $2 ),
ECPGt_int,(id),(long)1,(long)1,sizeof(int),
--- 104,113
if (sqlca.sqlcode)
break;
if (isnan(d))
printf(%d NaN '%s'\n, id, val);
+ else if (isinf(d))
+ printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, insert into nantest1 ( id , d ) values ( $1 + 3 , $2 ),
ECPGt_int,(id),(long)1,(long)1,sizeof(int),
diff -dcrpN pgsql.orig/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc pgsql/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc
*** pgsql.orig/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc 2010-02-09 11:43:57.0 +0100
--- pgsql/src/interfaces/ecpg/test/pgtypeslib/nan_test.pgc 2010-02-16 12:04:59.0 +0100
*** main(void)
*** 37,46
exec sql fetch from cur into :id, :d, :val;
if (sqlca.sqlcode)
break;
- if (isinf(d))
- printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
if (isnan(d))
printf(%d NaN '%s'\n, id, val);
exec sql insert into nantest1 (id, d) values (:id + 3, :d);
exec sql insert into nantest1 (id, d) values (:id + 6, :val);
--- 37,46
exec sql fetch from cur into :id, :d, :val;
if (sqlca.sqlcode)
break;
if (isnan(d))
printf(%d NaN '%s'\n, id, val);
+ else if (isinf(d))
+ printf(%d %sInf '%s'\n, id, (d 0 ? - : +), val);
exec sql insert into nantest1 (id, d) values (:id + 3, :d);
exec sql insert into nantest1 (id, d) values (:id + 6, :val);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Mon, Feb 15, 2010 at 02:58:47PM -0800, David E. Wheeler wrote:
On Feb 15, 2010, at 2:42 PM, Tim Bunce wrote:
I've not really looked the the DBD::Pg code much so this seemed like a
good excuse... It looks like the default is to call PQprepare() with
paramTypes Oid values of 0.
Yes, IIRC, 0 == unknown as far as the server is concerned. It just
tells the server to resolve it when it can.
An extra source of puzzlement is that the oid of the 'unknown' type is
705 not 0, and the unknown type isn't discussed in the docs (as far as I
could see).
http://developer.postgresql.org/pgdocs/postgres/libpq-exec.html says
If paramTypes is NULL, or any particular element in the array is zero,
the server assigns a data type to the parameter symbol in the same way
it would do for an untyped literal string.
Right, exactly.
But I don't know if that means it has the same semantics as using
'unknown' as a type to PL/Perl's spi_prepare(). The docs for
spi_prepare() don't mention if type parameters are optional or what
happens if they're omitted.
http://developer.postgresql.org/pgdocs/postgres/plperl-builtins.html#PLPERL-DATABASE
Same as in SQL PREPARE, I'm sure. Ultimately that's what's doing the work,
IIUC.
Looking at the code I see spi_prepare() maps the provided arg type names
to oids then calls SPI_prepare(). The docs for SPI_prepare() also don't
mention if the type parameters are optional or what happens if they're
omitted.
The docs for the int nargs parameter say number of input *parameters*
not number of parameters that Oid *argtypes describes
http://developer.postgresql.org/pgdocs/postgres/spi-spi-prepare.html
Guess I need to go and check the current behaviour... see below.
And like maybe a doc patch might be useful.
I would be great if someone who understood
I'm currently using:
my $placeholders = join ,, map { '$'.$_ } 1..$arity;
my $plan = spi_prepare(select * from $spname($placeholders),
@$arg_types) };
Ah, yeah, that's better, but I do think you should use quote_ident() on the
function name.
That would cause complications if included a schema name. I've opted to
specify that the name used in the signature should be in quoted form if
it needs quoting.
and it turns out that spi_prepare is happy to prepare a statement with
more placeholders than there are types provided.
Types or args?
These appear to be identical in behaviour:
spi_prepare(select * from foo($1,$2), 'unknown', 'unknown');
spi_prepare(select * from foo($1,$2), 'unknown')
spi_prepare(select * from foo($1,$2))
You can't specify a schema though, and the 'SP' is somewhat
artificial. Still, I'm coming round to the idea :)
What about `SP-schema::function_name()`?
Wouldn't work unless you'd installed an AUTOLOAD function into each
schema:: package that you wanted to use. (schema-SP::function_name()
could be made to work but that's just too bizzare :)
Agreed that SP is artificial, but there needs to be some kind of
handle for AUTOLOAD to wrap itself around. Maybe a singleton object
instead? (I was kind of thinking of SP as that, anyway:
use constant SP = 'PostgreSQL::PLPerl';
)
Something like that is probably best. I've made PostgreSQL::PLPerl::Call
export both call and SP where SP is a constant containing the name
of a class (PostgreSQL::PLPerl::Call::SP) that just has an AUTOLOAD.
I've attached the current docs and code.
Thanks for your help David!
Tim.
package PostgreSQL::PLPerl::Call;
=head1 NAME
PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from
PostgreSQL PL/Perl
=head1 SYNOPSIS
use PostgreSQL::PLPerl::Call;
Returning single-row single-column values:
$pi = call('pi'); # 3.14159265358979
$net = call('network(inet)', '192.168.1.5/24'); # '192.168.1.0/24';
$seqn = call('nextval(regclass)', $sequence_name);
$dims = call('array_dims(text[])', '{a,b,c}'); # '[1:3]'
# array arguments can be perl array references:
$ary = call('array_cat(int[], int[])', [1,2,3], [2,1]); # '{1,2,3,2,1}'
Returning multi-row single-column values:
@ary = call('generate_series(int,int)', 10, 15); # (10,11,12,13,14,15)
Returning single-row multi-column values:
# assuming create function func(int) returns table (r1 text, r2 int) ...
$row = call('func(int)', 42); # returns hash ref { r1=..., r2=... }
Returning multi-row multi-column values:
@rows = call('pg_get_keywords'); # ({...}, {...}, ...)
Alternative method-call syntax:
$pi = SP-pi();
$seqn = SP-nextval($sequence_name);
=head1 DESCRIPTION
The Ccall function provides a simple efficient way to call SQL functions
from PostgreSQL PL/Perl code.
The first parameter is a Isignature that specifies the name of the function
to call and, optionally, the types of the arguments.
Any further parameters are used as argument values for the function being
called.
=head2 Signature
The first parameter is a
[HACKERS] MIT Kerberos support in Windows builds
Just a heads-up really - following discussion with Magnus and other people involved in building our Windows packages, I intend to stop including MIT Kerberos support from 9.0 onwards. This is largely due to the fact that most (maybe all) users are more likely to use SSPI on Windows, and more importantly, due to the lack of timely releases of the Kerberos for Windows builds, as noted here: http://pgsnake.blogspot.com/2009/12/kerberos-support-in-postgresql-on.html. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LISTEN/NOTIFY and notification timing guarantees
Tom Lane wrote: We could adopt the historical policy of sending self-notifies pre-commit, but that doesn't seem tremendously appetizing from the standpoint of transactional integrity. But one traditional aspect of transactional integrity is that a transaction always sees *its own* uncommitted work. Wouldn't the historical policy of PostgreSQL self-notifies be consistent with 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
Re: [HACKERS] Streaming replication on win32, still broken
On Tue, Feb 16, 2010 at 7:20 PM, Magnus Hagander mag...@hagander.net wrote: 2010/2/16 Fujii Masao masao.fu...@gmail.com: On Tue, Feb 16, 2010 at 12:37 AM, Magnus Hagander mag...@hagander.net wrote: With the libpq fixes, I get further (more on that fix later, btw), but now I get stuck in this. When I do something on the master that generates WAL, such as insert a record, and then try to query this on the slave, the walreceiver process crashes with: PANIC: XX000: could not write to log file 0, segment 9 at offset 0, length 160: Invalid argument LOCATION: XLogWalRcvWrite, .\src\backend\replication\walreceiver.c:487 I'll keep digging at the details, but if somebody has a good idea here.. ;) Yeah, this problem was reproduced in my (very slow :-( ) MinGW environment, too. Though I've not idenfied the cause yet, I guess that it derives from wrong use of the type of local variables in XLogWalRcvWrite(). I'll continue investigation of it. Thanks! I will be somewhat spottily available over the next two days due to on-site work with clients. Let me know if you would be helped by some details of how to get a (somewhat faster) EC2 image up and running with MSVC to test on :-) Thanks! I can probably use the EC2 image by reading your great blog post. http://blog.hagander.net/archives/151-Testing-PostgreSQL-patches-on-Windows-using-Amazon-EC2.html But it might take some time to make my sysadmin open the port for rdesktop for some reasons... 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] ToDo: plpgsql plugin for query and expression verification
2010/2/16 Pavel Stehule pavel.steh...@gmail.com: I think, so these problem have to be identified in compile stage - but it can be too strict for all (and can slow down production) - it is reason for plugin. What do you think about this idea? How do you identify them? Running function body cannot be applied if the function is volatile. Also, I don't see how do you choose function argument values even in immutable cases. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LISTEN/NOTIFY and notification timing guarantees
On Tue, Feb 16, 2010 at 1:31 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane wrote: We could adopt the historical policy of sending self-notifies pre-commit, but that doesn't seem tremendously appetizing from the standpoint of transactional integrity. But one traditional aspect of transactional integrity is that a transaction always sees *its own* uncommitted work. True but notifications aren't sent until the transaction commits anyway. At the time when an application receives its self-notifies, it has already committed the transaction so there is no uncommitted work anymore. Wouldn't the historical policy of PostgreSQL self-notifies be consistent with that? No. The policy is also to not see the committed work if for some reason the transaction had to roll back during commit. In this case we'd also expect getting no notification from this transaction at all and this is what is violated here. Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
David Fetter wrote: Folks, Would it be worthwhile to light up some buildfarm animals on OpenVMS? http://www.openvms.org/stories.php?story=10/02/09/2319162 Sure, go for 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] ToDo: plpgsql plugin for query and expression verification
2010/2/16 Hitoshi Harada umi.tan...@gmail.com: 2010/2/16 Pavel Stehule pavel.steh...@gmail.com: I think, so these problem have to be identified in compile stage - but it can be too strict for all (and can slow down production) - it is reason for plugin. What do you think about this idea? How do you identify them? Running function body cannot be applied if the function is volatile. Also, I don't see how do you choose function argument values even in immutable cases. It is issue only for dynamic sql and polymorphic functions. But for all others we can do full check in validation stage. I thinking about similar tool to lint - just for plpgsql function. It cannot detect all bugs, but it can diagnose 99% of possible issues. I don't would to execute function - it is useless because you need good UI for execution all path. My idea is different. gram.y has check_sql_expr rutine. This is used for parser checking every static SQL fragment in plpgsql function. With some hook we can do full plan generation instead. Regards Pavel Stehule Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
Greg Smith wrote: If you think through the implications of that far enough, eventually you start to realize that you really can't even add a feature that requires an in-place upgrade hack to fix without first having the code that performs said hack done. Otherwise you're never completely sure that you put the right catalog pieces and related support code into the version you want to upgrade from. This is why it's not unheard of for commercial database products to require a working in-place upgrade code *before* the feature change gets committed. In this case, we get a lucky break in that it's easy to leave support for old path in there and punt the problem for now. I hope that we all learn something useful about this class of issue during this opportunity to get away with that with little downside. Yea, the crux of the matter is that we are getting away easy with 9.0 in only having to keep around some MOVE_* code in tqual.c. This is just the start of the pain we will have to bear for inplace upgrades. :-( The MOVE_* bits go away after a while by vacuum and there is an easy solution for 9.1 --- vacuum everything in 9.0. Where things really get hard is when we have to support two page formats or two data formats in the same database. You might think we will never get there, but there have been such changes in the past, and I suspect that we will have them in the future, maybe not in 9.1, but perhaps 9.3. Ultimately we are going to have to decide how to resolve the burden of code used just for binary upgrades, and as Tom pointed out, it is very hard to remove the old data format in the old database because new sessions could be creating it while it is being removed. It seems that only the next major version can clean out the old format, meaning you have to keep support for the old format around for a full major release, add code to remove it in that major release too, then remove all of the code in the _next_ major release. This is frankly a complexity we have never had to deal with before, and we don't even have the infrastructure to track that all of the old format is gone. So, in summary, MOVE_* problems look minor compared to the complexities ahead. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bug? autovacuum is not launched even if autovacuum_freeze_max_age is reached
Fujii Masao escribió: Hi, In HEAD, when autovacuum is disabled, autovacuum process is not launched forcibly to prevent XID wraparound even if we go through autovacuum_freeze_max_age. This seems to be because ShmemVariableCache-xidVacLimit is not initialized (i.e., SetTransactionIdLimit() is not called) until VACUUM is performed. OTOH, in older version, ShmemVariableCache-xidVacLimit is always initialized when backend updates the flat database file, and then autovacuum process seems to be launched forcibly as expected. Hmm. Yeah, this is a serious problem. The only caller of SetTransactionIdLimit is now vac_truncate_clog. We need another one, but where? For a moment I thought about adding one to autovacuum launcher's initialization, but what if it's disabled? Maybe check at backend startup whether the limit is valid, and call SetTransactionIdLimit if not? Related commit history: the call to update the xid limit was removed here: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=186b10c1f0a61f90d320a4ccce2ff8b31fa55210 which introduced a function TransactionIdLimitIsValid which could presumably have been used as I propose above. However, it was taken out in http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1a7bbfe645b8379e368ec9969f99fee455e3ecf3 -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Jakub Ouhrabka wrote: You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf Thanks, tried it. There is nothing in the log - the actual vacuum/analyze commands are not run (as there is no query activity). I suspect that autovacuum is checking each database if it should run - and decides not to run. See the randomly catch process in ps output/pg_stat_activity mentioned in earlier mail. I suspect that this checking generates the load. Is it possible? Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. Note that autovacuum_naptime=1min (default value) means that it's checking stats 650 times per minute (there's a throttle IIRC but still). Maybe you should decrease naptime a bit. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On Mon, Feb 15, 2010 at 7:51 PM, Jeroen Vermeulen j...@xs4all.nl wrote: AFAIC a statement could go to re-planning mode if the shortest execution time for the generic plan takes at least 10x longer than the longest planning time. That gives us a decent shot at finding statements where re-planning is a safe bet. A parameter that we or the user would have to tweak would just be a fragile approximation of that. So in principle I agree with this idea. I think a conservative value for the constant would be more like 100x though. If I told you we had an easy way to speed all your queries up by 10% by caching queries but were just choosing not to then I think you would be unhappy. Whereas if I told you we were spending 1% of the run-time planning queries I think most people would not be concerned. There's a second problem though. We don't actually know how long any given query is going to take to plan or execute. We could just remember how long it took to plan and execute last time or how long it took to plan last time and the average execution time since we cached that plan. Perhaps we should track the stddev of the execution plan, or the max execution time of the plan? Ie there are still unanswered questions about the precise heuristic to use but I bet we can come up with something reasonable. -- 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] Avoiding bad prepared-statement plans.
On Mon, Feb 15, 2010 at 7:11 PM, Bruce Momjian br...@momjian.us wrote: 1. Why do we only do bind-level planning for anonymous wire-level queries? 2. I realize we did anonymous-only because that was the only way we had in the protocol to _signal_ bind-time planning, but didn't we think of this when we were implementing the wire-level protocol? Is there any other difference between anonymous and non-anonymous queries? If this is the only major difference do we need to separate them? Is there any particular reason a driver would need two prepared queries if they're both just going to be planned at execution time? Incidentally, can you have two active anonymous portals at the same time? 4. Why don't we just always do planning at first bind time? When is that worse than using generic values? 6. When do our generic columns costs significantly worse than having specific constants? I assume unique columns are fine with generic constants. Well using parameters will always have a better chance of producing a better plan but that's not the only factor people consider important. For a lot of users *predictability* is more important than absolute performance. If my web server could run 10% faster that might be nice but if it's capable of keeping up at its current speed it's not terribly important. But if it means it crashes once a day because some particular combination of parameters causes a bad plan to be used for a specific user that's a bad trade-off. -- 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] Avoiding bad prepared-statement plans.
Well using parameters will always have a better chance of producing a better plan but that's not the only factor people consider important. For a lot of users *predictability* is more important than absolute performance. If my web server could run 10% faster that might be nice but if it's capable of keeping up at its current speed it's not terribly important. But if it means it crashes once a day because some particular combination of parameters causes a bad plan to be used for a specific user that's a bad trade-off. +1 Pavel -- 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] Explain buffers display units.
Greg Stark escribió: On Tue, Feb 16, 2010 at 2:48 AM, Robert Haas robertmh...@gmail.com wrote: Upon further review, I also notice that this patch seems to have falsified the EXPLAIN documentation - both the description of the BUFFERS option and the description of the FORMAT option are no longer accurate Oops. Well, I would like to know if I'm in the minority and have to roll this back before I fix that. My personal opinion is that displaying number of blocks in all EXPLAIN formats is more consistent. What are you going to do with YAML output anyway, which is machine readable yet some people prefer over our legacy text format? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Jakub Ouhrabka escreveu: These databases are archive databases, so there is no user activity - no connected users. But the stats collector generates load - 20-40% of modern 2.8GHz core all the time. Did you try to set stats_temp_directory in a RAM based filesystem? Any clues what does it cause and how to investigate it? OProfile? -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
David Fetter da...@fetter.org writes: Would it be worthwhile to light up some buildfarm animals on OpenVMS? Have we ever even claimed to support VMS? I have no particular desire to undertake a major new porting effort. 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: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Tue, Feb 16, 2010 at 2:04 PM, Bruce Momjian br...@momjian.us wrote: The MOVE_* bits go away after a while by vacuum and there is an easy solution for 9.1 --- vacuum everything in 9.0. Where things really get hard is when we have to support two page formats or two data formats in the same database. You might think we will never get there, but there have been such changes in the past, and I suspect that we will have them in the future, maybe not in 9.1, but perhaps 9.3. I think a O(size of database) step in the upgrade process is acceptable iff it can be performed while the database is operational. In this case that would mean having some code in 8.4.3 to prevent VACUUM FULL from being used once a flag indicating that a migration is under way. Then you would have to vacuum every table which would set a flag indicating that no MOVED_* bits were set. Then pg_migrator would check that that flag was set on every table before allowing you to migrate. This might actually be a reasonable thing to put in 9.0. We already have the code to prevent you from running VACUUM FULL -- namely that it doesn't exist any longer. And I think we can tell whether there are any MOVED_* bits set by looking at the vacuum freeze age of the table. The only thing we're missing is the youngest xid seen in 8.4 before the 9.0 migration. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 9.0 - core dump - plpgsql - #option dump
Hello
Server crash on code
postgres=# create or replace function f() returns void as $$#option
dump begin for i in 1..10 loop raise notice '%', i; end loop; return;
end $$ language plpgsql;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!
Program terminated with signal 11, Segmentation fault.
#0 0x7ffa5c92b9f3 in dump_expr (expr=value optimized out)
at pl_funcs.c:999
999 printf('%s', expr-query);
Missing separate debuginfos, use: debuginfo-install glibc-2.10.2-1.x86_64
(gdb)
Missing separate debuginfos, use: debuginfo-install glibc-2.10.2-1.x86_64
(gdb) bt
#0 0x7ffa5c92b9f3 in dump_expr (expr=value optimized out)
at pl_funcs.c:999
#1 dump_fori (expr=value optimized out) at pl_funcs.c:548
#2 0x7ffa5c92b190 in dump_stmt (stmt=value optimized out)
at pl_funcs.c:332
#3 dump_stmts (stmt=value optimized out) at pl_funcs.c:392
#4 0x7ffa5c92b5ae in dump_block (block=0x17d1758) at pl_funcs.c:409
#5 0x7ffa5c92b77c in plpgsql_dumptree (func=0x1761a78)
at pl_funcs.c:1085
#6 0x7ffa5c9226ed in do_compile (forValidator=value optimized out,
hashkey=value optimized out, function=value optimized out,
procTup=value optimized out, fcinfo=value optimized out)
at pl_comp.c:699
#7 plpgsql_compile (forValidator=value optimized out,
hashkey=value optimized out, function=value optimized out,
procTup=value optimized out, fcinfo=value optimized out)
at pl_comp.c:220
#8 0x7ffa5c91eed2 in plpgsql_validator (fcinfo=value optimized out)
at pl_handler.c:292
Regards
Pavel Stehule
--
Sent 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] possible bug with inheritance?
Bruce Momjian wrote:
For primary key, there is no enforcement of the primary key, e.g.:
test= CREATE TABLE parent (name TEXT);
CREATE TABLE
test= CREATE TABLE child (age INT) inherits (parent) ;
CREATE TABLE
test= ALTER TABLE parent ADD primary KEY (name);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
parent_pkey for table parent
ALTER TABLE
test= INSERT INTO parent (name) VALUES ('a');
INSERT 0 1
test= INSERT INTO child (name) VALUES ('a');
INSERT 0 1
test= SELECT * FROM parent;
name
--
a
a
(2 rows)
So, it seems like this is the ugly truth of our inheritance limitations
with primary key, and unless we can fix the primary key issues with
inheritance, our current behavior is the more predictable we can hope for.
[ Thread moved to hackers because this might be a valid bug. ]
Summary: ALTER TABLE SET NOT NULL on a parent table is passed to the
child, while ALTER TABLE ADD PRIMARY KEY is not, particularly the NOT
NULL part of the PRIMARY KEY specification.
OK, now I understand what you are getting at --- the following returns a
NULL value from the parent:
test= CREATE TABLE parent (name text);
CREATE TABLE
test= CREATE TABLE child (age int) INHERITS (parent) ;
CREATE TABLE
test= ALTER TABLE parent ADD PRIMARY KEY (name);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
parent_pkey for table parent
ALTER TABLE
test= INSERT INTO child (name) VALUES (null);
INSERT 0 1
test= \pset null '(null)'
Null display is (null).
test= SELECT * FROM parent;
name
(null)
(1 row)
while the parent has a NOT NULL specification:
test= \d parent
Table public.parent
Column | Type | Modifiers
+--+---
name | text | not null
Indexes:
parent_pkey PRIMARY KEY, btree (name)
Number of child tables: 1 (Use \d+ to list them.)
That does seem like something that should be fixed.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LISTEN/NOTIFY and notification timing guarantees
Joachim Wieland j...@mcknight.de writes: On Tue, Feb 16, 2010 at 1:31 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane wrote: We could adopt the historical policy of sending self-notifies pre-commit, but that doesn't seem tremendously appetizing from the standpoint of transactional integrity. But one traditional aspect of transactional integrity is that a transaction always sees *its own* uncommitted work. True but notifications aren't sent until the transaction commits anyway. At the time when an application receives its self-notifies, it has already committed the transaction so there is no uncommitted work anymore. Right. The application's view is that it sends COMMIT and gets any self-notifies back as part of the response to that. What is worrisome is that the notifies come out just before the actual commit and so it's still (barely) possible for the transaction to abort. In which case it should not have sent the notifies, and indeed did not send them as far as any other client is concerned. We really ought to try to make a similar guarantee for self-notifies. After sleeping on it I'm fairly convinced that we should approach it like this: 1. No special data path for self-notifies; we expect to pull them back out of the queue just like anything else. 2. Add an extra lock to serialize writers to the queue, so that messages are guaranteed to be added to the queue in commit order. As long as notify-sending is nearly the last thing in the pre-commit sequence, this doesn't seem to me to be a huge concurrency hit (certainly no worse than the existing implementation) and the improved semantics guarantee seems worth it. 3. When a transaction has sent notifies, perform an extra ProcessIncomingNotifies scan after finishing up post-commit work (so that an error wouldn't result in PANIC) but before we issue ReadyForQuery to the frontend. This will mean that what the client sees is CommandComplete message for COMMIT (or NOTIFY) NotificationResponse messages, including self-notifies ReadyForQuery where the notifies are guaranteed to arrive in commit order. This compares to the historical behavior of NotificationResponse messages for self-notifies CommandComplete message for COMMIT (or NOTIFY) ReadyForQuery NotificationResponse messages for other transactions where there's no particular guarantee about ordering of notifies from different transactions. At least for users of libpq, postponing the self-notifies till after CommandComplete won't make any difference, because libpq reads to the ReadyForQuery message before deciding the query is done. 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] auto_explain causes regression failures
With the following settings
custom_variable_classes = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_format = 'xml'
auto_explain.log_analyze = on
auto_explain.log_verbose = on
shared_preload_libraries = 'auto_explain'
I am getting regression failures on the rowtypes, transactions and
arrays tests. Diff file is attached. I'm going to look into it, but if
anyone has a good idea what's going on please speak up ASAP.
cheers
andrew
*** /home/andrew/pgl/pgsql.expltry2/src/test/regress/expected/transactions.out
2009-08-09 19:29:31.0 -0400
--- /home/andrew/pgl/pgsql.expltry2/src/test/regress/results/transactions.out
2010-02-16 10:28:28.0 -0500
***
*** 496,504
(1 row)
rollback to x;
-- should fail
fetch from foo;
! ERROR: cursor foo does not exist
commit;
begin;
create table abc (a int);
--- 496,506
(1 row)
rollback to x;
+ WARNING: AbortSubTransaction while in ABORT state
+ ERROR: cache lookup failed for attribute 1 of relation 28260
-- should fail
fetch from foo;
! ERROR: current transaction is aborted, commands ignored until end of
transaction block
commit;
begin;
create table abc (a int);
***
*** 527,532
--- 529,536
(1 row)
abort;
+ WARNING: AbortTransaction while in ABORT state
+ ERROR: cache lookup failed for attribute 1 of relation 28263
-- tests for the tid type
SELECT '(3, 3)'::tid = '(3, 4)'::tid;
?column?
==
*** /home/andrew/pgl/pgsql.expltry2/src/test/regress/expected/arrays.out
2009-08-09 19:29:31.0 -0400
--- /home/andrew/pgl/pgsql.expltry2/src/test/regress/results/arrays.out
2010-02-16 10:28:29.0 -0500
***
*** 15,22
--- 15,25
--
INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g)
VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}');
+ ERROR: unexpected refassgnexpr
UPDATE arrtest SET e[0] = '1.1';
+ ERROR: unexpected refassgnexpr
UPDATE arrtest SET e[1] = '2.2';
+ ERROR: unexpected refassgnexpr
INSERT INTO arrtest (f)
VALUES ('{too long}');
ERROR: value too long for type character(5)
***
*** 24,38
VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{foobar}',
'{{elt1, elt2}}', '{3.4, 6.7}',
'{abc,abcde}', '{abc,abcde}');
INSERT INTO arrtest (a, b[1:2], c, d[1:2])
VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}');
SELECT * FROM arrtest;
! a |b| c | d |e
|f| g
!
-+-+---+---+-+-+-
! {1,2,3,4,5} | {{{0,0},{1,2}}} | {}| {}| [0:1]={1.1,2.2}
| {} | {}
! {11,12,23} | {{3,4},{4,5}} | {foobar} | {{elt1,elt2}} | {3.4,6.7}
| {abc ,abcde} | {abc,abcde}
! {} | {3,4} | {foo,bar} | {bar,foo} |
| |
! (3 rows)
SELECT arrtest.a[1],
arrtest.b[1][1][1],
--- 27,40
VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{foobar}',
'{{elt1, elt2}}', '{3.4, 6.7}',
'{abc,abcde}', '{abc,abcde}');
+ ERROR: unexpected refassgnexpr
INSERT INTO arrtest (a, b[1:2], c, d[1:2])
VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}');
+ ERROR: unexpected refassgnexpr
SELECT * FROM arrtest;
! a | b | c | d | e | f | g
! ---+---+---+---+---+---+---
! (0 rows)
SELECT arrtest.a[1],
arrtest.b[1][1][1],
***
*** 40,90
arrtest.d[1][1],
arrtest.e[0]
FROM arrtest;
! a | b | c| d | e
! +---++--+-
! 1 | 0 || | 1.1
! 11 | | foobar | elt1 |
! | | foo| |
! (3 rows)
SELECT a[1], b[1][1][1], c[1], d[1][1], e[0]
FROM arrtest;
! a | b | c| d | e
! +---++--+-
! 1 | 0 || | 1.1
! 11 | | foobar | elt1 |
! | | foo| |
! (3 rows)
SELECT a[1:3],
b[1:1][1:2][1:2],
c[1:2],
d[1:1][1:2]
FROM arrtest;
! a |b| c | d
! +-+---+---
! {1,2,3}| {{{0,0},{1,2}}} | {}| {}
! {11,12,23} | {} | {foobar} | {{elt1,elt2}}
! {} | {} | {foo,bar} | {}
! (3 rows)
SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c
FROM arrtest;
a | b | c
---+---+---
! 1 | 3 |
! 1 | 2 | 1
!| 1 | 1
! (3 rows)
SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c
FROM arrtest;
!a |b| c
! ---+-+---
! [1:5] | [1:1][1:2][1:2] |
! [1:3] | [1:2][1:2] | [1:1]
!| [1:2] |
Re: [HACKERS] ToDo: plpgsql plugin for query and expression verification
Pavel Stehule pavel.steh...@gmail.com writes: I don't would to execute function - it is useless because you need good UI for execution all path. My idea is different. gram.y has check_sql_expr rutine. This is used for parser checking every static SQL fragment in plpgsql function. With some hook we can do full plan generation instead. Previous proposals in this line have foundered on examples like functions that create a temp table and then manipulate it. Only DDL-free functions can be statically checked in the way you suggest. Between that and the parameter-related limitations that Hitoshi points out, the use case seems to be rather restricted ... 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] psycopg2 license changed
Federico Di Gregorio wrote: Even if tests and examples code aren't almost never distributed except in the psycopg2 source package? A couple of other people contributed to the tests: if you really feel like it is so important I'll contact them and ask their permission to use the LGPL3 + exception (the contribution was without the exception) or remove the code (we won't lose much.) Yes, I believe you must contact any code contributors before changing the license because the assumption is that those code contributions matched the license at the time the code was contributed. If the license changes, the original contributions retain the original license unless you get their approval. Dave Page went through this when he changed the license of pgAdmin --- he had to contact all previous code contributors to get their approval. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Explain buffers display units.
Alvaro Herrera alvhe...@commandprompt.com writes: Greg Stark escribió: Oops. Well, I would like to know if I'm in the minority and have to roll this back before I fix that. My personal opinion is that displaying number of blocks in all EXPLAIN formats is more consistent. FWIW, I vote for number of blocks too. I tend to see those numbers as more indicative of number of I/O requests than amount of memory used. 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] psycopg2 license changed
Greg Smith wrote: Federico Di Gregorio wrote: Even if tests and examples code aren't almost never distributed except in the psycopg2 source package? A couple of other people contributed to the tests: if you really feel like it is so important I'll contact them and ask their permission to use the LGPL3 + exception (the contribution was without the exception) or remove the code (we won't lose much.) I understand that from a technical perspective these are all different bits. But the sort of people who get stressed about licenses might not, and that's why it's always better to have a simple, standard, unified license that covers the entire chunk of software you're packaging. If the examples show up in the source package, that means the source package has two licenses instead of one, and that's a bad thing. It's not a huge issue, I'm just afraid that if you don't get this nailed down now there's just going to another round of this tedious license investigation in the future one day. I'd think it's better for you and everyone else in the long run to just completely unify the license. And if takes another release for the examples to get that license change, I think that's OK. I wouldn't hold up the big work here--getting your next release out with the big LGPL3 switch for the main code--over this bit of trivia. I just think it's a potential future headache you should try to remove when you can. Agreed. A single license is easier unless there is some value in having two licenses. Doing another release to improve the license is certainly worthwhile. I also want to thank you for being flexible on this licensing issue. I never suspected we would come up with a solution so quickly. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
It could be interesting to see how big a porting effort it was ... ? I'd say go for it and let's see what is involved ... On Tue, 16 Feb 2010, Tom Lane wrote: David Fetter da...@fetter.org writes: Would it be worthwhile to light up some buildfarm animals on OpenVMS? Have we ever even claimed to support VMS? I have no particular desire to undertake a major new porting effort. 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 Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: plpgsql plugin for query and expression verification
2010/2/16 Pavel Stehule pavel.steh...@gmail.com: 2010/2/16 Hitoshi Harada umi.tan...@gmail.com: 2010/2/16 Pavel Stehule pavel.steh...@gmail.com: I think, so these problem have to be identified in compile stage - but it can be too strict for all (and can slow down production) - it is reason for plugin. What do you think about this idea? How do you identify them? Running function body cannot be applied if the function is volatile. Also, I don't see how do you choose function argument values even in immutable cases. It is issue only for dynamic sql and polymorphic functions. But for all others we can do full check in validation stage. I thinking about similar tool to lint - just for plpgsql function. It cannot detect all bugs, but it can diagnose 99% of possible issues. I don't would to execute function - it is useless because you need good UI for execution all path. My idea is different. gram.y has check_sql_expr rutine. This is used for parser checking every static SQL fragment in plpgsql function. With some hook we can do full plan generation instead. Hmm, type mismatching can be checked by your suggestion, but that's it. The true answer to your original post might be write unit test, isn't it? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bug? autovacuum is not launched even if autovacuum_freeze_max_age is reached
Alvaro Herrera alvhe...@commandprompt.com writes: Fujii Masao escribió: In HEAD, when autovacuum is disabled, autovacuum process is not launched forcibly to prevent XID wraparound even if we go through autovacuum_freeze_max_age. This seems to be because ShmemVariableCache-xidVacLimit is not initialized (i.e., SetTransactionIdLimit() is not called) until VACUUM is performed. OTOH, in older version, ShmemVariableCache-xidVacLimit is always initialized when backend updates the flat database file, and then autovacuum process seems to be launched forcibly as expected. Hmm. Yeah, this is a serious problem. I thought I had put in some workaround for that when I did the flat file changes. I don't remember what though, and it's evidently broken now in any case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 * We also discussed the idea of having a NOTIFY command that would work from Primary to Standby. Just curious, what's a use case for this? - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201002161102 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkt6wZ4ACgkQvJuQZxSWSsjrYwCfSWvHlTBFT/fIYcBToX9C57GO toAAoOLQhBj6NdVTayaVtRH8L7nk16qM =LBAH -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: plpgsql plugin for query and expression verification
2010/2/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I don't would to execute function - it is useless because you need good UI for execution all path. My idea is different. gram.y has check_sql_expr rutine. This is used for parser checking every static SQL fragment in plpgsql function. With some hook we can do full plan generation instead. Previous proposals in this line have foundered on examples like functions that create a temp table and then manipulate it. Only DDL-free functions can be statically checked in the way you suggest. No and yes. yes - 100% test are possible only on a) DDL free functions, b) 100% static schema. no - in reality schema is usually stable and we are able to check sql using stable schema. This proposal isn't about ideal checking - it isn't possible. It is about the maximum from what is possible. I would to identify bugs in not often using execution path before production. This case is real. Stored procedures works well and after half of year we finding broken identifiers in some queries. Between that and the parameter-related limitations that Hitoshi points out, the use case seems to be rather restricted ... why? why is it better? do you have a way for runtime checking of all possible execution path? regards Pavel 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] CommitFest Status Summary - 2010-02-14
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: * Listen / Notify rewrite. This is the only one of the remaining patches that is not marked as Ready for Committer, but I think it would be good if someone (probably Tom) at least took a look at it. I'm not sure if it's committable at this point, but we should at least try to provide some good feedback. I will look at this one. It'd be nice to get it in if at all possible, because the existing listen/notify infrastructure won't play very nicely with HS --- eg, inspecting pg_listener on the slave might yield the false impression that some of the slave-side backends had active LISTENs because of chance matches of PID. Good point. Is pg_listener the only place we expose PIDs in heap files? I know we expose them in views but those are not affected by HS, I believe. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
On Tue, Feb 16, 2010 at 08:11:15AM -0500, Andrew Dunstan wrote: David Fetter wrote: Folks, Would it be worthwhile to light up some buildfarm animals on OpenVMS? http://www.openvms.org/stories.php?story=10/02/09/2319162 Sure, go for it. cheers andrew Here's what I sent them: First Name : David Last Name : Fetter Organization : PostgreSQL Global Development Group E-mail Address : davidfet...@postgresql.org Products being ported : PostgreSQL We'll also need * Shell access from several accounts * Git or cvs client * Compiler tools * Perl of a fairly recent vintage * Outbound http access Warm Regards, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Explain buffers display units.
On Tue, Feb 16, 2010 at 3:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Greg Stark escribió: Oops. Well, I would like to know if I'm in the minority and have to roll this back before I fix that. My personal opinion is that displaying number of blocks in all EXPLAIN formats is more consistent. FWIW, I vote for number of blocks too. I tend to see those numbers as more indicative of number of I/O requests than amount of memory used. Ok, that's 3:1 against. I suspect we'll revisit this once you see all the other instrumentation I plan for 9.1. It will be much easier to make sense of all the numbers in consistent units. But we'll see then. I won't be able to do the rollback until about 11pm EST again today. -- 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] buildfarm breakage
Zdenek Kotala wrote: Andrew Dunstan p??e v po 08. 02. 2010 v 20:07 -0500: Our Solaris *moth members seem to have stopped building. Have we lost them? Hi Andrew, The answer is not simple. Yes, we lost Solaris 8 and 9 machines which was reinstalled and now they are used for different purpose. It was planned before the April and I announced it long time ago. It unfortunately happed and timing looks strange. And I did not find replacement. I have replacement for nevada/x86 machine already, but I need to setup it which is one item in my very long TODO list :(. Solaris 10 Sparc/x86 and nevada sparc are covered at this moment. I think we have to accept the inevitable result that Postgres support on Solaris is going to diminish over time. We certainly are going to get less support from Sun/Oracle, and one day the use of Postgres might even be obstructed on Solaris or void software support contracts. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:
Yes, IIRC, 0 == unknown as far as the server is concerned. It just
tells the server to resolve it when it can.
An extra source of puzzlement is that the oid of the 'unknown' type is
705 not 0, and the unknown type isn't discussed in the docs (as far as I
could see).
Yes, I noticed that, too. Greg, do you know the answer to that?
http://developer.postgresql.org/pgdocs/postgres/libpq-exec.html saysGuess I
need to go and check the current behaviour... see below.
And like maybe a doc patch might be useful.
I would be great if someone who understood
Can any SPI experts chime in here? It seems that the ability to omit types for
parameters in spi_prepare() is undocumented. Is that officially okay?
These appear to be identical in behaviour:
spi_prepare(select * from foo($1,$2), 'unknown', 'unknown');
spi_prepare(select * from foo($1,$2), 'unknown')
spi_prepare(select * from foo($1,$2))
Ah, interesting.
Wouldn't work unless you'd installed an AUTOLOAD function into each
schema:: package that you wanted to use. (schema-SP::function_name()
could be made to work but that's just too bizzare :)
Maybe SP-schema('public')-function_name()? I kind of like the idea of objects
created for specific schemas, though (as in your example). Maybe that, too, is
something that could be specified in the `use`statement. Or maybe
`SP::schema-function`? That's kind of nice, keeps things encapsulated under
SP. You could then do the identifier quoting, too. The downside is that, once
loaded, the schema package names would be locked down. If I created a new
schema in the connection, SP wouldn't know about it.
Something like that is probably best. I've made PostgreSQL::PLPerl::Call
export both call and SP where SP is a constant containing the name
of a class (PostgreSQL::PLPerl::Call::SP) that just has an AUTOLOAD.
Cool, thanks!
From the docs:
Immediately after the function name, in parenthesis, a comma separated list of
type names can be given. For example:
'pi()'
'generate_series(int,int)'
'array_cat(int[], int[])'
'myschema.myfunc(date, float8)'
It could also just be 'pi', no?
Functions with Cvaradic arguments can be called with a fixed number of
arguments by repeating the type name in the signature the same number of
times.
I assume that type names can be omitted her, too, yes?
$pi = SP-pi();
$seqn = SP-nextval($sequence_name);
Using this form you can't easily specify a schema name or argument types, and
you can't call varadic functions.
Why not?
Also, I notice a few `==head`s. I think that's one too many =s.
You can take this approach further by specifying some of the arguments in the
anonymous subroutine so they don't all have to be provided in the call:
$some_func = sub { call('some_func(int, date[], int)', $foo, shift,
$debug) };
...
$val = $some_func-(\...@dates);
Currying! :-)
If the function was executed in scalar context then an exception will be
thrown
if more than one row is returned. For example:
Someone's going to want an iterator object/cursor. :-P
For varadic functions, separate plans are created and cached for each distinct
number of arguments the function is called with.
Why?
Functions with a varadic argument can't be called with no values for that
argument. You'll get a function ... does not exist error. This appears to
be
a PostgreSQL limitation.
Hrm. Worth enquiring about.
So, is this on GitHub yet? That way I can submit patches.
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Tue, Feb 16, 2010 at 09:11:24AM -0800, David E. Wheeler wrote: An extra source of puzzlement is that the oid of the 'unknown' type is 705 not 0, and the unknown type isn't discussed in the docs (as far as I could see). Yes, I noticed that, too. Greg, do you know the answer to that? My guess is that, semantically, 0 means the datatype is unknown, whereas 705 means the datatype is known to be type unknown. I believe however the backend treats these cases identically (at least, simple testing doesn't reveal any differences), but they are not the same. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] OpenVMS?
On Tue, Feb 16, 2010 at 11:22 AM, David Fetter wrote: * Shell access from several accounts * Git or cvs client * Compiler tools * Perl of a fairly recent vintage * Outbound http access I had access to the HP testdrive before they closed it down (the Unix servers were down in Sept 08, but the VMS cluster was running till Jan 2010). They blocked all outbound internet access to the testdrive servers -- only telnet and ftp were allowed. Count me in for the OpenVMS porting effort (but I guess I will apply for an account seperately as I might port other things to OpenVMS in the future). I believe the porting effort is larger than a new Unix port but smaller than the Windows port, as most of the Unix and POSIX functions and system calls are supported on OpenVMS. MySQL has around 10 functions changed or written specifically for OpenVMS, most of those are related to utime(), $UMASK $UMASKDIR, open(), and Unix pathnames. (I think utime() support was added a few years ago to OpenVMS 7.3 8.0 -- so may be the code was added to MySQL for earlier VMS versions.) PostgreSQL uses fork(), which is not supported on OpenVMS. However, the techniques used by the WIN32 version of internal_forkexec() in src/backend/postmaster/postmaster.c give the VMS version a good starting point. Lastly, are we going to support ODS-2 disks?? And are we going to require GNV installed for building and running PostgreSQL on OpenVMS?? Rayson Warm Regards, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 16/02/10 17:11, David E. Wheeler wrote:
On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:
Wouldn't work unless you'd installed an AUTOLOAD function into each
schema:: package that you wanted to use. (schema-SP::function_name()
could be made to work but that's just too bizzare :)
Maybe SP-schema('public')-function_name()? I kind of like the idea of objects
created for specific schemas, though (as in your example). Maybe that, too, is something
that could be specified in the `use`statement. Or maybe `SP::schema-function`?
That's kind of nice, keeps things encapsulated under SP. You could then do the
identifier quoting, too. The downside is that, once loaded, the schema package names
would be locked down. If I created a new schema in the connection, SP wouldn't know
about it.
Perhaps it would be better to be explicit about what's going on?
SEARCHPATH-function()
SCHEMA('public')-function2()
Or did SP mean Stored Procedure?
On a (kind of) related note, it might be worthwhile to mention
search_path in the docs and point out it has the same pros/cons as unix
file paths.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 16, 2010, at 9:43 AM, Richard Huxton wrote:
Perhaps it would be better to be explicit about what's going on?
SEARCHPATH-function()
SCHEMA('public')-function2()
Or did SP mean Stored Procedure?
Yes.
On a (kind of) related note, it might be worthwhile to mention search_path in
the docs and point out it has the same pros/cons as unix file paths.
+1. It's a little like file paths and a little like name spaces, without quite
being either one.
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
On Tue, Feb 16, 2010 at 12:39:29PM -0500, Rayson Ho wrote: On Tue, Feb 16, 2010 at 11:22 AM, David Fetter wrote: * Shell access from several accounts * Git or cvs client * Compiler tools * Perl of a fairly recent vintage * Outbound http access I had access to the HP testdrive before they closed it down (the Unix servers were down in Sept 08, but the VMS cluster was running till Jan 2010). They blocked all outbound internet access to the testdrive servers -- only telnet and ftp were allowed. Outbound http access is for the buildfarm, which is pretty important to how we develop. Count me in for the OpenVMS porting effort (but I guess I will apply for an account seperately as I might port other things to OpenVMS in the future). I believe the porting effort is larger than a new Unix port but smaller than the Windows port, as most of the Unix and POSIX functions and system calls are supported on OpenVMS. You clearly know vastly more than I do about this, and should lead this effort :) Lastly, are we going to support ODS-2 disks?? And are we going to require GNV installed for building and running PostgreSQL on OpenVMS?? You tell us :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 16/02/10 17:51, David E. Wheeler wrote:
On Feb 16, 2010, at 9:43 AM, Richard Huxton wrote:
Perhaps it would be better to be explicit about what's going on?
SEARCHPATH-function()
SCHEMA('public')-function2()
Or did SP mean Stored Procedure?
Yes.
Hmm - might be worth avoiding that in case we get actual
transaction-spanning stored procedures at any point.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Richard Huxton wrote:
On 16/02/10 17:51, David E. Wheeler wrote:
On Feb 16, 2010, at 9:43 AM, Richard Huxton wrote:
Perhaps it would be better to be explicit about what's going on?
SEARCHPATH-function()
SCHEMA('public')-function2()
Or did SP mean Stored Procedure?
Yes.
Hmm - might be worth avoiding that in case we get actual
transaction-spanning stored procedures at any point.
Yeah ... I used to get a lot of questions on the spanish list about
pgAdmin calling some functions procedures (just because they returned
void). While it may be technically true, it'd cause trouble if we ever
get around to supporting true procedures.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XQuery support
I know this has been discussed several times and it seems the conclusin was it's impossible if we would like to use existing XQuery external modules (some are by license reasons and some are by techinical reasons). So it seems the only way to support XQuery is, developing our own XQuery functionality from scratch. I'm wondering if other people reach the same conclusion as me, or is it a totaly impossible project? Well xquilla on top of xerces should get us pretty much were we need to go. They are both under the Apache 2 license, which I believe would be ok. But they are C++ so I don't know if we could use them. I found Zorba, its C++ and Apache 2. But I don't know much about it. But it looks promising. http://www.zorba-xquery.com/ Sounds like a great project. Please, let us know (zorba-us...@lists.sourceforge.net) if you need any help or have questions with regard to plugging Zorba to PostGreSQL. We would love to see this happen and help. Best regards, Matthias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Tue, 2010-02-16 at 16:02 +, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 * We also discussed the idea of having a NOTIFY command that would work from Primary to Standby. Just curious, what's a use case for this? If you have some kind of cache above the DBMS, you need to invalidate it when a part of the database is updated. It makes sense that every reader would want to know about the update, not just those connected to the master. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LISTEN/NOTIFY and notification timing guarantees
On Tue, 2010-02-16 at 10:38 -0500, Tom Lane wrote: 2. Add an extra lock to serialize writers to the queue, so that messages are guaranteed to be added to the queue in commit order. I assume this is a heavyweight lock, correct? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NaN/Inf fix for ECPG
On Tue, Feb 16, 2010 at 12:21:34PM +0100, Boszormenyi Zoltan wrote: Does FreeBSD/MIPS really return true for isinf(NaN)? Actually it's a netbsd beta version, so maybe there's a bug in their libc. But anyway, the patch doesn't seem to hurt, so I committed it. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Maybe you should decrease naptime a bit. That did the trick, thanks! Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. I looked at the strace output and there are *writes* to the file not reads. Why? Is it a consequence of this optimization? Release notes 8.4: Reduce I/O load of writing the statistics collection file by writing the file only when requested (Martin Pihlak) Was autovacuum requesting to write this 20MB file 650x per minute? Anyway, thank you all for the quick answer and precise answers. PostgreSQL is really unique in this regard! Kuba Dne 16.2.2010 15:10, Alvaro Herrera napsal(a): Jakub Ouhrabka wrote: You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf Thanks, tried it. There is nothing in the log - the actual vacuum/analyze commands are not run (as there is no query activity). I suspect that autovacuum is checking each database if it should run - and decides not to run. See the randomly catch process in ps output/pg_stat_activity mentioned in earlier mail. I suspect that this checking generates the load. Is it possible? Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. Note that autovacuum_naptime=1min (default value) means that it's checking stats 650 times per minute (there's a throttle IIRC but still). Maybe you should decrease naptime a bit. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Jakub Ouhrabka wrote: Maybe you should decrease naptime a bit. That did the trick, thanks! Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. I looked at the strace output and there are *writes* to the file not reads. Why? Is it a consequence of this optimization? Release notes 8.4: Reduce I/O load of writing the statistics collection file by writing the file only when requested (Martin Pihlak) Was autovacuum requesting to write this 20MB file 650x per minute? Yes, exactly. Ideally, autovacuum would only request a new copy of the file if the one it got was considerably out of date. Obviously a tenth of a second is not old enough. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NaN/Inf fix for ECPG
Michael Meskes írta: On Tue, Feb 16, 2010 at 12:21:34PM +0100, Boszormenyi Zoltan wrote: Does FreeBSD/MIPS really return true for isinf(NaN)? Actually it's a netbsd beta version, so maybe there's a bug in their libc. I realized my typo after sending my mail. Sorry if I offended anyone calling NetBSD FreeBSD. :-) But anyway, the patch doesn't seem to hurt, so I committed it. Thanks. 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] Streaming Replication on win32
2010/2/16 Fujii Masao masao.fu...@gmail.com: On Tue, Feb 16, 2010 at 1:33 AM, Magnus Hagander mag...@hagander.net wrote: 2010/2/15 Tom Lane t...@sss.pgh.pa.us: Magnus Hagander mag...@hagander.net writes: I changed your patch to this, because I find it a lot simpler. The change is in the checking in pgwin32_recv - there is no need to ever call waitforsinglesocket, we can just exit out early. Thanks a lot, Magnus! Do you see any issue with that? This definitely looks cleaner, but is there a reason not to use bool instead of int here? No. Can include/port/win32.h refer to bool type? Nope, you're correct, it can't. Committed without that. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LISTEN/NOTIFY and notification timing guarantees
On Tue, Feb 16, 2010 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: 2. Add an extra lock to serialize writers to the queue, so that messages are guaranteed to be added to the queue in commit order. As long as fwiw, I think you're definitely on the right track. IMO, any scenario where an issued notification ends up being deferred for an indefinite period of time without alerting the issuer should be avoided if at all possible. Just to clarify though, does your proposal block all notifiers if any uncommitted transaction issued a notify? 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] Problem with 8.4 stats collector high load
Ideally, autovacuum would only request a new copy of the file if the one it got was considerably out of date. Obviously a tenth of a second is not old enough. I've tried to look at it and found that's already implemented - see autovac_refresh_stats(). STATS_READ_DELAY which is set to 1s. Am I reading the code correctly? If so then 1s is not enough for big clusters. I guess it would be feasible to crank STATS_READ_DELAY up a little bit, say to 10s. What do you think? Kuba Dne 16.2.2010 19:59, Alvaro Herrera napsal(a): Jakub Ouhrabka wrote: Maybe you should decrease naptime a bit. That did the trick, thanks! Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. I looked at the strace output and there are *writes* to the file not reads. Why? Is it a consequence of this optimization? Release notes 8.4: Reduce I/O load of writing the statistics collection file by writing the file only when requested (Martin Pihlak) Was autovacuum requesting to write this 20MB file 650x per minute? Yes, exactly. Ideally, autovacuum would only request a new copy of the file if the one it got was considerably out of date. Obviously a tenth of a second is not old enough. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Jakub Ouhrabka wrote: Ideally, autovacuum would only request a new copy of the file if the one it got was considerably out of date. Obviously a tenth of a second is not old enough. I've tried to look at it and found that's already implemented - see autovac_refresh_stats(). STATS_READ_DELAY which is set to 1s. Am I reading the code correctly? If so then 1s is not enough for big clusters. Note that it says it's not used for autovacuum workers; it's only used for the autovacuum launcher. The workers have their own set of problems, particularly the bit that two of them might choose to vacuum the same table. I don't think this is so serious a problem in 8.4, so maybe we could take out the check that limits it to the launcher. However, it needs some thought. You could try removing the if line and make it work unconditionally and see if it fixes the problem for you, even at the 1s value. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS]
_ Hotmail: Trusted email with powerful SPAM protection. http://clk.atdmt.com/GBL/go/201469227/direct/01/
Re: [HACKERS] OpenVMS?
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Rayson Ho Sent: Tuesday, February 16, 2010 9:39 AM To: David Fetter Cc: Andrew Dunstan; PG Hackers Subject: Re: [HACKERS] OpenVMS? On Tue, Feb 16, 2010 at 11:22 AM, David Fetter wrote: * Shell access from several accounts * Git or cvs client * Compiler tools * Perl of a fairly recent vintage * Outbound http access I had access to the HP testdrive before they closed it down (the Unix servers were down in Sept 08, but the VMS cluster was running till Jan 2010). They blocked all outbound internet access to the testdrive servers -- only telnet and ftp were allowed. Count me in for the OpenVMS porting effort (but I guess I will apply for an account seperately as I might port other things to OpenVMS in the future). I believe the porting effort is larger than a new Unix port but smaller than the Windows port, as most of the Unix and POSIX functions and system calls are supported on OpenVMS. MySQL has around 10 functions changed or written specifically for OpenVMS, most of those are related to utime(), $UMASK $UMASKDIR, open(), and Unix pathnames. (I think utime() support was added a few years ago to OpenVMS 7.3 8.0 -- so may be the code was added to MySQL for earlier VMS versions.) PostgreSQL uses fork(), which is not supported on OpenVMS. However, the techniques used by the WIN32 version of internal_forkexec() in src/backend/postmaster/postmaster.c give the VMS version a good starting point. For PostgreSQL, you will probably want to use LIB$SPAWN() as a rough equivalent to CreateProcess() on Windows http://www.sysworks.com.au/disk$vaxdocsep002/opsys/vmsos721/5932/5932pro_041.html Lastly, are we going to support ODS-2 disks?? And are we going to require GNV installed for building and running PostgreSQL on OpenVMS?? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
Greg Stark wrote:
On Mon, Feb 15, 2010 at 7:11 PM, Bruce Momjian br...@momjian.us wrote:
1. Why do we only do bind-level planning for anonymous wire-level queries?
2. I realize we did anonymous-only because that was the only way we had
in the protocol to _signal_ bind-time planning, but didn't we think of
this when we were implementing the wire-level protocol?
Is there any other difference between anonymous and non-anonymous
queries? If this is the only major difference do we need to separate
them? Is there any particular reason a driver would need two prepared
queries if they're both just going to be planned at execution time?
Well, anonymous prepared queries are replanned for _every_ bind, so I
don't see a huge value in allowing multiple unnamed queries, except you
have to re-send the old query to prepare if you need to reuse it.
In fact, this behavior was not totally clear so I updated the
documentation a little with the attached patch.
Incidentally, can you have two active anonymous portals at the same time?
No, the first one is deleted when the second is created, i.e., our docs
have:
An unnamed prepared statement lasts only until the next Parse statement
specifying the unnamed statement as destination is issued. (Note that a
simple Query message also destroys the unnamed statement.)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/protocol.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/protocol.sgml,v
retrieving revision 1.78
diff -c -c -r1.78 protocol.sgml
*** doc/src/sgml/protocol.sgml 3 Feb 2010 09:47:19 - 1.78
--- doc/src/sgml/protocol.sgml 16 Feb 2010 20:11:41 -
***
*** 737,745
para
The unnamed prepared statement is likewise planned during Parse processing
if the Parse message defines no parameters. But if there are parameters,
! query planning occurs during Bind processing instead. This allows the
! planner to make use of the actual values of the parameters provided in
! the Bind message when planning the query.
/para
note
--- 737,745
para
The unnamed prepared statement is likewise planned during Parse processing
if the Parse message defines no parameters. But if there are parameters,
! query planning occurs every time Bind parameters are supplied. This allows the
! planner to make use of the actual values of the parameters provided by
! each Bind message, rather than use generic estimates.
/para
note
Index: src/backend/tcop/postgres.c
===
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.588
diff -c -c -r1.588 postgres.c
*** src/backend/tcop/postgres.c 13 Feb 2010 01:32:19 - 1.588
--- src/backend/tcop/postgres.c 16 Feb 2010 20:11:45 -
***
*** 1469,1475
}
else
{
! /* special-case the unnamed statement */
psrc = unnamed_stmt_psrc;
if (!psrc)
ereport(ERROR,
--- 1469,1475
}
else
{
! /* Unnamed statements are re-prepared for every bind */
psrc = unnamed_stmt_psrc;
if (!psrc)
ereport(ERROR,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
Greg Stark wrote: On Tue, Feb 16, 2010 at 2:04 PM, Bruce Momjian br...@momjian.us wrote: The MOVE_* bits go away after a while by vacuum and there is an easy solution for 9.1 --- vacuum everything in 9.0. ?Where things really get hard is when we have to support two page formats or two data formats in the same database. ?You might think we will never get there, but there have been such changes in the past, and I suspect that we will have them in the future, maybe not in 9.1, but perhaps 9.3. I think a O(size of database) step in the upgrade process is acceptable iff it can be performed while the database is operational. In this case that would mean having some code in 8.4.3 to prevent VACUUM FULL from being used once a flag indicating that a migration is under way. Then you would have to vacuum every table which would set a flag indicating that no MOVED_* bits were set. Then pg_migrator would check that that flag was set on every table before allowing you to migrate. This might actually be a reasonable thing to put in 9.0. We already have the code to prevent you from running VACUUM FULL -- namely that it doesn't exist any longer. And I think we can tell whether there are any MOVED_* bits set by looking at the vacuum freeze age of the table. The only thing we're missing is the youngest xid seen in 8.4 before the 9.0 migration. That might work for this case, but I think long-term we will need to do such changes in the _next_ major release, and add some mechanism that pg_migrator could test to know that the old format is gone. I don't think backpatching to minor releases is really sustainable. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
Dann Corbit wrote: PostgreSQL uses fork(), which is not supported on OpenVMS. However, the techniques used by the WIN32 version of internal_forkexec() in src/backend/postmaster/postmaster.c give the VMS version a good starting point. For PostgreSQL, you will probably want to use LIB$SPAWN() as a rough equivalent to CreateProcess() on Windows Ah, LIB$SPAWN, that brings back memories. http://www.sysworks.com.au/disk$vaxdocsep002/opsys/vmsos721/5932/5932pro_041.html Lastly, are we going to support ODS-2 disks?? And are we going to require GNV installed for building and running PostgreSQL on OpenVMS?? I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
On Tue, Feb 16, 2010 at 2:56 PM, Dann Corbit wrote: For PostgreSQL, you will probably want to use LIB$SPAWN() as a rough equivalent to CreateProcess() on Windows We will need to support running PostgreSQL as a detached process, and thus it won't have access to the DCL CLI. The implication is that lib$spawn or system() will fail when used this way. http://labs.hoffmanlabs.com/node/794 So we will likely need to use sys$creprc() in internal_forkexec(). Rayson http://www.sysworks.com.au/disk$vaxdocsep002/opsys/vmsos721/5932/5932pro_041.html Lastly, are we going to support ODS-2 disks?? And are we going to require GNV installed for building and running PostgreSQL on OpenVMS?? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
Marc G. Fournier wrote: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? Well, there is going to be impact on the community too --- patches, testing, etc. The community effort is small, but isn't zero. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS]
_ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/201469229/direct/01/
Re: Looking for Bill Huang; was [HACKERS] Personal Copyright Notices
Bruce Momjian wrote: Bruce Momjian wrote: Some more _personalized_ copyright noticed have crept into our source tree: /src/tutorial/basics.sourceCopyright (c) 1994, Andrew Yu, University of California /contrib/intagg/Makefile Copyright (c) 2001 Digital Music Network by Mark L. Woodward /src/port/rint.c Copyright (c) 1999, repas AEG Automation GmbH /contrib/isn/isn.c Copyright (c) 2004-2006, Germn Mndez Bravo (Kronuz) /contrib/isn/isn.h Copyright (c) 2004-2006, Germn Mndez Bravo (Kronuz) /src/backend/utils/mb/Unicode/UCS_to_GB18030.plCopyright 2002 by Bill Huang I have now dealt with all of these except the last one: The UCS_to_GB18030.pl is a Perl script with this at the top: # Copyright 2002 by Bill Huang Does anyone know how to contact Bill Huang? Based on this posting: http://archives.postgresql.org/pgsql-announce/2002-06/msg3.php he lives in Japan and worked for Red Hat, and a Japan phone number is listed that I did not try. I did try emailing him at bill_huan...@ybb.ne.jp and hu...@redhat.com but both emails returned failure messages. The perl file is 100 lines so it is possible to rewrite it if necessary. This same file was mentioned in 2007 so I think we should just find a solution to this: http://archives.postgresql.org/pgsql-hackers/2007-03/msg01446.php I have been able to remove this last personal copyright thanks to Andreas 'ads' Scherbaum, who rewrote the perl script using another perl script from the same directory that was created before the offending file. Patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/utils/mb/Unicode/UCS_to_GB18030.pl === RCS file: /cvsroot/pgsql/src/backend/utils/mb/Unicode/UCS_to_GB18030.pl,v retrieving revision 1.5 diff -c -c -r1.5 UCS_to_GB18030.pl *** src/backend/utils/mb/Unicode/UCS_to_GB18030.pl 7 Mar 2005 04:30:52 - 1.5 --- src/backend/utils/mb/Unicode/UCS_to_GB18030.pl 16 Feb 2010 20:34:11 - *** *** 1,23 #! /usr/bin/perl # ! # Copyright 2002 by Bill Huang # ! # $PostgreSQL: pgsql/src/backend/utils/mb/Unicode/UCS_to_GB18030.pl,v 1.5 2005/03/07 04:30:52 momjian Exp $ # # Generate UTF-8 -- GB18030 code conversion tables from ! # map files provided by Unicode organization. ! # Unfortunately it is prohibited by the organization ! # to distribute the map files. So if you try to use this script, ! # you have to obtain ISO10646-GB18030.TXT from ! # the organization's ftp site. ! # ! # ISO10646-GB18030.TXT format: ! # GB18030 code in hex ! # UCS-2 code in hex ! # # and Unicode name (not used in this script) require ucs2utf.pl; # first generate UTF-8 -- GB18030 table $in_file = ISO10646-GB18030.TXT; --- 1,19 #! /usr/bin/perl # ! # Copyright (c) 2007-2010, PostgreSQL Global Development Group # ! # $Id$ # # Generate UTF-8 -- GB18030 code conversion tables from ! # ISO10646-GB18030.TXT ! # ! # file format: ! # GB18030 hex code ! # UCS-2 hex code require ucs2utf.pl; + # first generate UTF-8 -- GB18030 table $in_file = ISO10646-GB18030.TXT; *** *** 45,50 --- 41,47 } close( FILE ); + # # first, generate UTF8 -- GB18030 table # *** *** 66,71 --- 63,69 print FILE };\n; close(FILE); + # # then generate GB18030 -- UTF8 table # -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
Marc G. Fournier wrote: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? That's step one. Step two is community approval. Otherwise, anyone with an idea would be a committer. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] OpenVMS?
On Tue, 2010-02-16 at 15:47 -0500, Andrew Chernow wrote: Marc G. Fournier wrote: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? That's step one. Step two is community approval. Otherwise, anyone with an idea would be a committer. With all respect to OpenVMS, I really can't help but ask, why?. The maintenance load over time will be large and I can't imagine this being a wide use platform for us. Joshua D. Drake -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs
Applied. Thanks. --- Bruce Momjian wrote: Boszormenyi Zoltan wrote: Ah, I didn't even see that that section needed to be updated. Good catch. I'd suggest the following wording: For a commandSELECT/command or commandCREATE TABLE AS/command command, the tag is SELECT rows... [and the rest as you have it] We should probably also retitle that section from Retrieving Result Information for Other Commands to Retrieving Other Result Information and adjust the text of the opening sentence similarly. Also I think you need to update the docs for PQcmdtuples to mention that it not works for SELECT and CTAS. Ok, I will update libpq.sgml where this section resides. What's a CTA, btw? Do you mean CTE, a.k.a. Common Table Expression? Sorry, CTAS = CREATE TABLE AS SELECT. Okay, new patch is attached. Please read the docs changes, and comment. I have reviewed this patch and made some adjustments, attached. The major change is that our return of 0 0 in certain cases must remain, though I have improved the C comment explaining it with a separate CVS commit: /* * If a command completion tag was supplied, use it. Otherwise use the * portal's commandTag as the default completion tag. * * Exception: Clients expect INSERT/UPDATE/DELETE tags to have * counts, so fake them with zeros. This can happen with DO INSTEAD * rules if there is no replacement query of the same type as the * original. We print 0 0 here because technically there is no * query of the matching tag type, and printing a non-zero count for * a different query type seems wrong, e.g. an INSERT that does * an UPDATE instead should not print 0 1 if one row * was updated. See QueryRewrite(), step 3, for details. */ I have removed the part of the patch that chagned 0 0; it seems to run fine without it. The rest of my adjustments were minor. One major part of the patch seems to be the collection of the PORTAL_ONE_SELECT switch label into the label below it, which is a nice cleanup. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/libpq.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v retrieving revision 1.297 diff -c -c -r1.297 libpq.sgml *** doc/src/sgml/libpq.sgml 5 Feb 2010 03:09:04 - 1.297 --- doc/src/sgml/libpq.sgml 14 Feb 2010 03:11:00 - *** *** 2869,2880 /sect2 sect2 id=libpq-exec-nonselect !titleRetrieving Result Information for Other Commands/title para ! These functions are used to extract information from ! structnamePGresult/structname objects that are not ! commandSELECT/ results. /para variablelist --- 2869,2879 /sect2 sect2 id=libpq-exec-nonselect !titleRetrieving Other Result Information/title para ! These functions are used to extract other information from ! structnamePGresult/structname objects. /para variablelist *** *** 2925,2936 This function returns a string containing the number of rows affected by the acronymSQL/ statement that generated the structnamePGresult/. This function can only be used following !the execution of an commandINSERT/, commandUPDATE/, !commandDELETE/, commandMOVE/, commandFETCH/, or !commandCOPY/ statement, or an commandEXECUTE/ of a !prepared query that contains an commandINSERT/, !commandUPDATE/, or commandDELETE/ statement. If the !command that generated the structnamePGresult/ was anything else, functionPQcmdTuples/ returns an empty string. The caller should not free the return value directly. It will be freed when the associated structnamePGresult/ handle is passed to --- 2924,2935 This function returns a string containing the number of rows affected by the acronymSQL/ statement that generated the structnamePGresult/. This function can only be used following !the execution of a commandSELECT/, commandCREATE TABLE AS/, !commandINSERT/, commandUPDATE/, commandDELETE/, !commandMOVE/, commandFETCH/, or commandCOPY/ statement, !or an commandEXECUTE/ of a prepared query that contains an !commandINSERT/, commandUPDATE/, or commandDELETE/ statement. !If the command that generated the structnamePGresult/ was anything else, functionPQcmdTuples/ returns an empty string. The caller should not free the return value directly. It
Re: [HACKERS] OpenVMS?
On Tue, 16 Feb 2010, Bruce Momjian wrote: Marc G. Fournier wrote: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? Well, there is going to be impact on the community too --- patches, testing, etc. The community effort is small, but isn't zero. But its totally worth it. -- Sent 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 8.4 \c repeats version banner
Applied. --- Bruce Momjian wrote: Peter Eisentraut wrote: In 8.3, running \c from a file prints something like You are now connected to database postgres. In 8.4 it prints psql (8.4.1) You are now connected to database postgres. Is it intentional/sensible to repeat the startup banner every time the connection changes, or was this unintentionally introduced while the startup banner was reshuffled in 8.4? I did some reseach on this. I bet this behavior was added when we decided to print the backend version warning banner on \c as well as startup, because it is possible for the backend to be different version from the backend originally used for psql startup. The code that prints the psql banner and the warning banner are in the same function and share the same output line. What I did in the attached patch is to add a boolean to connection_warnings() to indicate whether it was being called on psql startup or via \c, and to supress the psql banner on \c if the client and server versions match: $ psql test psql (8.5devel) Type help for help. test= \c test You are now connected to database test. test= Any version mismatch will still print the psql banner for \c, which is what I think we want. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LISTEN/NOTIFY and notification timing guarantees
Jeff Davis pg...@j-davis.com writes: On Tue, 2010-02-16 at 10:38 -0500, Tom Lane wrote: 2. Add an extra lock to serialize writers to the queue, so that messages are guaranteed to be added to the queue in commit order. I assume this is a heavyweight lock, correct? Yeah, that seems the easiest way to do it. I think an LWLock could be made to work, but releasing it on error might be a bit funky. 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] LISTEN/NOTIFY and notification timing guarantees
Merlin Moncure mmonc...@gmail.com writes: On Tue, Feb 16, 2010 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: 2. Add an extra lock to serialize writers to the queue, so that messages are guaranteed to be added to the queue in commit order. As long as fwiw, I think you're definitely on the right track. IMO, any scenario where an issued notification ends up being deferred for an indefinite period of time without alerting the issuer should be avoided if at all possible. Just to clarify though, does your proposal block all notifiers if any uncommitted transaction issued a notify? It will block other notifiers until the transaction releases its locks, which should happen pretty promptly --- there are no user-accessible reasons for it to wait. 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: [GENERAL] FM format modifier does not remove leading zero from year
Applied. --- Bruce Momjian wrote: Guy Rouillier wrote: On 1/6/2010 3:29 PM, Tom Lane wrote: Guy Rouillierguyr-...@burntmail.com writes: Oracle states clearly in the SQL Reference manual: A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. *Toggles* the effect of the modifier? Egad, what drunken idiot chose that specification? Eh, tomato, tomahto. If you assume that someone will strip leading zeroes consistently, the Oracle approach makes sense. That would be a reasonable assumption to make; why would I strip the zero off the month but leave it on the day? So, in the unusual case that you want to do such a thing, you are asked to use a second occurrence of FM to turn zero suppression back off. I have developed the attached patch which implements FM control of YYY, YY, and Y specifications. I also documented that we do not match Oracle's toggle behavior. There are a few effects on regression test output which are part of this patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + [ text/x-diff is unsupported, treating like TEXT/PLAIN ] Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.500 diff -c -c -r1.500 func.sgml *** doc/src/sgml/func.sgml1 Feb 2010 15:38:21 - 1.500 --- doc/src/sgml/func.sgml6 Feb 2010 21:14:41 - *** *** 5174,5180 para literalFM/literal suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be !fixed-width. /para /listitem --- 5174,5184 para literalFM/literal suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be !fixed-width. In productnamePostgreSQL/productname, !literalFM/literal modifies only the next specification, while in !Oracle literalFM/literal affects all subsequent !specifications, and repeated literalFM/literal modifiers !toggle fill mode on and off. /para /listitem Index: src/backend/utils/adt/formatting.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.162 diff -c -c -r1.162 formatting.c *** src/backend/utils/adt/formatting.c2 Jan 2010 16:57:53 - 1.162 --- src/backend/utils/adt/formatting.c6 Feb 2010 21:14:42 - *** *** 515,520 --- 515,521 #define S_th(_s)(((_s) DCH_S_th) ? 1 : 0) #define S_TH_TYPE(_s) (((_s) DCH_S_TH) ? TH_UPPER : TH_LOWER) + /* Oracle toggles FM behavior, we don't; see docs. */ #define S_FM(_s)(((_s) DCH_S_FM) ? 1 : 0) #define S_SP(_s)(((_s) DCH_S_SP) ? 1 : 0) #define S_TM(_s)(((_s) DCH_S_TM) ? 1 : 0) *** *** 2411,2438 break; case DCH_YYY: case DCH_IYY: ! snprintf(buff, sizeof(buff), %03d, n-key-id == DCH_YYY ? ADJUST_YEAR(tm-tm_year, is_interval) : ADJUST_YEAR(date2isoyear(tm-tm_year, tm-tm_mon, tm-tm_mday), is_interval)); i = strlen(buff); ! strcpy(s, buff + (i - 3)); if (S_THth(n-suffix)) str_numth(s, s, S_TH_TYPE(n-suffix)); s += strlen(s); break; case DCH_YY: case DCH_IY: ! snprintf(buff, sizeof(buff), %02d, n-key-id == DCH_YY ? ADJUST_YEAR(tm-tm_year, is_interval) : ADJUST_YEAR(date2isoyear(tm-tm_year, tm-tm_mon, tm-tm_mday), is_interval)); i = strlen(buff); !
Re: [HACKERS] OpenVMS?
rocr...@gmx.de (Robert Doerfler) writes: On Tue, 16 Feb 2010, Bruce Momjian wrote: Marc G. Fournier wrote: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? Well, there is going to be impact on the community too --- patches, testing, etc. The community effort is small, but isn't zero. But its totally worth it. Do we have a patch yet? If it's small and easy, then that points to you being right. If ugly and hairy... then not so much... -- (format nil ~...@~s cbbrowne gmail.com) http://www3.sympatico.ca/cbbrowne/nonrdbms.html I think you ought to know I'm feeling very depressed -- Marvin the Paranoid Android -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OpenVMS?
scra...@hub.org (Marc G. Fournier) writes: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? But adding it in would require *some* degree of thought on the part of committers as to what might break VMS builds? If someone wants to make a branch to run on VMS, that would be a well and fine thing. It is quite likely that once we're on Git, that might be easily managed by having a repo which feeds off official releases, modifying only in those places where VMS-specific changes are required. It might even turn out to be the case that the patches are sufficiently small and undemanding that it would turn out to be easy to merge into the official release, so as to make it an official platform. I'd not want to assume that up front, though. -- output = reverse(moc.liamg @ enworbbc) http://linuxdatabases.info/info/slony.html As long as each individual is facing the TV tube alone, formal freedom poses no threat to privilege. --Noam Chomsky -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NaN/Inf fix for ECPG
I realized my typo after sending my mail. Sorry if I offended anyone calling NetBSD FreeBSD. :-) I was trying to stress the *beta* status. Maybe someone into NetBSD might be interested in reporting this as a bug. At least it behaves different to all other archs we have. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication on win32, still broken
2010/2/16 Fujii Masao masao.fu...@gmail.com: On Tue, Feb 16, 2010 at 7:20 PM, Magnus Hagander mag...@hagander.net wrote: 2010/2/16 Fujii Masao masao.fu...@gmail.com: On Tue, Feb 16, 2010 at 12:37 AM, Magnus Hagander mag...@hagander.net wrote: With the libpq fixes, I get further (more on that fix later, btw), but now I get stuck in this. When I do something on the master that generates WAL, such as insert a record, and then try to query this on the slave, the walreceiver process crashes with: PANIC: XX000: could not write to log file 0, segment 9 at offset 0, length 160: Invalid argument LOCATION: XLogWalRcvWrite, .\src\backend\replication\walreceiver.c:487 I'll keep digging at the details, but if somebody has a good idea here.. ;) Yeah, this problem was reproduced in my (very slow :-( ) MinGW environment, too. Though I've not idenfied the cause yet, I guess that it derives from wrong use of the type of local variables in XLogWalRcvWrite(). I'll continue investigation of it. Thanks! I will be somewhat spottily available over the next two days due to on-site work with clients. Let me know if you would be helped by some details of how to get a (somewhat faster) EC2 image up and running with MSVC to test on :-) Thanks! I can probably use the EC2 image by reading your great blog post. http://blog.hagander.net/archives/151-Testing-PostgreSQL-patches-on-Windows-using-Amazon-EC2.html Actually, that one deosn't work anymore, because I managed to break the image :-) If you send me your amazon id, I can get you premissions on my private image. I plan to clean it up and make it public, just haven't gotten around to it yet... -- 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] log_error_verbosity placement
Applied. --- Bruce Momjian wrote: I just realized that log_error_verbosity is in the wrong section in postgresql.conf and in our manual. It is listed under When to log when in fact it should be in the What to log section. I also added documentation of what terse mode actually does. I added verbosity information a few weeks ago. Patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest Status Summary - 2010-02-14
Tim Bunce wrote: On Sun, Feb 14, 2010 at 10:14:28PM -0500, Andrew Dunstan wrote: Robert Haas wrote: We're down to 5 patches remaining, and 1 day remaining, so it's time to try to wrap things up. * Package namespace and Safe init cleanup for plperl. Andrew Dunstan is taking care of this one, I believe. I will get this in, with changes as discussed recently. Here's a small extra patch for your consideration. It addresses a couple of minor loose-ends in plperl: - move on_proc_exit() call to after the plperl_*_init() calls so on_proc_exit will only be called if plperl_*_init() succeeds (else there's a risk of on_proc_exit consuming all the exit hook slots) - don't allow use of Safe version 2.21 as that's broken for PL/Perl. I have committed all the plperl changes that were under discussion, including this, and the change to the log level of perl warnings. Thanks for all your work, Tim, you have certainly given plperl a huge booster shot. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Tue, Feb 16, 2010 at 09:11:24AM -0800, David E. Wheeler wrote:
On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:
From the docs:
Immediately after the function name, in parenthesis, a comma separated list
of
type names can be given. For example:
'pi()'
'generate_series(int,int)'
'array_cat(int[], int[])'
'myschema.myfunc(date, float8)'
It could also just be 'pi', no?
Yes. A vestige from when the parens were still needed. Fixed.
Functions with Cvaradic arguments can be called with a fixed number of
arguments by repeating the type name in the signature the same number of
times.
I assume that type names can be omitted her, too, yes?
No, it seems not. You have to either repeat the type name the right number
of times, or use '...', which simply duplicates the type name for you
behind the scenes. I'll clarify that in the docs (and fix all the
places I spelt variadic wrong :)
$pi = SP-pi();
$seqn = SP-nextval($sequence_name);
Using this form you can't easily specify a schema name or argument types,
SP-schema.func() doesn't work. ($name=schema.func; SP-$name() works.)
and you can't call varadic functions.
Why not?
Using spi_prepare('select * from variadic_func($1)') the error is there
is no parameter $1. I suspect calls to varadic functions do need
correct nargs and type information given to the SPI_prepare call.
Also, I notice a few `==head`s. I think that's one too many =s.
Fixed. Thanks.
For varadic functions, separate plans are created and cached for each
distinct
number of arguments the function is called with.
Why?
It keeps the code simple and repeat calls fast.
Functions with a varadic argument can't be called with no values for that
argument. You'll get a function ... does not exist error. This appears
to be
a PostgreSQL limitation.
Hrm. Worth enquiring about.
I found it in the docs: A parameter marked VARIADIC matches *one* or
more occurrences of its element type.
http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html
So, is this on GitHub yet? That way I can submit patches.
I've uploaded PostgreSQL-PLPerl-Call-1.003.tar.gz to CPAN with these
changes. It's in git but not github yet. Maybe soonish.
Tim.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 16, 2010, at 2:06 PM, Tim Bunce wrote: I assume that type names can be omitted her, too, yes? No, it seems not. You have to either repeat the type name the right number of times, or use '...', which simply duplicates the type name for you behind the scenes. I'll clarify that in the docs (and fix all the places I spelt variadic wrong :) Pity. SP-schema.func() doesn't work. ($name=schema.func; SP-$name() works.) Ha! Document that. For varadic functions, separate plans are created and cached for each distinct number of arguments the function is called with. Why? It keeps the code simple and repeat calls fast. Yes, but if it's a variadic function, I suspect that it won't often be called with the same number of args. So you'd potentially end up caching a lot of extra stuff that would never be used again. I found it in the docs: A parameter marked VARIADIC matches *one* or more occurrences of its element type. http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html Ah, okay, that makes sense. So, is this on GitHub yet? That way I can submit patches. I've uploaded PostgreSQL-PLPerl-Call-1.003.tar.gz to CPAN with these changes. It's in git but not github yet. Maybe soonish. I saw. I think it might pay to heed Richard's suggestion not to use SP. By the way, I think it needs some documentation explaining how to load it inside PL/Perl. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest Status Summary - 2010-02-14
On Tue, Feb 16, 2010 at 04:42:29PM -0500, Andrew Dunstan wrote: Tim Bunce wrote: On Sun, Feb 14, 2010 at 10:14:28PM -0500, Andrew Dunstan wrote: Robert Haas wrote: We're down to 5 patches remaining, and 1 day remaining, so it's time to try to wrap things up. * Package namespace and Safe init cleanup for plperl. Andrew Dunstan is taking care of this one, I believe. I will get this in, with changes as discussed recently. Here's a small extra patch for your consideration. It addresses a couple of minor loose-ends in plperl: - move on_proc_exit() call to after the plperl_*_init() calls so on_proc_exit will only be called if plperl_*_init() succeeds (else there's a risk of on_proc_exit consuming all the exit hook slots) - don't allow use of Safe version 2.21 as that's broken for PL/Perl. I have committed all the plperl changes that were under discussion, including this, and the change to the log level of perl warnings. Thanks for all your work, Tim, you have certainly given plperl a huge booster shot. Thanks Andrew! And many thanks to you and the rest of the PostgreSQL developers for all your support/resistance/reviews along the way. The final changes are certainly better in many ways (though not all ;-) from my original patches. It's certainly been an interesting introduction to PostgreSQL development! Tim. p.s. One quick heads-up: David Wheeler has reported a possible issue with Safe 2.21. I hope to investigate that tomorrow. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest Status Summary - 2010-02-14
On Feb 16, 2010, at 2:19 PM, Tim Bunce wrote: It's certainly been an interesting introduction to PostgreSQL development! Interesting, eh? Look forward to your blog post about the experience. ;-P Tim. p.s. One quick heads-up: David Wheeler has reported a possible issue with Safe 2.21. I hope to investigate that tomorrow. Actually it's 2.22. 2.21 is already dead. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest Status Summary - 2010-02-14
It's certainly been an interesting introduction to PostgreSQL development! Hopefully we haven't scared you off - your work is definitely very much appreciated (and I at least hope to see you back for 9.1)! ...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] Listen / Notify - what to do when the queue is full
Joachim Wieland j...@mcknight.de writes: [ listen/notify patch ] Applied after rather a lot of hacking. Aside from the issues previously raised, I changed the NOTIFY syntax to include a comma between channel name and payload. The submitted syntax with no comma looked odd to me, and it would have been a real nightmare to extend if we ever decide we want to support expressions in NOTIFY. I found a number of implementation problems having to do with wraparound behavior and error recovery. I think they're all fixed, but any remaining bugs are probably my fault not yours. 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] OpenVMS?
Bruce Momjian br...@momjian.us writes: Marc G. Fournier wrote: On Tue, 16 Feb 2010, Bruce Momjian wrote: I hate to pour cold water on this, but why is it worth adding support for a platform that has such marginal usage. Because someone feels like dedicating their resources to it ... ? Well, there is going to be impact on the community too --- patches, testing, etc. The community effort is small, but isn't zero. No, I suspect the community effort would be *large*. VMS is sufficiently unlike Unix that this port would probably be akin to the Windows port in terms of invasiveness, fragility, and general need for everyone to bend over backwards for it. The discussion about fork substitutes should give you some idea of what we'd be in for. I think the odds of getting the community to support such a port are not easily distinguishable from zero, and I agree with Bruce's desire to dissuade anyone from pouring effort down the drain. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 16, 2010, at 3:01 PM, Tom Lane wrote: I think the reason the client-side docs recommend using zero is to avoid having clients know about the unknown type explicitly (in particular, to discourage people from hardwiring 705 into their code). AFAIR there's not a lot of difference in terms of what the parser will do with it. We should probably get rid of this in DBD::Pg then: % perl -MDBD::Pg -E 'say DBD::Pg::PG_UNKNOWN' 705 Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
