Re: pg_dump: Sorted output, referential integrity

On Tue, 11 Dec 2001, Philip Warner wrote:

> At 13:34 7/12/01 -0800, Stephan Szabo wrote:
> >
> >Well, the biggest thing I see on using alter table add constraint for
> >foreign keys is the expense involved if you do it after the tables are
> >populated.
>
> Is it really worse than loading the tables with the constraint in place?

I'd say its better than while loading, but currently the check isn't
performed at all I think, because the create constraint trigger
statements are after data load and they don't check the data at all.
At least that's how I remember it, I could be wrong.

> >I chose the theoretical cleanliness of checking each row
> >using the code we had over the speed of doing a special check for the
> >alter table case,
>
> Out of curiosity - what was the difference?

The check could be performed in a single statment on the fktable with
a not exists (limit 1). I've sort of hoped that the optimizer would
be able to potentially pick a better plan than run the subselect once
for every row in the fktable. :)  But at the time, I wasn't comfortable
with mucking with the triggers themselves, and that would have meant
having two things that each had a copy of the fk check logic.

> By the sounds of it, we may get 'alter table' in pg_dump by 7.3 or 7.4.

That'd be cool. :)