Hi,
On 2021-10-21 22:13:22 -0400, Tom Lane wrote:
> Andres Freund <and...@anarazel.de> writes:
> > I wonder though if for some of them we should instead replace the per-object
> > queries with one query returning the information for all objects of a type.
> > It
> > doesn't make all that much sense that we build and send one query for each
> > table and index.
>
> The trick is the problem I alluded to in another thread: it's not safe to
> do stuff like pg_get_expr() on tables we don't have lock on.
I was looking at getTableAttrs() - sending one query instead of #tables
queries yields a quite substantial speedup in a quick prototype. And I don't
think it changes anything around locking semantics.
> I've thought about doing something like
>
> SELECT unsafe-functions FROM pg_class WHERE oid IN (someoid, someoid, ...)
>
> but in cases with tens of thousands of tables, it seems unlikely that
> that's going to behave all that nicely.
That's kinda what I'm doing in the quick hack. But instead of using IN(...) I
made it unnest('{oid, oid, ...}'), that scales much better.
A pg_dump --schema-only of the regression database goes from
real 0m0.675s
user 0m0.039s
sys 0m0.029s
to
real 0m0.477s
user 0m0.037s
sys 0m0.020s
which isn't half-bad.
There's a few more cases like this I think. But most are harder because the
dumping happens one-by-one from dumpDumpableObject(). The relatively easy but
substantial cases I could find quickly were getIndexes(), getConstraints(),
getTriggers()
To see where it's worth putting in time it'd be useful if getSchemaData() in
verbose mode printed timing information...
> The *real* fix, I suppose, would be to fix all those catalog-inspection
> functions so that they operate with respect to the query's snapshot.
> But that's not a job I'm volunteering for. Besides which, pg_dump
> still has to cope with back-rev servers where it wouldn't be safe.
Yea, that's not a small change :(. I suspect that we'd need a bunch of new
caching infrastructure to make that reasonably performant, since this
presumably couldn't use syscache etc.
Greetings,
Andres Freund
diff --git c/src/bin/pg_dump/pg_dump.c i/src/bin/pg_dump/pg_dump.c
index ed8ed2f266e..7ec84428e9c 100644
--- c/src/bin/pg_dump/pg_dump.c
+++ i/src/bin/pg_dump/pg_dump.c
@@ -43,6 +43,7 @@
#include "access/transam.h"
#include "catalog/pg_aggregate_d.h"
#include "catalog/pg_am_d.h"
+#include "catalog/pg_attrdef.h"
#include "catalog/pg_attribute_d.h"
#include "catalog/pg_authid_d.h"
#include "catalog/pg_cast_d.h"
@@ -8397,6 +8398,14 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
{
DumpOptions *dopt = fout->dopt;
PQExpBuffer q = createPQExpBuffer();
+ PQExpBuffer tblidx = createPQExpBuffer();
+ PQExpBuffer tbloid = createPQExpBuffer();
+
+ PGresult *res;
+ bool first;
+ int i_tbloid;
+ int i_idx;
+ int i_natts;
int i_attnum;
int i_attname;
int i_atttypname;
@@ -8417,13 +8426,28 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_attfdwoptions;
int i_attmissingval;
int i_atthasdef;
+ int i_attdef;
+ int i_attdefoid;
+ int ntups;
+ int tblidx_prev = -1;
+ Oid tbloid_prev = InvalidOid;
+ int natts_prev = -1;
+ int natt_prev = -1;
+ TableInfo *tbinfo = NULL;
+ int tbstart = -1;
+
+ resetPQExpBuffer(tblidx);
+ resetPQExpBuffer(tbloid);
+
+ appendPQExpBufferStr(tblidx, "'{");
+ appendPQExpBufferStr(tbloid, "'{");
+
+ /* find all the user attributes and their types */
+ first = true;
for (int i = 0; i < numTables; i++)
{
TableInfo *tbinfo = &tblinfo[i];
- PGresult *res;
- int ntups;
- bool hasdefaults;
/* Don't bother to collect info for sequences */
if (tbinfo->relkind == RELKIND_SEQUENCE)
@@ -8433,301 +8457,376 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
if (!tbinfo->interesting)
continue;
- /* find all the user attributes and their types */
-
- /*
- * we must read the attribute names in attribute number order! because
- * we will use the attnum to index into the attnames array later.
- */
- pg_log_info("finding the columns and types of table \"%s.%s\"",
- tbinfo->dobj.namespace->dobj.name,
- tbinfo->dobj.name);
-
- resetPQExpBuffer(q);
-
- appendPQExpBufferStr(q,
- "SELECT\n"
- "a.attnum,\n"
- "a.attname,\n"
- "a.atttypmod,\n"
- "a.attstattarget,\n"
- "a.attstorage,\n"
- "t.typstorage,\n"
- "a.attnotnull,\n"
- "a.atthasdef,\n"
- "a.attisdropped,\n"
- "a.attlen,\n"
- "a.attalign,\n"
- "a.attislocal,\n"
- "pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,\n");
-
- if (fout->remoteVersion >= 90000)
- appendPQExpBufferStr(q,
- "array_to_string(a.attoptions, ', ') AS attoptions,\n");
+ if (first)
+ {
+ appendPQExpBuffer(tblidx, "%u", i);
+ appendPQExpBuffer(tbloid, "%u", tbinfo->dobj.catId.oid);
+ first = false;
+ }
else
- appendPQExpBufferStr(q,
- "'' AS attoptions,\n");
+ {
+ appendPQExpBuffer(tblidx, ", %u", i);
+ appendPQExpBuffer(tbloid, ", %u", tbinfo->dobj.catId.oid);
+ }
+ }
- if (fout->remoteVersion >= 90100)
+ appendPQExpBufferStr(tblidx, "}'::int[]");
+ appendPQExpBufferStr(tbloid, "}'::oid[]");
+
+ resetPQExpBuffer(q);
+ appendPQExpBufferStr(q,
+ "SELECT\n"
+ "src.idx,\n"
+ "src.tbloid,\n"
+ "count(*) OVER(PARTITION BY idx) AS natts,\n"
+ "a.attnum,\n"
+ "a.attname,\n"
+ "a.atttypmod,\n"
+ "a.attstattarget,\n"
+ "a.attstorage,\n"
+ "t.typstorage,\n"
+ "a.attnotnull,\n"
+ "a.atthasdef,\n"
+ "a.attisdropped,\n"
+ "a.attlen,\n"
+ "a.attalign,\n"
+ "a.attislocal,\n"
+ "pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,\n");
+
+ if (fout->remoteVersion >= 90000)
+ appendPQExpBufferStr(q,
+ "array_to_string(a.attoptions, ', ') AS attoptions,\n");
+ else
+ appendPQExpBufferStr(q,
+ "'' AS attoptions,\n");
+
+ if (fout->remoteVersion >= 90100)
+ {
+ /*
+ * Since we only want to dump COLLATE clauses for attributes whose
+ * collation is different from their type's default, we use a CASE
+ * here to suppress uninteresting attcollations cheaply.
+ */
+ appendPQExpBufferStr(q,
+ "CASE WHEN a.attcollation <> t.typcollation "
+ "THEN a.attcollation ELSE 0 END AS attcollation,\n");
+ }
+ else
+ appendPQExpBufferStr(q,
+ "0 AS attcollation,\n");
+
+ if (fout->remoteVersion >= 140000)
+ appendPQExpBuffer(q,
+ "a.attcompression AS attcompression,\n");
+ else
+ appendPQExpBuffer(q,
+ "'' AS attcompression,\n");
+
+ if (fout->remoteVersion >= 90200)
+ appendPQExpBufferStr(q,
+ "pg_catalog.array_to_string(ARRAY("
+ "SELECT pg_catalog.quote_ident(option_name) || "
+ "' ' || pg_catalog.quote_literal(option_value) "
+ "FROM pg_catalog.pg_options_to_table(attfdwoptions) "
+ "ORDER BY option_name"
+ "), E',\n ') AS attfdwoptions,\n");
+ else
+ appendPQExpBufferStr(q,
+ "'' AS attfdwoptions,\n");
+
+ if (fout->remoteVersion >= 100000)
+ appendPQExpBufferStr(q,
+ "a.attidentity,\n");
+ else
+ appendPQExpBufferStr(q,
+ "'' AS attidentity,\n");
+
+ if (fout->remoteVersion >= 110000)
+ appendPQExpBufferStr(q,
+ "CASE WHEN a.atthasmissing AND NOT a.attisdropped "
+ "THEN a.attmissingval ELSE null END AS attmissingval,\n");
+ else
+ appendPQExpBufferStr(q,
+ "NULL AS attmissingval,\n");
+
+ if (fout->remoteVersion >= 120000)
+ appendPQExpBufferStr(q,
+ "a.attgenerated,\n");
+ else
+ appendPQExpBufferStr(q,
+ "'' AS attgenerated,\n");
+
+ /*
+ * Get info about column defaults. This is skipped for a data-only
+ * dump, as it is only needed for table schemas.
+ */
+ if (!dopt->dataOnly)
+ {
+ appendPQExpBufferStr(q,
+ "CASE WHEN atthasdef THEN\n"
+ " pg_catalog.pg_get_expr(atdef.adbin, atdef.adrelid)\n"
+ "END AS attdef,\n");
+ appendPQExpBufferStr(q, "atdef.oid AS attdefoid\n");
+ }
+ else
+ {
+ appendPQExpBufferStr(q, "NULL::text AS attdef,\n");
+ appendPQExpBufferStr(q, "NULL::oid AS attdefoid\n");
+ }
+
+ /* need left join here to not fail on dropped columns ... */
+ appendPQExpBuffer(q,
+ "FROM\n"
+ " unnest(%s,\n"
+ " %s)\n"
+ " AS src(idx, tbloid)\n"
+ " JOIN pg_catalog.pg_attribute a\n"
+ " ON (src.tbloid = a.attrelid AND a.attnum > 0::pg_catalog.int2)\n"
+ " LEFT JOIN pg_catalog.pg_type t\n"
+ " ON (a.atttypid = t.oid)\n"
+ " LEFT JOIN pg_catalog.pg_attrdef atdef\n"
+ " ON (a.atthasdef AND atdef.adrelid = src.tbloid AND atdef.adnum = a.attnum)\n"
+ "ORDER BY idx, a.attnum\n",
+ tblidx->data,
+ tbloid->data);
+
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+
+ i_idx = PQfnumber(res, "idx");
+ i_tbloid = PQfnumber(res, "tbloid");
+ i_natts = PQfnumber(res, "natts");
+ i_attnum = PQfnumber(res, "attnum");
+ i_attname = PQfnumber(res, "attname");
+ i_atttypname = PQfnumber(res, "atttypname");
+ i_atttypmod = PQfnumber(res, "atttypmod");
+ i_attstattarget = PQfnumber(res, "attstattarget");
+ i_attstorage = PQfnumber(res, "attstorage");
+ i_typstorage = PQfnumber(res, "typstorage");
+ i_attidentity = PQfnumber(res, "attidentity");
+ i_attgenerated = PQfnumber(res, "attgenerated");
+ i_attisdropped = PQfnumber(res, "attisdropped");
+ i_attlen = PQfnumber(res, "attlen");
+ i_attalign = PQfnumber(res, "attalign");
+ i_attislocal = PQfnumber(res, "attislocal");
+ i_attnotnull = PQfnumber(res, "attnotnull");
+ i_attoptions = PQfnumber(res, "attoptions");
+ i_attcollation = PQfnumber(res, "attcollation");
+ i_attcompression = PQfnumber(res, "attcompression");
+ i_attfdwoptions = PQfnumber(res, "attfdwoptions");
+ i_attmissingval = PQfnumber(res, "attmissingval");
+ i_atthasdef = PQfnumber(res, "atthasdef");
+ i_attdef = PQfnumber(res, "attdef");
+ i_attdefoid = PQfnumber(res, "attdefoid");
+
+ ntups = PQntuples(res);
+
+ for (int r = 0; r < ntups; r++)
+ {
+ int tblidx;
+ int tbloid;
+ int natts;
+ int natt;
+ int attnum;
+ bool hasdefault;
+
+ tblidx = atoi(PQgetvalue(res, r, i_idx));
+ tbloid = atoi(PQgetvalue(res, r, i_tbloid));
+ natts = atoi(PQgetvalue(res, r, i_natts));
+ attnum = atoi(PQgetvalue(res, r, i_attnum));
+ natt = attnum - 1;
+
+ if (tblidx_prev == -1 || tblidx != tblidx_prev)
{
/*
- * Since we only want to dump COLLATE clauses for attributes whose
- * collation is different from their type's default, we use a CASE
- * here to suppress uninteresting attcollations cheaply.
+ * Either the first row for the first table, or rows for a
+ * different table are starting.
*/
- appendPQExpBufferStr(q,
- "CASE WHEN a.attcollation <> t.typcollation "
- "THEN a.attcollation ELSE 0 END AS attcollation,\n");
+
+ /*
+ * FIXME: verify indexes aren't running over end of index,
+ * e.g. due to a maliscious server.
+ */
+ Assert(attnum == 1);
+
+ tbinfo = &tblinfo[tblidx];
+
+ tbinfo->numatts = natts;
+ tbinfo->attnames = (char **) pg_malloc(natts * sizeof(char *));
+ tbinfo->atttypnames = (char **) pg_malloc(natts * sizeof(char *));
+ tbinfo->atttypmod = (int *) pg_malloc(natts * sizeof(int));
+ tbinfo->attstattarget = (int *) pg_malloc(natts * sizeof(int));
+ tbinfo->attstorage = (char *) pg_malloc(natts * sizeof(char));
+ tbinfo->typstorage = (char *) pg_malloc(natts * sizeof(char));
+ tbinfo->attidentity = (char *) pg_malloc(natts * sizeof(char));
+ tbinfo->attgenerated = (char *) pg_malloc(natts * sizeof(char));
+ tbinfo->attisdropped = (bool *) pg_malloc(natts * sizeof(bool));
+ tbinfo->attlen = (int *) pg_malloc(natts * sizeof(int));
+ tbinfo->attalign = (char *) pg_malloc(natts * sizeof(char));
+ tbinfo->attislocal = (bool *) pg_malloc(natts * sizeof(bool));
+ tbinfo->attoptions = (char **) pg_malloc(natts * sizeof(char *));
+ tbinfo->attcollation = (Oid *) pg_malloc(natts * sizeof(Oid));
+ tbinfo->attcompression = (char *) pg_malloc(natts * sizeof(char));
+ tbinfo->attfdwoptions = (char **) pg_malloc(natts * sizeof(char *));
+ tbinfo->attmissingval = (char **) pg_malloc(natts * sizeof(char *));
+ tbinfo->notnull = (bool *) pg_malloc(natts * sizeof(bool));
+ tbinfo->inhNotNull = (bool *) pg_malloc(natts * sizeof(bool));
+ tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(natts * sizeof(AttrDefInfo *));
+
+ tbstart = r;
+ tblidx_prev = tblidx;
+ tbloid_prev = tbloid;
+ natts_prev = natts;
+ natt_prev = 0;
+
+ pg_log_info("processing rows of table \"%s.%s\"",
+ tbinfo->dobj.namespace->dobj.name,
+ tbinfo->dobj.name);
}
else
- appendPQExpBufferStr(q,
- "0 AS attcollation,\n");
-
- if (fout->remoteVersion >= 140000)
- appendPQExpBuffer(q,
- "a.attcompression AS attcompression,\n");
- else
- appendPQExpBuffer(q,
- "'' AS attcompression,\n");
-
- if (fout->remoteVersion >= 90200)
- appendPQExpBufferStr(q,
- "pg_catalog.array_to_string(ARRAY("
- "SELECT pg_catalog.quote_ident(option_name) || "
- "' ' || pg_catalog.quote_literal(option_value) "
- "FROM pg_catalog.pg_options_to_table(attfdwoptions) "
- "ORDER BY option_name"
- "), E',\n ') AS attfdwoptions,\n");
- else
- appendPQExpBufferStr(q,
- "'' AS attfdwoptions,\n");
-
- if (fout->remoteVersion >= 100000)
- appendPQExpBufferStr(q,
- "a.attidentity,\n");
- else
- appendPQExpBufferStr(q,
- "'' AS attidentity,\n");
-
- if (fout->remoteVersion >= 110000)
- appendPQExpBufferStr(q,
- "CASE WHEN a.atthasmissing AND NOT a.attisdropped "
- "THEN a.attmissingval ELSE null END AS attmissingval,\n");
- else
- appendPQExpBufferStr(q,
- "NULL AS attmissingval,\n");
-
- if (fout->remoteVersion >= 120000)
- appendPQExpBufferStr(q,
- "a.attgenerated\n");
- else
- appendPQExpBufferStr(q,
- "'' AS attgenerated\n");
-
- /* need left join here to not fail on dropped columns ... */
- appendPQExpBuffer(q,
- "FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
- "ON a.atttypid = t.oid\n"
- "WHERE a.attrelid = '%u'::pg_catalog.oid "
- "AND a.attnum > 0::pg_catalog.int2\n"
- "ORDER BY a.attnum",
- tbinfo->dobj.catId.oid);
-
- res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
-
- ntups = PQntuples(res);
-
- tbinfo->numatts = ntups;
- tbinfo->attnames = (char **) pg_malloc(ntups * sizeof(char *));
- tbinfo->atttypnames = (char **) pg_malloc(ntups * sizeof(char *));
- tbinfo->atttypmod = (int *) pg_malloc(ntups * sizeof(int));
- tbinfo->attstattarget = (int *) pg_malloc(ntups * sizeof(int));
- tbinfo->attstorage = (char *) pg_malloc(ntups * sizeof(char));
- tbinfo->typstorage = (char *) pg_malloc(ntups * sizeof(char));
- tbinfo->attidentity = (char *) pg_malloc(ntups * sizeof(char));
- tbinfo->attgenerated = (char *) pg_malloc(ntups * sizeof(char));
- tbinfo->attisdropped = (bool *) pg_malloc(ntups * sizeof(bool));
- tbinfo->attlen = (int *) pg_malloc(ntups * sizeof(int));
- tbinfo->attalign = (char *) pg_malloc(ntups * sizeof(char));
- tbinfo->attislocal = (bool *) pg_malloc(ntups * sizeof(bool));
- tbinfo->attoptions = (char **) pg_malloc(ntups * sizeof(char *));
- tbinfo->attcollation = (Oid *) pg_malloc(ntups * sizeof(Oid));
- tbinfo->attcompression = (char *) pg_malloc(ntups * sizeof(char));
- tbinfo->attfdwoptions = (char **) pg_malloc(ntups * sizeof(char *));
- tbinfo->attmissingval = (char **) pg_malloc(ntups * sizeof(char *));
- tbinfo->notnull = (bool *) pg_malloc(ntups * sizeof(bool));
- tbinfo->inhNotNull = (bool *) pg_malloc(ntups * sizeof(bool));
- tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(ntups * sizeof(AttrDefInfo *));
- hasdefaults = false;
-
- i_attnum = PQfnumber(res, "attnum");
- i_attname = PQfnumber(res, "attname");
- i_atttypname = PQfnumber(res, "atttypname");
- i_atttypmod = PQfnumber(res, "atttypmod");
- i_attstattarget = PQfnumber(res, "attstattarget");
- i_attstorage = PQfnumber(res, "attstorage");
- i_typstorage = PQfnumber(res, "typstorage");
- i_attidentity = PQfnumber(res, "attidentity");
- i_attgenerated = PQfnumber(res, "attgenerated");
- i_attisdropped = PQfnumber(res, "attisdropped");
- i_attlen = PQfnumber(res, "attlen");
- i_attalign = PQfnumber(res, "attalign");
- i_attislocal = PQfnumber(res, "attislocal");
- i_attnotnull = PQfnumber(res, "attnotnull");
- i_attoptions = PQfnumber(res, "attoptions");
- i_attcollation = PQfnumber(res, "attcollation");
- i_attcompression = PQfnumber(res, "attcompression");
- i_attfdwoptions = PQfnumber(res, "attfdwoptions");
- i_attmissingval = PQfnumber(res, "attmissingval");
- i_atthasdef = PQfnumber(res, "atthasdef");
-
- for (int j = 0; j < ntups; j++)
{
- if (j + 1 != atoi(PQgetvalue(res, j, i_attnum)))
- fatal("invalid column numbering in table \"%s\"",
- tbinfo->dobj.name);
- tbinfo->attnames[j] = pg_strdup(PQgetvalue(res, j, i_attname));
- tbinfo->atttypnames[j] = pg_strdup(PQgetvalue(res, j, i_atttypname));
- tbinfo->atttypmod[j] = atoi(PQgetvalue(res, j, i_atttypmod));
- tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
- tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
- tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
- tbinfo->attidentity[j] = *(PQgetvalue(res, j, i_attidentity));
- tbinfo->attgenerated[j] = *(PQgetvalue(res, j, i_attgenerated));
- tbinfo->needs_override = tbinfo->needs_override || (tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
- tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
- tbinfo->attlen[j] = atoi(PQgetvalue(res, j, i_attlen));
- tbinfo->attalign[j] = *(PQgetvalue(res, j, i_attalign));
- tbinfo->attislocal[j] = (PQgetvalue(res, j, i_attislocal)[0] == 't');
- tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
- tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, j, i_attoptions));
- tbinfo->attcollation[j] = atooid(PQgetvalue(res, j, i_attcollation));
- tbinfo->attcompression[j] = *(PQgetvalue(res, j, i_attcompression));
- tbinfo->attfdwoptions[j] = pg_strdup(PQgetvalue(res, j, i_attfdwoptions));
- tbinfo->attmissingval[j] = pg_strdup(PQgetvalue(res, j, i_attmissingval));
- tbinfo->attrdefs[j] = NULL; /* fix below */
- if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
- hasdefaults = true;
- /* these flags will be set in flagInhAttrs() */
- tbinfo->inhNotNull[j] = false;
+ /* FIXME: verify attrs match */
+ Assert(tbloid_prev == tbloid);
+ Assert(natts_prev == natts);
+ Assert(natt_prev + 1 == natt);
+ natt_prev++;
}
- PQclear(res);
+ natt = r - tbstart;
+
+ if (natt + 1 != attnum)
+ fatal("invalid column numbering in table \"%s\"",
+ tbinfo->dobj.name);
+ tbinfo->attnames[natt] = pg_strdup(PQgetvalue(res, r, i_attname));
+ tbinfo->atttypnames[natt] = pg_strdup(PQgetvalue(res, r, i_atttypname));
+ tbinfo->atttypmod[natt] = atoi(PQgetvalue(res, r, i_atttypmod));
+ tbinfo->attstattarget[natt] = atoi(PQgetvalue(res, r, i_attstattarget));
+ tbinfo->attstorage[natt] = *(PQgetvalue(res, r, i_attstorage));
+ tbinfo->typstorage[natt] = *(PQgetvalue(res, r, i_typstorage));
+ tbinfo->attidentity[natt] = *(PQgetvalue(res, r, i_attidentity));
+ tbinfo->attgenerated[natt] = *(PQgetvalue(res, r, i_attgenerated));
+ tbinfo->needs_override = tbinfo->needs_override || (tbinfo->attidentity[natt] == ATTRIBUTE_IDENTITY_ALWAYS);
+ tbinfo->attisdropped[natt] = (PQgetvalue(res, r, i_attisdropped)[0] == 't');
+ tbinfo->attlen[natt] = atoi(PQgetvalue(res, r, i_attlen));
+ tbinfo->attalign[natt] = *(PQgetvalue(res, r, i_attalign));
+ tbinfo->attislocal[natt] = (PQgetvalue(res, r, i_attislocal)[0] == 't');
+ tbinfo->notnull[natt] = (PQgetvalue(res, r, i_attnotnull)[0] == 't');
+ tbinfo->attoptions[natt] = pg_strdup(PQgetvalue(res, r, i_attoptions));
+ tbinfo->attcollation[natt] = atooid(PQgetvalue(res, r, i_attcollation));
+ tbinfo->attcompression[natt] = *(PQgetvalue(res, r, i_attcompression));
+ tbinfo->attfdwoptions[natt] = pg_strdup(PQgetvalue(res, r, i_attfdwoptions));
+ tbinfo->attmissingval[natt] = pg_strdup(PQgetvalue(res, r, i_attmissingval));
+ tbinfo->attrdefs[natt] = NULL; /* fix below */
+
+ /* these flags will be set in flagInhAttrs() */
+ tbinfo->inhNotNull[natt] = false;
+
+ hasdefault = PQgetvalue(res, r, i_atthasdef)[0] == 't';
/*
* Get info about column defaults. This is skipped for a data-only
* dump, as it is only needed for table schemas.
*/
- if (!dopt->dataOnly && hasdefaults)
+ /*
+ * dropped columns shouldn't have defaults, but just in case,
+ * ignore 'em
+ */
+ if (!dopt->dataOnly && hasdefault)
{
- AttrDefInfo *attrdefs;
- int numDefaults;
+ AttrDefInfo *attrdef;
- pg_log_info("finding default expressions of table \"%s.%s\"",
- tbinfo->dobj.namespace->dobj.name,
- tbinfo->dobj.name);
+ /*
+ * dropped columns shouldn't have defaults, but just in case,
+ * ignore 'em
+ */
+ if (tbinfo->attisdropped[natt])
+ continue;
- printfPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
- "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
- "FROM pg_catalog.pg_attrdef "
- "WHERE adrelid = '%u'::pg_catalog.oid",
- tbinfo->dobj.catId.oid);
+ attrdef = pg_malloc(sizeof(AttrDefInfo));
+ tbinfo->attrdefs[natt] = attrdef;
- res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+ attrdef->dobj.objType = DO_ATTRDEF;
+ attrdef->dobj.catId.tableoid = AttrDefaultRelationId;
+ attrdef->dobj.catId.oid = atooid(PQgetvalue(res, r, i_attdefoid));
+ AssignDumpId(&attrdef->dobj);
+ attrdef->adtable = tbinfo;
+ attrdef->adnum = natt + 1;
+ attrdef->adef_expr = pg_strdup(PQgetvalue(res, r, i_attdef));
- numDefaults = PQntuples(res);
- attrdefs = (AttrDefInfo *) pg_malloc(numDefaults * sizeof(AttrDefInfo));
+ attrdef->dobj.name = pg_strdup(tbinfo->dobj.name);
+ attrdef->dobj.namespace = tbinfo->dobj.namespace;
- for (int j = 0; j < numDefaults; j++)
+ attrdef->dobj.dump = tbinfo->dobj.dump;
+
+ /*
+ * Figure out whether the default/generation expression should
+ * be dumped as part of the main CREATE TABLE (or similar)
+ * command or as a separate ALTER TABLE (or similar) command.
+ * The preference is to put it into the CREATE command, but in
+ * some cases that's not possible.
+ */
+ if (tbinfo->attgenerated[natt])
{
- int adnum;
-
- adnum = atoi(PQgetvalue(res, j, 2));
-
- if (adnum <= 0 || adnum > ntups)
- fatal("invalid adnum value %d for table \"%s\"",
- adnum, tbinfo->dobj.name);
-
/*
- * dropped columns shouldn't have defaults, but just in case,
- * ignore 'em
+ * Column generation expressions cannot be dumped
+ * separately, because there is no syntax for it. The
+ * !shouldPrintColumn case below will be tempted to set
+ * them to separate if they are attached to an inherited
+ * column without a local definition, but that would be
+ * wrong and unnecessary, because generation expressions
+ * are always inherited, so there is no need to set them
+ * again in child tables, and there is no syntax for it
+ * either. By setting separate to false here we prevent
+ * the "default" from being processed as its own dumpable
+ * object, and flagInhAttrs() will remove it from the
+ * table when it detects that it belongs to an inherited
+ * column.
*/
- if (tbinfo->attisdropped[adnum - 1])
- continue;
-
- attrdefs[j].dobj.objType = DO_ATTRDEF;
- attrdefs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
- attrdefs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
- AssignDumpId(&attrdefs[j].dobj);
- attrdefs[j].adtable = tbinfo;
- attrdefs[j].adnum = adnum;
- attrdefs[j].adef_expr = pg_strdup(PQgetvalue(res, j, 3));
-
- attrdefs[j].dobj.name = pg_strdup(tbinfo->dobj.name);
- attrdefs[j].dobj.namespace = tbinfo->dobj.namespace;
-
- attrdefs[j].dobj.dump = tbinfo->dobj.dump;
-
- /*
- * Figure out whether the default/generation expression should
- * be dumped as part of the main CREATE TABLE (or similar)
- * command or as a separate ALTER TABLE (or similar) command.
- * The preference is to put it into the CREATE command, but in
- * some cases that's not possible.
- */
- if (tbinfo->attgenerated[adnum - 1])
- {
- /*
- * Column generation expressions cannot be dumped
- * separately, because there is no syntax for it. The
- * !shouldPrintColumn case below will be tempted to set
- * them to separate if they are attached to an inherited
- * column without a local definition, but that would be
- * wrong and unnecessary, because generation expressions
- * are always inherited, so there is no need to set them
- * again in child tables, and there is no syntax for it
- * either. By setting separate to false here we prevent
- * the "default" from being processed as its own dumpable
- * object, and flagInhAttrs() will remove it from the
- * table when it detects that it belongs to an inherited
- * column.
- */
- attrdefs[j].separate = false;
- }
- else if (tbinfo->relkind == RELKIND_VIEW)
- {
- /*
- * Defaults on a VIEW must always be dumped as separate
- * ALTER TABLE commands.
- */
- attrdefs[j].separate = true;
- }
- else if (!shouldPrintColumn(dopt, tbinfo, adnum - 1))
- {
- /* column will be suppressed, print default separately */
- attrdefs[j].separate = true;
- }
- else
- {
- attrdefs[j].separate = false;
- }
-
- if (!attrdefs[j].separate)
- {
- /*
- * Mark the default as needing to appear before the table,
- * so that any dependencies it has must be emitted before
- * the CREATE TABLE. If this is not possible, we'll
- * change to "separate" mode while sorting dependencies.
- */
- addObjectDependency(&tbinfo->dobj,
- attrdefs[j].dobj.dumpId);
- }
-
- tbinfo->attrdefs[adnum - 1] = &attrdefs[j];
+ attrdef->separate = false;
+ }
+ else if (tbinfo->relkind == RELKIND_VIEW)
+ {
+ /*
+ * Defaults on a VIEW must always be dumped as separate
+ * ALTER TABLE commands.
+ */
+ attrdef->separate = true;
+ }
+ else if (!shouldPrintColumn(dopt, tbinfo, natt))
+ {
+ /* column will be suppressed, print default separately */
+ attrdef->separate = true;
+ }
+ else
+ {
+ attrdef->separate = false;
+ }
+
+ if (!attrdef->separate)
+ {
+ /*
+ * Mark the default as needing to appear before the table,
+ * so that any dependencies it has must be emitted before
+ * the CREATE TABLE. If this is not possible, we'll
+ * change to "separate" mode while sorting dependencies.
+ */
+ addObjectDependency(&tbinfo->dobj,
+ attrdef->dobj.dumpId);
}
- PQclear(res);
}
+ }
+
+ for (int i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+ PGresult *res;
+
+ /* Don't bother to collect info for sequences */
+ if (tbinfo->relkind == RELKIND_SEQUENCE)
+ continue;
+
+ /* Don't bother with uninteresting tables, either */
+ if (!tbinfo->interesting)
+ continue;
/*
* Get info about table CHECK constraints. This is skipped for a
@@ -8851,6 +8950,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
}
destroyPQExpBuffer(q);
+ destroyPQExpBuffer(tblidx);
+ destroyPQExpBuffer(tbloid);
}
/*
