Re: [HACKERS] Vote totals for SET in aborted transaction
- Original Message - From: "Marc G. Fournier" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: "Hannu Krosing" <[EMAIL PROTECTED]>; "Scott Marlowe" <[EMAIL PROTECTED]>; "PostgreSQL-development" <[EMAIL PROTECTED]> Sent: Monday, April 29, 2002 2:10 PM Subject: Re: [HACKERS] Vote totals for SET in aborted transaction LOCAL SET ? >> > > What happens inside of a nested transaction, assuming we do have those > evenually ... ? > > On Mon, 29 Apr 2002, Tom Lane wrote: > > > Hannu Krosing <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) > > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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 ... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
Jan Wieck <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
"Marc G. Fournier" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
On Mon, 29 Apr 2002, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> 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 ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
What happens inside of a nested transaction, assuming we do have those evenually ... ? On Mon, 29 Apr 2002, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
"Marc G. Fournier" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Hannu Krosing <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
Hannu Krosing <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
Thomas Lockhart <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
On Mon, 2002-04-29 at 18:20, Tom Lane wrote: > Thomas Lockhart <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
On Mon, 2002-04-29 at 17:53, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
On Mon, 29 Apr 2002, Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> 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 ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Thomas Lockhart <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
Thomas Lockhart <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Hannu Krosing <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
... > 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
... > 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
On Mon, 2002-04-29 at 17:30, Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
Scott Marlowe <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
> 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
On Fri, 26 Apr 2002, Bruce Momjian wrote:
> Tom Lane wrote:
> > Lincoln Yeoh <[EMAIL PROTECTED]> 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 ...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Lincoln Yeoh <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
At 10:34 AM 4/26/02 -0400, Tom Lane wrote: >Lincoln Yeoh <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
Jan Wieck <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Tom Lane wrote:
> Lincoln Yeoh <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED] | (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 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
Lincoln Yeoh <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
Marc G. Fournier wrote: > On Fri, 26 Apr 2002, Jan Wieck wrote: > > > Bruce Momjian wrote: > > > Marc G. Fournier wrote: > > > > On Thu, 25 Apr 2002, Bruce Momjian wrote: > > > > > > > > > > > > > > Marc is suggesting we may want to match Oracle somehow. > > > > > > > > > > I just want to have our SET work on a sane manner. > > > > > > > > Myself, I wonder why Oracle went the route they went ... does anyone have > > > > access to a Sybase / Informix system, to confirm how they do it? Is > > > > Oracle the 'odd man out', or are we going to be that? *Adding* something > > > > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ... > > > > but changing the behaviour is a totally different ... > > > > > > Yes, let's find out what the others do. I don't see DROP TABLE > > > rollbacking as totally different. How is it different from SET? > > > > Man, you should know that our transactions are truly all or > > nothing. If you discard a transaction, the stamps xmin and > > xmax are ignored. This is a fundamental feature of Postgres, > > and if you're half through a utility command when you ERROR > > out, it guarantees consistency of the catalog. And now you > > want us to violate this concept for compatibility to Oracle's > > misbehaviour? No, thanks! > > How does SET relate to xmin/xmax? :) > SET does not. But Bruce said he doesn't see DROP TABLE beeing totally different. That is related to xmin/xmax, isn't it? What I pointed out (or wanted to point out) is, that we cannot ignore rollback for catalog changes like DROP TABLE. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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? :) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Bruce Momjian wrote: > Marc G. Fournier wrote: > > On Thu, 25 Apr 2002, Bruce Momjian wrote: > > > > > > > > Marc is suggesting we may want to match Oracle somehow. > > > > > > I just want to have our SET work on a sane manner. > > > > Myself, I wonder why Oracle went the route they went ... does anyone have > > access to a Sybase / Informix system, to confirm how they do it? Is > > Oracle the 'odd man out', or are we going to be that? *Adding* something > > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ... > > but changing the behaviour is a totally different ... > > Yes, let's find out what the others do. I don't see DROP TABLE > rollbacking as totally different. How is it different from SET? Man, you should know that our transactions are truly all or nothing. If you discard a transaction, the stamps xmin and xmax are ignored. This is a fundamental feature of Postgres, and if you're half through a utility command when you ERROR out, it guarantees consistency of the catalog. And now you want us to violate this concept for compatibility to Oracle's misbehaviour? No, thanks! Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
Marc G. Fournier writes: > Myself, I wonder why Oracle went the route they went ... does anyone have > access to a Sybase / Informix system, to confirm how they do it? Is > Oracle the 'odd man out', or are we going to be that? *Adding* something > (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ... > but changing the behaviour is a totally different .. FWIW, Ingres also doesn't rollback SET. However all its SET functionality is the sort of stuff you wouldn't assume to rollback: auto-commit connection journaling logging session work locations maxidle You cannot do something sane like modify the date output through SET. Lee. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
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 <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
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 ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net > > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > > Online Campground Directoryhttp://www.camping-usa.com > >Online Giftshop Superstorehttp://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 > [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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 -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
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 -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directoryhttp://www.camping-usa.com >Online Giftshop Superstorehttp://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 [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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 -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
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 ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 = ' 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 ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
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 = ' 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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: SET DEFAULT (read again from .conf) | SET 'value' (might fail, fallback to .conf) | NO ACTION (ignore rollback) | ROLLBACK(return to value before transaction) Also, we should make all these settings DB dependant and be able to specify the configure settings in the .conf file, so that two databases running under the same postmaster bahave completely different, just to make the confusion perfect for every client. And for everyone who didn't get it, this was sarcasm! Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
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 > > [EMAIL PROTECTED] | (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 > [EMAIL PROTECTED] | (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 > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted 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 Inoue http://w2422.nsk.ne.jp/~inoue/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > > > > > > > > > > > I voted not only ? but also 2 and 3. > > > > > > And haven't I asked twice or so if it's a vote ? > > > > > > > > > > Yes, it is a vote, and now that we see how everyone feels, we can > > > > > decide what to do. > > > > > > > > > > Hiroshi, you can't vote for 2, 3, and ?. > > > > > > > > Why ? > > > > I don't think the items are exclusive. > > > > > > Well, 2 says roll back only after transaction aborts, > > > > Sorry for my poor understanding. > > Isn't it 1 ? > > OK, original email attached. 1 rolls back all SETs in an aborted > transaction. > 2 ignores SETs after transaction aborts, but SETs before > the transaction aborted are honored. Must I understand this from your previous posting (2 says roll back only after transaction aborts,) or original posting ? What I understood was 2 only says that SET fails between a failure and the subsequenct ROLLBACK call. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > > > > > > > > > I voted not only ? but also 2 and 3. > > > > > And haven't I asked twice or so if it's a vote ? > > > > > > > > Yes, it is a vote, and now that we see how everyone feels, we can > > > > decide what to do. > > > > > > > > Hiroshi, you can't vote for 2, 3, and ?. > > > > > > Why ? > > > I don't think the items are exclusive. > > > > Well, 2 says roll back only after transaction aborts, > > Sorry for my poor understanding. > Isn't it 1 ? OK, original email attached. 1 rolls back all SETs in an aborted transaction. 2 ignores SETs after transaction aborts, but SETs before the transaction aborted are honored. 3 honors all SETs. --- In the case of: SET x=1; BEGIN; SET x=2; query_that_aborts_transaction; SET x=3; COMMIT; at the end, should 'x' equal: 1 - All SETs are rolled back in aborted transaction 2 - SETs are ignored after transaction abort 3 - All SETs are honored in aborted transaction ? - Have SETs vary in behavior depending on variable Our current behavior is 2. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
Bruce Momjian wrote: > > > > > > > > > I voted not only ? but also 2 and 3. > > > > And haven't I asked twice or so if it's a vote ? > > > > > > Yes, it is a vote, and now that we see how everyone feels, we can > > > decide what to do. > > > > > > Hiroshi, you can't vote for 2, 3, and ?. > > > > Why ? > > I don't think the items are exclusive. > > Well, 2 says roll back only after transaction aborts, Sorry for my poor understanding. Isn't it 1 ? regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
Bruce Momjian wrote: > > OK, the votes are in: > > #1 > Lamar Owen > Jan Wieck > Tom Lane > Bruce Momjian > Joe Conway > Curt Sampson > Michael Loftis > Vince Vielhaber > Sander Steffann > > #2 > Bradley McLean > > > > #3 > > #? > Thomas Lockhart > Hiroshi Inoue > > Looks like #1 is the clear winner. I voted not only ? but also 2 and 3. And haven't I asked twice or so if it's a vote ? Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
