Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about SPI_prepare

Question about SPI_prepare

From
"Tim Keitt"
Date:
I have an application where I am building a plan with SPI_plan and
then this plan is called multiple times. There is one free parameter
($1) to the plan. The issue is with the order of the values returned.
If $1 is identical during successive calls to SPI_execute_plan, is
there any guarantee that the rows will be returned in the same order?
I know that repeated queries can return rows in different order, but
am specifically wondering about a prepared plan. (I can imagine the
plan somehow snap-shots the table when prepared such that rows always
come back in the same order, but I'm guessing that is not the case.)
If not, I simply have to add an order by clause which will incur some
overhead.

THK

-- 
Timothy H. Keitt
http://www.keittlab.org/

Re: Question about SPI_prepare

From
Sam Mason
Date:
On Tue, Nov 11, 2008 at 11:33:41AM -0600, Tim Keitt wrote:
> I have an application where I am building a plan with SPI_plan and
> then this plan is called multiple times. There is one free parameter
> ($1) to the plan. The issue is with the order of the values returned.
> If $1 is identical during successive calls to SPI_execute_plan, is
> there any guarantee that the rows will be returned in the same order?

No, AFAIK SeqScans will start from different places if there's someone
else doing a scan on the table (or has done recently).

 Sam

Re: Question about SPI_prepare

From
Joshua Tolley
Date:
On Tue, Nov 11, 2008 at 11:33:41AM -0600, Tim Keitt wrote:
> I have an application where I am building a plan with SPI_plan and
> then this plan is called multiple times. There is one free parameter
> ($1) to the plan. The issue is with the order of the values returned.
> If $1 is identical during successive calls to SPI_execute_plan, is
> there any guarantee that the rows will be returned in the same order?
> I know that repeated queries can return rows in different order, but
> am specifically wondering about a prepared plan. (I can imagine the
> plan somehow snap-shots the table when prepared such that rows always
> come back in the same order, but I'm guessing that is not the case.)
> If not, I simply have to add an order by clause which will incur some
> overhead.
>
> THK
>
> --
> Timothy H. Keitt
> http://www.keittlab.org/
>

Only ORDER BY will guarantee the order of results.

- Josh / eggyknap

Re: Question about SPI_prepare

From
Andrew Dunstan
Date:

Sam Mason wrote:
> On Tue, Nov 11, 2008 at 11:33:41AM -0600, Tim Keitt wrote:
>   
>> I have an application where I am building a plan with SPI_plan and
>> then this plan is called multiple times. There is one free parameter
>> ($1) to the plan. The issue is with the order of the values returned.
>> If $1 is identical during successive calls to SPI_execute_plan, is
>> there any guarantee that the rows will be returned in the same order?
>>     
>
> No, AFAIK SeqScans will start from different places if there's someone
> else doing a scan on the table (or has done recently).
>
>
>   
>   

Not only that, but the physical order of rows in the table is not 
constant. It can be changed by any insert/update/delete. If you need the 
rows in some order, use ORDER BY.

cheers

andrew