Skip site navigation (1) Skip section navigation (2)

Re: Changed default ordering in tables

From: Erwin Brandstetter <brandstetter(at)falter(dot)at>
To: Dave Page <dpage(at)pgadmin(dot)org>
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Re: Changed default ordering in tables
Date: 2011-07-26 18:13:13
Message-ID: 4E2F03B9.1040504@falter.at (view raw or flat)
Thread:
Lists: pgadmin-hackers
On 26.07.2011 18:12, Dave Page wrote:
> Hi Erwin
>
> On Tue, Jul 26, 2011 at 4:47 PM, Erwin Brandstetter
> <brandstetter(at)falter(dot)at>  wrote:
>> Aloha!
>>
>> In  v1.14 tables are opened with ORDER BY $pkey DESC.
>> I wonder if descending order ist intended. It used to be the other way round
>> and, as far as I am concerned, that was just fine in most cases.
>> We have the new feature "View Data" .. "View top / last 100 rows" anyway. No
>> need to change the default behavior?
> The default is determined like this:
>
>         orderBy = table->GetQuotedPrimaryKey();
>         if (orderBy.IsEmpty()&&  hasOids)
>              orderBy = wxT("oid");
>         if (!orderBy.IsEmpty())
>        {
>             if (pkAscending)
>                  orderBy += wxT(" ASC");
>             else
>                orderBy += wxT(" DESC");
>        }
>
> Essentially, we try to follow the ordering in the index.
>

Aloha Dave!

Fair enough. However, the following test-case shows the opposite effect in pgAdmin:

 > By default, B-tree indexes store their entries in ascending order with nulls last.
http://www.postgresql.org/docs/9.0/interactive/indexes-ordering.html

CREATE TABLE test(test_id integer primary key, test text);
INSERT INTO test VALUES (1, 'top'), (2, 'middle'), (3, 'bottom')
-- Now open the table in the browser of pgAdmin 1.14 Beta 3 (sorts DESC; incorrect)
-- Compare this with the behaviour in pgAdmin 1.12 (sorts ASC; correct)

Also, there are key types that do not sort. I quote the documementation:
 > Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an 
unspecified, implementation-dependent order.

Is it safe to assume descending order if pkAscending is not true? Not sure what "IsEmpty" implies exactly in the code ..

-- 
Erwin Brandstetter


In response to

Responses

pgadmin-hackers by date

Next:From: Dave PageDate: 2011-07-26 20:29:56
Subject: Re: Changed default ordering in tables
Previous:From: Dave PageDate: 2011-07-26 16:12:28
Subject: Re: Changed default ordering in tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group