FDW INSERT batching can change behavior
От | git@jasonk.me |
---|---|
Тема | FDW INSERT batching can change behavior |
Дата | |
Msg-id | 20240809030755.jubqv6f6vpxkfkzv@jasonk.me обсуждение исходный текст |
Ответы |
Re: FDW INSERT batching can change behavior
Re: FDW INSERT batching can change behavior |
Список | pgsql-bugs |
Hi, According to the code, foreign data wrapper INSERT ON CONFLICT batching has several limitations such as no RETURNING clause, no row triggers(?). I found one case that is not disallowed and ends up causing a behavior difference depending on whether batching is enabled and not. This example is derived from the contrib/postgres_fdw pg_regress test: CREATE EXTENSION postgres_fdw; CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw; DO $d$ BEGIN EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname '$$||current_database()||$$', port '$$||current_setting('port')||$$' )$$; END; $d$; CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; create table gloc1 ( a int PRIMARY KEY, b int generated always as (a * 2) stored); alter table gloc1 set (autovacuum_enabled = 'false'); create foreign table grem1 ( a int not null, b int generated always as (a * 2) stored) server loopback options(table_name 'gloc1'); create function counter() returns int8 language sql as $$select count(*) from grem1$$; ALTER FOREIGN TABLE grem1 ALTER COLUMN a SET DEFAULT (counter()); insert into grem1 (a) values (default), (default), (default), (default), (default); alter server loopback options (add batch_size '3'); insert into grem1 (a) values (default), (default), (default), (default), (default); The first insert does not use batching, so it goes R W R W R W R W R W (R for executing the default function to generate a slot: nodeModifyTable.c ExecModifyTable context.planSlot = ExecProcNode(subplanstate); W for inserting into the table). This way, whenever the default function is called, it returns a new value. The second insert uses batching, so it goes R R R W W W R R W W. The function returns the same value within a batch, and in this case, it causes a conflict: ERROR: duplicate key value violates unique constraint "gloc1_pkey" DETAIL: Key (a)=(5) already exists. CONTEXT: remote SQL command: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT), ($2, DEFAULT), ($3, DEFAULT) Tested on 15.2 and 16.4 I compiled myself. Jason
В списке pgsql-bugs по дате отправления: