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 ) );
}