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_fz8gczzcjzgxo2yfphadx...@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
