* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> The case where you see a tuple twice is if an update drops a new version
> of a row beyond your seqscan, and then commits before you get to the new
> version. But if it drops the new version of the row *behind* your
> seqscan, and then commits before you get to the original tuple, you'll
> not see either row version as committed. Both of these cases are
> inherent risks in SnapshotNow scans.
Ah, I see.
> > If there are other transactiosn which have open locks against the table,
> > wouldn't that prevent my being able to acquire ShareLock on it?
>
> What I'm worried about is that we very commonly release locks on
> catalogs as soon as we're done with the immediate operation --- not only
> read locks, but update locks as well. So there are lots of cases where
> locks are released before commit. It's possible that that never happens
> in operations applied to pg_tablespace, but I'd not want to bet on it.
Fair enough.
> I wonder whether it's practical to look at the on-disk directories
> instead of relying on pg_tablespace for this particular purpose.
So we'd traverse all of the tablespace directories and copy any
directories which we come across which have the oid of the template
database..? Sounds pretty reasonable, though I'm not sure that we'd
want to back-patch a large change like that.
> Or maybe we should just write this off as a case we can't realistically
> fix before we have MVCC catalog scans. It seems that any other fix is
> going to be hopelessly ugly.
I feel like we should be able to do better than what we have now, at
least. Using ShareLock prevented the specific case that we were
experiencing and is therefore MUCH better (for us, anyway) than
released versions where we ran into the error on a regular basis.
Thanks,
Stephen