Re: Need help extripating plpgsql

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(at)harte-lyne(dot)ca> 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(dot)klaver(at)gmail(dot)com