diff options
| -rw-r--r-- | sql/londiste/expected/londiste_seqs.out | 47 | ||||
| -rw-r--r-- | sql/londiste/functions/londiste.local_remove_table.sql | 20 | ||||
| -rw-r--r-- | sql/londiste/sql/londiste_seqs.sql | 15 |
3 files changed, 82 insertions, 0 deletions
diff --git a/sql/londiste/expected/londiste_seqs.out b/sql/londiste/expected/londiste_seqs.out index 459f7d93..ed21db3f 100644 --- a/sql/londiste/expected/londiste_seqs.out +++ b/sql/londiste/expected/londiste_seqs.out @@ -120,3 +120,50 @@ select * from londiste.local_remove_seq('seqbranch', 'masterseq'); 404 | Sequence not found: public.masterseq (1 row) +-- seq auto-removal +create table seqtable ( + id1 serial primary key, + id2 bigserial not null +); +select * from londiste.local_add_table('seqroot', 'seqtable'); + ret_code | ret_note +----------+------------------------------ + 200 | Table added: public.seqtable +(1 row) + +select * from londiste.local_add_seq('seqroot', 'seqtable_id1_seq'); + ret_code | ret_note +----------+----------------------------------------- + 200 | Sequence added: public.seqtable_id1_seq +(1 row) + +select * from londiste.local_add_seq('seqroot', 'seqtable_id2_seq'); + ret_code | ret_note +----------+----------------------------------------- + 200 | Sequence added: public.seqtable_id2_seq +(1 row) + +select * from londiste.get_table_list('seqroot'); + table_name | local | merge_state | custom_snapshot | table_attrs | dropped_ddl | copy_role | copy_pos | dest_table +-----------------+-------+-------------+-----------------+-------------+-------------+-----------+----------+------------ + public.seqtable | t | ok | | | | | 0 | +(1 row) + +select * from londiste.get_seq_list('seqroot'); + seq_name | last_value | local +-------------------------+------------+------- + public.seqtable_id1_seq | 30001 | t + public.seqtable_id2_seq | 30001 | t +(2 rows) + +select * from londiste.local_remove_table('seqroot', 'seqtable'); + ret_code | ret_note +----------+-------------------------------- + 200 | Table removed: public.seqtable +(1 row) + +select * from londiste.get_seq_list('seqroot'); + seq_name | last_value | local +----------+------------+------- +(0 rows) + diff --git a/sql/londiste/functions/londiste.local_remove_table.sql b/sql/londiste/functions/londiste.local_remove_table.sql index ac86566a..71a0806a 100644 --- a/sql/londiste/functions/londiste.local_remove_table.sql +++ b/sql/londiste/functions/londiste.local_remove_table.sql @@ -18,9 +18,14 @@ as $$ -- ---------------------------------------------------------------------- declare fq_table_name text; + qtbl text; + seqname text; tbl record; + tbl_oid oid; begin fq_table_name := londiste.make_fqname(i_table_name); + qtbl := londiste.quote_fqname(fq_table_name); + tbl_oid := londiste.find_table_oid(i_table_name); select local into tbl from londiste.table_info @@ -44,6 +49,21 @@ begin dest_table = null where queue_name = i_queue_name and table_name = fq_table_name; + + -- drop dependent sequence + for seqname in + select n.nspname || '.' || s.relname + from pg_catalog.pg_class s, + pg_catalog.pg_namespace n, + pg_catalog.pg_attribute a + where a.attrelid = tbl_oid + and a.atthasdef + and a.atttypid::regtype::text in ('integer', 'bigint') + and s.oid = pg_get_serial_sequence(qtbl, a.attname)::regclass::oid + and n.oid = s.relnamespace + loop + perform londiste.local_remove_seq(i_queue_name, seqname); + end loop; else if not pgq_node.is_root_node(i_queue_name) then select 400, 'Table not registered locally: ' || fq_table_name into ret_code, ret_note; diff --git a/sql/londiste/sql/londiste_seqs.sql b/sql/londiste/sql/londiste_seqs.sql index 27f3cd50..8487a2d0 100644 --- a/sql/londiste/sql/londiste_seqs.sql +++ b/sql/londiste/sql/londiste_seqs.sql @@ -36,4 +36,19 @@ select * from londiste.get_seq_list('seqbranch'); select * from londiste.local_remove_seq('seqbranch', 'masterseq'); select * from londiste.local_remove_seq('seqbranch', 'masterseq'); +-- seq auto-removal +create table seqtable ( + id1 serial primary key, + id2 bigserial not null +); +select * from londiste.local_add_table('seqroot', 'seqtable'); +select * from londiste.local_add_seq('seqroot', 'seqtable_id1_seq'); +select * from londiste.local_add_seq('seqroot', 'seqtable_id2_seq'); + +select * from londiste.get_table_list('seqroot'); +select * from londiste.get_seq_list('seqroot'); + +select * from londiste.local_remove_table('seqroot', 'seqtable'); + +select * from londiste.get_seq_list('seqroot'); |