8000 fix: replace CTEs with joins (#586) · shenmirs/postgres-meta@166fda0 · GitHub
[go: up one dir, main page]
Skip to content

Commit 166fda0

Browse files
soedirgosoedirgo
authored
fix: replace CTEs with joins (supabase#586)
* fix: replace CTEs with joins * chore: remove unused vars & files * chore: complete group by columns * fix: pre-aggregate primary_keys Can't aggregate over 2 source tables in the same query, otherwise it'll produce incorrect results: https://stackoverflow.com/a/27626358/12396224
1 parent d707f4f commit 166fda0

File tree

5 files changed

+67
-53
lines changed

5 files changed

+67
-53
lines changed

‎src/lib/PostgresMetaTables.ts

Lines changed: 1 addition & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
import { ident, literal } from 'pg-format'
22
import { DEFAULT_SYSTEM_SCHEMAS } from './constants.js'
33
import { coalesceRowsToArray, filterByList } from './helpers.js'
4-
import { columnsSql, primaryKeysSql, relationshipsOldSql, tablesSql } from './sql/index.js'
4+
import { columnsSql, tablesSql } from './sql/index.js'
55
import {
66
PostgresMetaResult,
77
PostgresTable,
@@ -250,15 +250,7 @@ COMMIT;`
250250
const generateEnrichedTablesSql = ({ includeColumns }: { includeColumns: boolean }) => `
251251
with tables as (${tablesSql})
252252
${includeColumns ? `, columns as (${columnsSql})` : ''}
253-
, primary_keys as (${primaryKeysSql})
254-
, relationships as (${relationshipsOldSql})
255253
select
256254
*
257255
${includeColumns ? `, ${coalesceRowsToArray('columns', 'columns.table_id = tables.id')}` : ''}
258-
, ${coalesceRowsToArray('primary_keys', 'primary_keys.table_id = tables.id')}
259-
, ${coalesceRowsToArray(
260-
'relationships',
261-
`(relationships.source_schema = tables.schema AND relationships.source_table_name = tables.name)
262-
OR (relationships.target_table_schema = tables.schema AND relationships.target_table_name = tables.name)`
263-
)}
264256
from tables`

‎src/lib/sql/index.ts

Lines changed: 0 additions & 2 deletions
8000
Original file line numberDiff line numberDiff line change
@@ -13,13 +13,11 @@ export const materializedViewsSql = await readFile(
1313
'utf-8'
1414
)
1515
export const policiesSql = await readFile(join(__dirname, 'policies.sql'), 'utf-8')
16-
export const primaryKeysSql = await readFile(join(__dirname, 'primary_keys.sql'), 'utf-8')
1716
export const publicationsSql = await readFile(join(__dirname, 'publications.sql'), 'utf-8')
1817
export const tableRelationshipsSql = await readFile(
1918
join(__dirname, 'table_relationships.sql'),
2019
'utf-8'
2120
)
22-
export const relationshipsOldSql = await readFile(join(__dirname, 'relationships_old.sql'), 'utf-8')
2321
export const rolesSql = await readFile(join(__dirname, 'roles.sql'), 'utf-8')
2422
export const schemasSql = await readFile(join(__dirname, 'schemas.sql'), 'utf-8')
2523
export const tablesSql = await readFile(join(__dirname, 'tables.sql'), 'utf-8')

‎src/lib/sql/primary_keys.sql

Lines changed: 0 additions & 16 deletions
This file was deleted.

‎src/lib/sql/relationships_old.sql

Lines changed: 0 additions & 25 deletions
This file was deleted.

‎src/lib/sql/tables.sql

Lines changed: 66 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,10 +16,67 @@ SELECT
1616
) AS size,
1717
pg_stat_get_live_tuples(c.oid) AS live_rows_estimate,
1818
pg_stat_get_dead_tuples(c.oid) AS dead_rows_estimate,
19-
obj_description(c.oid) AS comment
19+
obj_description(c.oid) AS comment,
20+
coalesce(pk.primary_keys, '[]') as primary_keys,
21+
coalesce(
22+
jsonb_agg(relationships) filter (where relationships is not null),
23+
'[]'
24+
) as relationships
2025
FROM
2126
pg_namespace nc
2227
JOIN pg_class c ON nc.oid = c.relnamespace
28+
left join (
29+
select
30+
table_id,
31+
jsonb_agg(_pk.*) as primary_keys
32+
from (
33+
select
34+
n.nspname as schema,
35+
c.relname as table_name,
36+
a.attname as name,
37+
c.oid :: int8 as table_id
38+
from
39+
pg_index i,
40+
pg_class c,
41+
pg_attribute a,
42+
pg_namespace n
43+
where
44+
i.indrelid = c.oid
45+
and c.relnamespace = n.oid
46+
and a.attrelid = c.oid
47+
and a.attnum = any (i.indkey)
48+
and i.indisprimary
49+
) as _pk
50+
group by table_id
51+
) as pk
52+
on pk.table_id = c.oid
53+
left join (
54+
select
55+
c.oid :: int8 as id,
56+
c.conname as constraint_name,
57+
nsa.nspname as source_schema,
58+
csa.relname as source_table_name,
59+
sa.attname as source_column_name,
60+
nta.nspname as target_table_schema,
61+
cta.relname as target_table_name,
62+
ta.attname as target_column_name
63+
from
64+
pg_constraint c
65+
join (
66+
pg_attribute sa
67+
join pg_class csa on sa.attrelid = csa.oid
68+
join pg_namespace nsa on csa.relnamespace = nsa.oid
69+
) on sa.attrelid = c.conrelid and sa.attnum = any (c.conkey)
70+
join (
71+
pg_attribute ta
72+
join pg_class cta on ta.attrelid = cta.oid
73+
join pg_namespace nta on A3E2 cta.relnamespace = nta.oid
74+
) on ta.attrelid = c.confrelid and ta.attnum = any (c.confkey)
75+
where
76+
c.contype = 'f'
77+
) as relationships
78+
on (relationships.source_schema = nc.nspname and relationships.source_table_name = c.relname)
79+
or (relationships.target_table_schema = nc.nspname and relationships.target_table_name = c.relname)
2380
WHERE
2481
c.relkind IN ('r', 'p')
2582
AND NOT pg_is_other_temp_schema(nc.oid)
@@ -31,3 +88,11 @@ WHERE
3188
)
3289
OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')
3390
)
91+
group by
92+
c.oid,
93+
c.relname,
94+
c.relrowsecurity,
95+
c.relforcerowsecurity,
96+
c.relreplident,
97+
nc.nspname,
98+
pk.primary_keys

0 commit comments

Comments
 (0)
0