4 ms·

> Technical goals of the project include: > Allow complete management of the database using only INSERT, UPDATE and DELETE commands (expose the DDL as DML) The

by e12e 7y ago

> Technical goals of the project include: > Allow complete management of the database using only INSERT, UPDATE and DELETE commands (expose the DDL as DML) There might be other reasons - but note that postgres actually supports transactions and rollback of DDL - Oracle with full enterprise lisence has something similar (there's undo/a "trashcan" style cache for schéma modifications). But in general (as far i understand; I have not used this in anger) pg lets you simply BEGIN drop table (...) ALTER table (...) (who's, something wrong) ROLLBACK. It's very neat, and not generally supported.

doctor_eval 7y ago
Yes almost all DDL is transactional in PG, and it works as you say. You can create tables, drop indexes, replace functions, update views - and then roll it all back. There are a very small number of DDL statements that you can’t do in a transaction, but the devs are fixing them (adding a value to an enum is the only one that comes to mind) We use it to great effect when upgrading schemas.
- pritambaral 7y ago
  > adding a value to an enum is the only one that comes to mind Adding enum values in a transaction is now supported in PG 12 as long as you don't try to use the new value in the same transaction.
cryptonector 7y ago
Yes, yes, but, it's nice to be able to treat schema as relational data itself. Indeed, the information_schema and pg_catalog already let you do that, but the information_schema is incomplete, and the pg_catalog is hard to use and unstable (that is, each release can change the pg_catalog backwards-incompatibly). Besides letting you read schema via normal SELECTs on a well-designed meta-schema, something like Aquameta can let you run DMLs as DDls too. That means you can now generate and execute DMLs dynamically but without needing EXECUTE -- you can just have a bunch of normal INSERT/UPDATE/DELETE statements (including via CTEs) and generate schema from other data the same way you'd generate data from data. I think that's a big plus. But then I've worked with a database before that did this schema as data-in-a-meta-schema thing, and I find it very comfortable. For example, without a metaschema you have to use CREATE THING IF NOT EXISTS then ALTER THING ... in order to apply schema changes. Whereas with something like Aquameta you just INSERT INTO ... WHERE NOT EXISTS ... (or with ON CONFLICT DO ...) and that's that. You can have one set of DDLs that create schema, and the same set of DDLs also updates schema. That's wonderful.
- e12e 7y ago
  I'm still not entirely clear on why this is a good thing. Is it a preference - sort of hygienic unhygienic macros - for data structures? Do you end up with old data in old schema - or is there some implicit conversion? Is a UPDATE that drops a column equivalent to dropping a column - and can you roll it back normally?
  - cryptonector 7y ago
    It's like functions that return functions: SQL that generates SQL. Normally, in PG land, one generates code using SQL, using the format() function, then one EXECUTEs it. That would still be the case for generated FUNCTION bodies, naturally, but for everything else you could just use DDLs on a small and well-tested metaschema that does this work for you. Just as in Lisp you might rather not use eval but instead use macros, here you'd prefer to use DDLs on a metaschema over DMLs. Hygiene is one of the motivators. And yes, you could DELETE from a view that represents columns instead of using ALTER TABLE ... DROP COLUMN ... All the usual transactional semantics apply. You can BEGIN, delete that row, and rollback, leaving the DB unchanged.

by e12e 7y ago

doctor_eval 7y ago

Yes almost all DDL is transactional in PG, and it works as you say. You can create tables, drop indexes, replace functions, update views - and then roll it all back. There are a very small number of DDL statements that you can’t do in a transaction, but the devs are fixing them (adding a value to an enum is the only one that comes to mind) We use it to great effect when upgrading schemas.

pritambaral 7y ago
> adding a value to an enum is the only one that comes to mind Adding enum values in a transaction is now supported in PG 12 as long as you don't try to use the new value in the same transaction.

cryptonector 7y ago

Yes, yes, but, it's nice to be able to treat schema as relational data itself. Indeed, the information_schema and pg_catalog already let you do that, but the information_schema is incomplete, and the pg_catalog is hard to use and unstable (that is, each release can change the pg_catalog backwards-incompatibly). Besides letting you read schema via normal SELECTs on a well-designed meta-schema, something like Aquameta can let you run DMLs as DDls too. That means you can now generate and execute DMLs dynamically but without needing EXECUTE -- you can just have a bunch of normal INSERT/UPDATE/DELETE statements (including via CTEs) and generate schema from other data the same way you'd generate data from data. I think that's a big plus. But then I've worked with a database before that did this schema as data-in-a-meta-schema thing, and I find it very comfortable. For example, without a metaschema you have to use CREATE THING IF NOT EXISTS then ALTER THING ... in order to apply schema changes. Whereas with something like Aquameta you just INSERT INTO ... WHERE NOT EXISTS ... (or with ON CONFLICT DO ...) and that's that. You can have one set of DDLs that create schema, and the same set of DDLs also updates schema. That's wonderful.

e12e 7y ago
I'm still not entirely clear on why this is a good thing. Is it a preference - sort of hygienic unhygienic macros - for data structures? Do you end up with old data in old schema - or is there some implicit conversion? Is a UPDATE that drops a column equivalent to dropping a column - and can you roll it back normally?
- cryptonector 7y ago
  It's like functions that return functions: SQL that generates SQL. Normally, in PG land, one generates code using SQL, using the format() function, then one EXECUTEs it. That would still be the case for generated FUNCTION bodies, naturally, but for everything else you could just use DDLs on a small and well-tested metaschema that does this work for you. Just as in Lisp you might rather not use eval but instead use macros, here you'd prefer to use DDLs on a metaschema over DMLs. Hygiene is one of the motivators. And yes, you could DELETE from a view that represents columns instead of using ALTER TABLE ... DROP COLUMN ... All the usual transactional semantics apply. You can BEGIN, delete that row, and rollback, leaving the DB unchanged.