On Wed, Apr 11, 2018 at 12:58 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > "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. -- Don Seiler www.seiler.us
