Re: Editing View Bug

On Thu, Nov 3, 2016 at 12:30 AM, Albin, Lloyd P <lalbin@scharp.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@fredhutch.org


Fred Hutchinson Cancer Research Center
1100 Fairview Ave. N., Mail Stop E3-129
Seattle, WA 98109
fredhutch.org