Re: batch fdw insert bug (Postgres 14)

pá 7. 5. 2021 v 11:48 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
> I am testing new features in Postgres 14, and I found bug
>
> EXPLAIN ANALYZE VERBOSE for insert to FDW table with batch_size 1000
> returns
>
>
> -------------------------------------------------------------------------------------------------------------------------------
> Insert on public.vzdalena_tabulka2 (cost=0.00..175.00 rows=0 width=0)
> (actual time=30.269..30.270 rows=0 loops=1)
> Remote SQL:
> \x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F
> Batch Size: 1000
> -> Function Scan on pg_catalog.generate_series g (cost=0.00..175.00
> rows=10000 width=36) (actual time=0.453..1.988 rows=10
> Output: g.i, ('AHOJ'::text || (g.i)::text)
> Function Call: generate_series(1, 10000)
> Planning Time: 0.075 ms
> Execution Time: 31.032 ms
> (8 rows)
>

reproducer

CREATE DATABASE omega;

\c omega

CREATE TABLE tabulka(a int, b varchar);

\c postgres

CREATE EXTENSION postgres_fdw;

CREATE SERVER omega_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'omega');

CREATE USER MAPPING FOR "pavel.stehule"
SERVER omega_db OPTIONS (user 'pavel.stehule');

CREATE FOREIGN TABLE vzdalena_tabulka(a int, b varchar)
SERVER omega_db
OPTIONS (table_name 'tabulka');

CREATE FOREIGN TABLE vzdalena_tabulka2(a int, b varchar)
SERVER omega_db
OPTIONS (table_name 'tabulka', batch_size '1000');

EXPLAIN ANALYZE VERBOSE INSERT INTO vzdalena_tabulka SELECT i, 'AHOJ' || i
FROM generate_series(1,10000) g(i);
EXPLAIN ANALYZE VERBOSE INSERT INTO vzdalena_tabulka2 SELECT i, 'AHOJ' || i
FROM generate_series(1,10000) g(i);

Pavel

>
> Regards
>
> Pavel
>