On 02/21/2013 12:14 PM, James B. Byrne wrote:
On Thu, February 21, 2013 13:23, Merlin Moncure wrote:
On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne
<byrnejb@xxxxxxxxxxxxx> wrote:
On Thu, February 21, 2013 12:38, James B. Byrne wrote:
I am trying, without success, to create a PG-9.2 database without
including the plpgsql extension. I have tried specifying template0
and the database is nonetheless created with plpgsql. I have
deleted plpgsql from template1 and the new database is
nonetheless created with plpgsql.
I desire to remove plpgsql from newly created databases because the
dump that is generated by pgdump contains this line:
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
Wrong line. This is the line
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
And yes, I went through this a year ago with PG-9.1 and resolved it
once by switching to template0 in the connection configuration. Now
it is back with PG-9.2. using the exact same configuration code
because evidently plpgsql is added regardless.
curious why you want to do this. there was actually some debate back
in the day about pros/cons of having pl/pgsql be a built-in feature,
which as you can see is where things are going.
I want to do this because my automated test harness is choking because
it cannot add an absolutely worthless COMMENT to that extension. It
cannot add the comment because the language extension is added to the
database with an incorrect owner. A database created by userid X
should, in ALL RESPECTS, be OWNED by userid X. When the ownership of
database Y is changed from user A to user B then all of the attributes
of database Y should become owned by B. For some reason this is not
the case with the plpgsql language extension.
if you don't mind surgery with a shotgun, you can simply drop the
extension after the load resolves.
I have tried this and it does not work. It does not work for the
simple reason that the test harness recreates the test database from
the dump file each run. The dump file is created with a COMMENT
statement which cannot be applied to the plpgsql language extension
statement unless the user that connects to the database is a
superuser. That condition makes the granting of DBCREATE to another
userid somewhat pointless.
I have resolved this by:
1. as the postgres user creating a copy of template1 (template2)
2. as the postgres user assigning the test userid as owner of template2
3. as the postgres user dropping the plpgsql extension from template2
4. as the test user adding the plpgsql extension to template2
5. specifying template2 in the database connection configuration file.
template1=# \dL
List of languages
Name | Owner | Trusted | Description
-----------+----------+---------+------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
plpythonu | postgres | f |
(2 rows)
template1=# alter language plpgsql owner to aklaver;
ALTER LANGUAGE
template1=# \dL
List of languages
Name | Owner | Trusted | Description
-----------+----------+---------+------------------------------
plpgsql | aklaver | t | PL/pgSQL procedural language
plpythonu | postgres | f |
(2 rows)
The current arrangement is not really satisfactory as it requires
either separate template databases for each userid granted the
DBCREATE role or the superuser role has to be granted in place of the
DBCREATE role.
That is sort of the point of the template system, different templates
for different situations.
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
