column "XXX_geom" of relation "XXX" does not exist

[JannikZed]

Bug report

Describe the bug

We are having some issues using your plugin. When trying to start for a large database, that has already several 100k entries, I'm running into this issue and I don't know, how to solve it - I can't see the column "XXX_geom" anywhere, so I was wondering, how exactly the plugin works.

error: 
          UPDATE pois
          SET ch_geo_points_geom = ST_SetSRID(ST_MakePoint(
              CAST((ch_geo_points::json->'lng')::text AS DOUBLE PRECISION),
              CAST((ch_geo_points::json->'lat')::text AS DOUBLE PRECISION)

          ), 4326)
          WHERE (ch_geo_points::json->'lng')::text != 'null' AND
                (ch_geo_points::json->'lat')::text != 'null'
           - column "ch_geo_points_geom" of relation "pois" does not exist
           - 

A clear and concise description of what the bug is.

Steps to reproduce the behavior

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior

A clear and concise description of what you expected to happen.

Screenshots

If applicable, add screenshots to help explain your problem.

Code snippets

If applicable, add code samples to help explain your problem.

System

• Node.js version:
• NPM version:
• Strapi version:
• Database:
• Operating system:

Additional context

Add any other context about the problem here.

[JannikZed]

so it looks like you are dynamically creating and destroying this field again .. why are you doing this? We have database with 200-500.000 entries and creating and destroying fields when starting and stopping makes the whole application unusable. Is there a specific reason, why you are dropping this database fields again?

[JannikZed]

          UPDATE pois
          SET ch_geo_points_geom = ST_SetSRID(ST_MakePoint(
              CAST((ch_geo_points::json->'lng')::text AS DOUBLE PRECISION),
              CAST((ch_geo_points::json->'lat')::text AS DOUBLE PRECISION)

          ), 4326)
          WHERE (ch_geo_points::json->'lng')::text != 'null' AND
                (ch_geo_points::json->'lat')::text != 'null'
           - deadlock detected

After some more restart tests, I'm now getting different errors:

detail: 'Process 27017 waits for ShareLock on transaction 1431111; blocked by process 27022.\n' +
    'Process 27022 waits for ShareLock on transaction 1431112; blocked by process 27017.',
  hint: 'See server log for query details.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'while updating tuple (935644,12) in relation "pois"',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'deadlock.c',
  line: '1130',
  routine: 'DeadLockReport'

[omikulcik]

HI @JannikZed, thank you for reporting this issue.

the xxx_geom field is created for every field that uses the location custom field because in the column without the _geom suffix the data is stored as JSON by Strapi and in the column with the suffix the data is stored in the geospatial point format. See the screenshot bellow. This is required for calculating distance etc.

As for the creating and dropping the fields, we assumed that Strapi validates the structure of database and would crash on startup if there were unknown extra columns. I will test whether this is true and if not I will remove this piece of code and publish a new version.