Re: pg_dump emits ALTER TABLE ONLY partitioned_table

On 2017/03/29 0:39, Robert Haas wrote:
> On Tue, Mar 28, 2017 at 6:50 AM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> Isn't it bogus that this is generating ALTER TABLE .. SET NOT NULL
>>> columns at all? You didn't say anything like that when setting up the
>>> database, so why should it be there when dumping?
>>
>> So we should find a way for the NOT NULL constraints added for the range
>> partition key columns to not be emitted *separately*? Like when a table
>> has primary key:
>>
>> --
>> -- Name: foo; Type: TABLE; Schema: public; Owner: amit
>> --
>>
>> CREATE TABLE foo (
>> a integer NOT NULL
>> );
>>
>>
>> ALTER TABLE foo OWNER TO amit;
>>
>> --
>> -- Name: foo foo_pkey; Type: CONSTRAINT; Schema: public; Owner: amit
>> --
>>
>> ALTER TABLE ONLY foo
>> ADD CONSTRAINT foo_pkey PRIMARY KEY (a);
>>
>> The NOT NULL constraint is emitted with CREATE TABLE, not separately.
>
> Hmm, that's not exactly what I was thinking, but I think what I was
> thinking was wrong, so, yes, can we do what you said?

I thought about this for a while. Although it seems we can do what I said
for (partitioned) tables themselves, it's not real clear to me how
straightforward it is to do for partitions (child tables). Inheritance or
localness of attributes/constraints including NOT NULL dictates whether an
attribute or a constraint is emitted separately. I think that any
additional consideration will make the logic to *not* dump separately (or
perhaps to not emit at all) will become more involved. For example, if a
NOT NULL constraint on a column has been inherited and originated in the
parent from the range partition key, then does it mean we should not emit
it or emit it but not separately?

Thanks,
Amit