ForbesLindesay/atdatabases

BulkUpdate with GIS data throws "malformed array literal" error

Opened this issue · 0 comments

Hi, I've been experiencing a bug where when I try to use pg-typed to bulk update items the process fails with a "malformed array literal" error.

Example:

import createConnectionPool, { ConnectionPool, Transaction } from '@databases/pg';

const db = createConnectionPool({
    connectionString: process.env.DATABASE,
    bigIntMode: 'number',
});

const ways = (d: ConnectionPool | Transaction = db) => databaseTables.ways(d);

const features = [
    {
        source_id: '1',
        name: 'Example 1',
        way_start: 'POINT(-0.3019319 51.4964681)',
        way_end: 'POINT(-0.3002367 51.4971694)',
        geometry: 'LINESTRING(-0.3019319 51.4964681, -0.3013388 51.4966133, -0.3000865 51.4968956, -0.3002367 51.4971694)'
    },
    {
        source_id: '2',
        name: 'Example 2,
        way_start: 'POINT(-0.3019596 51.4961875)',
        way_end: 'POINT(-0.3023104 51.4969638)',
        geometry: 'LINESTRING(-0.3019596 51.4961875, -0.3021035 51.4964748, -0.3022645 51.4967419, -0.3023718 51.4969482, -0.3023104 51.4969638)'
    },
    ...
];

await ways(db).bulkUpdate({
    whereColumnNames: [`source_id`],
    setColumnNames: [
        `name`,
        `way_start`,
        `way_end`,
        `geometry`,
    ],
    updates: features.map(({ source_id, name, way_start, way_end, geometry }) => ({
        where: { source_id },
        set: {
            name,
            way_start,
            way_end,
            geometry,
        },
    }),
});

Where ways table is configured like:

await db.query(
    sql`CREATE TABLE IF NOT EXISTS ways (
        source_id TYPE VARCHAR(255),
        name VARCHAR(255),
        way_start GEOGRAPHY(POINT, 4326) NOT NULL,
        way_end GEOGRAPHY(POINT, 4326) NOT NULL,
        geometry GEOGRAPHY(LINESTRING, 4326) NOT NULL
    )`
);

Error:

error: malformed array literal: "{"POINT(-4.7755398 55.9439548)","POINT(-4.7712995 55.94922)","POINT(-1.8069079 53.185004)", ....
at handleError node_modules/@databases/pg/src/Driver.ts:457:25)
at executeQueryInternal node_modules/@databases/pg/src/Driver.ts:419:5)
at processTicksAndRejections (node:internal/process/task_queues:95:5)
at async PgDriver._executeQuery node_modules/@databases/pg/src/Driver.ts:234:23)
at async PgDriver.executeAndReturnLast node_modules/@databases/pg/src/Driver.ts:266:12)
at async queryInternal node_modules/@databases/shared/src/utils.ts:49:21)
at async ConnectionPool._withDriverFromPool node_modules/@databases/shared/src/BaseConnectionPool.ts:56:22)
at async bulkUpdate node_modules/@databases/pg-bulk/src/index.ts:206:12)
at async Table.bulkUpdate node_modules/@databases/pg-typed/src/index.ts:962:12)

Versions:

Package Version
@databases/cache 1.0.0
@databases/pg 5.5.0
@databases/pg-migrations 5.0.2
@databases/pg-typed 4.4.1

Notes:

  • Features are always limited to a maximum of 1000 (I batch my changes), but unsure whether this could be related to long LINESTRINGs, although the error is about POINTs.
  • The batchUpdate works if I remove way_start, way_end, and geometry. I've tried removing each of those individually but any of the geometry types will trigger the error.

For now I've been working around this by updating the items individually (although this often results in a Error: Timed out waiting for connection from pool. - which I will look into separately.