PostgreSQL Source Code  git master
findoidjoins.c
Go to the documentation of this file.
1 /*
2  * findoidjoins.c
3  *
4  * Copyright (c) 2002-2020, PostgreSQL Global Development Group
5  *
6  * src/tools/findoidjoins/findoidjoins.c
7  */
8 #include "postgres_fe.h"
9 
10 #include "access/transam.h"
11 #include "catalog/pg_class_d.h"
12 
13 #include "fe_utils/connect.h"
14 #include "libpq-fe.h"
15 #include "pqexpbuffer.h"
16 
17 
18 int
19 main(int argc, char **argv)
20 {
21  PGconn *conn;
22  PQExpBufferData sql;
23  PGresult *res;
24  PGresult *pkrel_res;
25  PGresult *fkrel_res;
26  char *fk_relname;
27  char *fk_nspname;
28  char *fk_attname;
29  char *pk_relname;
30  char *pk_nspname;
31  int fk,
32  pk; /* loop counters */
33 
34  if (argc != 2)
35  {
36  fprintf(stderr, "Usage: %s database\n", argv[0]);
37  exit(EXIT_FAILURE);
38  }
39 
40  initPQExpBuffer(&sql);
41 
42  appendPQExpBuffer(&sql, "dbname=%s", argv[1]);
43 
44  conn = PQconnectdb(sql.data);
45  if (PQstatus(conn) == CONNECTION_BAD)
46  {
47  fprintf(stderr, "connection error: %s\n", PQerrorMessage(conn));
48  exit(EXIT_FAILURE);
49  }
50 
52  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
53  {
54  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
55  exit(EXIT_FAILURE);
56  }
57  PQclear(res);
58 
59  /* Get a list of system relations that have OIDs */
60 
61  printfPQExpBuffer(&sql,
62  "SELECT c.relname, (SELECT nspname FROM "
63  "pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
64  "FROM pg_catalog.pg_class c "
65  "WHERE c.relkind = " CppAsString2(RELKIND_RELATION)
66  " AND c.oid < '%u'"
67  " AND EXISTS(SELECT * FROM pg_attribute a"
68  " WHERE a.attrelid = c.oid AND a.attname = 'oid' "
69  " AND a.atttypid = 'oid'::regtype)"
70  "ORDER BY nspname, c.relname",
72  );
73 
74  res = PQexec(conn, sql.data);
75  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
76  {
77  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
78  exit(EXIT_FAILURE);
79  }
80  pkrel_res = res;
81 
82  /* Get a list of system columns of OID type (or any OID-alias type) */
83 
84  printfPQExpBuffer(&sql,
85  "SELECT c.relname, "
86  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
87  "a.attname "
88  "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
89  "WHERE a.attnum > 0"
90  " AND a.attname != 'oid'"
91  " AND c.relkind = " CppAsString2(RELKIND_RELATION)
92  " AND c.oid < '%u'"
93  " AND a.attrelid = c.oid"
94  " AND a.atttypid IN ('pg_catalog.oid'::regtype, "
95  " 'pg_catalog.regclass'::regtype, "
96  " 'pg_catalog.regoper'::regtype, "
97  " 'pg_catalog.regoperator'::regtype, "
98  " 'pg_catalog.regproc'::regtype, "
99  " 'pg_catalog.regprocedure'::regtype, "
100  " 'pg_catalog.regtype'::regtype, "
101  " 'pg_catalog.regconfig'::regtype, "
102  " 'pg_catalog.regdictionary'::regtype) "
103  "ORDER BY nspname, c.relname, a.attnum",
105  );
106 
107  res = PQexec(conn, sql.data);
108  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
109  {
110  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
111  exit(EXIT_FAILURE);
112  }
113  fkrel_res = res;
114 
115  /*
116  * For each column and each relation-having-OIDs, look to see if the
117  * column contains any values matching entries in the relation.
118  */
119 
120  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
121  {
122  fk_relname = PQgetvalue(fkrel_res, fk, 0);
123  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
124  fk_attname = PQgetvalue(fkrel_res, fk, 2);
125 
126  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
127  {
128  pk_relname = PQgetvalue(pkrel_res, pk, 0);
129  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
130 
131  printfPQExpBuffer(&sql,
132  "SELECT 1 "
133  "FROM \"%s\".\"%s\" t1, "
134  "\"%s\".\"%s\" t2 "
135  "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
136  "LIMIT 1",
137  fk_nspname, fk_relname,
138  pk_nspname, pk_relname,
139  fk_attname);
140 
141  res = PQexec(conn, sql.data);
142  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
143  {
144  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
145  exit(EXIT_FAILURE);
146  }
147 
148  if (PQntuples(res) != 0)
149  printf("Join %s.%s.%s => %s.%s.oid\n",
150  fk_nspname, fk_relname, fk_attname,
151  pk_nspname, pk_relname);
152 
153  PQclear(res);
154  }
155  }
156 
157  PQclear(fkrel_res);
158 
159  /* Now, do the same for referencing columns that are arrays */
160 
161  /* Get a list of columns of OID-array type (or any OID-alias type) */
162 
163  printfPQExpBuffer(&sql, "%s",
164  "SELECT c.relname, "
165  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
166  "a.attname "
167  "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
168  "WHERE a.attnum > 0"
169  " AND c.relkind = " CppAsString2(RELKIND_RELATION)
170  " AND a.attrelid = c.oid"
171  " AND a.atttypid IN ('pg_catalog.oid[]'::regtype, "
172  " 'pg_catalog.oidvector'::regtype, "
173  " 'pg_catalog.regclass[]'::regtype, "
174  " 'pg_catalog.regoper[]'::regtype, "
175  " 'pg_catalog.regoperator[]'::regtype, "
176  " 'pg_catalog.regproc[]'::regtype, "
177  " 'pg_catalog.regprocedure[]'::regtype, "
178  " 'pg_catalog.regtype[]'::regtype, "
179  " 'pg_catalog.regconfig[]'::regtype, "
180  " 'pg_catalog.regdictionary[]'::regtype) "
181  "ORDER BY nspname, c.relname, a.attnum"
182  );
183 
184  res = PQexec(conn, sql.data);
185  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
186  {
187  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
188  exit(EXIT_FAILURE);
189  }
190  fkrel_res = res;
191 
192  /*
193  * For each column and each relation-having-OIDs, look to see if the
194  * column contains any values matching entries in the relation.
195  */
196 
197  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
198  {
199  fk_relname = PQgetvalue(fkrel_res, fk, 0);
200  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
201  fk_attname = PQgetvalue(fkrel_res, fk, 2);
202 
203  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
204  {
205  pk_relname = PQgetvalue(pkrel_res, pk, 0);
206  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
207 
208  printfPQExpBuffer(&sql,
209  "SELECT 1 "
210  "FROM \"%s\".\"%s\" t1, "
211  "\"%s\".\"%s\" t2 "
212  "WHERE t2.oid = ANY(t1.\"%s\")"
213  "LIMIT 1",
214  fk_nspname, fk_relname,
215  pk_nspname, pk_relname,
216  fk_attname);
217 
218  res = PQexec(conn, sql.data);
219  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
220  {
221  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
222  exit(EXIT_FAILURE);
223  }
224 
225  if (PQntuples(res) != 0)
226  printf("Join %s.%s.%s []=> %s.%s.oid\n",
227  fk_nspname, fk_relname, fk_attname,
228  pk_nspname, pk_relname);
229 
230  PQclear(res);
231  }
232  }
233 
234  PQclear(fkrel_res);
235 
236  PQclear(pkrel_res);
237 
238  PQfinish(conn);
239 
240  termPQExpBuffer(&sql);
241 
242  exit(EXIT_SUCCESS);
243 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6687
void printfPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:237
#define EXIT_SUCCESS
Definition: settings.h:150
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3163
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4185
#define printf(...)
Definition: port.h:199
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2769
#define fprintf
Definition: port.h:197
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2692
#define FirstNormalObjectId
Definition: transam.h:155
PGconn * conn
Definition: streamutil.c:54
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
#define CppAsString2(x)
Definition: c.h:222
void PQclear(PGresult *res)
Definition: fe-exec.c:694
int main(int argc, char **argv)
Definition: findoidjoins.c:19
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
#define EXIT_FAILURE
Definition: settings.h:154
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1939
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6634
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
PGconn * PQconnectdb(const char *conninfo)
Definition: fe-connect.c:696