Thread: SET LOCAL again

Earlier I had argued that SET LOCAL should not be used in the context it
is now, and I had suggested SET TRANSACTION as a replacement.  However,
now that I look at it in the implementation, this syntax is just too
bizzare and prone to confuse.

Here are a couple of examples of what is/would be possible.

SET SESSION SESSION AUTHORIZATION

(This is semantically valid, since the parameter is the "session
authorization" and you want it to last for the session.)

SET TRANSACTION SESSION AUTHORIZATION

(Clearly confusing)

SET SESSION TRANSACTION ISOLATION LEVEL

(Syntactically valid, but nonsensical.)

SET TRANSACTION TRANSACTION ISOLATION LEVEL

(Stupid)

SET TRANSACTION ISOLATION LEVEL

(This seems to imply that the parameter name is "isolation level" whereas
in fact the "transaction" belongs to the parameter name.)

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
SET TRANSACTION SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL

(OK, you get the idea...)

As an alternative syntax I can suggest

SET name TO value [ ON COMMIT RESET ];

I think this is painfully clear, is similar to other SQL standard
commands, and draws on existing terminology (COMMIT/RESET).  OK, slightly
more typing, I guess.

Comments?

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> As an alternative syntax I can suggest

> SET name TO value [ ON COMMIT RESET ];

Ugh.  Why can't we stick with SET LOCAL?
        regards, tom lane

Tom Lane writes:

> > As an alternative syntax I can suggest
>
> > SET name TO value [ ON COMMIT RESET ];
>
> Ugh.  Why can't we stick with SET LOCAL?

SET LOCAL is already used for something else in the SQL standard.  Not
sure if we'll ever implement that, but it's something to be concerned
about.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
> As an alternative syntax I can suggest
>> 
> SET name TO value [ ON COMMIT RESET ];
>> 
>> Ugh.  Why can't we stick with SET LOCAL?

> SET LOCAL is already used for something else in the SQL standard.  Not
> sure if we'll ever implement that, but it's something to be concerned
> about.

Actually, it looks to me like the spec's SET LOCAL has a compatible
interpretation: it only affects the current transaction.

My main gripe with "ON COMMIT RESET" is that it's a misleading
description of what will happen --- RESETting a variable is quite
different from allowing it to revert to the pre-transaction state.
        regards, tom lane

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Tom Lane writes:
> > As an alternative syntax I can suggest
> >> 
> > SET name TO value [ ON COMMIT RESET ];
> >> 
> >> Ugh.  Why can't we stick with SET LOCAL?
> 
> > SET LOCAL is already used for something else in the SQL standard.  Not
> > sure if we'll ever implement that, but it's something to be concerned
> > about.
> 
> Actually, it looks to me like the spec's SET LOCAL has a compatible
> interpretation: it only affects the current transaction.
> 
> My main gripe with "ON COMMIT RESET" is that it's a misleading
> description of what will happen --- RESETting a variable is quite
> different from allowing it to revert to the pre-transaction state.

I don't like stuff trailing off at the end, especially three words. 
That SET command is getting so big, it may fall over.  ;-)

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

Bruce Momjian wrote:<br /><blockquote cite="mid200207301647.g6UGlkL16351@candle.pha.pa.us" type="cite"><pre
wrap="">TomLane wrote: </pre><blockquote type="cite"><pre wrap="">Peter Eisentraut <a class="moz-txt-link-rfc2396E"
href="mailto:peter_e@gmx.net"><peter_e@gmx.net></a>writes:   </pre><blockquote type="cite"><pre wrap="">Tom Lane
writes:
As an alternative syntax I can suggest     </pre> <pre wrap="">SET name TO value [ ON COMMIT RESET ];
</pre><blockquotetype="cite"><pre wrap="">Ugh.  Why can't we stick with SET LOCAL?
</pre></blockquote></blockquote><blockquotetype="cite"><pre wrap="">SET LOCAL is already used for something else in the
SQLstandard.  Not
 
sure if we'll ever implement that, but it's something to be concerned
about.     </pre></blockquote><pre wrap="">Actually, it looks to me like the spec's SET LOCAL has a compatible
interpretation: it only affects the current transaction.

My main gripe with "ON COMMIT RESET" is that it's a misleading
description of what will happen --- RESETting a variable is quite
different from allowing it to revert to the pre-transaction state.   </pre></blockquote><pre wrap="">
I don't like stuff trailing off at the end, especially three words. 
That SET command is getting so big, it may fall over.  ;-)
 </pre></blockquote> Perhaps ON COMMIT REVERT would be more intuitive.<br />