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

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?

<pre> Table
&quot;public.ktab_entry&quot;
Column | Type | Collation | Nullable |
Default
---------------+--------------------------+-----------+----------+----------------------------------------
id | integer | | not null |
nextval(&apos;ktab_entry_id_seq&apos;::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:
&quot;ktab_entry_pkey&quot; PRIMARY KEY, btree (id)
&quot;ktab_entry_slug_e1313695_uniq&quot; UNIQUE CONSTRAINT, btree
(slug)
&quot;ktab_entry_title_6950e951_uniq&quot; UNIQUE CONSTRAINT, btree
(title)
&quot;ktab_entry_search__d5071f_gin&quot; gin (search_vector)
&quot;ktab_entry_slug_e1313695_like&quot; btree (slug
varchar_pattern_ops)
&quot;ktab_entry_title_6950e951_like&quot; btree (title
varchar_pattern_ops)
Referenced by:
TABLE &quot;ktab_entry_tags&quot; CONSTRAINT
&quot;ktab_entry_tags_entry_id_294f83f9_fk_ktab_entry_id&quot; 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><span style="background-color:#FFFFFF"><font color="#300A24"> GNU
nano 2.9.3 /tmp/psql.edit.24305.sql
</font></span>

<font color="#3465A4">CREATE</font> OR REPLACE <font
color="#3465A4">FUNCTION</font> <font
color="#EF2929"><b>public</b></font>.<font
color="#D3D7CF">entry_search_vector_trigger(</font>)
RETURNS trigger
<font color="#3465A4">LANGUAGE</font> <font
color="#729FCF"><b>plpgsql</b></font>
<font color="#3465A4">AS</font> $function$ <font
color="#3465A4">BEGIN</font>
<font color="#3465A4">SELECT</font> <font
color="#D3D7CF">setweight(to_tsvector(</font>NEW.title), <font
color="#4E9A06">&apos;A&apos;</font>) ||
<font
color="#D3D7CF">setweight(to_tsvector(</font>NEW.content), <font
color="#4E9A06">&apos;B&apos;</font>) ||
<font
color="#D3D7CF">setweight(to_tsvector(</font>NEW.category), <font
color="#4E9A06">&apos;D&apos;</font>) ||
<font
color="#D3D7CF">setweight(to_tsvector(</font>COALESCE(<font
color="#D3D7CF">string_agg(</font>tag.tag, <font color="#4E9A06">&apos;,
&apos;</font>), $
<font color="#3465A4">INTO</font> NEW.search_vector
<font color="#3465A4">FROM</font> ktab_entry <font
color="#3465A4">AS</font> entry
LEFT JOIN ktab_entry_tags <font color="#3465A4">AS</font>
entry_tags ON entry_tags.entry_id $
LEFT JOIN ktab_tag <font color="#3465A4">AS</font> tag ON
tag.id = entry_tags.tag_id
<font color="#3465A4">WHERE</font> entry.id = NEW.id
<font color="#3465A4">GROUP</font> BY entry.id, category;
<font color="#75507B">RETURN</font> NEW;
<font color="#3465A4">END</font>;
$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>
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>. 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
>