MIME-Version: 1.0
In-Reply-To: <E1bkqKH-0004kP-EO@gothos.postgresql.org>
References: <E1bkqKH-0004kP-EO@gothos.postgresql.org>
From: Magnus Hagander <magnus@hagander.net>
Date: Tue, 20 Sep 2016 07:48:02 -0500
Message-ID: 
 <CABUevEw+OG5-4mkt1YZ4GqoGRiJrT0Nm_m4XKWKqdzyYEg0dGQ@mail.gmail.com>
Subject: Re: pgAdmin 4 commit: Add classid filter to queries
 on pg_depend. Fixes #17
To: Dave Page <dpage@pgadmin.org>
Cc: pgadmin-hackers <pgadmin-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary=001a113df2aaf38c73053cefd6fc
Precedence: bulk
Sender: pgadmin-hackers-owner@postgresql.org

--001a113df2aaf38c73053cefd6fc
Content-Type: text/plain; charset=UTF-8

Thanks!

I'll push a backpatch of this to pgadmin3 as well, just in case somebody
wants to build their own version even if there are no more official
releases.

In working on this I also noticed that we fairly often use the construct of
"(select oid from pg_class where relname=xxx)" instead of using
"::regclass". This could cause issues if somebody creates a table with the
same name in a different schema, for example. While not the highest
priority, it might be a good idea to go over the queries and replace those
patterns (and if we're doing that, it's probably not a bad idea to fully
qualify them as well, as we don in psql). There's also a couple of cases
where oid's are hardcoded, which is probably also a bad practice that needs
to be taken care of.

//Magnus


On Fri, Sep 16, 2016 at 5:24 AM, Dave Page <dpage@pgadmin.org> wrote:

> Add classid filter to queries on pg_depend. Fixes #1705
>
> There are a number of cases where queries in both pgadmin4 and pgadmin3
> are done against pg_depend but falis to constrain the query on classid.
>
> In particular, if for example a constraint and a sequence exists with the
> same oid (which is perfectly valid, as they are in different tables), a
> column will suddenly refer sequences that are completely incorrect. When we
> look up sequences, we have to make sure we only match dependencies against
> pg_class.
>
> Branch
> ------
> master
>
> Details
> -------
> http://git.postgresql.org/gitweb?p=pgadmin4.git;a=commitdiff;h=
> ce20dbc25a393004c5249d946f9a85a691cacd48
> Author: Magnus Hagander <magnus@hagander.net>
>
> Modified Files
> --------------
> .../columns/templates/catalog_object_column/sql/9.1_plus/properties.sql |
> 2 +-
> .../schemas/tables/templates/column/sql/9.1_plus/is_referenced.sql      |
> 1 +
> .../databases/schemas/tables/templates/column/sql/9.1_plus/nodes.sql    |
> 2 +-
> .../schemas/tables/templates/column/sql/9.1_plus/properties.sql         |
> 2 +-
> .../schemas/tables/templates/column/sql/9.2_plus/is_referenced.sql      |
> 1 +
> .../databases/schemas/tables/templates/column/sql/9.2_plus/nodes.sql    |
> 2 +-
> .../schemas/tables/templates/column/sql/9.2_plus/properties.sql         |
> 2 +-
> 7 files changed, 7 insertions(+), 5 deletions(-)
>
>
> --
> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-hackers
>

--001a113df2aaf38c73053cefd6fc
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thanks!<div><br></div><div>I&#39;ll push a backpatch of th=
is to pgadmin3 as well, just in case somebody wants to build their own vers=
ion even if there are no more official releases.</div><div><br></div><div>I=
n working on this I also noticed that we fairly often use the construct of =
&quot;(select oid from pg_class where relname=3Dxxx)&quot; instead of using=
 &quot;::regclass&quot;. This could cause issues if somebody creates a tabl=
e with the same name in a different schema, for example. While not the high=
est priority, it might be a good idea to go over the queries and replace th=
ose patterns (and if we&#39;re doing that, it&#39;s probably not a bad idea=
 to fully qualify them as well, as we don in psql). There&#39;s also a coup=
le of cases where oid&#39;s are hardcoded, which is probably also a bad pra=
ctice that needs to be taken care of.</div><div><br></div><div>//Magnus</di=
v><div><br><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">On Fri=
, Sep 16, 2016 at 5:24 AM, Dave Page <span dir=3D"ltr">&lt;<a href=3D"mailt=
o:dpage@pgadmin.org" target=3D"_blank">dpage@pgadmin.org</a>&gt;</span> wro=
te:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-=
left:1px #ccc solid;padding-left:1ex">Add classid filter to queries on pg_d=
epend. Fixes #1705<br>
<br>
There are a number of cases where queries in both pgadmin4 and pgadmin3 are=
 done against pg_depend but falis to constrain the query on classid.<br>
<br>
In particular, if for example a constraint and a sequence exists with the s=
ame oid (which is perfectly valid, as they are in different tables), a colu=
mn will suddenly refer sequences that are completely incorrect. When we loo=
k up sequences, we have to make sure we only match dependencies against pg_=
class.<br>
<br>
Branch<br>
------<br>
master<br>
<br>
Details<br>
-------<br>
<a href=3D"http://git.postgresql.org/gitweb?p=3Dpgadmin4.git;a=3Dcommitdiff=
;h=3Dce20dbc25a393004c5249d946f9a85a691cacd48" rel=3D"noreferrer" target=3D=
"_blank">http://git.postgresql.org/<wbr>gitweb?p=3Dpgadmin4.git;a=3D<wbr>co=
mmitdiff;h=3D<wbr>ce20dbc25a393004c5249d946f9a85<wbr>a691cacd48</a><br>
Author: Magnus Hagander &lt;<a href=3D"mailto:magnus@hagander.net">magnus@h=
agander.net</a>&gt;<br>
<br>
Modified Files<br>
--------------<br>
.../columns/templates/catalog_<wbr>object_column/sql/9.1_plus/<wbr>properti=
es.sql | 2 +-<br>
.../schemas/tables/templates/<wbr>column/sql/9.1_plus/is_<wbr>referenced.sq=
l=C2=A0 =C2=A0 =C2=A0 | 1 +<br>
.../databases/schemas/tables/<wbr>templates/column/sql/9.1_plus/<wbr>nodes.=
sql=C2=A0 =C2=A0 | 2 +-<br>
.../schemas/tables/templates/<wbr>column/sql/9.1_plus/<wbr>properties.sql=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2 +-<br>
.../schemas/tables/templates/<wbr>column/sql/9.2_plus/is_<wbr>referenced.sq=
l=C2=A0 =C2=A0 =C2=A0 | 1 +<br>
.../databases/schemas/tables/<wbr>templates/column/sql/9.2_plus/<wbr>nodes.=
sql=C2=A0 =C2=A0 | 2 +-<br>
.../schemas/tables/templates/<wbr>column/sql/9.2_plus/<wbr>properties.sql=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2 +-<br>
7 files changed, 7 insertions(+), 5 deletions(-)<br>
<span class=3D"HOEnZb"><font color=3D"#888888"><br>
<br>
--<br>
Sent via pgadmin-hackers mailing list (<a href=3D"mailto:pgadmin-hackers@po=
stgresql.org">pgadmin-hackers@postgresql.<wbr>org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgadmin-hackers" rel=3D"noref=
errer" target=3D"_blank">http://www.postgresql.org/<wbr>mailpref/pgadmin-ha=
ckers</a><br>
</font></span></blockquote></div><br>
<br></div></div></div>

--001a113df2aaf38c73053cefd6fc--