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: [email protected]
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle
_______________________________________________
Bucardo-general mailing list
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
