Re: Transactional-DDL DROP/CREATE TABLE

On Thu, Oct 6, 2016 at 4:21 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)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