On 26.07.2011 22:29, Dave Page wrote:
On Tue, Jul 26, 2011 at 7:13 PM, Erwin Brandstetter
<brandstet...@falter.at> wrote:
On 26.07.2011 18:12, Dave Page wrote:
Hi Erwin
On Tue, Jul 26, 2011 at 4:47 PM, Erwin Brandstetter
<brandstet...@falter.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
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
