On 18/10/13 18:01, Amit Kapila wrote:
On Wed, Oct 16, 2013 at 5:55 PM, Stéphan BEUZE
<stephan.be...@douane.finances.gouv.fr> wrote:
The following query is performed concurrently by two threads logged in with
two different users:
WITH raw_stat AS (
SELECT
host(client_addr) as client_addr,
pid ,
usename
FROM
pg_stat_activity
WHERE
usename = current_user
)
INSERT INTO my_stat(id, client_addr, pid, usename)
SELECT
nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
FROM (
SELECT
client_addr, pid, usename
FROM
raw_stat s
WHERE
NOT EXISTS (
SELECT
NULL
FROM
my_stat u
WHERE
current_date = u.creation
AND
s.pid = u.pid
AND
s.client_addr = u.client_addr
AND
s.usename = u.usename
)
) t;
From time to time, I get the following error: "tuple concurrently updated"
I can't figure out what throw this error and why this error is thrown. Can
you shed a light ?
I have tried by using this query in a loop of 5000 and run the loop
in 2 different connections with different users, but could not get the
error.
What I understood from sql statement is that it will insert new
rows when there are new/different connections, so simply running this
sql statement
from 2 connections might not insert any new rows.
a. Are there any new connections happening, how this table is
getting populated?
b. How did you concluded that above sql statement leads to error,
because this error doesn't seem to occur in path of above sql
statement.
c. Are there any other sql statements in connection where you see this
error?
Can you explain a bit more about your scenario, so that this error
can be reproduced easily.
-------------------------------
Here is the sql definition of the table mystat.
**mystats.sql**
CREATE TABLE mystat
(
id bigint NOT NULL,
creation date NOT NULL DEFAULT current_date,
client_addr text NOT NULL,
pid integer NOT NULL,
usename name NOT NULL,
CONSTRAINT statistiques_connexions_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
Some comments about SQL statements:
a. table name provided as part of schema (mystat) is different
from one used in sql statement(my_stat)
b. definition of sequence mystat_sequence is missing, although it
doesn't seem to be necessary, but if you can provide the definition
you are using
then it will be better.
Stephen - what framework or system are you using to run these two
threads? That sort of error looks very like the type of thing you would
get by sharing the connection object/pointer between two threads...
Cheers
Mark
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
