Re: Tid scan increments value of pg_stat_all_tables.seq_scan. (but not seq_tup_read)

On Mon, Feb 3, 2020 at 6:20 PM Kasahara Tatsuhito
<kasahara(dot)tatsuhito(at)gmail(dot)com> wrote:
> Therefore, from v12, Tid scan not only increases the value of
> seq_scan, but also acquires a predicate lock.

Based on further investigation and Fujii's advice, I've summarized
this issue as follows.

From commit 147e3722f7, Tid Scan came to
(A) increments num of seq_scan on pg_stat_*_tables
and
(B) take a predicate lock on the entire relation.

(A) may be confusing to users, so I think it is better to fix it.
For (B), an unexpected serialization error has occurred as follows, so
I think it should be fix.

=========================================================================
[Preparation]
CREATE TABLE tid_test (c1 int, c2 int);
INSERT INTO tid_test SELECT generate_series(1,1000), 0;

[Session-1:]
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
[Session-2:]
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
[Session-1:]
SELECT * FROM tid_test WHERE ctid = '(0,1)';
[Session-2:]
SELECT * FROM tid_test WHERE ctid = '(1,1)';
[Session-1:]
INSERT INTO tid_test SELECT 1001, 10;
[Session-2:]
INSERT INTO tid_test SELECT 1001, 10;
[Session-1:]
COMMIT;
[Session-2:]
COMMIT;

Result:
(-v11): Both session could commit.
(v12-): Session-2 raised error as following because of taking a
predicate lock on the entire table...
--------
ERROR: could not serialize access due to read/write dependencies
among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during
commit attempt.
HINT: The transaction might succeed if retried.
--------
=========================================================================

Attached patch fix both (A) and (B), so that the behavior of Tid Scan
back to the same as before v11.
(As a result, this patch is the same as the one that first attached.)

Best regards,
--
Tatsuhito Kasahara
kasahara.tatsuhito _at_ gmail.com