Thread: Table lock while adding a column and clients are logged in
hello pg fans, we have an application that communicates via ODBC directly to the postgres database. if i'm trying to add an additional column to a table in pgadmin while clients are logged in, pgadmin hangs. only if all cientsare logged out it returns to the normal state. according to our consultant of the application this behavior doesn'tappear in oracle or sql-server. how can i avoid this? cheers sven -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
Le 02/04/2011 11:09, Sven Haag a écrit : > hello pg fans, > > we have an application that communicates via ODBC directly to the postgres database. > > if i'm trying to add an additional column to a table in pgadmin while clients are logged in, pgadmin hangs. only if allcients are logged out it returns to the normal state. according to our consultant of the application this behavior doesn'tappear 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. -- Guillaume http://www.postgresql.fr http://dalibo.com
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? -------- Original-Nachricht -------- > Datum: Sat, 02 Apr 2011 11:38:29 +0200 > Von: Guillaume Lelarge <guillaume@lelarge.info> > An: Sven Haag <sven-haag@gmx.de> > CC: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] Table lock while adding a column and clients are logged in > Le 02/04/2011 11:09, Sven Haag a écrit : > > hello pg fans, > > > > we have an application that communicates via ODBC directly to the > postgres database. > > > > 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. > > > -- > Guillaume > http://www.postgresql.fr > http://dalibo.com -- NEU: FreePhone - kostenlos mobil telefonieren und surfen! Jetzt informieren: http://www.gmx.net/de/go/freephone
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 aCOMMIT (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
"Sven Haag" <sven-haag@gmx.de> writes: > hello pg fans, > > we have an application that communicates via ODBC directly to the > postgres database. > > 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? Discharge that clueless consultant and study up on NVCC :-) Then, find out why there are long running transactions in your application. > cheers sven > -- > Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir > belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de -- Jerry Sievers e: gsievers19@comcast.net p: 305.321.1144
Jerry Sievers <gsievers19@comcast.net> writes: > "Sven Haag" <sven-haag@gmx.de> writes: > >> hello pg fans, >> >> we have an application that communicates via ODBC directly to the >> postgres database. >> >> 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? > > Discharge that clueless consultant and study up on NVCC :-) Oops! Make that MVCC ! > Then, find out why there are long running transactions in your > application. > >> cheers sven >> -- >> Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir >> belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de > > -- > Jerry Sievers > e: gsievers19@comcast.net > p: 305.321.1144 -- Jerry Sievers e: gsievers19@comcast.net p: 305.321.1144
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 withthe SELECT. Sorry, but you're wrong about that. A statement that implicitly starts a transaction also implicitly COMMITs it. Otherwisesingle UPDATE and INSERT statements outside of transaction blocks would not COMMIT, and they do. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d983b2f651341263218540!
On 2 Apr 2011, at 11:09, Sven Haag wrote: > hello pg fans, > > we have an application that communicates via ODBC directly to the postgres database. > > if i'm trying to add an additional column to a table in pgadmin while clients are logged in, pgadmin hangs. only if allcients are logged out it returns to the normal state. according to our consultant of the application this behavior doesn'tappear in oracle or sql-server. > > how can i avoid this? That most likely means that at least one session is keeping a lock on at least one row at the start of your session, preventingPostgres to modify that row to alter the table definition. Oracle and SQL server don't "suffer" from this because they do not handle DDL statements transactionally (I could be mistakenabout SQL server, I don't know it all that well). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d983c51651341771511162!
On 3 Apr 2011, at 11:22, Alban Hertroys wrote: > Oracle and SQL server don't "suffer" from this because they do not handle DDL statements transactionally (I could be mistakenabout SQL server, I don't know it all that well). I forgot to mention, if you perform DDL in Oracle all your currently running transactions are implicitly rolled back. Thatmay be something you want to be aware of. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d983e5e651347237915052!
Alban Hertroys wrote on 03.04.2011 11:31: > On 3 Apr 2011, at 11:22, Alban Hertroys wrote: > >> Oracle and SQL server don't "suffer" from this because they do not >> handle DDL statements transactionally (I could be mistaken about >> SQL server, I don't know it all that well). > > > I forgot to mention, if you perform DDL in Oracle all your currently > running transactions are implicitly rolled back. Not quite. The current transaction is committed, not rolled back. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1001.htm#i2099120 Regards Thomas
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. Regards Thomas
-------- Original-Nachricht -------- > Datum: Sun, 03 Apr 2011 15:37:17 +0200 > Von: Thomas Kellerer <spam_eater@gmx.net> > An: pgsql-general@postgresql.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. > > Regards > Thomas hi thomas, well, as we are using the default setting here (according to the manual this is ON) this shouldn't be the case?! cheers sven -- GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
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@gmx.net> >> An: pgsql-general@postgresql.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
-------- Original-Nachricht -------- > Datum: Sun, 03 Apr 2011 16:25:35 +0200 > Von: Thomas Kellerer <spam_eater@gmx.net> > An: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] 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@gmx.net> > >> An: pgsql-general@postgresql.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 hi thomas, there are indeed "IDLE in transaction" queries running since hours. so i guess i have to commit all queries explicitly, evenif there are only SELECT statements. thanks a lot for all the help! Sven Haag -- NEU: FreePhone - kostenlos mobil telefonieren und surfen! Jetzt informieren: http://www.gmx.net/de/go/freephone