Re: Interest in serializing composite types?

On 2014-08-24, 6:32 PM, Darren Duncan wrote:
> I don't know if it does this (please tell me), but you know what would be an
> extremely useful feature in Postgres itself?  It is there being a simple fast
> way to query the database if it had any schema changes (anything DDL does) since
> it was last asked the question.  This could take the form of a LISTEN/NOTIFY of
> some system-defined channel.  If we assume in practice that schema changes are
> infrequent, then we can safely memoize any schema knowledge we have and know it
> is still valid until we get such a message saying there was a change, and then
> we flush the cache and check what we need to know again.
>
> The aforementioned would be widely useful and relatively simple to provide, so
> if it doesn't exist I think I'll formally propose it to hackers.
>
> Maybe it can be connected to the in-development feature about DDL triggers, or
> be implemented in terms of such, eg a DDL trigger fires off a NOTIFY.  But if it
> was provided by the system automatically, users won't have to explicitly define
> triggers / alter the schema in order to have the feature.

Replying to myself, I looked it up.

Starting with Postgres 9.3, which has been production-ready status for 11 months 
now, it looks like most of what I proposed is possible.

I'm not sure if I'm interpreting things right, but perhaps it is as simple as 
doing a LISTEN on "ddl_command_start", unless that's not the same kind of event.

Otherwise, here's longer-hand for what I meant:

CREATE OR REPLACE FUNCTION report_schema_did_change()
   RETURNS event_trigger
  LANGUAGE plpgsql
   AS $$
BEGIN
   NOTIFY event_schema_did_change;
END;
$$;

CREATE EVENT TRIGGER trg_schema_did_change ON ddl_command_start
    EXECUTE PROCEDURE report_schema_did_change();

... in which case you LISTEN on "event_schema_did_change".

See http://www.postgresql.org/docs/9.3/static/event-triggers.html et al.

Unfortunately that doesn't trigger for some kinds of DDL, eg on whole schemas, 
but it does trigger for most individual kinds of schema objects.

Chris, so my point is, probably a best way to handle memoization is to take 
advantage of said feature of Postgres 9.3+ so one can get more performance in 
DBD::Pg or tools using it, in the most effective way.

-- Darren Duncan