Hi All,
On Mon, Jul 13, 2015 at 2:12 PM, Tom Lane <[email protected]> wrote:
> Jim Nasby <[email protected]> writes:
> > On 7/13/15 3:39 PM, dinesh kumar wrote:
> >> Ah. It's' my bad interpretation. Let me work on it, and will send a new
> >> patch as a wrapper sql function for ereport.
>
> > You might want to present a plan for that; it's not as trivial as it
> > sounds due to how ereport works. In particular, I'd want to see (at
> > minimum) the same functionality that plpgsql's RAISE command now
> > provides (errdetail, errhint, etc).
>
>
Jim,
For now, I worked on (ERROR Level, ERROR Message, HIDE ERROR Stmt). In our
to do item description, I found this wrapper needs to return "Anyelement".
But, I believe, return "VOID" is enough for this function. Let me know if I
erred here.
In design phase,
1. I took a CustomDataType with the elevel code, elevel text
2. Populated this CDT with all existing pre-processors, except {FATAL,
PANIC}. Since, we don't expose these to client.
3. By matching the user elevel text, processing the report log function.
Find the attached patch with implementation.
> The real question is why the existing functionality in plpgsql isn't
> sufficient. Somebody who wants a "log from SQL" function can easily
> write a simple plpgsql function that does exactly what they want,
> with no more or fewer bells-n-whistles than they need. If we try
> to create a SQL function that does all that, it's likely to be a mess
> to use, even with named arguments.
>
> I'm not necessarily against the basic idea, but I think inventing
> something that actually offers an increment in usability compared
> to the existing alternative is going to be harder than it sounds.
>
>
Tom,
I agree with your inputs. We can build pl/pgsql function as alternative
for this.
My initial proposal, and implementation was, logging messages to log file
irrespectively of our log settings. I was not sure we can do this with some
pl/perlu. And then, I started working on our to do item,
ereport, wrapper callable from SQL, and found it can be useful to have a
direct function call with required log level.
Thanks.
Regards,
Dinesh
manojadinesh.blogspot.com
regards, tom lane
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 76f77cb..43dbaec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17850,6 +17850,15 @@ postgres=# SELECT * FROM
pg_xlogfile_name_offset(pg_stop_backup());
Return information about a file.
</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>pg_report_log(<parameter>elevel</><type>text</>,
<parameter>message</> <type>anyelement</type>,
<parameter>ishidestmt</><type>boolean</>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Write message into log file as per log level.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -17918,6 +17927,24 @@ SELECT (pg_stat_file('filename')).modification;
</programlisting>
</para>
+ <indexterm>
+ <primary>pg_report_log</primary>
+ </indexterm>
+ <para>
+ <function>pg_report_log</> is useful to write custom messages
+ into current log destination and returns <type>void</type>.
+ This function don't support the PANIC, FATAL log levels due to their
unique internal DB usage, which may cause the database instability. Using
<parameter>ishidestmt</>, function can write or ignore the current SQL
statement into the log file.
+ Typical usages include:
+<programlisting>
+postgres=# SELECT pg_report_log('NOTICE', 'Custom Message', true);
+NOTICE: Custom Message
+ pg_report_log
+---------------
+
+(1 row)
+</programlisting>
+ </para>
+
</sect2>
<sect2 id="functions-advisory-locks">
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c0495d9..1c7c263 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -76,6 +76,91 @@ current_query(PG_FUNCTION_ARGS)
}
/*
+ * pg_report_log()
+ *
+ * Printing custom log messages in log file.
+ */
+
+typedef struct
+{
+ int ecode;
+ char *level;
+} errorlevels;
+
+Datum
+pg_report_log(PG_FUNCTION_ARGS)
+{
+
+ /*
+ * Do not add FATAL, PANIC log levels to the below list.
+ */
+ errorlevels elevels[]={
+ {DEBUG5, "DEBUG5"}, {DEBUG4, "DEBUG4"}, {DEBUG3,
"DEBUG3"},
+ {DEBUG2, "DEBUG2"}, {DEBUG1, "DEBUG1"}, {LOG, "LOG"},
+ {COMMERROR, "COMMERROR"}, {INFO, "INFO"}, {NOTICE,
"NOTICE"},
+ {WARNING, "WARNING"}, {ERROR, "ERROR"}
+ /*
+ * Adding PGERROR to elevels if WIN32
+ */
+ #ifdef WIN32
+ ,{PGERROR, "PGERROR"}
+ #endif
+ };
+
+ int itr = 0;
+ bool ishidestmt = false;
+ int noelevel = (int) sizeof(elevels)/sizeof(*elevels);
+ char *level;
+
+ level = text_to_cstring(PG_GETARG_TEXT_P(0));
+ ishidestmt = PG_GETARG_BOOL(2);
+
+ /*
+ * Do not expose FATAL, PANIC log levels to outer world.
+ */
+ if(pg_strcasecmp("FATAL", level) == 0)
+ ereport(ERROR,
+ (errmsg("%s is an unsupported report log
level.", level)));
+
+ else if(pg_strcasecmp("PANIC", level) == 0)
+ ereport(ERROR,
+ (errmsg("%s is an unsupported report log
level.", level)));
+ else
+ {
+ while (itr < noelevel)
+ {
+ if (pg_strcasecmp(elevels[itr].level, level) == 0)
+ {
+ /*
+ * Is errhide statement true
+ */
+ if (ishidestmt == true)
+ {
+ ereport(elevels[itr].ecode,
+ (errmsg("%s",
text_to_cstring(PG_GETARG_TEXT_P(1))),
+
errhidestmt(true)));
+ break;
+ }
+ else
+ {
+ ereport(elevels[itr].ecode,_
+ (errmsg("%s",
text_to_cstring(PG_GETARG_TEXT_P(1)))));
+ break;
+ }
+ }
+ itr++;
+ }
+
+ /* Invalid log level */
+ if (itr == noelevel)
+ ereport(ERROR,
+ (errmsg("Unknown log level \"%s\"",
level)));
+ else
+ PG_RETURN_VOID();
+ }
+}
+
+/*
* Send a signal to another backend.
*
* The signal is delivered if the user is either a superuser or the same
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6fd1278..b745d80 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5344,6 +5344,14 @@ DESCR("tsm_bernoulli_reset(internal)");
DATA(insert OID = 3346 ( tsm_bernoulli_cost PGNSP PGUID 12 1 0 0 0
f f f f t f v 7 0 2278 "2281 2281 2281 2281 2281 2281 2281" _null_ _null_
_null_ _null_ _null_ tsm_bernoulli_cost _null_ _null_ _null_ ));
DESCR("tsm_bernoulli_cost(internal)");
+/* Logging function */
+
+DATA(insert OID = 6015 ( pg_report_log PGNSP PGUID 12 1 0 0 0
f f f f t f i 3 0 2278 "25 25 16" _null_ _null_ _null_ _null_ _null_
pg_report_log _null_ _null_ _null_ ));
+DESCR("write message to log file");
+DATA(insert OID = 6016 ( pg_report_log PGNSP PGUID 14 1 0 0 0
f f f f t f s 3 0 2278 "25 2283 16" _null_ _null_ _null_ _null_ _null_ "SELECT
pg_report_log($1::pg_catalog.text, $2::pg_catalog.text, $3::boolean)" _null_
_null_ _null_ ));
+DESCR("write message to log file");
+
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index fcb0bf0..3a2164b 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -495,6 +495,7 @@ extern Datum pg_typeof(PG_FUNCTION_ARGS);
extern Datum pg_collation_for(PG_FUNCTION_ARGS);
extern Datum pg_relation_is_updatable(PG_FUNCTION_ARGS);
extern Datum pg_column_is_updatable(PG_FUNCTION_ARGS);
+extern Datum pg_report_log(PG_FUNCTION_ARGS);
/* oid.c */
extern Datum oidin(PG_FUNCTION_ARGS);
diff --git a/src/include/utils/elog.h b/src/include/utils/elog.h
index 7684717..fcb7218 100644
--- a/src/include/utils/elog.h
+++ b/src/include/utils/elog.h
@@ -16,6 +16,13 @@
#include <setjmp.h>
+/*
+ * XXX
+ * If you are adding another elevel, make sure you update the
+ * pg_report_log() in src/backend/utils/adt/misc.c, with the
+ * new elevel
+ */
+
/* Error level codes */
#define DEBUG5 10 /* Debugging messages, in
categories of
* decreasing
detail. */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
