Обсуждение: When use prepared protocol, transaction will hold backend_xminuntil the end of the transaction.
Hi, hackers!
When execute sql with prepared protocol, read committed transaction will hold backend_xmin until the end of the transaction.
Is this behavior normal?
Should read committed transaction release backend_xmin immediately after SQL executing is completed? Just like
when executing sql with simple protocol.
- PostgreSQL 9.2
- CentOS 7.2
## test script
$ cat test.sql
begin;
select 1;
\sleep 1000s
## execute with simple protocol
$ pgbench -n -t 1 -f test.sql "service=admin"
postgres=# select * from pg_stat_activity where query='select 1;';
-[ RECORD 1 ]----+------------------------------
datid | 13805
datname | postgres
pid | 19641
usesysid | 16388
usename | admin
application_name | pgbench
client_addr |
client_hostname |
client_port | -1
backend_start | 2018-07-04 13:27:10.62635+08
xact_start | 2018-07-04 13:27:10.629609+08
query_start | 2018-07-04 13:27:10.629845+08
state_change | 2018-07-04 13:27:10.63035+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid |
backend_xmin |
query | select 1;
backend_type | client backend
## execute with prepared protocol
$ pgbench -n -t 1 -f test.sql "service=admin" -M prepared
postgres=# select * from pg_stat_activity where query='select 1;';
-[ RECORD 1 ]----+------------------------------
datid | 13805
datname | postgres
pid | 19662
usesysid | 16388
usename | admin
application_name | pgbench
client_addr |
client_hostname |
client_port | -1
backend_start | 2018-07-04 13:27:46.637134+08
xact_start | 2018-07-04 13:27:46.641348+08
query_start | 2018-07-04 13:27:46.64174+08
state_change | 2018-07-04 13:27:46.641778+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid |
backend_xmin | 3930269815
query | select 1;
backend_type | client backend
backend_xmin will affect dead tuple removing
CREATE TABLE
postgres=# insert into tbchj values(1);
INSERT 0 1
postgres=# delete from tbchj;
DELETE 1
postgres=# vacuum VERBOSE tbchj;
INFO: vacuuming "public.tbchj"
INFO: "tbchj": found 0 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL: 1 dead row versions cannot be removed yet, oldest xmin: 3930269815
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
chenhj <chjischj@163.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