Обсуждение: Altering table with open cursors
Hi Group,
I'm working on a database application which makes heavy use of cursors. pg_stat_activity shows that connected clients are always '<IDLE> in transaction', with a cursor open for a select. The issue that I'm having is that anytime I make changes to the table structures (ALTER TABLE tablename), the attempted operation is blocked by open cursors selecting rows from the same table. My current solution is to only make schema changes when the client applications are all disconnected.
Is there a way to alter the table schema without waiting for declared cursors to be released? Are the caveats to dangerous to deal with that I should not consider it? In general, the only changes I've been making are to add columns, which would not actually modify the result sets that the cursors are selecting anyway. Thanks for any thoughts/suggestions. I'm running PG 8.4.
Kind Regards,
-Joshua
Joshua Berry
I'm working on a database application which makes heavy use of cursors. pg_stat_activity shows that connected clients are always '<IDLE> in transaction', with a cursor open for a select. The issue that I'm having is that anytime I make changes to the table structures (ALTER TABLE tablename), the attempted operation is blocked by open cursors selecting rows from the same table. My current solution is to only make schema changes when the client applications are all disconnected.
Is there a way to alter the table schema without waiting for declared cursors to be released? Are the caveats to dangerous to deal with that I should not consider it? In general, the only changes I've been making are to add columns, which would not actually modify the result sets that the cursors are selecting anyway. Thanks for any thoughts/suggestions. I'm running PG 8.4.
Kind Regards,
-Joshua
Joshua Berry