Обсуждение: Triggers on system catalog

On Mon, Mar 28, 2011 at 10:19 AM, Shridhar Polas
<shridharpolas@gmail.com> wrote:
> Hi,
> Is there a way to create triggers on system catalog tables like
> pg_class, pg_attribute etc...?

No, this isn't supported, and, since the normal alterations of the
schema involve manipulating these tables, such an addition would be
fraught with the risk of breaking system behavior.

- Your trigger function needs to be aware of *ALL* functionalities
that involve those tables. For instance, a trigger on pg_class needs to be able to cope with
changes relating to views, sequences, inherited tables, not just its
use for tables.

- There are liable to be timing issues, as a series of updates to
these tables won't in general be consistent until *all* the updates
are done. E.g. - consider that when a table is created, there'd be an insert
to pg_class, and then, later, inserts to pg_attribute.  A trigger
firing against pg_class would capture a partially-completed table
creation, which mayn't be what you were expecting.

A proposal to adding triggers to system catalog tables won't be
terribly popular.
--
http://linuxfinances.info/info/postgresql.html

On Mar 29, 2011, at 2:17 PM, Christopher Browne <cbbrowne@gmail.com> wrote:
> A proposal to adding triggers to system catalog tables won't be
> terribly popular.

Well, I'd support it if I thought it had any chance of actually working, but I don't.

I do think we need some kind way of capturing DDL events, though. I wonder if the object-access-hook stuff KaiGai and I
didto support SE-PostgreSQL could be extended to meet this need... 

...Robert

* Robert Haas (robertmhaas@gmail.com) wrote:
> I do think we need some kind way of capturing DDL events, though. I wonder if the object-access-hook stuff KaiGai and
Idid to support SE-PostgreSQL could be extended to meet this need...
 

My inclination would be 'probably', but it's not likely to really be the
way we'd want to provide generalized DDL triggers..  Perhaps we might
flip it around and have the SE-PG things be called from a DDL-trigger
system, but as it's a security-type check, I'm not hugely thrilled by
that idea either.
Thanks,
    Stephen

On Tue, Mar 29, 2011 at 9:40 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> I do think we need some kind way of capturing DDL events, though. I wonder if the object-access-hook stuff KaiGai
andI did to support SE-PostgreSQL could be extended to meet this need...
 
>
> My inclination would be 'probably', but it's not likely to really be the
> way we'd want to provide generalized DDL triggers..

I guess I was imagining that DDL triggers would be primarily important
for things like Slony, that are already writing C code anyway, but
maybe that's overly optimistic...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Mar 29, 2011 at 08:21:04PM -0400, Robert Haas wrote:
> On Mar 29, 2011, at 2:17 PM, Christopher Browne <cbbrowne@gmail.com> wrote:
> > A proposal to adding triggers to system catalog tables won't be
> > terribly popular.
> 
> Well, I'd support it if I thought it had any chance of actually
> working, but I don't.
> 
> I do think we need some kind way of capturing DDL events, though. I
> wonder if the object-access-hook stuff KaiGai and I did to support
> SE-PostgreSQL could be extended to meet this need...

Jan Wieck sketched out a proposal, which I'll post in more detail when
I've transcribed it and checked that with him.  Part of it is to fire
triggers on the command completion code, and there are some code
cleanups that that would depend on.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Wed, Mar 30, 2011 at 9:59 AM, David Fetter <david@fetter.org> wrote:
> On Tue, Mar 29, 2011 at 08:21:04PM -0400, Robert Haas wrote:
>> On Mar 29, 2011, at 2:17 PM, Christopher Browne <cbbrowne@gmail.com> wrote:
>> > A proposal to adding triggers to system catalog tables won't be
>> > terribly popular.
>>
>> Well, I'd support it if I thought it had any chance of actually
>> working, but I don't.
>>
>> I do think we need some kind way of capturing DDL events, though. I
>> wonder if the object-access-hook stuff KaiGai and I did to support
>> SE-PostgreSQL could be extended to meet this need...
>
> Jan Wieck sketched out a proposal, which I'll post in more detail when
> I've transcribed it and checked that with him.  Part of it is to fire
> triggers on the command completion code, and there are some code
> cleanups that that would depend on.

See <http://wiki.postgresql.org/wiki/DDL_Triggers>

That already captured an understanding of this, which might already be
nearly adequate.
--
http://linuxfinances.info/info/linuxdistributions.html

On 3/30/2011 9:49 AM, Robert Haas wrote:
> On Tue, Mar 29, 2011 at 9:40 PM, Stephen Frost<sfrost@snowman.net>  wrote:
>>  * Robert Haas (robertmhaas@gmail.com) wrote:
>>>  I do think we need some kind way of capturing DDL events, though. I wonder if the object-access-hook stuff KaiGai
andI did to support SE-PostgreSQL could be extended to meet this need...
 
>>
>>  My inclination would be 'probably', but it's not likely to really be the
>>  way we'd want to provide generalized DDL triggers..
>
> I guess I was imagining that DDL triggers would be primarily important
> for things like Slony, that are already writing C code anyway, but
> maybe that's overly optimistic...
>

Slony is using C code in every performance critical path. Other than 
that, we are perfectly happy with PL/pgSQL code.

What I would envision for DDL triggers is that they first don't fire on 
an object type, but rather on a command completion code, like "CREATE 
TABLE" or "DROP SCHEMA".

To do anything useful with that of course would require that all DDL 
does go through tcop's ProcessUtility and actually synthesizes a proper 
Utility parsetree. That isn't the case today, so there would be some 
previous clean up work to be done.


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

On Mar 30, 2011, at 3:45 PM, Jan Wieck wrote:
> What I would envision for DDL triggers is that they first don't fire on an object type, but rather on a command
completioncode, like "CREATE TABLE" or "DROP SCHEMA". 
>
> To do anything useful with that of course would require that all DDL does go through tcop's ProcessUtility and
actuallysynthesizes a proper Utility parsetree. That isn't the case today, so there would be some previous clean up
workto be done. 

For those that missed my announcement at PG East; we're willing to sponsor some/all of this work (depending on how much
moneywe're talking). 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net