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