From b312a974de686fe9764ed06809eed1f8215452ce Mon Sep 17 00:00:00 2001 From: Marko Kreen Date: Mon, 9 Jul 2012 12:39:07 +0300 Subject: [PATCH] londiste.applied_execute: drop queue_name from pkey Reason - we want execute work also when parallel queues from partitioned databases are merged together into single queue. Thus we need to be able to detect if same changes have been applied through different queue. --- .../functions/londiste.execute_finish.sql | 3 +-- .../functions/londiste.execute_start.sql | 3 +-- .../functions/londiste.upgrade_schema.sql | 26 +++++++++++++++++++ sql/londiste/structure/tables.sql | 5 +--- 4 files changed, 29 insertions(+), 8 deletions(-) diff --git a/sql/londiste/functions/londiste.execute_finish.sql b/sql/londiste/functions/londiste.execute_finish.sql index 884f61dc..fa472a0c 100644 --- a/sql/londiste/functions/londiste.execute_finish.sql +++ b/sql/londiste/functions/londiste.execute_finish.sql @@ -27,8 +27,7 @@ begin select execute_sql, execute_attrs into sql, attrs from londiste.applied_execute - where queue_name = i_queue_name - and execute_file = i_file_name; + where execute_file = i_file_name; if not found then select 404, 'execute_file called without execute_start' into ret_code, ret_note; diff --git a/sql/londiste/functions/londiste.execute_start.sql b/sql/londiste/functions/londiste.execute_start.sql index c6898195..7817cb6c 100644 --- a/sql/londiste/functions/londiste.execute_start.sql +++ b/sql/londiste/functions/londiste.execute_start.sql @@ -45,8 +45,7 @@ begin end if; perform 1 from londiste.applied_execute - where queue_name = i_queue_name - and execute_file = i_file_name; + where execute_file = i_file_name; if found then select 301, 'EXECUTE(' || i_file_name || ') already applied' into ret_code, ret_note; diff --git a/sql/londiste/functions/londiste.upgrade_schema.sql b/sql/londiste/functions/londiste.upgrade_schema.sql index 2767f00e..a0cf11e2 100644 --- a/sql/londiste/functions/londiste.upgrade_schema.sql +++ b/sql/londiste/functions/londiste.upgrade_schema.sql @@ -37,6 +37,32 @@ begin alter table londiste.applied_execute add column execute_attrs text; end if; + -- applied_execute: drop queue_name from primary key + perform 1 from pg_catalog.pg_indexes + where schemaname = 'londiste' + and tablename = 'applied_execute' + and indexname = 'applied_execute_pkey' + and indexdef like '%queue_name%'; + if found then + alter table londiste.applied_execute + drop constraint applied_execute_pkey; + alter table londiste.applied_execute + add constraint applied_execute_pkey + primary key (execute_file); + end if; + + -- applied_execute: drop fkey to pgq_node + perform 1 from information_schema.table_constraints + where constraint_schema = 'londiste' + and table_schema = 'londiste' + and table_name = 'applied_execute' + and constraint_type = 'FOREIGN KEY' + and constraint_name = 'applied_execute_queue_name_fkey'; + if found then + alter table londiste.applied_execute + drop constraint applied_execute_queue_name_fkey; + end if; + -- create roles perform 1 from pg_catalog.pg_roles where rolname = 'londiste_writer'; if not found then diff --git a/sql/londiste/structure/tables.sql b/sql/londiste/structure/tables.sql index 35eda747..5f5429b6 100644 --- a/sql/londiste/structure/tables.sql +++ b/sql/londiste/structure/tables.sql @@ -147,10 +147,7 @@ create table londiste.applied_execute ( execute_time timestamptz not null default now(), execute_sql text not null, execute_attrs text, - primary key (queue_name, execute_file), - foreign key (queue_name) - references pgq_node.node_info (queue_name) - on delete cascade + primary key (execute_file) ); -- 2.39.5