Re: FTS trigger works 1 at a time, but fails with bulk insert script - Mailing list pgsql-general
| From | Malik Rumi |
|---|---|
| Subject | Re: FTS trigger works 1 at a time, but fails with bulk insert script |
| Date | |
| Msg-id | CAKd6oBwG0eAAg3CvpjrbuUdhtZu8WBHwmJ2ED_hKadZmAb7i=g@mail.gmail.com Whole thread Raw |
| In response to | Re: FTS trigger works 1 at a time, but fails with bulk insert script (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: FTS trigger works 1 at a time, but fails with bulk insert script
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: FTS trigger works 1 at a time, but fails with bulk insert script (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
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 "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><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">'A'</font>) ||
<font color="#D3D7CF">setweight(to_tsvector(</font>NEW.content), <font color="#4E9A06">'B'</font>) ||
<font color="#D3D7CF">setweight(to_tsvector(</font>NEW.category), <font color="#4E9A06">'D'</font>) ||
<font color="#D3D7CF">setweight(to_tsvector(</font>COALESCE(<font color="#D3D7CF">string_agg(</font>tag.tag, <font color="#4E9A06">', '</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.klaver@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.klaver@aklaver.com
pgsql-general by date: