Thread: search_path reset on error (making it not reset)

The search_path set for the connection is being reset if a query errors. Is there a setting to maintain the search_path despite errors.

The search_path set for the connection is being reset if a query errors. Is there a setting to maintain the search_path despite errors.

Are you setting `search_path` within a transaction? Set it outside an explicit transaction if you want it to persist after statement errors.

By the way, when asking questions like this you really should provide some basic info - at absolute minimum the version of PostgreSQL in use, and preferably a sample of SQL showing your problem and the exact text of any error messges.

Here's what happens when I test your problem:

$ psql regress
psql (9.1.4)
Type "help" for help.

regress=# select version();
                                                   version                                                  
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.0 20120507 (Red Hat 4.7.0-5), 64-bit
(1 row)

regress=# SHOW search_path;
   search_path  
-----------------
 "$user", public
(1 row)

regress=# BEGIN;
BEGIN
regress=# SET search_path = 'public';
SET
regress=# SHOW search_path;
 search_path
-------------
 public
(1 row)

regress=# BAD QUERY;
ERROR:  syntax error at or near "BAD"
LINE 1: BAD QUERY;
        ^
regress=# SHOW search_path;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
regress=# ROLLBACK;
ROLLBACK
regress=# SHOW search_path;
   search_path  
-----------------
 "$user", public
(1 row)

regress=# SET search_path = 'public';
SET
regress=# SHOW search_path;
 search_path
-------------
 public
(1 row)

regress=# BAD QUERY;
ERROR:  syntax error at or near "BAD"
LINE 1: BAD QUERY;
        ^
regress=# SHOW search_path;
 search_path
-------------
 public
(1 row)


--
Craig Ringer