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.klaver@aklaver.com <mailto:adrian.klaver@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 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@aklaver.com