Seems I did it.

Now I am working on creation of standartized test suite
(slony1-2.0.1/tests/testexecutedml/).
The complete patch (with test suite) will be ready soon, I'll email it.

Here is some illustrations how I test it manually (note the new operator
"EXECUTE DML" which executes replicated DML script within the same
transaction as e.g. above "EXECUTE SCRIPT", so it is rolled back on any
errors together with other instructions):


echo "
    `./_preamble.sh`
    TRY {
        EXECUTE SCRIPT (
            SET ID =3D `./_setid.sh`,
            EVENT NODE =3D `./_masterid.sh`,
            FILENAME =3D '`
                echo 'ALTER TABLE public.a ADD COLUMN id_copy INTEGER' >
/tmp/a1;
                echo -n /tmp/a1;
            `'
        );

        EXECUTE DML (
            EVENT NODE =3D `./_masterid.sh`,
            FILENAME =3D '`
                echo 'UPDATE a SET id_copy =3D id' > /tmp/a2;
                echo -n /tmp/a2;
            `'
        );

        EXECUTE SCRIPT (
            SET ID =3D `./_setid.sh`,
            EVENT NODE =3D `./_masterid.sh`,
            FILENAME =3D '`
                echo 'ALTER TABLE public.a RENAME COLUMN id_copy TO
id_copy1' > /tmp/a4;
                echo -n /tmp/a4;
            `'
        );
    } ON ERROR {
        echo 'Failed to create the table!';
    }
" | su - postgres -c slonik




On Sat, Mar 14, 2009 at 5:12 PM, Dmitry Koterov <dmitry at koterov.ru> wrote:

> Now I am working to implement ability to use DDL + DML commands in the sa=
me
> 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
>> within a single transaction: sl_event SYNC contain information only about
>> xid, not 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 conte=
nt
>>> 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/20090329/=
94037fc2/attachment.htm