Using PgSqlDump backyp geometry columns as

Using PgSqlDump backyp geometry columns as

Postby Feneck91 » Thu 10 Nov 2016 13:39

Using PgSqlDump generate 'geometry' columns as varchar.

Code: Select all
PgSqlConnection conn = <my PgSqlConnection>;
conn.Open();
PgSqlDump pgSqlDump = new PgSqlDump();
pgSqlDump.Connection = conn;
pgSqlDump.IncludeDrop = false;
pgSqlDump.IncludeBlob = true;
pgSqlDump.GenerateHeader = true;
pgSqlDump.IncludeUsers = false;
pgSqlDump.Mode = Devart.Common.DumpMode.All;
pgSqlDump.Backup(_strFilePath);


The bakcup cannot be used to make database structure because the structure is not good, cannot use a varchar in place of geometry.
It generate :
CREATE TABLE public."DMChantier"
(
"ChantierID" bigint,
"Nom" varchar(5) NOT NULL,
"Type" smallint NOT NULL,
"Finalite" smallint NOT NULL,
"Etat" smallint NOT NULL,
"Emprise" varchar NOT NULL,
"BoundingBox" varchar NOT NULL,
"DateValidite" date,
"DateDebut" date,
"DateFin" date,
"NbDC" smallint NOT NULL,
"Commentaires" varchar(1024),
"CheminRacine" varchar(260) NOT NULL,
"FKPackageChantierID" bigint
);

"Emprise" and "BoundingBox" are geometry, not varchar !

Any idea ?
Thanks for your reply.
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Using PgSqlDump backyp geometry columns as

Postby Pinturiccio » Mon 14 Nov 2016 13:32

We could not reproduce the issue. Please provide us the following information:
1. dotConnect for PostgreSQL version;
2. PostgreSQL version;
3. DDL script of your table generated via pgAdmin.
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Using PgSqlDump backyp geometry columns as

Postby Feneck91 » Tue 15 Nov 2016 10:10

dotConnect for PostgreSQL version = 7.5.649.0
PostgreSQL version = 9.5
Script CREATE by pgAdmin =
Code: Select all
-- Table: public."DMChantier"

-- DROP TABLE public."DMChantier";

CREATE TABLE public."DMChantier"
(
  "ChantierID" bigint NOT NULL DEFAULT nextval('"DMChantier_ChantierID_seq"'::regclass),
  "Nom" character varying(5) NOT NULL,
  "Type" smallint NOT NULL,
  "Finalite" smallint NOT NULL,
  "Etat" smallint NOT NULL,
  "Emprise" geometry NOT NULL,
  "BoundingBox" geometry NOT NULL,
  "DateValidite" date,
  "DateDebut" date,
  "DateFin" date,
  "NbDC" smallint NOT NULL,
  "Commentaires" character varying(1024),
  "CheminRacine" character varying(260) NOT NULL,
  "FKPackageChantierID" bigint,
  CONSTRAINT "DMChantier_pkey" PRIMARY KEY ("ChantierID"),
  CONSTRAINT "FK_DMChantier_DMChantier_FKPackageChantierID" FOREIGN KEY ("FKPackageChantierID")
      REFERENCES public."DMChantier" ("ChantierID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public."DMChantier"
  OWNER TO dmrole;

-- Index: public."IX_DMChantier_FKPackageChantierID"

-- DROP INDEX public."IX_DMChantier_FKPackageChantierID";

CREATE INDEX "IX_DMChantier_FKPackageChantierID"
  ON public."DMChantier"
  USING btree
  ("FKPackageChantierID");


-- Trigger: dmchantier_create_update_delete_trigger on public."DMChantier"

-- DROP TRIGGER dmchantier_create_update_delete_trigger ON public."DMChantier";

CREATE TRIGGER dmchantier_create_update_delete_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON public."DMChantier"
  FOR EACH ROW
  EXECUTE PROCEDURE public.dmchantier_create_update_delete_notify();


Is it normal or the format should be different?
In normal way, the geometry columns format should be preserved?
May be an option to set into providers?
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Using PgSqlDump backyp geometry columns as

Postby Pinturiccio » Wed 16 Nov 2016 13:19

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Using PgSqlDump backyp geometry columns as

Postby Feneck91 » Wed 16 Nov 2016 14:11

Great, waiting reply...
Sure, I'm not the first one to try to backup a spatial Postgres database using PgSqlDump, isn't it ?
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Using PgSqlDump backyp geometry columns as

Postby Pinturiccio » Thu 17 Nov 2016 11:38

The issue is only related to the PostgGIS datatypes. The standard PostgreSQL spatial types like point, box, circle and others work correctly. We will post here when the issue is fixed.
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Using PgSqlDump backyp geometry columns as

Postby Feneck91 » Wed 23 Nov 2016 13:10

Do you have an idea of delay to get a workaroud or but correction for this feature?
Best regards.
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Using PgSqlDump backyp geometry columns as

Postby Shalex » Thu 24 Nov 2016 13:03

The issue is fixed. A timeframe for the new public build is several weeks.
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Re: Using PgSqlDump backyp geometry columns as

Postby Pinturiccio » Mon 12 Dec 2016 10:13

New version of dotConnect for PostgreSQL 7.7 is released!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=34690
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Using PgSqlDump backyp geometry columns as

Postby Feneck91 » Wed 04 Jan 2017 09:16

Did you test to backup / restore this kind of fields ?
Ok, now, the geometry fields are correctly written like :
Code: Select all
CREATE TABLE public."DMChantier"
(
  "ChantierID" bigint,
  "Nom" varchar(5) NOT NULL,
  "Type" smallint NOT NULL,
  "Finalite" smallint NOT NULL,
  "Etat" smallint NOT NULL,
  "Emprise" geometry NOT NULL,
  "BoundingBox" geometry NOT NULL,
  "DateValidite" date,
  "DateDebut" date,
  "DateFin" date,
  "NbDC" smallint NOT NULL,
  "Commentaires" varchar(1024),
  "CheminRacine" varchar(260) NOT NULL,
  "FKPackageChantierID" bigint
);


But, this Table backup generate a line like :
Code: Select all
INSERT INTO public."DMChantier" VALUES (1, E'NALT4', 0, 4, 1, E'\\001\\003\\000\\000 \\346\\020\\000\\000\\001\\000\\000\\000\\037\\000\\000\\000\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0006@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000(@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000*@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000,@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\000.@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0000@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0001@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0001@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0002@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0003@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0007@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0006@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0003@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0002@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0001@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0000@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000.@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000.@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000,@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000,@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000*@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000(@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000(@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0005@\\000\\000\\000\\000\\000\\000&@\\000\\000\\000\\000\\000\\0006@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0006@', E'\\001\\003\\000\\000\\000\\001\\000\\000\\000\\005\\000\\000\\000\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0008@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0004@\\000\\000\\000\\000\\000\\000$@\\000\\000\\000\\000\\000\\0008@', '2010-01-01 AD', '2015-01-01 AD', '2018-12-31 AD', 32, E'', E'Z:\\NALT4', NULL);


Try to execute this request with PgSqlDump or with pgAdmin : it will not work :
For pgAdmin :
ERREUR: parse error - invalid geometry
LINE 1: ...public."DMChantier" VALUES (1, E'NALT4', 0, 4, 1, E'\\001\\0...
^
HINT: "\0" <-- parse error at position 2 within geometry
********** Erreur **********

ERREUR: parse error - invalid geometry
État SQL :XX000
Astuce : "\0" <-- parse error at position 2 within geometry
Caractère : 63


For PgSqlDump, an exception is raised :
Filename : lwgeom_pg.c
"\"\\0\" <-- parse error at position 2 within geometry"
"pg_parser_errhint"

error = parse error - invalid geometry
.

It seems that the backup work well for table CREATION, not for the data of the table.
A way to make it work well ?

In postgresql documentation
The geometry are not saved / backup into blob
Feneck91
 
Posts: 37
Joined: Mon 12 Aug 2013 13:52

Re: Using PgSqlDump backyp geometry columns as

Postby Pinturiccio » Thu 05 Jan 2017 15:57

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Using PgSqlDump backyp geometry columns as

Postby Pinturiccio » Fri 13 Jan 2017 14:30

We have fixed the bug with backing up values of PostGIS datatypes via the PgSqlDump class. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Using PgSqlDump backyp geometry columns as

Postby Pinturiccio » Mon 16 Jan 2017 16:02

New build of dotConnect for PostgreSQL 7.7.819 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=34832
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL