Editing View Bug - Mailing list pgadmin-support
From | Albin, Lloyd P |
---|---|
Subject | Editing View Bug |
Date | |
Msg-id | AE011E7AE62117479360E1E2BD341F4EEB6DC1D9@adama.fhcrc.org Whole thread Raw |
Responses |
Re: Editing View Bug
|
List | pgadmin-support |
<div class="WordSection1"><p class="MsoNormal">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.<p class="MsoNormal"> <p class="MsoNormal">CREATETABLE x (y varchar, z int);<p class="MsoNormal">CREATE VIEW v AS SELECT DISTINCT ON (y) y, z FROMx ORDER BY y, z DESC;<p class="MsoNormal"> <p class="MsoNormal">Right click on the view and select Properties.<p class="MsoNormal"> <pclass="MsoNormal">Security barrier: Off<p class="MsoNormal">Check options: Blank or No<p class="MsoNormal">Definition:<p class="MsoNormal"> <p class="MsoNormal">Change the ORDER BY to y, z DESC, 1<p class="MsoNormal"> <pclass="MsoNormal"><SAVE><p class="MsoNormal"> <p class="MsoNormal">Error saving properties: INTERNALSERVER ERROR<p class="MsoNormal"> <p class="MsoNormal">ERROR: WITH CHECK OPTION is supported only on automaticallyupdatable views<p class="MsoNormal">HINT: Views containing DISTINCT are not automatically updatable.<p class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal">Looking at the SQL Tab, I see:<p class="MsoNormal"> <p class="MsoNormal">CREATEOR REPLACE VIEW public.v<p class="MsoNormal"> WITH (check_option=no, security_barrier=false)<pclass="MsoNormal"> AS<p class="MsoNormal"> SELECT DISTINCT ON (x.y) x.y,<p class="MsoNormal"> x.z<p class="MsoNormal"> FROM x<p class="MsoNormal"> ORDER BY x.y, x.z DESC, 1;<p class="MsoNormal"> <pclass="MsoNormal">1) According to the Postgres Docs for 9.4.x and 9.6.x no is not listed a valid optionfor check_option.<p class="MsoNormal">2) It appears that Postgres is checking for the DISCTINCT before seeing the check_option=no<pclass="MsoNormal">3) Since no does not appear to be valid per the docs, maybe pgAdmin 4 needs to excludethe WITH line if check_option is set to no.<p class="MsoNormal"> <p class="MsoNormal">Lloyd<p class="MsoNormal"> <pclass="MsoNormal" style="margin-top:12.0pt;line-height:11.25pt;background:white"><b><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:#123054">LloydAlbin</span></b><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:#123054"><br/> Database Administrator<br /> Statistical Centerfor HIV/AIDS Research and Prevention (SCHARP)<br /> Vaccine and Infectious Disease Division (VIDD)<br /> Fred HutchinsonCancer Research Center (FHCRC)<br /> lalbin@fredhutch.org <br /><br /></span><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:#123054"><img alt="http://www.fredhutch.org/content/dam/public/email-signatures/3/fred_hutch_logo.png"height="43" id="_x0000_i1025" src="http://www.fredhutch.org/content/dam/public/email-signatures/3/fred_hutch_logo.png"width="154" /></span><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:#123054"><br/> Fred Hutchinson Cancer Research Center<br />1100 Fairview Ave. N., Mail Stop E3-129<br /> Seattle, WA 98109<br /><b><a href="http://www.fredhutch.org/"><span style="color:#123054">fredhutch.org</span></a></b></span><pclass="MsoNormal"> </div>
pgadmin-support by date: