I created a table to hold some forum messages :
table messages (
id serial primary key, parent_folder integer not null
references folders(id), --in another table -- for replies, this stores the message which we reply to
reply_to_id integer null
references messages( id ),thread_depth integer not null default 0
other fields :
message_title, message_text, author, etc.
);I want to know which depth the messages in a thread are, simply to shift them a little more to the right in the display, and I want to store this in the "thread_depth" field so I don't have to compute it at each SELECT I do.
My first thought was to use an "ON INSERT" rule which would change the "thread_depth" of messages inserted with a not-null "reply_to_id" by inserting an extra UPDATE after the INSERT. Problem was, the "NEW.id" which got passed to me by postgresql contained something like :
(nextval('messages_id_seq')::text)::integerAside from the funky double cast, trying to read the value incremented the sequence and the UPDATE failed because it tried to update a non-existent row.
So, NEW contained unevaluated default values, BUT reading it evaluates the values (of course) so it consumes an extra sequence number...
Is this normal ?
In the end I used a trigger which looks cleaner :
create or replace function vf.messages_set_depth()
returns trigger
as 'begin
if new.reply_to_id is not null then
new.thread_depth=1+(select thread_depth from messages where id=new.reply_to_id limit 1);
end if;
return new;
end;'
language plpgsql;
create trigger messages_set_depth_trigger
before insert or update on messages
for each row execute procedure messages_set_depth();And it works well this way. I don't intend to do mass message insertion so the fact that the trigger is called on every insert does not disturb me. However, the strange behaviour of the rule still bothers me.
Any comments ?
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
