Re: Table lock while adding a column and clients are logged in

Sven Haag wrote on 03.04.2011 16:13:
>
> -------- Original-Nachricht --------
>> Datum: Sun, 03 Apr 2011 15:37:17 +0200
>> Von: Thomas Kellerer<spam_eater(at)gmx(dot)net>
>> An: pgsql-general(at)postgresql(dot)org
>> Betreff: Re: [GENERAL] Table lock while adding a column and clients are logged in
>
>> Alban Hertroys wrote on 03.04.2011 11:17:
>>> On 2 Apr 2011, at 12:44, Thomas Kellerer wrote:
>>>
>>>> Even after a plain SELECT you should issue a COMMIT (or ROLLBACK)
>>>> to end the transaction that was implicitely started with the
>>>> SELECT.
>>>
>>> Sorry, but you're wrong about that. A statement that implicitly
>>> starts a transaction also implicitly COMMITs it. Otherwise single
>>> UPDATE and INSERT statements outside of transaction blocks would not
>>> COMMIT, and they do.
>>
>> AFAIK this is only true if you are running in auto commit mode.
>>
>> If you have auto commit turned off, a SELECT statement will leave the
>> current transaction as "IDLE in transaction" not "IDLE" which means it *will*
>> hold a lock on the tables involved that will prevent an ALTER TABLE.
>>
>
> well, as we are using the default setting here (according to the manual this is ON) this shouldn't be the case?!

The client defines the default behaviour, so it's your application that controls this.

Did you check that you have sessions that are show as "IDLE in transaction" in pg_stat_activity?

Regards
Thomas