On 04/11/2018 11:13 AM, Don Seiler wrote:
On Wed, Apr 11, 2018 at 12:58 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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 woultd 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.
A test case here confirms it sends individual INSERTS:
test_(postgres)# insert into fdw_test_table select * from fdw_test;
INSERT 0 3
Where fdw_test_table is the remote table and fdw_test is the local one.
postgres-2018-04-11 11:29:23.812 PDT-0LOG: statement: insert into
fdw_test_table select * from fdw_test;
postgres-2018-04-11 11:29:23.812 PDT-0LOG: execute pgsql_fdw_prep_2:
INSERT INTO public.fdw_test_rcv(id, fld_1, fld_2) VALUES ($1, $2, $3)
postgres-2018-04-11 11:29:23.812 PDT-0DETAIL: parameters: $1 = '1', $2
= 'one', $3 = 't'
postgres-2018-04-11 11:29:23.813 PDT-10140LOG: execute
pgsql_fdw_prep_2: INSERT INTO public.fdw_test_rcv(id, fld_1, fld_2)
VALUES ($1, $2, $3)
postgres-2018-04-11 11:29:23.813 PDT-10140DETAIL: parameters: $1 = '2',
$2 = 'two', $3 = 'f'
postgres-2018-04-11 11:29:23.813 PDT-10140LOG: execute
pgsql_fdw_prep_2: INSERT INTO public.fdw_test_rcv(id, fld_1, fld_2)
VALUES ($1, $2, $3)
postgres-2018-04-11 11:29:23.813 PDT-10140DETAIL: parameters: $1 = '3',
$2 = 'three', $3 = 'f'
So much for that idea(:
Don.
--
Don Seiler
www.seiler.us <http://www.seiler.us>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
