pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: no NOT NULL constraint found to drop
4+ messages / 3 participants
[nested] [flat]
* ERROR: no NOT NULL constraint found to drop
@ 2023-04-10 07:16 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Guo @ 2023-04-10 07:16 UTC (permalink / raw)
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
I run into error $subject with the query below:
# create table t1 (c int primary key null unique);
CREATE TABLE
# create table t2 (like t1);
CREATE TABLE
# alter table t2 alter c drop not null;
ERROR: no NOT NULL constraint found to drop
This starts since e056c557ae. I guess this shouldn't happen since the
comment says so.
/* this shouldn't happen */
elog(ERROR, "no NOT NULL constraint found to drop");
Thanks
Richard
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: no NOT NULL constraint found to drop
@ 2023-04-10 07:55 Michael Paquier <michael@paquier.xyz>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Paquier @ 2023-04-10 07:55 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Apr 10, 2023 at 03:16:06PM +0800, Richard Guo wrote:
> I run into error $subject with the query below:
>
> # create table t1 (c int primary key null unique);
> CREATE TABLE
> # create table t2 (like t1);
> CREATE TABLE
> # alter table t2 alter c drop not null;
> ERROR: no NOT NULL constraint found to drop
>
> This starts since e056c557ae. I guess this shouldn't happen since the
> comment says so.
>
> /* this shouldn't happen */
> elog(ERROR, "no NOT NULL constraint found to drop");
Thanks for the report. This is not the only issue that has been
pointed out with this patch, so it is going to be reverted if you look
around here:
https://www.postgresql.org/message-id/3863449.1681071102@sss.pgh.pa.us
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: no NOT NULL constraint found to drop
@ 2023-04-10 13:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2023-04-10 13:46 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Michael Paquier <michael@paquier.xyz> writes:
> On Mon, Apr 10, 2023 at 03:16:06PM +0800, Richard Guo wrote:
>> I run into error $subject with the query below:
>> # create table t1 (c int primary key null unique);
>> CREATE TABLE
>> # create table t2 (like t1);
>> CREATE TABLE
>> # alter table t2 alter c drop not null;
>> ERROR: no NOT NULL constraint found to drop
> Thanks for the report. This is not the only issue that has been
> pointed out with this patch, so it is going to be reverted if you look
> around here:
It's still good to know about it for next time. The issue I guess is
that LIKE with no options propagates column attnotnull bits, but not
constraints, so we now have an inconsistency: t2.c has attnotnull set
but there is nothing in pg_constraint to justify it. It seems to me
we're going to have to think about what we want to happen in this
case. In a green field we'd probably not propagate NOT NULL unless
told to copy constraints ... but is it okay to break functional
compatibility with the old behavior?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: no NOT NULL constraint found to drop
@ 2023-04-12 00:31 Michael Paquier <michael@paquier.xyz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Paquier @ 2023-04-12 00:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Apr 10, 2023 at 09:46:59AM -0400, Tom Lane wrote:
> It's still good to know about it for next time. The issue I guess is
> that LIKE with no options propagates column attnotnull bits, but not
> constraints, so we now have an inconsistency: t2.c has attnotnull set
> but there is nothing in pg_constraint to justify it. It seems to me
> we're going to have to think about what we want to happen in this
> case. In a green field we'd probably not propagate NOT NULL unless
> told to copy constraints ... but is it okay to break functional
> compatibility with the old behavior?
I am not sure about that, TBH, though I would tend to not break
compatibility just for the sake of breaking it. Anyway, shouldn't we
have a test that does a DROP NOT NULL after a LIKE copies it? At
least, we'll be able to track that.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-04-12 00:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-10 07:16 ERROR: no NOT NULL constraint found to drop Richard Guo <guofenglinux@gmail.com>
2023-04-10 07:55 ` Michael Paquier <michael@paquier.xyz>
2023-04-10 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-12 00:31 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox