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

Re: Changed default ordering in tables

From: Dave Page <dpage(at)pgadmin(dot)org>
To: Erwin Brandstetter <brandstetter(at)falter(dot)at>
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Re: Changed default ordering in tables
Date: 2011-07-26 20:29:56
Message-ID: CA+OCxowSkR2JANEgi6YrT5=OnV_Ct5ugBJv6a+Bu4kksWzwBpw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgadmin-hackers
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.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

pgadmin-hackers by date

Next:From: Erwin BrandstetterDate: 2011-07-26 20:53:54
Subject: Re: Changed default ordering in tables
Previous:From: Erwin BrandstetterDate: 2011-07-26 18:13:13
Subject: Re: Changed default ordering in tables

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