now getting log messages!
Hello Adrian,
Your suggestions have changed something and my messages are appearing in the log file. As per your suggestion, I enabled ' log_connections' and 'log_disconnections' and restarted the server.
I now have something to work with. In addition, I now know what to expect when attempting to log messages (I'm a SQL newbie).
Thank you for your help.
Ian
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: 31 May, 2018 15:27
To: ib@ianbellsoftware.com; pgsql-general@lists.postgresql.org
Subject: Re: unable to write 'raise' messages to log file?
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
On 05/31/2018 01:03 PM, Ian Bell wrote:
Hello Adrian,
Your suggestions have changed something and my messages are appearing in the log file. As per your suggestion, I enabled ' log_connections' and 'log_disconnections' and restarted the server.
Hmm, again:)
Changing log_connections, log_disconnections would not have enabled
logging the 'log' messages. The server restart would be the likely
trigger for catching the logging settings for client_min_messages and
log_min_messages. What solicited the hmm was that in your first post you
said you had restarted the server multiple times when changing the
logging settings.
Do you have more then one instance of Postgres running on this machine?
If so maybe you where restarting the wrong server previously?
I now have something to work with. In addition, I now know what to expect when attempting to log messages (I'm a SQL newbie).
On that score the 'log' message level is one you need to be careful of
as it ranks differently in the client versus in the log file:
https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
"client_min_messages (enum)
Controls which message levels are sent to the client. Valid values
are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR,
FATAL, and PANIC. Each level includes all the levels that follow it. The
later the level, the fewer messages are sent. The default is NOTICE.
Note that LOG has a different rank here than in log_min_messages.
log_min_messages (enum)
Controls which message levels are written to the server log. Valid
values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE,
WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the
levels that follow it. The later the level, the fewer messages are sent
to the log. The default is WARNING. Note that LOG has a different rank
here than in client_min_messages. Only superusers can change this setting.
"
Thank you for your help.
Ian
--
Adrian Klaver
adrian.klaver@aklaver.com
Hello Adrian,
I agree with your 'Hmm' skepticism and will be looking at this later tonight. On the bright side, I finally have the logging working properly and this should make it relative easy for me to backtrack and find/understand the mistake I was making.
I only have one instance of the PostgreSql server running. I've intentionally kept my PostgreSql configuration as simple as possible so I can focus on ADO.NET.
Thanks for the pointer about the 'log' setting. I will take a closer look at this.
Cheers,
Ian
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: 31 May, 2018 16:24
To: ib@ianbellsoftware.com; pgsql-general@lists.postgresql.org
Subject: Re: now getting log messages!
On 05/31/2018 01:03 PM, Ian Bell wrote:
Hello Adrian,
Your suggestions have changed something and my messages are appearing in the log file. As per your suggestion, I enabled ' log_connections' and 'log_disconnections' and restarted the server.
Hmm, again:)
Changing log_connections, log_disconnections would not have enabled logging the 'log' messages. The server restart would be the likely trigger for catching the logging settings for client_min_messages and log_min_messages. What solicited the hmm was that in your first post you said you had restarted the server multiple times when changing the logging settings.
Do you have more then one instance of Postgres running on this machine?
If so maybe you where restarting the wrong server previously?
I now have something to work with. In addition, I now know what to expect when attempting to log messages (I'm a SQL newbie).
On that score the 'log' message level is one you need to be careful of as it ranks differently in the client versus in the log file:
https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
"client_min_messages (enum)
Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The default is NOTICE.
Note that LOG has a different rank here than in log_min_messages.
log_min_messages (enum)
Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log. The default is WARNING. Note that LOG has a different rank here than in client_min_messages. Only superusers can change this setting.
"
Thank you for your help.
Ian
--
Adrian Klaver
adrian.klaver@aklaver.com