elpaso
commented
10 months ago Can you share the SQL to create a table that shows the issue?
Closed bbscout closed 10 months ago
elpaso
commented
10 months ago Can you share the SQL to create a table that shows the issue?
bbscout
commented
10 months ago I have generated the SQL script using pgAdmin's backup function, which should contain the structure of the table exhibiting the issue. I'm relatively new to PostGIS, so I hope I have understood your request correctly.
Please note, there are some triggers in place related to KART data version-control systems. However, the issue persists even when these triggers are not utilized, indicating that it occurs independently of the KART system.
Here is the SQL script from a pgAdmin backup of the table in question:
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.3 (Debian 15.3-1.pgdg110+1)
-- Dumped by pg_dump version 15.3
-- Started on 2023-11-23 12:05:08 UTC
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- TOC entry 252 (class 1259 OID 38672)
-- Name: PlochyRZV_p; Type: TABLE; Schema: up; Owner: *****
--
CREATE TABLE up."PlochyRZV_p" (
fid integer NOT NULL,
"CasH" integer,
"Typ" character varying(2),
"Index" character varying(254),
geom public.geometry(MultiPolygon,5514),
"Zmena" boolean
);
ALTER TABLE up."PlochyRZV_p" OWNER TO *****;
--
-- TOC entry 251 (class 1259 OID 38671)
-- Name: PlochyRZV_p_fid_seq; Type: SEQUENCE; Schema: up; Owner: *****
--
CREATE SEQUENCE up."PlochyRZV_p_fid_seq"
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE up."PlochyRZV_p_fid_seq" OWNER TO *****;
--
-- TOC entry 4337 (class 0 OID 0)
-- Dependencies: 251
-- Name: PlochyRZV_p_fid_seq; Type: SEQUENCE OWNED BY; Schema: up; Owner: *****
--
ALTER SEQUENCE up."PlochyRZV_p_fid_seq" OWNED BY up."PlochyRZV_p".fid;
--
-- TOC entry 4176 (class 2604 OID 38675)
-- Name: PlochyRZV_p fid; Type: DEFAULT; Schema: up; Owner: *****
--
ALTER TABLE ONLY up."PlochyRZV_p" ALTER COLUMN fid SET DEFAULT nextval('up."PlochyRZV_p_fid_seq"'::regclass);
--
-- TOC entry 4179 (class 2606 OID 38679)
-- Name: PlochyRZV_p PlochyRZV_p_pkey; Type: CONSTRAINT; Schema: up; Owner: *****
--
ALTER TABLE ONLY up."PlochyRZV_p"
ADD CONSTRAINT "PlochyRZV_p_pkey" PRIMARY KEY (fid);
--
-- TOC entry 4177 (class 1259 OID 38680)
-- Name: PlochyRZV_p_idx_geom; Type: INDEX; Schema: up; Owner: *****
--
CREATE INDEX "PlochyRZV_p_idx_geom" ON up."PlochyRZV_p" USING gist (geom);
--
-- TOC entry 4180 (class 1259 OID 40837)
-- Name: idx_geom_b; Type: INDEX; Schema: up; Owner: *****
--
CREATE INDEX idx_geom_b ON up."PlochyRZV_p" USING gist (geom);
--
-- TOC entry 4181 (class 1259 OID 40831)
-- Name: idx_plochyrzv_p_geom; Type: INDEX; Schema: up; Owner: *****
--
CREATE INDEX idx_plochyrzv_p_geom ON up."PlochyRZV_p" USING gist (geom);
--
-- TOC entry 4182 (class 2620 OID 38681)
-- Name: PlochyRZV_p _kart_track_trigger; Type: TRIGGER; Schema: up; Owner: *****
--
CREATE TRIGGER _kart_track_trigger AFTER INSERT OR DELETE OR UPDATE ON up."PlochyRZV_p" FOR EACH ROW EXECUTE FUNCTION up._kart_track_proc('fid');
-- Completed on 2023-11-23 12:05:08 UTC
--
-- PostgreSQL database dump complete
--
I tested your table (without the _kart_track_trigger because there is no _kart_track_proc function).
I cannot reproduce the issue with:
bbscout
commented
4 months ago Sorry, my fault. I just found the error on my side. I had enabled constraints in Layer Properties > Attributes Form >> Constraints. Thank you for your patience, and apologies for any confusion.
What is the bug or the crash?
When working with PostGIS layers in QGIS, any field named "Index" automatically generates unexpected values such as "_8_300" or "_8_299" during certain operations like splitting or copying features. This behavior does not occur with GeoPackage layers and seems to be specific to PostGIS layers. It looks like autoincrementing PRIMARY_KEY field which my "Index" field is not.
Steps to reproduce the issue
Versions
QGIS version 3.34.0-Prizren QGIS code revision ffbdd67881 Qt version 5.15.3 Python version 3.9.5 GDAL/OGR version 3.7.2 PROJ version 9.3.0 EPSG Registry database version v10.094 (2023-08-08) GEOS version 3.12.0-CAPI-1.18.0 SQLite version 3.41.1 PDAL version 2.5.5 PostgreSQL client version 15.2 SpatiaLite version 5.1.0 QWT version 6.1.6 QScintilla2 version 2.13.4 OS version Windows 10 Version 2009
Active Python plugins cartolinegen 3.2.2 changeDataSource 3.1 citygen 0.3 czech_slovak_freegeodata-main 0.6 fgdb_to_gpkg 1.0 joinlines 0.4 joinmultiplelines Version 0.4.1 kart 1.0.13 layout_panel-main 0.3 MemoryLayerSaver 5.0.1 OSMDownloader 1.0.3 qgis2web 3.16.0 db_manager 0.1.20 grassprovider 2.12.99 MetaSearch 0.3.6 processing 2.12.99
Supported QGIS version
New profile
Additional context
The issue persists regardless of the presence of triggers or default values in the PostGIS schema, and seems to be unrelated to the naming of spatial indexes. The "Index" field does not serve as a primary key and is not related to any spatial index directly. This problem may lead to significant data integrity concerns for workflows that rely on the "Index" field remaining unchanged unless manually edited.
The data editing issue occurs prior to saving to the database, indicating a potential problem on the QGIS side rather than PostGIS. It's apparent that using the field name "Index" is not ideal, but it's mandated by Czech legislation (Decree 500/2006 Coll.). Therefore, creating workarounds to avoid this issue is complicated and impractical. The automatic modification of the "Index" field values upon editing operations in QGIS raises data integrity concerns, especially for legal and administrative processes that rely on the stability of this field as defined by the mentioned decree.