Re: When use prepared protocol, transaction will hold backend_xmin until the end of the transaction.

chenhj <chjischj(at)163(dot)com> writes:
> When execute sql with prepared protocol, read committed transaction will hold backend_xmin until the end of the transaction.

No, just till the active portal is dropped.

In the case you show, the issue is that libpq doesn't bother to issue
an explicit Close Portal message, but just lets the unnamed portal
get recycled implicitly by the next query (cf. PQsendQueryGuts).
So the portal stays open, and its snapshot stays alive, till some
other command is sent. This is different from the behavior for simple
query mode, where the portal is automatically closed after execution.

I agree this isn't very desirable now that we have mechanisms to
advance the advertised xmin as soon as snapshots go away.

Perhaps portals could be taught to drop their snapshots as soon as
the query has reached completion, but it'd be a little bit ticklish
to not break valid use-patterns for cursors.

Another idea would be to fix it on the client side by including an
explicit Close command in the PQsendQuery sequence. But if there
are similar usage patterns in other client libraries, it might take
a long time to get them all up to speed.

regards, tom lane