Re: Checking for changes in other tables

From: CR Lender <crlender(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Checking for changes in other tables
Date: 2013-04-28 22:14:15
Message-ID: 517D9F37.4030402@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry for the late reply, I had to give this some time to digest.
I'm still trying to wrap my head around the concept that triggers don't
"belong" to a table...

On 2013-04-26 17:15, Richard Huxton wrote:
> On 26/04/13 10:01, CR Lender wrote:
> I think this is more a problem of terminology rather than your current
> triggers. Triggers aren't really "part" of a table, but they are
> observing it, so it's a sensible place to list them when viewing a
> table-definition in psql. There's no reason the trigger function is even
> in the same schema as the targetted table.

Okay, that's true for the trigger function (procedure), but not for the
trigger itself. As far as I can tell, triggers aren't directly
adressable, except through their tables. I can have two separate
triggers with the same name (in the same schema) on different tables.

> How would it feel if the syntax was more like the following?
>
> CREATE TRIGGER ... OBSERVING UPDATES ON persons ...
>
> or even
>
> PUBLISH UPDATE,INSERT,DELETE ON persons AS person_changes;
> SUBSCRIBE TO person_changes CALLING PROCEDURE ...;
>
> A different "feel", but no difference in behaviour.

Yes, I see your point; that's how it would look if triggers were
completely separate from their tables, in a pub/sub way. I guess I
wouldn't have a bad feeling about this if I could define them like that.

On the other hand: triggers can't just subscribe to anything, they can
only react to events on a single table; they are automatically and
silently deleted when the table they are observing is dropped; they can
be enabled or disabled via ALTER TABLE, not ALTER TRIGGER.

AFAICS, there's also no \d command in psql to list triggers; they are
only shown when the observed table is inspected with \d.

All of this makes it hard for me to see a trigger as a detached observer
rather than a "behavior" of a table, so to speak.

I need to think about this some more.

Thanks for your help,
crl

In response to

Browse pgsql-general by date

  From Date Subject
Next Message roshan2013 2013-04-29 02:55:47 Save postgresql node to catch only DML except delete queries
Previous Message Simon Riggs 2013-04-28 19:42:26 Re: Implementing DB2's "distinct" types