/* * Copyright (c) 2020 Huawei Technologies Co.,Ltd. * * openGauss is licensed under Mulan PSL v2. * You can use this software according to the terms and conditions of the Mulan PSL v2. * You may obtain a copy of Mulan PSL v2 at: * * http://license.coscl.org.cn/MulanPSL2 * * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND, * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT, * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE. * See the Mulan PSL v2 for more details. * ------------------------------------------------------------------------- * * prepare.sql * Prepare DB4AI.Snapshot functionality. * * IDENTIFICATION * src/gausskernel/dbmind/db4ai/snapshots/prepare.sql * * ------------------------------------------------------------------------- */ CREATE OR REPLACE FUNCTION db4ai.prepare_snapshot_internal( IN s_id BIGINT, -- snapshot id IN p_id BIGINT, -- parent id IN m_id BIGINT, -- matrix id IN r_id BIGINT, -- root id IN i_schema NAME, -- snapshot namespace IN i_name NAME, -- snapshot name IN i_commands TEXT[], -- DDL and DML commands defining snapshot modifications IN i_comment TEXT, -- snapshot description IN i_owner NAME, -- snapshot owner INOUT i_idx INT, -- index for exec_cmds INOUT i_exec_cmds TEXT[], -- DDL and DML for execution IN i_mapping NAME[] DEFAULT NULL -- mapping of user columns to backing column; generate rules if not NULL ) RETURNS RECORD LANGUAGE plpgsql SECURITY INVOKER AS $$ DECLARE command_str TEXT; -- command string for iterator e_stack_act TEXT; -- current stack for validation row_count BIGINT; -- number of rows in this snapshot BEGIN BEGIN RAISE EXCEPTION 'SECURITY_STACK_CHECK'; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS e_stack_act = PG_EXCEPTION_CONTEXT; IF CURRENT_SCHEMA = 'db4ai' THEN e_stack_act := pg_catalog.replace(e_stack_act, ' prepare_snapshot(', ' db4ai.prepare_snapshot('); e_stack_act := pg_catalog.replace(e_stack_act, ' prepare_snapshot_internal(', ' db4ai.prepare_snapshot_internal('); e_stack_act := pg_catalog.replace(e_stack_act, ' sample_snapshot(', ' db4ai.sample_snapshot('); END IF; IF e_stack_act LIKE E'referenced column: i_idx\n' 'SQL statement "SELECT (db4ai.prepare_snapshot_internal(s_id, p_id, m_id, r_id, i_schema, s_name, i_commands, i_comment,\n' ' CURRENT_USER, idx, exec_cmds)).i_idx"\n%' THEN e_stack_act := pg_catalog.substr(e_stack_act, 200); END IF; IF e_stack_act NOT SIMILAR TO 'PL/pgSQL function db4ai.prepare_snapshot\(name,name,text\[\],name,text\) line (209|575|641|748) at assignment%' AND e_stack_act NOT LIKE 'PL/pgSQL function db4ai.sample_snapshot(name,name,name[],numeric[],name[],text[]) line 264 at IF%' THEN RAISE EXCEPTION 'direct call to db4ai.prepare_snapshot_internal(bigint,bigint,bigint,bigint,name,name,text[],text,name,' 'int,text[],name[]) is not allowed' USING HINT = 'call public interface db4ai.prepare_snapshot instead'; END IF; END; --generate rules from the mapping IF i_mapping IS NOT NULL THEN DECLARE sel_view TEXT := 'CREATE OR REPLACE VIEW db4ai.v' || s_id::TEXT || ' WITH(security_barrier) AS SELECT '; ins_grnt TEXT := 'GRANT INSERT ('; ins_rule TEXT := 'CREATE OR REPLACE RULE _INSERT AS ON INSERT TO db4ai.v' || s_id::TEXT || ' DO INSTEAD INSERT INTO ' 'db4ai.t' || coalesce(m_id, s_id)::TEXT || '('; ins_vals TEXT := ' VALUES ('; upd_grnt TEXT; upd_rule TEXT; dist_key NAME[] := pg_catalog.array_agg(coalesce(m[1], pg_catalog.replace(m[2], '""', '"'))) FROM pg_catalog.regexp_matches( pg_catalog.getdistributekey('db4ai.t' || (coalesce(m_id, p_id))::TEXT),'([^\s",]+)|"((?:[^"]*"")*[^"]*)"', 'g') m; BEGIN FOR idx IN 3 .. pg_catalog.array_length(i_mapping, 1) BY 3 LOOP IF idx = 3 THEN ins_grnt := ins_grnt || pg_catalog.quote_ident(i_mapping[idx]); ins_rule := ins_rule || coalesce(i_mapping[idx-2], i_mapping[idx-1])::TEXT; ins_vals := ins_vals || 'new.' || pg_catalog.quote_ident(i_mapping[idx]); ELSE sel_view := sel_view || ', '; ins_grnt := ins_grnt || ', ' || pg_catalog.quote_ident(i_mapping[idx]); ins_rule := ins_rule || ', ' || coalesce(i_mapping[idx-2], i_mapping[idx-1]); ins_vals := ins_vals || ', ' || 'new.' || pg_catalog.quote_ident(i_mapping[idx]); END IF; IF i_mapping[idx-2] IS NULL THEN -- handle shared columns without private (only CSS) sel_view := sel_view || i_mapping[idx-1]; ELSE IF i_mapping[idx-1] IS NULL THEN -- handle sole private column (all MSS and added CSS columns) sel_view := sel_view || i_mapping[idx-2]; ELSE -- handle shadowing (CSS CASE) sel_view := sel_view || 'coalesce(' || i_mapping[idx-2] || ', ' || i_mapping[idx-1] || ')'; END IF; IF dist_key IS NULL OR NOT i_mapping[idx-2] = ANY(dist_key) THEN -- no updates on DISTRIBUTE BY columns upd_grnt := CASE WHEN upd_grnt IS NULL -- grant update only on private column THEN 'GRANT UPDATE (' ELSE upd_grnt ||', ' END || pg_catalog.quote_ident(i_mapping[idx]); upd_rule := CASE WHEN upd_rule IS NULL -- update only private column THEN 'CREATE OR REPLACE RULE _UPDATE AS ON UPDATE TO db4ai.v' || s_id::TEXT || ' DO INSTEAD UPDATE db4ai.t' || coalesce(m_id, s_id)::TEXT || ' SET ' ELSE upd_rule || ', ' END || i_mapping[idx-2] || '=new.' || pg_catalog.quote_ident(i_mapping[idx]); -- update private column END IF; END IF; sel_view := sel_view || ' AS ' || pg_catalog.quote_ident(i_mapping[idx]); END LOOP; i_exec_cmds := i_exec_cmds || ARRAY [ [ 'O', sel_view || ', xc_node_id, ctid FROM db4ai.t' || coalesce(m_id, s_id)::TEXT || CASE WHEN m_id IS NULL THEN '' ELSE ' WHERE _' || s_id::TEXT END ], [ 'O', 'GRANT SELECT, DELETE ON db4ai.v' || s_id::TEXT || ' TO "' || i_owner || '"'], [ 'O', ins_grnt || ') ON db4ai.v' || s_id::TEXT || ' TO "' || i_owner || '"'], [ 'O', ins_rule || CASE WHEN m_id IS NULL THEN ')' ELSE ', _' || s_id::TEXT || ')' END || ins_vals || CASE WHEN m_id IS NULL THEN ')' ELSE ', TRUE)' END ], [ 'O', 'CREATE OR REPLACE RULE _DELETE AS ON DELETE TO db4ai.v' || s_id::TEXT || ' DO INSTEAD ' || CASE WHEN m_id IS NULL THEN 'DELETE FROM db4ai.t' || s_id::TEXT ELSE 'UPDATE db4ai.t' || m_id::TEXT || ' SET _' || s_id::TEXT || '=FALSE' END || ' WHERE t' || coalesce(m_id, s_id)::TEXT || '.xc_node_id=old.xc_node_id AND t' || coalesce(m_id, s_id)::TEXT || '.ctid=old.ctid' ] ]; IF upd_rule IS NOT NULL THEN i_exec_cmds := i_exec_cmds || ARRAY [ [ 'O', upd_grnt || ') ON db4ai.v' || s_id::TEXT || ' TO "' || i_owner || '"'], [ 'O', upd_rule || ' WHERE t' || coalesce(m_id, s_id)::TEXT || '.xc_node_id=old.xc_node_id AND t' || coalesce(m_id, s_id)::TEXT || '.ctid=old.ctid' ]]; END IF; RETURN; END; END IF; -- Execute the queries LOOP EXIT WHEN i_idx = 1 + pg_catalog.array_length(i_exec_cmds, 1); CASE i_exec_cmds[i_idx][1] WHEN 'O' THEN -- RAISE NOTICE 'owner executing: %', i_exec_cmds[i_idx][2]; EXECUTE i_exec_cmds[i_idx][2]; i_idx := i_idx + 1; WHEN 'U' THEN RETURN; ELSE -- this should never happen RAISE EXCEPTION 'prepare snapshot internal error2: % %', idx, i_exec_cmds[idx]; END CASE; END LOOP; EXECUTE 'DROP RULE IF EXISTS _INSERT ON db4ai.v' || s_id::TEXT; EXECUTE 'DROP RULE IF EXISTS _UPDATE ON db4ai.v' || s_id::TEXT; EXECUTE 'DROP RULE IF EXISTS _DELETE ON db4ai.v' || s_id::TEXT; EXECUTE 'COMMENT ON VIEW db4ai.v' || s_id::TEXT || ' IS ''snapshot ' || pg_catalog.quote_ident(i_schema) || '.' || pg_catalog.quote_ident(i_name) || ' backed by db4ai.t' || coalesce(m_id, s_id)::TEXT || CASE WHEN pg_catalog.length(i_comment) > 0 THEN ' comment is "' || i_comment || '"' ELSE '' END || ''''; EXECUTE 'REVOKE ALL PRIVILEGES ON db4ai.v' || s_id::TEXT || ' FROM "' || i_owner || '"'; EXECUTE 'GRANT SELECT ON db4ai.v' || s_id::TEXT || ' TO "' || i_owner || '" WITH GRANT OPTION'; EXECUTE 'SELECT COUNT(*) FROM db4ai.v' || s_id::TEXT INTO STRICT row_count; INSERT INTO db4ai.snapshot (id, parent_id, matrix_id, root_id, schema, name, owner, commands, comment, row_count) VALUES (s_id, p_id, m_id, r_id, i_schema, i_name, '"' || i_owner || '"', i_commands, i_comment, row_count); END; $$; CREATE OR REPLACE FUNCTION db4ai.prepare_snapshot( IN i_schema NAME, -- snapshot namespace, default is CURRENT_USER or PUBLIC IN i_parent NAME, -- parent snapshot name IN i_commands TEXT[], -- DDL and DML commands defining snapshot modifications IN i_vers NAME DEFAULT NULL, -- override version postfix IN i_comment TEXT DEFAULT NULL -- description of this unit of data curation ) RETURNS db4ai.snapshot_name LANGUAGE plpgsql SECURITY INVOKER SET client_min_messages TO ERROR AS $$ DECLARE s_id BIGINT; -- snapshot id r_id BIGINT; -- root id m_id BIGINT; -- matrix id p_id BIGINT; -- parent id c_id BIGINT; -- column id for backing table s_name NAME; -- current snapshot name s_mode VARCHAR(3); -- current snapshot mode s_vers_del CHAR; -- snapshot version delimiter, default '@' s_vers_sep CHAR; -- snapshot version separator, default '.' s_uv_proj TEXT; -- snapshot user view projection list p_name_vers TEXT[]; -- split full parent name into name and version p_sv_proj TEXT; -- parent snapshot system view projection list current_compatibility_mode TEXT; -- current compatibility mode none_represent INT; -- 0 or NULL command_str TEXT; -- command string for iterator pattern TEXT; -- command pattern for matching ops_arr BOOLEAN[]; -- operation classes in i_commands ops_str TEXT[] := '{ALTER, INSERT, DELETE, UPDATE}'; -- operation classes as string ALTER_OP INT := 1; -- ALTER operation class INSERT_OP INT := 2; -- INSERT operation class DELETE_OP INT := 3; -- DELETE operation class UPDATE_OP INT := 4; -- UPDATE operation class adminuser BOOLEAN; -- current user privileges vers_arr INT[]; -- split version digits exec_cmds TEXT[]; -- commands for execution qual_name TEXT; -- qualified snapshot name ALTER_CLAUSE INT := 1; -- ALTER clause class WHERE_CLAUSE INT := 2; -- WHERE clause class - for insert, update and delete FROM_CLAUSE INT := 3; -- FROM clause class - for insert, update and delete (as USING) SET_CLAUSE INT := 4; -- SET clause class - for updates and insert -- (as generic SQL: projection, list, VALUES, ...) AS_CLAUSE INT := 5; -- AS clause class - for delete and update -- (correlation name) - default is "snapshot" current_op INT; -- currently parsed operation class next_op INT; -- following operation class current_clauses TEXT[]; -- clauses for current operation next_clauses TEXT[]; -- clauses for next operation mapping NAME[]; -- mapping user column names to backing column names newmap BOOLEAN := FALSE; -- mapping has changed res db4ai.snapshot_name; -- composite result BEGIN -- obtain active message level BEGIN EXECUTE 'SET LOCAL client_min_messages TO ' || pg_catalog.quote_ident(pg_catalog.current_setting('db4ai.message_level')); RAISE INFO 'effective client_min_messages is %', pg_catalog.upper(pg_catalog.current_setting('db4ai.message_level')); EXCEPTION WHEN OTHERS THEN END; BEGIN EXECUTE 'SELECT rolsystemadmin FROM pg_roles WHERE rolname=CURRENT_USER' INTO STRICT adminuser; IF adminuser IS FALSE THEN RAISE EXCEPTION 'In the current version, the DB4AI.SNAPSHOT feature is available only to administrators.'; END IF; END; -- obtain active snapshot mode BEGIN s_mode := pg_catalog.upper(pg_catalog.current_setting('db4ai_snapshot_mode')); EXCEPTION WHEN OTHERS THEN s_mode := 'MSS'; END; IF s_mode NOT IN ('CSS', 'MSS') THEN RAISE EXCEPTION 'invalid snapshot mode: ''%''', s_mode; END IF; -- obtain relevant configuration parameters BEGIN s_vers_del := pg_catalog.upper(pg_catalog.current_setting('db4ai_snapshot_version_delimiter')); EXCEPTION WHEN OTHERS THEN s_vers_del := '@'; END; BEGIN s_vers_sep := pg_catalog.upper(pg_catalog.current_setting('db4ai_snapshot_version_separator')); EXCEPTION WHEN OTHERS THEN s_vers_sep := '.'; END; current_compatibility_mode := pg_catalog.current_setting('sql_compatibility'); IF current_compatibility_mode = 'ORA' OR current_compatibility_mode = 'A' THEN none_represent := 0; ELSE none_represent := NULL; END IF; -- check all input parameters IF i_schema IS NULL OR i_schema = '' THEN i_schema := CASE WHEN (SELECT 0=COUNT(*) FROM pg_catalog.pg_namespace WHERE nspname = CURRENT_USER) THEN 'public' ELSE CURRENT_USER END; END IF; IF i_schema LIKE ANY(ARRAY['%;%', '%[%', '%]%', '%,%', '%(%', '%)%']) THEN RAISE EXCEPTION 'Please use specification input: schema name'; END IF; IF i_parent LIKE ANY(ARRAY['%;%', '%[%', '%]%', '%,%', '%(%', '%)%']) THEN RAISE EXCEPTION 'Please use specification input: parent snapshot name'; END IF; IF i_vers LIKE ANY(ARRAY['%;%', '%[%', '%]%', '%,%']) THEN RAISE EXCEPTION 'Please use specification input: override version postfix'; END IF; IF i_comment LIKE ANY(ARRAY['%;%', '%,%']) THEN RAISE EXCEPTION 'Please use specification input: comment(snapshot description)'; END IF; IF i_parent IS NULL OR i_parent = '' THEN RAISE EXCEPTION 'i_parent cannot be NULL or empty'; ELSE i_parent := pg_catalog.replace(i_parent, pg_catalog.chr(1), s_vers_del); i_parent := pg_catalog.replace(i_parent, pg_catalog.chr(2), s_vers_sep); p_name_vers := pg_catalog.regexp_split_to_array(i_parent, s_vers_del); IF pg_catalog.array_length(p_name_vers, 1) <> 2 OR pg_catalog.array_length(p_name_vers, 2) <> none_represent THEN RAISE EXCEPTION 'i_parent must contain exactly one ''%'' character', s_vers_del USING HINT = 'reference a snapshot using the format: snapshot_name' || s_vers_del || 'version'; END IF; END IF; -- check if parent exists BEGIN SELECT id, matrix_id, root_id FROM db4ai.snapshot WHERE schema = i_schema AND name = i_parent INTO STRICT p_id, m_id, r_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'parent snapshot %.% does not exist' , pg_catalog.quote_ident(i_schema), pg_catalog.quote_ident(i_parent); END; --SELECT nextval('db4ai.snapshot_sequence') ==> -1 at first time fetch SELECT nextval('db4ai.snapshot_sequence') + 1 INTO STRICT s_id; -- extract highest used c_id from existing backing table or parent () -- cannot use information_schema here, because the current user has no read permission on the backing table SELECT 1 + pg_catalog.max(pg_catalog.ltrim(attname, 'f')::BIGINT) FROM pg_catalog.pg_attribute INTO STRICT c_id WHERE attrelid = ('db4ai.t' || coalesce(m_id, p_id)::TEXT)::regclass AND attnum > 0 AND NOT attisdropped AND attname like 'f%'; IF c_id IS NULL THEN RAISE EXCEPTION 'prepare snapshot internal error3: %', coalesce(m_id, p_id); END IF; IF i_commands IS NULL OR pg_catalog.array_length(i_commands, 1) = none_represent OR pg_catalog.array_length(i_commands, 2) <> none_represent THEN RAISE EXCEPTION 'i_commands array malformed' USING HINT = 'pass SQL DML and DDL operations as TEXT[] literal, e.g. ''{ALTER, ADD a int, DROP c, DELETE, ' 'WHERE b=5, INSERT, FROM t, UPDATE, FROM t, SET x=y, SET z=f(z), WHERE t.u=v}'''; END IF; -- extract normalized projection list p_sv_proj := pg_catalog.substring(pg_catalog.pg_get_viewdef('db4ai.v' || p_id::TEXT), '^SELECT (.*), t[0-9]+\.xc_node_id, t[0-9]+\.ctid FROM.*$'); mapping := array(SELECT pg_catalog.unnest(ARRAY[ m[1], m[2], coalesce(m[3], pg_catalog.replace(m[4],'""','"'))]) FROM pg_catalog.regexp_matches(p_sv_proj, CASE s_mode WHEN 'CSS' -- inherited CSS columns are shared (private: nullable, shared: not null, user_cname: not null) THEN '(?:COALESCE\(t[0-9]+\.(f[0-9]+), )?t[0-9]+\.(f[0-9]+)(?:\))? AS (?:([^\s",]+)|"((?:[^"]*"")*[^"]*)")' -- all MSS columns are private (privte: not null, shared: nullable, user_cname: not null) ELSE '(?:COALESCE\()?t[0-9]+\.(f[0-9]+)(?:, t[0-9]+\.(f[0-9]+)\))? AS (?:([^\s",]+)|"((?:[^"]*"")*[^"]*)")' END, 'g') m); -- In principle two MSS naming conventions are possible: -- (a) plain column names for MSS, allowing direct operations, but only with CompactSQL, not with TrueSQL. Conversion to CSS -- then needs to rename user columns (if they are in f[0-9]+) or simply add columns max fXX + 1 -- (b) translated columns names for MSS and CSS. Simple MSS->CSS conversion. No direct operations, always using rewrite. -- This is the more general approach! -- The need for rewriting using rules: -- UPDATE SET AS T SET T.x=y, "T.x"=y, “_$%_\\’”=NULL, T.z=DEFAULT, (a, b, T.c) = (SELECT 1 a, 2 b, 3 c) -- FROM H AS I, J as K -- WHERE _x_=5 AND _T.z_=5 AND v='T.z' AND (SELECT x, T.z FROM A as T) -- unqualified, ambiguous, quoted, string literals, ... in SET clause maybe still manageable but in WHERE -- no guarantee for correctness possible -> need to use system's SQL parser with rewrite rules! */ -- create / upgrade + prepare target snapshots for SQL DML/DDL operations IF s_mode = 'MSS' THEN DECLARE s_bt_proj TEXT; -- snapshot backing table projection list s_bt_dist TEXT; -- DISTRIBUTE BY clause for creating backing table BEGIN s_bt_proj := ''; FOR idx IN 3 .. pg_catalog.array_length(mapping, 1) BY 3 LOOP s_bt_proj := s_bt_proj || pg_catalog.quote_ident(mapping[idx]) || ' AS ' || mapping[idx-2] || ','; END LOOP; s_bt_dist := pg_catalog.getdistributekey('db4ai.t' || coalesce(m_id, p_id)::TEXT); s_bt_dist := CASE WHEN s_bt_dist IS NULL THEN ' DISTRIBUTE BY REPLICATION' ELSE ' DISTRIBUTE BY HASH(' || s_bt_dist || ')' END; s_bt_dist := ''; -- we silently drop DISTRIBUTE_BY exec_cmds := ARRAY [ [ 'O', 'CREATE TABLE db4ai.t' || s_id::TEXT || ' WITH (orientation = column, compression = low)' -- extract and propagate DISTRIBUTE BY from parent || s_bt_dist || ' AS SELECT ' || pg_catalog.rtrim(s_bt_proj, ',') || ' FROM db4ai.v' || p_id::TEXT ]]; END; ELSIF s_mode = 'CSS' THEN IF m_id IS NULL THEN exec_cmds := ARRAY [ [ 'O', 'UPDATE db4ai.snapshot SET matrix_id = ' || p_id::TEXT || ' WHERE schema = ''' || i_schema || ''' AND name = ''' || i_parent || '''' ], [ 'O', 'ALTER TABLE db4ai.t' || p_id::TEXT || ' ADD _' || p_id::TEXT || ' BOOLEAN NOT NULL DEFAULT TRUE' ], [ 'O', 'ALTER TABLE db4ai.t' || p_id::TEXT || ' ALTER _' || p_id::TEXT || ' SET DEFAULT FALSE' ], [ 'O', 'CREATE OR REPLACE VIEW db4ai.v' || p_id::TEXT || ' WITH(security_barrier) AS SELECT ' || p_sv_proj || ', xc_node_id, ctid FROM db4ai.t' || p_id::TEXT || ' WHERE _' || p_id::TEXT ]]; m_id := p_id; END IF; exec_cmds := exec_cmds || ARRAY [ [ 'O', 'ALTER TABLE db4ai.t' || m_id::TEXT || ' ADD _' || s_id::TEXT || ' BOOLEAN NOT NULL DEFAULT FALSE' ], [ 'O', 'UPDATE db4ai.t' || m_id::TEXT || ' SET _' || s_id::TEXT || ' = TRUE WHERE _' || p_id::TEXT ]]; END IF; -- generate and append grant, create view and rewrite rules for new snapshot exec_cmds := (db4ai.prepare_snapshot_internal(s_id, p_id, m_id, r_id, i_schema, s_name, i_commands, i_comment, CURRENT_USER, NULL, exec_cmds, mapping)).i_exec_cmds; FOREACH command_str IN ARRAY i_commands LOOP IF command_str IS NULL THEN RAISE EXCEPTION 'i_commands array contains NULL values'; END IF; END LOOP; -- apply SQL DML/DDL according to snapshot mode FOREACH command_str IN ARRAY (i_commands || ARRAY[NULL] ) LOOP command_str := pg_catalog.btrim(command_str); pattern := pg_catalog.upper(pg_catalog.regexp_replace(pg_catalog.left(command_str, 30), '\s+', ' ', 'g')); IF pattern is NULL THEN next_op := NULL; ELSIF pattern = 'ALTER' THEN -- ALTER keyword is optional next_op := ALTER_OP; ELSIF pattern = 'DELETE' THEN next_op := DELETE_OP; ELSIF pattern = 'INSERT' THEN next_op := INSERT_OP; ELSIF pattern = 'UPDATE' THEN next_op := UPDATE_OP; ELSIF pg_catalog.left(pattern, 7) = 'DELETE ' THEN next_op := DELETE_OP; SELECT coalesce(m[1], m[2]), m [3] FROM pg_catalog.regexp_matches(command_str, '^\s*DELETE\s+FROM\s*(?: snapshot |"snapshot")\s*(?:AS\s*(?: ([^\s"]+) |"((?:[^"]*"")*[^"]*)")\s*)?(.*)\s*$', 'i') m INTO next_clauses[AS_CLAUSE], next_clauses[FROM_CLAUSE]; RAISE NOTICE E'XXX DELETE \n%\n%', command_str, pg_catalog.array_to_string(next_clauses, E'\n'); ELSIF pg_catalog.left(pattern, 7) = 'INSERT ' THEN next_op := INSERT_OP; SELECT coalesce(m[1], m[2]), m [3] FROM pg_catalog.regexp_matches(command_str, '^\s*INSERT\s+INTO\s*(?: snapshot |"snapshot")\s*(.*)\s*$', 'i') m INTO STRICT next_clauses[SET_CLAUSE]; RAISE NOTICE E'XXX INSERT \n%\n%', command_str, pg_catalog.array_to_string(next_clauses, E'\n'); ELSIF pg_catalog.left(pattern, 7) = 'UPDATE ' THEN next_op := UPDATE_OP; SELECT coalesce(m[1], m[2]), m [3] FROM pg_catalog.regexp_matches(command_str, '^\s*UPDATE\s*(?: snapshot |"snapshot")\s*(?:AS\s*(?: ([^\s"]+) |"((?:[^"]*"")*[^"]*)")\s*)?(.*)\s*$', 'i') m INTO STRICT next_clauses[AS_CLAUSE], next_clauses[SET_CLAUSE]; DECLARE nested INT := 0; -- level of nesting quoted BOOLEAN := FALSE; -- inside quoted identifier cur_ch VARCHAR; -- current character in tokenizer idx INTEGER := 0; -- loop counter, cannot use FOR .. iterator start_pos INTEGER := 1; stmt TEXT := next_clauses[SET_CLAUSE]; BEGIN -- BEGIN splitter code for testing pattern := ''; LOOP idx := idx + 1; cur_ch := pg_catalog.substr(stmt, idx, 1); EXIT WHEN cur_ch IS NULL OR (ascii(cur_ch) <> ascii(' ') AND cur_ch = ''); CASE cur_ch WHEN '"' THEN IF quoted AND pg_catalog.substr(stmt, idx + 1, 1) = '"' THEN idx := idx + 1; ELSE quoted := NOT quoted; END IF; IF quoted THEN CONTINUE; END IF; WHEN '(' THEN nested := nested + 1; CONTINUE; WHEN ')' THEN nested := nested - 1; IF nested < 0 THEN RAISE EXCEPTION 'syntax error at or near '')'' in ''%'' at position ''%''', stmt, idx; END IF; CONTINUE; WHEN ' ' THEN IF quoted OR nested > 0 THEN CONTINUE; ELSIF pattern IS NULL OR pg_catalog.length(pattern) = 0 THEN start_pos := idx; CONTINUE; END IF; ELSE pattern := pattern || pg_catalog.upper(cur_ch); CONTINUE; END CASE; -- END splitter code for testing IF pattern IN ('FROM', 'WHERE') THEN next_clauses[FROM_CLAUSE] := pg_catalog.substr(next_clauses[SET_CLAUSE], start_pos + 1); next_clauses[SET_CLAUSE] := pg_catalog.left(next_clauses[SET_CLAUSE], start_pos - 1); EXIT; END IF; pattern := ''; start_pos := idx; END LOOP; END; RAISE NOTICE E'XXX UPDATE \n%\n%', command_str, pg_catalog.array_to_string(next_clauses, E'\n'); ELSIF pg_catalog.left(pattern, 6) = 'ALTER ' THEN SELECT coalesce(m[1], m[2]), m [3] FROM pg_catalog.regexp_matches(command_str, '^\s*ALTER\s+TABLE\s*(?: snapshot |"snapshot")\s*(.*)\s*$', 'i') m INTO STRICT next_clauses[ALTER_CLAUSE]; RAISE NOTICE E'XXX ALTER \n%\n%', command_str, pg_catalog.array_to_string(next_clauses, E'\n'); IF current_op IS NULL OR current_clauses[ALTER_CLAUSE] IS NULL THEN next_op := ALTER_OP; ELSE current_clauses[ALTER_CLAUSE] := current_clauses[ALTER_CLAUSE] || ', ' || next_clauses[ALTER_CLAUSE]; next_clauses[ALTER_CLAUSE] := NULL; END IF; ELSIF pg_catalog.left(pattern, 4) = 'ADD ' OR pg_catalog.left(pattern, 5) = 'DROP ' THEN --for chaining, conflicting ALTER ops must be avoided by user IF current_op IS NULL OR current_op <> ALTER_OP THEN next_op := ALTER_OP; -- ALTER keyword is optional next_clauses[ALTER_CLAUSE] := command_str; ELSIF current_clauses[ALTER_CLAUSE] IS NULL THEN current_clauses[ALTER_CLAUSE] := command_str; CONTINUE; -- allow chaining of ALTER ops ELSE current_clauses[ALTER_CLAUSE] := current_clauses[ALTER_CLAUSE] || ', ' || command_str; CONTINUE; -- allow chaining of ALTER ops END IF; ELSIF pg_catalog.left(pattern, 6) = 'WHERE ' THEN IF current_op IS NULL THEN RAISE EXCEPTION 'missing INSERT / UPDATE / DELETE keyword before WHERE clause in i_commands at: ''%''', command_str; ELSIF current_op NOT IN (INSERT_OP, UPDATE_OP, DELETE_OP) THEN RAISE EXCEPTION 'illegal WHERE clause in % at: ''%''', ops_str[current_op], command_str; ELSIF current_clauses[WHERE_CLAUSE] IS NULL THEN current_clauses[WHERE_CLAUSE] := command_str; ELSE RAISE EXCEPTION 'multiple WHERE clauses in % at: ''%''', ops_str[current_op], command_str; END IF; CONTINUE; ELSIF pg_catalog.left(pattern, 5) = 'FROM ' THEN IF current_op IS NULL THEN RAISE EXCEPTION 'missing INSERT / UPDATE keyword before FROM clause in i_commands at: ''%''', command_str; ELSIF current_op NOT IN (INSERT_OP, UPDATE_OP) THEN RAISE EXCEPTION 'illegal FROM clause in % at: ''%''', ops_str[current_op], command_str; ELSIF current_clauses[FROM_CLAUSE] IS NULL THEN current_clauses[FROM_CLAUSE] := command_str; ELSE RAISE EXCEPTION 'multiple FROM clauses in % at: ''%''', ops_str[current_op], command_str; END IF; CONTINUE; ELSIF pg_catalog.left(pattern, 6) = 'USING ' THEN IF current_op IS NULL THEN RAISE EXCEPTION 'missing DELETE keyword before USING clause in i_commands at: ''%''', command_str; ELSIF current_op NOT IN (DELETE_OP) THEN RAISE EXCEPTION 'illegal USING clause in % at: ''%''', ops_str[current_op], command_str; ELSIF current_clauses[FROM_CLAUSE] IS NULL THEN current_clauses[FROM_CLAUSE] := command_str; ELSE RAISE EXCEPTION 'multiple USING clauses in DELETE at: ''%''', command_str; END IF; CONTINUE; ELSIF pg_catalog.left(pattern, 4) = 'SET ' THEN IF current_op IS NULL THEN RAISE EXCEPTION 'missing UPDATE keyword before SET clause in i_commands at: ''%''', command_str; ELSIF current_op NOT IN (UPDATE_OP) THEN RAISE EXCEPTION 'illegal SET clause in % at: ''%''', ops_str[current_op], command_str; --for chaining, conflicting assignments must be avoided by user ELSE -- allow chaining of SET current_clauses[SET_CLAUSE] := CASE WHEN current_clauses[SET_CLAUSE] IS NULL THEN command_str ELSE current_clauses[SET_CLAUSE] || ' ' || command_str END; END IF; CONTINUE; ELSIF pg_catalog.left(pattern, 3) = 'AS ' THEN IF current_op IS NULL THEN RAISE EXCEPTION 'missing UPDATE / DELETE keyword before AS clause in i_commands at: ''%''', command_str; ELSIF current_op NOT IN (UPDATE_OP, DELETE_OP) THEN RAISE EXCEPTION 'illegal AS clause in % at: ''%''', ops_str[current_op], command_str; ELSIF current_clauses[AS_CLAUSE] IS NULL THEN DECLARE as_pos INT := 3 + pg_catalog.strpos(pg_catalog.upper(command_str), 'AS '); BEGIN current_clauses[AS_CLAUSE] := pg_catalog.ltrim(pg_catalog.substr(command_str, as_pos)); END; ELSE RAISE EXCEPTION 'multiple AS clauses in % at: ''%''', ops_str[current_op], command_str; END IF; CONTINUE; ELSE -- generic SQL allowed only in INSERT IF current_op IS NULL THEN RAISE EXCEPTION 'missing ALTER / INSERT / UPDATE / DELETE keyword before SQL clause: ''%''', command_str; ELSIF current_op NOT IN (INSERT_OP) THEN RAISE EXCEPTION 'illegal SQL clause in % at: ''%''', ops_str[current_op], command_str; ELSE -- allow chaining of generic SQL for PROJ LIST, VALUES, ... current_clauses[SET_CLAUSE] := CASE WHEN current_clauses[SET_CLAUSE] IS NULL THEN command_str ELSE current_clauses[SET_CLAUSE] || ' ' || command_str END; END IF; CONTINUE; END IF; IF current_op IS NOT NULL THEN IF current_clauses IS NULL AND current_op NOT IN (DELETE_OP) THEN RAISE EXCEPTION 'missing auxiliary clauses in %', CASE WHEN command_str IS NULL THEN ops_str[current_op] ELSE ops_str[current_op] || ' before: ''' || command_str || '''' END; END IF; IF current_clauses[AS_CLAUSE] IS NULL THEN current_clauses[AS_CLAUSE] := 'snapshot'; END IF; ops_arr[current_op] := TRUE; IF current_op = ALTER_OP THEN command_str := NULL; -- stores DDL statement for adding / dropping columns DECLARE dropif TEXT := NULL; -- drop if exists expect BOOLEAN := TRUE; -- expect keyword alt_op VARCHAR(4); -- alter operation: NULL or 'ADD' or 'DROP' quoted BOOLEAN := FALSE; -- inside quoted identifier cur_ch VARCHAR; -- current character in tokenizer idx INTEGER := 0; -- loop counter, cannot use FOR .. iterator tokens TEXT := current_clauses[ALTER_CLAUSE] || ','; BEGIN -- BEGIN tokenizer code for testing pattern := ''; LOOP idx := idx + 1; cur_ch := pg_catalog.substr(tokens, idx, 1); EXIT WHEN cur_ch IS NULL OR (ascii(cur_ch) <> ascii(' ') AND cur_ch = ''); CASE cur_ch WHEN '"' THEN IF quoted AND pg_catalog.substr(tokens, idx + 1, 1) = '"' THEN pattern := pattern || '"'; idx := idx + 1; ELSE quoted := NOT quoted; END IF; IF quoted THEN CONTINUE; END IF; WHEN ',' THEN IF quoted THEN pattern := pattern || cur_ch; CONTINUE; ELSIF pattern IS NULL OR pg_catalog.length(pattern) = 0 THEN pattern := ','; ELSE idx := idx - 1; -- reset on comma for next loop END IF; WHEN ' ', E'\n', E'\t' THEN IF quoted THEN pattern := pattern || cur_ch; CONTINUE; ELSIF pattern IS NULL OR pg_catalog.length(pattern) = 0 THEN CONTINUE; END IF; ELSE pattern := pattern || CASE WHEN quoted THEN cur_ch::TEXT ELSE pg_catalog.lower(cur_ch) END; CONTINUE; END CASE; -- END tokenizer code for testing IF alt_op = 'DROP' AND pg_catalog.upper(dropif) = 'IF' THEN IF pattern = ',' THEN pattern := dropif; -- interpret 'if' as column name (not a keyword) idx := idx - 1; -- reset on comma for next loop ELSIF pg_catalog.upper(pattern) <> 'EXISTS' THEN RAISE EXCEPTION 'expected EXISTS keyword in % operation after ''%'' in: ''%''', alt_op, dropif, current_clauses[ALTER_CLAUSE]; END IF; END IF; IF expect THEN IF pg_catalog.upper(pattern) IN ('ADD', 'DROP') THEN IF alt_op IS NULL THEN alt_op := pg_catalog.upper(pattern); expect := FALSE; ELSE RAISE EXCEPTION 'unable to extract column name in % operation: ''%''', alt_op, current_clauses[ALTER_CLAUSE]; END IF; ELSE RAISE EXCEPTION 'expected ADD or DROP keyword before ''%'' in: ''%''', pattern, current_clauses[ALTER_CLAUSE] USING HINT = 'currently only ADD and DROP supported'; END IF; ELSIF pattern = ',' THEN expect := TRUE; -- allow chaining of ALTER ops ELSIF alt_op IS NULL THEN -- accept all possibly legal text following column name -- leave exact syntax check to SQL compiler IF command_str IS NOT NULL THEN command_str := command_str || ' ' || pattern; END IF; ELSIF pg_catalog.upper(pattern) = 'COLUMN' THEN -- skip keyword COLUMN between ADD/DROP and column name ELSIF alt_op = 'DROP' AND pg_catalog.upper(pattern) = 'IF' AND dropif IS NULL THEN dropif := pattern; -- 'IF' is not a keyword ELSIF alt_op = 'DROP' AND pg_catalog.upper(pattern) = 'EXISTS' AND pg_catalog.upper(dropif) = 'IF' THEN dropif := pattern; -- 'EXISTS' is not a keyword ELSIF alt_op IN ('ADD', 'DROP') THEN -- attempt to map the pattern FOR idx IN 3 .. pg_catalog.array_length(mapping, 1) BY 3 LOOP IF pattern = mapping[idx] THEN IF alt_op = 'ADD' THEN -- check if pattern was mapped to an existing column RAISE EXCEPTION 'column "%" already exists in current snapshot', pattern; ELSIF alt_op = 'DROP' THEN -- DROP a private column (MSS and CSS) IF mapping[idx-2] IS NOT NULL THEN command_str := CASE WHEN command_str IS NULL THEN 'ALTER TABLE db4ai.t' || coalesce(m_id, s_id)::TEXT ELSE command_str || ',' END || ' DROP ' || mapping[idx-2]::TEXT; END IF; mapping := mapping[1:(idx-3)] || mapping[idx+1:(pg_catalog.array_length(mapping, 1))]; newmap := TRUE; alt_op := NULL; EXIT; END IF; END IF; END LOOP; -- apply the mapping IF alt_op = 'ADD' THEN -- ADD a private column (MSS and CSS) command_str := CASE WHEN command_str IS NULL THEN 'ALTER TABLE db4ai.t' || coalesce(m_id, s_id)::TEXT ELSE command_str || ',' END || ' ADD f' || c_id::TEXT; mapping := mapping || ARRAY [ 'f' || c_id::TEXT, NULL, pattern ]::NAME[]; newmap := TRUE; c_id := c_id + 1; ELSIF alt_op = 'DROP' THEN -- check whether pattern needs mapping to an existing column IF dropif IS NULL OR pg_catalog.upper(dropif) <> 'EXISTS' THEN RAISE EXCEPTION 'unable to map field "%" to backing table in % operation: ''%''', pattern, alt_op, current_clauses[ALTER_CLAUSE]; END IF; END IF; dropif := NULL; alt_op := NULL; ELSE -- checked before, this should never happen RAISE EXCEPTION 'unexpected ALTER clause: %', alt_op; END IF; pattern := ''; END LOOP; IF quoted THEN RAISE EXCEPTION 'unterminated quoted identifier ''"%'' at or near: ''%''', pg_catalog.substr(pattern, 1, pg_catalog.char_length(pattern)-1), current_clauses[ALTER_CLAUSE]; END IF; -- CREATE OR REPLACE: cannot drop columns from view - MUST use DROP / CREATE -- clear view dependencies for backing table columns exec_cmds := exec_cmds || ARRAY [ 'O', 'DROP VIEW IF EXISTS db4ai.v' || s_id::TEXT ]; -- append the DDL statement for the backing table (if any) IF command_str IS NOT NULL THEN exec_cmds := exec_cmds || ARRAY [ 'O', command_str ]; END IF; IF newmap THEN -- generate and append grant, create view and rewrite rules for new snapshot exec_cmds := (db4ai.prepare_snapshot_internal(s_id, p_id, m_id, r_id, i_schema, s_name, i_commands, i_comment, CURRENT_USER, NULL, exec_cmds, mapping)).i_exec_cmds; newmap := FALSE; END IF; END; ELSIF current_op = INSERT_OP THEN IF current_clauses[SET_CLAUSE] IS NULL THEN RAISE EXCEPTION 'missing SELECT or VALUES clause in INSERT operation'; END IF; exec_cmds := exec_cmds || ARRAY [ 'U', 'INSERT INTO db4ai.v' || s_id::TEXT || ' ' || current_clauses[SET_CLAUSE] -- generic SQL || CASE WHEN current_clauses[FROM_CLAUSE] IS NULL THEN '' ELSE ' ' || current_clauses[FROM_CLAUSE] END || CASE WHEN current_clauses[WHERE_CLAUSE] IS NULL THEN '' ELSE ' ' || current_clauses[WHERE_CLAUSE] END ]; ELSIF current_op = DELETE_OP THEN exec_cmds := exec_cmds || ARRAY [ 'U', 'DELETE FROM db4ai.v' || s_id::TEXT || ' AS ' || current_clauses[AS_CLAUSE] || CASE WHEN current_clauses[FROM_CLAUSE] IS NULL THEN '' ELSE ' ' || current_clauses[FROM_CLAUSE] END -- USING || CASE WHEN current_clauses[WHERE_CLAUSE] IS NULL THEN '' ELSE ' ' || current_clauses[WHERE_CLAUSE] END ]; ELSIF current_op = UPDATE_OP THEN command_str := NULL; -- stores DDL statement for adding shadow columns IF current_clauses[SET_CLAUSE] IS NULL THEN RAISE EXCEPTION 'missing SET clause in UPDATE operation'; END IF; -- extract updated fields and check their mapping FOR pattern IN SELECT coalesce(m[1], pg_catalog.replace(m[2],'""','"')) FROM pg_catalog.regexp_matches(current_clauses[SET_CLAUSE], '([^\s"]+)\s*=|"((?:[^"]*"")*[^"]*)"\s*=','g') m LOOP FOR idx IN 3 .. pg_catalog.array_length(mapping, 1) BY 3 LOOP IF pattern = mapping[idx] THEN -- ADD a private column (only CSS) IF mapping[idx-2] IS NULL THEN command_str := CASE WHEN command_str IS NULL THEN 'ALTER TABLE db4ai.t' || m_id::TEXT ELSE command_str || ',' END || ' ADD f' || c_id::TEXT || ' ' || pg_catalog.format_type(atttypid, atttypmod)::TEXT FROM pg_catalog.pg_attribute WHERE attrelid = ('db4ai.t' || m_id::TEXT)::regclass AND attname = mapping[idx-1]; mapping[idx-2] := 'f' || c_id::TEXT; newmap := TRUE; c_id := c_id + 1; END IF; pattern := NULL; EXIT; END IF; END LOOP; -- check if pattern was mapped IF pattern IS NOT NULL THEN RAISE EXCEPTION 'unable to map field "%" to backing table in UPDATE operation: %', pattern, current_clauses[SET_CLAUSE]; END IF; END LOOP; -- append the DDL statement for the backing table for adding shadow columns (if any) IF command_str IS NOT NULL THEN exec_cmds := exec_cmds || ARRAY [ 'O', command_str ]; END IF; IF newmap THEN -- generate and append grant, create view and rewrite rules for new snapshot exec_cmds := (db4ai.prepare_snapshot_internal(s_id, p_id, m_id, r_id, i_schema, s_name, i_commands, i_comment, CURRENT_USER, NULL, exec_cmds, mapping)).i_exec_cmds; newmap := FALSE; END IF; exec_cmds := exec_cmds || ARRAY [ 'U', 'UPDATE db4ai.v' || s_id::TEXT || ' AS ' || current_clauses[AS_CLAUSE] || ' ' || current_clauses[SET_CLAUSE] || CASE WHEN current_clauses[FROM_CLAUSE] IS NULL THEN '' ELSE ' ' || current_clauses[FROM_CLAUSE] END || CASE WHEN current_clauses[WHERE_CLAUSE] IS NULL THEN '' ELSE ' ' || current_clauses[WHERE_CLAUSE] END ]; END IF; END IF; current_op := next_op; next_op := NULL; -- restore ALTER clause for ADD / DROP without 'ALTER' keyword, else reset to NULL current_clauses := next_clauses; next_clauses := NULL; END LOOP; -- compute final version string IF i_vers IS NULL OR i_vers = '' THEN BEGIN vers_arr := pg_catalog.regexp_split_to_array(p_name_vers[2], CASE s_vers_sep WHEN '.' THEN '\.' ELSE s_vers_sep END); IF pg_catalog.array_length(vers_arr, 1) <> 3 OR pg_catalog.array_length(vers_arr, 2) <> none_represent OR vers_arr[1] ~ '[^0-9]' OR vers_arr[2] ~ '[^0-9]' OR vers_arr[3] ~ '[^0-9]' THEN RAISE EXCEPTION 'illegal version format'; END IF; IF ops_arr[ALTER_OP] THEN vers_arr[1] := vers_arr[1] + 1; vers_arr[2] := 0; vers_arr[3] := 0; ELSIF ops_arr[INSERT_OP] OR ops_arr[DELETE_OP] THEN vers_arr[2] := vers_arr[2] + 1; vers_arr[3] := 0; ELSE vers_arr[3] := vers_arr[3] + 1; END IF; i_vers := s_vers_del || pg_catalog.array_to_string(vers_arr, s_vers_sep); EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'parent has nonstandard version %. i_vers cannot be null or empty', p_name_vers[2] USING HINT = 'provide custom version using i_vers parameter for new snapshot'; END; ELSE i_vers := pg_catalog.replace(i_vers, pg_catalog.chr(2), s_vers_sep); IF LEFT(i_vers, 1) <> s_vers_del THEN i_vers := s_vers_del || i_vers; ELSIF pg_catalog.char_length(i_vers) < 2 THEN RAISE EXCEPTION 'illegal i_vers: ''%''', s_vers_del; END IF; IF pg_catalog.strpos(pg_catalog.substr(i_vers, 2), s_vers_del) > 0 THEN RAISE EXCEPTION 'i_vers may contain only one single, leading ''%'' character', s_vers_del USING HINT = 'specify snapshot version as [' || s_vers_del || ']x' || s_vers_sep || 'y' || s_vers_sep || 'z or [' || s_vers_del || ']label with optional, leading ''' || s_vers_del || ''''; END IF; END IF; IF pg_catalog.char_length(p_name_vers[1] || i_vers) > 63 THEN RAISE EXCEPTION 'snapshot name too long: ''%''', p_name_vers[1] || i_vers; ELSE s_name := p_name_vers[1] || i_vers; END IF; -- the final name of the snapshot qual_name := pg_catalog.quote_ident(i_schema) || '.' || pg_catalog.quote_ident(s_name); -- check for duplicate snapshot IF 0 < (SELECT COUNT(0) FROM db4ai.snapshot WHERE schema = i_schema AND name = s_name) THEN RAISE EXCEPTION 'snapshot % already exists' , qual_name; END IF; IF s_mode = 'MSS' THEN exec_cmds := exec_cmds || ARRAY [ 'O', 'COMMENT ON TABLE db4ai.t' || s_id::TEXT || ' IS ''snapshot backing table, root is ' || qual_name || '''' ]; END IF; -- Execute the queries RAISE NOTICE E'accumulated commands:\n%', pg_catalog.array_to_string(exec_cmds, E'\n'); DECLARE idx INTEGER := 1; -- loop counter, cannot use FOR .. iterator BEGIN LOOP EXIT WHEN idx = 1 + pg_catalog.array_length(exec_cmds, 1); WHILE exec_cmds[idx][1] = 'U' LOOP -- RAISE NOTICE 'user executing: %', exec_cmds[idx][2]; DECLARE e_message TEXT; -- exception message BEGIN EXECUTE exec_cmds[idx][2]; idx := idx + 1; EXCEPTION WHEN undefined_table THEN GET STACKED DIAGNOSTICS e_message = MESSAGE_TEXT; -- during function invocation, search path is redirected to {pg_temp, pg_catalog, function_schema} and becomes immutable RAISE INFO 'could not resolve relation % using system-defined "search_path" setting during function invocation: ''%''', pg_catalog.substr(e_message, 10, 1 + pg_catalog.strpos(pg_catalog.substr(e_message,11), '" does not exist')), pg_catalog.array_to_string(pg_catalog.current_schemas(TRUE),', ') USING HINT = 'snapshots require schema-qualified table references, e.g. schema_name.table_name'; RAISE; END; END LOOP; IF idx < pg_catalog.array_length(exec_cmds, 1) AND (exec_cmds[idx][1] IS NULL OR exec_cmds[idx][1] <> 'O') THEN -- this should never happen RAISE EXCEPTION 'prepare snapshot internal error1: % %', idx, exec_cmds[idx]; END IF; -- execute owner statements (if any) and epilogue idx := (db4ai.prepare_snapshot_internal(s_id, p_id, m_id, r_id, i_schema, s_name, i_commands, i_comment, CURRENT_USER, idx, exec_cmds)).i_idx; END LOOP; END; s_uv_proj := ''; FOR idx IN 3 .. pg_catalog.array_length(mapping, 1) BY 3 LOOP s_uv_proj := s_uv_proj || pg_catalog.quote_ident(mapping[idx]) || ','; END LOOP; -- create custom view, owned by current user EXECUTE 'CREATE VIEW ' || qual_name || ' WITH(security_barrier) AS SELECT '|| pg_catalog.rtrim(s_uv_proj, ',') || ' FROM db4ai.v' || s_id::TEXT; EXECUTE 'COMMENT ON VIEW ' || qual_name || ' IS ''snapshot view backed by db4ai.v' || s_id::TEXT || CASE WHEN pg_catalog.length(i_comment) > 0 THEN ' comment is "' || i_comment || '"' ELSE '' END || ''''; EXECUTE 'ALTER VIEW ' || qual_name || ' OWNER TO "' || CURRENT_USER || '"'; -- return final snapshot name res := ROW(i_schema, s_name); return res; END; $$; COMMENT ON FUNCTION db4ai.prepare_snapshot() IS 'Prepare snapshot from existing for data curation';