How to fix 25008: held_cursor_requires_same_isolation_level in PostgreSQL

-- Close the cursor first
CLOSE my_cursor;

-- Now safe to change isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Reopen cursor with new isolation level
BEGIN;
DECLARE my_cursor CURSOR WITH HOLD FOR
  SELECT * FROM orders WHERE status = 'pending';

-- Set at session start
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
DECLARE persistent_cursor CURSOR WITH HOLD FOR
  SELECT * FROM products ORDER BY id;
COMMIT;

-- All subsequent transactions inherit the session default
BEGIN;
FETCH 10 FROM persistent_cursor;  -- Works correctly
COMMIT;

-- Instead of WITH HOLD cursor
BEGIN;
DECLARE standard_cursor CURSOR FOR
  SELECT * FROM transactions WHERE date > CURRENT_DATE - 30;

FETCH 100 FROM standard_cursor;
COMMIT;  -- Cursor automatically closed

-- Subsequent transaction can use different isolation level
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Define new operations here
COMMIT;

-- Check current isolation level
SHOW transaction_isolation;

-- Only proceed if it matches the cursor's original level
-- In application code (pseudocode):
-- if (current_isolation_level != cursor_isolation_level) {
--   close_cursor();
--   set_isolation_level(cursor_isolation_level);
--   reopen_cursor();
-- }

BEGIN;
-- Ensure isolation level matches
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
FETCH FROM my_held_cursor;
COMMIT;

// Example with node-postgres
const pool = new Pool({
  // Reset isolation level when connection is returned to pool
  connectionTimeoutMillis: 30000,
  idleTimeoutMillis: 30000,
  max: 10,
  // Add connection reset hook
  onRelease: async (connection) => {
    await connection.query('CLOSE ALL;');  // Close any cursors
    await connection.query('SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;');
  }
});