Thread: After updating dataset the record goes to the end of the dataset

Hi all,<br /><br />I am using Postgres in a Delphi application through ODBC. I am having an issue with updating
records.<br/><br />When I create a dataset to get the records in a table then after I update one of these records and
thenrefresh the dataset, the record goes to the end of the dataset. This is disappointing when editing records on a
DBGrid,where users find their updated records jump to the end of the grid. Even after restarting the application, the
updatedrecord keeps showing at the end of the grid. I have tried the same thing with SQL Server and it works normally,
soI thought it was a postgres behvior.<br /><br />Has anyone an idea about what could be the cause of such a
behavior.<br/><br />Thanks to all in advance.<br /><br />Nacef<br /> 

Yes I don't issue any sort statement, and I indeed want the data to be show as it is stored in the database. But after
updatinga row (I don't update the ID, just some fields), it keeps its same place on the DB but jumps to the end of the
datasetand by the way to the end of the DBGrid.<br /><br />Nacef<br /><br /><div class="gmail_quote">On Tue, Apr 22,
2008at 12:06 PM, Roland Voegtli <voegtli@scnat.ch> wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="Ih2E3d"><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Hasanyone an idea about what could be the cause of such a behavior.<br /></blockquote><br /></div> You don't issue any
sortstatement with the query, obviously. So you get the records the way they are stored in the database.<br /><br />
cheers<br/> ®<br /><br /></blockquote></div><br /> 

Nacef LABIDI, 22.04.2008 11:54:
> Hi all,
> 
> I am using Postgres in a Delphi application through ODBC. I am having an 
> issue with updating records.
> 
> When I create a dataset to get the records in a table then after I 
> update one of these records and then refresh the dataset, the record 
> goes to the end of the dataset. This is disappointing when editing 
> records on a DBGrid, where users find their updated records jump to the 
> end of the grid. Even after restarting the application, the updated 
> record keeps showing at the end of the grid. I have tried the same thing 
> with SQL Server and it works normally, so I thought it was a postgres 
> behvior.
> 
> Has anyone an idea about what could be the cause of such a behavior.

Rows in a relational database are not sorted. This is true for any RDBMS.

If you want to apply a certain sort order you *have* to use an ORDER BY clause for your SELECT. 
If the rows show up in the order you "expect" without an ORDER BY this is pure coincidence (even with SQL Server)

The simply solution is to add an ORDER BY that sorts e.g. by a timestamp that is set when the row is created.

Regards
Thomas

On Tue, Apr 22, 2008 at 3:24 PM, Nacef LABIDI <nacef.l@gmail.com> wrote:
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

On Tue, Apr 22, 2008 at 12:10:41PM +0200, Nacef LABIDI wrote:
> Yes I don't issue any sort statement, and I indeed want the data to be show
> as it is stored in the database. But after updating a row (I don't update
> the ID, just some fields), it keeps its same place on the DB but jumps to
> the end of the dataset and by the way to the end of the DBGrid.

why do you think it stays in the same place in db?

besides - without "order by" you cannot depend on the order of rows.
basically i treat them as in "random" order (which is not true, but
helps me remember to never count on the "default" ordering.

depesz

-- 
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

You are right about the fact that the rows don't keep the order they where created with at the start. I have verfied
this.<br/><br />I will explain more my case :<br /><br />I am writing an application where here is some resources to
planevents on. I want to provide the user with the ability to customize the order in which resources are displayed on
thescreen. So there is a mapping between the rows positions in the dataset and they display positions. After updating
oneof these resources the row jumps to the end of the dataset so does the resource on the display. And the order gets
scrambled.I cannot set this order in the databe since it is customizable for each user.<br /><br />When I have tested
thiswith SQLServer it works well, since the rows doesn't change position on the DB.<br /><br />I hope that you
understandmy issue and I will provide any explanations if someting isn't clear enough.<br /><br />Thanks to all<br
/>Nacef<br/><br /><div class="gmail_quote">On Tue, Apr 22, 2008 at 12:15 PM, hubert depesz lubaczewski <<a
href="mailto:depesz@depesz.com">depesz@depesz.com</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On
Tue,Apr 22, 2008 at 12:10:41PM +0200, Nacef LABIDI wrote:<br /> > Yes I don't issue any sort statement, and I indeed
wantthe data to be show<br /> > as it is stored in the database. But after updating a row (I don't update<br /> >
theID, just some fields), it keeps its same place on the DB but jumps to<br /> > the end of the dataset and by the
wayto the end of the DBGrid.<br /><br /></div>why do you think it stays in the same place in db?<br /><br /> besides -
without"order by" you cannot depend on the order of rows.<br /> basically i treat them as in "random" order (which is
nottrue, but<br /> helps me remember to never count on the "default" ordering.<br /><br /> depesz<br /><font
color="#888888"><br/> --<br /> quicksil1er: "postgres is excellent, but like any DB it requires a<br /> highly paid
DBA. here's my CV!" :)<br /><a href="http://www.depesz.com/" target="_blank">http://www.depesz.com/</a> - blog dla
ciebie(i moje CV)<br /></font></blockquote></div><br /> 

On Tue, Apr 22, 2008 at 4:01 PM, Nacef LABIDI <nacef.l@gmail.com> wrote:

>
> I am writing an application where here is some resources to plan events on.
> I want to provide the user with the ability to customize the order in which
> resources are displayed on the screen.


To hope that the database would do that automatically for you is IMHO
wrong. One way I can think of is to store the ordering information
along with the user and then fetch the items in the desired order
while displaying. Isn't that what you would anyway require if there
are multiple users and each one wants to see the items in a different
order ?

Thanks,
Pavan

-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

On Tue, Apr 22, 2008 at 12:31:54PM +0200, Nacef LABIDI wrote:
> When I have tested this with SQLServer it works well, since the rows doesn't
> change position on the DB.
> I hope that you understand my issue and I will provide any explanations if
> someting isn't clear enough.

well. in postgresql rows do change position. and even in mssql depending
on position from table files is a very big mistake.

suggestion - add default "order by" by some id or timestamp column, and
modify it to fit your user preferences.

for example:

let's say that you have rows with ids: 1,2,3

by default you get them in order: 1,2,3.

if user wants to change the ordering to 2,3,1, store his preferences in
some other table and do it like this:

select t.* from table t join preferences p on t.id = p.id_in_table where
p.user = 'current user' order by p.ordering;

regards,

depesz

<font size="-1"><font face="Helvetica, Arial, sans-serif">Nacef,<br /><br /> Simply add a SERIAL column to your table.
Whenyou add a new row, a new value will be stored in this column if you do not specify the column name in the insert
query.<br/> In your select query, always order by this column.<br /> When doing an update , do not update this column.
Whenre-ordering, update this column to indicate order. So if you want to move a row up, issue two update queries,
updatingthis column. Make sure you have some primary key in this table to uniquely identify these row.<br /><br />
Neverrely on the internal implementation of a database to keep the data in the order you insert/update.<br /><br />
Padam<br/></font></font><br /> Nacef LABIDI wrote: <blockquote
cite="mid:f16f7ea00804220331u226df1ean9a17e12d9324e078@mail.gmail.com"type="cite">You are right about the fact that the
rowsdon't keep the order they where created with at the start. I have verfied this.<br /><br /> I will explain more my
case:<br /><br /> I am writing an application where here is some resources to plan events on. I want to provide the
userwith the ability to customize the order in which resources are displayed on the screen. So there is a mapping
betweenthe rows positions in the dataset and they display positions. After updating one of these resources the row
jumpsto the end of the dataset so does the resource on the display. And the order gets scrambled. I cannot set this
orderin the databe since it is customizable for each user.<br /><br /> When I have tested this with SQLServer it works
well,since the rows doesn't change position on the DB.<br /><br /> I hope that you understand my issue and I will
provideany explanations if someting isn't clear enough.<br /><br /> Thanks to all<br /> Nacef<br /><br /><div
class="gmail_quote">OnTue, Apr 22, 2008 at 12:15 PM, hubert depesz lubaczewski <<a href="mailto:depesz@depesz.com"
moz-do-not-send="true">depesz@depesz.com</a>>wrote:<br /><blockquote class="gmail_quote" style="border-left: 1px
solidrgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On Tue, Apr 22, 2008 at
12:10:41PM+0200, Nacef LABIDI wrote:<br /> > Yes I don't issue any sort statement, and I indeed want the data to be
show<br/> > as it is stored in the database. But after updating a row (I don't update<br /> > the ID, just some
fields),it keeps its same place on the DB but jumps to<br /> > the end of the dataset and by the way to the end of
theDBGrid.<br /><br /></div> why do you think it stays in the same place in db?<br /><br /> besides - without "order
by"you cannot depend on the order of rows.<br /> basically i treat them as in "random" order (which is not true, but<br
/>helps me remember to never count on the "default" ordering.<br /><br /> depesz<br /><font color="#888888"><br />
--<br/> quicksil1er: "postgres is excellent, but like any DB it requires a<br /> highly paid DBA.  here's my CV!" :)<br
/><ahref="http://www.depesz.com/" moz-do-not-send="true" target="_blank">http://www.depesz.com/</a> - blog dla ciebie
(imoje CV)<br /></font></blockquote></div><br /></blockquote><br /><pre class="moz-signature" cols="72">-- 
 
PGP Id 9EED2E09</pre>

Nacef LABIDI wrote:
> Yes I don't issue any sort statement, and I indeed want the data to be show
> as it is stored in the database.
That's what you're getting. PostgreSQL has an MVCC design. How it works 
in general terms
(simplified, but I lack the expertise to give a complete explanation 
even if one was required) is:
When you update a row the old row is marked as dead and a new row is 
inserted. If there is no
spare space in the table near the old row (assuming there's any reason 
for the DB to even try
to put the new row near the old one) then the new row will be placed 
elsewhere, such as at
the end of the table.

In other words, after an UPDATE the row really is often at the end of 
the table.

In any case as others have explained you should never rely on the 
database ordering
of records; you should always use an ORDER BY if you care about order. 
The database
makes no guarantees about the order of returned rows.

The database may optimise row retrieval in ways you do not expect. For 
example,
in recent versions of PostgreSQL if there is a sequential scan in 
progress on a table
and you start another query that also runs a sequential scan on the 
table, PostgreSQL
may synchronize the two scans. That'll cause your query to start part 
way through the
table. If the table contains alphabetically ordered data you might get 
something like:

J
K
L
M
... etc ...
A
B
C

So ... in any database, always use ORDER BY if you care about order. 
Just because it usually
works in some databases doesn't mean it won't break just rarely enough 
to drive you insane
while debugging...

--
Craig Ringer

Thank you all for your useful comments.

I have tried to order my records as you said and got it work as I wanted.

Thank you again

On Tue, Apr 22, 2008 at 7:00 AM, Nacef LABIDI <nacef.l@gmail.com> wrote:
> Thank you all for your useful comments.
>
> I have tried to order my records as you said and got it work as I wanted.

Cool beans!  I was just watching this conversation, but wanted to
point out that using the methods mentioned in this thread (i.e. ones
that use order by) is good practice.  It's quite possible that at some
point in the future the storage engine of MS-SQL could change and
break your application on that platform.  By using the order by
method, you ensure you're using a method that works today and in the
future on all major platforms.