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 20:53:54
Message-ID: 4E2F2962.9070600@falter.at (view raw or flat)
Thread:
Lists: pgadmin-hackers
On 26.07.2011 22:29, Dave Page wrote:
> On Tue, Jul 26, 2011 at 7:13 PM, Erwin Brandstetter
> <brandstetter(at)falter(dot)at>  wrote:
>> 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.
> Actually, no, we don't (sorry). The flag is simply set by the caller
> to do FIRST/LAST 100 rows. Otherwise, it defaults to true.
>
>> 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)
> It works correctly for me.
>
>> 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.
> Right, but a primary key is a UNIQUE + NOT NULL (and a flag in the
> catalogs). Unique indexes are always B-Trees in Postgres.
>
>> Is it safe to assume descending order if pkAscending is not true? Not sure
>> what "IsEmpty" implies exactly in the code ..
> The IsEmpty bit is testing to see if the user has set any explicit
> ordering on the Sort/Filter dialogue - if so, that takes precedence.

That may be the key here. When I open the simple test table from above in v1.14 I get descending order. On opening the Sort/Filter dialog it see an 
explicit ORDER BY test_id DESCENDING - which I did not set.
Once I delete that, the sort order falls back to ascending - as it should to begin with.
However, after reopening the table explicit descending order is back.

Interesting that you cannot reproduce the effect. Ar you on Apple?
I am tested with all combinations of pgAdmin 1.12 & 1.14 Beta 3 on Win XP Pro and postgres 9.0.4 and 8.4.8 on Debian Squeeze.

Playing with the new Features "View Data" .. "View top / last 100 rows" has no lasting side effects on this problem.

-- 
Erwin Brandstetter


In response to

Responses

pgadmin-hackers by date

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

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