Issue with cursor pagination on a materialized view

[kalepail]

I'm submitting a ...

• bug report
• feature request
• question

PostGraphile version: 4.0.0-rc.4

Having an issue with cursor pagination on a materialized view.

If we go here: https://core-test.gly.sh/graphiql

And enter this query

query listTrustlines(
  $after: Cursor
  $first: Int
  $before: Cursor
  $last: Int
) {
  trustlines: allCgAssets(
    after: $after
    first: $first
    before: $before
    last: $last
  ) {
    nodes {
      assetcode
    }
    pageInfo {
      hasNextPage
      hasPreviousPage
      startCursor
      endCursor
    }
    totalCount
  }
}

With these params

{
  "after": null,
  "first": 144,
  "before": null, 
  "last": null
}

We get this page info

"pageInfo": {
        "hasNextPage": true,
        "hasPreviousPage": false,
        "startCursor": "WyJuYXR1cmFsIiwxXQ==",
        "endCursor": "WyJuYXR1cmFsIiwxNDRd"
      }

If we then use the endCursor we can paginate down through the rows

{
  "after": "WyJuYXR1cmFsIiwxNDRd",
  "first": 144,
  "before": null, 
  "last": null
}

"pageInfo": {
        "hasNextPage": true,
        "hasPreviousPage": true,
        "startCursor": "WyJuYXR1cmFsIiwxNDVd",
        "endCursor": "WyJuYXR1cmFsIiwyODhd"
      }

However now if we try and go back with the startCursor

{
  "after": null,
  "first": null,
  "before": "WyJuYXR1cmFsIiwxNDVd", 
  "last": 144
}

We get

"pageInfo": {
        "hasNextPage": false,
        "hasPreviousPage": false,
        "startCursor": "WyJuYXR1cmFsIiwxXQ==",
        "endCursor": "WyJuYXR1cmFsIiwxNDRd"
      }

Which you'll see is the same as our initial call before entering cursors except the hasNextPage is false. It should be true.

Hmm... 🤔

I'm not very familiar with Postgres. I'm tying into the public Stellar database so while I control my own instance I'm more familiar with GraphQL than with Postgres.

Here's the command I'm using to generate the table.

CREATE MATERIALIZED VIEW cg_assets
AS
SELECT DISTINCT assetcode, lastmodified FROM trustlines t INNER JOIN cg_issuers i ON t.issuer = i.issuer
ORDER BY lastmodified DESC, assetcode ASC
WITH DATA

My guess is it's something to due with ordering or key uniqueness but I'm not smart enough to actually know.

[benjie]

@tyvdh Sorry it's taken me so long to get around to fixing this; I knew it was going to be a pain because I remembered the pain of implementing it the first time!

I've implemented a fix in this PR:

https://github.com/graphile/graphile-build/pull/297/files

I've heavily documented the logic to make it easier for people to tweak in future if it contains bugs; including adding a bunch of assert calls.

[kalepail]

@benjie Installed today. All fixed. Thank you so much.

[benjie]

Awesome; thanks for the bug report 👍