> In 8.0 beta 1 I just tried:
> 
>      psql template1
>      > begin;
>      > select * from pg_class;
>      ... got stuff ...
>      > select * from aaa;
>      ERROR: relation "zzz" does not exist
>      > select * from pg_class;
>      ERROR: current transaction is aborted....
> 
> Should psql run every statement in a nested transaction and only
rollback
> that TX? Or is that not possible/desirable. If possible, this would be
a
> *great* feature for those  of use prone to mis-spellings.

Nested transactions are not designed to help you with spelling errors.
They are designed to give you more flexible options with rolling back
data to keep it in a valid state.

psql is designed to follow the same logical statement progression as any
other connection to the database.  Your suggestion could muck up sql
scripts piped through it to the database.

If you make a lot of spelling errors, just macro 'savepoint x' and fire
that when typing multiple related statements into psql after each
statement.  

Merlin

Wed, 11 Aug 2004 11:12:33 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: Does psql use nested transactions?
>> Should psql run every statement in a nested transaction and only rollback
>> that TX?

> psql is designed to follow the same logical statement progression as any
> other connection to the database.  Your suggestion could muck up sql
> scripts piped through it to the database.

The other objection is that this would create very substantial overhead.

It might be reasonable to offer that behavior as an option, but I think
I'd only want it on for interactive input.

			regards, tom lane

Wed, 11 Aug 2004 11:47:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Does psql use nested transactions? 
At 01:47 AM 12/08/2004, Tom Lane wrote:
>It might be reasonable to offer that behavior as an option, but I think
>I'd only want it on for interactive input.

My preference would be for something like:

    set savepoint_per_statement=true

then interactive & scripts could choose to use either feature.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 


Thu, 12 Aug 2004 01:59:55 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Does psql use nested transactions? 
Philip Warner wrote:
> At 01:47 AM 12/08/2004, Tom Lane wrote:
> >It might be reasonable to offer that behavior as an option, but I think
> >I'd only want it on for interactive input.
> 
> My preference would be for something like:
> 
>     set savepoint_per_statement=true
> 
> then interactive & scripts could choose to use either feature.

Added to TODO list:

* Add an option to automatically use savepoints for each statement in a
  multi-statement transaction.

  When enabled, this would allow errors in multi-statement transactions
  to be automatically ignored.


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

Tue, 17 Aug 2004 13:14:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Does psql use nested transactions?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO list:

> * Add an option to automatically use savepoints for each statement in a
>   multi-statement transaction.

>   When enabled, this would allow errors in multi-statement transactions
>   to be automatically ignored.

Note that whoever implements this had better be mindful of the bad
experience we had with autocommit.  If there is any way that this
behavior could be turned on underneath an application/script without
the app knowing about it, this would be spectacularly dangerous.

Like I said, my preference would be that it couldn't be turned on at all
except when psql's input is interactive.

			regards, tom lane

Tue, 17 Aug 2004 13:39:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Does psql use nested transactions? 
Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>Added to TODO list:
> 
> 
>>* Add an option to automatically use savepoints for each statement in a
>>  multi-statement transaction.
> 
> 
>>  When enabled, this would allow errors in multi-statement transactions
>>  to be automatically ignored.

> Like I said, my preference would be that it couldn't be turned on at all
> except when psql's input is interactive.

I strongly vote for the same solution.

Regards
Gaetano Mendola





Tue, 17 Aug 2004 19:58:26 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Does psql use nested transactions?
Hi Tom

On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The other objection is that this would create very substantial overhead.

why?

> It might be reasonable to offer that behavior as an option, but I think
> I'd only want it on for interactive input.

How could the server tell interactive input from scripted intput
without an explicit setting meant to do it?  Is ther already any
conciousness on the server part about who is talking to it?

Other than performance issues, I don't see why this is more dangerous
than any other settings.

Regards
Haroldo

-- 
Por favor registre haroldo.stenger@gmail.com como mi nueva y unica
direccion de correo en lugar de la vieja hstenger@adinet.com.uy
Please, record haroldo.stenger@gmail.com as my new and unique email
address instead of ye old hstenger@adinet.com.uy
Gracias. Thankyou.

Tue, 17 Aug 2004 15:33:01 -0300
Haroldo Stenger <haroldo.stenger@gmail.com>
Re: Does psql use nested transactions?
Haroldo Stenger <haroldo.stenger@gmail.com> writes:
> On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It might be reasonable to offer that behavior as an option, but I think
>> I'd only want it on for interactive input.

> How could the server tell interactive input from scripted intput
> without an explicit setting meant to do it?

The server wouldn't.  One of the implications of this requirement is
that we don't implement the feature on the server side, but in psql.
psql then checks isatty(fileno(stdin)) before allowing it.

> Other than performance issues, I don't see why this is more dangerous
> than any other settings.

Silently ignoring errors is almost always a really bad idea.  Doing it
underneath an application that isn't expecting it is always a bad idea.

Perhaps more to the point, what we are talking about here is an option
to change the behavior of transactions from what it formerly was,
without any change in the SQL commands being issued.  From a backwards-
compatibility standpoint this is monstrously dangerous.  If you have
forgotten the server-side-autocommit fiasco, I suggest you go and read
the archives for a bit ...

			regards, tom lane

Tue, 17 Aug 2004 14:42:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Does psql use nested transactions? 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Added to TODO list:
> 
> > * Add an option to automatically use savepoints for each statement in a
> >   multi-statement transaction.
> 
> >   When enabled, this would allow errors in multi-statement transactions
> >   to be automatically ignored.
> 
> Note that whoever implements this had better be mindful of the bad
> experience we had with autocommit.  If there is any way that this
> behavior could be turned on underneath an application/script without
> the app knowing about it, this would be spectacularly dangerous.
> 
> Like I said, my preference would be that it couldn't be turned on at all
> except when psql's input is interactive.

Should we allow it only to be enabled inside a multi-statement
transaction, and it turns off when the transaction completes?

Doing it only for interactive mode seems too error-prone to me (it works
in psql, but not from my script).

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

Tue, 17 Aug 2004 14:44:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Does psql use nested transactions?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Doing it only for interactive mode seems too error-prone to me (it works
> in psql, but not from my script).

You're missing the point: a script cannot safely work this way.  A human
typing at the terminal can notice that his command failed and react to
that, but a psql script cannot.

			regards, tom lane

Tue, 17 Aug 2004 14:56:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Does psql use nested transactions? 
> Bruce Momjian
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Added to TODO list:
> >
> > > * Add an option to automatically use savepoints for each
> statement in a
> > >   multi-statement transaction.
> >
> > >   When enabled, this would allow errors in multi-statement
> transactions
> > >   to be automatically ignored.
> >
> > Note that whoever implements this had better be mindful of the bad
> > experience we had with autocommit.  If there is any way that this
> > behavior could be turned on underneath an application/script without
> > the app knowing about it, this would be spectacularly dangerous.
> >
> > Like I said, my preference would be that it couldn't be turned on at all
> > except when psql's input is interactive.
>
> Should we allow it only to be enabled inside a multi-statement
> transaction, and it turns off when the transaction completes?
>

Yes, this is a very very good option to have.

This behaviour allows much closer mimicking of Oracle and other RDBMS's
transactional behaviour - also some people (not on this list) have decided
that this is in fact the correct ANSI behaviour for transactions (accepting
that this is unclear, and not wishing to restart that debate).

- It should not apply ONLY to psql, but to all interfaces i.e. it should be
server-side behaviour.

- The option should be available at the session level, so that all
transactions within a session exhibit this behaviour - and not just as an
option within a transaction.

Tom is right to draw analogy to Autocomit, not just because of earlier
fiascos, but also because of the similarity that is required in the way that
the option is set. i.e. it can be manipulated as a session level default,
but is not completely transparent to the user.

These aren't my just preferences, but an attempt at a clear statement of
what is required for compatibility....

Best Regards, Simon Riggs


Tue, 17 Aug 2004 21:43:01 +0100
"Simon Riggs" <simon@2ndquadrant.com>
Re: Does psql use nested transactions?
On Tue, Aug 17, 2004 at 03:33:01PM -0300, Haroldo Stenger wrote:

Haroldo,

> On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > The other objection is that this would create very substantial overhead.
> 
> why?

Establishing a savepoint is a non-trivial operation (cost-wise).  Several
internal server structures have to be prepared for it.  It's way cheaper than
normal transaction start and commit, but undesirable anyway if done once per
query.  Worse if it's going to be seldom used.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La experiencia nos dice que el hombre pelï¿½ millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarï¿½an al hombre" (Ijon Tichy)

Tue, 17 Aug 2004 16:58:56 -0400
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
Re: Does psql use nested transactions?
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes:
> Establishing a savepoint is a non-trivial operation (cost-wise).  Several
> internal server structures have to be prepared for it.

Check

> It's way cheaper than normal transaction start and commit,

Is it?  You have the same amount of work to do (sooner or later) in
terms of updating pg_clog, plus extra work to update pg_subtrans.
And in the abort case it can be worse than aborting a full xact, because
we have to do retail rather than wholesale release of locks, buffers, etc.

I have not had an opportunity to benchmark it but I fear a savepoint may
cost near as much as a full xact in practice.

			regards, tom lane

Tue, 17 Aug 2004 17:10:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Does psql use nested transactions? 
Hola Alvaro

On Tue, 17 Aug 2004 16:58:56 -0400, Alvaro Herrera Munoz
<alvherre@dcc.uchile.cl> wrote:
> > On Wed, 11 Aug 2004 11:47:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > The other objection is that this would create very substantial overhead.
> >
> > why?
> 
> Establishing a savepoint is a non-trivial operation (cost-wise).  Several
> internal server structures have to be prepared for it.  It's way cheaper than
> normal transaction start and commit, but undesirable anyway if done once per
> query.  Worse if it's going to be seldom used.

I see. So, any heavy use of savepoints, say, issuing one before each
SQL command that might violate a restriction, would worsen a lot the
performance of my application?

Saludos
Haroldo

-- 
Por favor registre haroldo.stenger@gmail.com como mi nueva y unica
direccion de correo en lugar de la vieja hstenger@adinet.com.uy
Please, record haroldo.stenger@gmail.com as my new and unique email
address instead of ye old hstenger@adinet.com.uy
Gracias. Thankyou.

Tue, 17 Aug 2004 20:21:13 -0300
Haroldo Stenger <haroldo.stenger@gmail.com>
Re: Does psql use nested transactions?
At 07:10 AM 18/08/2004, Tom Lane wrote:
>I have not had an opportunity to benchmark it but I fear a savepoint may
>cost near as much as a full xact in practice.

Out of curiosity, does this mean that using a savepoint per statement will 
be no worse than using psql in autocommit mode?




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 


Wed, 18 Aug 2004 09:23:44 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Does psql use nested transactions? 
At 04:44 AM 18/08/2004, Bruce Momjian wrote:
>it turns off when the transaction completes?

Not sure I like this part; I would like to be able to set the variable at 
the start of a psql session and have it run for the entire session. Or, 
even better, set it in a psql initialization file.

Also, will the 'interactive-session' check consider an included file (\i) 
to be interactive? I'd vote yes.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 


Wed, 18 Aug 2004 09:26:54 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Does psql use nested transactions?
At 09:26 AM 18/08/2004, Philip Warner wrote:
>Also, will the 'interactive-session' check consider an included file (\i) 
>to be interactive? I'd vote yes.

In retrospect, I assume the interactive-session limit would not be a 
problem here since the command would be entered interactively. The source 
of the following commands is irrelevant.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 


Wed, 18 Aug 2004 10:35:56 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Does psql use nested transactions?
On Tue, Aug 17, 2004 at 08:21:13PM -0300, Haroldo Stenger wrote:

Hola,

> On Tue, 17 Aug 2004 16:58:56 -0400, Alvaro Herrera Munoz
> <alvherre@dcc.uchile.cl> wrote:

> > Establishing a savepoint is a non-trivial operation (cost-wise).  Several
> > internal server structures have to be prepared for it.  It's way cheaper than
> > normal transaction start and commit, but undesirable anyway if done once per
> > query.  Worse if it's going to be seldom used.
> 
> I see. So, any heavy use of savepoints, say, issuing one before each
> SQL command that might violate a restriction, would worsen a lot the
> performance of my application?

It depends.  If you are going to establish savepoints just for the sake
of it, and later release them, then yes --- there is no point in
incurring in the overhead (and your application will be slower).  But if
the application has any chance to fail within the savepoint, and you can
rollback to a savepoint to retry in a situation which would normally
force you to rollback a transaction and start again, then you could
_win_ performance.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I personally became interested in Linux while I was dating an English major
who wouldn't know an operating system if it walked up and bit him."
(Val Henson)


Tue, 17 Aug 2004 20:48:59 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Does psql use nested transactions?
On Wed, Aug 18, 2004 at 09:23:44AM +1000, Philip Warner wrote:
> At 07:10 AM 18/08/2004, Tom Lane wrote:
> >I have not had an opportunity to benchmark it but I fear a savepoint may
> >cost near as much as a full xact in practice.
> 
> Out of curiosity, does this mean that using a savepoint per statement will 
> be no worse than using psql in autocommit mode?

I think it would be slightly better.  A transaction-commit XLog entry
requires fsync, whereas there is no XLog entry for savepoint release.
As Tom points out, however, savepoint rollback may be more expensive
than main transaction rollback due to retail releasing of some
resources.  But in the case of a long transaction and query-long
savepoints, then retail-releasing may have little work to do and be not
that much more expensive than wholesale releasing.

OTOH, pg_clog and pg_subtrans entries are not fsync()'ed, so it's
mostly write() traffic which should be cached at the kernel level.

Of course, we should really measure it to be sure ...

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiï¿½ndose", y el computador dirï¿½ "especifique el tipo de ciervo"
(Jason Alexander)


Tue, 17 Aug 2004 20:55:08 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Does psql use nested transactions?

"Simon Riggs" <simon@2ndquadrant.com> writes:

> This behaviour allows much closer mimicking of Oracle and other RDBMS's
> transactional behaviour

<begin 2c>This is my single biggest pet peeve with Postgres. When I was first
starting it was the single most frustrating violation of the "least surprise"
principle and now that I've been working with it for over a year it's the one
that most continues to interfere with productive work.

With Oracle I found it *extremely* useful on many occasions when doing manual
updates to be able to check out the effects of my statements before committing
them. It also meant I could do several updates or deletes and commit them all
together.

With Postgres I effectively have to work in autocommit mode. Starting over
from scratch every time I make a typo is infeasible. It feels like trying to
type in a C program using "cat". I've done it before but it's not something I
want to repeat often.


In an ideal world I would actually have suggested all drivers would want to
work this way. They could wrap a nested transaction around every statement and
throw an exception if it fails. The application code could catch the exception
and handle it properly without having to manually fiddle with database bits.

That's actually why I mentioned a couple times during the nested transaction
development that it would be best to design with the assumption that there
would be at least one level of nested transaction open at all times.

But in practice I think applications can manage their own transactions
reasonably. It's just users that expect to be able to recover from any
individual statement at any time without advance planning.

-- 
greg


17 Aug 2004 21:48:44 -0400
Greg Stark <gsstark@mit.edu>
Re: Does psql use nested transactions?
On 17 Aug 2004, Greg Stark wrote:

> With Postgres I effectively have to work in autocommit mode. Starting over
> from scratch every time I make a typo is infeasible. It feels like trying to
> type in a C program using "cat". I've done it before but it's not something I
> want to repeat often.

I agree. I would like to not use autocommit but I do way to many syntax 
errors for it to be practical in pg.

Autocommit for me is just a hack to get around the above "limitation".

> In an ideal world I would actually have suggested all drivers would want to
> work this way.

For interactive use I'd like simple errors like syntax error to not abort 
a transaction. For other uses it's good that it aborts.

-- 
/Dennis Bjï¿½rklund


Wed, 18 Aug 2004 06:58:31 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Does psql use nested transactions?
On Tue, Aug 17, 2004 at 02:56:19PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Doing it only for interactive mode seems too error-prone to me (it works
> > in psql, but not from my script).
> 
> You're missing the point: a script cannot safely work this way.  A human
> typing at the terminal can notice that his command failed and react to
> that, but a psql script cannot.

It is not just a typo that you make in the SQL command. You often need
to do

	insert into table which has primary key
	if the insert failed, do update of the existing record

It should be upto the application to decide if the failed insert
should lead to abortion of the transaction, or if it should be
silently ignored and based on the return value of the insert proceed
with update.

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
 .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ...
		Only self-confident people can be simple.

Wed, 18 Aug 2004 08:39:19 +0200
Honza Pazdziora <adelton@informatics.muni.cz>
Re: Does psql use nested transactions?
> It is not just a typo that you make in the SQL command. You often need
> to do
> 
> 	insert into table which has primary key
> 	if the insert failed, do update of the existing record

Do the update first then the insert.

Chris


Wed, 18 Aug 2004 14:47:26 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Does psql use nested transactions?
On Wed, Aug 18, 2004 at 02:47:26PM +0800, Christopher Kings-Lynne wrote:
> >It is not just a typo that you make in the SQL command. You often need
> >to do
> >
> >	insert into table which has primary key
> >	if the insert failed, do update of the existing record
> 
> Do the update first then the insert.

That can still fail as concurrent session might run the same series of
update (which affects 0 records) and insert (which will fail).

Anyway, this was just an example of ways of using the database server
to do part of the work -- letting the database server do the checks
for you, raise an exception for you which you (your application) can
test and happily ignore. It's not just the insert / update thing.
The same goes for foreign keys, checks, anything where you knowingly
run a statement which can fail, and you act based on the exception you
get, _continuing_ with your transaction. This way, large part of the
application logic is shifted to the server and to the database schema.
The client just tries if the command will run OK.

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
 .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ...
		Only self-confident people can be simple.

Wed, 18 Aug 2004 08:55:36 +0200
Honza Pazdziora <adelton@informatics.muni.cz>
Re: Does psql use nested transactions?
On Wed, 2004-08-18 at 02:48, Greg Stark wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> 
> > This behaviour allows much closer mimicking of Oracle and other RDBMS's
> > transactional behaviour
> 
> <begin 2c>This is my single biggest pet peeve with Postgres. When I was first
> starting it was the single most frustrating violation of the "least surprise"
> principle and now that I've been working with it for over a year it's the one
> that most continues to interfere with productive work.
> 
> With Oracle I found it *extremely* useful on many occasions when doing manual
> updates to be able to check out the effects of my statements before committing
> them. It also meant I could do several updates or deletes and commit them all
> together.
> 
> With Postgres I effectively have to work in autocommit mode. Starting over
> from scratch every time I make a typo is infeasible. It feels like trying to
> type in a C program using "cat". I've done it before but it's not something I
> want to repeat often.

It really isn't necessary to do that.  If you are entering commands into
psql manually, either they are so few that you can easily repeat them,
using readline editing, or you can write the commands as a script in an
external file, with BEGIN and END at its top and bottom, and run it with
\i /path/to/file.

If it fails, re-edit it with \!vi /path/to/file and repeat \i

You certainly do not have to work in autocommit mode.  It will be nice
to have nested transactions, but this is not the compelling reason for
doing it.

Oliver Elphick




Thu, 19 Aug 2004 12:01:05 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: Does psql use nested transactions?

Oliver Elphick <olly@lfix.co.uk> writes:

> It really isn't necessary to do that.  If you are entering commands into
> psql manually, either they are so few that you can easily repeat them,
> using readline editing, 

That's what I meant by feeling like I was typing a C program using "cat".

> or you can write the commands as a script in an
> external file, with BEGIN and END at its top and bottom, and run it with
> \i /path/to/file.

Sure you could do that. But it kind of defeats the purpose of having an
interactive client. It feels like being transported back to the 70s.

In any case neither of these solutions addresses the situation where any of
the steps in the sequence of commands takes a long time to run. It's wasteful
and simply silly to be reexecuting 5 minute queries over and over (nevermind
hour long queries or day-long queries) because I typoed on the select to see
if it worked the way I expected at the end. That's actually the usual time
that I want to double check my work before committing, not simple quick
queries.

Or similarly for the situation of non-deterministic queries. Sometimes I use
random() for loading sample data but then I want to check to make sure things
were distributed the way I wanted...

-- 
greg


19 Aug 2004 12:39:43 -0400
Greg Stark <gsstark@mit.edu>
Re: Does psql use nested transactions?