Re: Archiving Data to Another DB?

On 04/11/2018 11:13 AM, Don Seiler wrote:
> On Wed, Apr 11, 2018 at 12:58 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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 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(dot)klaver(at)aklaver(dot)com