Bug report #19564

PostgreSQL sequences not always used when adding feature

Added by Nicolas Boisteault over 1 year ago. Updated 7 months ago.

Status:Open
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:3.3(master) Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:27391

Description

I have several PostgreSQL layers with no geometries. All have an 'id' column with serial type so they have a primay key and a sequence. When I add a feature, with some of these tables I have 'nextval('schema.id_seq'::regclass)' in the id field (which is the correct behavior) and with some others I have a NULL value.
I can't see no difference between the tables with the good behavior and the others.
I've also noticed that when I open the 'Attributes form' tab for wrong behavior tables the id field has 'Range' Widget Type proposed. For good behavior tables it is 'Text Edit' Widget Type.
Issue also occurs in QGIS 2.18.x and I have tested with 3 different Windows machines. Database is PostgreSQL 9.6 on Ubuntu 16.04.
I can give limited access to my database if needed.

annuaire.sql (10.9 KB) Nicolas Boisteault, 2018-09-26 04:08 PM

not_working_gid_sequence.png (53 KB) David Mancini, 2019-03-22 09:22 PM

working_gid_sequence.png (46.5 KB) David Mancini, 2019-03-22 09:22 PM

History

#1 Updated by Tudor Bărăscu over 1 year ago

It would be easier to just paste here the SQL code that creates the tables/sequences so your issue can be replicated without access. It's easier for everyone + you may find your solution meanwhile.

#2 Updated by Giovanni Manghi over 1 year ago

  • Status changed from Open to Feedback

#3 Updated by Nicolas Boisteault about 1 year ago

Same issue with last nightly. I've attached the SQL code. For example table 'choix_secteur_activite' has the good behavior and 'choix_public_cible' doesn't.

#4 Updated by Giovanni Manghi about 1 year ago

Nicolas Boisteault wrote:

Same issue with last nightly. I've attached the SQL code. For example table 'choix_secteur_activite' has the good behavior and 'choix_public_cible' doesn't.

they both work the same here (with 'nextval('schema.id_seq'::regclass)'). QGIS Master, pgsql 10

#5 Updated by Nicolas Boisteault about 1 year ago

I have tested on a fresh install of PostgreSQL 9.6 and can't reproduce the issue. As I am the only one having this issue I guess it is not a QGIs one. Thank you for your tests. You can close issue.

#6 Updated by Giovanni Manghi about 1 year ago

  • Resolution set to invalid
  • Status changed from Feedback to Closed

#7 Updated by David Mancini 9 months ago

I am experiencing the exact same behaviour as described by the original poster. Today for the first time I ran into an issue where the PostgreSQL sequence was not providing the nextval('name_of_sequence'::regclass) in the edit form. When comparing against other tables in the database, others preform normally.

Digging a little deeper through the layer properties I also saw that the working table showed my gid SERIAL PRIMARY KEY column as 'Text Edit' widget Type, whereas the non-working table showed it as 'Range' as described by the original poster.

At the database level both these tables were created with the exact same process. Here is the SQL for comparison.
CREATE TABLE "temp_BC_ParcelMap_Knockout_20170727_a"
(
gid serial NOT NULL,
grmn_type character varying,
the_geom geometry(Geometry,4326),
CONSTRAINT "temp_BC_ParcelMap_Knockout_20170727_a_pkey" PRIMARY KEY (gid)
)
WITH (
OIDS=TRUE
);
ALTER TABLE "temp_BC_ParcelMap_Knockout_20170727_a"
OWNER TO postgres;

CREATE TABLE "temp_BC_ParcelMap_Knockout_20190319_a"
(
gid serial NOT NULL,
grmn_type character varying,
the_geom geometry(Geometry,4326),
CONSTRAINT "temp_BC_ParcelMap_Knockout_20190319_a_pkey" PRIMARY KEY (gid)
)
WITH (
OIDS=TRUE
);
ALTER TABLE "temp_BC_ParcelMap_Knockout_20190319_a"
OWNER TO postgres;

It seems all the new tables I create are affected by this behaviour, but older tables which have been within the database for the past few years work as expected. Was there any solution to this issue?

I've tested these tables in QGIS 3.6.0 and QGIS 2.18.21 and the bug is affecting both. Our database server is running PostgreSQL 9.4.19 on Debian 8.11.

#8 Updated by Nicolas Boisteault 9 months ago

  • Status changed from Closed to Reopened

Thanks David for your feedback. We used trigger to increment sequence as a workaround.

#9 Updated by Alessandro Pasotti 9 months ago

Does it change anything if you change the widget type from TEXT to RANGE or vice-versa?

#10 Updated by Giovanni Manghi 9 months ago

  • Status changed from Reopened to Feedback
  • Resolution deleted (invalid)

#11 Updated by David Mancini 9 months ago

Unfortunately no, changing the widget type from Range to Text does not resolve the issue.

The only work around I was able to come up with within QGIS 3.6.0 was setting a Default expression as follows: maximum( "gid" ) + 1

Not ideal, but does at least allow me to save new features and copy / paste new features in from tables without gid. Having a look into the database I can see that this workaround still does not utilize the currently defined sequence as the Next Value does not continue to grow as new features are added in this way.

#12 Updated by Giovanni Manghi 9 months ago

  • Status changed from Feedback to Open

#13 Updated by David Mancini 7 months ago

Nicolas Boisteault wrote:

Thanks David for your feedback. We used trigger to increment sequence as a workaround.

Hello Nicolas. Any chance you can provide the TRIGGER statement you used for your workaround?

Also available in: Atom PDF