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.
# reproduction
## env
- 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
postgres=# create table tbchj(id int);
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
Regards
Chen Huajun
