serverlog function

Andreas Pflug <pgadmin@pse-consulting.de> writes:

For adminstrator's convenience, I'd like to see a function that returns
the serverlog.

What do you mean by "returns the serverlog"? Are you going to magically
recover data that has gone to stderr or the syslogd daemon? If so, how?
And why wouldn't you just go and look at the log file, instead?

regards, tom lane

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

For adminstrator's convenience, I'd like to see a function that returns
the serverlog.

What do you mean by "returns the serverlog"? Are you going to magically
recover data that has gone to stderr or the syslogd daemon? If so, how?
And why wouldn't you just go and look at the log file, instead?

I'd like to see the serverlog even if I can't "go and look at the log
file", because I don't have file access to the server.

Regards,
Andreas

Andreas Pflug wrote:

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

For adminstrator's convenience, I'd like to see a function that
returns the serverlog.

What do you mean by "returns the serverlog"? Are you going to magically
recover data that has gone to stderr or the syslogd daemon?

Hm, what I missed is that pg_ctl's -l parameter converts to a simple
stderr redirection, and it's hardly possible to find out where it's going.
This could be solved by a file log_destination option or a
freopen(...,stderr) from a guc variable.

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Hm, what I missed is that pg_ctl's -l parameter converts to a simple
stderr redirection, and it's hardly possible to find out where it's going.
This could be solved by a file log_destination option or a
freopen(...,stderr) from a guc variable.

Any such patch would be rejected, because it would break the ability
to pipe stderr into another program (such as logrotate). And what of
the syslog case?

regards, tom lane

Andreas Pflug wrote:

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

For adminstrator's convenience, I'd like to see a function that returns
the serverlog.

What do you mean by "returns the serverlog"? Are you going to magically
recover data that has gone to stderr or the syslogd daemon? If so, how?
And why wouldn't you just go and look at the log file, instead?

I'd like to see the serverlog even if I can't "go and look at the log
file", because I don't have file access to the server.

Understand. Unfortunately, we don't allow such functionality. The only
solution I can think of is to use syslog and send the logs to a machine
where you do have access.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 07 June 2004 14:30
To: Andreas Pflug
Cc: PostgreSQL Development
Subject: Re: [HACKERS] serverlog function (log_destination file)

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Hm, what I missed is that pg_ctl's -l parameter converts to

a simple

stderr redirection, and it's hardly possible to find out

where it's going.

This could be solved by a file log_destination option or a
freopen(...,stderr) from a guc variable.

Any such patch would be rejected, because it would break the
ability to pipe stderr into another program (such as
logrotate). And what of the syslog case?

I see the problems with the existing mechanisms, but just to float an
idea - what about adding a GUC variable that can be used to specify an
amount of shared memory to use as a fifo area in which a copy of the log
output is stored for return to clients that might want it (accessing it
via internal functions)?

Regards, Dave

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Hm, what I missed is that pg_ctl's -l parameter converts to a simple
stderr redirection, and it's hardly possible to find out where it's going.
This could be solved by a file log_destination option or a
freopen(...,stderr) from a guc variable.

Any such patch would be rejected, because it would break the ability
to pipe stderr into another program (such as logrotate). And what of
the syslog case?

Might it be sensible to have pg_ctl write its log destination (if any)
out to a file in the data dir? That plus the log_destination setting
might provide enough info at least for some common cases.

cheers

andrew

Bruce Momjian wrote:

Andreas Pflug wrote:

I'd like to see the serverlog even if I can't "go and look at the log
file", because I don't have file access to the server.

Understand. Unfortunately, we don't allow such functionality. The only
solution I can think of is to use syslog and send the logs to a machine
where you do have access.

What I mean is not how to set up a log solution, but how to offer a
convenient way for the windows spoiled admins who like to have a gui for
all kind of stuff, and just wants to hit a button "show server log",
regardless of server location and platform type. This is a request that
was recommended for pgadmin3.

AFAICS, we have some alternatives:
- try to grab the currently created files/syslog/eventlog. Seems hard to
do, because we'd depend on additional external tools.
- redirect stderr to a postgresql.conf known file. Disadvantage: breaks
piping.
- maintain a sharedMem for the latest messages. Disadvantage: limited
space, no access to older entries after postmaster restart.
- additional log_destination "file". Disadvantage: Yet Another File
besides the redirected stderr, but this seems a minor problem.

Regards,
Andreas

"Dave Page" <dpage@vale-housing.co.uk> writes:

... what about adding a GUC variable that can be used to specify an
amount of shared memory to use as a fifo area in which a copy of the log
output is stored for return to clients that might want it (accessing it
via internal functions)?

No, that's a nonstarter, because having the postmaster log into shared
memory means that the postmaster probably goes down too anytime a
backend crashes. The shared area would have to have a mutual-exclusion
lock, and we definitely do not want the postmaster participating in any
lock protocols.

If I were trying to solve Andreas' problem, I'd pipe stderr to some
program that stores recent log output in a file that I know the location
of and can read from the hypothetical log-grabber function. Actually I
don't see that there's any need to involve Postgres itself in this issue
at all --- seems like the only agreement needed is between the GUI and
the postmaster launching script about where the log file is.

regards, tom lane

On Mon, Jun 07, 2004 at 16:06:53 +0200,
Andreas Pflug <pgadmin@pse-consulting.de> wrote:

AFAICS, we have some alternatives:

You could also pipe the logs to a program that writes them to a table
in the database. As long as the logging level wasn't set so high that
inserting the log entries was logged. This should be a fairly simple
program to write and could be provided to people that wanted to provide
this feature for their server.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 07 June 2004 15:32
To: Dave Page
Cc: Andreas Pflug; PostgreSQL Development
Subject: Re: [HACKERS] serverlog function (log_destination file)

If I were trying to solve Andreas' problem, I'd pipe stderr
to some program that stores recent log output in a file that
I know the location of and can read from the hypothetical
log-grabber function. Actually I don't see that there's any
need to involve Postgres itself in this issue at all ---
seems like the only agreement needed is between the GUI and
the postmaster launching script about where the log file is.

Thanks Tom. I wonder if we (the pgAdmin team) finally need to bite the
proverbial bullet and write a helper daemon that can allow access to
logs as well as config files and pg_ctl etc. as an optional extra
component.

Regards, Dave.

Tom Lane wrote:

If I were trying to solve Andreas' problem, I'd pipe stderr to some
program that stores recent log output in a file that I know the location
of and can read from the hypothetical log-grabber function. Actually I
don't see that there's any need to involve Postgres itself in this issue
at all --- seems like the only agreement needed is between the GUI and
the postmaster launching script about where the log file is.

What if there's no file access (e.g. only db admin, not sys admin, no
file sharing, firewalled with only 5432 port access or similar)? I'd
like a solution that needs just enabling in postgresql.conf and a button
in pgadmin3 (probably phppgadmin would follow soon), on any platform.

Regards,
Andreas

"Dave Page" <dpage@vale-housing.co.uk> writes:

Thanks Tom. I wonder if we (the pgAdmin team) finally need to bite the
proverbial bullet and write a helper daemon that can allow access to
logs as well as config files and pg_ctl etc. as an optional extra
component.

Red Hat's RHDB group already did a fair amount of work on such a tool:
see Control Center available from http://sources.redhat.com/rhdb/

IIRC it's all in Java and thus at least theoretically portable to
Windows.

At the moment Red Hat is devoting no resources to it (and so I think
it's stuck in the 7.3 time frame) but I'd be very happy to see someone
else pick it up and work on it.

regards, tom lane

Andreas Pflug <pgadmin@pse-consulting.de> writes:

What if there's no file access

If you don't have any access to the machine then you are not really a
DBA, you only play one on TV. You can't for example start and stop the
postmaster remotely. So I don't have a lot of sympathy for the notion
that the logs have to be externally accessible, and none whatever for
the notion that this has to be possible in every configuration.

regards, tom lane

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

What if there's no file access

If you don't have any access to the machine then you are not really a
DBA, you only play one on TV.

However you may call me, I can think of many cases where I'd like to
look at the server log, without wanting to change the configuration or
start/stop the postmaster. The error message sent to the application
might be lost, or an interference with another app might have caused
troubles, so a collected log is fine for such cases, preferrably
displayed by the same tool I'm tuning my queries with.

What you're telling me is that people should have a telnet session to
the linux box and tail /usr/data/pgsql/serverlog, while they're working
with comfortable gui tools otherwise. Sound dissatisfying.

The rhdb control center seems fine for a sysadmin-dbadmin who's going to
install and reconfigure a machine, but I certainly don't want this for
SQL centric work. And a Java GUI is a bad idea on win32 either...

Regards,
Andreas

Andreas wrote:

AFAICS, we have some alternatives:
- try to grab the currently created files/syslog/eventlog.
Seems hard to
do, because we'd depend on additional external tools.
- redirect stderr to a postgresql.conf known file.
Disadvantage: breaks
piping.
- maintain a sharedMem for the latest messages. Disadvantage: limited
space, no access to older entries after postmaster restart.
- additional log_destination "file". Disadvantage: Yet Another File
besides the redirected stderr, but this seems a minor problem.

Another alternative would be to add code to the admin tool to get the log
via scp or a similar method. IMHO PostgreSQL is doing the right thing here
by using the OS logging, and breaking that isn't a good idea when there are
other ways to solve the problem.

If I were trying to solve Andreas' problem, I'd pipe stderr
to some program that stores recent log output in a file that
I know the location of and can read from the hypothetical
log-grabber function. Actually I don't see that there's any
need to involve Postgres itself in this issue at all ---
seems like the only agreement needed is between the GUI and
the postmaster launching script about where the log file is.

Thanks Tom. I wonder if we (the pgAdmin team) finally need to bite the
proverbial bullet and write a helper daemon that can allow access to
logs as well as config files and pg_ctl etc. as an optional extra
component.

It would certainly make things a lot easier for the box admin if you did
this as extension functions in pg instead of a separate daemon (even if
these are not installed in pg by default, but have to be installed
manually. Better if they are included by default, of course, from the
easy-to-admin perspective). Then it's just one set of firewall rules,
one process to check if it's running, etc.

Specifically about the logs, I still think there is a lot of value to
being able to read the logs remotely even if you can't restart
postmaster. Looking at the server logs can help you debug the database
client (for example when the app won't give you the full error msg, but
it goes in the server log). It won't help you if it's a postmaster
problem (since you won't get to it), but there are a lot of other
situations where it will.
(This is, btw, how MSSQL does it at least - you can view the server log
remotely if the server starts. If not, you'll have to go to the log
viewer on the server)

//Magnus

Magnus Hagander wrote:

Specifically about the logs, I still think there is a lot of value to
being able to read the logs remotely even if you can't restart
postmaster.

Since I believe that retrieving the logs easily without server file
access is a feature that's welcomed by many users, here's my proposal.

The attached diff will
- add a guc-variable log_filename
- extend log_destination to accept the keyword 'file'
- elog to that file if configured
- provide two functions pg_logfile_length() and pg_logfile to obtain the
contents.

int4 pg_logfile_length()
cstring pg_logfile(int4 size, int4 position)
size (may be null meaning max) is the chunk size (max: currently 50000)
position (may be null meaning -size) is the start position; positive
counting from log file start, negative from end.

Regards,
Andreas

Looks good to me. The only issue I saw was that the default file name
mentioned in postgresql.conf doesn't match the actual default.

Is this ready to be added to the patch queue?

---------------------------------------------------------------------------

Andreas Pflug wrote:

Magnus Hagander wrote:

Specifically about the logs, I still think there is a lot of value to
being able to read the logs remotely even if you can't restart
postmaster.

Since I believe that retrieving the logs easily without server file
access is a feature that's welcomed by many users, here's my proposal.

The attached diff will
- add a guc-variable log_filename
- extend log_destination to accept the keyword 'file'
- elog to that file if configured
- provide two functions pg_logfile_length() and pg_logfile to obtain the
contents.

int4 pg_logfile_length()
cstring pg_logfile(int4 size, int4 position)
size (may be null meaning max) is the chunk size (max: currently 50000)
position (may be null meaning -size) is the start position; positive
counting from log file start, negative from end.

Regards,
Andreas

Index: backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.402
diff -u -r1.402 postmaster.c
--- backend/postmaster/postmaster.c	3 Jun 2004 02:08:03 -0000	1.402
+++ backend/postmaster/postmaster.c	8 Jun 2004 18:07:30 -0000
@@ -532,6 +532,9 @@
/* If timezone is not set, determine what the OS uses */
pg_timezone_initialize();

+    /* open alternate logfile, if any */
+	LogFileOpen();
+
#ifdef EXEC_BACKEND
write_nondefault_variables(PGC_POSTMASTER);
#endif
Index: backend/storage/ipc/ipc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/storage/ipc/ipc.c,v
retrieving revision 1.87
diff -u -r1.87 ipc.c
--- backend/storage/ipc/ipc.c	12 Dec 2003 18:45:09 -0000	1.87
+++ backend/storage/ipc/ipc.c	8 Jun 2004 18:07:31 -0000
@@ -111,6 +111,8 @@
on_proc_exit_list[on_proc_exit_index].arg);

elog(DEBUG3, "exit(%d)", code);
+
+ LogFileClose();
exit(code);
}

Index: backend/utils/adt/misc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/misc.c,v
retrieving revision 1.34
diff -u -r1.34 misc.c
--- backend/utils/adt/misc.c	2 Jun 2004 21:29:29 -0000	1.34
+++ backend/utils/adt/misc.c	8 Jun 2004 18:07:36 -0000
@@ -103,3 +103,64 @@
{
PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0),SIGINT));
}
+
+Datum pg_logfile_length(PG_FUNCTION_ARGS)
+{
+	extern FILE *logfile; // in elog.c
+
+	if (logfile)
+		PG_RETURN_INT32(ftell(logfile));
+	PG_RETURN_INT32(0);
+}
+
+
+#define MAXLOGFILECHUNK 50000
+Datum pg_logfile(PG_FUNCTION_ARGS)
+{
+	size_t size=MAXLOGFILECHUNK;
+	char *buf=0;
+	size_t nbytes;
+
+	char *filename = LogFileName();
+	if (filename)
+	{
+		if (!PG_ARGISNULL(0))
+			size = PG_GETARG_INT32(0);
+		if (size > MAXLOGFILECHUNK)
+		{
+			size = MAXLOGFILECHUNK;
+			ereport(WARNING,
+					(errcode(ERRCODE_OUT_OF_MEMORY),
+					 errmsg("Maximum size is %d.", MAXLOGFILECHUNK)));
+		}
+
+		FILE *f=fopen(filename, "r");
+		if (f)
+		{
+			if (PG_ARGISNULL(1))
+				fseek(f, -size, SEEK_END);
+			else
+			{
+				long pos = PG_GETARG_INT32(1);
+				if (pos >= 0)
+					fseek(f, pos, SEEK_SET);
+				else
+					fseek(f, pos, SEEK_END);
+			}
+			buf = palloc(size+1);
+			nbytes = fread(buf, 1, size, f);
+			buf[nbytes] = 0;
+
+			fclose(f);
+		}
+		else
+		{
+			ereport(WARNING,
+					(errcode(ERRCODE_NO_DATA),
+					 errmsg("Could not open log file %s.", filename)));
+		}
+		free(filename);
+	}
+
+	PG_RETURN_CSTRING(buf);
+}
Index: backend/utils/error/elog.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/error/elog.c,v
retrieving revision 1.140
diff -u -r1.140 elog.c
--- backend/utils/error/elog.c	3 Jun 2004 02:08:04 -0000	1.140
+++ backend/utils/error/elog.c	8 Jun 2004 18:07:39 -0000
@@ -71,8 +71,10 @@
PGErrorVerbosity Log_error_verbosity = PGERROR_VERBOSE;
char       *Log_line_prefix = NULL; /* format for extra log line info */
unsigned int Log_destination = LOG_DESTINATION_STDERR;
+char *Log_filename = NULL;

bool in_fatal_exit = false;
+FILE *logfile = NULL;

#ifdef HAVE_SYSLOG
char *Syslog_facility; /* openlog() parameters */
@@ -936,6 +938,69 @@

/*
+ * Name of configured log file, or NULL
+ * must be freed after usage
+ */
+char*
+LogFileName(void)
+{
+	if (Log_filename && (Log_destination & LOG_DESTINATION_FILE))
+	{
+		if (is_absolute_path(Log_filename))
+			return strdup(Log_filename);
+		else
+		{
+			char *buf = malloc(strlen(DataDir) + strlen(Log_filename) +2);
+			if (!buf)
+				ereport(FATAL,
+						(errcode(ERRCODE_OUT_OF_MEMORY),
+						 errmsg("out of memory")));
+
+			sprintf(buf, "%s/%s", DataDir, Log_filename);
+
+			return buf;
+		}
+	}
+	return NULL;
+}
+
+
+/*
+ * Open log file, if configured.
+ */
+void
+LogFileOpen(void)
+{
+	char *filename = LogFileName();
+
+	if (filename)
+	{
+		LogFileClose();
+
+		logfile = fopen(filename, "at");
+
+		if (!logfile)
+			ereport(WARNING,
+					(errcode(ERRCODE_CONFIG_FILE_ERROR),
+					 errmsg("could not open log file %s", filename)));
+
+		free(filename);
+	}
+}
+
+
+/*
+ * Close log file, if open.
+ */
+void
+LogFileClose(void)
+{
+	if (logfile)
+		fclose(logfile);
+	logfile = NULL;
+}
+
+/*
* Initialization of error output file
*/
void
@@ -1445,6 +1510,11 @@
if ((Log_destination & LOG_DESTINATION_STDERR) || whereToSendOutput == Debug)
{
fprintf(stderr, "%s", buf.data);
+	}
+
+	if (logfile && (Log_destination & LOG_DESTINATION_FILE))
+	{
+		fprintf(logfile, "%s", buf.data);
}

pfree(buf.data);
Index: backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.210
diff -u -r1.210 guc.c
--- backend/utils/misc/guc.c	30 May 2004 23:40:38 -0000	1.210
+++ backend/utils/misc/guc.c	8 Jun 2004 18:07:47 -0000
@@ -76,6 +76,8 @@
static const char *assign_log_destination(const char *value,
bool doit, GucSource source);

+extern char *Log_filename;
+
#ifdef HAVE_SYSLOG
extern char *Syslog_facility;
extern char *Syslog_ident;
@@ -1644,13 +1646,23 @@
{
{"log_destination", PGC_POSTMASTER, LOGGING_WHERE,
gettext_noop("Sets the target for log output."),
-		 gettext_noop("Valid values are combinations of stderr, syslog "
+		 gettext_noop("Valid values are combinations of stderr, file, syslog "
"and eventlog, depending on platform."),
GUC_LIST_INPUT | GUC_REPORT
},
&log_destination_string,
"stderr", assign_log_destination, NULL
},
+	{
+		{"log_filename", PGC_POSTMASTER, LOGGING_WHERE,
+		 gettext_noop("Sets the target filename for log output."),
+		 gettext_noop("May be specified as relative to the cluster directory "
+					  "or as absolute path."),
+		 GUC_LIST_INPUT | GUC_REPORT
+		},
+		&Log_filename,
+		"postgresql.log", NULL, NULL
+	},

#ifdef HAVE_SYSLOG
{
@@ -4775,6 +4787,8 @@

if (pg_strcasecmp(tok,"stderr") == 0)
newlogdest |= LOG_DESTINATION_STDERR;
+		else if (pg_strcasecmp(tok,"file") == 0)
+			newlogdest |= LOG_DESTINATION_FILE;
#ifdef HAVE_SYSLOG
else if (pg_strcasecmp(tok,"syslog") == 0)
newlogdest |= LOG_DESTINATION_SYSLOG;
Index: backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.113
diff -u -r1.113 postgresql.conf.sample
--- backend/utils/misc/postgresql.conf.sample	7 Apr 2004 05:05:50 -0000	1.113
+++ backend/utils/misc/postgresql.conf.sample	8 Jun 2004 18:07:48 -0000
@@ -147,9 +147,10 @@

# - Where to Log -

-#log_destination = 'stderr'	# Valid values are combinations of stderr,
+#log_destination = 'stderr'	# Valid values are combinations of stderr, file,
# syslog and eventlog, depending on
# platform.
+#log_filename = 'pgsql.log'
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

Index: include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.334
diff -u -r1.334 pg_proc.h
--- include/catalog/pg_proc.h	2 Jun 2004 21:29:29 -0000	1.334
+++ include/catalog/pg_proc.h	8 Jun 2004 18:08:03 -0000
@@ -3588,6 +3588,12 @@
DATA(insert OID = 2243 ( bit_or						   PGNSP PGUID 12 t f f f i 1 1560 "1560" _null_ aggregate_dummy - _null_));
DESCR("bitwise-or bit aggregate");

+DATA(insert OID = 2550(  pg_logfile_length		       PGNSP PGUID 12 f f f f v 0 23 "" _null_ pg_logfile_length - _null_ ));
+DESCR("length of log file");
+DATA(insert OID = 2551(  pg_logfile		               PGNSP PGUID 12 f f f f v 2 2275 "23 23" _null_ pg_logfile - _null_ ));
+DESCR("return log file contents");
+
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
Index: include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.241
diff -u -r1.241 builtins.h
--- include/utils/builtins.h	2 Jun 2004 21:29:29 -0000	1.241
+++ include/utils/builtins.h	8 Jun 2004 18:08:05 -0000
@@ -356,6 +356,8 @@
extern Datum current_database(PG_FUNCTION_ARGS);
extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+extern Datum pg_logfile_length(PG_FUNCTION_ARGS);
+extern Datum pg_logfile(PG_FUNCTION_ARGS);

/* not_in.c */
extern Datum int4notin(PG_FUNCTION_ARGS);
Index: include/utils/elog.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/elog.h,v
retrieving revision 1.68
diff -u -r1.68 elog.h
--- include/utils/elog.h	5 Apr 2004 03:02:10 -0000	1.68
+++ include/utils/elog.h	8 Jun 2004 18:08:06 -0000
@@ -182,8 +182,12 @@
#define LOG_DESTINATION_STDERR   1
#define LOG_DESTINATION_SYSLOG   2
#define LOG_DESTINATION_EVENTLOG 4
+#define LOG_DESTINATION_FILE     8

/* Other exported functions */
extern void DebugFileOpen(void);
+extern char *LogFileName(void);
+extern void LogFileOpen(void);
+extern void LogFileClose(void);

#endif /* ELOG_H */

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Oh, it would need doc additions. I can do that when I apply, or you can
resubmit.

---------------------------------------------------------------------------

Andreas Pflug wrote:

Magnus Hagander wrote:

Specifically about the logs, I still think there is a lot of value to
being able to read the logs remotely even if you can't restart
postmaster.

Since I believe that retrieving the logs easily without server file
access is a feature that's welcomed by many users, here's my proposal.

The attached diff will
- add a guc-variable log_filename
- extend log_destination to accept the keyword 'file'
- elog to that file if configured
- provide two functions pg_logfile_length() and pg_logfile to obtain the
contents.

int4 pg_logfile_length()
cstring pg_logfile(int4 size, int4 position)
size (may be null meaning max) is the chunk size (max: currently 50000)
position (may be null meaning -size) is the start position; positive
counting from log file start, negative from end.

Regards,
Andreas

Index: backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.402
diff -u -r1.402 postmaster.c
--- backend/postmaster/postmaster.c	3 Jun 2004 02:08:03 -0000	1.402
+++ backend/postmaster/postmaster.c	8 Jun 2004 18:07:30 -0000
@@ -532,6 +532,9 @@
/* If timezone is not set, determine what the OS uses */
pg_timezone_initialize();

+    /* open alternate logfile, if any */
+	LogFileOpen();
+
#ifdef EXEC_BACKEND
write_nondefault_variables(PGC_POSTMASTER);
#endif
Index: backend/storage/ipc/ipc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/storage/ipc/ipc.c,v
retrieving revision 1.87
diff -u -r1.87 ipc.c
--- backend/storage/ipc/ipc.c	12 Dec 2003 18:45:09 -0000	1.87
+++ backend/storage/ipc/ipc.c	8 Jun 2004 18:07:31 -0000
@@ -111,6 +111,8 @@
on_proc_exit_list[on_proc_exit_index].arg);

elog(DEBUG3, "exit(%d)", code);
+
+ LogFileClose();
exit(code);
}

Index: backend/utils/adt/misc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/misc.c,v
retrieving revision 1.34
diff -u -r1.34 misc.c
--- backend/utils/adt/misc.c	2 Jun 2004 21:29:29 -0000	1.34
+++ backend/utils/adt/misc.c	8 Jun 2004 18:07:36 -0000
@@ -103,3 +103,64 @@
{
PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0),SIGINT));
}
+
+Datum pg_logfile_length(PG_FUNCTION_ARGS)
+{
+	extern FILE *logfile; // in elog.c
+
+	if (logfile)
+		PG_RETURN_INT32(ftell(logfile));
+	PG_RETURN_INT32(0);
+}
+
+
+#define MAXLOGFILECHUNK 50000
+Datum pg_logfile(PG_FUNCTION_ARGS)
+{
+	size_t size=MAXLOGFILECHUNK;
+	char *buf=0;
+	size_t nbytes;
+
+	char *filename = LogFileName();
+	if (filename)
+	{
+		if (!PG_ARGISNULL(0))
+			size = PG_GETARG_INT32(0);
+		if (size > MAXLOGFILECHUNK)
+		{
+			size = MAXLOGFILECHUNK;
+			ereport(WARNING,
+					(errcode(ERRCODE_OUT_OF_MEMORY),
+					 errmsg("Maximum size is %d.", MAXLOGFILECHUNK)));
+		}
+
+		FILE *f=fopen(filename, "r");
+		if (f)
+		{
+			if (PG_ARGISNULL(1))
+				fseek(f, -size, SEEK_END);
+			else
+			{
+				long pos = PG_GETARG_INT32(1);
+				if (pos >= 0)
+					fseek(f, pos, SEEK_SET);
+				else
+					fseek(f, pos, SEEK_END);
+			}
+			buf = palloc(size+1);
+			nbytes = fread(buf, 1, size, f);
+			buf[nbytes] = 0;
+
+			fclose(f);
+		}
+		else
+		{
+			ereport(WARNING,
+					(errcode(ERRCODE_NO_DATA),
+					 errmsg("Could not open log file %s.", filename)));
+		}
+		free(filename);
+	}
+
+	PG_RETURN_CSTRING(buf);
+}
Index: backend/utils/error/elog.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/error/elog.c,v
retrieving revision 1.140
diff -u -r1.140 elog.c
--- backend/utils/error/elog.c	3 Jun 2004 02:08:04 -0000	1.140
+++ backend/utils/error/elog.c	8 Jun 2004 18:07:39 -0000
@@ -71,8 +71,10 @@
PGErrorVerbosity Log_error_verbosity = PGERROR_VERBOSE;
char       *Log_line_prefix = NULL; /* format for extra log line info */
unsigned int Log_destination = LOG_DESTINATION_STDERR;
+char *Log_filename = NULL;

bool in_fatal_exit = false;
+FILE *logfile = NULL;

#ifdef HAVE_SYSLOG
char *Syslog_facility; /* openlog() parameters */
@@ -936,6 +938,69 @@

/*
+ * Name of configured log file, or NULL
+ * must be freed after usage
+ */
+char*
+LogFileName(void)
+{
+	if (Log_filename && (Log_destination & LOG_DESTINATION_FILE))
+	{
+		if (is_absolute_path(Log_filename))
+			return strdup(Log_filename);
+		else
+		{
+			char *buf = malloc(strlen(DataDir) + strlen(Log_filename) +2);
+			if (!buf)
+				ereport(FATAL,
+						(errcode(ERRCODE_OUT_OF_MEMORY),
+						 errmsg("out of memory")));
+
+			sprintf(buf, "%s/%s", DataDir, Log_filename);
+
+			return buf;
+		}
+	}
+	return NULL;
+}
+
+
+/*
+ * Open log file, if configured.
+ */
+void
+LogFileOpen(void)
+{
+	char *filename = LogFileName();
+
+	if (filename)
+	{
+		LogFileClose();
+
+		logfile = fopen(filename, "at");
+
+		if (!logfile)
+			ereport(WARNING,
+					(errcode(ERRCODE_CONFIG_FILE_ERROR),
+					 errmsg("could not open log file %s", filename)));
+
+		free(filename);
+	}
+}
+
+
+/*
+ * Close log file, if open.
+ */
+void
+LogFileClose(void)
+{
+	if (logfile)
+		fclose(logfile);
+	logfile = NULL;
+}
+
+/*
* Initialization of error output file
*/
void
@@ -1445,6 +1510,11 @@
if ((Log_destination & LOG_DESTINATION_STDERR) || whereToSendOutput == Debug)
{
fprintf(stderr, "%s", buf.data);
+	}
+
+	if (logfile && (Log_destination & LOG_DESTINATION_FILE))
+	{
+		fprintf(logfile, "%s", buf.data);
}

pfree(buf.data);
Index: backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.210
diff -u -r1.210 guc.c
--- backend/utils/misc/guc.c	30 May 2004 23:40:38 -0000	1.210
+++ backend/utils/misc/guc.c	8 Jun 2004 18:07:47 -0000
@@ -76,6 +76,8 @@
static const char *assign_log_destination(const char *value,
bool doit, GucSource source);

+extern char *Log_filename;
+
#ifdef HAVE_SYSLOG
extern char *Syslog_facility;
extern char *Syslog_ident;
@@ -1644,13 +1646,23 @@
{
{"log_destination", PGC_POSTMASTER, LOGGING_WHERE,
gettext_noop("Sets the target for log output."),
-		 gettext_noop("Valid values are combinations of stderr, syslog "
+		 gettext_noop("Valid values are combinations of stderr, file, syslog "
"and eventlog, depending on platform."),
GUC_LIST_INPUT | GUC_REPORT
},
&log_destination_string,
"stderr", assign_log_destination, NULL
},
+	{
+		{"log_filename", PGC_POSTMASTER, LOGGING_WHERE,
+		 gettext_noop("Sets the target filename for log output."),
+		 gettext_noop("May be specified as relative to the cluster directory "
+					  "or as absolute path."),
+		 GUC_LIST_INPUT | GUC_REPORT
+		},
+		&Log_filename,
+		"postgresql.log", NULL, NULL
+	},

#ifdef HAVE_SYSLOG
{
@@ -4775,6 +4787,8 @@

if (pg_strcasecmp(tok,"stderr") == 0)
newlogdest |= LOG_DESTINATION_STDERR;
+		else if (pg_strcasecmp(tok,"file") == 0)
+			newlogdest |= LOG_DESTINATION_FILE;
#ifdef HAVE_SYSLOG
else if (pg_strcasecmp(tok,"syslog") == 0)
newlogdest |= LOG_DESTINATION_SYSLOG;
Index: backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.113
diff -u -r1.113 postgresql.conf.sample
--- backend/utils/misc/postgresql.conf.sample	7 Apr 2004 05:05:50 -0000	1.113
+++ backend/utils/misc/postgresql.conf.sample	8 Jun 2004 18:07:48 -0000
@@ -147,9 +147,10 @@

# - Where to Log -

-#log_destination = 'stderr'	# Valid values are combinations of stderr,
+#log_destination = 'stderr'	# Valid values are combinations of stderr, file,
# syslog and eventlog, depending on
# platform.
+#log_filename = 'pgsql.log'
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

Index: include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.334
diff -u -r1.334 pg_proc.h
--- include/catalog/pg_proc.h	2 Jun 2004 21:29:29 -0000	1.334
+++ include/catalog/pg_proc.h	8 Jun 2004 18:08:03 -0000
@@ -3588,6 +3588,12 @@
DATA(insert OID = 2243 ( bit_or						   PGNSP PGUID 12 t f f f i 1 1560 "1560" _null_ aggregate_dummy - _null_));
DESCR("bitwise-or bit aggregate");

+DATA(insert OID = 2550(  pg_logfile_length		       PGNSP PGUID 12 f f f f v 0 23 "" _null_ pg_logfile_length - _null_ ));
+DESCR("length of log file");
+DATA(insert OID = 2551(  pg_logfile		               PGNSP PGUID 12 f f f f v 2 2275 "23 23" _null_ pg_logfile - _null_ ));
+DESCR("return log file contents");
+
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
Index: include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.241
diff -u -r1.241 builtins.h
--- include/utils/builtins.h	2 Jun 2004 21:29:29 -0000	1.241
+++ include/utils/builtins.h	8 Jun 2004 18:08:05 -0000
@@ -356,6 +356,8 @@
extern Datum current_database(PG_FUNCTION_ARGS);
extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+extern Datum pg_logfile_length(PG_FUNCTION_ARGS);
+extern Datum pg_logfile(PG_FUNCTION_ARGS);

/* not_in.c */
extern Datum int4notin(PG_FUNCTION_ARGS);
Index: include/utils/elog.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/elog.h,v
retrieving revision 1.68
diff -u -r1.68 elog.h
--- include/utils/elog.h	5 Apr 2004 03:02:10 -0000	1.68
+++ include/utils/elog.h	8 Jun 2004 18:08:06 -0000
@@ -182,8 +182,12 @@
#define LOG_DESTINATION_STDERR   1
#define LOG_DESTINATION_SYSLOG   2
#define LOG_DESTINATION_EVENTLOG 4
+#define LOG_DESTINATION_FILE     8

/* Other exported functions */
extern void DebugFileOpen(void);
+extern char *LogFileName(void);
+extern void LogFileOpen(void);
+extern void LogFileClose(void);

#endif /* ELOG_H */

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Looks good to me. The only issue I saw was that the default file name
mentioned in postgresql.conf doesn't match the actual default.

I'm really not happy with the concept that the postmaster overrides
its stderr direction.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Looks good to me. The only issue I saw was that the default file name
mentioned in postgresql.conf doesn't match the actual default.

I'm really not happy with the concept that the postmaster overrides
its stderr direction.

Me either without more thought.

If we start logging to a file explicitly, do we need to revisit the log
rotation discussion which seems to have gone nowhere several times recently?

cheers

andrew

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Looks good to me. The only issue I saw was that the default file name
mentioned in postgresql.conf doesn't match the actual default.

I'm really not happy with the concept that the postmaster overrides
its stderr direction.

I agree, that's why I implemented it as *additional* log_destination.

Regards,
Andreas

Andreas Pflug wrote:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Looks good to me. The only issue I saw was that the default file name
mentioned in postgresql.conf doesn't match the actual default.

I'm really not happy with the concept that the postmaster overrides
its stderr direction.

I agree, that's why I implemented it as *additional* log_destination.

One idea would be to send a message to stderr when this option is used
stating that everything is going to 'filename'.

Also can we close/reopen the file on SIGHUP. My guess is we can't
because of all the backends accessing the output file.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sorry I didn't get back on this earlier, yesterday morning my internet
access was literally struck by lightning, I'm running temporary hardware
now.

Bruce Momjian wrote:

Looks good to me. The only issue I saw was that the default file name
mentioned in postgresql.conf doesn't match the actual default.

I'll fix the default filename difference, postgresql.log seems best.
I'll add doc too.

One idea would be to send a message to stderr when this option is used
stating that everything is going to 'filename'.

I can ereport LogFileOpen and LogFileClose, do we need this? Currently,
only open problems will be reported.

Also can we close/reopen the file on SIGHUP. My guess is we can't
because of all the backends accessing the output file.

I'd also like it flushed in pg_logfile and pg_logfile_length, same
problem; any hints welcome.

Regards,
Andreas

Andreas Pflug wrote:

Sorry I didn't get back on this earlier, yesterday morning my internet
access was literally struck by lightning, I'm running temporary hardware
now.

Bruce Momjian wrote:

Looks good to me. The only issue I saw was that the default file name
mentioned in postgresql.conf doesn't match the actual default.

I'll fix the default filename difference, postgresql.log seems best.
I'll add doc too.

One idea would be to send a message to stderr when this option is used
stating that everything is going to 'filename'.

I can ereport LogFileOpen and LogFileClose, do we need this? Currently,
only open problems will be reported.

Actually, my idea of sending a message to stderr saying we are using a
pre-configured file is so folks aren't surprised by the fact they can't
see any stderr anymore. But doesn't syslog have the same issue. Maybe
not, and that's why we need a message like:

"All standard output and standard error are going to postgresql.conf"

and send that to the processes standard error.

Also can we close/reopen the file on SIGHUP. My guess is we can't
because of all the backends accessing the output file.

I'd also like it flushed in pg_logfile and pg_logfile_length, same
problem; any hints welcome.

I don't understand this. I was thinking of close/reopen so log files
could be rotated.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Actually, my idea of sending a message to stderr saying we are using a
pre-configured file is so folks aren't surprised by the fact they can't
see any stderr anymore.

Hm? I thought we'd just established that the patch wasn't going to
suppress output to stderr.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Actually, my idea of sending a message to stderr saying we are using a
pre-configured file is so folks aren't surprised by the fact they can't
see any stderr anymore.

Hm? I thought we'd just established that the patch wasn't going to
suppress output to stderr.

Oh, I didn't see that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

I was thinking of close/reopen so log files
could be rotated.

Log file rotation is fine, if we find a consensus quite soon how to
implement it... Seems as if I might find some time to implement it until
feature freeze.

The attached patch has the default filename issue fixed, and
documentation. Since I don't have a doc build system functional, there
might be tag mismatches or other typos; please check. IMHO this should
be committed without waiting for log rotation stuff.

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:

The attached patch has the default filename issue fixed, and
documentation. Since I don't have a doc build system functional, there
might be tag mismatches or other typos; please check. IMHO this should
be committed without waiting for log rotation stuff.

This has got portability issues (fopen("ab")) and I don't care for its
use of malloc in preference to palloc either. Also, pg_logfile() will
dump core if LogFileName returns null.

The bigger issue though is whether this is useful at all, if you cannot
solve the file rotation issue (and I don't think you can). As
implemented, the secondary log file cannot be truncated without
restarting the postmaster. I think that reduces it from a possibly
useful feature to a useless toy. (The fact that pg_logfile_length
returns int and not something wider is pretty silly in this connection.)

My vote is not to apply until and unless something that can rotate the
logfile is demonstrated ...

regards, tom lane

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

The attached patch has the default filename issue fixed, and
documentation. Since I don't have a doc build system functional, there
might be tag mismatches or other typos; please check. IMHO this should
be committed without waiting for log rotation stuff.

This has got portability issues (fopen("ab")) and I don't care for its
use of malloc in preference to palloc either. Also, pg_logfile() will
dump core if LogFileName returns null.

The bigger issue though is whether this is useful at all, if you cannot
solve the file rotation issue (and I don't think you can). As
implemented, the secondary log file cannot be truncated without
restarting the postmaster. I think that reduces it from a possibly
useful feature to a useless toy. (The fact that pg_logfile_length
returns int and not something wider is pretty silly in this connection.)

My vote is not to apply until and unless something that can rotate the
logfile is demonstrated ...

Agreed. Lets see where it goes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

This has got portability issues (fopen("ab"))

My doc says b is ignored on ansi systems, and recommends using it. Do
you have other experiences?

and I don't care for its
use of malloc in preference to palloc either.

Do we already have an applicable memory context in the postmaster at
that early stage of initialization?

Also, pg_logfile() will dump core if LogFileName returns null.

How that?

char *filename=LogFileName();
if (filename)
{
...
free(filename);
}

The bigger issue though is whether this is useful at all, if you cannot
solve the file rotation issue (and I don't think you can). As
implemented, the secondary log file cannot be truncated without
restarting the postmaster. I think that reduces it from a possibly
useful feature to a useless toy.

This patch isn't trying to be better on logfile handling than the
default stderr redirection behavior, besides being able to access it
through the postmaster. Seems you insist to name this a toy, many users
don't.

(The fact that pg_logfile_length
returns int and not something wider is pretty silly in this connection.)

2GB logfile seems pretty big...

Regards,
Andreas

Were are we on this?

---------------------------------------------------------------------------

Andreas Pflug wrote:

Tom Lane wrote:

This has got portability issues (fopen("ab"))

My doc says b is ignored on ansi systems, and recommends using it. Do
you have other experiences?

and I don't care for its
use of malloc in preference to palloc either.

Do we already have an applicable memory context in the postmaster at
that early stage of initialization?

Also, pg_logfile() will dump core if LogFileName returns null.

How that?

char *filename=LogFileName();
if (filename)
{
...
free(filename);
}

The bigger issue though is whether this is useful at all, if you cannot
solve the file rotation issue (and I don't think you can). As
implemented, the secondary log file cannot be truncated without
restarting the postmaster. I think that reduces it from a possibly
useful feature to a useless toy.

This patch isn't trying to be better on logfile handling than the
default stderr redirection behavior, besides being able to access it
through the postmaster. Seems you insist to name this a toy, many users
don't.

(The fact that pg_logfile_length
returns int and not something wider is pretty silly in this connection.)

2GB logfile seems pretty big...

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073