Lost search_path after transaction fails - Mailing list pgsql-bugs

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
line24.'. 
[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://davidn@davidnewall.com/INBOX/Sent X-Identity-Key: id1 Message-ID:
X-Archive-Number: 200902/77