dimitri
MS SQL(9) 2005 To Postgresql 9.6 Migration
Dear Dimitri, I try to convert from SQL server 9 to postgesql9.6 but it fails because sqlserver catalog sys.indexes doesn't have column filter_definition, it fails even if i set in freetds.conf tds version = 7.2 :
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 'mytable'))
order by SchemaName,
o.[name],
i.[name],
ic.is_included_column,
ic.key_ordinal;
Thanks
Best course of action for you now would be to fix the query in the pgloader source directory (it's a SQL file at https://github.com/dimitri/pgloader/blob/master/src/sources/mssql/sql/list-all-indexes.sql and recompile, then use your own version.
We could add support for MS SQL variants then based on your work, where depending on the version of MS SQL Server we are connected to we would use different catalog queries. We have the support for that for PostgreSQL already. Before we go there though we need to have a query that works in your case, and then to figure out what the catalog tables look like depending on the MS SQL version.
For now, it's your turn to hack the SQL query until it works.
NULL seems to work:
diff --git a/src/sources/mssql/sql/list-all-indexes.sql b/src/sources/mssql/sql/list-all-indexes.sql
index 4278032..d68f196 100644
--- a/src/sources/mssql/sql/list-all-indexes.sql
+++ b/src/sources/mssql/sql/list-all-indexes.sql
@@ -8,7 +8,7 @@
co.[name] as ColumnName,
i.is_unique,
i.is_primary_key,
- i.filter_definition
+ null as filter_definition
...
rm build/bin/pgloader
make
# pgmig4 has WITH SCHEMA ONLY
build/bin/pgloader pgmig4
...
Index Build Completion 0 42 0.929s
Create Indexes 0 42 3.202s
Reset Sequences 0 22 0.074s
Primary Keys 0 17 0.029s
...
ps: pgloader version "3.6.6b111ba" compiled with SBCL 2.0.2.176-d185355bb
pps: warning, WITH SCHEMA ONLY emptied all my tables
ppps: I see dozens of times, but it seems harmless: "Max connections reached, increase value of TDS_MAX_CONN"
Could you write a SQL CASE expression that returns i.filter_definition in recent version of SQL Server and NULL with version 2005 or before? In Postgres I would use current_setting('server_version_num')::int < 90499 or something like that for 9.4 (server_version_num would be 090401 for 9.4.1 and 100010 for 10.10).
Given such a CASE expression it would be easy to fix pgloader to support SQL Server versions with or without support for index filter definition from the same query...
Something like that may do the trick ?
if(COL_LENGTH('sys.indexes', 'filter_definition') IS NOT NULL)
begin
exec ('
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 (~{~a~^ or ~})~]
~:[~*~;and (~{~a~^ and ~})~]
order by SchemaName,
o.[name],
i.[name],
ic.is_included_column,
ic.key_ordinal
')
end
else
begin
exec ('
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,
null as 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 (~{~a~^ or ~})~]
~:[~*~;and (~{~a~^ and ~})~]
order by SchemaName,
o.[name],
i.[name],
ic.is_included_column,
ic.key_ordinal
')
end
;
Well that's T-SQL rather than “just” a query, I was thinking it might be possible to avoid having to sort to non-SQL logic there (and EXEC)... so I will cook a patch for doing variant support for MS SQL instead... time allowing.
The client (your Common Lisp program) shouldn’t care, I think, as long as the result set has the same columns and datatypes? I’ve used dynamic SQL many times with no problem.
On Apr 11, 2020, at 9:27 AM, Dimitri Fontaine [email protected] wrote:
Well that's T-SQL rather than “just” a query, I was thinking it might be possible to avoid having to sort to non-SQL logic there (and EXEC)... so I will cook a patch for doing variant support for MS SQL instead... time allowing.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/dimitri/pgloader/issues/1093#issuecomment-612458232, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA4KJ52SLMF4MPTQPIOP5STRMCLA3ANCNFSM4LA7OJ7Q.
(Oh it’s probably your templating logic and need to double quotes-within-quotes within the template text, which is problematic)
On Apr 11, 2020, at 9:32 AM, Aaron West [email protected] wrote:
The client (your Common Lisp program) shouldn’t care, I think, as long as the result set has the same columns and datatypes? I’ve used dynamic SQL many times with no problem.
On Apr 11, 2020, at 9:27 AM, Dimitri Fontaine <[email protected] mailto:[email protected]> wrote:
Well that's T-SQL rather than “just” a query, I was thinking it might be possible to avoid having to sort to non-SQL logic there (and EXEC)... so I will cook a patch for doing variant support for MS SQL instead... time allowing.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/dimitri/pgloader/issues/1093#issuecomment-612458232, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA4KJ52SLMF4MPTQPIOP5STRMCLA3ANCNFSM4LA7OJ7Q.
Hello, I have this error and just add parameter create no indexes. Then, if needed, create index manually.
