0

I have a doubt regarding the result of a specific query.

SELECT final_copy,num_chars FROM table1 t1,table2 t2

WHERE t1.numid = t2.id

Getting two rows with this query,

final_copy | num_chars

------------+-----------------

     1 | 1272
       | 1075

Here final_copy 1 is the latest record inserted. This query was running in a loop and always returning the record with final_copy as 1 in the first row consistently. As you can see the query was not using any 'order by'.

But now the query results are getting inconsistent, i.e. the rows are not returned based on there last insert date.

My doubt is that is there any chance of changing the behavior of query results when the vacuum or reindexing. Last week I have done reindexing and vacuum of these tables. Will that change the behavior of the results?

My database is postgresql version 7.4.3.

RunningAdithya
  • 1,656
  • 2
  • 16
  • 22
  • 1
    You should really upgrade that db... 7.4.3 is nearly a decade old (06/2004) and no longer maintained. The latest version is 9.3. The oldest that is still maintained is 8.4.17. – Denis de Bernardy Oct 03 '13 at 10:58
  • Thanks for advice, right now we are in the process of upgrading db to version 9.2. – RunningAdithya Oct 03 '13 at 11:03

1 Answers1

2

Lack of an order by clause yields, per sql specs, an undefined order. You need to specify the order (potentially using an extra column, if needed) if you want them in the order they were inserted.

(Technically, the order you're seeing is that in which rows are physically read by the query's plan, meaning the order is subject to both mvcc's internal cuisine and the specific plan you end up using, which may vary due to table statistics and your joining two tables.)

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • Is there any chances that autovacuum or reindexing affect the behaviour of the results? – RunningAdithya Oct 03 '13 at 11:06
  • 1
    Yes -- it'll yield rows in the order they're physically read and/or returned, depending on what your final query is doing, so anything that touches the physical tables or their contents can affect the ordering. But again, you should **NOT** design your queries around this. The ***only*** means to ensure a consistent order in SQL is to specify an explicit `order by` clause. – Denis de Bernardy Oct 03 '13 at 11:28