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.

Thanks to all in advance.

Nacef

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.

Nacef

On Tue, Apr 22, 2008 at 12:06 PM, Roland Voegtli <voegtli@scnat.ch> wrote:

> Has anyone an idea about what could be the cause of such a behavior.
> >
>
> You don't issue any sort statement with the query, obviously. So you get
> the records the way they are stored in the database.
>
> cheers
> (R)
>
>

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


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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)

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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.

I will explain more my case :

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. So there is a mapping between the
rows positions in the dataset and they display positions. After updating one
of 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.

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.

Thanks to all
Nacef

On Tue, Apr 22, 2008 at 12:15 PM, hubert depesz lubaczewski <
depesz@depesz.com> wrote:

> 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)
>

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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

> 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.
>

The data is not guaranteed to be ordered if you do not specify an ORDER BY
clause in your query. Try adding a meaningful ORDER BY clause, that should
solve the problem.

HTH,

Best regards,
--
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: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


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Nacef,

Simply add a SERIAL column to your table. When you 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.
In your select query, always order by this column.
When doing an update , do not update this column. When re-ordering, update this column to indicate order. So if you want to move a row up, issue two update queries, updating this column. Make sure you have some primary key in this table to uniquely identify these row.

Never rely on the internal implementation of a database to keep the data in the order you insert/update.

Padam

Nacef LABIDI wrote: 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.

I will explain more my case :

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. So there is a mapping between the rows positions in the dataset and they display positions. After updating one of 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.

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.

Thanks to all
Nacef



On Tue, Apr 22, 2008 at 12:15 PM, hubert depesz lubaczewski &lt;depesz@depesz.com&gt; wrote:


On Tue, Apr 22, 2008 at 12:10:41PM +0200, Nacef LABIDI wrote:
&gt; Yes I don't issue any sort statement, and I indeed want the data to be show
&gt; as it is stored in the database. But after updating a row (I don't update
&gt; the ID, just some fields), it keeps its same place on the DB but jumps to
&gt; 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. &nbsp;here's my CV!"
http://www.depesz.com/ - blog dla ciebie (i moje CV)






-- PGP Id 9EED2E09

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql