Hi, Jeremy, not works after add DB.synchronize block, test is failing, but
use PG still works.
在2021年11月21日星期日 UTC+8 上午1:52:03<Jeremy Evans> 写道：
> 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/b4e5a09a-602d-4751-9599-ef54ecfaaa4bn%40googlegroups.com.
