Merge pull request #34017 from elpaso/bugfix-gh29560-pg-identity-pk-d…

…efault-values PG: fix default value for IDENTITY PKs
qgis · Jan 27, 2020 · ec4d9f6 · ec4d9f6
2 parents 1c21326 + 63e66c2
commit ec4d9f6
Show file tree

Hide file tree

Showing 3 changed files with 59 additions and 0 deletions.
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -1111,6 +1111,31 @@ bool QgsPostgresProvider::loadFields()
     }
 
     mAttrPalIndexName.insert( i, fieldName );
+    // If this is an identity field with constraints and there is no default, let's look for a sequence:
+    // we might have a default value created by a sequence named <table>_<field>_seq
+    if ( ! identityMap[tableoid ][ attnum ].isEmpty()
+         && notNullMap[tableoid][ attnum ]
+         && uniqueMap[tableoid][attnum]
+         && defValMap[tableoid][attnum].isEmpty() )
+    {
+      const QString seqName { mTableName + '_' + fieldName + QStringLiteral( "_seq" ) };
+      const QString seqSql { QStringLiteral( "SELECT c.oid "
+                                             "  FROM pg_class c "
+                                             "  LEFT JOIN pg_namespace n "
+                                             "    ON ( n.oid = c.relnamespace ) "
+                                             "  WHERE c.relkind = 'S' "
+                                             "    AND c.relname = %1 "
+                                             "    AND n.nspname = %2" )
+                             .arg( quotedValue( seqName ) )
+                             .arg( quotedValue( mSchemaName ) )
+                           };
+      QgsPostgresResult seqResult( connectionRO()->PQexec( seqSql ) );
+      if ( seqResult.PQntuples() == 1 )
+      {
+        defValMap[tableoid][attnum] = QStringLiteral( "nextval(%1::regclass)" ).arg( quotedIdentifier( seqName ) );
+      }
+    }
+
     mDefaultValues.insert( mAttributeFields.size(), defValMap[tableoid][attnum] );
 
     QgsField newField = QgsField( fieldName, fieldType, fieldTypeName, fieldSize, fieldPrec, fieldComment, fieldSubType );

diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -1420,6 +1420,25 @@ def testFeatureCountEstimatedOnView(self):
         self.assertTrue(vl.isValid())
         self.assertTrue(self.source.featureCount() > 0)
 
+    def testIdentityPk(self):
+        """Test a table with identity pk, see GH #29560"""
+
+        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable key=\'gid\' srid=4326 type=POLYGON table="qgis_test"."b29560"(geom) sql=', 'testb29560', 'postgres')
+        self.assertTrue(vl.isValid())
+
+        feature = QgsFeature(vl.fields())
+        geom = QgsGeometry.fromWkt('POLYGON EMPTY')
+        feature.setGeometry(geom)
+        self.assertTrue(vl.dataProvider().addFeature(feature))
+
+        del(vl)
+
+        # Verify
+        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable key=\'gid\' srid=4326 type=POLYGON table="qgis_test"."b29560"(geom) sql=', 'testb29560', 'postgres')
+        self.assertTrue(vl.isValid())
+        feature = next(vl.getFeatures())
+        self.assertIsNotNone(feature.id())
+
 
 class TestPyQgsPostgresProviderCompoundKey(unittest.TestCase, ProviderTestCase):
 

diff --git a/tests/testdata/provider/testdata_pg.sql b/tests/testdata/provider/testdata_pg.sql
@@ -671,3 +671,18 @@ CREATE VIEW qgis_test.b31799_test_view_ctid AS (SELECT ctid, geom, random() FROM
 CREATE VIEW qgis_test.b32523 AS
   SELECT pk, random()
   FROM qgis_test.some_poly_data;
+
+----------------------------------------------
+--
+-- IDENTITY pk
+-- See https://github.com/qgis/QGIS/issues/29560
+--
+
+CREATE TABLE qgis_test.b29560 (
+    gid int8 NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    geom geometry(polygon)
+);
+
+INSERT INTO qgis_test.b29560 (geom)
+VALUES ('POLYGON EMPTY'::geometry);
+