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.
Best Answer
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
timestampcolumn or simplebooleanflag in the underlying table might suffice to mark rows that should trigger delayed events.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_SCHEMAvariable set.This related answer has more information and an example:
How do I list all columns for a specified table