BUG #14368: Strange behaviour of set_config() in case of transaction failure

From: alena(dot)zubets(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14368: Strange behaviour of set_config() in case of transaction failure
Date: 2016-10-12 15:15:01
Message-ID: 20161012151501.1413.41489@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14368
Logged by: Alena Zubets
Email address: alena(dot)zubets(at)gmail(dot)com
PostgreSQL version: 9.4.5
Operating system: Ubuntu 4.8.2
Description:

Documentation (https://www.postgresql.org/docs/9.4/static/sql-set.html)
states following about SET command:
1) "If SET (or equivalently SET SESSION) is issued within a transaction that
is later aborted, the effects of the SET command disappear when the
transaction is rolled back."
2) "The function set_config provides equivalent functionality;"

psql (9.4.0, server 9.4.5)
WARNING: Console code page (852) differs from Windows code page (1250)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Type "help" for help.

db_name=> \set VERBOSITY verbose
db_name=> SELECT quote_literal(current_setting('conf.param'));
ERROR: 42704: unrecognized configuration parameter "conf.param"
LOCATION: GetConfigOptionByName, guc.c:7587
db_name=> DO
db_name-> $$
db_name$> BEGIN
db_name$> PERFORM set_config('conf.param', 'value', FALSE);
db_name$>
db_name$> RAISE NOTICE 'conf.param = %', current_setting('conf.param');
db_name$>
db_name$> RAISE EXCEPTION 'ERROR';
db_name$>
db_name$> END
db_name$> $$;
NOTICE: 00000: conf.param = value
LOCATION: exec_stmt_raise, pl_exec.c:3068
ERROR: P0001: ERROR
LOCATION: exec_stmt_raise, pl_exec.c:3068
db_name=> SELECT quote_literal(current_setting('conf.param'));
quote_literal
---------------
''
(1 row)

db_name=> SELECT set_config('conf.param', 'value', FALSE);
set_config
------------
value
(1 row)

db_name=> DO
db_name-> $$
db_name$> BEGIN
db_name$> PERFORM set_config('conf.param', 'another_value', FALSE);
db_name$>
db_name$> RAISE NOTICE 'conf.param = %', current_setting('conf.param');
db_name$>
db_name$> RAISE EXCEPTION 'ERROR';
db_name$>
db_name$> END
db_name$> $$;
NOTICE: 00000: conf.param = another_value
LOCATION: exec_stmt_raise, pl_exec.c:3068
ERROR: P0001: ERROR
LOCATION: exec_stmt_raise, pl_exec.c:3068
db_name=> SELECT quote_literal(current_setting('conf.param'));
quote_literal
---------------
'value'
(1 row)

Problem, that I see here is that the result of set_config() is not being
fully rolled back when:
1) corresponding configuration parameter was not set yet
2) transaction, within which set_config() is being called fails

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2016-10-12 21:20:21 Re: BUG #14344: string_agg(DISTINCT ..) crash
Previous Message Tillmann Schulz 2016-10-12 11:42:53 Re: Return Codes of BatchUpdateException in PostgreSql 9.6