ERROR: must be owner of language plpgsql

Lee Hambley picture Lee Hambley · Dec 6, 2010 · Viewed 23.1k times · Source

I'm using PostgreSQL v9.0.1 with Rails (and it's deps) @ v2.3.8, owing to the use of the fulltext capability of postgres, I have a table which is defined as:

CREATE TABLE affiliate_products (
    id integer NOT NULL,
    name character varying(255),
    model character varying(255),
    description text,
    price numeric(9,2),
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    textsearch_vector tsvector,
);

Note the last line, this ensures that active record isn't able to process it with the standard schema dumper, so I have to set config.active_record.schema_format = :sql in ./config/environment.rb; and use rake db:test:clone_structure instead of rake db:test:clone.

None of this is too remarkable, only inconvenient - however rake db:test:clone_structure fails with the error:

ERROR: must be owner of language plpgsql

Because of line #16 in my resulting ./db/development_schema.sql:

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;

Under PostgreSQL v9.0+ the language plpsql is installed by the superuser, to the initial template, which is then available to the newly created schema.

I cannot run tests on this project without resolving this, and even editing ./db/development_schema.sql manually is futile as it is regenerated every time I run rake db:test:clone_structure (and ignored by rake db:test:clone).

I hope someone can shed some light on this?

Note: I have used both the pg 0.9.0 adapter gem, and the postgres gem at version 0.7.9.2008.01.28 - both display identical behaviour.

My teammates run PostgreSQL v8.4 where the language installation is a manual step.

Answer

David Dehghan picture David Dehghan · Sep 25, 2011

I had the same problem. I fixed my template with the commands below

psql template1
template1=# alter role my_user_name with superuser;

read more at http://gilesbowkett.blogspot.com/2011/07/error-must-be-owner-of-language-plpgsql.html