Re: Postgres 15 SELECT query doesn't use index under RLS

[Alexander Okulovich]

Hi Tom,


Can you force it in either direction with "set enable_seqscan = off"
(resp. "set enable_indexscan = off")?  If so, how do the estimated
costs compare for the two plan shapes?

Here are the results from the prod instance:

seqscan off 

indexscan_off 

Just noticed that the WHEN clause differs from the initial one (392 ids 
under RLS). Probably, this is why the execution time isn't so 
catastrophic. Please let me know if this matters, and I'll rerun this 
with the initial request.


Speaking of the stage vs local Docker Postgres instance, the execution 
time on stage is so short (0.1 ms with seq scan, 0.195 with index scan) 
that we probably should not consider them. But I'll execute the requests 
if it's necessary.



Maybe your prod installation has a bloated index, and that's driving
up the estimated cost enough to steer the planner away from it.
We tried to make REINDEX CONCURRENTLY on a prod copy, but the planner 
still used Seq Scan instead of Index Scan afterward.


Kind regards,

Alexander

Postgres Locking

[Dirschel, Steve]

Relatively new to Postgres.  Running into a locking situation and I need to 
make sure I understand output.  I found this query to show a lock tree:

wldomart01a=> WITH
wldomart01a->   RECURSIVE l AS (
wldomart01a(>   SELECT pid, locktype, mode, granted,
wldomart01a(>  
ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid)
 obj
wldomart01a(> FROM pg_locks),
wldomart01a->   pairs AS (
wldomart01a(>   SELECT w.pid waiter, l.pid locker, l.obj, l.mode
wldomart01a(> FROM l w
wldomart01a(> JOIN l
wldomart01a(>   ON l.obj IS NOT DISTINCT FROM w.obj
wldomart01a(>  AND l.locktype=w.locktype
wldomart01a(>  AND NOT l.pid=w.pid
wldomart01a(>  AND l.granted
wldomart01a(>WHERE NOT w.granted),
wldomart01a->   tree AS (
wldomart01a(>   SELECT l.locker pid, l.locker root, 
NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) 
OVER () all_pids
wldomart01a(> FROM ( SELECT DISTINCT locker FROM pairs l 
WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l
wldomart01a(>UNION ALL
wldomart01a(>   SELECT w.waiter pid, tree.root, w.obj, w.mode, 
tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()
wldomart01a(> FROM tree
wldomart01a(> JOIN pairs w
wldomart01a(>   ON tree.pid=w.locker
wldomart01a(>  AND NOT w.waiter = ANY ( all_pids ))
wldomart01a->SELECT
wldomart01a->   path, repeat(' .', lvl)||' '|| tree.pid as 
pid_tree, tree.pid,
wldomart01a->   (clock_timestamp() - a.xact_start)::interval(3) 
AS ts_age,
wldomart01a->   replace(a.state, 'idle in transaction', 
'idletx') state,
wldomart01a->   wait_event_type wait_type,
wldomart01a->   wait_event,
wldomart01a->   (clock_timestamp() - state_change)::interval(3) 
AS change_age,
wldomart01a->   lvl,
wldomart01a->   (SELECT count(*) FROM tree p WHERE p.path ~ 
('^'||tree.path) AND NOT p.path=tree.path) blocked,
wldomart01a->   repeat(' .', lvl)||' '||left(query,100) query
wldomart01a-> FROM tree
wldomart01a->   JOIN pg_stat_activity a
wldomart01a->USING (pid)
wldomart01a->  ORDER BY path;
   path| pid_tree | pid  |ts_age| state  | wait_type |  wait_event  
 |  change_age  | lvl | blocked |   query
---+--+--+--++---+---+--+-+-+
3740  |  3740| 3740 | 01:23:03.294 | idletx | Client| ClientRead
| 00:00:00.004 |   0 |   1 |  update "wln_mart"."ee_fact" set  +
   |  |  |  ||   |  
 |  | | | "changed_on" = $1 +
   |  |  |  ||   |  
 |  | | | where "ee_fact_id" = $2
3740.3707 |  . 3707  | 3707 | 01:23:03.294 | active | Lock  | transactionid 
| 01:23:03.29  |   1 |   0 |  . update "wln_mart"."ee_fact" set+
   |  |  |  ||   |  
 |  | | | "changed_on" = $1 +
   |  |  |  ||   |  
 |  | | | where "ee_fact_id" = $2
(2 rows)

Above I can see PID 3740 is blocking PID 3707.   The PK on table 
wln_mart.ee_fact is ee_fact_id.  I assume PID 3740 has updated a row (but not 
committed it yet) that PID 3707 is also trying to update.  But I am being told 
those 2 sessions should not be trying to process the same PK rows.

Here is output from pg_locks for those 2 sessions:

wldomart01a=> select * from pg_locks where pid in (3740,3707) order by pid;
   locktype| database | relation | page | tuple | virtualxid | 
transactionid | classid | objid | objsubid | virtualtransaction | pid  |   
mode   | granted | fastpath |   waitstart
---+--+--+--+---++---+-+---+--++--+--+-+--+---
transactionid |  |  |  |   || 251189989 
| |   |  | 54/196626  | 3707 | ExclusiveLock| t 
  | f|
relation  |91999 |94619 |  |   ||   
| |   |  | 54/196626  | 3707 | RowExclusiveLock | t 
  | t|
relation  |91999 |94615 |  |   ||   
| |   |

Re: Postgres Locking

[Tom Lane]

"Dirschel, Steve"  writes:
> Above I can see PID 3740 is blocking PID 3707.  The PK on table
> wln_mart.ee_fact is ee_fact_id.  I assume PID 3740 has updated a row
> (but not committed it yet) that PID 3707 is also trying to update.

Hmm. We can see that 3707 is waiting for 3740 to commit, because it's
trying to take ShareLock on 3740's transactionid:

> transactionid |  |  |  |   || 
> 251189986 | |   |  | 54/196626  | 3707 | 
> ShareLock| f   | f| 2023-10-31 14:40:21.837507-05

251189986 is indeed 3740's, because it has ExclusiveLock on that:

> transactionid |  |  |  |   || 
> 251189986 | |   |  | 60/259887  | 3740 | 
> ExclusiveLock| t   | f|

There are many reasons why one xact might be waiting on another to commit,
not only that they tried to update the same tuple.  However, in this case
I suspect that that is the problem, because we can also see that 3707 has
an exclusive tuple-level lock:

> tuple |91999 |93050 |0 | 1 || 
>   | |   |  | 54/196626  | 3707 | ExclusiveLock
> | t   | f|

That kind of lock would only be held while queueing to modify a tuple.
(Basically, it establishes that 3707 is next in line, in case some
other transaction comes along and also wants to modify the same tuple.)
It should be released as soon as the tuple update is made, so 3707 is
definitely stuck waiting to modify a tuple, and AFAICS it must be stuck
because of 3740's uncommitted earlier update.

> But I am being told those 2 sessions should not be trying to process the
> same PK rows.

Perhaps "should not" is wrong.  Or it could be some indirect update
(caused by a foreign key with CASCADE, or the like).

You have here the relation OID (try "SELECT 93050::regclass" to
decode it) and the tuple ID, so it should work to do

SELECT * FROM that_table WHERE ctid = '(0,1)';

to see the previous state of the problematic tuple.  Might
help to decipher the problem.

regards, tom lane