porsager / porsager/postgres

Wrong inferred array types when invoking `sql.array()` for the first time.

Open
#789 1 comment 6 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
JavaScript
Stars
8.7k
Forks
374
Avg merge
11d 16h
Merged PRs (30d)
1

Description

When using sql.array() immediately after connection is made, the inferred data type if wrong.

import { postgres } from './deps.ts';

const sql = postgres(Deno.env.get('DATABASE_URL')!, {
    debug: (conn, query, params, types) => {
        console.log('conn:', conn);
        console.log('query:', query);
        console.log('params:', params);
        console.log('types:', types);
        console.log('-----\n');
    },
});

const r1 = await sql`SELECT ${sql.array(['text', 'array'])};`;
console.log('first:', r1);
console.log('=====\n');

const r2 = await sql`SELECT ${sql.array(['text', 'array'])};`;
console.log('second:', r2);
console.log('=====\n');

Code above gives following output:

conn: 2
query: 
      select b.oid, b.typarray
      from pg_catalog.pg_type a
      left join pg_catalog.pg_type b on b.oid = a.typelem
      where a.typcategory = 'A'
      group by b.oid, b.typarray
      order by b.oid
    
params: []
types: []
-----

conn: 2
query: SELECT $1;
params: [ [ "text", "array" ] ]
types: [ 25 ]
-----

first: Result(1) [ { "?column?": "text,array" } ]
=====

conn: 2
query: SELECT $1;
params: [ [ "text", "array" ] ]
types: [ 1009 ]
-----

second: Result(1) [ { "?column?": [ "text", "array" ] } ]
=====

It seems the first query failed to utilize type oids from the initial pg_type query, thus treating the array as 25 TEXT.
The second (and so on) query does set the correct type to 1009 TEXT[].

The bug occurs both in deno and node, using latest postgres.js 3.4.3

Maybe there's a bug for the type query thing, or maybe we should always default type of arrays to 1009 TEXT[]?

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by running the issue's Deno reproduction and inspect the initial pg_catalog.pg_type query, followed by the first parameter type inference for sql.array(). Trace why the first query uses OID 25 while the second uses 1009; done means the first and subsequent array queries infer TEXT[] consistently in both Deno and Node.

Written by the indexing model from the issue text.

Assessment

Tech stack
deno, javascript, nodejs, postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.