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

-------- Original-Nachricht --------
> Datum: Sun, 03 Apr 2011 16:25:35 +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

> 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

hi thomas,

there are indeed "IDLE in transaction" queries running since hours. so i guess i have to commit all queries explicitly, even if 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