autocommit vs TRUNCATE et al
There are a number of statements, such as TRUNCATE TABLE, that refuse to
run in a transaction block because they perform actions that can't be
rolled back later.
These statements currently do not look at autocommit, which means that
if autocommit is off, their tests will succeed ... but then a
transaction block is started anyway, defeating the point of the test.
We could fix these statements to fail if autocommit is off, which means
that you could not use them at all except by setting autocommit on.
Ugh.
Or we could fix them to force an autocommit. Which would mean that
these "dangerous" statements would become even more dangerous, since
that's exactly the behavior a person using autocommit-off would not
expect. Also ugh.
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?
regards, tom lane
Tom Lane wrote:
There are a number of statements, such as TRUNCATE TABLE, that refuse to
run in a transaction block because they perform actions that can't be
rolled back later.These statements currently do not look at autocommit, which means that
if autocommit is off, their tests will succeed ... but then a
transaction block is started anyway, defeating the point of the test.We could fix these statements to fail if autocommit is off, which means
that you could not use them at all except by setting autocommit on.
Ugh.Or we could fix them to force an autocommit. Which would mean that
these "dangerous" statements would become even more dangerous, since
that's exactly the behavior a person using autocommit-off would not
expect. Also ugh.Anyone see a way out of this catch-22? If not, which is the least
bad alternative?
I think the "least bad" is the first option -- disallow TRUNCATE unless
autocommit is on. With the second option, people would be caught by surprise
at precisely the worst possible moment. Better to make them take the extra step.
Joe
Tom Lane wrote:
There are a number of statements, such as TRUNCATE TABLE, that refuse to
run in a transaction block because they perform actions that can't be
rolled back later.These statements currently do not look at autocommit, which means that
if autocommit is off, their tests will succeed ... but then a
transaction block is started anyway, defeating the point of the test.We could fix these statements to fail if autocommit is off, which means
that you could not use them at all except by setting autocommit on.
Ugh.Or we could fix them to force an autocommit. Which would mean that
these "dangerous" statements would become even more dangerous, since
that's exactly the behavior a person using autocommit-off would not
expect. Also ugh.Anyone see a way out of this catch-22? If not, which is the least
bad alternative?
Ugh, I see what you mean. With the old code, you had to do a BEGIN to
start a multi-statement transaction, while with autocommit off, you are
always in one. I don't think forcing them to turn on autocommit makes
any sense; it isn't obvious, and it doesn't buy anything. I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too. In fact,
you can just put the xact commit check in the same place SET is handled
in postgres.c. It isn't great, but it is clean. ;-)
You could also throw a NOTICE mentioning it was committed as a separate
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 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
... I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too. In fact,
you can just put the xact commit check in the same place SET is handled
in postgres.c. It isn't great, but it is clean. ;-)
Well, "clean" isn't the adjective I would use ;-), but this might be the
most useful approach. The analogy to SET hadn't occurred to me.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
... I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too. In fact,
you can just put the xact commit check in the same place SET is handled
in postgres.c. It isn't great, but it is clean. ;-)Well, "clean" isn't the adjective I would use ;-), but this might be the
Clean in coding terms, _only_.
most useful approach. The analogy to SET hadn't occurred to me.
Yea, the SET behavior appeared pretty queer to me, but now that I have
used it, I am getting used to it.
--
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 19073Bruce Momjian wrote:
most useful approach. The analogy to SET hadn't occurred to me.
Yea, the SET behavior appeared pretty queer to me, but now that I have
used it, I am getting used to it.
So does that mean:
set autocommit to off;
begin;
insert into foo values('a');
insert into bar values('b');
truncate table foobar;
will automatically commit the two inserts?
Joe
Joe Conway wrote:
Bruce Momjian wrote:
most useful approach. The analogy to SET hadn't occurred to me.
Yea, the SET behavior appeared pretty queer to me, but now that I have
used it, I am getting used to it.So does that mean:
set autocommit to off;
begin;
insert into foo values('a');
insert into bar values('b');
truncate table foobar;will automatically commit the two inserts?
No, the entire transaction will aborted because TRUNCATE has to be at
the start of a multi-statement transaction. This will also fail:
set autocommit to off;
begin;
truncate table foobar;
insert into foo values('a');
insert into bar values('b');
but this will work:
set autocommit to off;
truncate table foobar;
insert into foo values('a');
insert into bar values('b');
In the last case, the TRUNCATE will happen, and the INSERTs will be in
their own multi-statement transaction. A SET in place of TRUNCATE will
behave the same way.
--
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 19073Bruce Momjian wrote:
Joe Conway wrote:
Bruce Momjian wrote:
most useful approach. The analogy to SET hadn't occurred to me.
Yea, the SET behavior appeared pretty queer to me, but now that I have
used it, I am getting used to it.
<snip examples>
In the last case, the TRUNCATE will happen, and the INSERTs will be in
their own multi-statement transaction. A SET in place of TRUNCATE will
behave the same way.
Hmmm. It does look strange. We ought to make this prominent in the release
notes and docs.
Joe
On Fri, 18 Oct 2002, Tom Lane wrote:
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?
Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial,
I know :-).
Regardless, the first option seems the less of the two evils.
Gavin
Gavin Sherry wrote:
On Fri, 18 Oct 2002, Tom Lane wrote:
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial,
I know :-).Regardless, the first option seems the less of the two evils.
Even though TRUNCATE was modeled after Oracle's TRUNCATE and
Oracle's TRUNCATE commits the running tx, truncates the
relation, and starts a new tx, regardless of whether or not
TRUNCATE is the first statement of the tx?
Mike Mascari
mascarm@mascari.com
Mike Mascari wrote:
Gavin Sherry wrote:
On Fri, 18 Oct 2002, Tom Lane wrote:
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial,
I know :-).Regardless, the first option seems the less of the two evils.
Even though TRUNCATE was modeled after Oracle's TRUNCATE and
Oracle's TRUNCATE commits the running tx, truncates the
relation, and starts a new tx, regardless of whether or not
TRUNCATE is the first statement of the tx?
That seems just too harsh to me. I think we should impose some
structure to it, though we will have compatibility issues.
--
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 19073On Fri, 18 Oct 2002, Mike Mascari wrote:
Gavin Sherry wrote:
On Fri, 18 Oct 2002, Tom Lane wrote:
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial,
I know :-).Regardless, the first option seems the less of the two evils.
Even though TRUNCATE was modeled after Oracle's TRUNCATE and
Oracle's TRUNCATE commits the running tx, truncates the
relation, and starts a new tx, regardless of whether or not
TRUNCATE is the first statement of the tx?
Why should we be *only* as good as Oracle? :-)
Gavin
Gavin Sherry <swm@linuxworld.com.au> writes:
On Fri, 18 Oct 2002, Tom Lane wrote:
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?
Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial,
I know :-).
I was about to say that the entire *point* of TRUNCATE is to be
transaction-unsafe ;-)
But on the other hand ... now that we have relation versioning (like
CLUSTER) it seems like TRUNCATE could generate new versions of the
relation and its indexes, without touching the originals. This would
make it transaction-safe, at the cost of not releasing the original
version's disk space till you commit. Seems like a good tradeoff to me.
It's not happening for 7.3, in any case, so we need a stopgap answer.
There are other examples --- CREATE/DROP DATABASE, for example ---
where we'd probably need an answer anyway; I doubt we'll ever make
those completely transaction-safe.
regards, tom lane
On Friday 18 October 2002 11:25 pm, Tom Lane wrote:
Gavin Sherry <swm@linuxworld.com.au> writes:
On Fri, 18 Oct 2002, Tom Lane wrote:
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?
Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial,
I know :-).
I was about to say that the entire *point* of TRUNCATE is to be
transaction-unsafe ;-)
I actually was considering using a transaction-safe TRUNCATE in an application
involving daily imports of 170MB of data into a set of linked tables. Since
the import takes a finite amount of time, it would be nice to have the
previous data available while the new is being imported. And TRUNCATE is
significantly faster than DELETE over 170MB of data.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
Added to TODO:
* Make a transaction-safe TRUNCATE
---------------------------------------------------------------------------
Lamar Owen wrote:
On Friday 18 October 2002 11:25 pm, Tom Lane wrote:
Gavin Sherry <swm@linuxworld.com.au> writes:
On Fri, 18 Oct 2002, Tom Lane wrote:
Anyone see a way out of this catch-22? If not, which is the least
bad alternative?Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial,
I know :-).I was about to say that the entire *point* of TRUNCATE is to be
transaction-unsafe ;-)I actually was considering using a transaction-safe TRUNCATE in an application
involving daily imports of 170MB of data into a set of linked tables. Since
the import takes a finite amount of time, it would be nice to have the
previous data available while the new is being imported. And TRUNCATE is
significantly faster than DELETE over 170MB of data.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
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 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
There are a number of statements, such as TRUNCATE TABLE, that refuse to
run in a transaction block because they perform actions that can't be
rolled back later.These statements currently do not look at autocommit, which means that
if autocommit is off, their tests will succeed ... but then a
transaction block is started anyway, defeating the point of the test.
... I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too.
There is another aspect of this, which is: what if one of these
statements is called by a user-defined function, say a plpgsql function
that does a TRUNCATE and then other stuff? If the function is called
by a SELECT that's not inside a transaction block, then its
IsTransactionBlock() test will succeed --- but the possibility remains
that the later actions of the function could cause an elog and
transaction rollback. Which is what we wanted to prevent.
We can go with the auto-COMMIT idea for statements that are invoked at
the outer interactive level, but that doesn't work for stuff invoked
inside a function. I think we need to forbid these statements inside
functions, too. We already have that for VACUUM, because of its
internal commits causing problems for functions, but we'll need to
extend it to all of them.
Just FYI, the statements involved are
VACUUM
TRUNCATE TABLE
CREATE/DROP DATABASE
REINDEX (all forms)
ALTER USER changing password
DROP USER
ALTER and DROP USER just issue NOTICEs rather than failing, which seems
pretty bogus in itself. The reason they are worried is that there's
no mechanism for rolling back updates of the flat password file.
I think we could fix that by arranging not to write the flat password
file at all until we are ready to commit the current transaction;
will take a look at it.
REINDEX perhaps could be treated as transaction-safe in the forms that
build a new index file rather than truncating. Will look at that, too.
Another place that is calling IsTransactionBlock is parser/analyze.c
while processing DECLARE CURSOR. I think this is pretty bogus for
several reasons:
1. There's no good reason to make DECLARE CURSOR outside a transaction
block be an ERROR; at most it should be a NOTICE.
2. Parse analysis is the wrong place anyway; it should be tested
at execution time, methinks.
3. If the cursor is being declared and used inside a function, then
it could be used successfully without being inside a transaction
block at all.
Point 3 makes me think we should just get rid of the test entirely.
Comments?
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
There are a number of statements, such as TRUNCATE TABLE, that refuse to
run in a transaction block because they perform actions that can't be
rolled back later.These statements currently do not look at autocommit, which means that
if autocommit is off, their tests will succeed ... but then a
transaction block is started anyway, defeating the point of the test.... I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too.There is another aspect of this, which is: what if one of these
statements is called by a user-defined function, say a plpgsql function
that does a TRUNCATE and then other stuff? If the function is called
by a SELECT that's not inside a transaction block, then its
IsTransactionBlock() test will succeed --- but the possibility remains
that the later actions of the function could cause an elog and
transaction rollback. Which is what we wanted to prevent.We can go with the auto-COMMIT idea for statements that are invoked at
the outer interactive level, but that doesn't work for stuff invoked
inside a function. I think we need to forbid these statements inside
functions, too. We already have that for VACUUM, because of its
internal commits causing problems for functions, but we'll need to
extend it to all of them.Just FYI, the statements involved are
VACUUM
TRUNCATE TABLE
CREATE/DROP DATABASE
REINDEX (all forms)
ALTER USER changing password
DROP USERALTER and DROP USER just issue NOTICEs rather than failing, which seems
pretty bogus in itself. The reason they are worried is that there's
no mechanism for rolling back updates of the flat password file.
I think we could fix that by arranging not to write the flat password
file at all until we are ready to commit the current transaction;
will take a look at it.
Yes, I thought we had those secure, but I see now we don't. We only
have interlocking so the file is reread at the proper time.
REINDEX perhaps could be treated as transaction-safe in the forms that
build a new index file rather than truncating. Will look at that, too.Another place that is calling IsTransactionBlock is parser/analyze.c
while processing DECLARE CURSOR. I think this is pretty bogus for
several reasons:
1. There's no good reason to make DECLARE CURSOR outside a transaction
block be an ERROR; at most it should be a NOTICE.
2. Parse analysis is the wrong place anyway; it should be tested
at execution time, methinks.
3. If the cursor is being declared and used inside a function, then
it could be used successfully without being inside a transaction
block at all.Point 3 makes me think we should just get rid of the test entirely.
Comments?
I thought the transaction test was in DECLARE so people didn't create
cursors outside of transactions and then wonder why they didn't work.
If it is going to fail, an ERROR seems more appropriate than a NOTICE.
I can see it happening inside a function, yes.
Another question related to this is the handling of SET/SHOW/RESET in
functions. People should realize it isn't really the first command in
the transaction so will be part of the transaction. The big issue is
that SET has a fallback when it is not first in a transaciton, namely to
be part of the transaction, while TRUNCATE doesn't have that fallback
because it can't be rolled back.
--
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... I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too.We can go with the auto-COMMIT idea for statements that are invoked at
the outer interactive level,
What I just committed uses your idea of auto-committing TRUNCATE et al,
but now that I review the thread I think that everyone else thought that
that was a dangerous idea. How do you feel about simply throwing an error
in autocommit-off mode, instead? (At least it's a localized change now)
regards, tom lane
Tom Lane wrote:
... I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too.We can go with the auto-COMMIT idea for statements that are invoked at
the outer interactive level,What I just committed uses your idea of auto-committing TRUNCATE et al,
but now that I review the thread I think that everyone else thought that
that was a dangerous idea. How do you feel about simply throwing an error
in autocommit-off mode, instead? (At least it's a localized change now)
Yes, I saw more votes to not allow it, as you said, but turning off
autocommit just seemed really strange to me, because then they have to
turn it on again to continue. It just seemed strange to tell them to
set something to execute a command.
Maybe we can throw a WARNING when autocommit is on. Would that make
everyone happy?
--
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