Re: ResourceOwners for Snapshots? holdable portals

Hi,

I'm toying around with the idea of tracking snaphots more accurately to
be able to advance Xmin for read committed transactions.

I think it's relatively easy to do it in the straightforward way, which
is to just add "destroy snapshots" in the spots where a snapshot
variable goes out of scope. However, I've been thinking in doing it in
a little more elaborate (and, AFAICS, better) way: having the
ResourceOwner code be responsible for keeping track of snapshots.

Offhand I don't see any big problem with that, althought I admit I
haven't yet tried any code. One thing that jumps at me, however, is the
handling of holdable portals.

We currently just copy the portal's content into a Materialize node, and
let the snapshot go away at transaction's end. This works, but ISTM we
could improve that by keeping track of the portal's snapshot separately
from the transaction -- that is to say, to hang it from the portal's
ResourceOwner. This would allow us to avoid the Materialize node
altogether, and just keep the xmin back until the portal's gone. Vacuum
would, of course, not be able to clean up rows needed by the portal. I
don't see this as a problem, but rather as an improvement.

Thoughts?

Also, is there anything else on the whole Snapshots-on-ResourceOwners
idea that could be a showstopper?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> We currently just copy the portal's content into a Materialize node, and
> let the snapshot go away at transaction's end. This works, but ISTM we
> could improve that by keeping track of the portal's snapshot separately
> from the transaction -- that is to say, to hang it from the portal's
> ResourceOwner. This would allow us to avoid the Materialize node
> altogether, and just keep the xmin back until the portal's gone.

That's a pretty horrid idea: what if the query being executed by the
portal has side-effects? You can't get away with not executing it
to completion before you close the transaction. Not to mention that
it depends on locks not just snapshots.

As far as the general point goes, I had been thinking of managing
snapshots in a central cache, because if you want to advance xmin
intratransaction then some piece of code has to be aware of *all* the
open snapshots in the backend; and the ResourceOwners can't do that
conveniently because they're fairly independent. Or were you meaning
that you would do that and on top of it have the ResourceOwners track
references into the cache?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > We currently just copy the portal's content into a Materialize node, and
> > let the snapshot go away at transaction's end. This works, but ISTM we
> > could improve that by keeping track of the portal's snapshot separately
> > from the transaction -- that is to say, to hang it from the portal's
> > ResourceOwner. This would allow us to avoid the Materialize node
> > altogether, and just keep the xmin back until the portal's gone.
>
> That's a pretty horrid idea: what if the query being executed by the
> portal has side-effects? You can't get away with not executing it
> to completion before you close the transaction.

Ah, excellent point -- I guess that's what I was missing.

> As far as the general point goes, I had been thinking of managing
> snapshots in a central cache, because if you want to advance xmin
> intratransaction then some piece of code has to be aware of *all* the
> open snapshots in the backend; and the ResourceOwners can't do that
> conveniently because they're fairly independent. Or were you meaning
> that you would do that and on top of it have the ResourceOwners track
> references into the cache?

Yeah, I think there needs to be a separate list either way, but having
references to it from ResourceOwners means there's no need to have extra
cleanup calls at (sub)transaction commit/abort.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.