Re: Transactional-DDL DROP/CREATE TABLE

On 6 Oct 2016 12:06 p.m., "Francisco Olarte" <folarte(at)peoplecall(dot)com> wrote:
>
> On Thu, Oct 6, 2016 at 11:21 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)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