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><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 <http://tag.id> = entry_tags.tag_id
<font color="#3465A4">WHERE</font> entry.id
<http://entry.id> = NEW.id
<font color="#3465A4">GROUP</font> BY entry.id
<http://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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
