Exception on dotnet ef database update

Exception on dotnet ef database update

[roji]

I don't think it's going to be possible to embed COPY FROM STDIN inside an SQL script file like that - although I'll investigate a little. On the other hand I don't think it would offer any performance advantages over simple inserting with multiple values, the latter would probably even be safer:

INSERT INTO "City" ("Id", "DepartementId", "Insee", "Line5", "Name", "Population", "PostalCode", "PostalName") VALUES
(1, '01', '001', 'Abergement-Clémenciat', '825 01400', 'L ABERGEMENT CLEMENCIAT'),
(2, '01', '002', 'Abergement-de-Varey', '204 01640', 'L ABERGEMENT DE VAREY');

Using COPY programmatically is also a possibility as you say, with either BeginTextImport (or BeginBinaryImport, depending on whether on what you have on the client side). But that takes things out of the migration script.

I found the error, when i execute the database update, i have a fixture file which execute after the database creation. This c# file find the sql files to insert the datas. Only now i have 2 new files which contains an instruction COPY instead of INSERT.
example

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- Data for Name: City; Type: TABLE DATA; Schema: public; Owner: jerome
--

COPY "City" ("Id", "DepartementId", "Insee", "Line5", "Name", "Population", "PostalCode", "PostalName") FROM stdin;
1   01  001     Abergement-Clémenciat  825 01400   L ABERGEMENT CLEMENCIAT
2   01  002     Abergement-de-Varey 204 01640   L ABERGEMENT DE VAREY
3   01  004     Ambérieu-en-Bugey  13144   01500   AMBERIEU EN BUGEY
4   01  005     Ambérieux-en-Dombes    1469    01330   AMBERIEUX EN DOMBES
5   01  006     Ambléon    120 01300   AMBLEON

This file is generated with the command

pg_dump -a -d MediagralEF -n public -t '"City"' > City.sql

I use the sql method of MigrationBuilder to execute this file

        protected override void Up(MigrationBuilder migrationBuilder)
        {         
            string sql = "";
            try
            {
                foreach(string file in Directory.GetFiles(@"Migrations/PostgreSql/Fixtures/Base").OrderBy(f => f)) {
                    sql = File.ReadAllText(file);
                    migrationBuilder.Sql(sql, true);
                }
                sql = File.ReadAllText(@"Migrations/PostgreSql/CorrErreurTables.sql");
                migrationBuilder.Sql(sql, true);
            }
            catch (System.Exception)
            {
                System.Console.WriteLine("Erreur dans le fichier : " + sql);
                throw;
            }   
        }        

Only the files which contains the COPY instruction failed. Maybe i should use the ngpsql connector directly with the BeginTextImport instead of migrationBuilder. I already use BeginTextImport for an another migration phase.

After investigation, the small files with COPY are ok, but i have a huge file (282 mo) which is the origin of exception.

[roji]

OK, in that case there's probably a bug somewhere in Npgsql, I'll try to investigate.

Argh! no the small files throw the exception also.
I will use the insert instruction in place.

it's ok, no error with the inserts.
sorry for this issue, false alert.
Thanks.

[dharmaturtle]

On pg_dump, the flag --column-inserts will switch the COPY to INSERT