Date: Mon, 6 Jul 2020 00:01:29 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: David Gilman <davidgilman1@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: "tuple concurrently updated" in pg_restore --jobs
Message-ID: <20200706050129.GW4107@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

I hit this issue intermittently (roughly half the time) while working with a
patch David submitted, and finally found a recipe to reproduce it on an
unpatched v12 instance.

I was surprised to see pg_restore -j2 is restoring ACLs in pre-data in
parallel.  Note different session IDs and PIDs:

2020-07-05 23:31:27.448 CDT,"pryzbyj","secondary_dump",24037,"[local]",5f02a91f.5de5,70,,LOG,00000,"statement: REVOKE SELECT ON TABLE pg_catalog.pg_proc FROM PUBLIC; ",,,,,,,,,"pg_restore","client backend"
2020-07-05 23:31:27.448 CDT,"pryzbyj","secondary_dump",24036,"[local]",5f02a91f.5de4,78,,LOG,00000,"statement: GRANT SELECT(tableoid) ON TABLE pg_catalog.pg_proc TO PUBLIC; ",,,,,,,,,"pg_restore","client backend"
2020-07-05 23:31:27.450 CDT,"pryzbyj","secondary_dump",24036,"[local]",5f02a91f.5de4,79,,LOG,00000,"statement: GRANT SELECT(oid) ON TABLE pg_catalog.pg_proc TO PUBLIC; ",,,,,,,,,"pg_restore","client backend"
2020-07-05 23:31:27.450 CDT,"pryzbyj","secondary_dump",24037,"[local]",5f02a91f.5de5,71,,ERROR,XX000,"tuple concurrently updated",,,,,,"REVOKE SELECT ON TABLE pg_catalog.pg_proc FROM PUBLIC;

postgres=# CREATE DATABASE pryzbyj;
postgres=# \c pryzbyj 
pryzbyj=# REVOKE ALL ON pg_proc FROM postgres;
pryzbyj=# GRANT SELECT (tableoid, oid, proname) ON pg_proc TO public;
pryzbyj=# \dp+ pg_catalog.pg_proc
   Schema   |  Name   | Type  | Access privileges | Column privileges | Policies 
------------+---------+-------+-------------------+-------------------+----------
 pg_catalog | pg_proc | table | =r/postgres       | tableoid:        +| 
            |         |       |                   |   =r/postgres    +| 
            |         |       |                   | oid:             +| 
            |         |       |                   |   =r/postgres    +| 
            |         |       |                   | proname:         +| 
            |         |       |                   |   =r/postgres     | 

[pryzbyj@database ~]$ pg_dump pryzbyj -Fc -f pg_dump.out
[pryzbyj@database ~]$ pg_restore pg_dump.out -j2 -d pryzbyj --clean -v
...
pg_restore: entering main parallel loop
pg_restore: launching item 3744 ACL TABLE pg_proc
pg_restore: launching item 3745 ACL COLUMN pg_proc.proname
pg_restore: creating ACL "pg_catalog.TABLE pg_proc"
pg_restore: creating ACL "pg_catalog.COLUMN pg_proc.proname"
pg_restore:pg_restore:  while PROCESSING TOC:
finished item 3745 ACL COLUMN pg_proc.proname
pg_restore: from TOC entry 3744; 0 0 ACL TABLE pg_proc postgres
pg_restore: error: could not execute query: ERROR:  tuple concurrently updated
Command was: REVOKE ALL ON TABLE pg_catalog.pg_proc FROM postgres;