Thread: unable to write 'raise' messages to log file?
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 ) );
}
On 05/31/2018 11:20 AM, Ian Bell wrote:
> 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.
>
>
> 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.
>
Do they show up in a client? For example psql:
test_(aklaver)> create or replace function TestWithBasicTypeArguments( i
int, n numeric, t text)
returns integer
as $$
begin
raise log 'Test.TestWithArguments: i = %, n = %, t = %', i, n, t;
return 0;
end;
CREATE FUNCTION
test_(aklaver)> set client_min_messages = 'debug1';
test_(aklaver)> select testwithbasictypearguments(1, 2.5, 'test');
LOG: Test.TestWithArguments: i = 1, n = 2.5, t = test
testwithbasictypearguments
----------------------------
0
(1 row)
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/31/2018 11:20 AM, Ian Bell wrote:[snip]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.
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.
Do they show up in a client? For example psql:
test_(aklaver)> select testwithbasictypearguments(1, 2.5, 'test');
LOG: Test.TestWithArguments: i = 1, n = 2.5, t = test
testwithbasictypearguments
----------------------------
0
(1 row)
--
Adrian Klaver
adrian.klaver@aklaver.com
Hello Adrian,
Yes, if I call the function in PSQL then the expected message is displayed
the console display. Here is what is displayed in PSQL:
testdb=# select * from test.testwithbasictypearguments( 1,
2::numeric, '3' );
LOG: statement: select * from test.testwithbasictypearguments( 1,
2::numeric, '3' );
LOG: Test.TestWithArguments: i = 1, n = 2, t = 3
testwithbasictypearguments
----------------------------
0
(1 row)
testdb=#
Ian
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: 31 May, 2018 15:07
To: ib@ianbellsoftware.com; pgsql-general@lists.postgresql.org
Subject: Re: unable to write 'raise' messages to log file?
On 05/31/2018 11:20 AM, Ian Bell wrote:
> 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.
>
>
> Ive tried flushing/rotating the log files by executing *select
> pg_rotate_logfile()* in PSQL but my messages never appear in the log
> files. Ive 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.
>
Do they show up in a client? For example psql:
test_(aklaver)> create or replace function TestWithBasicTypeArguments( i
int, n numeric, t text) returns integer as $$
begin
raise log 'Test.TestWithArguments: i = %, n = %, t = %', i, n, t;
return 0;
end;
CREATE FUNCTION
test_(aklaver)> set client_min_messages = 'debug1';
test_(aklaver)> select testwithbasictypearguments(1, 2.5, 'test');
LOG: Test.TestWithArguments: i = 1, n = 2.5, t = test
testwithbasictypearguments
----------------------------
0
(1 row)
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/31/2018 12:15 PM, Olivier Gautherot wrote: > On Thu, May 31, 2018 at 3:07 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 05/31/2018 11:20 AM, Ian Bell wrote: > > 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. > > > > 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 <http://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. > > > > Do they show up in a client? For example psql: > > [snip] > > test_(aklaver)> select testwithbasictypearguments(1, 2.5, 'test'); > LOG: Test.TestWithArguments: i = 1, n = 2.5, t = test > testwithbasictypearguments > ---------------------------- > 0 > (1 row) > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > I've used the logs successfully here. What are the values of > log_min_messages and log_min_error_statement in your postgresql.conf? The settings are shown in the original post. > > Olivier Gautherot -- Adrian Klaver adrian.klaver@aklaver.com
On 05/31/2018 12:15 PM, Olivier Gautherot wrote:On Thu, May 31, 2018 at 3:07 PM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
On 05/31/2018 11:20 AM, Ian Bell wrote:
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.
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 <http://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.
Do they show up in a client? For example psql:
[snip]
test_(aklaver)> select testwithbasictypearguments(1, 2.5, 'test');
LOG: Test.TestWithArguments: i = 1, n = 2.5, t = test
testwithbasictypearguments
----------------------------
0
(1 row)
-- Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
I've used the logs successfully here. What are the values of log_min_messages and log_min_error_statement in your postgresql.conf?
The settings are shown in the original post.
On 05/31/2018 12:18 PM, Ian Bell wrote:
> Hello Adrian,
>
> Yes, if I call the function in PSQL then the expected message is displayed
> the console display. Here is what is displayed in PSQL:
>
> testdb=# select * from test.testwithbasictypearguments( 1,
> 2::numeric, '3' );
> LOG: statement: select * from test.testwithbasictypearguments( 1,
> 2::numeric, '3' );
> LOG: Test.TestWithArguments: i = 1, n = 2, t = 3
> testwithbasictypearguments
> ----------------------------
> 0
> (1 row)
>
> testdb=#
>
> Ian
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: 31 May, 2018 15:07
> To: ib@ianbellsoftware.com; pgsql-general@lists.postgresql.org
> Subject: Re: unable to write 'raise' messages to log file?
>
> On 05/31/2018 11:20 AM, Ian Bell wrote:
>> 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.
>>
>
>>
>> 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.
>>
>
>
> Do they show up in a client? For example psql:
>
> test_(aklaver)> create or replace function TestWithBasicTypeArguments( i
> int, n numeric, t text) returns integer as $$
>
> begin
>
> raise log 'Test.TestWithArguments: i = %, n = %, t = %', i, n, t;
>
> return 0;
>
> end;
>
> CREATE FUNCTION
>
> test_(aklaver)> set client_min_messages = 'debug1';
>
> test_(aklaver)> select testwithbasictypearguments(1, 2.5, 'test');
> LOG: Test.TestWithArguments: i = 1, n = 2.5, t = test
> testwithbasictypearguments
> ----------------------------
> 0
> (1 row)
Hmm.
Some suggestions:/questions:
1) Enable log_connections, log_disconnections in postgresql.conf at
least temporarily. This will help you see the sessions you are working in
2) Then in the session that you do:
select * from test.testwithbasictypearguments( 1, 2::numeric, '3' );
also do:
select 1/0;
This will help also prove the logging is tracking your session or not.
3) Are you sure you do not have another postgresql.conf in the mix that
is overriding your settings?
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hello Olivier,
Yes, I have used ‘notice’, ‘log’ and the various ‘debug’ levels but again none of my message show up in the log files.
Ian
From: Olivier Gautherot [mailto:olivier@gautherot.net]
Sent: 31 May, 2018 15:26
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: ib@ianbellsoftware.com; pgsql-general@lists.postgresql.org
Subject: Re: unable to write 'raise' messages to log file?
Hi Ian,
On Thu, May 31, 2018 at 3:19 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 05/31/2018 12:15 PM, Olivier Gautherot wrote:
On Thu, May 31, 2018 at 3:07 PM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
On 05/31/2018 11:20 AM, Ian Bell wrote:
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.
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 <http://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.
Do they show up in a client? For example psql:
[snip]
test_(aklaver)> select testwithbasictypearguments(1, 2.5, 'test');
LOG: Test.TestWithArguments: i = 1, n = 2.5, t = test
testwithbasictypearguments
----------------------------
0
(1 row)
-- Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
I've used the logs successfully here. What are the values of log_min_messages and log_min_error_statement in your postgresql.conf?
The settings are shown in the original post.
By any chance... did you try "RAISE NOTICE..." in your function? That's the level I usually use.
Olivier Gautherot