sql - tsvector_update_trigger 找不到 tsvector 列

Question

我正在使用 Ruby on Rails 开发一个非常简单的博客，目前正在使用 pg_search 实现搜索功能以进行 PostgreSQL 全文搜索。不幸的是，我在更新 posts 表中的 tsvector 列时遇到了问题(我相信)。经过几个小时的搜索，我无法自己解决问题 - 尽管我怀疑如果您以前做过类似的事情会很容易。

无论如何，错误内容如下:

irb(main):001:0> post = FactoryGirl.build :post
=> #<Post id: nil, title: "Optimized uniform infrastructure", body: "<p>Inventore consectetur culpa nulla eius voluptati...", published: true, created_at: "2012-09-09  04:08:51", updated_at: nil, tsv: nil>
irb(main):002:0> post.save
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "tsv" does not exist
    LINE 1: SELECT tsvector_update_trigger(tsv, 'pg_catalog.english', ti...
    QUERY:  SELECT tsvector_update_trigger(tsv, 'pg_catalog.english', title, body)
    CONTEXT:  PL/pgSQL function "posts_before_insert_update_row_tr" line 3 at assignment
    : INSERT INTO "posts" ("body", "created_at", "published", "title", "updated_at") VALUES
    ($1, $2, $3, $4, $5) RETURNING "id"

tsv 列是在这样的迁移中创建的:

class AddTsvToPosts < ActiveRecord::Migration
    def change
        add_column :posts, :tsv, :tsvector
        add_index(:posts, :tsv, using: 'gin')
    end
end

触发器是使用 hair_trigger 定义的这样:

class Post < ActiveRecord::Base
  include PgSearch

  pg_search_scope :search, against: [:title, :body],
    using: {
      tsearch: {
        dictionary: 'english',
        prefix: true,
        tsvector_column: 'tsv'
      }
    }

  trigger.before(:insert, :update) do
    "new.tsv := tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);"
  end
end

整个过程产生以下 sql 模式(使用 config.active_record.schema_format = :sql)-- -- PostgreSQL 数据库转储 --

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = 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';


SET search_path = public, pg_catalog;

--
-- Name: posts_before_insert_update_row_tr(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION posts_before_insert_update_row_tr() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    new.tsv := tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);
    RETURN NEW;
END;
$$;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: posts; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE posts (
    id integer NOT NULL,
    title character varying(255),
    body text,
    published boolean,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    tsv tsvector
);


--
-- Name: posts_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE posts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: posts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE posts_id_seq OWNED BY posts.id;


--
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE schema_migrations (
    version character varying(255) NOT NULL
);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY posts ALTER COLUMN id SET DEFAULT nextval('posts_id_seq'::regclass);


--
-- Name: posts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY posts
    ADD CONSTRAINT posts_pkey PRIMARY KEY (id);


--
-- Name: index_posts_on_tsv; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_posts_on_tsv ON posts USING gin (tsv);


--
-- Name: unique_schema_migrations; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE UNIQUE INDEX unique_schema_migrations ON schema_migrations USING btree (version);


--
-- Name: posts_before_insert_update_row_tr; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER posts_before_insert_update_row_tr BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE posts_before_insert_update_row_tr();


--
-- PostgreSQL database dump complete
--

SET search_path TO "$user",public;

INSERT INTO schema_migrations (version) VALUES ('20131230213035');

INSERT INTO schema_migrations (version) VALUES ('20140115101632');

INSERT INTO schema_migrations (version) VALUES ('20140115183846');

如果有人能帮我弄清楚为什么 rails 不承认 tsv 列的存在，我会soooooo 高兴! :)

Answer 1

If someone could help me to figure out on why rails doesn't acknowledges the existance of the tsv column

这不是 rails，而是这段代码:

   new.tsv := tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

除了无法解释此上下文中的 tsv 的句法问题外，真正令人担忧的是 tsvector_update_trigger 返回 trigger，而不是 tsvector，因此它应该由 SQL 引擎在插入或更新后调用，而不是由用户代码显式调用。

事实上，之所以使用预先存在的 tsvector_update_trigger 正是为了让程序员避免首先编写触发器。您应该使用 CREATE TRIGGER 语句直接引用它，如下所示:

CREATE TRIGGER trigger_name 
BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE 
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

然后您的函数 posts_before_insert_update_row_tr() 就不需要了。

参见 Triggers for Automatic Updates在文档中。