From: Michael Monnerie <michael.monnerie@it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Re: duplicate primary index in bayes db from SpamAssassin
Date: Tue, 15 Apr 2008 08:26:31 +0200
User-Agent: KMail/1.9.6 (enterprise 20070904.708012)
References: <200804141757.21574@zmi.at> <4501.1208188801@sss.pgh.pa.us>
In-Reply-To: <4501.1208188801@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart18600185.XPXWiJ6995";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200804150826.37501@zmi.at>

--nextPart18600185.XPXWiJ6995
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On Montag, 14. April 2008 Tom Lane wrote:
> What PG version is this?

I've looked a little more into history, and PostgreSQL was 8.1.5=20
originally from CD, then 8.1.9 and now 8.1.11.

I'm currently investigating a way to delete duplicates from the table.=20
Is there any chance to do that? A=20
SELECT * from bayes_token;
shows no dups, as there's a primary index... at least it works here.

This is the bayes_token table and its index:
CREATE TABLE bayes_token (
    id integer DEFAULT 0 NOT NULL,
    token bytea DEFAULT ''::bytea NOT NULL,
    spam_count integer DEFAULT 0 NOT NULL,
    ham_count integer DEFAULT 0 NOT NULL,
    atime integer DEFAULT 0 NOT NULL
);
ALTER TABLE ONLY bayes_token
    ADD CONSTRAINT bayes_token_pkey PRIMARY KEY (id, token);

Could I create a new db without indices, restore the data there, and=20
then run a "delete from.. where duplicates"? Sometimes there's 3x the=20
same content in the primary index, but I'd need to delete all except=20
the one with the highest spam_count.

I just found I even have a duplicate in bayes_vars:

COPY bayes_vars (id, username, spam_count, ham_count, token_count,=20
last_expire, last_atime_delta, last_expire_reduce, oldest_token_age,=20
newest_token_age) FROM stdin;
1       vscan   194393  517531  2602114 1206030039      2764800 15304  =20
1203265204      1206057801
1       vscan   194398  517535  2602799 1206030039      2764800 15304  =20
1203265204      1206064729
4       vscan   18305   25403   2042983 1208143427      1382400 13268  =20
1206501543      1208216540
\.

Definition:
CREATE TABLE bayes_vars (
    id serial NOT NULL,
    username character varying(200) DEFAULT ''::character varying NOT=20
NULL,
    spam_count integer DEFAULT 0 NOT NULL,
    ham_count integer DEFAULT 0 NOT NULL,
    token_count integer DEFAULT 0 NOT NULL,
    last_expire integer DEFAULT 0 NOT NULL,
    last_atime_delta integer DEFAULT 0 NOT NULL,
    last_expire_reduce integer DEFAULT 0 NOT NULL,
    oldest_token_age integer DEFAULT 2147483647 NOT NULL,
    newest_token_age integer DEFAULT 0 NOT NULL
);
ALTER TABLE ONLY bayes_vars
    ADD CONSTRAINT bayes_vars_pkey PRIMARY KEY (id);

But here, I could see the dups with SELECT, and even delete one record:

# select * from bayes_vars;
 id | username | spam_count | ham_count | token_count | last_expire |=20
last_atime_delta | last_expire_reduce | oldest_token_age |=20
newest_token_age
=2D---+----------+------------+-----------+-------------+-------------+----=
=2D-------------+--------------------+------------------+------------------
  1 | vscan    |     194393 |    517531 |     2602114 |  1206030039 |      =
   =20
2764800 |              15304 |       1203265204 |       1206057801
  1 | vscan    |     194398 |    517535 |     2602799 |  1206030039 |      =
   =20
2764800 |              15304 |       1203265204 |       1206064729
  4 | vscan    |      18375 |     25828 |     2050196 |  1208229525 |      =
   =20
1382400 |              13268 |       1206501543 |       1208240610
(3 Zeilen)

bayes_pg_v1=3D# delete from bayes_vars where spam_count =3D194393;
DELETE 1
bayes_pg_v1=3D# select * from bayes_vars;
 id | username | spam_count | ham_count | token_count | last_expire |=20
last_atime_delta | last_expire_reduce | oldest_token_age |=20
newest_token_age
=2D---+----------+------------+-----------+-------------+-------------+----=
=2D-------------+--------------------+------------------+------------------
  1 | vscan    |     194398 |    517535 |     2602799 |  1206030039 |      =
   =20
2764800 |              15304 |       1203265204 |       1206064729
  4 | vscan    |      18375 |     25829 |     2050215 |  1208229525 |      =
   =20
1382400 |              13268 |       1206501543 |       1208240637
(2 Zeilen)

mfg zmi
=2D-=20
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4

--nextPart18600185.XPXWiJ6995
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)

iD8DBQBIBEqdzhSR9xwSCbQRAsfeAJ4y1ElbYuiIy5e6VWJK55+VBLl2+QCfb6CB
gXGPhHm6GpycgFubsT2I+hQ=
=Hi+r
-----END PGP SIGNATURE-----

--nextPart18600185.XPXWiJ6995--