Re: Lost search_path after transaction fails

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