Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).

Reviewing patterns for the following problem: I have a matview that needs to be updated if an underlying condition is altered (in this case, an inclusion BOOLEAN).

Was considering implementing a TRIGGER, on update, which would then either execute the matview creation UDF or possibly call 'NOTIFY' to be handled by some listening event, perhaps at a later point in time as the immediate matview update isn't necessarily required.

Workflow :

  • UPDATE made to some underlying table.

  • TRIGGER is executed.

  • (Obtain schema information here).

  • Either,

    • EXECUTE matview creation
    • NOTIFY command for queue / processing at a later time.

First question, am I on the right track here or are there other, more efficient / effective patterns I should review?

Given the nature of my multi-tenant design, is it possible to obtain the current schema from the underlying table from which the trigger event was called as I'll need that information to pass into my matview creation UDF.

  • I found the answer to my second question, in case anyone else is looking : TG_TABLE_SCHEMA and TG_TABLE_NAME TRIGGERS – mjpeters_ May 2 '13 at 14:00

If the effect isn't required until a later point in time, I would execute the payload at that later point in time and not via trigger. This way you can collect multiple updates on the underlying table and avoid redundant calls of the DDL script.

Depending on your circumstances, a timestamp column or simple boolean flag in the underlying table might suffice to mark rows that should trigger delayed events.

is it possible to obtain the current schema from the underlying table ..

Yes. You can query practically any possible detail from the system catalogs or from the standardized (but much slower) information schema. Furthermore, in a trigger procedure you have the TG_TABLE_SCHEMA variable set.

This related answer has more information and an example:
How do I list all columns for a specified table

  • This solution, a timestamp column or simple boolean flag will work perfectly, thanks! – mjpeters_ May 1 '13 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.