Thread: DDL commands take forever

DDL commands take forever

From
"Leonardo M." Ramé
Date:
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


Re: DDL commands take forever

From
Bill Moran
Date:
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/

Re: DDL commands take forever

From
"Leonardo M." Ramé
Date:
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


Re: DDL commands take forever

From
"Leonardo M." Ramé
Date:
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


Re: DDL commands take forever

From
Merlin Moncure
Date:
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

Re: DDL commands take forever

From
"Leonardo M." Ramé
Date:
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


Re: DDL commands take forever

From
Merlin Moncure
Date:
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

Re: DDL commands take forever

From
"Leonardo M." Ramé
Date:
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


Re: DDL commands take forever

From
Merlin Moncure
Date:
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

Re: DDL commands take forever

From
"Leonardo M." Ramé
Date:
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


Re: DDL commands take forever

From
Merlin Moncure
Date:
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

Re: DDL commands take forever

From
Raymond O'Donnell
Date:
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