Opened 7 years ago

Closed 7 years ago

Last modified 6 years ago

#5495 closed enhancement (fixed)

[pg] Explicitly list column names in COPY syntax

Reported by: strk Owned by: warmerdam
Priority: normal Milestone: 2.0.0
Component: OGR_SF Version: unspecified
Severity: normal Keywords:
Cc:

Description

In presence of DDL triggers by the time COPY is called the structure of the just-created TABLE could be changed. Explicitly listing the expected column names in the COPY construct would play nicer with such scenarios.

I'll work on a patch if I find the time

Change History (10)

comment:1 Changed 7 years ago by strk

Component: defaultOGR_SF

comment:2 Changed 7 years ago by strk

Resolution: invalid
Status: newclosed

It turns out the code is already providing the explicit column names, since 1.4.0: http://trac.osgeo.org/gdal/browser/tags/gdal_1_4_0/ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp#L1749

Closing as invalid

comment:3 Changed 7 years ago by strk

Resolution: invalid
Status: closedreopened

Actually, I just noticed that the problem is that the list of fields in the table area really re-extracted after lots of ALTER calls. But by specifying a null value the COPY statement does not allow using the default for the fields for which it does not have a value.

comment:4 Changed 7 years ago by strk

A behavior that might probably work better would be to just omit the fields that did not come from the original source table.

comment:5 Changed 7 years ago by strk

Same issue with INSERT ! ogr2ogr is actually querying the database catalogue to find out which fields are present. The PostgreSQL logs show:

SELECT a.attname, a.attnum, t.typname, t.typname = ANY(ARRAY['int2','int4','serial']) AS isfid FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n, pg_index i WHERE a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND c.relnamespace = n.oid AND c.oid = i.indrelid AND i.indisprimary = 't' AND t.typname !~ '^geom' AND c.relname = 'importer_7547520ae73d11e39bc80090f58b1ecf' AND (i.indkey[0]=a.attnum OR i.indkey[1]=a.attnum OR i.indkey[2]=a.attnum OR i.indkey[3]=a.attnum OR i.indkey[4]=a.attnum OR i.indkey[5]=a.attnum OR i.indkey[6]=a.attnum OR i.indkey[7]=a.attnum OR i.indkey[8]=a.attnum OR i.indkey[9]=a.attnum) AND n.nspname='cdb_importer' ORDER BY a.attnum

Indeed both INSERT and COPY use the real explicit names, but the problem is with passing a null to fields that do not accept one.

I'll see how to skip the fields that have a default value, given ogr2ogr does not define a default for the fields added by itself, right ?

comment:6 Changed 7 years ago by strk

Actually, we don't even need to know about the existence of a default, it would be enough to NOT mention fields that were not available in the original table (the one we came from). Maybe poFeature->IsFieldSet? ?

comment:7 Changed 7 years ago by Even Rouault

Sandro, hopefully the following commit should fix your use case :

trunk r27424 "PG: when creating a table and filling it, avoid re-reading the table definition from PG system tables (#5495)"

comment:8 Changed 7 years ago by strk

It does, thank you ! Any chance to see this backported ?

comment:9 Changed 7 years ago by Even Rouault

Milestone: 2.0
Resolution: fixed
Status: reopenedclosed

Hum, that looks a bit too risky to backport.

comment:10 Changed 6 years ago by Even Rouault

Milestone: 2.02.0.0

Milestone renamed

Note: See TracTickets for help on using tickets.