PgPool parameterized queries

Hi,
I am not able to get parameterized queries to work with pgpool. I am using pg@6.1.5, Postgres 9.6 and pgpool-II 3.6.1. When I run the parameterized query, it runs fine the first time (when it is not cached). When I run it again, the resultset is returned from the cache and it is empty. Here's a script that I am using to query the the DB.
/////////////////////////////////////////////////////////////////////////
var pg = require('pg');

// instantiate a new client
// the client will read connection information from the same environment variables used by postgres cli tools
var client = new pg.Client();
client.connect(function (err) {
if (err) throw err;

// execute a query on our database
client.query('SELECT * from table where id=$1', [1], function (err, result) {
if (err) throw err;

// just print the result to the console
console.log(result.rows[0]);

// disconnect the client
client.end(function (err) {
  if (err) throw err;
});

});
});
/////////////////////////////////////////////////////////////////////////

[brianc]

I appreciate you filing an issue here & would like to try and help; however, I receive multiple issues & request for help on a nearly daily basis and having properly formatted code is something that really makes triaging things easier - could you update your issue text by applying some proper formatting to your code? thanks!

Hi Brian,
Sorry, my first time posting here. I've reposted with formatted code.

Hi,
I am not able to get parameterized queries to work with pgpool. I am using pg@6.1.5, Postgres 9.6 and pgpool-II 3.6.1. When I run the parameterized query, it runs fine the first time (when it is not cached). When I run it again, the resultset is returned from the cache and it is empty. Here's a script that I am using to query the the DB.

var pg = require('pg');

// instantiate a new client
// the client will read connection information from the same environment variables used by postgres cli tools
var client = new pg.Client();
client.connect(function (err) {
  if (err) throw err;
  // execute a query on our database
  client.query('SELECT * from table where id=$1', [1], function (err, result) {
   if (err) throw err;
  // just print the result to the console
  console.log(result.rows[0]);
  // disconnect the client
  client.end(function (err) {
    if (err) throw err;
  });
 });
});

[brianc]

Hey @Goose53 - node-postgres never caches resultsets returned from the server under any circumstances. I'm not sure why you're getting an empty resultset but it's likely a problem wth your app code. I see nothing wrong w/ the code above - do you have a self-contained example? Maybe use CREATE TEMP TABLE in the beginning of your script so I can run it and see the behavior?

[brianc]

Closing because I haven't heard back with more info. Feel free to post back w/ more info & we can reopen this & look at it more! 🙇‍♂️

[Foxprodev]

#513
Looks similar.
In my case:

• I'am using parameterized queries and PGPool-II
• I've got no errors(even no timeout).
• My queries callback stacked forever.
• My connection pool is full of stacked connections :)

Looks like we got this since 2014

This turned out to be an issue with PgPool-II. The authors of PgPool II have fixed the issue in the in the July 2017 release. I was able to verify that the fixed resolved my problem

http://www.pgpool.net/pipermail/pgpool-general/2017-May/005569.html
http://www.pgpool.net/docs/latest/en/html/release-3-6-5.html (Issue 5511)