PostgreSQL crashed when executing SQL including IndexScan hint for non-btree indexes of a partitioned table

[tideri]

Hi! At one of my customers, when SQL was executed with IndexScan hint for non-btree indexes (such as the gin index) against a partitioned table, the backend process crashed and a core dump was output. (Actually, I found it when pg_bigm was used.)

postgres(1863)@[local]:5432=# select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

The details are described below, but it seems that pg_hint_plan does not take into consideration indexes that do not support order by such as GIN indexes (indexes other than btree indexes).

This issue appears to occur in all currently released branches of pg_hint_plan. I have confirmed that this phenomenon occurs in the following versions.

• PostgreSQL 11.17 & pg_hint_plan 11_1_3_7
• PostgreSQL 12.10 & pg_hint_plan 12_1_3_7
• PostgreSQL 13.8 & pg_hint_plan 13_1_3_7

How to reproduce this issue

You can reproduce this event with the following SQL. Also, as I wrote in [Occurrence condition], I confirmed that this isse occured with indexes other than btree, such as HASH indexes.

LOAD 'pg_hint_plan';
CREATE TABLE p (tool text, description tsvector) PARTITION BY HASH(tool);
CREATE TABLE p_d1 PARTITION OF p FOR VALUES WITH(MODULUS 7,REMAINDER 1);
CREATE INDEX p_idx ON p USING gin (description);
select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';

Occurrence condition

In my observation, this issue can be reproduced following condition.

1. Creating a partitioned table by declarative partitioning.
2. One of the following types of index is created on one of the columns of the 1)'s partitioned table
    - HASH
    - GIN
    - GiST
    - SP-GiST
    - BRIN
3. Load pg_hint_plan
4. Execute SQL including IndexScan(1's partition table 2's index) 

Cause & backtrace

Below is the backtrace for the combination of PostgreSQL 13.8 and pg_hint_plan 13_1_3_7.

The segmentation fault seems to be caused by info->reserver_sort[i] being NULL in restrict_indexes(). Also info->nulls_first[i] is NULL. This is because the IndexOptInfo type reverse_sort and nulls_first seem to be set to NULL except for btree indexes in PostgreSQL. It seems that restrict_indexes() does not consider anything other than btree indexes.

I think we need to check if info->amcanorderbyop is true or false and if (info->amcanorderbyop == false) , we need to skip checking info->reserver_sort[i] and info->nulls_first[i].

Core was generated by `postgres: postgres postgres [local] SELE'.
Program terminated with signal 11, Segmentation fault.
#0  0x00007f1916d57f30 in restrict_indexes (root=0x13c64b8, hint=0x1306dd8, rel=0x13d2eb0, using_parent_hint=true) at pg_hint_plan.c:3536
3536                                                    != info->reverse_sort[i] ||
Missing separate debuginfos, use: debuginfo-install glibc-2.17-196.el7.x86_64
(gdb) bt
#0  0x00007f1916d57f30 in restrict_indexes (root=0x13c64b8, hint=0x1306dd8, rel=0x13d2eb0, using_parent_hint=true) at pg_hint_plan.c:3536
#1  0x00007f1916d58d76 in setup_hint_enforcement (root=0x13c64b8, rel=0x13d2eb0, rshint=0x0, rphint=0x7ffd6492bac8) at pg_hint_plan.c:3948
#2  0x00007f1916d5aa2f in pg_hint_plan_set_rel_pathlist (root=0x13c64b8, rel=0x13d2eb0, rti=2, rte=0x13d2a18) at pg_hint_plan.c:4758
#3  0x00000000007867da in set_rel_pathlist (root=0x13c64b8, rel=0x13d2eb0, rti=2, rte=0x13d2a18) at allpaths.c:540
#4  0x0000000000787532 in set_append_rel_pathlist (root=0x13c64b8, rel=0x1306858, rti=1, rte=0x13066f8) at allpaths.c:1260
#5  0x000000000078669e in set_rel_pathlist (root=0x13c64b8, rel=0x1306858, rti=1, rte=0x13066f8) at allpaths.c:480
#6  0x0000000000786408 in set_base_rel_pathlists (root=0x13c64b8) at allpaths.c:352
#7  0x00000000007861f5 in make_one_rel (root=0x13c64b8, joinlist=0x13d2808) at allpaths.c:222
#8  0x00000000007bcb4c in query_planner (root=0x13c64b8, qp_callback=0x7c2ebe <standard_qp_callback>, qp_extra=0x7ffd6492bde0) at planmain.c:269
#9  0x00000000007c0091 in grouping_planner (root=0x13c64b8, inheritance_update=false, tuple_fraction=0) at planner.c:2059
#10 0x00000000007be5c2 in subquery_planner (glob=0x1306a68, parse=0x13065e8, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at planner.c:1015
#11 0x00000000007bcff1 in standard_planner (parse=0x13065e8, 
    query_string=0x1305528 "select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';", cursorOptions=256, boundParams=0x0) at planner.c:405
#12 0x00007f1916d574a0 in pg_hint_plan_planner (parse=0x13065e8, 
    query_string=0x1305528 "select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';", cursorOptions=256, boundParams=0x0)
    at pg_hint_plan.c:3176
#13 0x00000000007bcd76 in planner (parse=0x13065e8, query_string=0x1305528 "select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';", 
    cursorOptions=256, boundParams=0x0) at planner.c:273
#14 0x00000000008c09d3 in pg_plan_query (querytree=0x13065e8, 
    query_string=0x1305528 "select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';", cursorOptions=256, boundParams=0x0) at postgres.c:874
#15 0x00000000008c0afe in pg_plan_queries (querytrees=0x13c6438, 
    query_string=0x1305528 "select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';", cursorOptions=256, boundParams=0x0) at postgres.c:965
#16 0x00000000008c0e41 in exec_simple_query (query_string=0x1305528 "select /*+ IndexScan(p p_idx) */ * from p where  description = '%hoge%';")
    at postgres.c:1157
#17 0x00000000008c4df1 in PostgresMain (argc=1, argv=0x1331ef8, dbname=0x1331e08 "postgres", username=0x1331de8 "postgres") at postgres.c:4347
#18 0x0000000000825b4f in BackendRun (port=0x1329de0) at postmaster.c:4550
#19 0x0000000000825360 in BackendStartup (port=0x1329de0) at postmaster.c:4234
#20 0x0000000000821c00 in ServerLoop () at postmaster.c:1739
#21 0x00000000008214e1 in PostmasterMain (argc=3, argv=0x1300030) at postmaster.c:1412
#22 0x00000000007370c0 in main (argc=3, argv=0x1300030) at main.c:210

(gdb) p info->reverse_sort[0]
Cannot access memory at address 0x0
(gdb) p info->nulls_first[0] 
Cannot access memory at address 0x0

[tideri]

I found the workaround for this issue. Instead of writing a hint clause for the partition table (parent table), write the hint clause by enumerating the partitions (child table) and the indexes of the partitions by the number of partitions like following:

/*+ IndexScan(child table 1 child index 1) IndexScan(child table 2 child index 2) IndexScan(child table 3 child index 3) */

[horiguti]

Sorry for being late. This is fixed in pg_hint_plan 15. It will be back-patched soon.

[ayaiwt]

Hi This back-patch does not seem to be supported yet. Do you have any plans to deal with it?

[jjune235]

hi. Is it possible to patch this bug, at least for version 13?