Shulou(Shulou.com)06/01 Report--

This article mainly introduces the example analysis of the system catalog table in PostgreSQL, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

The main role of PostgreSQL's system catalogs for us is to make some database transactions more convenient and automatic, such as finding tables without indexes, finding dependencies between objects in the database, doing some healthy checks on the database, and information collection work, which provides some database system monitoring solutions and related information supply.

OK has a clear purpose, so learning this knowledge can help us achieve what we want to achieve. We are summarizing it.

1 to solve some common problems of DBA and the defects in the operation of the system

2 provide clear values and instructions for monitoring the performance and problems of the POSTGRESQL system

(3) to solve the problem of the relationship between some OBJECTS in the system after the establishment of the application system.

It is mentioned in the book that by understanding and familiar with the information that exists in pg_catalog, we can understand the various objects in the system, such as, tables, veiws, functions indexes, foreign data wrapper (FDWs), triggers, constratins, rules, users, groups and so on.

Let's take PG12 as an example. There are 63 objects for pg_catalog in the newly installed PG12.

Of course, there is also some information, which is included in information_schema.

Pg_class is an extremely important table, which stores some object information of the system, tables, indexes, views, sequences, materialized views, types of combinations, Toast tables, and so on.

In pg_class, a letter is used to mark which type the current object belongs to.

You can learn the actual type of each object through the relkind field in pg_class

This leads to another problem: if these objects need to be recorded in pg_class, you need a primary key. How does the pg_class in PG use the primary key to mark each objects?

This is the oid message, and the primary key in pg_class is oid. Here we need to make an extension. I know the table name, but I don't know how to solve the OID situation. Another way of information transformation of PG, regclass, is introduced in the following figure.

Through the select statement, we will already know the OBJECT name in the corresponding way, for example, the text, plus single quotation marks, is the number does not need to add any match, followed by:: regclass:: as the intermediary symbol, and finally oid is the type of information you want to convert.

Note: through the above operation, we have mastered a way of information conversion.

SELECT c.oid FROM pg_class c join pg_namespace n ON

(c.relnamespace = n.oid) WHERE relname = 'table name'

Of course, the relevant information can also be found in other ways.

SELECT relname, case relkind when'r 'then' table' WHEN'v 'THEN' VIEW' END as type FROM pg_class WHERE relname like 'pg_sta%' AND relkind IN

At the same time, the system has statistical information about tables, indexes, columns, sequences and so on. Through these statistical information, we can also have a deeper understanding of some of the situation in the system.

Here are some commands and examples to show how to use these system tables or what kind of information those function commands can help us.

1 terminates all current query execution related to the test database, such as the current need for effective vacuum, so now it is necessary to clean up some query that cannot be completed for a long time.

Here we will use pg_stat_activity, through this table you can view the current connections related to PG, but in fact, in the test, connect to the database through Python, execute some simple statements through an endless loop, and the statements are executed quickly, which is not shown in pg_stat_activity, so pg_stat_activity is still more suitable for the discovery and analysis of long-running statements.

SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname =

Current_database () AND pid pg_backend_pid () and usename = 'admin'

The following figure shows a query in an endless loop, and after executing this statement, it is terminated because the SESSION is cut off. It needs to be emphasized here that pid pg_backend_pid () must be added to prevent cutting off the key in the system.

For example, some processes where the red line is drawn below.

Therefore, understanding and understanding the tables of the system and using them can solve many of the system problems we face.

Thank you for reading this article carefully. I hope the article "sample Analysis of system Catalog tables in PostgreSQL" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.