contribute pg_get_viewdef2 et al - Mailing list pgadmin-hackers

Dave Page wrote:

>After a couple of releases of PostgreSQL you will start to see why a bit
>more clearly :-)
>
>Basically, things tend to get far more messy with every release because
>things in the database get moved around, new catalogues are added and
>old ones deprecated etc. In pgAdmin I, we used to make extensive use of
>functions and views on the server. They became an absolute nightmare to
>maintain requiring increasingly complex code to check and recreate them
>as required because we could never be sure that a user would
>upgrade/reinstall them following a server upgrade, or that they hadn't
>fiddled with them. Of course, that's ignoring the fact that each part of
>the system that could use alternate code got increasingly more complex,
>and you still ended up writing to the lowest common denominator anyway.
>
>
I can imagine... That's why I didn't rely on the existence of the
function, but have a fully functional fallback solution.

>Tom is being cautious and to be honest I'm on his side on this one. If a
>release goes out including functions used in the dumping of databases
>that do not work quite as they should because a few parentheses are
>missing, that'll be a major blot in PostgreSQL's reputation and is bound
>to spark up debate in places like /. where the MySQL fans will finally
>have something negative to say about PostgreSQL that is actually valid.
>
Yeah. All functions pgsql delivers must be 150 %, to keep the advantage
of reliability feature stability and maturity. I accept this as major
design goal; the MySQL guys should stay in BCCDWC-land
(By-Chance-Correct-Data-Who-Cares). That's why I proposed to take
STATEMENT TRIGGERS off the 7.4 announced features list (declaring them
experimental), because they aren't implemented completely (dataset
unknown to trigger function, provoking LOLs from other DBMS gurus). I
need these triggers soon, and eventually I'll have a look at completing
them myself. But so far, I'm in pgadmin3.

>
>I would suggest submitting your functions as patches to the backend that
>do all the reformatting but do not mess with the parentheses. I suspect
>this is the only way you'll get the code into either the main system or
>/contrib. Nobody seemed averse to that idea when I mentioned it on the
>list a while ago.
>
Well that doesn't make sense at all. Indentation and line formatting can
be done quite well on the client side, as it is implemented now. It
can't be done any better in the backend (except for exotic conditions,
e.g. distinguishing the ON SELECT/INSERT/UPDATE/DELETE TO expression in
pg_get_ruledef2 from the statement).
On the other side, parentheses optimization can't be done on the client
side without a complete sql parser (we don't want this in pgsql3, I
presume?), but it's easy with the backend's internal node evaluation.

Wanna see some parentheses orgy? This is real life:

pg_get_viewdef version:

SELECT ...
 FROM (((((((((((((pt_partner par
   JOIN pr_liste_partner rpl  ON (((par.partner_nr )::numeric = (rpl.partner_nr )::numeric)))
   JOIN pt_liste ptl  ON (((ptl.liste_nr )::numeric = (rpl.liste_nr )::numeric)))
   JOIN pv_anrede pva  ON (((par.partner_nr )::numeric = (pva.partner_nr )::numeric)))
   LEFT JOIN  pr_partner_adresse rpa2  ON ((((par.partner_nr )::numeric = (rpa2.partner_nr )::numeric) AND
((rpa2.prior_adresse)::smallint = 1 ))))  
   LEFT JOIN  pv_adresse adr2  ON (((rpa2.adresse_nr )::numeric = (adr2.adresse_nr )::numeric)))
   LEFT JOIN  pd_staat sta2  ON (((adr2.key_staat )::text = (sta2.key_staat )::text)))
   LEFT JOIN  ((pr_partner_komm rpe
        JOIN pt_komm kom1  ON ((((rpe.komm_nr )::numeric = (kom1.komm_nr )::numeric) AND ((kom1.key_kommtyp )::integer
=8 ))))  
        JOIN pj_komm_email ema  ON (((rpe.komm_nr )::numeric = (ema.komm_nr )::numeric)))
        ON ((((par.partner_nr )::numeric = (rpe.partner_nr )::numeric) AND ((rpe.prior_email )::smallint = 1 ))))
   LEFT JOIN  (((pr_partner_komm rpt
        JOIN pt_komm kom2  ON ((((rpt.komm_nr )::numeric = (kom2.komm_nr )::numeric) AND (((((((kom2.key_kommtyp
)::integer= 1 ) OR ((kom2.key_kommtyp )::integer = 3 )) OR ((kom2.key_kommtyp )::integer = 4 )) OR ((kom2.key_kommtyp
)::integer= 5 )) OR ((kom2.key_kommtyp )::integer = 7 )) OR ((kom2.key_kommtyp )::integer = 12 )))))  
        JOIN pj_komm_telefon tel  ON (((rpt.komm_nr )::numeric = (tel.komm_nr )::numeric)))
        JOIN pd_staat stt  ON (((tel.key_staat )::text = (stt.key_staat )::text)))
        ON ((((par.partner_nr )::numeric = (rpt.partner_nr )::numeric) AND ((rpt.prior_tel )::smallint = 1 ))))
   LEFT JOIN  (((pr_partner_komm rpf
        JOIN pt_komm kom3  ON ((((rpf.komm_nr )::numeric = (kom3.komm_nr )::numeric) AND ((((kom3.key_kommtyp
)::integer= 2 ) OR ((kom3.key_kommtyp )::integer = 6 )) OR ((kom3.key_kommtyp )::integer = 12 )))))  
        JOIN pj_komm_telefon fax  ON (((rpf.komm_nr )::numeric = (fax.komm_nr )::numeric)))
        JOIN pd_staat stf  ON (((fax.key_staat )::text = (stf.key_staat )::text)))
        ON ((((par.partner_nr )::numeric = (rpf.partner_nr )::numeric) AND ((rpf.prior_fax )::smallint = 1 ))))
   LEFT JOIN  pr_partner_partner prpp  ON (((((prpp.partner_nr2 )::numeric = (par.partner_nr )::numeric) AND
((prpp.prior_partner)::smallint = 1 )) AND (prpp.key_partnerrolle IN ( 
         SELECT pd_partnerrolle.key_partnerrolle
           FROM pd_partnerrolle
          WHERE (pd_partnerrolle.key_rollentyp IN (
                 SELECT pd_rollentyp.key_rollentyp
                   FROM pd_rollentyp
                  WHERE ((((pd_rollentyp.key_rollengruppe )::integer = 2 ) OR ((pd_rollentyp.key_rollengruppe
)::integer= 3 )) OR ((pd_rollentyp.key_rollengruppe )::integer = 4 )))))))))  
                   LEFT JOIN  pv_anrede pva2  ON (((pva2.partner_nr )::numeric = (prpp.partner_nr )::numeric)))
                   LEFT JOIN  pr_partner_adresse rpa3  ON ((((pva2.partner_nr )::numeric = (rpa3.partner_nr )::numeric)
AND((rpa3.prior_adresse )::smallint = 1 ))))  
                   LEFT JOIN  pv_adresse pvadr2  ON (((pvadr2.adresse_nr )::numeric = (rpa3.adresse_nr )::numeric)))
                  WHERE ((par.key_partnertyp )::integer = 1 )));


as opposed to pg_get_viewdef2 version:

SELECT ....
   FROM pt_partner par
   JOIN pr_liste_partner rpl  ON par.partner_nr::numeric = rpl.partner_nr::numeric
   JOIN pt_liste ptl  ON ptl.liste_nr::numeric = rpl.liste_nr::numeric
   JOIN pv_anrede pva  ON par.partner_nr::numeric = pva.partner_nr::numeric
   LEFT JOIN  pr_partner_adresse rpa2  ON par.partner_nr::numeric = rpa2.partner_nr::numeric AND
rpa2.prior_adresse::smallint= 1  
   LEFT JOIN  pv_adresse adr2  ON rpa2.adresse_nr::numeric = adr2.adresse_nr::numeric
   LEFT JOIN  pd_staat sta2  ON adr2.key_staat::text = sta2.key_staat::text
   LEFT JOIN  (pr_partner_komm rpe
        JOIN pt_komm kom1  ON rpe.komm_nr::numeric = kom1.komm_nr::numeric AND kom1.key_kommtyp::integer = 8
        JOIN pj_komm_email ema  ON rpe.komm_nr::numeric = ema.komm_nr::numeric )
        ON par.partner_nr::numeric = rpe.partner_nr::numeric AND rpe.prior_email::smallint = 1
   LEFT JOIN  (pr_partner_komm rpt
        JOIN pt_komm kom2  ON rpt.komm_nr::numeric = kom2.komm_nr::numeric AND (((((kom2.key_kommtyp::integer = 1 OR
kom2.key_kommtyp::integer= 3 ) OR kom2.key_kommtyp::integer = 4 ) OR kom2.key_kommtyp::integer = 5 ) OR
kom2.key_kommtyp::integer= 7 ) OR kom2.key_kommtyp::integer = 12 )  
        JOIN pj_komm_telefon tel  ON rpt.komm_nr::numeric = tel.komm_nr::numeric
        JOIN pd_staat stt  ON tel.key_staat::text = stt.key_staat::text )
        ON par.partner_nr::numeric = rpt.partner_nr::numeric AND rpt.prior_tel::smallint = 1
   LEFT JOIN  (pr_partner_komm rpf
        JOIN pt_komm kom3  ON rpf.komm_nr::numeric = kom3.komm_nr::numeric AND ((kom3.key_kommtyp::integer = 2 OR
kom3.key_kommtyp::integer= 6 ) OR kom3.key_kommtyp::integer = 12 )  
        JOIN pj_komm_telefon fax  ON rpf.komm_nr::numeric = fax.komm_nr::numeric
        JOIN pd_staat stf  ON fax.key_staat::text = stf.key_staat::text )
        ON par.partner_nr::numeric = rpf.partner_nr::numeric AND rpf.prior_fax::smallint = 1
   LEFT JOIN  pr_partner_partner prpp  ON (prpp.partner_nr2::numeric = par.partner_nr::numeric AND
prpp.prior_partner::smallint= 1 ) AND prpp.key_partnerrolle IN ( 
         SELECT pd_partnerrolle.key_partnerrolle
           FROM pd_partnerrolle
          WHERE pd_partnerrolle.key_rollentyp IN (
                 SELECT pd_rollentyp.key_rollentyp
                   FROM pd_rollentyp
                  WHERE (pd_rollentyp.key_rollengruppe::integer = 2 OR pd_rollentyp.key_rollengruppe::integer = 3 ) OR
pd_rollentyp.key_rollengruppe::integer= 4 ))  
                   LEFT JOIN  pv_anrede pva2  ON pva2.partner_nr::numeric = prpp.partner_nr::numeric
                   LEFT JOIN  pr_partner_adresse rpa3  ON pva2.partner_nr::numeric = rpa3.partner_nr::numeric AND
rpa3.prior_adresse::smallint= 1  
                   LEFT JOIN  pv_adresse pvadr2  ON pvadr2.adresse_nr::numeric = rpa3.adresse_nr::numeric
                  WHERE par.key_partnertyp::integer = 1 ));



The second version will create exactly the same plan as the first, and
this view isn't the largest view I have to deal with...
As you can see, there are still some parentheses which could be omitted
(e.g. with multiple bool expressions), but the defensive strategy says
"if not sure, put them in". It would even be desirable to reduce the
number of casts, but unfortunately implicit and explicit casts cannot be
distinguished so that would be really unsafe.

Still, the latter is the second best version: retrieving the original
source including formatting and comments is what I really want.

Regards,
Andreas