How to get Postgress function logs (RAISE NOTICE/ RAISE INFO) using nodejs server

[msudhayaraj]

Environment

Knex version: 0.20.11
Database + version: Pg 11
OS: Ubuntu 16.04 LTS

Question

How to get Postgress function logs ?
i am using knex and pg 11. i had some pg procedures and function,
i need to access the procedure log (RAISE NOTICE & RAISE INFO) using nodejs server.

i tried below code.

var reqKnex = require('knex');
var pConn = {
    host: 'SEVER',
    port: 'PORT',
    user: 'DBUSER',
    password: 'DBPASSWORD',
    database: 'DBNAME'
};
var knexConn = new reqKnex({
    client: 'pg',
    native: false,
    connection: pConn,
    pool: {
        "max": 300,
        "min": 10
    }
});
var query = "select * from pglog_test()";
knexConn.client.on('notice', function (msg) {
console.log("logs from postgress: ", msg.message);
});
knexConn.raw(query).then(function (result) {
    console.log(result);
}).catch(function (error) {
    console.log(error);
});

This is my sample nodejs code.
my pg procedure is

CREATE OR REPLACE FUNCTION pglog_test(
	)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
declare
 counter integer := 0 ; 
  n integer:= 10;
begin
loop
exit when counter = n ; 
counter := counter + 1 ; 
  RAISE NOTICE ' Test log %',counter;
  end loop; 
  return 'SUCCESS';
end;

$BODY$;

I need to get the procedure logs (RAISE NOTICE).
how can i get it. the same way i tried with pg npm that was works fine.
by using knex how to get pls suggest me.

working code using pg npm

const { Client } = require('pg');

const client = new Client({
    user: 'USER',
    host: 'SERVER',
    database: 'DBNAME',
    password: 'PASSWORD',
    port: 'PORT',
});
client.connect();

var query = "select * from pglog_test()";

client.on('notice', function (msg) {
    console.log("logs from postgress: ", msg.message);
});

client.query(query, (err, res) => {
    console.log(err, res);
});

[elhigu]

Knex doesn't have any special support for this.

Though maybe that client in your pg example is actually connection. You could try to bind those event handlers in https://knexjs.org/#Installation-pooling-afterCreate when knex actually creates new pg connection to its pool.

Closing as non-knex issue (and as usage question). If anyone knows better, please feel free to keep on discussion in this thread.

[msudhayaraj]

Knex doesn't have any special support for this.

Though maybe that client in your pg example is actually connection. You could try to bind those event handlers in https://knexjs.org/#Installation-pooling-afterCreate when knex actually creates new pg connection to its pool.

Closing as non-knex issue (and as usage question). If anyone knows better, please feel free to keep on discussion in this thread.

my pg sample is just for the reference code. Likewise i need to get pg database procedure logs using knex.it might be the knex issue.
is there any way to get the pg database procedure logs ?
if there pls guide me.

[elhigu]

Did you try setting that notice event handler in https://knexjs.org/#Installation-pooling-afterCreate ? It may do the same thing that your reference code is doing.

is there any way to get the pg database procedure logs ?

Knex doesn't have any special support for that. So if your reference code does it, then knex.raw and that notice event binding in knex should work.

[msudhayaraj]

Knex doesn't have any special support for this.

Though maybe that client in your pg example is actually connection. You could try to bind those event handlers in https://knexjs.org/#Installation-pooling-afterCreate when knex actually creates new pg connection to its pool.

Closing as non-knex issue (and as usage question). If anyone knows better, please feel free to keep on discussion in this thread.

Thank you @elhigu

I tried with pooling-afterCreate, i got the result.

The code is below

var pConn = {
    host: 'SERVER',
    port: 'PORT',
    user: 'USER',
    password: 'PASSWORD',
    database: 'DATABAENAME'
};
var query = "select * from pglog_test()";
try {

    const knex = require('knex')({
        client: 'pg',
        connection: pConn,
        pool: {
            afterCreate: function (conn, done) {
                conn.query('select 1 as result', function (err, result) {
                    conn.on('notice', function (msg) {
                        // here we can get the pg procedure logs (raise notice/raise info)
                        console.log('logs from postgress' + msg);
                    });
                    done(err, conn);
                });
            }
        }
    });
    // procedure calling
    knex.raw(query).then(function (result) {
        console.log(result);
    }).catch(function (error) {
        console.log(error);
    });
} catch (error) {
    console.log(error);
}