from:"Malik Rumi"

Re: impact of version upgrade on fts

2021-04-26 Thread Malik Rumi

Thanks, I'll check it out.
*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*


On Sun, Apr 25, 2021 at 2:32 PM Robert Treat  wrote:

> On Sun, Apr 25, 2021 at 11:27 AM Adrian Klaver
>  wrote:
> >
> > On 4/25/21 5:28 AM, Malik Rumi wrote:
> > > Greetings.
> > >
> > > I am about to do a long overdue upgrade to the latest version, which I
> > > believe is 13.2. However, I have full text search in my current install
> > > (9.4) and I am wondering if there are any special provisions I need to
> > > take to make sure that is not lost in the transition?  If this would be
> > > true for any other packages, please advise and or point me to the place
> > > in the docs where I can read up on this. I looked and did not see
> > > anything, which just proves I didn't know what to look for. Thanks.
> >
> > FTS used to be a separate extension(tsearch2) before version Postgres
> > version 8.3. Since then it has been integrated into the core code, so it
> > would not be lost. That being said it would be advisable to read the
> > release notes for 9.5 --> 13 to see what changed in the core code.
> >
>
> You should also check the release notes / process of any "non-core"
> extensions you might be using, for example PostGIS has had a number of
> changes and you'll need to upgrade the extension itself to work in the
> new version of Postgres. Specifics around that will also depend on how
> you instead to run your upgrade process.
>
>
> Robert Treat
> https://xzilla.net
>

impact of version upgrade on fts

2021-04-25 Thread Malik Rumi

Greetings.

I am about to do a long overdue upgrade to the latest version, which I
believe is 13.2. However, I have full text search in my current install
(9.4) and I am wondering if there are any special provisions I need to take
to make sure that is not lost in the transition?  If this would be true for
any other packages, please advise and or point me to the place in the docs
where I can read up on this. I looked and did not see anything, which just
proves I didn't know what to look for. Thanks.



*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*

Re: Triggers and Full Text Search *

2020-04-25 Thread Malik Rumi

@Ericson
I have the script (statements?) by which I created the triggers, but since
you asked I do not see them in pga4, from which I manage my postgres. I
don't know if this is significant or not. Also, this was originally done
quite a while ago, so my memory may be fuzzy. From the text of the
statement "runSQL..." I think I ran this in the terminal. So this is the
closest thing I can find to your request. The text that I previously posted
can be found in 'triggers functions' under this schema in pga4, but not
these statements. Also, further up the pga4 tree, "event triggers" is
blank. I mention these things because I am not sure of their importance.
Thanks.

triggers.py

# Trigger on insert or update of ktab.Entry

migrations.RunSQL('''CREATE OR REPLACE FUNCTION
entry_search_vector_trigger() RETURNS trigger AS $$
BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tags.tag, ', '), '')),
'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON entry_tags.entry_id =
entry.id
LEFT JOIN ktab_tags AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update BEFORE INSERT OR UPDATE ON ktab_entry
  FOR EACH ROW EXECUTE PROCEDURE entry_search_vector_trigger();''')

# Trigger after ktab.Author is updated
'''
Since I don't have author, and besides, his author was a separate table -
SKIP
CREATE OR REPLACE FUNCTION author_search_vector_trigger() RETURNS trigger
AS $$
BEGIN
  UPDATE ktab_entry SET id = id WHERE author_id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE ON ktab_author
  FOR EACH ROW EXECUTE PROCEDURE author_search_vector_trigger();
'''

# Trigger after ktab.Entry.tags are added, deleted from a entry
migrations.RunSQL('''CREATE OR REPLACE FUNCTION
tags_search_vector_trigger() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
  ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE OR DELETE ON
ktab_entry_tags
  FOR EACH ROW EXECUTE PROCEDURE tags_search_vector_trigger();
''')

# Trigger after ktab.Tag is updated
migrations.RunSQL('''CREATE OR REPLACE FUNCTION tag_search_vector_trigger()
RETURNS trigger AS $$
BEGIN
  UPDATE ktab_entry SET id = id WHERE id IN (
SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER UPDATE ON ktab_tag
  FOR EACH ROW EXECUTE PROCEDURE tag_search_vector_trigger();


*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*


On Tue, Apr 21, 2020 at 1:25 PM Ericson Smith  wrote:

> My apologies - I did not look closely at the manual. Many many years ago
> (6.xx days I had a similar problem and leapt to answer).
>
> Could you post your CREATE TRIGGER statements as well?
>
>
> On Wed, Apr 22, 2020 at 1:21 AM Malik Rumi  wrote:
>
>> @Ericson,
>> Forgive me for seeming dense, but how does COPY help or hurt here?
>>
>> @Andreas,
>> I had to laugh at your reference to "prose". Would you believe I am
>> actually a published playwright? Long before I started coding, of course.
>> Old habits die hard.
>>
>> entry_search_vector_trigger
>> BEGIN
>>   SELECT setweight(to_tsvector(NEW.title), 'A') ||
>>  setweight(to_tsvector(NEW.content), 'B') ||
>>  setweight(to_tsvector(NEW.category), 'D') ||
>>  setweight(to_tsvector(COALESCE(string_agg(tag.tag,
>> ', '), '')), 'C')
>>   INTO NEW.search_vector
>>   FROM ktab_entry AS entry
>> LEFT JOIN ktab_entry_tags AS entry_tags ON
>> entry_tags.entry_id = entry.id
>> LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
>>   WHERE entry.id = NEW.id
>>   GROUP BY entry.id, category;
>>   RETURN NEW;
>> END;
>>
>> tag_search_vector_trigger
>> BEGIN
>>   UPDATE ktab_entry SET id = id WHERE id IN (
>> SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
>>   );
>>   RETURN NEW;
>> END;
>>
>> tags_search_vector_trigger
>> BEGIN
>>   IF (TG_OP = 'DELETE') THEN
>&

Re: Triggers and Full Text Search *

2020-04-21 Thread Malik Rumi

@Ericson,
Forgive me for seeming dense, but how does COPY help or hurt here?

@Andreas,
I had to laugh at your reference to "prose". Would you believe I am
actually a published playwright? Long before I started coding, of course.
Old habits die hard.

entry_search_vector_trigger
BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;

tag_search_vector_trigger
BEGIN
  UPDATE ktab_entry SET id = id WHERE id IN (
SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
  );
  RETURN NEW;
END;

tags_search_vector_trigger
BEGIN
  IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
  ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
  END IF;
END;

search_vector_update
BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;

search_vector_update  (tags)
BEGIN
  IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
  ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
  END IF;
END;

Thank you!



*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*


On Tue, Apr 21, 2020 at 1:05 PM Ericson Smith  wrote:

> I think COPY bypasses the triggers.
>
> Best Regards
> - Ericson Smith
> +1 876-375-9857 (whatsapp)
> +1 646-483-3420 (sms)
>
>
>
> On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh 
> wrote:
>
>> På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <
>> malik.a.r...@gmail.com>:
>>
>> [...]
>>
>> I am not (yet) posting the trigger code because this post is long
>> already, and if your answers are 1) yes, 2) no and 3) triggers often work /
>> fail like this, then there’s no point and we can wrap this up. But if not,
>> I will happily post what I have. Thank you.
>>
>>
>> This is too much prose for the regular programmer, show us the code, and
>> point out what doesn't work for you, then we can help:-)
>>
>> --
>> Andreas Joseph Krogh
>>
>

Triggers and Full Text Search *

2020-04-21 Thread Malik Rumi

More than a year ago, I implemented full text search on one of my sites.
>From the beginning, there was one problem (or at least, what I perceive to
be a problem): when I use a script to insert many documents at once, they
do *not* get indexed in fts. If a document is created or inserted one at a
time, fts indexes immediately. The workaround I came up with was just to
open each of those script inserted documents and then close them. As soon
as they are opened, they get indexed.

I assume this has to do with the trigger, which is set to BEFORE, and which
I carefully followed from the blog post that I got the code from. I wrote
to that author at the time, but he was of no help. My thought was that the
trigger was not firing, and thus the documents were not getting indexed,
because until the document was actually there, there was nothing to index.
Therefore, I thought a simple switch from BEFORE to AFTER would solve my
problem. However, in the example in the official docs, BEFORE is used as
well, so I abandoned that idea and decided to post this question.

Another solution I had in mind was to simply include an additional step in
my insert script to sleep for one second, during which the current document
would be opened, and hopefully indexed, and then closed, so the script
could go on to the next document. Note my insert script is in Python and
goes through Django. This is not a ‘pure’ postgresql operation, if that
matters.

My questions are:

   1.

   Does this sleep / open / close / proceed idea seem like a workable
   solution?
   2.

   Is there a better workaround?
   3.

   At first blush, I would think the speed of insertion would not be an
   issue for any trigger - it would seem to defeat the purpose - but am I
   wrong about that?


I am not (yet) posting the trigger code because this post is long already,
and if your answers are 1) yes, 2) no and 3) triggers often work / fail
like this, then there’s no point and we can wrap this up. But if not, I
will happily post what I have. Thank you.




*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*

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

2018-10-09 Thread Malik Rumi

I have several different logs. I'm not sure which is the 'right' one. None
of them show anything other than routine start and stop activity, even
after I added a single Entry document. However, I did notice despite the
'incomplete startup', the last one is the only one that mentions listening
on any port. That might be just a version change. I originally installed
9.4. I tried 9.5 but knew it didn't go right. I tried 10 when I was still
on Ubuntu 16.04, which is not supposed to be compatible with 10. I have
since gone up to Ubuntu 18.04 but have not tried to re-install pg 10.
Bottom line, this seems to be a completely different problem.


malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.4-main.log.1

2018-10-07 11:09:53 CDT [1223-1] [unknown]@[unknown] LOG:  incomplete
startup packet
2018-10-07 11:09:53 CDT [1222-1] LOG:  database system was shut down at
2018-10-06 17:41:15 CDT
2018-10-07 11:09:53 CDT [1224-1] postgres@postgres FATAL:  the database
system is starting up

2018-10-07 11:09:54 CDT [1149-1] LOG:  database system is ready to accept
connections
2018-10-07 was two days ago!

malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.4-main.log

2018-10-08 22:22:17 CDT [1266-2] LOG:  database system is shut down
2018-10-09 07:59:49 CDT [1216-1] LOG:  database system was shut down at
2018-10-08 22:22:17 CDT
2018-10-09 07:59:49 CDT [1217-1] [unknown]@[unknown] LOG:  incomplete
startup packet
2018-10-09 07:59:50 CDT [1227-1] postgres@postgres FATAL:  the database
system is starting up

2018-10-09 07:59:51 CDT [1154-1] LOG:  database system is ready to accept
connections
What's with the fatal and the incomplete startup? Given that, how ready is
it really to accept connections?

malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.5-main.log

2018-10-08 22:22:18 CDT [1271-2] LOG:  database system is shut down
2018-10-09 07:59:49 CDT [1210-1] LOG:  database system was shut down at
2018-10-08 22:22:18 CDT
2018-10-09 07:59:49 CDT [1211-1] [unknown]@[unknown] LOG:  incomplete
startup packet
2018-10-09 07:59:50 CDT [1226-1] postgres@postgres FATAL:  the database
system is starting up

2018-10-09 07:59:50 CDT [1153-1] LOG:  database system is ready to accept
connections
Same question / observation

malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.5-main.log.1

2018-10-07 11:09:53 CDT [1220-1] LOG:  database system was shut down at
2018-10-06 17:41:15 CDT
2018-10-07 11:09:53 CDT [1221-1] [unknown]@[unknown] LOG:  incomplete
startup packet
2018-10-07 11:09:53 CDT [1225-1] postgres@postgres FATAL:  the database
system is starting up

2018-10-07 11:09:54 CDT [1150-1] LOG:  database system is ready to accept
connections
Same - and this was two days ago, too

malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-10-main.log
2018-10-08 22:22:16.047 CDT [1159] LOG:  received fast shutdown request
2018-10-08 22:22:17.337 CDT [1159] LOG:  aborting any active transactions
2018-10-08 22:22:17.927 CDT [1159] LOG:  worker process: logical
replication launcher (PID 1281) exited with exit code 1
2018-10-08 22:22:17.988 CDT [1276] LOG:  shutting down
2018-10-08 22:22:19.327 CDT [1159] LOG:  database system is shut down
2018-10-09 07:59:48.574 CDT [1155] LOG:  listening on IPv4 address
"127.0.0.1", port 5434
2018-10-09 07:59:48.727 CDT [1155] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5434"
2018-10-09 07:59:50.590 CDT [1223] LOG:  database system was shut down at
2018-10-08 22:22:19 CDT
2018-10-09 07:59:51.058 CDT [1155] LOG:  database system is ready to accept
connections
2018-10-09 07:59:51.617 CDT [1274] [unknown]@[unknown] LOG:  incomplete
startup packet


*“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 6:36 PM Adrian Klaver 
wrote:

> On 10/8/18 3:54 PM, Adrian Klaver wrote:
> > On 10/8/18 1:58 PM, Malik Rumi wrote:
> >> 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
> >
> > So I am going to assume Entry and Tag map to the tables ktab_entry and
> > public.ktab_entry_tags respectively.
> >
> >> 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=',')
> >&g

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

2018-10-08 Thread Malik Rumi

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 
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?
>
>
> >
> > 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()
> > 
> >
> >   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
> >

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

2018-10-08 Thread Malik Rumi

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?

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()


  GNU
nano 2.9.3   /tmp/psql.edit.24305.sql
 

CREATE OR REPLACE FUNCTION public.entry_search_vector_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
  SELECT setweight(to_tsvector(NEW.title), A) ||
 setweight(to_tsvector(NEW.content), B) ||
 setweight(to_tsvector(NEW.category), D) ||
 setweight(to_tsvector(COALESCE(string_agg(tag.tag, ,
), $
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS
entry_tags ON entry_tags.entry_id $
LEFT JOIN ktab_tag AS tag ON
tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;
$function$

*“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 
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
>

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

2018-10-08 Thread Malik Rumi

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. 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...

*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*

On Tue, Oct 2, 2018 at 6:27 PM Adrian Klaver 
wrote:

> On 10/2/18 10:34 AM, Malik Rumi wrote:
> > I have set up Postgres FTS on a Django/Python web site, and it works as
> > expected except for this one thing. When I wrote a script to bulk insert
> > legacy docs, the script works fine but the FTS trigger does not fire. I
> > have to go back and open each document one at a time to get them indexed.
> >
> > What am I missing to make this work? Thanks.
>
> Have no idea as there is not enough information.
>
> To begin with:
>
> 1) What is code below?
>
> 2) What is the trigger definition and on what table?
>
> 3) What is the function the trigger is calling?
>
> 4) For good measure what version of Postgres?
>
> >
> >  BEGIN
> >SELECT setweight(to_tsvector(NEW.title), 'A') ||
> >   setweight(to_tsvector(NEW.content), 'B') ||
> >   setweight(to_tsvector(NEW.category), 'D') ||
> >   setweight(to_tsvector(COALESCE(string_agg(tag.tag,
> > ', '), '')), 'C')
> >INTO NEW.search_vector
> >FROM ktab_entry AS entry
> >  LEFT JOIN ktab_entry_tags AS entry_tags ON
> > entry_tags.entry_id = entry.id <http://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;
> >
> >
> > */“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
>

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

2018-10-02 Thread Malik Rumi

I have set up Postgres FTS on a Django/Python web site, and it works as
expected except for this one thing. When I wrote a script to bulk insert
legacy docs, the script works fine but the FTS trigger does not fire. I
have to go back and open each document one at a time to get them indexed.

What am I missing to make this work? Thanks.

BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;


*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*

Re: impact of version upgrade on fts

impact of version upgrade on fts

Re: Triggers and Full Text Search *

Re: Triggers and Full Text Search *

Triggers and Full Text Search *

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

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

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

10 matches

Site Navigation

Mail list logo

Footer information