#7203 closed defect (fixed)
Carto: Double upload results in sequence errors
Reported by: | pramsey | Owned by: | warmerdam |
---|---|---|---|
Priority: | normal | Milestone: | 2.2.4 |
Component: | OGR_SF | Version: | svn-trunk |
Severity: | normal | Keywords: | |
Cc: |
Description
Running this upload twice
ogr2ogr \ --config DEBUG ON \ --config CARTO_API_KEY xxxxxxxx \ -t_srs EPSG:4326 \ -f Carto \ -nln pts_tmp \ "Carto:pramsey" \ pts2.shp
results in one success and then errors, one per record it seems:
ERROR 1: HTTP error code : 400 ERROR 1: RunSQL Error Message:HTTP error code : 400 ERROR 1: Error returned by server : relation "pts_tmp_cartodb_id_seq" does not exist
I'm wondering if this has something to do with the table creation in RunDeferredCreationIfNecessary which both creates a serial
column and then also creates and associates a sequence. Thought maybe that doesn't matter and we just get some orphaned sequences.
Attachments (2)
Change History (11)
comment:2 by , 7 years ago
The problem is that Carto is modifying the table away from ogr's expectation. OGR is assuming the primary key seq will always be named <table>_<column>_seq, and that's not true, as it looks like CDB_Cartodbfy() is changing out the sequence name at the end of the first load. I think that (a) ogr shouldn't make assumptions and (b) Carto shouldn't f with valid sequences in CDB_Cartodbfy(), so here's a patch for (a) and I'll look at why (b) is happening on our side.
comment:3 by , 7 years ago
Isn't there a default mode? Using -overwrite
works, I guess the table is cleanly dropped first. Explicitly using -append
results in the errors, so I guess append is the default mode.
comment:4 by , 7 years ago
I guess that it depends on the driver. I think that user friendly and safe behavior would be to create a table straight ahead with -nln if table does not exist and throw an error if it does exist, with a hint to use explicitly either -append or -overwrite. Of course the default must not be -overwrite. Some drivers support also truncate, like PostGIS driver through a config option. Might be useful in Carto driver as well.
Actually it seems that in this case the default comes from the layer creation option "OVERWRITE" of the Carto driver http://www.gdal.org/drv_carto.html.
comment:5 by , 7 years ago
Using %s_%s_fid_seq
as the sequence name should give the CDB_Cartodbfy() function the space it need to create a %s_%s_seq
when it re-writes the table (as it must, since the table as created by ogr lacks a the_geom_webmercator
). It's a bit of a guess, whether it's better to upload only one geometry and accept the overhead in CDB_Cartodbfy() or to write a perfect table (at the cost of uploading two copies of the geometry). A tricky cake-and-also-eat-it solution would be to create the table with a mercator column, and add the _CDB_update_the_geom_webmercator trigger before loading the data. That probably exposes too much Carto guts though, best not to do that.
comment:8 by , 7 years ago
@pramsey Would be nice to provide patches that actually compile ;-)
I've fixed it differently by retrieving the sequence name.
The fact that "ogr2ogr -f carto" ran the second time proceeds to append mode is a bit of a side effect of the ogr2ogr logic and not completely intended. Normally you should do "ogr2ogr -append -t_srs EPSG:4326 -nln pts_tmp carto:pramsey pts2.shp"
comment:9 by , 7 years ago
Thank you Even, I should have labelled it "patch for discussion purposes" :)
I think that you command is not a good one without either -append or -overwrite http://www.gdal.org/ogr2ogr.html. But perhaps you would like to see some other error message?