Goal | Find routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. |
Notes | Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. The query does not consider the routines that are a part of an extension. In the returned body of routine the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser. There could be multiple routines with the same name but with different parameters in the same schema (overloading). Thus, for the unique identification of the routine it is necessary to present also its parameters in addition to the schema name and routine name. |
Type | General (Overview of some aspect of the database.) |
License | MIT License |
Data Source | INFORMATION_SCHEMA+system catalog |
SQL Query |
|
This query belongs to the following collections:
Name | Description |
---|---|
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
This query is classified under the following categories:
Name | Description |
---|---|
Concurrency control | Queries of this category provide information about concurrency control. |
Does not work in some earlier PostgreSQL version | Queries of this category provide information that was not available in some earlier PostgreSQL version |
User-defined routines | Queries of this category provide information about the user-defined routines |
Further reading and related materials: