[Solved] Function "similarity" does not exist

Elander · 2018-11-09T21:48:57.247Z

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?

Thank you!

shabble · 2018-11-09T22:12:35.177Z

Datum point: I had this when I moved to a different hosting provider, and was able to upgrade my php from 5.ancient to 7.2.

Thought I’d try postgres, (having used mysql previously,) but got this exact error. I dug around a bit on google but never did find an answer myself.

Out of laziness, and thinking it was something stupid I was doing, I didn’t post here and simply went back to mysql.

$ ttrss-bug-report

tt-rss version (including git commit id):

# git rev-parse --verify HEAD
3e3cccd5da2e25c90f9e1d11590bba651180ef83

Platform (i.e. Linux distro, PHP, PostgreSQL, etc) versions:

# cat /etc/issue
Ubuntu 18.04.1 LTS \n \l
# uname -a
Linux li1004-82 4.15.0-38-generic #41-Ubuntu SMP Wed Oct 10 10:59:38 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

# php --version
PHP 7.2.10-0ubuntu0.18.04.1 (cli) (built: Sep 13 2018 13:45:02) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.10-0ubuntu0.18.04.1, Copyright (c) 1999-2018, by Zend Technologies

# mysql --version
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

# psql --version
psql (PostgreSQL) 10.5 (Ubuntu 10.5-0ubuntu0.18.04)

I’m no longer personally invested in the outcome of this, but it’s there if it helps.

shabble · 2018-11-09T22:23:32.491Z

Logs - not sure how useful they'll be however...

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

darknite323 · 2018-11-10T04:07:38.066Z

Maybe this? : PostgreSQL: pg_trgm Extension Installed but Operators and Functions do not exist

Elander · 2018-11-10T09:48:30.591Z

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.

Elander · 2018-11-10T10:26:32.152Z

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;

that was all