Skip site navigation (1) Skip section navigation (2)

Re: cataloguing NOT NULL constraints

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: cataloguing NOT NULL constraints
Date: 2011-08-03 21:26:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Excerpts from Dean Rasheed's message of sáb jul 23 04:37:06 -0400 2011:
> On 22 July 2011 22:28, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> >> mine was that we need a command such as
> >>
> >> ALTER TABLE foo ALTER COLUMN bar SET NOT NULL name_of_notnull_constr
> >>
> >> where the last bit is what's new.
> >
> > Well, if you don't have that, I don't see how you have any chance of
> > pg_dump working correctly.
> Ah yes, pg_dump's dumpConstraint() needs a clause to alter a table
> adding a named NOT NULL constraint (and the DOMAIN case should be
> preserving the constraint's name too). So it looks like some new
> syntax for ALTER TABLE to add named NOT NULL constraints is probably
> needed before this can be committed.

So after writing the code to handle named NOT NULL constraints for
tables, I'm thinking that dumpConstraints needs to be fixed thusly:

@@ -12888,6 +12968,27 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
                         NULL, NULL);
+   else if (coninfo->contype == 'n' && tbinfo)
+   {
+       /* NOT NULL constraint on a table */
+       if (coninfo->separate)
+       {
+           write_msg(NULL, "NOT NULL constraints cannot be dumped separately from their owning table\n");
+           exit_nicely();
+       }
+   }
+   else if (coninfo->contype == 'n' && tbinfo == NULL)
+   {
+       /* NOT NULL constraint on a domain */
+       TypeInfo   *tyinfo = coninfo->condomain;
+       /* Ignore if not to be dumped separately */
+       if (coninfo->separate)
+       {
+           write_msg(NULL, "NOT NULL constraints cannot be dumped separately from their owning domain\n");
+           exit_nicely();
+       }
+   }
        write_msg(NULL, "unrecognized constraint type: %c\n", coninfo->contype);

There doesn't seem to be any point in giving pg_dump the ability to dump
such constraints separately; I don't think there's any situation in
which dependencies between the table/domain and NOT NULL constraints
would get circular (which is what causes them to acquire the "separate"
flag).  I don't want to write code that is going to be always
unused, particularly not in a beast as hairy such as pg_dump.

Note that the code dumps not null constraints along with the table
definition, which includes the constraint name, so it works perfectly
fine already.

Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2011-08-03 21:35:57
Subject: Re: mosbench revisited
Previous:From: James RobinsonDate: 2011-08-03 21:19:15
Subject: Postgres / plpgsql equivalent to python's getattr() ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group