From: Daniel Gustafsson <daniel@yesql.se>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_C4D8970F-0F10-49B1-8364-DCB1B6E9D5AF"
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\))
Subject: Fix projection of pg_type for Greenplum query
Message-Id: <7D9FFCE3-7670-4EDB-89B0-18E2B079E3E3@yesql.se>
Date: Tue, 23 Jul 2019 10:13:00 +0200
To: pgadmin-hackers@lists.postgresql.org
Precedence: bulk


--Apple-Mail=_C4D8970F-0F10-49B1-8364-DCB1B6E9D5AF
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

The bug in https://redmine.postgresql.org/issues/4179 seems to be due to
pg_type being projected from but not selected, the attached patch adds a =
FROM
clause entry and also adds projection of typname.  This makes the query =
work on
5X_STABLE for me.

cheers ./daniel


--Apple-Mail=_C4D8970F-0F10-49B1-8364-DCB1B6E9D5AF
Content-Disposition: attachment;
	filename=from_pg_type.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="from_pg_type.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=20a18d1948d845f1a7b6a3603543d0b951f93b5839=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20Daniel=20Gustafsson=20<daniel@yesql.se>=0A=
Date:=20Tue,=2023=20Jul=202019=2010:08:59=20+0200=0ASubject:=20[PATCH]=20=
Fix=20properties=20query=20for=20Greenplum=0A=0AThe=20query=20projected=20=
a=20column=20from=20pg_catalog.pg_class=20but=20didn't=0Ainclude=20it=20=
in=20the=20FROM=20clause.=20Fix=20by=20adding=20a=20LEFT=20OUTER=20JOIN=20=
on=0Athe=20catalog=20relation=20and=20also=20promote=20typname=20to=20a=20=
projection=20as=0Ait=20might=20be=20available.=0A=0AFixes=20=
https://redmine.postgresql.org/issues/4179=0A---=0A=20=
.../schemas/tables/templates/tables/sql/gpdb_5.0_plus/properties.sql=20=20=
=20|=203=20++-=0A=201=20file=20changed,=202=20insertions(+),=201=20=
deletion(-)=0A=0Adiff=20--git=20=
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templ=
ates/tables/sql/gpdb_5.0_plus/properties.sql=20=
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templ=
ates/tables/sql/gpdb_5.0_plus/properties.sql=0Aindex=20=
c690a1b8..ca7231ac=20100644=0A---=20=
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templ=
ates/tables/sql/gpdb_5.0_plus/properties.sql=0A+++=20=
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templ=
ates/tables/sql/gpdb_5.0_plus/properties.sql=0A@@=20-59,7=20+59,7=20@@=20=
FROM=20(=0A=20=09=09substring(array_to_string(tst.reloptions,=20',')=20=
FROM=20'autovacuum_freeze_table_age=3D([0-9]*)')=20AS=20=
toast_autovacuum_freeze_table_age,=0A=20=09=09=
array_to_string(rel.reloptions,=20',')=20AS=20table_vacuum_settings_str,=0A=
=20=09=09array_to_string(tst.reloptions,=20',')=20AS=20=
toast_table_vacuum_settings_str,=0A-=09=09rel.reloptions=20AS=20=
reloptions,=20tst.reloptions=20AS=20toast_reloptions,=20NULL=20AS=20=
reloftype,=20NULL=20AS=20typname,=0A+=09=09rel.reloptions=20AS=20=
reloptions,=20tst.reloptions=20AS=20toast_reloptions,=20NULL=20AS=20=
reloftype,=20typ.typname=20AS=20typname,=0A=20=09=09typ.typrelid=20AS=20=
typoid,=0A=20=09=09(CASE=20WHEN=20rel.reltoastrelid=20=3D=200=20THEN=20=
false=20ELSE=20true=20END)=20AS=20hastoasttable,=0A=20=09=09=09--=20=
Added=20for=20pgAdmin4=0A@@=20-79,6=20+79,7=20@@=20FROM=20(=0A=20=09=09=
LEFT=20OUTER=20JOIN=20pg_constraint=20con=20ON=20con.conrelid=3Drel.oid=20=
AND=20con.contype=3D'p'=0A=20=09=09LEFT=20OUTER=20JOIN=20pg_class=20tst=20=
ON=20tst.oid=20=3D=20rel.reltoastrelid=0A=20=09=09LEFT=20OUTER=20JOIN=20=
gp_distribution_policy=20gdp=20ON=20gdp.localoid=20=3D=20rel.oid=0A+=09=09=
LEFT=20OUTER=20JOIN=20pg_type=20typ=20ON=20typ.oid=20=3D=20rel.reltype=0A=
=20=0A=20=09=20WHERE=20rel.relkind=20IN=20('r','s','t')=20AND=20=
rel.relnamespace=20=3D=20{{=20scid=20}}=0A=20=09{%=20if=20tid=20%}=20=20=
AND=20rel.oid=20=3D=20{{=20tid=20}}::oid=20{%=20endif=20%}=0A--=20=0A=
2.14.1.145.gb3622a4ee=0A=0A=

--Apple-Mail=_C4D8970F-0F10-49B1-8364-DCB1B6E9D5AF--