On Mon, 2006-05-15 at 11:17 -0400, Jan Wieck wrote:
> I suspect this is yet another failed attempt to merge before the 
> ENABLE_SUBSCRIPTION has been processed. I guess some day we will have to 
> remove the whole ENABLE_SUBSCRIPTION part but instead let the 
> SUBSCRIBE_SET travel from the origin instead.
> 
I'm pretty sure this is not it.  My log table was already successfully
merged and synced prior to the state transition call and I am not
performing any merge within my state transition.

I have built my test system again from scratch and have run new tests as
below.  Sorry this is so long and tedious - I want to be sure there are
no errors or ommisions.

After replication set-up, installation of my partition rolling objects,
and creation of an initial partition (perf_metric_old) I select from my
log table (which is in replication set 1):

marc-# select * from partitions.partition_log;
 partition_name  | start_state_id | end_state_id |     completed_time
| errmsg
-----------------+----------------+--------------+------------------------+--------
 perf_metric_old |              4 |            5 | 2001-01-01
00:00:00-06 |
(1 row)

This is identical on both provider and subscriber.

To test replication on this table, I manually insert a record
('dummy_dummy') at the provider and query it at the subscriber:

marc=# select * from partitions.partition_log ;
 partition_name  | start_state_id | end_state_id |        completed_time
| errmsg
-----------------+----------------+--------------+-------------------------------+--------
 perf_metric_old |              4 |            5 | 2001-01-01
00:00:00-06  |
 dummy_dummy     |              4 |            5 | 2006-05-15
10:29:00.177674-06 |
(2 rows)

I now perform my first state transition - this creates a new partition
table at each node and records the fact in the partitions_log table.

On the provider, I have:

marc=# select * from partitions.partition_log ;
   partition_name   | start_state_id | end_state_id |
completed_time     | errmsg
--------------------+----------------+--------------+-------------------------------+--------
 perf_metric_old    |              4 |            5 | 2001-01-01
00:00:00-06     |
 dummy_dummy        |              4 |            5 | 2006-05-15
10:29:00.177674-06 |
 perf_metric_200605 |              1 |            2 | 2006-05-15
10:33:40.143371-06 |
(3 rows)

And on the subscriber:

marc=# select * from partitions.partition_log ;
 partition_name  | start_state_id | end_state_id |        completed_time
| errmsg
-----------------+----------------+--------------+-------------------------------+--------
 perf_metric_old |              4 |            5 | 2001-01-01
00:00:00-06  |
 dummy_dummy     |              4 |            5 | 2006-05-15
10:29:00.177674-06 |
(2 rows)

I now manually delete the record I manually inserted earlier and on the
provider I get:


marc=# select * from partitions.partition_log ;    partition_name   |
start_state_id | end_state_id |        completed_time     | errmsg
--------------------+----------------+--------------+-------------------------------+--------
 perf_metric_old    |              4 |            5 | 2001-01-01
00:00:00-06     |
 perf_metric_200605 |              1 |            2 | 2006-05-15
10:33:40.143371-06 |
(2 rows)

And on the subscriber:

marc=# select * from partitions.partition_log ;
 partition_name  | start_state_id | end_state_id |     completed_time
| errmsg
-----------------+----------------+--------------+------------------------+--------
 perf_metric_old |              4 |            5 | 2001-01-01
00:00:00-06 |
(1 row)

So, replication worked prior to and after my state transition but
apparantly not within the state transition transaction itself.  Here is
what is done in the transaction:

1) Create temporary replication set 999999998 using _slony.storeset
2) Create partition using sql statement 'create table
perf_metric_200605() inherits (perf_metric_base)'.  To make this happen
at all nodes, we use _slony.ddlscript()
3) Create indexes for new partition by calling partitions.copy_indexes
using _slony.ddlscript()
4) Insert a log of the successful state transition into
partitions.partition_log

To proovide a little more context, the set-up of a new partition
involves a number of discrete time-separated steps.  Tests are made at
each step to ensure the previous one completed in order to avoid the
merge problem.  The steps are:
1) create partition table and a temporary set to merge it into.  This is
the step I have tested above.
2) Add the table to the temporary set and subscribe the set to
subscriber nodes
3) merge the temporary set with the primary set
4) activate the new partition by changing the views and instead-of
triggers


I will continue to investigate this myself but any and all suggestions
will be appreciated.  

I am next going to try moving the insert to the start of the transaction
to see if the logtrigger dislikes firing after a ddlscript call.

__
Marc


> 
> Jan
> 
> 
> On 5/12/2006 8:16 PM, Marc Munro wrote:
> 
> > I have an application that builds new partitions on a monthly basis, and
> > adds them to the primary replication set.  Each new partition goes
> > through a number of states before becoming the active partition with
> > each state change logged in a replicated table.
> > 
> > My log of state changes is not being properly replicated.  If I manually
> > insert into the log table, the change gets successfully replicated.  If
> > my automated script does it, the change is not replicated.
> > 
> > The state transitions involve creating new temporary replication sets,
> > creating new tables at each node, adding tables to the temporary
> > replication set, subscribing sets, merging sets, and so on.
> > 
> > A state transition is performed within a single transaction and may
> > involve calls to _slony_schema.ddlscript().  All state transitions
> > finish, if successful, with an insert into my log table.
> > 
> > Is the use of ddlscript within the same transaction as the insert
> > somehow disabling the normal logtrigger actions?  Any other
> > possibilities?  Is there a simple workaround?
> > 
> > All ideas and suggestions are welcomed.  Thanks.
> > __
> > Marc
> > 
> > 
> > ------------------------------------------------------------------------
> > 
> > _______________________________________________
> > Slony1-general mailing list
> > Slony1-general at gborg.postgresql.org
> > http://gborg.postgresql.org/mailman/listinfo/slony1-general
> 
> 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 191 bytes
Desc: This is a digitally signed message part
Url : http://gborg.postgresql.org/pipermail/slony1-general/attachments/20060515/a67d3373/attachment.bin