pgsql-odbc list problem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

pgsql-odbc list problem

Alejandro D. Burne
Hi, I'll be posting a lot of messages to [hidden email]
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: pgsql-odbc list problem

Richard Huxton
Alejandro D. Burne wrote:
> Hi, I'll be posting a lot of messages to [hidden email]
> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: pgsql-odbc list problem

Alejandro D. Burne
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 <[hidden email]>:

> Alejandro D. Burne wrote:
> > Hi, I'll be posting a lot of messages to [hidden email]
> > 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
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

weirdness with the a sql update

snorkel
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

>  
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: weirdness with the a sql update

Martijn van Oosterhout
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   <[hidden email]>   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.

attachment0 (240 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: weirdness with the a sql update

Douglas McNaught
In reply to this post by snorkel
Tony Caduto <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: weirdness with the a sql update

Sven Willenberger
In reply to this post by snorkel
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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: weirdness with the a sql update

Michael Fuhr
In reply to this post by snorkel
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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: weirdness with a sql update

snorkel
Just wanted to say thanks for all the great replies explaining why
Postgres does this.

Tony

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match