Good, if you're sure that the returned type is 1111 I can add it to the translate table. I think it would be CHAR(36) as UUID have always the same size.

Yes, when I manually map to char(36) it comes back blank. I haven't had a chance to build an external use case, but it might possibly be an issue if select of uuid is via prepared statement. I have submitted a case on their driver, but if you have a test env already, maybe it's something connect specific?

Indeed, and I am working on it. I already can automatically specify UUID columns as CHAR(36) when using discovery and read such columns; remains to handle the write operations.

However, as you have customer having tables of UUID type, let's see what can be done waiting to this issue fix. Discovery fails and manually declaring the column as CHAR(36) does work either. The reason why is that the UUID data type is specific to PostgreSQL but is not a SQL type nor a JDBC type.

Therefore, these columns cannot be handled and must be removed from the CONNECT jdbc table. When using discovery, this is done by executing:

SET connect_type_conv=SKIP;

It is Ok if these columns are not used in current operations. However, it may be necessary to handle them.

Let us suppose a PostgreSQL table was created by:

CREATE TABLE testuuid (

id UUID  DEFAULT uuid_generate_v4(),

msg TEXT);

For write operations, this can be done externally to MariaDB using psql or pgAdmin, but also from MariaDB by creating a table allowing to execute SQL statements. For instance:

create table crpg (

command varchar(128) not null,

number int(5) not null flag=1,

message varchar(255) flag=2)

engine=connect table_type=jdbc

connection='jdbc:postgresql:dbname?user=username&password=userpwd'

option_list='Execsrc=1';

Then you can have PostgreSQL execute a command using PostgreSQL syntax, for instance:

select * from crpg where command = 'delete from testuuid where id is null';

This replies for instance:

However, to query such a table, you must force PostgreSQL to return the string representation of the UUID column. This can be done by creating a table specifying the PostgreSQL query using the SRCDEF option. For instance:

create or replace table tuuid (

  `id` char(36) DEFAULT NULL,

  `msg` varchar(8192) DEFAULT NULL

) engine=connect table_type=jdbc

connection='jdbc:postgresql:dbname?user=username&password=userpwd'

srcdef="select id::varchar, msg from testuuid where id::varchar like '%f3%'";

Then

select * from tuuid;

replies:

This oblige to create a table for each query or use the same one altered by ALTER TABLE.

I tried to automate it with a procedure but unsuccessfully.

Note: In the last CREATE TABLE discovery cannot be used and the columns must be defined explicitly because PosgreSQL returns a type of VARCHAR(2147483647), which is invalid for MariaDB. Unfortunately these column definitions must be updated to mach the SRCDEF query result set.

Hope a fix will be available soon.

I think this shouldn't be this hard.
You are supposed to be able to simply setString() and getString() for uuid according to the postgres jdbc driver guys. You can probably find my bug report posted there and add to it. As soon as I get to a real computer I'll add that link here.

Here is the link to my issue on the Postgresql jdbc driver uuid problem:

https://github.com/pgjdbc/pgjdbc/issues/1133

Indeed, getString doesn't work (it returns a null string).

But I could make it to work by getObject().toString.

However it is more complex in the other way. PostgreSQL does not accept a String to create a UUID.

I'll try the way indicated in:
http://crafted-software.blogspot.fr/2013/03/uuid-values-from-jdbc-to-postgres.html

I think if you post on the link above, they intend that getString() should simply return char36 and you should simply be able to setString as a char to set it - anything else is a driver bug in my view. If you post, they will fix it I think.

I can confirm that using the PostgreSQL JDBC driver 42.2.1 getString returns null for a UUID column and that trying to set it by setString fails with a message saying something like:

org.postgresql.util.PSQLException: ERROR:column "id" is of type uuid but the expression is of type character varying

  hint: You should rewrite the expression or apply to it a transformation of type.

(translated from French)

This is bad because, even it is easy to get the UUID value by getObject and toString and to set it by setObject of a UUID Java object, it requires CONNECT to be updated.

Indeed, when reading, the remote column type can be retrieved and when it is 1111 a special function must be called. However, the local column type being CHAR(36) or VARCHAR(36), CONNECT has to get the type of the remote column to know if it must call a specialized function. This is not trivial and could be avoided if the JDBC driver was fixed.

Perhaps you should report this on the PostgreSQL JDBC driver site so this could be fixed. If so, just specying the column as CHAR(36) or VARCHAR(36) will work, even with older MariaDB version (just discovery won't, unless SKIP is specified)

Can you please post that to the thread listed above for postgres jdbc driver bugs?

Well, I tried simply declaring the UUID as char(36) or varchar(36) and then setting the postgresl url parameter to stringType=unspecified but I am still getting blank UUIDs and no error message.

I would be happy at this stage if CONNECT could read uuid if I manually specify them as char(36) or varchar(36). Is this reasonable without a driver change, or is it too cumbersome?

If I understood their last post correctly, it seems like connect would need no change to read a UUID column...so I guess I don't understnd.
Maybe a hack is for CONNECT to just see if driver type = postgresql and type=OTHER (i.e. 1111) and remote type=uuid then that would be a very specific indication that getObject() and convert the uuid to string. They are mapping other stuff to 1111 so checking the remote type (or whatever it is called ... shows in the returned column info).

Strangely enough, when I call the same JDBCInterface functions from a Java test client, at least reading UUID with getString works allright. But when the same functions is called from CONNECT via JNI, it returns a null string.

Anyhow, I have made a fix in CONNECT that I will publish by the time I thoroughly test it.

UUID will be translated to CHAR(36) when column definitions are set using discovery. Locally a PostgreSQL UUID column will be handled like a CHAR or VARCHAR column. Example:

Using the PostgreSQL table testuuid in the text database:

                     Table « public.testuuid »

 Colonne | Type | Collationnement | NULL-able |     Par défaut

---------+------+-----------------+-----------+--------------------

 id      | uuid |                 |           | uuid_generate_v4()

 msg     | text |                 |           |

Its column definitions can by queried by:

create or replace table juuidcol engine=connect table_type=JDBC tabname=testuuid catfunc=columns

connection='jdbc:postgresql:test?user=postgres&password=pwd';

select table_name "Table", column_name "Column", data_type "Type", type_name "Name", column_size "Size" from juuidcol;

This returns:

Note: PostgreSQL, when a column size is undefined, returns 2147483647, which is not acceptable for MariaDB. CONNECT change it to the value of the connect_conv_size session variable. Also, for TEXT columns the data type returned is 12 (SQL_VARCHAR) instead of -1 the SQL_TEXT value.

Accessing this table via JDBC by:

CREATE TABLE juuid ENGINE=connect TABLE_TYPE=JDBC TABNAME=testuuid

CONNECTION='jdbc:postgresql:test?user=postgres&password=pwd';

it will be created as:

CREATE TABLE `juuid` (

  `id` char(36) DEFAULT NULL,

  `msg` varchar(8192) DEFAULT NULL

) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:postgresql:test?user=postgres&password=pwd' `TABLE_TYPE`='JDBC' `TABNAME`='testuuid';

Note: 8192 being here the connect_conv_size value.
Let's populate it:

insert into juuid(msg) values('First');

insert into juuid(msg) values('Second');

select * from juuid;

Here the id column values come from the DEFAULT of the PostgreSQL column that was specified as uuid_generate_v4().
It can be set from MariaDB. For instance:

insert into juuid values('2f835fb8-73b0-42f3-a1d3-8a532b38feca','inserted');

insert into juuid values(NULL,'null');

insert into juuid values('','random');

select * from juuid;

The first insert specifies a valid UUID character representation. The second one set it to NULL. The third one (a void string) generates a Java random UUID.
UPDATE command obeys the same specification.

These commands both work:

select * from juuid where id = '2f835fb8-73b0-42f3-a1d3-8a532b38feca';

delete from juuid where id = '2f835fb8-73b0-42f3-a1d3-8a532b38feca';

However, this one fails:

select * from juuid where id like '%4f%';

Saying:

1296: Got error 174 'ExecuteQuery: org.postgresql.util.PSQLException:

ERROR: operator does not exist : uuid ~~ unknown

hint: no operator correspond to the data name and to the argument types.

This because CONNECT cond_push feature added the WHERE clause to the query sent to PostgreSQL:

SELECT id, msg FROM testuuid WHERE id LIKE '%4f%'

and the LIKE operator does not apply to UUID in PostgreSQL.
A new session variable was added to CONNECT: connect_cond_push. It permit to specify if cond_push is enable or not for CONNECT and defaults to 1 (enabled). In this case, you can execute:

set connect_cond_push=0;

Doing so, the where clause will be executed by MariaDB only and the query will not fail anymore.