SEO

Dear list, I don't know whether pgsql-admin or the SA list is the
correct one to ask, so I start here. I have a bayes Postgres database
for SpamAssassin, which I use shared from 2 servers to have the same
content. I think this could be a locking issue or whatever, I don't
care. BUT: I have lots of "duplicate index" elements now, preventing a
restore or cleanup. Here's the dump and diff if anyone cares:
http://zmi.at/x/bayes_dup_idx.dump.bz2 (upload currently running)
http://zmi.at/x/bayes_dup_idx.diff.bz2

Could the problem be caused by any settings to the DB, or because of the
stored procedures? Normally there should be no way this can happen, so
what can cause this?

mfg zmi
--
// 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

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

iD8DBQBIA37hzhSR9xwSCbQRAosAAJ9ssydP1DfhJf5JcYtnN2 PLVVcA9gCfTpbU
t0WewrClcT7p/3jq/8wBXsk=
=ghrp
-----END PGP SIGNATURE-----

Michael Monnerie <michael.monnerie@it-management.at> writes:
> care. BUT: I have lots of "duplicate index" elements now, preventing a
> restore or cleanup.

What PG version is this?

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Michael Monnerie wrote:
> Dear list, I don't know whether pgsql-admin or the SA list is the
> correct one to ask, so I start here. I have a bayes Postgres database
> for SpamAssassin, which I use shared from 2 servers to have the same
> content. I think this could be a locking issue or whatever, I don't
> care.

What do you mean "shared from 2 servers"? Are these two servers using a
shared network mount with the database files? Or do you mean that these
two servers connect as clients to a single Postgres server?

What versions of things are all these running? Please be as specific as
humanly possible.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

Sorry:
# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.11 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20061115 (prerelease) (SUSE Linux)

It's the RPM installed from openSUSE 10.2

mfg zmi
--
// 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

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

iD8DBQBIA7EQzhSR9xwSCbQRAlcRAKCQfhrQAqiR0PNvd0ecLh eq26Y6KACgzEll
+x7UX5y2iXtIuhQh5pPhXbs=
=Iliu
-----END PGP SIGNATURE-----

On Montag, 14. April 2008 Alvaro Herrera wrote:
> What do you mean "shared from 2 servers"? *Are these two servers
> using a shared network mount with the database files? *Or do you mean
> that these two servers connect as clients to a single Postgres
> server?

Two servers have SA running, and connect via different users (who have
the same access rights) to the same Postgres server running the bayes
db.

> What versions of things are all these running? *Please be as specific
> as humanly possible.

# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.11 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20061115 (prerelease) (SUSE Linux)

It's the RPM installed from openSUSE 10.2

The SA versions are 3.2.0 from one server and 3.2.4 from the other.

mfg zmi
--
// 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

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

iD8DBQBIA7pDzhSR9xwSCbQRAtxNAJsHv6ht9aOeSXgrn6T5Bz F845TErwCfXSYz
vn2+7IV9jnBMU17PEV6Oa4s=
=DXN/
-----END PGP SIGNATURE-----

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
originally from CD, then 8.1.9 and now 8.1.11.

I'm currently investigating a way to delete duplicates from the table.
Is there any chance to do that? A
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
then run a "delete from.. where duplicates"? Sometimes there's 3x the
same content in the primary index, but I'd need to delete all except
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,
last_expire, last_atime_delta, last_expire_reduce, oldest_token_age,
newest_token_age) FROM stdin;
1 vscan 194393 517531 2602114 1206030039 2764800 15304
1203265204 1206057801
1 vscan 194398 517535 2602799 1206030039 2764800 15304
1203265204 1206064729
4 vscan 18305 25403 2042983 1208143427 1382400 13268
1206501543 1208216540
\.

Definition:
CREATE TABLE bayes_vars (
id serial NOT NULL,
username character varying(200) DEFAULT ''::character varying NOT
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 |
last_atime_delta | last_expire_reduce | oldest_token_age |
newest_token_age
----+----------+------------+-----------+-------------+-------------+------------------+--------------------+------------------+------------------
1 | vscan | 194393 | 517531 | 2602114 | 1206030039 |
2764800 | 15304 | 1203265204 | 1206057801
1 | vscan | 194398 | 517535 | 2602799 | 1206030039 |
2764800 | 15304 | 1203265204 | 1206064729
4 | vscan | 18375 | 25828 | 2050196 | 1208229525 |
1382400 | 13268 | 1206501543 | 1208240610
(3 Zeilen)

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

mfg zmi
--
// 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

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

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

On Montag, 14. April 2008 Michael Monnerie wrote:
> > What do you mean "shared from 2 servers"? *Are these two servers
> > using a shared network mount with the database files? *Or do you
> > mean that these two servers connect as clients to a single Postgres
> > server?
>
> Two servers have SA running, and connect via different users (who
> have the same access rights) to the same Postgres server running the
> bayes db.

SA=SpamAssassin, I should possibly explain. There are stored procedures
in the DB which get called, could the problem be connected to this?

mfg zmi
--
// 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

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

iD8DBQBIBPOizhSR9xwSCbQRAsXRAKC+zezt02RkqSxDjF3Uaw PhT69Z5wCfUTpT
3+xhXPOQQ3P51zLLbsOhGU0=
=bcYt
-----END PGP SIGNATURE-----

On Montag, 14. April 2008 Michael Monnerie wrote:
> problem with duplicate primary index entries...

I just got a warning in my Log:

Apr 16 01:52:19 db1.zmi.at postgres[21103]: [3-1] DB=bayes_pg_v1_dupidx
U=postgres H=[local] 48053eaf.526f VACUUM:WARNUNG:
Index »bayes_token_pkey« enthält 2539650 Zeilenversionen, aber Tabelle
enthält 4752337 Zeilenversionen
(Index contains 2539650 rows, but table has 4752337 rows)

BTW: I know that the DB language cannot be changed without recreating,
but can I change the language of the log messages?

mfg zmi
--
// 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

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

iD4DBQBIBUC7zhSR9xwSCbQRAmeZAJi4r/Jd8rkLbckWDKN8G/2InCp4AJ41tS8q
x1VJKM4yeyxdTQ5pL9jJ6Q==
=OkBh
-----END PGP SIGNATURE-----

On Mittwoch, 16. April 2008 Michael Monnerie wrote:
> problem with duplicate primary index entries...

I created the bayes db fresh, named it bayes_pg_v2 and re-relearned all
ham/spam from 3 concurrent connections into the db. No problem so far.

But I renamed the old, broken db and tried a
and got this:
INFO: »bayes_token«: 20567514 entfernbare, 4752337 nicht entfernbare
Zeilenversionen in 186274 Seiten gefunden
DETAIL: 0 tote Zeilenversionen können noch nicht entfernt werden.
Nicht entfernbare Zeilenversionen sind zwischen 56 und 56 Bytes lang.
Es gibt 0 unbenutzte Itemzeiger.
Gesamter freier Platz (einschließlich entfernbare Zeilenversionen) ist
1154820852 Bytes.
33480 Seiten sind leer oder werden leer werden, einschließlich 0 am Ende
der Tabelle.
186149 Seiten mit 1154819352 freien Bytes sind mögliche Ziele zum
Verschieben.

It looks like autovacuum - which is on - could not be run, and the db
growed a lot, and now a VACUUM cannot finish because of the duplicate
index:
INFO: Index »bayes_token_pkey« enthält 2539650 Zeilenversionen in 76273
Seiten
DETAIL: 0 Indexzeilenversionen wurde entfernt.
11727 Indexseiten wurden gelöscht, 11727 sind gegenwärtig
wiederverwendbar.
CPU 10.44s/2.49u sec elapsed 172.59 sec.
WARNUNG: Index »bayes_token_pkey« enthält 2539650 Zeilenversionen, aber
Tabelle enthält 4752337 Zeilenversionen
TIP: Bauen Sie den Index mit REINDEX neu.

But REINDEX doesn't work of course, because of the duplicates...

I'd like to drop that broken db, or should I keep it for analyzation?

mfg zmi
--
// 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

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

iD8DBQBIBwAvzhSR9xwSCbQRAq4uAKCSqqoN/HP0URn5CUeMHaqGqu8OJACgtvxU
QkHAepyFbTa3K31HbPxrIBs=
=4Jcp
-----END PGP SIGNATURE-----

Michael Monnerie <michael.monnerie@it-management.at> writes:
> I'd like to drop that broken db, or should I keep it for analyzation?

Looking back over the thread, it seems that the problems you saw could
be explained by the VACUUM-related bugs we fixed in 8.1.9 and 8.1.10;
although obviously this theory requires assuming that the dups were
there before you updated to 8.1.11. You didn't say much about time
frame so I don't know if that's plausible.

This latest bit with many fewer index entries than table entries is just
weird, though. The 8.1.10 VACUUM bug could have led to having *more*
index entries than table entries, but not the other way around.

Before you zap the database, would you be willing to make the actual
database files (not a dump, but the stored files) available? I'd
be interested to take a closer look at that index.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin