dbGetQuery failing due to stale Redshift OIDs

Overview

I am querying an AWS Redshift table over an RPostgres::Redshift connection. The query sometimes fails with error:

Error: Failed to fetch row: ERROR:  could not open relation with OID 8574286

The ERROR: could not open relation with OID <oid-number> portion of the error comes from Redshift and the OID is different each time.

Environment

The RPostgres::Redshift connection is running on an Ubuntu 20.04 machine with libpq-dev 12.19-0ubuntu0.20.04.1, R 4.2.1, DBI 1.2.3, and RPostgres 1.4.5.

Description

The table in question is managed by an independent process which replaces the table periodically. The table OID changes each time it is replaced. When the table replacement process executes, it drops the table and renames a replacement table in a single transaction:

BEGIN TRANSACTION;

DROP TABLE my_table;
ALTER TABLE my_table_tmp RENAME TO my_table;

END TRANSACTION;

When a query over a RPostgres::Redshift connection occurs at the same time, the above error is returned.

DBI::dbGetQuery(con, "SELECT col1, col2 FROM my_table")

I suspect that the select statement is not creating a lock on my_table, which is allowing the table replacement to happen concurrently. This changes the table OID and the select query fails trying to find a stale OID.

My expectation is that the query would run without error, possibly a bit slower if it has to wait for the table replacement to complete.

No source files or tests are named. Start by reproducing DBI::dbGetQuery(con, "SELECT col1, col2 FROM my_table") while the documented DROP TABLE and ALTER TABLE replacement transaction runs, then inspect RPostgres query and result-fetch handling. Done means the concurrent Redshift query no longer fails with a stale relation OID, with regression coverage for the replacement scenario.