Thread: pgsql-odbc list problem
Hi, I'll be posting a lot of messages to pgsql-odbc@postgresql.org without sucess. I try to unsuscribe/suscribe with the same results. I'll be using this account. Any clues? There is any restriction with gmail accounts? Thanks. Alejandro
Alejandro D. Burne wrote: > Hi, I'll be posting a lot of messages to pgsql-odbc@postgresql.org > without sucess. I try to unsuscribe/suscribe with the same results. > I'll be using this account. > Any clues? There is any restriction with gmail accounts? Thanks. Alejandro At least one of your messages got through (although just recently). If you weren't subscribed, your posts will be queued to be read by a human moderator. This usually adds a delay of a couple of days. You can subscribe via the web: http://www.postgresql.org/community/lists/ -- Richard Huxton Archonet Ltd
Ok, may be the size/attachments in the mails. There is a restriction on the size? because if I attach 2 files (80kb total) the mail don't return from the list (without any warning), the only mail that arrives was a mail with few chars. I'll be watching on http://archives.postgresql.org/pgsql-odbc/2005-07/index.php for my mails (I wrote them on 22/07/2005) without news. Another example, today I wrote 2 mails one arrives the other not (yet?). Thanks, Alejandro. 2005/8/4, Richard Huxton <dev@archonet.com>: > Alejandro D. Burne wrote: > > Hi, I'll be posting a lot of messages to pgsql-odbc@postgresql.org > > without sucess. I try to unsuscribe/suscribe with the same results. > > I'll be using this account. > > Any clues? There is any restriction with gmail accounts? Thanks. Alejandro > > At least one of your messages got through (although just recently). > > If you weren't subscribed, your posts will be queued to be read by a > human moderator. This usually adds a delay of a couple of days. > > You can subscribe via the web: http://www.postgresql.org/community/lists/ > > -- > Richard Huxton > Archonet Ltd >
Hi, I just noticed this, if I do a update like this: update new_requests set name = 'tony' where request_id = 2 If I do a select * from new_requests that record I just updated is now at the bottom , before the update it was at the top? Why is Postgresql changing the ordering of the results after a simple update? It almost looks like the record is being dropped and then readded to the end. Thanks, Tony > >
On Thu, Aug 04, 2005 at 09:19:44AM -0500, Tony Caduto wrote: > Hi, > > I just noticed this, if I do a update like this: > > update new_requests set name = 'tony' where request_id = 2 > > If I do a select * from new_requests that record I just updated is now > at the bottom , before the update it was at the top? Table in SQL don't have an implicit order. If you want the rows in a particular order, you need to use ORDER BY. > Why is Postgresql changing the ordering of the results after a simple > update? Because it's silly to order stuff if you didn't ask for a particular order. Waste of CPU cycles. > It almost looks like the record is being dropped and then readded to the > end. Under the hood, that's what happens. But it might appear at the end, in the middle, anywhere. If want an order, use ORDER BY, otherwise it'll be random. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes: > Hi, > > I just noticed this, if I do a update like this: > > update new_requests set name = 'tony' where request_id = 2 > > If I do a select * from new_requests that record I just updated is now > at the bottom , before the update it was at the top? > > Why is Postgresql changing the ordering of the results after a simple > update? Results are returned in an arbitrary order unless you supply an ORDER BY clause in your query. > It almost looks like the record is being dropped and then readded to > the end. Yup. In Postgres, UPDATE == DELETE + INSERT, so the new row will very likely go into a different place (this is so that existing transactions can still see the old row before your transaction commits). -Doug
On Thu, 2005-08-04 at 09:19 -0500, Tony Caduto wrote: > Hi, > > I just noticed this, if I do a update like this: > > update new_requests set name = 'tony' where request_id = 2 > > If I do a select * from new_requests that record I just updated is now > at the bottom , before the update it was at the top? > > Why is Postgresql changing the ordering of the results after a simple > update? > It almost looks like the record is being dropped and then readded to the > end. > > Thanks, > If I understand MVCC correctly, a "new" tuple is actually created and the "old" tuple is marked with an effective "end" transaction id (or marked "dead" for any transactions with an id greater than the transaction id that updated that particular tuple). Your subsequent select then sees the "new" tuple and, in natural order, it would appear at the bottom. Vacuuming is the process by which these "old" tuples are examined and, if there are no transactions open with an id less than the "end" transaction id associated with that dead tuple, it is removed (or the space is marked as available for a new tuple to be written). Sven
On Thu, Aug 04, 2005 at 09:19:44AM -0500, Tony Caduto wrote: > I just noticed this, if I do a update like this: > > update new_requests set name = 'tony' where request_id = 2 > > If I do a select * from new_requests that record I just updated is now > at the bottom , before the update it was at the top? SQL doesn't guarantee any particular row order unless you use ORDER BY. Without ORDER BY, simple queries in PostgreSQL are likely to return rows in an order based on their physical location on disk. You can see this location by looking at a row's ctid field: SELECT ctid, * FROM new_requests; See "System Columns" in the documentation for more information about "hidden" columns like ctid: http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html > Why is Postgresql changing the ordering of the results after a simple > update? > It almost looks like the record is being dropped and then readded to the > end. PostgreSQL uses Multi-Version Concurrency Control (MVCC), which creates a new version of the row. Until you vacuum the table, the old row still exists for the benefit of other transactions that might still have visibility to it. http://www.postgresql.org/docs/8.0/static/mvcc.html http://www.postgresql.org/docs/8.0/static/maintenance.html#VACUUM-FOR-SPACE-RECOVERY New rows are added where PostgreSQL finds room for them, which in your case is apparently at the end of the table. Here's an example: CREATE TABLE foo (id integer, name text); INSERT INTO foo VALUES (1, 'Adam'); INSERT INTO foo VALUES (2, 'Bob'); INSERT INTO foo VALUES (3, 'Charlie'); INSERT INTO foo VALUES (4, 'David'); SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Adam (0,2) | 2 | Bob (0,3) | 3 | Charlie (0,4) | 4 | David (4 rows) UPDATE foo SET name = 'Billie' WHERE id = 2; SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Adam (0,3) | 3 | Charlie (0,4) | 4 | David (0,5) | 2 | Billie (4 rows) Notice that the new version of the row was added at the end of the table, at ctid (0,5). Now let's vacuum the table, which will free up the old row at (0,2) if no other transactions need it any more. Then we'll update another row and see where it goes: VACUUM foo; UPDATE foo SET name = 'Dwight' WHERE id = 4; SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Adam (0,2) | 4 | Dwight (0,3) | 3 | Charlie (0,5) | 2 | Billie (4 rows) Notice that the new row with id 4 was added where the old row with id 2 had been, at ctid (0,2). Let's do another update: UPDATE foo SET name = 'Alex' WHERE id = 1; SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,2) | 4 | Dwight (0,3) | 3 | Charlie (0,5) | 2 | Billie (0,6) | 1 | Alex (4 rows) The new row was added at the end, because the old version of the row at (0,1) might still be visible to other transactions; likewise for the old version of the row with id 4, which had been at (0,4). Let's do another vacuum and then an update and an insert: VACUUM foo; UPDATE foo SET name = 'Arnold' WHERE id = 1; INSERT INTO foo VALUES (5, 'Ernie'); SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Arnold (0,2) | 4 | Dwight (0,3) | 3 | Charlie (0,4) | 5 | Ernie (0,5) | 2 | Billie (5 rows) Notice how new rows and new versions of old rows get put where PostgreSQL finds room for them. This is one of the reasons for doing regular vacuuming: if you're deleting or updating rows from a table, the table will continue to grow unless you free up the old rows (aka "dead tuples") so their space can be reused. Aside from wasting space, dead tuples can slow down queries. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Just wanted to say thanks for all the great replies explaining why Postgres does this. Tony