ignoreIndexes should not disable to-one relation [to->PRIMARY KEY]

[roytan883]

I'm submitting a ...

• [x] Bug
• [x] feature request

PostGraphile version: Library: 4.6.0

Steps to reproduce:

config ignoreIndexes: false

CREATE TABLE web.tb
(
    bid          VARCHAR(64) PRIMARY KEY NOT NULL ,
    content   TEXT
);
CREATE TABLE web.ta
(
    aid       VARCHAR(64) PRIMARY KEY NOT NULL,
    bid       VARCHAR(64)                 NOT NULL REFERENCES web.tb (bid),
    name   TEXT
);

• Current behavior:

can only call gql:

{
  allTas(first: 10) {
    nodes {
      aid
      bid
      name
    }
  }
}

• Expected behavior:

No need CREATE INDEX ON web.ta (bid);. Because bid already REFERENCE tb's PRIMARY KEY, so it should work when access from ta.bid->tb.

PS: I know that CREATE INDEX ON web.ta (bid); can enable both ta->tb and tb->ta, but in this case I only want ta->tb by PRIMARY KEY.

{
  allTas(first: 10) {
    nodes {
      aid
      bid
      name
      tbByBid{
        content
      }
    }
  }
}

The problem is that when ignoreIndexes: false， from table ta can not access tb by bid. I must create an index for bid at ta to enable access. But this index is no sence, because the table tb already has PRIMARY KEY index bid.

so, if i don't want create index on bid at ta, currently the weird way is create a Computed Column, like this:

CREATE OR REPLACE FUNCTION web.ta_tb_by_bid(obj web.ta)
    RETURNS web.tb AS
$$
SELECT *
    FROM web.tb
    WHERE bid = obj.bid
    LIMIT 1;
$$ LANGUAGE SQL STABLE;

Is this PK Computed Column high efficiency?

[benjie]

I recognise this issue; currently we tie the forward and reverse relations together since noIgnoreIndexes is implemented via the “omit” system which doesn’t currently differentiate. There won’t be a quick fix for this.

[roytan883]

So i use Computed Column to implement it, is it OK ? will it impact performance?

[benjie]

The most efficient way to implement it would be with @pgQuery directive via makeExtendSchemaPlugin; I cannot guarantee that your function will be inlined.

[benjie]

V5 is much smarter and does not make this mistake.