phppostgresqlsessionsequencelimesurvey

How can I copy sequences from one schema to another in Postgresql?

The objective is to figure out how to do this after copying tables from a schema, schema 1, to a new schema, schema 2, using the following method

create table if not exists {schema 2}.{lime_table} (like {schema 1}.{lime_table} including all);

insert into {schema 2}.{lime_table} (select * from {schema 1}.{lime_table});

Copying tables between schemas with this method leaves sequences in schema 2 pointing to the sequences in schema 1. To remove dependencies/coupling with schema 1, I created identical sequences in schema 2 with a script that utilized a template like

create sequence
    if not exists {schema_2_dot_sequence_name}
    increment by 1
    minvalue 1
    maxvalue 2147483647
    start {start_with} -- taken from `schema 1`.sequence.last_val
    cache 1
    no cycle
    owned by {schema_2_dot_table_dot_id_field_name}
;

and then altered the id columns in schema 2 using

alter table {schema_2_dot_table}
    alter column {id_field_name}
        set default nextval({schema_2_dot_sequence}::regclass)

After making these database changes, I pointed my application (Limesurvey) to schema 2.

Now when trying to insert records to schema 2, the error currval of sequence "<sequence_name>" is not yet defined in this session is thrown. If I point my app back to schema 1 (creating a new database connection/session), I do not get this error, so it leads me to think that the sequence "migration" I did is wrong. The app is using the php function lastInsertId.

Update: I did a pg_dump and did not find any instances of schema 1 in the output, so maybe the migration I did works and there is a config in the app somewhere pointing to the schema 1...

Solution

  • After looking at the stack trace, I found references to schema 1 being made. For example, C:\...\CDbCommandBuilder.php(62): CDbConnection->getLastInsertID("public.lime_user_groups_ugid_seq"), where public is schema 1. And because schema 1 didn't appear in the pg_dump, I knew it must've been an issue with the app configs. I found there were a few hidden schema 1 strings to change in the codebase with the help of https://forums.limesurvey.org/forum/installation-a-update-issues/111790-installation-on-a-different-schema. This means the method I used to migrate/copy tables and their sequences from one schema to another works.

    Update: The problem was in the app configs. Our database uses a pgpool connection with two pg servers. Connecting to the pool created the error. It went away after connected directly to the main/master pg server.