Postgresql – how to prevent log output from PostgreSQL stored procedure

configurationloggingpostgresqlstored-procedures

I am running a number of PostgreSQL scripts that used to produce excessive log output. I managed to reduce most of the output to an acceptable amount by passing --quiet as parameter to the psql command line client and adding SET client_min_messages='warning'; to the beginning of my SQL scripts.

This works fine for most basic statements like SELECT, INSERT, UPDATE, etc.) However, when I call a stored function in a script using e.g. SELECT my_func(my_args);, there is still log output similar to

my_func

(omitted a long with many '-' here because SF thinks that's a headline)

(1 row)

The output is useless; it only makes me having to scroll back up a long way after the script has run and also makes it much harder than necessary to spot any relevant error output.

How can I get rid of it ?

Best Answer

Since you seem to be running the script from psql, you could do

copy ( select my_func(my_args) ) to stdout;

or even

\copy ( select my_func(my_args) ) to /dev/null

You could specify "-A -t" on the psql command line to globally quieten the output of all query statements to a single line.

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

OK, got it from someone else. This query should do the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

Postgresql – weird postgresql log entries

incomplete startup packet means a connection was established but the Postgres server didn't get the handshake it was expecting (I get a ton of these as my monitoring system checks to make sure port 5432 is open but isn't smart enough to actually log in to & query the DB).
Whether or not this is a problem depends on if you expect to have something connecting and not handshaking: For me seeing it once every 5 minutes isn't an issue, but if you're not expecting it this message could be an indication that you're being port scanned.

WARNING: there is already a transaction in progress means exactly what it says: Someone tried to start a transaction while already in a transaction (BEGIN ... BEGIN).
If you see this a lot somebody wrote some bad SQL in a program and you should find/fix it because the corresponding COMMIT and ROLLBACK statements may not do what you expect. If you see it once and never again someone probably fat-fingered a transaction in psql.

Best Answer

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

Postgresql – weird postgresql log entries

Related Topic