Suddenly "String cannot represent value" error when querying geometry column

[barbalex]

I'm submitting a ...

• bug report

PostGraphile version: 4.5.5

Steps to reproduce:

I am running postgraphile like this:

DEBUG="graphile-build:warn" npx postgraphile \
  --append-plugins postgraphile-plugin-connection-filter,@graphile-contrib/pg-order-by-related  \
  --jwt-token-identifier auth.jwt_token \
  --default-role postgres \
  --jwt-secret secret \
  -c postgres://user:secret@localhost:5432/apflora \
  -s apflora \
  --cors \
  --enable-query-batching \
  --enhance-graphiql \
  --watch \
  --export-schema-json schema.json

In two of 50 tables we use geometry fields for points that we have so far managed according to #575 (comment) i.e. without using @graphile/postgis. The reason being that I have so far not managed to get updating a geometry to work with @graphile/postgis (see graphile/postgis#30), plus: @graphile/postgis didn't exist yet back then.

So we are querying the coordinates using functions to add computed columns. And updating them by passing a string like

`SRID=4326;POINT(${long} ${lat})`

into the geometry field. Which graphiql shows as being of type String.

This worked for probably almost a year now.

Today it started throwing this error:

apollo client GraphQL error: Message: String cannot represent value: { type: "Point", coordinates: [8.18270745945302, 47.8113666059014] }, Location: [{"line":22,"column":3}], Path: allTpops,nodes,3,geomPoint

What could be the problem?

[benjie]

Can you submit a small SQL schema and GraphQL query that reproduces this issue please? The issue seems to be coming from the geomPoint field on Tpops.

[barbalex]

(I am using the error from table pop which is same as for table tpop)

The SQL schema is here: https://github.com/barbalex/apf2/blob/master/sql/apflora/createTables.sql#L427-L440,
the important part being:

CREATE TABLE apflora.pop (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v1mc(),
  ap_id UUID DEFAULT NULL REFERENCES apflora.ap (id) ON DELETE CASCADE ON UPDATE CASCADE,
  nr integer DEFAULT NULL,
  name varchar(150) DEFAULT NULL,
  status integer DEFAULT NULL REFERENCES apflora.pop_status_werte (code) ON DELETE SET NULL ON UPDATE CASCADE,
  status_unklar boolean default false,
  status_unklar_begruendung text DEFAULT NULL,
  bekannt_seit smallint DEFAULT NULL,
  geom_point geometry(Point, 4326) default null,
  changed date DEFAULT NOW(),
  changed_by varchar(20) DEFAULT null
);

The query is (copied from the request):

query TreePopsQuery($isAp: Boolean!, $filter: PopFilter!) {
  allPops(filter: $filter, orderBy: [NR_ASC, NAME_ASC]) @include(if: $isAp) {
    nodes {
      ...PopFields
      __typename
    }
    __typename
  }
}

fragment PopFields on Pop {
  id
  label
  apId
  nr
  name
  status
  statusUnklar
  statusUnklarBegruendung
  bekanntSeit
  lv95X
  wgs84Lat
  lv95Y
  wgs84Long
  geomPoint
  changedBy
  __typename
}

variables are:

{isAp: true, filter: {apId: {in: ["6c52d174-4f62-11e7-aebe-67a303eb0640"]}}}

Field label is computed:

create function apflora.pop_label(pop apflora.pop) returns text as $$
  select coalesce(pop.nr::text, '(keine Nr)') || ': ' || coalesce(pop.name, '(kein Name)')
$$ language sql stable;
-- make label sortable, as of postgraphile 4.4/postgraphile@next
comment on function apflora.pop_label(apflora.pop) is e'@sortable';

[barbalex]

When I uncomment the geomPoint field from the fragment, the error unsurprisingly does not occur.

[benjie]

Did you upgrade Postgres or PostGIS recently? It’s like to_json of the geomPoint has changed.

[barbalex]

Yes, I work on a new computer with postgres 12 (previous 11) and newly installed postgis (so probably also a newer version).

How would that influence to_json?

[benjie]

Previously I think Postgres' to_json was converting the value to a string. Now, it seems to be converting it to GeoJSON. But PostGraphile expects it to be a string (as it expects all unknown types to be String-able, this is a decision I regret but cannot change in v4).

[benjie]

Potential solution is to use tweakToText on it. Here's how that's done internally:

https://github.com/graphile/graphile-engine/blob/f0ed87f60addeee80d929bde7407f7c434463db9/packages/graphile-build-pg/src/plugins/PgTypesPlugin.ts#L456

In a plugin you'd need to write something similar in a build Graphile Engine hook.

[barbalex]

Thanks, that explains it.

I tried not reading the geometry field by removing it from the fragments and hoped that would solve the problem. But the query that updates it also throws this error.

Would using @graphile/postgis maybe circumvent the problem?

(I can't try it as I haven't managed to update the geometry using it graphile/postgis#30)

[barbalex]

Actually: using @graphile/postgis I can query the data nicely. So if I manage to update it that would be great.

[benjie]

I'm going to close this because PostGIS types aren't explicitly supported without the PostGIS plugin (and you've managed to get updating working with that).