Triggers on system catalog

Hi,

Is there a way to create triggers on system catalog tables like
pg_class, pg_attribute etc...?

Thanks,
Shridhar

On Mon, Mar 28, 2011 at 10:19 AM, Shridhar Polas <shridharpolas(at)gmail(dot)com>wrote:

> Hi,
>
> Is there a way to create triggers on system catalog tables like
> pg_class, pg_attribute etc...?
>

No, Postgres does not support triggers on system catalogs; we do not have
DDL triggers either, if that's what you were trying to achieve.

Regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Yes, you'd need to hack Postgres to add that capabilty, but that's too
darned difficult since Postgres internally does not use SQL to perform the
DML operations on system catalog. I'd suggest searching for past discussions
on -hackers around this topic.

Regards,

On Tue, Mar 29, 2011 at 5:37 AM, Shridhar Polas <shridharpolas(at)gmail(dot)com>wrote:

> Thanks Gurjeet.
>
> You got it absolutely correct what I am trying to do.
>
> It is clear to me that we can not write trigger on system catalog.
>
> Can we hack postgres somehow to have triggers create on system catalog?
>
> I am using postgres 9.0 version.
>
> Thanks,
> Shridhar
>
>
>
> On Mon, Mar 28, 2011 at 8:03 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>wrote:
>
>> On Mon, Mar 28, 2011 at 10:19 AM, Shridhar Polas <shridharpolas(at)gmail(dot)com
>> > wrote:
>>
>>> Hi,
>>>
>>> Is there a way to create triggers on system catalog tables like
>>> pg_class, pg_attribute etc...?
>>>
>>
>> No, Postgres does not support triggers on system catalogs; we do not have
>> DDL triggers either, if that's what you were trying to achieve.
>>
>>

--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

On Mon, Mar 28, 2011 at 10:19 AM, Shridhar Polas
<shridharpolas(at)gmail(dot)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(at)gmail(dot)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...

...Robert

* Robert Haas (robertmhaas(at)gmail(dot)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 I 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.. 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(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)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 I 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(at)gmail(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)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(at)gmail(dot)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(at)snowman(dot)net> wrote:
>> * Robert Haas (robertmhaas(at)gmail(dot)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 I 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 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.

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