Re: Editing View Bug

Thanks for reporting.

*RM created:* https://redmine.postgresql.org/issues/1924

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Nov 3, 2016 at 12:30 AM, Albin, Lloyd P <lalbin(at)scharp(dot)org> wrote:

> If you try and edit a view that does not use Check Option and has
> DISTINCT, the view will fail to be updated. Here an example to cause the
> failure.
>
>
>
> CREATE TABLE x (y varchar, z int);
>
> CREATE VIEW v AS SELECT DISTINCT ON (y) y, z FROM x ORDER BY y, z DESC;
>
>
>
> Right click on the view and select Properties.
>
>
>
> Security barrier: Off
>
> Check options: Blank or No
>
> Definition:
>
>
>
> Change the ORDER BY to y, z DESC, 1
>
>
>
> <SAVE>
>
>
>
> Error saving properties: INTERNAL SERVER ERROR
>
>
>
> ERROR: WITH CHECK OPTION is supported only on automatically updatable views
>
> HINT: Views containing DISTINCT are not automatically updatable.
>
>
>
>
>
> Looking at the SQL Tab, I see:
>
>
>
> CREATE OR REPLACE VIEW public.v
>
> WITH (check_option=no, security_barrier=false)
>
> AS
>
> SELECT DISTINCT ON (x.y) x.y,
>
> x.z
>
> FROM x
>
> ORDER BY x.y, x.z DESC, 1;
>
>
>
> 1) According to the Postgres Docs for 9.4.x and 9.6.x no is not listed a
> valid option for check_option.
>
> 2) It appears that Postgres is checking for the DISCTINCT before seeing
> the check_option=no
>
> 3) Since no does not appear to be valid per the docs, maybe pgAdmin 4
> needs to exclude the WITH line if check_option is set to no.
>
>
>
> Lloyd
>
>
>
> *Lloyd Albin*
> Database Administrator
> Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
> Vaccine and Infectious Disease Division (VIDD)
> Fred Hutchinson Cancer Research Center (FHCRC)
> lalbin(at)fredhutch(dot)org
>
> [image:
> http://www.fredhutch.org/content/dam/public/email-signatures/3/fred_hutch_logo.png]
> Fred Hutchinson Cancer Research Center
> 1100 Fairview Ave. N., Mail Stop E3-129
> Seattle, WA 98109
> *fredhutch.org <http://www.fredhutch.org/>*
>
>
>