Dean Myerson <dean@deanmyerson.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.
regards, tom lane