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