Hey, 2012/10/9 Tom Lane <t...@sss.pgh.pa.us>
> Dean Myerson <d...@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. > Obviously, typo. \d[S+] your_table_name instead of \dt. -- // Dmitriy.
