unable to write 'raise' messages to log file?

From: "Ian Bell" <ib(at)ianbellsoftware(dot)com>
To: <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: unable to write 'raise' messages to log file?
Date: 2018-05-31 18:20:21
Message-ID: !&!AAAAAAAAAAAYAAAAAAAAADldbAmb6+pIq6nH7MxZl07CgAAAEAAAAKg/AOTHwA5Fj0N12IA50xYBAAAAAA==@ianbellsoftware.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am having considerable difficulty logging information in PL-pgSQL
functions by using the 'RAISE' statement. I am asking for
comments/suggestions on what I am doing wrong.

Each time I restart the server then server startup messages appear in the
log file. In addition, changing the message level settings in
Postgresql.conf (e.g. changing 'client_min_messages' from 'notice' to
'debug1') adds more details to the log file. So this indicates PostgreSql
is correctly configured for logging.

I've tried flushing/rotating the log files by executing 'select
pg_rotate_logfile()' in PSQL but my messages never appear in the log files.
I've tried calling my PL-pgSQL functions in PSQL, PgAdmin4, OmniDB and
ADO.NET but again my messages never appear in the log file.

On very rare occasions, I see my messages the log file if I restart the
PostgreSql server however restarting the server generally does not flush my
messages to the log files.

System Information:

Win10, 64 bit

PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit

.NET Framework V4.5.1

Npgsql V4 RC

PostgreSql log file directory

D:\PostgreSQL\10\data\log

Postgresql.conf setting

log_destination = 'stderr'

logging_collector = on

log_directory = 'log'

client_min_messages = debug1

log_min_messages = debug1

log_min_error_statement = debug1

log_statement = 'all'

Sample PL-pgSQL function

create or replace function Test.TestWithBasicTypeArguments( i int, n
numeric, t text)
returns integer
as $$
begin
raise log 'Test.TestWithArguments: i = %, n = %, t = %', i, n, t;
return 0;
end;
$$ language plpgsql;

ADO.NET function call using Npgsql library

internal int TestWithBasicTypeArguments( int i, double d, string s )
{
var cmd = new NpgsqlCommand(
"Test.TestWithBasicTypeArguments".ToLower(), _connection );
cmd.CommandType = System.Data.CommandType.StoredProcedure;

var par = new NpgsqlParameter();
par.ParameterName = "i".ToLower();
par.NpgsqlDbType = NpgsqlDbType.Integer;
par.Value = i;
cmd.Parameters.Add( par );

par = new NpgsqlParameter();
par.ParameterName = "n".ToLower();
par.NpgsqlDbType = NpgsqlDbType.Numeric;
par.Value = d;
cmd.Parameters.Add( par );

par = new NpgsqlParameter();
par.ParameterName = "t".ToLower();
par.NpgsqlDbType = NpgsqlDbType.Text;
par.Value = s;
cmd.Parameters.Add( par );

return Convert.ToInt32( cmd.ExecuteScalar() );
}

NUnit test method

[Test]
public void TestWithBasicTypeArguments()
{
int status = _myClass.TestWithBasicTypeArguments( 1, 2.2, "3" );
Assert.That( 0, Is.EqualTo( status ) );
}

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-05-31 19:07:18 Re: unable to write 'raise' messages to log file?
Previous Message George Neuner 2018-05-31 17:57:59 Re: Pgagent is not reading pgpass file either in Windows or Linux.