1

I have a table objects like this:

id | date       | other_ids
===|============|=========
1  | 1489495210 | {3}
2  | 1489495520 | {}
3  | 1489495560 | {5,9}
4  | 1489496588 | {4}
5  | 1489496948 | {}
6  | 1489497022 | {1,3,8}
7  | 1489497035 | {3}
8  | 1489497318 | {2,4}
9  | 1489507260 | {}

I am attempting to write a query to output a list of Object ids with the latest date for each of a specified array of other_ids. For example:

specified_other_ids = [1, 2, 4]
ids = //...
# => ids = [6, 8]
# Note that the max date occurs at id 8 for other_id 2 AND 4, so no duplicates returned

My understanding is that UNNEST is used to break the other_ids arrays into rows, and then I should be able to use DISTINCT ON with the un-nested rows. So this is my attempt:

ids = Object.from("objects, UNNEST(objects.other_ids) AS other_id")
            .where("other_id IN (?)", specified_other_ids)
            .order("other_id, date DESC")
            .pluck("DISTINCT ON(other_id) id")

This works as I would expect when running in development, staging and production consoles. However, when running rspec in the test environment (via Codeship) every test running that code fails when that query is run, with the following error:

ActiveRecord::StatementInvalid:
PG::InvalidColumnReference: ERROR:  function expression in FROM cannot refer to other relations of same query level

So my first thought is that Postgres is somehow configured differently in the test environment, though I also have a sense that I'm somehow not using the Postgres UNNEST function properly. Where should I start looking for a solution to this?

P.S. I'm not an experienced Rails/SQL dev, so detailed explanations would be much appreciated to help me learn!

Stuart
  • 36,683
  • 19
  • 101
  • 139
  • Are you sure your test environment also has (at least) PostgreSQL 9.3+? See: http://stackoverflow.com/q/10773568/1499698 (Note: function calls in `FROM` clause are implicit `LATERAL JOIN`s after 9.3). – pozs Mar 14 '17 at 15:13
  • @pozs No, I'm not sure it has 9.3+. Here comes a naive question: how can I check/change that in the test environment? Regarding the implicit `LATERAL JOIN`, what is the implication of that? Performance? Is there a better way of doing it without a `LATERAL JOIN`? Thanks for bearing with me – I'm happy to read up relevant sections of docs/articles if you have suggestions. – Stuart Mar 14 '17 at 16:09
  • Making progress – [Codeship docs](https://documentation.codeship.com/basic/databases/postgresql/) indicate default version of PostgreSQL is 9.2, and shows how to configure alternate versions for Rails projects. – Stuart Mar 14 '17 at 16:37
  • @stuart, Did you get this working with the documentation link above? If not, could you reach out to us via https://helpdesk.codeship.com so we can help getting your builds to green? Thanks :) – mlocher Mar 15 '17 at 08:30
  • @mlocher Thanks for reaching out – I did get it working, thank you! The documentation I linked in my last comment contained all the information I needed to get the tests running on the correct version of PostgreSQL, and that fixed the error. I'll add an answer here to help direct people who find themselves in a similar situation to the solution. – Stuart Mar 15 '17 at 12:27
  • @Stuart if you're not figured out yet: there is no implication, other that the JOIN will always be LATERAL (with function calls in the FROM clause). It is implicit in a manner that you don't have to write it / notify the engine about you want a LATERAL JOIN, because it will be one anyway. You *can* write the `LATERAL` keyword there, if you want to, but it will change nothing. – pozs Mar 16 '17 at 09:59
  • @pozs My question was more asking what a LATERAL JOIN is/does, compared to other types of JOIN. I think it's too fundamental a question for you to address here – I'll do some reading on core SQL topics. – Stuart Mar 16 '17 at 18:51

1 Answers1

1

As pointed out by @pozs, the error appears to be the result of the test environment being set up with the wrong version of PostgreSQL. At the time of writing Codeship uses version 9.2 by default.

This Codeship documentation page details how to change that default, by changing the port in the database.yml file:

PostgreSQL version 9.6 is running on port 5436 and configured (almost) identical to the others. Make sure to specify the correct port in your project configuration if you want to test against this version.

Similar to the other versions, you need to work around our auto-configuration for Rails based projects by adding the following command to your Setup Commands.

sed -i "s|5432|5436|" "config/database.yml"
Stuart
  • 36,683
  • 19
  • 101
  • 139