Mssql: IDENTITY columns not detected in tables outside the default schema.

[nathanael-ruf]

I noticed this while migrating a mssql databse with many custom schemas to Postgres. The resulting columns in Postgres weren't SERIAL.

• pgloader --version

  3.6.9 (this docker image because the db is on Azure), but I think I already know the cause which is still an issue in latest master.

• did you test a fresh compile from the source tree?

  No (first try failed because I'm on ARM), but as mentioned above can reproduce by running the query that is in master.

• did you search for other similar issues?

• how can I reproduce the bug?

  1. Set up a mssql instance (e.g. using docker: docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Secret123" -p 1433:1433 --name repro-mssql -d mcr.microsoft.com/mssql/server:2019-latest)
  2. Run these queries:

  CREATE SCHEMA pg_loader_test;

  CREATE TABLE pg_loader_test.MyTable (
      id INT IDENTITY(1,1) PRIMARY KEY
  );

  3. Run the (simplified) "read" query from

     pgloader/src/sources/mssql/sql/list-all-columns.sql

     Line 33 in 2079646

     COLUMNPROPERTY(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity'),

  select c.TABLE_SCHEMA,
     c.TABLE_NAME,
     c.COLUMN_NAME,
     c.DATA_TYPE,
     c.IS_NULLABLE,
     COLUMNPROPERTY(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS IsIdentity
  from INFORMATION_SCHEMA.COLUMNS c
      join INFORMATION_SCHEMA.TABLES t
          on c.TABLE_SCHEMA = t.TABLE_SCHEMA
          and c.TABLE_NAME = t.TABLE_NAME
  where c.TABLE_SCHEMA = 'pg_loader_test'

=> IsIdentity is NULL, but expected is 1.

Replacing the selection with COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS IsIdentity seems to work as expected.

[nathanael-ruf]

I forked https://github.com/jahangiranwari/pgloader and fixed the bug. All my columns are now serial as expected.