"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