Re: Proposal: Snapshot cloning

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

>>> set_current_snapshot() would have to sanity check that the xmin of the new
>>> snapshot isn't older than the current globaloldestxmin. 
>
>> That would solve the backend to backend IPC problem nicely.
>
> But it fails on the count of making sure that globaloldestxmin doesn't
> advance past the snap you want to use.  And exactly how will you pass
> a snap through a table?  It won't become visible until you commit ...
> whereupon your own xmin isn't blocking the advance of globaloldestxmin.

Hm, good point. You could always do it in a separate connection, but that
starts to get annoying. I was more envisioning passing it around out-of-band
though, something like:


$db->execute(SET TRANSACTION ISOLATION LEVEL SERIALIZABLE);
$snap = $db->execute(select current_snapshot());
 for each db {     if (fork())         $slave[i] = $db->connect();         $slave[i]->execute(select
set_snapshot($snap));        $slave[i]->execute(copy table[i] to file[i]); }
 


I'm also wondering about something like:
 $db->execute(SET TRANSACTION ISOLATION LEVEL SERIALIZABLE); $snap = $db->execute(select current_snapshot());
 if (fork())     $slave = $db->connect();     $slave->execute(select set_snapshot($snap);     $slave->execute(copy tab
fromhugefile);     signal parent } else {     while(no signal yet) {         $rows_loaded_so_far = $db->execute(select
count(*)from tab);         display_progress($rows_loaded_so_far);         sleep(60);     } }
 


Sorry for the vaguely perlish pseudocode but it's the clearest way I can think
to write it. I don't think it would make much sense to try to do anything like
this in plpgsql; I think you really do want to be doing it in a language
outside the database where it's easier to open multiple connections and handle
IPC.

I realize the second idea might take more hackery than just setting the
snapshot... In particular as written above it wouldn't work because the slave
would be writing with a new xid that isn't actually in the snapshot.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com