SET LOCAL again

SET LOCAL again

Post by Peter Eisentra » Sat, 27 Jul 2002 05:52:16



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?

--

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

 
 
 

SET LOCAL again

Post by Tom La » Sun, 28 Jul 2002 00:10:00



> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

SET LOCAL again

Post by Peter Eisentra » Thu, 01 Aug 2002 01:35:14


Quote: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.

--

---------------------------(end of broadcast)---------------------------

 
 
 

SET LOCAL again

Post by Tom La » Thu, 01 Aug 2002 01:45:20



> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

SET LOCAL again

Post by Bruce Momji » Thu, 01 Aug 2002 01:47:51




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

  +  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

 
 
 

SET LOCAL again

Post by Thomas Sw » Thu, 01 Aug 2002 06:48:52


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body>
<blockquote type="cite"


  </pre>
  <blockquote type="cite">

    </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>
      <blockquote type="cite">
        <pre wrap="">Ugh.  Why can't we stick with SET LOCAL?
        </pre>
      </blockquote>
    </blockquote>
    <blockquote type="cite">
      <pre wrap="">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.
      </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>
</body>
</html>

 
 
 

1. Help to set local variable equal to value of field in result set

Hi,

I have a table in my database where I store some of my application
settings. The table has 2 NVARCHAR columns; key_name and key_value.
One of the keys is a date. In a stored procedure how can I set a local
variable of type DATETIME to a key_value which is stored as NVARCHAR.

For example:



N'MyDateKey'))

Thanks Paul

2. Error "1206 - Invalid day in date " while importing

3. set up question again

4. Extended ER model

5. SET ROLE problems again :)

6. Merge replication betveen Navision Financials 2.50 databses

7. Long Search Times\Find again on found set

8. Wanted: beta testers for C++ ODBC class library

9. Join with empty result set, again...

10. change collation setting without install sql server again?

11. (me again..) setting a limit for returned records

12. setting a sp local variable to value of an EXEC statement

13. setting local variable