Record not able to insert properly using pool.query

[sawrubgupta]

Hello I have been using this library for more than 2 years but never faced this issue but in this few weeks I am facing a issue problem

I am using database pooling

this is my configuration

import pg from 'pg';
const Pool = pg.Pool;

const dbPort = Number(process.env.DBPORT || 5432);

  const pool = new Pool({
  user: process.env.DB_USERNAME || 'localhost',
  host: process.env.HOST || '127.0.0.1',
  database:process.env.DATABASE || 'ligh',
  password: process.env.PASS || 'password',
  port: dbPort  ,
  idleTimeoutMillis: 30000, // 30 seconds
  min:2,
  max:20,
  connectionTimeoutMillis: 15000

  }); 

Now issue is I hit a create record api and then hit list record api, everything work perfectly fine till now but after some seconds, my newly inserted record disappears automatically.

No delete query is running sometimes it work and sometimes it doesnt.

import pool from '../../db';

export const createWhatPlugin = async (req: Request, res: Response) => {

    try {
		const addWhatPlugin = `INSERT INTO fiat_what_paywall(id, price) VALUES($1, $2)`;
        const pluginlValues = [whatPluginId, price];
        const whatPluginRows = await pool.query(addWhatPlugin, pluginlValues);
		
		console.log(whatPluginRows.rowCount)
		
		  } catch (e) {
        return response.somethingWentWrong(res);
    }
	}

List of whatplugin api

export const whatPluginList = async (req: Request, res: Response) => {

    try {
        const uid = res.locals.uid;

        const whatPluginList = `SELECT id, price FROM fiat_what_paywall `;
        const whatPluginRows = await pool.query(whatPluginList, whatPluginValues);

        if (whatPluginRows.rowCount === 0) return response.successMessage(res, whatPluginRows.rows, "No what plugin found");

        let result = whatPluginRows.rows

        return response.successMessage(res, result, "what Plugin list");

    } catch (e) {
        return response.somethingWentWrong(res);
    }
}

Now after hitting createWhatPlugin we hit whatPluginList api, and data comes in list api but after sometimes data automatically disappear.

I believe maybe its library issue ? can you please let me know 🙏

[charmander]

Are you using transactions anywhere? If you leave a client in an open transaction state without committing it or rolling it back before returning it to the pool, that transaction will never be committed, but other queries that happen to check out the same client will see its effects.

[sawrubgupta]

Hello, sorry for late reply, So wherever the API needs transaction I uses transaction and at other places I use simple pool.query So you are saying, whenever error occurs in error case I should use ROLLBACK ? This is my transaction API body

import pool from './db'
const client = await poo.connect();
try{
await client.query('BEGIN');

// all the client querty executes here

await client.query("COMMIT");

}catch(){

}finally{
  client.release();
}

I think after client.release if there is no commit. rollback automatically happen right ? Please enlighten me, if I am doing anything wrong 🙏

[brianc]

Yeah definitely need a rollback there in catch. otherwise if there is an error your txn will be left open but the client will be released back to the pool

On Mon, Aug 5, 2024 at 7:38 AM sawrubgupta @.***> wrote:

Hello, sorry for late reply, So wherever the API needs transaction I uses transaction and at other places I use simple pool.query So you are saying, whenever error occurs in error case I should use ROLLBACK ? This is my transaction API body

import pool from './db' const client = await poo.connect(); try{ await client.query('BEGIN');

// all the client querty executes here

await client.query("COMMIT");

}catch(){

}finally{ client.release(); }

— Reply to this email directly, view it on GitHub https://github.com/brianc/node-postgres/issues/3286#issuecomment-2268865493, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAMHINX37K2YDQDY4BWKB3ZP5P47AVCNFSM6AAAAABLWFUZNKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRYHA3DKNBZGM . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[sawrubgupta]

Yes, I added rollback everywhere in my code where i am using transaction, weird issues are coming up in code .

suppose sometime code is stuck at

const rows = await client.query(sql, val);

the code dont go below these lines, idk its issue with db or transsaction but I am using transaction in correct way.

Now this issue also comes sometimes

C:\Users\Saurabh\Desktop\prodninjamicroServceProd\extensionAndwebService\node_modules\pg\lib\client.js:132
      const error = this._ending ? new Error('Connection terminated') : new Error('Connection terminated unexpectedly')      
                                                                        ^
Error: Connection terminated unexpectedly

[sawrubgupta]

Guys please help, Its really scary, customer enters data but its not entering