From 80882acd6ba407847fed0ef308e440b88796e0e1 Mon Sep 17 00:00:00 2001 From: Guilhem Moulin Date: Wed, 23 Apr 2025 17:55:57 +0200 Subject: Change layer cache logic to target destination layers rather than sources. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit In a future commit we'll fingerprint layers to detect changes. Comparing modification times is not enough since some sources (for instance Naturvårdsverket's SCI_Rikstackande) are updated on the server even though no objects are being added; the source layer remains unchanged but the file differs because of OBJECTID changes we are not interested in. Rather than using another cache layer/table for fingerprints, we cache destination layernames rather than triplets (source_path, archive_member, layername), along with the time at which the import was started rather than source_path's mtime. There is indeed no value in having exact source_path's mtime in the cache. What we need is simply a way to detect whether source paths have been updated in a subsequent run. Thanks to the shared locks the ctime of any updated source path will be at least the time when the locks are released, thereby exceeding the last_updated value. --- schema.sql | 48 +++++++++++++++++++++++------------------------- 1 file changed, 23 insertions(+), 25 deletions(-) (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql index b4d7608..e5a1002 100644 --- a/schema.sql +++ b/schema.sql @@ -2751,32 +2751,30 @@ ALTER SEQUENCE postgis."vbk:vindkraftverk_ogc_fid_seq" OWNED BY postgis."vbk:vin -- --- Name: sourcecache; Type: TABLE; Schema: public; Owner: webmap_import +-- Name: layercache; Type: TABLE; Schema: public; Owner: webmap_import -- -CREATE TABLE public.sourcecache ( +CREATE TABLE public.layercache ( ogc_fid bigint NOT NULL, - source_path character varying(2047) NOT NULL, - archive_member character varying(2047) NOT NULL, layername character varying(255) NOT NULL, - mtime_ns bigint + last_updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL ); -ALTER TABLE public.sourcecache OWNER TO webmap_import; +ALTER TABLE public.layercache OWNER TO webmap_import; -- --- Name: TABLE sourcecache; Type: COMMENT; Schema: public; Owner: webmap_import +-- Name: TABLE layercache; Type: COMMENT; Schema: public; Owner: webmap_import -- -COMMENT ON TABLE public.sourcecache IS 'Metadata cache for sources files'; +COMMENT ON TABLE public.layercache IS 'Layer metadata cache'; -- --- Name: sourcecache_ogc_fid_seq; Type: SEQUENCE; Schema: public; Owner: webmap_import +-- Name: layercache_ogc_fid_seq; Type: SEQUENCE; Schema: public; Owner: webmap_import -- -CREATE SEQUENCE public.sourcecache_ogc_fid_seq +CREATE SEQUENCE public.layercache_ogc_fid_seq START WITH 1 INCREMENT BY 1 NO MINVALUE @@ -2784,13 +2782,13 @@ CREATE SEQUENCE public.sourcecache_ogc_fid_seq CACHE 1; -ALTER TABLE public.sourcecache_ogc_fid_seq OWNER TO webmap_import; +ALTER TABLE public.layercache_ogc_fid_seq OWNER TO webmap_import; -- --- Name: sourcecache_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: webmap_import +-- Name: layercache_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: webmap_import -- -ALTER SEQUENCE public.sourcecache_ogc_fid_seq OWNED BY public.sourcecache.ogc_fid; +ALTER SEQUENCE public.layercache_ogc_fid_seq OWNED BY public.layercache.ogc_fid; -- @@ -3165,10 +3163,10 @@ ALTER TABLE ONLY postgis."vbk:vindkraftverk" ALTER COLUMN ogc_fid SET DEFAULT ne -- --- Name: sourcecache ogc_fid; Type: DEFAULT; Schema: public; Owner: webmap_import +-- Name: layercache ogc_fid; Type: DEFAULT; Schema: public; Owner: webmap_import -- -ALTER TABLE ONLY public.sourcecache ALTER COLUMN ogc_fid SET DEFAULT nextval('public.sourcecache_ogc_fid_seq'::regclass); +ALTER TABLE ONLY public.layercache ALTER COLUMN ogc_fid SET DEFAULT nextval('public.layercache_ogc_fid_seq'::regclass); -- @@ -4044,19 +4042,19 @@ ALTER TABLE ONLY postgis."vbk:vindkraftverk" -- --- Name: sourcecache sourcecache_pkey; Type: CONSTRAINT; Schema: public; Owner: webmap_import +-- Name: layercache layercache_layername_key; Type: CONSTRAINT; Schema: public; Owner: webmap_import -- -ALTER TABLE ONLY public.sourcecache - ADD CONSTRAINT sourcecache_pkey PRIMARY KEY (ogc_fid); +ALTER TABLE ONLY public.layercache + ADD CONSTRAINT layercache_layername_key UNIQUE (layername); -- --- Name: sourcecache sourcecache_source_path_archive_member_layername_key; Type: CONSTRAINT; Schema: public; Owner: webmap_import +-- Name: layercache layercache_pkey; Type: CONSTRAINT; Schema: public; Owner: webmap_import -- -ALTER TABLE ONLY public.sourcecache - ADD CONSTRAINT sourcecache_source_path_archive_member_layername_key UNIQUE (source_path, archive_member, layername); +ALTER TABLE ONLY public.layercache + ADD CONSTRAINT layercache_pkey PRIMARY KEY (ogc_fid); -- @@ -5210,17 +5208,17 @@ GRANT SELECT,USAGE ON SEQUENCE postgis."vbk:vindkraftverk_ogc_fid_seq" TO webmap -- --- Name: TABLE sourcecache; Type: ACL; Schema: public; Owner: webmap_import +-- Name: TABLE layercache; Type: ACL; Schema: public; Owner: webmap_import -- -GRANT SELECT ON TABLE public.sourcecache TO webmap_guest; +GRANT SELECT ON TABLE public.layercache TO webmap_guest; -- --- Name: SEQUENCE sourcecache_ogc_fid_seq; Type: ACL; Schema: public; Owner: webmap_import +-- Name: SEQUENCE layercache_ogc_fid_seq; Type: ACL; Schema: public; Owner: webmap_import -- -GRANT SELECT,USAGE ON SEQUENCE public.sourcecache_ogc_fid_seq TO webmap_guest; +GRANT SELECT,USAGE ON SEQUENCE public.layercache_ogc_fid_seq TO webmap_guest; -- -- cgit v1.2.3