Thread: [BUGS] BUG #14900: MView not null constraint
The following bug has been logged on the website: Bug reference: 14900 Logged by: Neil Anderson Email address: neil@postgrescompare.com PostgreSQL version: 10.1 Operating system: OSx Description: This is probably not a bug but I spotted a question on StackOverflow that I've been attempting to answer re: not null constraints and materialized views. (https://stackoverflow.com/questions/47242219/how-to-indicate-that-a-column-is-not-nullable-when-creating-a-materialized-view/47245081#47245081) If it's not a bug should postgres support ALTER MATERIALIZED VIEW ALTER COLUMN column SET NOT NULL syntax? Thanks, Neil -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
neil@postgrescompare.com writes:
> This is probably not a bug
Nope, it isn't.
> If it's not a bug should postgres support
> ALTER MATERIALIZED VIEW ALTER COLUMN column SET NOT NULL syntax?
Doesn't seem like a great idea to me. What's supposed to happen
if a view update would result in a violation of the constraint?
It's way too late to undo whatever change of the underlying data
caused that, so it seems like the only possible answer is for
REFRESHes to fail, resulting in the view getting more and more
out of date until and unless something manual is done to resolve
the problem. I can't see that that's a useful behavior to have.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 2017-11-12 12:12 PM, Tom Lane wrote: > neil@postgrescompare.com writes: >> This is probably not a bug > > Nope, it isn't. > >> If it's not a bug should postgres support >> ALTER MATERIALIZED VIEW ALTER COLUMN column SET NOT NULL syntax? > > Doesn't seem like a great idea to me. What's supposed to happen > if a view update would result in a violation of the constraint? > It's way too late to undo whatever change of the underlying data > caused that, so it seems like the only possible answer is for > REFRESHes to fail, resulting in the view getting more and more > out of date until and unless something manual is done to resolve > the problem. I can't see that that's a useful behavior to have. > > regards, tom lane > > Makes sense. Seems like the scaffolding tool's problem and in fact they do have a way to declare it non-nullable via comments now https://github.com/gajus/postloader#handling-non-nullable-columns-in-materialized-views Thanks for the info! -- Neil Anderson neil@postgrescompare.com http://www.postgrescompare.com
Attachment
On 2017-11-12 12:12 PM, Tom Lane wrote: > neil@postgrescompare.com writes: >> This is probably not a bug > > Nope, it isn't. > >> If it's not a bug should postgres support >> ALTER MATERIALIZED VIEW ALTER COLUMN column SET NOT NULL syntax? > > Doesn't seem like a great idea to me. What's supposed to happen > if a view update would result in a violation of the constraint? > It's way too late to undo whatever change of the underlying data > caused that, so it seems like the only possible answer is for > REFRESHes to fail, resulting in the view getting more and more > out of date until and unless something manual is done to resolve > the problem. I can't see that that's a useful behavior to have. > > regards, tom lane > > Makes sense. Seems like the scaffolding tool's problem and in fact they do have a way to declare it non-nullable via comments now https://github.com/gajus/postloader#handling-non-nullable-columns-in-materialized-views Thanks for the info! -- Neil Anderson neil@postgrescompare.com http://www.postgrescompare.com