Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Unanswered: SELECT * after UPDATE

    Hi guys, i just started to use Postgresql 8.3.8 and i am working with Dell Store 2 example database from dbsamples project on pgfoundry. In database i have an orders table which is :

    Table "public.orders"
    Column | Type | Modifiers
    -------------+---------------+----------------------------------------------------------
    orderid | integer | not null default nextval('orders_orderid_seq'::regclass)

    orderdate | date | not null
    customerid | integer |
    netamount | numeric(12,2) | not null
    tax | numeric(12,2) | not null
    totalamount | numeric(12,2) | not null
    Indexes:
    "orders_pkey" PRIMARY KEY, btree (orderid)
    "ix_order_custid" btree (customerid)
    Foreign-key constraints:
    "fk_customerid" FOREIGN KEY (customerid) REFERENCES customers(customerid) ON DELETE SET NULL

    and there are 120000 rows on table. I updated row with id 1 and then when i wrote query "SELECT * from orders" i cant see the updated row (row with id 1) on the result set. However if query is "SELECT * from orders ORDER BY ..." then i can see updated row on result set. It remained same even i restarted computer. Why it is happening?

    Thanks.



    Mustafa Dikici

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You can't assume that your results will be in any particular order without an order by clause.

  3. #3
    Join Date
    Nov 2009
    Posts
    4
    Quote Originally Posted by artacus72 View Post
    You can't assume that your results will be in any particular order without an order by clause.

    i just tried it with LIMIT 1 option and interestingly (at least for me) it showed only 1 result starting with orderid,2 . Now my new question is now a bit deeper. Since table loosed its original order, i should use an explicit ORDER BY clause for ordering and i think it will have more cost than classic SELECT * clause since it will use explicit sorting. So is there a way for a safe UPDATE which will not harm original order of the table? Actually i am new on Postgresql database and it looks really very deep and excited for me where i can always find solutions which are relatively easy and make big impacts.

    Thanks

    Mustafa Dikici

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    So is there a way for a safe UPDATE which will not harm original order of the table?
    No. It has to do with the way MVCC works. I'm sure id is going to be your pk so it will have minimal impact to order by it.

    If you're the curious type, try this:
    Code:
    SELECT o.*, ctid
    FROM orders o
    The ctid column will be in the format {page,row} so you can see where it is stored on disk. Then update one of those rows and run the query again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •