[BUGS] BUG #6018: ctrl +C cause data inconsistent for sync standby
The following bug has been logged online: Bug reference: 6018 Logged by: lxzou Email address: zoulx1...@163.com PostgreSQL version: 9.1beta1 Operating system: Linux Description:ctrl +C cause data inconsistent for sync standby Details: Dear Sir: I am interested in standby function of PG, so i test the new feature about sync standby in PG9.1. I have a question, i install only one primary postgres and no standby server. When i execute an insert statement in primary, this statement was blocked. But when I press Ctrl + C in psql, the statement was commited. In my view, the walsender can't send the xlog to standby server, the statement should be rollback, but not commit; because this will cause the data between primary and standby inconsistent. Thank you for your time. Look forward to your reply. -- 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: Bug with STABLE function using the wrong snapshot (probably during planning)
Hi Matthijs, Thanks for the report. On Tue, Mar 22, 2011 at 04:31:47PM +0100, Matthijs Bomhoff wrote: > The bit of SQL below does not behave the way it should on postgres 8.4.4 > (tested by me) and 9.0.3 (verified independently on #postgresql). On git master, too. > The third statement in the quux() function calls the a_bar() function that > should find a single row in the 'bar' table and return its value. This single > row is INSERTed into the 'bar' table on the previous line. However, the > SELECT statement in the a_bar() function throws the following error: "ERROR: > query returned no rows". It thus appears not to see the INSERTed value in the > 'bar' table. (The expected behavior is that the a_bar() function returns the > value 500 instead of throwing an error.) > > Removing the STABLE attribute from a_bar() works around the problem, as does > moving the "INSERT INTO bar ..." statement out of the quux() function and > executing it before calling the quux() function itself. > > Some initial debugging by RhodiumToad on #postgresql led to the following > observation: The error occurs only when the "SELECT ... WHERE i = a_bar();" > is being planned, not when it is being executed, with the snapshot being used > to plan the query apparently being too old to see the result of the preceding > insert. Quite so. All the core procedural languages have _SPI_execute_plan() manage CommandCounterIncrement() and PushActiveSnapshot()/PopActiveSnapshot() for the SQL statements they execute. Many statements use a snapshot during planning, but _SPI_prepare_plan() never pushes one. Therefore, in this example, planning uses the snapshot pushed in PortalRunSelect(). Expressions evaluated at plan time miss any changes from earlier in the volatile function. This is fine when they merely give "wrong" answers: we might get an inferior selectivity estimate. In your example, a function that actually needs to see the latest data to avoid throwing an error, we do have a problem. The simplest fix I can see is to have _SPI_prepare_plan() push/pop a new snapshot when analyze_requires_snapshot() returns true on the raw parse tree. That strategy can break down in the other direction if the caller is STABLE; consider this example: CREATE TABLE foo(i INTEGER); CREATE TABLE bar(i INTEGER); INSERT INTO foo(i) SELECT s.a FROM generate_series(1,2) s(a); INSERT INTO bar(i) VALUES(500); BEGIN; CREATE OR REPLACE FUNCTION a_bar() RETURNS INTEGER AS $EOF$ DECLARE result INTEGER; BEGIN EXECUTE 'SELECT i FROM bar' INTO STRICT result; RETURN result; END $EOF$ LANGUAGE plpgsql STABLE; CREATE OR REPLACE FUNCTION quux() RETURNS INTEGER AS $EOF$ BEGIN LOOP RAISE NOTICE 'iteration'; EXECUTE 'SELECT COUNT(*) FROM foo WHERE i = a_bar()'; PERFORM pg_sleep(3); END LOOP; END $EOF$ LANGUAGE plpgsql STABLE; SELECT quux(); -- concurrently: -- INSERT INTO bar VALUES (501); ROLLBACK; With the current code, the function call runs indefinitely. With the _SPI_prepare_plan() change, it fails during planning on the next iteration after the concurrent change. This seems less severe than the current bug, but it's still not great. We could preserve the behavior of that example by instead adding a "read_only" parameter to SPI_prepare* (or defining new functions with the parameter) and having that parameter control snapshot acquisition as it does for SPI_execute*. Opinions? Better ideas? > BEGIN; > > CREATE TABLE foo(i INTEGER); > CREATE TABLE bar(i INTEGER); > > CREATE OR REPLACE FUNCTION a_bar() RETURNS INTEGER AS $EOF$ > DECLARE > result INTEGER; > BEGIN > EXECUTE 'SELECT i FROM bar' INTO STRICT result; > RETURN result; > END > $EOF$ LANGUAGE plpgsql STABLE; > > CREATE OR REPLACE FUNCTION quux() RETURNS INTEGER AS $EOF$ > DECLARE > result INTEGER; > BEGIN > INSERT INTO foo(i) SELECT s.a FROM generate_series(1,1000,1) s(a); > INSERT INTO bar(i) VALUES(500); > SELECT INTO STRICT result COUNT(*) FROM foo WHERE i = a_bar(); > RETURN result; > END > $EOF$ LANGUAGE plpgsql; > > SELECT quux(); > > ROLLBACK; -- 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 #6018: ctrl +C cause data inconsistent for sync standby
On Tue, May 10, 2011 at 11:38 AM, lxzou wrote: > I am interested in standby function of PG, so i test the new feature about > sync standby in PG9.1. I have a question, i install only one primary > postgres and no standby server. When i execute an insert statement in > primary, this statement was blocked. But when I press Ctrl + C in psql, the > statement was commited. In my view, the walsender can't send the xlog to > standby server, the statement should be rollback, but not commit; because > this will cause the data between primary and standby inconsistent. The transaction gets blocked by sync rep *after* it's committed on the master. So, we cannot rollback such a transaction because it's already been committed on the master (i.e., WAL has already been flushed to the disk). Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 9.1beta1 "collate" test failure
Hello all, while packaging 9.1 beta1, I noticed that the "collate" test case now fails (see attached regression.diffs). It seems to work when I run this under LANG= LANGUAGE= (i. e. in C locale), but it fails under both en_US.UTF-8 and de_DE.UTF-8. Is this just a bug/quirk/expectation of the test suite, i. e. should I always run the tests under the C locale? Or does it actually point towards an unexpected behaviour? Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) *** /home/martin/debian/psql/9.1/postgresql-9.1beta1/src/test/regress/expected/collate.out 2011-04-27 23:17:22.0 +0200 --- /home/martin/debian/psql/9.1/postgresql-9.1beta1/src/test/regress/results/collate.out 2011-05-10 14:32:53.116257734 +0200 *** *** 49,70 a | b ---+- 1 | abc 3 | bbc ! (2 rows) SELECT * FROM collate_test1 WHERE b >= 'abc' COLLATE "C"; a | b ---+- 1 | abc 3 | bbc ! (2 rows) SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' COLLATE "C"; a | b ---+- 1 | abc 3 | bbc ! (2 rows) SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "POSIX"; -- fail ERROR: collation mismatch between explicit collations "C" and "POSIX" --- 49,76 a | b ---+- 1 | abc + 2 | Abc 3 | bbc ! 4 | ABD ! (4 rows) SELECT * FROM collate_test1 WHERE b >= 'abc' COLLATE "C"; a | b ---+- 1 | abc + 2 | Abc 3 | bbc ! 4 | ABD ! (4 rows) SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' COLLATE "C"; a | b ---+- 1 | abc + 2 | Abc 3 | bbc ! 4 | ABD ! (4 rows) SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "POSIX"; -- fail ERROR: collation mismatch between explicit collations "C" and "POSIX" *** *** 95,112 SELECT a, b FROM collate_test5 ORDER BY b; a | b ---+- - 4 | ABD - 2 | Abc 1 | abc 3 | bbc (4 rows) SELECT a, b FROM collate_test1 ORDER BY b; a | b ---+- - 4 | ABD - 2 | Abc 1 | abc 3 | bbc (4 rows) --- 101,118 SELECT a, b FROM collate_test5 ORDER BY b; a | b ---+- 1 | abc + 2 | Abc + 4 | ABD 3 | bbc (4 rows) SELECT a, b FROM collate_test1 ORDER BY b; a | b ---+- 1 | abc + 2 | Abc + 4 | ABD 3 | bbc (4 rows) *** *** 122,130 SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; a | b ---+- - 4 | ABD - 2 | Abc 1 | abc 3 | bbc (4 rows) --- 128,136 SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; a | b ---+- 1 | abc + 2 | Abc + 4 | ABD 3 | bbc (4 rows) *** *** 132,140 SELECT * FROM collate_test1 ORDER BY b; a | b ---+- - 4 | ABD - 2 | Abc 1 | abc 3 | bbc (4 rows) --- 138,146 SELECT * FROM collate_test1 ORDER BY b; a | b ---+- 1 | abc + 2 | Abc + 4 | ABD 3 | bbc (4 rows) *** *** 205,213 SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; a | coalesce ---+-- - 4 | ABD - 2 | Abc 1 | abc 3 | bbc (4 rows) --- 211,219 SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; a | coalesce ---+-- 1 | abc + 2 | Abc + 4 | ABD 3 | bbc (4 rows) *** *** 230,239 SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3; a | b | greatest ---+-+-- 2 | Abc | CCC 4 | ABD | CCC - 1 | abc | abc - 3 | bbc | bbc (4 rows) SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3; --- 236,245 SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3; a | b | greatest ---+-+-- + 1 | abc | CCC 2 | Abc | CCC + 3 | bbc | CCC 4 | ABD | CCC (4 rows) SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3; *** *** 249,262 a | x | y | lower | lower ---+-+-+---+--- 1 | hij | hij | hij | hij ! 2 | HIJ | HIJ | foo | foo (2 rows) SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; a | nullif ---+ - 4 | ABD 2 | Abc 3 | bbc 1 | (4 rows) --- 255,268 a | x | y | lower | lower ---+-+-+---+--- 1 | hij | hij | hij | hij ! 2 | HIJ | HIJ | hij | foo (2 rows) SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; a | nullif ---+ 2 | Abc + 4 | ABD 3 | bbc 1 | (4 rows) *** *** 280,288 SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; a | b ---+-- - 4 | ABD 2 | Abc 1 | abcd 3 | bbc (4 rows) --- 286,294 SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; a | b ---+-- 2 | Abc 1 | abcd + 4 | ABD 3 | bbc
[BUGS] [9.1 beta 1] log_timezone = "unknown" does not work any more
Hello all, I (or rather the Debian postgresql-common) test suite noticed that postgresql.conf's "log_timezone = unknown" does not work any more: server starting FATAL: invalid value for parameter "log_timezone": "unknown" I think this should be considered a regression, as it's still in postgresql.conf.sample, and in the documentation [1]. Thanks, Martin [1] http://developer.postgresql.org/pgdocs/postgres/runtime-config-logging.html#GUC-LOG-TIMEZONE -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] 9.1beta1 "collate" test failure
Martin Pitt writes: > while packaging 9.1 beta1, I noticed that the "collate" test case now > fails (see attached regression.diffs). It seems to work when I run > this under LANG= LANGUAGE= (i. e. in C locale), but it fails under > both en_US.UTF-8 and de_DE.UTF-8. [ raised eyebrow... ] What platform? This seems to point towards "C" locale not doing what it is supposed to ... 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 #6019: invalid cached plan on inherited table
The following bug has been logged online: Bug reference: 6019 Logged by: Email address: etdirl...@gmail.com PostgreSQL version: 9.0.4 Operating system: SLES 11 SP1 and WinXP SP3 Description:invalid cached plan on inherited table Details: Cached execution plan of SQL stored procedure (which select from inherited table) executed from within PLPGSQL function is used even when inheritance descendant is already removed. It behaves like a bug from older versions of pgsql (v<8.3) when temporary tables created and removed from within functions were still referenced by cached plans. -- same behavior on linux and windows: -- uname -a -- SMP 2010-05-20 11:14:20 +0200 x86_64 x86_64 x86_64 GNU/Linux -- select version(); -- PostgreSQL 9.0.3 on x86_64-suse-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit -- Microsoft Windows XP [Version 5.1.2600] SP3 -- select version(); -- PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit -- PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 32-bit -- to reproduce, execute following (in single transaction) BEGIN; -- cleanup DROP TABLE IF EXISTS tst CASCADE; -- create parent table CREATE TABLE tst (id serial NOT NULL PRIMARY KEY); -- create some partition CREATE TABLE tst_1 (CONSTRAINT tst_1_id_check CHECK (id >= 0 AND id < 3)) INHERITS (tst); CREATE OR REPLACE FUNCTION tst_gt_inner(arg tst.id%TYPE) RETURNS BOOLEAN AS $$ -- select something from parent table SELECT EXISTS (SELECT 1 FROM tst WHERE id > $1) $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION tst_gt_outer(arg tst.id%TYPE) RETURNS VOID AS $$ DECLARE b BOOLEAN; v VARCHAR; BEGIN -- this will output same OID as in the ERROR message (for the one below it would be "tst_1,r,140828") SELECT INTO v relname || ',' || relkind || ',' || oid FROM pg_class WHERE relname = 'tst_1'; raise notice '%', v; -- obtain result of tst_gt_inner from within plpgsql b := tst_gt_inner(arg); -- ... END; $$ LANGUAGE PLPGSQL VOLATILE; -- insert some data INSERT INTO tst_1 VALUES (1); -- when executing sql function from within plpgsql function, plan for tst_gt_inner will be cached SELECT * FROM tst_gt_outer(3); -- then drop partition DROP TABLE tst_1; -- calling it directly is still OK SELECT * FROM tst_gt_inner(3); -- try to perform tst_gt_outer second time will end in XX000 SELECT * FROM tst_gt_outer(3); COMMIT; -- result: /* ERROR: could not open relation with OID 140828 SQL state: XX000 Context: SQL function "tst_gt_inner" statement 1 PL/pgSQL function "tst_gt_outer" line 9 at assignment */ -- 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 beta 1] log_timezone = "unknown" does not work any more
Martin Pitt writes: > Hello all, > I (or rather the Debian postgresql-common) test suite noticed that > postgresql.conf's "log_timezone = unknown" does not work any more: That was an intentional change, actually. Do you have a real use case for setting it that way? > I think this should be considered a regression, as it's still in > postgresql.conf.sample, and in the documentation [1]. Hm. I forgot to modify postgresql.conf.sample, and I'm not entirely sure what to change it to. Maybe just do #log_timezone = '(default is system's timezone)' Anyone have a better idea? 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] [9.1 beta 1] log_timezone = "unknown" does not work any more
Hello, Tom Lane [2011-05-10 10:35 -0400]: > That was an intentional change, actually. Do you have a real use case > for setting it that way? I don't, it's quite an useless setting. I just stumbled over it as I use the standard upstream postgresql.conf.sample files (uncommented, of course) in the self tests for configuration rewriting on major version upgrade. I'm happy to change them to use a real value. > > I think this should be considered a regression, as it's still in > > postgresql.conf.sample, and in the documentation [1]. > > Hm. I forgot to modify postgresql.conf.sample, and I'm not entirely > sure what to change it to. Maybe just do > > #log_timezone = '(default is system's timezone)' FWIW, looks tood to me. This makes it a lot more obvious that this isn't an accepted literal value. Thanks! Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- 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.1beta1 "collate" test failure
Tom Lane [2011-05-10 10:03 -0400]: > [ raised eyebrow... ] What platform? This seems to point towards "C" > locale not doing what it is supposed to ... After your comment I checked locales on my system, and for some reason I had an /usr/lib/locales/C.UTF-8/ besides the usual locale-archive. I cleaned this up, and it works now. Sorry for the noise! Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- 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.1beta1 "collate" test failure
Martin Pitt writes: > Tom Lane [2011-05-10 10:03 -0400]: >> [ raised eyebrow... ] What platform? This seems to point towards "C" >> locale not doing what it is supposed to ... > After your comment I checked locales on my system, and for some reason > I had an /usr/lib/locales/C.UTF-8/ besides the usual locale-archive. I > cleaned this up, and it works now. Hmm ... I think that's probably still a bug, actually. Apparently what happened is that initdb injected an entry for "C" with encoding UTF8 into pg_collation, and then that was used instead of the intended "all-encodings" C entry, and then since the actual LC_COLLATE string wasn't precisely "C" we didn't go down the strcmp() code path but used whatever the locale file said to do ... which evidently wasn't really "C" behavior. We probably need to tweak initdb to be real sure it won't add entries that conflict with the built-in ones. This is already prohibited by CREATE COLLATION but initdb has to bypass that check. Thanks for the report! 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] Changed behaviour of \'
Hello again, sorry for spamming you today, but I promise that this is the last mail; it's the one remaining test case failure for me. I have some test cases to verify the handling of the obsolete \' escaping in different locales (cf. CVE-2006-2313). Up to 9.0, \' was still allowed in safe locales, but not in unsafe ones (sorry for German error messages, but they just complain about unsafe usage of \'): --- 8< $ printf "set client_encoding='SJIS'; select ''a'" | psql -Atq template1 FEHLER: unsichere Verwendung von \' in Zeichenkettenkonstante LINE 1: select '\'a' ^ HINT: Verwenden Sie '', um Quotes in Zeichenketten zu schreiben. \' ist in bestimmten Client-seitigen Kodierungen unsicher. $ printf "set client_encoding='UTF8'; select ''a'" | psql -Atq template1 WARNUNG: nicht standardkonforme Verwendung von \' in Zeichenkettenkonstante LINE 1: select '\'a' ^ HINT: Verwenden Sie '', um Quotes in Zeichenketten zu schreiben, oder verwenden Sie die Syntax für Escape-Zeichenketten (E'...'). 'a --- 8< (Note the last line here, where it outputs 'a). 9.1 still rejects \' in SJIS, but it now also rejects \' in UTF-8: --- 8< $ printf "set client_encoding='SJIS'; select ''a'" | psql -Atq template1 ERROR: unterminated quoted string at or near "'" LINE 1: select '\'a' $ printf "set client_encoding='UTF8'; select ''a'" | psql -Atq template1 ERROR: unterminated quoted string at or near "'" LINE 1: select '\'a' --- 8< Since HISTORY does not mention this, is that an explicit decision to finally deprecate the old \' syntax (which would be great, as it makes this thing a lot more robust and deterministic, but it might be worth mentioning it in HISTORY), or an unintended side effect? Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
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
Re: [BUGS] Changed behaviour of \'
On Tue, May 10, 2011 at 06:20:23PM +0200, Martin Pitt wrote: > Since HISTORY does not mention this, is that an explicit decision to > finally deprecate the old \' syntax (which would be great, as it makes > this thing a lot more robust and deterministic, but it might be worth > mentioning it in HISTORY), or an unintended side effect? release notes clearly mentions is: http://developer.postgresql.org/pgdocs/postgres/release-9-1.html >> - Change the default value of standard_conforming_strings to on (Robert Haas) >> This removes a long-standing incompatibility with the SQL standard; >> escape_string_warning has produced warnings about this usage for years. E'' >> strings are the proper way to embed escapes in strings and are unaffected by >> this change. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Changed behaviour of \'
hubert depesz lubaczewski writes: > On Tue, May 10, 2011 at 06:20:23PM +0200, Martin Pitt wrote: >> Since HISTORY does not mention this, is that an explicit decision to >> finally deprecate the old \' syntax (which would be great, as it makes >> this thing a lot more robust and deterministic, but it might be worth >> mentioning it in HISTORY), or an unintended side effect? > release notes clearly mentions is: > http://developer.postgresql.org/pgdocs/postgres/release-9-1.html > - Change the default value of standard_conforming_strings to on (Robert Haas) > This removes a long-standing incompatibility with the SQL standard; > escape_string_warning has produced warnings about this usage for years. E'' > strings are the proper way to embed escapes in strings and are unaffected by > this change. Hmm ... considering that's the first thing in the release notes, I'm surprised Martin missed it. Maybe he was looking for something mentioning backslashes ... should we add a bit that specifically says that backslashes are now no-ops by default? 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] Changed behaviour of \'
hubert depesz lubaczewski [2011-05-10 18:37 +0200]: > release notes clearly mentions is: > http://developer.postgresql.org/pgdocs/postgres/release-9-1.html > >> - Change the default value of standard_conforming_strings to on (Robert > >> Haas) > [...] Ah, of course. Thanks! (grep failure, sorry) Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- 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] Changed behaviour of \'
Tom Lane wrote: > hubert depesz lubaczewski writes: > > On Tue, May 10, 2011 at 06:20:23PM +0200, Martin Pitt wrote: > >> Since HISTORY does not mention this, is that an explicit decision to > >> finally deprecate the old \' syntax (which would be great, as it makes > >> this thing a lot more robust and deterministic, but it might be worth > >> mentioning it in HISTORY), or an unintended side effect? > > > release notes clearly mentions is: > > http://developer.postgresql.org/pgdocs/postgres/release-9-1.html > > - Change the default value of standard_conforming_strings to on (Robert > > Haas) > > This removes a long-standing incompatibility with the SQL standard; > > escape_string_warning has produced warnings about this usage for years. E'' > > strings are the proper way to embed escapes in strings and are unaffected by > > this change. > > Hmm ... considering that's the first thing in the release notes, I'm > surprised Martin missed it. Maybe he was looking for something > mentioning backslashes ... should we add a bit that specifically says > that backslashes are now no-ops by default? I added the word "backslash" before escapes in the attached applied patch. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/release-9.1.sgml b/doc/src/sgml/release-9.1.sgml new file mode 100644 index d70c806..7737381 *** a/doc/src/sgml/release-9.1.sgml --- b/doc/src/sgml/release-9.1.sgml *** *** 62,68 standard; escape_string_warning has produced warnings about this usage for years. E'' !strings are the proper way to embed escapes in strings and are unaffected by this change. --- 62,68 standard; escape_string_warning has produced warnings about this usage for years. E'' !strings are the proper way to embed backslash escapes in strings and are unaffected by this change. -- 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 #5994: Can't excute DBI->connect to oracle by client site
I run my test program with 2 ways in the same login environment.One succeed and
one fail.
1) psql -d dbi_link_test
-->connect succeed
2) psql -h HOSTNAME -d dbi_link_test
-->connect fail
The different is connection options(Unix Domain Sockets/TCP Sockets).
- Original Message -
From: "Robert Haas"
To: "Li Hongbing"
Cc:
Sent: Tuesday, May 10, 2011 10:40 AM
Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client
site
On Tue, Apr 26, 2011 at 5:36 AM, Li Hongbing wrote:
I am trying to create a 64-bit postgresql 8.4 database server which can
retrieve data from Oracle(10gR2) at an other server by DBI-LINK.
DBI-LINK works good on postgresql server site when I connect to linux by
telnet.When I connect to postgresql server by postgresql client just like
pgAdmin or EMS SQL Manager,I got a error like this:
error from Perl function "set_up_connection": DBI
connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 43 at line 35.
so I write test code to confirm DBI connect function
CREATE OR REPLACE FUNCTION dbi_link.test2()
RETURNS char AS
$BODY$
use DBI;
use DBD::Oracle;
my $dbname = 'xxx';
my $user = 'rmtrsys';
my $passwd = 'rmtrsys';
my $dbh =
DBI->connect("dbi:Oracle:host=192.168.138.173;sid=smmacs;port=1521", $user,
$passwd,{ora_charset => 'UTF8'}) or
die "Database connection not made: $DBI::errstr";
$dbh -> disconnect;
return "OK";
$BODY$
LANGUAGE 'plperlu' VOLATILE
COST 100;
When I connect to linux by telnet and excute psql -d dbi_link_test to run,it
works good
dbi_link_test=# select dbi_link.test2();
test2
---
OK
(1 row)
But when I connect by postgresql client (pgAdmin or EMS SQL Manager) to run
same sql, it returned an error:
NOTICE: DBI
connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10
ERROR: error from Perl function "test2": Database connection not made:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10.
** エラ�` **
ERROR: error from Perl function "test2": Database connection not made:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10.
SQLステ�`ト:XX000
when got this error,I can't shutdown the server normally,ths shutdown
command will not terminate unless I cancel it.
[postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
waiting for server to shut down...LOG: received smart shutdown request
.LOG: autovacuum launcher shutting down
..
Above test let me know the DBI-LINK crushed by DBI->connect, I think the
really reason is one of these:
1.postgresql create backend process has a bug,most likely with bad process
environment var
2.Orace OCI functions has a bug
3.DBD:Oracle has a bug
However,I should report this as a postgresql bug ,becourse of run same sql
must get same result anywhere(server or client).
My guess is that, depending on how you connect, something in your
environment is different, and that is what is making the Oracle
connection succeed or fail.
--
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
--
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 #5994: Can't excute DBI->connect to oracle by client site
On 05/10/11 8:09 PM, 李紅兵 wrote: I run my test program with 2 ways in the same login environment.One succeed and one fail. 1) psql -d dbi_link_test -->connect succeed 2) psql -h HOSTNAME -d dbi_link_test -->connect fail The different is connection options(Unix Domain Sockets/TCP Sockets). is your server listen_addresses = '*' ? if not, its not listening on the interface that will respond to connections to 'HOSTNAME' and, is pg_hba.conf configured for an appropriate authentication method for `host` connections ? -- 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] Changed behaviour of \'
Bruce Momjian writes: > Tom Lane wrote: >> Hmm ... considering that's the first thing in the release notes, I'm >> surprised Martin missed it. Maybe he was looking for something >> mentioning backslashes ... should we add a bit that specifically says >> that backslashes are now no-ops by default? > I added the word "backslash" before escapes in the attached applied > patch. Actually, I had something more like this in mind ... commit ea964a451e51a32b71d004d261874adb1e135066 Author: Tom Lane Date: Tue May 10 23:44:33 2011 -0400 Be more explicit about the meaning of the change in standard_conforming_strings. diff --git a/doc/src/sgml/release-9.1.sgml b/doc/src/sgml/release-9.1.sgml index 7737381..280e0bb 100644 --- a/doc/src/sgml/release-9.1.sgml +++ b/doc/src/sgml/release-9.1.sgml @@ -58,8 +58,9 @@ - This removes a long-standing incompatibility with the SQL - standard; escape_string_warning has produced warnings about this usage for years. E'' strings are the proper way to embed backslash escapes in strings and are 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 #6019: invalid cached plan on inherited table
"" writes: > Cached execution plan of SQL stored procedure (which select from inherited > table) executed from within PLPGSQL function is used even when inheritance > descendant is already removed. Don't hold your breath waiting for a fix for that :-(. There isn't any support for detecting plan-invalidation events for SQL-language functions, and in most situations it would be overkill because the plans are only cached for the lifespan of one calling query anyway. The only case where the plan could survive longer is this specific example of a SQL function called from a "simple expression" in a plpgsql function, and even then it only survives for one complete transaction. I've thought for some time that SQL functions should be reimplemented to cache information more the way plpgsql functions do, ie, with a session-lifespan data structure for each function. If we did that and made the individual query plans be stored in plancache, then invalidation of this type would occur automatically --- and, not insignificantly, the plans would survive long enough to pay back the added overhead of tracking invalidation causes for them. A somewhat related point is that people have occasionally complained because all the queries in a SQL function are run through parse analysis at once, causing things like CREATE TABLE foo ... ; INSERT INTO foo ... ; to not work because the second command is analyzed before the first is executed. If we were reimplementing SQL functions then it'd make sense to try to do something about that at the same time. None of this is on the TODO list, but I'll go fix that momentarily. 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 #5994: Can't excute DBI->connect to oracle by client site
Perhaps you misunderstanding what I mean. I can connect to postgreSQL server correctly, but When I connect postgreSQL with TCP Sockets, it return error to connect to oracle server by DBI->connect from perlu script. When I connect postgreSQL with Unix Domain Sockets,it works well. My conf file: listen_addresses = '*' # what IP address(es) to listen on; hostall all 0.0.0.0/0 password - Original Message - From: "John R Pierce" To: Sent: Wednesday, May 11, 2011 11:40 AM Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site On 05/10/11 8:09 PM, 李紅兵 wrote: I run my test program with 2 ways in the same login environment.One succeed and one fail. 1) psql -d dbi_link_test -->connect succeed 2) psql -h HOSTNAME -d dbi_link_test -->connect fail The different is connection options(Unix Domain Sockets/TCP Sockets). is your server listen_addresses = '*' ? if not, its not listening on the interface that will respond to connections to 'HOSTNAME' and, is pg_hba.conf configured for an appropriate authentication method for `host` connections ? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6020: Wrong data type returned after CAST in FROM
The following bug has been logged online:
Bug reference: 6020
Logged by: Skylar Hawk
Email address: skylar.j.h...@gmail.com
PostgreSQL version: 9.0.3
Operating system: OpenBSD
Description:Wrong data type returned after CAST in FROM
Details:
Hello,
I noticed a strange nuance. When I do a regular select on data that I CAST
as a CHAR(), the data I am returned is of type character() as I would
expect. For example:
SELECT
CAST('abcd' AS CHAR(4)) AS data
This returns 'abcd' where the type is character(4).
However, if I do the cast in the FROM portion of the query such as this:
SELECT
data
FROM
CAST('abcd' AS CHAR(4)) AS data
then I am returned 'abcd' and the type is bpchar.
I did not have this issue when I was using Postgres 8.4. I upgraded last
week, and that's when I noticed the issue in a couple database functions I
had written.
-Sky
--
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 #5994: Can't excute DBI->connect to oracle by client site
On 11/05/11 13:53, 李紅兵 wrote: > Perhaps you misunderstanding what I mean. > I can connect to postgreSQL server correctly, > but When I connect postgreSQL with TCP Sockets, it return error to > connect to oracle server by DBI->connect from perlu script. > > When I connect postgreSQL with Unix Domain Sockets,it works well. Are you _certain_ that you're connecting to the same database on the same machine when you use TCP vs unix sockets? The simplest explanation for this problem would be that you're running different test perlu code or running it on different machines/DBs when connecting via TCP than when connecting via unix socket. If that's not the case, about the only thing I can imagine is some weird kernel security module getting in the way. Maybe something that tries to isolate processes started as a result of tcp/ip connections so they can't make new connections themselves, or something crazy like that. You wouldn't happen to be on a box with SELinux/Apparmor/TOMOYO/SMACK/etc, would you? -- 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
