Thread: Vote on SET in aborted transaction
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 transaction2 - SETs are ignored after transaction abort3- 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, Pennsylvania19026
* Bruce Momjian (pgman@candle.pha.pa.us) [020423 12:30]: > > 1 - All SETs are rolled back in aborted transaction > 2 - SETs are ignored after transaction abort > 3 - All SETs are honored in aborted transaction > ? - Have SETs vary in behavior depending on variable > > Our current behavior is 2. > > Please vote and I will tally the results. #2, no change in behavior. But I base that on the assumption that #1 or #3 involve serious amounts of work, and don't see the big benefit. I liked the line of thought that was distinguishing between in-band (rolled back) and out-of-band (honored) SETs, although I don't think any acceptable syntax was arrived at, and I don't have a suggestion. If this were solved, I'd vote for '?'. Hmm. Maybe I do have a suggestion: SET [TRANSACTIONAL] ... But it might not be very practical. -Brad
Bradley McLean wrote: > * Bruce Momjian (pgman@candle.pha.pa.us) [020423 12:30]: > > > > 1 - All SETs are rolled back in aborted transaction > > 2 - SETs are ignored after transaction abort > > 3 - All SETs are honored in aborted transaction > > ? - Have SETs vary in behavior depending on variable > > > > Our current behavior is 2. > > > > Please vote and I will tally the results. > > #2, no change in behavior. > > But I base that on the assumption that #1 or #3 involve serious amounts > of work, and don't see the big benefit. I don't want to make any big comments during the vote, but I should mention that #1 is needed by Tom's SET for namespace path, and #1 or #3 is needed to clearly handle query timeouts. Just thought I would refresh people's memory on how this discussion got started. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
1 SET should follow transaction semantics and rollback. Jan Bruce Momjian wrote: > OK, would people please vote on how to handle SET in an aborted > transaction? This vote will allow us to resolve the issue and move > forward if needed. > > In the case of: > > SET x=1; > BEGIN; > SET x=2; > query_that_aborts_transaction; > SET x=3; > COMMIT; > > at the end, should 'x' equal: > > 1 - All SETs are rolled back in aborted transaction > 2 - SETs are ignored after transaction abort > 3 - All SETs are honored in aborted transaction > ? - Have SETs vary in behavior depending on variable > > Our current behavior is 2. > > Please vote and I will tally the results. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 1 - All SETs are rolled back in aborted transaction
> 2 - SETs are ignored after transaction abort
> 3 - All SETs are honored in aborted transaction
> ? - Have SETs vary in behavior depending on variable
My vote is 1 - roll back all SETs.
I'd be willing to consider making the behavior variable-specific
if anyone can identify particular variables that need to behave
differently. But overall I think it's better that the behavior
be consistent --- so you'll need a good argument to convince me
that anything should behave differently ;-).
There is a variant case that should also have been illustrated:
what if there is no error, but the user does ROLLBACK instead of
COMMIT? The particular case that is causing difficulty for me is
begin;create schema foo;set search_path = foo;rollback;
There is *no* alternative here but to roll back the search_path
setting. Therefore, the only alternatives that actually count
are 1 and ? --- if you don't like 1 then you are voting for
variable-specific behavior, because search_path is going to behave
this way whether you like it or not.
regards, tom lane
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
Bruce Momjian wrote: > OK, would people please vote on how to handle SET in an aborted > transaction? This vote will allow us to resolve the issue and move > forward if needed. > > In the case of: > > SET x=1; > BEGIN; > SET x=2; > query_that_aborts_transaction; > SET x=3; > COMMIT; > > at the end, should 'x' equal: > > 1 - All SETs are rolled back in aborted transaction > 2 - SETs are ignored after transaction abort > 3 - All SETs are honored in aborted transaction > ? - Have SETs vary in behavior depending on variable > > Our current behavior is 2. 1 makes the most sense to me. I think it should be consistent for all SET variables. Joe
Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > 1 - All SETs are rolled back in aborted transaction > > 2 - SETs are ignored after transaction abort > > 3 - All SETs are honored in aborted transaction > > ? - Have SETs vary in behavior depending on variable > > My vote is 1 - roll back all SETs. Hmm I don't understand which to vote, sorry. Are they all exclusive in the first place ? > I'd be willing to consider making the behavior variable-specific > if anyone can identify particular variables that need to behave > differently. But overall I think it's better that the behavior > be consistent --- so you'll need a good argument to convince me > that anything should behave differently ;-). > > There is a variant case that should also have been illustrated: > what if there is no error, but the user does ROLLBACK instead of > COMMIT? The particular case that is causing difficulty for me is > > begin; > create schema foo; > set search_path = foo; > rollback; > > There is *no* alternative here but to roll back the search_path > setting. begin;xxxx;ERROR: parser: parse error at or near "xxxx" There's *no* alternative here but to call *rollback*(commit). However PostgreSQL doesn't call *rollback* automatically and it's the user's responsibility to call *rollback* on errors. IMHO what to do with errors is users' responsibility basically. The behavior of the *search_path" variable is a *had better* or *convenient* kind of thing not a *no alternative* kind of thing. regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue wrote: > > I'd be willing to consider making the behavior variable-specific > > if anyone can identify particular variables that need to behave > > differently. But overall I think it's better that the behavior > > be consistent --- so you'll need a good argument to convince me > > that anything should behave differently ;-). > > > > There is a variant case that should also have been illustrated: > > what if there is no error, but the user does ROLLBACK instead of > > COMMIT? The particular case that is causing difficulty for me is > > > > begin; > > create schema foo; > > set search_path = foo; > > rollback; > > > > There is *no* alternative here but to roll back the search_path > > setting. > > begin; > xxxx; > ERROR: parser: parse error at or near "xxxx" > > There's *no* alternative here but to call *rollback*(commit). > However PostgreSQL doesn't call *rollback* automatically and > it's the user's responsibility to call *rollback* on errors. > IMHO what to do with errors is users' responsibility basically. > The behavior of the *search_path" variable is a *had better* > or *convenient* kind of thing not a *no alternative* kind > of thing. I understand from an ODBC perspective that it is the apps responsibility, but we need some defined behavior for a psql script that is fed into the database. Assuming the SET commands continue to come after it is aborted but before the COMMIT/ROLLBACK, we need to define how to handle it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> OK, would people please vote on how to handle SET in an aborted
> transaction?
> at the end, should 'x' equal:
> 1 - All SETs are rolled back in aborted transaction
> 2 - SETs are ignored after transaction abort
> 3 - All SETs are honored in aborted transaction
> ? - Have SETs vary in behavior depending on variable
I'll vote for "?", if for no other reason that you are proposing taking
away a huge chunk of "language space" by apriori disallowing out of band
behaviors for anything starting with "SET". I think that is likely
Hiroshi's concern also.
If we can fit all current "SET" behaviors into a transaction model, then
I'm not against that (though we should review the list of attributes
which *are* currently affected before settling on this). afaik we have
not reviewed current behaviors and have not thought through the "what
if's" that some soft of premature policy decision might constrain in the
future.
Let me give you some examples. We might someday have nested
transactions, or transactions which can be resumed from the point of
failure. We *might* want to be able to affect recovery behaviors, and we
*might* want to do so with something like
begin;
update foo...
update bar...
<last update fails>
set blah to blah
update baz...
update bar...
<last update now succeeds>
end;
Now we currently *don't* support this behavior, but istm that we
shouldn't preclude it in the language by forcing some blanket "all SET
statements will be transaction aware".
What language elements would you propose to cover the out of band cases
if you *do* disallow "SET" in that context? If you don't have a
candidate, I'd be even more reluctant to go along with the results of
some arbitrary vote which is done in a narrow context.
And btw, if we *are* going to put transaction semantics on all of our
global variables (which is the context for starting this "SET"
discussion, right? Is that really the context we are still in, even
though you have phrased a much more general statement above?) then let's
have the discussion on *HOW* we are going to accomplish that *BEFORE*
deciding to make a semantic constraint on our language support.
Hmm, if we are going to use transaction semantics, then we should
consider using our existing transaction mechanisms, and if we use our
existing transaction mechanisms we should consider pushing these global
variables into tables or in memory tables a la "temp tables". We get the
transaction semantics for free, with the cost of value lookup at the
beginning of a transaction or statement (not sure what we can get away
with here).
If we are *not* going to use those existing mechanisms, then what
mechanism *are* we going to use? Some sort of "abort hook" mechanism to
allow SET to register things to be rolled back?
If we end up making changes and increasing constraints, then we should
also expect some increased functionality as part of the scheme,
specifically "SET extensibility". We should allow (future) packages to
define their parameters and allow SET to help.
Just some thoughts...
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
> Let me give you some examples. We might someday have nested
> transactions, or transactions which can be resumed from the point of
> failure. We *might* want to be able to affect recovery behaviors, and we
> *might* want to do so with something like
> begin;
> update foo...
> update bar...
> <last update fails>
> set blah to blah
> update baz...
> update bar...
> <last update now succeeds>
> end;
Sure, once we have savepoints or nested transactions I would expect SET
to work like that. The "alternative 1" should better be phrased as
"SETs should work the same way as regular SQL commands do".
I agree with your comment that it would be useful to look closely at the
list of settable variables to see whether any of them need different
semantics. Here's the list of everything that can be SET after backend
start (some of these require superuser privilege to set, but that seems
irrelevant):
datestyle
timezone
XactIsoLevel
client_encoding
server_encoding
seed
session_authorization
enable_seqscan
enable_indexscan
enable_tidscan
enable_sort
enable_nestloop
enable_mergejoin
enable_hashjoin
ksqo
geqo
debug_assertions
debug_print_query
debug_print_parse
debug_print_rewritten
debug_print_plan
debug_pretty_print
show_parser_stats
show_planner_stats
show_executor_stats
show_query_stats
show_btree_build_stats
explain_pretty_print
stats_command_string
stats_row_level
stats_block_level
trace_notify
trace_locks
trace_userlocks
trace_lwlocks
debug_deadlocks
sql_inheritance
australian_timezones
password_encryption
transform_null_equals
geqo_threshold
geqo_pool_size
geqo_effort
geqo_generations
geqo_random_seed
sort_mem
vacuum_mem
trace_lock_oidmin
trace_lock_table
max_expr_depth
wal_debug
commit_delay
commit_siblings
effective_cache_size
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
geqo_selection_bias
client_min_messages
default_transaction_isolation
dynamic_library_path
search_path
server_min_messages
Right offhand, I am not seeing anything here for which there's a
compelling case not to roll it back on error.
In fact, I have yet to hear *any* plausible example of a variable
that we would really seriously want not to roll back on error.
> And btw, if we *are* going to put transaction semantics on all of our
> global variables (which is the context for starting this "SET"
> discussion, right? Is that really the context we are still in, even
> though you have phrased a much more general statement above?) then let's
> have the discussion on *HOW* we are going to accomplish that *BEFORE*
> deciding to make a semantic constraint on our language support.
Hardly necessary: we'll just make guc.c keep track of the
start-of-transaction values of all variables that have changed in the
current transaction, and restore them to that value upon transaction
abort. Doesn't seem like a big deal to me. We've got tons of other
code that does exactly the same sort of thing.
> Hmm, if we are going to use transaction semantics, then we should
> consider using our existing transaction mechanisms, and if we use our
> existing transaction mechanisms we should consider pushing these global
> variables into tables or in memory tables a la "temp tables".
Quite a few of the GUC settings are values that need to be set and used
during startup, before we have table access up and running. I do not
think that it's very practical to expect them to be accessed through
table access mechanisms.
> If we end up making changes and increasing constraints, then we should
> also expect some increased functionality as part of the scheme,
> specifically "SET extensibility".
It might well be a good idea to allow variables to be added to guc.c's
lists on-the-fly by the initialization routines of loadable modules.
But that's orthogonal to this discussion, IMHO.
regards, tom lane
Vote number 1 -- ROLL BACK Bruce Momjian wrote: >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. >
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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Bruce Momjian wrote: > > OK, would people please vote on how to handle SET in an aborted > transaction? This vote will allow us to resolve the issue and move > forward if needed. > > In the case of: > > SET x=1; > BEGIN; > SET x=2; > query_that_aborts_transaction; > SET x=3; > COMMIT; > > at the end, should 'x' equal: > > 1 - All SETs are rolled back in aborted transaction > 2 - SETs are ignored after transaction abort > 3 - All SETs are honored in aborted transaction > ? - Have SETs vary in behavior depending on variable > > Our current behavior is 2. > > Please vote and I will tally the results. Is it a vote in the first place ? I will vote the current(2 + 3 + ?). regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Tom Lane wrote: > > Right offhand, I am not seeing anything here for which there's a > compelling case not to roll it back on error. > > In fact, I have yet to hear *any* plausible example of a variable > that we would really seriously want not to roll back on error. Honetsly I don't understand what kind of example you expect. How about the following ? [The curren schema is schema1] begin;create schema foo;set search_path = foo;create table t1 (....);. [error occurs]rollback;insert into t1 select * fromschema1.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 Inouehttp://w2422.nsk.ne.jp/~inoue/
OK, the votes are in:#1Lamar OwenJan WieckTom LaneBruce MomjianJoe ConwayCurt SampsonMichael LoftisVince VielhaberSanderSteffann #2Bradley McLean #3#?Thomas LockhartHiroshi 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, Pennsylvania19026
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 connectionuntil the end? I don't think so! Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > > Hiroshi Inoue wrote: > > Tom Lane wrote: > > > > > > > > Right offhand, I am not seeing anything here for which there's a > > > compelling case not to roll it back on error. > > > > > > In fact, I have yet to hear *any* plausible example of a variable > > > that we would really seriously want not to roll back on error. > > > > Honetsly I don't understand what kind of example you > > expect. How about the following ? > > > > [The curren schema is schema1] > > > > begin; > > create schema foo; > > set search_path = foo; > > create table t1 (....); > > . > > [error occurs] > > rollback; > > insert into t1 select * from schema1.t1; > > > > Should the search_path be put back in this case ? > > As I mentioned already many times, it doesn't seem > > *should be* kind of thing. > > Sure should it! You gave an example for the need to roll > back, because > otherwise you would end up with an invalid > search path "foo". What's wrong with it ? The insert command after *rollback* would fail. It seems the right thing to me. Otherwise the insert command would try to append the data of the table t1 to itself. The insert command is for copying schema1.t1 to foo.t1 in case the previous create schema command suceeded. regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Honetsly I don't understand what kind of example you
> expect. How about the following ?
> [The curren schema is schema1]
> begin;
> create schema foo;
> set search_path = foo;
> create table t1 (....);
> .
> [error occurs]
> rollback;
> insert into t1 select * from schema1.t1;
> Should the search_path be put back in this case ?
Sure it should be. Otherwise it's pointing at a nonexistent schema.
regards, tom lane
Bruce Momjian wrote: > > OK, the votes are in: > > #1 > Lamar Owen > Jan Wieck > Tom Lane > Bruce Momjian > Joe Conway > Curt Sampson > Michael Loftis > Vince Vielhaber > Sander Steffann > > #2 > Bradley McLean > > > > #3 > > #? > Thomas Lockhart > Hiroshi Inoue > > Looks like #1 is the clear winner. I voted not only ? but also 2 and 3. And haven't I asked twice or so if it's a vote ? Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > OK, the votes are in: > > > > #1 > > Lamar Owen > > Jan Wieck > > Tom Lane > > Bruce Momjian > > Joe Conway > > Curt Sampson > > Michael Loftis > > Vince Vielhaber > > Sander Steffann > > > > #2 > > Bradley McLean > > > > > > > > #3 > > > > #? > > Thomas Lockhart > > Hiroshi Inoue > > > > Looks like #1 is the clear winner. > > I voted not only ? but also 2 and 3. > And haven't I asked twice or so if it's a vote ? Yes, it is a vote, and now that we see how everyone feels, we can decide what to do. Hiroshi, you can't vote for 2, 3, and ?. Please pick one. I picked '?' for you because it seemed the closest to your intent. I can put you down for 1/3 of a vote for all three if you wish. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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
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, Pennsylvania19026
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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 -
AllSETs 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,
Pennsylvania19026
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) >
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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, Pennsylvania19026
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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, Pennsylvania19026
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 #
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.
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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 #
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?
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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
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, Pennsylvania19026
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 Inouehttp://w2422.nsk.ne.jp/~inoue/
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, Pennsylvania19026
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
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 >
> 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
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,
fallbackto .conf) | NO ACTION (ignore rollback) | ROLLBACK (return
tovalue 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
confusionperfect 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 #
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, Pennsylvania19026
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
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 ...
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, Pennsylvania19026
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 ...
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, Pennsylvania19026
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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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, Pennsylvania19026
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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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 >
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, Pennsylvania19026
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, Pennsylvania19026
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 ...
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.
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, Pennsylvania19026
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 alllight. --XTC
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 totallydifferent .. FWIW, Ingres also doesn't rollback SET. However all its SET functionality is the sort of stuff you wouldn't assume to rollback: auto-commitconnectionjournalingloggingsessionwork locationsmaxidle You cannot do something sane like modify the date output through SET. Lee.
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 compatibilityto 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 #
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 #
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? :)
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 likeDROP 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 #
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
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, Pennsylvania19026
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,
Pennsylvania19026
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
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
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 #
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
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 ...
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, Pennsylvania19026
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.
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.
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
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
> 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.
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
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, Pennsylvania19026
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
...
> 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
...
> 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
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 hereend;-- does SHOW
foonow 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
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
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
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 >
> 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
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 ?
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?
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
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, Pennsylvania19026
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
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
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
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
"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
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) >
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 ...
"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
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 #
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
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 ...
> 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
----- 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 >