Re: pg_dump --snapshot

* Greg Stark (stark(at)mit(dot)edu) wrote:
> On Mon, May 6, 2013 at 10:02 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > At the database level, it rolls back the whole kaboodle. Not what I
> > meant at all and I would expect people to start twitching at the
> > prospect.
>
> I think it would be pretty sweet but we don't have the infrastructure
> for it. We would need to retain enough information in the WAL log (or
> somewhere else) to reverse the records.

Let me start off by saying that I do like the general idea. We're
rather different from Oracle, which makes me wonder if we might be in a
slightly better position to handle this kind of an option from.

For example, I'm not sure that we need more information in the WAL..
What we need is a way to tell VACUUM to skip over 'recently modified'
records and not mark them as dead until some time has passed. This is
essentially what we're already doing with the feedback mechanism in
replication, isn't it? Would it be possible to make that a simple timer
instead of requiring a replication system which is feeding back that
information based on queries which are running on the replica?

> But more interestingly expdp does in fact have a user option to
> specify a timestamp or scn (analogous to wal position) and use
> flashback query to dump the data at that point in time. That's a
> pretty clear a parallel to what you propose here.

What happens to objects which have been dropped after the user-specified
flashback point? My guess is that they simply aren't there for the user
to go look at, but perhaps I'm wrong. Either way though, I'm not sure
that we'd accept such a poor solution to that problem. For that matter,
what do we do today wrt replication feedback when/if that happens?

Thanks,

Stephen