Re: On the need for a snapshot in exec_bind_message()

On 2018-09-05 19:11:56 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2018-09-05 18:45:52 -0400, Tom Lane wrote:
> >> The snapshot has little to do with the query plan, usually. It's about
> >> what view of the database the executed query will see, and particularly
> >> about what view the parameter input functions will see, if they look.
>
> > The snapshot in exec_bind_message() shouldn't be about what the executed
> > query sees, no? Sure, we'll evaluate input params and might replan etc,
> > but other than that?
>
> As things stand, yeah, it's not used for execution.
>
> > It'd not be insane to rejigger things so there's a version of
> > PushActiveSnapshot() doesn't eagerly compute the snapshot, but instead
> > does so on first access. Obviously we couldn't use that everywhere, but
> > the exec_bind_message() seems like a prime case for it.
>
> I dislike this proposal, because it introduces an element of uncertainty
> into when the snapshot is taken. It doesn't seem a lot different from
> saying that we'll try to save a gettimeofday call by postponing
> calculation of the statement_timestamp until somebody asks for it.
> Then it's no longer the statement start time, but some hard-to-define
> time within execution.

I don't think that really is comparable. The timestamp really exists on
a (well, for our purposes) absolute scale. That's much less clearer for
snapshots in read committed: Without access to the database or returning
results the moment the snapshot has been taken really doesn't mean that
much - the session could really just have been scheduled out just before
taking the snapshot (or waited for the procarray lock, to be more on
topic).

Additionally, this isn't the snapshot for the actual query execution -
it's the one for the parameter evaluation. And we *already* take a
separate snapshot for that from the one actually executing the query, so
skew between the two really can't be meaningful.

> I think the other idea of trying to keep the bind-time snapshot in use
> throughout execution is more worth pursuing.

Hm, that seems to have its own set of problems. Some of them
nontrivial. It's e.g. perfectly legal to bind multiple statements and
then execute them - and I think it's quite possible that pgjdbc would
generate something like that.

And then there's:
> The main problem with it, now that I think harder, is that we need the
> execution snapshot to be taken after we've acquired whatever locks the
> query needs.

Greetings,

Andres Freund