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
FROMusersJOIN phone_numbers ON users.id = phone_numbers.user_idJOIN messages ON messages.phone_number =
phone_numbers.phone_number
whereusers.id = $1
order bymessages.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$
declareuserId users%ROWTYPE;
begin
truncate table mes_del;
FOR userId IN SELECT users.id FROM users ORDER BY users.id DESC limit 5LOOP 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."
