Thread: Preserving ORDER of TEMP Tables during transaction

Greetings,

Within a PL/PgSQL function I do a

CREATE TEMPORARY TABLE v_temp ON COMMIT DROP
AS
SELECT ctime FROM source ORDER BY ctime
WITH DATA;

Then I use the v_temp in the same transaction block:

FOR v_ctime IN
     SELECT ctime FROM v_temp
LOOP
         ....
END LOOP;

Now I am curious, will the loop return values for ctime in the *same order*
as the query that created the temporary table, or is this undefined?

With other words: can I rely on the ORDER BY of the query that defined
the temporary table? Is there a way to do that?

Regards,
Ludo Smissaert

On Nov 13, 2011, at 11:13, Ludo Smissaert <ludo@ludikidee.com> wrote:

> Greetings,
>
> Within a PL/PgSQL function I do a
>
> CREATE TEMPORARY TABLE v_temp ON COMMIT DROP
> AS
> SELECT ctime FROM source ORDER BY ctime
> WITH DATA;
>
> Then I use the v_temp in the same transaction block:
>
> FOR v_ctime IN
>    SELECT ctime FROM v_temp
> LOOP
>        ....
> END LOOP;
>
> Now I am curious, will the loop return values for ctime in the *same order*
> as the query that created the temporary table, or is this undefined?
>
> With other words: can I rely on the ORDER BY of the query that defined the temporary table? Is there a way to do
that?
>
> Regards,
> Ludo Smissaert
>
>

Why risk basing your query's success on an implementation artifact?  Put an explicit ORDER BY on the SELECT FROM
v_temp.

Related question, though.  Does the time to perform a sort vary based upon the entropy of the input data?  If the
originalORDER BY does result in the records being provided to sorter in order already does the sort basically finish
immediatelyor is the algorithm strictly dependent upon the number of records to sort? 

David J.

On 11/13/11 17:58, David Johnston wrote:
>  On Nov 13, 2011, at 11:13, Ludo Smissaert <ludo@ludikidee.com> wrote:
>  Within a PL/PgSQL function I do a
>
>  CREATE TEMPORARY TABLE v_temp ON COMMIT DROP AS SELECT ctime FROM
>  source ORDER BY ctime WITH DATA;
>
>  Then I use the v_temp in the same transaction block:
>
>  FOR v_ctime IN SELECT ctime FROM v_temp LOOP .... END LOOP;
>
>  Now I am curious, will the loop return values for ctime in the *same
>  order* as the query that created the temporary table, or is this
>  undefined?
>
>  With other words: can I rely on the ORDER BY of the query that
>  defined the temporary table? Is there a way to do that?
>
>  Why risk basing your query's success on an implementation artifact?
>  Put an explicit ORDER BY on the SELECT FROM v_temp.
>
>  Related question, though. Does the time to perform a sort vary based
>  upon the entropy of the input data? If the original ORDER BY does
>  result in the records being provided to sorter in order already does
>  the sort basically finish immediately or is the algorithm strictly
>  dependent upon the number of records to sort?

The algorithm is that I am returning a SETOF cursors pointing
to two different tables and data of these two tables will be
printed by the client like this:

row 1 of table a
    set of rows from table b, depending on value of preceding a
row 2 of table a
    set depending on ... etc.

The first cursor encompasses all rows of a and is needed
by the client for alignment.

dummy_cursor_a        --  all rows for alignment
next_of_a                    -- first row
details_from_b_depending_on_previous_a
next_of_a
details_from_b

The client receives instructions in what to do with the cursors,
and basically does not know anything about the sort of data
it prints. It is just instructed in how to handle the cursors.

Further the entire result set of a depends on a dynamically
generated WHERE-clause.

EXECUTE 'CREATE TEMPORARY TABLE v_temp ON COMMIT DROP AS  '
   'SELECT <projection> FROM <view> WHERE ' || v_filter || ' ORDER BY '
  'WITH DATA;'


Well, I guess I will think of something simpler.

Thanks for answering.

Regards,

Ludo Smissaert

On Sun, Nov 13, 2011 at 12:28 PM, Ludo Smissaert <ludo@ludikidee.com> wrote:
> The algorithm is that I am returning a SETOF cursors pointing
> to two different tables and data of these two tables will be
> printed by the client like this:

Have you actually measure the cost of adding the order by to the
select from the view?