cascading lock issue

[James Pang <jamespang886@xxxxxxxxx>]

Hi,

   below is a cascading lock and blocking issue,  pid(4490 holding RowExclusiveLock and blocking pid(4732) on "alter table", when pid(4732) waiting, new coming select on the table got blocked too. since only RowExclusiveLock granted on relation, why the query select got blocked too ? just because there is another "alter table" that's sitting in the queue before this "select session" ?  is it expected in PGv14? 

  details as below:  

pid=4490
    test=# begin;
    BEGIN
    stest=*# update test1 set relname=relname||'test' where oid<1000;
    UPDATE 27

pid=4732
    alter table test1 alter column relkind type char(10);

pid=5151
    select count(*) from test1;

# select pid,query,state,wait_event,wait_event_type from pg_stat_activity;
  pid      query                                                    state                  wait_event          wait_event_type   backend_xid
 4490 | update test1 set relname=relname||'test' where oid<1000;  | idle in transaction | ClientRead           | Client           | 1053128912
 4732 | alter table test1 alter column relkind type char(10);     | active              | relation             | Lock             | 1053128921
 5151 | select count(*) from test1;                               | active              | relation             | Lock             |
 

2024-03-27 11:05:53.239 UTC:[local]:postgres@test:[4732]:[4-1]:psqlLOG:  statement: alter table test1 alter column relkind type char(10);
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[5-1]:psqlLOG:  process 4732 still waiting for AccessExclusiveLock on relation 46869 of database 16446 after 1000.037 ms
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[6-1]:psqlDETAIL:  Process holding the lock: 4490. Wait queue: 4732.
2024-03-27 11:05:54.240 UTC:[local]:postgres@test:[4732]:[7-1]:psqlSTATEMENT:  alter table test1 alter column relkind type char(10);
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[4-1]:psqlLOG:  process 5151 still waiting for AccessShareLock on relation 46869 of database 16446 after 1000.032 ms
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[5-1]:psqlDETAIL:  Process holding the lock: 4490. Wait queue: 4732, 5151.
2024-03-27 11:06:25.278 UTC:[local]:postgres@test:[5151]:[6-1]:psqlSTATEMENT:  select count(*) from test1;

-[ RECORD 4 ]------+-----------------
locktype           | relation
database           | 16446
relation           | 46869
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 4/7
pid                | 4490
mode               | RowExclusiveLock
granted            | t
fastpath           | f
waitstart          |


-[ RECORD 2 ]------+------------------------------
locktype           | relation
database           | 16446
relation           | 46869
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 5/52
pid                | 4732
mode               | AccessExclusiveLock
granted            | f
fastpath           | f
waitstart          | 2024-03-27 11:05:53.240797+00
-[ RECORD 3 ]------+------------------------------
locktype           | transactionid
database           |
relation           |
page               |
tuple              |
virtualxid         |
transactionid      | 1053128921
classid            |
objid              |
objsubid           |
virtualtransaction | 5/52
pid                | 4732
mode               | ExclusiveLock
granted            | t
fastpath           | f
waitstart          |


-[ RECORD 2 ]------+------------------------------
locktype           | relation
database           | 16446
relation           | 46869
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 6/90
pid                | 5151
mode               | AccessShareLock
granted            | f
fastpath           | f
waitstart          | 2024-03-27 11:06:24.278703+00

Thanks,

James