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