错误:必须是语言plpgsql的所有者

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:

我使用PostgreSQL v9.0.1和Rails(它是deps) @ v2.3.8，由于使用了postgres的全文功能，我有一个表定义为:

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.

注意最后一行，这确保了活动记录不能使用标准模式dumper处理它，因此我必须设置config.active_record。schema_format =:sql in ./config/environment.rb;使用rake db:test:clone_structure而不是rake db:test:clone。

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

但是rake db:test:clone_structure在错误中失败了:

ERROR: must be owner of language plpgsql

错误:必须是语言plpgsql的所有者

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

因为在我的结果中第16行。

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;

创建或替换过程语言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.

在PostgreSQL v9.0+下，超级用户将把语言plpsql安装到初始模板，然后新创建的模式可以使用该模板。

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).

如果不解决这个问题，甚至不编辑./db/development_schema，我就不能在这个项目上运行测试。每次运行rake db:test:clone_structure(被rake db:test:clone)时，都会重新生成sql，所以手工sql是无效的。

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.

注意:我使用了pg 0.9.0适配器gem，以及版本0.7.9.2008.01.28的postgres gem——它们都显示了相同的行为。

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

我的团队成员运行PostgreSQL v8.4，其中语言安装是手动步骤。

5 个解决方案

#1

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

阅读更多:http://gilesbowkett.blogspot.com/2011/07/error-must-be-owner-of-language-plpgsql.html

#2

For new readers, I read this older post after having run into this error in one of my own projects. I strongly feel that giving the app's PostgreSQL a superuser role is a terrible idea and changing the template is not ideal either. Since the referenced PSQL commands that are added by db:structure:dump are not needed by the Rails app's database, I have written a custom rake task that comments out the problematic lines in structure.sql. I have shared that code publicly on Github as a Gist at https://gist.github.com/rietta/7898366.

对于新读者，我在自己的一个项目中遇到这个错误后阅读了这篇旧文章。我强烈认为，给应用程序的PostgreSQL一个超级用户角色是一个糟糕的想法，改变模板也不理想。由于db:structure:dump不需要Rails应用程序的数据库，所以添加了引用的PSQL命令，所以我编写了一个自定义rake任务，在structure.sql中注释有问题的行。我在Github上公开分享了这段代码，作为https://github.com/rietta/7898366的一个要点。

#3

The solution was as follows:

解决办法如下:

On my installation, there are standard templates template0 and template1 - at least as I understand it postgres will look for the highest numbered templateN when creating a new database, unless the template is specified.

在我的安装中，有标准模板template0和template1——至少据我所知，postgres将在创建新数据库时查找编号最高的模板，除非指定了模板。

In this instance, as template0 included plpgsql, so did template1… the idea being that you will customise template1 to suite your site specific default needs, and in the case that you blow everything up, you would restore template1 from template0.

在这个例子中，因为template0包含plpgsql，所以template1也包含…您将定制template1来满足您的站点特定的默认需求，如果您将所有内容都搞砸，您将从template0恢复template1。

As my site specific requirement was to install plpgsql as part of the automated build of my web application (a step we had to keep to maintain 8.4 compatibility) - the solution was easy: remove plpgsql from template1 and the warning/error went away.

由于我的站点特定的需求是将plpgsql作为web应用程序自动构建的一部分(我们必须保持8.4兼容性)，所以解决方案很简单:从template1删除plpgsql，然后警告/错误就消失了。

In the case that the site-specific defaults would change, and we should need to go back to the default behaviour, we would simply remove template1 and recreate it (which would use template0)

如果站点特定的默认值会改变，并且我们应该返回到默认行为，我们只需删除template1并重新创建它(使用template0)

#4

I encountered this error while attempting to do RAILS_ENV=development bundle exec rake db:reset. I was able to accomplish the same thing (for my purposes) by doing RAILS_ENV=development bundle exec rake db:drop db:create db:migrate instead.

我在尝试执行RAILS_ENV=development bundle exec rake db:reset时遇到了这个错误。通过执行RAILS_ENV=development bundle exec rake db:drop db:create db:migrate db: migration，我可以完成同样的事情(出于我的目的)。

#5

I just filter the plpgsql extension statements from the structure.sql file post-dump:

我只是过滤了结构中的plpgsql扩展语句。sql文件post-dump:

# lib/tasks/db.rake

namespace :db do
  desc "Fix 'ERROR:  must be owner of extension plpgsql' complaints from Postgresql"
  task :fix_psql_dump do |task|
    filename = ENV['DB_STRUCTURE'] || File.join(ActiveRecord::Tasks::DatabaseTasks.db_dir, "structure.sql")
    sql = File.read(filename)
    sql.sub!(/(CREATE EXTENSION IF NOT EXISTS plpgsql)/, '-- \1')
    sql.sub!(/(COMMENT ON EXTENSION plpgsql)/, '-- \1')
    File.open(filename, 'w') do |f|
      f.write(sql)
    end
    task.reenable
  end
end

Rake::Task["db:structure:dump"].enhance do
  Rake::Task["db:fix_psql_dump"].invoke
end