On 4/21/20 11: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.....
The script code via Python/Django/psycopg2 would be helpful as my
suspicion is that you are seeing the effects of open transactions.
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 <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;
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 <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;
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 <[email protected]
<mailto:[email protected]>> 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
<[email protected] <mailto:[email protected]>> wrote:
På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi
<[email protected] <mailto:[email protected]>>:
[...]
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
--
Adrian Klaver
[email protected]
