Help understanding SIReadLock growing without bound on completedtransaction - Mailing list pgsql-general

mode: SIReadLock              

1. It's my understanding that these locks should be cleared when there are no conflicting transactions.  These locks had existed for > 1 week and we have no transactions that last more than a few seconds (the oldest transaction in pg_stat_activity is always < 1minute old).
   
2. Why would a transaction that is finished continue accumulating locks over time?  
   

1. There were a total of six pids in pg_locks that didn't exist in pg_stat_activity.  They held a variety of SIReadLocks, but they weren't increasing in number over time.  I'm not sure how long they were present; I only know that the problematic pid existed for a week due to its continual growth reflecting in our internal lock monitoring system.
   
2. I tried finding overlapping SIReadLocks (see query below), but none were returned (I realize that the SSI conflict resolution algo is much more involved than this simple query)
   
3. PG version: 9.6.17
   

SELECT 

    waiting.locktype           AS w_locktype,

    LEFT(waiting.relation::regclass::text,25) AS waiting_table,

    COALESCE(waiting_stm.query,'?')          AS w_query,

    waiting.page AS w_page,

    waiting.tuple AS w_tuple,

    waiting.pid                AS w_pid,

    other.locktype             AS o_locktype,

    LEFT(other.relation::regclass::text,15)   AS other_table,

    LEFT(COALESCE(other_stm.query, '?'), 50)            AS other_query,

    other.page AS o_page,

    other.tuple AS o_tuple, 

    other.pid                  AS other_pid,

    other.GRANTED              AS o_granted

FROM

    pg_catalog.pg_locks AS waiting

LEFT JOIN

    pg_catalog.pg_stat_activity AS waiting_stm

    ON        waiting_stm.pid = waiting.pid

JOIN

    pg_catalog.pg_locks AS other

    ON (

        (

            waiting."database" = other."database"

        AND waiting.relation  = other.relation

        and waiting.locktype = other.locktype

        AND ( CASE WHEN other.locktype = 'page' THEN waiting.page IS NOT DISTINCT FROM other.page

           WHEN other.locktype = 'tuple' THEN waiting.page IS NOT DISTINCT FROM other.page and waiting.tuple IS NOT DISTINCT FROM other.tuple

           ELSE true END

        )

        )

        OR waiting.transactionid = other.transactionid

    ) AND waiting.pid <> other.pid

LEFT JOIN

    pg_catalog.pg_stat_activity AS other_stm

    ON        other_stm.pid = other.pid

WHERE waiting.pid IN (2263461, 2263276, 2263283, 2263284, 2263459, 2263527 )