fabriziomello
commented
7 months ago @kimec thanks for reaching out, can u send a complete reproducible SQL script including the complete INSERT statement???
Open kimec opened 7 months ago
fabriziomello
commented
7 months ago @kimec thanks for reaching out, can u send a complete reproducible SQL script including the complete INSERT statement???
kimec
commented
7 months ago Hello @fabriziomello,
The DDL for the table is
CREATE TABLE public.test_table
(
id bigserial NOT NULL,
id2 int8 NOT NULL,
id3 text NOT NULL,
column1 text NOT NULL,
column2 text NOT NULL,
column3 numeric NOT NULL,
column4 timestamp NOT NULL,
column5 int8 NOT NULL,
column6 int8 NOT NULL DEFAULT 0,
column7 text NULL,
column8 text NULL,
column9 bool NOT NULL DEFAULT FALSE,
column10 text NOT NULL DEFAULT '-'::text,
column11 text NOT NULL DEFAULT '-'::text,
column12 text NOT NULL DEFAULT '-'::text,
column13 bool NOT NULL DEFAULT FALSE,
column14 timestamp NOT NULL DEFAULT timezone('UTC'::text, CURRENT_TIMESTAMP),
column15 numeric NULL,
column16 numeric NULL,
column17 text NULL,
column18 text NULL,
column19 text NULL,
column20 text NULL,
column21 text NULL,
column22 numeric NULL,
column23 numeric NULL,
column24 int4 NULL,
column25 int4 NULL,
column26 text NULL,
column27 bool NULL,
column28 bool NULL,
CONSTRAINT test_table_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_column17 ON public.test_table USING btree (column17);
CREATE INDEX test_table_history_idx ON public.test_table USING btree (column5, id DESC) WHERE (NOT ((column3 = (0)::numeric) OR (column1 = 'XXXXX'::text)));
CREATE INDEX test_table_id2_idx ON public.test_table USING btree (id2);
CREATE INDEX test_table_column5 ON public.test_table USING btree (column5);
CREATE INDEX test_table_id3 ON public.test_table USING btree (id3);
SELECT create_hypertable('test_table', 'id', chunk_time_interval => 25000000);INSERT statement example is
INSERT INTO test_table (id2, id3, column1, column2, column21, column3, column4, column5, column17, column6,
column10, column11, column12, column15, column16, column19, column20, column22, column23,
column24, column25, column18, column7, column27, column28)
SELECT
123,
'c7aa52f76c522c5137b1927348bb3271',
'SENTINEL1',
'aa',
'aa',
0,
'2024-01-17 10:24:02',
15294609,
'SENTINEL2',
-3,
'bb',
'cc',
'dd',
0.0,
0.0,
'ee',
'ee',
0,
0,
0,
0,
NULL,
NULL,
'FALSE',
'TRUE'
WHERE NOT EXISTS (SELECT 1 FROM test_table WHERE column17 = 'SENTINEL2' AND column1 = 'SENTINEL1' AND id >= 1);I will try to come up with some generator script next week. We did our testing by dumping data from the affected system and restoring it on a separate test system. Unfortunately, the dumped dataset is quite large and contains sensitive data.
fabriziomello
commented
7 months ago @kimec did some tests with your schema and I was not able to reproduce the problem. One potential workaround can be create an unique index and use INSERT ON CONFLICT DO NOTHING.
But anyway would be nice you can end up with a reproducible test case so we can provide a fix.
kimec
commented
6 months ago We tried to create a reproducer but were not able to trigger the segfault on artificially generated data. I should note, the dataset that causes the segfault was migrated from Postgres 10 Timescale 1.6. That being said, we migrated the dataset incrementally upgrading both Postgres and Timescale in steps according the compatibility charts in TS documentation. We will be able to drop the migrated dataset fully in a month or two by which time all our live partitions would be created by Pg 15 and TS 2.13 exclusively. I will get back to you then whether the issue still persists on new partitions. If not, I will close the issue. Since this issue may be related to our specific upgrade path, please treat it with a lower priority.
What type of bug is this?
Crash
What subsystems and features are affected?
Query executor
What happened?
INSERT INTO table [...] WHERE NOT EXISTS (SELECT 1 FROM table WHERE some_condition IS TRUE)causes instant segfault for a partition of certain size (likely fully occupied). The affected table is chunked on scalarBIGSRIALwithchunk_time_interval => 25000000TimescaleDB version affected
2.13.1
PostgreSQL version used
15.5
What operating system did you use?
CentOS 7.9.2009
What installation method did you use?
RPM
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
How can we reproduce the bug?
We can replicate the segfault on two separate servers with identical configuration every time, however, the crash is dependent on partition occupancy. It does not occur if a partition is not fully occupied. We haven't examined the exact occupancy threshold needed to trigger the segfault.