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

Mon, 08 Oct 2018 13:26:49 -0700 

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.kla...@aklaver.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.kla...@aklaver.com
>

Reply via email to