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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070914/=
fdccaa4b/attachment-0001.htm