Re: cataloguing NOT NULL constraints

On 3 August 2011 22:26, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> 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();
> +       }
> +   }
>    else
>    {
>        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.
>

Wow. Yes I think you're right - it is a hairy beast :-)

It was the code immediately above that for CHECK constraints that made
me assume NOT NULLs would need similar logic. But I haven't quite
figured out how a CHECK constraint's dependencies could form a loop,
so I don't know if it could happen for a NOT NULL.

Regards,
Dean