Thread: FTS trigger works 1 at a time, but fails with bulk insert script
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.”
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.klaver@aklaver.com
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 <adrian.klaver@aklaver.com> 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.klaver@aklaver.com
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 <adrian.klaver@aklaver.com> 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.klaver@aklaver.com
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
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
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
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
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@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
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@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
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><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@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
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><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@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
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=',') > # 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']) I thought that you needed to pass a model object to add(). If I am following the above it is just a csv field value. Assuming log_statement = 'mod', I would tail the postgresql.conf file to see what is actually hitting the database. > */“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
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=',') > # 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']) I thought that you needed to pass a model object to add(). If I am following the above it is just a csv field value. Assuming log_statement = 'mod', I would tail the postgresql.conf file to see what is actually hitting the database. > */“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
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=',') >> # 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']) > > I thought that you needed to pass a model object to add(). If I am > following the above it is just a csv field value. > > Assuming log_statement = 'mod', I would tail the postgresql.conf file to > see what is actually hitting the database. Aargh, meant tail the Postgres log file. > > > >> */“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
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=',') >> # 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']) > > I thought that you needed to pass a model object to add(). If I am > following the above it is just a csv field value. > > Assuming log_statement = 'mod', I would tail the postgresql.conf file to > see what is actually hitting the database. Aargh, meant tail the Postgres log file. > > > >> */“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
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
<snip>
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
<snip>
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
<snip>
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
<snip>
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
<snip>
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
<snip>
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
<snip>
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
<snip>
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 <adrian.klaver@aklaver.com> 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=',')
>> # 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'])
>
> I thought that you needed to pass a model object to add(). If I am
> following the above it is just a csv field value.
>
> Assuming log_statement = 'mod', I would tail the postgresql.conf file to
> see what is actually hitting the database.
Aargh, meant tail the Postgres log file.
>
>
>
>> */“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
On 10/9/18 3:25 PM, Malik Rumi wrote: > 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. The current logs will be the ones without the number suffix so: postgresql-9.4-main.log -- Is the current log postgresql-9.4-main.log.1 -- Is a past log. The lower the number the more recent it was rotated out. So this is the last log before the current one. The information in the logs is controlled by settings in the postgresql.conf file: https://www.postgresql.org/docs/10/static/runtime-config-logging.html Assuming you are using the Debian/Ubuntu packing of Postgres do: pg_lsclusters This will return something like: Ver Cluster Port Status Owner Data directory Log file 9.6 main 5422 down postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log 10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log The first two fields above(Ver, Cluster) are the important ones for now. Use them to navigate to: /etc/postgresql/Ver/Cluster to find the postgresql.conf file for the cluster. Look at see how your logging is configured and make adjustments using the information in the logging conf link above. You will need to reload the server to get the changes to take. At the least I would set log_connections and log_disconnections to 'on'. This will at least allow you to see which server you are connecting to. I would also set log_statement to at least 'mod' so you can see your INSERT's. > > > malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.4-main.log.1 > <snip> > 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 > <snip> > 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 > <snip> > 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 > <snip> > 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 > <snip> > 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 > <snip> > 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 > <snip> > 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 > <snip> > 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 <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> 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=',') > >> # 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']) > > > > I thought that you needed to pass a model object to add(). If I am > > following the above it is just a csv field value. > > > > Assuming log_statement = 'mod', I would tail the postgresql.conf > file to > > see what is actually hitting the database. > > Aargh, meant tail the Postgres log file. > > > > > > > > >> */“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