Home > mailing lists

pgadmin3 bug - Mailing list pgadmin-support

From	Marc Persuy
Subject	pgadmin3 bug
Date	August 19, 2003 13:16:11
Msg-id	002401c36653$ff703bd0$6800a8c0@marcxp Whole thread Raw
List	pgadmin-support

Tree view

Hello: here is a bug in pgadmin3.

I use a postgresQL 7.3.2 database, upon which I added some comments on view definition AND view attributes.

for instance :

---------------------------------------------------------

CREATE TABLE regions_sys (
    num_region integer DEFAULT nextval('num_region_seq'::text) NOT NULL,
    owner_tag integer,
    num_world_set integer,
    cd_pays character varying(2),
    cd_zone character varying(8),
    is_zone boolean,
);

CREATE OR REPLACE VIEW public.regions AS

SELECT sys.num_region, sys.num_world_set, sys.owner_tag, sys.cd_pays, sys.cd_zone, priv.valide, sys.is_zone,    FROM (regions_sys sys
   LEFT JOIN regions_priv_allies priv ON ((sys.num_region = priv.num_region )))
WHERE ((sys.owner_tag = ANY (
         SELECT allies.owner_tag
           FROM allies );

COMMENT ON VIEW public.regions IS 'Chacune des zones ainsi définies appartient à un « espace de travail » ou « world_set » qui peut être soit global (1 seul espace de travail global – ou longue distance – par compte client t2g) soit local (espaces locaux affinant une zone globale).';

COMMENT ON COLUMN regions.num_world_set IS 'Cf. world_set.num_world_set : espace de travail auquel appartient la région';
COMMENT ON COLUMN regions.cd_pays IS 'Valorisé pour toutes les régions incluses dans un pays';
COMMENT ON COLUMN regions.cd_zone IS 'Libellé court de la zone';
COMMENT ON COLUMN regions.is_zone IS 'Vrai si la région est définie en tant que zone dans un espace de travail global ou local';
-------------------------------------------

IN this case, pgadmin3 shows 5 entries for the view "regions" in the main tree in view list. 1 for each comment defined on the view, whithout filtering comments defined at the attribute level and not at the view level.

Suggested change :

in file pgView.cpp, lines 113 to 124 :

        wxT("SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, ")
               wxT("pg_get_viewdef(c.oid") + collection->GetDatabase()->GetPrettyOption() + wxT(") AS definition\n")
        wxT(" FROM pg_class c\n")
        wxT(" LEFT OUTER JOIN pg_description des ON des.objoid=c.oid\n")
        wxT(" WHERE ((c.relhasrules AND (EXISTS (\n")
        wxT("           SELECT r.rulename FROM pg_rewrite r\n")
        wxT("            WHERE ((r.ev_class = c.oid)\n")
        wxT("              AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))\n")
        wxT("   AND relnamespace = ") + collection->GetSchema()->GetOidStr() + wxT("\n")
        + restriction
        + wxT(" ORDER BY relname"));

should be replaced by :

        wxT("SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, ")
               wxT("pg_get_viewdef(c.oid") + collection->GetDatabase()->GetPrettyOption() + wxT(") AS definition\n")
        wxT(" FROM pg_class c\n")
        wxT(" LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0)\n")
        wxT(" WHERE ((c.relhasrules AND (EXISTS (\n")
        wxT("           SELECT r.rulename FROM pg_rewrite r\n")
        wxT("            WHERE ((r.ev_class = c.oid)\n")
        wxT("              AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))\n")
        wxT("   AND relnamespace = ") + collection->GetSchema()->GetOidStr() + wxT("\n")
        + restriction
        + wxT(" ORDER BY relname"));

But, I should recognize that, despite being supported by PostgresQL 7.3.2, I'm not sure that this view condition is proper sql.

If not, to maintain the left outer join from view def with view description, one should define an intermediate view on table pg_description.

Similarly, commenting view columns is supported by current product, but in fact an undocumented feature.

pgadmin-support by date:

From: "Dave Page"
Date: 19 August 2003, 11:02:27
Subject: Re: Nonexistent URL on the pgAdmin3 website

From: "Dave Page"
Date: 19 August 2003, 14:00:27
Subject: Re: pgadmin3 bug

pgadmin3 bug - Mailing list pgadmin-support

Previous

Next