Pgloader: not supported type SYB-MSDATETIMEOFFSET

[bartlibert]

• [x] pgloader --version

pgloader version "3.6.1"
compiled with SBCL 1.4.15.debian  

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

No, as I have a compilation issue (#897)

• [x] did you search for other similar issues?

Yes, there are similar issues, but not for this specific type

• [x] how can I reproduce the bug?
  Unfortunately, I cannot provide access to a test database at the moment.
  The issue occurs when trying to import a database table that has a "datetimeoffset" type.

LOAD DATABASE
    FROM mssql://user:pass@localhost/database
    INTO postgresql:///database

        WITH include drop, create tables, create indexes,
                 foreign keys, reset sequences,
                 create schemas

        MATERIALIZE ALL VIEWS

        ALTER SCHEMA 'dbo' RENAME TO 'public'

        CAST type datetimeoffset to timestamptz

set work_mem to '256MB', maintenance_work_mem to '2048 MB';

• [x] pgloader output you obtain

Note: some information removed for privacy reasons

pgloader version 3.6.1
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
mkdir -p #P"/tmp/pgloader/"
2019-05-23T13:09:30.006000Z NOTICE Starting pgloader, log system is ready.
2019-05-23T13:09:30.014000Z INFO Starting monitor
2019-05-23T13:09:30.018000Z LOG pgloader version "3.6.1"
2019-05-23T13:09:30.056000Z INFO Parsed command: ** hidden **
2019-05-23T13:09:30.082000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://bart@UNIX:5432/database {10069ACB13}>
2019-05-23T13:09:30.082000Z DEBUG SET client_encoding TO 'utf8'
2019-05-23T13:09:30.082000Z DEBUG SET work_mem TO '256MB'
2019-05-23T13:09:30.083000Z DEBUG SET maintenance_work_mem TO '2048 MB'
2019-05-23T13:09:30.083000Z DEBUG SET application_name TO 'pgloader'
2019-05-23T13:09:30.096000Z LOG Migrating from #<MSSQL-CONNECTION mssql://sa@localhost:1433/database {10069AC263}>
2019-05-23T13:09:30.096000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://bart@UNIX:5432/database {10069ACB13}>
Max connections reached, increase value of TDS_MAX_CONN
2019-05-23T13:09:30.159000Z SQL MSSQL: sending query: -- params: dbname
--         table-type-name
--         including
--         filter-list-to-where-clause including
--         excluding
--         filter-list-to-where-clause excluding
  select c.TABLE_SCHEMA,
         c.TABLE_NAME,
         c.COLUMN_NAME,
         c.DATA_TYPE,
         CASE
         WHEN c.COLUMN_DEFAULT LIKE '((%' AND c.COLUMN_DEFAULT LIKE '%))' THEN
             CASE
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'newid()' THEN 'GENERATE_UUID'
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) LIKE 'convert(%varchar%,getdate(),%)' THEN 'today'
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'getdate()' THEN 'CURRENT_TIMESTAMP'
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) LIKE '''%''' THEN SUBSTRING(c.COLUMN_DEFAULT,4,len(c.COLUMN_DEFAULT)-6)
                 ELSE SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4)
             END
         WHEN c.COLUMN_DEFAULT LIKE '(%' AND c.COLUMN_DEFAULT LIKE '%)' THEN
             CASE
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'newid()' THEN 'GENERATE_UUID'
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) LIKE 'convert(%varchar%,getdate(),%)' THEN 'today'
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'getdate()' THEN 'CURRENT_TIMESTAMP'
                 WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) LIKE '''%''' THEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4)
                 ELSE SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2)
             END
         ELSE c.COLUMN_DEFAULT
         END,
         c.IS_NULLABLE,
         COLUMNPROPERTY(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity'),
         c.CHARACTER_MAXIMUM_LENGTH,
         c.NUMERIC_PRECISION,
         c.NUMERIC_PRECISION_RADIX,
         c.NUMERIC_SCALE,
         c.DATETIME_PRECISION,
         c.CHARACTER_SET_NAME,
         c.COLLATION_NAME

    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_CATALOG = 'database'
         and t.TABLE_TYPE = 'BASE TABLE'
         and ((c.table_schema = 'dbo' and c.table_name LIKE 'access'))


order by c.table_schema, c.table_name, c.ordinal_position;
2019-05-23T13:09:30.196000Z SQL MSSQL: sending query: -- params: including
--         filter-list-to-where-clause including
--         excluding
--         filter-list-to-where-clause excluding
    select schema_name(schema_id) as SchemaName,
           o.name as TableName,
           REPLACE(i.name, '.', '_') as IndexName,
           co.[name] as ColumnName,
           i.is_unique,
           i.is_primary_key,
           i.filter_definition

    from sys.indexes i
         join sys.objects o on i.object_id = o.object_id
         join sys.index_columns ic on ic.object_id = i.object_id
             and ic.index_id = i.index_id
         join sys.columns co on co.object_id = i.object_id
             and co.column_id = ic.column_id

   where schema_name(schema_id) not in ('dto', 'sys')
         and ((schema_name(schema_id) = 'dbo' and o.name LIKE 'access'))


order by SchemaName,
         o.[name],
         i.[name],
         ic.is_included_column,
         ic.key_ordinal;
2019-05-23T13:09:30.216000Z SQL MSSQL: sending query: -- params: dbname
--         including
--         filter-list-to-where-clause including
--         excluding
--         filter-list-to-where-clause excluding
   SELECT
           REPLACE(KCU1.CONSTRAINT_NAME, '.', '_') AS 'CONSTRAINT_NAME'
         , KCU1.TABLE_SCHEMA AS 'TABLE_SCHEMA'
         , KCU1.TABLE_NAME AS 'TABLE_NAME'
         , KCU1.COLUMN_NAME AS 'COLUMN_NAME'
         , KCU2.TABLE_SCHEMA AS 'UNIQUE_TABLE_SCHEMA'
         , KCU2.TABLE_NAME AS 'UNIQUE_TABLE_NAME'
         , KCU2.COLUMN_NAME AS 'UNIQUE_COLUMN_NAME'
         , RC.UPDATE_RULE AS 'UPDATE_RULE'
         , RC.DELETE_RULE AS 'DELETE_RULE'

    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
         JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
              ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
                 AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
                 AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
         JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
              ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
                 AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
                 AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

   WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
         AND KCU1.TABLE_CATALOG = 'database'
         AND KCU1.CONSTRAINT_CATALOG = 'database'
         AND KCU1.CONSTRAINT_SCHEMA NOT IN ('dto', 'sys')
         AND KCU1.TABLE_SCHEMA NOT IN ('dto', 'sys')
         AND KCU2.TABLE_SCHEMA NOT IN ('dto', 'sys')

         and ((kcu1.table_schema = 'dbo' and kcu1.table_name LIKE 'access'))


ORDER BY KCU1.CONSTRAINT_NAME, KCU1.ORDINAL_POSITION;
2019-05-23T13:09:30.238000Z INFO Processing source catalogs
2019-05-23T13:09:30.269000Z NOTICE Prepare PostgreSQL database.
2019-05-23T13:09:30.272000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://bart@UNIX:5432/database {}>
2019-05-23T13:09:30.272000Z DEBUG SET client_encoding TO 'utf8'
2019-05-23T13:09:30.272000Z DEBUG SET work_mem TO '256MB'
2019-05-23T13:09:30.272000Z DEBUG SET maintenance_work_mem TO '2048 MB'
2019-05-23T13:09:30.273000Z DEBUG SET application_name TO 'pgloader'
2019-05-23T13:09:30.275000Z DEBUG BEGIN
2019-05-23T13:09:30.314000Z SQL DROP TABLE IF EXISTS public.access_time CASCADE;
2019-05-23T13:09:30.315000Z SQL CREATE TABLE public.access_time
(
  siteid                   text not null,
  level                    smallint not null,
  revision                 int not null,
  changetime               datetimeoffset not null,
  redemption               smallint not null,
  promo                    smallint not null,
  pts                      smallint not null,
  lock                     smallint not null,
  tx                       smallint not null,
  format                   smallint not null,
  check                    smallint not null,
  color                    text not null,
  lastchange               bigint default NULL,
  changeby                 text default NULL,
  action_1                 smallint default NULL,
  action_2                 smallint default NULL
);
2019-05-23T13:09:30.315000Z ERROR Database error 42704: type "datetimeoffset" does not exist
QUERY: CREATE TABLE public.access_time
(
  siteid                   text not null,
  level                    smallint not null,
  revision                 int not null,
  changetime               datetimeoffset not null,
  redemption               smallint not null,
  promo                    smallint not null,
  pts                      smallint not null,
  lock                     smallint not null,
  tx                       smallint not null,
  format                   smallint not null,
  check                    smallint not null,
  color                    text not null,
  lastchange               bigint default NULL,
  changeby                 text default NULL,
  action_1                 smallint default NULL,
  action_2                 smallint default NULL
);
2019-05-23T13:09:30.316000Z FATAL Failed to create the schema, see above.
2019-05-23T13:09:30.319000Z LOG report summary reset
2019-05-23T13:09:30.352000Z INFO Stopping monitor

[dimitri]

Can you try using the following used-defined cast rule in your pgloader command? This might just work, in which case I will add the rule to the default set of casting rules for MS SQL. It if still doesn't work, we might have to hack get-column-sql-expression at https://github.com/dimitri/pgloader/blob/master/src/sources/mssql/mssql-schema.lisp#L165 to enable specific SQL select support for this datatype.

  cast type datetimeoffset to timestamptz

[bartlibert]

I already have this cast in my script (see above). Do I need to specify it somewhere else or in another way?

[stlbucket]

i am getting the same result with pgloader installed via homebrew on mac. i'm not using docker at the moment, but was seeing similar behavior:

2019-06-06T16:13:03.012000-07:00 LOG pgloader version "3.6.1"
2019-06-06T16:13:03.014000-07:00 LOG Data errors in '/private/tmp/pgloader/'
2019-06-06T16:13:03.014000-07:00 LOG Parsing commands from file #P"/Users/dir/pgloader.cmd"
2019-06-06T16:13:03.152000-07:00 LOG Migrating from #<MSSQL-CONNECTION mssql://... {100608A7E3}>
2019-06-06T16:13:03.153000-07:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://... {100608BD43}>
Max connections reached, increase value of TDS_MAX_CONN
2019-06-06T16:13:04.696000-07:00 ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "sysdatetimeoffset()"
QUERY: CREATE TABLE public.company
(
  ...
  addeddate                timestamptz not null default 'sysdatetimeoffset()',
  ...
);
2019-06-06T16:13:04.696000-07:00 FATAL Failed to create the schema, see above.
Max connections reached, increase value of TDS_MAX_CONN
2019-06-06T16:13:04.944000-07:00 LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
  fetch meta data          0        218                     1.383s
   Create Schemas          0          0                     0.004s
 Create SQL Types          0          0                     0.008s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

[stlbucket]

my command file is the same as @bartlibert's

[dimitri]

Hi @stlbucket ; it seems the cast rule has been used by your instance of pgloader this time, and kept the default value. You can have a cast rule that drops the default value that way:

  cast type datetimeoffset to timestamptz drop default

[dimitri]

Note: as soon as we figure out the proper way to support Datetimeoffset my intention is to patch pgloader to make it the default processing, including processing the default value correctly and all. The testing we are doing here is necessary because I don't have a test environment with MS SQL handy.

[pkan522]

I am also getting this error.

`...
debugger invoked on a SIMPLE-ERROR in thread

not supported type SYB-MSDATETIMEOFFSET
...`

[pkan522]

Something like this might work in "get-column-sql-expression" in mssql-schema.lisp

(:datetimeoffset (format nil "select left([~a], 19) + substring([~a], 29, 3)" name name))

[dimitri]

Ok, can you folks give me links to the proper documentation for the datatype in MS SQL, so that we confirm it looks and behave like a timestamp. Then if you can hack around the pgloader code and try things out and see what happens that's be great. The 3 places to edit are going to be around there, I suppose:

• https://github.com/dimitri/pgloader/blob/master/src/monkey/mssql.lisp#L98
• https://github.com/dimitri/pgloader/blob/master/src/sources/mssql/mssql-cast-rules.lisp#L65
• https://github.com/dimitri/pgloader/blob/master/src/sources/mssql/mssql-schema.lisp#L166

[rudi-bruchez]

I might have corrected this problem, with changes in the above source files. I'm importing a mssql db and if everything works I'll submit a pull request.

Basically :

added
(:source (:type "datetimeoffset") :target (:type "timestamptz"))
in mssql-cast-rules.lisp

added

:syb-msdatetime2
               :syb-msdatetimeoffset)
              (with-foreign-pointer (%buf +numeric-buf-sz+)

in mssql.lisp

and
(:datetimeoffset (format nil "convert(varchar(35), [~a], 127)" name))
in mssql-schema.lisp

Beware of CONVERT(varchar...). In mssql, the varchar default size is 30 in a cast.
Good practice is to dimension it explicitely. Not enough here for datetimeoffset (cast to 32 chars) so the varchar size needs to be explicitely set.

Btw, the import is slow and the SELECT seen for SQL Server is waiting on ACK feedbacks from pgloader to send next packets. Here there somewhere where we can see the loop, or maybe I should check the freetds configuration if I can improve that.
How difficult would it be to switch to Microsoft ODBC driver (there is a Linux build) ?

[dimitri]

Thanks a lot for your work here @rudi-bruchez ! I have now merged your PR to improve our MS SQL support, that's awesome!

About switching to an ODBC driver, I'm favorable to it yeah. When I picked a driver for MS SQL I had memories of ODBC being generic and pretty slow, and avoided it. I found FreeTDS and happily built my support using it. Now, FreeTDS is not supported in windows so we can't build pgloader in windows with MS SQL support, that feels... well, suboptimal. And I have read in MS documentation that ODBC is advised and known faster than other options.

So yeah, I would review and help cook a PR that switched from FreeTDS to ODBC for the MS SQL driver, for sure! Feel free to get started!

[rudi-bruchez]

So yeah, I would review and help cook a PR that switched from FreeTDS to ODBC for the MS SQL driver, for sure! Feel free to get started!

I'm working again on this project ;) If I have the energy, I might look into it, yes. But one problem is that I don't think the Microsoft ODBC driver is open source, I'll check. That would be great also because it would allow to add specific settings in the connection string like access to secondary servers when using SQL Server high availability. So looking into it during the week.

[rudi-bruchez]

Can you point me where, in the source code, you are using FreeTDS? I'm not all too familiar with LISP but should be able to figure out how to make the changes.

[dimitri]

Hi @rudi-bruchez ; we use the TDS driver through our integration with the cl-mssql Common Lisp driver available from https://github.com/archimag/cl-mssql, as seen in our ASD file at https://github.com/dimitri/pgloader/blob/master/pgloader.asd#L34. You can change that to use a Common Lisp implementation for ODBC.

The mapping code from the cl-mssql driver to pgloader is done in https://github.com/dimitri/pgloader/blob/master/src/sources/mssql/mssql-connection.lisp and then in https://github.com/dimitri/pgloader/blob/master/src/sources/mssql/mssql-schema.lisp#L163 there's a little more code that depend on cl-mssql that we can see.

Maybe @semenmiroshnichenko would be interested in this effort, as he just had his first PR merged in pgloader and he's been working on MS SQL support.