You are viewing a plain text version of this content. The canonical link for it is here.
Posted to log4j-user@logging.apache.org by Peter Rifel <pr...@mixpo.com> on 2013/08/22 23:12:13 UTC
Log4j2: Column casting in JDBCAppender
Hello,
I'm wondering if it is at all possible to cast columns to a certain type (a custom enum) with the JDBCAppender. I have an existing PostgreSQL database that I send all warnings and errors to and the log level is currently being stored as an enum. When I try and log to the database I get the following message:
org.postgresql.util.PSQLException: ERROR: column "level" is of type log_level but expression is of type character varying Hint: You will need to rewrite or cast the expression.
Looking through the JDBCDatabaseManager it doesn't appear that this is possible; including the cast in the pattern string results in it getting passed as the value itself rather than the prepared statement. Does anyone have any other ideas on how to accomplish this? I wouldn't mind making contributions to the source code to help implement this feature if it is desired.
Thanks,
Peter
Re: Log4j2: Column casting in JDBCAppender
Posted by Gary Gregory <ga...@gmail.com>.
On Thu, Aug 22, 2013 at 6:32 PM, Nick Williams <
nicholas@nicholaswilliams.net> wrote:
> Well we don't need what's currently provided with those legacy classes,
> because a different approach was taken with this JDBCAppender to handle ID
> columns.
>
> I have several problems with this approach:
>
> 1) The implementation would be far more complicated, because the dialects
> would have to be used to build the prepared statement SQL and bind
> arguments to it.
> 2) Performance. I spent considerable time experimenting with ways to
> improve the performance of the JDBCAppender. One thing I discovered, for
> example, was that it was (much ... like 50%) more efficient to have a loop
> with a bunch of if-statements in it than to use polymorphism and method
> invocations to handle the different logic branches. The problem was the
> overhead of stack management (adding/removing method invocations from the
> stack). Stack management erased the alternate approaches I took every
> single time. If we now have to defer to a dialect to perform certain
> operations, performance will be impacted by stack management.
>
Can you add this to comments (Javadoc or not) in the code. Let's make sure
someone doesn't undo your research and implementation.
Gary
> 3) Coverage. The link you provided has 6 dialects. Hibernate, on the other
> hand, has 36 dialects. It could easily snowball to that.
> 4) Testing. Each dialect would either have to lack comprehensive testing,
> or we'd need a bunch of database set up.
> 5) As you said, this is a "one-off." The problem is in PGSQL's JDBC
> implementation. I'm very resistive to the idea of adding an entire dialect
> feature such as this merely to work around a bug only present in PGSQL's
> JDBC driver.
>
> If someone could demonstrate a compelling need for dialects outside of
> working around this one bug in PGSQL's driver, then I might be convinced to
> also include casting in those dialects. Short of that, I think the
> JDBCAppender is much better off written generically enough that a simple,
> generic prepared statement can handle 99% of cases correctly.
>
> Nick
>
> On Aug 22, 2013, at 5:13 PM, Scott Deboy wrote:
>
> > That doesn't look like much work. Particularly since you could lift
> > what's provided and just extend it to support the one-off we're
> > talking about here, yes?
> >
> > On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
> >> Yes, I mean like that. I want to avoid something like that.
> >>
> >> N
> >>
> >> On Aug 22, 2013, at 4:59 PM, Scott Deboy wrote:
> >>
> >>> You mean like:
> >>>
> http://logging.apache.org/log4j/companions/receivers/apidocs/org/apache/log4j/db/dialect/package-summary.html
> >>>
> >>> Scott
> >>>
> >>> On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
> >>>> IMO, this is a bug in the PGSQL JDBC driver and not something that
> Log4j
> >>>> can
> >>>> easily (or should) address. For a detailed discussion, see the PGSQL
> >>>> thread
> >>>> about casting text to PGSQL enums [1].
> >>>>
> >>>> The problem lies in PGSQL's type handling. With PGSQL, MySQL, and any
> >>>> other
> >>>> database type that supports enums, you can write a query like this:
> >>>>
> >>>> INSERT INTO myTable (enumColumn) VALUES ('enumValue');
> >>>>
> >>>> And the database engine implicitly casts the text to enum. If you try
> >>>> this
> >>>> out on a PGSQL prompt, it works. However, unlike MySQL and other
> >>>> vendors,
> >>>> PGSQL does NOT allow String parameters in prepared statements to be
> >>>> auto-casted to enums. So the following is valid in all vendors that
> >>>> support
> >>>> enums:
> >>>>
> >>>> statement = connection.prepareStatement("INSERT INTO myTable
> >>>> (enumColumn)
> >>>> VALUES ('enumValue')");
> >>>>
> >>>> But the following, while valid with MySQL and everything else, breaks
> >>>> with
> >>>> PGSQL's JDBC driver:
> >>>>
> >>>> statement = connection.prepareStatement("INSERT INTO myTable
> >>>> (enumColumn)
> >>>> VALUES (?)");
> >>>> statement.setString(1, "enumValue");
> >>>>
> >>>> IMO, this was a stupid decision on PGSQL's part, and from the thread
> it
> >>>> sounds like others agree with me. It makes it extremely difficult to
> use
> >>>> PGSQL enums with, for example, the Java Persistence API or O/RMs in
> >>>> general.
> >>>> From the sounds of the thread, the guys over at PGSQL have to
> intention
> >>>> of
> >>>> fixing this.
> >>>>
> >>>> Now, as for your suggestion. As much as I'd like to accommodate your
> >>>> need
> >>>> here, it's simply not feasible. Nearly every single database vendor
> has
> >>>> a
> >>>> different way to cast data types. Some have a CAST function, others a
> >>>> CAST
> >>>> keyword, and then PGSQL has that odd double-colon syntax (::). If we
> were
> >>>> to
> >>>> add a casting feature, we would then have to have a sense of database
> >>>> dialects like an O/RM and learn the syntax of each database, correctly
> >>>> applying the syntax based on what type of database we're logging to.
> >>>> This
> >>>> would be a heavyweight bulky addition to such a simple feature. My
> >>>> recommendations, in descending order of how much effort it will
> require:
> >>>>
> >>>> 1) Submit a patch to PGSQL's JDBC driver and lobby for its acceptance.
> >>>> 2) Switch to a database vendor that's less [insert insulting word
> here]
> >>>> about its enum casting--like any of the other ones. My favorite is
> >>>> MySQL.
> >>>> 3) Stop using a database enum column.
> >>>>
> >>>> :-/
> >>>>
> >>>> Nick
> >>>>
> >>>> [1]
> >>>>
> http://www.postgresql.org/message-id/CAPPfruyta0A0xGhG4Zh785sS0_FZ8GczzcjZGXo2yfPhaDxuaA@mail.gmail.com
> >>>>
> >>>> On Aug 22, 2013, at 4:12 PM, Peter Rifel wrote:
> >>>>
> >>>>> Hello,
> >>>>>
> >>>>> I'm wondering if it is at all possible to cast columns to a certain
> >>>>> type
> >>>>> (a custom enum) with the JDBCAppender. I have an existing PostgreSQL
> >>>>> database that I send all warnings and errors to and the log level is
> >>>>> currently being stored as an enum. When I try and log to the
> database
> >>>>> I
> >>>>> get the following message:
> >>>>>
> >>>>> org.postgresql.util.PSQLException: ERROR: column "level" is of type
> >>>>> log_level but expression is of type character varying Hint: You will
> >>>>> need
> >>>>> to rewrite or cast the expression.
> >>>>>
> >>>>> Looking through the JDBCDatabaseManager it doesn't appear that this
> is
> >>>>> possible; including the cast in the pattern string results in it
> >>>>> getting
> >>>>> passed as the value itself rather than the prepared statement. Does
> >>>>> anyone have any other ideas on how to accomplish this? I wouldn't
> mind
> >>>>> making contributions to the source code to help implement this
> feature
> >>>>> if
> >>>>> it is desired.
> >>>>>
> >>>>> Thanks,
> >>>>>
> >>>>> Peter
> >>>>
> >>>>
> >>>
> >>> ---------------------------------------------------------------------
> >>> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> >>> For additional commands, e-mail: log4j-user-help@logging.apache.org
> >>>
> >>
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> >> For additional commands, e-mail: log4j-user-help@logging.apache.org
> >>
> >>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> > For additional commands, e-mail: log4j-user-help@logging.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> For additional commands, e-mail: log4j-user-help@logging.apache.org
>
>
--
E-Mail: garydgregory@gmail.com | ggregory@apache.org
Java Persistence with Hibernate, Second Edition<http://www.manning.com/bauer3/>
JUnit in Action, Second Edition <http://www.manning.com/tahchiev/>
Spring Batch in Action <http://www.manning.com/templier/>
Blog: http://garygregory.wordpress.com
Home: http://garygregory.com/
Tweet! http://twitter.com/GaryGregory
Re: Log4j2: Column casting in JDBCAppender
Posted by Nick Williams <ni...@nicholaswilliams.net>.
Well we don't need what's currently provided with those legacy classes, because a different approach was taken with this JDBCAppender to handle ID columns.
I have several problems with this approach:
1) The implementation would be far more complicated, because the dialects would have to be used to build the prepared statement SQL and bind arguments to it.
2) Performance. I spent considerable time experimenting with ways to improve the performance of the JDBCAppender. One thing I discovered, for example, was that it was (much ... like 50%) more efficient to have a loop with a bunch of if-statements in it than to use polymorphism and method invocations to handle the different logic branches. The problem was the overhead of stack management (adding/removing method invocations from the stack). Stack management erased the alternate approaches I took every single time. If we now have to defer to a dialect to perform certain operations, performance will be impacted by stack management.
3) Coverage. The link you provided has 6 dialects. Hibernate, on the other hand, has 36 dialects. It could easily snowball to that.
4) Testing. Each dialect would either have to lack comprehensive testing, or we'd need a bunch of database set up.
5) As you said, this is a "one-off." The problem is in PGSQL's JDBC implementation. I'm very resistive to the idea of adding an entire dialect feature such as this merely to work around a bug only present in PGSQL's JDBC driver.
If someone could demonstrate a compelling need for dialects outside of working around this one bug in PGSQL's driver, then I might be convinced to also include casting in those dialects. Short of that, I think the JDBCAppender is much better off written generically enough that a simple, generic prepared statement can handle 99% of cases correctly.
Nick
On Aug 22, 2013, at 5:13 PM, Scott Deboy wrote:
> That doesn't look like much work. Particularly since you could lift
> what's provided and just extend it to support the one-off we're
> talking about here, yes?
>
> On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
>> Yes, I mean like that. I want to avoid something like that.
>>
>> N
>>
>> On Aug 22, 2013, at 4:59 PM, Scott Deboy wrote:
>>
>>> You mean like:
>>> http://logging.apache.org/log4j/companions/receivers/apidocs/org/apache/log4j/db/dialect/package-summary.html
>>>
>>> Scott
>>>
>>> On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
>>>> IMO, this is a bug in the PGSQL JDBC driver and not something that Log4j
>>>> can
>>>> easily (or should) address. For a detailed discussion, see the PGSQL
>>>> thread
>>>> about casting text to PGSQL enums [1].
>>>>
>>>> The problem lies in PGSQL's type handling. With PGSQL, MySQL, and any
>>>> other
>>>> database type that supports enums, you can write a query like this:
>>>>
>>>> INSERT INTO myTable (enumColumn) VALUES ('enumValue');
>>>>
>>>> And the database engine implicitly casts the text to enum. If you try
>>>> this
>>>> out on a PGSQL prompt, it works. However, unlike MySQL and other
>>>> vendors,
>>>> PGSQL does NOT allow String parameters in prepared statements to be
>>>> auto-casted to enums. So the following is valid in all vendors that
>>>> support
>>>> enums:
>>>>
>>>> statement = connection.prepareStatement("INSERT INTO myTable
>>>> (enumColumn)
>>>> VALUES ('enumValue')");
>>>>
>>>> But the following, while valid with MySQL and everything else, breaks
>>>> with
>>>> PGSQL's JDBC driver:
>>>>
>>>> statement = connection.prepareStatement("INSERT INTO myTable
>>>> (enumColumn)
>>>> VALUES (?)");
>>>> statement.setString(1, "enumValue");
>>>>
>>>> IMO, this was a stupid decision on PGSQL's part, and from the thread it
>>>> sounds like others agree with me. It makes it extremely difficult to use
>>>> PGSQL enums with, for example, the Java Persistence API or O/RMs in
>>>> general.
>>>> From the sounds of the thread, the guys over at PGSQL have to intention
>>>> of
>>>> fixing this.
>>>>
>>>> Now, as for your suggestion. As much as I'd like to accommodate your
>>>> need
>>>> here, it's simply not feasible. Nearly every single database vendor has
>>>> a
>>>> different way to cast data types. Some have a CAST function, others a
>>>> CAST
>>>> keyword, and then PGSQL has that odd double-colon syntax (::). If we were
>>>> to
>>>> add a casting feature, we would then have to have a sense of database
>>>> dialects like an O/RM and learn the syntax of each database, correctly
>>>> applying the syntax based on what type of database we're logging to.
>>>> This
>>>> would be a heavyweight bulky addition to such a simple feature. My
>>>> recommendations, in descending order of how much effort it will require:
>>>>
>>>> 1) Submit a patch to PGSQL's JDBC driver and lobby for its acceptance.
>>>> 2) Switch to a database vendor that's less [insert insulting word here]
>>>> about its enum casting--like any of the other ones. My favorite is
>>>> MySQL.
>>>> 3) Stop using a database enum column.
>>>>
>>>> :-/
>>>>
>>>> Nick
>>>>
>>>> [1]
>>>> http://www.postgresql.org/message-id/CAPPfruyta0A0xGhG4Zh785sS0_FZ8GczzcjZGXo2yfPhaDxuaA@mail.gmail.com
>>>>
>>>> On Aug 22, 2013, at 4:12 PM, Peter Rifel wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> I'm wondering if it is at all possible to cast columns to a certain
>>>>> type
>>>>> (a custom enum) with the JDBCAppender. I have an existing PostgreSQL
>>>>> database that I send all warnings and errors to and the log level is
>>>>> currently being stored as an enum. When I try and log to the database
>>>>> I
>>>>> get the following message:
>>>>>
>>>>> org.postgresql.util.PSQLException: ERROR: column "level" is of type
>>>>> log_level but expression is of type character varying Hint: You will
>>>>> need
>>>>> to rewrite or cast the expression.
>>>>>
>>>>> Looking through the JDBCDatabaseManager it doesn't appear that this is
>>>>> possible; including the cast in the pattern string results in it
>>>>> getting
>>>>> passed as the value itself rather than the prepared statement. Does
>>>>> anyone have any other ideas on how to accomplish this? I wouldn't mind
>>>>> making contributions to the source code to help implement this feature
>>>>> if
>>>>> it is desired.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Peter
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
>>> For additional commands, e-mail: log4j-user-help@logging.apache.org
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
>> For additional commands, e-mail: log4j-user-help@logging.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> For additional commands, e-mail: log4j-user-help@logging.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
For additional commands, e-mail: log4j-user-help@logging.apache.org
Re: Log4j2: Column casting in JDBCAppender
Posted by Scott Deboy <sc...@gmail.com>.
That doesn't look like much work. Particularly since you could lift
what's provided and just extend it to support the one-off we're
talking about here, yes?
On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
> Yes, I mean like that. I want to avoid something like that.
>
> N
>
> On Aug 22, 2013, at 4:59 PM, Scott Deboy wrote:
>
>> You mean like:
>> http://logging.apache.org/log4j/companions/receivers/apidocs/org/apache/log4j/db/dialect/package-summary.html
>>
>> Scott
>>
>> On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
>>> IMO, this is a bug in the PGSQL JDBC driver and not something that Log4j
>>> can
>>> easily (or should) address. For a detailed discussion, see the PGSQL
>>> thread
>>> about casting text to PGSQL enums [1].
>>>
>>> The problem lies in PGSQL's type handling. With PGSQL, MySQL, and any
>>> other
>>> database type that supports enums, you can write a query like this:
>>>
>>> INSERT INTO myTable (enumColumn) VALUES ('enumValue');
>>>
>>> And the database engine implicitly casts the text to enum. If you try
>>> this
>>> out on a PGSQL prompt, it works. However, unlike MySQL and other
>>> vendors,
>>> PGSQL does NOT allow String parameters in prepared statements to be
>>> auto-casted to enums. So the following is valid in all vendors that
>>> support
>>> enums:
>>>
>>> statement = connection.prepareStatement("INSERT INTO myTable
>>> (enumColumn)
>>> VALUES ('enumValue')");
>>>
>>> But the following, while valid with MySQL and everything else, breaks
>>> with
>>> PGSQL's JDBC driver:
>>>
>>> statement = connection.prepareStatement("INSERT INTO myTable
>>> (enumColumn)
>>> VALUES (?)");
>>> statement.setString(1, "enumValue");
>>>
>>> IMO, this was a stupid decision on PGSQL's part, and from the thread it
>>> sounds like others agree with me. It makes it extremely difficult to use
>>> PGSQL enums with, for example, the Java Persistence API or O/RMs in
>>> general.
>>> From the sounds of the thread, the guys over at PGSQL have to intention
>>> of
>>> fixing this.
>>>
>>> Now, as for your suggestion. As much as I'd like to accommodate your
>>> need
>>> here, it's simply not feasible. Nearly every single database vendor has
>>> a
>>> different way to cast data types. Some have a CAST function, others a
>>> CAST
>>> keyword, and then PGSQL has that odd double-colon syntax (::). If we were
>>> to
>>> add a casting feature, we would then have to have a sense of database
>>> dialects like an O/RM and learn the syntax of each database, correctly
>>> applying the syntax based on what type of database we're logging to.
>>> This
>>> would be a heavyweight bulky addition to such a simple feature. My
>>> recommendations, in descending order of how much effort it will require:
>>>
>>> 1) Submit a patch to PGSQL's JDBC driver and lobby for its acceptance.
>>> 2) Switch to a database vendor that's less [insert insulting word here]
>>> about its enum casting--like any of the other ones. My favorite is
>>> MySQL.
>>> 3) Stop using a database enum column.
>>>
>>> :-/
>>>
>>> Nick
>>>
>>> [1]
>>> http://www.postgresql.org/message-id/CAPPfruyta0A0xGhG4Zh785sS0_FZ8GczzcjZGXo2yfPhaDxuaA@mail.gmail.com
>>>
>>> On Aug 22, 2013, at 4:12 PM, Peter Rifel wrote:
>>>
>>>> Hello,
>>>>
>>>> I'm wondering if it is at all possible to cast columns to a certain
>>>> type
>>>> (a custom enum) with the JDBCAppender. I have an existing PostgreSQL
>>>> database that I send all warnings and errors to and the log level is
>>>> currently being stored as an enum. When I try and log to the database
>>>> I
>>>> get the following message:
>>>>
>>>> org.postgresql.util.PSQLException: ERROR: column "level" is of type
>>>> log_level but expression is of type character varying Hint: You will
>>>> need
>>>> to rewrite or cast the expression.
>>>>
>>>> Looking through the JDBCDatabaseManager it doesn't appear that this is
>>>> possible; including the cast in the pattern string results in it
>>>> getting
>>>> passed as the value itself rather than the prepared statement. Does
>>>> anyone have any other ideas on how to accomplish this? I wouldn't mind
>>>> making contributions to the source code to help implement this feature
>>>> if
>>>> it is desired.
>>>>
>>>> Thanks,
>>>>
>>>> Peter
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
>> For additional commands, e-mail: log4j-user-help@logging.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> For additional commands, e-mail: log4j-user-help@logging.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
For additional commands, e-mail: log4j-user-help@logging.apache.org
Re: Log4j2: Column casting in JDBCAppender
Posted by Nick Williams <ni...@nicholaswilliams.net>.
Yes, I mean like that. I want to avoid something like that.
N
On Aug 22, 2013, at 4:59 PM, Scott Deboy wrote:
> You mean like:
> http://logging.apache.org/log4j/companions/receivers/apidocs/org/apache/log4j/db/dialect/package-summary.html
>
> Scott
>
> On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
>> IMO, this is a bug in the PGSQL JDBC driver and not something that Log4j can
>> easily (or should) address. For a detailed discussion, see the PGSQL thread
>> about casting text to PGSQL enums [1].
>>
>> The problem lies in PGSQL's type handling. With PGSQL, MySQL, and any other
>> database type that supports enums, you can write a query like this:
>>
>> INSERT INTO myTable (enumColumn) VALUES ('enumValue');
>>
>> And the database engine implicitly casts the text to enum. If you try this
>> out on a PGSQL prompt, it works. However, unlike MySQL and other vendors,
>> PGSQL does NOT allow String parameters in prepared statements to be
>> auto-casted to enums. So the following is valid in all vendors that support
>> enums:
>>
>> statement = connection.prepareStatement("INSERT INTO myTable (enumColumn)
>> VALUES ('enumValue')");
>>
>> But the following, while valid with MySQL and everything else, breaks with
>> PGSQL's JDBC driver:
>>
>> statement = connection.prepareStatement("INSERT INTO myTable (enumColumn)
>> VALUES (?)");
>> statement.setString(1, "enumValue");
>>
>> IMO, this was a stupid decision on PGSQL's part, and from the thread it
>> sounds like others agree with me. It makes it extremely difficult to use
>> PGSQL enums with, for example, the Java Persistence API or O/RMs in general.
>> From the sounds of the thread, the guys over at PGSQL have to intention of
>> fixing this.
>>
>> Now, as for your suggestion. As much as I'd like to accommodate your need
>> here, it's simply not feasible. Nearly every single database vendor has a
>> different way to cast data types. Some have a CAST function, others a CAST
>> keyword, and then PGSQL has that odd double-colon syntax (::). If we were to
>> add a casting feature, we would then have to have a sense of database
>> dialects like an O/RM and learn the syntax of each database, correctly
>> applying the syntax based on what type of database we're logging to. This
>> would be a heavyweight bulky addition to such a simple feature. My
>> recommendations, in descending order of how much effort it will require:
>>
>> 1) Submit a patch to PGSQL's JDBC driver and lobby for its acceptance.
>> 2) Switch to a database vendor that's less [insert insulting word here]
>> about its enum casting--like any of the other ones. My favorite is MySQL.
>> 3) Stop using a database enum column.
>>
>> :-/
>>
>> Nick
>>
>> [1]
>> http://www.postgresql.org/message-id/CAPPfruyta0A0xGhG4Zh785sS0_FZ8GczzcjZGXo2yfPhaDxuaA@mail.gmail.com
>>
>> On Aug 22, 2013, at 4:12 PM, Peter Rifel wrote:
>>
>>> Hello,
>>>
>>> I'm wondering if it is at all possible to cast columns to a certain type
>>> (a custom enum) with the JDBCAppender. I have an existing PostgreSQL
>>> database that I send all warnings and errors to and the log level is
>>> currently being stored as an enum. When I try and log to the database I
>>> get the following message:
>>>
>>> org.postgresql.util.PSQLException: ERROR: column "level" is of type
>>> log_level but expression is of type character varying Hint: You will need
>>> to rewrite or cast the expression.
>>>
>>> Looking through the JDBCDatabaseManager it doesn't appear that this is
>>> possible; including the cast in the pattern string results in it getting
>>> passed as the value itself rather than the prepared statement. Does
>>> anyone have any other ideas on how to accomplish this? I wouldn't mind
>>> making contributions to the source code to help implement this feature if
>>> it is desired.
>>>
>>> Thanks,
>>>
>>> Peter
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
> For additional commands, e-mail: log4j-user-help@logging.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
For additional commands, e-mail: log4j-user-help@logging.apache.org
Re: Log4j2: Column casting in JDBCAppender
Posted by Scott Deboy <sc...@gmail.com>.
You mean like:
http://logging.apache.org/log4j/companions/receivers/apidocs/org/apache/log4j/db/dialect/package-summary.html
Scott
On 8/22/13, Nick Williams <ni...@nicholaswilliams.net> wrote:
> IMO, this is a bug in the PGSQL JDBC driver and not something that Log4j can
> easily (or should) address. For a detailed discussion, see the PGSQL thread
> about casting text to PGSQL enums [1].
>
> The problem lies in PGSQL's type handling. With PGSQL, MySQL, and any other
> database type that supports enums, you can write a query like this:
>
> INSERT INTO myTable (enumColumn) VALUES ('enumValue');
>
> And the database engine implicitly casts the text to enum. If you try this
> out on a PGSQL prompt, it works. However, unlike MySQL and other vendors,
> PGSQL does NOT allow String parameters in prepared statements to be
> auto-casted to enums. So the following is valid in all vendors that support
> enums:
>
> statement = connection.prepareStatement("INSERT INTO myTable (enumColumn)
> VALUES ('enumValue')");
>
> But the following, while valid with MySQL and everything else, breaks with
> PGSQL's JDBC driver:
>
> statement = connection.prepareStatement("INSERT INTO myTable (enumColumn)
> VALUES (?)");
> statement.setString(1, "enumValue");
>
> IMO, this was a stupid decision on PGSQL's part, and from the thread it
> sounds like others agree with me. It makes it extremely difficult to use
> PGSQL enums with, for example, the Java Persistence API or O/RMs in general.
> From the sounds of the thread, the guys over at PGSQL have to intention of
> fixing this.
>
> Now, as for your suggestion. As much as I'd like to accommodate your need
> here, it's simply not feasible. Nearly every single database vendor has a
> different way to cast data types. Some have a CAST function, others a CAST
> keyword, and then PGSQL has that odd double-colon syntax (::). If we were to
> add a casting feature, we would then have to have a sense of database
> dialects like an O/RM and learn the syntax of each database, correctly
> applying the syntax based on what type of database we're logging to. This
> would be a heavyweight bulky addition to such a simple feature. My
> recommendations, in descending order of how much effort it will require:
>
> 1) Submit a patch to PGSQL's JDBC driver and lobby for its acceptance.
> 2) Switch to a database vendor that's less [insert insulting word here]
> about its enum casting--like any of the other ones. My favorite is MySQL.
> 3) Stop using a database enum column.
>
> :-/
>
> Nick
>
> [1]
> http://www.postgresql.org/message-id/CAPPfruyta0A0xGhG4Zh785sS0_FZ8GczzcjZGXo2yfPhaDxuaA@mail.gmail.com
>
> On Aug 22, 2013, at 4:12 PM, Peter Rifel wrote:
>
>> Hello,
>>
>> I'm wondering if it is at all possible to cast columns to a certain type
>> (a custom enum) with the JDBCAppender. I have an existing PostgreSQL
>> database that I send all warnings and errors to and the log level is
>> currently being stored as an enum. When I try and log to the database I
>> get the following message:
>>
>> org.postgresql.util.PSQLException: ERROR: column "level" is of type
>> log_level but expression is of type character varying Hint: You will need
>> to rewrite or cast the expression.
>>
>> Looking through the JDBCDatabaseManager it doesn't appear that this is
>> possible; including the cast in the pattern string results in it getting
>> passed as the value itself rather than the prepared statement. Does
>> anyone have any other ideas on how to accomplish this? I wouldn't mind
>> making contributions to the source code to help implement this feature if
>> it is desired.
>>
>> Thanks,
>>
>> Peter
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: log4j-user-unsubscribe@logging.apache.org
For additional commands, e-mail: log4j-user-help@logging.apache.org
Re: Log4j2: Column casting in JDBCAppender
Posted by Nick Williams <ni...@nicholaswilliams.net>.
IMO, this is a bug in the PGSQL JDBC driver and not something that Log4j can easily (or should) address. For a detailed discussion, see the PGSQL thread about casting text to PGSQL enums [1].
The problem lies in PGSQL's type handling. With PGSQL, MySQL, and any other database type that supports enums, you can write a query like this:
INSERT INTO myTable (enumColumn) VALUES ('enumValue');
And the database engine implicitly casts the text to enum. If you try this out on a PGSQL prompt, it works. However, unlike MySQL and other vendors, PGSQL does NOT allow String parameters in prepared statements to be auto-casted to enums. So the following is valid in all vendors that support enums:
statement = connection.prepareStatement("INSERT INTO myTable (enumColumn) VALUES ('enumValue')");
But the following, while valid with MySQL and everything else, breaks with PGSQL's JDBC driver:
statement = connection.prepareStatement("INSERT INTO myTable (enumColumn) VALUES (?)");
statement.setString(1, "enumValue");
IMO, this was a stupid decision on PGSQL's part, and from the thread it sounds like others agree with me. It makes it extremely difficult to use PGSQL enums with, for example, the Java Persistence API or O/RMs in general. From the sounds of the thread, the guys over at PGSQL have to intention of fixing this.
Now, as for your suggestion. As much as I'd like to accommodate your need here, it's simply not feasible. Nearly every single database vendor has a different way to cast data types. Some have a CAST function, others a CAST keyword, and then PGSQL has that odd double-colon syntax (::). If we were to add a casting feature, we would then have to have a sense of database dialects like an O/RM and learn the syntax of each database, correctly applying the syntax based on what type of database we're logging to. This would be a heavyweight bulky addition to such a simple feature. My recommendations, in descending order of how much effort it will require:
1) Submit a patch to PGSQL's JDBC driver and lobby for its acceptance.
2) Switch to a database vendor that's less [insert insulting word here] about its enum casting--like any of the other ones. My favorite is MySQL.
3) Stop using a database enum column.
:-/
Nick
[1] http://www.postgresql.org/message-id/CAPPfruyta0A0xGhG4Zh785sS0_FZ8GczzcjZGXo2yfPhaDxuaA@mail.gmail.com
On Aug 22, 2013, at 4:12 PM, Peter Rifel wrote:
> Hello,
>
> I'm wondering if it is at all possible to cast columns to a certain type (a custom enum) with the JDBCAppender. I have an existing PostgreSQL database that I send all warnings and errors to and the log level is currently being stored as an enum. When I try and log to the database I get the following message:
>
> org.postgresql.util.PSQLException: ERROR: column "level" is of type log_level but expression is of type character varying Hint: You will need to rewrite or cast the expression.
>
> Looking through the JDBCDatabaseManager it doesn't appear that this is possible; including the cast in the pattern string results in it getting passed as the value itself rather than the prepared statement. Does anyone have any other ideas on how to accomplish this? I wouldn't mind making contributions to the source code to help implement this feature if it is desired.
>
> Thanks,
>
> Peter