public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dean Rasheed <[email protected]>
To: Aleksander Alekseev <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Amul Sul <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Subject: Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).
Date: Mon, 4 Mar 2024 13:50:21 +0000
Message-ID: <CAEZATCWZfdZVnsiJdCSZfMNsOSrTtEZ2gjiZ-HQoNc2FMYxGhA@mail.gmail.com> (raw)
In-Reply-To: <CAJ7c6TOtv4T0LfxJB8-bSDX-5e9tpKLTMGpYzWhywWeHaxLxGg@mail.gmail.com>
References: <CAAJ_b94QonkgsbDXofakHDnORQNgafd1y3Oa5QXfpQNJyXyQ7A@mail.gmail.com>
	<[email protected]>
	<CAAJ_b94vRvR8AymA08Dz9Cs_3rHq+dRfY_-tnuJU48jkEuojwg@mail.gmail.com>
	<CALDaNm0ELv1v+DUw3iOfbOpCHqW49kFmaU2QN3APFeFp_ge2dg@mail.gmail.com>
	<CAAJ_b951Qm4uH7ak_MvGDRQ1nUarPvk3Nr2pfmqySS_EtDzBhA@mail.gmail.com>
	<CAJ7c6TOtv4T0LfxJB8-bSDX-5e9tpKLTMGpYzWhywWeHaxLxGg@mail.gmail.com>

On Mon, 4 Mar 2024 at 12:34, Aleksander Alekseev
<[email protected]> wrote:
>
> > This was an experimental patch, I was looking for the comment on the proposed
> > approach -- whether we could simply skip the throwaway NOT NULL constraint for
> > deferred PK constraint.  Moreover,  skip that for any PK constraint.
>
> I confirm that the patch fixes the bug. All the tests pass. Looks like
> RfC to me.
>

I don't think that this is the right fix. ISTM that the real issue is
that dropping a NOT NULL constraint should not mark the column as
nullable if it is part of a PK, whether or not that PK is deferrable
-- a deferrable PK still marks a  column as not nullable.

The reason pg_dump creates these throwaway NOT NULL constraints is to
avoid a table scan to check for NULLs when the PK is later created.
That rationale still applies to deferrable PKs, so we still want the
throwaway NOT NULL constraints in that case, otherwise we'd be hurting
performance of restore.

Regards,
Dean

view thread (8+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).
  In-Reply-To: <CAEZATCWZfdZVnsiJdCSZfMNsOSrTtEZ2gjiZ-HQoNc2FMYxGhA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox