Skip to content

2.25.1.0-b287

2.25.1.0-b287: [#25776] YSQL: Prefetch the pg_authid table during login
@d-uspenskiy d-uspenskiy tagged this 03 Feb 19:38 
Summary:
On login postgres performs read from multiple sys tables. YSQL uses table prefetching optimization to reduce number of rpc to master.
Currently prefetched tables are:
```
- 	YbRegisterTable(prefetcher, YB_PFETCH_TABLE_PG_AUTH_MEMBERS); // pg_auth_members
- 	YbRegisterTable(prefetcher, YB_PFETCH_TABLE_PG_DATABASE); // pg_database
- 	YbRegisterTable(prefetcher, YB_PFETCH_TABLE_PG_DB_ROLE_SETTINGS); // pg_db_role_setting
```

It is expected that postgres will read from prefetched tables only. In case postgres make a read from another table (due to some tricky schema which is not covered by our unit tests) YSQL raises the following `FATAL` error.

```
      LOG(DFATAL) << "Sys table prefetching is enabled but table "
                  << table_id
                  << " was not prefetched. Prefetched tables are: "
                  << CollectionToString(data_, [](const auto& item) { return item.first; });
```

`debug` build crashes on this error but `release` build only log the error and try to perform requested read.
This behavior allows to find scenarios where prefetch optimization doesn't cover all the tables (`debug` build). And also it doesn't block user login in `release` build.

But release build may face the `Snapshot too old` problem. Because read from non-prefetched table will be performed with using same read time as read from prefetched tables and also response cache will not be used.

It is not possible to reset read time because in this case inconsistent state may be read:
- suppose some DDL makes changes in sys table `tbl_1` and `tbl_2` simultaneously and do not increment catalog version
- `tbl_1` is prefetched on login while `tbl_2` is not
- entries from `tbl_1` came from cache with read `t1` and entries from `tbl_2` without the cache will be read at `t2`
- in case of DDL was happened between `t1` and `t2` read data from `tbl_1` and `tbl_2` will be inconsistent

Also using cache will not solve the issue in all the cases because some entries might be pushed out from `LRU` cache. And reads will still be sent to a master.
It is not possible to recover from such error in case of on demand reading. YSQL only can recover from such error on prefetching tables. Because no data from this tables are used and all the tables are known. So YSQL might re-read all this data again with different cache options to renew the cache.

This diff only implements registering `pg_authid` table for prefetching without introducing any mechanism to prevent `Snapshot Too old` error in future (i.e. in case there some tricky scenario where some other sys tables get read on login).

Jira: DB-15064

Test Plan:
New unit test was introduced

```
./yb_build.sh --gtest_filter PgCatalogPerfTest.RestrictedConnections
```

Reviewers: myang, tnayak, pjain

Reviewed By: myang

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D41514
Assets 2
Loading