It's quite easy to reject BEGIN, COMMIT etc., because slonik splits its
input by ";".

The only complex part is SQL comments detection (/* ... */ and --), but
seems it could be done in scan_for_statements() - return NOT the first
position after a ";", but the first position after "; /* comment */ ", so
all next statements are always contain NO comments before them. So it's easy
to detect "BEGIN" etc. in the first position of a string.


On 9/16/07, Jan Wieck <JanWieck at yahoo.com> wrote:
>
> On 9/16/2007 5:19 AM, Dmitry Koterov wrote:
> > Could you please add a protection of submitting these commands in slonik
> > in future Slony versions?
> > I suppose ideally there should be no DDL script which causes the
> > replication to be broken via slonik...
>
> In an ideal world, one would have a chance to create foolproof software.
> Then again, would it be an ideal world if there still would be fools?
>
> I agree that we should reject those scripts that we can easily detect as
> broken. However, with the evolving grammar of Postgres' SQL dialect and
> the explicit requirement of Slony to support cross DB version
> replication, I don't see how that could ever become foolproof.
>
>
> Jan
>
>
> >
> > On 9/15/07, *Andrew Hammond* <andrew.george.hammond at gmail.com
> > <mailto:andrew.george.hammond at gmail.com>> wrote:
> >
> >     On 9/14/07, *Christopher Browne* < cbbrowne at ca.afilias.info
> >     <mailto:cbbrowne at ca.afilias.info>> wrote:
> >
> >         Richard Yen <dba at richyen.com <mailto:dba at richyen.com>> writes:
> >         >  Hi All,
> >         >
> >         >  Ran an EXECUTE SCRIPT command, but somewhere, the script
> >         failed.  The
> >         >  DDL statements were successfully executed on the provider,
> but
> >         for
> >         >  the two subscribers, naturally, nothing happened.
> >         >
> >         >  Now, I'm wondering what I need to do?  Manually add the
> >         columns in on
> >         >  the subscribers?  Attempt to drop the columns from the
> >         provider?  Do
> >         >  something else?
> >         >
> >         >  Here is the output of my slonik command:
> >         > > perform a single schema change
> >         > > DDL script consisting of 4 SQL statements
> >         > > DDL Statement 0: (0,6) [BEGIN;]
> >         > > <stdin>:6: WARNING:  there is already a transaction in
> progress
> >         > > DDL Statement 1: (6,57) [
> >         > > ALTER TABLE m_nodes ADD COLUMN crawl_host VARCHAR;]
> >         > > DDL Statement 2: (57,109) [
> >         > > ALTER TABLE m_nodes ADD COLUMN crawl_port SMALLINT;]
> >         > > DDL Statement 3: (109,117) [
> >         > > COMMIT;]
> >         > > Complete DDL Event...
> >         > > Event submission for DDL failed - PGRES_FATAL_ERROR
> >         > > <stdin>:6: WARNING:  there is no transaction in progress
> >         >
> >         >  Not sure if it helps much.  I'm still scouring around my logs
> >         for the
> >         >  statement that failed, but I don't think I can uncover it...
> >         >
> >         >  Any suggestions/little known facts about what I can do?
> >
> >         <http://slony.info/documentation/ddlchanges.html
> >         <http://slony.info/documentation/ddlchanges.html>>
> >
> >         I can point to where the problem lies:
> >
> >            "The script must not contain transaction BEGIN or END
> statements,
> >             as the script is already executed inside a transaction."
> >
> >         I see a BEGIN and a COMMIT, which *guarantees* that the
> submission
> >         works wrongly.
> >
> >         If you submit a DDL script consisting of:
> >
> >         BEGIN;
> >         ALTER TABLE DROP COLUMN CRAWL_HOST;
> >         ALTER TABLE DROP COLUMN CRAWL_PORT;
> >         COMMIT;
> >
> >         (which re-commits, in the opposite fashion, the same sin that
> >         the first script did)
> >
> >         That should put the "master" back into the appropriate form.
> >
> >         You could then resubmit a slonik DDL script consisting of
> >         ALTER TABLE m_nodes ADD COLUMN crawl_host VARCHAR;
> >         ALTER TABLE m_nodes ADD COLUMN crawl_port SMALLINT;
> >         WITH NEITHER A BEGIN NOR A COMMIT IN IT...
> >
> >         If you're lucky, then you have not submitted any updates that
> would
> >         have tried to add new tuples to table m_nodes, and this DDL
> script
> >         should be able to propagate.
> >
> >         If there are tuples queued up with
> >         m_nodes.crawl_port/m_nodes.crawl_host, sitting in sl_log_1, then
> I'm
> >         not quite sure offhand what to suggest.
> >
> >
> >     You mean aside from reading the manual before using an admittedly
> >     bare-metal tool? :)
> >
> >     Actually, I think this points out a potential new feature in slonik:
> >     detect and refuse to run EXECUTE DDL commands which include
> >     transaction management statements (BEGIN, COMMIT, ROLLBACK,
> >     SAVEPOINT and RELEASE).
> >
> >     Oh, and we probably need to update the documentation to talk about
> >     savepoints too. I can't think of any legitimate way to use them, but
> >     then again, it is a Friday afternoon so maybe I'm just being dense.
> >
> >     Andrew
> >
> >
> >     _______________________________________________
> >     Slony1-general mailing list
> >     Slony1-general at lists.slony.info <mailto:
> Slony1-general at lists.slony.info>
> >     http://lists.slony.info/mailman/listinfo/slony1-general
> >
> >
> >
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > Slony1-general mailing list
> > Slony1-general at lists.slony.info
> > http://lists.slony.info/mailman/listinfo/slony1-general
>
>
> --
> #=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D JanWieck at Yahoo.com #
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070917/=
8bb28388/attachment-0001.htm