PostgreSQL primary key sequence is not updated when -preserve_fid is used

[cmartinez]

When loading a layer on PostGIS using ogr2ogr, a sequence is created for the serial primary key. If -preserve_fid paramenter is provided, the sequence start is not properly initialized (i. e. sequence starts on 1). This leads to problems if the layer is later edited. Is this an intended behaviour or should be considered a bug?

Note that I am using GDAL version 1.11.3.

For instance, using --preserve_fid

ogr2ogr -preserve_fid -update -f PostgreSQL "PG:host='localhost' dbname='test'" myshp.shp  myshp -nln "test1"

We get the following sequence status:

# SELECT last_value from test1_ogc_fid_seq ;
 last_value
------------
          1
(1 row)

Instead, if we don't use -preserve_fid, the sequence is properly initialized to 102:

# Not using -preserve_fid: sequence properly initialized to 102
ogr2ogr -update -f PostgreSQL "PG:host='localhost' dbname='test'" myshp.shp  myshp  -nln "test2"

# SELECT last_value from test2_ogc_fid_seq ;
 last_value
------------
        102
(1 row)

What are the problems you face when editing such layer?

If you later try to add a new record to the layer without providing an id, it will raise an error, defeating the purpose of having a serial PK. For instance:

INSERT INTO test1 (wkb_geometry)
VALUES (ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)', 4258)) ;

ERROR: duplicate key value violates unique constraint "test1_pkey"
SQL state: 23505
Detail: Key (ogc_fid)=(1) already exists.

They you must provide the ogc_fid by hand:

INSERT INTO test1 (ogc_fid, wkb_geometry)
VALUES (102, ST_GeomFromText('MULTIPOINT(-5.34510248270791 36.1506091987899)', 4258)) ;

Do you have an alternative suggestion about what to do for the sequence?

The sequence should be initialized in the same way as it is done when -preserve_fid is not provided. For the layer & sequence in the example, the following SQL statement would work:

SELECT set_val('test1_ogc_fid_seq', max(ogc_fid)) FROM test1 ;