Postgres Pro
Downloads
Home   >   mailing lists

Re: batch fdw insert bug (Postgres 14) - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: batch fdw insert bug (Postgres 14)
Date
Msg-id CAFj8pRB51ZEY20cknAzr_7zi4w3t3wNqoxEKR-EmqENTGg-XTQ@mail.gmail.com
Whole thread Raw
In response to batch fdw insert bug (Postgres 14)  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses RE: batch fdw insert bug (Postgres 14)
List pgsql-hackers
Tree view


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

pgsql-hackers by date:

Previous
From: Andrey Lepikhov
Date:
Subject: Re: Asynchronous Append on postgres_fdw nodes.
Next
From: Nitin Jadhav
Date:
Subject: Query regarding RANGE Partitioning