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...

On 9/15/07, Andrew Hammond <andrew.george.hammond at gmail.com> wrote:
>
> On 9/14/07, Christopher Browne <cbbrowne at ca.afilias.info> wrote:
> >
> > Richard Yen <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 >
> >
> > 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
> http://lists.slony.info/mailman/listinfo/slony1-general
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070916/=
e18fa1ef/attachment-0001.htm