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.
-- 
output = reverse("ofni.sesabatadxunil" "@" "enworbbc")
http://cbbrowne.com/info/unix.html
Rules of the Evil Overlord  #162. "If I steal something very important
to the hero, I will not put it on public display.
<http://www.eviloverlord.com/>