The primary key for the public.txrep table must be unique, and evidently
you already had a row with the same primary key. It seems likely that
the combination [username, email, signedby and ip] will very often be
duplicated, like every time you get another email from that person.
Try this:
- redefine txrep_pkey as a data retrieval index (dups allowed)
- use last_hit as the primary key. This should work provided that
CURRENT_TIMESTAMP ticks faster than new rows can arrive. (this may be
hardware dependent).
- Otherwise, create a sequence object and use that as the source of
primary key values. Using it this way will generate primary keys in
data arrival sequence and will not return duplicate values.
Martin
On Mon, 2019-12-09 at 13:28 +0100, Benny Pedersen wrote:
>
> 2019-12-09 12:07:53.477 UTC [16458] DETAIL: Key (username, email,
> signedby, ip)=(u...@example.org, u...@example.com, example.com, none)
> already exists.
> 2019-12-09 12:07:53.477 UTC [16458] STATEMENT: INSERT INTO txrep
> (username,email,ip,count,totscore,signedby) VALUES ($1,$2,$3,$4,$5,$6)
> 2019-12-09 12:07:53.479 UTC [16459] ERROR: duplicate key value
> violates
> unique constraint "txrep_pkey"
>
> --
> -- PostgreSQL database dump
> --
>
> -- Dumped from database version 11.4
> -- Dumped by pg_dump version 11.4
>
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> SET idle_in_transaction_session_timeout = 0;
> SET client_encoding = 'UTF8';
> SET standard_conforming_strings = on;
> SELECT pg_catalog.set_config('search_path', '', false);
> SET check_function_bodies = false;
> SET xmloption = content;
> SET client_min_messages = warning;
> SET row_security = off;
>
> SET default_tablespace = '';
>
> SET default_with_oids = false;
>
> --
> -- Name: txrep; Type: TABLE; Schema: public; Owner: spamassassin
> --
>
> CREATE TABLE public.txrep (
> username character varying(100) DEFAULT ''::character varying
> NOT
> NULL,
> email character varying(255) DEFAULT ''::character varying NOT
> NULL,
> ip character varying(40) DEFAULT ''::character varying NOT NULL,
> count bigint DEFAULT '0'::bigint NOT NULL,
> totscore double precision DEFAULT '0'::double precision NOT NULL,
> signedby character varying(255) DEFAULT ''::character varying
> NOT
> NULL,
> last_hit timestamp without time zone DEFAULT CURRENT_TIMESTAMP
> NOT
> NULL
> )
> WITH (fillfactor='95');
>
>
> ALTER TABLE public.txrep OWNER TO spamassassin;
>
> --
> -- Name: txrep txrep_pkey; Type: CONSTRAINT; Schema: public; Owner:
> spamassassin
> --
>
> ALTER TABLE ONLY public.txrep
> ADD CONSTRAINT txrep_pkey PRIMARY KEY (username, email,
> signedby,
> ip);
>
>
> --
> -- Name: txrep_last_hit; Type: INDEX; Schema: public; Owner:
> spamassassin
> --
>
> CREATE INDEX txrep_last_hit ON public.txrep USING btree (last_hit);
>
>
> --
> -- Name: txrep update_txrep_update_last_hit; Type: TRIGGER; Schema:
> public; Owner: spamassassin
> --
>
> CREATE TRIGGER update_txrep_update_last_hit BEFORE UPDATE ON
> public.txrep FOR EACH ROW EXECUTE PROCEDURE
> public.update_txrep_last_hit();
>
>
> --
> -- PostgreSQL database dump complete
> --
>
>
> how to solve this ?
