Re: Tid scan increments value of pg_stat_all_tables.seq_scan. (butnot seq_tup_read) - Mailing list pgsql-hackers

From Kasahara Tatsuhito
Subject Re: Tid scan increments value of pg_stat_all_tables.seq_scan. (butnot seq_tup_read)
Date
Msg-id CAP0=ZVJD+-Zwi-sdb9KyR-Hv175VXM92mBFN2qfVQqp7iaFP-A@mail.gmail.com
Whole thread Raw
In response to Re: Tid scan increments value of pg_stat_all_tables.seq_scan. (butnot seq_tup_read)  (Kasahara Tatsuhito <kasahara.tatsuhito@gmail.com>)
Responses Re: Tid scan increments value of pg_stat_all_tables.seq_scan. (butnot seq_tup_read)
List pgsql-hackers
On Mon, Feb 3, 2020 at 6:20 PM Kasahara Tatsuhito
<kasahara.tatsuhito@gmail.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

Attachment

pgsql-hackers by date:

Previous
From: Julien Rouhaud
Date:
Subject: Re: Expose lock group leader pid in pg_stat_activity
Next
From: Konstantin Knizhnik
Date:
Subject: Re: [Proposal] Global temporary tables