PostgreSQL has transactional DDL for most database objects (certainly tables, indices etc but not databases, users). However practically any DDL will get an ACCESS EXCLUSIVE lock on the target object, making it completely inaccessible until the DDL transaction finishes. Also, not all situations are quite handled- for example, if you try to select from table foo while another transaction is dropping it and creating a replacement table foo, then the blocked transaction will finally receive an error rather than finding the new foo table. (Edit: this was fixed in or before PostgreSQL 9.3)

CREATE INDEX ... CONCURRENTLY is exceptional, it uses three transactions to add an index to a table while allowing concurrent updates, so it cannot itself be performed in a transaction.

Also the database maintenance command VACUUM cannot be used in a transaction.

I'd argue that if I try to select from table foo while another transaction is dropping and recreating it, then I an OK with the old version or error. I am not OK with the new version, because it was not committed yet, so I must not see it. I am OK with an error, because in concurrent transactional access one has to be prepared to restart transactions anyway. If errors happen more often than necessary it might reduce performance, but it is still correct.
–
Jan HudecJun 4 '14 at 14:39

@JanHudec: you won't see an uncommitted version of the new table, only the result of the entire transaction that dropped/recreated it. i.e. a transaction that drops, recreates and repopulates a table is effectively atomic wrt other processes selecting from that table. (but everything will get blocked as soon as they even try to read the table's schema)
–
araqnidJun 4 '14 at 16:08