4 ms·

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 i

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.

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.