Bug report #16083
problem with ON INSERT TO ... DO INSTEAD postgres rules
| 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 | Copied to github as #: | 23997 |
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
Associated revisions
db_manager versioning: add RETURNING to INSERT rule (fixes #16083)
db_manager versioning: add RETURNING to INSERT rule (fixes #16083)
(cherry picked from commit 3f7f95ee262ea3646d61600c21faed0866bc70b0)
History
#1
Updated by Luca Lanteri over 7 years 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 7 years 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 7 years ago
Please check that this is adequately described in the manual.
Thanks.
#4
Updated by Jürgen Fischer almost 7 years ago
- Related to Bug report #16552: Inserting data on version views not working in 2.18 (OK in 2.14) added