Thread: help with rules for system table

Hi,

  I have to archieve functions like this:
  When users define a new index, I will do something (for instance,
increase an counter in my table or do some other statistics). However,
I defined rule for insert on pg_class and when the entry is actually an
index, I do my thing.  Obviously my rule is never executed. If I create
a similar rule for a table I defined, it works.  Does this mean that I
can't create rules for system tables?  If not, does anyone know how to
do this?

Any suggestions or solutions are appreciated.

Thanks,
Ruey-Lung Hsiao

> Does this mean that I can't create rules for system tables?

that's right... you cannot create rules for system tables...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

rlhsiao@gmail.com writes:
> Does this mean that I can't create rules for system tables?

Not ones that will be invoked by system operations, anyway.

            regards, tom lane

On Sun, Jan 08, 2006 at 06:35:06PM -0800, rlhsiao@gmail.com wrote:
> Hi,
>
>   I have to archieve functions like this:
>   When users define a new index, I will do something (for instance,
> increase an counter in my table or do some other statistics). However,
> I defined rule for insert on pg_class and when the entry is actually an
> index, I do my thing.  Obviously my rule is never executed. If I create
> a similar rule for a table I defined, it works.  Does this mean that I
> can't create rules for system tables?  If not, does anyone know how to
> do this?

Many system operations completely bypass the 'normal' access methods for
touching the system tables, so generally you can't do things like
triggers or rules.

Depending on what you need to do there may be other ways to accomplish
it, though. For example, it's trivial to get a count of indexes on a
table...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Adding -general back into this...

No, I don't think there's any way to trigger on this programmatically, though there has been talk from time-to-time
aboutadding support for triggers on DDL. In the meantime you'll just need to scan the system tables for new indexes. 

I'm also wondering if there's some other, better way to do what you ultimately are trying to do. Adding a function
basedon CREATE INDEX seems extremely odd, at least to me. 

> From: zu zu [mailto:rlhsiao@gmail.com]
> Hi Jim,
>
>  Thank you for the message.
>  Actually what I wanted to do is:
>  whenever a user defines a new index, I will automatically generate 2
> functions (specific to that index) in the database.
> Of course, I can achieve this by, for instance, check if there's new
> index defined every night and if so, generate corresponding
> functions. Since most of the time, my database won't have drastic
> changes at all so this would be a waste of computational power.
> Originally I thought rule and trigger are the perfect
> solution for this.
> Now it seems that it's not possible to achieve this.
>
> Do you know other ways to achieve this?
>
> Thanks
> Ruey-Lung
>
> Jim C. Nasby wrote:
> > On Sun, Jan 08, 2006 at 06:35:06PM -0800, rlhsiao@gmail.com wrote:
> >
> >> Hi,
> >>
> >>   I have to archieve functions like this:
> >>   When users define a new index, I will do something (for instance,
> >> increase an counter in my table or do some other
> statistics). However,
> >> I defined rule for insert on pg_class and when the entry
> is actually an
> >> index, I do my thing.  Obviously my rule is never
> executed. If I create
> >> a similar rule for a table I defined, it works.  Does this
> mean that I
> >> can't create rules for system tables?  If not, does anyone
> know how to
> >> do this?
> >>
> >
> > Many system operations completely bypass the 'normal'
> access methods for
> > touching the system tables, so generally you can't do things like
> > triggers or rules.
> >
> > Depending on what you need to do there may be other ways to
> accomplish
> > it, though. For example, it's trivial to get a count of indexes on a
> > table...
> >
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461