Cloud Functions: Query timeout with max = 1

[seriousManual]

Hello, the Google Cloud Functions documentation recommends to use a Pool with a specified max size of 1 (https://cloud.google.com/sql/docs/mysql/connect-functions).

When setting max to one, the pool connects successfully, but times out when querying any table. A minimal example:

async function run() {
    const pool = new Pool({
        max: 1,
        host: configuration.database.postgres.host,
        user: configuration.database.postgres.user,
        password: configuration.database.postgres.password,
        database: configuration.database.postgres.database,
    });

    await pool.connect();
    console.log('connect');

    const result = await pool.query('SELECT * FROM "user" limit 1');
    console.log(result.rows);
}

run()

Do you a hint for me how I can mitigate this? Thanks in advance!

[charmander]

Does it not time out when not setting max: 1?

[seriousManual]

Hi @charmander ,

absolutely, the part max: 1 was the only thing that made the piece of code suceed or timeout.

[charmander]

Oh, you’re trying to connect two clients at the same time without releasing the first client until the second client is done. That won’t work with a max: 1 pool. In case you’re misunderstanding the API: pool.connect() acquires a client, it doesn’t start the pool or anything.

So either do this:

async function run() {
    const pool = new Pool({
        max: 1,
        host: configuration.database.postgres.host,
        user: configuration.database.postgres.user,
        password: configuration.database.postgres.password,
        database: configuration.database.postgres.database,
    });

    const client = await pool.connect();
    console.log('connect');
    client.release();

    const result = await pool.query('SELECT * FROM "user" limit 1');
    console.log(result.rows);
}

run()

or this:

async function run() {
    const pool = new Pool({
        max: 1,
        host: configuration.database.postgres.host,
        user: configuration.database.postgres.user,
        password: configuration.database.postgres.password,
        database: configuration.database.postgres.database,
    });

    const result = await pool.query('SELECT * FROM "user" limit 1');
    console.log(result.rows);
}

run()

[seriousManual]

Oh, thanks for the clarification! So the problem was that it is not necessary to explicitly call connect on the pool because this is implicitly done when running a query?

Thank you very much!

[charmander]

@seriousManual Yes. (And any time you get a client with pool.connect(), it has to be released with client.release(). https://node-postgres.com/features/pooling/)