[Bucardo-general] b5: delta_ rows not being deleted

[Bill McGonigle]

On 02/12/2013 02:10 PM, Greg Sabino Mullane wrote:
> The actual function is in bucardo.schema: that's what does the actual
> work in deleting old rows.

Just getting back to this; I think I see where the problem is coming 
from, but if somebody could correct/validate my understanding that would 
be great.  I think there might be some postgresql tricks going on here 
that I'm missing.

For this example, I'm looking at table public.saved_cart in database 
ecommerce.

I the schema, we get to:

>                       -- See how many dbgroups are being used by this table
>                       SELECT INTO drows
>                         COUNT(DISTINCT target)
>                         FROM bucardo.bucardo_delta_targets
>                         WHERE tablename::regclass::text = \$2;

and I do:

ecommerce=# SELECT COUNT(DISTINCT target) FROM 
bucardo.bucardo_delta_targets WHERE tablename::regclass::text = 
'saved_cart';
  count
-------
      1
(1 row)


OK, then on to:

>                      myst = 'DELETE FROM bucardo.'
>                       || deltatable
>                       || ' USING (SELECT txntime AS tt FROM bucardo.'
>                       || tracktable
>                       || ' GROUP BY 1 HAVING COUNT(*) = '
>                       || drows
>                       || ') AS foo'
>                       || ' WHERE txntime = tt'
>                       || ' AND txntime < now() - interval '
>                       || quote_literal(\$1);
>

Starting on the inside, I ask:

ecommerce=# SELECT txntime AS tt FROM bucardo.track_public_saved_cart 
GROUP BY 1 HAVING COUNT(*) = 1;
               tt
-------------------------------
  2013-06-20 14:46:03.436795-04
  2013-06-20 14:46:03.439999-04
  2013-06-20 14:46:03.440761-04
  2013-06-20 14:46:03.441479-04
  2013-06-20 14:46:22.016835-04
  2013-06-20 14:46:25.823726-04
  2013-06-20 14:46:28.140117-04
  2013-06-20 14:46:28.141634-04
  2013-06-20 14:46:28.142479-04
  2013-06-20 14:47:46.14217-04
(10 rows)

n.b. without the HAVING clause I get:

ecommerce=# SELECT count(txntime) AS tt FROM 
bucardo.track_public_saved_cart;
    tt
---------
  4473419
(1 row)

but then, onto the full statement, as a SELECT instead of DELETE for 
investigation purposes, and I get:

ecommerce=# SELECT count(txntime) FROM bucardo.delta_public_saved_cart 
USING (SELECT txntime AS tt FROM bucardo.track_public_saved_cart GROUP 
BY 1) AS foo WHERE txntime = tt AND txntime < now() - interval '45 seconds';
ERROR:  syntax error at or near "USING"
LINE 1: ...unt(txntime) FROM bucardo.delta_public_saved_cart USING (SEL...

I can't seem to find documentation about using 'USING' without a JOIN 
(is it implicit?).

These are on v8.4 - I'm wondering if either of these might behave 
differently on v9.  Equally as likely I'm missing something.

Thanks,
-Bill

-- 
Bill McGonigle, Owner
BFC Computing, LLC
http://bfccomputing.com/
Telephone: +1.855.SW.LIBRE
Email, IM, VOIP: bill at bfccomputing.com
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle