*** pgsql/src/backend/utils/misc/postgresql.conf.sample 2007-02-01 11:38:25.000000000 +1100 --- workingpgsql/src/backend/utils/misc/postgresql.conf.sample 2007-02-14 15:30:45.000000000 +1100 *************** *** 257,262 **** --- 257,267 ---- #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' + # - How to Log - + + #log_output_type = 'text' #Valid values are 'SQL' or 'text' + #log_output_table_name = 'auditlogs' + # - When to Log - *** pgsql/src/backend/utils/misc/guc.c 2007-02-14 15:16:24.000000000 +1100 --- workingpgsql/src/backend/utils/misc/guc.c 2007-02-14 16:14:03.000000000 +1100 *************** *** 153,158 **** --- 153,159 ---- static const char *show_tcp_keepalives_idle(void); static const char *show_tcp_keepalives_interval(void); static const char *show_tcp_keepalives_count(void); + static const char *check_logtype_combination(const char *facility, bool doit, GucSource source); /* * GUC option variables that are exported from this module *************** *** 210,215 **** --- 211,217 ---- static char *log_statement_str; static char *log_min_error_statement_str; static char *log_destination_string; + static char *log_output_type_string; #ifdef HAVE_SYSLOG static char *syslog_facility_str; *************** *** 324,329 **** --- 326,333 ---- gettext_noop("Reporting and Logging"), /* LOGGING_WHERE */ gettext_noop("Reporting and Logging / Where to Log"), + /* LOGGING_HOW */ + gettext_noop("Reporting and Logging / How to Log"), /* LOGGING_WHEN */ gettext_noop("Reporting and Logging / When to Log"), /* LOGGING_WHAT */ *************** *** 2158,2163 **** --- 2162,2187 ---- }, #endif + /* + * For the logs to be output as INSERT statements + */ + { + {"log_output_table_name", PGC_POSTMASTER, LOGGING_HOW, + gettext_noop("The name of the table where the logs will be uploaded to."), + NULL + }, + &Log_output_table_name, + "", NULL, NULL + }, + { + {"log_output_type", PGC_POSTMASTER, LOGGING_HOW, + gettext_noop("Decides the output type of the log. Text or SQL."), + gettext_noop("Valid values are text, SQL.") + }, + &log_output_type_string, + "text", check_logtype_combination, NULL + }, + { {"TimeZone", PGC_USERSET, CLIENT_CONN_LOCALE, gettext_noop("Sets the time zone for displaying and interpreting time stamps."), *************** *** 6022,6027 **** --- 6046,6068 ---- return value; } + /* + * Check and set the log output type. SQL or text. + * Defaults to text. + */ + static const char * + check_logtype_combination(const char *value, bool doit, GucSource source) + { + Log_output_sql = false; + + if ( (value != NULL) && (pg_strcasecmp(value, "SQL") == 0) ) + { + Log_output_sql = true; + } + + return value; + } + #ifdef HAVE_SYSLOG static const char * *** pgsql/src/include/utils/elog.h 2007-01-16 16:29:39.000000000 +1100 --- workingpgsql/src/include/utils/elog.h 2007-02-14 16:21:21.000000000 +1100 *************** *** 273,278 **** --- 273,280 ---- extern PGErrorVerbosity Log_error_verbosity; extern char *Log_line_prefix; extern int Log_destination; + extern bool Log_output_sql; + extern char *Log_output_table_name; /* Log destination bitmap */ #define LOG_DESTINATION_STDERR 1 *** pgsql/src/include/utils/guc_tables.h 2007-01-16 16:29:40.000000000 +1100 --- workingpgsql/src/include/utils/guc_tables.h 2007-02-14 16:22:41.000000000 +1100 *************** *** 59,64 **** --- 59,65 ---- QUERY_TUNING_OTHER, LOGGING, LOGGING_WHERE, + LOGGING_HOW, LOGGING_WHEN, LOGGING_WHAT, STATS, *** pgsql/src/backend/utils/error/elog.c 2007-02-14 15:16:22.000000000 +1100 --- workingpgsql/src/backend/utils/error/elog.c 2007-02-15 11:36:57.000000000 +1100 *************** *** 83,88 **** --- 83,91 ---- char *Log_line_prefix = NULL; /* format for extra log line info */ int Log_destination = LOG_DESTINATION_STDERR; + bool Log_output_sql = false; + char *Log_output_table_name = NULL; + #ifdef HAVE_SYSLOG static bool openlog_done = false; static char *syslog_ident = NULL; *************** *** 116,122 **** } while (0) ! static void log_line_prefix(StringInfo buf); static void send_message_to_server_log(ErrorData *edata); static void send_message_to_frontend(ErrorData *edata); static char *expand_fmt_string(const char *fmt, ErrorData *edata); --- 119,125 ---- } while (0) ! static void log_line_prefix(char *loglineprefix, StringInfo buf); static void send_message_to_server_log(ErrorData *edata); static void send_message_to_frontend(ErrorData *edata); static char *expand_fmt_string(const char *fmt, ErrorData *edata); *************** *** 124,130 **** static const char *error_severity(int elevel); static void append_with_tabs(StringInfo buf, const char *str); static bool is_log_level_output(int elevel, int log_min_level); ! /* * errstart --- begin an error-reporting cycle --- 127,135 ---- static const char *error_severity(int elevel); static void append_with_tabs(StringInfo buf, const char *str); static bool is_log_level_output(int elevel, int log_min_level); ! static void get_SQL_type_server_log(StringInfo buf, ErrorData *edata); ! static void get_timestamp(StringInfo buf); ! static void get_error_message(StringInfo buf, ErrorData *edata); /* * errstart --- begin an error-reporting cycle *************** *** 1330,1336 **** * Format tag info for log lines; append to the provided buffer. */ static void ! log_line_prefix(StringInfo buf) { /* static counter for line numbers */ static long log_line_number = 0; --- 1335,1341 ---- * Format tag info for log lines; append to the provided buffer. */ static void ! log_line_prefix(char *loglineprefix, StringInfo buf) { /* static counter for line numbers */ static long log_line_number = 0; *************** *** 1353,1369 **** } log_line_number++; ! if (Log_line_prefix == NULL) return; /* in case guc hasn't run yet */ ! format_len = strlen(Log_line_prefix); for (i = 0; i < format_len; i++) { ! if (Log_line_prefix[i] != '%') { /* literal char, just copy */ ! appendStringInfoChar(buf, Log_line_prefix[i]); continue; } /* go to char after '%' */ --- 1358,1374 ---- } log_line_number++; ! if (loglineprefix == NULL) return; /* in case guc hasn't run yet */ ! format_len = strlen(loglineprefix); for (i = 0; i < format_len; i++) { ! if (loglineprefix[i] != '%') { /* literal char, just copy */ ! appendStringInfoChar(buf, loglineprefix[i]); continue; } /* go to char after '%' */ *************** *** 1372,1378 **** break; /* format error - ignore it */ /* process the option */ ! switch (Log_line_prefix[i]) { case 'u': if (MyProcPort) --- 1377,1383 ---- break; /* format error - ignore it */ /* process the option */ ! switch (loglineprefix[i]) { case 'u': if (MyProcPort) *************** *** 1557,1641 **** initStringInfo(&buf); ! log_line_prefix(&buf); ! appendStringInfo(&buf, "%s: ", error_severity(edata->elevel)); ! ! if (Log_error_verbosity >= PGERROR_VERBOSE) ! appendStringInfo(&buf, "%s: ", unpack_sql_state(edata->sqlerrcode)); ! ! if (edata->message) ! append_with_tabs(&buf, edata->message); else - append_with_tabs(&buf, _("missing error text")); - - if (edata->cursorpos > 0) - appendStringInfo(&buf, _(" at character %d"), - edata->cursorpos); - else if (edata->internalpos > 0) - appendStringInfo(&buf, _(" at character %d"), - edata->internalpos); - - appendStringInfoChar(&buf, '\n'); - - if (Log_error_verbosity >= PGERROR_DEFAULT) { ! if (edata->detail) ! { ! log_line_prefix(&buf); ! appendStringInfoString(&buf, _("DETAIL: ")); ! append_with_tabs(&buf, edata->detail); ! appendStringInfoChar(&buf, '\n'); ! } ! if (edata->hint) ! { ! log_line_prefix(&buf); ! appendStringInfoString(&buf, _("HINT: ")); ! append_with_tabs(&buf, edata->hint); ! appendStringInfoChar(&buf, '\n'); ! } ! if (edata->internalquery) ! { ! log_line_prefix(&buf); ! appendStringInfoString(&buf, _("QUERY: ")); ! append_with_tabs(&buf, edata->internalquery); ! appendStringInfoChar(&buf, '\n'); ! } ! if (edata->context) ! { ! log_line_prefix(&buf); ! appendStringInfoString(&buf, _("CONTEXT: ")); ! append_with_tabs(&buf, edata->context); ! appendStringInfoChar(&buf, '\n'); ! } if (Log_error_verbosity >= PGERROR_VERBOSE) { ! /* assume no newlines in funcname or filename... */ ! if (edata->funcname && edata->filename) { ! log_line_prefix(&buf); ! appendStringInfo(&buf, _("LOCATION: %s, %s:%d\n"), ! edata->funcname, edata->filename, ! edata->lineno); } ! else if (edata->filename) { ! log_line_prefix(&buf); ! appendStringInfo(&buf, _("LOCATION: %s:%d\n"), ! edata->filename, edata->lineno); } } ! } ! ! /* ! * If the user wants the query that generated this error logged, do it. ! */ ! if (edata->elevel >= log_min_error_statement && debug_query_string != NULL) ! { ! log_line_prefix(&buf); ! appendStringInfoString(&buf, _("STATEMENT: ")); ! append_with_tabs(&buf, debug_query_string); ! appendStringInfoChar(&buf, '\n'); ! } #ifdef HAVE_SYSLOG /* Write to syslog, if enabled */ --- 1562,1646 ---- initStringInfo(&buf); ! if ((Log_output_sql == true) && ! (Log_output_table_name != NULL) && ! (pg_strcasecmp(Log_output_table_name, "") != 0)) ! { ! get_SQL_type_server_log(&buf, edata); ! } else { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfo(&buf, "%s: ", error_severity(edata->elevel)); ! if (Log_error_verbosity >= PGERROR_VERBOSE) + appendStringInfo(&buf, "%s: ", unpack_sql_state(edata->sqlerrcode)); + + /* Get the error message and cursor position if any. */ + get_error_message(&buf, edata); + + appendStringInfoChar(&buf, '\n'); + + if (Log_error_verbosity >= PGERROR_DEFAULT) { ! if (edata->detail) ! { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfoString(&buf, _("DETAIL: ")); ! append_with_tabs(&buf, edata->detail); ! appendStringInfoChar(&buf, '\n'); ! } ! if (edata->hint) ! { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfoString(&buf, _("HINT: ")); ! append_with_tabs(&buf, edata->hint); ! appendStringInfoChar(&buf, '\n'); ! } ! if (edata->internalquery) { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfoString(&buf, _("QUERY: ")); ! append_with_tabs(&buf, edata->internalquery); ! appendStringInfoChar(&buf, '\n'); } ! if (edata->context) { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfoString(&buf, _("CONTEXT: ")); ! append_with_tabs(&buf, edata->context); ! appendStringInfoChar(&buf, '\n'); ! } ! if (Log_error_verbosity >= PGERROR_VERBOSE) ! { ! /* assume no newlines in funcname or filename... */ ! if (edata->funcname && edata->filename) ! { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfo(&buf, _("LOCATION: %s, %s:%d\n"), ! edata->funcname, edata->filename, ! edata->lineno); ! } ! else if (edata->filename) ! { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfo(&buf, _("LOCATION: %s:%d\n"), ! edata->filename, edata->lineno); ! } } } ! ! /* ! * If the user wants the query that generated this error logged, do it. ! */ ! if (edata->elevel >= log_min_error_statement && debug_query_string != NULL) ! { ! log_line_prefix(Log_line_prefix, &buf); ! appendStringInfoString(&buf, _("STATEMENT: ")); ! append_with_tabs(&buf, debug_query_string); ! appendStringInfoChar(&buf, '\n'); ! } ! } #ifdef HAVE_SYSLOG /* Write to syslog, if enabled */ *************** *** 2079,2081 **** --- 2084,2323 ---- return false; } + + /* + * This function is to construct the log string into a SQL format. + * For text based log, the output string is based on the user's input whereas + * for SQL we need a fixed format. So a seperate method to simplify. + * Benefit to the user is that he can continue to specify arbitrary + * log_line_prefix. + */ + static void + get_SQL_type_server_log(StringInfo buf, ErrorData *edata) + { + int format_len = 0; + int i; + int proc_id = 0; + char **loglineprefixvalues = NULL; + + StringInfoData msgbuf; + + initStringInfo(&msgbuf); + + loglineprefixvalues = (char **) palloc(10 * sizeof(char *)); + + for (i=0; i < 10; i++) + strcpy(loglineprefixvalues[i], "NULL"); + + if (Log_line_prefix != NULL) + format_len = strlen(Log_line_prefix); + + for (i = 0; i < format_len; i++) + { + if (Log_line_prefix[i] != '%') + { + /* literal char, ignore for SQL style output */ + continue; + } + /* go to char after '%' */ + i++; + if (i >= format_len) + break; /* format error - ignore it */ + + /* process the option */ + + switch (Log_line_prefix[i]) + { + case 'm': + { + initStringInfo(&msgbuf); + log_line_prefix("%m", &msgbuf); + loglineprefixvalues[0] = pstrdup(msgbuf.data); + } + break; + case 't': + { + initStringInfo(&msgbuf); + log_line_prefix("%t", &msgbuf); + loglineprefixvalues[1] = pstrdup(msgbuf.data); + } + break; + case 'u': + { + initStringInfo(&msgbuf); + log_line_prefix("%u", &msgbuf); + loglineprefixvalues[2] = pstrdup(msgbuf.data); + } + break; + case 'd': + { + initStringInfo(&msgbuf); + log_line_prefix("%d", &msgbuf); + loglineprefixvalues[3] = pstrdup(msgbuf.data); + } + break; + case 'c': + { + initStringInfo(&msgbuf); + log_line_prefix("%c", &msgbuf); + loglineprefixvalues[4] = pstrdup(msgbuf.data); + } + break; + case 'r': + { + initStringInfo(&msgbuf); + log_line_prefix("%r", &msgbuf); + loglineprefixvalues[5] = pstrdup(msgbuf.data); + } + break; + case 'h': + { + initStringInfo(&msgbuf); + log_line_prefix("%h", &msgbuf); + loglineprefixvalues[6] = pstrdup(msgbuf.data); + } + break; + + case 'p': + proc_id = MyProcPid; + break; + case 'i': + { + initStringInfo(&msgbuf); + log_line_prefix("%i", &msgbuf); + loglineprefixvalues[7] = pstrdup(msgbuf.data); + } + break; + case 's': + { + initStringInfo(&msgbuf); + log_line_prefix("%s", &msgbuf); + loglineprefixvalues[8] = pstrdup(msgbuf.data); + } + break; + case 'x': + { + initStringInfo(&msgbuf); + log_line_prefix("%x", &msgbuf); + loglineprefixvalues[9] = pstrdup(msgbuf.data); + } + break; + default: + /* Nothing else for SQL type. ignore it */ + break; + } + } + + /* + * We need timestamp as the unique column. So we get it even if the user + * hasn't given it as an option. + */ + if ( (loglineprefixvalues[0] == NULL) || (strcasecmp(loglineprefixvalues[0],"NULL") == 0) ) + { + initStringInfo(&msgbuf); + get_timestamp(&msgbuf); + loglineprefixvalues[0] = pstrdup(msgbuf.data); + } + + /* Construct the SQL type log string. */ + initStringInfo(&msgbuf); + appendStringInfo(&msgbuf, "INSERT INTO %s VALUES (" , Log_output_table_name); + + appendStringInfo(&msgbuf, "%s,%s,%s,%s,%s,%s,%s,%d,%s,%s, %s,", + loglineprefixvalues[0], + loglineprefixvalues[1], + loglineprefixvalues[2], + loglineprefixvalues[3], + loglineprefixvalues[4], + loglineprefixvalues[5], + loglineprefixvalues[6], + proc_id, + loglineprefixvalues[7], + loglineprefixvalues[8], + loglineprefixvalues[9] + ); + appendStringInfo(&msgbuf, "%s,", error_severity(edata->elevel)); + appendStringInfo(&msgbuf, "%s,", unpack_sql_state(edata->sqlerrcode)); + get_error_message(&msgbuf, edata); + + /* + * If the user wants the query that generated this error logged, do it. + */ + if (edata->elevel >= log_min_error_statement && debug_query_string != NULL) + { + appendStringInfoString(&msgbuf, _(",STATEMENT: ")); + append_with_tabs(&msgbuf, debug_query_string); + } + appendStringInfoString(&msgbuf, _(")")); + + appendStringInfo(buf, pstrdup(msgbuf.data)); + + pfree(loglineprefixvalues); + pfree (msgbuf.data); + } + + /* + * Appends the buffer with the error message and the cursor position. + */ + static void + get_error_message(StringInfo buf, ErrorData *edata) + { + StringInfoData msgbuf; + + initStringInfo(&msgbuf); + + if (edata->message) + append_with_tabs(&msgbuf, edata->message); + else + append_with_tabs(&msgbuf, _("missing error text")); + + if (edata->cursorpos > 0) + appendStringInfo(&msgbuf, _(" at character %d"), + edata->cursorpos); + else if (edata->internalpos > 0) + appendStringInfo(&msgbuf, _(" at character %d"), + edata->internalpos); + appendStringInfo(buf, "%s", pstrdup(msgbuf.data)); + } + + /* + * Calculates and returns the timestamp + */ + static void + get_timestamp(StringInfo buf) + { + /* + * Note: for %m, %t, and %s we deliberately use the C + * library's strftime/localtime, and not the equivalent + * functions from src/timezone. This ensures that all + * backends will report log entries in the same timezone, + * namely whatever C-library setting they inherit from the + * postmaster. If we used src/timezone then local + * settings of the TimeZone GUC variable would confuse the + * log. + */ + time_t stamp_time; + char strfbuf[128], + msbuf[8]; + struct timeval tv; + + gettimeofday(&tv, NULL); + stamp_time = tv.tv_sec; + + strftime(strfbuf, sizeof(strfbuf), + /* leave room for milliseconds... */ + /* Win32 timezone names are too long so don't print them. */ + #ifndef WIN32 + "%Y-%m-%d %H:%M:%S %Z", + #else + "%Y-%m-%d %H:%M:%S ", + #endif + localtime(&stamp_time)); + + /* 'paste' milliseconds into place... */ + sprintf(msbuf, ".%03d", (int) (tv.tv_usec / 1000)); + strncpy(strfbuf + 19, msbuf, 4); + + appendStringInfoString(buf, strfbuf); + }