QGIS » QGIS Application

Last Friday I encountered a new error that I've never seen before. When editing a new PostGIS table in QGIS 3.6.0 I'm unable to save my edits as the 'gid' column auto populates it's value as NULL. This behaviour seems to occur when drawing a new spatial feature, as well as when copy and pasting new features from a separate shapefile without 'gid' column.

There error received is as follows.
Could not commit changes to layer tablename

Errors: ERROR: 1 feature(s) not added.

Provider errors:
      PostGIS error while adding features: ERROR:  null value in column "gid" violates not-null constraint
    DETAIL:  Failing row contains (null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0106000020E6100000010000000103000000010000000400000060015885FD2F...).

Looking into the database the table is using gid SERIAL PRIMARY KEY to define the "gid" column. I've tried dropping and re-adding the "gid" column, creating new tables, even manually creating a sequence and manually defining a new gid column as gid integer NOT NULL DEFAULT nextval('"sequencename"'::regclass) but no luck.

The strange thing is other tables within our PostgreSQL/PostGIS database do respond properly, providing the nextval('sequencename'::regclass) within the editor form and attribute table when creating new features or copy and pasting features in from another source.

I looked into our database and it is reporting itself as
PostgreSQL 9.4.19 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit
POSTGIS="2.1.4 r12966" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER

I suspect that the database may have recently been upgraded to this version 9.4.19 which maybe related to this new glitch. Older tables in our database seem to function properly, recreating those same database tables using new tablenames seems to run into this new glitch.

When searching online I came across an older ticket - Bug report #19564 which seems to describe the same behaviour. As described in this post, tables that do function properly seem to identify the 'gid' column as a 'Text Edit' widget type, whereas the new tables that are broken seem to identify 'gid' column as a 'Range' widget type. Looking at the database both tables were created the same manner using gid SERIAL PRIMARY KEY. However, somehow QGIS is detecting a difference between these two tables.

This issues seems is not only limited to the latest QGIS 3.6.0, I also tried QGIS 2.18.21 and the results are the same (some older tables provide nextval('sequencename'::regclass) and newer tables remain NULL. Without the ability for the database to auto-increment a gid column using a sequence properly, it makes our database significantly harder to use within QGIS. This issue seems to be affecting more users so I decided to file a new issue to see if any others have been able to find a suitable solution.

I've attached some screenshots for your reference. It seems that either QGIS, the PostgreSQL database or the way in which SERIAL columns are being communicated from one to the other has somehow changed recently, but I'm not sure as to where the change exists or how to resolve the behaviour. I'm hoping someone with more knowledge of the inner workings of QGIS and the PostGIS layer management will have some more understanding of what is happening here. This is the strangest behaviour I have ever come across in my 10 years of working with PostgreSQL/PostGIS and I'm absolutely stumped.