Problems with linked table

[Gerhard Duennebeil]

Hello,

I am using H2 2.1.214 and I am using the "CREATE LINKED TABLE" feature to connect to a postgres (postgres 13) database.

Not sure whether this is of importance but in this specal case I am linking to a view on the postgres server which is augmented with an "instead of" trigger.

Everything seems fine when reading the linked tables but when I try to write I get the error message back, that the column mentioned in the update is not existing.

--> update reloadflag_disabled set rf=true;

2022-10-13 16:19:29 jdbc[4]:
/**/stat1.execute("update reloadflag_disabled set rf=true");
2022-10-13 16:19:29 jdbc[4]: Table      :     potential plan item cost 2.101 index PUBLIC.""
2022-10-13 16:19:29 table: RELOADFLAG_DISABLED:
SELECT * FROM RELOADFLAG_DISABLED T;
2022-10-13 16:19:29 table: RELOADFLAG_DISABLED:
DELETE FROM RELOADFLAG_DISABLED WHERE "RF"=?  {1: FALSE};
2022-10-13 16:19:29 table: RELOADFLAG_DISABLED:
DELETE FROM RELOADFLAG_DISABLED WHERE "RF"=?  {1: FALSE};
2022-10-13 16:19:29 table: RELOADFLAG_DISABLED:
DELETE FROM RELOADFLAG_DISABLED WHERE "RF"=?  {1: FALSE};
2022-10-13 16:19:29 jdbc[4]: exception
org.h2.jdbc.JdbcSQLNonTransientException: Fehler beim Zugriff auf eine verknüpfte Tabelle mit SQL Befehl "DELETE FROM RELOADFLAG_DISABLED WHERE ""RF""=? ", Grund: "org.postgresql.util.PSQLException: ERROR: column ""RF"" does not exist\000a  Position: 39"
Error accessing linked table with SQL statement "DELETE FROM RELOADFLAG_DISABLED WHERE ""RF""=? ", cause: "org.postgresql.util.PSQLException: ERROR: column ""RF"" does not exist\000a  Position: 39"; SQL statement:

As far as I understand the trace output, H2 is quoting the column name and sending it in upper case letters. So postgres' reaction is in so far correct.

I tried modifiying the connection by adding ;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE.

This changes the error message in a way indicating that h2 now has internal problems identifiying the column.

2022-10-13 16:23:08 jdbc[4]:
/**/stat1.execute("update reloadflag_disabled set rf=true");
2022-10-13 16:23:08 jdbc[4]: exception
org.h2.jdbc.JdbcSQLSyntaxErrorException: Feld "rf" nicht gefunden
Column "rf" not found; SQL statement:
update reloadflag_disabled set rf=true [42122-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.table.Table.getColumn(Table.java:749)
at org.h2.command.Parser.readTableColumn(Parser.java:1084)
at org.h2.command.Parser.readUpdateSetClause(Parser.java:1151)
at org.h2.command.Parser.parseUpdate(Parser.java:1120)
at org.h2.command.Parser.parsePrepared(Parser.java:858)
at org.h2.command.Parser.parse(Parser.java:689)
at org.h2.command.Parser.parse(Parser.java:661)
at org.h2.command.Parser.prepareCommand(Parser.java:569)

Is there any known solution and/or workaround?

Best regards

Gerhard

[Noel Grandin]

Unfortunately that smells like the postgresql database driver is returning bad metadata,

because it should be returning column names with the correct case from the SELECT.

But you'd have to debug way down to be sure.

[Gerhard Duennebeil]

Well, I did some heavy debugging :-)

As a reminder: All this applies to the following artifact and it's associated source code from the maven repo:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
     <version>2.1.214</version>
</dependency>

I found the code which sets up the connection (org.h2.table.TableLink).

Line 123ff has a routine "private void readMetaData() throws SQLException {..."

Within the routine at line 199 a routine is called "n = convertColumnName(n);"

Prior to the call the variable n holds the name of the column as provided by the postgres jdbc driver, which is lower case.

After the call n holds the same name but now in upper case.

Not sure, whether this is the intended behavior due to some requirements of H2.

But at least it looks suspicious to me.

Anyone who has more insight on the intentions of these code lines who can advise?

BR

Gerhard

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAFYHVnUxR2JFq-RTMWoxhQKuiQOtG_-MFwXXHhQrJ-W_uLYnQw%40mail.gmail.com.

[Noel Grandin]

Firstly, let me congratulate you some very good debugging!

On Fri, 14 Oct 2022 at 08:05, Gerhard Duennebeil <gerhard.d...@gmail.com> wrote:

Within the routine at line 199 a routine is called "n = convertColumnName(n);"

Can you debug into that convertColumnName and see which of the 4 if branches it is hitting?

and what values the

    storesMixedCase

    storesLowerCase

    storesMixedCaseQuoted

    supportsMixedCaseIdentifiers

fields have ?

It is possible we may have to add another special case, to make Postgresql happy.

[Gerhard Duennebeil]

Hello,

it hits this case:
        } else if ((storesMixedCase || storesLowerCase) &&
                columnName.equals(StringUtils.toLowerEnglish(columnName))) {
            columnName = StringUtils.toUpperEnglish(columnName);

- storesMixedCase is false

- storesLowerCase is true

- column name is "rf" so "rf".equals( StringUtils.toLowerEnglish("rf"))

I further traced the problem down to the statement which really fails ("update rfd set rf=false").

For some reason that I need to find out separately the variable emitUpdates is false so the code tries to execute a delete followed by an insert.

Building the delete ("DELETE FROM RFD where "RF"=?) inserts the name of the column (LinkedIndex.java, Line 217) using a routine named "addColumnName".

This routine fetches the name of the column ( LinkedIndex.java, Line 136) which is now upper case.

I applied a hot patch there, converting the name to lower and everything works.

Looks we have the smoking gun here. It only needs a better way to fix that problem than my hot patch.

Maybe it's a bit naive, but wouldn't it be possible to store the original name beside the local name and reflect that original name back to the linked database?

That might make a lot of complicated logic unnecessary.

BR

Gerhard

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAFYHVnVUyBa9x4DhkZXBH1a00n6Dx9qrZNHxmN97yc0bAk7wUw%40mail.gmail.com.

[Ritu Jain]

Hi

H2 console is not found, request is refused in the browser

Using 

Http://localhost:8080/h2-console

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAFYHVnVUyBa9x4DhkZXBH1a00n6Dx9qrZNHxmN97yc0bAk7wUw%40mail.gmail.com.

[Noel Grandin]

On 10/14/2022 9:28 AM, Gerhard Duennebeil wrote:
>
> Maybe it's a bit naive, but wouldn't it be possible to store the original name beside the local name and reflect that
> original name back to the linked database?
>

Oh, we wish, that would be so awesome.

The case-sensitivity of identifiers in SQL is a horrible, terrible, no-good mess.

So..... when we ask the Postgresql JDBC driver what the column name is, we receive "rf".

Now, unfortunately, we need to know:
Is this the "one true name" of the column?
Or is it just a representation of an identifier that is case-insensitive, for which any of 'RF' 'rf' 'Rf' 'rF' is
acceptable ?

And....... we don't know, and there is insufficient information to be sure. So we have to guess, and in your case, we
guess wrong.

The simplest fix, is for you to change your query to quote 'rf' at source i.e. to write

update reloadflag_disabled set 'rf'=true

The longer, nastier fix, would be that in TableLink#readMetaData we would have add extra logic to detect the ambiguous
case, and issue some test queries to find out which of the cases we are dealing with.

If you want to spin up a patch/PR to add the logic, feel free, but I'll understand if you're not interested :-)