Add a database view for postgres foreign keys
What does this MR do?
Add a database view and associated rails model for querying foreign key relationships between tables.
I plan to use this in #332199 (closed) where I need to check that a partition being dropped is not referenced by a foreign key, as that would create a long-running lock, but it will be generally useful elsewhere too.
Migration details
Up
== 20210719145532 AddForeignKeysView: migrating ===============================
-- execute("CREATE OR REPLACE VIEW postgres_foreign_keys AS\nSELECT\n pg_constraint.oid AS oid,\n pg_constraint.conname AS name,\n constrained_namespace.nspname::text || '.'::text || constrained_table.relname::text AS constrained_table_identifier,\n referenced_namespace.nspname::text || '.'::text || referenced_table.relname::text AS referenced_table_identifier\nFROM pg_constraint\n INNER JOIN pg_class constrained_table ON constrained_table.oid = pg_constraint.conrelid\n INNER JOIN pg_class referenced_table ON referenced_table.oid = pg_constraint.confrelid\n INNER JOIN pg_namespace constrained_namespace ON constrained_table.relnamespace = constrained_namespace.oid\n INNER JOIN pg_namespace referenced_namespace ON referenced_table.relnamespace = referenced_namespace.oid\nWHERE contype = 'f';\n")
-> 0.0047s
== 20210719145532 AddForeignKeysView: migrated (0.0047s) ======================
Down
== 20210719145532 AddForeignKeysView: reverting ===============================
-- execute("DROP VIEW IF EXISTS postgres_foreign_keys\n")
-> 0.0023s
== 20210719145532 AddForeignKeysView: reverted (0.0024s) ======================
Queries
SELECT * FROM postgres_foreign_keys WHERE referenced_table_name = 'public.web_hook_logs'
(from PostgresForeignKey.by_referenced_table_identifier('public.web_hook_logs'))
Query Plan
Nested Loop (cost=602.86..855.44 rows=5 width=132) (actual time=14.597..14.602 rows=0 loops=1)
Buffers: shared hit=11283 read=3
I/O Timings: read=2.329 write=0.000
-> Nested Loop (cost=602.72..854.57 rows=5 width=264) (actual time=14.596..14.601 rows=0 loops=1)
Buffers: shared hit=11283 read=3
I/O Timings: read=2.329 write=0.000
-> Hash Join (cost=602.44..850.67 rows=5 width=200) (actual time=14.594..14.599 rows=0 loops=1)
Hash Cond: (pg_constraint.confrelid = referenced_table.oid)
Buffers: shared hit=11283 read=3
I/O Timings: read=2.329 write=0.000
-> Index Scan using pg_constraint_conrelid_contypid_conname_index on pg_catalog.pg_constraint (cost=0.28..245.05 rows=910 width=76) (actual time=0.060..3.931 rows=911 loops=1)
Filter: (pg_constraint.contype = 'f'::"char")
Rows Removed by Filter: 1383
Buffers: shared hit=1848 read=3
I/O Timings: read=2.329 write=0.000
-> Hash (cost=601.60..601.60 rows=45 width=132) (actual time=10.445..10.448 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=9432
I/O Timings: read=0.000 write=0.000
-> Hash Join (cost=4.51..601.60 rows=45 width=132) (actual time=9.127..10.437 rows=1 loops=1)
Hash Cond: (referenced_table.relnamespace = referenced_namespace.oid)
Buffers: shared hit=9432
I/O Timings: read=0.000 write=0.000
-> Index Scan using pg_class_oid_index on pg_catalog.pg_class referenced_table (cost=0.29..573.74 rows=8966 width=72) (actual time=0.014..6.376 rows=5028 loops=1)
Buffers: shared hit=9431
I/O Timings: read=0.000 write=0.000
-> Hash (cost=4.10..4.10 rows=10 width=68) (actual time=0.021..0.022 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
I/O Timings: read=0.000 write=0.000
-> Seq Scan on pg_catalog.pg_namespace referenced_namespace (cost=0.00..4.10 rows=10 width=68) (actual time=0.008..0.011 rows=10 loops=1)
Buffers: shared hit=1
I/O Timings: read=0.000 write=0.000
-> Index Scan using pg_class_oid_index on pg_catalog.pg_class constrained_table (cost=0.29..0.78 rows=1 width=72) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (constrained_table.oid = pg_constraint.conrelid)
I/O Timings: read=0.000 write=0.000
-> Index Scan using pg_namespace_oid_index on pg_catalog.pg_namespace constrained_namespace (cost=0.14..0.15 rows=1 width=68) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (constrained_namespace.oid = constrained_table.relnamespace)
I/O Timings: read=0.000 write=0.000
Statistics
Time: 17.278 ms
- planning: 2.379 ms
- execution: 14.899 ms
- I/O read: 2.329 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 11283 (~88.10 MiB) from the buffer pool
- reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Does this MR meet the acceptance criteria?
Conformity
-
I have included changelog trailers, or none are needed. (Does this MR need a changelog?) -
I have added/updated documentation, or it's not needed. (Is documentation required?) -
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) -
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) -
I have self-reviewed this MR per code review guidelines. -
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) -
I have followed the style guides. -
This change is backwards compatible across updates, or this does not apply.
Availability and Testing
-
I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) -
I have tested this MR in all supported browsers, or it's not needed. -
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.
Edited by Simon Tomlinson