Bug report #6798

when copying a table by d&d from one schema to another the serial property is lost in the pk

Added by Giovanni Manghi almost 7 years ago. Updated 7 months ago.

Status:Closed
Priority:Normal
Assignee:-
Category:DB Manager
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:end of life
Crashes QGIS or corrupts data:No Copied to github as #:15946

Description

When copying (by d&d) a PG table from one schema to another, it seems that the primary key column loses the autocremental property (in the new table), resulting in a error when adding features.

bug_6798_qgis_2_18.mp4 - screencast (#6789, QGIS 2.18.0) (869 KB) R. R., 2016-11-21 11:48 AM

6798_rename_primary_key.mp4 (1.37 MB) R. R., 2016-12-18 02:55 AM

pg_create_sequence_qgis_2x.py Magnifier (1.57 KB) R. R., 2017-03-04 01:06 PM

6798_create_sequence.mp4 (4.57 MB) R. R., 2017-03-04 01:06 PM

6798_create_sequence_version_2.mp4 (2.3 MB) R. R., 2017-03-12 06:09 AM

pg_create_sequence_qgis_2x_version_2.py Magnifier (1.75 KB) R. R., 2017-03-12 06:09 AM


Related issues

Related to QGIS Application - Bug report #13823: DB Manager - Renaming constraints and indexes fails when ... Closed 2015-11-13
Duplicated by QGIS Application - Bug report #14104: DB Manager: sequence issue when copying a table from a Po... Closed 2016-01-14

History

#1 Updated by Paolo Cavallini almost 7 years ago

  • Assignee changed from Giuseppe Sucameli to anonymous -

#2 Updated by Jürgen Fischer over 6 years ago

  • Assignee deleted (anonymous -)

#3 Updated by Giovanni Manghi about 6 years ago

it happens only when in the dialog the user does not specify the primary key by enabling the "primary key" checkbox, so while it a very boring bug for real work, it is not that critical.

#4 Updated by Paolo Cavallini about 6 years ago

Why not setting the box checked by default?

#5 Updated by Giovanni Manghi over 5 years ago

  • Target version changed from Version 2.0.0 to Version 2.4

#6 Updated by Jürgen Fischer over 5 years ago

  • Target version changed from Version 2.4 to Future Release - High Priority

#7 Updated by Giovanni Manghi almost 4 years ago

  • Subject changed from primary key issue when copying a table from one schema to another to when copying a table by d&d from one schema to another the serial property is lost in the pk
  • Target version deleted (Future Release - High Priority)

Giovanni Manghi wrote:

it happens only when in the dialog the user does not specify the primary key by enabling the "primary key" checkbox, so while it a very boring bug for real work, it is not that critical.

this workaround does not work anymore.

#8 Updated by Bo Victor Thomsen almost 4 years ago

A more specific explanation of the problem:

A "serial" data type in PostgreSQL is syntactic sugar for "integer NOT NULL DEFAULT nextval('some_sequence'::regclass)", combined with the implicit creation of the sequence used in the DEFAULT clause.

So the D&D function do create the correct base type for the column, but fails to create the corresponding sequence and the default clause for the column.

#9 Updated by Paul Kanelli over 3 years ago

See also #14104

#10 Updated by Paul Kanelli over 3 years ago

This issue is quite annoying and it's unfixed for 3 years. Can someone set the priority to high or blocker?

#11 Updated by Giovanni Manghi over 3 years ago

Paul Kanelli wrote:

This issue is quite annoying and it's unfixed for 3 years. Can someone set the priority to high or blocker?

we usually don't raise priority based on age of tickets. Blockers are regressions, and high priority is usually reserved for issues causing crash or data corruption (that are not regressions). This issue does not fit either.

#12 Updated by Paul Kanelli over 3 years ago

Thx for the info. Here comes the workaround:

SELECT S E T V A L('<schema><table>_<column>_seq', (SELECT M A X(<column>) FROM <schema>.<table>));

ALTER TABLE <schema>.<table>
ALTER COLUMN <column> SET DEFAULT nextval('<schema><table>_<column>_seq'::regclass);

Please ignore the spaces in SETVAL and MAX.

Example:

SELECT S E T V A L('public.buildings_gid_seq', (SELECT M A X(gid) FROM public.buildings));

ALTER TABLE public.buildings
ALTER COLUMN gid SET DEFAULT nextval('public.buildings_gid_seq'::regclass);

#13 Updated by Paul Kanelli over 3 years ago

  • Assignee set to Giuseppe Sucameli

#14 Updated by Paul Kanelli over 3 years ago

  • Assignee deleted (Giuseppe Sucameli)

#15 Updated by Jason Wheatley over 3 years ago

Paul - Thanks for the work around. I'll have to test that.
This is definitely an annoying bug. I only found this out recently when we decided to switch to PostGIS/QGIS as our main production workflow. Anytime you need to move a table from one location to another, you have to create a new primary key column (check primary key box and type in a new column name) otherwise you are unable to add any new records to those imported tables.

I would love to see this one fixed!

#16 Updated by Giovanni Manghi over 3 years ago

Paul Kanelli wrote:

Thx for the info. Here comes the workaround:

SELECT S E T V A L('<table>_<column>_seq', (SELECT M A X(<column>) FROM <schema>.<table>));

ALTER TABLE <schema>.<table>
ALTER COLUMN <column> SET DEFAULT nextval('<table>_<column>_seq'::regclass);

Please ignore the spaces in SETVAL and MAX.

if this is a patch that can be applied in DB manager code please submit it via pull request in the QGIS github repository.

#17 Updated by Paul Kanelli over 3 years ago

Hi Giovanni, it's just a SQL query to sync the primary key. I've no programming skills.

Related: #14712

#18 Updated by R. R. over 3 years ago

Is there any chance that we get this issue fixed soon?

#19 Updated by R. R. about 3 years ago

(In some cases the name of the primary key is placed in quotes when copying a table by D&D (see also: #15226). After removing the quotes in the "Import vector layer" dialog, adding new features works properly. To me, this is really mysterious.)

EDIT: fixed

#20 Updated by R. R. almost 3 years ago

In QGIS 2.18.0 the sequence is completely missing after copying a table by D&D, but maybe this is better than a broken seuqence.

#21 Updated by R. R. almost 3 years ago

#22 Updated by R. R. almost 3 years ago

See also: #15991

#23 Updated by R. R. almost 3 years ago

It seems that renaming the primary key is the only way to create a working sequence (see screencast 6798_rename_primary_key.mp4).

#24 Updated by R. R. over 2 years ago

Here comes a workaround for the missing sequence ...created with a lot of help from gis.stackexchange.com.

#25 Updated by Giovanni Manghi over 2 years ago

Reinhard Reiterer wrote:

Here comes a workaround for the missing sequence ...created with a lot of help from gis.stackexchange.com.

it would be nice of you can do a PR to add this script to

https://github.com/qgis/QGIS-Processing

#26 Updated by R. R. over 2 years ago

Hi Giovanni, I will check GitHub as soon as I find some time. At the moment, I do not know how a PR works. However, I have implemented a small improvement. There is no longer need to reload the layer manually.

#27 Updated by Giovanni Manghi over 2 years ago

  • Regression? set to No
  • Easy fix? set to No

#28 Updated by Giovanni Manghi 7 months ago

  • Status changed from Open to Closed
  • Resolution set to end of life

Also available in: Atom PDF