Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 294026336CE for ; Mon, 25 May 2009 12:04:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 52832-03 for ; Mon, 25 May 2009 12:04:00 -0300 (ADT) Received: from mx3.hub.org (mx3.hub.org [206.223.169.73]) by mail.postgresql.org (Postfix) with ESMTP id 66CFC63367C for ; Mon, 25 May 2009 12:04:00 -0300 (ADT) X-Greylist: delayed 00:07:29.876386 by SQLgrey-1.7.6 Received: from smtp-out113.alice.it (smtp-out113.alice.it [85.37.17.113]) by mx3.hub.org (Postfix) with ESMTP id C30E037B687 for ; Mon, 25 May 2009 12:03:59 -0300 (ADT) Received: from FBCMMO03.fbc.local ([192.168.68.197]) by smtp-out113.alice.it with Microsoft SMTPSVC(6.0.3790.3959); Mon, 25 May 2009 16:56:24 +0200 Received: from FBCMCL01B04.fbc.local ([192.168.69.85]) by FBCMMO03.fbc.local with Microsoft SMTPSVC(6.0.3790.1830); Mon, 25 May 2009 16:56:24 +0200 Received: from kerberos.webthatworks.it ([82.50.69.203]) by FBCMCL01B04.fbc.local with Microsoft SMTPSVC(6.0.3790.3959); Mon, 25 May 2009 16:55:56 +0200 Received: from dawn.webthatworks.it (dawn.webthatworks.it [192.168.1.12]) by kerberos.webthatworks.it (Postfix) with ESMTP id C7C5BE6AE7 for ; Mon, 25 May 2009 16:56:17 +0200 (CEST) Received: from dawn.webthatworks.it (localhost [127.0.0.1]) by dawn.webthatworks.it (Postfix) with ESMTP id 79F5B3DB19 for ; Mon, 25 May 2009 16:56:17 +0200 (CEST) Date: Mon, 25 May 2009 16:56:17 +0200 From: Ivan Sergio Borgonovo To: pgsql-sql@postgresql.org Subject: Re: Obtaining a limited number of records from a long query Message-ID: <20090525165617.02d1658a@dawn.webthatworks.it> In-Reply-To: <027f01c9dd44$054655e0$ec5a3d0a@marktestcr.marktest.pt> References: <020201c9dd3d$372c3f40$ec5a3d0a@marktestcr.marktest.pt> <4A1AA0FA.1070507@networkmail.eu> <025501c9dd40$86155440$ec5a3d0a@marktestcr.marktest.pt> <20090525161347.166789ac@dawn.webthatworks.it> <027f01c9dd44$054655e0$ec5a3d0a@marktestcr.marktest.pt> Organization: http://www.webthatworks.it X-Mailer: Claws Mail 3.7.1 (GTK+ 2.16.1; x86_64-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 25 May 2009 14:55:56.0807 (UTC) FILETIME=[E8B10970:01C9DD48] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200905/109 X-Sequence-Number: 32601 On Mon, 25 May 2009 15:20:57 +0100 "Oliveiros Cristina" wrote: > Thank you for pointing it out, Ivan. > The query I am trying to restrict output for happens to be an > ORDER BY query. > Actually I wasn't aware of this detail. > I'll leave a mental note for myself that results might be > unexpected for not ORDER BY queries a bit more about "unexpected": - if people insert other rows... and by chance they happen to fall before the ones you already retrieved... no matter of the ORDER BY, you may "miss" them in the "paginated result". - without an order by there is no "granted" order. This is not a deficiency of PostgreSQL, it is not mandated by sql standard and it offers chances for optimisation The fact that without an order by clause they may return in the same order... is implementation dependent and Postgresql don't behave that way or it happens just by chance. -- Ivan Sergio Borgonovo http://www.webthatworks.it