Hi all,
Postgers version - 11.17
OS: Linux
We recently had an interesting missing foreign key index issue that was very hard to detect. It involved a parent/child relationship where the data type of the parent key did not match the data type of the child key. (It’s a longer story about why they don’t match so I won’t get into it here). The issue presented itself in our database as long running commits (> 1 minute). We immediately thought unindexed foreign key but after running a query to identify any unindexed foreign keys we couldn’t find one.
After some investigating we learned that the long running commit was in fact being caused by the check for child keys when deleting a parent record as part of multiple sql statement transaction. The internal query that postgres was running looked like this:
"SELECT 1 FROM ONLY “<app_schema>”.”<child_table>" x WHERE $1 OPERATOR(pg_catalog.=) “<child_column>"::pg_catalog."numeric" FOR KEY SHARE OF x"
The problem ended up being that the parent data type (numeric) was different than the child(bigint). So when we were deleting the parent, the check for child records on the child table was ignoring the index (on the bigint column) on the child column because of the differing data types (numeric vs bigint). The short term workaround was to create an index casting the child column as numeric which resolved the long running commits.
We have log_min_duration_statement set to log any query that runs longer that 2 seconds but the long running internal statement was not logged. My question: is there any way to record an entry in the postgres server log for the long running internal/recursive query that was checking for child records as a result of of deleting the parent record?
The only way we were able to identify the long running query that was checking for child records was to ctrl-c out of the long running delete from the parent delete statement in a test system. We tried 'set log_statement= “all”' in psql before executing the long running delete in the test system but the internal check for child records was not logged.
If it is possible to log long running internal queries as outlined above any guidance would be appreciated.
Regards,
Postgers version - 11.17
OS: Linux
We recently had an interesting missing foreign key index issue that was very hard to detect. It involved a parent/child relationship where the data type of the parent key did not match the data type of the child key. (It’s a longer story about why they don’t match so I won’t get into it here). The issue presented itself in our database as long running commits (> 1 minute). We immediately thought unindexed foreign key but after running a query to identify any unindexed foreign keys we couldn’t find one.
After some investigating we learned that the long running commit was in fact being caused by the check for child keys when deleting a parent record as part of multiple sql statement transaction. The internal query that postgres was running looked like this:
"SELECT 1 FROM ONLY “<app_schema>”.”<child_table>" x WHERE $1 OPERATOR(pg_catalog.=) “<child_column>"::pg_catalog."numeric" FOR KEY SHARE OF x"
The problem ended up being that the parent data type (numeric) was different than the child(bigint). So when we were deleting the parent, the check for child records on the child table was ignoring the index (on the bigint column) on the child column because of the differing data types (numeric vs bigint). The short term workaround was to create an index casting the child column as numeric which resolved the long running commits.
We have log_min_duration_statement set to log any query that runs longer that 2 seconds but the long running internal statement was not logged. My question: is there any way to record an entry in the postgres server log for the long running internal/recursive query that was checking for child records as a result of of deleting the parent record?
The only way we were able to identify the long running query that was checking for child records was to ctrl-c out of the long running delete from the parent delete statement in a test system. We tried 'set log_statement= “all”' in psql before executing the long running delete in the test system but the internal check for child records was not logged.
If it is possible to log long running internal queries as outlined above any guidance would be appreciated.
Regards,
Craig Jackson
