Will tables partitioned using inheritance always lock all partitions with AccessShareLocks?

Will tables partitioned with inheritance based partitioning always take AccessShareLock locks on all child tables, even when constraints would exclude those partitions?

I'm currently trying to reduce the number of AccessShareLocks taken when querying an inheritance based partitioned table. We've been seeing a large amount of waits on LWLock:LockManager on this table, so we are attempting to reduce the number of AccessShareLocks taken by the most frequent queries on this table. The table uses inheritance based partitioning and CHECK constraints. The parent table items is where rows are inserted, and later archived to finished_items when in an appropriate state.

The table structure looks like:

CREATE TABLE items (
    id bigint NOT NULL,
    state character varying(255) DEFAULT 'pending'::character varying NOT NULL,
    deleted_at boolean,
    CONSTRAINT state_valid CHECK (((state)::text = ANY (ARRAY[('accepted'::character varying)::text, ('assigned'::character varying)::text, ('blocked'::character varying)::text, ('broken'::character varying)::text, ('canceled'::character varying)::text, ('canceling'::character varying)::text, ('finished'::character varying)::text, ('limited'::character varying)::text, ('expired'::character varying)::text]))) 
);

CREATE INDEX ON items(state);

CREATE TABLE finished_items (
    id bigint NOT NULL,
    state character varying(255) DEFAULT 'pending'::character varying NOT NULL,
    deleted_at boolean,
    CONSTRAINT state_is_finished CHECK (((state)::text = ANY (ARRAY[('finished'::character varying)::text, ('broken'::character varying)::text, ('expired'::character varying)::text, ('canceled'::character varying)::text, ('timed_out'::character varying)::text])))
) INHERITS (items);

CREATE TABLE deleted_items (
    id bigint NOT NULL,
    state character varying(255) DEFAULT 'pending'::character varying NOT NULL,
    deleted_at boolean,
    CONSTRAINT is_deleted CHECK ((deleted_at IS NOT NULL)) 
) INHERITS (items);

Notably fi

Yes, it will lock all the tables. The system can't know the constraint rules out the partition until the constraint is inspected, and with inheritance-based partitioning a lock is needed to do that inspection.

StackExchange Database Administrators Q#318615, answer score: 4