Jan,
Thanks for the explanation, that does help to explain, and adds a lot to my
postgres knowledge in general ..
Based on your explanation, I understand how running VACUUM wiped out the new
tuples that did not have a corresponding XID in pg_log.
However, there is one aspect of this I still do not quite grasp ..
What happens if the INSERT/DELETE is done without a transaction
(BEGIN/COMMIT)? Is an XID still generated for that particular tuple, or is the
tuple instantly commited with no XID stamped into the beginning/ending fields?
Also, I don't understand why vacuum didn't wipe out all tuples in the
database, rather than just the new ones. Here's why:
When I updated the "new" database with the new records I used the DELETE then
INSERT trick to avoid having to write logic to first see if there was an
existing record and then to update only the changing fields. Since I actually
deleted, then inserted, I'm guessing that the XID would change so that when I
moved the database over to the other server, ALL of the XIDs would be
different, not just the newly added rows. In which case, I would expect
VACUUM to wipe everything. Instead, it only wiped the new rows, which tells
me that even though I DELETED/INSERTED all existing rows, that somehow the
XID's still sync with the XID's on the other server.
Assuming the XIDs did change, I'd guess that though I had exactly the same
number of rows I started with (666730 instead of +1400000) it is because the
XIDs happened to correspond, but not necessarily with their original
relationships. Which would mean that I still had 666730 rows, but not the
original ones. Probably a smattering of new and old ones.
I'm just theorizing off of the top of my head .. please let me know where I
have gone wrong!
Much Thanks,
Bryan
-----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==---------- http://www.newsfeeds.com The
LargestUsenet Servers in the World!
------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----
