Re: [BUGS] BUG #5103: pg_ctl -w (re)start fails with custom unix_socket_directory
On 22.02.2010 23:38, Bruce Momjian wrote: Michael Renner wrote: Looking through the available variables probably only PGHOST seems to be of interest for pg_ctl's purposes; psql's manpage already refers to The Documentation for further variables and information. What's needed in pg_ctl's case is just a pointer for the uninformed, at least for me that'd have sufficed. Based on your suggestion, I have documented the use of PGHOST by pg_ctl with the attached patch. I specifically mentioned the socket location. Thanks, highly appreciated! best regards, Michael Renner -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5342: Error
The following bug has been logged online: Bug reference: 5342 Logged by: Shilpa.R Email address: shilpar1...@gmail.com PostgreSQL version: PostgreSQL Data Operating system: Windows Xp Description:Error Details: I found an error stating syntax error near or at sets in the following query select agegrp, cartype, risk, count(*) as count from trset group by risk, grouping sets ((agegrp,cartype)) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5343: Documentation error for pg_dump
The following bug has been logged online: Bug reference: 5343 Logged by: Rob Dean Email address: rob.d...@pressassociation.com PostgreSQL version: 8.3 Operating system: linux Description:Documentation error for pg_dump Details: The last sentence of the penultimate paragraph in the Description for pg_dump in the Documentation states :- The tar format (-Ft) is not compressed and it is not possible to reorder data when loading, but it is otherwise quite flexible; moreover, it can be manipulated with standard Unix tools such as tar. and the description of the tar format option states :- t tar Output a tar archive suitable for input into pg_restore. Using this archive format allows reordering and/or exclusion of database objects at the time the database is restored. It is also possible to limit which data is reloaded at restore time. The information on reordering at restore time is surely completely contradictory between the two. The 8.4 documentation is the same. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5342: Error
Shilpa.R shilpar1...@gmail.com wrote: PostgreSQL version: PostgreSQL Data That's not very informative; what do you get from running?: select version(); I found an error stating syntax error near or at sets in the following query select agegrp, cartype, risk, count(*) as count from trset group by risk, grouping sets ((agegrp,cartype)) I'm not familiar with grouping sets -- where in the PostgreSQL documentation do you find those defined? -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5342: Error
Shilpa.R escreveu: select agegrp, cartype, risk, count(*) as count from trset group by risk, grouping sets ((agegrp,cartype)) This is not a bug. If you had searched the fine manual, you would figure out that grouping sets is not supported by PostgreSQL yet. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5343: Documentation error for pg_dump
Rob Dean rob.d...@pressassociation.com writes: The information on reordering at restore time is surely completely contradictory between the two. The 8.4 documentation is the same. I'm sure that discrepancy goes way back :-(. Some experimentation indicates that you can reorder some things but not others --- in particular you can't pull two TABLE DATA sections from a tar archive out-of-order. So it might be that someone thought the restriction had been fixed based on limited testing, and changed one of the items but missed the other. I'll see about improving it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5118: start-status-insert-fatal
Bruce Momjian br...@momjian.us wrote: I think you should just edit the TODO wiki and list all the things we agree need fixing: Done, although with the wealth of opinions and dearth of agreement I referenced much material and said that more discussion was needed before starting development. If anyone figures I missed anything or got overly expansive, have it it. ;-) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5343: Documentation error for pg_dump
Rob Dean rob.d...@pressassociation.com writes: The information on reordering at restore time is surely completely contradictory between the two. The 8.4 documentation is the same. Actually, on looking closer, I think the original author meant to draw a distinction between database objects and table data items. It was certainly confusing though, since it was not stated explicitly that the former didn't include the latter, and that's not what someone would expect if they weren't deeply immersed in the guts of pg_dump. I've rephrased it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5339: Version of Perl detected incorrectly
Alex Hunsaker bada...@gmail.com writes: On Tue, Feb 23, 2010 at 00:50, Alex Hunsaker bada...@gmail.com wrote: On Mon, Feb 22, 2010 at 14:31, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to stay with the same basic implementation and just hack up the regexp some more to cope with 5.11's more verbose -v output. And here is a stab at that: Grr... stupid word wrapping. Attached. Looks good, applied. I did throw in one more [0-9] just to be on the safe side. BTW, it's really *not* necessary to include configure in submitted diffs. That's a derived file. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5339: Version of Perl detected incorrectly
On Mon, Feb 22, 2010 at 04:31:05PM -0500, Tom Lane wrote: Alex Hunsaker bada...@gmail.com writes: How about something like the below? I still think that this is optimizing the wrong thing. We care about the clarity of the message the user sees, not about how short or clean the Perl code is. I'm inclined to stay with the same basic implementation and just hack up the regexp some more to cope with 5.11's more verbose -v output. There's no need to try to parse the perl -v output, which is intended for humans and may change in future. Using perl -e 'print $]' will give you the version number in floating point format for all versions of perl. For perl5 the format is 5.xxxyyy so testing for = 5.008 (or ideally 5.008001) will work fine. Tim. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5339: Version of Perl detected incorrectly
Tim Bunce tim.bu...@pobox.com writes: On Mon, Feb 22, 2010 at 04:31:05PM -0500, Tom Lane wrote: I still think that this is optimizing the wrong thing. We care about the clarity of the message the user sees, not about how short or clean the Perl code is. There's no need to try to parse the perl -v output, which is intended for humans and may change in future. Using perl -e 'print $]' will give you the version number in floating point format for all versions of perl. $ /usr/local/bin/perl4 -e 'print $]' $RCSfile: perl.c,v $$Revision: 4.0.1.8 $$Date: 1993/02/05 19:39:30 $ Patch level: 36 I will refrain from further comment. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5339: Version of Perl detected incorrectly
On Tue, Feb 23, 2010 at 04:02:11PM -0500, Tom Lane wrote: Tim Bunce tim.bu...@pobox.com writes: On Mon, Feb 22, 2010 at 04:31:05PM -0500, Tom Lane wrote: I still think that this is optimizing the wrong thing. We care about the clarity of the message the user sees, not about how short or clean the Perl code is. There's no need to try to parse the perl -v output, which is intended for humans and may change in future. Using perl -e 'print $]' will give you the version number in floating point format for all versions of perl. $ /usr/local/bin/perl4 -e 'print $]' $RCSfile: perl.c,v $$Revision: 4.0.1.8 $$Date: 1993/02/05 19:39:30 $ Patch level: 36 I will refrain from further comment. Ah, sorry. It needs to be in numeric context: perl -e 'print($]+0)' Tim. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] New PL/Perl failure with Safe 2.2x due to recursion (8.x 9.0)
[Resend. I misspelled the mailing list address on the original.] David Wheeler has discovered a new PL/Perl failure when using Safe 2.2x. It's not good. In this email I'll try to explain the cause and some possible solutions. PL/Perl compiles plperl functions inside a 'Safe compartment' which restricts what operations can be compiled and effectively does a chroot in the package namespace. The compilation returns a reference to the compiled subroutine. Until recent versions of Safe the _execution_ of that subroutine reference happened 'outside' of the Safe compartment. Safe was only 'in effect' during the compilation. The big change in Safe 2.20 (contributed by Alex Hunsaker) was that returned subroutine references were 'wrapped' in extra code that executed the subroutine inside the Safe compartment. The ticket for this change, with much discussion, is at http://rt.perl.org/rt3/Ticket/Display.html?id=60374#txn-628047 The original motivation for the change was to fix a problem with using sort {} in a threaded perl. As a side-effect it also increased security: the Safe restrictions for plperl were in effect at runtime as well as compiletime. Unfortunately it also had a number of subtle knock-on effects that we've been dealing with via Safe 2.21..2.23. So far so good, but now we've hit another problem. - Forwarded message from David E. Wheeler david.whee...@pgexperts.com - [...] When I move that function so that it's created just before my test function that uses it, it works. It's only if it's created by another process and already in the database when my test script connects that it fails. Okay, here's the test case: Create this function: CREATE OR REPLACE FUNCTION foo( integer) RETURNS SETOF INT LANGUAGE plperl AS $$ $$; Then disconnect and reconnect and run this: CREATE OR REPLACE FUNCTION try() RETURNS VOID LANGUAGE plperl AS $$ my $sth = spi_query(SELECT id FROM foo( 0 ) AS g(id)); while( my $row = spi_fetchrow($sth) ) { } $$; SELECT try(); Result: ERROR: error from Perl function try: Undefined subroutine main::mksafefunc called at line 3. (in cleanup) creation of Perl function foo failed:(in cleanup) Undefined subroutine main::mksafefunc called at line 3. at line 3. FYI, it's the call to spi_fetchrow() that fails, not spi_prepare(). PostgreSQL 8.4.2, Perl 5.10.1 with ithreads, Safe 2.23. - End forwarded message - I believe (but haven't yet confirmed) that the problem here is recursion. When plperl recurses into itself via foo() the Safe restrictions are still in effect. So plperl can't find the mksafefunc subroutine because it's still 'inside' the chroot'd namespace. This affects all versions of PostgreSQL. After some head scratching I think the best course of action is to change Safe to make the new behaviour optional and default to off. In other words restore the pre-2.20 behaviour. That'll fix the immediate problem for all PostgreSQL versions. Security will be no better or worse that it was before Safe 2.20. The old sort { } bug (where $a $b) don't work will return but that seems a very small price to pay for a simple fix. I'd like to see PostgreSQL re-enable use of the wrapped subroutines in the future but it'll require some development effort. The plperl entry points will need to detect if Safe is 'in effect' and locally undo it. There's some prior art in http://search.cpan.org/perldoc?Safe::Hole that might be useful. I don't think it's viable to tackle this for PostgreSQL 9.0. Tomorrow I'll confirm my hypothesis and work on (another) patch for Safe to disable the wrapping and test it with PostgreSQL 8.4 and 9.0. Tim. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5344: pg_restore some foreign keys missing
The following bug has been logged online: Bug reference: 5344 Logged by: Simon Ng Email address: simon94...@yahoo.com PostgreSQL version: 8.1.11 Operating system: RedHat Linux Description:pg_restore some foreign keys missing Details: from server1 with Postgres 8.1.11 on RedHat Linux pg_dump -C -f db1.tar.gz -F c -Z5 -U postgres db1 copy db1.tar.gz over to server2 with Postgres 8.1.11 on RedHat Linux drop database db1; pg_restore -C -d template1 -h localhost -U postgres db1.tar.gz the database of db1 is restored but the schema is incomplete. Some tables have the proper foreign keys but some tables don't. copy the same db1.tar.gz from server1 to a Win XP machine with Postgres 8.4 drop database db1; pg_restore -C -d template1 -h localhost -U postgres db1.tar.gz Those tables that have missing foreign keys in server2 do have the proper foreign keys defined. server1 and server2 have the same version of OS and same version of Postgres. Why are some foreign keys missing during the restore? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] RETURNS TABLE returns NULL set when called by another RETURNS TABLE
Pavel, all: Apparently if you use one returns table function to call a 2nd returns table function, it returns a recordset which consists entirely of nulls. Here's the test case: create table srf_data ( id serial, cat int, val text ); insert into srf_data ( cat, val ) values ( 1, 'josh' ), ( 1, 'selena' ), ( 2, 'bruce' ), ( 2, 'josh' ), ( 3, 'robert' ); create or replace function srf1 ( this_cat int ) returns table ( id1 int, val1 text ) language sql as $f$ select id, val from srf_data where cat = $1; $f$; create or replace function srf2 ( ) returns table ( id1 int, val1 text ) language plpgsql as $f$ begin return query select id1, val1 from srf1(1); return; end; $f$; select * from srf2(); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] RETURNS TABLE returns NULL set when called by another RETURNS TABLE
Josh Berkus j...@agliodbs.com writes: Apparently if you use one returns table function to call a 2nd returns table function, it returns a recordset which consists entirely of nulls. In HEAD that example fails with psql:josh.sql:30: ERROR: column reference id1 is ambiguous LINE 1: select id1, val1 from srf1(1) ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: select id1, val1 from srf1(1) CONTEXT: PL/pgSQL function srf2 line 2 at RETURN QUERY val1 is just as ambiguous. I think you got bit by the name collision; the output parameters would start out NULLs and thus lead to the described behavior, in versions before 9.0. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] RETURNS TABLE returns NULL set when called by another RETURNS TABLE
val1 is just as ambiguous. I think you got bit by the name collision; the output parameters would start out NULLs and thus lead to the described behavior, in versions before 9.0. Aha, yeah, that's probably it. Take this example as the reason we had to change the behavior ... --Josh Berkus -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5344: pg_restore some foreign keys missing
On Tue, Feb 23, 2010 at 5:43 PM, Simon Ng simon94...@yahoo.com wrote: The following bug has been logged online: Bug reference: 5344 Logged by: Simon Ng Email address: simon94...@yahoo.com PostgreSQL version: 8.1.11 Operating system: RedHat Linux Description: pg_restore some foreign keys missing Details: from server1 with Postgres 8.1.11 on RedHat Linux pg_dump -C -f db1.tar.gz -F c -Z5 -U postgres db1 copy db1.tar.gz over to server2 with Postgres 8.1.11 on RedHat Linux drop database db1; pg_restore -C -d template1 -h localhost -U postgres db1.tar.gz the database of db1 is restored but the schema is incomplete. Some tables have the proper foreign keys but some tables don't. copy the same db1.tar.gz from server1 to a Win XP machine with Postgres 8.4 drop database db1; pg_restore -C -d template1 -h localhost -U postgres db1.tar.gz Those tables that have missing foreign keys in server2 do have the proper foreign keys defined. server1 and server2 have the same version of OS and same version of Postgres. Why are some foreign keys missing during the restore? My guess is that the creation of those foreign key constraints failed for some reason while restoring the dump. Check the output of pg_restore and see if there are any errors there. ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] RETURNS TABLE returns NULL set when called by another RETURNS TABLE
2010/2/24 Josh Berkus j...@agliodbs.com: Pavel, all: Apparently if you use one returns table function to call a 2nd returns table function, it returns a recordset which consists entirely of nulls. Here's the test case: create table srf_data ( id serial, cat int, val text ); insert into srf_data ( cat, val ) values ( 1, 'josh' ), ( 1, 'selena' ), ( 2, 'bruce' ), ( 2, 'josh' ), ( 3, 'robert' ); create or replace function srf1 ( this_cat int ) returns table ( id1 int, val1 text ) language sql as $f$ select id, val from srf_data where cat = $1; $f$; create or replace function srf2 ( ) returns table ( id1 int, val1 text ) language plpgsql as $f$ begin return query select id1, val1 from srf1(1); return; end; $f$; there is identifier's conflict - try to use alias create or replace function srf2() returns table(id1 int, val1 text) language plpgsql as $$ begin return query select s.id1, s.val1 from srf(1) s; return; end; $$ Regards Pavel Stehule Pavel select * from srf2(); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] RETURNS TABLE returns NULL set when called by another RETURNS TABLE
2010/2/24 Josh Berkus j...@agliodbs.com: val1 is just as ambiguous. I think you got bit by the name collision; the output parameters would start out NULLs and thus lead to the described behavior, in versions before 9.0. Aha, yeah, that's probably it. Take this example as the reason we had to change the behavior ... yes - I am very happy with this change. It is the biggest change in plpgsql over 10 years. Pavel --Josh Berkus -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs