[BUGS] SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
L.S. After an upgrade to v8.4 one of our clients is experiencing heaps of problems, they get errors like "ERROR: SPI_connect failed: SPI_ERROR_CONNECT". I refer to this earlier post, it looks a lot like it: http://archives.postgresql.org/pgsql-general/2009-07/msg00388.php We too had no problem when running v8.3 Tom, you stated: Really? Could we see a self-contained example? We're working on that, but its very difficult. We have a way to reproduce it in our application, but this works only in the production environment, not in our development situation (the more users, the more faster the problem appears). Some users are able to work 15 minutes without problems and then they get errors. Restarting the app and thus the connection silences it again for a while, etc. Though a reproducable 'psql' testcase is not yet available, I do have a bit of additional information that might serve as a clue to some: * when they happen, the code triggering the errors seems to be fairly limited * a small part are all triggered in before triggers and all are in a custom function session_userid() * the bulk part are triggered in both after as well as deferred triggers that in turn call a couple of custom functions _all_ of these custom function have the following structure in common: CREATE OR REPLACE FUNCTION x() RETURNS int LANGUAGE 'plpgsql' IMMUTABLE STRICT SECURITY INVOKER AS 'DECLARE result INT := 0; BEGIN BEGIN SELECT id INTO STRICT result FROM x WHERE x; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION ''Unknown (%)...!!'', x WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION ''More than one found (%)...!!'', x; END; RETURN result; END;'; => mind the fact that it is marked 'immutable' => mind the fact that due to the exception usage, it contains an inner begin/end block Though the frequency / localisation of these errors seems too high to be a coincidence, I do have to point out that these are not the only functions with the same structure. I have a whole set of _status() and _type() functions that look exactly the same, but who never come up in an error. However, this might simply be due to the usage-pattern of my application. Any clues as to how to gather additional information that might bring us closer to a solution is appreciated also. I'd have no problem with applying some patch as long as it's safe enough for a production environment ;) -- Best, Frank. -- 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] SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Frank van Vugt writes: > Any clues as to how to gather additional information that might bring us > closer to a solution is appreciated also. A stack trace from the point of the error would probably tell us a great deal. Maybe you could attach to a backend with gdb, set a breakpoint at the failure return in SPI_connect(), and then provoke the error manually? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Hi Tom, Op maandag 13 juli 2009, schreef Tom Lane: > Frank van Vugt writes: > > Any clues as to how to gather additional information that might bring us > > closer to a solution is appreciated also. > > A stack trace from the point of the error would probably tell us a great > deal. Maybe you could attach to a backend with gdb, set a breakpoint > at the failure return in SPI_connect(), and then provoke the error > manually? Just fyi, a breakpoint at SPI_connect with condition _SPI_curid != _SPI_connected produced the following backtrace: Program received signal SIGUSR2, User defined signal 2. 0x2b539af2b5f5 in recv () from /lib64/libc.so.6 (gdb) bt #0 0x2b539af2b5f5 in recv () from /lib64/libc.so.6 #1 0x0054d692 in secure_read () #2 0x00552c74 in pq_recvbuf () #3 0x00553077 in pq_getbyte () #4 0x005ce5f6 in PostgresMain () #5 0x005a50fb in ServerLoop () #6 0x005a5c2a in PostmasterMain () #7 0x0055498e in main () However, after continuing this did NOT give the SPI_connect error message, so this probably is about something else completely? We cannot reproduce the error anymore due to end of working hours, will try again tomorrow morning (localtime). More to follow. -- Best, Frank. -- 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] SPI_ERROR_CONNECT within pl/pgsql, PG 8.4
Frank van Vugt writes: > Just fyi, a breakpoint at SPI_connect with condition > _SPI_curid != _SPI_connected Right, that's what to look for. > produced the following backtrace: > Program received signal SIGUSR2, User defined signal 2. > 0x2b539af2b5f5 in recv () from /lib64/libc.so.6 > (gdb) bt > #0 0x2b539af2b5f5 in recv () from /lib64/libc.so.6 > #1 0x0054d692 in secure_read () > #2 0x00552c74 in pq_recvbuf () > #3 0x00553077 in pq_getbyte () > #4 0x005ce5f6 in PostgresMain () > #5 0x005a50fb in ServerLoop () > #6 0x005a5c2a in PostmasterMain () > #7 0x0055498e in main () This is a normal interbackend communication signal. You need to configure gdb to ignore SIGUSR2 (ie, pass it on and not stop execution). Probably SIGUSR1 too. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4914: uuid_generate_v4 not present in eithersource or yum/rpm
On Fri, Jul 10, 2009 at 12:31:05PM -0400, Alvaro Herrera wrote: - Dan Boeriu wrote: - > Could you please tell me how you installed it? What exact version did you download? Was it source or yum/rpm? - > I tried both with source compiling with the flags for oops-uuid and still didn't get the function uuid_generate_v4. - > - > If you installed from source please send me the configure flags you used. - > If you installed from yum/rpm please tell me what version/platform. - - I compiled from source. Did you enter the contrib/uuid-ossp directory - and ran make install in there? - - $ pg_config --configure - '--enable-debug' '--enable-depend' '--enable-cassert' '--enable-nls' '--cache-file=/home/alvherre/tmp/pgconfig.84_rel.cache' '--enable-thread-safety' '--with-python' '--with-perl' '--with-tcl' '--with-openssl' '--with-libxml' '--with-ossp-uuid' '--prefix=/pgsql/install/84_rel' '--with-pgport=55516' - Just an addition to this topic. We're using SLES 11, and uuid-ossp isn't delivered in the postgresql-contrib package, we opened a case with Novell and this was their reply: --- With regards to Service Request # 10497898141: Thank you for the response. Since the PostgreSQL project determined uuid off by default, the SLES RPMs have been left at the default. Recompiling with the settings to turn on uuid is how to enable uuid, but recompiling in no something supported. I will close this Service Request, since enabling uuid and recompiling are not supported. --- Dave -- 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 #4914: uuid_generate_v4 not present in eithersource or yum/rpm
David Kerr wrote: > --- > With regards to Service Request # 10497898141: > > Thank you for the response. Since the PostgreSQL project determined > uuid off by default, the SLES RPMs have been left at the default. > Recompiling with the settings to turn on uuid is how to enable uuid, > but recompiling in no something supported. That seems pretty silly. Surely if they distribute the OSSP uuid library they could enable the feature on their packages. Of course, you can install the contrib module yourself without recompiling the whole of Postgres, to avoid losing support. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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 #4890: Allow insert character has no equivalent in "LATIN2"
(Please reply to the list, not just to me)
I'm not sure about this so far. Re the specific issue you mention of
conversion between cp1250 and latin-2 (ISO-8859-2) the Unicode tables
at:
http://unicode.org/Public/MAPPINGS/ISO8859/8859-2.TXT
appear to agree - there's no PER MILLE in ISO-8859-2.
With a UTF-8 database, Pg correctly doesn't accept PER MILLE as a valid
ISO-8859-2 char:
-- Connecting with unicode (utf-8) client
CREATE TABLE test (x);
INSERT INTO test(x) VALUES ('‰');
SET client_encoding='iso-8859-2';
SELECT * from test;
ERROR: character 0xe280b0 of encoding "UTF8" has no equivalent in
"LATIN2"
If the encoding is set to WIN1250 Pg outputs the appropriate byte. So
it's doing the right thing in each individual case where a UTF-8 DB is
concerned.
Your problem, though, is that if you connect to a LATIN2 database with a
WIN1250 client and INSERT a string containing the per-mille glyph, Pg
accepts it and it should not. If it does, indeed, accept it, then I
agree that's a bug.
I haven't tested with a LATIN2 database as I'd have to re-initdb and the
machine I'm working on has semi-useful databases on it. What you're
saying makes sense, though, presuming your client really is sending
win1250 per-mille (byte 0x89).
I'd still like to know how you're setting your client encoding. You
can't just run "SET client_encoding='win1250'" - you must tell the
client program, or the terminal it runs in, to use the appropriate
encoding as well. Otherwise when you paste the per-mille character
you'll see the right glyph, but the CLIENT will interpret that as the
character in the encoding you specified.
So, if you're using a utf-8 terminal, that means that the terminal will
send 0xe2 0x80 0xb0 for per-mille, which when interpreted as win1250
becomes ‰ , so that's what the server thinks you sent it.
In that case, though, you'd find that the euro symbol, which isn't
defined in latin-2, will cause an error:
ERROR: character 0xe282ac of encoding "UTF8" has no equivalent in
"LATIN2"
--
Craig Ringer
--
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 #4890: Allow insert character has no equivalent in "LATIN2"
Craig Ringer writes: > Your problem, though, is that if you connect to a LATIN2 database with a > WIN1250 client and INSERT a string containing the per-mille glyph, Pg > accepts it and it should not. If it does, indeed, accept it, then I > agree that's a bug. The table in win12502mic() in latin2_and_win1250.c just translates 0x89 to 0x89, which is wrong according to your comments (it should have a zero entry for characters with no LATIN2 equivalent). The table looks to have quite a few one-to-one conversions, so I am wondering whether this is the only bug in it. Anyone want to go through the rest of it? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4918: Weird input syntax for intervals
The following bug has been logged online: Bug reference: 4918 Logged by: Frank Spies Email address: frank.sp...@biotronik.com PostgreSQL version: 8.4 Operating system: Linux Description:Weird input syntax for intervals Details: It feels totally weird that the two queries 1) select interval '13 month' 2) select interval '13' month do not have the same result: result for 1) is "1 year 1 mon", result for 2 is "1 mon". Is this correct behaviour? -- 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 #4918: Weird input syntax for intervals
"Frank Spies" writes: > It feels totally weird that the two queries > 1) select interval '13 month' > 2) select interval '13' month > do not have the same result: result for 1) is "1 year 1 mon", result for 2 > is "1 mon". Is this correct behaviour? $ psql psql (8.4.0) Type "help" for help. regression=# select interval '13 month'; interval -- 1 year 1 mon (1 row) regression=# select interval '13' month; interval -- 1 year 1 mon (1 row) You sure you are on 8.4.0? We were fooling with the interval input code quite late in 8.4 beta. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4914: uuid_generate_v4 not present in eithersource or yum/rpm
On Monday 13 July 2009 19:17:49 David Kerr wrote: > We're using SLES 11, and uuid-ossp isn't delivered in the > postgresql-contrib package, we opened a case with Novell and this was their > reply: > [some nonsense] I'm sorry to say that SUSE just isn't a good source if you want to do serious PostgreSQL deployment. They ship PostgreSQL because it's there, but they, as an organization, just don't care enough to consistently provide the packages, updates, and support that you'd want for serious use. So if you can't use a different operating system, either accept what you get and hope you get away with it, or prepare to spend some resources to do your own maintenance. -- 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 #4914: uuid_generate_v4 not present in eithersource or yum/rpm
On Tue, Jul 14, 2009 at 01:18:55AM +0300, Peter Eisentraut wrote: - On Monday 13 July 2009 19:17:49 David Kerr wrote: - > We're using SLES 11, and uuid-ossp isn't delivered in the - > postgresql-contrib package, we opened a case with Novell and this was their - > reply: - > [some nonsense] - - I'm sorry to say that SUSE just isn't a good source if you want to do serious - PostgreSQL deployment. They ship PostgreSQL because it's there, but they, as - an organization, just don't care enough to consistently provide the packages, - updates, and support that you'd want for serious use. So if you can't use a - different operating system, either accept what you get and hope you get away - with it, or prepare to spend some resources to do your own maintenance. - I'm working on my management to allow me to roll my own PG and get a 3rd party support. Responses from Novell (like what I sent to the list) only help to further that cause. Dave -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
