Por que alguns DBMS não permitem a reversão de certas instruções DDL?

A razão pela qual isso funciona no PostgreSQL é que os catálogos do sistema são tabelas regulares. Portanto, criar uma nova função, por exemplo, requer apenas inserir uma linha na pg_proctabela, alterar o valor padrão de uma coluna requer apenas fazer uma atualização em alguma linha em pg_attrdef, e assim por diante. Como as tabelas são transacionais de qualquer maneira, você quase teria que sair do seu caminho para não funcionar dessa maneira. (Muitos detalhes de implementação dolorosos foram omitidos aqui. ;-))

Suponho, não conhecendo o código-fonte, que outros mecanismos de banco de dados usam algumas estruturas internas personalizadas para representar as informações do catálogo do sistema. E então eles teriam que colocar um esforço extra, muito esforço extra provavelmente, para fazer o DDL transacional funcionar, e aparentemente não é uma prioridade para eles.

O outro lado disso é que esta é a razão pela qual as atualizações de versões principais do PostgreSQL são tão dolorosas. Outros produtos podem presumivelmente projetar suas estruturas internas de metadados com alterações e atualizações em mente e, portanto, não há problemas com a atualização para uma nova versão principal. No PostgreSQL, não há como alterar uma tabela de catálogo do sistema para repentinamente parecer uma versão mais recente de uma tabela de catálogo do sistema, pelo menos não enquanto mantém o sistema online, porque isso exigiria acesso aos catálogos do sistema. Urgh.

A maioria não? Desapontamento.

Eu uso principalmente SQL Server e ele faz. Eu sei que a Oracle não, mas pensei que a Oracle poderia ser uma aberração.

No SQL Server, tenho certeza de que você pode executar várias instruções DDL em uma única transação, embora também ache que haja algumas restrições (das quais esqueci). Você pode criar, alterar ou descartar a maioria das coisas e revertê-las, se quiser. O Red-Gate SQL Compare (uma ferramenta que adoro) tira proveito disso.

O problema de fazer isso é que o escopo da transação fica bastante interessante... Ao envolver os catálogos do sistema em uma transação de atualização (DDL), você corre o risco de tomar alguns bloqueios realmente importantes e pode bloquear o acesso aos catálogos do sistema. Os usuários não podem fazer muito se suas consultas não encontrarem suas tabelas nos catálogos!

Em suma, porém, é útil poder incluir DDL em uma transação de várias instruções.

Mais útil, o comando SQL Server DDL TRUNCATE também pode ser um elemento de uma transação de várias instruções . Você pode truncar uma tabela de destino (muito rápido), construí-la e, em seguida, fazer um commit se gostar do resultado. Se algo der errado, você reverte e pronto!, é como se você nunca tivesse mexido na mesa. O espaço de registro também é minimizado. Eu aproveito isso com bastante frequência.

No SQL Server, podemos reverter as instruções DDL, sem usar o auto commit no final da instrução. Em outros SGBDs não sei, mas lembro que no Oracle não dá para fazer o mesmo. Acredito que seja específico de cada SGBD, não sei o que diria o padrão SQL sobre isso, mas tenho certeza que nenhum produtor implementa 100% o padrão.

Há uma pergunta semelhante no SO: é possível executar várias instruções DDL dentro de uma transação (dentro do SQL Server)?

O Oracle tem análise de consulta compartilhada, então um SELECT * FROM table_a feito por uma sessão é (normalmente) igual ao de outra sessão. Isso seria interrompido se uma sessão pensasse que havia dez colunas na tabela e outra pensasse que havia onze.