Обсуждение: Transactional-DDL DROP/CREATE TABLE

Hi

I have code that does (inside a single transaction)

DROP TABLE IF EXISTS mytable; CREATE TABLE mytable ....

Occasionally this produces

ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index" DETAIL: Key (typname,
typnamespace)=(mytable, 2200) already exists.

I can get away from this by using CREATE TABLE IF NOT EXISTS in the
same code, but there's the potential that the wrong data will end up
in the table if that happens, and it also seems a little.... odd.

Would you not expect this transaction to be atomic? ie at commit time,
the transaction should drop any table with the same name that has been
created by another transaction.

Geoff

On Thu, Oct 6, 2016 at 11:21 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> DROP TABLE IF EXISTS mytable; CREATE TABLE mytable ....
>
> Occasionally this produces
>
> ERROR: duplicate key value violates unique constraint
> "pg_type_typname_nsp_index" DETAIL: Key (typname,
> typnamespace)=(mytable, 2200) already exists.
>
> I can get away from this by using CREATE TABLE IF NOT EXISTS in the
> same code, but there's the potential that the wrong data will end up
> in the table if that happens, and it also seems a little.... odd.
>
> Would you not expect this transaction to be atomic? ie at commit time,
> the transaction should drop any table with the same name that has been
> created by another transaction.

It seems to be atomic, either it drop/creates or does nothing. What
you want is a beyond atomicity. What does the other transaction do?
What if the other transaction hasn't commited? or it has created the
table anew ( no drop, the table wasn't there ). What are the isolation
levels involved?

If all the transactions operating in the table are doing just what you
show an nothing more, and they are all serializable, I MAY expect
that, but wouldn't put much money in it.

Francisco Olarte.

On 6 Oct 2016 12:06 p.m., "Francisco Olarte" <folarte@peoplecall.com> wrote:
>
> On Thu, Oct 6, 2016 at 11:21 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> > DROP TABLE IF EXISTS mytable; CREATE TABLE mytable ....
> >
> > Occasionally this produces
> >
> > ERROR: duplicate key value violates unique constraint
> > "pg_type_typname_nsp_index" DETAIL: Key (typname,
> > typnamespace)=(mytable, 2200) already exists.
> >
> > I can get away from this by using CREATE TABLE IF NOT EXISTS in the
> > same code, but there's the potential that the wrong data will end up
> > in the table if that happens, and it also seems a little.... odd.
> >
> > Would you not expect this transaction to be atomic? ie at commit time,
> > the transaction should drop any table with the same name that has been
> > created by another transaction.
>
> It seems to be atomic, either it drop/creates or does nothing. What
> you want is a beyond atomicity. What does the other transaction do?
> What if the other transaction hasn't commited? or it has created the
> table anew ( no drop, the table wasn't there ). What are the isolation
> levels involved?

But surely Transactional DDL implies that (it should appear that) nothing happens until transaction-commit. That means "drop table if exists" should drop the table if it exists at commit time, not drop the table if it didn't exist when the code was first run.

If the other transaction hasn't committed, then it should either fail with rollback when committed (because it tried to create a table that exists at commit time) or drop the new table (because it also has a drop clause).

Geoff

Hi Geoff:

On Thu, Oct 6, 2016 at 1:58 PM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> But surely Transactional DDL implies that (it should appear that) nothing
> happens until transaction-commit. That means "drop table if exists" should
> drop the table if it exists at commit time, not drop the table if it didn't
> exist when the code was first run.

I'm not sure even transactional DML works that way. Bear in mind you
are supposed to be informed of the result of your commands. I mean,
you issue drop if exists and then you are allowed to issue a different
command depending on the result of the drop, i.e., you may be willing
to recreate the table if it existed and not create it if not, so the
drop must record your intentions and lock the table definition, like a
delete does with data rows.

> If the other transaction hasn't committed, then it should either fail with
> rollback when committed (because it tried to create a table that exists at
> commit time) or drop the new table (because it also has a drop clause).

It depends on the implementation. IIRC with serializable isolation
level you are guaranteeed a final result coherent with some serial
order of execution of all the completed transactions, but even there
you are not guaranteed it will find an order of execution for all of
them, some may be aborted. Trying to do what you pretend will result
in an extremely deadlock-prone system.

And anyway, what isolation level are you working on? Because it seems
you are using a weaker one than serializable, as I think serializable
should give you more or less what you are expecting ( not on commit
time, but second drop could probably get stuck until first transaction
commits ).

And surely Transactional D*L does not imply what you state in all
isolation levels. And drop table if exsits means if it exists when the
server executes your command, not on the future ( the server cannot
know if it will exist then, your own transaction may recreate it or
not. Maybe you know your command sequence is not going to depend on
intermediate results, the server does not ).


Francisco Olarte.

On 10/06/2016 02:21 AM, Geoff Winkless wrote:
> Hi
>
> I have code that does (inside a single transaction)
>
> DROP TABLE IF EXISTS mytable; CREATE TABLE mytable ....
>
> Occasionally this produces
>
> ERROR: duplicate key value violates unique constraint
> "pg_type_typname_nsp_index" DETAIL: Key (typname,
> typnamespace)=(mytable, 2200) already exists.
>
> I can get away from this by using CREATE TABLE IF NOT EXISTS in the
> same code, but there's the potential that the wrong data will end up
> in the table if that happens, and it also seems a little.... odd.
>
> Would you not expect this transaction to be atomic? ie at commit time,
> the transaction should drop any table with the same name that has been
> created by another transaction.

This is how I can trigger the ERROR:

Session 1:

test=# begin ;
BEGIN
test=# drop table if exists ddl_test;
NOTICE:  table "ddl_test" does not exist, skipping
DROP TABLE
test=# create table ddl_test(id int);
CREATE TABLE
test=# commit ;
COMMIT

Session 2 (concurrent to session1):

test=# begin ;
BEGIN
test=# drop table if exists ddl_test;
NOTICE:  table "ddl_test" does not exist, skipping
DROP TABLE
test=# create table ddl_test(id int);
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(ddl_test, 2200) already exists.
test=# commit ;
ROLLBACK


So not having the table when you start both sessions seems to be the issue.


>
> Geoff
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 6 October 2016 at 15:04, Francisco Olarte <folarte@peoplecall.com> wrote:
> And anyway, what isolation level are you working on? Because it seems
> you are using a weaker one than serializable, as I think serializable
> should give you more or less what you are expecting ( not on commit
> time, but second drop could probably get stuck until first transaction
> commits ).

Nope. Serializable ignores the DROP, and then freezes on CREATE (and
then fails when the first transaction COMMITs). Which is also broken,
because the transaction should fail if (at COMMIT time) the table
cannot be CREATEd, but that's no reason to not create a table within a
temporary namespace and perform actions against that table until the
COMMIT, at which point the table can either be validated systemwide or
the transaction rolled back.

> And drop table if exsits means if it exists when the
> server executes your command, not on the future ( the server cannot
> know if it will exist then, your own transaction may recreate it or
> not. Maybe you know your command sequence is not going to depend on
> intermediate results, the server does not ).

Then that effectively makes the IF EXISTS useless, because it might in
fact exist by the time the transaction is committed.

> Bear in mind you
> are supposed to be informed of the result of your commands. I mean,
> you issue drop if exists and then you are allowed to issue a different
> command depending on the result of the drop, i.e., you may be willing
> to recreate the table if it existed and not create it if not, so the
> drop must record your intentions and lock the table definition, like a
> delete does with data rows.

The point of a DROP ... IF EXISTS should surely be that after the
command, that table should no longer exist, either because it didn't
exist or because it has been dropped (indeed, the result of "DROP...IF
EXISTS" is "DROP TABLE"). The idea that this can't be done at
commit-time because people might use the NOTICE response as some sort
of branch is (IMO) logically bankrupt: you can quite happily test for
existence without requiring any sort of atomic DROP, if that's your
intention.

Geoff

Adrian:

On Thu, Oct 6, 2016 at 4:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> This is how I can trigger the ERROR:

This is how you can trigger the ISSUE, IMO it is a correct behaviour.

Anyway, your example lacks some important details:
1.- SHOW your isolation level.
2.- SHOW your command interleaving.

Here is an example session where IT WORKS like you pretend, and the
table exists before starting showing those details:

\set PROMPT1 'session1-%`date +%H:%M:%S` [%x]'
session1-17:27:26 []start transaction isolation level serializable;
START TRANSACTION
session1-17:27:35 [*]drop table if exists ddl_test;
DROP TABLE
session1-17:27:44 [*]create table ddl_test(id int);
CREATE TABLE
session1-17:28:03 [*]commit;
COMMIT
session1-17:28:10
-----
\set PROMPT1 'session2-%`date +%H:%M:%S` [%x]'
session2-17:27:29 []start transaction isolation level serializable;
START TRANSACTION
session2-17:27:39 [*]drop table if exists ddl_test;
****GAP****
DROP TABLE
session2-17:28:10 [*]create table ddl_test(id int);
CREATE TABLE
session2-17:28:23 [*]commit;
COMMIT
session2-17:28:28

in the ***GAP*** mark session 2 was blocked, and it unblocked when I
issued commit in session 1. ( note the timestamps of command end are
the ones starting the next line, and except the one I marked they were
nearly instant )

Note how you can follow the command interleaving and the isolation level.

OTOH, as you point, not having the table shows the issue again:

session1-17:33:56 []start transaction isolation level serializable;
START TRANSACTION
session1-17:33:59 [*]drop table if exists ddl_test;
NOTICE:  table "ddl_test" does not exist, skipping
DROP TABLE
session1-17:34:08 [*]create table ddl_test(id int);
CREATE TABLE
session1-17:34:19 [*]commit;
COMMIT

session2-17:28:28 []start transaction isolation level serializable;
START TRANSACTION
session2-17:34:04 [*]drop table if exists ddl_test;
NOTICE:  table "ddl_test" does not exist, skipping
DROP TABLE
session2-17:34:13 [*]create table ddl_test(id int);
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(ddl_test, 2200) already exists.
session2-17:34:30 [!]

This time session 2 stopped at the create table and direcly aborted
when session1 commited. Correct, IMO, although antiestetic behaviour.
I think it is due to drop being a no-op if table did not exist, as
commands are not postponed ( it must show you the notice or not before
completing ), so you are just issuing to create commands for the same
table.


Your serial postponed execution is a nice desire, but I doubt it is necessary .

Francisco Olarte.

On Thu, Oct 6, 2016 at 9:31 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> This is how I can trigger the ERROR:
>
> Session 1:
>
> test=# begin ;
> BEGIN
> test=# drop table if exists ddl_test;
> NOTICE:  table "ddl_test" does not exist, skipping
> DROP TABLE
> test=# create table ddl_test(id int);
> CREATE TABLE
> test=# commit ;
> COMMIT
>
> Session 2 (concurrent to session1):
>
> test=# begin ;
> BEGIN
> test=# drop table if exists ddl_test;
> NOTICE:  table "ddl_test" does not exist, skipping
> DROP TABLE
> test=# create table ddl_test(id int);
> ERROR:  duplicate key value violates unique constraint
> "pg_type_typname_nsp_index"
> DETAIL:  Key (typname, typnamespace)=(ddl_test, 2200) already exists.
> test=# commit ;
> ROLLBACK

I recommend using a transactional advisory lock to serialize these.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Geoff:

On Thu, Oct 6, 2016 at 5:43 PM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> Nope. Serializable ignores the DROP, and then freezes on CREATE (and
> then fails when the first transaction COMMITs).

Yep, I tested it too.

> Which is also broken,
> because the transaction should fail if (at COMMIT time) the table
> cannot be CREATEd, but that's no reason to not create a table within a
> temporary namespace and perform actions against that table until the
> COMMIT, at which point the table can either be validated systemwide or
> the transaction rolled back.

Well, that maybe a nice new addition to the standard, but I doubt it
would fly. If you want that kind of behaviour you should implement
them app-side, they are not that difficult.


>
>> And drop table if exsits means if it exists when the
>> server executes your command, not on the future ( the server cannot
>> know if it will exist then, your own transaction may recreate it or
>> not. Maybe you know your command sequence is not going to depend on
>> intermediate results, the server does not ).
> Then that effectively makes the IF EXISTS useless, because it might in
> fact exist by the time the transaction is committed.

Lots of people find it useful as it is. Is just that normally people
do not try to interleave conditional drop plus create on interleaved
transactions without an upper level retrying loop and expect it to
magically work as they think it should.

And, following that train of thought unconditional drop is useless,
because by commit time table may not exists, and select is useless,
because at commit time rows may not exist or have other values.


> The point of a DROP ... IF EXISTS should surely be that after the
> command, that table should no longer exist, either because it didn't
> exist or because it has been dropped (indeed, the result of "DROP...IF
> EXISTS" is "DROP TABLE").

That exactly what is does ( unless your transaction aborts in the command ).

> The idea that this can't be done at
> commit-time because people might use the NOTICE response as some sort
> of branch is (IMO) logically bankrupt: you can quite happily test for
> existence without requiring any sort of atomic DROP, if that's your
> intention.

You are contradicting yourself. First you say after the command it
must not exist. Then you say to do it at commit time. If it is done at
commit time you cannot guarantee it does not exist after the command.
And using the branch for testing is not logically bankrupt, atomic
operations with responses are there for a second, this is why
processors have 'test and set' and 'compare-exchange' and similar.
This one is similar to a test and set, you set existence to false and
test whether it existed before. I can easily test and then set, but is
not the same as TAS. And the notice is not the reason it is not done
at commit time, the reason is the one you said, action must be taken
when you issue the command, not a magic convenient time in the future.


Francisco Olarte.

On 6 October 2016 at 16:57, Francisco Olarte <folarte@peoplecall.com> wrote:
> You are contradicting yourself. First you say after the command it
> must not exist. Then you say to do it at commit time. If it is done at
> commit time you cannot guarantee it does not exist after the command.

I'm not contradicting myself at all, and frankly I'd rather this
argument didn't degenerate into the level of playground sarcasm that
you seem to be employing.

_As far as the transaction is concerned_, after the command the table
should not exist. The rest of the system should not give two hoots
about what happens inside my transaction until after COMMIT. This is
how I can DROP a table, then roll it back and magically that table
still exists; it's how I can insert values into a table and roll back,
and those values aren't in the table; it's how I can delete rows from
a table, roll back and those values "magically" (as you put it) still
exist. So the DROP is done at COMMIT time, as far as everyone else is
concerned, but immediately as far as the transaction is concerned.

> And using the branch for testing is not logically bankrupt, atomic
> operations with responses are there for a second, this is why
> processors have 'test and set' and 'compare-exchange' and similar.

But that's NOT what this function is for. It's designed to remove a
table without producing an error if it didn't exist. The fact that its
RETURN value is "DROP TABLE", whether it dropped or not, shows this.

> And the notice is not the reason it is not done
> at commit time, the reason is the one you said, action must be taken
> when you issue the command, not a magic convenient time in the future

I've no idea what this paragraph means.

Geoff

On 6 October 2016 at 16:47, Kevin Grittner <kgrittn@gmail.com> wrote:
> I recommend using a transactional advisory lock to serialize these.

Thanks Kevin, that does seem like the best (although not particularly
pleasant) solution.

Geoff

On 10/06/2016 09:09 AM, Geoff Winkless wrote:
> On 6 October 2016 at 16:57, Francisco Olarte <folarte@peoplecall.com> wrote:
>> You are contradicting yourself. First you say after the command it
>> must not exist. Then you say to do it at commit time. If it is done at
>> commit time you cannot guarantee it does not exist after the command.
>
> I'm not contradicting myself at all, and frankly I'd rather this
> argument didn't degenerate into the level of playground sarcasm that
> you seem to be employing.

I do not see sarcasm, I see someone trying to work through what is a
complex scenario.

>
> _As far as the transaction is concerned_, after the command the table

What command?

> should not exist. The rest of the system should not give two hoots
> about what happens inside my transaction until after COMMIT. This is
> how I can DROP a table, then roll it back and magically that table
> still exists; it's how I can insert values into a table and roll back,
> and those values aren't in the table; it's how I can delete rows from
> a table, roll back and those values "magically" (as you put it) still
> exist. So the DROP is done at COMMIT time, as far as everyone else is
> concerned, but immediately as far as the transaction is concerned.
>
>> And using the branch for testing is not logically bankrupt, atomic
>> operations with responses are there for a second, this is why
>> processors have 'test and set' and 'compare-exchange' and similar.
>
> But that's NOT what this function is for. It's designed to remove a
> table without producing an error if it didn't exist. The fact that its
> RETURN value is "DROP TABLE", whether it dropped or not, shows this.

What function?

Part of the problem with trying to sort out what you want is working off
only snippets of code at a time.

So is it possible to show a complete example of what you are doing?

>
>> And the notice is not the reason it is not done
>> at commit time, the reason is the one you said, action must be taken
>> when you issue the command, not a magic convenient time in the future
>
> I've no idea what this paragraph means.
>
> Geoff
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Geoff Winkless <pgsqladmin@geoff.dj> writes:
> On 6 October 2016 at 16:47, Kevin Grittner <kgrittn@gmail.com> wrote:
>> I recommend using a transactional advisory lock to serialize these.

> Thanks Kevin, that does seem like the best (although not particularly
> pleasant) solution.

I'm a bit confused about exactly what the context is here.  AFAICS,
the fragment you quoted should work as you expect, as long as the
table always exists beforehand.  Then, the DROPs serialize the
transactions' access to the table and all is well[1].  On the other hand,
if the table *doesn't* exist beforehand, there is nothing to serialize
on and the behavior Adrian exhibited is what I'd expect.  (It's a bit
unfortunate that the complaint is about a duplicate type name not
a duplicate relation name: that's an artifact of the order in which
the rows are stored into the catalogs, and maybe we should try to
change it, because it's confusing.  But one way or the other you're
going to get a unique-index failure in whichever transaction is slightly
behind.)

But if all your transactions are doing this and only this to the table,
then there should never be an instant where the table doesn't exist.
Is there more DDL going on that you have not shown us?

            regards, tom lane

[1] at least, since PG 9.2 or thereabouts.

On 6 October 2016 at 18:25, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> I do not see sarcasm, I see someone trying to work through what is a complex
> scenario.

When someone talks about things "magically working as you think it
should" I see sarcasm. Perhaps I misread, in which case I apologise.

>> _As far as the transaction is concerned_, after the command the table
>
> What command?

The "DROP TABLE IF EXISTS" command.

>> But that's NOT what this function is for. It's designed to remove a
>> table without producing an error if it didn't exist. The fact that its
>> RETURN value is "DROP TABLE", whether it dropped or not, shows this.
>
> What function?

I'm mixing up terminologies (statement, not function). I'm still
talking about "DROP TABLE IF EXISTS" here.

Geoff

On 6 October 2016 at 18:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm a bit confused about exactly what the context is here.  AFAICS,
> the fragment you quoted should work as you expect, as long as the
> table always exists beforehand. Then, the DROPs serialize the
> transactions' access to the table and all is well.  On the other hand,
> if the table *doesn't* exist beforehand, there is nothing to serialize
> on and the behavior Adrian exhibited is what I'd expect.

I accept that this is how things are. I'm just surprised that "DROP
TABLE IF EXISTS" doesn't do the exists-test at commit time, rather
than effectively being "DROP TABLE IF
EXISTED-AT-SOME-RANDOM-POINT-IN-THE-PAST".

At the end of the day this isn't a massive deal - I can simply add
exception code around the failure, as Francisco suggested, or add
oplocks around the code (as per Kevin), or (I suppose) I could do an
individual transaction to CREATE TABLE IF NOT EXISTS as a separate
transaction before I start; it's just something that caught me out
because I didn't expect it to be a problem.

Geoff

On Thu, Oct 6, 2016 at 4:21 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> Hi
>
> I have code that does (inside a single transaction)
>
> DROP TABLE IF EXISTS mytable; CREATE TABLE mytable ....
>
> Occasionally this produces
>
> ERROR: duplicate key value violates unique constraint
> "pg_type_typname_nsp_index" DETAIL: Key (typname,
> typnamespace)=(mytable, 2200) already exists.
>
> I can get away from this by using CREATE TABLE IF NOT EXISTS in the
> same code, but there's the potential that the wrong data will end up
> in the table if that happens, and it also seems a little.... odd.
>
> Would you not expect this transaction to be atomic? ie at commit time,
> the transaction should drop any table with the same name that has been
> created by another transaction.

Transactions do not guarantee atomicity in the sense that you mean.
MVCC rules (which DDL generally fall under) try to interleave work as
much as possible which is the problem you're facing.   What you want
is fully serialized creation which can be accomplished with advisory
lock or (better, imo) a leading

LOCK TABLE mytable;

Also, this is not a good pattern.  You ought to be using temp tables
or other mechanics to store transaction local data.

merlin

On 10 October 2016 at 14:49, Merlin Moncure <mmoncure@gmail.com> wrote:
> MVCC rules (which DDL generally fall under) try to interleave work as
> much as possible which is the problem you're facing.

Mmff. Yes, that exposes a fundamental misunderstanding on my part: I
had thought that under MVCC things were done independently and
resolved at COMMIT time, as opposed to potentially-conflicting
transactions resulting in one transaction blocking _mid-transaction_
until the other resolves, as it does.

So I suppose the logic goes, it's not clear that the DROP / CREATE
results in a potential conflict until the table is created, so since
both transactions drop a non-existent table, they both then try to
create the table, and I get my error. I had thought that the DDL would
simply have its own view on the data and therefore be able to do all
of its work up to COMMIT, but on reconsidering I can see that the
amount of overhead involved in the COMMIT would be phenomenal.

Having said all of that, I'm confused as to why CREATE TABLE in tr1
doesn't block a subsequent DROP TABLE IF EXISTS in tr2.

So if, in two psql sessions you run (shown in order of execution):

tr1:
   BEGIN;
   DROP TABLE IF EXISTS mytable;
   CREATE TABLE mytable (test int);

tr2:
   BEGIN;
   DROP TABLE IF EXISTS mytable; -- could block here, no?
   CREATE TABLE mytable (test int); -- actually blocks here

> Also, this is not a good pattern.  You ought to be using temp tables
> or other mechanics to store transaction local data.

The data isn't transaction-local. Having said that, the _actual_
pattern is much worse than that, but it's not my design, I just have
to work with it.

Geoff