Re: Creating and managing triggers

Hey,

2012/10/9 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Dean Myerson <dean(at)deanmyerson(dot)org> writes:
> > I need to create some triggers and the docs seem pretty straightforward.
> > When I tried to create one using CREATE TRIGGER, it took over 20
> > minutes, and the second one hadn't finished over more than an hour. And
> > I later found that all other database users in the company were locked
> > out during this process. The table getting the triggers has about 187000
> > rows in it and is pretty central, so lots of functions join with it.
>
> CREATE TRIGGER, per se, should be nearly instantaneous. It sounds like
> the CREATE TRIGGER command is blocked behind some other operation that
> has a (not necessarily exclusive) lock on the table; and then everything
> else is queueing up behind the CREATE TRIGGER's exclusive lock request.
>
> Look into pg_locks and pg_stat_activity to see what's holding things up.
>
> I'd bet on an old idle-in-transaction session, that may have done
> nothing more exciting than reading the table at issue, but is still
> blocking things for failure to close its transaction. Sitting idle with
> an open transaction is something to be discouraged for a lot of reasons
> besides this one.
>
> > ... They restarted the database server when the second
> > create trigger hung, so I don't know what happened with it.
>
> Whoever "they" is needs to learn a bit more about being a Postgres DBA,
> methinks. There are smaller hammers than a database restart.
>
> > I didn't
> > even save the name, obviously a problem on my part. But there should be
> > some equivalent of Show Trigger, shouldn't there?
>
> psql's \dt command is the usual thing, or if you like GUIs you could try
> PgAdmin.
>
Obviously, typo.
\d[S+] your_table_name instead of \dt.

--
// Dmitriy.