Comment by derefr | Hacker News Reader

Comment by derefr | Hacker News Reader

I would like to point out that SQL itself standardizes a set of tables (called the information_schema) that can be queried, in a completely uniform way, to learn pretty much every (SQL-standard-formalized) fact about your database.

information_schema doesn't cover anything the DBMS is doing that's not standardized by SQL, though. This would mean that, for PG in particular, things like partitioning/inheritance, tablespaces, and the distinction between roles and schemas, aren't represented in the information_schema. (The objects do show up, but only as their SQL-standard "superclass"—e.g. partitioned tables just look like a set of regular tables + constraints + triggers + rewrite rules; table columns with special types look like their underlying storage type; etc.)

The information_schema also doesn't cover the pragmatics of the administration of the database instance itself, like, for PG, the type of data you'd access through any of the pg_stat_ tables.

But neither of these concerns are really relevant if you're building a generic tool that wants to prod at SQL-standard database objects, I would think. Just use the information_schema!

---

I would like to link here the relevant part of the SQL standards document, because it's actually a very helpful and thorough reference. (Even if you never program for more than one DBMS, just learning how it defines words like "catalog", "schema", and "object" will make reading any particular DBMS's docs 10x easier.)

But, sadly, the SQL standard itself is a proprietary document that you have to purchase! (See here: https://modern-sql.com/standard) This is a pretty odd thing, considering that unpaid developers of FOSS systems like PG need to reference the standard for compliance.

> But, sadly, the SQL standard itself is a proprietary document that you have to purchase!

This is a massive problem with standards in general. ISO 8601, for example, the international standard for date and time representation that everyone should be using, similarly consists of multiple documents that you have to purchase at significant prices. The part covering "basic rules" costs 158 CHF (160 USD) and the part covering "extensions" costs 178 CHF (180 USD).

I think this is seriously hampering the adoption of standards outside of industries that explicitly require compliance, and particularly in open source. If you can't even know whether you're compliant without spending serious money, why care about compliance at all?

To derefr's point, I'll also point to the "meta" PostgreSQL extension (part of the Aquameta project, which I maintain) whose purpose is to provide a more readable version of information_schema:

https://github.com/aquametalabs/meta http://blog.aquameta.com/intro-meta/

information_schema is pretty unruly, and pg_catalog is just crazy, in terms of readability, but if you just want to do some simple introspection, meta might be helpful.

That said, I see value in a nice Javascript object that is easy to traverse and can be retrieved all at once.

There's broken link in your article to https://github.com/aquametalabs/aquameta/tree/master/core/00...

Right, and anything else should be in the pg_catalog; information_schema are just views over that. https://www.postgresql.org/docs/current/catalogs.html

True! Good point and clarification.

The nice aspect of pg-structure for me was getting all of that information_schema goodness already pulled out into hashes/DTOs from a 1-line "await pgStructure(...)" call.

I.e. w/o pg-structure, I'd probably end up writing a mini/hacky version of it that did the same thing, "do these ~3-4 SQL queries and mash them into some nice hashes for my code generator to consume".

Which is not terrible, but it's easier to just pull in another npm package. :-)

As the author of a postgres-specific schema inspection tool, I found I very quickly ran into limitations of information_schema. Many of the critical details are simply not there.

As a DBA every full fledged system I have worked with veers off of information schema almost immediately, and you are going to be forced to implement some special set of queries if you want an accurate version of any real db schema.

This will also likely reflect the current version of the database engine you are working with, there's no consistent approach to know everything for all databases for all versions.

Reply on news.ycombinator.com