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
> savepoints.
> ..."

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.


Don Seiler

Reply via email to