Quick Links

Re: FTS trigger works 1 at a time, but fails with bulk insert script

From:	Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To:	Malik Rumi <malik(dot)a(dot)rumi(at)gmail(dot)com>
Cc:	pgsql-general(at)postgresql(dot)org
Subject:	Re: FTS trigger works 1 at a time, but fails with bulk insert script
Date:	2018-10-08 20:32:09
Message-ID:	a8981642-a559-68a8-bdb3-0fa43e1c4562@aklaver.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

On 10/8/18 1:25 PM, Malik Rumi wrote:
> I hope this comes out readable. If not I can do a separate attachment. I
> notice it says 'BEFORE INSERT'. Maybe that should be after?

No as the return value would be ignored:

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

"The return value of a row-level trigger fired AFTER or a
statement-level trigger fired BEFORE or AFTER is always ignored; it
might as well be null. However, any of these types of triggers might
still abort the entire operation by raising an error."

So what is the script you used to do the bulk INSERT?

>
> <pre> Table
> "public.ktab_entry"
> Column | Type | Collation | Nullable |
> Default
> ---------------+--------------------------+-----------+----------+----------------------------------------
> id | integer | | not null |
> nextval('ktab_entry_id_seq'::regclass)
> title | character varying(100) | | not null |
> slug | character varying(100) | | not null |
> content | text | | not null |
> posted_date | timestamp with time zone | | not null |
> chron_date | date | | not null |
> clock | time without time zone | | not null |
> category | character varying(25) | | not null |
> search_vector | tsvector | | |
> image1 | character varying(100) | | |
> image2 | character varying(100) | | |
> image3 | character varying(100) | | |
> Indexes:
> "ktab_entry_pkey" PRIMARY KEY, btree (id)
> "ktab_entry_slug_e1313695_uniq" UNIQUE CONSTRAINT, btree
> (slug)
> "ktab_entry_title_6950e951_uniq" UNIQUE CONSTRAINT, btree
> (title)
> "ktab_entry_search__d5071f_gin" gin (search_vector)
> "ktab_entry_slug_e1313695_like" btree (slug
> varchar_pattern_ops)
> "ktab_entry_title_6950e951_like" btree (title
> varchar_pattern_ops)
> Referenced by:
> TABLE "ktab_entry_tags" CONSTRAINT
> "ktab_entry_tags_entry_id_294f83f9_fk_ktab_entry_id" FOREIGN
> KEY (entry_id) REFERENCES ktab_entry(id) DEFERRABLE INITIALLY DEFERRED
> Triggers:
> search_vector_update BEFORE INSERT OR UPDATE ON ktab_entry FOR EACH
> ROW EXECUTE PROCEDURE entry_search_vector_trigger()
> </pre>
>
> <pre> GNU
> nano 2.9.3 /tmp/psql.edit.24305.sql
> 
>
> CREATE OR REPLACE color="#3465A4">FUNCTION color="#EF2929">public. color="#D3D7CF">entry_search_vector_trigger()
> RETURNS trigger
> LANGUAGE color="#729FCF">plpgsql
> AS $function$ color="#3465A4">BEGIN
> SELECT color="#D3D7CF">setweight(to_tsvector(NEW.title), color="#4E9A06">'A') ||
> color="#D3D7CF">setweight(to_tsvector(NEW.content), color="#4E9A06">'B') ||
> color="#D3D7CF">setweight(to_tsvector(NEW.category), color="#4E9A06">'D') ||
> color="#D3D7CF">setweight(to_tsvector(COALESCE( color="#D3D7CF">string_agg(tag.tag, ',
> '), $
> INTO NEW.search_vector
> FROM ktab_entry color="#3465A4">AS entry
> LEFT JOIN ktab_entry_tags color="#3465A4">AS entry_tags ON entry_tags.entry_id $
> LEFT JOIN ktab_tag AS tag
> ON tag.id <http://tag.id> = entry_tags.tag_id
> WHERE entry.id
> <http://entry.id> = NEW.id
> GROUP BY entry.id
> <http://entry.id>, category;
> RETURN NEW;
> END;
> $function$
> </pre>
> */“None of you has faith until he loves for his brother or his neighbor
> what he loves for himself.”/*
>
>
> On Mon, Oct 8, 2018 at 2:57 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 10/8/18 12:29 PM, Malik Rumi wrote:
> > 1. This code is entry_search_vector_trigger(), one of 3 trigger
> > functions based on the Django model that created the site.
> > 2. So this is the trigger definition (as far as I know) and it is
> on the
> > Entry table. There is also a Tag table and the Tags intersection
> table.
> > 3. Uhh, I'm not sure. I assume this is it, that when a new entry is
> > posted, the function that parses the entry into searchable text and
> > indexes the words is called. But I can tell you I got this code from
> > this blog post:
> > blog.lotech.org/postgres-full-text-search-with-django.html
> <http://blog.lotech.org/postgres-full-text-search-with-django.html>
> >
> <http://blog.lotech.org/postgres-full-text-search-with-django.html>. I
> > asked the author about this issue. He said he wasn't sure wthout
> > debugging if it was something he left out or something I did wrong.
> > 4. Postgresql 9.4. Yea, I know, I should upgrade...
>
> Your function name does not match up with the code on the site, so we
> will need to see the actual trigger/function.
>
> In psql do:
>
> \d entry
>
> to see the trigger definition and then post it here.
>
> Also from that definition you can get the function name.
>
> Again in psql do:
>
> \ef fnc_name
>
> to confirm the function is the one you think it is.
>
> Would also be helpful to see the script you wrote to do the bulk insert.
>
> >
> > */“None of you has faith until he loves for his brother or his
> neighbor
> > what he loves for himself.”/*
> >
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Re: FTS trigger works 1 at a time, but fails with bulk insert script at 2018-10-08 20:25:25 from Malik Rumi

Responses

Re: FTS trigger works 1 at a time, but fails with bulk insert script at 2018-10-08 20:58:45 from Malik Rumi

Browse pgsql-general by date

	From	Date	Subject
Next Message	Jeremy Schneider	2018-10-08 20:49:14	Re: survey: pg_stat_statements total_time and entry deallocation
Previous Message	Malik Rumi	2018-10-08 20:25:25	Re: FTS trigger works 1 at a time, but fails with bulk insert script