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
