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

On 07/21/2012 12:42 PM, imagenesis(at)gmail(dot)com wrote:
> 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