Unable to add a new feature when joining two PostGIS tables

[devfaz74]

Describe the bug

I have two Postgresql tables: parent and child, as the following:

CREATE SEQUENCE public.parent_id_seq;

CREATE TABLE public.parent
(
    id integer NOT NULL DEFAULT nextval('public.parent_id_seq'::regclass),
    type character varying COLLATE pg_catalog."default",
    CONSTRAINT parent_pkey PRIMARY KEY (id)
);

CREATE SEQUENCE public.child_id_seq;

CREATE TABLE public.child
(
    id integer NOT NULL DEFAULT nextval('public.child_id_seq'::regclass),
    name character varying COLLATE pg_catalog."default",
    parent_id integer,
    CONSTRAINT child_pkey PRIMARY KEY (id),
    CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id)
        REFERENCES public.parent (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

In QGIS I have made a join between these two tables. But I am not able to add new features from the attribute table. When I do so I got the following error:

Could not commit changes to layer child

Errors: ERROR: 1 feature(s) not added.
  
  Provider errors:
      PostGIS error while adding features: ERROR:  invalid input syntax for integer: "nextval('public.parent_id_seq'::regclass)"
    LINE 1: ...ild"("id","name","parent_id") VALUES ($1,'name 9','nextval('...

How to Reproduce
1- Create these two tables, as described above.
2- Add them to QGIS.
3- Right click on the parent table, then select properties, then from the Joins tab create a new Join as shown in the attached screen shot.
4- Then allow editing both layers.
5- After that open the attribute table of the parent layer, then select Add Feature, and fill in the type and name fields.
6- Save the parent layer, it will be saved with no problems. Then the child layer, it will generate the error mentioned above.

QGIS and OS versions

QGIS version
3.10.0-A Coruña
QGIS code revision
6ffa89e
Compiled against Qt
5.11.2
Running against Qt
5.11.2
Compiled against GDAL/OGR
3.0.2
Running against GDAL/OGR
3.0.2
Compiled against GEOS
3.8.0-CAPI-1.13.1
Running against GEOS
3.8.0-CAPI-1.13.1
Compiled against SQLite
3.29.0
Running against SQLite
3.29.0
PostgreSQL Client Version
11.5
SpatiaLite Version
4.3.0
QWT Version
6.1.3
QScintilla2 Version
2.10.8
Compiled against PROJ
6.2.1
Running against PROJ
Rel. 6.2.1, November 1st, 2019
OS Version
Windows 10 (10.0)
Active python plugins
quick_map_services;
db_manager;
MetaSearch;
processing

Running on Windows 10 machine.

[SebastienPeillet]

I reproduce the bug, this is due to the id column in the parent table (serial type).

The default values nextval('public.parent_id_seq'::regclass) is used to create the child entity but there is no update when parent entity is commited (and nextval('public.parent_id_seq'::regclass) is converted to int value during this step so the link is broken).

The solution is to activate the "Evaluate default values on provider side" option in the project properties.

Like this, a new feature in parent table will automatically get the converted value of nextval('public.parent_id_seq'::regclass) and this value will be transmitted to the child table instead of nextval('public.parent_id_seq'::regclass).

Maybe we should also activate this option by default when a join is added with the upertOnEdit option (qgsvectorlayerjoinbuffer.cpp), with the following lines :

  if ( joinInfo.hasUpsertOnEdit() )
  {
    QgsProject::instance()->setEvaluateDefaultValues( true );
  }

But I'm not sure it's the best option. If someone has an opinion on this, feel free to give it !

[gioman]

The solution is to activate the "Evaluate default values on provider side" option in the project properties.

Closing then.