DB.run("UPDATE investing_latest_news SET title = title, preview = preview;") not work done invoke with roda request?

[Billy Zheng]

Because i don't know what happen, so, i just try to reproduce here.

I use following  migrate to add chinese full text search feature use 
zhparser plugin, it works!

https://github.com/zw963/marketbet_crawler/blob/master/db/migrations/20211112143230_add_zhparser_extension.rb

I use following migrate to create a stored generated column, for copy 
title, preview into a new ts_vector column, and create index on it, for ts 
search better performance, it works too!

https://github.com/zw963/marketbet_crawler/blob/master/db/migrations/20211112143437_add_stored_generated_column_to_investing_latest_news.rb

For now, both of them works, but, i hope i can add/delete new chinese 
token  into it.

so, i add following code into roda routes.

```
r.post do
      r.is 'add-ts-keyword' do
        new_keyword = r.params['new_keyword']
        DB.run("INSERT INTO zhparser.zhprs_custom_word 
values('#{new_keyword}') ON CONFLICT DO NOTHING;")
        r.redirect request.referrer
      end

      r.is 'remove-ts-keyword' do
        keyword = r.params['keyword']
        DB.run("DELETE FROM zhparser.zhprs_custom_word WHERE 
word='#{keyword}';")
        r.redirect request.referrer
      end

      r.is 'sync-ts-keyword' do
        DB.run("SELECT sync_zhprs_custom_word();UPDATE 
investing_latest_news SET title = title, preview = preview;")
        r.redirect request.referrer
      end
end
```

All of above SQL works too.

but, the issue come from the sync-ts-keyword, when i send a post request to 
try to run

DB.run("SELECT sync_zhprs_custom_word();UPDATE investing_latest_news SET 
title = title, preview = preview;")

those code seem like not always work,  i can saw logs like this when do 
development.

I, [2021-11-19T23:36:23.725756 #116437]  INFO -- : (0.062334s) SELECT 
sync_zhprs_custom_word();UPDATE investing_l
atest_news SET title = title, preview = preview;

but stored generated column not get updated, in fact, i even don't know if 
UPDATE sql
not work or just stored generated column not updated but UPDATE works. 

e.g. I add a new token into zhparser table  use /add-ts-keyword, then 
/sync-ts-keyword, it works, i can see ts_vector column get updated as 
expected, but when i try /remove-ts-keyword, then /sync-ts-keyword,  the 
ts_vector column not updated.

the strange things is, if i run "DB.run("SELECT 
sync_zhprs_custom_word();UPDATE investing_latest_news SET title = title, 
preview = preview;")" on console, or run 
SQL directly use psql, it always works on any cases, ts_vector always get 
updated!

I don't know what things do wrong, could you please give some clue for 
debug this?

Thank you.



-- 
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/13dae5d2-d36d-4ff7-932e-1086ff21048fn%40googlegroups.com.