Hannu Krosing wrote:

>I'm seeing 'query too complex' errors again.
>
>Last time I saw was a long time ago, after I had done several subscribe
>attempts that were rolled back and it generated 500Mb query when I
>finally got past the subscribe step.
>
>This time I get the same symptoms after doing a subscribe of a set with
>7 tables one of them over 2M. the subscribe itself took between 30 and
>60 minutes, but it still managest to make the query too big/complex .
>This query is "too complex" for the master (actually forwarding
>subscriber) which runs pg7.4.5, the final subscriber is running pg8.0.3
>
>The "complex" query is about 200Mb and looks like this
>
>declare LOG cursor for
> select log_origin, log_xid, log_tableid,     log_actionseq,
>log_cmdtype, log_cmddata
>   from "_balance_cluster".sl_log_1
>  where log_origin = 1
>   and ( (log_tableid in
>(1046,1048,1041,1037,1035,1031,1021,1016,1013,1011,1010,1009,1003,1002,1018,1019,1032,1012,1023,1030,1029,1028,1027,1026,1025,1
>         and (   log_xid < '1122646369'
>             and "_balance_cluster".xxid_lt_snapshot(log_xid,
>'1122646097:1122646369:''1122646367'',''1122646363'',''1122646341'',''1122646097'',''11226
>             )
>         and (   log_xid >= '1122645550'
>             and "_balance_cluster".xxid_ge_snapshot(log_xid,
>'1122645550:1122645552:''1122645550''')
>             )
>         )
>       or
>         (   log_tableid in (3,9008,9007,9005,9004,2002,2001)
>         and (   log_xid < '1122646369'
>             and "_balance_cluster".xxid_lt_snapshot(log_xid,
>'1122646097:1122646369:''1122646367'',''1122646363'',''1122646341'',''1122646097'',''11226
>             )
>         and ( log_xid >= '1122645804')
>         and log_actionseq not in ('376213267','376213268',
> ..this in list has ~16000 integers and is 192492 characters long.. )
>         )
>       )
> order by log_actionseq;
>
>What is it slon is trying to exclude with "log_actionseq not in(...)" ?
>
>Any good advice for moving forward or am I stuck with this one ?
>  
>
Oh, yeah, that was on my "to do" list, and got lost :-(

I ran into the same problem when too much time passed between (I think)
submitting SUBSCRIBE SET and then starting the slon for the subscriber.

The big part of the query is the "and log_actionseq not in (... ENORMOUS
LIST ...)" part, right?

Time to look into finding a way to compress that...  (What I'm thinking
of doing is to have that clause combine adjacent values together...)

I think you're a bit out of luck on this particular subscription.

What you might try when you restart it is to, during the subscription
period, bump up the amount of time per SYNC (e.g. - increase -s and -t
intervals) so that there aren't so many outstanding SYNCs when the
subscription gets going.