Re: Lost search_path after transaction fails

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<---

David Newall wrote:
> 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.

ecpg implicitly runs everything inside transactions. You don't need to
run START TRANSACTION, that's implicit. Therefore the "set search_path"
command is in fact run in the same transaction as the failing insert, as
also hinted by the warning "there is already a transaction in progress"
at the START TRANSACTION command.

You could use -t, or add a COMMIT after the "set search_path".

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:
> ecpg implicitly runs everything inside transactions. You don't need to
> run START TRANSACTION, that's implicit. Therefore the "set
> search_path" command is in fact run in the same transaction as the
> failing insert, as also hinted by the warning "there is already a
> transaction in progress" at the START TRANSACTION command.

Thanks for your reply. Committing after setting search_path does
resolve this problem. It surprises me that a session parameter is
treated in this way.