PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal Type ▲ Data source Last update License
421 FILLFACTOR is probably too big Find base tables in case of which the FILLFACTOR property has perhaps a too big value. Try to find base tables that probably encounter UPDATE operations. In the tables that have frequent updates you want to have free space in table pages (blocks) to accommodate new row versions, which the system automatically creates as a result of fulfilling UPDATE statements. If a new row version will be put to another page by the system, then it means that table indexes have to be updated as well. Thus, the more there are indexes, the more the table would benefit from keeping a new row version in the same page as the old version. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-21 09:19 MIT License View
422 FILLFACTOR is probably too small Too small fillfactor wastes storage space. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-02 10:43 MIT License View
423 FILLFACTOR is probably too small (2) Find base tables that probably implement many to many relationship type, have no clear sign that there are columns that should be updated, but still have fillfactor less than 100. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-11 14:38 MIT License View
424 Flag parameters A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-19 11:03 MIT License View
425 Foreign key column has a default value that is not present in the parent table Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
426 Foreign key column has a simple check constraint that is attached directly to the table Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
427 Foreign key columns that are associated with a sequence generator Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-08 00:42 MIT License View
428 Foreign key columns that have no index Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
429 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
430 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint while the referenced table has the primary key Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
431 Foreign key references a non-key (has optional columns) Find foreign key constraints that referenced column is optional. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-10 12:12 MIT License View
432 Foreign key references to a unique index columns not a unique key columns Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key. Problem detection system catalog base tables only 2022-10-21 10:30 MIT License View
433 Foreign key refers to a table that has at least one subtable in the inheritance hierarchy Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
434 Foreign servers without user mappings Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource." Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
435 FOR UPDATE in derived tables Find derived tables that subquery uses FOR UPDATE construct. Reading a data element shouldn't block other read operations of the same element. Thus, you shouldn't use exclusive locking command in a view. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-12 11:11 MIT License View
436 FOR UPDATE is not allowed with aggregate functions Implement explicit locking correctly. Instead of writing, for instance, SELECT Count(*) AS cnt FROM person WHERE person_id=1 FOR UPDATE; one has to write SELECT person_id FROM person WHERE person_id=1 FOR UPDATE; Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:23 MIT License View
437 FOR UPDATE is not needed if there is no FROM clause in the SELECT statement Find routines that use SELECT … FOR UPDATE without selecting rows from a specific table. For instance: SELECT 'text' AS v FOR UPDATE; Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-05 14:10 MIT License View
438 Full text search columns that have no gin or gist index Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index. These are the preferred index types for text search. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-07 09:22 MIT License View
439 Full text search columns with other type of index than gin or gist index Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index but have another type of index (e.g., b-tree). Gin and Gist are the preferred index types for text search. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-07 09:22 MIT License View
440 Function in a function-based index of a column is different from the function that is used in the subquery of a derived table Find cases where the function of a function-based index of a column is different from the function that is used in the query in a derived table based on the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View