clone schema - pg_get_serial_sequence returns null

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

clone schema - pg_get_serial_sequence returns null

Olivier Leprêtre

Hi,

 

I want to clone a schema, so I wrote the script below. My problem is that I can’t get sequences with pg_get_serial_sequence for the cloned primary keys.

 

The sequence seems to be ok in the cloned schema :

  idapp integer NOT NULL DEFAULT nextval('pr_1_1.ptbt_idapp_seq'::regclass),

  CONSTRAINT adn_ptbt_pkey PRIMARY KEY (idapp)

 

But if I run select pg_get_serial_sequence('pr_1_1.ptbt', 'idapp') it returns <NULL>

Conversely, running pg_get_serial_sequence('source.ptbt', 'idapp') (from the source schema) returns the correct sequence.

 

I’m using 9.6, I saw that it could be a problem because column is not directly as “serial”, is this an answer without solution ?

 

Any help will be appreciated !

 

Thanks,

 

Olivier

 

 

CREATE OR REPLACE FUNCTION params.prm_colne_schema(source_schema text, dest_schema text) RETURNS boolean AS

$BODY$

 

DECLARE

  object text;

  buffer text;

  default_ text;

  column_ text;

  sqls text;

BEGIN

            IF dest_schema <> 'public' THEN

                        EXECUTE 'DROP SCHEMA if exists ' || dest_schema || ' CASCADE';

                        EXECUTE 'CREATE SCHEMA ' || dest_schema;

            END IF;           

 

            -- copy sequences

            FOR object IN

                        SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema

            LOOP

                        EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;

            END LOOP;

 

            -- copy tables without constraints

            FOR object IN

                        SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema  and substr(table_name,1,4) <> 'src_'

            LOOP       

                        buffer := dest_schema || '.' || object;

 

                        EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING DEFAULTS)';

 

                        FOR column_, default_ IN

                                   SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object

                        LOOP

                                   EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ ||' DROP NOT NULL;';

                        END LOOP;

 

            END LOOP;

 

            -- copy tables with constraints

            FOR object IN

                                   SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema  and substr(table_name,1,4) = 'src_'

            LOOP       

                        buffer := dest_schema || '.' || object;

 

                        EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING ALL)';

                        -- copy sequences

                        FOR column_, default_ IN

                                   SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'

                        LOOP

                                   sqls='ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;

                                   RAISE NOTICE 'default=%',sqls;

                                   EXECUTE (sqls);

                        END LOOP;

            END LOOP;

 

            RETURN true;

EXCEPTION

            WHEN others THEN

            RETURN false;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: clone schema - pg_get_serial_sequence returns null

Regina Obe-2

I think you'd have better luck asking this on pgsql-general news list. https://www.postgresql.org/list/pgsql-general/

 

 

There is no PostGIS related question here.

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Sunday, July 30, 2017 2:24 PM
To: [hidden email]
Subject: [postgis-users] clone schema - pg_get_serial_sequence returns null

 

Hi,

 

I want to clone a schema, so I wrote the script below. My problem is that I can’t get sequences with pg_get_serial_sequence for the cloned primary keys.

 

The sequence seems to be ok in the cloned schema :

  idapp integer NOT NULL DEFAULT nextval('pr_1_1.ptbt_idapp_seq'::regclass),

  CONSTRAINT adn_ptbt_pkey PRIMARY KEY (idapp)

 

But if I run select pg_get_serial_sequence('pr_1_1.ptbt', 'idapp') it returns <NULL>

Conversely, running pg_get_serial_sequence('source.ptbt', 'idapp') (from the source schema) returns the correct sequence.

 

I’m using 9.6, I saw that it could be a problem because column is not directly as “serial”, is this an answer without solution ?

 

Any help will be appreciated !

 

Thanks,

 

Olivier

 

 

CREATE OR REPLACE FUNCTION params.prm_colne_schema(source_schema text, dest_schema text) RETURNS boolean AS

$BODY$

 

DECLARE

  object text;

  buffer text;

  default_ text;

  column_ text;

  sqls text;

BEGIN

            IF dest_schema <> 'public' THEN

                        EXECUTE 'DROP SCHEMA if exists ' || dest_schema || ' CASCADE';

                        EXECUTE 'CREATE SCHEMA ' || dest_schema;

            END IF;           

 

            -- copy sequences

            FOR object IN

                        SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema

            LOOP

                        EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;

            END LOOP;

 

            -- copy tables without constraints

            FOR object IN

                        SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema  and substr(table_name,1,4) <> 'src_'

            LOOP       

                        buffer := dest_schema || '.' || object;

 

                        EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING DEFAULTS)';

 

                        FOR column_, default_ IN

                                   SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object

                        LOOP

                                   EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ ||' DROP NOT NULL;';

                        END LOOP;

 

            END LOOP;

 

            -- copy tables with constraints

            FOR object IN

                                   SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema  and substr(table_name,1,4) = 'src_'

            LOOP       

                        buffer := dest_schema || '.' || object;

 

                        EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING ALL)';

                        -- copy sequences

                        FOR column_, default_ IN

                                   SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'

                        LOOP

                                   sqls='ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;

                                   RAISE NOTICE 'default=%',sqls;

                                   EXECUTE (sqls);

                        END LOOP;

            END LOOP;

 

            RETURN true;

EXCEPTION

            WHEN others THEN

            RETURN false;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: clone schema - pg_get_serial_sequence returns null

Ahsan Hadi
You can try using this extension available on github for schema cloning, it provides complete schema clone (DDL and Data).


-- Ahsan

On Mon, Jul 31, 2017 at 2:09 PM, Regina Obe <[hidden email]> wrote:

I think you'd have better luck asking this on pgsql-general news list. https://www.postgresql.org/list/pgsql-general/

 

 

There is no PostGIS related question here.

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Sunday, July 30, 2017 2:24 PM
To: [hidden email]
Subject: [postgis-users] clone schema - pg_get_serial_sequence returns null

 

Hi,

 

I want to clone a schema, so I wrote the script below. My problem is that I can’t get sequences with pg_get_serial_sequence for the cloned primary keys.

 

The sequence seems to be ok in the cloned schema :

  idapp integer NOT NULL DEFAULT nextval('pr_1_1.ptbt_idapp_seq'::regclass),

  CONSTRAINT adn_ptbt_pkey PRIMARY KEY (idapp)

 

But if I run select pg_get_serial_sequence('pr_1_1.ptbt', 'idapp') it returns <NULL>

Conversely, running pg_get_serial_sequence('source.ptbt', 'idapp') (from the source schema) returns the correct sequence.

 

I’m using 9.6, I saw that it could be a problem because column is not directly as “serial”, is this an answer without solution ?

 

Any help will be appreciated !

 

Thanks,

 

Olivier

 

 

CREATE OR REPLACE FUNCTION params.prm_colne_schema(source_schema text, dest_schema text) RETURNS boolean AS

$BODY$

 

DECLARE

  object text;

  buffer text;

  default_ text;

  column_ text;

  sqls text;

BEGIN

            IF dest_schema <> 'public' THEN

                        EXECUTE 'DROP SCHEMA if exists ' || dest_schema || ' CASCADE';

                        EXECUTE 'CREATE SCHEMA ' || dest_schema;

            END IF;           

 

            -- copy sequences

            FOR object IN

                        SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema

            LOOP

                        EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;

            END LOOP;

 

            -- copy tables without constraints

            FOR object IN

                        SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema  and substr(table_name,1,4) <> 'src_'

            LOOP       

                        buffer := dest_schema || '.' || object;

 

                        EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING DEFAULTS)';

 

                        FOR column_, default_ IN

                                   SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object

                        LOOP

                                   EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ ||' DROP NOT NULL;';

                        END LOOP;

 

            END LOOP;

 

            -- copy tables with constraints

            FOR object IN

                                   SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema  and substr(table_name,1,4) = 'src_'

            LOOP       

                        buffer := dest_schema || '.' || object;

 

                        EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING ALL)';

                        -- copy sequences

                        FOR column_, default_ IN

                                   SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'

                        LOOP

                                   sqls='ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;

                                   RAISE NOTICE 'default=%',sqls;

                                   EXECUTE (sqls);

                        END LOOP;

            END LOOP;

 

            RETURN true;

EXCEPTION

            WHEN others THEN

            RETURN false;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



--
Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874   
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...