Bug report #16083

problem with ON INSERT TO ... DO INSTEAD postgres rules

Added by Luca Lanteri over 1 year ago. Updated over 1 year ago.

Status:Closed
Priority:Normal
Assignee:Jürgen Fischer
Category:Data Provider/PostGIS
Affected QGIS version:2.18.2 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No

Description

Hi to all,

starting from QIS 2.16 I can't insert new feature in postgres views using rules.
When I try to insert a new feature I have this error [0]. All works fine with QGIS 2.14.
The strange things is that the error say about an "INSERT RETURNING" when I don't need any RETURING clause.
The problem happens only with INSERT rules. UPDATE and DELETE are ok.

[0]
Impossibile applicare le modifiche al vettore v_frane_pol_full

Errori: ERRORE: 1 geometria non aggiunta.
Errori della sorgente dati:
Errore PostGIS nell'aggiunta delle geometrie: ERROR: cannot perform INSERT RETURNING on relation "v_frane_pol_full"
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

_______________
To reproduce the issue:

1) create test structure

CREATE TABLE test
(
gid serial NOT NULL,
geom geometry(Point,32632)
)
WITH (
OIDS=FALSE
);
ALTER TABLE test
OWNER TO postgres;

CREATE OR REPLACE VIEW v_test AS
SELECT test.gid,
test.geom
FROM test;

ALTER TABLE v_test
OWNER TO postgres;

CREATE OR REPLACE RULE test_ins AS
ON INSERT TO v_test DO INSTEAD INSERT INTO test (gid, geom)
VALUES (new.gid, new.geom);

2) try to insert a new feature


Related issues

Related to QGIS Application - Bug report #16552: Inserting data on version views not working in 2.18 (OK i... Closed 05/15/2017

Associated revisions

Revision 3f7f95ee
Added by Jürgen Fischer about 1 year ago

db_manager versioning: add RETURNING to INSERT rule (fixes #16083)

Revision 8b263e4c
Added by Jürgen Fischer about 1 year ago

db_manager versioning: add RETURNING to INSERT rule (fixes #16083)

(cherry picked from commit 3f7f95ee262ea3646d61600c21faed0866bc70b0)

History

#1 Updated by Luca Lanteri over 1 year ago

This is the postgres log [0]:
The 2.18 query add a RETURNING clause that is non present in QGIS 2.14

With QGIS 2.18
2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS STATEMENT: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) RETURNING "oper_id"

With QGIS 2.14
2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS LOG: execute addfeatures: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc","mat_a_int","mat_gabbio","mat_cls","mat_legna","i_manuten","i_nessuna","i_prolung","i_pulizia","i_ricostru","i_sottomur","i_svuotam","so_dissest","so_interra","so_scalzat","so_sifonat","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

#2 Updated by Luca Lanteri over 1 year ago

  • Status changed from Open to Closed

After a short discussion in qgis-dev list I find out that this was a deliberate change to allow QGIS to directly use the newly inserted features (e.g. add child records to a newly created feature).
From QGIS 2.16 is mandatory to add the RETURNING clause in te INSERT RULES of view.

#3 Updated by Paolo Cavallini over 1 year ago

Please check that this is adequately described in the manual.
Thanks.

#4 Updated by Jürgen Fischer about 1 year ago

  • Related to Bug report #16552: Inserting data on version views not working in 2.18 (OK in 2.14) added

Also available in: Atom PDF