Hi. The session search_path is lost after a failed transaction, using SQL embedded in C, pre-processed by ecpg without -t. I'm running Postgresql 8.2.11, as packaged by Ubuntu for Ubuntu 7.10 (8.2.11-0ubuntu0.7.10). Kernel is 2.6.25.13.
I don't subscribe to this list so please copy me in replies. David -----8<--------8<--------8<--------8<--------8<--------8<--------8<--- ECPG demonstratiion of PostgreSQL losing 'search_path' [18457]: ECPGdebug: set to 1 Setting up table [18457]: ECPGconnect: opening database circle on <DEFAULT> port <DEFAULT> [18457]: ECPGexecute line 13: QUERY: drop table statements . t on connection circle [18457]: ECPGexecute line 13 Ok: DROP TABLE [18457]: ECPGexecute line 14: QUERY: create table statements . t ( i integer unique ) on connection circle [18457]: ECPGexecute line 14 Ok: CREATE TABLE [18457]: ECPGexecute line 15: QUERY: insert into statements . t values ( 1 ) , ( 2 ) on connection circle [18457]: ECPGexecute line 15 Ok: INSERT 0 2 [18457]: ECPGtrans line 16 action = commit connection = circle [18457]: ecpg_finish: Connection circle closed. [18457]: ECPGconnect: opening database circle on <DEFAULT> port <DEFAULT> [18457]: ECPGexecute line 20: QUERY: set search_path to statements , public on connection circle [18457]: ECPGexecute line 20 Ok: SET Demonstrate fault after failed transaction [18457]: ECPGtrans line 23 action = start transaction connection = circle [18457]: there is already a transaction in progress[18457]: raising sqlcode -603 [18457]: ECPGexecute line 24: QUERY: insert into t ( i ) values ( 2 ) on connection circle [18457]: ECPGexecute line 24: Error: ERROR: duplicate key violates unique constraint "t_i_key" [18457]: raising sqlstate 23505 (sqlcode: -403) in line 24, ''duplicate key violates unique constraint "t_i_key"' in line 24.'. [18457]: ECPGtrans line 25 action = commit connection = circle ***This is the fault [18457]: ECPGtrans line 27 action = start transaction connection = circle [18457]: ECPGexecute line 28: QUERY: delete from t where i > 2 on connection circle [18457]: ECPGexecute line 28: Error: ERROR: relation "t" does not exist [18457]: raising sqlstate 42P01 (sqlcode: -400) in line 28, ''relation "t" does not exist' in line 28.'. [18457]: ECPGtrans line 29 action = commit connection = circle Must reset search_path [18457]: ECPGexecute line 32: QUERY: set search_path to statements , public on connection circle [18457]: ECPGexecute line 32 Ok: SET [18457]: ECPGtrans line 33 action = start transaction connection = circle [18457]: there is already a transaction in progress[18457]: raising sqlcode -603 [18457]: ECPGtrans line 34 action = start transaction connection = circle [18457]: there is already a transaction in progress[18457]: raising sqlcode -603 [18457]: ECPGexecute line 35: QUERY: delete from t where i > 2 on connection circle [18457]: ECPGexecute line 35 Ok: DELETE 0 [18457]: raising sqlcode 100 in line 35, 'No data found in line 35.'. [18457]: ECPGtrans line 36 action = commit connection = circle [18457]: ecpg_finish: Connection circle closed. -----8<--------8<--------8<--------8<--------8<--------8<--------8<--- exec sql include sqlca; #include <stdio.h> main() { exec sql int i; printf("ECPG demonstratiion of PostgreSQL losing 'search_path'\n"); ECPGdebug(1,stdout); printf("\n\nSetting up table\n"); exec sql connect to circle; exec sql drop table statements.t; exec sql create table statements.t(i integer unique); exec sql insert into statements.t values (1), (2); exec sql commit; exec sql disconnect; exec sql connect to circle; exec sql set search_path to statements, public; printf("\n\nDemonstrate fault after failed transaction\n"); exec sql start transaction; exec sql insert into t(i) values (2); exec sql commit; printf("***This is the fault\n"); exec sql start transaction; exec sql delete from t where i > 2; exec sql commit; printf("\nMust reset search_path\n"); exec sql set search_path to statements, public; exec sql start transaction; exec sql start transaction; exec sql delete from t where i > 2; exec sql commit; exec sql disconnect; return 0; } -----8<--------8<--------8<--------8<--------8<--------8<--------8<--- >From - Thu Feb 12 21:09:07 2009 X-Mozilla-Status: 0000 X-Mozilla-Status2: 00000000 X-Mozilla-Keys: FCC: imap://dav...@davidnewall.com/INBOX/Sent X-Identity-Key: id1 Message-ID: <4993fc4b.3050...@davidnewall.com> Date: Thu, 12 Feb 2009 21:09:07 +1030 From: David Newall X-Mozilla-Draft-Info: internal/draft; vcard=0; receipt=0; uuencode=0 User-Agent: Thunderbird 2.0.0.12 (X11/20080227) MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit DDDDDD -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs