Re: DDL commands take forever - Mailing list pgsql-general

2009/12/29 Leonardo M. <l.rame@griensu.com>:
> El mar, 29-12-2009 a las 14:48 -0300, Leonardo M. Ramé escribió:
>> El mar, 29-12-2009 a las 11:20 -0500, Bill Moran escribió:
>> > In response to "Leonardo M." Ramé <l.rame@griensu.com>:
>> >
>> > > Hi, I need to create a trigger on a table used by our sofware, the
>> > > problem is, when I issue a "create trigger...." on this table, it takes
>> > > forever. It doesn't matter if I use pgAdmin, or psql.
>> > >
>> > > The only way to do it is by disconnecting all the instances of the
>> > > program from the database, execute the DDL command, and reconnect the
>> > > program again.
>> > >
>> > > What can be causing this behavior? any workaround?.
>> >
>> > Most likely those programs have locks out that are causing the DDL command
>> > to have to wait until the locks clear.
>> >
>> > Disconnecting everyone is the quick workaround.
>> >
>> > The real fix is to ensure that your application doesn't hold onto DB locks
>> > longer than is needed, which won't guarantee that everything moves fast,
>> > but will help immensely.  Often this is caused by the application holding
>> > transactions open for long periods of time, which is a bad idea anyway
>> > since it interferes with vacuum and other maintenance operations.
>> >
>> > You can look at the pg_locks table to see what locks are out, to help you
>> > diagnose what apps are holding locks open.
>> >
>> > --
>> > Bill Moran
>> > http://www.potentialtech.com
>> > http://people.collaborativefusion.com/~wmoran/
>> >
>>
>> Thanks Bill, I'll take a look at my app. I hope the problem is on my
>> side, the worst case would be the cause of the problem is on the
>> connection component I'm using to connect to Postgres (the app is
>> written in Delphi with Zeos Lib).
>>
>>
>> --
>> Leonardo M. Ramé
>> Griensu S.A. - Medical IT Córdoba
>> Tel.: 0351-4247979
>>
>>
>
> Well, I'm trying to debug the problem, and found that when I do a simple
> "select * from table" from my app, then go to pgAdmin, and do "select *
> from pg_locks", it shows many locks (23 to be exact).

Those locks are not coming from your query.  Something else is
connecting and acquiring locks.  If your database is busy, this is
normal: every transaction is going to apply a lock of some sort or
another.   The key thing to look out for is the level of lock, what it
is on, and whether or not it has been granted.

Also, you should check: select * from pg_stat_activity; to see who is
executing what queries and if you have any long running transactions
that shouldn't be.

merlin