Vote on SET in aborted transaction

* Bruce Momjian (pgman@candle.pha.pa.us) [020423 12:30]:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

Our current behavior is 2.

Please vote and I will tally the results.

#2, no change in behavior.

But I base that on the assumption that #1 or #3 involve serious amounts
of work, and don't see the big benefit.

I liked the line of thought that was distinguishing between in-band
(rolled back) and out-of-band (honored) SETs, although I don't think
any acceptable syntax was arrived at, and I don't have a suggestion.
If this were solved, I'd vote for '?'.

Hmm. Maybe I do have a suggestion: SET [TRANSACTIONAL] ...
But it might not be very practical.

-Brad

Bradley McLean wrote:

* Bruce Momjian (pgman@candle.pha.pa.us) [020423 12:30]:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

Our current behavior is 2.

Please vote and I will tally the results.

#2, no change in behavior.

But I base that on the assumption that #1 or #3 involve serious amounts
of work, and don't see the big benefit.

I don't want to make any big comments during the vote, but I should
mention that #1 is needed by Tom's SET for namespace path, and #1 or #3
is needed to clearly handle query timeouts.

Just thought I would refresh people's memory on how this discussion got
started.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

1

SET should follow transaction semantics and rollback.

Jan

Bruce Momjian wrote:

OK, would people please vote on how to handle SET in an aborted
transaction? This vote will allow us to resolve the issue and move
forward if needed.

In the case of:

SET x=1;
BEGIN;
SET x=2;
query_that_aborts_transaction;
SET x=3;
COMMIT;

at the end, should 'x' equal:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

Our current behavior is 2.

Please vote and I will tally the results.

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Bruce Momjian <pgman@candle.pha.pa.us> writes:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

My vote is 1 - roll back all SETs.

I'd be willing to consider making the behavior variable-specific
if anyone can identify particular variables that need to behave
differently. But overall I think it's better that the behavior
be consistent --- so you'll need a good argument to convince me
that anything should behave differently ;-).

There is a variant case that should also have been illustrated:
what if there is no error, but the user does ROLLBACK instead of
COMMIT? The particular case that is causing difficulty for me is

begin;
create schema foo;
set search_path = foo;
rollback;

There is *no* alternative here but to roll back the search_path
setting. Therefore, the only alternatives that actually count
are 1 and ? --- if you don't like 1 then you are voting for
variable-specific behavior, because search_path is going to behave
this way whether you like it or not.

regards, tom lane

Bruce Momjian wrote:

OK, would people please vote on how to handle SET in an aborted
transaction? This vote will allow us to resolve the issue and move
forward if needed.

In the case of:

SET x=1;
BEGIN;
SET x=2;
query_that_aborts_transaction;
SET x=3;
COMMIT;

at the end, should 'x' equal:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

Our current behavior is 2.

1 makes the most sense to me. I think it should be consistent for all
SET variables.

Joe

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

My vote is 1 - roll back all SETs.

Hmm I don't understand which to vote, sorry.
Are they all exclusive in the first place ?

I'd be willing to consider making the behavior variable-specific
if anyone can identify particular variables that need to behave
differently. But overall I think it's better that the behavior
be consistent --- so you'll need a good argument to convince me
that anything should behave differently ;-).

There is a variant case that should also have been illustrated:
what if there is no error, but the user does ROLLBACK instead of
COMMIT? The particular case that is causing difficulty for me is

begin;
create schema foo;
set search_path = foo;
rollback;

There is *no* alternative here but to roll back the search_path
setting.

begin;
xxxx;
ERROR: parser: parse error at or near "xxxx"

There's *no* alternative here but to call *rollback*(commit).
However PostgreSQL doesn't call *rollback* automatically and
it's the user's responsibility to call *rollback* on errors.
IMHO what to do with errors is users' responsibility basically.
The behavior of the *search_path" variable is a *had better*
or *convenient* kind of thing not a *no alternative* kind
of thing.

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

Hiroshi Inoue wrote:

I'd be willing to consider making the behavior variable-specific
if anyone can identify particular variables that need to behave
differently. But overall I think it's better that the behavior
be consistent --- so you'll need a good argument to convince me
that anything should behave differently ;-).

There is a variant case that should also have been illustrated:
what if there is no error, but the user does ROLLBACK instead of
COMMIT? The particular case that is causing difficulty for me is

begin;
create schema foo;
set search_path = foo;
rollback;

There is *no* alternative here but to roll back the search_path
setting.

begin;
xxxx;
ERROR: parser: parse error at or near "xxxx"

There's *no* alternative here but to call *rollback*(commit).
However PostgreSQL doesn't call *rollback* automatically and
it's the user's responsibility to call *rollback* on errors.
IMHO what to do with errors is users' responsibility basically.
The behavior of the *search_path" variable is a *had better*
or *convenient* kind of thing not a *no alternative* kind
of thing.

I understand from an ODBC perspective that it is the apps
responsibility, but we need some defined behavior for a psql script that
is fed into the database.

Assuming the SET commands continue to come after it is aborted but
before the COMMIT/ROLLBACK, we need to define how to handle it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

OK, would people please vote on how to handle SET in an aborted
transaction?
at the end, should 'x' equal:
1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

I'll vote for "?", if for no other reason that you are proposing taking
away a huge chunk of "language space" by apriori disallowing out of band
behaviors for anything starting with "SET". I think that is likely
Hiroshi's concern also.

If we can fit all current "SET" behaviors into a transaction model, then
I'm not against that (though we should review the list of attributes
which *are* currently affected before settling on this). afaik we have
not reviewed current behaviors and have not thought through the "what
if's" that some soft of premature policy decision might constrain in the
future.

Let me give you some examples. We might someday have nested
transactions, or transactions which can be resumed from the point of
failure. We *might* want to be able to affect recovery behaviors, and we
*might* want to do so with something like

begin;
update foo...
update bar...
<last update fails>
set blah to blah
update baz...
update bar...
<last update now succeeds>
end;

Now we currently *don't* support this behavior, but istm that we
shouldn't preclude it in the language by forcing some blanket "all SET
statements will be transaction aware".

What language elements would you propose to cover the out of band cases
if you *do* disallow "SET" in that context? If you don't have a
candidate, I'd be even more reluctant to go along with the results of
some arbitrary vote which is done in a narrow context.

And btw, if we *are* going to put transaction semantics on all of our
global variables (which is the context for starting this "SET"
discussion, right? Is that really the context we are still in, even
though you have phrased a much more general statement above?) then let's
have the discussion on *HOW* we are going to accomplish that *BEFORE*
deciding to make a semantic constraint on our language support.

Hmm, if we are going to use transaction semantics, then we should
consider using our existing transaction mechanisms, and if we use our
existing transaction mechanisms we should consider pushing these global
variables into tables or in memory tables a la "temp tables". We get the
transaction semantics for free, with the cost of value lookup at the
beginning of a transaction or statement (not sure what we can get away
with here).

If we are *not* going to use those existing mechanisms, then what
mechanism *are* we going to use? Some sort of "abort hook" mechanism to
allow SET to register things to be rolled back?

If we end up making changes and increasing constraints, then we should
also expect some increased functionality as part of the scheme,
specifically "SET extensibility". We should allow (future) packages to
define their parameters and allow SET to help.

Just some thoughts...

- Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:

Let me give you some examples. We might someday have nested
transactions, or transactions which can be resumed from the point of
failure. We *might* want to be able to affect recovery behaviors, and we
*might* want to do so with something like

begin;
update foo...
update bar...
<last update fails>
set blah to blah
update baz...
update bar...
<last update now succeeds>
end;

Sure, once we have savepoints or nested transactions I would expect SET
to work like that. The "alternative 1" should better be phrased as
"SETs should work the same way as regular SQL commands do".

I agree with your comment that it would be useful to look closely at the
list of settable variables to see whether any of them need different
semantics. Here's the list of everything that can be SET after backend
start (some of these require superuser privilege to set, but that seems
irrelevant):

datestyle
timezone
XactIsoLevel
client_encoding
server_encoding
seed
session_authorization
enable_seqscan
enable_indexscan
enable_tidscan
enable_sort
enable_nestloop
enable_mergejoin
enable_hashjoin
ksqo
geqo
debug_assertions
debug_print_query
debug_print_parse
debug_print_rewritten
debug_print_plan
debug_pretty_print
show_parser_stats
show_planner_stats
show_executor_stats
show_query_stats
show_btree_build_stats
explain_pretty_print
stats_command_string
stats_row_level
stats_block_level
trace_notify
trace_locks
trace_userlocks
trace_lwlocks
debug_deadlocks
sql_inheritance
australian_timezones
password_encryption
transform_null_equals
geqo_threshold
geqo_pool_size
geqo_effort
geqo_generations
geqo_random_seed
sort_mem
vacuum_mem
trace_lock_oidmin
trace_lock_table
max_expr_depth
wal_debug
commit_delay
commit_siblings
effective_cache_size
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
geqo_selection_bias
client_min_messages
default_transaction_isolation
dynamic_library_path
search_path
server_min_messages

Right offhand, I am not seeing anything here for which there's a
compelling case not to roll it back on error.

In fact, I have yet to hear *any* plausible example of a variable
that we would really seriously want not to roll back on error.

And btw, if we *are* going to put transaction semantics on all of our
global variables (which is the context for starting this "SET"
discussion, right? Is that really the context we are still in, even
though you have phrased a much more general statement above?) then let's
have the discussion on *HOW* we are going to accomplish that *BEFORE*
deciding to make a semantic constraint on our language support.

Hardly necessary: we'll just make guc.c keep track of the
start-of-transaction values of all variables that have changed in the
current transaction, and restore them to that value upon transaction
abort. Doesn't seem like a big deal to me. We've got tons of other
code that does exactly the same sort of thing.

Hmm, if we are going to use transaction semantics, then we should
consider using our existing transaction mechanisms, and if we use our
existing transaction mechanisms we should consider pushing these global
variables into tables or in memory tables a la "temp tables".

Quite a few of the GUC settings are values that need to be set and used
during startup, before we have table access up and running. I do not
think that it's very practical to expect them to be accessed through
table access mechanisms.

If we end up making changes and increasing constraints, then we should
also expect some increased functionality as part of the scheme,
specifically "SET extensibility".

It might well be a good idea to allow variables to be added to guc.c's
lists on-the-fly by the initialization routines of loadable modules.
But that's orthogonal to this discussion, IMHO.

regards, tom lane

Vote number 1 -- ROLL BACK

Bruce Momjian wrote:

On Wed, 24 Apr 2002, Michael Loftis wrote:

Vote number 1 -- ROLL BACK

I agree.. Number 1 - ROLL BACK

Bruce Momjian wrote:

OK, would people please vote on how to handle SET in an aborted
transaction? This vote will allow us to resolve the issue and move
forward if needed.

In the case of:

SET x=1;
BEGIN;
SET x=2;
query_that_aborts_transaction;
SET x=3;
COMMIT;

at the end, should 'x' equal:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

Our current behavior is 2.

Please vote and I will tally the results.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Bruce Momjian wrote:

OK, would people please vote on how to handle SET in an aborted
transaction? This vote will allow us to resolve the issue and move
forward if needed.

In the case of:

SET x=1;
BEGIN;
SET x=2;
query_that_aborts_transaction;
SET x=3;
COMMIT;

at the end, should 'x' equal:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

Our current behavior is 2.

Please vote and I will tally the results.

Is it a vote in the first place ?
I will vote the current(2 + 3 + ?).

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

Tom Lane wrote:

Right offhand, I am not seeing anything here for which there's a
compelling case not to roll it back on error.

In fact, I have yet to hear *any* plausible example of a variable
that we would really seriously want not to roll back on error.

Honetsly I don't understand what kind of example you
expect. How about the following ?

[The curren schema is schema1]

begin;
create schema foo;
set search_path = foo;
create table t1 (....);
.
[error occurs]
rollback;
insert into t1 select * from schema1.t1;

Should the search_path be put back in this case ?
As I mentioned already many times, it doesn't seem
*should be* kind of thing.

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

OK, the votes are in:

#1
Lamar Owen
Jan Wieck
Tom Lane
Bruce Momjian
Joe Conway
Curt Sampson
Michael Loftis
Vince Vielhaber
Sander Steffann

#2
Bradley McLean

#3

#?
Thomas Lockhart
Hiroshi Inoue

Looks like #1 is the clear winner.

---------------------------------------------------------------------------

Bruce Momjian wrote:

OK, would people please vote on how to handle SET in an aborted
transaction? This vote will allow us to resolve the issue and move
forward if needed.

In the case of:

SET x=1;
BEGIN;
SET x=2;
query_that_aborts_transaction;
SET x=3;
COMMIT;

at the end, should 'x' equal:

1 - All SETs are rolled back in aborted transaction
2 - SETs are ignored after transaction abort
3 - All SETs are honored in aborted transaction
? - Have SETs vary in behavior depending on variable

Our current behavior is 2.

Please vote and I will tally the results.

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hiroshi Inoue wrote:

Tom Lane wrote:

Right offhand, I am not seeing anything here for which there's a
compelling case not to roll it back on error.

In fact, I have yet to hear *any* plausible example of a variable
that we would really seriously want not to roll back on error.

Honetsly I don't understand what kind of example you
expect. How about the following ?

[The curren schema is schema1]

begin;
create schema foo;
set search_path = foo;
create table t1 (....);
.
[error occurs]
rollback;
insert into t1 select * from schema1.t1;

Should the search_path be put back in this case ?
As I mentioned already many times, it doesn't seem
*should be* kind of thing.

Sure should it! You gave an example for the need to roll
back, because otherwise you would end up with an invalid
search path "foo".

I still believe that rolling back is the only right thing to
do. What if your application doesn't even know that some
changes happened? Have a trigger that set's seqscan off, does
some stuff and intends to reset it later again. Now it elog's
out before, so your application will have to live with this
mis-setting on this pooled DB connection until the end? I
don't think so!

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck wrote:

Hiroshi Inoue wrote:

Tom Lane wrote:

Right offhand, I am not seeing anything here for which there's a
compelling case not to roll it back on error.

In fact, I have yet to hear *any* plausible example of a variable
that we would really seriously want not to roll back on error.

Honetsly I don't understand what kind of example you
expect. How about the following ?

[The curren schema is schema1]

begin;
create schema foo;
set search_path = foo;
create table t1 (....);
.
[error occurs]
rollback;
insert into t1 select * from schema1.t1;

Should the search_path be put back in this case ?
As I mentioned already many times, it doesn't seem
*should be* kind of thing.

Sure should it! You gave an example for the need to roll
back, because

otherwise you would end up with an invalid
search path "foo".

What's wrong with it ? The insert command after *rollback*
would fail. It seems the right thing to me. Otherwise
the insert command would try to append the data of the
table t1 to itself. The insert command is for copying
schema1.t1 to foo.t1 in case the previous create schema
command suceeded.

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Honetsly I don't understand what kind of example you
expect. How about the following ?

[The curren schema is schema1]

begin;
create schema foo;
set search_path = foo;
create table t1 (....);
.
[error occurs]
rollback;
insert into t1 select * from schema1.t1;

Should the search_path be put back in this case ?

Sure it should be. Otherwise it's pointing at a nonexistent schema.

regards, tom lane

Bruce Momjian wrote:

OK, the votes are in:

#1
Lamar Owen
Jan Wieck
Tom Lane
Bruce Momjian
Joe Conway
Curt Sampson
Michael Loftis
Vince Vielhaber
Sander Steffann

#2
Bradley McLean

#3

#?
Thomas Lockhart
Hiroshi Inoue

Looks like #1 is the clear winner.

I voted not only ? but also 2 and 3.
And haven't I asked twice or so if it's a vote ?

Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

Hiroshi Inoue wrote:

Bruce Momjian wrote:

OK, the votes are in:

#1
Lamar Owen
Jan Wieck
Tom Lane
Bruce Momjian
Joe Conway
Curt Sampson
Michael Loftis
Vince Vielhaber
Sander Steffann

#2
Bradley McLean

#3

#?
Thomas Lockhart
Hiroshi Inoue

Looks like #1 is the clear winner.

I voted not only ? but also 2 and 3.
And haven't I asked twice or so if it's a vote ?

Yes, it is a vote, and now that we see how everyone feels, we can
decide what to do.

Hiroshi, you can't vote for 2, 3, and ?. Please pick one. I picked '?'
for you because it seemed the closest to your intent. I can put you
down for 1/3 of a vote for all three if you wish.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026