Implement the compatibility of PostgreSQL system tables

[Orion7r]

Development Task

Description

The system library of PostgreSQL is information schema and pg catalog, which is different from TiDB. These two databases are implemented in TiDB for PostgreSQL, but the implementation of system tables and system views in these two databases is not perfect and needs to be supplemented.

The implementation of system tables and system views is mainly to be compatible with query requests connected from clients, such as PgAdmin and Navicat, so that these clients can connect to TiDB for PostgreSQL using PostgreSQL protocol.

Detailed information about information_schema and pg_catalog can be found in the following two links. information_schema pg_catalog

This section mainly covers the InfoSchema and Executor directories. System tables and views are defined in InfoSchema. System table structures can be defined in the tables.go file, and infoschema_reader.go file in Executor, which involves initialization of related system table data.

Currently, we only implement the related system tables for information_schema, but there is no initialization for the data that exists in the system tables, which means that most of the tables are empty, which is definitely not normal. For pg_catalog, instead of implementing information_schema, we use the session module to execute SQL statements to initialize system tables, system views and data in pg_catalog before starting TiDB. This needs to be changed later to the same implementation as information_schema.

Task List

1. The initial data for the system table in information_schema
2. Tables and corresponding data in the pg_catalog system library

Methods or modules that need to be implemented：

• [x] setDataForPgInformationSchemaCatalogName
• [x] setDataForPgAdministrableRoleAuthorizations
• [ ] setDataForPgApplicableRole
• [ ] setDataForPgAttributes
• [ ] setDataForPgCharacterSets
• [ ] setDataForPgCheckConstraintRoutineUsage
• [ ] setDataForPgCheckConstraints
• [x] setDataForPgCollations
• [x] setDataForPgCollationCharacterSetApplicability
• [ ] setDataForPgColumnColumnUsage
• [ ] setDataForPgColumnDomainUsage
• [ ] setDataForPgColumnOptions
• [ ] setDataForPgColumnPrivileges
• [ ] setDataForPgColumnUdtUsage
• [ ] setDataForPgColumns
• [ ] setDataForPgConstraintColumnUsage
• [ ] setDataForPgConstraintTableUsage
• [ ] setDataForPgDataTypePrivileges
• [ ] setDataForPgDomainConstraints
• [ ] setDataForPgDomainUdtUsage
• [ ] setDataForPgDomains
• [ ] setDataForPgElementTypes
• [ ] setDataForPgEnabledRoles
• [ ] setDataForPgForeignDataWrapperOptions
• [ ] setDataForPgForeignDataWrappers
• [ ] setDataForPgForeignServerOptions
• [ ] setDataForPgForeignServers
• [ ] setDataForPgForeignTableOptions
• [ ] setDataForPgForeignTales
• [x] setDataForPgKeyColumnUsage
• [ ] setDataForPgParameters
• [ ] setDataForPgReferentialConstraints
• [ ] setDataForPgRoleColumnGrants
• [ ] setDataForPgRoleRoutineGrants
• [ ] setDataForPgRoleTableGrants
• [ ] setDataForPgTableConstraints
• [ ] setDataForPgRoleUdtGrants
• [ ] setDataForPgRoleUsageGrants
• [ ] setDataForPgRoutinePrivileges
• [ ] setDataForPgRoutines
• [x] setDataForPgSchemata
• [x] setDataForPgSequences
• [ ] setDataForPgSqlFeatures
• [ ] setDataForPgSqlImplementationInfo
• [ ] setDataForPgSqlParts
• [ ] setDataForPgSqlSizing
• [ ] setDataForPgTableConstraints
• [ ] setDataForPgTablePrivileges
• [x] setDataForPgTables
• [ ] setDataForPgTransforms
• [ ] setDataForPgTriggeredUpdateColumns
• [ ] setDataForPgTriggers
• [ ] setDataForPgUdtPrivileges
• [ ] setDataForPgUsagePrivileges
• [ ] setDataForPgUserDefinedTypes
• [ ] setDataForPgUserMappingOptions
• [ ] setDataForPgUserMappings
• [ ] setDataForPgViewColumnUsage
• [ ] setDataForPgViewRoutineUsage
• [ ] setDataForPgViewTableUsage
• [x] setDataForPgViews
• [ ] System table implementation in pg_catalog
• [ ] Data for pg_catalog tables