[YSQL][LST] Race condition of ERROR: Not found: The object '00004777000030008000000000000000.mv1' does not exist: OBJECT_NOT_FOUND if a query is still using the old mat_view object which is now invalid owing to the refresh mat view operation being execut

[kripasreenivasan]

Jira Link: DB-5146

Description

• Create a base table
• Create a materialized view of the table
• Start thread 1 to execute varioaus select operations on the mat view
• Have a thread 2 insert data to the base table
• Start thread 3 to refresh the materialized view All three threads must execute in parallel. Issue: The select operation encounters a race condition and hit this error since the select is using the old mat view object which is non-existent owing to the refresh operation. This is very easily reproducible with LST

Execute

./long_system_test.py --threads=10 --runtime=0 --complexity=average --max-columns=10 --runtime=3

on LST code containing materialized views.

2023-01-23 10:23:56,602 worker_9   ERROR    Unexpected query failure: InternalError_
Query: SELECT count(mv1.c1_jsonb) FROM mv1 ORDER BY 1 ASC OFFSET 14;
  values: None
  runtime: 2023-01-23 10:23:56.560 - 2023-01-23 10:23:56.601
  supports explain: True
  supports rollback: True
  affected rows: None
Action: RandomSelectAction
Error class: InternalError_
Error code: XX000
Error message: ERROR:  Not found: The object '00004777000030008000000000000000.mv1' does not exist: OBJECT_NOT_FOUND
    @     0x555a69afdad8  errmsg
    @     0x555a69b29305  HandleYBStatusAtErrorLevel
    @     0x555a69858271  ybcBeginForeignScan
    @     0x555a6984d00a  ExecInitForeignScan
    @     0x555a698214cb  ExecInitNode
    @     0x555a6982acc2  ExecInitAgg
    @     0x555a6982145f  ExecInitNode
    @     0x555a698489aa  ExecInitSort
    @     0x555a698214f8  ExecInitNode
    @     0x555a6983dc37  ExecInitLimit
    @     0x555a69821516  ExecInitNode
    @     0x555a6981982c  standard_ExecutorStart
    @     0x7f39ae8743dd  pgss_ExecutorStart
    @     0x7f39ae86b1ab  ybpgm_ExecutorStart
    @     0x555a699b8cbb  PortalStart
    @     0x555a699b688a  yb_exec_simple_query_impl
    @     0x555a699b6f08  yb_exec_query_wrapper_one_attempt
    @     0x555a699b3fd8  PostgresMain
    @     0x555a69922dcc  BackendRun
    @     0x555a69922213  ServerLoop
    @     0x555a6991e4ab  PostmasterMain
    @     0x555a6987b64d  PostgresServerProcessMain
    @     0x555a6987bdd2  main
    @     0x7f39b2e8f825  __libc_start_main
    @     0x555a696248f9  _start

    @     0x555a69afdad8  errmsg
    @     0x555a699b5445  YBPrepareCacheRefreshIfNeeded
    @     0x555a699b35b1  PostgresMain
    @     0x555a69922dcc  BackendRun
    @     0x555a69922213  ServerLoop
    @     0x555a6991e4ab  PostmasterMain
    @     0x555a6987b64d  PostgresServerProcessMain
    @     0x555a6987bdd2  main
    @     0x7f39b2e8f825  __libc_start_main
    @     0x555a696248f9  _start

[tverona1]

Simplified repro:

-- setup
create table t1 as select * from generate_series(1,10000) as col;
create materialized view mv1 as select * from t1 where col > 500;
-- connection 1
BEGIN;
select * from mv1 limit 10;
-- connection 2
refresh materialized view mv1;
-- connection 1
select * from mv1 limit 10;
ERROR:  Not found: The object ‘000033e8000030008000000000000000.mv1’ does not exist: OBJECT_NOT_FOUND
CONTEXT:  Catalog Version Mismatch: A DDL occurred while processing this query. Try again.

[simon-mottram-cucumber]

Just a note to illustrate the impact of this. I was going to use materialized views to cache some foreign data wrapper tables. But I cannot refresh them while our system is receiving queries so basically that rules materialized views out for our system.

Instead I'm having to resort to uglier cache tables which have to be maintained by code.

So this issue is pretty big for some people