BUG #14900: MView not null constraint

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #14900: MView not null constraint

Neil Anderson-2
The following bug has been logged on the website:

Bug reference:      14900
Logged by:          Neil Anderson
Email address:      [hidden email]
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: BUG #14900: MView not null constraint

Tom Lane-2
[hidden email] 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: BUG #14900: MView not null constraint

Neil Anderson-2
On 2017-11-12 12:12 PM, Tom Lane wrote:

> [hidden email] 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
[hidden email]
http://www.postgrescompare.com

neil.vcf (174 bytes) Download Attachment