Re: batch fdw insert bug (Postgres 14)

[Pavel Stehule]

pá 7. 5. 2021 v 11:48 odesílatel Pavel Stehule <pavel.steh...@gmail.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
>