Slony1 PostgreSQL Replication - error in slon's query

Author

error in slon's query

Joachim Wieland

2005-07-02, 7:23 am

Hi,

I tried to replicate the pgbench example as described in the tutorial.
However I get an error when I start slon on the slave node. Can
anybody tell me if I've done something wrong or if this is a slon bug?

When I run

slon $CLUSTERNAME "dbname=$SLAVEDBNAME user=$REPLICATIONUSE
R host=$SLAVEHOST password=passwd"

that's what I get:

DEBUG2 remoteWorkerThread_1
: SYNC 1087 processing
DEBUG2 remoteWorkerThread_1
: syncing set 1 with 0 table(s) from provider 1
DEBUG2 remoteWorkerThread_1
: Didn't add or to provider
DEBUG3 remoteWorkerThread_1
: activate helper 1
DEBUG4 remoteWorkerThread_1
: waiting for log data
ERROR remoteWorkerThread_1
: "declare LOG cursor for select log_origin, log_xid, log_tableid, log_actionseq, log_cmdtype, log_cmddata from "_pgbench_rep".sl_log_1 where log_origin = 1 and ( order by log_actionseq; " PGRES_FATAL_ERROR ERROR: synt
ax error at or near "order" at character 170
ERROR remoteWorkerThread_1
: "close LOG; " PGRES_FATAL_ERROR ERROR: current transaction is aborted, commands ignored until end of transaction block
DEBUG2 remoteHelperThread_1
_1: 0.002 seconds until close cursor
DEBUG4 remoteHelperThread_1
_1: change helper thread
DEBUG4 remoteHelperThread_1
_1: send DONE/ERROR line toworker
DEBUG3 remoteHelperThread_1
_1: waiting for workgroup to finish
ERROR remoteWorkerThread_1
: helper 1 finished with error

(this is slony 1.1 and pgsql 8.0.3)

Thanks
Joachim

cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org

2005-07-02, 9:23 am

> Hi,
>
> I tried to replicate the pgbench example as described in the tutorial.
> However I get an error when I start slon on the slave node. Can
> anybody tell me if I've done something wrong or if this is a slon bug?
>
> When I run
>
> slon $CLUSTERNAME " dbname=3D$SLAVEDBNAM
E user=3D$REPLICATIONU
SER
> host=3D$SLAVEHOST password=3Dpasswd"
>
> that's what I get:
>
> DEBUG2 remoteWorkerThread_1
: SYNC 1087 processing
> DEBUG2 remoteWorkerThread_1
: syncing set 1 with 0 table(s) from provide=
r 1

Based just on the last line, I can point to the issue.

The problem is that you set up a subscription to a replication set that
involves no tables at all.

There is a pathology there that if you aren't replicating any tables, the
query that looks for table data fails in much the manner that you see.

We haven't looked to fixing it because it's kind of a silly case;
replication is arguably NOT broken because you're not replicating any
tables.

You'll presumably have to unsubscribe set 1 and create a new set that
actually *does* have tables in it.

Joachim Wieland

2005-07-02, 1:23 pm

On Sat, Jul 02, 2005 at 09:17:42AM -0400, cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org wrote:
[color=darkred]
> Based just on the last line, I can point to the issue.

> The problem is that you set up a subscription to a replication set that
> involves no tables at all.

> There is a pathology there that if you aren't replicating any tables, the
> query that looks for table data fails in much the manner that you see.

That was exactly the problem. Thanks!

> We haven't looked to fixing it because it's kind of a silly case;
> replication is arguably NOT broken because you're not replicating any
> tables.

> You'll presumably have to unsubscribe set 1 and create a new set that
> actually *does* have tables in it.

Ok, but a warning would be nice anyway.

Joachim

--
"Who is General Failure and why is he reading my hard disk ?"

Christopher Browne

2005-07-04, 9:24 am

Joachim Wieland wrote:

>On Sat, Jul 02, 2005 at 09:17:42AM -0400, cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org wrote:
>
>
>
>Ok, but a warning would be nice anyway.
>
>
>
I was about to add this only to discover that there already is a
warning, which was added in April, and thus is reported in 1.1.
-- ---
-- Check to see if the set contains any tables - gripe if not - bug
#1226
-- ---
if not exists (select true
from @NAMESPACE@.sl_table
where tab_set = p_sub_set) then
raise notice ''subscribeSet:: set % has no tables - risk of
problems - see bug 1226'', p_sub_set;
raise notice
''http://gborg.postgresql.org/project/slony1/bugs/bugupdate.php?1226'';
end if;

I just tested a little example:

cbbrowne@dba2:/tmp> cat
create_empty_set.slonik
Monday 10:47:51
cluster name = T1;
node 11 admin conninfo = 'dbname=slony_test1'
;
node 22 admin conninfo = 'dbname=slony_test2'
;

create set (id = 255, origin = 11, comment='blank empty set');
subscribe set (id=255, provider = 11, receiver = 22, forward = false);

cbbrowne@dba2:/tmp> slonik
create_empty_set.slonik
Monday 10:45:02
create_empty_set.slonik:6: NOTICE: subscribeSet:: set 255 has no tables
- risk of problems - see bug 1226
create_empty_set.slonik:6: NOTICE:
http://gborg.postgresql.org/project...update.php?1226
cbbrowne@dba2:/tmp>

The warning is there... It is (hopefully) somewhere in your PostgreSQL
logs...

Sponsored Links