Thread: DDL commands take forever
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?. -- Leonardo M. Ramé Griensu S.A. - Medical IT Córdoba Tel.: 0351-4247979
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/
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
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). 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
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
El mar, 29-12-2009 a las 14:18 -0500, Merlin Moncure escribió: > >> > > > > 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 > In fact, my app is acquiring the locks. Also the app doesn't have long running transactions, and the locks shows even if I just to a SELECT, to reproduce the problem don't have to do an "BEGIN ... COMMIT/ROLLBACK". I found the problem is in the way I connect the app to the database, when I set the Transaction Isolation Level to ReadCommitted, the locking problem appears, when I use the default connection method, the locks doesn't appear when I do "select * from pg_locks". This solves the locking problem, but what happens to transactions? the app is still working in transaction mode, or just applying changes after every Insert/Update/Delete?. -- Leonardo M. Ramé Griensu S.A. - Medical IT Córdoba Tel.: 0351-4247979
2009/12/29 Leonardo M. <l.rame@griensu.com>: > El mar, 29-12-2009 a las 14:18 -0500, Merlin Moncure escribió: >> >> >> > >> > 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 >> > > In fact, my app is acquiring the locks. Also the app doesn't have long > running transactions, and the locks shows even if I just to a SELECT, to > reproduce the problem don't have to do an "BEGIN ... COMMIT/ROLLBACK". > > I found the problem is in the way I connect the app to the database, > when I set the Transaction Isolation Level to ReadCommitted, the locking > problem appears, when I use the default connection method, the locks > doesn't appear when I do "select * from pg_locks". > > This solves the locking problem, but what happens to transactions? the > app is still working in transaction mode, or just applying changes after > every Insert/Update/Delete?. huh...the default transaction mode _is_ read committed :-). merlin
El mar, 29-12-2009 a las 15:05 -0500, Merlin Moncure escribió: > > This solves the locking problem, but what happens to transactions? the > > app is still working in transaction mode, or just applying changes after > > every Insert/Update/Delete?. > > huh...the default transaction mode _is_ read committed :-). > > merlin > Merlin, knowning this, I'm asking to the developers of the connection library because in their code, if I use the default connection mode, then the transactions are ingnored, applying the changes immediately after every Insert, Update or Delete. -- Leonardo M. Ramé Griensu S.A. - Medical IT Córdoba Tel.: 0351-4247979
2009/12/29 Leonardo M. <l.rame@griensu.com>: > El mar, 29-12-2009 a las 15:05 -0500, Merlin Moncure escribió: >> > This solves the locking problem, but what happens to transactions? the >> > app is still working in transaction mode, or just applying changes after >> > every Insert/Update/Delete?. >> >> huh...the default transaction mode _is_ read committed :-). >> >> merlin >> > > Merlin, knowning this, I'm asking to the developers of the connection > library because in their code, if I use the default connection mode, > then the transactions are ingnored, applying the changes immediately > after every Insert, Update or Delete. right. IIRC the zeos library has a transaction mode that controls if commits are explicit or invoked via the library commit method. either way, you you need to make sure that transactions are not left open...this can lead (as you noticed) to unexpected problems like blocking queries, performance problems, data loss, etc. if you notice the slow ddl issue again, throw an immediate select * from pg_locks and look for granted = f. If you find some and they match your pid, then you know that you have a transaction open that is blocking you. From there, it's just a matter if using pg_locks and pg_stat_activity to narrow down who/what is doing it. You should especially take note of 'idle in transaction' in pg_stat_activity...this is classic red flag of leaky application code. merlin
El mar, 29-12-2009 a las 15:44 -0500, Merlin Moncure escribió: > right. IIRC the zeos library has a transaction mode that controls if > commits are explicit or invoked via the library commit method. either > way, you you need to make sure that transactions are not left > open...this can lead (as you noticed) to unexpected problems like > blocking queries, performance problems, data loss, etc. > > if you notice the slow ddl issue again, throw an immediate select * > from pg_locks and look for granted = f. If you find some and they > match your pid, then you know that you have a transaction open that is > blocking you. From there, it's just a matter if using pg_locks and > pg_stat_activity to narrow down who/what is doing it. You should > especially take note of 'idle in transaction' in > pg_stat_activity...this is classic red flag of leaky application code. > > merlin I did the Select * from pg_locks right after your answer, and found that almost all locks originated by my app have "granted = t", also, all are in "<IDLE> in transaction". The interesting thing is the app is doing only Selects, without opening transactions. -- Leonardo M. Ramé Griensu S.A. - Medical IT Córdoba Tel.: 0351-4247979
2009/12/29 Leonardo M. <l.rame@griensu.com>: > El mar, 29-12-2009 a las 15:44 -0500, Merlin Moncure escribió: >> right. IIRC the zeos library has a transaction mode that controls if >> commits are explicit or invoked via the library commit method. either >> way, you you need to make sure that transactions are not left >> open...this can lead (as you noticed) to unexpected problems like >> blocking queries, performance problems, data loss, etc. >> >> if you notice the slow ddl issue again, throw an immediate select * >> from pg_locks and look for granted = f. If you find some and they >> match your pid, then you know that you have a transaction open that is >> blocking you. From there, it's just a matter if using pg_locks and >> pg_stat_activity to narrow down who/what is doing it. You should >> especially take note of 'idle in transaction' in >> pg_stat_activity...this is classic red flag of leaky application code. >> >> merlin > > I did the Select * from pg_locks right after your answer, and found that > almost all locks originated by my app have "granted = t", also, all are > in "<IDLE> in transaction". The interesting thing is the app is doing > only Selects, without opening transactions. ok, the problem is clear: find out why those happened (a client issued 'begin' without subsequent 'commit') and your problem will go away. Turn on sql logging if you have to. merlin
On 29/12/2009 20:59, Leonardo M. Ramé wrote: > in "<IDLE> in transaction". The interesting thing is the app is doing > only Selects, without opening transactions. Everything in PG happens in a transaction, whether you open one explicitly or not. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie