Does psql use nested transactions?

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

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

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

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

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

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

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.

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

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

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

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

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)

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

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.

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

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

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

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)

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)