RE: Parallel INSERT (INTO ... SELECT ...)

From: Greg Nancarrow <gregn4422(at)gmail(dot)com>
--------------------------------------------------
On Mon, Jan 25, 2021 at 10:23 AM tsunakawa(dot)takay(at)fujitsu(dot)com <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> (8)
> + /*
> + * If the trigger type is RI_TRIGGER_FK, this indicates a FK exists in
> + * the relation, and this would result in creation of new CommandIds
> + * on insert/update/delete and this isn't supported in a parallel
> + * worker (but is safe in the parallel leader).
> + */
> + trigtype = RI_FKey_trigger_type(trigger->tgfoid);
> + if (trigtype == RI_TRIGGER_FK)
> + {
> + if (max_parallel_hazard_test(PROPARALLEL_RESTRICTED, context))
> + return true;
> + }
>
> Here, RI_TRIGGER_FK should instead be RI_TRIGGER_PK, because RI_TRIGGER_FK triggers do not generate command IDs. See RI_FKey_check() which is called in RI_TRIGGER_FK case. In there, ri_PerformCheck() is called with the detectNewRows argument set to false, which causes CommandCounterIncrement() to not be called.
>

Hmmm, I'm not sure that you have read and interpreted the patch code correctly.
The existence of a RI_TRIGGER_FK trigger indicates the table has a foreign key, and an insert into such a table will generate a new commandId (so we must avoid that, as we don't currently have the technology to support sharing of new command IDs across the participants in the parallel operation). This is what the code comment says, It does not say that such a trigger generates a new command ID.

See Amit's updated comment here: https://github.com/postgres/postgres/commit/0d32511eca5aec205cb6b609638ea67129ef6665

In addition, the 2nd patch has an explicit test case for this (testing insert into a table that has a FK).
--------------------------------------------------

First of all, I anticipate this parallel INSERT SELECT feature will typically shine, and expected to work, in the ETL or ELT into a data warehouse or an ODS for analytics. Bearing that in mind, let me list some issues or questions below. But the current state of the patch would be of course attractive in some workloads, so I don't think these are not necessarily blockers.

(1)
According to the classic book "The Data Warehouse Toolkit" and the website [1] by its author, the fact table (large transaction history) in the data warehouse has foreign keys referencing to the dimension tables (small or medium-sized master or reference data). So, parallel insert will be effective if it works when loading data into the fact table with foreign keys.

To answer the above question, I'm assuming:

CREATE TABLE some_dimension (key_col int PRIMARY KEY);
CREATE TABLE some_fact (some_key int REFERENCES some_dimension);
INSERT INTO some_fact SELECT ...;

My naive question is, "why should new command IDs be generated to check foreign key constraints in this INSERT case? The check just reads the parent (some_dimension table here)..."

Looking a bit deeper into the code, although ri_PerformCheck() itself tries to avoid generating command IDs, it calls _SPI_execute_snapshot() with the read_only argument always set to false. It in turn calls _SPI_execute_plan() -> CommandCounterIncrement() as follows:

[_SPI_execute_plan()]
/*
* If not read-only mode, advance the command counter before each
* command and update the snapshot.
*/
if (!read_only && !plan->no_snapshots)
{
CommandCounterIncrement();
UpdateActiveSnapshotCommandId();
}

Can't we pass true to read_only from ri_PerformCheck() in some cases?

(2)
Likewise, dimension tables have surrogate keys that are typically implemented as a sequence or an identity column. It is suggested that even fact tables sometimes (or often?) have surrogate keys. But the current patch does not parallelize the statement when the target table has a sequence or an identity column.

I was looking at the sequence code, and my naive (again) idea is that the parallel leader and workers allocates numbers from the sequence independently, and sets the largest number of them as the session's currval at the end of parallel operation. We have to note in the documentation that gaps in the sequence numbers will arise and not used in parallel DML.

(3)
As Hou-san demonstrated, the current patch causes the resulting table and index to become larger when inserted in parallel than in inserted serially. This could be a problem for analytics use cases where the table is just inserted and read only afterwards. We could advise the user to run REINDEX CONCURRENTLY after loading data, but what about tables?

BTW, I don't know if Oracle and SQL Server have similar issues. They may have some reason about this why they take an exclusive lock on the target table.

(4)
When the target table is partitioned, is the INSERT parallelized among its partitions? Some plan like:

Parallel Append on parent_table
-> Insert on partiton1
-> Insert on partiton2

[1]
Fact Table Surrogate Key | Kimball Dimensional Modeling Techniques
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/fact-surrogate-key/

Regards
Takayuki Tsunakawa