machine-parseable object descriptions - Mailing list pgsql-hackers

For Dimitri's patch to add support for dropped objects in event
triggers, there's an open question about how to report objects that are
being dropped in a tabular format.  What I proposed last had three
columns: (type, schema, identity).  The "type" is a description of the
object class; I propose the following list:

table
view
materialized view
foreign table
sequence
...
function
aggregate
type
cast
collation
table constraint
domain constraint
default value
...
operator of access method
function of access method
...
table column
view column
materialized view column
...
default acl for relations
default acl for sequences
default acl for functions
default acl for types

Ellipses hide uninteresting examples.  Opinions on this please?


After the object type we have the schema, which would be NULL for
objects that don't belong to schemas (extensions, access methods, casts,
languages, etc).

Then we have the identity, which is a texualt representation of the
other stuff needed to uniquely identify the object being referred to.
For most stuff it's just the name, but for other things it is a bit more
complex.  For example, for a function it'd be the function signature;
for a table (and for most other object classes) it's the table name.
For columns, it's the qualified column name i.e.
<tablename>.<columnname>.  Note the schema name never appears here, even
if the object is not in path; the whole point of this is to be
machine-parseable and we have the schema separately anyway.  So some
tweaks to functions such as format_type_internal are necessary, to
supress schema-qualifying when not wanted.

So the main thing here is about the particular format for each specific
object class.  For example, it seems to me we need to schema-qualify
type names in function signatures.  For casts I propose "(%s as %s)",
where each %s is a schema-qualified type name.  For constraints we can
use "%s on %s" where there's no schema qualification (the schema column
carries the table's schema, for the constraint must always belong to the
same schema).  For operators, we use "%s(%s, %s)" where the first %s is
the operator name and the other two are schema-qualified type names.
For default values (pg_attrdef tuples) we use "for %s" where the %s is
the column's identity (i.e. <tabname>.<colname>; again not
schema-qualified because the attrdef "is" in the same schema as the
table)).  For operator classes and families we use "%s for %s" where the
first one is the opclass/opfam name, and the second is the AM name.  For
pg_amop and pg_amproc objects we use "%d (%s, %s) of %s": strategy
number, qualified type names, and AM name.  For rules and triggers, "%s
on %s": rule name and relation name (unqualified).  For all the rest, we
simply use the unqualified object name.

pg_default_acl objects are the funniest of all, and they end up as
"belonging to role %s in schema %s".  I am especially open to
suggestions in this one.

I think this kind of machine-parseable object representation would be
useful for cases other than event triggers, so I am thinking in
committing this part before the pg_dropped_objects patch.  I haven't
split it out yet from the event triggers patch; I will be sending a
patch later, in the meantime I welcome comments on the above.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services