[BUGS] BUG #8354: stripped positions can generate nonzero rank in ts_rank_cd
The following bug has been logged on the website:
Bug reference: 8354
Logged by: Alex Hill
Email address: a...@hill.net.au
PostgreSQL version: 9.2.4
Operating system: OS X 10.8.4 Mountain Lion
Description:
Hi all,
The docs for ts_rank_cd state:
"This function requires positional information in its input. Therefore it
will not work on "stripped" tsvector values — it will always return zero."
However if a tsvector contains some stripped lexemes and some non-stripped,
ts_rank_cd will rank extents including the non-stripped values.
For example, this evaluates to zero as expected:
SELECT ts_rank_cd(strip(to_tsvector('text search')),
plainto_tsquery('text search'))
But this doesn't:
SELECT ts_rank_cd(to_tsvector('text') || strip(to_tsvector('search')),
plainto_tsquery('text search'))
I think this is a bug, if not in the code then in the documentation, which
isn't clear on what happens when stripped and positioned lexemes are mixed
in one tsvector.
I would prefer that stripped lexemes were completely ignored by ts_rank_cd:
my use case is using this as a fifth pseudo-weight, which matches a @@ query
but doesn't add to a ts_rank_cd ranking.
What do you think?
Cheers,
Alex
--
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] Unable to handle error in plperl
On Mon, Jul 15, 2013 at 5:56 AM, Ming Lai wrote:
> I know how elog works. elog only show the status, but it does not allow me
> to execute another query when the current query fails because one of the
> invalid column was specified.
Hrm? Im not sure what you mean. If you elog(ERROR) outside of eval the
current transaction will be aborted. Thats why I suggested doing
elog(INFO) instead. The below example works fine for me. Perhaps you
can highlight exactly what you think it broken so I can understand?
=> begin;
BEGIN
=> create table a_table (a_column int);
CREATE TABLE
=> CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
my $sql = "";
my $status = "";
my $r = "";
$sql = 'SELECT non_exist_column from a_table limit 1';
eval { spi_exec_query($sql);};
if ($@) {
$status = 'invalid: '.$@;
my $rv = spi_exec_query('SELECT true as col;');
return "$status\nQuery after error: ".$rv->{rows}[0]{'col'};
} else {
$status = 'valid';
}
return $status;
$$ LANGUAGE plperl;
CREATE FUNCTION
=> select foo();
foo
──
invalid: column "non_exist_column" does not exist at line 6.↵
↵
Query after error: t
(1 row)
=> select true;
bool
──
t
(1 row)
=> commit;
COMMIT
--
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] Unable to handle error in plperl
On Wed, Jul 10, 2013 at 2:36 PM, Alex Lai wrote:
> Dear all,
>
> I have a situation. I am unable to pass control back to the function once
> it hit the "undefined_column" error code.
> I am not sure there's a way to return '123' instead exit from the function.
>
> Here is my code
>
> CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
> my $sql = "";
> my $status = "";
> my $r = "";
> $sql = 'SELECT non_exist_column from a_table limit 1';
> eval { spi_exec_query($sql);};
> if ($@) {
> $status = 'invalid: '.$@;
> elog(ERROR, $status);
Its this bit here that is tripping you up. Perhaps you meant
elog(INFO, ...) or something?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Unable to handle error in plperl
Dear all,
I have a situation. I am unable to pass control back to the function
once it hit the "undefined_column" error code.
I am not sure there's a way to return '123' instead exit from the function.
Here is my code
CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
my $sql = "";
my $status = "";
my $r = "";
$sql = 'SELECT non_exist_column from a_table limit 1';
eval { spi_exec_query($sql);};
if ($@) {
$status = 'invalid: '.$@;
elog(ERROR, $status);
return '123';
} else {
$status = 'valid';
}
return $status;
$$ LANGUAGE plperl;
When I run it
select foo();
ERROR: invalid: column "non_exist_column" does not exist at line 6.
CONTEXT: PL/Perl function "foo"
When I select from the valid column
CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
my $sql = "";
my $status = "";
my $r = "";
$sql = 'SELECT exist_column from a_table limit 1';
eval { spi_exec_query($sql);};
if ($@) {
$status = 'invalid: '.$@;
elog(ERROR, $status);
return '123';
} else {
$status = 'valid';
}
return $status;
$$ LANGUAGE plperl;
When I run it
select foo();
foo
--
valid
(1 row)
--
Best regards,
Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
m...@sesda3.com
--
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 #7783: lower & upper function incorrect work
The following bug has been logged on the website: Bug reference: 7783 Logged by: Alexander Shniperson Email address: alex.shniper...@gmail.com PostgreSQL version: 9.1.7 Operating system: osx lion 10.7.5 Description: Hi. I have problem at my project, try to do simple steps: 1) create DataBase with ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' 2) create new table at just created database CREATE TABLE testtable ( id serial NOT NULL, name character varying(50) NOT NULL, CONSTRAINT testtable_pkey PRIMARY KEY (id ) ) 3) insert one simple record with Russian word INSERT INTO testtable (id, name) VALUES (1, 'Залог'); 4) try to select data with upper & lower functions with 'like' operator select *, lower(name), upper(name) from testtable where lower(name) like '%залог%' 5) as result you must see nothing, just fields without data fix it please, thanks. -- 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 #7781: pgagent incorrect installation
The following bug has been logged on the website:
Bug reference: 7781
Logged by: Alexander Shniperson
Email address: alex.shniper...@gmail.com
PostgreSQL version: 9.1.7
Operating system: osx lion 10.7.5
Description:
Hi.
Just installed last PostgreSQL 9.1.7 and installed pgAgent from Application
builder, but when i run pgAdmin no Jobs at all and even more in console i
have error "com.apple.launchd: (com.edb.launchd.pgagent[55389])
posix_spawn("/Library/pgAgent/bin/pgagent", ...): No such file or directory
"
fix it please, thanks.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: [BUGS] Re: [BUGS] BUG #6645: Getting an error with “ERROR: PL/Perl function must return reference to hash or array”?
On Wed, May 16, 2012 at 11:53 AM, Tom Lane wrote: > Alex Hunsaker writes: >> That being said it seems we failed to take any magic (aka string >> overloads) that a blessed reference might have. Ill see about >> submitting a patch for 9.3 (9.2 just entered beta). Anyone have any >> thoughts on if we should backpatch a fix? > > Right offhand I'd be +1 for making that change, but not for backpatching > it; but I'm not a big plperl user. Would such a case have worked before > 9.1? If it did and we broke it in 9.1, that would be a good reason to > back-patch into 9.1. If it never worked, then it sounds like a new > feature. Yeah, it worked pre 9.1. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: [BUGS] BUG #6645: Getting an error with “ERROR: PL/Perl function must return reference to hash or array”?
On Wed, May 16, 2012 at 10:47 AM, wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6645
> Logged by: Evna Carroll
> Email address: m...@evancarroll.com
> PostgreSQL version: 9.1.2
> Operating system: Linux
> Description:
>
> This is a cross post from dba.stackexchange.com:
> http://dba.stackexchange.com/q/17998/2639
>
> The follow code used to work in Postgresql 8.4.11 with perl v5.10.1:
> CREATE OR REPLACE FUNCTION testfunction(OUT text) AS $$
> use URI;
> return URI->new('http://www.google.com/')->canonical;
> $$ LANGUAGE plperlu;
URI->canonical() returns some kind of blessed object, you can get it
to work by coercing the result to a string first:
CREATE OR REPLACE FUNCTION testfunction(OUT text) AS $$
use URI;
return URI->new('http://www.google.com/')->canonical().'';
$$ LANGUAGE plperlu;
We tightened this up over in:
commit 7c64c9f6b767b84597d69cfa2ae03d9a9655ec75
Author: Tom Lane
Date: Thu Oct 13 18:02:43 2011 -0400
Fix up Perl-to-Postgres datatype conversions in pl/perl.
This patch restores the pre-9.1 behavior that pl/perl functions returning
VOID ignore the result value of their last Perl statement. 9.1.0
unintentionally threw an error if the last statement returned a reference,
as reported by Amit Khandekar.
[...snip...]
In addition, ensure we throw errors for attempts to return arrays or hashes
when the function's declared result type is not an array or composite type,
respectively. Pre-9.1 versions rather uselessly returned strings like
ARRAY(0x221a9a0) or HASH(0x221aa90), while 9.1.0 threw an error for the
hash case and returned a garbage value for the array case.
[...snip...]
As noted above if you return a reference you would get a mostly
useless string like "HASH(0x...)". Post commit above it now gives you
the error "ERROR: cannot convert Perl hash to non-composite type
text" instead. That seemed better at the time because its almost
always a mistake (with what you return or your declared return type).
That being said it seems we failed to take any magic (aka string
overloads) that a blessed reference might have. Ill see about
submitting a patch for 9.3 (9.2 just entered beta). Anyone have any
thoughts on if we should backpatch a fix?
--
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] hstore parser incorrectly handles malformed input
On Sat, Apr 28, 2012 at 17:20, Ryan Kelly wrote:
> I don't think any language supports the empty key edge case in this way.
> The only language I know of that will let you get away with it is Perl,
> and in that case you get undef, not the empty string, and it's a warning
> if you have warnings on:
>
> $ use warnings;
> $ my $h = { 1 => };
> Odd number of elements in anonymous hash
Well... kind of but not really, perl (as usual) is a bit funky here.
Try it with more than one key. What ends up happening is the 2nd key
is used as the first keys value... Surprise! You also don't get the
warning, it sees 1=>2,(void) or something.
$ perl -E 'my $h = {1=>,2=>}; say $h->{1};'
2
Anyway, +1 for what tom proposed.
--
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 #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
Dropping the BEGIN has cleared up the issue. Thank you. On 4/3/2012 9:50 AM, John R Pierce wrote: On 04/03/12 9:40 AM, Tom Lane wrote: Alex Matzinger writes: > The connection that is executing the SELECT 1 are generally open for 1-5 > hours before they are killed. The specific connection only executes > SELECT 1. The transaction is simply BEGIN, and then SELECT 1's, no > other query is executed. Lose the "BEGIN" and it will probably work more nicely. indeed,a 1-5 hour long transaction means VACUUM can't clean up anything newer than the oldest active transaction, and thats not per database, thats cluster-wide. -- 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 #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
The connection that is executing the SELECT 1 are generally open for 1-5 hours before they are killed. The specific connection only executes SELECT 1. The transaction is simply BEGIN, and then SELECT 1's, no other query is executed. The updates we make to the primary database are quiet large, usually several megabytes. Alex On 4/2/2012 11:49 PM, Heikki Linnakangas wrote: On 03.04.2012 02:23, amatzin...@experts-exchange.com wrote: On a hot standby database, while the primary is being updated, Postgres will randomly kill a process which is performing a "Select 1" command. The error is this: 2012-04-02 13:36:13.269 PDT,"smxuser","smxprd1",39523,"127.0.0.1:57893",4f79ffad.9a63,1,"",2012-04-02 12:36:13 PDT,3/32,0,FATAL,40001,"terminating connection due to conflict with recovery","User query might have needed to see row versions that must be removed.","In a moment you should be able to reconnect to the database and repeat your command.",,,"" We have 5 hot standby's set up, which all preform this SELECT 1, and postgres kills them across all standby's. There should never be a situation that SELECT 1 is in conflict with data, as it it never using any table in the database. The system doesn't make a difference between queries like "SELECT 1" that don't access any tables, and those that do. Even if "SELECT 1" doesn't access any tables, a subsequent statement in the same transaction might. I'm assuming that those "SELECT 1"s were issued in transactions that had been open for a long time, because you shouldn't get recovery conflicts with very short transactions, in practice anyway. -- 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 #6511: calling spi_exec_query from non-main package, results in: couldn't fetch $_TD
On Mon, Mar 19, 2012 at 14:40, Alvaro Herrera wrote: > > Excerpts from Alex Hunsaker's message of mié mar 07 01:08:25 -0300 2012: >> The attached fixes it for me, It would be great if you could confirm that. > > Thanks -- applied to 9.1 and master. Thanks! -- 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 #6511: calling spi_exec_query from non-main package, results in: couldn't fetch $_TD
On Thu, Mar 15, 2012 at 16:13, Bruce Momjian wrote: > On Tue, Mar 06, 2012 at 09:08:25PM -0700, Alex Hunsaker wrote: >> [ Calling a plperl trigger function from a plperl function ] >> >> Yeah, there were some optimization done for 9.1 to try and make calls >> a bit faster. The problem is we are fetching "_TD" not "main::_TD", >> which means we try to find and use $_TD from whatever the current >> package is. This should only happen from a nested plperl to plperl >> trigger where the outer call was in a different package, otherwise the >> package is always main. >> >> The attached fixes it for me, It would be great if you could confirm that. >> >> Thanks for the report! > > So, should this be applied? Yes, but I would like to get conformation that it fixes the issue. Either way Ill probably just end up submitting it to the next commit fest. (I know most commiters are still busy with the current one). -- 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 #6511: calling spi_exec_query from non-main package, results in: couldn't fetch $_TD
On Sat, Mar 3, 2012 at 12:19, wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6511
> Logged by: Mark Murawski
> Email address: ma...@kobaz.net
> PostgreSQL version: 9.1.3
> Operating system: Linux - Debian Squeeze postgres 9.1 from backports
> Description:
>
> CREATE OR REPLACE FUNCTION myfunc() RETURNS text AS $BODY$
> package foo;
> sub foo {
> main::spi_exec_query(q{INSERT INTO mytable VALUES (1) RETURNING id});
> };
>
> package main;
> foo::foo();
> return;
> $BODY$ LANGUAGE plperlu VOLATILE COST 100;
>
> --
>
> pbx=# select * from myfunc();
> ERROR: couldn't fetch $_TD at line 4.
> CONTEXT: PL/Perl function "myfunc"
[ Calling a plperl trigger function from a plperl function ]
Yeah, there were some optimization done for 9.1 to try and make calls
a bit faster. The problem is we are fetching "_TD" not "main::_TD",
which means we try to find and use $_TD from whatever the current
package is. This should only happen from a nested plperl to plperl
trigger where the outer call was in a different package, otherwise the
package is always main.
The attached fixes it for me, It would be great if you could confirm that.
Thanks for the report!
*** a/src/pl/plperl/plperl.c
--- b/src/pl/plperl/plperl.c
***
*** 2062,2068 plperl_call_perl_trigger_func(plperl_proc_desc *desc, FunctionCallInfo fcinfo,
ENTER;
SAVETMPS;
! TDsv = get_sv("_TD", 0);
if (!TDsv)
elog(ERROR, "couldn't fetch $_TD");
--- 2062,2068
ENTER;
SAVETMPS;
! TDsv = get_sv("main::_TD", 0);
if (!TDsv)
elog(ERROR, "couldn't fetch $_TD");
--
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 #6426: Complex query runs 10 times longer with "LIMIT 20"
Andrew Schetinin wrote: Hi Alex, Thank you. I played with subqueries, but in this case I did not see a good enough improvement. In my specific case, what I've seen from the query execution plans, is that without LIMIT the query uses Hash Joins, but once I add LIMIT, it starts using Nested Loop Joins almost everywhere. Regards, Andrew On Wed, Feb 1, 2012 at 9:42 PM, Alex Lai <mailto:a...@sesda2.com>> wrote: Hi Andrew, I posted for another post, its may give you a workaround. I still not fully understand how PG choose execute plan that slow down so much. I had the same situation in one of my query. Use the subquery can speed up almost by 100 times faster. .. Hope this help. Best Regards, Alex Lai -- Best regards, Alex Lai OMI SIPS DBA ADNET Systems , Inc. 7515 Mission Drive, Suite A100 Lanham, MD 20706 301-352-4657 (phone) 301-352-0437 (fax) a...@sesda2.com <mailto:a...@sesda2.com> -- -- Andrew Schetinin Hi Andrew, I am interested in see your query. -- Best regards, Alex Lai OMI SIPS DBA ADNET Systems , Inc. 7515 Mission Drive, Suite A100 Lanham, MD 20706 301-352-4657 (phone) 301-352-0437 (fax) a...@sesda2.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 #6426: Complex query runs 10 times longer with "LIMIT 20"
7 width=12) (actual time=0.358..2.251 rows=43 loops=1) -> Bitmap Heap Scan on filemeta_l3 b (cost=209.87..12077.66 rows=7727 width=12) (actual time=0.262..0.685 rows=108 loops=1) Recheck Cond: esdt)::text = 'ROLPT'::text) AND (starttime >= '2012-01-28 08:14:57'::timestamp without time zone) AND (starttime <= '2012-05-07 07:41:2 7'::timestamp without time zone)) OR (((esdt)::text = 'ROLPT'::text) AND (endtime >= '2012-01-28 08:14:57'::timestamp without time zone) AND (endtime <= '2012-05-07 07:41:27'::timestamp without time zone))) Filter: ((source)::text = 'OMPS-NPP'::text) -> BitmapOr (cost=209.87..209.87 rows=9895 width=0) (actual time=0.223..0.223 rows=0 loops=1) -> Bitmap Index Scan on ak_filemeta_l3_esdt_starttime_endtime (cost=0.00..107.72 rows=4961 width=0) (actual time=0.127..0.127 rows=106 loops=1) Index Cond: (((esdt)::text = 'ROLPT'::text) AND (starttime >= '2012-01-28 08:14:57'::timestamp without time zone) AND (starttime <= '2012-05-0 7 07:41:27'::timestamp without time zone)) -> Bitmap Index Scan on ak_filemeta_l3_esdt_endtime (cost=0.00..98.29 rows=4934 width=0) (actual time=0.093..0.093 rows=108 loops=1) Index Cond: (((esdt)::text = 'ROLPT'::text) AND (endtime >= '2012-01-28 08:14:57'::timestamp without time zone) AND (endtime <= '2012-05-07 07 :41:27'::timestamp without time zone)) -> Index Scan using pk_filemeta_archiveset on filemeta_archiveset a (cost=0.00..5.21 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=108) Index Cond: ((a.fileid = b.fileid) AND (a.archiveset = 61000)) -> Index Scan using pk_filemeta_l3 on filemeta_l3 b (cost=0.00..3.37 rows=1 width=14) (actual time=0.015..0.015 rows=1 loops=1) Index Cond: (b.fileid = a.fileid) -> Index Scan using ak_filemeta_archiveset_fileid on filemeta_archiveset a (cost=0.00..2.52 rows=6 width=4) (actual time=0.012..0.012 rows=1 loops=1) Index Cond: (a.fileid = b.fileid) Total runtime: 2.711 ms Hope this help. Best Regards, Alex Lai -- Best regards, Alex Lai OMI SIPS DBA ADNET Systems , Inc. 7515 Mission Drive, Suite A100 Lanham, MD 20706 301-352-4657 (phone) 301-352-0437 (fax) a...@sesda2.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 #6344: Trouble with plperl
On Sun, Dec 18, 2011 at 01:25, wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6344
> Logged by: Kirill
> Email address: k_deni...@inbox.ru
> PostgreSQL version: 9.1.2
> Operating system: Linux Debian
> Description:
>
> plperl function:
> open(FILE,">>/test/test.txt");
> print FILE "Проверка(Cyrillic text)";
> close FILE;
> Base codepage and client codepage cp1251.
> Record in file broken codepage.
Im not exactly sure what you are saying, but my hunch is you need to
use Encode or give the file handle an encoding:
binmode(FILE, ':encoding(cp1251)'); (or via open)
or
use Encode qw(encode);
my $str =decode("cp1251", "Проверка(Cyrillic text));
print FILE $str;
--
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 #6300: duplicate key value violates unique constraint
On Fri, Nov 18, 2011 at 14:31, Tigran Mkrtchyan wrote: > > On Fri, Nov 18, 2011 at 9:52 PM, Tomas Vondra wrote: > > Anyway this definitely is not a bug, > > Still not convinced. You are unlikely to convince anyone here otherwise... Its works that way by design. > [...] I don't think you will be happy of /var/log/messages will be > full of error messages when a user tries to create an existing file. > Well, of course I can check myself the file exists, but why I need DB > transactions if my application have to take care about that. If you don't want them logged you could 'catch' them by creating a wrapper function in plpsql, something like: http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE -- 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] plperl no longer provides string representations of composite values
On Fri, Oct 28, 2011 at 16:42, Mischa POSLAWSKY wrote:
> Composite types are converted to and from Perl hashes since commit
> REL9_1_ALPHA4~146 (Convert Postgres arrays to Perl arrays on PL/perl input
> arguments; 2011-02-17), but are not stringified as claimed by the commit
> message and release notes (unless nested in an array).
>
> To illustrate:
>
> CREATE TYPE foo AS (bar INTEGER, baz TEXT);
> DO $$ my $val = spi_exec_query(q< SELECT ROW(42,'test')::foo AS col
> >)->{rows}->[0]->{col}; elog(NOTICE, "$val ".ref($val)) $$ LANGUAGE plperl;
> NOTICE: HASH(0xb864a744) HASH
I take it this is your complaint.
> DO $$ my $val = spi_exec_query(q< SELECT ARRAY[ ROW(42,'test')::foo ] AS col
> >)->{rows}->[0]->{col}; elog(NOTICE, "$val ".ref($val)) $$ LANGUAGE plperl;
> NOTICE: {"(42,test)"} PostgreSQL::InServer::ARRAY
And you are saying this is OK.
You'll note the release notes and commit message only mention that
arrays have the backwards compatible mode. We didn't make a backwards
compatible string version for composites as you could already pass
those in prior to 9.1 (so really nothing changed). What did change is
we unified most of the code so the fact that spi could return arrays
also meant it could return hashes.
So yes that seems like a bit of an oversight. We could fix it by doing
something similar to what we did for arrays. That is turn composites
from hashrefs to blessed objects and overload their string operator.
I'm a bit worried that cure would be worse than whats "broken". Any
code that was using ref to test for 'HASH' would now break. Arrays did
not really have this concern because they were always a string prior
to 9.1. Composites on the other hand have been around a lot longer--
and while I know I don't have any checks like that in /my/ plperl
code, I don't think its too far fetched to imagine someone else does.
I suppose this is a long winded way of saying i'm not very inclined to
dump time into "fixing" this unless there is some kind of consensus
that we should.
Anyway I see 2 work arounds:
- You could change your query to return text:
SELECTROW(42,'test')::foo::text
- use the new encode_typed_literal() function 9.1 provides
> To make matters worse, received values cannot be used in queries because
> spi_exec_prepared() simply ignores hash arguments:
>
> # DO $$ my $q = spi_prepare('SELECT $1 AS col', 'foo' ); elog(NOTICE,
> spi_exec_prepared($q, { bar=>42, baz=>'test' } )->{rows}->[0]->{col}) $$
> LANGUAGE plperl;
> ERROR: spi_exec_prepared: expected 1 argument(s), 0 passed at line 1.
Not really, the error here is you can't call spi_exec_prepared() like that try:
spi_exec_prepared($q, {}, {bar=>42,...});
Per the documentation the prototype is:
spi_exec_prepared(plan [, attributes], arguments)
We can't distinguish attributes (which are a hashref) from a composite
type (also a hashref), so you need to pass an explicit value for
attributes. That's why its complaining about it expected 1 argument
but 0 were passed. If you pass some other type of reference (like an
array) we know it can't be attributes so we use that as the first
argument.
It may not be pretty, but we didn't want to break the api :-(.
--
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 #6256: loading small file eats all memory and crashes system
On Sat, Oct 15, 2011 at 14:16, Rachael Nelson wrote: > Attached is a tiny bit of top output (top.txt), the file being imported > (postgres.sql.gz - about 32MB), and the perl module script that generates Thanks. So the problem here seems to be postgres does not handle a multi-hundred megabyte single statement very well. In-fact you don't even need a table, I was able to run it out of memory using a 306MB single insert with the multirow values syntax. I would suggest using COPY FROM instead, it should be much faster. If you can't do that then try breaking up that big insert into multiple smaller ones. For the curious, find below the memory dump. Im not exactly sure where all the memory is going, I count ~1.5G below. This box has 8G total with ~7 Free before I ran the query. TopMemoryContext: 61360 total in 7 blocks; 3920 free (13 chunks); 57440 used TopTransactionContext: 8192 total in 1 blocks; 7520 free (0 chunks); 672 used MessageContext: 1487668504 total in 87 blocks; 2256 free (1 chunks); 1487666248 used Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used Relcache by OID: 24576 total in 2 blocks; 14912 free (3 chunks); 9664 used CacheMemoryContext: 817840 total in 20 blocks; 111704 free (0 chunks); 706136 used . MdSmgr: 8192 total in 1 blocks; 7584 free (0 chunks); 608 used LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used -- 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 #6256: loading small file eats all memory and crashes system
On Sat, Oct 15, 2011 at 13:40, Rachael Nelson wrote: > When I run this it does the same: > > psql test dev < postgres.sql > > the db is named test, connecting with the dev user. I assume you meant to hit reply all :-) If you can provide a self contained test case ideally in the form of an .sql file that would help. The table definition at the very least is a must. -- 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 #6256: loading small file eats all memory and crashes system
On Fri, Oct 14, 2011 at 21:43, Rachael wrote: > > The following bug has been logged online: > > Bug reference: 6256 > Logged by: Rachael > Email address: rulonr...@gmail.com > PostgreSQL version: 9.11 > Operating system: Arch Linux > Description: loading small file eats all memory and crashes system > Details: > > While attempting to import a sql file, postgres eats all my physical memory > (4GB) and swap file space(2GB). > > The file itself is a series of insert statements that is 10,000 rows by 100 > columns wide. A smaller file (1000 rows) loads just fine. I'll be glad to > provide sample files. The command I'm using is inside a perl script: > > print `psql $database $user < postgres.sql`; What happens when you remove perl from the equation? Or at the very least while doing the import have you tired looking a top or some other tool to see where the memory is going? -- 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 #6204: Using plperl functions generate crash
On Wed, Sep 14, 2011 at 03:15, pasman wrote: > > The following bug has been logged online: > > Bug reference: 6204 > Logged by: pasman > Email address: pasma...@gmail.com > PostgreSQL version: 9.1.0 > Operating system: WinXP sp3 > Description: Using plperl functions generate crash > Details: > > After install postgresql 9.1.0 and Activestate perl 5.14.1 execute: > > create function perl_test() returns void as > $$ > $$ > language plperl My hunch is the binary distribution of postgres you are using was compiled with a different version (or brand, maybe strawberry?) of perl in mind. I don't know what installer you used, I'd guess the one from enterprisedb. I looked around for a bit on their website but didn't see what flavor of perl for windows it wants... :-( -- 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 #6198: init-po fails for plperl due to invalid xsubpp path (contains ExtUtils)
On Sun, Sep 4, 2011 at 16:57, Tomas Vondra wrote: > On 5 Září 2011, 0:27, Alex Hunsaker wrote: >> On Sun, Sep 4, 2011 at 13:48, init-po fails for plperl due to invalid >> xsubpp path wrote: > So yes, it's almost the same as your results. > > > Aha! When I run configure like this > > $ ./configure --with-perl --enable-nls=cs Whoa, I totally missed that it was without --with-perl. :-) > then it works, so obviously the "--with-perl" option is required. > Shouldn't this behave a bit differently, e.g. not allowing "enable-nls" > without "with-perl"? Allowing that and getting not-fully-working tree is > not a good thing I guess ... Hrm, I don't know much about the nls stuff... but it seems like a reasonable request to me. -- 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 #6198: init-po fails for plperl due to invalid xsubpp path (contains ExtUtils)
On Sun, Sep 4, 2011 at 13:48, init-po fails for plperl due to invalid
xsubpp path wrote:
>
> Description: init-po fails for plperl due to invalid xsubpp path
> (contains ExtUtils)
> Details:
>
> When I try to prepare a fresh .pot file for plpgsql, it fails like this
>
> $ ./configure --enable-nls=cs
> $ cd src/pl/plperl
> $ gmake init-po
> '/usr/bin/perl' /ExtUtils/xsubpp -typemap /ExtUtils/typemap SPI.xs >SPI.c
> Can't open perl script "/ExtUtils/xsubpp": Directory or file does not
> exist.
> gmake: *** [SPI.c] Error 2
> gmake: *** Deleting file `SPI.c'
>
> This is due to invalid xsubpp/typemap paths - the xsubpp is available here
>
> $ which xsubpp
> /usr/bin/xsubpp
>
> but the GNUmakefile contains this:
>
> $(PERL) $(perl_privlibexp)/ExtUtils/xsubpp -typemap
> $(perl_privlibexp)/ExtUtils/typemap $< >$@
Erm... we have been using perl_privlibexp basically forever... What
version of perl is this? It might be helpful if you could provide the
output of the following:
$ perl -e 'use Config; print "$Config{privlibexp}\n";'
$ ls `perl -e 'use Config; print "$Config{privlibexp}\n";'`/ExtUtils
I get:
$ perl -e 'use Config; print "$Config{privlibexp}\n";'
/usr/share/perl5/core_perl
$ ls `perl -e 'use Config; print "$Config{privlibexp}\n";'`/ExtUtils
[ snip ]
MM_VOS.pm
MM_Win32.pm
MM_Win95.pm
MY.pm
Packlist.pm
ParseXS.pm
testlib.pm
typemap
xsubpp
Here we can see both typemap and xsubpp where we expect them.
> After changing to
>
> xsubpp -typemap
> /usr/src/linux-2.6.38-gentoo-r6/tools/perf/scripts/perl/Perf-Trace-Util/type
> map $< >$@
Why are you using that for the typemap? it looks like you just used
find and decided to use the first file it found named 'typemap'... It
would be interesting to know where the real typemap file is. Maybe you
could paste the output of find /usr -name typemap?
--
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 #6166: configure from source fails with 'This platform is not thread-safe.' but was actually /tmp perms
The following bug has been logged online: Bug reference: 6166 Logged by: Alex Soto Email address: aps...@gmail.com PostgreSQL version: 9.0.4 Operating system: Linux (CentOS release 5.0 (Final)) Description:configure from source fails with 'This platform is not thread-safe.' but was actually /tmp perms Details: I was trying to build the 9.0.4 source tarball as the postgres user on a test machine. The configure step failed with the error: This platform is not thread-safe. Check the file 'config.log' or compile and run src/test/thread/thread_test for the exact reason. Use --disable-thread-safety to disable thread safety. As I started looking through the log file I noticed that it failed to write to the /tmp directory. Other configure steps had written to /var/tmp, but this step tried to write to /tmp for some reason. I fixed by correcting the permissions on the /tmp dir, but I thought the error message was a little misleading, so I thought I'd report the problem. The specific configure I ran was: ./configure --disable-integer-datetimes but I don't think the config option likely makes a difference. -- 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 #6159: Can't create unlogged table
The following bug has been logged online: Bug reference: 6159 Logged by: Alex Email address: perepelica.a...@gmail.com PostgreSQL version: 9.1beta3 Operating system: linux x86_64 archlinux Description:Can't create unlogged table Details: Execute from pgadmin create unlogged table public.test ( id serial primary key, some_text text ); And get such error ERROR: only temporary relations may be created in temporary schemas -- 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 #6139: LIMIT doesn't return correct result when the value is huge
On Tue, Aug 2, 2011 at 00:25, Hitoshi Harada wrote: > db1=# select * from test_xy order by x LIMIT 9223372036854775807 OFFSET 6; [ ...] > db1=# select * from test_xy order by x LIMIT pow(2, 63); > ERROR: bigint out of range > ERROR: bigint out of range > > Maybe a parser converts literal unexpectedly? pow(2, 63) != 9223372036854775807, pow(2, 63) - 1 does :-). On top of that pow(2, 63) seems to default to the double variant of pow() which causes rounding issues. You probably want LIMIT pow(2, 63::numeric)-1. -- 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 #6138: Install failed
The following bug has been logged online: Bug reference: 6138 Logged by: Alex Dragojlovic Email address: aleksandar.dragojlo...@gmail.com PostgreSQL version: 8.4 Operating system: OSX Lion Description:Install failed Details: I cannot install PostgreSql on my laptop with OSX Lion. Closest solution I found is this: http://www.azattis.com/2011/07/os-x-lion-postgresql-84-install-problem.html. Can you help me? -- 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 #6054: Insert to table, which has fkey to table,which is parenttable for another table - error
The following bug has been logged online: Bug reference: 6054 Logged by: Alex Email address: alexander.ochkal...@gmail.com PostgreSQL version: 8.4.8 Operating system: CentOS Description:Insert to table, which has fkey to table,which is parenttable for another table - error Details: CREATE TABLE t1 (t1_id numeric(10,0) NOT NULL PRIMARY KEY); CREATE TABLE t2 (t2_id numeric(10,0) NOT NULL PRIMARY KEY) INHERITS (t1); CREATE TABLE t3 (t3_id numeric(10,0) NOT NULL PRIMARY KEY , t1_id numeric(10,0) NOT NULL REFERENCES t1(t1_id)) ; INSERT INTO t2 VALUES(1,2); INSERT INTO t3 VALUES(3,1); ERROR: insert or update on table "t3" violates --- foreign key constraint "t3_t1_id_fkey" DETAIL: Key (t1_id)=(1) is not present in table "t1". BUT SELECT t1_id FROM t1 WHERE t1_id = 1; -->1 -- 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] 9.1 plperlu bug with null rows in trigger hash
On Mon, May 30, 2011 at 11:02, Alvaro Herrera wrote: > Excerpts from Alvaro Herrera's message of sáb may 28 01:06:42 -0400 2011: >> Excerpts from Alex Hunsaker's message of vie may 27 12:14:25 -0400 2011: >> > On Mon, May 23, 2011 at 20:08, Greg Sabino Mullane >> > wrote: >> > > On Mon, May 23, 2011 at 05:04:40PM -0600, Alex Hunsaker wrote: >> > > ... >> > >> Greg, can you confirm the attached fixes it for you? >> > > >> > > Yes, seems to have done the job, thank you. >> > >> > Thanks for testing! >> > >> > [ Does a little dance to try and attract a -commiter ] >> >> Okay, I'll handle it :-) > > Pushed, thanks. Great thanks! -- 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] 9.1 plperlu bug with null rows in trigger hash
On Mon, May 23, 2011 at 20:08, Greg Sabino Mullane wrote: > On Mon, May 23, 2011 at 05:04:40PM -0600, Alex Hunsaker wrote: > ... >> Greg, can you confirm the attached fixes it for you? > > Yes, seems to have done the job, thank you. Thanks for testing! [ Does a little dance to try and attract a -commiter ] This was broken as part of: commit 87bb2ade2ce646083f39d5ab3e3307490211ad04 Author: Alvaro Herrera Date: Thu Feb 17 22:11:50 2011 -0300 Convert Postgres arrays to Perl arrays on PL/perl input arguments More generally, arrays are turned in Perl array references, and row and composite types are turned into Perl hash references. This is done recursively, in a way that's natural to every Perl programmer. To avoid a backwards compatibility hit, the string representation of each structure is also available if the function requests it. Authors: Alexey Klyukin and Alex Hunsaker. Some code cleanups by me. Which also means it only breaks HEAD/9.1 (I did test and review 9.0 and down.) Per http://search.cpan.org/~jesse/perl-5.14.0/pod/perlguts.pod#AVs,_HVs_and_undefined_values, we do not want to use &PL_sv_undef for undef values in hashes and arrays. I (inadvertently) broke this in the above commit. As perldoc mentions &PL_sv_undef seems like the intuitive thing to use. But its wrong in certain cases. We have 6 other uses of &PL_sv_undef, 2 &PL_sv_no and 1 &PL_sv_yes. These are all ok as none of them use the HV/AV store interface. I elected _not_ to add any regression tests. (If we forget about this in the future, it will likely be in other code paths). Instead I added comments to the places that used &PL_sv_undef noting that we explicitly avoid it on purpose. -- 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] 9.1 plperlu bug with null rows in trigger hash
On Mon, May 23, 2011 at 14:59, Greg Sabino Mullane wrote:
> I've not been able to duplicate this in a standalone script yet,
> but in the guts of Bucardo is a trigger function called validate_goat()
> that is giving this error on 9.1 HEAD, but not on previous versions:
>
> "Failed to add table "public.pgbench_tellers": DBD::Pg::st execute
> failed: ERROR: Modification of non-creatable hash value attempted,
> subscript "pkey" at line 4."
> ...
> Some Googling suggests it might
> be because we are using &PL_sv_undef instead of a proper
> newSV(0).
Yep. Per
http://search.cpan.org/~jesse/perl-5.14.0/pod/perlguts.pod#AVs,_HVs_and_undefined_values
|...For example, intuition tells you that this XS code:
|
|AV *av = newAV();
|av_store( av, 0, &PL_sv_undef );
|
| is equivalent to this Perl code:
|
|my @av;
|$av[0] = undef;
| Unfortunately, this isn't true. AVs use &PL_sv_undef as a marker for
indicating that an array element has not yet been initialized.
We have a few places that have that pattern :-(.
I was able to reproduce it fairly easily(1) by passing in NULL values
explicitly. Fixed in the attached.
I looked at 9.0 and below and they did this correctly. This code path
was heavily re-factored in 9.1 for better array and composite type
support . As noted in perlguts using &PL_sv_undef follows your
intuition, but its wrong :-(. Classic perl xs I suppose.
Greg, can you confirm the attached fixes it for you?
--
[1]
=> create or replace function td() returns trigger language plperlu as
$bc$
$_TD->{new}{a} = 1;
return 'MODIFY';
$bc$;
CREATE FUNCTION
=> create table trig_test(a int);
CREATE TABLE
=> create trigger test_trig before insert on trig_test for each row
execute procedure td();
CREATE TRIGGER
=> insert into trig_test values (NULL);
CONTEXT: PL/Perl function "td"
ERROR: Modification of non-creatable hash value attempted, subscript
"a" at line 2.
*** a/src/pl/plperl/plperl.c
--- b/src/pl/plperl/plperl.c
***
*** 1357,1363 make_array_ref(plperl_array_info *info, int first, int last)
for (i = first; i < last; i++)
{
if (info->nulls[i])
! av_push(result, &PL_sv_undef);
else
{
Datum itemvalue = info->elements[i];
--- 1357,1366
for (i = first; i < last; i++)
{
if (info->nulls[i])
! {
! /* We cant use &PL_sv_undef here... see perlguts */
! av_push(result, newSV(0));
! }
else
{
Datum itemvalue = info->elements[i];
***
*** 2639,2646 plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc)
if (isnull)
{
! /* Store (attname => undef) and move on. */
! hv_store_string(hv, attname, &PL_sv_undef);
continue;
}
--- 2642,2652
if (isnull)
{
! /*
! * Store (attname => undef) and move on. Note, we cant use
! * &PL_sv_undef here... see perlguts for more
! */
! hv_store_string(hv, attname, newSV(0));
continue;
}
--
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] Plperl trigger variables no longer global
On Sun, May 15, 2011 at 14:02, Robert Haas wrote:
>> Fixed in the attached plus added regression tests for both issues (use
>> strict; && Global symbol "$_TD" requires explicit package name, test
>> recursive trigger calls). Although Ill admit, given the point we are
>> in the release I could see a revert also being justified.
>>
>> Greg, big thanks for testing! keep it up! :)
>
> Do we need to apply this patch?
Only if we want use strict and trigger functions to work in 9.1 :). I
realize I did a poor job of explaining the problem and the solution,
that probably made it hard for a -commiter to pick up. Here is a stab
at that.
[ pgcon is spinning up so I won't hold my breath waiting for a response ]
Problem:
CREATE FUNCTION wheredidmytdgo()
RETURNS TRIGGER
LANGUAGE plperlu
AS
$bc$
use strict;
my $new = $_TD->{new};
return;
$bc$;
fails with ERROR: Global symbol "$_TD" requires explicit package name
at line 3.
Analysis:
The above fails due to
http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=ef19dc6d39dd2490ff61489da55d95d6941140bf.
It moved declaring of $_TD from plperl_create_sub to
plperl_call_perl_trigger_func(). Before it was always declared even
for non trigger functions. This broke CREATE FUNCTION validation as
$_TD has not been declared. Assuming you could get past the validator
things would still not work, plperl_call_trigger_func() tried to
declare it using get_sv("_TD", GV_ADD) which seems like it should
work, but does not[1].
We (I) failed to notice this during review as it only shows when
running under "use strict", which apparently is uncommon.
Fix:
My proposed fix is instead of declaring $_TD in
plperl_call_perl_trigger_func(), just declare it as a global the same
way we declare %_SHARED. It keeps things simple and we should get to
keep any performance benefits from the patch.
While playing with it I also noticed it was failing to be local()ized
properly meaning nested trigger functions would clobber $_TD, also fix
that.
Also added regression tests for both issues.
--
(1) If it was working and creating a global correctly you should be
able to call a trigger function so that $_TD gets declared and then
create a new trigger function that uses strict and references $_TD
without any problems. But it doesn't work...
# create or replace function td() returns trigger language plperlu as
$bc$ return; $bc$;
CREATE FUNCTION
# create table trig_test(a int);
CREATE TABLE
# create trigger test_trig after insert on trig_test execute procedure td();
CREATE TRIGGER
# insert into trig_test values (1);
INSERT 0 1
# CREATE or replace FUNCTION wheredidmytdgo()
RETURNS TRIGGER
LANGUAGE plperlu
AS
$bc$
use strict;
my $new = $_TD->{new};
return;
$bc$;
ERROR: Global symbol "$_TD" requires explicit package name at line 3.
--
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] Upgrading from 1.10 to 1.12 - cannot set up server
We figured it out: the server add simply does not show up until you close and reopen the app. Once we do that, it behaves normally. It is a bug, but not a show-stopper. Thanks! Alexander Bryant IT Services for VIDD and SCHARP -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Monday, May 09, 2011 7:39 PM To: Bryant, Alex Cc: pgsql-bugs@postgresql.org; Lowe, Doug Subject: Re: [BUGS] Upgrading from 1.10 to 1.12 - cannot set up server On Fri, Apr 22, 2011 at 3:29 PM, Bryant, Alex wrote: > We are moving from PGAdmin III 1.10 on Windows XP Pro 32 bit to PG > Admin III on Windows 7 Enterprise 64-bit. PG Admin III installed > without issue and the computer is running all other database programs normally. > > When we open up PG Admin III and click Server-Add, we are entering > exactly the same info as we did for version 1.10 on the old computer > (which is still available, up and running, and we used it to compare > side-by-side. The trouble is, once we hit OK on the appropriate Add > screen after filling out all the data, the server does not add. There > is no error, just nothing at all happens save for a message in the > bottom left saying "Connecting to server - Done". > > Is there a compatibility issue with Windows 7 64 bit? Is there another > view we are missing that would reveal the server? Have you any suggestions? If you haven't already, you might want to try this question on the pgadmin-support mailing list. http://archives.postgresql.org/pgadmin-support/ -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #6010: booting problem
The following bug has been logged online: Bug reference: 6010 Logged by: alex Email address: alexandr.kas...@gmail.com PostgreSQL version: 9.1 beta1 Operating system: snow leopard Description:booting problem Details: after changing sysctl.conf values and rebooting (i ve used values in readme file), my permissions changed and now i cant get into my ~/Library folder and all my settings for applications, my mail, other things which is saved in this folder - gone (not accessible)! when i rebooted again, nothing new happened. it is very frustrating that your simple installation steps can be so dangerous. what do i have to do now? fuck. -- 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] Plperl trigger variables no longer global
On Thu, May 5, 2011 at 06:51, Alvaro Herrera wrote:
> Excerpts from Alex Hunsaker's message of mié may 04 23:53:34 -0300 2011:
>
>> After playing with it a bit more I see 2 clear options:
>> 1) make $_TD global like %_SHARED. This should not cause any problems
>> as we make $_TD private via local() before each trigger call. Also pre
>> 9.1 non trigger functions could still access and check the definedness
>> of $_TD so if someone was relying on that (for whatever unknown
>> reason) that will work again.
>
> This is strange. Are you saying that there's no decent way to make a
> variable global in C code?
Im sure we could... I don't see any reason to do it in C. (performance
or otherwise)
In other news I found another bug with this-- it was trying to
local($_TD) by using SAVESPTR() when it seems it really should be
using save_item(). Currently its not really localizing $_TD, which at
the very least means recursive triggers might modify the callers $_TD.
Ugh.
Fixed in the attached plus added regression tests for both issues (use
strict; && Global symbol "$_TD" requires explicit package name, test
recursive trigger calls). Although Ill admit, given the point we are
in the release I could see a revert also being justified.
Greg, big thanks for testing! keep it up! :)
*** a/src/pl/plperl/expected/plperl_trigger.out
--- b/src/pl/plperl/expected/plperl_trigger.out
***
*** 255,260 SELECT * FROM trigger_test;
--- 255,289
5 | third line(modified by trigger)(modified by trigger) | ("(5)")
(4 rows)
+ DROP TRIGGER "test_valid_id_trig" ON trigger_test;
+ CREATE OR REPLACE FUNCTION trigger_recurse() RETURNS trigger AS $$
+ use strict;
+
+ if ($_TD->{new}{i} == 1)
+ {
+ spi_exec_query("insert into trigger_test (i, v) values (2, 'child');");
+
+ if ($_TD->{new}{i} != 1)
+ {
+ die "recursive trigger modified: ". $_TD->{new}{i};
+ }
+ }
+ return;
+ $$ LANGUAGE plperl;
+ CREATE TRIGGER "test_trigger_recurse" BEFORE INSERT ON trigger_test
+ FOR EACH ROW EXECUTE PROCEDURE "trigger_recurse"();
+ INSERT INTO trigger_test (i, v) values (1, 'top');
+ SELECT * FROM trigger_test;
+i | v | foo
+ ---+--+-
+ 1 | first line(modified by trigger) | ("(2)")
+ 2 | second line(modified by trigger) | ("(3)")
+ 4 | immortal | ("(4)")
+ 5 | third line(modified by trigger)(modified by trigger) | ("(5)")
+ 2 | child|
+ 1 | top |
+ (6 rows)
+
CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$
if ($_TD->{old}{v} eq $_TD->{args}[0])
{
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 1,7
# src/pl/plperl/plc_perlboot.pl
use 5.008001;
! use vars qw(%_SHARED);
PostgreSQL::InServer::Util::bootstrap();
--- 1,7
# src/pl/plperl/plc_perlboot.pl
use 5.008001;
! use vars qw(%_SHARED $_TD);
PostgreSQL::InServer::Util::bootstrap();
*** a/src/pl/plperl/plperl.c
--- b/src/pl/plperl/plperl.c
***
*** 1976,1983 plperl_call_perl_trigger_func(plperl_proc_desc *desc, FunctionCallInfo fcinfo,
ENTER;
SAVETMPS;
! TDsv = get_sv("_TD", GV_ADD);
! SAVESPTR(TDsv);/* local $_TD */
sv_setsv(TDsv, td);
PUSHMARK(sp);
--- 1976,1986
ENTER;
SAVETMPS;
! TDsv = get_sv("_TD", 0);
! if(!TDsv)
! elog(ERROR, "couldn't fetch $_TD");
!
! save_item(TDsv);/* local $_TD */
sv_setsv(TDsv, td);
PUSHMARK(sp);
*** a/src/pl/plperl/sql/plperl_trigger.sql
--- b/src/pl/plperl/sql/plperl_trigger.sql
***
*** 122,127 UPDATE trigger_test SET i = 100 where i=1;
--- 122,151
SELECT * FROM trigger_test;
+ DROP TRIGGER "test_valid_id_trig" ON trigger_test;
+
+ CREATE OR REPLACE FUNCTION trigger_recurse() RETURNS trigger AS $$
+ use strict;
+
+ if ($_TD->{new}{i} == 1)
+ {
+ spi_exec_query("insert into trigger_test (i, v) values (2, 'child');");
+
+ if ($_TD->{new}{i} != 1)
+ {
+ die "recursive trigger modified: ". $_TD->{new}{i};
+ }
+ }
+ return;
+ $$ LANGUAGE plperl;
+
+ CREATE TRIGGER "test_trigger_recurse" BEFORE INSERT ON trigger_test
+ FOR EACH ROW EXECUTE PROCEDURE "trigger_recurse"();
+
+ INSERT INTO trigger_test (i, v) values (1, 'top');
+
+ SELECT * FROM trigger_test;
+
CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$
if ($_TD->{old}{v} eq $_TD->{args}[0])
{
--
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] Plperl trigger variables no longer global
On Wed, May 4, 2011 at 16:20, Alex Hunsaker wrote:
>
> This seems to be broken by
> http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=ef19dc6d39dd2490ff61489da55d95d6941140bf
> (Set up PLPerl trigger data using C code instead of Perl code.)
>
> Im not sure what the right fix is. Copying what
> plperl_call_trigger_func() does for _TD ("get_sv("_TD", GV_ADD); ..."
> into plperl_create_sub() does not seem to work.
Just a small clarification, this only breaks when running under use strict;.
After playing with it a bit more I see 2 clear options:
1) make $_TD global like %_SHARED. This should not cause any problems
as we make $_TD private via local() before each trigger call. Also pre
9.1 non trigger functions could still access and check the definedness
of $_TD so if someone was relying on that (for whatever unknown
reason) that will work again.
2) revert the optimization
#1 is very small and I don't see any downsides (maybe we should local
$_TD before regular perl calls as well??). Find it attached.
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 1,7
# src/pl/plperl/plc_perlboot.pl
use 5.008001;
! use vars qw(%_SHARED);
PostgreSQL::InServer::Util::bootstrap();
--- 1,7
# src/pl/plperl/plc_perlboot.pl
use 5.008001;
! use vars qw(%_SHARED $_TD);
PostgreSQL::InServer::Util::bootstrap();
*** a/src/pl/plperl/plperl.c
--- b/src/pl/plperl/plperl.c
***
*** 1976,1982 plperl_call_perl_trigger_func(plperl_proc_desc *desc, FunctionCallInfo fcinfo,
ENTER;
SAVETMPS;
! TDsv = get_sv("_TD", GV_ADD);
SAVESPTR(TDsv);/* local $_TD */
sv_setsv(TDsv, td);
--- 1976,1985
ENTER;
SAVETMPS;
! TDsv = get_sv("_TD", 0);
! if (!TDsv)
! elog(ERROR, "couldn't fetch $_TD");
!
SAVESPTR(TDsv);/* local $_TD */
sv_setsv(TDsv, td);
--
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] Plperl trigger variables no longer global
On Tue, May 3, 2011 at 17:40, Greg Sabino Mullane wrote:
> This works in 9.0 but not in git/9.1 beta:
>
> CREATE FUNCTION wheredidmytdgo()
> RETURNS TRIGGER
> LANGUAGE plperlu
> AS
> $bc$
> use strict; use warnings;
> my $new = $_TD->{new};
> return;
> $bc$;
>
> The error is:
>
> ERROR: Global symbol "$_TD" requires explicit package name at line 3.
> CONTEXT: compilation of PL/Perl function "wheredidmytdgo"
This seems to be broken by
http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=ef19dc6d39dd2490ff61489da55d95d6941140bf
(Set up PLPerl trigger data using C code instead of Perl code.)
Im not sure what the right fix is. Copying what
plperl_call_trigger_func() does for _TD ("get_sv("_TD", GV_ADD); ..."
into plperl_create_sub() does not seem to work.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Upgrading from 1.10 to 1.12 - cannot set up server
We are moving from PGAdmin III 1.10 on Windows XP Pro 32 bit to PG Admin III on Windows 7 Enterprise 64-bit. PG Admin III installed without issue and the computer is running all other database programs normally. When we open up PG Admin III and click Server-Add, we are entering exactly the same info as we did for version 1.10 on the old computer (which is still available, up and running, and we used it to compare side-by-side. The trouble is, once we hit OK on the appropriate Add screen after filling out all the data, the server does not add. There is no error, just nothing at all happens save for a message in the bottom left saying "Connecting to server - Done". Is there a compatibility issue with Windows 7 64 bit? Is there another view we are missing that would reveal the server? Have you any suggestions? Thank you so much. Alexander Bryant IT Services for VIDI and Scharp Fred Hutchison Cancer Research Center 1616 Eastlake, 5th Floor 206-667-2671 (w) 206-601-8054 (c)
Re: [BUGS] postgres 9 streaming replication
Fujii Masao wrote: On Wed, Mar 23, 2011 at 9:07 PM, Alex Lai wrote: I have no idea why I keep getting the message:\ could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" Can you connect from slave to master by the following command? psql "replication=1" -h -p 5432 -U If successful, the settings in recovery.conf would be wrong. Otherwise, I guess that the setting in pg_hba.conf of the master would be wrong. Did you reload the configuration files? What happens if you use "all" keyword instead of specifying the real user name? What happens if you use "0.0.0.0/0" instead of specifying the slave's ip? You would need to do trial and error approach. Regards, After changed to all for -U in pg_hba.conf, it connected without problem. Thanks for your help. -- Best regards, Alex Lai OMI SIPS DBA ADNET Systems , Inc. 7515 Mission Drive, Suite A100 Lanham, MD 20706 301-352-4657 (phone) 301-352-0437 (fax) a...@sesda2.com -- 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 #5950: backend terminating after altering table
The following bug has been logged online: Bug reference: 5950 Logged by: alex Email address: perepelica.a...@gmail.com PostgreSQL version: 9.0.3 Operating system: archlinux x86_64 Description:backend terminating after altering table Details: Such steps: 1. create table t ( ); 2. alter table t add childs t; 3. alter table t add id serial not null primary key; This messages from psql: NOTICE: ALTER TABLE will create implicit sequence "t_id_seq" for serial column "t.id" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_pkey" for table "t" 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. backend - terminated -- 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] postgres 9 streaming replication
Khadtare, Sharad wrote: Hi, Problem solved after removing trigger entry from recovery.conf file Thx for help Regards, Sharad K -Original Message- From: Fujii Masao [mailto:masao.fu...@gmail.com] Sent: Tuesday, January 25, 2011 5:55 PM To: Khadtare, Sharad Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] postgres 9 streaming replication On Tue, Jan 25, 2011 at 8:59 PM, Khadtare, Sharad wrote: Pls find below logfile of standby and recovery.conf in standby data directory. bash-3.2$ cat logfile LOG: database system was interrupted while in recovery at log time 2011-01-25 05:28:35 EST HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: entering standby mode LOG: restored log file "00010037" from archive LOG: redo starts at 0/3720 LOG: consistent recovery state reached at 0/3800 LOG: database system is ready to accept read only connections cp: cannot stat `/db/postgres/archive/00010038': No such file or directory LOG: unexpected pageaddr 0/3000 in log file 0, segment 56, offset 0 cp: cannot stat `/db/postgres/archive/00010038': No such file or directory LOG: trigger file found: /db/postgres/trigger The above message means that you created the trigger file and promoted the standby server to the master. Since the standby was running as the master, walreceiver was not invoked in the standby. To start replication, you need to create the standby (taking the base backup from the master is required) and start it after you *ensure* that there is no trigger file in the standby. I hope this helps.. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center I have a same problem trying to get slave replication using 9.0 streaming replication. After the solution, I still not able to get through. First, I scp the whole data directory from master to slave server. In master server, I added the following lines in pg_hba.conf. host replication my_user_name slave_ip/32 trust host all all x.x.x.x/32 trust Note: my_user_name replaced with my real super user name and slave_ip replaced with slave server's ip. In master server, I also added the following lines in postgresql.conf. wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 32 listen_addresses='*' In slave server, I added the following line in postgresql.conf. hot_standby = on In slave server, I added the following line in recovery.conf. standby_mode = 'on' primary_conninfo = 'host=master_ip port=5432 user=my_user_name' trigger_file = '/path_to/pgsql.trigger' Note: my_user_name replaced with my real super user name and master_ip replaced with master server's ip. I removed trigger_file from recovery.conf and restart the slave. It still have the same problem. I then started master and slave servers. I got the error message from slave pg_log. ... cp: cannot stat `/path_to/arch_replicate/0001004A001F': No such file or directory cp: cannot stat `/path_to/arch_replicate/0001004A001F': No such file or directory 107602011-03-22 09:42:03 EDTFATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" I got the error message from master pg_log. ... 1380902011-03-22 09:37:51 EDTLOG: connection received: host=slave_server_ip port=51686 1380902011-03-22 09:37:51 EDTauthenticationFATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" 1381002011-03-22 09:37:56 EDTLOG: connection received: host=slave_server_ip port=51687 1381002011-03-22 09:37:56 EDTauthenticationFATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" 1381102011-03-22 09:38:00 EDTLOG: connection received: host=slave_server_ip port=51688 1381102011-03-22 09:38:00 EDTauthenticationFATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" I was able to psql connect from slave to master or from master to slave by using. From slave server, I can connect from slave to master by the following command. psql database_name -f master_ip -p 5432 From master server, I can connect from master to slave by the following command. psql database_name -f slave_ip -p 5432 I have no idea why I keep getting the message:\ could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" Your help will be highly appreciated. Alex Lai -- Best regards, Alex Lai OMI SIPS DBA ADNET Systems , Inc. 75
[BUGS] BUG #5941: i don t understand
The following bug has been logged online: Bug reference: 5941 Logged by: GIMENEZ alex Email address: parabuenara...@hotmail.com PostgreSQL version: 8.4 Operating system: windows 7 Description:i don t understand Details: hello i have a probleme to install poker tracker 3 please help me thank you -- 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 #5665: catalog/schemapg.h: No such file or directory
I'm seeing this problem too, so I might be able to shed some light on it. Here's exactly what I did: > cd /usr/src/postgresql-9.0.2 > tar -xjvf postgresql-9.0.2.tar.bz2 > mv postgresql-9.0.2 src > mkdir obj-x86_64 > cd obj-x86_64 > /usr/src/postgresql-9.0.2/src/configure > '--prefix=/usr/local/postgresql-9.0.2' \ '--exec-prefix=/usr/local/postgresql-9.0.2' \ '--libdir=/usr/local/postgresql-9.0.2/lib64' \ '--sysconfdir=/usr/etc' \ '--localstatedir=/var' \ '--disable-nls' \ '--enable-integer-datetimes' > make ... make[4]: Entering directory `/loot/src/postgresql-9.0.2/obj-x86_64/src/backend/utils/cache' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -I../../../../src/include -I/usr/src/postgresql-9.0.2/src/src/include -D_GNU_SOURCE -c -o relcache.o /usr/src/postgresql-9.0.2/src/src/backend/utils/cache/relcache.c /usr/src/postgresql-9.0.2/src/src/backend/utils/cache/relcache.c:57:30: fatal error: catalog/schemapg.h: No such file or directory compilation terminated. > find /usr/src/postgresql-9.0.2 -name schemapg.h -ls -rw-r--r-- 1 dylansrc 14754 Dec 13 22:20 ./src/src/backend/catalog/schemapg.h lrwxrwxrwx 1 dylansrc68 Jan 29 06:38 ./obj-x86_64/src/include/catalog/schemapg.h -> /loot/src/postgresql-9.0.2/obj-x86_64/src/backend/catalog/schemapg.h > make -C /usr/src/postgresql-9.0.2/obj-x86_64/src/backend/catalog schemapg.h make: `/usr/src/postgresql-9.0.2/src/src/backend/catalog/schemapg.h' is up to date. > make -v GNU Make 3.82 It looks like this is related to VPATH, in that make finds the distributed schemapg.h when it goes to generate it in the obj directory, and so it's never created there. Perhaps schemapg.h should not be distributed if it's generated, or the symlink should point to srcdir instead of builddir. -- 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 #5835: PL/Python crashes
On Thu, Jan 13, 2011 at 06:57, Julien Demoor wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5835
> Logged by: Julien Demoor
> Email address: jdem...@gmail.com
> PostgreSQL version: 9.0.2
> Operating system: Ubuntu Linux
> Description: PL/Python crashes
> Details:
> BEGIN;
> CREATE OR REPLACE FUNCTION test_none_3(a numeric[]) RETURNS numeric(12,4) AS
> $$
> return 1;
> $$ LANGUAGE plpython2u;
> SELECT test_none_3('{NULL, NULL, NULL}'::numeric[]) FROM
> generate_series(1,244) ORDER BY generate_series DESC LIMIT 1;
> ROLLBACK;
It looks like we are not incrementing a reference count for NULL
entries in an array. The attached fixes it for me. I did look for
other places where we might have missed this and did not find any. (
the PyDict_SetItemString() that use Py_None look fishy, but apparently
work ).
Can you see if it fixes it for you?
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
***
*** 2043,2049 PLyList_FromArray(PLyDatumToOb *arg, Datum d)
--- 2043,2052
elm->typlen, elm->typbyval, elm->typalign,
&isnull);
if (isnull)
+ {
+ Py_INCREF(Py_None);
PyList_SET_ITEM(list, i, Py_None);
+ }
else
PyList_SET_ITEM(list, i, elm->func(elm, elem));
}
--
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 #5719: Constraint Problem
On Thu, Oct 21, 2010 at 12:03, Alex Hunsaker wrote: > Or if you just want the not null constraints: > create table kittu1 (like kota1 including constraints); Correction, the above should read: "Or, if you want check constraints". Not null constraints are always copied, and including constraints only deals with check constraints. On Thu, Oct 21, 2010 at 22:01, KOTAPATI KRISHNAIAH wrote: [ Example shrunk ] > CREATE TABLE cons > ( > id integer NOT NULL primary key, > ); > > The above cons table already existed. > when i'm creating the above same structure of table by using below this > Query i'm not getting constraints ... > > create table vijju1(like cons including constraints); > > CREATE TABLE vijju1 > ( > id integer NOT NULL, > ); Or in other words create table like is not copying the primary key constraint. The simple answer is to use "including indexes". There is quite a bit of discussion on this, some interesting points: http://archives.postgresql.org/pgsql-hackers/2009-12/msg02018.php and a long thread with a patch...: http://postgresql.1045698.n5.nabble.com/CREATE-TABLE-LIKE-INCLUDING-INDEXES-support-td2220980.html The sad news is I dont think anything appened for 8.4 or 9.0. -- 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 #5719: Constraint Problem
On Thu, Oct 21, 2010 at 03:05, KOTAPATI.KRISHNAIAH wrote: > > The following bug has been logged online: > > Bug reference: 5719 > Logged by: KOTAPATI.KRISHNAIAH > Email address: kotapati.kris...@hotmail.com > PostgreSQL version: PostgreSQL8.3.1 > Operating system: Microsoft Windows [Version 6.1.7600] > Description: Constraint Problem > Details: > > create table kittu1 as select * from kota1; when i use this command ,i got > the following details: I may have misunderstood the problem, I dont see a bug here. Im assuming you want kittu1 to have the constraints that kota1 has. create table ... as select ... won't work simply because the the select does not have the information needed. Try: create table kittu1 (like kota1 including constraints including defaults including indexes); insert into kittu1 select * from kota1; Or if you just want the not null constraints: create table kittu1 (like kota1 including constraints); See http://www.postgresql.org/docs/8.3/static/sql-createtable.html for more. [ note it looks like with postgresql 9.0 and up you can just say like kota1 including all ] -- 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 #5704: not correct restrictions plperlu
On Wed, Oct 13, 2010 at 10:34, Robert Haas wrote:
> On Mon, Oct 11, 2010 at 4:24 PM, Igor wrote:
>> PostgreSQL version: 8.1
>> Operating system: CentOs 5
>> Description: not correct restrictions plperlu
>> Details:
>>
>> rights of functions on plperlu
>> lower than the user postgres
>>
>> external system command not execut (telnet)
>
> It's very unclear from this description what you think the problem is.
Yeah, however if I had to guess: the system user 'postges' can execute
telnet but system('telnet') from within plperlu fails. (Or the other
way around?) Still not much we can do without error messages or test
cases ;). My hunch would be hrm, its centos... selinux related? Try
fiddling with that. Also make sure you specify the full path or that
your PATH is set right.
> Please provide a self-contained test case showing what you think
> should happen and what actually does happen.
Obviously including any relevant error messages :).
--
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 #5692: fatal error
On Mon, Oct 4, 2010 at 11:59, V.J. wrote:
> * Starting PostgreSQL 8.4 database server
> * The PostgreSQL server failed to start. Please check
> the log output:
> 2010-10-04 19:58:40 CEST FATAL: konnte auf private Schl?sseldatei
>>>server.key<< nicht zugreifen: No such file or directory
Looks like you have ssl = on but are missing some of the required ssl
files ("server.key"). See
http://www.postgresql.org/docs/8.4/static/ssl-tcp.html for more.
--
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] Exclude constraint problem
Tom Lane wrote: So these two geometry values do not overlap in the original database, but they do overlap in the clone, apparently because the output representation of geometry doesn't result in an exact reconstruction of the value. Somebody better complain over in the postgis lists. Thanks for doing all of that leg work. Whinging has commenced. - alex -- 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] Exclude constraint problem
Tom Lane wrote: Alex Zepeda writes: I'd be happy to provide someone with a copy of the dump... but it's ~70MB bzip'd. I'd be willing to take a look ... but if my WAG is right, the insertion order would be critical information to reproduce the problem. Is it possible to tell what order the rows were inserted in? Yes. There are two relevant columns: id -- a sequence created_at -- a timestamp w/ timezone The timestamps are all a bit wonky, but in a consistent manner due to how ActiveRecord handles timezones. - alex -- 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] Exclude constraint problem
Tom Lane wrote: Alex Zepeda writes: If I try to manually add the constraint on the table I get: blockface=# alter table bus_positions add constraint "exclude_time_buffer" EXCLUDE USING gist (vehicle WITH =, buffer_time WITH &&, bbox_dup WITH &&); NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "exclude_time_buffer" for table "bus_positions" ERROR: could not create exclusion constraint "exclude_time_buffer" Try manually adding a similar constraint (ie, one redundant with the existing one) on the source box. Does it work? So... erm... dinky machine (4GB physical + 4GB swap for now), rather large data set (a hair over a million rows). I ran out of memory before the add constraint operation could complete (reindex as well). My inclination would be to drop the most recent half or so of the rows and attempt to reindex. I'd be happy to provide someone with a copy of the dump... but it's ~70MB bzip'd. - alex -- 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] Exclude constraint problem
Jeff Davis wrote:
Are you using any floating point values, or floating-point timestamps
(that's a compile-time option)?
I'm using whatever the default compile time options are.
It's worth noting that if I delete the row, and then attempt to insert
a row with the same data on the original database... it fails.
In this case the period is the 'reported_at' column +- one minute, and
the second, conflicting, row has a 'reported_at' value of more than one
second greater than the original row.
If you dump a floating-point value, and then reload it, it may be
different than the one you started with. That can cause a problem with
either UNIQUE or EXCLUDE constraints.
The index is on an integer (vehicle), a polygon (created with ST_Expand),
and a period. When I attempt to delete+reinsert the row, I use the
binary representation of the polygon.
If you are not using floating point values, please try to make a
self-contained test case that includes data that can reproduce the
problem.
I'll try. Input is serialized, so would simply logging the queries
suffice?
Tom: the machine collecting the data is a FreeBSD 7.2p8/x64 box, the other
is a 32-bit Ubuntu 10.04 virtual machine for doing bad things with mapnik.
I certainly *hope* the BSD guys have their FP math stuff in order.
If I try to manually add the constraint on the table I get:
blockface=# alter table bus_positions add constraint "exclude_time_buffer" EXCLUDE USING gist
(vehicle WITH =, buffer_time WITH &&, bbox_dup WITH &&);
NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "exclude_time_buffer" for
table "bus_positions"
ERROR: could not create exclusion constraint "exclude_time_buffer"
DETAIL: Key (vehicle, buffer_time, bbox_dup)=(54, [2010-08-28 07:08:21-07, 2010-08-28 07:10:21-07), 010320E6100100050076374F75C8995EC07E91D09673E5424076374F75C8995EC09A42E73576E54240E8DEC325C7995EC09A42E73576E54240E8DEC325C7995EC07E91D09673E5424076374F75C8995EC07E91D09673E54240)
conflicts with key (vehicle, buffer_time, bbox_dup)=(54, [2010-08-28 07:09:34-07, 2010-08-28 07:11:34-07), 010320E6100100050016FBCBEEC9995EC09A42E73576E5424016FBCBEEC9995EC0B6F3FDD478E5424088A2409FC8995EC0B6F3FDD478E5424088A2409FC8995EC09A42E73576E5424016FBCBEEC9995EC09A42E73576E54240).
When I go back to the original data, yup, there are two rows with those
data in them.
On both machines:
blockface=# SELECT period('2010-08-28 07:08:21-07', '2010-08-28 07:10:21-07') && period('2010-08-28 07:09:34-07', '2010-08-28 07:11:34-07') AS period_intersect,
'010320E6100100050076374F75C8995EC07E91D09673E5424076374F75C8995EC09A42E73576E54240E8DEC325C7995EC09A42E73576E54240E8DEC325C7995EC07E91D09673E5424076374F75C8995EC07E91D09673E54240'::geometry &&
'010320E6100100050016FBCBEEC9995EC09A42E73576E5424016FBCBEEC9995EC0B6F3FDD478E5424088A2409FC8995EC0B6F3FDD478E5424088A2409FC8995EC09A42E73576E5424016FBCBEEC9995EC09A42E73576E54240'::geometry AS geom_intersect;
period_intersect | geom_intersect
--+
t | t
(1 row)
Another thing I stumbled over was that I could not (accidentally) insert an
empty period on a column with an exclude constraint using the && operator.
- alex
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Exclude constraint problem
I'm relatively unsure where and how to proceed. Short version: I dumped a table with an exclude constraint with pg_dump, and loaded it into another instance of postgres where it promptly failed to create the constraint. The complaint was... key conflicts with key. IOW, duplicate data. This should not happen, correct? If it *should* happen, why? If it shouldn't happen, where should I start looking? Long version: I'm playing with postgres 9.0b4, postgis 1.5.2 (SVN r5851). The btree_gist contrib module and period pgFoundry module are used as appropriate. The machine is a jail on a FreeBSD 7.2/x64 box. I'm trying to track some GPS data that's in need of sanitization. Often times reports will come in a few seconds apart with identical coordinates and identical non-zero speeds, so I'd like to reject rows with a similar position and similar time for a given vehicle. The constraint is such: "exclude_time_buffer" EXCLUDE USING gist (vehicle WITH =, buffer_time WITH &&, bbox_dup WITH &&) Buffer time is a period +- 1 minute from the report time, calculated by the client and inserted with the other clumns. bbox_dup is a geometry column containing a polygon. - alex -- 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 #5601: cannot create language plperl;
On Mon, Aug 9, 2010 at 01:30, Rene Novotny wrote: > Thank's a lot > > with Activestate 64 bit perl 5.10 it works. > > It would be very desirable for plperl supporter to to add some plperl release > notes with Postgresql > production release for users to know , with what PERL distribution it works. Yeah, with any luck the enterprisedb people are reading this thread and will fix that. (I imagine its a similar problem with the other pl's like plpython) -- 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 #5605: round(attr_name,int) works bad
On Fri, Aug 6, 2010 at 02:12, Adam Radlowski wrote: > PostgreSQL version: 8.4.4 > Operating system: Gentoo 64 bit (PSQL 8.4.4), Fedora 12 32 bit (PSQL > 8.4.1) > select round(any_numeric_field*something,2) from any_table; > I get information, that the function dos not exist. Can we get the exact *input* and the error? I cant replicate it here: select round(100::numeric*100, 2); > If I use round(numeric_field*something,int) in "insert" command, round > returns always only the value of "numeric_field". So... insert into table (field) values (round(100*2, 1)) inserts 100? I find that quite hard to believe. Again some actually sql might help here as I might just be misunderstanding. > Comment: > In some cases I can use for example: > select round(100.0*any_numeric_field)/100.0 from any_table; > ..but in programs wchich are generating SQL code and I can't modify them it > is important problem. Sounds like a problem with whatever is generating the SQL not a problem with postgres. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 12:25, Tom Lane wrote: > regression=# select string_agg(f1 order by f1, ',') from text_tbl; > ERROR: function string_agg(text) does not exist > LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > > It's not perfect (I don't think it's practical to get the HINT to > read "Put the ORDER BY at the end" ;-)) but at least it should > get people pointed in the right direction when they do this. Not to mention I think most of the confusion came from using the 1 argument version first (with an order by) and then jumping straight to the 2 arg version. -- 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 #5601: cannot create language plperl;
On Thu, Aug 5, 2010 at 00:13, Rene Novotny wrote: > > The following bug has been logged online: > PostgreSQL version: 9.0 beta 4 > Operating system: Win 7 64 bit > Description: cannot create language plperl; > Details: > > ERROR: Could not load library > c:/Program Files/PostgreSQL/9.0/lib/plperl.dll > unknown error 126 Near as I can tell that means it cant find a .dll or it is incompatible. > For a memory reasons we need to use 64 bit Windows > postgresql , you finally made one. > But when we installed Activestate 64 bit perl 5.12 perl > ( for 64 bit postgres i suppose 64 bit perl ) > it doesn't work. Please let me know , with what > perl you compiled the plperl.dll on windows ( i suppose that ActiveState ). If you are using the enterprisedb one click installer it looks like you need to grab something that has perl510.dll (maybe activestate 5.10?) Just a guess, I dont have a windows machine here (or really know anything about the windows build) I just downloaded the windows .zip file and skimmed the contents of plperl.dll. Of course you are always welcome to build your own postgresql if you need 5.12. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 17:07, Tom Lane wrote:
> Alex Hunsaker writes:
>> I dunno about anyone else but (a, ',' order by a) just looks weird.
>
> I suppose, but aren't you just focusing on the argument being constant?
Yes.
>> Or in other words, any thoughts on:
>> select string_agg(delim, expression);
>
> That looks pretty weird to me anyway, with or without use of ORDER BY.
> Nobody would think to write the delimiter first. Usually you put the
> "most important" argument first, and no one would see the delimiter
> as the most important one.
No argument about the most important arg first. I think we have a
difference of opinion on what the important argument is :-)
It might just be my perl upbringing talking, for example you join(',',
...) or split(',', ) in perl.
Either way *shrug* Im happy to leave it alone.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 16:33, Thom Brown wrote: > I was afraid that the function would be pulled completely, but from > looking at the patch, you're only removing the function with a > single-parameter signature, which is quite innocuous. So I'm "for" > now. Ahh, Now I see why you were worried about people calling you a witch :) On another note, I do wonder if we could avoid more confusion by just reordering the arguments. In-fact I bet the original argument ordering was done precisely so it would match the 1 argument version. I dunno about anyone else but (a, ',' order by a) just looks weird. Or in other words, any thoughts on: select string_agg(delim, expression); I don't want to stretch this out, but while we are making changes... -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 13:42, Tom Lane wrote: > Robert Haas writes: >> On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker wrote: >>> I think forcing an initdb might be more trouble than this wart is worth. > >> +1. I would not make this change unless we have to force an initdb >> anyway. And I really hope we don't, because I'm sort of hoping the >> next 9.0 release will be rc1. > > Hm? I don't think that an initdb here would have any impact on whether > we can call the next drop RC1 or not. We're talking about removing a > single built-in entry in pg_proc --- it's one of the safest changes we > could possibly make. Great, I was afraid people would want another beta if we forced an initdb. So a hearty +1 for fixing it and not doing another beta (pending other bugs obviously). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 13:11, Tom Lane wrote: > Alex Hunsaker writes: >> On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: >>> If we were a bit earlier in the 9.0 cycle I would suggest that this >>> confusion is a sufficient reason to drop the one-argument form of >>> string_agg. It's too late now though. > >> FWIW I think we can still change it. Isn't this type of issue part >> of what beta is for? If we were in RC that would be a different story >> :) > > Well, it'd take an initdb to get rid of it. I think forcing an initdb might be more trouble than this wart is worth. > In the past we've avoided > forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem > to be in the mode of encouraging beta testers to test pg_upgrade, so > maybe that concern isn't worth much at the moment. I have one or two 9.0-beta databases, a forced initdb would defiantly motivate me to try pg_upgrade :). To me, the question is are we planning on releasing a new beta anyway? Maybe its worth it then. If we were planning on going RC after this last beta (and I dont think we were?), I agree with Kevin, its not something worth pushing the release 9.0 for. By that I mean I assume if we force an initdb that we would want to do another beta regardless. Either way, I don't have strong feelings on this other than if we dont fix it now when will we? Maybe we will get "lucky" and someone will find an issue that we have to initdb for anyways :). -- 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] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: > If we were a bit earlier in the 9.0 cycle I would suggest that this > confusion is a sufficient reason to drop the one-argument form of > string_agg. It's too late now though. FWIW I think we can still change it. Isn't this type of issue part of what beta is for? If we were in RC that would be a different story :) -- 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 #5598: Compatibility modes
On Wed, Aug 4, 2010 at 10:42, Robert Haas wrote: > On Wed, Aug 4, 2010 at 10:53 AM, Shine wrote: >> We would like to know if there is a workaround built into postgres by >> setting some kind of COMPATIBILITY variable (similar to SQL SERVER 2005 / >> 2008)... > > Sorry, there is no such mode... However, you can add them back if you must: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html Be aware, they were removed for a reason. See http://www.postgresql.org/docs/8.4/static/release-8-3.html section E.17.2.1 for more. -- 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 #5585: SSL problems with long COPYs
On Sun, Aug 1, 2010 at 14:02, Karl Denninger wrote: > (Whether Postgress 9's internal replication will solve this for me when it > is released is something I'm not sure about - I think the answer is "no", > since if I'm reading the docs correctly Postgres 9 requires that both master > and slave be in sync via some other method before the replication is enabled Not only that, but you cant sync certain tables or databases. However, I have had great success with a simple: select pg_start_backup(); ... rsync database dir to slave, fixup postgresql.conf,recovery.conf select pg_stop_backup(); That lets you "sync" up the slave with 0 downtime. (the io from rsync can be a problem tho) -- 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 #5585: SSL problems with long COPYs
On Sun, Aug 1, 2010 at 00:08, Karl Denninger wrote: > > The following bug has been logged online: > > Bug reference: 5585 > Logged by: Karl Denninger > Email address: k...@denninger.net > PostgreSQL version: 8.4.4 > Operating system: FreeBSD 8.0 > Description: SSL problems with long COPYs > Details: > > This is a copy of a message I posted this evening on the SLONY list. > > Synopsis: With SSL ON a large table copy containing a BYTEA field fails > repeatedly a few minutes into the operation. My guess is its due to the server or client disabling ssl renegotiation, per the docs: ssl_renegotiation_limit (integer) Specifies how much data can flow over an SSL encrypted connection before renegotiation of the session will take place. Renegotiation of the session decreases the chance of doing cryptanalysis when large amounts of data are sent, but it also carries a large performance penalty. The sum of sent and received traffic is used to check the limit. If the parameter is set to 0, renegotiation is disabled. The default is 512MB. Note: SSL libraries from before November 2009 are insecure when using SSL renegotiation, due to a vulnerability in the SSL protocol. As a stop-gap fix for this vulnerability, some vendors also shipped SSL libraries incapable of doing renegotiation. If any of these libraries are in use on the client or server, SSL renegotiation should be disabled. Id try setting that to 0 in your postgresql.conf and see if it still fails. -- 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 #5578: postrgesql database crash
On Thu, Jul 29, 2010 at 20:01, yhou wrote: > Operating system: windows 2000 > My program will use postgresal database, after start my program ,I find it > hangs always, so I check the db log,and find these exception: > > 2010-07-27 14:08:22 EDT LOG: loaded library > "$libdir/plugins/plugin_debugger.dll" > 2010-07-27 14:08:22 EDT LOG: loaded library > "$libdir/plugins/plugin_debugger.dll" > 2010-07-27 14:08:22 EDT LOG: CreateProcess call failed: A blocking > operation was interrupted by a call to WSACancelBlockingCall. Huh, what happens if you take out the loading of plugin_debugger.dll (and any shared_preload_lib/local_preload_lib for that matter) ? -- 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 #5571: global hash %_SHARED is not declared as global in the new version
On Mon, Jul 26, 2010 at 03:58, mile wrote:
> To reproduce this use the following function:
>
> create or replace function perl_shared() returns void as $$
> use strict;
> elog(INFO, $_SHARED{'stuff'});
> $_SHARED{'stuff'} = '1';
> for my $k (keys %_SHARED)
> {
> elog(INFO, $k);
> }
> $$ language plperl;
Great, Thanks! The below patch fixes it for me. Basically we
declared %_SHARED inside the PostgreSQL::InServer; package when it
needed to be declared in main::. For the curious 8.4 and down don't
have this issue as the "use vars qw(%_SHARED)" is in the PERL_BOOT
define, which gets run at the interp start-up time (basically its perl
-e PERL_BOOT).
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 2,7
--- 2,8
# $PostgreSQL: pgsql/src/pl/plperl/plc_perlboot.pl,v 1.5 2010/02/16
21:39:52 adunstan Exp $
use 5.008001;
+ use vars qw(%_SHARED);
PostgreSQL::InServer::Util::bootstrap();
***
*** 9,15 package PostgreSQL::InServer;
use strict;
use warnings;
- use vars qw(%_SHARED);
sub plperl_warn {
(my $msg = shift) =~ s/\(eval \d+\) //g;
--- 10,15
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 2,7
--- 2,8
# $PostgreSQL: pgsql/src/pl/plperl/plc_perlboot.pl,v 1.5 2010/02/16 21:39:52 adunstan Exp $
use 5.008001;
+ use vars qw(%_SHARED);
PostgreSQL::InServer::Util::bootstrap();
***
*** 9,15 package PostgreSQL::InServer;
use strict;
use warnings;
- use vars qw(%_SHARED);
sub plperl_warn {
(my $msg = shift) =~ s/\(eval \d+\) //g;
--- 10,15
--
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 #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
On Fri, Jul 23, 2010 at 12:19, Daniel Grace wrote: > Please see BUG #5564 -- I accidentally submitted this one before I was > finished typing the details. Ahh, sorry for the noise. I was just trolling for bug reports that had no replys yet :-) -- 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 #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
On Fri, Jul 16, 2010 at 18:04, Daniel Grace wrote: > However, in some circumstances Postgres will fail How exactly? this is what I get: => SELECT STRING_AGG(DISTINCT t::text order by t::text) FROM foo; string_agg abc (1 row) => SELECT STRING_AGG(DISTINCT t::text order by t::text desc) FROM foo; string_agg cba (1 row) Maybe its so obvious I missed it? -- 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 #5570: global hash %_SHARED does not work in the new version
On Fri, Jul 23, 2010 at 10:17, Milen wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5570
> Logged by: Milen
> Email address: m...@avangardsolutions.com
> PostgreSQL version: v9.0beta2
> Operating system: Linux
> Description: global hash %_SHARED does not work in the new version
> Details:
>
> Hello,
> We can't find the global hash %_SHARED in the new version of plperl.c.
Huh, can we get more info? are you using plperl or plperlu? A
complete testcase would also help.
It works for me:
=> SELECT version();
version
PostgreSQL 9.0beta3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.5.0 20100610 (prerelease), 64-bit
=> create or replace function perl_shared() returns void as $$
elog(INFO, $_SHARED{'stuff'});
$_SHARED{'stuff'} = '1';
for my $k (keys %_SHARED)
{
elog(INFO, $k);
}
$$ language plperl;
=> select perl_shared();
INFO:
CONTEXT: PL/Perl function "perl_shared"
INFO: stuff
CONTEXT: PL/Perl function "perl_shared"
perl_shared
-
(1 row)
=> select perl_shared();
INFO: 1
CONTEXT: PL/Perl function "perl_shared"
INFO: stuff
CONTEXT: PL/Perl function "perl_shared"
perl_shared
-
(1 row)
--
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 #5567: will not install
On Thu, Jul 22, 2010 at 11:21, Chris Browne wrote: > 2. PostgreSQL Version 8.1 was the first version where AIX became > sufficiently supported to allow it to be tested on a regular > basis on the PostgreSQL Build Farm. > http://pgbuildfarm.org/cgi-bin/show_status.pl > > There were material changes to the build process introduced in > version 8.1 to allow substantially all parts of PostgreSQL > including contrib modules to compile and run on AIX. Yeah I'll note we dont have any coverage of AIX on 8.0 and below. Even then we only seem to have coverage of AIX 5.3. -- 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 #5567: will not install
On Thu, Jul 22, 2010 at 11:51, Denise Kanyuh wrote: > > Alex, > Yes -- I am aware that we are on an old release -- > > when you say - my hunch is > > they do not ship binaries for AIX (7.1). > > > what binaries are you taking about --- we use the same installer and files > on AIX 6.1 and it works -- it appears that there is something different in > AIX 7.1 -- either something that is not longer in the AIX 7.1 OS base (is > that what you where thinking) or someing in posgres that need to change to > accomidate this new OS? > Its this bit: >> The installation failed at step: PostGreSOL Installation. >> For additional information, please review the log files stored in the >> archive: /opt/IBM/tivoli/tip/logs.zip Ive no idea what that the above is doing, it looks like some kind of installer... Given the error you seem to get "Unknown architecture for shared libraries", it sounds like its using precompiled binaries for a different architecture or older version of AIX. Again, Ive no idea as all that is hidden by the installer. I suspect if you can download the tarball and go through the ./configure && make dance and show us any errors you hit along the way, someone might be able to help... If you dont get any errors, then maybe showing us the output of: uname -a, gcc -v, (and on the resultant binaries) file initdb, ldd initdb. An strace (or ktrace, not sure what AIX has if any) of initdb may also be helpful. It might also be interesting to see what happens when you run initdb directly, the installers log file may be omitting something interesting. Im no AIX guy, but you have not provided nearly enough info for someone to help you :-(.
Re: [BUGS] BUG #5567: will not install
On Thu, Jul 22, 2010 at 06:13, Denise Kanyuh wrote: > PostgreSQL version: 8.0.15 [ BTW the latest version of 8.0 is 8.0.25, also note 8.0 is getting EOL'ed within the next month or so, see http://www.postgresql.org/about/news.1214 ] > When trying to test our product which incorporates postgress on the newest > release of AIX we get this error -- will AIX 7.1 be supported on any release > of postgres? Skimming http://www.postgresql.org/docs/8.4/interactive/installation-platform-notes.html-- while it does not say anything specific about AIX 7.1, the biggest problems seem to be with old gcc versions. I would think if 7.1 ships with a decent version of gcc it will 'just work'. In any event you may want to give that page a look. > The installation failed at step: PostGreSOL Installation. > For additional information, please review the log files stored in the > archive: /opt/IBM/tivoli/tip/logs.zip > logs.zip include the initDB.out file which found a message: > # pwd > /opt/IBM/tivoli/tip/logs/tbsm > # more initDB.out > Error: Unknown architecture for shared libraries This looks like its coming from a 3rd party installer, my hunch is they do not ship binaries for AIX (7.1). -- 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 #5472: Postgres hard crash in ...WHERE IN (SELECT * FROM (VALUES (),...) AS t(col))
On Tue, May 25, 2010 at 11:26, Alex Hunsaker wrote: > On Tue, May 25, 2010 at 10:39, Vlad Romascanu wrote: > >> The following reproducibly crashes Postgres 8.4.3 (segfault) inside >> int84eq() on both Windows and Linux, but works just fine in 8.3.4: > > Hrm... Both work for me (8.4.3 and 8.4.4). Ahh it breaks on 32 bit Program received signal SIGSEGV, Segmentation fault. 0x08276adf in int84eq (fcinfo=0xbfad27cc) at int8.c:271 271 PG_RETURN_BOOL(val1 == val2); (gdb) bt #0 0x08276adf in int84eq (fcinfo=0xbfad27cc) at int8.c:271 #1 0x082ee0bb in FunctionCall2 (flinfo=0x8923578, arg1=2, arg2=1) at fmgr.c:1309 #2 0x0818c27e in execTuplesMatch (slot1=0x89228b8, slot2=0x8922878, numCols=1, matchColIdx=0x891e628, eqfunctions=0x8923578, evalContext=0x890c720) at execGrouping.c:97 #3 0x081a479b in ExecUnique (node=0x8922e08) at nodeUnique.c:88 #4 0x08190278 in ExecProcNode (node=0x8922e08) at execProcnode.c:439 #5 0x081a2a6b in ExecNestLoop (node=0x8922c78) at nodeNestloop.c:120 #6 0x081902f8 in ExecProcNode (node=0x8922c78) at execProcnode.c:404 #7 0x0818f368 in ExecutePlan (queryDesc=0x891ab98, direction=ForwardScanDirection, count=0) at execMain.c:1504 -- 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 #5472: Postgres hard crash in ...WHERE IN (SELECT * FROM (VALUES (),...) AS t(col))
On Tue, May 25, 2010 at 10:39, Vlad Romascanu wrote: > The following reproducibly crashes Postgres 8.4.3 (segfault) inside > int84eq() on both Windows and Linux, but works just fine in 8.3.4: Hrm... Both work for me (8.4.3 and 8.4.4). -- => SELECT col1, col2 FROM t1 WHERE col1 IN ( SELECT * FROM (VALUES (1),(2)) AS (> t2(col1) ) -> ; col1 | col2 --+-- 1 |2 2 |3 => SELECT col1, col2 FROM t1 WHERE col1::integer IN ( SELECT * FROM (VALUES (> (1),(2)) AS t2(col1) ); col1 | col2 --+-- 1 |2 2 |3 -- 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] bool: symbol name collision
On Tue, May 11, 2010 at 12:42, Robert Haas wrote: > I guess the question that comes to mind for me is how many other > things fall into this category. We define a lot of symbols like int4 > and int32 that other people could also have defined, and I don't > really want to s/^/pg/ all of them. If it's really only a question of > renaming bool I could see doing it. You mean i'd get the pleasure of 'fixing' all my 3rd party C modules? Not that that is a huge problem, we have broken calling conventions in most releases... -- 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 #5423: problems installing postgresql-8.4 deleted folder / .s.PGSQL.lock not initiate the connecti
The following bug has been logged online: Bug reference: 5423 Logged by: Alex Soto Paez Email address: alejandro.soto...@gmail.com PostgreSQL version: 8.4 Operating system: linux 9.10 Description:problems installing postgresql-8.4 deleted folder / .s.PGSQL.lock not initiate the connecti Details: problems installing postgresql-8.4 deleted folder / var / run / postgresql / .s.PGSQL.lock not initiate the connection to port 5432 when installing postgresql-8.4 is installed correctly while running the command: $ Service postgresql-8.4 status Published online defautl port 5432, but at reboot the machine no longer seems in line appears down and restart the server payable are parachute with the error: $sudo /ect/init.d/postgresql-8.4 restart The PostgreSQL server failed to start. Please check the log output: 2010-04-14 18:48:24 CDT FATAL: could not lock file >: no such file? directory [Fail] run and hand it to guide the pid file and everything goes well but when you see another restart the computer I get the same error described above. not save the folder with the Pid and does not connect to port 5432 Someone can help me In advance thank you very much. -- 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 #5422: problems installing postgresql-8.4 deleted folder / .s.PGSQL.lock not initinot initiate the connecti
The following bug has been logged online: Bug reference: 5422 Logged by: Alex Soto Paez Email address: alejandro.soto...@gmail.com PostgreSQL version: 8.4 Operating system: linux Description:problems installing postgresql-8.4 deleted folder / .s.PGSQL.lock not initinot initiate the connecti Details: The PostgreSQL server failed to start. Please check the log output: 2010-04-14 18:48:24 CDT FATAL: could not lock file >: no such file? directory [Fail] problems installing postgresql-8.4 deleted folder / var / run / postgresql / .s.PGSQL.lock not initiate the connection to port 5432 -- 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 #5362: WARNING could not determine encoding
The following bug has been logged online: Bug reference: 5362 Logged by: ALEX Email address: bav...@mail.ru PostgreSQL version: 8.3 Operating system: linux ubuntu 9.10 server Description:WARNING could not determine encoding Details: # sudo pg_createcluster -e koi8 --locale=uk_UA.koi8u --lc-ctype=uk_UA.koi8u --lc-collate=uk_UA.koi8u -d /mnt/test 8.3 test Creating new cluster (configuration: /etc/postgresql/8.3/test, data: /mnt/test)... could not determine encoding for locale "uk_UA.koi8u": codeset is "KOI8-U" WARNING: could not determine encoding for locale "uk_UA.koi8u": codeset is "KOI8-U" DETAIL: Please report this to . WARNING: could not determine encoding for locale "uk_UA.koi8u": codeset is "KOI8-U" DETAIL: Please report this to . -- 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 #5352: Bug in PL/PgSQL "SELECT .. INTO" statement parser
On Mon, Mar 1, 2010 at 02:22, Oleg wrote: > CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS > $body$ > DECLARE > row_test1 test1%rowtype; > row_test2 test2%rowtype; > BEGIN > SELECT test1, chunk_id > FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id) > LIMIT 1 > INTO row_test1, row_test2; *shrug* it works if you put the INTO after SELECT. From the manual: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-SELECT-INTO : The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just : after the list of select_expressions in a SELECT command, or at the end of the command for other command types. : It is recommended that you follow this convention in case the PL/pgSQL parser becomes stricter in future versions. -- 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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Thu, Feb 25, 2010 at 14:06, David E. Wheeler wrote: > On Feb 25, 2010, at 12:58 PM, Tim Bunce wrote: >> There is one fairly good answer: >> >> Use a perl that's compiled to support multiplicity but not threads. > That solves the problem with recursion or with $a and $b or both? Yes ATM because we only kick in the extra security if you are on threads... Its a bit of a kludge in Safe. I know Tim wants to rectify that... -- 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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Thu, Feb 25, 2010 at 13:03, Alex Hunsaker wrote: > On Thu, Feb 25, 2010 at 12:59, Greg Sabino Mullane wrote: >> Just don't break anything in 9.0 that relies on plperl please. :) To that >> end, let me know when HEAD has something somewhat stable, and I'll >> run some tests against it (e.g. Bucardo, which uses lots of plperl) > > Defiantly, the goal is to not break anything :). Err oops, as David Fetter pointed out... I *think* i meant to say definitely. -- 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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Thu, Feb 25, 2010 at 12:59, Greg Sabino Mullane wrote: > Just don't break anything in 9.0 that relies on plperl please. :) To that > end, let me know when HEAD has something somewhat stable, and I'll > run some tests against it (e.g. Bucardo, which uses lots of plperl) Defiantly, the goal is to not break anything :). -- 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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Thu, Feb 25, 2010 at 12:31, David E. Wheeler
wrote:
> I think Tom meant, what sorts of changes to PostgreSQL do you think might
> solve the problem?
Here is what Tim said:
>Doesn't seem too icky. Basically plperl would need to save the values of
>PL_defstash, PL_incgv and PL_op_mask when a plperl interpreter is
>initialized. And then local()'ly restore them in the plperl entry points.
>Should only be a few lines of code - in theory :)
Basically when we go to compile a new plperl sub we would 'break' out
of the safe, compile the sub (which it itself would go back into the
safe) and then because we local()ly set/restore we would be reset to
the same context when we returned.
Not only is there some prior art for this method (Safe::Hole). After
playing with it a bit last night I agree it should be fairly small and
simple. Im a bit worried there might be some corner cases. All the
easy ones I see cant happen with plperl only with arbitrary 3rd party
modules. Things we might need to do in addition would be: ignoring
END blocks, saving PL_curstash, Invalidating the ISA and method
caches, saving/restoring INC... And that would only be because im
worried there might be some strange issues with the new
plperl.plperl_init. Its hand waving at this point.
Another way I played with last night is calling
Opcode::_save_call_sv("main", Opcode::full_ops, sub_to_compile)
directly instead of perl_call_sv() to compile the sub (or in
pl/plperl.c plperl_create_sub replace the call to perl_call_sv with
Opcode::_safe_call_sv). It should be even simpler and safer. We
would still need to save and local() a few things... But it should
work.
Anyway, im hoping to look at this more tonight... My lunch is over :)
--
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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Thu, Feb 25, 2010 at 12:20, Tom Lane wrote:
> Alex Hunsaker writes:
>> 3) patch postgres to fix the recursive issue (What I'm leaning towards)
>> [ fixes both issues ]
>
> How exactly would you propose doing that?
Well that's the thing, probably by what I described below that. Namely
get something working for 9.1 and after we know its good and solid see
if we can back patch it. Unfeasible? If its really really simple and
straight forward maybe we can find a -commiter willing to commit it
sooner. But I'm dubious. I think the feeling between me and Tim is
patching postgres is a last resort... Maybe if its to fix both sort
{} and this it might be worth it. (That's at least how I parsed what
you said :) ). Ill see if I can figure something out via straight
Safe tonight.
--
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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Thu, Feb 25, 2010 at 11:04, David E. Wheeler
wrote:
> There seem to be no good answers here.
Yeah, Here is the thing I don't think we can fix 'safe' or even patch
perl to get recursive calls to work. Maybe Tim sees a way? We can
work around it in 9.0 with plperl.on_init. But breaking the back
branches makes it a non-starter.
Basically the problem as I see it is:
1) we call a plperl function which makes perl safe by denying certain
perl operations such as eval.
2) now that the interpreter is in that context, we try to compile a
new sub (using eval) and it fails.
I just don't see a way to make it work without making Safe useless.
For instance we could import the function that compiles the perl sub
into the safe. But then anyone could call it and eval in random code.
Maybe Tim has something up his sleeve? (That does not require plperl.on_init?)
Here are the options I see:
1) revert safe to pre 2.2x behavior breaking sort {}, but fixing this
issue. There would be a new function or a way to opt-in to the new
2.2x secure behavior (which would also fix sort, but this issue would
remain). (Tim's favored ATM)
[ breaks sort ]
2) patch perl to fix the sort {} issue (last I looked it would be
quite invasive and I think I would have a hard time getting it into
blead let alone 5.10.2 and 5.8.10), this issue would still be broken
[ still need to revert safe ]
3) patch postgres to fix the recursive issue (What I'm leaning towards)
[ fixes both issues ]
4) patch postgres to fix the *a, *b issue
[ still need to revert safe ]
5) make safe default import *a, *b to fix sort {} (rejected in the past)
[ still need to revert safe]
6) we might be able to do something in Safe to work around this... I
have an idea or two but I don't think they will pan out. (Basically it
used to work because we were only in the Safe context for that sub {},
we might be able to restore that behavior somehow. that would fix
both issues... I don't have any bright ideas at the moment)
Ill also point out Tim has more or less pointed out all these
solutions up-thread as well.
Anyone see any other options? There are hybrid solutions here. For
instance if we did #1, we could also prepare a patch for 9.1 that will
'opt-in' for the more secure closures and the sort {} fixes. The
patch would in essence be #3. After that's been field tested for a
while we could see about back patching it.
If wishes were horses we'd all be eating steak.
--
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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Wed, Feb 24, 2010 at 22:01, Alex Hunsaker wrote:
> Ok I can get behind this. I did some testing and we could probably
> even store less than than that if we could do the equivalent of:
> Safe->share('::mksafefunc');
> pl_perl_createsub()
> Safe->unshare('::mksafefunc');
On 2nd thought this basically requires your fix anyway. To make it so
you can share something in safe from within safe means we will need to
enable more opcodes there... so it would end up being the same
solution.
--
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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Tue, Feb 23, 2010 at 15:54, Tim Bunce wrote:
> Doesn't seem too icky. Basically plperl would need to save the values of
> PL_defstash, PL_incgv and PL_op_mask when a plperl interpreter is
> initialized. And then local()'ly restore them in the plperl entry points.
> Should only be a few lines of code - in theory :)
Ok I can get behind this. I did some testing and we could probably
even store less than than that if we could do the equivalent of:
Safe->share('::mksafefunc');
pl_perl_createsub()
Safe->unshare('::mksafefunc');
See my quick test case:
my $s = Safe->new();
$s->permit(qw(print));
our $obj = sub { return eval 'sub { print "b\n";}' };
$obj->()->();
$s->share(qw($obj));
$s->reval('$obj->()->();');
print $@ . "\n";
---
b
b
(BTW the above fails with the helpful "Undefined subroutine &main::
called at (eval 6) line 1." without the ->permt(qw(print))")
So we might not even have to store anything if we can make it behave
as above. However I think it will be cleaner to me to locally restore
them as your originally suggested.
BTW sorry for my scatter braininess. I keep flip flopping between
revet Safe or patch postgres. ATM it seems if the patch is simple we
can get it back patched and into 9.0. So my vote is lets try that, if
its to hard then lets see about reverting Safe. Sound Ok?
--
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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Wed, Feb 24, 2010 at 20:37, Alex Hunsaker wrote:
> On Wed, Feb 24, 2010 at 20:19, Tom Lane wrote:
>> Seems entirely unacceptable.
> I think we will see if we can get this fixed on the Safe/perl side then.
BTW the trade off here is we revert back to sort { $a <=> $b } not
working. That is if you could call it a trade off... The level of
breaking is not really comparable :)
--
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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Wed, Feb 24, 2010 at 20:19, Tom Lane wrote: >What you're saying, IIUC, is > that if function A calls function B via a SPI command, and B wasn't > executed previously in the current session, it would fail? Seems > entirely unacceptable. Yep, thats right :(. Thanks, thats exactly the kind of feedback I wanted to get. I think we will see if we can get this fixed on the Safe/perl side then. Tim, I think unless the Safe::Hole stuff is really straight forward it seems like (as we previously agreed) the best change is to revert safe to its old behavior for now. -- 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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Wed, Feb 24, 2010 at 19:17, Alex Hunsaker wrote:
> On Tue, Feb 23, 2010 at 15:23, Tim Bunce wrote:
>
>> I believe (but haven't yet confirmed) that the problem here is recursion.
>> This affects all versions of PostgreSQL.
> Ill keep digging.
Ok I understand now, basically the problem is (as Tim also described elsewhere):
postgres->plperl_call_perl_func->SPI->postgres->plperl_create_sub
On that last call to plperl_create_sub we are still executing under
Safe (as its the same interpreter). And so it fails when it tries to
compile a new sub.
ISTM the easiest and safest fix would be to not allow recursive plperl
creations. You could still call plperl functions within functions,
just not if they are not defined. This limitation really blows so im
hoping someone else has a better idea? Alternately we could also
break out of the safe, compile the sub and then go back to it as Tim
suggested up-thread. I think this could work as long as its not to
nasty (which Tim does not seem to think it would be).
Thoughts? Better Ideas?
[ patch against 8.3/8.4 ]
*** a/src/pl/plperl/plperl.c
--- b/src/pl/plperl/plperl.c
***
*** 126,131 static HTAB *plperl_proc_hash = NULL;
--- 126,132
static HTAB *plperl_query_hash = NULL;
static bool plperl_use_strict = false;
+ static bool plperl_executing = false;
/* this is saved and restored by plperl_call_handler */
static plperl_call_data *current_call_data = NULL;
***
*** 1117,1125 plperl_call_perl_func(plperl_proc_desc *desc,
FunctionCallInfo fcinfo)
--- 1118,1132
}
PUTBACK;
+ if (desc->lanpltrusted)
+ plperl_executing = true;
+
/* Do NOT use G_KEEPERR here */
count = perl_call_sv(desc->reference, G_SCALAR | G_EVAL);
+ if (desc->lanpltrusted)
+ plperl_executing = false;
+
SPAGAIN;
if (count != 1)
***
*** 1697,1702 compile_plperl_function(Oid fn_oid, bool is_trigger)
--- 1704,1721
check_interp(prodesc->lanpltrusted);
+ /
+* Dont let us recursively create a plperl function from a
plperl function
+* as plperl_create_sub gets called we are running under Safe
and fails.
+* TODO: We could break out of the safe via Safe::HOLE or some
such.
+/
+ if (prodesc->lanpltrusted && plperl_executing)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+errmsg("could not create plperl function
\"%s\"", prodesc->proname),
+errdetail("plperl functions can not
recursivley define other
plperl functions"),
+errhint("try calling the function first")));
+
prodesc->reference = plperl_create_sub(prodesc->proname,
proc_source,
prodesc->lanpltrusted);
--
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] New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)
On Tue, Feb 23, 2010 at 15:23, Tim Bunce wrote:
> I believe (but haven't yet confirmed) that the problem here is recursion.
> This affects all versions of PostgreSQL.
Hrm... This seems to work for me in HEAD. It certainly breaks in 8.3.
Am I missing something?
$ bin/psql postgres
psql (9.0devel)
Type "help" for help.
postgres=# CREATE OR REPLACE FUNCTION foo( integer) RETURNS SETOF INT
LANGUAGE plperl AS $$ $$;
CREATE FUNCTION
postgres=# \q
$ bin/psql postgres
psql (9.0devel)
Type "help" for help.
postgres=# 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) ) {
}
$$;
CREATE FUNCTION
postgres=# SELECT try();
try
-
(1 row)
Seems like assuming I did the above correctly we just have a bug in
the older branches where the "SELECT id FROM foo(0)..." part is
getting compiled/executed in the wrong perl context. In-fact I would
not be surprised at all if there are other dragons lurking when plperl
calls something that tries to compile/call a plperl function. Safe
>2.20 or older.
Ill keep digging.
--
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 00:50, Alex Hunsaker wrote:
> On Mon, Feb 22, 2010 at 14:31, Tom Lane 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.
*** config/perl.m4
--- config/perl.m4
***
*** 10,16 if test -z "$PERL"; then
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n ['s/This is perl, v[a-z ]*//p'] | sed ['s/ .*//']`
AC_MSG_NOTICE([using perl $pgac_perl_version])
if echo "$pgac_perl_version" | sed ['s/[.a-z_]/ /g'] | \
$AWK '{ if ([$]1 = 5 && [$]2 >= 8) exit 1; else exit 0;}'
--- 10,16
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n ['s/This is perl.*v[a-z ]*\([0-9]\.[\.0-9]*\).*$/\1/p']`
AC_MSG_NOTICE([using perl $pgac_perl_version])
if echo "$pgac_perl_version" | sed ['s/[.a-z_]/ /g'] | \
$AWK '{ if ([$]1 = 5 && [$]2 >= 8) exit 1; else exit 0;}'
*** configure
--- configure
***
*** 6867,6873 fi
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n 's/This is perl, v[a-z ]*//p' | sed 's/ .*//'`
{ $as_echo "$as_me:$LINENO: using perl $pgac_perl_version" >&5
$as_echo "$as_me: using perl $pgac_perl_version" >&6;}
if echo "$pgac_perl_version" | sed 's/[.a-z_]/ /g' | \
--- 6867,6873
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n 's/This is perl.*v[a-z ]*\([0-9]\.[\.0-9]*\).*$/\1/p'`
{ $as_echo "$as_me:$LINENO: using perl $pgac_perl_version" >&5
$as_echo "$as_me: using perl $pgac_perl_version" >&6;}
if echo "$pgac_perl_version" | sed 's/[.a-z_]/ /g' | \
--
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 14:31, Tom Lane 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:
$ echo "This is perl, version 4.0" | sed -n 's/This is perl.*v[a-z
]*\([0-9]\.[\.0-9]*\).*$/\1/p'
4.0
$ echo "This is perl, v5.8.0" | sed -n 's/This is perl.*v[a-z
]*\([0-9]\.[\.0-9]*\).*$/\1/p'
5.8.0
$ echo "This is perl, v5.10.1" | sed -n 's/This is perl.*v[a-z
]*\([0-9]\.[\.0-9]*\).*$/\1/p'
5.10.1
$ echo "This is perl 5, version 11, subversion 4
(v5.11.4-114-ga4cc961*) built for" | sed -n 's/This is perl.*v[a-z
]*\([0-9]\.[\.0-9]*\).*$/\1/p'
5.11.4
---
*** config/perl.m4
--- config/perl.m4
***
*** 10,16 if test -z "$PERL"; then
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n ['s/This is perl,
v[a-z ]*//p'] | sed ['s/ .*//']`
AC_MSG_NOTICE([using perl $pgac_perl_version])
if echo "$pgac_perl_version" | sed ['s/[.a-z_]/ /g'] | \
$AWK '{ if ([$]1 = 5 && [$]2 >= 8) exit 1; else exit 0;}'
--- 10,16
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n ['s/This is
perl.*v[a-z ]*\([0-9]\.[\.0-9]*\).*$/\1/p']`
AC_MSG_NOTICE([using perl $pgac_perl_version])
if echo "$pgac_perl_version" | sed ['s/[.a-z_]/ /g'] | \
$AWK '{ if ([$]1 = 5 && [$]2 >= 8) exit 1; else exit 0;}'
*** configure
--- configure
***
*** 6867,6873 fi
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n 's/This is perl,
v[a-z ]*//p' | sed 's/ .*//'`
{ $as_echo "$as_me:$LINENO: using perl $pgac_perl_version" >&5
$as_echo "$as_me: using perl $pgac_perl_version" >&6;}
if echo "$pgac_perl_version" | sed 's/[.a-z_]/ /g' | \
--- 6867,6873
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n 's/This is
perl.*v[a-z ]*\([0-9]\.[\.0-9]*\).*$/\1/p'`
{ $as_echo "$as_me:$LINENO: using perl $pgac_perl_version" >&5
$as_echo "$as_me: using perl $pgac_perl_version" >&6;}
if echo "$pgac_perl_version" | sed 's/[.a-z_]/ /g' | \
--
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 14:31, Tom Lane wrote: > Alex Hunsaker 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. Cant argue with that. However, I dont think my sed foo is up to the challenge ATM. :) BTW this is the perl commit that changed it: commit ded326e4b6fad7e2479796691d0c27b89d2fe080 Author: David Golden Date: Thu Nov 12 10:46:30 2009 -0500 Change perl -v version format New format: This is perl 5, version 11, subversion 1 (v5.11.1) ... The rationale for this change is that the Perl 5 interpreter will never increment PERL_REVISION from 5 to 6, so we want people to start focusing on the PERL_VERSION number as most significant and PERL_SUBVERSION as equivalent to a "release number". In other words, "perl 5" is a language, this is the 11th version of it, and the second release of that version (counting from zero). Among other things, this makes the output of -v and -V more consistent. The old v-string style is included for familiarity and usage in code. For builds from git, it will include the same extended format as it did before, e.g. "(v5.11.1-176-gaf24cc9*)" -- 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 14:17, Alex Hunsaker wrote: > On Mon, Feb 22, 2010 at 13:07, Tom Lane wrote: >> Alex Hunsaker writes: >>> ! perl_version_error=`$PERL -e 'use 5.00801;' 2>&1` ... > How about something like the below? Find attached one that modifies config/perl.m4 as well :) perl_configure_check.patch.gz Description: GNU Zip compressed data -- 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 13:07, Tom Lane wrote:
> Alex Hunsaker writes:
>> ! perl_version_error=`$PERL -e 'use 5.00801;' 2>&1`
>
> This is not a path towards an acceptable solution, as it effectively
> assumes what we are setting out to prove, namely that we have found
> a reasonably modern version of perl. Try it in perl 4...
Well I would if I had it sitting around =). I did try to compile
one... but chickened out as soon as I hit an error:
$ make
`sh cflags perly.o` perly.c
CCCMD = cc -c -O
In file included from perly.y:38:
perl.h:279: error: conflicting types for ‘sys_errlist’
/usr/include/bits/sys_errlist.h:28: note: previous declaration of
‘sys_errlist’ was here
In file included from perl.h:540,
from perly.y:38:
arg.h:685:1: warning: "AF_LOCAL" redefined
In file included from /usr/include/sys/socket.h:40,
from /usr/include/netinet/in.h:25,
from perl.h:224,
from perly.y:38:
/usr/include/bits/socket.h:116:1: warning: this is the location of the
previous definition
How about something like the below? Basically If we find "This is
perl v4" we bail right then. Otherwise we use the version check I
proposed up-thread? I may have inadvertently used some
bash/gnu-isms... sorry about that.
*** a/configure
--- b/configure
***
*** 6867,6884 fi
fi
if test "$PERL"; then
! pgac_perl_version=`$PERL -v 2>/dev/null | sed -n 's/This is perl,
v[a-z ]*//p' | sed 's/ .*//'`
! { $as_echo "$as_me:$LINENO: using perl $pgac_perl_version" >&5
! $as_echo "$as_me: using perl $pgac_perl_version" >&6;}
! if echo "$pgac_perl_version" | sed 's/[.a-z_]/ /g' | \
! $AWK '{ if ($1 = 5 && $2 >= 8) exit 1; else exit 0;}'
! then
{ $as_echo "$as_me:$LINENO: WARNING:
*** The installed version of Perl, $PERL, is too old to use with PostgreSQL.
! *** Perl version 5.8 or later is required, but this is
$pgac_perl_version." >&5
$as_echo "$as_me: WARNING:
*** The installed version of Perl, $PERL, is too old to use with PostgreSQL.
! *** Perl version 5.8 or later is required, but this is
$pgac_perl_version." >&2;}
PERL=""
fi
fi
--- 6867,6885
fi
if test "$PERL"; then
! perl_version_error=""
! if $PERL -v 2>/dev/null | grep -q 'This is perl, v4' 2>/dev/null; then
! perl_version_error="Perl version 5.8 or later is required, but
this is perl v4"
! else
! perl_version_error=`$PERL -e 'use 5.00801;' 2>&1`
! fi
! if test -n "$perl_version_error"; then
{ $as_echo "$as_me:$LINENO: WARNING:
*** The installed version of Perl, $PERL, is too old to use with PostgreSQL.
! *** $perl_version_error." >&5
$as_echo "$as_me: WARNING:
*** The installed version of Perl, $PERL, is too old to use with PostgreSQL.
! *** $perl_version_error." >&2;}
PERL=""
fi
fi
Thoughts?
--
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 12:44, Alex Hunsaker wrote: > On Mon, Feb 22, 2010 at 10:57, Jonathan wrote: >> configure: using perl >> configure: WARNING: >> *** The installed version of Perl, /home/leto/bin/perl, is too old to use >> with PostgreSQL. >> *** Perl version 5.8 or later is required, but this is . > > Yeah find the below which attempts to clean this up a bit. For the curious this what it looks like after (I of course bumped the check to the non-existent perl 5.11.10 version): configure: WARNING: *** The installed version of Perl, /usr/bin/perl, is too old to use with PostgreSQL. *** Perl v5.11.10 required--this is only v5.10.1, stopped at -e line 1. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
