Schema validation not working with NpgSql v5

[MichalUssuri]

Database schema auto validation is not working after the update of NpgSql to version 5.0.0.
(Enabled e.g by: properties.SchemaAction = SchemaAutoAction.Validate;)
The validation always fails with errors like:

Wrong column type in test.systems for column ID. Found: **integer**, Expected **int4**; 
Wrong column type in test.systems for column Name. Found: **character varying**, Expected **varchar(30)**;
...

Exception stack:

Received unexpected error: Schema validation failed: see list of validation errors.
   at NHibernate.Cfg.Configuration.ValidateSchema(Dialect dialect, IDatabaseMetadata databaseMetadata)
   at NHibernate.Tool.hbm2ddl.SchemaValidator.Validate()
   at NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners)
   at NHibernate.Cfg.Configuration.BuildSessionFactory()
   at NHibernate.NetCore.ServiceCollectionExtensions.<>c.<AddHibernate>b__5_1(IServiceProvider provider)

Problem description:
The code in Table.cs Table.ValidateColumns
https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Mapping/Table.cs
is comparing column types:

bool typesMatch = column.GetSqlType(dialect, mapping).StartsWith(columnInfo.TypeName, StringComparison.OrdinalIgnoreCase);

The columnInfo.TypeName is getting column type from NpgSql NpgsqlSchema.GetColumns
https://github.com/npgsql/npgsql/blob/main/src/Npgsql/NpgsqlSchema.cs
where the value is taken from data_type column from SQL query:

SELECT
    table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, is_nullable,
    udt_name::regtype::text AS data_type, character_maximum_length, character_octet_length, numeric_precision,
    numeric_precision_radix, numeric_scale, datetime_precision, character_set_catalog, character_set_schema,
    character_set_name, collation_catalog
  FROM information_schema.columns");

Unfortunately in version 5.0.0, there were npgsql/npgsql#3012, which changed the returned value from udt_name AS data_type to udt_name::regtype::text AS data_type.
npgsql/npgsql@9036668#diff-9e5577d0cfb25bc8279488c960f8dc2427d580f90156386cda9d14953caafd1d
This change caused, that "user-facing type names" are now returned from the database.

Here are some example results returned from SQL query:

SELECT udt_name AS old_data_type, udt_name::regtype::text AS new_data_type
  FROM information_schema.columns

old_data_type	new_data_type
int8          	bigint
int4          	integer
char          	character
varchar       	character varying
bool          	boolean

Please, fix this issue.

[hazzik]

Duplicate of #1895

[fredericDelaporte]

For me it is not the same than #1895. Validation was mostly working with Npgsql prior to its v5, and #1895 is about some of the few cases it did not. But validation with Npgsql does mostly not work anymore from its v5 version, due to a large change in the retrieved types naming.

It may still be closed, but more for a reason like external issue, if such a driver behavior is not something NHibernate is meant to take into account.

[hazzik]

@fredericDelaporte but the reason is exactly same. This way or another these tickets are duplicates. Maybe we should close the other one as a duplicate?

[fredericDelaporte]

I doubt we will fix #1895 (with Npgsql < v5) if we only work on fixing #2876, and conversely, we will likely not fix #2876 if we only work on fixing #1895.

[hazzik]

Ok, going to fix separately to #1895