Relation "selected" does not exist with external Postgres

Hello,
I’m trying to set up a discourse server hosted off of a pair of servers that will act as a failover pair–to do so I need them to talk to the same database. I already have a postgres server running elsewhere so I followed the instructions here and it appears to connect okay. When I try to rebuild the app, however, I’ve been getting the error:

I, [2019-07-23T17:33:20.623719 #14] INFO – : > cd /var/www/discourse && su discourse -c ‘bundle exec rake
db:migrate’
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR: relation “selected” does not exist
LINE 7: OR (t.closed AND tsp.execute_at >= now())
^
: WITH selected AS (
SELECT tsp.id
FROM topic_status_updates tsp
JOIN topics t
ON t.id = tsp.topic_id
WHERE tsp.execute_at < now()
OR (t.closed AND tsp.execute_at >= now())
)

UPDATE topic_status_updates
SET deleted_at = now(), deleted_by_id = -1
WHERE id in (SELECT * FROM selected)

When I go to the discourse database, there is no ‘selected’ table present. What can I do to fix this? I had an instance working when I had it locally off of once server–this only started happening once I moved the database.

1 Like

Does the database exist at all?

1 Like

It is—there are 125 tables in total when I access the database

2 Likes

That is expected, selected in this case is a CTE.

What is the PostgreSQL version “elsewhere” ?

3 Likes

The version running on our database server is 9.4.22

Oh that is too old. Minimum supported version currently is PostgreSQL 10.

5 Likes

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.