Table type annotation

[beikov]

@loiclefevre suggested today to that we support some of the new table types of Oracle, and I think that some sort of @TableType annotation might be interesting.

I would agree, because we have numerous ones since 18c: sharded, reference, memoptimized for read, for write, blockchain, etc...

The most interesting table type would be "immutable" which allows only insert and select.

Maybe this is too much of a DDL aspect and this should be left to schema management tools, but I wanted to drop this idea here so I don't forget it.

[beikov]

I'd like to revive this discussion a bit as I recently wanted to try out if the PostgreSQL unlogged table type could be used to improve testsuite performance. Another thing that comes to mind is the fact that many databases have the concept of column vs. row tables, one of them being HANA for which we currently ship 2 dialects to handle this difference in DDL generation.

There are also a couple of Jiras that request this or something similar:

• https://hibernate.atlassian.net/browse/HHH-1083
• https://hibernate.atlassian.net/browse/HHH-2231
• https://hibernate.atlassian.net/browse/HHH-9465

Ideally, this would not only be an annotation, but we'd also add a configuration property to configure the default table type. For the purpose of my PostgreSQL testing I'd like to introduce the configuration property hibernate.dialect.default_table_type analogous to our existing configuration property hibernate.dialect.storage_engine.

Comments, concerns, ideas?

[gavinking]

@beikov I don't think we need this because we already added a feature very much like it to the JPA @Table annotation in JPA 3.2:

https://github.com/jakartaee/persistence/pull/485/files#diff-a9d33d0441522587a8403d7f087749c112f7693e185e33dbc24cc9968d9bd880R107

[sebersole]

And we'd immediately need something similar to DialectOverride for these for app portability.

Well, that or boil these down to a few we want to support (immutable, unlogged) which we apply or not based on whether the Dialect in use supports them.

[gavinking]

Wdyt about adding that to JPA as well though?

I mean I guess it comes down to how important and useful and how "universal" is the notion of a table type.

Clearly it's at least somewhat useful or we would not be having this conversation.

On the other hand it doesn't seem to be a concept which is exists in every database. Looks like we're talking about 2 or 3 databases at most.

[beikov]

I don't mind trying to come up with an abstraction for one thing or the other, but this escape hatch is useful nonetheless. The following table types are what are possible AFAIK:

• immutable
• unlogged
• row
• column
• temporary?

[gavinking]

• immutable

• unlogged

• row

• column

• temporary?

Apart from temporary is any one of those meaningful on more than one RDBMS?

[beikov]

Here is a list of DB documentations that have different table types:

• Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6
• PostgreSQL: https://www.postgresql.org/docs/current/sql-createtable.html
• DB2: https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-table-external
• HANA: https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-sql-reference-guide/create-table-statement-data-definition
• MySQL: https://dev.mysql.com/doc/refman/8.0/en/create-table.html
• MariaDB: https://mariadb.com/kb/en/create-table/
• H2: https://www.h2database.com/html/commands.html#create_table

Just to add to the list, I see that DB2 has external tables for mapping external data, which is a concept that multiple DBs have. In PostgreSQL for example it's called foreign tables.

A few databases have this concept of a "non-persistent" table which usually means in-memory or no duration guarantee. That roughly translates to "doesn't survive a database server restart", but not sure if it's worth abstracting, though I'm not against adding a @NonPersistent annotation to default the table type to the DBMS specific value.

I think that it is worth adding an abstraction for temporary tables as the correct usage with transactions might involve prepare and cleanup actions (as can be seen in our multi-table mutation code), but apart from that, I think the other table types are pretty much unique to the respective database.