Unable to add new features in QGIS for PostGIS table using sequence for gid. Data type serial: 'integer overflow'

[sylviaobk]

PostGIS 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
Postgres PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 64-bit
QGIS 2.18 Las Palmas

CREATE TABLE myschema.mytable
(
  gid serial NOT NULL,
  the_geom geometry(Point,25832),
  CONSTRAINT mytable_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=TRUE
);

In QGIS I added the PostGIS - layer "myschema.mytable" and tried to save a new object.
Following message appears:

"Konnte Änderungen am Layer mytable nicht festschreiben

Fehler: FEHLER: Ein Objekt nicht hinzugefügt.

Datenanbieterfehler:
PostGIS-Fehler beim Attributhinzufügen: FEHLER: NULL-Wert in Spalte »gid«
verletzt Not-Null-Constraint
DETAIL: Fehlgeschlagene Zeile enthält (null,
0102000020E8640000020000004DAC4DB4A84918414C986446399155416E8719...)."

"Could not commit changes on layer mytable

Error: ERROR: One object is not added.

Data provider error:
PostGIS-Error on add attribute: ERROR: NULL- value in column »gid«
is not permitted as an input because of Not-Null-Constraint
DETAIL: Failed row contains (null,
0102000020E8640000020000004DAC4DB4A84918414C986446399155416E8719...)."

In QGIS should be the default for column 'gid':
nextval('myschema.mytable_gid_seq'::regclass)

Instead the default for column 'gid' is 'NULL'.

To reproduce the error select on PostGIS:

SELECT 'myschema.mytable'::regclass::oid;

If the result is bigger than the maximum integer - value 2147483647
it is not possible to use data type 'serial' in QGIS to add a new object.
But in PostGIS I can add objects if the value of column "gid" is of data type 'serial'
and the result is bigger than 2147483647 because PostGIS has no "integer overflow".

(Please refer to #29489, https://issues.qgis.org/issues/21673)

I think QGIS can't handle sequences after integer overflow.

Is it possible to change the data type of regclass::oid from 'integer' into 'biginteger'
in QGIS??

[PeterPetrik]

Do you use QGIS 2.18 Las Palmas ? This version is not supported anymore. If so, please update to QGIS 3.x. Thanks

[sylviaobk]

I have one thing to add: it is the same behaviour with QGIS 3.x

[elpaso]

Checked, there is no way you can overflow serial, you should change type to bigserial or use an uuid.

[elpaso]

Re-opening for the NULL issue, the serial overflow is not a bug.

[jef-n]

Duplicate of #29489

[sylviaobk]

Let me clarify my Problem:

example with bug:
first database:

CREATE TABLE myschema.mytable_bigserial
(
gid bigserial NOT NULL,
nr integer,
the_geom geometry(Point,25832),
CONSTRAINT mytable_bigserial_pkey PRIMARY KEY (gid)
)
WITH (
OIDS=TRUE
);

SELECT 'myschema.mytable_bigserial'::regclass::oid;
oid

2481381085

This result 2481381085 is bigger than maximum integer - value 2147483647
That's why in QGIS the vlaue for the column "gid" is not pre-filled.

example without bug:
second database:

CREATE TABLE myschema.mytable_bigserial
(
gid bigserial NOT NULL,
nr integer,
the_geom geometry(Point,25832),
CONSTRAINT mytable_bigserial_pkey PRIMARY KEY (gid)
)
WITH (
OIDS=TRUE
);

SELECT 'myschema.mytable_bigserial'::regclass::oid;
oid

836622329
This result 836622329 is smaller than maximum integer - value 2147483647
That's why in QGIS the value for the column gid is pre-filled.
gid:
nextval('myschema.mytable_bigserial_gid_seq'::regclass)

The "integer overflow" is not referred to the value of the column "gid"
but the value of "SELECT 'myschema.mytable_bigserial'::regclass::oid;"

[elpaso]

@sylviaobk I cannot reproduce the issue (maybe because my table OID is much lower and it does not overflow) and I cannot just create 4 billion tables to make it overflow.
But there is achance that in PG provider we have an issue with integer overflow, because we are storing the table oid as an integer (signed 4 bytes) instead of a unsigned integer.
My question is: if I prepare a windows build with a patch would you be able to test if that patch solves your issue?

[sylviaobk]

Yes, me and my peers would test the patch!! Thank you :-)

[elpaso]

Ok, I was able to reproduce the issue on windows, reopening (again)

[elpaso]

@sylviaobk here is the test build http://www.itopen.it/bulk/qgis-mxe-release-2019-06-12-08-08-19.zip

No installation required (unzip it somewhere and run qgis.exe), no Python.

[sylviaobk]

I ran \qgis-mxe-release-2019-06-12-08-08-19\qgis-mxe-release\qgis.exe,
added myschema.mytable_gidserial and tried to add a new object
"gid" is still "NULL", same behaviour like before

[elpaso]

I think we have two different issues here:

1. the value for the sequence overflows (in case of "serial"), there is nothing we can do in that case, if you use "bigserial" the problem should go away until you overflow bigserial
2. the OID value of the relation select regclass('myschema.mytable_gid_seq')::oid; is 2481381085 (and it overflows int but not unsigned int, this is the issue that my patch try to fix.

I can reproduce issue 1 (using "serial") on both Linux and windows but as I said I don't think that is is a bug and we cannot do anything about it.
I cannot reproduce 2 because on my DB the OID of the sequence is lower:

select regclass('myschema.mytable_gid_seq')::oid;
 regclass 
----------
   592602
select regclass('myschema.mytable)::oid;
 regclass 
----------
   592604

but my patch uses unsigned int instead of int and that's why I believe it should fix the issue.

The default value that QGIS uses for gid should be something like

nextval('myschema.mytable_gid_seq'::regclass)

that is the next value of the sequence, if that overflows we are back to issue 1 and you should get an error message like (for type "serial")

Could not commit changes to layer mytable

Errors: ERROR: 1 feature(s) not added.
  
  Provider errors:
      PostGIS error while adding features: ERRORE:  nextval: è stato raggiunto il valore massimo della sequenza "mytable_gid_seq" (2147483647)

Note also that if the table is created with "bigserial" a sequence with value 2147483647 will not overflow and the error 1 will not appear.

[sylviaobk]

2. the OID value of the relation select regclass('myschema.mytable_gid_seq')::oid; is 2481381085 (and it >overflows int but not unsigned int, this is the issue that my patch try to fix.
   ....
   but my patch uses unsigned int instead of int and that's why I believe it should fix the issue.

I had high hopes your patch could fix the issue, and I don't understand why it doesn't.
But thank you for your efforts!

[elpaso]

Just to be sure I fully understand your procedure, would you be able to share a short video of the operations that leads to the error?

[sylviaobk]

It is a mp4 file

steps.zip

[elpaso]

Are you missing the default value for gid?

ALTER TABLE ONLY myschema.mytable ALTER COLUMN gid SET DEFAULT nextval('myschema.mytable_gid_seq'::regclass);

[sylviaobk]

The default value for gid is missing in QGIS

The default value for gid is not missing in SQL - insert statement on the data base.
In the video you can see me inserting a new object with SQL on the data base and gid is filled automatically. The problem with the missing default is in QGIS only.
And it is not missing in QGIS when "SELECT 'myschema.mytable_bigserial'::regclass::oid;" is smaller than 2147483647.

It doesn't help to ALTER TABLE ONLY myschema.mytable ALTER COLUMN gid SET DEFAULT nextval('myschema.mytable_gid_seq'::regclass);
This is implicit to data type serial

The missing default value for gid in QGIS is exactly the bug I tried to describe

[elpaso]

Thank you, it is clear now. I'm looking for other OID overflows in the provider code now, I'll provide you a new version to test shortly.

[elpaso]

@sylviaobk some more fixes to the OIDs http://www.itopen.it/bulk/qgis-mxe-release-2019-06-12-14-02-44.zip please check it.

[sylviaobk]

That's it! :-)
The default value for gid is now pre-filled for a new object in QGIS and incremented automatically with "save object".
Which official download version of QGIS could include this bugfix and is it possible to patch version 2.18?

Thanks for your help!

[nirvn]

@sylviaobk , 2.18 is RIP, there's no way to overstate the need to move to the new LTR 3.4. If there are legitimate issues with 3.4 that prevents you from leading code of deceased 2.18, you should file tickets.

[sylviaobk]

So the bugfix would be implemented in LTR 3.4?

[elpaso]

@sylviaobk thank you for your help in identifying the root of the issue and for testing the patches, I will backport this into 3.4 branch and it will land into the LTR.