Re: DDL commands take forever

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(dot)rame(at)griensu(dot)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).

On the other hand, if I do the same query with pgAdmin on one machine,
and the "select * from pg_locks" from another machine, there are only
three locks.

Is there a way to tell Postgres how to handle locks at connection time?,
maybe pgAdmin is using a *special* parameter to connect?.

--
Leonardo M. Ramé
Griensu S.A. - Medical IT Córdoba
Tel.: 0351-4247979