On Mon, Jul 2, 2018 at 10:14 AM, Łukasz Jarych <[email protected]> wrote:
>
> Hi,
>
> i see. thank you
>
> I am using:
>
> [image: image.png]
>
> Best,
> Jacek
>
> pon., 2 lip 2018 o 16:03 Adrian Klaver <[email protected]>
> napisał(a):
>
>> On 07/02/2018 06:57 AM, Łukasz Jarych wrote:
>> > Hi,
>> >
>> > "
>> > Strange. "audit_sq" looks like an invalid sequence table. I went
>> > here,https://www.postgresql.org/docs/9.6/static/sql-createsequence.html,
>>
>> > and checked all the way back to version 7.1 and "maxvalue" has been a
>> > column since back then.
>>
>> What version of Postgres are you actually doing the cloning in?
>>
>> Per Tom's post:
>>
>> https://www.postgresql.org/docs/10/static/release-10.html
>>
>> "
>> Move sequences' metadata fields into a new pg_sequence system catalog
>> (Peter Eisentraut)
>>
>> ...
>>
>> A sequence relation now stores only the fields that can be modified by
>> nextval(), that is last_value, log_cnt, and is_called.
>>
>> ...
>>
>> The main incompatibility introduced by this change is that selecting
>> from a sequence relation now returns only the three fields named above.
>> To obtain the sequence's other properties, applications must look into
>> pg_sequence. The new system view pg_sequences can also be used for this
>> purpose; it provides column names that are more compatible with existing
>> code.
>> "
>>
>> >
>> > Maybe skip that table for now? It even says the last value is 1. You
>> > should also check the other sequence tables. You can get them by doing:
>> >
>> > select * from information_schema.sequences;"
>> >
>> > Result of select:
>> >
>> > image.png
>> >
>> > Are you sure that I can skip " audit_sq" seq?
>> >
>> > I wrote here because i am newbie and i can not update this. Hope for
>> > your help Guys.
>> >
>> > Best,
>> > Jacek
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > pon., 2 lip 2018 o 15:30 Tom Lane <[email protected]
>> > <mailto:[email protected]>> napisał(a):
>> >
>> > =?UTF-8?Q?=C5=81ukasz_Jarych?= <[email protected]
>> > <mailto:[email protected]>> writes:
>> > > I am trying to use :
>> >
>> > > "select * from clone_schema('public','Version8',true) but i am
>> > getting
>> > > error:
>> >
>> > > "Column "max_value" does not exist.
>> > > LINE 1: SELECT last_value, max_value, start_value, increment_by,
>> > min...
>> > > HINT: Maybe you wanted to point to column "
>> "audit_sq.last_value"?
>> > > QUERY: SELECT last_value, max_value, start_value, increment_by,
>> > min_value,
>> > > cache_value, log_cnt, is_cycled, is_called FROM public.audit_sq;
>> > > CONTEXT: function PL/pgSQL clone_schema(text,text,boolean), row
>> 66 in
>> > > EXECUTE
>> >
>> > I guess audit_sq is a sequence? It looks to me like this function
>> has
>> > not been taught about the changes in sequence metadata in PG v10.
>> > You need to update it, or talk to its author about an update.
>> >
>> > regards, tom lane
>> >
>>
>>
>> --
>> Adrian Klaver
>> [email protected]
>>
>
Lukas,
Once again, in Version 10, the developers have changed the system catalogs.
Please use the attached clone_schema_10.sql which
has been adjusted for PostgreSQL 10.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)
-- DROP FUNCTION public.clone_schema(text, text, boolean);
CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
RETURNS void AS
$BODY$
-- Initial code by Emanuel '3manuek'
-- Last revision 2017-04-17 by Melvin Davidson
-- Added SELECT REPLACE for schema views
--
-- This function will clone all sequences, tables, indexes, rules, triggers,
-- data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);
DECLARE
src_oid oid;
tbl_oid oid;
seq_oid oid;
func_oid oid;
owner_id oid;
con_oid oid;
v_path text;
v_func text;
v_args text;
v_conname text;
v_rule text;
v_trig text;
object text;
buffer text;
srctbl text;
default_ text;
v_column text;
qry text;
dest_qry text;
v_def text;
v_stat integer;
seqval bigint;
seq_name name;
sq_type oid;
sq_last_value bigint;
sq_max_value bigint;
sq_start_value bigint;
sq_increment_by bigint;
sq_min_value bigint;
sq_cache_value bigint;
sq_log_cnt bigint;
-- sq_is_called boolean;
sq_is_cycled boolean;
sq_typname name;
seq_cycled text;
seq_owner name;
-- sq_cycled char(10);
BEGIN
-- Check that source_schema exists
SELECT oid INTO src_oid
FROM pg_namespace
WHERE nspname = quote_ident(source_schema);
IF NOT FOUND
THEN
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
END IF;
-- Check that dest_schema does not yet exist
PERFORM nspname
FROM pg_namespace
WHERE nspname = quote_ident(dest_schema);
IF FOUND
THEN
RAISE NOTICE 'dest schema % already exists!', dest_schema;
RETURN ;
END IF;
EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
-- Add schema comment
SELECT description INTO v_def
FROM pg_description
WHERE objoid = src_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' ||
quote_literal(v_def);
END IF;
SET search_path TO dest_schema, source_schema, 'public', pg_catalog;
SELECT current_schemas(TRUE) INTO v_path;
RAISE NOTICE 'search path = %', v_path;
-- Create sequences
-- TODO: Find a way to make this sequence's owner is the correct table.
FOR object IN
SELECT sequence_name::text
FROM information_schema.sequences
WHERE sequence_schema = quote_ident(source_schema)
LOOP
SELECT oid, relowner into seq_oid, owner_id
FROM pg_class
WHERE relname = quote_ident(object)
AND relkind = 'S';
SELECT seqtypid,
seqstart,
seqincrement,
seqmax,
seqmin,
seqcache,
seqcycle
INTO sq_type,
sq_start_value,
sq_increment_by,
sq_max_value,
sq_min_value,
sq_cache_value,
sq_is_cycled
FROM pg_sequence
WHERE seqrelid = seq_oid;
SELECT typname INTO sq_typname
FROM pg_type
WHERE oid = sq_type;
SELECT rolname INTO seq_owner
FROM pg_authid WHERE oid = owner_id;
-- EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object);
-- srctbl := quote_ident(source_schema) || '.' || quote_ident(object);
IF sq_is_cycled
THEN
seq_cycled = ' CYCLE';
ELSE
seq_cycled = ' NO CYCLE';
END IF;
EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' AS ' || sq_typname
|| ' INCREMENT ' || sq_increment_by
|| ' MINVALUE ' || sq_min_value
|| ' MAXVALUE ' || sq_max_value
|| ' START WITH ' || sq_start_value
|| ' CACHE ' || sq_cache_value
|| seq_cycled;
-- || ' OWNED BY ' || seq_owner;
/* buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
IF include_recs
THEN
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value ||
', ' || sq_is_called || ');' ;
ELSE
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value
|| ', ' || sq_is_called || ');' ;
END IF;
*/
-- add sequence comments
SELECT oid INTO tbl_oid
FROM pg_class
WHERE relkind = 'S'
AND relnamespace = src_oid
AND relname = quote_ident(object);
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
END LOOP;
-- Create tables
FOR object IN
SELECT TABLE_NAME::text
FROM information_schema.tables
WHERE table_schema = quote_ident(source_schema)
AND table_type = 'BASE TABLE'
LOOP
buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
quote_ident(source_schema) || '.' || quote_ident(object)
|| ' INCLUDING ALL)';
-- Add table comment
SELECT oid INTO tbl_oid
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = src_oid
AND relname = quote_ident(object);
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON TABLE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
IF include_recs
THEN
-- Insert records from source table
EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' ||
quote_ident(source_schema) || '.' || quote_ident(object) || ';';
END IF;
FOR v_column, default_ IN
SELECT column_name::text,
REPLACE(column_default::text, quote_ident(source_schema) || '.',
quote_ident(dest_schema) || '.' )
FROM information_schema.COLUMNS
WHERE table_schema = dest_schema
AND TABLE_NAME = object
AND column_default LIKE 'nextval(%' || quote_ident(source_schema) ||
'%::regclass)'
LOOP
EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || v_column || ' SET
DEFAULT ' || default_;
END LOOP;
END LOOP;
-- set column statistics
FOR tbl_oid, srctbl IN
SELECT oid, relname
FROM pg_class
WHERE relnamespace = src_oid
AND relkind = 'r'
LOOP
FOR v_column, v_stat IN
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid = tbl_oid
AND attnum > 0
LOOP
buffer := quote_ident(dest_schema) || '.' || quote_ident(srctbl);
-- RAISE EXCEPTION 'ALTER TABLE % ALTER COLUMN % SET STATISTICS %',
buffer, v_column, v_stat::text;
EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' ||
quote_ident(v_column) || ' SET STATISTICS ' || v_stat || ';';
END LOOP;
END LOOP;
-- add FK constraint
FOR qry IN
SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' ||
quote_ident(rn.relname)
|| ' ADD CONSTRAINT ' || quote_ident(ct.conname) || '
' || replace(pg_get_constraintdef(ct.oid), quote_ident(source_schema) || '.',
quote_ident(dest_schema) || '.') || ';'
FROM pg_constraint ct
JOIN pg_class rn ON rn.oid = ct.conrelid
WHERE connamespace = src_oid
AND rn.relkind = 'r'
AND ct.contype = 'f'
LOOP
EXECUTE qry;
END LOOP;
-- Add constraint comment
FOR con_oid IN
SELECT oid
FROM pg_constraint
WHERE conrelid = tbl_oid
LOOP
SELECT conname INTO v_conname
FROM pg_constraint
WHERE oid = con_oid;
SELECT description INTO v_def
FROM pg_description
WHERE objoid = con_oid;
IF FOUND
THEN
EXECUTE 'COMMENT ON CONSTRAINT ' || v_conname || ' ON ' ||
quote_ident(dest_schema) || '.' || quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
END LOOP;
-- Create views
FOR object IN
SELECT table_name::text,
view_definition
FROM information_schema.views
WHERE table_schema = quote_ident(source_schema)
LOOP
buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
SELECT view_definition INTO v_def
FROM information_schema.views
WHERE table_schema = quote_ident(source_schema)
AND table_name = quote_ident(object);
SELECT REPLACE(v_def, source_schema, dest_schema) INTO v_def;
-- RAISE NOTICE 'view def, % , source % , dest % ', v_def, source_schema,
dest_schema;
EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;
-- Add comment
SELECT oid INTO tbl_oid
FROM pg_class
WHERE relkind = 'v'
AND relnamespace = src_oid
AND relname = quote_ident(object);
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON VIEW ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ' || quote_literal(v_def);
END IF;
END LOOP;
-- Create functions
FOR func_oid IN
SELECT oid, proargnames
FROM pg_proc
WHERE pronamespace = src_oid
LOOP
SELECT pg_get_functiondef(func_oid) INTO qry;
SELECT proname, oidvectortypes(proargtypes) INTO v_func, v_args
FROM pg_proc
WHERE oid = func_oid;
SELECT replace(qry, quote_ident(source_schema) || '.',
quote_ident(dest_schema) || '.') INTO dest_qry;
EXECUTE dest_qry;
-- Add function comment
SELECT description INTO v_def
FROM pg_description
WHERE objoid = func_oid
AND objsubid = 0;
IF FOUND
THEN
-- RAISE NOTICE 'func_oid %, object %, v_args %', func_oid::text,
quote_ident(object), v_args;
EXECUTE 'COMMENT ON FUNCTION ' || quote_ident(dest_schema) || '.' ||
quote_ident(v_func) || '(' || v_args || ')'
|| ' IS ' || quote_literal(v_def) ||';' ;
END IF;
END LOOP;
-- add Rules
SET search_path TO dest_schema;
FOR v_def IN
SELECT definition
FROM pg_rules
WHERE schemaname = quote_ident(source_schema)
LOOP
IF v_def IS NOT NULL
THEN
SELECT replace(v_def, source_schema || '.', dest_schema || '.') INTO
v_def;
EXECUTE ' ' || v_def;
END IF;
END LOOP;
-- add triggers
FOR v_def IN
SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname NOT LIKE 'RI_%'
AND tgrelid IN (SELECT oid
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = src_oid)
LOOP
SELECT replace(v_def, source_schema || '.', dest_schema || '.') INTO
dest_qry;
EXECUTE dest_qry;
END LOOP;
-- Disable inactive triggers
-- D = disabled
FOR tbl_oid IN
SELECT oid
FROM pg_trigger
WHERE tgenabled = 'D'
AND tgname NOT LIKE 'RI_%'
AND tgrelid IN (SELECT oid
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = src_oid)
LOOP
SELECT t.tgname, c.relname INTO object, srctbl
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
WHERE t.oid = tbl_oid;
IF FOUND
THEN
EXECUTE 'ALTER TABLE ' || dest_schema || '.' || srctbl || ' DISABLE
TRIGGER ' || object || ';';
END IF;
END LOOP;
-- Add index comment
FOR tbl_oid IN
SELECT oid
FROM pg_class
WHERE relkind = 'i'
AND relnamespace = src_oid
LOOP
SELECT relname INTO object
FROM pg_class
WHERE oid = tbl_oid;
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
END LOOP;
-- add rule comments
FOR con_oid IN
SELECT oid, *
FROM pg_rewrite
WHERE rulename <> '_RETURN'::name
LOOP
SELECT rulename, ev_class INTO v_rule, tbl_oid
FROM pg_rewrite
WHERE oid = con_oid;
SELECT relname INTO object
FROM pg_class
WHERE oid = tbl_oid
AND relkind = 'r';
SELECT description INTO v_def
FROM pg_description
WHERE objoid = con_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON RULE ' || v_rule || ' ON ' ||
quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
END IF;
END LOOP;
-- add trigger comments
FOR con_oid IN
SELECT oid, *
FROM pg_trigger
WHERE tgname NOT LIKE 'RI_%'
LOOP
SELECT tgname, tgrelid INTO v_trig, tbl_oid
FROM pg_trigger
WHERE oid = con_oid;
SELECT relname INTO object
FROM pg_class
WHERE oid = tbl_oid
AND relkind = 'r';
SELECT description INTO v_def
FROM pg_description
WHERE objoid = con_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON TRIGGER ' || v_trig || ' ON ' ||
quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
END IF;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.clone_schema(text, text, boolean)
OWNER TO postgres;
COMMENT ON FUNCTION public.clone_schema(text, text, boolean) IS 'Duplicates
sequences, tables, indexes, rules, triggers, data(optional),
views & functions from the source schema to the destination schema';
