Subject: Primary key constraint violation without error?
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Mob-Art
Date: Mon, 24 Jul 2006 14:12:39 +0200
Message-Id: <1153743159.28381.15.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Actually it's not violation, but I get no error message on violating
it...

The story is like this. I have few tables from where I extract
messageIds for particular users. Then, last 100 messages for each user I
transfer to spare table, to do something with that. That spare table has
strange behaviour.

I have two functions. First one extract last 100 messageIds for
particular user, and second one finds all the users, and then inserts
last 100 messageIds for particular user. 

The table definition is like this:


CREATE TABLE mes_del
(
  "messageId" int4 NOT NULL,
  CONSTRAINT pk PRIMARY KEY ("messageId")
) 
WITHOUT OIDS;

And the two functions are like this:
-- this function returns last 100 messageIds for particular user
CREATE OR REPLACE FUNCTION punibrisitablica(int4)
  RETURNS SETOF mes_del AS
$BODY$SELECT	
	messages.id as messagesId
FROM
	users
	JOIN phone_numbers
		ON users.id = phone_numbers.user_id
	JOIN messages
		ON messages.phone_number = phone_numbers.phone_number
where
	users.id = $1
order by
	messages.time desc
limit 100;$BODY$
  LANGUAGE 'sql' VOLATILE;


-- this function goes trough all the users and inserts messageIds
-- to table mes_del
CREATE OR REPLACE FUNCTION punimessages()
  RETURNS bool AS
$BODY$

declare
	userId users%ROWTYPE;

begin

	truncate table mes_del;

	FOR userId IN
		SELECT users.id FROM users ORDER BY users.id DESC limit 5
	LOOP
		INSERT INTO mes_del SELECT * FROM puniBrisiTablica(userId.id);
		RAISE NOTICE 'Ubacili smo za usera %.', userId.id;
	END LOOP;

	return true;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

I appologize for the line breaks, but Evolution is a bit stupid email
client...

In second function there is LIMIT 5, because there are 40000 users in my
database, and it's going to be easier to explain like this.

Now, there is sequence of the commands I run trough psql:

First, I truncate the table mes_del:

l_netsms=# truncate table mes_del;
TRUNCATE TABLE

Then I run function punimessages() for filling the messageIds to the
mes_del table:

l_netsms=# select punimessages();
NOTICE:  Ubacili smo za usera 4162.
NOTICE:  Ubacili smo za usera 4161.
NOTICE:  Ubacili smo za usera 4160.
NOTICE:  Ubacili smo za usera 4159.
NOTICE:  Ubacili smo za usera 4158.
 punimessages
--------------
 t
(1 row)

l_netsms=# select count(*) from mes_del;
 count
-------
    60
(1 row)

There are 60 messages for those five users. 

Now I run the function again:

l_netsms=# select punimessages();
NOTICE:  Ubacili smo za usera 4162.
NOTICE:  Ubacili smo za usera 4161.
NOTICE:  Ubacili smo za usera 4160.
NOTICE:  Ubacili smo za usera 4159.
NOTICE:  Ubacili smo za usera 4158.
 punimessages
--------------
 t
(1 row)

Shouldn't I get errors that I'm violating primary key constraint when
INSERTing again same data?

l_netsms=# select count(*) from mes_del;
 count
-------
    60
(1 row)

l_netsms=#

If I execute INSERT statement from the second function, I get the error:

l_netsms=# INSERT INTO mes_del SELECT * FROM puniBrisiTablica(4158);
ERROR:  duplicate key violates unique constraint "pk"
l_netsms=#

This is expected, I'm just unsure why ain't I receiving that error when
running punimessages() function?

Postgres is 8.1.2 running on linux 2.6.17.

	Mike
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."