Hi all,
why START TRANSACTION READ ONLY is allowed
and not BEGIN READ ONLY ?







Regards
Gaetano Mendola


Mon, 27 Oct 2003 01:22:04 +0100
Gaetano Mendola <mendola@bigfoot.com>
BEGIN vs START TRANSACTION
Gaetano Mendola wrote:
> Hi all,
> why START TRANSACTION READ ONLY is allowed
> and not BEGIN READ ONLY ?

I think because START TRANSACTION is SQL standard?   However, I thought
BEGIN WORK was SQL standard, and we don't support READ ONLY there
either --- hmmm.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 26 Oct 2003 22:05:29 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BEGIN vs START TRANSACTION
> I think because START TRANSACTION is SQL standard?   However, I thought
> BEGIN WORK was SQL standard, and we don't support READ ONLY there
> either --- hmmm.

BEGIN is no part of the SQL standard.  The only way to begin a 
transaction under the SQL standard is START TRANSACTION.

Chris



Mon, 27 Oct 2003 11:15:56 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: BEGIN vs START TRANSACTION
On Sun, 2003-10-26 at 19:22, Gaetano Mendola wrote:
> Hi all,
> why START TRANSACTION READ ONLY is allowed
> and not BEGIN READ ONLY ?

As Chris KL points out, it's not required by the standard (since BEGIN
isn't part of the standard to begin with). I suppose we could add it,
but it seems a little pointless -- "BEGIN ; SET ..." seems just as good.

-Neil



Mon, 27 Oct 2003 04:00:14 -0500
Neil Conway <neilc@samurai.com>
Re: BEGIN vs START TRANSACTION
Christopher Kings-Lynne wrote:
 >> I think because START TRANSACTION is SQL standard?   However, I thought
 >> BEGIN WORK was SQL standard, and we don't support READ ONLY there
 >> either --- hmmm.
 >
 >
 > BEGIN is no part of the SQL standard.  The only way to begin a
 > transaction under the SQL standard is START TRANSACTION.

These IMHO means push people to not use BEGIN anymore.


Regards
Gaetano Mendola


Mon, 27 Oct 2003 10:26:27 +0100
Gaetano Mendola <mendola@bigfoot.com>
Re: BEGIN vs START TRANSACTION
Gaetano Mendola wrote:
> Hi all,
> why START TRANSACTION READ ONLY is allowed
> and not BEGIN READ ONLY ?

Seems it should be allowed so that BEGIN and START TRANSACTION behave
the same.

In fact, the BEGIN manual page says:

   <xref linkend="sql-start-transaction"
   endterm="sql-start-transaction-title"> has the same functionality
   as <command>BEGIN</>.

which is currently not true because START TRANSACTION has additional
options.  The following patch fixes it.  I will put it into 7.5 after an
appropriate delay.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Index: doc/src/sgml/ref/begin.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/begin.sgml,v
retrieving revision 1.26
diff -c -c -r1.26 begin.sgml
*** doc/src/sgml/ref/begin.sgml	9 Sep 2003 18:28:52 -0000	1.26
--- doc/src/sgml/ref/begin.sgml	9 Nov 2003 03:06:01 -0000
***************
*** 21,26 ****
--- 21,28 ----
   <refsynopsisdiv>
  <synopsis>
  BEGIN [ WORK | TRANSACTION ]
+     [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ]
+     [ READ WRITE | READ ONLY ]
  </synopsis>
   </refsynopsisdiv>
   
***************
*** 49,54 ****
--- 51,63 ----
     other sessions will be unable to see the intermediate states
     wherein not all the related updates have been done.
    </para>
+ 
+   <para>
+    If the isolation level or read/write mode is specified, the new
+    transaction has those characteristics, as if
+    <xref linkend="sql-set-transaction" endterm="sql-set-transaction-title"> 
+    was executed.
+   </para>
   </refsect1>
    
   <refsect1>
***************
*** 65,70 ****
--- 74,85 ----
      </listitem>
     </varlistentry>
    </variablelist>
+ 
+   <para>
+    See under <xref linkend="sql-set-transaction"
+    endterm="sql-set-transaction-title"> about the meaning of the
+    other parameters.
+   </para>
   </refsect1>
    
   <refsect1>
Index: doc/src/sgml/ref/start_transaction.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/start_transaction.sgml,v
retrieving revision 1.8
diff -c -c -r1.8 start_transaction.sgml
*** doc/src/sgml/ref/start_transaction.sgml	6 Nov 2003 22:08:14 -0000	1.8
--- doc/src/sgml/ref/start_transaction.sgml	9 Nov 2003 03:06:01 -0000
***************
*** 33,41 ****
     This command begins a new transaction. If the isolation level or
     read/write mode is specified, the new transaction has those
     characteristics, as if <xref linkend="sql-set-transaction"
!    endterm="sql-set-transaction-title"> was executed. In all other
!    respects, the behavior of this command is identical to the <xref
!    linkend="sql-begin" endterm="sql-begin-title"> command.
    </para>
   </refsect1>
  
--- 33,40 ----
     This command begins a new transaction. If the isolation level or
     read/write mode is specified, the new transaction has those
     characteristics, as if <xref linkend="sql-set-transaction"
!    endterm="sql-set-transaction-title"> was executed. It is the same
!    as the <xref linkend="sql-begin" endterm="sql-begin-title"> command.
    </para>
   </refsect1>
  
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.437
diff -c -c -r2.437 gram.y
*** src/backend/parser/gram.y	6 Nov 2003 22:08:14 -0000	2.437
--- src/backend/parser/gram.y	9 Nov 2003 03:06:07 -0000
***************
*** 3607,3617 ****
  					n->options = NIL;
  					$$ = (Node *)n;
  				}
! 			| BEGIN_P opt_transaction
  				{
  					TransactionStmt *n = makeNode(TransactionStmt);
  					n->kind = TRANS_STMT_BEGIN;
! 					n->options = NIL;
  					$$ = (Node *)n;
  				}
  			| START TRANSACTION transaction_mode_list_or_empty
--- 3607,3617 ----
  					n->options = NIL;
  					$$ = (Node *)n;
  				}
! 			| BEGIN_P opt_transaction transaction_mode_list_or_empty
  				{
  					TransactionStmt *n = makeNode(TransactionStmt);
  					n->kind = TRANS_STMT_BEGIN;
! 					n->options = $3;
  					$$ = (Node *)n;
  				}
  			| START TRANSACTION transaction_mode_list_or_empty
Sat, 8 Nov 2003 22:07:29 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] BEGIN vs START TRANSACTION
Bruce Momjian writes:

> In fact, the BEGIN manual page says:
>
>    <xref linkend="sql-start-transaction"
>    endterm="sql-start-transaction-title"> has the same functionality
>    as <command>BEGIN</>.
>
> which is currently not true because START TRANSACTION has additional
> options.

Then the that manual page should be fixed.

> The following patch fixes it.  I will put it into 7.5 after an
> appropriate delay.

I object to adding unnecessary complications like that.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 9 Nov 2003 10:18:33 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] BEGIN vs START TRANSACTION
Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > In fact, the BEGIN manual page says:
> >
> >    <xref linkend="sql-start-transaction"
> >    endterm="sql-start-transaction-title"> has the same functionality
> >    as <command>BEGIN</>.
> >
> > which is currently not true because START TRANSACTION has additional
> > options.
> 
> Then the that manual page should be fixed.
> 
> > The following patch fixes it.  I will put it into 7.5 after an
> > appropriate delay.
> 
> I object to adding unnecessary complications like that.

Shouldn't BEGIN and START TRANSACTION have the same mechanics?  The
changes to the code were the addition of only one line.  The rest of the
patch was docs.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 9 Nov 2003 09:05:11 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] BEGIN vs START TRANSACTION
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Peter Eisentraut wrote:
>> I object to adding unnecessary complications like that.

> Shouldn't BEGIN and START TRANSACTION have the same mechanics?  The
> changes to the code were the addition of only one line.  The rest of the
> patch was docs.

My initial reaction was the same as Peter's, but after seeing the small
size of the patch I reconsidered.  It seems to make sense that BEGIN
should be an exact synonym for START TRANSACTION.

			regards, tom lane

Sun, 09 Nov 2003 10:18:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] BEGIN vs START TRANSACTION 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Peter Eisentraut wrote:
> >> I object to adding unnecessary complications like that.
> 
> > Shouldn't BEGIN and START TRANSACTION have the same mechanics?  The
> > changes to the code were the addition of only one line.  The rest of the
> > patch was docs.
> 
> My initial reaction was the same as Peter's, but after seeing the small
> size of the patch I reconsidered.  It seems to make sense that BEGIN
> should be an exact synonym for START TRANSACTION.

Let me give you my logic on this --- if people think of BEGIN and START
TRANSACTION as the same, and they do \h begin, they aren't going to see
the read only and isolation options for START TRANSACTION, and I doubt
they are going to think to look there because they think they are the
same.  That's why I think it is good to add those clauses to BEGIN
WORK/TRANSACTION.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 9 Nov 2003 22:10:47 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] BEGIN vs START TRANSACTION
Bruce Momjian wrote:

> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> > Peter Eisentraut wrote:
>> >> I object to adding unnecessary complications like that.
>> 
>> > Shouldn't BEGIN and START TRANSACTION have the same mechanics?  The
>> > changes to the code were the addition of only one line.  The rest of the
>> > patch was docs.
>> 
>> My initial reaction was the same as Peter's, but after seeing the small
>> size of the patch I reconsidered.  It seems to make sense that BEGIN
>> should be an exact synonym for START TRANSACTION.
> 
> Let me give you my logic on this --- if people think of BEGIN and START
> TRANSACTION as the same, and they do \h begin, they aren't going to see
> the read only and isolation options for START TRANSACTION, and I doubt
> they are going to think to look there because they think they are the
> same.  That's why I think it is good to add those clauses to BEGIN
> WORK/TRANSACTION.
> 

Since BEGIN isn't standard, wouldn't it be time to redirect them on the 
BEGIN manpage to the START TRANSACTION manpage and tell them there that 
BEGIN does not support the full syntax of START TRANSACTION?


Jan

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


Mon, 10 Nov 2003 11:02:32 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: [HACKERS] BEGIN vs START TRANSACTION
Jan Wieck wrote:
> Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> >> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> > Peter Eisentraut wrote:
> >> >> I object to adding unnecessary complications like that.
> >> 
> >> > Shouldn't BEGIN and START TRANSACTION have the same mechanics?  The
> >> > changes to the code were the addition of only one line.  The rest of the
> >> > patch was docs.
> >> 
> >> My initial reaction was the same as Peter's, but after seeing the small
> >> size of the patch I reconsidered.  It seems to make sense that BEGIN
> >> should be an exact synonym for START TRANSACTION.
> > 
> > Let me give you my logic on this --- if people think of BEGIN and START
> > TRANSACTION as the same, and they do \h begin, they aren't going to see
> > the read only and isolation options for START TRANSACTION, and I doubt
> > they are going to think to look there because they think they are the
> > same.  That's why I think it is good to add those clauses to BEGIN
> > WORK/TRANSACTION.
> > 
> 
> Since BEGIN isn't standard, wouldn't it be time to redirect them on the 
> BEGIN manpage to the START TRANSACTION manpage and tell them there that 
> BEGIN does not support the full syntax of START TRANSACTION?

Yea, we could do that, and if it was hard to add, we would, but it seems
easier to just say BEGIN and START TRANSACTION are the same except the
later is standard, rather than have the later have additional
functionality too.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 10 Nov 2003 11:20:43 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] BEGIN vs START TRANSACTION
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Jan Wieck wrote:
>> Since BEGIN isn't standard, wouldn't it be time to redirect them on the 
>> BEGIN manpage to the START TRANSACTION manpage and tell them there that 
>> BEGIN does not support the full syntax of START TRANSACTION?

> Yea, we could do that, and if it was hard to add, we would, but it seems
> easier to just say BEGIN and START TRANSACTION are the same except the
> later is standard, rather than have the later have additional
> functionality too.

IIRC, the code patch only added about two lines to gram.y.  It seems a
bit silly to add *more* lines of documentation to explain that the two
statements aren't alike than it would take lines of code to make them
work alike.

			regards, tom lane

Mon, 10 Nov 2003 11:23:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] BEGIN vs START TRANSACTION 
On Mon, Nov 10, 2003 at 11:23:20AM -0500, Tom Lane wrote:

> IIRC, the code patch only added about two lines to gram.y.  It seems a
> bit silly to add *more* lines of documentation to explain that the two
> statements aren't alike than it would take lines of code to make them
> work alike.

But maybe it would be useful to point out the difference; for example,
users will get confused if they try to start a subtransaction inside a
plpgsql function using BEGIN.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El miedo atento y previsor es la madre de la seguridad" (E. Burke)

Mon, 10 Nov 2003 14:05:31 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: [HACKERS] BEGIN vs START TRANSACTION