Fri Mar 16 19:06:17 PDT 2007
- Previous message: [Slony1-patches] Bug in slonik_execute_script.pl
- Next message: [Slony1-patches] Re: [Slony1-general] Execute script and "execute only on"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
I had sent this patch out a few months ago and got no response. Now that we
have a patches list I thought I'd resend it in-case it got lost.
The goal of the patch is to allow DBA's to explcitly control what tables get
locked/altered during an EXECUTE script. If your doing an ALTER table to
add a column to some table that is in the edges of your schema you might not
really need to lock all tables and take an outage.
The patch is meant to be applied against HEAD.
The idea is that you can specify something like
EXECUTE SCRIPT(set id=1, filename='new_column.sql',
event node=1, lock tables = ('public.test1','public.address')
);
The ddl_updates_tbl_locks.sql should be added to tests/testddl
The diff file should be applied against CVS HEAD.
Let me know if there are any concerns.
Steve Singer
-------------- next part --------------
ALTER TABLE table5 DROP COLUMN a;
ALTER TABLE table1 ADD COLUMN xy int4;
UPDATE table1 SET xy=1;
-------------- next part --------------
? tests/testddl/ddl_updates_tbl_locks.sql
Index: doc/adminguide/slonik_ref.sgml
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/doc/adminguide/slonik_ref.sgml,v
retrieving revision 1.65
diff -c -r1.65 slonik_ref.sgml
*** doc/adminguide/slonik_ref.sgml 31 Oct 2006 22:09:39 -0000 1.65
--- doc/adminguide/slonik_ref.sgml 18 Dec 2006 04:27:50 -0000
***************
*** 2548,2553 ****
--- 2548,2563 ----
subscribed to the set.</para></listitem>
</varlistentry>
+ <varlistentry><term><literal>LOCK TABLES = ('fully.qualified_table1',
+ 'fully.qualified_table2','fully_qualified.table3')
+ </literal</term>
+ <listitem><para>(Optional) This option tells Slony to only obtain locks on the
+ tables specified. It is important that any tables that are referenced in
+ the script being executed be listed here or that this option not be specified
+ which will result in a lock being obtained on all tables.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
<para> See also the warnings in &rddlchanges;.</para>
***************
*** 2556,2573 ****
it can get stuck behind other database activity.</para>
<para> At the start of this event, all replicated tables are
! unlocked via the function
<function>alterTableRestore(tab_id)</function>. After the SQL
script has run, they are returned to <quote>replicating
state</quote> using
<function>alterTableForReplication(tab_id)</function>. This means
! that all of these tables are locked by this &lslon process for the
duration of the SQL script execution.</para>
<para> If a table's columns are modified, it is very important
that the triggers be regenerated, otherwise they may be
inappropriate for the new form of the table schema.</para>
<para> Note that if you need to make reference to the cluster
name, you can use the token <command>@CLUSTERNAME@</command>; if
you need to make reference to the &slony1; namespace, you can use
--- 2566,2590 ----
it can get stuck behind other database activity.</para>
<para> At the start of this event, all replicated tables are
! locked and replication is stopped via the function
<function>alterTableRestore(tab_id)</function>. After the SQL
script has run, they are returned to <quote>replicating
state</quote> using
<function>alterTableForReplication(tab_id)</function>. This means
! that all of these tables are locked by the slonik process(on the event node) or
! by the slon process(for other nodes) for the
duration of the SQL script execution.</para>
<para> If a table's columns are modified, it is very important
that the triggers be regenerated, otherwise they may be
inappropriate for the new form of the table schema.</para>
+ <para> The LOCK TABLES option can be used to tell Slony to only
+ lock the tables and suspend replication on the tables indicated.
+ If this option is not specified all replicated tables will be locked
+ during the execution of the script.
+ </para>
+
<para> Note that if you need to make reference to the cluster
name, you can use the token <command>@CLUSTERNAME@</command>; if
you need to make reference to the &slony1; namespace, you can use
***************
*** 2587,2595 ****
</refsect1>
<refsect1> <title> Locking Behaviour </title>
! <para> Each replicated table receives an exclusive lock, on the
! origin node, in order to remove the replication triggers; after
! the DDL script completes, those locks will be cleared. </para>
<para> After the DDL script has run on the origin node, it will
then run on subscriber nodes, where replicated tables will be
--- 2604,2645 ----
</refsect1>
<refsect1> <title> Locking Behaviour </title>
! <para> If the LOCK TABLES options is not specified then each replicated table
! receives an exclusive lock on the
! origin node in order to remove the replication triggers. Every other replicated
! table will have its application and constraint triggers re-enablled on the slave
! databases for the duration of the EXECUTE SCRIPT. After
! the DDL script completes the locks will be cleared and triggers will be reverted.
! All non-Slony database
! activity on replicated tables should be stopped to prevent the risk of deadlock.</para>
!
! <para> If the LOCK TABLES option is specified then only the the tables
! listed will have the replication triggers removed from them on the origin or other triggers
! enabled on the slave. This will require
! obtaining an exclusive lock on each listed table for the duration of the EXECUTE script.
! After the DDL script completes, those locks will be cleared.
! This allows the DBA to control which tables will be locked during the EXECUTE SCRIPT
! </para>
!
! <para>
! Any tables that are referenced in the DDL script need to be locked. The following guidelines
! are useful:
! </para>
! <itemizedlist>
! <listitem><para>Any tables referenced by an ALTER table must be locked</para></listitem>
! <listitem><para>If adding a foreign key both the table the key is being added to and
! the table the foreign key references must be locked</para></listitem>
! <listitem><para>Any tables that are the target of INSERT or UPDATE statements must be locked
! </para></listitem>
! <listitem><para>Any tables that are being queried must be locked</para></listitem>
! <listitem><para>If a table is being dropped it should be removed from all replication sets
! before the DDL script is run and thus does not need to be locked</para></listitem>
! <listitem><para>If in doubt don't use the LOCK TABLES option and allow Slony to lock everything
! </para></listitem>
!
! </itemizedlist>
!
!
<para> After the DDL script has run on the origin node, it will
then run on subscriber nodes, where replicated tables will be
Index: src/backend/slony1_funcs.sql
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_funcs.sql,v
retrieving revision 1.103
diff -c -r1.103 slony1_funcs.sql
*** src/backend/slony1_funcs.sql 15 Dec 2006 05:34:18 -0000 1.103
--- src/backend/slony1_funcs.sql 18 Dec 2006 04:27:53 -0000
***************
*** 3664,3669 ****
--- 3664,3671 ----
Processes DROP_TRIGGER event to make sure that trigger trig_tgname on
replicated table trig_tabid IS disabled.';
+
+
-- ----------------------------------------------------------------------
-- FUNCTION ddlScript_prepare (set_id, only_on_node)
--
***************
*** 3671,3680 ****
--- 3673,3700 ----
-- ----------------------------------------------------------------------
create or replace function @NAMESPACE at .ddlScript_prepare (int4, int4)
returns integer
+ as '
+ begin
+ return @NAMESPACE at .ddlScirpt_prepare($1,$2,null);
+ end;
+ ' language plpgsql;
+
+
+ comment on function @NAMESPACE at .ddlScript_prepare (int4, int4) is
+ 'Prepare for DDL script execution on origin. All replicated tables will be prepared';
+
+ -- ----------------------------------------------------------------------
+ -- FUNCTION ddlScript_prepare (set_id, only_on_node,p_lock_tab_ids)
+ --
+ -- Generate the DDL_SCRIPT event
+ -- ----------------------------------------------------------------------
+ create or replace function @NAMESPACE at .ddlScript_prepare (int4, int4,int4[])
+ returns integer
as '
declare
p_set_id alias for $1;
p_only_on_node alias for $2;
+ p_lock_tab_ids alias for $3;
v_set_origin int4;
begin
-- ----
***************
*** 3697,3714 ****
p_set_id;
end if;
-- ----
-- Create a SYNC event, run the script and generate the DDL_SCRIPT event
-- ----
- perform @NAMESPACE at .alterTableRestore(tab_id) from @NAMESPACE at .sl_table where tab_set in (select set_id from @NAMESPACE at .sl_set where set_origin = @NAMESPACE at .getLocalNodeId(''_ at CLUSTERNAME@''));
-
perform @NAMESPACE at .createEvent(''_ at CLUSTERNAME@'', ''SYNC'', NULL);
return 1;
end;
' language plpgsql;
! comment on function @NAMESPACE at .ddlScript_prepare (int4, int4) is
! 'Prepare for DDL script execution on origin';
-- perform @NAMESPACE at .ddlScript_int(p_set_id, p_script, p_only_on_node);
--- 3717,3747 ----
p_set_id;
end if;
+
+
+ if p_lock_tab_ids is not null then
+
+ perform @NAMESPACE at .alterTableRestore(tab_id) from @NAMESPACE at .sl_table where tab_set in (select set_id from @NAMESPACE at .sl_set where set_origin = @NAMESPACE at .getLocalNodeId(''_ at CLUSTERNAME@''))
+ and tab_id =any (p_lock_tab_ids)
+ ;
+ else
+
+ perform @NAMESPACE at .alterTableRestore(tab_id) from @NAMESPACE at .sl_table where tab_set in (select set_id from @NAMESPACE at .sl_set where set_origin = @NAMESPACE at .getLocalNodeId(''_ at CLUSTERNAME@''));
+ end if;
+
-- ----
-- Create a SYNC event, run the script and generate the DDL_SCRIPT event
-- ----
perform @NAMESPACE at .createEvent(''_ at CLUSTERNAME@'', ''SYNC'', NULL);
return 1;
end;
' language plpgsql;
!
! comment on function @NAMESPACE at .ddlScript_prepare (int4, int4,int4[]) is
! 'Prepare for DDL script execution on origin. All replicated tables will be prepared if
! lock_tab_ids is null otherwise just the specified tables will be prepared. ';
!
-- perform @NAMESPACE at .ddlScript_int(p_set_id, p_script, p_only_on_node);
***************
*** 3720,3756 ****
create or replace function @NAMESPACE at .ddlScript_complete (int4, text, int4)
returns integer
as '
declare
p_set_id alias for $1;
p_script alias for $2;
p_only_on_node alias for $3;
v_set_origin int4;
begin
perform @NAMESPACE at .updateRelname(p_set_id, p_only_on_node);
! perform @NAMESPACE at .alterTableForReplication(tab_id) from @NAMESPACE at .sl_table where tab_set in (select set_id from @NAMESPACE at .sl_set where set_origin = @NAMESPACE at .getLocalNodeId(''_ at CLUSTERNAME@''));
return @NAMESPACE at .createEvent(''_ at CLUSTERNAME@'', ''DDL_SCRIPT'',
! p_set_id, p_script, p_only_on_node);
end;
' language plpgsql;
comment on function @NAMESPACE at .ddlScript_complete(int4, text, int4) is
! 'ddlScript_complete(set_id, script, only_on_node)
After script has run on origin, this fixes up relnames, restores
triggers, and generates a DDL_SCRIPT event to request it to be run on
! replicated slaves.';
-- ----------------------------------------------------------------------
! -- FUNCTION ddlScript_prepare_int (set_id, only_on_node)
--
-- Prepare for the DDL_SCRIPT event
-- ----------------------------------------------------------------------
! create or replace function @NAMESPACE at .ddlScript_prepare_int (int4, int4)
returns int4
as '
declare
p_set_id alias for $1;
p_only_on_node alias for $2;
v_set_origin int4;
v_no_id int4;
v_row record;
--- 3753,3813 ----
create or replace function @NAMESPACE at .ddlScript_complete (int4, text, int4)
returns integer
as '
+ begin
+ return @NAMESPACE at .ddlScript_complete($1,$2,$3,null);
+ end;
+ ' language plpgsql;
+
+
+ -- perform @NAMESPACE at .ddlScript_int(p_set_id, p_script, p_only_on_node);
+
+ -- ----------------------------------------------------------------------
+ -- FUNCTION ddlScript_complete (set_id, script, only_on_node)
+ --
+ -- Generate the DDL_SCRIPT event
+ -- ----------------------------------------------------------------------
+ create or replace function @NAMESPACE at .ddlScript_complete (int4, text, int4,int4[])
+ returns integer
+ as '
declare
p_set_id alias for $1;
p_script alias for $2;
p_only_on_node alias for $3;
+ p_lock_tab_ids alias for $4;
v_set_origin int4;
begin
perform @NAMESPACE at .updateRelname(p_set_id, p_only_on_node);
!
! if p_lock_tab_ids is not null then
! perform @NAMESPACE at .alterTableForReplication(tab_id) from @NAMESPACE at .sl_table where tab_set in (select set_id from @NAMESPACE at .sl_set where set_origin = @NAMESPACE at .getLocalNodeId(''_ at CLUSTERNAME@'')) and tab_id = any (p_lock_tab_ids) ;
! else
! perform @NAMESPACE at .alterTableForReplication(tab_id) from @NAMESPACE at .sl_table where tab_set in (select set_id from @NAMESPACE at .sl_set where set_origin = @NAMESPACE at .getLocalNodeId(''_ at CLUSTERNAME@''));
! end if;
!
return @NAMESPACE at .createEvent(''_ at CLUSTERNAME@'', ''DDL_SCRIPT'',
! p_set_id, p_script, p_only_on_node,array_to_string(p_lock_tab_ids,'',''));
end;
' language plpgsql;
comment on function @NAMESPACE at .ddlScript_complete(int4, text, int4) is
! 'ddlScript_complete(set_id, script, only_on_node,p_lock_tab_ids)
After script has run on origin, this fixes up relnames, restores
triggers, and generates a DDL_SCRIPT event to request it to be run on
! replicated slaves. ';
-- ----------------------------------------------------------------------
! -- FUNCTION ddlScript_prepare_int (set_id, only_on_node,lock_table_ids)
--
-- Prepare for the DDL_SCRIPT event
-- ----------------------------------------------------------------------
! create or replace function @NAMESPACE at .ddlScript_prepare_int (int4, int4,int4[])
returns int4
as '
declare
p_set_id alias for $1;
p_only_on_node alias for $2;
+ p_lock_table_ids alias for $3;
v_set_origin int4;
v_no_id int4;
v_row record;
***************
*** 3788,3797 ****
return 0;
end if;
-- ----
-- Restore all original triggers and rules of all sets
-- ----
! for v_row in select * from @NAMESPACE at .sl_table
loop
perform @NAMESPACE at .alterTableRestore(v_row.tab_id);
end loop;
--- 3845,3856 ----
return 0;
end if;
+
-- ----
-- Restore all original triggers and rules of all sets
-- ----
! for v_row in select * from @NAMESPACE at .sl_table
! where tab_id =any (p_lock_table_ids) or p_lock_table_ids is null
loop
perform @NAMESPACE at .alterTableRestore(v_row.tab_id);
end loop;
***************
*** 3799,3805 ****
end;
' language plpgsql;
! comment on function @NAMESPACE at .ddlScript_prepare_int (int4, int4) is
'ddlScript_prepare_int (set_id, only_on_node)
Do preparatory work for a DDL script, restoring
--- 3858,3864 ----
end;
' language plpgsql;
! comment on function @NAMESPACE at .ddlScript_prepare_int (int4, int4,int4[]) is
'ddlScript_prepare_int (set_id, only_on_node)
Do preparatory work for a DDL script, restoring
***************
*** 3807,3828 ****
-- ----------------------------------------------------------------------
! -- FUNCTION ddlScript_complete_int (set_id, only_on_node)
--
-- Complete the DDL_SCRIPT event
-- ----------------------------------------------------------------------
! create or replace function @NAMESPACE at .ddlScript_complete_int (int4, int4)
returns int4
as '
declare
p_set_id alias for $1;
p_only_on_node alias for $2;
v_row record;
begin
-- ----
-- Put all tables back into replicated mode
-- ----
for v_row in select * from @NAMESPACE at .sl_table
loop
perform @NAMESPACE at .alterTableForReplication(v_row.tab_id);
end loop;
--- 3866,3889 ----
-- ----------------------------------------------------------------------
! -- FUNCTION ddlScript_complete_int (set_id, only_on_node,p_lock_table_ids)
--
-- Complete the DDL_SCRIPT event
-- ----------------------------------------------------------------------
! create or replace function @NAMESPACE at .ddlScript_complete_int (int4, int4,int4[])
returns int4
as '
declare
p_set_id alias for $1;
p_only_on_node alias for $2;
+ p_lock_table_ids alias for $3;
v_row record;
begin
-- ----
-- Put all tables back into replicated mode
-- ----
for v_row in select * from @NAMESPACE at .sl_table
+ where tab_id =any (p_lock_table_ids) or p_lock_table_ids is null
loop
perform @NAMESPACE at .alterTableForReplication(v_row.tab_id);
end loop;
***************
*** 3830,3836 ****
return p_set_id;
end;
' language plpgsql;
! comment on function @NAMESPACE at .ddlScript_complete_int(int4, int4) is
'ddlScript_complete_int(set_id, script, only_on_node)
Complete processing the DDL_SCRIPT event. This puts tables back into
--- 3891,3897 ----
return p_set_id;
end;
' language plpgsql;
! comment on function @NAMESPACE at .ddlScript_complete_int(int4, int4,int4[]) is
'ddlScript_complete_int(set_id, script, only_on_node)
Complete processing the DDL_SCRIPT event. This puts tables back into
***************
*** 5902,5904 ****
--- 5963,5994 ----
to specify fields for the passed-in tab_id.
In PG versions > 7.3, this looks like (field1,field2,...fieldn)';
+
+
+
+
+
+ -- ----------------------------------------------------------------------
+ -- FUNCTION getTabId(fully_qualifed_name)
+ -- Get the slony table id for the fully qualified name
+ --
+ --
+ -- ----------------------------------------------------------------------
+ create or replace function @NAMESPACE at .getTabId(text)
+ returns integer
+ as '
+ declare
+ p_fq_name alias for $1;
+ result integer;
+ begin
+
+ select into result tab_id from @NAMESPACE at .sl_table where
+ tab_nspname || ''.'' || tab_relname = p_fq_name limit 1;
+ return result;
+ end;
+ ' language plpgsql;
+
+ comment on function @NAMESPACE at .getTabId(text) is
+ 'Returns the slony table_id for the fully qualified (schema.tablename) passed.
+ If no such table exists null is returned.
+ ';
\ No newline at end of file
Index: src/slon/remote_worker.c
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slon/remote_worker.c,v
retrieving revision 1.131
diff -c -r1.131 remote_worker.c
*** src/slon/remote_worker.c 12 Dec 2006 20:13:01 -0000 1.131
--- src/slon/remote_worker.c 18 Dec 2006 04:27:57 -0000
***************
*** 1340,1360 ****
char *ddl_script = event->ev_data2;
int ddl_only_on_node = (int)strtol(event->ev_data3, NULL, 10);
int num_statements = -1, stmtno;
!
PGresult *res;
ExecStatusType rstat;
!
!
! slon_appendquery(&query1,
! "select %s.ddlScript_prepare_int(%d, %d); ",
! rtcfg_namespace,
! ddl_setid, ddl_only_on_node);
!
! if (query_execute(node, local_dbconn, &query1) < 0) {
! slon_log(SLON_ERROR, "remoteWorkerThread_%d: DDL preparation failed - set %d - only on node %\n",
! node->no_id, ddl_setid, ddl_only_on_node);
! slon_retry();
}
num_statements = scan_for_statements (ddl_script);
slon_log(SLON_CONFIG, "remoteWorkerThread_%d: DDL request with %d statements\n",
--- 1340,1371 ----
char *ddl_script = event->ev_data2;
int ddl_only_on_node = (int)strtol(event->ev_data3, NULL, 10);
int num_statements = -1, stmtno;
! const char * lock_table_list = event->ev_data4;
!
PGresult *res;
ExecStatusType rstat;
!
! if(lock_table_list != NULL )
! {
! slon_appendquery(&query1,
! "select %s.ddlScript_prepare_int(%d,%d, '{%s}'); ",
! rtcfg_namespace,
! ddl_setid, ddl_only_on_node,lock_table_list);
! }/*locks*/
! else
! {
! slon_appendquery(&query1,
! "select %s.ddlScript_prepare_int(%d, %d,null); ",
! rtcfg_namespace,
! ddl_setid, ddl_only_on_node);
! }
! if (query_execute(node, local_dbconn, &query1) < 0)
! {
! slon_log(SLON_ERROR, "remoteWorkerThread_%d: DDL preparation failed - set %d - only on node %d\n",
! node->no_id, ddl_setid, ddl_only_on_node);
! slon_retry();
}
+
num_statements = scan_for_statements (ddl_script);
slon_log(SLON_CONFIG, "remoteWorkerThread_%d: DDL request with %d statements\n",
***************
*** 1402,1411 ****
slon_log (SLON_CONFIG, "DDL success - %s\n", PQresStatus(rstat));
}
! slon_mkquery(&query1, "select %s.ddlScript_complete_int(%d, %d); ",
! rtcfg_namespace,
! ddl_setid,
! ddl_only_on_node);
/* DDL_SCRIPT needs to be turned into a log shipping script */
/* Note that the issue about parsing that mandates breaking
--- 1413,1437 ----
slon_log (SLON_CONFIG, "DDL success - %s\n", PQresStatus(rstat));
}
!
! if(lock_table_list != NULL )
! {
!
! slon_mkquery(&query1, "select %s.ddlScript_complete_int(%d,%d,'{%s}'); ",
! rtcfg_namespace,
! ddl_setid,
! ddl_only_on_node,lock_table_list);
!
! }
! else
! {
!
!
! slon_mkquery(&query1, "select %s.ddlScript_complete_int(%d, %d,null); ",
! rtcfg_namespace,
! ddl_setid,
! ddl_only_on_node);
! }
/* DDL_SCRIPT needs to be turned into a log shipping script */
/* Note that the issue about parsing that mandates breaking
***************
*** 6091,6093 ****
--- 6117,6122 ----
}
slon_log(SLON_DEBUG3, " compressed actionseq subquery... %s\n", dstring_data(action_subquery));
}
+
+
+
Index: src/slonik/parser.y
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slonik/parser.y,v
retrieving revision 1.27
diff -c -r1.27 parser.y
*** src/slonik/parser.y 31 Oct 2006 22:09:40 -0000 1.27
--- src/slonik/parser.y 18 Dec 2006 04:27:58 -0000
***************
*** 50,60 ****
O_USE_KEY,
O_WAIT_CONFIRMED,
O_WAIT_ON,
!
END_OF_OPTIONS = -1
} option_code;
/*
* Common given option list
*/
--- 50,70 ----
O_USE_KEY,
O_WAIT_CONFIRMED,
O_WAIT_ON,
! O_LOCK_TABLES,
END_OF_OPTIONS = -1
} option_code;
+ /**
+ * A structure that stores a list of table names as a linked list.
+ * Table names are stored in fully qualified format.
+ */
+ typedef struct table_list {
+ char * name;
+ struct table_list * next;
+ } table_list;
+
+
/*
* Common given option list
*/
***************
*** 63,69 ****
int lineno;
int32 ival;
char *str;
!
struct option_list *next;
} option_list;
--- 73,79 ----
int lineno;
int32 ival;
char *str;
! table_list * table_list;
struct option_list *next;
} option_list;
***************
*** 76,86 ****
int lineno;
int ival;
char *str;
} statement_option;
! #define STMT_OPTION_INT(_code,_dfl) {_code, -1, _dfl, NULL}
! #define STMT_OPTION_STR(_code,_dfl) {_code, -1, -1, _dfl}
! #define STMT_OPTION_YN(_code,_dfl) {_code, -1, _dfl, NULL}
! #define STMT_OPTION_END {END_OF_OPTIONS, -1, -1, NULL}
/*
--- 86,100 ----
int lineno;
int ival;
char *str;
+ table_list * table_list;
} statement_option;
! #define STMT_OPTION_INT(_code,_dfl) {_code, -1, _dfl, NULL,NULL}
! #define STMT_OPTION_STR(_code,_dfl) {_code, -1, -1, _dfl,NULL}
! #define STMT_OPTION_YN(_code,_dfl) {_code, -1, _dfl, NULL,NULL}
! #define STMT_OPTION_TBLLIST(_code,_dfl) {_code, -1,-1,NULL,_dfl}
! #define STMT_OPTION_END {END_OF_OPTIONS, -1, -1, NULL,NULL}
!
!
/*
***************
*** 109,114 ****
--- 123,129 ----
option_list *opt_list;
SlonikAdmInfo *adm_info;
SlonikStmt *statement;
+ table_list * table_list;
}
%type <ival> id
***************
*** 169,175 ****
%type <opt_list> option_item_id
%type <opt_list> option_item_literal
%type <opt_list> option_item_yn
!
/*
* Keyword tokens
--- 184,191 ----
%type <opt_list> option_item_id
%type <opt_list> option_item_literal
%type <opt_list> option_item_yn
! %type <table_list> table_list
! %type <table_list> table_list_items
/*
* Keyword tokens
***************
*** 247,253 ****
%token K_WAIT
%token K_SYNC
%token K_SLEEP
!
/*
* Other scanner tokens
*/
--- 263,269 ----
%token K_WAIT
%token K_SYNC
%token K_SLEEP
! %token K_TABLES
/*
* Other scanner tokens
*/
***************
*** 1345,1350 ****
--- 1361,1367 ----
STMT_OPTION_STR( O_FILENAME, NULL ),
STMT_OPTION_INT( O_EVENT_NODE, 1 ),
STMT_OPTION_INT( O_EXECUTE_ONLY_ON, -1 ),
+ STMT_OPTION_TBLLIST( O_LOCK_TABLES,NULL),
STMT_OPTION_END
};
***************
*** 1354,1366 ****
new->hdr.stmt_type = STMT_DDL_SCRIPT;
new->hdr.stmt_filename = current_file;
new->hdr.stmt_lno = $1;
!
if (assign_options(opt, $4) == 0)
{
new->ddl_setid = opt[0].ival;
new->ddl_fname = opt[1].str;
new->ev_origin = opt[2].ival;
new->only_on_node = opt[3].ival;
new->ddl_fd = NULL;
}
else
--- 1371,1410 ----
new->hdr.stmt_type = STMT_DDL_SCRIPT;
new->hdr.stmt_filename = current_file;
new->hdr.stmt_lno = $1;
!
if (assign_options(opt, $4) == 0)
{
+ table_list * lock_list_ptr;
+ int lock_count;
+ int table_ind;
new->ddl_setid = opt[0].ival;
new->ddl_fname = opt[1].str;
new->ev_origin = opt[2].ival;
new->only_on_node = opt[3].ival;
+ lock_count=0;
+ for(lock_list_ptr=opt[4].table_list;
+ lock_list_ptr!=NULL;
+ lock_list_ptr=lock_list_ptr->next)
+ {
+ lock_count++;
+ }
+
+ if(lock_count > 0 )
+ {
+ new->table_locks=malloc(sizeof(char*)*lock_count+1);
+ for(lock_list_ptr=opt[4].table_list,table_ind=0;
+ lock_list_ptr!=NULL;
+ lock_list_ptr=lock_list_ptr->next,table_ind++)
+ {
+ new->table_locks[table_ind] = lock_list_ptr->name;
+
+ }
+ new->table_locks[table_ind]=NULL;
+ }
+ else
+ {
+ new->table_locks=NULL;
+ }
new->ddl_fd = NULL;
}
else
***************
*** 1662,1667 ****
--- 1706,1712 ----
new->ival = 1;
new->str = NULL;
new->lineno = yylineno;
+ new->table_list=NULL;
new->next = NULL;
$$ = new;
***************
*** 1685,1690 ****
--- 1730,1736 ----
new->ival = -2;
new->str = NULL;
new->lineno = yylineno;
+ new->table_list=NULL;
new->next = NULL;
$$ = new;
***************
*** 1703,1708 ****
--- 1749,1755 ----
new->ival = -2;
new->str = NULL;
new->lineno = yylineno;
+ new->table_list=NULL;
new->next = NULL;
$$ = new;
***************
*** 1732,1737 ****
--- 1779,1796 ----
$3->opt_code = O_SECONDS;
$$ = $3;
}
+ | K_LOCK K_TABLES '=' table_list
+ {
+ option_list * new;
+
+ new = (option_list*) malloc(sizeof(option_list));
+ new->ival=-1;
+ new->str=NULL;
+ new->next=NULL;
+ new->table_list=$4;
+ new->opt_code = O_LOCK_TABLES;
+ $$=new;
+ }
;
option_item_id : id
***************
*** 1742,1747 ****
--- 1801,1807 ----
new->ival = $1;
new->str = NULL;
new->lineno = yylineno;
+ new->table_list=NULL;
new->next = NULL;
$$ = new;
***************
*** 1756,1761 ****
--- 1816,1822 ----
new->ival = -1;
new->str = $1;
new->lineno = yylineno;
+ new->table_list=NULL;
new->next = NULL;
$$ = new;
***************
*** 1770,1775 ****
--- 1831,1837 ----
new->ival = 1;
new->str = NULL;
new->lineno = yylineno;
+ new->table_list=NULL;
new->next = NULL;
$$ = new;
***************
*** 1782,1787 ****
--- 1844,1850 ----
new->ival = 0;
new->str = NULL;
new->lineno = yylineno;
+ new->table_list=NULL;
new->next = NULL;
$$ = new;
***************
*** 1829,1836 ****
;
lno :
! { $$ = yylineno; }
;
%%
--- 1892,1920 ----
;
lno :
! { $$ = yylineno; }
;
+ table_list : '(' table_list_items ')'
+ {
+ $$=$2;
+ }
+ ;
+
+
+ table_list_items : table_list_items ',' literal
+ {
+ $$ = malloc(sizeof(table_list));
+ $$->name=$3;
+ $$->next = $1;
+ }
+ | literal
+ {
+ $$ = malloc(sizeof(table_list));
+ $$->name = $1;
+ $$->next=NULL;
+ }
+ ;
+
%%
***************
*** 1876,1881 ****
--- 1960,1966 ----
case O_USE_KEY: return "key";
case O_WAIT_CONFIRMED: return "confirmed";
case O_WAIT_ON: return "wait on";
+ case O_LOCK_TABLES: return "lock tables";
case END_OF_OPTIONS: return "???";
}
return "???";
***************
*** 1924,1929 ****
--- 2009,2015 ----
s_opt->lineno = u_opt->lineno;
s_opt->ival = u_opt->ival;
s_opt->str = u_opt->str;
+ s_opt->table_list = u_opt->table_list;
}
return errors;
Index: src/slonik/scan.l
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slonik/scan.l,v
retrieving revision 1.26
diff -c -r1.26 scan.l
*** src/slonik/scan.l 31 Oct 2006 22:09:40 -0000 1.26
--- src/slonik/scan.l 18 Dec 2006 04:27:58 -0000
***************
*** 139,145 ****
update { return K_UPDATE; }
yes { return K_YES; }
wait { return K_WAIT; }
!
{digit}+ { return T_NUMBER; }
{identifier} { return T_IDENT; }
--- 139,145 ----
update { return K_UPDATE; }
yes { return K_YES; }
wait { return K_WAIT; }
! tables { return K_TABLES; }
{digit}+ { return T_NUMBER; }
{identifier} { return T_IDENT; }
Index: src/slonik/slonik.c
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slonik/slonik.c,v
retrieving revision 1.71
diff -c -r1.71 slonik.c
*** src/slonik/slonik.c 12 Dec 2006 14:54:48 -0000 1.71
--- src/slonik/slonik.c 18 Dec 2006 04:28:00 -0000
***************
*** 3834,3846 ****
char rex4[256];
PGresult *res;
ExecStatusType rstat;
#define PARMCOUNT 1
const char *params[PARMCOUNT];
int paramlens[PARMCOUNT];
int paramfmts[PARMCOUNT];
!
adminfo1 = get_active_adminfo((SlonikStmt *) stmt, stmt->ev_origin);
if (adminfo1 == NULL)
return -1;
--- 3834,3847 ----
char rex4[256];
PGresult *res;
ExecStatusType rstat;
+ SlonDString lock_tab_ids;
#define PARMCOUNT 1
const char *params[PARMCOUNT];
int paramlens[PARMCOUNT];
int paramfmts[PARMCOUNT];
!
adminfo1 = get_active_adminfo((SlonikStmt *) stmt, stmt->ev_origin);
if (adminfo1 == NULL)
return -1;
***************
*** 3863,3879 ****
dstring_nappend(&script, buf, rc);
}
dstring_terminate(&script);
-
dstring_init(&query);
slon_mkquery(&query,
! "select \"_%s\".ddlScript_prepare(%d, %d); ",
! stmt->hdr.script->clustername,
! stmt->ddl_setid, /* dstring_data(&script), */
! stmt->only_on_node);
!
if (db_exec_evcommand((SlonikStmt *) stmt, adminfo1, &query) < 0)
{
dstring_free(&query);
return -1;
}
--- 3864,3943 ----
dstring_nappend(&script, buf, rc);
}
dstring_terminate(&script);
dstring_init(&query);
+ dstring_init(&lock_tab_ids);
+
+ if(stmt->table_locks!=NULL)
+ {
+
+
+ char ** table;
+ /**
+ * Only Obtain locks on the specified tables.
+ */
+ dstring_append(&lock_tab_ids,"'{");
+ for(table=stmt->table_locks;
+ *table!=NULL;
+ table++)
+ {
+ int tab_id;
+ /**
+ * Get the tab_id for each
+ */
+ slon_mkquery(&query,
+ "select \"_%s\".getTabId('%s');",
+ stmt->hdr.script->clustername,*table);
+ res = db_exec_select((SlonikStmt*) stmt,adminfo1,&query);
+ if(res==NULL)
+ {
+ dstring_free(&query);
+ dstring_free(&lock_tab_ids);
+ return -1;
+ }
+ if(PQntuples(res)==0 || PQgetisnull(res,0,0) )
+ {
+ printf("%s is not a replicated table\n",*table);
+ dstring_free(&query);
+ dstring_free(&lock_tab_ids);
+ PQclear(res);
+ return -1;
+ }
+ tab_id = atoi(PQgetvalue(res,0,0));
+ PQclear(res);
+ if(table==stmt->table_locks)
+ {
+ sprintf(buf,"%d",tab_id);
+ dstring_append(&lock_tab_ids,buf);
+ }
+ else
+ {
+ sprintf(buf,",%d",tab_id);
+ dstring_append(&lock_tab_ids,buf);
+ }
+ }
+ dstring_append(&lock_tab_ids,"}'");
+
+ }
+ else {
+ dstring_append(&lock_tab_ids,"null");
+ }
+ dstring_terminate(&lock_tab_ids);
+ /**
+ * Lock replicated tables requested. (If none requested all tables get locked)
+ */
+
+ dstring_reset(&query);
slon_mkquery(&query,
! "select \"_%s\".ddlScript_prepare(%d, %d,%s); ",
! stmt->hdr.script->clustername,
! stmt->ddl_setid, /* dstring_data(&script), */
! stmt->only_on_node,
! dstring_data(&lock_tab_ids));
!
if (db_exec_evcommand((SlonikStmt *) stmt, adminfo1, &query) < 0)
{
dstring_free(&query);
+ dstring_free(&lock_tab_ids);
return -1;
}
***************
*** 3923,3932 ****
printf("Submit DDL Event to subscribers...\n");
! slon_mkquery(&query, "select \"_%s\".ddlScript_complete(%d, $1::text, %d); ",
stmt->hdr.script->clustername,
stmt->ddl_setid,
! stmt->only_on_node);
paramlens[PARMCOUNT-1] = 0;
paramfmts[PARMCOUNT-1] = 0;
--- 3987,3997 ----
printf("Submit DDL Event to subscribers...\n");
! slon_mkquery(&query, "select \"_%s\".ddlScript_complete(%d, $1::text, %d,%s); ",
stmt->hdr.script->clustername,
stmt->ddl_setid,
! stmt->only_on_node,
! dstring_data(&lock_tab_ids));
paramlens[PARMCOUNT-1] = 0;
paramfmts[PARMCOUNT-1] = 0;
***************
*** 3950,3955 ****
--- 4015,4021 ----
dstring_free(&script);
dstring_free(&query);
+ dstring_free(&lock_tab_ids);
return 0;
}
Index: src/slonik/slonik.h
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slonik/slonik.h,v
retrieving revision 1.29
diff -c -r1.29 slonik.h
*** src/slonik/slonik.h 31 Oct 2006 22:09:40 -0000 1.29
--- src/slonik/slonik.h 18 Dec 2006 04:28:00 -0000
***************
*** 408,413 ****
--- 408,414 ----
int ev_origin;
int only_on_node;
FILE *ddl_fd;
+ char ** table_locks;
};
Index: tests/testddl/exec_ddl.sh
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testddl/exec_ddl.sh,v
retrieving revision 1.2
diff -c -r1.2 exec_ddl.sh
*** tests/testddl/exec_ddl.sh 29 Mar 2006 17:10:31 -0000 1.2
--- tests/testddl/exec_ddl.sh 18 Dec 2006 04:28:00 -0000
***************
*** 5,8 ****
--- 5,16 ----
FILENAME = '${testname}/ddl_updates.sql',
EVENT NODE = 1
);
+
+ EXECUTE SCRIPT (
+ SET ID=1,
+ FILENAME = '${testname}/ddl_updates_tbl_locks.sql',
+ EVENT NODE=1,
+ LOCK TABLES=('public.table5','public.table1')
+
+ );
"
Index: tests/testddl/init_add_tables.ik
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testddl/init_add_tables.ik,v
retrieving revision 1.2
diff -c -r1.2 init_add_tables.ik
*** tests/testddl/init_add_tables.ik 9 Jan 2006 20:12:56 -0000 1.2
--- tests/testddl/init_add_tables.ik 18 Dec 2006 04:28:00 -0000
***************
*** 4,6 ****
--- 4,7 ----
set add table (id=3, set id=1, origin=1, fully qualified name = 'public.table3', key = SERIAL);
set add table (id=4, set id=1, origin=1, fully qualified name = 'public.table4');
set add table (id=5, set id=1, origin=1, fully qualified name = 'public.billing_discount');
+ set add table (id=6, set id=1, origin=1, fully qualified name='public.table5');
\ No newline at end of file
Index: tests/testddl/init_schema.sql
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testddl/init_schema.sql,v
retrieving revision 1.2
diff -c -r1.2 init_schema.sql
*** tests/testddl/init_schema.sql 9 Jan 2006 20:12:56 -0000 1.2
--- tests/testddl/init_schema.sql 18 Dec 2006 04:28:00 -0000
***************
*** 26,31 ****
--- 26,32 ----
primary key (id1, id2)
);
+
insert into table4 (data) values ('BA Baracus');
insert into table4 (data) values ('HM Murdoch');
insert into table4 (data) values ('Face');
***************
*** 48,50 ****
--- 49,61 ----
ALTER TABLE ONLY billing_discount
ADD CONSTRAINT billing_discount_pkey PRIMARY KEY (billing_discount_id);
+
+ CREATE TABLE table5 (
+
+ id serial4
+ ,a int4
+ ,PRIMARY KEY(id)
+ );
+
+ INSERT INTO table5(a) VALUES (1);
+ INSERT INTO table5(b) VALUES (2);
- Previous message: [Slony1-patches] Bug in slonik_execute_script.pl
- Next message: [Slony1-patches] Re: [Slony1-general] Execute script and "execute only on"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the Slony1-patches mailing list