Обсуждение: Question about SPI_prepare
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/
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
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
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