Thread

Re: Vote on SET in aborted transaction

Lamar Owen <lamar.owen@wgcr.org> — 2002-04-23T14:13:29Z

On Tuesday 23 April 2002 04:27 pm, 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.

> at the end, should 'x' equal:

> 	1 - All SETs are rolled back in aborted transaction

This seems the correct behavior.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Vote on SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-23T16:27:31Z

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

Re: Vote on SET in aborted transaction

Bradley McLean <brad@bradm.net> — 2002-04-23T16:43:03Z

* 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

Re: Vote on SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-23T16:46:43Z

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

Re: Vote on SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-23T16:59:58Z

    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 #

Re: Vote on SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-23T17:09:59Z

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

Re: Vote on SET in aborted transaction

Joe Conway <mail@joeconway.com> — 2002-04-23T18:27:52Z

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

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-24T06:13:59Z

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/

Re: Vote on SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-24T13:56:38Z

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

Re: Vote on SET in aborted transaction

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-24T14:23:27Z

> 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

Re: Vote on SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-24T15:31:45Z

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

Re: Vote on SET in aborted transaction

Michael Loftis <mloftis@wgops.com> — 2002-04-24T17:33:19Z

Vote 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.
>

Re: Vote on SET in aborted transaction

Vince Vielhaber <vev@michvhf.com> — 2002-04-24T18:20:23Z

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
==========================================================================

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-24T23:42:43Z

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/

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T00:06:59Z

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/

Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T00:39:28Z

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

Re: Vote on SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-25T00:52:31Z

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 #

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T01:06:04Z

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/

Re: Vote on SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-25T01:08:00Z

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

Re: Vote totals for SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T01:10:26Z

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/

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T01:12:45Z

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

Re: Vote totals for SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T01:16:45Z

Bruce Momjian wrote:
> 
> 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 ?.

Why ?
I don't think the items are exclusive.
 
regards,
Hiroshi Inoue

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T01:17:39Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > 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 ?.
> 
> Why ?
> I don't think the items are exclusive.

Well, 2 says roll back only after transaction aborts, 3 says honor all
SET's, and ? says choose the behavior depending on the variable.  How
can you have 2, 3, and ?.  Seems ? is the catch-all vote because it
doesn't predefine the same behavior for all variables.

-- 
  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

Re: Vote on SET in aborted transaction

Michael Loftis <mloftis@wgops.com> — 2002-04-25T01:26:20Z


Hiroshi Inoue wrote:

>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.
>
Exactly, in this example shows exactly why SETs should be part of the
transaction and roll back. Heck the insert may not even fail after all
anyway and insert into the wrong schema. If the insert depends on the
schema create succeeding it should be in the same transaction. (IE it
would get rolled back or not happen at all)

>
>
>regards, 
>Hiroshi Inoue
>	http://w2422.nsk.ne.jp/~inoue/
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: Vote totals for SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T01:28:46Z

Bruce Momjian wrote:
> 
> > > >
> > > > 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 ?.
> >
> > Why ?
> > I don't think the items are exclusive.
> 
> Well, 2 says roll back only after transaction aborts,

Sorry for my poor understanding.
Isn't it 1 ?

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

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T01:29:47Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > > > >
> > > > > 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 ?.
> > >
> > > Why ?
> > > I don't think the items are exclusive.
> > 
> > Well, 2 says roll back only after transaction aborts,
> 
> Sorry for my poor understanding.
> Isn't it 1 ?

OK, original email attached. 1 rolls back all SETs in an aborted
transaction.  2 ignores SETs after transaction aborts, but  SETs before
the transaction aborted are honored.  3 honors all SETs.

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


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.

-- 
  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

Re: Vote totals for SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T01:41:19Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> > >
> > > > > >
> > > > > > 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 ?.
> > > >
> > > > Why ?
> > > > I don't think the items are exclusive.
> > >
> > > Well, 2 says roll back only after transaction aborts,
> >
> > Sorry for my poor understanding.
> > Isn't it 1 ?
> 
> OK, original email attached. 1 rolls back all SETs in an aborted
> transaction. 

> 2 ignores SETs after transaction aborts, but  SETs before
> the transaction aborted are honored.

Must I understand this from your previous posting
(2 says roll back only after transaction aborts,)
or original posting ? What I understood was 2 only
says that SET fails between a failure and the
subsequenct ROLLBACK call.

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

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T01:46:58Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Hiroshi Inoue wrote:
> > > Bruce Momjian wrote:
> > > >
> > > > > > >
> > > > > > > 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 ?.
> > > > >
> > > > > Why ?
> > > > > I don't think the items are exclusive.
> > > >
> > > > Well, 2 says roll back only after transaction aborts,
> > >
> > > Sorry for my poor understanding.
> > > Isn't it 1 ?
> > 
> > OK, original email attached. 1 rolls back all SETs in an aborted
> > transaction. 
> 
> > 2 ignores SETs after transaction aborts, but  SETs before
> > the transaction aborted are honored.
> 
> Must I understand this from your previous posting
> (2 says roll back only after transaction aborts,)
> or original posting ? What I understood was 2 only
> says that SET fails between a failure and the
> subsequenct ROLLBACK call.

Yes, 2 says that SET fails between failure query and COMMIT/ROLLBACK
call, which is current behavior.

-- 
  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

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T01:49:04Z

Michael Loftis wrote:
> 
> Hiroshi Inoue wrote:
> 
> >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.
> >
> Exactly, in this example shows exactly why SETs should be part of the
> transaction and roll back. Heck the insert may not even fail after all
> anyway and insert into the wrong schema. If the insert depends on the
> schema create succeeding it should be in the same transaction. (IE it
> would get rolled back or not happen at all)

Where's the restriction that all objects in a schema
must be created in an transaction ? Each user has his
reason and would need various kind of command call sequence.
What I've mainly insisted is what to do with errors is
users' responsibilty but I've never seen the agreement
for it. So my current understanding is you all
are thinking what to do with errors is system's
responsibilty. Then no matter how users call commands
the dbms must behave appropriately, mustn't it ?

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

Re: Vote on SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T01:59:15Z

Hiroshi Inoue wrote:
> Michael Loftis wrote:
> > 
> > Hiroshi Inoue wrote:
> > 
> > >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.
> > >
> > Exactly, in this example shows exactly why SETs should be part of the
> > transaction and roll back. Heck the insert may not even fail after all
> > anyway and insert into the wrong schema. If the insert depends on the
> > schema create succeeding it should be in the same transaction. (IE it
> > would get rolled back or not happen at all)
> 
> Where's the restriction that all objects in a schema
> must be created in an transaction ? Each user has his
> reason and would need various kind of command call sequence.
> What I've mainly insisted is what to do with errors is
> users' responsibilty but I've never seen the agreement
> for it. So my current understanding is you all
> are thinking what to do with errors is system's
> responsibilty. Then no matter how users call commands
> the dbms must behave appropriately, mustn't it ?

Hiroshi, we need a psql solution too.  People are feeding query files
into psql all the time and we should have an appropriate behavior for
them.

I now understand your point that from a ODBC perspective, you may not
want SETs rolled back and you would rather ODBC handle what to do with
SETs.  Not sure I like pushing that job off to the application
programmer, but I think I see your point.

-- 
  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

Re: Vote totals for SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-25T02:00:41Z

Bruce Momjian wrote:
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> > > 
> > > Hiroshi Inoue wrote:
> > > > Bruce Momjian wrote:
> > > > >
> > > > > > > >
> > > > > > > > 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 ?.
> > > > > >
> > > > > > Why ?
> > > > > > I don't think the items are exclusive.
> > > > >
> > > > > Well, 2 says roll back only after transaction aborts,
> > > >
> > > > Sorry for my poor understanding.
> > > > Isn't it 1 ?
> > > 
> > > OK, original email attached. 1 rolls back all SETs in an aborted
> > > transaction. 
> > 
> > > 2 ignores SETs after transaction aborts, but  SETs before
> > > the transaction aborted are honored.
> > 
> > Must I understand this from your previous posting
> > (2 says roll back only after transaction aborts,)
> > or original posting ? What I understood was 2 only
> > says that SET fails between a failure and the
> > subsequenct ROLLBACK call.
> 
> Yes, 2 says that SET fails between failure query and COMMIT/ROLLBACK
> call, which is current behavior.

    What about a SET variable that controls the behaviour of
    SET variables :-)


Jan

-- 

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

Re: Vote on SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-25T02:06:10Z

Hiroshi Inoue wrote:
> >     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.

    Wrong about your entire example is that the rollback is sheer
    wrong placed to make up your case ;-p

    There is absolutely no need to put the insert outside of  the
    transaction that is intended to copy schema1.t1 to foo.t1.


Jan

--

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

Re: Vote on SET in aborted transaction

Michael Loftis <mloftis@wgops.com> — 2002-04-25T02:06:12Z

Hiroshi Inoue wrote:

>Michael Loftis wrote:
>
>>Hiroshi Inoue wrote:
>>
>>>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.
>>>
>>Exactly, in this example shows exactly why SETs should be part of the
>>transaction and roll back. Heck the insert may not even fail after all
>>anyway and insert into the wrong schema. If the insert depends on the
>>schema create succeeding it should be in the same transaction. (IE it
>>would get rolled back or not happen at all)
>>
>
>Where's the restriction that all objects in a schema
>must be created in an transaction ? Each user has his
>reason and would need various kind of command call sequence.
>What I've mainly insisted is what to do with errors is
>users' responsibilty but I've never seen the agreement
>for it. So my current understanding is you all
>are thinking what to do with errors is system's
>responsibilty. Then no matter how users call commands
>the dbms must behave appropriately, mustn't it ?
>
IMHO as a user and developer it's more important to behave consistently.
A rollback should cause everything inside of a transaciton block to
rollback. If you need to keep something then it should either be done in
it's own transaction, or outside of an explicit transaction entirely.

There is no restriction. The system is handling an error in the way
instructed by the user either ROLLBACK or COMMIT. If you COMMIT with
errors, it's your problem. But if you askt he system to ROLLBACK it's
the users expectation that the DBMS will ROLLBACK. Not ROLLBACK this and
that, but leave another thing alone. You say BEGIN ... COMMIT you expect
a COMMIT, you say BEGIN ... ROLLBACK you expect a ROLLBACK. You say
BEGIN ... END the DBMS should 'do the right thing' (IE COMMIT if
successfull, ROLLBACK if not). Thats the behaviour I'd expect from ANY
transactional system.

The user will (and rightfully so) expect a ROLLBACK to do just that for
everything. Yes this will break the way things work currently, but on
the whole, and going forward, it makes the system consistent. Right now
we roll back SELECTs, CREATEs, UPDATEs, etc., but not SETs (or atleast
from what I can tell that's what we do.)

I understand what you're saying Hiroshi-san, but really, it's a very
weak reason. If you (as a programmer/developer) do something like in
your earlier example (perform an insert after ROLLBACK) then you know an
error occurred, and it's your own fault for inserting into the wrong
table outside of the transaction.

Re: Vote totals for SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T02:08:51Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> >
> > Must I understand this from your previous posting
> > (2 says roll back only after transaction aborts,)
> > or original posting ? What I understood was 2 only
> > says that SET fails between a failure and the
> > subsequenct ROLLBACK call.
> 
> Yes, 2 says that SET fails between failure query and COMMIT/ROLLBACK
> call, which is current behavior.

Oh I see. It was my mistake to have participated this vote.
I'm not qualified from the first because I wasn't able to
understand your vote list. 

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

Re: Vote on SET in aborted transaction

Michael Loftis <mloftis@wgops.com> — 2002-04-25T02:08:56Z

Bruce Momjian wrote:

>Hiroshi, we need a psql solution too.  People are feeding query files
>into psql all the time and we should have an appropriate behavior for
>them.
>
>I now understand your point that from a ODBC perspective, you may not
>want SETs rolled back and you would rather ODBC handle what to do with
>SETs.  Not sure I like pushing that job off to the application
>programmer, but I think I see your point.
>

Ahhh Hiroshi is talkign formt he aspect of ODBC?  Well, thats an ODBC 
issue, should be handled by the ODBC driver.  Compliance with ODBC spec 
(or non-compliance) is not the issue of PostgreSQL proper.  Thats the 
issue of the ODBC driver and it's maintainers (sorry if I'm sounding 
like a bastard but heh).

If we start catering to all the different driver layers then we'll end 
up with a huge mess.  What we're 'catering' to is the SQLxx specs, and 
the expectations of a user when running and developing programs, am I right?

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T02:20:58Z

Michael Loftis wrote:
> 
> Hiroshi Inoue wrote:
>
> >Where's the restriction that all objects in a schema
> >must be created in an transaction ? Each user has his
> >reason and would need various kind of command call sequence.
> >What I've mainly insisted is what to do with errors is
> >users' responsibilty but I've never seen the agreement
> >for it. So my current understanding is you all
> >are thinking what to do with errors is system's
> >responsibilty. Then no matter how users call commands
> >the dbms must behave appropriately, mustn't it ?
> >
> IMHO as a user and developer it's more important to behave consistently.
> A rollback should cause everything inside of a transaciton block to
> rollback.

Where does the *should* come from ?
The standard says that changes to the database should
be put back but doesn't say everything should be put back.

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

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T02:37:36Z

Jan Wieck wrote:
> 
> Hiroshi Inoue wrote:
> > >     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.
> 
>     Wrong about your entire example is that the rollback is sheer
>     wrong placed to make up your case ;-p

Is this issue on the wrong(? not preferable) sequnence
of calls ?
Please don't miss the point.

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

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T02:52:44Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> Hiroshi, we need a psql solution too.  People are feeding query files
> into psql all the time and we should have an appropriate behavior for
> them.

What are you expecting for psql e.g. the following
wrong(?) example ?

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

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

Re: Vote on SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T02:53:55Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Hiroshi Inoue wrote:
> > Hiroshi, we need a psql solution too.  People are feeding query files
> > into psql all the time and we should have an appropriate behavior for
> > them.
> 
> What are you expecting for psql e.g. the following
> wrong(?) example ?
> 
> 	[The curren schema is schema1]
>         begin;
>         create schema foo;
>         set search_path = foo;
>         create table t1 (....); [error occurs]
>         commit;
>         insert into t1 select * from schema1.t1;

I am expecting the INSERT will use the search_path value that existed
before the error transaction began.

-- 
  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

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T02:54:36Z

Michael Loftis wrote:
> 
> Bruce Momjian wrote:
> 
> >Hiroshi, we need a psql solution too.  People are feeding query files
> >into psql all the time and we should have an appropriate behavior for
> >them.
> >
> >I now understand your point that from a ODBC perspective, you may not
> >want SETs rolled back and you would rather ODBC handle what to do with
> >SETs.  Not sure I like pushing that job off to the application
> >programmer, but I think I see your point.
> >
> 
> Ahhh Hiroshi is talkign formt he aspect of ODBC?  Well, thats an ODBC
> issue, should be handled by the ODBC driver. 

No. 

regards,
Hiroshi Inoue

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T03:00:28Z

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> >
> > What are you expecting for psql e.g. the following
> > wrong(?) example ?
> >
> >       [The curren schema is schema1]
> >         begin;
> >         create schema foo;
> >         set search_path = foo;
> >         create table t1 (....); [error occurs]
> >         commit;
> >         insert into t1 select * from schema1.t1;
> 
> I am expecting the INSERT will use the search_path value that existed
> before the error transaction began.
> 

So you see foo.t1 which is a copy of schema1.t1
if all were successful and you may be able to see
the doubled schema1.t1 in case of errors.

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

Re: Vote on SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T03:03:08Z

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Hiroshi Inoue wrote:
> > >
> > > What are you expecting for psql e.g. the following
> > > wrong(?) example ?
> > >
> > >       [The curren schema is schema1]
> > >         begin;
> > >         create schema foo;
> > >         set search_path = foo;
> > >         create table t1 (....); [error occurs]
> > >         commit;
> > >         insert into t1 select * from schema1.t1;
> > 
> > I am expecting the INSERT will use the search_path value that existed
> > before the error transaction began.
> > 
> 
> So you see foo.t1 which is a copy of schema1.t1
> if all were successful and you may be able to see
> the doubled schema1.t1 in case of errors.

Yes, I think that is how it would behave.  If you don't roll back 'set
search_path', you are pointing to a non-existant schema.

Probably the proper thing here would be to have the INSERT in the
transaction too.

-- 
  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

Re: Vote on SET in aborted transaction

Hiroshi Inoue <inoue@tpf.co.jp> — 2002-04-25T03:11:51Z

Bruce Momjian wrote:
> 
> > > >
> > > > What are you expecting for psql e.g. the following
> > > > wrong(?) example ?
> > > >
> > > >       [The curren schema is schema1]
> > > >         begin;
> > > >         create schema foo;
> > > >         set search_path = foo;
> > > >         create table t1 (....); [error occurs]
> > > >         commit;
> > > >         insert into t1 select * from schema1.t1;
> > >
> > > I am expecting the INSERT will use the search_path value that existed
> > > before the error transaction began.
> > >
> >
> > So you see foo.t1 which is a copy of schema1.t1
> > if all were successful and you may be able to see
> > the doubled schema1.t1 in case of errors.
> 
> Yes, I think that is how it would behave.  If you don't roll back 'set
> search_path', you are pointing to a non-existant schema.

OK I see your standpoint. If Tom agrees with Bruce I don't
object any more.

regards,
Hiroshi Inoue

Re: Vote totals for SET in aborted transaction

Sander Steffann <sander@steffann.nl> — 2002-04-25T12:05:43Z

>     What about a SET variable that controls the behaviour of
>     SET variables :-)

Or two commands for the same thing:
- a SET command that behaves as it does now
- a TSET command that is transaction-aware

Ouch... :-)
Sander

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-25T12:52:19Z

Just curious here, but has anyone taken the time to see how others are
doing this?  For instance, if we go with 1, are going against how everyone
else handles it?  IMHO, its not a popularity contest ...

Personally, I do agree with #1, but I'm curious as to how those coming
from other DBMS are going to have problems if this isn't what they are
expecting ...


On Wed, 24 Apr 2002, 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.
>
> ---------------------------------------------------------------------------
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: Vote totals for SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-25T14:18:26Z

Sander Steffann wrote:
> >     What about a SET variable that controls the behaviour of
> >     SET variables :-)
>
> Or two commands for the same thing:
> - a SET command that behaves as it does now
> - a TSET command that is transaction-aware
>
> Ouch... :-)
> Sander

    Naw, that's far too easy. I got it now, a

        CONFIGURE variable ON ROLLBACK <action>

        action: SET DEFAULT             (read again from .conf)
              | SET 'value'             (might fail, fallback to .conf)
              | NO ACTION               (ignore rollback)
              | ROLLBACK                (return to value before transaction)

    Also,  we  should make all these settings DB dependant and be
    able to specify the configure settings in the .conf file,  so
    that  two  databases running under the same postmaster bahave
    completely different, just to make the confusion perfect  for
    every client.

    And for everyone who didn't get it, this was sarcasm!


Jan

--

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

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T15:50:27Z

Marc G. Fournier wrote:
> 
> Just curious here, but has anyone taken the time to see how others are
> doing this?  For instance, if we go with 1, are going against how everyone
> else handles it?  IMHO, its not a popularity contest ...

Yes, good point.  I don't know that they use SET, but if they do, we
should find out how they handle it, though I doubt they have thought
through their SET handling as well as we have.  My guess is that they do
3, honor all SETs.

-- 
  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

Re: Vote totals for SET in aborted transaction

Mike Mascari <mascarm@mascari.com> — 2002-04-25T17:16:51Z

Bruce Momjian wrote:
> 
> Marc G. Fournier wrote:
> >
> > Just curious here, but has anyone taken the time to see how others are
> > doing this?  For instance, if we go with 1, are going against how everyone
> > else handles it?  IMHO, its not a popularity contest ...
> 
> Yes, good point.  I don't know that they use SET, but if they do, we
> should find out how they handle it, though I doubt they have thought
> through their SET handling as well as we have.  My guess is that they do
> 3, honor all SETs.

Connected to:
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;

TO_CHAR(S
---------
25-APR-02

SQL> COMMIT;

Commit complete.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';

Session altered.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;

TO_CHAR(SY
----------
2002 04 25

Of course, with Oracle, the only operations which can be rolled back are
INSERTs, UPDATEs, and DELETEs (DML statements). A long time ago, on a
planet far, far away, I argued that PostgreSQL should follow Oracle's
behavior in this regard. I stand corrected. The ability to rollback DROP
TABLE is a very nice feature Oracle doesn't have, and to remain
consistent, I agree with all of those that have voted for #1.

Mike Mascari
mascarm@mascari.com

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-25T17:59:43Z

On Thu, 25 Apr 2002, Mike Mascari wrote:

> Bruce Momjian wrote:
> >
> > Marc G. Fournier wrote:
> > >
> > > Just curious here, but has anyone taken the time to see how others are
> > > doing this?  For instance, if we go with 1, are going against how everyone
> > > else handles it?  IMHO, its not a popularity contest ...
> >
> > Yes, good point.  I don't know that they use SET, but if they do, we
> > should find out how they handle it, though I doubt they have thought
> > through their SET handling as well as we have.  My guess is that they do
> > 3, honor all SETs.
>
> Connected to:
> Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
> PL/SQL Release 8.0.5.0.0 - Production
>
> SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;
>
> TO_CHAR(S
> ---------
> 25-APR-02
>
> SQL> COMMIT;
>
> Commit complete.
>
> SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
>
> Session altered.
>
> SQL> ROLLBACK;
>
> Rollback complete.
>
> SQL> SELECT TO_CHAR(SYSDATE) FROM DUAL;
>
> TO_CHAR(SY
> ----------
> 2002 04 25
>
> Of course, with Oracle, the only operations which can be rolled back are
> INSERTs, UPDATEs, and DELETEs (DML statements). A long time ago, on a
> planet far, far away, I argued that PostgreSQL should follow Oracle's
> behavior in this regard. I stand corrected. The ability to rollback DROP
> TABLE is a very nice feature Oracle doesn't have, and to remain
> consistent, I agree with all of those that have voted for #1.

Okay, based on this, I'm pseudo-against ... I think, for reasons of
reducing headaches for ppl posting, there should be some sort of 'SET
oracle_quirks' operation that would allow for those with largish legacy
apps trying to migrate over to do so without having to check for "odd"
behaviours like this ...

Or maybe "SET set_rollbacks = oracle"?  with default being #1 as discussed
...

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T18:26:01Z

Marc G. Fournier wrote:
> Okay, based on this, I'm pseudo-against ... I think, for reasons of
> reducing headaches for ppl posting, there should be some sort of 'SET
> oracle_quirks' operation that would allow for those with largish legacy
> apps trying to migrate over to do so without having to check for "odd"
> behaviours like this ...
> 
> Or maybe "SET set_rollbacks = oracle"?  with default being #1 as discussed

Yes, I understand.  However, seeing that we have gone 6 years with this
never being an issue, I think we should just shoot for #1 and keep open
to the idea of having a compatibility mode, and the possibility that #1
may not fit for all SET variables and we may have to do some special
cases for those.

My guess is that we should implement #1 and see what feedback we get in
7.3.

-- 
  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

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-25T19:01:21Z

On Thu, 25 Apr 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > Okay, based on this, I'm pseudo-against ... I think, for reasons of
> > reducing headaches for ppl posting, there should be some sort of 'SET
> > oracle_quirks' operation that would allow for those with largish legacy
> > apps trying to migrate over to do so without having to check for "odd"
> > behaviours like this ...
> >
> > Or maybe "SET set_rollbacks = oracle"?  with default being #1 as discussed
>
> Yes, I understand.  However, seeing that we have gone 6 years with this
> never being an issue, I think we should just shoot for #1 and keep open
> to the idea of having a compatibility mode, and the possibility that #1
> may not fit for all SET variables and we may have to do some special
> cases for those.
>
> My guess is that we should implement #1 and see what feedback we get in
> 7.3.

IMHO, it hasn't been thought out well enough to be implemented yet ... the
options have been, but which to implement haven't ... right now, #1 is
proposing to implement something that goes against what *at least* one of
DBMS does ... so now you have programmers coming from that environment
expecting one thing to happen, when a totally different thing results ...

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T20:32:25Z

Marc G. Fournier wrote:
> > My guess is that we should implement #1 and see what feedback we get in
> > 7.3.
> 
> IMHO, it hasn't been thought out well enough to be implemented yet ... the
> options have been, but which to implement haven't ... right now, #1 is
> proposing to implement something that goes against what *at least* one of
> DBMS does ... so now you have programmers coming from that environment
> expecting one thing to happen, when a totally different thing results ...

But, they don't expect our current behavior either (which is really
weird).  At least I haven't seen anyone complaining about our current
weird behavior, and we are improving it, at least as our users request
it.

In fact, Oracle doesn't implement rollback for DROP TABLE, and we
clearly wanted that feature, so do we ignore rollback for SET too?

I guess I don't see it as a killer if we can do better than Oracle, or
at least most of our users (including you) think it is better than
Oracle.  If someone wants Oracle behavior after we do #1, we can add it,
right?

-- 
  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

Re: Vote totals for SET in aborted transaction

Vince Vielhaber <vev@michvhf.com> — 2002-04-25T21:04:30Z

On Thu, 25 Apr 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > > My guess is that we should implement #1 and see what feedback we get in
> > > 7.3.
> >
> > IMHO, it hasn't been thought out well enough to be implemented yet ... the
> > options have been, but which to implement haven't ... right now, #1 is
> > proposing to implement something that goes against what *at least* one of
> > DBMS does ... so now you have programmers coming from that environment
> > expecting one thing to happen, when a totally different thing results ...
>
> But, they don't expect our current behavior either (which is really
> weird).  At least I haven't seen anyone complaining about our current
> weird behavior, and we are improving it, at least as our users request
> it.
>
> In fact, Oracle doesn't implement rollback for DROP TABLE, and we
> clearly wanted that feature, so do we ignore rollback for SET too?
>
> I guess I don't see it as a killer if we can do better than Oracle, or
> at least most of our users (including you) think it is better than
> Oracle.  If someone wants Oracle behavior after we do #1, we can add it,
> right?

I've often wondered why the "but that's how the other RDBMS is doing
it" is only used when convenient.  Case in point is the issue (that's
been resolved) with the insert into foo(foo.bar) ...  where every one
I checked accepted it, but that wasn't a good enough reason for us to
support it.  Until the fact that applications that were using that
syntax was causing PostgreSQL not to be used was the issue resolved.
Now I'm seeing the "but that's the way Oracle does it" excuse being
used to justify a change.  Can we try for some consistancy?

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
==========================================================================

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-25T21:25:47Z

Marc is suggesting we may want to match Oracle somehow.

I just want to have our SET work on a sane manner.

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

Vince Vielhaber wrote:
> On Thu, 25 Apr 2002, Bruce Momjian wrote:
> 
> > Marc G. Fournier wrote:
> > > > My guess is that we should implement #1 and see what feedback we get in
> > > > 7.3.
> > >
> > > IMHO, it hasn't been thought out well enough to be implemented yet ... the
> > > options have been, but which to implement haven't ... right now, #1 is
> > > proposing to implement something that goes against what *at least* one of
> > > DBMS does ... so now you have programmers coming from that environment
> > > expecting one thing to happen, when a totally different thing results ...
> >
> > But, they don't expect our current behavior either (which is really
> > weird).  At least I haven't seen anyone complaining about our current
> > weird behavior, and we are improving it, at least as our users request
> > it.
> >
> > In fact, Oracle doesn't implement rollback for DROP TABLE, and we
> > clearly wanted that feature, so do we ignore rollback for SET too?
> >
> > I guess I don't see it as a killer if we can do better than Oracle, or
> > at least most of our users (including you) think it is better than
> > Oracle.  If someone wants Oracle behavior after we do #1, we can add it,
> > right?
> 
> I've often wondered why the "but that's how the other RDBMS is doing
> it" is only used when convenient.  Case in point is the issue (that's
> been resolved) with the insert into foo(foo.bar) ...  where every one
> I checked accepted it, but that wasn't a good enough reason for us to
> support it.  Until the fact that applications that were using that
> syntax was causing PostgreSQL not to be used was the issue resolved.
> Now I'm seeing the "but that's the way Oracle does it" excuse being
> used to justify a change.  Can we try for some consistancy?
> 
> 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
> ==========================================================================
> 
> 
> 
> 
> ---------------------------(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

Re: Vote totals for SET in aborted transaction

Vince Vielhaber <vev@michvhf.com> — 2002-04-25T21:42:33Z

On Thu, 25 Apr 2002, Bruce Momjian wrote:

>
> Marc is suggesting we may want to match Oracle somehow.
>
> I just want to have our SET work on a sane manner.

As do I.  But to Marc's suggestion, we discussed an oracle compatibility
factor in the past and it was dismissed.  I seem to recall someone even
volunteering to write it for us.

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
==========================================================================

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-26T01:56:57Z

On Thu, 25 Apr 2002, Bruce Momjian wrote:

>
> Marc is suggesting we may want to match Oracle somehow.
>
> I just want to have our SET work on a sane manner.

Myself, I wonder why Oracle went the route they went ... does anyone have
access to a Sybase / Informix system, to confirm how they do it?  Is
Oracle the 'odd man out', or are we going to be that?  *Adding* something
(ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
but changing the behaviour is a totally different ...

> ---------------------------------------------------------------------------
>
> Vince Vielhaber wrote:
> > On Thu, 25 Apr 2002, Bruce Momjian wrote:
> >
> > > Marc G. Fournier wrote:
> > > > > My guess is that we should implement #1 and see what feedback we get in
> > > > > 7.3.
> > > >
> > > > IMHO, it hasn't been thought out well enough to be implemented yet ... the
> > > > options have been, but which to implement haven't ... right now, #1 is
> > > > proposing to implement something that goes against what *at least* one of
> > > > DBMS does ... so now you have programmers coming from that environment
> > > > expecting one thing to happen, when a totally different thing results ...
> > >
> > > But, they don't expect our current behavior either (which is really
> > > weird).  At least I haven't seen anyone complaining about our current
> > > weird behavior, and we are improving it, at least as our users request
> > > it.
> > >
> > > In fact, Oracle doesn't implement rollback for DROP TABLE, and we
> > > clearly wanted that feature, so do we ignore rollback for SET too?
> > >
> > > I guess I don't see it as a killer if we can do better than Oracle, or
> > > at least most of our users (including you) think it is better than
> > > Oracle.  If someone wants Oracle behavior after we do #1, we can add it,
> > > right?
> >
> > I've often wondered why the "but that's how the other RDBMS is doing
> > it" is only used when convenient.  Case in point is the issue (that's
> > been resolved) with the insert into foo(foo.bar) ...  where every one
> > I checked accepted it, but that wasn't a good enough reason for us to
> > support it.  Until the fact that applications that were using that
> > syntax was causing PostgreSQL not to be used was the issue resolved.
> > Now I'm seeing the "but that's the way Oracle does it" excuse being
> > used to justify a change.  Can we try for some consistancy?
> >
> > 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
> > ==========================================================================
> >
> >
> >
> >
> > ---------------------------(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
>

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-26T02:20:49Z

Marc G. Fournier wrote:
> On Thu, 25 Apr 2002, Bruce Momjian wrote:
> 
> >
> > Marc is suggesting we may want to match Oracle somehow.
> >
> > I just want to have our SET work on a sane manner.
> 
> Myself, I wonder why Oracle went the route they went ... does anyone have
> access to a Sybase / Informix system, to confirm how they do it?  Is
> Oracle the 'odd man out', or are we going to be that?  *Adding* something
> (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
> but changing the behaviour is a totally different ...

Yes, let's find out what the others do.  I don't see DROP TABLE
rollbacking as totally different.  How is it different from SET?

-- 
  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

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-26T02:22:22Z

Vince Vielhaber wrote:
> On Thu, 25 Apr 2002, Bruce Momjian wrote:
> 
> >
> > Marc is suggesting we may want to match Oracle somehow.
> >
> > I just want to have our SET work on a sane manner.
> 
> As do I.  But to Marc's suggestion, we discussed an oracle compatibility
> factor in the past and it was dismissed.  I seem to recall someone even
> volunteering to write it for us.

Yes, doing SET the Oracle way would be part of a much larger project
that turns on Oracle compatibility.  We can add some comment to the code
and come back to this area if we start to consider an Oracle mode more
seriously.

-- 
  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

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-26T03:31:18Z

On Thu, 25 Apr 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > On Thu, 25 Apr 2002, Bruce Momjian wrote:
> >
> > >
> > > Marc is suggesting we may want to match Oracle somehow.
> > >
> > > I just want to have our SET work on a sane manner.
> >
> > Myself, I wonder why Oracle went the route they went ... does anyone have
> > access to a Sybase / Informix system, to confirm how they do it?  Is
> > Oracle the 'odd man out', or are we going to be that?  *Adding* something
> > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
> > but changing the behaviour is a totally different ...
>
> Yes, let's find out what the others do.  I don't see DROP TABLE
> rollbacking as totally different.  How is it different from SET?

SET currently has an "accepted behaviour" with other DBMSs, or, at least,
with Oracle, and that is to ignore the rollback ...

DROP TABLE also had an "accepted behaviour", and that was to leave it
DROPed, so "oops, I screwed up and just lost a complete table as a
result", which, IMHO, isn't particularly good ...

NOTE that I *do* think that #1 is what *should* happen, but there should
be some way of turning off that behaviour so that we don't screw up ppl
expecting "Oracles behaviour" ... I just think that implementing #1
without the 'switch' is implementing a half-measure that is gonna come
back and bite us ...

Re: Vote totals for SET in aborted transaction

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2002-04-26T03:48:49Z

At 04:01 PM 4/25/02 -0300, Marc G. Fournier wrote:
> > My guess is that we should implement #1 and see what feedback we get in
> > 7.3.
>
>IMHO, it hasn't been thought out well enough to be implemented yet ... the
>options have been, but which to implement haven't ... right now, #1 is
>proposing to implement something that goes against what *at least* one of
>DBMS does ... so now you have programmers coming from that environment
>expecting one thing to happen, when a totally different thing results ...

I don't know about those programmers, but AFAIK when I shift from one DBMS 
to another I expect weird things to happen, because the whole DBMS world is 
filled with all sorts of "no standard" behaviour.

SET XXX doesn't even directly map to Oracle's stuff in the first place. 
Since it looks different, I think the migrator shouldn't be surprised if it 
works differently. They might expect it to work the same, but if it doesn't 
they'll just go "OK yet another one of those".

What would be good are "RDBMS X to Postgresql" migration docs. I believe 
there's already an Oracle to Postgresql migration document. So putting all 
these things there and linking to them would be helpful.
---

I'm sorry if this has been discussed already:

There may be some SETs which operate on a different level of the 
application. We may wish to clearly differentiate them from those that are 
transactional and can operate in the domain of other SQL statements. Or put 
those in config files and they never appear in SETs?

Coz some things should not be rolled back. So you guys might come up with a 
different keyword for it.

e.g.
CONFIG: for non transactional stuff that can appear as SQL statements.
SET: for stuff that can be transactional.

Practical example: Does doing an enable seqscan affect OTHER db connections 
and transactions as well? If it doesn't then yes it should be 
transactional, whereas if does then it shouldn't bother being 
transactional. And there could well be two cases operating in different 
domains. e.g. CONFIG globalseqscan=0 and SET seqscan=0.

Regards,
Link.

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-26T04:43:48Z

Marc G. Fournier wrote:
> > Yes, let's find out what the others do.  I don't see DROP TABLE
> > rollbacking as totally different.  How is it different from SET?
> 
> SET currently has an "accepted behaviour" with other DBMSs, or, at least,
> with Oracle, and that is to ignore the rollback ...
> 
> DROP TABLE also had an "accepted behaviour", and that was to leave it
> DROPed, so "oops, I screwed up and just lost a complete table as a
> result", which, IMHO, isn't particularly good ...
> 
> NOTE that I *do* think that #1 is what *should* happen, but there should
> be some way of turning off that behaviour so that we don't screw up ppl
> expecting "Oracles behaviour" ... I just think that implementing #1
> without the 'switch' is implementing a half-measure that is gonna come
> back and bite us ...

Yes, I understand, and the logical place would be GUC.  However, if we
add every option someone would ever want to GUC, the number of options
would be huge.

We currently have a problem doing #2.  My suggestion is that we go to #1
and wait to see if anyone actually asks for the option of choosing #3.

-- 
  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

Re: Vote totals for SET in aborted transaction

Curt Sampson <cjs@cynic.net> — 2002-04-26T05:36:37Z

On Fri, 26 Apr 2002, Marc G. Fournier wrote:

> NOTE that I *do* think that #1 is what *should* happen, but there should
> be some way of turning off that behaviour so that we don't screw up ppl
> expecting "Oracles behaviour" ...

I don't think this follows. If it's only for people's expectations,
but we default to #1, their expectations will be violated until
they figure out that the option is there. After they figure out
it's there, well, they don't expect it to behave like Oracle any
more, so they don't need the switch, right?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: Vote totals for SET in aborted transaction

Lee Kindness <lkindness@csl.co.uk> — 2002-04-26T08:46:04Z

Marc G. Fournier writes:
 > Myself, I wonder why Oracle went the route they went ... does anyone have
 > access to a Sybase / Informix system, to confirm how they do it?  Is
 > Oracle the 'odd man out', or are we going to be that?  *Adding* something
 > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
 > but changing the behaviour is a totally different ..

FWIW, Ingres also doesn't rollback SET. However all its SET
functionality is the sort of stuff you wouldn't assume to rollback:

 auto-commit
 connection
 journaling
 logging
 session
 work locations
 maxidle

You cannot do something sane like modify the date output through SET.

Lee.

Re: Vote totals for SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-26T13:16:30Z

Bruce Momjian wrote:
> Marc G. Fournier wrote:
> > On Thu, 25 Apr 2002, Bruce Momjian wrote:
> >
> > >
> > > Marc is suggesting we may want to match Oracle somehow.
> > >
> > > I just want to have our SET work on a sane manner.
> >
> > Myself, I wonder why Oracle went the route they went ... does anyone have
> > access to a Sybase / Informix system, to confirm how they do it?  Is
> > Oracle the 'odd man out', or are we going to be that?  *Adding* something
> > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
> > but changing the behaviour is a totally different ...
>
> Yes, let's find out what the others do.  I don't see DROP TABLE
> rollbacking as totally different.  How is it different from SET?

    Man,  you  should know that our transactions are truly all or
    nothing.  If you discard a transaction, the stamps  xmin  and
    xmax are ignored.  This is a fundamental feature of Postgres,
    and if you're half through a utility command when  you  ERROR
    out,  it  guarantees consistency of the catalog.  And now you
    want us to violate this concept for compatibility to Oracle's
    misbehaviour? No, thanks!


Jan

--

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

Re: Vote totals for SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-26T13:22:36Z

Curt Sampson wrote:
> On Fri, 26 Apr 2002, Marc G. Fournier wrote:
>
> > NOTE that I *do* think that #1 is what *should* happen, but there should
> > be some way of turning off that behaviour so that we don't screw up ppl
> > expecting "Oracles behaviour" ...
>
> I don't think this follows. If it's only for people's expectations,
> but we default to #1, their expectations will be violated until
> they figure out that the option is there. After they figure out
> it's there, well, they don't expect it to behave like Oracle any
> more, so they don't need the switch, right?

    Beeing  able  to  "read" is definitely an advantage in the IT
    world.  Someone just  has  to  do  it  before  finishing  the
    implementation based on assumptions :-)


Jan

--

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

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-26T13:24:12Z

On Fri, 26 Apr 2002, Jan Wieck wrote:

> Bruce Momjian wrote:
> > Marc G. Fournier wrote:
> > > On Thu, 25 Apr 2002, Bruce Momjian wrote:
> > >
> > > >
> > > > Marc is suggesting we may want to match Oracle somehow.
> > > >
> > > > I just want to have our SET work on a sane manner.
> > >
> > > Myself, I wonder why Oracle went the route they went ... does anyone have
> > > access to a Sybase / Informix system, to confirm how they do it?  Is
> > > Oracle the 'odd man out', or are we going to be that?  *Adding* something
> > > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
> > > but changing the behaviour is a totally different ...
> >
> > Yes, let's find out what the others do.  I don't see DROP TABLE
> > rollbacking as totally different.  How is it different from SET?
>
>     Man,  you  should know that our transactions are truly all or
>     nothing.  If you discard a transaction, the stamps  xmin  and
>     xmax are ignored.  This is a fundamental feature of Postgres,
>     and if you're half through a utility command when  you  ERROR
>     out,  it  guarantees consistency of the catalog.  And now you
>     want us to violate this concept for compatibility to Oracle's
>     misbehaviour? No, thanks!

How does SET relate to xmin/xmax? :)

Re: Vote totals for SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-26T13:33:25Z

Marc G. Fournier wrote:
> On Fri, 26 Apr 2002, Jan Wieck wrote:
>
> > Bruce Momjian wrote:
> > > Marc G. Fournier wrote:
> > > > On Thu, 25 Apr 2002, Bruce Momjian wrote:
> > > >
> > > > >
> > > > > Marc is suggesting we may want to match Oracle somehow.
> > > > >
> > > > > I just want to have our SET work on a sane manner.
> > > >
> > > > Myself, I wonder why Oracle went the route they went ... does anyone have
> > > > access to a Sybase / Informix system, to confirm how they do it?  Is
> > > > Oracle the 'odd man out', or are we going to be that?  *Adding* something
> > > > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
> > > > but changing the behaviour is a totally different ...
> > >
> > > Yes, let's find out what the others do.  I don't see DROP TABLE
> > > rollbacking as totally different.  How is it different from SET?
> >
> >     Man,  you  should know that our transactions are truly all or
> >     nothing.  If you discard a transaction, the stamps  xmin  and
> >     xmax are ignored.  This is a fundamental feature of Postgres,
> >     and if you're half through a utility command when  you  ERROR
> >     out,  it  guarantees consistency of the catalog.  And now you
> >     want us to violate this concept for compatibility to Oracle's
> >     misbehaviour? No, thanks!
>
> How does SET relate to xmin/xmax? :)
>

    SET does not. But Bruce said he doesn't see DROP TABLE beeing
    totally different. That is related to  xmin/xmax,  isn't  it?
    What  I  pointed  out  (or  wanted  to point out) is, that we
    cannot ignore rollback for catalog changes like DROP TABLE.


Jan

--

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

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-26T14:34:38Z

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Coz some things should not be rolled back. So you guys might come up with a 
> different keyword for it.

> CONFIG: for non transactional stuff that can appear as SQL statements.
> SET: for stuff that can be transactional.

People keep suggesting this, and I keep asking for a concrete example
where non-rollback is needed, and I keep not getting one.  I can't see
the value of investing work in creating an alternative behavior when
we have no solid example to justify it.

The "Oracle compatibility" argument would have some weight if we were
making any concerted effort to be Oracle-compatible across the board;
but I have not detected any enthusiasm for that.  Given that it's not
even the same syntax ("SET ..." vs "ALTER SESSION ...") I'm not sure
why an Oracle user would expect it to behave exactly the same.

> Practical example: Does doing an enable seqscan affect OTHER db connections 
> and transactions as well?

There are no SET commands that affect other backends.  (There are
GUC variables with system-wide effects, but we don't allow them to be
changed by SET; rollback or not won't affect that.)

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-26T14:46:27Z

Jan Wieck wrote:
> Bruce Momjian wrote:
> > Marc G. Fournier wrote:
> > > On Thu, 25 Apr 2002, Bruce Momjian wrote:
> > >
> > > >
> > > > Marc is suggesting we may want to match Oracle somehow.
> > > >
> > > > I just want to have our SET work on a sane manner.
> > >
> > > Myself, I wonder why Oracle went the route they went ... does anyone have
> > > access to a Sybase / Informix system, to confirm how they do it?  Is
> > > Oracle the 'odd man out', or are we going to be that?  *Adding* something
> > > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ...
> > > but changing the behaviour is a totally different ...
> >
> > Yes, let's find out what the others do.  I don't see DROP TABLE
> > rollbacking as totally different.  How is it different from SET?
> 
>     Man,  you  should know that our transactions are truly all or
>     nothing.  If you discard a transaction, the stamps  xmin  and
>     xmax are ignored.  This is a fundamental feature of Postgres,
>     and if you're half through a utility command when  you  ERROR
>     out,  it  guarantees consistency of the catalog.  And now you
>     want us to violate this concept for compatibility to Oracle's
>     misbehaviour? No, thanks!

So you do see a difference between SET and DROP TABLE because the second
is a utility command. OK, I'll buy that, but my point was different.

My point was that we don't match Oracle for DROP TABLE, so why is
matching for SET so important?

-- 
  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

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-26T14:49:55Z

Tom Lane wrote:
> Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> > Coz some things should not be rolled back. So you guys might come up with a 
> > different keyword for it.
> 
> > CONFIG: for non transactional stuff that can appear as SQL statements.
> > SET: for stuff that can be transactional.
> 
> People keep suggesting this, and I keep asking for a concrete example
> where non-rollback is needed, and I keep not getting one.  I can't see
> the value of investing work in creating an alternative behavior when
> we have no solid example to justify it.
> 
> The "Oracle compatibility" argument would have some weight if we were
> making any concerted effort to be Oracle-compatible across the board;
> but I have not detected any enthusiasm for that.  Given that it's not
> even the same syntax ("SET ..." vs "ALTER SESSION ...") I'm not sure
> why an Oracle user would expect it to behave exactly the same.

Agreed.  OK, let me summarize.

We had a vote that was overwhemingly #1.  Marc made a good point that we
should see how other databases behave, and we now know that Oracle and
Ingres do #3 (honor all SETs in an aborted transaction).  Does anyone
want to change their vote from #1 to #3.

Second, there is the idea of doing #1, and having a GUC variable for #3.
Does anyone want that?  I think Marc may.  Anyone else?

-- 
  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

Re: Vote totals for SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-26T15:15:08Z

Bruce Momjian wrote:
> So you do see a difference between SET and DROP TABLE because the second
> is a utility command. OK, I'll buy that, but my point was different.
>
> My point was that we don't match Oracle for DROP TABLE, so why is
> matching for SET so important?

    Good  point,  I  never  understood the compatibility issue on
    this level either. Applications that  create/drop  tables  at
    runtime  are  IMNSVHO  self-modifying  code.  Thus,  I  don't
    consider it a big porting issue.   Applications  that  do  it
    should be "replaced", not ported.

Jan

--

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

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-26T15:20:52Z

Jan Wieck <janwieck@yahoo.com> writes:
>     SET does not. But Bruce said he doesn't see DROP TABLE beeing
>     totally different. That is related to  xmin/xmax,  isn't  it?

I think what Bruce meant was "if rollback is good for DROP TABLE,
why isn't it good for SET"?

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2002-04-26T15:35:36Z

At 10:34 AM 4/26/02 -0400, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> > Coz some things should not be rolled back. So you guys might come up 
> with a
> > different keyword for it.
>
> > CONFIG: for non transactional stuff that can appear as SQL statements.
> > SET: for stuff that can be transactional.
>
>People keep suggesting this, and I keep asking for a concrete example
>where non-rollback is needed, and I keep not getting one.  I can't see

Sorry, I wasn't clear enough. I'm not asking for non-rollback behaviour.

I was trying to say that _IF_ one ever needs to "SET" stuff that can't be 
rolled back then it may be better to use some other keyword for that feature.

I'm actually for #1 SET being rolled back and to not have any "Oracle 
behaviour" settings at all. Anything that can't be rolled back shouldn't 
use SET.

> > Practical example: Does doing an enable seqscan affect OTHER db 
> connections
> > and transactions as well?
>
>There are no SET commands that affect other backends.  (There are
>GUC variables with system-wide effects, but we don't allow them to be
>changed by SET; rollback or not won't affect that.)

OK.

Cheerio,
Link

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-26T15:49:19Z

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> I was trying to say that _IF_ one ever needs to "SET" stuff that can't be 
> rolled back then it may be better to use some other keyword for that feature.
> I'm actually for #1 SET being rolled back and to not have any "Oracle 
> behaviour" settings at all. Anything that can't be rolled back shouldn't 
> use SET.

Ah, I understand.  Okay, I see a perfect candidate for the other syntax:

	ALTER SESSION SET ...

(or whatever the heck that Oracle syntax was).  But I'm still looking
for a case of a variable where we actually want this behavior.

The Ingres examples Lee cited were interesting --- but they all appear
to me to correspond to system-wide settings, which we do not allow SET
to modify anyway.  (To change system-wide settings, you have to change
postgresql.conf, and then SIGHUP or restart the postmaster.  This
ensures all the backends get the word.  And indeed this behavior is
outside transactional control.)

I'm still looking for an example of something that is (a) reasonable
to set on a per-backend basis, and (b) not reasonable to roll back
if it's set in a transaction that fails.

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-26T16:58:06Z

On Fri, 26 Apr 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> > > Coz some things should not be rolled back. So you guys might come up with a
> > > different keyword for it.
> >
> > > CONFIG: for non transactional stuff that can appear as SQL statements.
> > > SET: for stuff that can be transactional.
> >
> > People keep suggesting this, and I keep asking for a concrete example
> > where non-rollback is needed, and I keep not getting one.  I can't see
> > the value of investing work in creating an alternative behavior when
> > we have no solid example to justify it.
> >
> > The "Oracle compatibility" argument would have some weight if we were
> > making any concerted effort to be Oracle-compatible across the board;
> > but I have not detected any enthusiasm for that.  Given that it's not
> > even the same syntax ("SET ..." vs "ALTER SESSION ...") I'm not sure
> > why an Oracle user would expect it to behave exactly the same.
>
> Agreed.  OK, let me summarize.
>
> We had a vote that was overwhemingly #1.  Marc made a good point that we
> should see how other databases behave, and we now know that Oracle and
> Ingres do #3 (honor all SETs in an aborted transaction).  Does anyone
> want to change their vote from #1 to #3.
>
> Second, there is the idea of doing #1, and having a GUC variable for #3.
> Does anyone want that?  I think Marc may.  Anyone else?

Actually, in light of Tom's comment about it not being the same syntax, I
have to admit that I missed that syntax difference in the original post :(
I withdraw my GUC variable desire, unless/until someone does go with an
'ALTER SESSION' command ...

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-26T18:32:19Z

Marc G. Fournier wrote:
> > Second, there is the idea of doing #1, and having a GUC variable for #3.
> > Does anyone want that?  I think Marc may.  Anyone else?
> 
> Actually, in light of Tom's comment about it not being the same syntax, I
> have to admit that I missed that syntax difference in the original post :(
> I withdraw my GUC variable desire, unless/until someone does go with an
> 'ALTER SESSION' command ...

It is good we had the 'compatibility' discussion.  It is an important
point to always consider.

TODO updated:

	o Abort all SET changes made in an aborted transaction  
          
-- 
  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

Re: Vote totals for SET in aborted transaction

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2002-04-27T00:12:23Z

At 11:49 AM 4/26/02 -0400, Tom Lane wrote:
>I'm still looking for an example of something that is (a) reasonable
>to set on a per-backend basis, and (b) not reasonable to roll back
>if it's set in a transaction that fails.

The way I see it is if (a) and you don't want it rolled back, you could put 
it in a transaction of its own.
BEGIN;
SET backend pref;
COMMIT;

And if that transaction fails, maybe it should :).

So other than for performance, the example should also have a reason to 
belong with other statements in a transaction.

Have a nice weekend,
Link.

Re: Vote totals for SET in aborted transaction

Philip Warner <pjw@rhyme.com.au> — 2002-04-27T01:54:17Z

At 11:50 25/04/02 -0400, Bruce Momjian wrote:
>Marc G. Fournier wrote:
> >
> > Just curious here, but has anyone taken the time to see how others are
> > doing this?  For instance, if we go with 1, are going against how everyone
> > else handles it?  IMHO, its not a popularity contest ...

Dec/RDB (and I think Oracle as well) ignores transactions. Even 
configuration commands (eg. setting date formats etc) ignore transactions.

I think the key thing here is that they view variables as part of a 
programming language built on top of the database backend (like plpgsql). 
As a result they separate variable management from database management.

FWIW, I would be in the '?' camp - assuming that means some kind of 
session-specific setting...failing that, I'd probably start looking for an 
interactive form of plpgsql, so I could get persistant variables.

Re: Vote totals for SET in aborted transaction

scott.marlowe <scott.marlowe@ihs.com> — 2002-04-29T15:09:54Z

I've been thinking this over and over, and it seems to me, that the way 
SETS in transactions SHOULD work is that they are all rolled back, period, 
whether the transaction successfully completes OR NOT.

Transactions ensure that either all or none of the DATA in the database is 
changed.  That nature is good.  But does it make sense to apply 
transactional mechanics to SETtings?  I don't think it does.

SETtings aren't data operators, so they don't need to be rolled back / 
committed so to speak.  Their purpose is to affect the way things like the 
database works in a more overreaching sense, not the data underneath it.

For this reason, I propose that a transaction should "inherit" its 
environment, and that all changes EXCEPT for those affecting tuples should 
be rolled back after completion, leaving the environment the way we found 
it.  If you need the environment changed, do it OUTSIDE the transaction.

I would argue that the rollback on failure / don't rollback on completion 
is actually the worse possible way to handle this, because, again, this 
isn't about data, it's about environment.  And I don't think things inside 
a transaction should be mucking with the environment around them when 
they're done.

But that's just my opinion, I could be wrong.  Scott Marlowe

Re: Vote totals for SET in aborted transaction

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-29T15:29:56Z

> I've been thinking this over and over, and it seems to me, that the way
> SETS in transactions SHOULD work is that they are all rolled back, period,
> whether the transaction successfully completes OR NOT.

Very interesting! This is a *consistant* use of SET which allows
transactions to be constructed as self-contained units without
side-effects on subsequent transactions. Beautifully powerful.

                  - Thomas

I've got some other thoughts on features for other aspects of schemas
and table and query properties, but this proposal for SET behavior
stands on its own so I'll hold off on muddying the discussion.

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T15:30:35Z

Scott Marlowe <scott.marlowe@ihs.com> writes:
> I've been thinking this over and over, and it seems to me, that the way 
> SETS in transactions SHOULD work is that they are all rolled back, period, 
> whether the transaction successfully completes OR NOT.

This would make it impossible for SET to have any persistent effect
at all.  (Every SQL command is inside a transaction --- an
implicitly-established one if necesary, but there is one.)

It might well be useful to have some kind of LOCAL SET command that
behaves the way you describe (effects good only for current transaction
block), but I don't think it follows that that should be the only
behavior available.

What would you expect if LOCAL SET were followed by SET on the same
variable in the same transaction?  Presumably the LOCAL SET would then
be nullified; or is this an error condition?

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-29T15:33:36Z

Hannu Krosing wrote:
> On Mon, 2002-04-29 at 17:09, Scott Marlowe wrote:
> > For this reason, I propose that a transaction should "inherit" its 
> > environment, and that all changes EXCEPT for those affecting tuples should 
> > be rolled back after completion, leaving the environment the way we found 
> > it.  If you need the environment changed, do it OUTSIDE the transaction.
> 
> Unfortunately there is no such time in postgresql where commands are
> done outside transaction.
> 
> If you don't issue BEGIN; then each command is implicitly run in its own
> transaction. 
> 
> Rolling each command back unless it is in implicit transaction would
> really confuse the user.

Agreed, very non-intuitive.  And can you imagine how many applications
we would break.

-- 
  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

Re: Vote totals for SET in aborted transaction

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-29T15:44:26Z

...
> This would make it impossible for SET to have any persistent effect
> at all.  (Every SQL command is inside a transaction --- an
> implicitly-established one if necesary, but there is one.)

Of course the behavior would need to be defined from the user's
viewpoint, not from a literal description of how the internals work.
There *is* a difference from a user's PoV between explicit transactions
and single queries, no matter how that is implemented in the PostgreSQL
backend...

Let's not let trivial english semantics divert the discussion please.

                   - Thomas

Re: Vote totals for SET in aborted transaction

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-29T15:51:34Z

...
> Agreed, very non-intuitive.  And can you imagine how many applications
> we would break.

What is non-intuitive about it? What it *does* do is free the programmer
from worrying about side effects which *do* break applications.

Rather than dismissing this out of hand, try to look at what it *does*
enable. It allows developers to tune specific queries without having to
restore values afterwards. Values or settings which may change from
version to version, so end up embedding time bombs into applications.

And the number of current applications "broken"? None, as a starting
point ;)

                  - Thomas

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T15:53:29Z

Hannu Krosing <hannu@tm.ee> writes:
> Perhaps we could do 
> SET SET TO LOCAL TO TRANSACTION;
> Which would affect itself and all subsequent SET commands up to 
> SET SET TO GLOBAL;
> or end of transaction.

This makes my head hurt.  If I do

	SET foo TO bar;
	begin;
	SET SET TO GLOBAL;
	SET foo TO baz;
	SET SET TO LOCAL TO TRANSACTION;
	end;

(assume no errors) what is the post-transaction state of foo?

What about this case?

	SET foo TO bar;
	begin;
	SET SET TO GLOBAL;
	SET foo TO baz;
	SET SET TO LOCAL TO TRANSACTION;
	SET foo TO quux;
	end;

Of course this last case also exists with my idea of a LOCAL SET
command,

	SET foo TO bar;
	begin;
	SET foo TO baz;
	LOCAL SET foo TO quux;
	-- presumably SHOW foo will show quux here
	end;
	-- does SHOW foo now show bar, or baz?

Arguably you'd need to keep track of up to three values of a SET
variable to make this work --- the permanent (pre-transaction) value,
to roll back to if error; the SET value, which will become permanent
if we commit; and the LOCAL SET value, which may mask the pending
permanent value.  This seems needlessly complex though.  Could we get
away with treating the above case as an error?

In any case I find a LOCAL SET command more reasonable than making
SET's effects depend on the value of a SETtable setting.  There is
circular logic there.  If I do

	begin;
	SET SET TO LOCAL TO TRANSACTION;
	end;

what is the post-transaction behavior of SET?  And if you say LOCAL,
how do you justify it?  Why wouldn't the effects of this SET be local?

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T16:06:21Z

Thomas Lockhart <lockhart@fourpalms.org> writes:
> Let's not let trivial english semantics divert the discussion please.

It's hardly a trivial point, seeing that transactions are such a
fundamental aspect of the system.  The statements that we have now that
depend on being-in-a-transaction-block-or-not (eg, VACUUM) are ugly
kluges IMHO.

Let me give you another reason why having only local SET would be a bad
idea: how are you going to issue a SET with any persistent effect when
working through an interface like JDBC that wraps every command you give
in a BEGIN/END block?  We have also talked about modifying the backend's
behavior to act like BEGIN is issued implicitly as soon as you execute
any command, so that explicit COMMIT is always needed (at least some
people think this is necessary for SQL spec compliance).  Either one of
these are going to pose severe problems for the user-friendliness of SET
if it only comes in a local flavor.

I can certainly think of uses for a local-effects flavor of SET.
But I don't want that to be the only flavor.

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T16:20:07Z

Thomas Lockhart <lockhart@fourpalms.org> writes:
> Rather than dismissing this out of hand, try to look at what it *does*
> enable. It allows developers to tune specific queries without having to
> restore values afterwards. Values or settings which may change from
> version to version, so end up embedding time bombs into applications.

I think it's a great idea.  I just want it to be a different syntax from
the existing SET, so as not to break existing applications that expect
SET to be persistent.  It seems to me that marking such a command with
a new syntax is reasonable from a user-friendliness point of view too:
if you write "LOCAL SET foo" or some similar syntax, it is obvious to
every onlooker what your intentions are.  If we redefine "SET" to have
context-dependent semantics, I think we are just creating a recipe for
confusion.

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Hannu Krosing <hannu@tm.ee> — 2002-04-29T16:27:10Z

On Mon, 2002-04-29 at 17:09, Scott Marlowe wrote:
> For this reason, I propose that a transaction should "inherit" its 
> environment, and that all changes EXCEPT for those affecting tuples should 
> be rolled back after completion, leaving the environment the way we found 
> it.  If you need the environment changed, do it OUTSIDE the transaction.

Unfortunately there is no such time in postgresql where commands are
done outside transaction.

If you don't issue BEGIN; then each command is implicitly run in its own
transaction. 

Rolling each command back unless it is in implicit transaction would
really confuse the user.

> I would argue that the rollback on failure / don't rollback on completion 
> is actually the worse possible way to handle this, because, again, this 
> isn't about data, it's about environment.  And I don't think things inside 
> a transaction should be mucking with the environment around them when 
> they're done.

That would assume nested transactions which we don't have yet.

---------------
Hannu

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-29T16:29:33Z

Oh, I like ... kinda like in perl where if you set a variable 'my' inside
of conditional, it no longer exists outside of that conditional ...

I do like this ...

On Mon, 29 Apr 2002, Scott Marlowe wrote:

> I've been thinking this over and over, and it seems to me, that the way
> SETS in transactions SHOULD work is that they are all rolled back, period,
> whether the transaction successfully completes OR NOT.
>
> Transactions ensure that either all or none of the DATA in the database is
> changed.  That nature is good.  But does it make sense to apply
> transactional mechanics to SETtings?  I don't think it does.
>
> SETtings aren't data operators, so they don't need to be rolled back /
> committed so to speak.  Their purpose is to affect the way things like the
> database works in a more overreaching sense, not the data underneath it.
>
> For this reason, I propose that a transaction should "inherit" its
> environment, and that all changes EXCEPT for those affecting tuples should
> be rolled back after completion, leaving the environment the way we found
> it.  If you need the environment changed, do it OUTSIDE the transaction.
>
> I would argue that the rollback on failure / don't rollback on completion
> is actually the worse possible way to handle this, because, again, this
> isn't about data, it's about environment.  And I don't think things inside
> a transaction should be mucking with the environment around them when
> they're done.
>
> But that's just my opinion, I could be wrong.  Scott Marlowe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: Vote totals for SET in aborted transaction

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-29T16:30:32Z

> It's hardly a trivial point, seeing that transactions are such a
> fundamental aspect of the system.  The statements that we have now that
> depend on being-in-a-transaction-block-or-not (eg, VACUUM) are ugly
> kluges IMHO.

This is certainly not in the same category. And I'm sure you can see
upon rereading my post that I made no claim that this is a trivial
point. Though it certainly can be fun to pick and choose words to make
them into whatever we want them to say, I *meant* that focusing on
trivial details in semantics of postings was diverting the discussion
from the underlying technical issues which I'm sure you see. But here we
go again... ;)

> Let me give you another reason why having only local SET would be a bad
> idea: how are you going to issue a SET with any persistent effect when
> working through an interface like JDBC that wraps every command you give
> in a BEGIN/END block?  We have also talked about modifying the backend's
> behavior to act like BEGIN is issued implicitly as soon as you execute
> any command, so that explicit COMMIT is always needed (at least some
> people think this is necessary for SQL spec compliance).  Either one of
> these are going to pose severe problems for the user-friendliness of SET
> if it only comes in a local flavor.

Ah, good, a technical issue :) And you are right, this would need to be
addressed. But that certainly is not a fundamental problem.

> I can certainly think of uses for a local-effects flavor of SET.
> But I don't want that to be the only flavor.

Right. And there was no suggestion that there be so; the original
proposal used "BEGIN/END blocks" to differentiate the usage. Think about
SET SESSION... as a possible syntax to completely decouple the behaviors
if an explicit notation is desired.

We currently have a specific behavior of SET which does not quite match
other databases. We are considering changing the behavior *farther away*
from conventional behavior. I have no problem with that. But if we are
changing it, look farther ahead to see where we want to end up. We now
have schemas to help encapsulate information. We could start attaching
properties to schemas to help encapsulate behaviors. We may someday have
nested transactions, which encapsulate transaction behaviors at a finer
grain than we have now. Let's choose approaches and behaviors which
could support these things in the future, as well as supporting our
current feature set.

                  - Thomas

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-29T16:32:12Z

On Mon, 29 Apr 2002, Tom Lane wrote:

> Scott Marlowe <scott.marlowe@ihs.com> writes:
> > I've been thinking this over and over, and it seems to me, that the way
> > SETS in transactions SHOULD work is that they are all rolled back, period,
> > whether the transaction successfully completes OR NOT.
>
> This would make it impossible for SET to have any persistent effect
> at all.  (Every SQL command is inside a transaction --- an
> implicitly-established one if necesary, but there is one.)

Why?  What I think Scott is proposing is that on COMMIT *or* ABORT, all
SETs since the BEGIN are reversed ... hrmmm ... that didnt' sound right
either ... is there no way of distiguishing between an IMPLICT transcation
vs an EXPLICIT one?

INSERT ...

vs

BEGIN
INSERT ...
COMMIT

?

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-29T16:38:44Z

On Mon, 29 Apr 2002, Bruce Momjian wrote:

> Hannu Krosing wrote:
> > On Mon, 2002-04-29 at 17:09, Scott Marlowe wrote:
> > > For this reason, I propose that a transaction should "inherit" its
> > > environment, and that all changes EXCEPT for those affecting tuples should
> > > be rolled back after completion, leaving the environment the way we found
> > > it.  If you need the environment changed, do it OUTSIDE the transaction.
> >
> > Unfortunately there is no such time in postgresql where commands are
> > done outside transaction.
> >
> > If you don't issue BEGIN; then each command is implicitly run in its own
> > transaction.
> >
> > Rolling each command back unless it is in implicit transaction would
> > really confuse the user.
>
> Agreed, very non-intuitive.  And can you imagine how many applications
> we would break.

Since there is obviously no defined standard for how a SET should be
treated within a transaction ... who cares?  God, how many changes have we
made in the past that "break applications" but did them anyway?

Just as a stupid question here ... but, why do we wrap single queries into
a transaction anyway?  IMHO, a transaction is meant to tell the backend to
remember this sequence of events, so that if it fails, you can roll it
back ... with a single INSERT/UPDATE/DELETE, why 'auto-wrapper' it with a
BEGIN/END?

Re: Vote totals for SET in aborted transaction

Hannu Krosing <hannu@tm.ee> — 2002-04-29T16:41:17Z

On Mon, 2002-04-29 at 17:30, Tom Lane wrote:
> Scott Marlowe <scott.marlowe@ihs.com> writes:
> > I've been thinking this over and over, and it seems to me, that the way 
> > SETS in transactions SHOULD work is that they are all rolled back, period, 
> > whether the transaction successfully completes OR NOT.
> 
> This would make it impossible for SET to have any persistent effect
> at all.  (Every SQL command is inside a transaction --- an
> implicitly-established one if necesary, but there is one.)
> 
> It might well be useful to have some kind of LOCAL SET command that
> behaves the way you describe (effects good only for current transaction
> block), but I don't think it follows that that should be the only
> behavior available.
> 
> What would you expect if LOCAL SET were followed by SET on the same
> variable in the same transaction?  Presumably the LOCAL SET would then
> be nullified; or is this an error condition?

Perhaps we could do 

SET SET TO LOCAL TO TRANSACTION;

Which would affect itself and all subsequent SET commands up to 

SET SET TO GLOBAL;

or end of transaction.

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

SET SET TO GLOBAL 

could also be written as 

SET SET TO NOT LOCAL TO TRANSACTION;

to comply with genral verbosity of SQL ;)

----------
Hannu

Re: Vote totals for SET in aborted transaction

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-04-29T16:45:02Z

Marc G. Fournier wrote:
> On Mon, 29 Apr 2002, Bruce Momjian wrote:
> 
> > Hannu Krosing wrote:
> > > On Mon, 2002-04-29 at 17:09, Scott Marlowe wrote:
> > > > For this reason, I propose that a transaction should "inherit" its
> > > > environment, and that all changes EXCEPT for those affecting tuples should
> > > > be rolled back after completion, leaving the environment the way we found
> > > > it.  If you need the environment changed, do it OUTSIDE the transaction.
> > >
> > > Unfortunately there is no such time in postgresql where commands are
> > > done outside transaction.
> > >
> > > If you don't issue BEGIN; then each command is implicitly run in its own
> > > transaction.
> > >
> > > Rolling each command back unless it is in implicit transaction would
> > > really confuse the user.
> >
> > Agreed, very non-intuitive.  And can you imagine how many applications
> > we would break.
> 
> Since there is obviously no defined standard for how a SET should be
> treated within a transaction ... who cares?  God, how many changes have we
> made in the past that "break applications" but did them anyway?

Well, I think SET being always rolled back in a multi-statement
transaction is not the behavior most people would want.  I am sure there
are some cases people would want it, but I doubt it should be the
default.

> Just as a stupid question here ... but, why do we wrap single queries into
> a transaction anyway?  IMHO, a transaction is meant to tell the backend to
> remember this sequence of events, so that if it fails, you can roll it
> back ... with a single INSERT/UPDATE/DELETE, why 'auto-wrapper' it with a
> BEGIN/END?

Because INSERT/UPDATE/DELETE is actually INSERT/UPDATE/DELETE on every
effected row, with tiggers and all, so it is not as _single_ as it
appears.

-- 
  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

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T16:51:07Z

Thomas Lockhart <lockhart@fourpalms.org> writes:
>> I can certainly think of uses for a local-effects flavor of SET.
>> But I don't want that to be the only flavor.

> Right. And there was no suggestion that there be so; the original
> proposal used "BEGIN/END blocks" to differentiate the usage.

Right.  But I don't like the notion of making SET's behavior vary
depending on context.  I think it's better both from a user-friendliness
standpoint and from a compatibility standpoint to use different syntaxes
to indicate the desired behavior.

> Think about
> SET SESSION... as a possible syntax to completely decouple the behaviors
> if an explicit notation is desired.

Well, if you accept the notion of distinguishing it by syntax, then
we're down to arguing about which case should be associated with the
existing syntax.  And I think persistent has to win on compatibility
grounds.  (Doesn't the Perl DBI driver also do the automatic-begin
thing?  Breaking all Java apps and all Perl apps that issue SETs is
rather a big compatibility problem IMHO...)

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T17:04:52Z

Hannu Krosing <hannu@tm.ee> writes:
> If we go with your syntax I would prefer SET LOCAL to LOCAL SET , so
> that LOCAL feels tied more to variable rather than to SET .

I agree.  I was originally thinking that that way might require LOCAL to
become a reserved word, but we should be able to avoid it.

With Thomas' nearby suggestion of SET SESSION ..., we'd have

	SET [ SESSION | LOCAL ] varname TO value

and it only remains to argue which case is the default ;-)

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T17:09:52Z

Hannu Krosing <hannu@tm.ee> writes:
> And I also think that this will solve the original issue, which iirc was
> rolling back SET TIMEOUT at ABORT.

It does provide a way to deal with that problem.  But we still have the
example of

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

to mandate changing the behavior of plain SET to roll back on error.

> If we have LOCAL SET, there is no need to have any other mechanism for
> ROLLING BACK/COMMITing SET's - SET and DML can be kept totally separate,
> as they should be based on fact that SET does not directly affect data.

That can only work if you have no connection at all between SETs and
data that is in the database; which seems to me to be a rather large
restriction on what SET can be used for.  (In particular, search_path
couldn't be treated as a SET variable at all; we'd have to invent some
other specialized command for it.)

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Doug McNaught <doug@wireboard.com> — 2002-04-29T17:14:43Z

"Marc G. Fournier" <scrappy@hub.org> writes:

> Just as a stupid question here ... but, why do we wrap single queries into
> a transaction anyway?  IMHO, a transaction is meant to tell the backend to
> remember this sequence of events, so that if it fails, you can roll it
> back ... with a single INSERT/UPDATE/DELETE, why 'auto-wrapper' it with a
> BEGIN/END?

Well, a single query (from the user's perspective) may involve a
funciton call that itself executes one or more other queries.  I think
you want these to be under transactional control.

Plus, it's my understanding that the whole MVCC implementation depends
on "everything is in a transaction."

-Doug

Re: Vote totals for SET in aborted transaction

Hannu Krosing <hannu@tm.ee> — 2002-04-29T17:39:25Z

On Mon, 2002-04-29 at 17:53, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > Perhaps we could do 
> > SET SET TO LOCAL TO TRANSACTION;
> > Which would affect itself and all subsequent SET commands up to 
> > SET SET TO GLOBAL;
> > or end of transaction.
> 
> This makes my head hurt.  If I do
> 
> 	SET foo TO bar;
> 	begin;
> 	SET SET TO GLOBAL;
> 	SET foo TO baz;
> 	SET SET TO LOCAL TO TRANSACTION;
> 	end;
> 
> (assume no errors) what is the post-transaction state of foo?

should be baz

I'm elaborating the idea of SET with transaction scope here with
possibility to do global SETs as well. Any global SET will also affect
local set (by either setting it or just unsetting the local one).

> 
> What about this case?
> 
> 	SET foo TO bar;
> 	begin;
> 	SET SET TO GLOBAL;
> 	SET foo TO baz;
> 	SET SET TO LOCAL TO TRANSACTION;
> 	SET foo TO quux;
> 	end;

baz again, as local foo==quux disappears at transaction end
 
> Of course this last case also exists with my idea of a LOCAL SET
> command,
> 
> 	SET foo TO bar;
> 	begin;
> 	SET foo TO baz;
> 	LOCAL SET foo TO quux;
> 	-- presumably SHOW foo will show quux here
> 	end;
> 	-- does SHOW foo now show bar, or baz?

baz

I assume here only two kinds of SETs - global ones that happen always
and local ones that are valid only within the transaction

> Arguably you'd need to keep track of up to three values of a SET
> variable to make this work --- the permanent (pre-transaction) value,
> to roll back to if error;

I started from the idea of not rolling back SETs as they do not affect
data but I think that transaction-local SETs are valuable.

If we go with your syntax I would prefer SET LOCAL to LOCAL SET , so
that LOCAL feels tied more to variable rather than to SET .

> the SET value, which will become permanent
> if we commit; and the LOCAL SET value, which may mask the pending
> permanent value.  This seems needlessly complex though.  Could we get
> away with treating the above case as an error?
> 
> In any case I find a LOCAL SET command more reasonable than making
> SET's effects depend on the value of a SETtable setting.  There is
> circular logic there.  If I do
> 
> 	begin;
> 	SET SET TO LOCAL TO TRANSACTION;
> 	end;
> 
> what is the post-transaction behavior of SET?

It is always GLOBAL unless SET TO LOCAL

I explicitly defined this command as applying to itself and all
following commands in order to avoid this circularity so END would
invalidate it

But I already think that LOCAL SET / SET LOCAL is better and more clear.

> And if you say LOCAL,
> how do you justify it?  Why wouldn't the effects of this SET be local?

------------
Hannu

Re: Vote totals for SET in aborted transaction

Hannu Krosing <hannu@tm.ee> — 2002-04-29T17:47:54Z

On Mon, 2002-04-29 at 18:20, Tom Lane wrote:
> Thomas Lockhart <lockhart@fourpalms.org> writes:
> > Rather than dismissing this out of hand, try to look at what it *does*
> > enable. It allows developers to tune specific queries without having to
> > restore values afterwards. Values or settings which may change from
> > version to version, so end up embedding time bombs into applications.
> 
> I think it's a great idea. 

So do I. 

And I also think that this will solve the original issue, which iirc was
rolling back SET TIMEOUT at ABORT.

If we have LOCAL SET, there is no need to have any other mechanism for
ROLLING BACK/COMMITing SET's - SET and DML can be kept totally separate,
as they should be based on fact that SET does not directly affect data.

--------------
Hannu

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-29T18:10:13Z

What happens inside of a nested transaction, assuming we do have those
evenually ... ?

On Mon, 29 Apr 2002, Tom Lane wrote:

> Hannu Krosing <hannu@tm.ee> writes:
> > Perhaps we could do
> > SET SET TO LOCAL TO TRANSACTION;
> > Which would affect itself and all subsequent SET commands up to
> > SET SET TO GLOBAL;
> > or end of transaction.
>
> This makes my head hurt.  If I do
>
> 	SET foo TO bar;
> 	begin;
> 	SET SET TO GLOBAL;
> 	SET foo TO baz;
> 	SET SET TO LOCAL TO TRANSACTION;
> 	end;
>
> (assume no errors) what is the post-transaction state of foo?
>
> What about this case?
>
> 	SET foo TO bar;
> 	begin;
> 	SET SET TO GLOBAL;
> 	SET foo TO baz;
> 	SET SET TO LOCAL TO TRANSACTION;
> 	SET foo TO quux;
> 	end;
>
> Of course this last case also exists with my idea of a LOCAL SET
> command,
>
> 	SET foo TO bar;
> 	begin;
> 	SET foo TO baz;
> 	LOCAL SET foo TO quux;
> 	-- presumably SHOW foo will show quux here
> 	end;
> 	-- does SHOW foo now show bar, or baz?
>
> Arguably you'd need to keep track of up to three values of a SET
> variable to make this work --- the permanent (pre-transaction) value,
> to roll back to if error; the SET value, which will become permanent
> if we commit; and the LOCAL SET value, which may mask the pending
> permanent value.  This seems needlessly complex though.  Could we get
> away with treating the above case as an error?
>
> In any case I find a LOCAL SET command more reasonable than making
> SET's effects depend on the value of a SETtable setting.  There is
> circular logic there.  If I do
>
> 	begin;
> 	SET SET TO LOCAL TO TRANSACTION;
> 	end;
>
> what is the post-transaction behavior of SET?  And if you say LOCAL,
> how do you justify it?  Why wouldn't the effects of this SET be local?
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-29T18:13:44Z

On Mon, 29 Apr 2002, Tom Lane wrote:

> Hannu Krosing <hannu@tm.ee> writes:
> > If we go with your syntax I would prefer SET LOCAL to LOCAL SET , so
> > that LOCAL feels tied more to variable rather than to SET .
>
> I agree.  I was originally thinking that that way might require LOCAL to
> become a reserved word, but we should be able to avoid it.
>
> With Thomas' nearby suggestion of SET SESSION ..., we'd have
>
> 	SET [ SESSION | LOCAL ] varname TO value
>
> and it only remains to argue which case is the default ;-)

Ah, I do like the syntax ... and would go with SESSION as default, but
that is based on me tinking about how 'local' variables work in perl,
where if you don't explicitly state its local, its automatically global
...

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T18:19:14Z

"Marc G. Fournier" <scrappy@hub.org> writes:
> What happens inside of a nested transaction, assuming we do have those
> evenually ... ?

Presumably, an error inside a nested transaction would cause you to
revert back to whatever the SET situation was at start of that
subtransaction.

Offhand this doesn't seem any harder than any other part of what we'd
have to do for nested transactions.

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Jan Wieck <janwieck@yahoo.com> — 2002-04-29T18:39:38Z

Marc G. Fournier wrote:
>
> What happens inside of a nested transaction, assuming we do have those
> evenually ... ?

Folks,

    I  don't  really  get it. We had a voting and I think I saw a
    clear enough result with #1, transactional behaviour, as  the
    winner.    Maybe   I   missed   something,  but  what's  this
    disscussion about?

Jan

--

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

Re: Vote totals for SET in aborted transaction

Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T18:49:27Z

Jan Wieck <janwieck@yahoo.com> writes:
>     I  don't  really  get it. We had a voting and I think I saw a
>     clear enough result with #1, transactional behaviour, as  the
>     winner.    Maybe   I   missed   something,  but  what's  this
>     disscussion about?

We agreed on transactional behavior ... but Scott is proposing a variant
that was not considered earlier, and it seems worth considering.

			regards, tom lane

Re: Vote totals for SET in aborted transaction

Marc G. Fournier <scrappy@hub.org> — 2002-04-29T18:52:19Z

On Mon, 29 Apr 2002, Jan Wieck wrote:

> Marc G. Fournier wrote:
> >
> > What happens inside of a nested transaction, assuming we do have those
> > evenually ... ?
>
> Folks,
>
>     I  don't  really  get it. We had a voting and I think I saw a
>     clear enough result with #1, transactional behaviour, as  the
>     winner.    Maybe   I   missed   something,  but  what's  this
>     disscussion about?

This discussion is about a #4 option that nobody considered ...

Re: Vote totals for SET in aborted transaction

Thomas Lockhart <lockhart@fourpalms.org> — 2002-04-29T20:06:48Z

> I  don't  really  get it. We had a voting and I think I saw a
> clear enough result with #1, transactional behaviour, as  the
> winner.    Maybe   I   missed   something,  but  what's  this
> disscussion about?

Getting the right solution ;)

There was not a consensus, just a vote, and the *reasons* for the lack
of consensus were not yet being addressed. They are now (or some are
anyway), and the new proposal helped set that in motion.

I would think that a vote in the absence of consensus is not always
optimal (I'll leave aside stating my view on this case ;), but it has
helped focus the discussion. It is always amazing to me how threads
emerge which bring a consensus when there wasn't even one on the
horizon.

                    - Thomas

Re: Vote totals for SET in aborted transaction

John Ingram <jingram@ncinter.net> — 2002-04-30T02:55:56Z

----- Original Message -----
From: "Marc G. Fournier" <scrappy@hub.org>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Hannu Krosing" <hannu@tm.ee>; "Scott Marlowe" <scott.marlowe@ihs.com>;
"PostgreSQL-development" <pgsql-hackers@postgresql.org>
Sent: Monday, April 29, 2002 2:10 PM
Subject: Re: [HACKERS] Vote totals for SET in aborted transaction

LOCAL <NESTED TRANSACTION NAME> SET  ....  ?

>>

>
> What happens inside of a nested transaction, assuming we do have those
> evenually ... ?
>
> On Mon, 29 Apr 2002, Tom Lane wrote:
>
> > Hannu Krosing <hannu@tm.ee> writes:
> > > Perhaps we could do
> > > SET SET TO LOCAL TO TRANSACTION;
> > > Which would affect itself and all subsequent SET commands up to
> > > SET SET TO GLOBAL;
> > > or end of transaction.
> >
> > This makes my head hurt.  If I do
> >
> > SET foo TO bar;
> > begin;
> > SET SET TO GLOBAL;
> > SET foo TO baz;
> > SET SET TO LOCAL TO TRANSACTION;
> > end;
> >
> > (assume no errors) what is the post-transaction state of foo?
> >
> > What about this case?
> >
> > SET foo TO bar;
> > begin;
> > SET SET TO GLOBAL;
> > SET foo TO baz;
> > SET SET TO LOCAL TO TRANSACTION;
> > SET foo TO quux;
> > end;
> >
> > Of course this last case also exists with my idea of a LOCAL SET
> > command,
> >
> > SET foo TO bar;
> > begin;
> > SET foo TO baz;
> > LOCAL SET foo TO quux;
> > -- presumably SHOW foo will show quux here
> > end;
> > -- does SHOW foo now show bar, or baz?
> >
> > Arguably you'd need to keep track of up to three values of a SET
> > variable to make this work --- the permanent (pre-transaction) value,
> > to roll back to if error; the SET value, which will become permanent
> > if we commit; and the LOCAL SET value, which may mask the pending
> > permanent value.  This seems needlessly complex though.  Could we get
> > away with treating the above case as an error?
> >
> > In any case I find a LOCAL SET command more reasonable than making
> > SET's effects depend on the value of a SETtable setting.  There is
> > circular logic there.  If I do
> >
> > begin;
> > SET SET TO LOCAL TO TRANSACTION;
> > end;
> >
> > what is the post-transaction behavior of SET?  And if you say LOCAL,
> > how do you justify it?  Why wouldn't the effects of this SET be local?
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>