search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.42k stars 546 forks source link

Primary key Sequence fields from MS SQL are mapped to bigint #1054

Closed auspex closed 4 years ago

auspex commented 4 years ago 
pgloader version "3.6.1"
compiled with SBCL 1.5.4.debian

No

Yes

load database
     from mssql://geoserver@cpr/cpr_prod
     into postgresql://sahfos_dba:*@docker/cpr_prod

Database contains two tables defined as

CREATE TABLE [dbo].[system_parameter](
    [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [parameter_group_id] [int] NOT NULL,
    [name] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_system_parameter] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)) ON [PRIMARY]

GO

CREATE TABLE [dbo].[system_parameter_group](
    [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_system_parameter_group] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)) ON [PRIMARY]

ALTER TABLE [dbo].[system_parameter]  WITH CHECK ADD  CONSTRAINT [FK_system_parameter_system_parameter_group] FOREIGN KEY([parameter_group_id])
REFERENCES [dbo].[system_parameter_group] ([id])

sahfos_dba@[local] cpr_prod>\d system_parameter
                                           Table "public.system_parameter"
       Column       |           Type           | Collation | Nullable |                   Default                    
--------------------+--------------------------+-----------+----------+----------------------------------------------
 id                 | bigint                   |           | not null | nextval('system_parameter_id_seq'::regclass)
 parameter_group_id | integer                  |           | not null | 
 name               | text                     |           | not null | 
Indexes:
    "idx_72431_pk_system_parameter" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_system_parameter_system_parameter_group" FOREIGN KEY (parameter_group_id) REFERENCES system_parameter_group(id)

sahfos_dba@[local] cpr_prod>\d system_parameter_group
                                          Table "public.system_parameter_group"
      Column      |           Type           | Collation | Nullable |                      Default                       
------------------+--------------------------+-----------+----------+----------------------------------------------------
 id               | bigint                   |           | not null | nextval('system_parameter_group_id_seq'::regclass)
 name             | text                     |           | not null | 
Indexes:
    "idx_72440_pk_system_parameter_group" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "system_parameter" CONSTRAINT "fk_system_parameter_system_parameter_group" FOREIGN KEY (parameter_group_id) REFERENCES system_parameter_group(id)

The Sequence fields (defined as [int] IDENTITY(1,1) in SQL Server) are cast to bigint, but the foreign keys remain int. This doesn't cause a problem when pgloader creates the new tables, but the Dotnet Core Entity Framework throws a fit because the two types don't match.

While I can guarantee Postgresql will be happy by casting all int to bigint, I'm expecting this will cause trouble down the road with my EntityFramework app running on either SQL Server or Postgresql, where it's going to want the types to match.

A SQL Server int should always map to integer, not sometimes to bigint.

dimitri commented 4 years ago

PostgreSQL sequences are always generating bigint numbers. The usual wisdom is to always use bigint as primary keys. pgloader does not implement Fkey graph resolution to match the integer size of all the schema that way. You have two choices:

  1. use a CAST clause to have parameter_group_id created as a bigint
  2. contribute code to pgloader so that it closes over the key graph and is smart about the whole picture
  3. sponsor money to make 2. happen if your company doesn't want to have one of their developer spend time on it
nan0tube commented 3 years ago

Encountered the same issue here.

PostgreSQL sequences are always generating bigint numbers.

@dimitri thanks for the great tool and appreciate the workarounds, but just wanted to point out that sequences can be smallint, integer, or bigint since PostgreSQL 10 (https://www.postgresql.org/docs/10/sql-createsequence.html).