Re: pg_catalog

Hi,

I have tried other sources but to no avail. Could someone please tell me which tables in pg_catalog are effected by creating or dropping a trigger. If there is a work flow diagram or source code location you want to point me to rather than listing them that will be fine also. Some plonker deleted some rows directly in the pg_triggers table .... I need to fix it.

Many Thanks

Graham

On Mon, Jan 28, 2013 at 10:24 AM, Graham Little <graham(dot)little(at)aspone(dot)co(dot)uk>
wrote:
>
> Hi,
>
>
>
> I have tried other sources but to no avail. Could someone please tell me
which tables in pg_catalog
> are effected by creating or dropping a trigger. If there is a work flow
diagram or source code location
> you want to point me to rather than listing them that will be fine also.

I don't know if its completely right, but when a trigger is created the
following catalog are affecteds:
- pg_trigger (new entry)
- pg_depend (new entry)
- pg_class (update relhastriggers column)

> Some plonker deleted some rows directly in the pg_triggers table …. I
need to fix it.

Maybe you simply DROP and CREATE trigger solve it...

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Fabrizio,

Thank you very much for your email, I was able to run a script to generate update statements to the tables you mentioned and this has fixed my problem for me.

select 'UPDATE pg_catalog.pg_class SET reltriggers=' || b.reltriggers-1 || ' WHERE relname=' || '''' || a.table_name || '''' || ' and oid=' || b.oid
from information_schema.tables a, pg_catalog.pg_class b
where a.table_type = 'BASE TABLE'
and a.table_schema = 'public'
and a.table_name not like '%dbmirror%'
and a.table_name = b.relname
order by a.table_name asc

Many Thanks

Graham

From: Fabrízio de Royes Mello [mailto:fabriziomello(at)gmail(dot)com]
Sent: 28 January 2013 15:03
To: Graham Little
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] pg_catalog

On Mon, Jan 28, 2013 at 10:24 AM, Graham Little <graham(dot)little(at)aspone(dot)co(dot)uk<mailto:graham(dot)little(at)aspone(dot)co(dot)uk>> wrote:
>
> Hi,
>
>
>
> I have tried other sources but to no avail. Could someone please tell me which tables in pg_catalog
> are effected by creating or dropping a trigger. If there is a work flow diagram or source code location
> you want to point me to rather than listing them that will be fine also.

I don't know if its completely right, but when a trigger is created the following catalog are affecteds:
- pg_trigger (new entry)
- pg_depend (new entry)
- pg_class (update relhastriggers column)

> Some plonker deleted some rows directly in the pg_triggers table .... I need to fix it.

Maybe you simply DROP and CREATE trigger solve it...

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello