Postgres Pro
Downloads
Home   >   mailing lists

Thread: maximum number of triggers on a table?

maximum number of triggers on a table?

From
derek@spencerogne.com (Derek Spencer)
Date:
Does Postgresql have a limit on the number of triggers that can exist
on any given table?  We're using v7.2.3.

I have a table with 36 triggers and upon adding another trigger
(really a foreign key reference) the alter table query hangs and
doesn't return...

I tried to find a reference to this in the documentation or FAQ but
was not successful.

-Derek

Re: maximum number of triggers on a table?

From
Stephan Szabo
Date:
On 29 Oct 2002, Derek Spencer wrote:

> Does Postgresql have a limit on the number of triggers that can exist
> on any given table?  We're using v7.2.3.
>
> I have a table with 36 triggers and upon adding another trigger
> (really a foreign key reference) the alter table query hangs and
> doesn't return...

I don't know of a trigger limit, but does this table have data in it?
It's possible that the alter table is being slow in checking the initial
state of the constraint.

Re: maximum number of triggers on a table?

From
Robert Treat
Date:
I don't know of any limit on triggers allowed, and looking at my
development server I have a couple tables with more than 36 (one has
100+) though the majority of this are RI triggers. Can you post the sql
involved?

Robert Treat

On Tue, 2002-10-29 at 18:50, Derek Spencer wrote:
> Does Postgresql have a limit on the number of triggers that can exist
> on any given table?  We're using v7.2.3.
>
> I have a table with 36 triggers and upon adding another trigger
> (really a foreign key reference) the alter table query hangs and
> doesn't return...
>
> I tried to find a reference to this in the documentation or FAQ but
> was not successful.
>
> -Derek
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: maximum number of triggers on a table?

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> I have a table with 36 triggers and upon adding another trigger
>> (really a foreign key reference) the alter table query hangs and
>> doesn't return...

> I don't know of a trigger limit, but does this table have data in it?
> It's possible that the alter table is being slow in checking the initial
> state of the constraint.

I'm wondering if the ALTER is waiting to acquire lock on the referenced
table (it will need exclusive lock to add the trigger).  If some open
transaction has used the referenced table and is now sitting around
without committing, it would cause the above behavior.

            regards, tom lane