I recently reinstalled tt-rss from an old slower raspberry pi to a faster similar version. While doing so I’ve run into a problem with the “af_psql_trgm” plugin. I have two feeds that sometimes turn up with quite similar titles (and content) so this plugin helped me a lot in the past but whenever I turn it on now, I only get error messages in the logs and the feeds stop updating.
This is what the error messages states:
SQLSTATE[42883]: Undefined function: 7 ERROR: function “similarity” does not exist LINE 1: select ‘similarity’::regproc ^#0 /var/www/html/tt-rss/plugins/af_psql_trgm/init.php(256): PDO->query('select ‘similar…’) #1 /var/www/html/tt-rss/classes/rssutils.php(752): Af_Psql_Trgm->hook_article_filter(Array) #2 /var/www/html/tt-rss/classes/rssutils.php(192): RSSUtils::update_rss_feed(13, true, false) #3 /var/www/html/tt-rss/update.php(166): RSSUtils::update_daemon_common() #4 {main}
I have installed the postgresql extension via
CREATE EXTENSION pg_trgm;
I’m running version v18.8 (643d191) directly from git, with nginx, php7.2 and postgresql 10
Does anyone know which step I forgot to take or how I can resolve this?
2018-10-21 19:17:50.592 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: select 'similarity'::regproc
2018-10-21 19:17:50.592 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:17:50.592 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000013
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: SELECT DISTINCT ttrss_feeds.id,last_updated,ttrss_feeds.owner_uid
FROM ttrss_feeds, ttrss_users, ttrss_user_prefs WHERE
ttrss_user_prefs.owner_uid = ttrss_feeds.owner_uid AND
ttrss_users.id = ttrss_user_prefs.owner_uid AND
ttrss_user_prefs.pref_name = 'DEFAULT_UPDATE_INTERVAL' AND
ttrss_user_prefs.profile IS NULL AND
feed_url = $1
AND ((
ttrss_feeds.update_interval = 0
AND ttrss_user_prefs.value != '-1'
AND ttrss_feeds.last_updated < NOW() - CAST((ttrss_user_prefs.value || ' minutes') AS INTERVAL)
) OR (
ttrss_feeds.update_interval > 0
AND ttrss_feeds.last_updated < NOW() - CAST((ttrss_feeds.update_interval || ' minutes') AS INTERVAL)
) OR (ttrss_feeds.last_updated IS NULL
AND ttrss_user_prefs.value != '-1')
OR (last_updated = '1970-01-01 00:00:00'
AND ttrss_user_prefs.value != '-1'))
AND ttrss_users.last_login >= NOW() - INTERVAL '30 days'
ORDER BY ttrss_feeds.id LIMIT 50
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000006
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000007
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:17:50.643 UTC [32643] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000008
2018-10-21 19:21:28.760 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: function "similarity" does not exist at character 8
2018-10-21 19:21:28.760 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: select 'similarity'::regproc
2018-10-21 19:21:28.760 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:21:28.760 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000014
2018-10-21 19:21:28.799 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:21:28.799 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: SELECT DISTINCT ttrss_feeds.id,last_updated,ttrss_feeds.owner_uid
FROM ttrss_feeds, ttrss_users, ttrss_user_prefs WHERE
ttrss_user_prefs.owner_uid = ttrss_feeds.owner_uid AND
ttrss_users.id = ttrss_user_prefs.owner_uid AND
ttrss_user_prefs.pref_name = 'DEFAULT_UPDATE_INTERVAL' AND
ttrss_user_prefs.profile IS NULL AND
feed_url = $1
AND ((
ttrss_feeds.update_interval = 0
AND ttrss_user_prefs.value != '-1'
AND ttrss_feeds.last_updated < NOW() - CAST((ttrss_user_prefs.value || ' minutes') AS INTERVAL)
) OR (
ttrss_feeds.update_interval > 0
AND ttrss_feeds.last_updated < NOW() - CAST((ttrss_feeds.update_interval || ' minutes') AS INTERVAL)
) OR (ttrss_feeds.last_updated IS NULL
AND ttrss_user_prefs.value != '-1')
OR (last_updated = '1970-01-01 00:00:00'
AND ttrss_user_prefs.value != '-1'))
AND ttrss_users.last_login >= NOW() - INTERVAL '30 days'
ORDER BY ttrss_feeds.id LIMIT 500
2018-10-21 19:21:28.800 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:21:28.800 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000007
2018-10-21 19:21:28.800 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:21:28.800 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000008
2018-10-21 19:21:28.800 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 ERROR: current transaction is aborted, commands ignored until end of transaction block
2018-10-21 19:21:28.800 UTC [32678] ttrss-bentriver15@ttrss-dizzyactor22 STATEMENT: DEALLOCATE pdo_stmt_00000009
thanks for the suggestion, I tried it and it improves something, meaning I get a “clear data” link next to the plugin like I used to have in the old setup and didn’t have before, but the end result is also the same.
Could it have something to do with the PHP version?
Also: in the old setup there was an extra “tab” under “Feeds” for this extension where you can change some settings. Now when the plugin is enabled, I get the “tab” as well, but it’s blank.
Ok, I found it, I should have initialized the extension on the specific database.
In case others are struggling too: assuming that the db is called “ttrss” I switched to the command line of postgresql: sudo -u postgres psql --dbname=ttrss
then install the extension there: CREATE EXTENSION pg_trgm;
