25

I have debugging messages in functions. Those message are raised like

RAISE NOTICE 'Value of id : %', id;

I set my log file with \o messages.txt

Then I do what I need to do with \i process.sql

And when the execution is terminated, \o.

The problem is that I don't have the messages raised by notices into messages.txt. The messages are displayed on the screen but I want them to be written in messages.txt

How could I do that ?

I tried to use RAISE LOG... and messages are written in log file... It's not what I want.

I have a work around with

plsql -f /path/to/process.sql > messages.txt 2>&1

but I would like to know how I could use \i and \o on plsql client having the messages into the file specified in \o

My client, on cygwin is psql (PostgreSQL) 8.2.11 and the server version is 9.0.7

7
  • 1
    While not the cause of your problem, using psql 8.2 against Pg 9.0 may cause you all kinds of grief because of all the system catalog schema changes and new server features. If you encounter weird or unexpected issues, try with psql 9.x. Commented Sep 4, 2012 at 22:09
  • @CraigRinger The problem is that the client psql for cygwin is not available. At least the last time I tried to upgrade the client on cygwin. I can't use the \d functionnality but everything else seem ok.
    – Luc M
    Commented Sep 4, 2012 at 23:51
  • @CraigRinger Didn't know that the client wasn't supported anymore. Thanks.
    – Luc M
    Commented Sep 5, 2012 at 0:10
  • I just double checked that; I thought cygwin was unsupported and I hadn't heard of anyone using it for ages, but it looks like people are still building PostgreSQL 9.2 beta on Cygwin in the buildfarm; see brolga. It should work. You'll probably just have to compile from source if you want the current Pg, it'll just be the binary Cygwin packages that're unmaintained. I'll delete that comment to avoid any future confusion. Commented Sep 5, 2012 at 0:18
  • The question remains though: Why use Cygwin for psql? libpq I could understand if you have non-portable software that has to run in Cygwin, but when psql is available natively for Windows what's the appeal of running an ancient version on Cygwin? Commented Sep 5, 2012 at 0:24

4 Answers 4

12

I'm afraid that you won't like this answer, but currently it seems impossible. From the psql documentation:

Saves future query results to the file filename or pipes future results into a separate Unix shell to execute command. If no arguments are specified, the query output will be reset to the standard output.

"Query results" includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d), but not error messages.

And as you noticed, there is no way redirecting error messages when using psql interactively.

(I've been playing around with all kinds of redirection from \o to no avail. It looks like that the query output channel is different from the one getting error messages - and even errors from the server and raised in your procedures go different ways.

test=# SHOW client_min_messages;
 client_min_messages
---------------------
 notice

-- added the above to show it's not a config problem

CREATE FUNCTION raise_test() RETURNS integer AS
$body$
BEGIN
    RAISE NOTICE 'notice';
    RETURN 1;
END;
$body$
LANGUAGE plpgsql;

test=# \o | cat > out.sql
test=# SELECT raise_test(); -- you could put this in a file and call \i your_file, it's just the same
NOTICE:  notice

test=# \o | cat > out.sql 2>&1
test=# SELECT raise_test();
NOTICE:  notice

out.sql contains

 raise_test
------------
          1
(1 row)

in both cases. This is why I am clueless about what channel/file descriptor is used for outputting the messages raised from a procedure.) )

(There is a thread on PostgreSQL-hackers that may shed some light on this issue: http://postgresql.1045698.n5.nabble.com/psql-output-locations-td5068313.html )

What one could possibly do is starting psql like

psql test >/tmp/psql.out 2>&1

and this will redirect all output to the specified file. The only problem with this that you don't even have a prompt, and lose the commandline editing capabilities.

7
  • 1
    No, I don't like your answer. :-)
    – Luc M
    Commented Sep 4, 2012 at 13:46
  • channel/file descriptor is used for outputting the messages raised from a procedure: it's the standard error. \o | cat > out.sql 2>&1 redirects the standard error of cat but not the one of psql, so it's of no use for the notices. Commented Jul 21, 2015 at 14:31
  • @dezso, How pgAdmin or any other GUI tools displays the NOTICE information?
    – RBB
    Commented Sep 4, 2019 at 15:51
  • @Spike they are not build on top of psql, so probably without any issues. Commented Sep 5, 2019 at 9:48
  • @dezso, yeah I get it. I got an issue like, I have set client_min_message to debug within my session and run some dynamic sqls with (DO - without creating any function), it raises notice in pgAdmin by not with psql. I printed the client_min_message value before the executing the DO statement. It shows debug, but the message is not printing in the console.
    – RBB
    Commented Sep 5, 2019 at 14:07
5

We can execute shell command directly from psql using \! meta command.

user@db=# \! psql -U user -h localhost your_db -e 'select your_function()' &> debug.txt

open debug.txt with \e.

localhost:5432 user@db=# \e debug.txt

raise message will shown in your default editor. a bit tricky, still quite handy for commandLine lover.

1

Here is a function to print to stdout and stderr, useful to track script runs on the cloud.

  CREATE OR REPLACE
Function debug_info(message text) Returns Text
  LANGUAGE plpgsql AS $_$
  declare
      -- Since server time will be in another TZ
      timeis text := to_char(now() at 
               time zone 'Asia/Kolkata', 
               'YYYY-MM-DD-HH24:MI');
      val    text := '';
  BEGIN
  RAISE   notice 'Date=% [%]', timeis, message;
  val :=  concat(timeis,' ',message);
  Return        val;
  END $_$;

-- Usage in pgsql script
...
select result from debug_info('doing hello world') as result;
..

-- Outputs on stdout
2018-11-11-20:25 doing hello world

-- Output on stderr
Date=2018-11-11-20:25 [doing hello world]
0

Not a solution to the original Question, but an addition to OP's workaround (which did not work for me)


With the following as raiseTest.sql

BEGIN;

CREATE FUNCTION count_to_hundred() RETURNS VOID AS
$_$
BEGIN
    FOR i IN 1..100 
    LOOP
        RAISE NOTICE '%', i;
    END LOOP;

    RETURN;
END;
$_$
LANGUAGE plpgsql;


SELECT count_to_hundred();

ROLLBACK;



I'm not sure, why writing the output to a file like noted in the OP does not work, but piping it into tee actually worked out:

psql -f raiseTest.sql 2>&1 | tee messages.txt


Tee writes the stdin into one or more files and back into the stdout. So you'll have all the RAISE statements in your console and in the file(s) you provided. (cf. tee's man page)


Configuration:

  • Postgres 8.4
  • Cygwin 2.0.2
  • tee 8.23
2
  • The question was about getting the messages raised by NOTICE while you are into an interactive session and use \o
    – Luc M
    Commented Sep 17, 2015 at 15:36
  • Oh, you're right there. I totally missed that part, since the workaround in the OP did not work for me and I concentrated on this only. Commented Sep 17, 2015 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.