Thread: Hot Standby and cancelling idle queries

Recent change:

An idle-in-transaction transaction can also hold a temporary file. Think
of an open cursor, for example. Therefore, remove the distinction
between CONFLICT_MODE_ERROR and CONFLICT_MODE_ERROR_IF_NOT_IDLE,
idle-in-transaction backends need to be killed too when a tablespace is
dropped.


Open cursors still have snapshots, so they would not be treated as idle
in transaction. If the user has a held cursor then they can keep it,
since it has already read the database and released the snapshot. So
this change seems both unnecessary and harsh, since even if it is
necessary we can work out how to avoid this easily enough.

-- Simon Riggs           www.2ndQuadrant.com

Simon Riggs <simon@2ndQuadrant.com> writes:
> An idle-in-transaction transaction can also hold a temporary file. Think
> of an open cursor, for example. Therefore, remove the distinction
> between CONFLICT_MODE_ERROR and CONFLICT_MODE_ERROR_IF_NOT_IDLE,
> idle-in-transaction backends need to be killed too when a tablespace is
> dropped.

Um ... I think you have forgotten about WITH HOLD cursors, which will
also have temp files.  Implication: whatever you are thinking of here
would require killing EVERY session.  Conclusion: you need a different
design.
        regards, tom lane

Simon Riggs wrote:
> Recent change:
> 
> An idle-in-transaction transaction can also hold a temporary file. Think
> of an open cursor, for example. Therefore, remove the distinction
> between CONFLICT_MODE_ERROR and CONFLICT_MODE_ERROR_IF_NOT_IDLE,
> idle-in-transaction backends need to be killed too when a tablespace is
> dropped.
> 
> Open cursors still have snapshots, so they would not be treated as idle
> in transaction.

A backend is idle-in-transaction whenever a transaction is open and the
backend is waiting for a command from the client. Whether it has active
snapshots or open cursors doesn't affect that.

> If the user has a held cursor then they can keep it,
> since it has already read the database and released the snapshot.

A held cursor can keep a temp file open.

I suspect you missed the context of this change. It's about the code in
tablespc.c, to kill all backends that might have a temporary file in a
tablespace that's being dropped. It's not about tuple visibility but
temporary files.

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com

On Thu, 2009-11-26 at 08:30 +0200, Heikki Linnakangas wrote:
> I suspect you missed the context of this change. It's about the code
> in tablespc.c, to kill all backends that might have a temporary file
> in a tablespace that's being dropped. It's not about tuple visibility
> but temporary files.

Got you now.

-- Simon Riggs           www.2ndQuadrant.com

On Wednesday 25 November 2009 17:25:43 Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > An idle-in-transaction transaction can also hold a temporary file. Think
> > of an open cursor, for example. Therefore, remove the distinction
> > between CONFLICT_MODE_ERROR and CONFLICT_MODE_ERROR_IF_NOT_IDLE,
> > idle-in-transaction backends need to be killed too when a tablespace is
> > dropped.
> 
> Um ... I think you have forgotten about WITH HOLD cursors, which will
> also have temp files.  Implication: whatever you are thinking of here
> would require killing EVERY session.  Conclusion: you need a different
> design.
Actually WITH HOLD cursors should not pose a problem - the code already tries 
to handle that:

In fd.c:OpenTemporaryFile:
 * BUT: if the temp file is slated to outlive the current transaction, * force it into the database's default
tablespace,so that it will not * pose a threat to possible tablespace drop attempts. */
 

Whether thats generally a good idea is another question.

Andres