Обсуждение: not sorted clustered index (8.2)

hi,

As part of the definition of a clustered index, the default sort of a
table is based on the clustered index. In our application sometimes we
see that the sort is invalid. Our table is like:
iid identity (clustered primary key)
data varchar
if we do a query like:
select * from table where iid in (1,2,3)

we suspect that we get the result based on the iid, but sometimes this
is not happening. Is this a known issue?
our configuration is postgresql 8.2 on ubuntu

greetings,

Adriaan

Adriaan van Kekem wrote:
> hi,
>
> As part of the definition of a clustered index, the default sort of a
> table is based on the clustered index. In our application sometimes we
> see that the sort is invalid. Our table is like:
> iid identity (clustered primary key)
> data varchar
> if we do a query like:
> select * from table where iid in (1,2,3)
>
> we suspect that we get the result based on the iid, but sometimes this
> is not happening. Is this a known issue?
> our configuration is postgresql 8.2 on ubuntu

this is not a bug - your application should not depend on a particular
sort order without using ORDER BY.
That this works in other RDBMS is an artefact of their implementation
and you should probably not rely on that (and keep in mind that
clustering is a on-time operation in postgresql so modifications to the
table will change the ordering again)


Stefan