Re: Proposal: Snapshot cloning

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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 from hugefile);
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