BUG #18011: Declarative partition privilege problem cause incorrect execution plans - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18011
Logged by:          zhilong liu
Email address:      liuzhilong62@outlook.com
PostgreSQL version: 13.1
Operating system:   linux 7
Description:

Firstly,I'm sorry that there are no reproduction issues.(I tried it, but it
didn't work)
So,I'll describe the production problem directly below.
sql:
 update  TABLE_RECORD
                        set IS_DELETED = '1', DATE_UPDATED =
LOCALTIMESTAMP(0)
                        WHERE APPL_NO = $1
                        AND IS_DELETED = '0'
                        AND DATE_CREATED > now() - interval '31' day
                        AND DATE_CREATED < now()

partition table definition：
# \d+ TABLE_RECORD

Partitioned table "public.TABLE_RECORD"
             Column             |            Type             | Collation |
Nullable |                      Default                      | Storage  |
Stats target |       Description        

--------------------------------+-----------------------------+-----------+----------+---------------------------------------------------+----------+--------------+--------------------------
 id_TABLE_RECORD    | character varying(32)       |           | not null |
nextval('seq_TABLE_RECORD'::regclass) | extended |              | 
 appl_no                        | character varying(100)      |           |
        |                                                   | extended |
         | 
 reject_appl_no                 | character varying(100)      |           |
        |                                                   | extended |
         | 
...
 created_by                     | character varying(100)      |           |
not null | 'sys'::character varying                          | extended |
          | 
 date_created                   | timestamp without time zone |           |
not null | now()                                             | plain    |
          | 
 updated_by                     | character varying(100)      |           |
not null | 'sys'::character varying                          | extended |
          | 
 date_updated                   | timestamp without time zone |           |
not null | now()                                             | plain    |
          | 
Partition key: RANGE (date_created)
Indexes:
    "date_TABLE_RECORD" btree (date_created)
    "date_updated_reject_reason_record" btree (date_updated)
    "idx_rms_reject_reason" btree (appl_no, is_deleted)
    "nk_TABLE_RECORD" btree (appl_no)
Partitions: TABLE_RECORD_202211 FOR VALUES FROM ('2022-11-01 00:00:00') TO
('2022-12-01 00:00:00'),
...
            TABLE_RECORD_202303 FOR VALUES FROM ('2023-03-01 00:00:00') TO
('2023-04-01 00:00:00'),
            TABLE_RECORD_202304 FOR VALUES FROM ('2023-04-01 00:00:00') TO
('2023-05-01 00:00:00'),
            TABLE_RECORD_202305 FOR VALUES FROM ('2023-05-01 00:00:00') TO
('2023-06-01 00:00:00'),
            TABLE_RECORD_202306 FOR VALUES FROM ('2023-06-01 00:00:00') TO
('2023-07-01 00:00:00'),
 ...
            TABLE_RECORD_202512 FOR VALUES FROM ('2025-12-01 00:00:00') TO
('2026-01-01 00:00:00'),
            TABLE_RECORD_other DEFAULT

Data distribution：
 select count(*),tableoid::regclass from TABLE_RECORD group by 2;
 count |            tableoid             
-------+---------------------------------
  6929 | TABLE_RECORD_202306
   945 | TABLE_RECORD_202305
  1413 | TABLE_RECORD_202304
...


Log output：
Query Text: update  TABLE_RECORD
                        set IS_DELETED = '1', DATE_UPDATED =
LOCALTIMESTAMP(0)
                        WHERE APPL_NO = $1
                        AND IS_DELETED = '0'
                        AND DATE_CREATED > now() - interval '31' day
                        AND DATE_CREATED < now()
...
Update on TABLE_RECORD  (cost=0.14..203.79 rows=39 width=2960)
  Update on TABLE_RECORD_202211 TABLE_RECORD_1
 ... 
  ->  Index Scan using TABLE_RECORD_202304_date_created_idx on
TABLE_RECORD_202304 TABLE_RECORD_6  (cost=0.44..5.47 rows=1 width=481)
        Index Cond: ((date_created > (now() - '31 days'::interval day)) AND
(date_created < now()))
        Filter: (((appl_no)::text = 'WDSOT2306297210457'::text) AND
((is_deleted)::text = '0'::text))
  ->  Index Scan using TABLE_RECORD_202305_date_created_idx on
TABLE_RECORD_202305 TABLE_RECORD_7  (cost=0.44..5.47 rows=1 width=483)
        Index Cond: ((date_created > (now() - '31 days'::interval day)) AND
(date_created < now()))
        Filter: (((appl_no)::text = 'WDSOT2306297210457'::text) AND
((is_deleted)::text = '0'::text))
  ->  Index Scan using TABLE_RECORD_202306_date_created_idx on
TABLE_RECORD_202306 TABLE_RECORD_8  (cost=0.44..5.47 rows=1 width=485)
        Index Cond: ((date_created > (now() - '31 days'::interval day)) AND
(date_created < now()))
        Filter: (((appl_no)::text = 'WDSOT2306297210457'::text) AND
((is_deleted)::text = '0'::text))
...


The planner(or executor) should trim partitions —just access partitions of
202305 and 202306,it does.And the executor should use the
idx_rms_reject_reason(appl_no, is_deleted)  index,it doesn't.Indexes on
DATE_CREATED are used


We collected statistics and killed sessions because of plan cache,but that
didn't work.
Then,We found that the user does not have permissions on the partitions.So
we did some authorization operations:
grant select,update,delete,insert  on TABLE_RECORD_202305 to appuser1;
grant select,update,delete,insert  on TABLE_RECORD_202306 to appuser1;

explain the sql again,it's correct!
   ->  Index Scan using TABLE_RECORD_202304_date_created_idx on
TABLE_RECORD_202304 TABLE_RECORD_6  (cost=0.44..5.47 rows=1 width=481)
         Index Cond: ((date_created > (now() - '31 days'::interval day)) AND
(date_created < now()))
         Filter: (((appl_no)::text = 'WNCOT2304255342156'::text) AND
((is_deleted)::text = '0'::text))
   ->  Index Scan using idx_rms_reject_reason_25 on TABLE_RECORD_202305
TABLE_RECORD_7  (cost=0.43..30.39 rows=1 width=483)
         Index Cond: (((appl_no)::text = 'WNCOT2304255342156'::text) AND
((is_deleted)::text = '0'::text))
         Filter: ((date_created < now()) AND (date_created > (now() - '31
days'::interval day)))
   ->  Index Scan using idx_rms_reject_reason_14 on TABLE_RECORD_202306
TABLE_RECORD_8  (cost=0.56..42.57 rows=17 width=485)
         Index Cond: (((appl_no)::text = 'WNCOT2304255342156'::text) AND
((is_deleted)::text = '0'::text))
         Filter: ((date_created < now()) AND (date_created > (now() - '31
days'::interval day)))

This rarely happens (we have many pgsql databases, but basically this
privilege problem occurs once a year), and it is very difficult to locate
and reproduce.