From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Peter Eisentraut <peter@eisentraut.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Performance degradation on concurrent COPY into a single relation in PG16.
Date: Mon, 25 Sep 2023 15:42:26 -0400
Message-ID: <257696.1695670946@sss.pgh.pa.us> (raw)
In-Reply-To: <20230916000011.2ugpkkkp7bpp4cfh@awork3.anarazel.de>
References: <20230812200504.li5vomg36j2tretx@awork3.anarazel.de>
<20230816111546.wrkepwwjwfuottpq@alvherre.pgsql>
<20230816225432.fibahevlqsssj2hv@awork3.anarazel.de>
<1578568.1694037713@sss.pgh.pa.us>
<20230916000011.2ugpkkkp7bpp4cfh@awork3.anarazel.de>
Andres Freund <andres@anarazel.de> writes:
> On 2023-09-06 18:01:53 -0400, Tom Lane wrote:
>> It turns out that this patch is what's making buildfarm member
>> chipmunk fail in contrib/pg_visibility [1]. That's easily reproduced
>> by running the test with shared_buffers = 10MB. I didn't dig further
>> than the "git bisect" result:
> At first I was a bit confounded by not being able to reproduce this. My test
> environment had max_connections=110 for some other reason - and the problem
> doesn't reproduce with that setting...
I just did a git bisect run to discover when the failure documented
in bug #18130 [1] started. And the answer is commit 82a4edabd.
Now, it's pretty obvious that that commit didn't in itself cause
problems like this:
postgres=# \copy test from 'bug18130.csv' csv
ERROR: could not read block 5 in file "base/5/17005": read only 0 of 8192 bytes
CONTEXT: COPY test, line 472: "0,185647715,222655,489637,2,2023-07-31,9100.0000000,302110385,2023-07-30 14:16:36.750981+00,14026347..."
IMO there must be some very nasty bug lurking in the new
multiple-block extension logic, that happens to be exposed by this
test case with 82a4edabd's adjustments to the when-to-extend choices
but wasn't before that.
To save other people the trouble of extracting the in-line data
in the bug submission, I've attached the test files I was using.
The DDL is simplified slightly from what was submitted. I'm not
entirely sure why a no-op trigger is needed to provoke the bug...
regards, tom lane
[1] https://www.postgresql.org/message-id/18130-7a86a7356a75209d%40postgresql.orgAttachments:bug18130.sql (text/plain, 4.7K) download
-- run this, then \copy test from 'bug18130.csv' csv
create table if not exists test (
a smallint,
b bigint,
c bigint,
d bigint,
e smallint,
plan_date date,
g numeric(18,7),
h bigint,
i timestamptz,
j bigint,
k integer,
l smallint,
primary key (a, b, c, d, e, plan_date)
) partition by list(a);
/* this is just to generate partition structure automatically */
create or replace function test_add_partitions(a integer, year integer)
returns void
volatile strict
as
$$
declare
parent text;
root text;
begin
root := 'test_' || a::text;
execute 'create table if not exists ' || root || ' partition of test for
values in (' || a::text || ') partition by hash (b);';
for b in 0..7 loop
parent := root || '_' || b::text;
execute 'create table if not exists ' || parent ||
' partition of ' || root || ' for values with (modulus
8, remainder ' || b::text || ')' ||
' partition by range (plan_date);';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm1 partition of ' ||
parent ||
' for values from (''' || year::text || '-01-01'') to
(''' || year::text ||
'-02-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm2 partition of ' ||
parent ||
' for values from (''' || year::text || '-02-01'') to
(''' || year::text ||
'-03-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm3 partition of ' ||
parent ||
' for values from (''' || year::text || '-03-01'') to
(''' || year::text ||
'-04-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm4 partition of ' ||
parent ||
' for values from (''' || year::text || '-04-01'') to
(''' || year::text ||
'-05-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm5 partition of ' ||
parent ||
' for values from (''' || year::text || '-05-01'') to
(''' || year::text ||
'-06-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm6 partition of ' ||
parent ||
' for values from (''' || year::text || '-06-01'') to
(''' || year::text ||
'-07-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm7 partition of ' ||
parent ||
' for values from (''' || year::text || '-07-01'') to
(''' || year::text ||
'-08-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm8 partition of ' ||
parent ||
' for values from (''' || year::text || '-08-01'') to
(''' || year::text ||
'-09-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm9 partition of ' ||
parent ||
' for values from (''' || year::text || '-09-01'') to
(''' || year::text ||
'-10-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm10 partition of ' ||
parent ||
' for values from (''' || year::text || '-10-01'') to
(''' || year::text ||
'-11-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm11 partition of ' ||
parent ||
' for values from (''' || year::text || '-11-01'') to
(''' || year::text ||
'-12-01'');';
execute 'create table if not exists ' ||
parent || '_y' || year::text || 'm12 partition of ' ||
parent ||
' for values from (''' || year::text || '-12-01'') to
(''' || (year + 1)::text ||
'-01-01'');';
end loop;
end;
$$ language plpgsql;
select test_add_partitions(0, 2022);
select test_add_partitions(0, 2023);
select test_add_partitions(0, 2024);
create or replace function t_test_trigger_func()
returns trigger as
$$
BEGIN
-- NEW.h := nextval(TG_ARGV[0]::regclass);
-- NEW.i := NOW();
RETURN NEW;
END;
$$ language plpgsql volatile;
-- create sequence if not exists test_seq;
create or replace trigger t_test_trigger before insert or update on test for each row execute procedure t_test_trigger_func(); -- ('test_seq');
bug18130.csv (text/plain, 96.4K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, hlinnaka@iki.fi, alvherre@alvh.no-ip.org, sawada.mshk@gmail.com, dgrowleyml@gmail.com, dean.a.rasheed@gmail.com, jakub.wartak@enterprisedb.com, andrew@dunslane.net, peter@eisentraut.org, pgsql-hackers@lists.postgresql.org
Subject: Re: Performance degradation on concurrent COPY into a single relation in PG16.
In-Reply-To: <257696.1695670946@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox