Server crashes due to the lack of memory

[laurenz]

This was raised on the pgsql-bugs list:
https://www.postgresql.org/message-id/flat/18308-f55e8ec2ee192cb5%40postgresql.org

There seem to be crashes with PostgreSQL 15.4 and pg_shao_plans with the query

select tc.table_schema, tc.table_name, 
       tc.constraint_type,tc.constraint_name, tc.is_deferrable,
       tc2.table_name as child_table,
       rc.unique_constraint_name as refer_key,
       case when rc.unique_constraint_name is not null then
            (select tc1.table_name 
               from information_schema.table_constraints tc1
              where tc1.constraint_name = rc.unique_constraint_name)
       end as ref_table,
       cc.check_clause,
       kc.column_name,
       kc.ordinal_position
  from information_schema.table_constraints tc
  left join information_schema.referential_constraints rc
    on tc.constraint_name = rc.constraint_name
  left join information_schema.check_constraints cc
    on tc.constraint_name = cc.constraint_name
  left join information_schema.key_column_usage kc
    on tc.constraint_name = kc.constraint_name
  left join LATERAL (
       select tc2.table_name, rc1.unique_constraint_name 
              from information_schema.referential_constraints rc1
              join information_schema.table_constraints tc2
                on rc1.constraint_name = tc2.constraint_name 
             where tc.constraint_name = rc1.unique_constraint_name
             ) tc2 on true
 where tc.table_name = 't1';

I didn't try to reproduce the error.

[japinli]

It seems space_left is less then zero, which leads later memcpy crash. Here we have no space for the plan. Maybe we should skip the plan if there has no space left.

diff --git a/pg_show_plans.c b/pg_show_plans.c
index 3eb2d3c..d560f71 100644
--- a/pg_show_plans.c
+++ b/pg_show_plans.c
@@ -283,6 +283,10 @@ append_query_plan(ExplainState *es)
 		offset += pgsp_cache->plan_len[i] + 1;
 	space_left = max_plan_length - offset;

+	/* skip the query plan if no space left */
+	if (space_left <= 0)
+		return;
+
 	if (pgsp->plan_format == EXPLAIN_FORMAT_TEXT)
 		new_plan->len--; /* Discard '\n'. */

[kovmir]

Thanks @japinli! Even if this code section is not the cause of the crashes, it is still error prone.

[kovmir]

@laurenz, I cannot reproduce that. What about you?

[laurenz]

I didn't try... did you try creating a table t1 with some constraints?

If you cannot reproduce it, just close it as not reproducible. Sorry for creating work for you.

[kovmir]

did you try creating a table t1 with some constraints?

Yes, I did. I followed all the steps specified on the mailing lists thread you had linked.

[kovmir]

@japinli, can you please try out the code from this branch and see whether it fixes your problem?

[japinli]

On Fri, 26 Jan 2024 at 22:47, Ivan Kovmir ***@***.***> wrote: @japinli, can you please try out the code from [this](https://github.com/cybertec-postgresql/pg_show_plans/tree/fix-server-crash) branch and see whether it fixes your problem?

Here is the output of `fix-server-crash` branch: ``` [local]:2161732 postgres=# select tc.table_schema, tc.table_name, tc.constraint_type,tc.constraint_name, tc.is_deferrable, tc2.table_name as child_table, rc.unique_constraint_name as refer_key, case when rc.unique_constraint_name is not null then (select tc1.table_name from information_schema.table_constraints tc1 where tc1.constraint_name = rc.unique_constraint_name) end as ref_table, cc.check_clause, kc.column_name, kc.ordinal_position from information_schema.table_constraints tc left join information_schema.referential_constraints rc on tc.constraint_name = rc.constraint_name left join information_schema.check_constraints cc on tc.constraint_name = cc.constraint_name left join information_schema.key_column_usage kc on tc.constraint_name = kc.constraint_name left join LATERAL ( select tc2.table_name, rc1.unique_constraint_name from information_schema.referential_constraints rc1 join information_schema.table_constraints tc2 on rc1.constraint_name = tc2.constraint_name where tc.constraint_name = rc1.unique_constraint_name ) tc2 on true where tc.table_name = 't1'; WARNING: not enough memory to append new query plans table_schema | table_name | constraint_type | constraint_name | is_deferrable | child_table | refer_key | ref_table | check_clause | column_name | ordinal_position --------------+------------+-----------------+-----------------+---------------+-------------+-----------+-----------+--------------+-------------+------------------ (0 rows) ```

[japinli]

@japinli, can you please try out the code from this branch and see whether it fixes your problem?

Thanks for working on this. It seems fix the crash.

After reading the patch, now it must hold all of the new plan, not partial, right? Is that expected?

[kovmir]

Prior to this patch the extension was supposed to snip the part of a plan that does not fit and store the leftover. With this patch applied, the extension ignores the plan that does not fit and emits a warning instead.

[kovmir]

Fixed in 572fa77.