[Bug]: PG Segfault when ETL upsert triggers insert to empty hypertable

[bharrisau]

What type of bug is this?

Crash

What subsystems and features are affected?

Data ingestion

What happened?

Sling is running an ETL from external SQL Server DB. I wanted to trigger on insert+update to the target table, to generate new rows when there is a change to get deltas. Postgres would segfault and enter recovery mode. Workaround was to type manual "UPDATE X WHERE" for single record to generate the first delta row in the hyper table, then restart the ETL.

Running truncate on the delta hypertable causes the crash to reappear.

TimescaleDB version affected

timescaledb-2-postgresql-16/noble,now 2.17.1~ubuntu24.04

PostgreSQL version used

postgresql-16/now 16.4-1.pgdg22.04+2

What operating system did you use?

Ubuntu 24.04.01

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

2024-10-29 11:37:59.165 AWST [6783] LOG:  server process (PID 3102803) was terminated by signal 11: Segmentation fault
2024-10-29 11:37:59.165 AWST [6783] DETAIL:  Failed process was running: create temporary table temp29Eub as
                with src_table as (
                        select "pack_summary_id", "event_id", "summary_group_id", "product_id", "pack_id", "pack_count", [...] from "public"."szdb_pack_summary_tmp"
                )
                , updates as (
                        update "public"."szdb_pack_summary" tgt
                        set "event_id" = src."event_id", "summary_group_id" = src."summary_group_id", "product_id" = src."product_id", "pack_id" = src."pack_id", "pack_count" = src."pack_count", [...]

How can we reproduce the bug?

The ETL command that triggers the bug is:

create temporary table temp29Eub as
        with src_table as (
                select "pack_summary_id", "event_id", "summary_group_id", "product_id", "pack_id", "pack_count", [...]
)
, updates as (
update "public"."szdb_pack_summary" tgt
set "event_id" = src."event_id", "summary_group_id" = src."summary_group_id", "product_id" = src."product_id", "pack_id" = src."pack_id", [...]
from src_table src
where src."pack_summary_id" = tgt."pack_summary_id"
returning tgt.*
)
select * from updates


Setup for the database is
CREATE TABLE szdb_pack_summary_diff (
    pack_summary_id integer NOT NULL,
    time timestamptz NOT NULL,
    packs bigint,
    count bigint,
    weight double precision,
    event_id integer NOT NULL,
    product_id integer,
    pack_id integer
);
SELECT create_hypertable('szdb_pack_summary_diff', by_range('time', INTERVAL '1 week'));

CREATE OR REPLACE FUNCTION szdb_diff_pack_summary() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.fruit_count <> OLD.fruit_count THEN
        INSERT INTO szdb_pack_summary_diff (pack_summary_id, time, packs, count, weight, event_id, product_id, pack_id)
        VALUES (NEW.pack_summary_id, NOW(), NEW.pack_count - OLD.pack_count, NEW.fruit_count - OLD.fruit_count, NEW.weight_grams - OLD.weight_grams, NEW.event_id, NEW.product_id, NEW.pack_id);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION szdb_init_pack_summary() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO szdb_pack_summary_diff (pack_summary_id, time, packs, count, weight, event_id, product_id, pack_id)
    VALUES (NEW.pack_summary_id, NOW(), NEW.pack_count, NEW.fruit_count, NEW.weight_grams, NEW.event_id, NEW.product_id, NEW.pack_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_drop_summary_diff AFTER
INSERT ON szdb_pack_summary
FOR EACH ROW EXECUTE FUNCTION szdb_init_pack_summary();

CREATE TRIGGER after_update_drop_summary_diff AFTER
UPDATE ON szdb_pack_summary
FOR EACH ROW EXECUTE FUNCTION szdb_diff_pack_summary();

[svenklemm]

Looks like your reproducer is missing the table definition of the table szdb_pack_summary

[bharrisau]

I would have only copied what I could get from the ETL run that crashed. It's probably missing the _tmp table too with the streamed data from external.

I'll have a play and see if it can get a minimal reproduction. Didn't want to mess around too much as the DB was in use and the segfaults were causing issues with other systems.

[bharrisau]

Minimal repro.

CREATE TABLE test_diff (
    id integer NOT NULL,
    time timestamptz NOT NULL,
    count1 bigint,
    count2 double precision
);

CREATE TABLE test_src (
    id integer NOT NULL,
    count1 bigint,
    count2 double precision
);

CREATE TABLE test_dst (
    id integer NOT NULL,
    count1 bigint,
    count2 double precision
);

SELECT create_hypertable('test_diff', by_range('time', INTERVAL '1 week'));

CREATE OR REPLACE FUNCTION test_fn_diff() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.count1 <> OLD.count1 THEN
        INSERT INTO test_diff (id, time, count1, count2)
        VALUES (NEW.id, NOW(), NEW.count1 - OLD.count1, NEW.count2 - OLD.count2);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_fn_init() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO test_diff (id, time, count1, count2)
    VALUES (NEW.id, NOW(), NEW.count1, NEW.count2);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

INSERT INTO test_src (id, count1, count2)
    VALUES (1, 2, 2.0), (2, 2, 2.0), (3, 3, 3.0);

INSERT INTO test_dst (id, count1, count2)
    VALUES (1, 1, 1.0);

CREATE TRIGGER test_init AFTER
INSERT ON test_dst
FOR EACH ROW EXECUTE FUNCTION test_fn_init();

CREATE TRIGGER test_diff AFTER
UPDATE ON test_dst
FOR EACH ROW EXECUTE FUNCTION test_fn_diff();

create temporary table temp_1234 as
    with src_table as (
            select "id", "count1", "count2" FROM test_src
)
, updates as (
    update "public"."test_dst" tgt
    set "count1" = src."count1", "count2" = src."count2"
    from src_table src
    where src."id" = tgt."id"
    returning tgt.*
)
select * from updates

[bharrisau]

Have a core dump - but can't find the debug symbols in the ubuntu repos.

(gdb) bt full
#0  0x0000631e7c32b6c5 in ?? ()
No symbol table info available.
#1  0x0000631e801f0a70 in ?? ()
No symbol table info available.
#2  0x0000631e7d794658 in ?? ()
No symbol table info available.
#3  0x00007fff9551c270 in ?? ()
No symbol table info available.
#4  0x0000631e7d498dd4 in ?? ()
No symbol table info available.
#5  0x00007fff9551c2e0 in ?? ()
No symbol table info available.
#6  0x0000797e9051aea6 in ?? ()
No symbol table info available.
#7  0x0000006400000081 in ?? ()
No symbol table info available.
#8  0x0000000000000000 in ?? ()
No symbol table info available.

[akuzm]

Thanks for the repro, I can reproduce the segfault.

Stack trace

Program received signal SIGSEGV, Segmentation fault.
0x0000622a11c7293a in EventTriggerAlterTableRelid (objectId=objectId@entry=27861930) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/event_trigger.c:1564
1564		currentEventTriggerState->currentCommand->d.alterTable.objectId = objectId;
(gdb) bt
#0  0x0000622a11c7293a in EventTriggerAlterTableRelid (objectId=objectId@entry=27861930) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/event_trigger.c:1564
#1  0x0000622a11cfacb6 in AlterTableInternal (relid=relid@entry=27861930, cmds=0x52d0001509e0, recurse=false) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/tablecmds.c:4119
#2  0x000071f6d64f1237 in chunk_set_replica_identity (chunk=<optimized out>) at /home/akuzm/ts2/ts/src/chunk.c:903
#3  chunk_create_table_constraints (ht=0x525000091338, chunk=0x525000124b60) at /home/akuzm/ts2/ts/src/chunk.c:923
#4  0x000071f6d64dd0c2 in chunk_create_from_hypercube_after_lock (ht=<optimized out>, cube=<optimized out>, schema_name=<optimized out>, table_name=<optimized out>, prefix=<optimized out>) at /home/akuzm/ts2/ts/src/chunk.c:1077
#5  0x000071f6d64df558 in chunk_create_from_point_after_lock (ht=0x525000091338, p=<optimized out>, schema_name=0x5250000913bc "_timescaledb_internal", table_name=0x0, prefix=0x5250000913fc "_hyper_66") at /home/akuzm/ts2/ts/src/chunk.c:1209
#6  ts_chunk_create_for_point (ht=0x525000091338, p=<optimized out>, found=<optimized out>, schema=<optimized out>, prefix=<optimized out>) at /home/akuzm/ts2/ts/src/chunk.c:1365
#7  0x000071f6d652cf32 in ts_hypertable_create_chunk_for_point (h=0x525000091338, point=point@entry=0x525000124a20, found=found@entry=0x71f8d53d7020) at /home/akuzm/ts2/ts/src/hypertable.c:1005
#8  0x000071f6d65be063 in ts_chunk_dispatch_get_chunk_insert_state (dispatch=0x5250001226e0, point=<optimized out>, on_chunk_changed=<optimized out>, data=<optimized out>) at /home/akuzm/ts2/ts/src/nodes/chunk_dispatch/chunk_dispatch.c:136
#9  0x000071f6d65c048a in chunk_dispatch_exec (node=0x525000119048) at /home/akuzm/ts2/ts/src/nodes/chunk_dispatch/chunk_dispatch.c:440
#10 0x000071f6d65aa239 in ExecProcNode (node=0x525000119048) at /home/akuzm/ts2/install/include/postgresql/server/executor/executor.h:262
#11 ExecModifyTable (cs_node=<optimized out>, pstate=<optimized out>) at /home/akuzm/ts2/ts/src/nodes/hypertable_modify.c:722
#12 hypertable_modify_exec (node=0x525000118498) at /home/akuzm/ts2/ts/src/nodes/hypertable_modify.c:174
#13 0x0000622a11dae3c0 in ExecProcNode (node=0x525000118498) at /home/akuzm/ts2/build-pg-asan/../pg/src/include/executor/executor.h:262
#14 ExecutePlan (estate=<optimized out>, planstate=0x525000118498, operation=CMD_INSERT, numberTuples=0, direction=<optimized out>, dest=0x622a1269e220 <spi_printtupDR>, execute_once=<optimized out>, use_parallel_mode=<optimized out>, sendTuples=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/executor/execMain.c:1638
#15 standard_ExecutorRun (queryDesc=<optimized out>, direction=<optimized out>, count=0, execute_once=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/executor/execMain.c:363
#16 0x0000622a11e8c09d in _SPI_pquery (queryDesc=0x5290000a1438, tcount=0, fire_triggers=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/executor/spi.c:2909
#17 _SPI_execute_plan (plan=<optimized out>, options=0x7ffd59cf2710, snapshot=<optimized out>, crosscheck_snapshot=<optimized out>, fire_triggers=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/executor/spi.c:2678
#18 0x0000622a11e8d83d in SPI_execute_plan_with_paramlist (plan=0x51900004d4a0, params=0x52500007b638, read_only=false, tcount=0) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/executor/spi.c:750
#19 0x000071f8d8ec30cb in exec_stmt_execsql (estate=estate@entry=0x71f8d5570e20, stmt=0x5250000ffd18) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_exec.c:4286
#20 0x000071f8d8eb43d3 in exec_stmts (estate=0x71f8d5570e20, stmts=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_exec.c:2091
#21 0x000071f8d8eb7aee in exec_stmt_if (estate=0x71f8d5570e20, stmt=0x5250000ffdc8) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_exec.c:2527
#22 exec_stmts (estate=0x71f8d5570e20, stmts=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_exec.c:2035
#23 0x000071f8d8eb21a4 in exec_stmt_block (estate=estate@entry=0x71f8d5570e20, block=block@entry=0x5250000ffef0) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_exec.c:1942
#24 0x000071f8d8eaf4d0 in exec_toplevel_block (block=0x5250000ffef0, estate=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_exec.c:1633
#25 plpgsql_exec_trigger (func=0x52d00012d730, trigdata=0x71f8d542dd20) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_exec.c:1040
#26 0x000071f8d8ee45d3 in plpgsql_call_handler (fcinfo=0x7ffd59cf3520) at /home/akuzm/ts2/build-pg-asan/../pg/src/pl/plpgsql/src/pl_handler.c:268
#27 0x0000622a11d3eefe in ExecCallTriggerFunc (trigdata=trigdata@entry=0x71f8d542dd20, tgindx=tgindx@entry=0, finfo=<optimized out>, finfo@entry=0x52500009e348, instr=instr@entry=0x0, per_tuple_context=per_tuple_context@entry=0x5250000bb900) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/trigger.c:2430
#28 0x0000622a11d477d5 in AfterTriggerExecute (estate=0x52500009da20, event=0x5250000f2a68, relInfo=0x52500009df70, src_relInfo=<optimized out>, dst_relInfo=0x52500009df70, trigdesc=0x52500009e188, finfo=0x52500009e348, instr=0x0, per_tuple_context=0x5250000bb900, trig_tuple_slot1=0x0, trig_tuple_slot2=0x0) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/trigger.c:4594
#29 afterTriggerInvokeEvents (events=<optimized out>, firing_id=<optimized out>, estate=0x52500009da20, delete_ok=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/trigger.c:4833
#30 0x0000622a11d45ee4 in AfterTriggerEndQuery (estate=0x52500009da20) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/trigger.c:5188
#31 0x0000622a11daeb37 in standard_ExecutorFinish (queryDesc=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/executor/execMain.c:438
#32 0x0000622a11c5daad in ExecCreateTableAs (pstate=<optimized out>, stmt=<optimized out>, params=<optimized out>, queryEnv=<optimized out>, qc=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/commands/createas.c:346
#33 0x0000622a1229d20b in ProcessUtilitySlow (pstate=pstate@entry=0x5190000467b8, pstmt=pstmt@entry=0x529000083278, queryString=queryString@entry=0x525000007a20 "create temporary table temp_1234 as\n    with src_table as (\n", ' ' <repeats 12 times>, "select \"id\", \"count1\", \"count2\" FROM test_src\n)\n, updates as (\n    update \"public\".\"test_dst\" tgt\n    set \"count1\" = src.\"count1"..., context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=params@entry=0x0, queryEnv=queryEnv@entry=0x0, dest=<optimized out>, qc=0x71f8d5415360) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/tcop/utility.c:1668
#34 0x0000622a1229aac2 in standard_ProcessUtility (pstmt=0x529000083278, queryString=<optimized out>, readOnlyTree=<optimized out>, context=PROCESS_UTILITY_TOPLEVEL, params=<optimized out>, queryEnv=<optimized out>, dest=<optimized out>, qc=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/tcop/utility.c:1037
#35 0x000071f6d653f427 in prev_ProcessUtility (args=<optimized out>) at /home/akuzm/ts2/ts/src/process_utility.c:104
#36 0x000071f6d653f427 in timescaledb_ddl_command_start (pstmt=<optimized out>, query_string=<optimized out>, readonly_tree=false, context=<optimized out>, params=<optimized out>, queryEnv=<optimized out>, dest=<optimized out>, completion_tag=<optimized out>) from /home/akuzm/ts2/install/lib/postgresql/timescaledb-2.18.0-dev.so
#37 0x0000622a122992da in PortalRunUtility (portal=0x52500002aa20, pstmt=0x529000083278, isTopLevel=<optimized out>, setHoldSnapshot=<optimized out>, dest=<optimized out>, qc=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/tcop/pquery.c:1163
#38 0x0000622a12297acb in PortalRunMulti (portal=portal@entry=0x52500002aa20, isTopLevel=true, setHoldSnapshot=false, dest=dest@entry=0x529000083310, altdest=altdest@entry=0x529000083310, qc=0x71f8d5415360) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/tcop/pquery.c:1327
#39 0x0000622a12296658 in PortalRun (portal=portal@entry=0x52500002aa20, count=count@entry=9223372036854775807, isTopLevel=true, run_once=true, dest=dest@entry=0x529000083310, altdest=altdest@entry=0x529000083310, qc=0x71f8d5415360) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/tcop/pquery.c:794
#40 0x0000622a12293450 in exec_simple_query (query_string=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/tcop/postgres.c:2470
#41 0x0000622a1228d70d in PostgresMain (dbname=dbname@entry=0x529000011390 "test", username=username@entry=0x529000011370 "akuzm") at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/tcop/postgres.c:5609
#42 0x0000622a120eb533 in BackendRun (port=port@entry=0x515000003a00) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/postmaster/postmaster.c:4514
#43 0x0000622a120ea3c3 in BackendStartup (port=0x515000003a00) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/postmaster/postmaster.c:4242
#44 ServerLoop () at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/postmaster/postmaster.c:1809
#45 0x0000622a120e5491 in PostmasterMain (argc=<optimized out>, argv=<optimized out>) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/postmaster/postmaster.c:1481
#46 0x0000622a11eca2a3 in main (argc=1, argv=0x5020000001f0) at /home/akuzm/ts2/build-pg-asan/../pg/src/backend/main/main.c:202
(gdb)