Limits on PostgreSQL schema changes inside transac

问题:

My database background is with Oracle, so I was surprised to discover that Postgres includes schema changes in transactions - if you begin one, create a table and then rollback, the table goes away. It works for adding and removing columns as well. Obviously this is very nice.

We're about to make some changes to the way we deploy schema deltas that rely on this feature. Before we do, I'd like to find out how far the transactional guarantee extends, but I can't find any information on it in the documentation. I assume I'm just using the wrong search terms - my searches just go to big lists of commands which include the words 'transaction', 'create' and 'table'.

Can anyone give me some pointers to docs or discussions about transactional schema changes in Postgres? (We're using 8.2.13, although we'll be upgrading in the not too distant future.) Or just some details about statement that won't be included in the transaction?

回答1:

According to quick grep on docs, these commands cannot be executed in transactions:

• cluster
• commit prepared
• create database
• create tablespace
• discard
• drop database
• drop tablespace
• rollback prepared
• vacuum

回答2:

• nextval and setval operations on sequences are never rolled back.
• REINDEX DATABASE
• REINDEX SYSTEM

There's an article about transactional DDL on the PostgreSQL Wiki

回答3:

As of version 9.1 of PosgreSQL, it appears that schema create statements are indeed transactional.

select * from pg_namespace where nspname = 'foo';
 nspname | nspowner | nspacl 
---------+----------+--------
(0 rows)

begin;
create schema foo;
rollback;

select * from pg_namespace where nspname = 'foo';
 nspname | nspowner | nspacl 
---------+----------+--------
(0 rows)

begin;
create schema foo;
commit;

select * from pg_namespace where nspname = 'foo';
 nspname | nspowner | nspacl 
---------+----------+--------
 foo     |       10 | NULL
(1 row)

回答4:

Two sessions concurrently running "CREATE TABLE" is a little racy:

http://postgresql.1045698.n5.nabble.com/Errors-on-CREATE-TABLE-IF-NOT-EXISTS-td5659080.html

CREATE TABLE does a preliminary check to see whether a name conflict exists. If so, it either errors out (normally) or exits with a notice (in the IF NOT EXISTS case). But there's a race condition: a conflicting transaction can create the table after we make that check and before we create it ourselves.

Both the linked thread initiator and I hit this in automated testing environments, so it's not much more than an annoyance there. (I doubt it would effect your schema migrations, but it can be seen as a limit on ddl changes)

perl -MDBI -E 'fork; fork; $d=DBI->connect("dbi:Pg:dbname=$ENV{USER}");' \
 $d->do("CREATE TABLE a (b int)")'
DBD::Pg::db do failed: ERROR:
   duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(a, 2200) already exists. at -e line 1.

回答5:

From the manual, section 13.5 (Currency Control: Caveats):

Some DDL commands, currently only TRUNCATE and the table-rewriting forms of ALTER TABLE, are not MVCC-safe. This means that after the truncation or rewrite commits, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the DDL command committed. This will only be an issue for a transaction that did not access the table in question before the DDL command started […]

Regarding table rewriting the ALTER TABLE section mentions

Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table […] to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed […] Adding or removing a system oid column also requires rewriting the entire table.