Re: Notes on converting from MySQL 5.0.x to PostgreSQL

Ron Johnson <ron.l.johnson@cox.net> writes:
> Scott Marlowe wrote:
>> However, the more interesting thing here, is that every
>> statement, including DDL is transactable, except for a couple of
>> big odd ones, like create database. So, in postgresql, you can do:

> But isn't that what it means to be "transactional"?  Or am I spoiled
>  by my "big, expensive enterprise database"?

Being able to roll back DDL (table-schema modifications) isn't that
common.  Since PG keeps most of its schema information in tables,
we have it easier than some other systems supporting DDL rollback,
but it's still tricky.  As an example, a long time ago we used to
name table files after the table and database directories after the
database, which made it easy to see what was what under $PGDATA,
but prevented a lot of DDL from being transactional.  For instance

    BEGIN;
    DROP TABLE foo;
    CREATE TABLE foo (some-new-definition);
    ROLLBACK;

couldn't work because there would need to be two physical files named
foo in the interim until you commit or roll back.  ALTER TABLE RENAME
had some related problems.  Now we name all the filesystem objects using
OIDs that can be chosen to never collide, even if they belong to
database objects with similar names.

Last I checked, mysql was still using table names for file names,
so they're on the wrong side of this.

            regards, tom lane