Philip Warner <pjw at rhyme.com.au> writes:
> It looks like my "out of memory" errors may have been caused by long
> running txs. At least I have one at the moment that seems to be; there
> is a single xid in sl_log_1 that has 45000 rows. The slon process dies
> every time it tries to process it.
>
> Is there a way around this? Is the slon process reading the whole tx
> into memory?
>
> Any help would bre appreciated...
>
> FWIW, the counts by XID from sl_log_1 are:
>
>  log_origin | log_xid | count
> ------------+---------+-------
>           3 | 4795427 | 45228
>           3 | 4795451 |     6
>           3 | 4795452 |     6
>           3 | 4795481 |     6
>           3 | 4795501 |     6
>           3 | 4795505 |     6
> ....etc for 8000 more rows.
>
> The large single xid has about 1.5GB of data and was caused by doing an
> "insert...select..." statement.
>
> Can slon be hacked to avoid buffering so much in memory?

I don't see any easy "slon hack" here; I'm not so sure that the
problem is actually within slon; it may be that the big transaction is
causing the backend to fall over, in which case trying to "fix" slon
would be a futile exercise...

The approach that comes to mind is more of a "data hack"; to chop out
that XID and apply it via "hacked" means.

Something like...

select log_actionseq, 
case 
  when log_cmdtype = 'I' then 'insert into ' || c.relname || ' ' 
  when log_cmdtype = 'U' then 'update only ' || c.relname || ' set ' 
  when log_cmdtype = 'D' then 'delete from only ' || c.relname || ' where ' 
end || log_cmddata as query
into keep_4795427_queries
from _someschema.sl_log_1, _someschema.sl_table t, pg_class c 
where 
  log_xid = '4795427' and
  t.tab_id = log_tableid and
  c.oid = t.tab_reloid;

Then you can dump the table keep_4795427_queries, which will consist
of the series of action IDs and the insert/delete/update queries.

It's a loose sort of "log shipping log."

Copy that to stdout, sort it (to get it in actionid order), and you
have something you can apply in as many pieces as you wish.

Delete from sl_log_1 where log_xid = '4795427' and you're done :-).

I daresay this update is Not Entirely Safe; there is *some* risk that
other updates in the same SYNC could interfere due to the fact that
they are being applied in a somewhat different order.  In effect,
those updates are taking place AFTER the big 1.5GB set of updates.

On the other hand, reality is that this 1.5GB set of updates probably
took a considerable period of time to apply; it probably was mostly
"in effect" before any of the other updates that arrived during that
SYNC.  So while not "safe," there's some reason to expect it not to be
"totally disagreeable."
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)