On Sat, Nov 20, 2021 at 6:06 AM Billy Zheng <vil...@gmail.com> wrote:
> 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!
>
The difference between the two is that use of PG correctly results in the
same connection, where that isn't guaranteed in Sequel due to the use of a
connection pool. You could try:
r.is 'sync-ts-keyword' do
DB.synchronize do
DB.run("SELECT sync_zhprs_custom_word();")
DB.run("UPDATE investing_latest_news SET title = title, preview =
preview;")
end
r.redirect request.referer
end
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 sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSdUWW2%3DNFzcLqsHo%2BBN2cnV2zCuQe%2Bz-YSKENwzM2dcDg%40mail.gmail.com.
