cybertec-postgresql
Size of Hash table and warning querying catalog
Hi Team,
-
When the server starts, pg_show_plans makes a hashtable on the shared-memory in order to temporarily store query plans. The hashtable size cannot be changed, so the plans are not stored if the hashtable is full. --- What is the size of the hashtable ? I see max_plan_length*max_connections is reserved, is the assumption correct or are these different ?
-
We get below warning when query the catalog table:
postgres=# select pg_catalog.shobj_description(d.oid, 'pg_database') as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY 1;
WARNING: The 1th level plan could not be stored in the entry whose pid is 42363 because the entry could not find.
WARNING: The 1th level plan could not be stored in the entry whose pid is 42363 because the entry could not find.
WARNING: The 1th level plan could not be stored in the entry whose pid is 42363 because the entry could not find.
WARNING: The 1th level plan could not be stored in the entry whose pid is 42363 because the entry could not find.
Description
--------------------------------------------------------
default administrative connection database
default template for new databases
To me, that sounds more like a bug: this error is thrown if pg_show_plans tries to store a plan with a nested_level > 0, but there is no plan stored for that process ID yet.
It would be great if you can come up with a reproducible test case so that we can reproduce the problem.
I installed the package, added it in shared_preload_libraries and created the extension. After this, I logged into the database using psql and ran shortcut for listing databases with size '\l+'
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+-------------+----------+-------------+-------------+----------------------------------
pg13upgrade_dev | pg13upgrade | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/pg13upgrade +
| | | | | pg13upgrade=CTc/pg13upgrade +
| | | | | grp_pg13upgrade_rw=c/pg13upgrade+
| | | | | grp_pg13upgrade_ro=c/pg13upgrade
pgrepmgr | pgrepmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres +
| | | | | =c/postgres
(5 rows)
postgres=# show shared_preload_libraries ;
shared_preload_libraries
----------------------------------------------------
pg_show_plans, repmgr, pg_stat_statements, pgaudit
(1 row)
postgres=# \l+
WARNING: The 1th level plan could not be stored in the entry whose pid is 44939 because the entry could not find.
WARNING: The 1th level plan could not be stored in the entry whose pid is 44939 because the entry could not find.
WARNING: The 1th level plan could not be stored in the entry whose pid is 44939 because the entry could not find.
WARNING: The 1th level plan could not be stored in the entry whose pid is 44939 because the entry could not find.
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------------+-------------+----------+-------------+-------------+----------------------------------+---------+------------+--------------------------------------------
pg13upgrade_dev | pg13upgrade | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/pg13upgrade +| 15 GB | pg_default |
| | | | | pg13upgrade=CTc/pg13upgrade +| | |
| | | | | grp_pg13upgrade_rw=c/pg13upgrade+| | |
| | | | | grp_pg13upgrade_ro=c/pg13upgrade | | |
pgrepmgr | pgrepmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 16 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 15 GB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8125 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres +| 8549 kB | pg_default | default template for new databases
| | | | | =c/postgres | | |
(5 rows)
@nikhil-postgres I have come up with a possible solution, are you willing to help me test it?
To me, that sounds more like a bug: this error is thrown if pg_show_plans tries to store a plan with a
nested_level> 0, but there is no plan stored for that process ID yet.It would be great if you can come up with a reproducible test case so that we can reproduce the problem.
Yes indeed.
pg_show_plans stores the all the plan nests within a single hash map entry. So that first (technically 0th) nest level creates and new entry, but all the further ones append to the already existing one (created at 0th nest level). However delete_entry() gets invoked each time we go one level above, therefore deleting the whole hash entry, rather than truncating the plan while leaving the entry in place.
The right behavior is to call delete_entry() no sooner than we reach nest level 0 from above, which will destroy the entry altogether.