Re: idle in transaction

Alexey,

A transaction being started doesn't prevent you from creating an index.
  A lock on a table prevents you from creating an index.  A select
statement will cause locks on the table it selects from and those locks
are not released until the transaction is committed.  What I suspect is
happening in your application (I say this from experience because I
needed to do a lot of work in my application to avoid this) is the
following:

You have code that is getting a connection from your connection pool and
using it only for select operations.  And because you are only doing
selects you are not commiting or rolling back before returning the
connection.  But because you aren't commiting or rollingback the locks
the select aquired are still being held and thus indexes can't be
created, vacuum full can't be run, etc.

You need to make sure that you always commit/rollback before returning a
connection to your connection pool (or make sure your connection pool
does that for you).

thanks,
--Barry


Alexey Yudichev wrote:
>   It seems that connection.commit() commits current transaction and immediately begins a new one so that connection
statusis always "idle in transaction". During that time no indicies could be created/dropped, no vaccum command can be
issuedetc because of locks I suppose.  
>   I use PostgreSQL 7.1 and tried 7.2 drivers and 7.3 drivers (with option compatible=7.1).
>   Is there anything that can be done to allow creating index without restarting the connection pool?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>