Index out of bounds when creating a relation with a stripe foreign table

[thomtrp]

Describe the bug

Hello, I am from Twenty core team. I am using stripe foreign data wrapper.
 I created a foreign table customer (based on stripe customers) and it worked well, my data are available as expected.

But I have an issue when I try to create a relationship with one of my local object using pg_graphql.

Following the supabase doc, I tried to create a relation with my local object favorite. When trying to fetch the customers linked to a given favorite, it results with an error:

index out of bounds: the len is 1 but the index is 18446744073709551615

To Reproduce

Steps to reproduce the behavior:

1. Using stripe foreign data wrapper, create a foreign table based on a stripe object
2. Follow the supabase doc to create a relation with a local table. In my case I did:

• Created a comment on the foreign table customer @graphql({"primary_key_columns": ["id"], "totalCount": {"enabled": true}})
• Added a customerId field on my table favorite
• Created a comment on my table favorite so it knows customerId is a foreign key @graphql({"totalCount":{"enabled":true},"foreign_keys":[{"local_name":"favoriteCollection","local_columns":["customerId"],"foreign_name":"customer","foreign_schema":"my_schema","foreign_table":"customer","foreign_columns":["id"]}]})

3. Run a query that will test the relation. In my case that was:

query {
  favoriteCollection(filter: {workspaceMemberId:{eq:"123"}}) {
    edges {
      node {
        customer {
          id: id
        }
     }
   }
  }
}

Expected behavior

Customer object should be returned properly.

Additional context

If I add another object to fetch that would be working without customer, the query stops failing but customer is not returned:

query {
  favoriteCollection(filter: {workspaceMemberId:{eq:"123"}}) {
    edges {
      node {
        company {
          id: id
        }
        customer {
          id: id
        }
     }
   }
  }
}

If I add again another object to fetch, the error changes to: index out of bounds: the len is 1 but the index is 1

query {
  favoriteCollection(filter: {workspaceMemberId:{eq:"123"}}) {
    edges {
      node {
        company {
          id: id
        }
        user {
          id: id
        }
        customer {
          id: id
        }
     }
   }
  }
}

[olirice]

@imor this smells like a wrappers issue/limitation. any thoughts?

[olirice]

@thomtrp its worth mentioning that exposing foreign tables through APIs is not recommended and will raise a lint error in our security advisor. You can read more about why that's a risky idea in the linter docs

[imor]

@olirice Hard to say if it's pg_graphql or wrappers without more info, although if it occurs only with a foreign table then wrappers is likely involved. 18446744073709551615 is 2^64 - 1, so sounds like an underflow or incorrect conversion between u64 and i64.

@thomtrp do you see a similar error when you query the customer table using SQL directly? What about a SQL query joining customer and favourite tables? Are you able to write a GQL query in which only customer table is used and still see the error? Asking all these questions to try to isolate the interaction between pg_graphql and wrappers and see where the error is coming from.

[thomtrp]

@imor @olirice thank you for the answers, here are additional informations:

1. Fetching customers directly through pg_graphql queries works well. This kind of query works:

query { customerCollection(filter: {id:{eq:"cus_PcFjftKnRyMSLb"}}) { edges { node { id: id } } } }

2. Using relations without going through pg_graphql but through a join works as well:

select * from favorite f join customer c on f."customerId" = c.id

3. I also forgot to say that we are also using Postgres foreign data wrapper and we do not have this kind of issue. We can manage relations through pg_graphql. Main difference I see between both wrappers are:

• Stripe is using ids that are neither numbers or uuids but strings, such as cus_PcFjftKnRyMSLb for a customer. Maybe this is not properly managed by pg_graphql foreign key system for views?
• Foreign table options are not the same. Postgres uses {table_name: string, schema_name: string} while Stripe uses {object: string} since it does not have a schema.

[thomtrp]

@imor here is an additional information. I don't know yet if this is related:

query { customerCollection(first: 30) { edges { node { __typename id: id } cursor __typename } pageInfo { hasNextPage startCursor endCursor __typename } totalCount __typename } }

This query is failing with the error: FATAL: the database system is in recovery mode I just need to remove hasNextPage and totalCount so it works well.

Checking pg_graphql code, it looks like both are using a COUNT so this is probably the issue there. I tried to reproduce using normal sql queries but the COUNT works well on my customer table so it's really when I use pg_graphql.

Also, the same query on a foreign table that use postgres_fdw works well. This is only using stripe_fdw. Does this wrapper miss a count implementation? I did not see it in the code but I don't know how this is usually done.

Thank you!

[imor]

@thomtrp thanks for the information. Stripe's non-numeric ids are unlikely to be the cause. The fact that it works fine with postgres_fdw indicates it might be the Stripe wrapper might be causing the issue. The error about database being in recovery mode indicates that it crashed. This may or may not be related. I'll try to get the transpiled sql query which you can run directly to find out what the problem is. Failing that I'll try to reproduce the problem and see what is causing the error.