Now I am working to implement ability to use DDL + DML commands in the same
transaction of slonik call for fully transparent slonik usage. Seems it's
possible nevertheless, but needs some patching work (at least, log and use
txid and sl_action_seq in SYNC before and after DDL_SCRIPT).

I will email here when this work will succeed or fail.


On Mon, Mar 9, 2009 at 1:56 AM, Dmitry Koterov <dmitry at koterov.ru> wrote:

> Unfortunately, this method does not work.
>
> Because we cannot have more than one separated group of sl_log items with=
in
> a single transaction: sl_event SYNC contain information only about xid, n=
ot
> about sequence of sl_log item. So, if we have
>
> xid=3D123: (0) SYNC
> xid=3D123: (1) DDL_SCRIPT
> xid=3D123: (2) SYNC
> xid=3D123: (3) DDL_SCRIPT
>
> then slon cannot detect are sl_log items of (2) SYNC were performed *
> before* (3) DDL_SCRIPT or *after* it. And the following sequence becomes
> broken:
>
> (1) ALTER TABLE a ADD COLUMN id_copy INTEGER;
> (2) UPDATE a SET id_copy =3D id;
> (3) ALTER TABLE a RENAME COLUMN id_copy TO abcd;
>
> But, while digging the source, I have suddenly found 2 bugs. I reported
> them here (with solution patches attached):
> http://www.slony.info/bugzilla/show_bug.cgi?id=3D75
>
>
>
>
> On Sat, Mar 7, 2009 at 1:40 AM, Dmitry Koterov <dmitry at koterov.ru> wrote:
>
>> Hello.
>>
>> I work with Slony for about 2 years in a heavy-loaded web site. I'd like
>> to mix DDL and DML code to perform them in a single transaction (in
>> deployment procedure it would be very handy and important).
>>
>> Digging the source of slonik and _schemadoc I assumed that I possibly
>> could do it via slonik
>> (for better readability I replaced FILENAME=3D'xxx' clause by the content
>> of xxx itself):
>>
>>
>> -- Perform my DDL modifications.
>> EXECUTE SCRIPT {
>>   ALTER TABLE tbl ADD COLUMN id_copy INTEGER;
>> }
>>
>> EXECUTE SCRIPT {
>>   -- This is needed to deny execution of this SQL in other nodes.
>>   EXECUTE ONLY ON =3D <my_origin_node>
>>
>>   -- Temporariy turn on replication mode.
>>   SET session_replication_role TO origin;
>>   -- Perform my DML operations. They will be replicated via logTrigger as
>> usual.
>>   UPDATE tbl SET id_copy =3D id;
>>   UPDATE tbl SET abc =3D 123;
>>   -- Reset replication mode back.
>>   SET session_replication_role TO local;
>> }
>>
>> -- Perform any other DDL modifications.
>> EXECUTE SCRIPT {
>>   ALTER TABLE tbl ADD COLUMN c INTEGER;
>> }
>> ...
>>
>> Please say would this method work without side effects or not?
>> Thank you!
>>
>> B.r.,
>>   Dmitry Koterov,
>>   chief architect at MoiKrug.Ru, Yandex, Moscow.
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20090314/=
81e19013/attachment.htm