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

Sven Haag wrote on 02.04.2011 12:13:
>>> if i'm trying to add an additional column to a table in pgadmin
>>> while
>> clients are logged in, pgadmin hangs. only if all cients are logged
>> out it returns to the normal state. according to our consultant of
>> the application this behavior doesn't appear in oracle or
>> sql-server.
>>>
>>> how can i avoid this?
>>>
>>
>> When you add a new column to a table, the session needs an
>> AccessExclusiveLock to this table, which means no one can have a
>> lock on the object while you add the column. IOW, pgAdmin (and any
>> other tool) will hang until no one works on the table. If it's a
>> heavily used table, there's not much you can do about it, but
>> wait.

> yes i saw that in the documentation too. i guess it doesn't matter
> what kind of lock level the odbc driver is using (row-lock)?
>
> but how can it be, that in oracle and sqlserver this is not
> happening?
>
Actually SQL Server is even more prone to these kind of locks. And it will happen in Oracle just as well.

PostgreSQL is more "sensible" when it comes to transactions that are not properly closed (Oracle is a bit more "forgiving" there - especially with SELECT statements).

I bet you see a lot of "IDLE in transaction" entries in your pg_stat_activity (as opposed to plain "IDLE" entries).

This means you are not ending (e.g. committing) your transactions properly. Even after a plain SELECT you should issue a COMMIT (or ROLLBACK) to end the transaction that was implicitely started with the SELECT.

The ALTER TABLE should not be a problem if you only see "IDLE" sessions.

Regards
Thomas