"Where any" query doesn't seem to work

[JwanKhalaf]

I have a fairly simple query (Line 21):

Line 20 never gets executed and I'm not sure why. Line 22 does.

I'm guessing something is wrong with my query, but I have followed the Wiki with regards to "where in/any". What am I missing?

I tried where name in ($1::text, $2::text) and [redFighter, blueFighter] but that too didn't work!

I'm really confused.

[charmander]

The callback never being called suggests something wrong with the connection, not with the query. Do other queries in the same spot work (like SELECT 1)? Which version of PostgreSQL are you using (or is it a different database server that claims to be PostgreSQL-compatible)?

[JwanKhalaf]

Thanks for the reply @charmander.

All queries go through this:

const pool = new Pool();

module.exports = {
    query: (text, params) => pool.query(text, params),
};

I'm using a Postgres instance on Elephant SQL. Version is:

psql (13.2, server 11.6 (Ubuntu 11.6-1.pgdg18.04+1)

I added another simple query like you suggested:

And that does work, that gets printed out to the console! But not the other query with the where in/any.

[charmander]

@JwanKhalaf Please try a query with parameters against a normal, non-Elephant-SQL database if you can. (It can still be non-local.) This might be a bug in that service.

[JwanKhalaf]

@charmander thanks, I just tried it against a local Postgres database and I got the same problem. :(

[JwanKhalaf]

I wonder if it has something to do with the fact that the whole code gets called every 3 seconds? The issue is in the last method below, but I've added other code for context. Basically, dataObserver gets called every 3 seconds to check some stuff on an API and does some reading/writing to the database.

setInterval(dataObserver, 3000);

function dataObserver() {
  request(stateUrl, function (err, statusCode, data) {
    if (err) {
      log.message(data, "debug");
      log.message('0: ' + err.message, "error");
    } else {
      try {
        fightData = parseJson(data);
      } catch (error) {
        log.message('1: ' + error, "error");
      }

      matchCheck = fightData.remaining;
      statusCheck = fightData.status;

      setMatchType();
      setMatchStatus();

      if (matchStatus != oldStatus && matchType != 'Exhibition') {
        oldStatus = matchStatus;
        let redFighter = fightData.p1name;
        let blueFighter = fightData.p2name;
        let redBets = fightData.p1total;
        let blueBets = fightData.p2total;

        switch (matchStatus) {
          case 'open':
            break;
          case 'locked':
            break;
          case 'redWon':
            checkDatabase(redFighter, blueFighter);
            setTimeout(function () {
              addMatch(redFighter, blueFighter, redBets, blueBets, redFighter);
              addMatchResults(redFighter, blueFighter);
              addFavor(redFighter, blueFighter, redBets, blueBets);
            }, 10000);
            break;
          case 'blueWon':
            checkDatabase(redFighter, blueFighter);
            setTimeout(function () {
              addMatch(redFighter, blueFighter, redBets, blueBets, blueFighter);
              addMatchResults(blueFighter, redFighter);
              addFavor(redFighter, blueFighter, redBets, blueBets);
            }, 10000);
            break;
          default:
            log.message('Unknown match status!', "error");
            break;
        }
      }
    }
  })
};

// this is where the issue is
function checkDatabase(redFighter, blueFighter) {
  console.log('checking database!');
  db.query('select name from fightertable where name = any($1)', [[redFighter, blueFighter]], (err, res) => {
    console.log('we are inside check database query');
    if (err) {
      log.message('2: ' + err.message, "error");
    } else if (typeof res.rows[0] == 'undefined' && typeof res.rows[1] == 'undefined') {
      addFighterName(redFighter, blueFighter);
    } else if (res.rows[0].name == redFighter) {
      addFighterName(blueFighter);
    } else if (res.rows[0].name == blueFighter) {
      addFighterName(redFighter);
    } else {
      log.message('Database check failed!', "error");
    }
  });
}

[JwanKhalaf]

hmm, I put the call to checkDatabase inside a setTimeout() and gave it 10 seconds, and still got the same problem :(

[noisy-murmure]

I have the same style of issues, the following code always return "undefined" (but if i had console.log:

const testTokenQuery = async (account_id, query) => {
    pool.query(query, [account_id], function(err, result) {
    // If I had console.log(String(JSON.stringify(result.rows[0].ac_account_id))) ===> That return the good answer
      return String(JSON.stringify(result.rows[0].ac_account_id))
    })
}
const testToken = async (token) => {
    const checkTokenId_query = sql = fs.readFileSync('./sqlqueries/checkTokenId_query.sql').toString() //===> contains my query, a select by id ...
    const decodedToken = jwt.decode(token, 'JWT_SIGN_SECRET')
    const tok_account_id = decodedToken.account_id
    return await testTokenQuery(tok_account_id, checkTokenId_query)
}

const tokenReturn = async (token) => {
    console.log(await testToken(token))
}

tokenReturn('eyJ..............blablabla......ValidToken...................JT0PhXCjV00') // ==========> return "undefined"

It's like if the result was ready after it was returned ...

[charmander]

@noisy-murmure That’s caused by mixing callbacks and promises incorrectly.

const testTokenQuery = async (account_id, query) => {
    const result = await pool.query(query, [account_id])
    return JSON.stringify(result.rows[0].ac_account_id)
}

[charmander]

@JwanKhalaf Do you use db.connect anywhere?

[noisy-murmure]

Thanx charmander ! That's a step forward for me ...

But now I have a "Promise { pending }" message ...

The objective is not to write in the console log but to return the value for evaluation in a "mother-function" ... console.log is just for testing, but the function don't return value :

const testTokenQuery = async (account_id, query) => {
    const results = await pool.query(query, [account_id])
    return JSON.stringify(results.rows[0].ac_account_id)
}
const testToken = async (token) => {
    const checkTokenId_query = sql = fs.readFileSync('./sqlqueries/checkTokenId_query.sql').toString()
    const decodedToken = jwt.decode(token, 'JWT_SIGN_SECRET')
    const tok_account_id = decodedToken.account_id
    return await testTokenQuery(tok_account_id, checkTokenId_query)
}

const tokenReturn = async (token) => {
    console.log(await testToken(token)) //=====> That's works but it's not what i want
    return await testToken(token) //====> That's return "Promise { <pending>}" ... = [
}

console.log(tokenReturn('eyJhbG...Validtoken...CjV00')) ===> Promise { <pending>}

Do you have an idea please ? Thanx for your help.

[charmander]

@noisy-murmure Introduction to promises/async is out of the scope of pg issues, sorry.

[charmander]

query: (text, params) => pool.query(text, params),

The code uses a callback, but this wrapper function doesn’t pass the callback.