Обсуждение: BUG #18011: Declarative partition privilege problem cause incorrect execution plans

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.

On Sun, 2 Jul 2023 at 03:17, PG Bug reporting form
<noreply@postgresql.org> wrote:
>   ->  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()))

> The planner(or executor) should trim partitions —just access partitions of
> 202305 and 202306,it does.

Going by what you've shown above, it does *not* prune that partition.
There's a note in the documents [1] about execution time partition
pruning not working for modify table:

"Execution-time partition pruning currently only occurs for the Append
and MergeAppend node types. It is not yet implemented for the
ModifyTable node type, but that is likely to be changed in a future
release of PostgreSQL."

> And the executor should use the
> idx_rms_reject_reason(appl_no, is_deleted)  index,it doesn't.Indexes on
> DATE_CREATED are used

When is the last time the statistics were updated on
TABLE_RECORD_202306? Does a manual ANALYZE on that table cause the
plan to change?

> 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;

When you query a partitioned table, the permissions of each partition
are not checked. Effectively these inherit from the partitioned table.
If you want to access the partitions directly from appuser1, then
you'll need to grant permissions.

David

[1] https://www.postgresql.org/docs/13/ddl-partitioning.html#DDL-PARTITION-PRUNING