Home > mailing lists

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	October 8, 2018 23:58:45
Msg-id	CAKd6oBy1B8Q-6JXSRf0wZZA2-oHoLAQJyxHLyuE5UKhGb0HtkA@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 Re: FTS trigger works 1 at a time, but fails with bulk insert script
List	pgsql-general

Tree view

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

There's actually three, but they are all basically the same. The differences have to do with the source material being inserted:

# usr/local/bin/python3.6

# coding: utf-8

from os import environ

environ['DJANGO_SETTINGS_MODULE'] = 'chronicle.settings'

import django

django.setup()

from ktab.models import Entry, Tag

from django.utils.text import slugify

import csv

filename = '/home/malikarumi/Projects/Progress_Logs/edit_blogpost.csv'

with open(filename, 'rt') as text:

reader = csv.DictReader(text, delimiter=',')

# next(reader, None)

for row in reader:

my_entry = Entry.objects.create(

title=row['title'], slug=row['slug'], chron_date=row['created'],

clock=row['clock'], content=row['content'])

my_entry.tags.add(row['tag'])

“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 3:32 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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.klaver@aklaver.com
> <mailto: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>
> >
> <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 <mailto:adrian.klaver@aklaver.com>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

pgsql-general by date:

From: Jeremy Schneider
Date: 08 October 2018, 23:49:14
Subject: Re: survey: pg_stat_statements total_time and entry deallocation

From: Adrian Klaver
Date: 09 October 2018, 01:54:03
Subject: Re: FTS trigger works 1 at a time, but fails with bulk insert script

Re: FTS trigger works 1 at a time, but fails with bulk insert script - Mailing list pgsql-general

Previous

Next