Home > mailing lists

Thread: Redirect RAISE NOTICE and errors to separate log file

Redirect RAISE NOTICE and errors to separate log file

From

Nikhil Ingale

Date:

17 February 2023, 18:59:14

Hi All,

I have below given procedure where I have to redirect the messages and errors to separate log files.

CREATE OR REPLACE PROCEDURE()

language plpgsql

AS $$

Declare

insrtStr character varying(5000);

BEGIN

insrtStr := 'INSERT into test(a,b,c) SELECT * FROM demo';

RAISE NOTICE 'insrtStr';

EXECUTE insrtStr;

EXCEPTION WHEN OTHERS THEN

v_sqlerrm := sqlerrm;

   v_sqlstate := sqlstate;

    RAISE NOTICE 'exception: % %  ', v_sqlstate ,  v_sqlerrm ;

END;

$$;

psql -U postgres -d proddb -f test.sql --log-file=test.log

Reason for printing the insrtStr is to keep track of table inserts.

Above is just an example procedure. Procedure that I have written merges the data from server tables.

RAISE NOTICE logs and exceptions are printed on the console. But I want to separate out the logs.

Please provide your valuable feedback.

Thanks & Regards,

Nik

Re: Redirect RAISE NOTICE and errors to separate log file

From

"David G. Johnston"

Date:

17 February 2023, 19:27:41

On Fri, Feb 17, 2023 at 8:59 AM Nikhil Ingale <niks.bgm@gmail.com> wrote:

Hi All,
I have below given procedure where I have to redirect the messages and errors to separate log files.

CREATE OR REPLACE PROCEDURE()
language plpgsql
AS $$
Declare
insrtStr character varying(5000);
BEGIN
insrtStr := 'INSERT into test(a,b,c) SELECT * FROM demo';
RAISE NOTICE 'insrtStr';
EXECUTE insrtStr;
EXCEPTION WHEN OTHERS THEN
v_sqlerrm := sqlerrm;
   v_sqlstate := sqlstate;
    RAISE NOTICE 'exception: % %  ', v_sqlstate ,  v_sqlerrm ;
END;
$$;

psql -U postgres -d proddb -f test.sql --log-file=test.log

Reason for printing the insrtStr is to keep track of table inserts.

Above is just an example procedure. Procedure that I have written merges the data from server tables.
RAISE NOTICE logs and exceptions are printed on the console. But I want to separate out the logs.

Please provide your valuable feedback.

Leverage your shell. All non-query output is sent to stderr.

vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -c 'select * from whisper();' postgres

NOTICE: Hi!
whisper
---------
Hello
(1 row)

vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -c 'select * from whisper();' postgres 2>/dev/null
whisper
---------
Hello
(1 row)

David J.

Re: Redirect RAISE NOTICE and errors to separate log file

From

Nikhil Ingale

Date:

17 February 2023, 19:51:34

I need the non query output in the user defined log file.

How can I achieve that?

On Fri, Feb 17, 2023 at 9:57 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Feb 17, 2023 at 8:59 AM Nikhil Ingale <niks.bgm@gmail.com> wrote:
Hi All,
I have below given procedure where I have to redirect the messages and errors to separate log files.

CREATE OR REPLACE PROCEDURE()
language plpgsql
AS $$
Declare
insrtStr character varying(5000);
BEGIN
insrtStr := 'INSERT into test(a,b,c) SELECT * FROM demo';
RAISE NOTICE 'insrtStr';
EXECUTE insrtStr;
EXCEPTION WHEN OTHERS THEN
v_sqlerrm := sqlerrm;
   v_sqlstate := sqlstate;
    RAISE NOTICE 'exception: % %  ', v_sqlstate ,  v_sqlerrm ;
END;
$$;

psql -U postgres -d proddb -f test.sql --log-file=test.log

Reason for printing the insrtStr is to keep track of table inserts.

Above is just an example procedure. Procedure that I have written merges the data from server tables.
RAISE NOTICE logs and exceptions are printed on the console. But I want to separate out the logs.

Please provide your valuable feedback.
Leverage your shell. All non-query output is sent to stderr.

vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -c 'select * from whisper();' postgres
NOTICE: Hi!
whisper
---------
Hello
(1 row)

vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -c 'select * from whisper();' postgres 2>/dev/null
whisper
---------
Hello
(1 row)
David J.

Re: Redirect RAISE NOTICE and errors to separate log file

From

"David G. Johnston"

Date:

17 February 2023, 19:58:45

On Fri, Feb 17, 2023 at 9:52 AM Nikhil Ingale <niks.bgm@gmail.com> wrote:

I need the non query output in the user defined log file.
How can I achieve that?

Learn shell scripting.

At its simplest, have the user replace "/dev/null" in my example with whatever path they want.

psql doesn't expose specifying a non-query output channel so you are forced to deal with this in a higher/wrapper layer where you can manipulate stderr.

David J.

Re: Redirect RAISE NOTICE and errors to separate log file

From

Nikhil Ingale

Date:

17 February 2023, 20:20:13

I'm able to achieve it now. Thank you for the hint David.

On Fri, Feb 17, 2023 at 10:29 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Feb 17, 2023 at 9:52 AM Nikhil Ingale <niks.bgm@gmail.com> wrote:
I need the non query output in the user defined log file.
How can I achieve that?

Learn shell scripting.

At its simplest, have the user replace "/dev/null" in my example with whatever path they want.

psql doesn't expose specifying a non-query output channel so you are forced to deal with this in a higher/wrapper layer where you can manipulate stderr.

David J.