Thread: New record position
Hello,
I am using Slackware Linux 10, Postgresql 8.0.1.
My computer had a incorrectly power down (last week) and I have executed the
vacuum command: VACCUM FULL ANALYZE VERBOSE;
to recicle and verify my database.
Before the power-down, all records had inserted into a table have displayed at
LAST record. Like:SELECT * from tb1;f1 | f2------|--------rec1 | vl1
INSERT into tb1 values ('rec2','vl2');SELECT * from tb1;f1 | f2------|--------rec1 | vl1rec2 | vl2
But After the power-down and vacuum, the new records inserted have appeared in
random location (FIRST, between other records, etc...). Ie:
INSERT into tb1 values ('rec3','vl3');SELECT * from tb1;f1 | f2------|--------rec1 | vl1rec3 | vl3
<<=====rec2 | vl2
Why it? I can't undestand why the new record location was change. Shouldn't it
apper at the LAST record???
What need I do??
Thank you.
--------
Lucas Vendramin
Brazil
This is a feature of relational databases, you should explicitly specify
ordering if you want persistent order.
btw, why do you bothering ?
Oleg
On Wed, 30 Mar 2005 lucas@presserv.org wrote:
> Hello,
> I am using Slackware Linux 10, Postgresql 8.0.1.
> My computer had a incorrectly power down (last week) and I have executed the
> vacuum command:
> VACCUM FULL ANALYZE VERBOSE;
> to recicle and verify my database.
> Before the power-down, all records had inserted into a table have displayed at
> LAST record. Like:
> SELECT * from tb1;
> f1 | f2
> ------|--------
> rec1 | vl1
>
> INSERT into tb1 values ('rec2','vl2');
> SELECT * from tb1;
> f1 | f2
> ------|--------
> rec1 | vl1
> rec2 | vl2
>
> But After the power-down and vacuum, the new records inserted have appeared in
> random location (FIRST, between other records, etc...). Ie:
>
> INSERT into tb1 values ('rec3','vl3');
> SELECT * from tb1;
> f1 | f2
> ------|--------
> rec1 | vl1
> rec3 | vl3 <<=====
> rec2 | vl2
>
> Why it? I can't undestand why the new record location was change. Shouldn't it
> apper at the LAST record???
> What need I do??
> Thank you.
> --------
> Lucas Vendramin
> Brazil
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> Why it? I can't undestand why the new record location was change. > Shouldn't it > apper at the LAST record??? > What need I do?? > Thank you. The SQL spec specifies that if you don't use ORDER BY, well, the records come out in any order they want. Actually it's the order they are on disk, which is more or less random as inserting new records will fill the space left by deleted ones, and vacuum full will compact them. If you want order, use ORDER BY. If you want to order them in the order they were inserted, order by a SERIAL PRIMARY KEY field...
Okay, I will use the "order by" clause.
I was worried about it. I have thought that my database had crashed.
Thank you.
Quoting Oleg Bartunov <oleg@sai.msu.su>:
> This is a feature of relational databases, you should explicitly specify
> ordering if you want persistent order.
> btw, why do you bothering ?
>
> Oleg
> On Wed, 30 Mar 2005 lucas@presserv.org wrote:
>
>> Hello, ....
>> INSERT into tb1 values ('rec3','vl3');
>> SELECT * from tb1;
>> f1 | f2
>> ------|--------
>> rec1 | vl1
>> rec3 | vl3 <<=====
>> rec2 | vl2
>>
>> Why it? I can't undestand why the new record location was change.
>> Shouldn't it apper at the LAST record???
lucas@presserv.org writes: > Why it? I can't undestand why the new record location was change. Shouldn't it > apper at the LAST record??? > What need I do?? SQL only imposes an order on the return set if you add an "ORDER BY" clause. You can't expect any particular order to either recur or NOT recur unless you have specifically requested a particular ordering. There's no bug; just use ORDER BY if you need to, and, if you don't, make sure you don't expect any particular ordering... -- (format nil "~S@~S" "cbbrowne" "cbbrowne.com") http://www3.sympatico.ca/cbbrowne/spiritual.html "The present need for security products far exceeds the number of individuals capable of designing secure systems. Consequently, industry has resorted to employing folks and purchasing "solutions" from vendors that shouldn't be let near a project involving securing a system." -- Lucky Green
There's a difference between "natural" order (the location in the database or on disk) and "record" order (the order specified by the primary key)... -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Chris Browne Sent: Wednesday, March 30, 2005 3:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] New record position lucas@presserv.org writes: > Why it? I can't undestand why the new record location was change. Shouldn't it > apper at the LAST record??? > What need I do?? SQL only imposes an order on the return set if you add an "ORDER BY" clause. You can't expect any particular order to either recur or NOT recur unless you have specifically requested a particular ordering. There's no bug; just use ORDER BY if you need to, and, if you don't, make sure you don't expect any particular ordering... -- (format nil "~S@~S" "cbbrowne" "cbbrowne.com") http://www3.sympatico.ca/cbbrowne/spiritual.html "The present need for security products far exceeds the number of individuals capable of designing secure systems. Consequently, industry has resorted to employing folks and purchasing "solutions" from vendors that shouldn't be let near a project involving securing a system." -- Lucky Green ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
gpatnude@hotmail.com ("Greg Patnude") writes:
> There's a difference between "natural" order (the location in the
> database or on disk) and "record" order (the order specified by the
> primary key)...
That's well and fine; I could see the "natural order" in which data is
returned varying over time in view of the fact that it is probably
quickest to start by first returning the rows that are sitting in
shared cache, and only then going to the table to get more.
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/postgresql.html
"The present need for security products far exceeds the number of
individuals capable of designing secure systems. Consequently,
industry has resorted to employing folks and purchasing "solutions"
from vendors that shouldn't be let near a project involving securing a
system." -- Lucky Green