Re: [Postgres-xc-developers] Nodes Out of Sync

[David E. W. <da...@ju...>]

On Jan 28, 2014, at 5:45 PM, David E. Wheeler <da...@ju...> wrote:

> I was trying to diagnose this issue:
> 
>    psql --set ON_ERROR_ROLLBACK=1 --set ON_ERROR_STOP=1 --file pg.sql
>    psql:pg.sql:3: ERROR:  SAVEPOINT is not yet supported.
> 
> The first three lines are:
> 
>    BEGIN;
> 
>    SET client_min_messages = warning;
>    CREATE SCHEMA sqitch;
> 
> I had thought that it was the CREATE SCHEMA statement it was complaining about, so I tried this:
> 
>    psql -d repcluster --set ON_ERROR_ROLLBACK=1 -c 'BEGIN; CREATE SCHEMA foo; COMMIT;'
>    ERROR:  Failed to PREPARE the transaction on one or more nodes

So, I think the problem is that ON_ERROR_ROLLBACK=1 implicitly uses savepoints. From the docs:

>            ON_ERROR_ROLLBACK
>                When on, if a statement in a transaction block generates an error,
>                the error is ignored and the transaction continues. When
>                interactive, such errors are only ignored in interactive sessions,
>                and not when reading script files. When off (the default), a
>                statement in a transaction block that generates an error aborts
>                the entire transaction. The on_error_rollback-on mode works by
>                issuing an implicit SAVEPOINT for you, just before each command
>                that is in a transaction block, and rolls back to the savepoint on
>                error.

So you guys might want to document that this option is not supported.

In fact, I was not able to get this example to work at all:

    $ psql --command 'BEGIN; CREATE SCHEMA foo; COMMIT;'
    ERROR:  Failed to PREPARE the transaction on one or more nodes

The log for my local data node has:

    2014-01-30 15:12:29 PST dwheeler 10.4.34.1(45978) 25740 52eadc5d.648c WARNING:  there is already a transaction in progress
    2014-01-30 15:12:29 PST dwheeler 10.4.34.1(45978) 25740 52eadc5d.648c WARNING:  there is no transaction in progress

Ah, I see, the docs for --command explain this:

    If the command string contains multiple SQL commands, they are
    processed in a single transaction,

So I guess I will get rid of the transaction stuff in my script.

Thanks,

David