I'm currently implementing search functionality in my rails app using pg_search for PostgreSQL full text search. Unfortunately, I'm having a problem with getting the migration to add the tsvector column, GIN INDEX and the TRIGGER in the listings table. The migration runs successfully but doesn't create the things specified in the migration file.
Error when trying to access the tsv column:
I, [2020-03-22T10:49:43.273416 #64203] INFO -- : Started GET "/api/v1/public/search?q=Adidas" for ::1 at 2020-03-22 10:49:43 +1000
D, [2020-03-22T10:49:43.367465 #64203] DEBUG -- : (6.1ms) SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
I, [2020-03-22T10:49:43.383171 #64203] INFO -- : Processing by Api::V1::Public::ListingsController#search as */*
I, [2020-03-22T10:49:43.383234 #64203] INFO -- : Parameters: {"q"=>"Adidas"}
D, [2020-03-22T10:49:43.638039 #64203] DEBUG -- : Listing Load (12.4ms) SELECT "listings".* FROM "listings" INNER JOIN (SELECT "listings"."id" AS pg_search_id, (ts_rank(("listings"."tsv"), (to_tsquery('simple', ''' ' || 'Adidas' || ' ''' || ':*')), 0)) AS rank FROM "listings" WHERE (("listings"."tsv") @@ (to_tsquery('simple', ''' ' || 'Adidas' || ' ''' || ':*')))) AS pg_search_8a836f245cd6a84ba9cbd1 ON "listings"."id" = pg_search_8a836f245cd6a84ba9cbd1.pg_search_id ORDER BY pg_search_8a836f245cd6a84ba9cbd1.rank DESC, "listings"."id" ASC LIMIT $1 [["LIMIT", 100]]
D, [2020-03-22T10:49:43.638832 #64203] DEBUG -- : ↳ app/controllers/application_controller.rb:47:in `public_render_listings_helper'
I, [2020-03-22T10:49:43.639121 #64203] INFO -- : Completed 500 Internal Server Error in 256ms (ActiveRecord: 22.8ms | Allocations: 45123)
F, [2020-03-22T10:49:43.640013 #64203] FATAL -- :
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column listings.tsv does not exist
LINE 1: ...SELECT "listings"."id" AS pg_search_id, (ts_rank(("listings"...
^
):
The Migration file:
class AddTsVectorColumns < ActiveRecord::Migration[6.0]
def change
def up
add_column :listings, :tsv, :tsvector
add_index :listings, :tsv, using: 'gin'
execute <<-SQL
CREATE OR REPLACE FUNCTION update_listings_tsv() RETURNS trigger AS $$
BEGIN
new.tsv := (
SELECT
setweight(to_tsvector(l.item_name), 'A') ||
setweight(to_tsvector(coalesce((string_agg(brands.name, ' ')), '')), 'A') ||
setweight(to_tsvector(coalesce((string_agg(colours.name, ' ')), '')), 'B') ||
setweight(to_tsvector(l.description), 'B') ||
setweight(to_tsvector(categories.name), 'B') ||
setweight(to_tsvector(sub_categories.name), 'B') ||
setweight(to_tsvector(sizes.name), 'B') ||
setweight(to_tsvector(users.username), 'C')
FROM listings l
JOIN users ON users.id = l.user_id
JOIN categories ON categories.id = l.category_id
JOIN sub_categories ON sub_categories.id = l.sub_category_id
JOIN sizes ON sizes.id = l.size_id
JOIN conditions ON conditions.id = l.condition_id
-- Associative Tables
JOIN brands_listings ON brands_listings.listing_id = brands_listings.brand_id
JOIN brands ON brands.id = brands_listings.brand_id
JOIN colours_listings ON colours_listings.listing_id = colours_listings.colour_id
JOIN colours ON colours.id = colours_listings.colour_id
WHERE l.id = new.id
GROUP BY l.id, users.id, categories.id, sub_categories.id, sizes.id, conditions.id
);
RETURN new;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON listings FOR EACH ROW EXECUTE PROCEDURE update_listings_tsv();
SQL
end
def down
execute <<-SQL
DROP TRIGGER tsvectorupdate
ON listings
SQL
remove_index :listings, :tsv
remove_column :listings, :tsv
end
end
end
The output of the migration:
D, [2020-03-22T13:23:39.965602 #70710] DEBUG -- : primary::SchemaMigration Create (0.4ms) INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version" [["version", "20200313140854"]]
D, [2020-03-22T13:23:39.968646 #70710] DEBUG -- : (0.9ms) COMMIT
I, [2020-03-22T13:23:39.969992 #70710] INFO -- : Migrating to AddTsVectorColumns (20200321060306)
== 20200321060306 AddTsVectorColumns: migrating ===============================
== 20200321060306 AddTsVectorColumns: migrated (0.0000s) ======================
D, [2020-03-22T13:23:39.973220 #70710] DEBUG -- : (0.4ms) BEGIN
D, [2020-03-22T13:23:39.975042 #70710] DEBUG -- : primary::SchemaMigration Create (0.4ms) INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version" [["version", "20200321060306"]]
D, [2020-03-22T13:23:39.977113 #70710] DEBUG -- : (0.4ms) COMMIT
D, [2020-03-22T13:23:39.986874 #70710] DEBUG -- : ActiveRecord::InternalMetadata Load (0.8ms) SELECT "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT $2 [["key", "environment"], ["LIMIT", 1]]
D, [2020-03-22T13:23:39.993805 #70710] DEBUG -- : (0.2ms) BEGIN
D, [2020-03-22T13:23:39.999419 #70710] DEBUG -- : ActiveRecord::InternalMetadata Create (4.2ms) INSERT INTO "ar_internal_metadata" ("key", "value", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "key" [["key", "environment"], ["value", "development"], ["created_at", "2020-03-22 03:23:39.992922"], ["updated_at", "2020-03-22 03:23:39.992922"]]
D, [2020-03-22T13:23:40.002363 #70710] DEBUG -- : (0.7ms) COMMIT
D, [2020-03-22T13:23:40.003788 #70710] DEBUG -- : (0.4ms) SELECT pg_advisory_unlock(6123583507257778380)
D, [2020-03-22T13:23:40.258046 #70710] DEBUG -- : (0.6ms) SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
The listing model (where the tsv column is used):
class Listing < ApplicationRecord
include PgSearch::Model
pg_search_scope(
:search,
against: [],
using: {
tsearch: {
tsvector_column: 'tsv',
any_word: true,
prefix: true,
negation: true,
dictionary: 'simple'
}
}
)
end
Extract from the sql schema (with config.active_record.schema_format = :sql) -- -- PostgreSQL database dump --
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
--
-- 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;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: listings; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE listings (
id bigint NOT NULL,
item_name character varying,
description character varying,
price integer,
shipping_cost integer,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
category_id bigint,
sub_category_id bigint,
size_id bigint,
user_id bigint,
full_item_name text,
cached_votes_total integer DEFAULT 0,
cached_votes_score integer DEFAULT 0,
cached_votes_up integer DEFAULT 0,
cached_votes_down integer DEFAULT 0,
cached_weighted_score integer DEFAULT 0,
cached_weighted_total integer DEFAULT 0,
cached_weighted_average double precision DEFAULT 0.0,
flaremeter integer DEFAULT 0,
condition_id bigint,
cover_photo_data text
);
--
-- Name: listings_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE listings_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: listings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE listings_id_seq OWNED BY listings.id;
--
-- PostgreSQL database dump complete
--
SET search_path TO "$user", public;
INSERT INTO "schema_migrations" (version) VALUES
('20200223103428'),
('20200313111818'),
('20200313140854'),
('20200321060306');
I had the def up and def down defined inside def change. Removing the def change method solved my issue.