Search Postgresql Archives

Re: Creating and managing triggers

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hey,

2012/10/9 Tom Lane <tgl@xxxxxxxxxxxxx>
Dean Myerson <dean@xxxxxxxxxxxxxxx> 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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux