Re: pg_dump --snapshot

Greg Stark <stark(at)mit(dot)edu> writes:
> [ ideas about dumping some past state of a table ]

> If I try to access a table whose schema has changed then I might use
> the wrong tupledesc and see rows that don't decode properly. That
> would be a disaster. Can we protect against that by noticing that the
> pg_class row isn't visible to our snapshot and throw an error? Would
> that be sufficient to protect against all schema changes? Would it
> cause massive false positives based on whether vacuum had happened to
> have run recently?

No, no, and I'm not sure :-(. It might be sufficient to notice whether
the pg_class row and all relevant pg_attribute rows are visible in your
snapshot, at least for the narrow purpose of deciding whether you can
dump data. (This would probably not, for instance, be enough to give
you reliable info about check or foreign key constraints.)

In general though, any such facility would surely block vacuuming on
the table, indeed probably *all* tables in the database, which would
be pretty disastrous in the long run. I think a better answer for
people who need such a facility is to keep a WAL archive and use PITR
when they actually need to get back yesterday's data.

regards, tom lane