Attached is my first attempt at a patch to allow EXECUTE SCRIPT to take a
user specified list of tables to lock.
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
ALTER TABLE table5 DROP COLUMN a;
ALTER TABLE table1 ADD COLUMN xy int4;
UPDATE table1 SET xy=1;
? 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 @[EMAIL PROTECTED] (int4, int4)
returns integer
+ as '
+ begin
+ return @[EMAIL PROTECTED]($1,$2,null);
+ end;
+ ' language plpgsql;
+
+
+ comment on function @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED](tab_id) from @[EMAIL PROTECTED] where tab_set
in (select set_id from @[EMAIL PROTECTED] where set_origin = @[EMAIL
PROTECTED](''[EMAIL PROTECTED]@''));
-
perform @[EMAIL PROTECTED](''[EMAIL PROTECTED]@'', ''SYNC'', NULL);
return 1;
end;
' language plpgsql;
! comment on function @[EMAIL PROTECTED] (int4, int4) is
! 'Prepare for DDL script execution on origin';
-- perform @[EMAIL PROTECTED](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 @[EMAIL PROTECTED](tab_id) from @[EMAIL PROTECTED] where
tab_set in (select set_id from @[EMAIL PROTECTED] where set_origin = @[EMAIL
PROTECTED](''[EMAIL PROTECTED]@''))
+ and tab_id =any (p_lock_tab_ids)
+ ;
+ else
+
+ perform @[EMAIL PROTECTED](tab_id) from @[EMAIL PROTECTED] where
tab_set in (select set_id from @[EMAIL PROTECTED] where set_origin = @[EMAIL
PROTECTED](''[EMAIL PROTECTED]@''));
+ end if;
+
-- ----
-- Create a SYNC event, run the script and generate the DDL_SCRIPT event
-- ----
perform @[EMAIL PROTECTED](''[EMAIL PROTECTED]@'', ''SYNC'', NULL);
return 1;
end;
' language plpgsql;
!
! comment on function @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED](p_set_id, p_script, p_only_on_node);
***************
*** 3720,3756 ****
create or replace function @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED](p_set_id, p_only_on_node);
! perform @[EMAIL PROTECTED](tab_id) from @[EMAIL PROTECTED] where tab_set
in (select set_id from @[EMAIL PROTECTED] where set_origin = @[EMAIL
PROTECTED](''[EMAIL PROTECTED]@''));
return @[EMAIL PROTECTED](''[EMAIL PROTECTED]@'', ''DDL_SCRIPT'',
! p_set_id, p_script, p_only_on_node);
end;
' language plpgsql;
comment on function @[EMAIL PROTECTED](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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED] (int4, text, int4)
returns integer
as '
+ begin
+ return @[EMAIL PROTECTED]($1,$2,$3,null);
+ end;
+ ' language plpgsql;
+
+
+ -- perform @[EMAIL PROTECTED](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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED](p_set_id, p_only_on_node);
!
! if p_lock_tab_ids is not null then
! perform @[EMAIL PROTECTED](tab_id) from @[EMAIL PROTECTED]
where tab_set in (select set_id from @[EMAIL PROTECTED] where set_origin =
@[EMAIL PROTECTED](''[EMAIL PROTECTED]@'')) and tab_id = any (p_lock_tab_ids) ;
! else
! perform @[EMAIL PROTECTED](tab_id) from @[EMAIL PROTECTED]
where tab_set in (select set_id from @[EMAIL PROTECTED] where set_origin =
@[EMAIL PROTECTED](''[EMAIL PROTECTED]@''));
! end if;
!
return @[EMAIL PROTECTED](''[EMAIL PROTECTED]@'', ''DDL_SCRIPT'',
! p_set_id, p_script,
p_only_on_node,array_to_string(p_lock_tab_ids,'',''));
end;
' language plpgsql;
comment on function @[EMAIL PROTECTED](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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED]
loop
perform @[EMAIL PROTECTED](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 @[EMAIL PROTECTED]
! where tab_id =any (p_lock_table_ids) or p_lock_table_ids is null
loop
perform @[EMAIL PROTECTED](v_row.tab_id);
end loop;
***************
*** 3799,3805 ****
end;
' language plpgsql;
! comment on function @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED]
loop
perform @[EMAIL PROTECTED](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 @[EMAIL PROTECTED] (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 @[EMAIL PROTECTED]
+ where tab_id =any (p_lock_table_ids) or p_lock_table_ids is null
loop
perform @[EMAIL PROTECTED](v_row.tab_id);
end loop;
***************
*** 3830,3836 ****
return p_set_id;
end;
' language plpgsql;
! comment on function @[EMAIL PROTECTED](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 @[EMAIL PROTECTED](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 @[EMAIL PROTECTED](text)
+ returns integer
+ as '
+ declare
+ p_fq_name alias for $1;
+ result integer;
+ begin
+
+ select into result tab_id from @[EMAIL PROTECTED] where
+ tab_nspname || ''.'' || tab_relname = p_fq_name limit 1;
+ return result;
+ end;
+ ' language plpgsql;
+
+ comment on function @[EMAIL PROTECTED](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);
_______________________________________________
Slony1-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/slony1-general
