0

I am using PostgreSQL and accessing it over SQLAlchemy with a Python3.4 application.

When I got an still existing object/row from the database, modifying it and store it back, the row is moved to the end of the table.

I want to prevent this.

I want all rows in the same order like they where created, no matter how often they are updated.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • that should not happen. sql updates don't delete & re-insert modified object. – Haleemur Ali Apr 11 '15 at 01:37
  • please post your sqlalchemy code that defines the database table and the code that updates it. – Haleemur Ali Apr 11 '15 at 01:38
  • How are you determining that the row is being moved? Can you post your schema and the code that modifies the table? – Blender Apr 11 '15 at 01:41
  • if you designed your schema correctly then you shouldn't be afraid of in which position does the record persists, you have to manage the sort in your queries when mining your data – void Apr 11 '15 at 01:44
  • 1
    SQL tables represent unordered sets. There should be no relevance to whether a particular record does or does not move in a particular operation. – Gordon Linoff Apr 11 '15 at 01:57
  • I see I think to much object orientated. ;) –  Apr 12 '15 at 02:07

1 Answers1

1

What you observe is most probably the result of a query without ORDER BY, in which case rows are returned in arbitrary order. Postgres typically (but not necessarily) retrieves rows in physical order in this case.

Contrary to what @Haleemur commented an UPDATE is in fact very similar to a DELETE and INSERT in PostgreSQL's MVCC model. A new row version is written for every update. Only large columns that are stored out-of-line ("TOASTed") are not rewritten unless these columns are updated.

So, if you don't ORDER BY (which you probably should), rows are typically moved to the end of the table when updated (according to their physical location), but that can be changed any time and without warning, for instance by another UPDATE or by VACUUM. Never rely on the physical location.

I want all rows in the same order like they where created, no matter how often they are updated.

So add a serial column to the table and order by it:

CREATE TABLE foo (foo_id serial PRIMARY KEY, foo text);

SELECT * FROM foo ORDER BY foo_id;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I use in SQLAlchemy "Column('oid', sa.Integer, primary_key=True)" to create an object id. I am not sure but in the back this would result in a serial with autoincrement function. But would aks if it is cleary defined how PostgreSQL fill this 'oid' with values. What would happen if the max-value is reached? Does it start again with 1 or the first non existing value (maybe with one I deleded before). etc... Is it save to order by 'oid' (serial)? Does it really represent the creation order of the rows? –  Apr 12 '15 at 02:05