Re: partitioning : replicate_partition doesn't seem to be working

From: "Ow Mun Heng" <ow(dot)mun(dot)heng(at)wdc(dot)com>
To: "Ow Mun Heng" <ow(dot)mun(dot)heng(at)wdc(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: partitioning : replicate_partition doesn't seem to be working
Date: 2008-10-18 08:19:19
Message-ID: 88C32A5D9FC71B4BB1B911B7B210496913D9CFF5@wdmyexbe03.my.asia.wdc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hmm... seems like a SNAFU on my part.

I didn't know that I need to execute it using EXECUTE SCRIPT via slonik.

I was doing it via pgadmin & psql.

Reading this :
http://lists.slony.info/pipermail/slony1-general/2008-October/008929.html

and trying it found the issue.

Thanks and sorry for the noise.

Ps : I can't count how many times I've bang my head against the wall on
these sort of things and when I decided to send the email to the list to ask
for help, then I solve it. :-)

-----Original Message-----
From: Ow Mun Heng
Sent: Saturday, October 18, 2008 4:07 PM
To: 'pgsql-general(at)postgresql(dot)org'
Subject: partitioning : replicate_partition doesn't seem to be working

Quoted from
http://www.nabble.com/Group-Sets-For-Replication-w--Partition-d19369646.html

> Basically, what you would need to do, weekly, is to run a slonik
> "execute script" script where the SQL script consists of something
> similar to what's in tests/testpartition/gen_ddl_sql.sh:

> -> create the new table, with indication of inheritace from the parent;

> -> create a rule to indicate when data gets inserted into the new
   table, assuming you're using rules to control this;

> -> run replicate_partition() on this newly-created table.

I've created the 2 tables w/ inheritance info into both the master and the
slave.

Then I've run the following

/*select
_hmxmms_my_cluster.replicate_partition(200810,'xmms'::text,'2replicate_test'
::text,NULL::text,'Replicate test partition 2'::text)*/

select
_hmxmms_my_cluster.add_empty_table_to_replication(10,200810,'xmms'::text,'2r
eplicate_test'::text,NULL::text,'Replicate test partition 2'::text)

This successfully adds the table into the set #10, put it as a tab_id of
200810 in sl_table. However, in sl_table, the tab_altered column/state will
still be marked as false.

In addition to that, the triggers are not even created.

If I do execute this directly

select
_hmxmms_my_cluster.setaddtable_int(10,200810,'xmms.2replicate_test','2replic
ate_test_pkey','')

Then the tab_altered state/column will be marked as true and the trigger
will appear

select * from pg_catalog.pg_trigger where tgname =
'_hmxmms_my_cluster_logtrigger_200810'

I've tried to debug the scripts but thus far, has not been able to pinpoint
the location in which it is failing.

Thanks

Browse pgsql-general by date

  From Date Subject
Next Message Daniel Verite 2008-10-18 09:35:51 Re: auto insert data every one minute
Previous Message Ow Mun Heng 2008-10-18 08:07:00 partitioning : replicate_partition doesn't seem to be working