unable to write 'raise' messages to log file?

["Ian Bell" <ib@xxxxxxxxxxxxxxxxxxx>]

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

}