Error when ordering by column in related table when using custom query

[Mattusdk]

I'm submitting a ...

• bug report
• feature request
• [x ] question

PostGraphile version: postgraphile@4.5.0
pg-order-by-related version: @graphile-contrib/pg-order-by-related@1.0.0-beta.6

Minimal SQL file that can be loaded into a clean database:

create table foo (
  id integer primary key,
  foo_name text not null
);

create table bar (
  id integer primary key,
  bar_name text not null,
  foo_id integer references foo (id)
);

insert into foo (id, foo_name) values
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd');

insert into bar (id, bar_name, foo_id) values
  (1, 'one', 1),
  (2, 'two', 2),
  (3, 'three', 3),
  (4, 'four', 4);

CREATE INDEX ON bar(foo_id);

CREATE FUNCTION get_bar_custom_query() RETURNS SETOF bar AS $$
SELECT * FROM bar
$$ LANGUAGE sql STABLE;

COMMENT ON FUNCTION get_bar_custom_query() IS E'@sortable';

Query:

query {
  first: getBarCustomQuery(orderBy: FOO_BY_FOO_ID__ID_ASC, first: 2) {
    pageInfo {
      endCursor
      hasNextPage
      hasPreviousPage
      startCursor
    }
    edges {
      node {
        id
        barName
        foo {
          fooName
          id
        }
      }
    }
  }
  second: getBarCustomQuery(orderBy: FOO_BY_FOO_ID__ID_ASC, first: 2, after: "WyJmb29fYnlfZm9vX2lkX19pZF9hc2MiLDJd") {
    pageInfo {
      endCursor
      hasNextPage
      hasPreviousPage
      startCursor
    }
    edges {
      node {
        id
        barName
        foo {
          fooName
          id
        }
      }
    }
  }
}

CLI command:

postgraphile \                                      
  --dynamic-json \
  --no-setof-functions-contain-nulls \
  --no-ignore-rbac \
  --no-ignore-indexes \
  --show-error-stack=json \
  --extended-errors hint,detail,errcode \
  --append-plugins @graphile-contrib/pg-simplify-inflector,@graphile-contrib/pg-order-by-related \
  --enhance-graphiql \
  --allow-explain \
  --enable-query-batching \
  --legacy-relations omit \
  --connection postgres://user:password@localhost:5432/my_db \
  --schema public

Current behavior:

Error: "The order supplied is not unique, so before/after cursors cannot be used. Please ensure the supplied order includes all the columns from the primary key or a unique constraint."

Expected behavior:

Result will be ordered by the column from the related table.

[benjie]

Does this only happen on custom queries, or does it affect tables too? It's very hard to guarantee unique ordering with functions, and I'd recommend against custom ordering over large result sets from custom queries/computed columns for performance reasons. @pgQuery via makeExtendSchemaPlugin might be more appropriate for this.

[Mattusdk]

It works perfectly when using the base queries provided by PostGraphile, the problem only occurs when we try to do it with this custom query.
I guess we will just have to find another solution, I think we have a pretty complex/unique use case for this, because we initially did not use a custom query for this, but switched to try to improve performance.

[benjie]

Can you add ID_ASC to the order list? That should make it unique (we do this automatically for table types, but not for functions because ordering in functions is expensive).

[benjie]

But if the ordering that you've specified is unique, then this plugin needs to indicate so.

[Mattusdk]

I have tested adding ID_ASC to the list and it does seem to have fixed the problem.

It does seem that, at least in the case I am testing, the ordering I specify should be unique, if I'm understanding everything correctly. Below is a simple reproduction of the setup we have:

create table foo (
  id integer primary key,
  foo_name text not null
);

create table bar (
  id integer primary key,
  bar_name text not null,
  foo_id integer unique references foo (id)
);

The only real difference between this example and the one above is that the foreign key foo_id has a unique constraint. If the orderBy clause is set to FOO_BY_FOO_ID__ID_ASC in a similar query to the one above, it should be unique, no?

[benjie]

This one with a unique constraint would be unique, yes. Without wouldn't be. This is equivalent to just sorting by FOO_ID_ASC directly?

[Mattusdk]

Yes, exactly, and that is the strange thing when querying a custom query, the pagination works correctly when ordering by FOO_ID_ASC but not when ordering by FOO_BY_FOO_ID__ID_ASC, even though they should be exactly the same and should both be unique.

But like we found out before it does work correctly when ordering by both FOO_BY_FOO_ID__ID_ASC and ID_ASC.

[benjie]

Basically the plugin needs to declare that the order is unique, like we do in core here:

https://github.com/graphile/graphile-engine/blob/9dca5c8631e6c336b59c499d901c774d41825c60/packages/graphile-build-pg/src/plugins/PgOrderAllColumnsPlugin.js#L37-L39