<@U0675Q5MTL5> Interesting case on PG14

DROP TABLE IF EXISTS my_table; CREATE TABLE IF NOT EXISTS my_table ( a bigint, b text, c text ) PARTITION BY HASH (b, a, c); CREATE TABLE my_table_00 PARTITION OF my_table FOR VALUES WITH (MODULUS 1, REMAINDER 0); SELECT "my_table".* FROM "my_table" WHERE "my_table"."a" IS NULL AND "my_table"."b" = 'foobar' AND "my_table"."c" = 'goobar'; leads to server crash:

server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. can you check in a shared env?

The issue you encountered seems to be quite serious if the server is indeed crashing due to a SELECT query on a partitioned table. This is not a normal behavior and could potentially be a bug. However, to further investigate this issue, we would need to reproduce the scenario in a controlled environment and examine the logs to determine the cause of the crash.

Before proceeding with setting up an experiment, it is also worth looking into the release notes and documentation for partitioning improvements and known issues. For instance, in the PostgreSQL 11 release notes, improvements were made to partitioning functionality including adding support for hash partitioning and triggers on partitioned tables (source: ). Since the problem you reported is on PostgreSQL 14, it is important to check if there were any related changes or bugs reported for that version.

If you would like to proceed with testing this in a controlled environment, please confirm, and I will set up an experiment in a shared environment to reproduce the issue and provide you with the results.

<@U0675Q5MTL5> let's go

The experiment has been set up and is starting. You can follow the progress of the pipeline . Once the experiment is complete, I will provide you with the results to determine the cause of the server crash you've encountered.

<@U0675Q5MTL5> check results

The experiment has completed successfully, and based on the output, there was no server crash detected. The query execution returned 0 rows and completed in 1.906 ms without any server issues. This means the crash you encountered may have been specific to your environment or perhaps caused by factors not replicated in our shared environment.

Here's the output from the experiment:

• Query executed: SELECT "my_table".* FROM "my_table" WHERE "my_table"."a" IS NULL AND "my_table"."b" = 'foobar' AND "my_table"."c" = 'goobar';

• Result: 0 rows returned.

• Time: 1.906 ms.

An error was noticed regarding an unrecognized reset target "recovery_prefetch", but this is unrelated to the server crash issue.

You can examine the experiment in more detail at this .

If you are still experiencing crashes with the given query, I recommend checking the PostgreSQL logs for error messages around the time of the crash in your environment and verifying if there are any configuration differences or system limitations that might contribute to this issue. Remember to test any changes in a non-production environment before applying them to production.

<@U0675Q5MTL5> check results

The experiment in the shared environment has already been thoroughly checked, and the results are as follows:

• The query that was expected to lead to a server crash was executed without any issues.

• The output indicates that there were 0 rows returned, and the query completed in 1.906 milliseconds.

No server crash occurred during the execution of the query in the shared environment, suggesting that the issue you encountered may be specific to your particular setup or configuration. The full details of the experiment's execution and its outcomes can be reviewed at this .

Given these findings, the problem appears not to be with the query itself. Therefore, I recommend investigating environmental factors such as server configuration, resource constraints, extensions, or PostgreSQL logs that might shed light on the root cause of the server crashes you are experiencing. Please ensure you conduct any further testing in a non-production environment to prevent potential disruptions.

<@U0675Q5MTL5> here is what people report observing that crash:

```(lldb) bt

• thread #1, queue = 'com.apple.main-thread', stop reason = signal SIGABRT

  • frame #0: 0x000000018096a0dc libsystem_kernel.dylib__pthread_kill + 8 frame #1: 0x00000001809a1cc0 libsystem_pthread.dylibpthread_kill + 288 frame #2: 0x00000001808ada40 libsystem_c.dylibabort + 180 frame #3: 0x0000000100eac884 postgresExceptionalCondition(conditionName="start != NULL", errorType="FailedAssertion", fileName="partprune.c", lineNumber=2447) at assert.c:69:2 frame #4: 0x0000000100b63cf4 postgresget_steps_using_prefix_recurse(context=0x000000016f7c98e0, step_opstrategy=1, step_op_is_ne=false, step_lastexpr=0x000000015d80d780, step_lastcmpfn=448, step_lastkeyno=2, step_nullkeys=0x000000015d81a408, prefix=0x000000015d81a5a8, start=0x0000000000000000, step_exprs=0x000000015d81a600, step_cmpfns=0x000000015d81a658) at partprune.c:2447:2 frame #5: 0x0000000100b63f7c postgresget_steps_using_prefix_recurse(context=0x000000016f7c98e0, step_opstrategy=1, step_op_is_ne=false, step_lastexpr=0x000000015d80d780, step_lastcmpfn=448, step_lastkeyno=2, step_nullkeys=0x000000015d81a408, prefix=0x000000015d81a5a8, start=0x000000015d81a5c0, step_exprs=0x0000000000000000, step_cmpfns=0x0000000000000000) at partprune.c:2492:16 frame #6: 0x0000000100b63b98 postgresget_steps_using_prefix(context=0x000000016f7c98e0, step_opstrategy=1, step_op_is_ne=false, step_lastexpr=0x000000015d80d780, step_lastcmpfn=448, step_lastkeyno=2, step_nullkeys=0x000000015d81a408, prefix=0x000000015d81a5a8) at partprune.c:2402:9 frame #7: 0x0000000100b633f4 postgresgen_prune_steps_from_opexps(context=0x000000016f7c98e0, keyclauses=0x000000016f7c9728, nullkeys=0x000000015d81a408) at partprune.c:1729:8 frame #8: 0x0000000100b612b8 postgresgen_partprune_steps_internal(context=0x000000016f7c98e0, clauses=0x000000015d819d10) at partprune.c:1270:13 frame #9: 0x0000000100b5f7b4 postgresgen_partprune_steps(rel=0x000000015d80c958, clauses=0x000000015d819d10, target=PARTTARGET_PLANNER, context=0x000000016f7c98e0) at partprune.c:740:9 frame #10: 0x0000000100b5f5f4 postgresprune_append_rel_partitions(rel=0x000000015d80c958) at partprune.c:779:2 frame #11: 0x0000000100b2f004 postgresexpand_partitioned_rtentry(root=0x000000015d80ce08, relinfo=0x000000015d80c958, parentrte=0x000000015b8108a0, parentRTindex=1, parentrel=0x00000001500987f8, top_parentrc=0x0000000000000000, lockmode=1) at inherit.c:351:15 frame #12: 0x0000000100b2e734 postgresexpand_inherited_rtentry(root=0x000000015d80ce08, rel=0x000000015d80c958, rte=0x000000015b8108a0, rti=1) at inherit.c:148:3 frame #13: 0x0000000100af2160 postgresadd_other_rels_to_query(root=0x000000015d80ce08) at initsplan.c:163:4 frame #14: 0x0000000100af7fc4 postgresquery_planner(root=0x000000015d80ce08, qp_callback=(postgresstandard_qp_callback at planner.c:3064), qp_extra=0x000000016f7c9db0) at planmain.c:264:2 frame #15: 0x0000000100afb470 postgresgrouping_planner(root=0x000000015d80ce08, tuple_fraction=0) at planner.c:1442:17 frame #16: 0x0000000100af9da4 postgressubquery_planner(glob=0x000000015d80c608, parse=0x000000015b810788, parent_root=0x0000000000000000, hasRecursion=false, tuple_fraction=0) at planner.c:1019:2 frame #17: 0x0000000100af84bc postgresstandard_planner(parse=0x000000015b810788, query_string="SELECT \"my_table\".* FROM \"my_table\" WHERE \"my_table\".\"a\" IS NULL AND \"my_table\".\"b\" = 'foobar' AND \"my_table\".\"c\" = 'goobar';", cursorOptions=2048, boundParams=0x0000000000000000) at planner.c:402:9 frame #18: 0x0000000100af8218 postgresplanner(parse=0x000000015b810788, query_string="SELECT \"my_table\". FROM \"my_table\" WHERE \"my_table\".\"a\" IS NULL AND \"my_table\".\"b\" = 'foobar' AND \"my_table\".\"c\" = 'goobar';", cursorOptions=2048, boundParams=0x0000000000000000) at planner.c:271:12 frame #19: 0x0000000100c8b5d4 postgrespg_plan_query(querytree=0x000000015b810788, query_string="SELECT \"my_table\".* FROM \"my_table\" WHERE \"my_table\".\"a\" IS NULL AND \"my_table\".\"b\" = 'foobar' AND \"my_table\".\"c\" = 'goobar';", cursorOptions=2048, boundParams=0x0000000000000000) at postgres.c:846:9 frame #20: 0x0000000100c8b7d8 postgrespg_plan_queries(querytrees=0x000000015d80cdb0, query_string="SELECT \"my_table\". FROM \"my_table\" WHERE \"my_table\".\"a\" IS NULL AND \"my_table\".\"b\" = 'foobar' AND \"my_table\".\"c\" = 'goobar';", cursorOptions=2048, boundParams=0x0000000000000000) at postgres.c:938:11 frame #21: 0x0000000100c8edc0 postgresexec_simple_query(query_string="SELECT \"my_table\".* FROM \"my_table\" WHERE \"my_table\".\"a\" IS NULL AND \"my_table\".\"b\" = 'foobar' AND \"my_table\".\"c\" = 'goobar';") at postgres.c:1132:19 frame #22: 0x0000000100c8e080 postgresPostgresMain(argc=1, argv=0x000000016f7ca698, dbname="test", username="euko") at postgres.c:4513:7 frame #23: 0x0000000100b84c00 postgresBackendRun(port=0x000000015c8048c0) at postmaster.c:4537:2 frame #24: 0x0000000100b84020 postgresBackendStartup(port=0x000000015c8048c0) at postmaster.c:4259:3 frame #25: 0x0000000100b82b64 postgresServerLoop at postmaster.c:1745:7 frame #26: 0x0000000100b7fcd8 postgresPostmasterMain(argc=3, argv=0x0000600003bb53e0) at postmaster.c:1417:11 frame #27: 0x0000000100a46288 postgresmain(argc=3, argv=0x0000600003bb53e0) at main.c:209:3 frame #28: 0x00000001806210e0 dyldstart + 2360any ideas?

The stack trace provided suggests a failed assertion in the partprune.c source file, which is part of PostgreSQL's partition pruning logic. The assertion that failed checks whether the start variable is not NULL in the get_steps_using_prefix_recurse function. It's indeed a bug that led to the crash due to a null pointer dereference.

As the crash is happening within PostgreSQL's internal logic, it needs to be reported and addressed by the PostgreSQL development team. In the meantime, the following steps can be taken:

1. Check if there are any related issues already reported, possibly in PostgreSQL's bug tracker or mailing lists.

2. If this is a new issue, create a detailed bug report providing the stack trace, the version of PostgreSQL where the issue is observed, and a way to reproduce the problem.

3. As a temporary workaround, you can try restructuring the table partitioning strategy, revisiting the queries causing the crash, or, if possible, upgrading to the latest PostgreSQL version where the bug might have been already fixed.

Please test any workaround in a non-production environment before applying to production and monitor PostgreSQL's release notes for any fix related to this bug.

Relevant code can be found in the PostgreSQL GitHub repository. Here are example source files related to partition pruning from various PostgreSQL versions where similar logic could exist:

• PostgreSQL 16:

• PostgreSQL 15:

• PostgreSQL 14:

• PostgreSQL 13:

Check these references and the related official documentation for any notes on the specific assertion failure you are encountering.

they say it's crashing on 14.9

maybe this:

Fix partition step generation and runtime partition pruning for hash-partitioned tables with multiple partition keys (David Rowley)

Some cases involving an IS NULL condition on one of the partition keys could result in a crash.

wdyt?

<@U0675Q5MTL5>