RE: Avoid CommandCounterIncrement in RI trigger when INSERT INTO referencing table

From: Hou, Zhijie/侯 志杰 <houzj(dot)fnst(at)fujitsu(dot)com>
> After some more on how to support parallel insert into fk relation.
> It seems we do not have a cheap way to implement this feature.
>
> In RI_FKey_check, Currently, postgres execute "select xx for key share" to
> check that foreign key exists in PK table.
> However "select for update/share" is considered as parallel unsafe. It may be
> dangerous to do this in parallel mode, we may want to change this.

Hmm, I guess the parallel leader and workers can execute SELECT FOR KEY SHARE, if the parallelism infrastructure allows execution of SPI calls. The lock manager supports tuple locking in parallel leader and workers by the group locking. Also, the tuple locking doesn't require combo Cid, which is necessary for parallel UPDATE and DELETE.

Perhaps the reason why SELECT FOR is treated as parallel-unsafe is that tuple locking modifies data pages to store lock information in the tuple header. But now, page modification is possible in parallel processes, so I think we can consider SELECT FOR as parallel-safe. (I may be too optimistic.)

> And also, "select for update/share" is considered as "not read only" which will
> force readonly = false in _SPI_execute_plan.

read_only is used to do CCI. Can we arrange to skip CCI?

> At the same time, " simplifying foreign key/RI checks " thread is trying to
> replace "select xx for key share" with index_beginscan()+table_tuple_lock() (I
> think it’s parallel safe).
> May be we can try to support parallel insert fk relation after " simplifying foreign
> key/RI checks " patch applied ?

Why do you think it's parallel safe?

Can you try running parallel INSERT SELECT on the target table with FK and see if any problem happens?

If some problem occurs due to the tuple locking, I think we can work around it by avoiding tuple locking. That is, we make parallel INSERT SELECT lock the parent tables in exclusive mode so that the check tuples won't be deleted. Some people may not like this, but it's worth considering because parallel INSERT SELECT would not have to be run concurrently with short OLTP transactions. Anyway, tuple locking partly disturbs parallel INSERT speedup because it modifies pages in the parent tables and emits WAL.

Surprisingly, Oracle doesn't support parallel INSERT SELECT on a table with FK as follows. SQL Server doesn't mention anything, so I guess it's supported. This is a good chance for PostgreSQL to exceed Oracle.

https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/types-parallelism.html#GUID-D4CFC1F2-44D3-4BE3-B5ED-6A309EB8BF06

Table 8-1 Referential Integrity Restrictions
DML Statement Issued on Parent Issued on Child Self-Referential
INSERT (Not applicable) Not parallelized Not parallelized

Regards
Takayuki Tsunakawa