Hi, Jeremy, i am back!
after some research, i have some conclusion:
1. the really issue come from following SQL statement. "DB.run("SELECT
sync_zhprs_custom_word();UPDATE investing_latest_news SET title = title")",
other part no issue.
2. I add a updated_at trigger use * sequel_postgresql_triggers
<https://github.com/jeremyevans/sequel_postgresql_triggers> * gem, when i
run, the the above SQL statement, updated_at get updated as expected.
so, that is not a issue for RodaRequest anyway, in fact, it can be
describe as:
title was got updated as expect when run update statement but, the " stored
generated column" which created by following migrate code not get updated
as expected.
```
run <<'HEREDOC'
ALTER TABLE investing_latest_news
ADD COLUMN textsearchable_index_col tsvector GENERATED ALWAYS
AS
(
to_tsvector(
'zhparser',
coalesce(title, '')
)
)
STORED;
HEREDOC
run 'CREATE INDEX investing_latest_news_textsearch_idx_index ON
investing_latest_news USING GIN (textsearchable_index_col);' end
```
You may want ask me why not use trigger, the reason is, because this
totally a personal project, so i use more recently pg version,
as describe in pg document version 14.
https://www.postgresql.org/docs/14/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS
Use trigger to achieve this was obsoleted by official document, use stored
generated column is recommended.
3. okay, this is the most interesting part.
After i change the UPDATE statement code from
r.is 'sync-ts-keyword' do
DB.run("SELECT sync_zhprs_custom_word();")
DB.run("UPDATE investing_latest_news SET title = title, preview =
preview;")
r.redirect request.referer
end
into
r.is 'sync-ts-keyword' do
db = PG.connect(URI(DB_URL))
db.exec("SELECT sync_zhprs_custom_word();")
db.exec("UPDATE investing_latest_news SET title = title, preview =
preview;")
r.redirect r.referer
end
All works like a charm!
so, i guess if this caused by Sequel not support "stored generated column"
for now?
> please post a minimal self contained example showing it using just
Sequel, with no other libraries.
Is there exists a sample for how to write this self contained example ? if
have, i can try create a issue
on github with it.
在2021年11月20日星期六 UTC+8 上午1:38:06<Jeremy Evans> 写道：
> On Fri, Nov 19, 2021 at 9:05 AM Billy Zheng <[email protected]> wrote:
>
>> Okay, i update my code, like this:
>>
>> ```
>> r.is 'add-ts-keyword' do
>> new_keyword = r.params['new_keyword']
>>
>> if new_keyword.present?
>> DB.run(Sequel.lit("INSERT INTO zhparser.zhprs_custom_word
>> values(?) ON CONFLICT DO NOTHING;", new_keyword))
>> end
>>
>> r.redirect request.referrer
>> end
>>
>> r.is 'remove-ts-keyword' do
>> keyword = r.params['keyword']
>>
>> if keyword.present?
>> DB.run(Sequel.lit("DELETE FROM zhparser.zhprs_custom_word WHERE
>> word=?;", keyword))
>> end
>>
>> r.redirect request.referrer
>> end
>>
>> r.is 'sync-ts-keyword' do
>> DB.run("SELECT sync_zhprs_custom_word();")
>> DB.run("UPDATE investing_latest_news SET title = title, preview =
>> preview;")
>> r.redirect request.referrer
>> end
>> ```
>>
>> But it still not work.
>>
>> For describe my issue, i record a video, and upload here.
>>
>> https://raw.githubusercontent.com/zw963/zw963.github.io/master/files/1.mp4
>>
>> Could you please check?
>>
>>
>> > UPDATE just sets columns to their current value, so it will only have
>> an effect if there are triggers or something similar in play.
>>
>> As you can see in the video, the first time, i add "美元“ token into
>> parser, the UPDATE statement works, but then after i remove add from parser,
>> the UPDATE not work anymore, and finally, when i UPDATE from
>> bin/console, it works again.
>>
>
> Sorry, I don't have time to watch vidoes. This is a Sequel help form, so
> if you think there is a problem, please post a minimal self contained
> example showing it using just Sequel, with no other libraries.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/28f0e0b1-be8c-43b0-b8c6-487142f9a7b6n%40googlegroups.com.
