Fixed PostgreSQL LIMIT 1 bug.

Using following SELECT (created from a Message.fifo_lifo_rest call) on a VIP
devel database always hangs for several seconds:

SELECT messages.id FROM "messages" INNER JOIN "ressources" ON "ressources".id = "messages".ressource_id INNER JOIN "membership_messages" ON membership_messages.message_id = messages.id INNER JOIN "memberships" ON "memberships".id = "membership_messages".membership_id INNER JOIN "participants" ON "participants".id = "memberships".participant_id WHERE ("ressources"."namespace" = 'numlab' AND "ressources"."ressource" = 'results' AND "participants"."id" = 3) ORDER BY messages.id ASC  LIMIT 1;

When omitting "LIMIT 1" all went well. Also when setting LIMIT greater than 5.
If using another resource, e.g. solutions or exercises, all went well too. Also
when using DESC instead of ASC.

I really don't know why Postgres responds like that. So I made a workaround, by
selecting the first element from the list in ruby, knowing that this solution
is much slower than using LIMIT 1 at database level, but only when the list is
not empty.

1 files changed, 3 insertions, 1 deletions

diff --git a/app/models/message.rb b/app/models/message.rb
index 7cfb417..24eb296 100644
--- a/app/models/message.rb
+++ b/app/models/message.rb
@@ -128,11 +128,13 @@ class Message < ActiveRecord::Base
 
   # return first messages from fifo/lifo queue
   def self.fifo_lifo_rest(namespace, ressource, participant_id, options={:queue_type => :fifo})
-    find(:first, :readonly => false, :lock => true,
+    m=find(:all, :readonly => false, :lock => true,
+      :select => "messages.id",
       :joins => [:ressource, { :membership_messages => { :membership => :participant } }], 
       :conditions => { :participants => { :id => participant_id },
                        :ressources => { :namespace => namespace, :ressource => ressource } },
       :order => :messages.to_s+".id #{(options[:queue_type]==:fifo)?'ASC':'DESC'}")
+    if m.empty? then nil else find(m[0]) end
   end
  
   # get a record  out of the message table