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@xxxxxxxxxxx>: > > > > > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
