On Wed, Apr 11, 2018 at 12:58 PM, Adrian Klaver <adrian.kla...@aklaver.com>
> "F.33.3. Transaction Management
> During a query that references any remote tables on a foreign server,
> postgres_fdw opens a transaction on the remote server if one is not already
> open corresponding to the current local transaction. The remote transaction
> is committed or aborted when the local transaction commits or aborts.
> Savepoints are similarly managed by creating corresponding remote
Interesting, I'll work on a test case later!
> I may be missing something, but why not reverse your original set up?
> Assuming transactional behavior works as expected something like:
> 1) Setup postgres_fdw in main database.
> 2) Create FOREIGN TABLE pointing to table in archive database.
> 3) INSERT INTO/SELECT from main table to archive table.
> 4) DELETE FROM main table.
I had considered this as well, as this would allow me to rollback the
delete (assuming my intel on postgres_fdw transactions was correct, which
it may not be after all). I wondered if a remote insert would be broken up
into individual inserts like the remote delete was, as that would be
equally unappealing for the same reasons. But obviously worth confirming.