Parameter type hinting does not work with the native pg driver (switching between pg and pg.native)

[OJezu]

The following snippet

pg = require('pg');
pg.connect(con_str, function(error , client, done){
    client.query({
            text: 'UPDATE \
                    action_stats \
                SET \
                    counter = counter + 1 \
                WHERE \
                    user_id = $1 \
                    AND (($2 IS NULL AND group_id IS NULL) OR ($2 IS NOT NULL AND group_id = $2))',
            types: [25, 25] //hint types to postgresql so it has no problem interpreting when $2 is null
        }, ['user-1', null], function(result, error){
            console.log(result, error);
            done();
        });
})

does work when pg is non-native driver. However, when pg is native

--- pg = require('pg');
+++ pg = require('pg').native;

it results in the following error:

{ [Error: could not determine data type of parameter $2]
  severity: 'ERROR',
  code: '42P08',
  position: '100',
  file: 'parse_param.c',
  line: '305',
  routine: 'check_parameter_resolution_walker' }

Keep up the great work :)

[brianc]

Hey thanks. 😄

Sorry you're running into this issue. I'll have to look at adding those to the native bindings.

[brianc]

Is there any reason you have to use parameter type hinting instead of hinting with the type in the query? I think generally its better to use the hint in the query like $1::text because the OIDs are harder to reason about in your code. Also, not 100% sure the OIDs stay the same between versions?

[OJezu]

I think that in that case, the type of the parameter has to be dynamic, as $2::text IS NULL will be false while $2 actually is null.
To be honest I cannot be bothered to check, since we refactored the part that needed that construct I think over a year ago now. Now we are just altering the query, and doing just IS NULL or = check.
As far as I remember, I thought to myself "While this is a nice hack, of which I am very proud, the »different query« approach will probably index better".

To be honest, I think that maybe removing type hinting from non-native may be the best solution.

[charmander]

types isn’t documented (anymore?).