Обсуждение: Howto see template objects in PGAdmin3
PG-9.2 PGAdmin3-1.16.0 I have run into an old problem wherein the postgres user has ownership of the PSQL language extension in a newly created database. I wish to remove this extension from template1 as the database is automatically recreated on every test run and I am unable to specify template0 to the test software. I am attempting to do this from inside PGAdmin3 but I find that I no longer see either template0 or template1 in the object tree. Where are they found? --=20 *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
On Tue, 2013-02-12 at 15:32 -0500, James B. Byrne wrote: > PG-9.2 > PGAdmin3-1.16.0 > > I have run into an old problem wherein the postgres user has ownership > of the PSQL language extension in a newly created database. I wish to > remove this extension from template1 as the database is automatically > recreated on every test run and I am unable to specify template0 to > the test software. > > I am attempting to do this from inside PGAdmin3 but I find that I no > longer see either template0 or template1 in the object tree. Where > are they found? > You first need to check the "Show System Objects in the treeview" in the Options dialog. Then, they'll be available in the Databases node of the treeview. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Thu, February 14, 2013 06:23, Guillaume Lelarge wrote: > On Tue, 2013-02-12 at 15:32 -0500, James B. Byrne wrote: >> PG-9.2 >> PGAdmin3-1.16.0 >> >> I am attempting to do this from inside PGAdmin3 but I find that I no >> longer see either template0 or template1 in the object tree. Where >> are they found? >> > > You first need to check the "Show System Objects in the treeview" > in the Options dialog. Then, they'll be available in the Databases > node of the treeview. > Thank you. With your help I now can view template1 in PGAdmin3. I notice that the extension in question, plpgsql, has no owner in template1 and yet I still receive this error when attempting to build a new test database from a pgdump: -- -- PostgreSQL database dump -- SET statement_timeout =3D 0; SET client_encoding =3D 'UTF8'; SET standard_conforming_strings =3D on; SET check_function_bodies =3D false; SET client_min_messages =3D warning; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: - -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; . . . ERROR: must be owner of extension plpgsql Is this a bug? --=20 *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
On 2/14/2013 7:06 AM, James B. Byrne wrote: > CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; plpgsql is a LANGUAGE, not an extension, I thought? you use CREATE LANGUAGE to enable it.. -- john r pierce 37N 122W somewhere on the middle of the left coast
On 02/14/2013 03:11 PM, John R Pierce wrote: > On 2/14/2013 7:06 AM, James B. Byrne wrote: >> CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; > > plpgsql is a LANGUAGE, not an extension, I thought? > > you use CREATE LANGUAGE to enable it.. > http://www.postgresql.org/docs/9.2/interactive/sql-createlanguage.html Note: As of PostgreSQL 9.1, most procedural languages have been made into "extensions", and should therefore be installed with CREATE EXTENSION not CREATE LANGUAGE. Direct use of CREATE LANGUAGE should now be confined to extension installation scripts. If you have a "bare" language in your database, perhaps as a result of an upgrade, you can convert it to an extension using CREATE EXTENSION langname FROM unpackaged. -- Adrian Klaver adrian.klaver@gmail.com