Re: [HACKERS] FW: Duplicate oids!
Patrick Macdonald wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Dec 13, 2002 at 09:43:19AM -0500, Tom Lane wrote: Actually, if you don't mind grabbing a copy of pg_filedump --- see http://sources.redhat.com/rhdb/tools.html Has this been updated for 7.3? Last time I looked it only did 7.2, and the site shows an old date. If it hasn't, are there plans to update it sometime soon? It would be very useful to me right now... AFAIK it has not been updated yet. Patrick, do you have any near-term plans to do so? If not, perhaps Alvaro would like to do the legwork ;-) Yes, it's on my list of things to do. Look for an updated version by middle of the week (once all the RHDB 2.1 work is finished). I've updated the pg_filedump utility for PostgreSQL 7.3. The new version, 1.1, requires a PostgreSQL 7.3 source tree to build and can be used against RHDB 2.x/1.x and PostgreSQL 7.3/7.2/7.1 installations. All questions and comments about the tool should be directed to [EMAIL PROTECTED], not this list. The pg_filedump utility can be found at the Red Hat Database Project site (http://sources.redhat.com/rhdb). Cheers, Patrick ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: Duplicate oids!
I've now got a copy of pg_filedump and compiled it, can you tell me the command line parameters to pass it (and the file that I must process) so I can give you exactly what you require. Thanks Steve -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 13 December 2002 14:43 To: Steve King Cc: 'Hannu Krosing'; '[EMAIL PROTECTED]' Subject: Re: [HACKERS] FW: Duplicate oids! Steve King [EMAIL PROTECTED] writes: The ctids are different however vaccum is run on this table and the record is updated. It would be useful to look at xmin,xmax,cmin,cmax of these tuples too. Actually, if you don't mind grabbing a copy of pg_filedump --- see http://sources.redhat.com/rhdb/tools.html --- then a dump of page 7 of that relation would be good to look at. I am wondering about the states of the infomask bits for these tuples... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] FW: Duplicate oids!
Steve King [EMAIL PROTECTED] writes: I've now got a copy of pg_filedump and compiled it, can you tell me the command line parameters to pass it (and the file that I must process) so I can give you exactly what you require. I'd recommend pg_filedump -f -i -R blocknum filename where blocknum is whatever page you need to look at (the high part of the TIDs of the bad tuples), and filename is going to be of the form $PGDATA/base/dbnum/filenum You get the DB number from select oid from pg_database where datname = 'yourdbname'; and the file number from select relfilenode from pg_class where relname = 'yourtablename'; regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FW: Duplicate oids!
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Dec 13, 2002 at 09:43:19AM -0500, Tom Lane wrote: Actually, if you don't mind grabbing a copy of pg_filedump --- see http://sources.redhat.com/rhdb/tools.html Has this been updated for 7.3? Last time I looked it only did 7.2, and the site shows an old date. If it hasn't, are there plans to update it sometime soon? It would be very useful to me right now... AFAIK it has not been updated yet. Patrick, do you have any near-term plans to do so? If not, perhaps Alvaro would like to do the legwork ;-) Yes, it's on my list of things to do. Look for an updated version by middle of the week (once all the RHDB 2.1 work is finished). I believe it should be possible to make a single version of pg_filedump that understands both the 7.2 and 7.3 page layouts (the version field in the page header would work for telling what you're looking at) Correct. The tool will be updated to understand the different page layouts/formats. Two tools would be a pain... Cheers, Patrick -- Patrick Macdonald Red Hat Database Development ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: Duplicate oids!
Steve King [EMAIL PROTECTED] writes: Files output from pg_filedump are below, I have two tables with duplicate oids and these are the pg_filedumps for them. Hmm. You seem to have a rather unusual usage pattern for these tables --- it looks like there are *lots* of failed (rolled back) updates of the same tuple. Can you give us some details about the update commands being issued against these tables? As an example, the page from the machine table has many entries like this: Item 9 -- Length: 261 Offset: 5288 (0x14a8) Flags: USED OID: 9646238 CID: min(2) max(0) XID: min(119186229) max(0) Block Id: 7 linp Index: 9 Attributes: 41 Size: 32 infomask: 0x2a02 (HASVARLENA|XMIN_INVALID|XMAX_INVALID|UPDATED) which is evidently a failed (aborted) update of the row with OID 9646238. There are 27 other items on the same page with different XIDs --- all failed commands, according to the XMIN_INVALID bits --- and these two: Item 10 -- Length: 261 Offset: 7928 (0x1ef8) Flags: USED OID: 9646238 CID: min(5) max(2) XID: min(119162725) max(119208868) Block Id: 759 linp Index: 30 Attributes: 41 Size: 32 infomask: 0x2902 (HASVARLENA|XMIN_COMMITTED|XMAX_INVALID|UPDATED) Item 18 -- Length: 261 Offset: 7664 (0x1df0) Flags: USED OID: 9646238 CID: min(2) max(2) XID: min(119162754) max(119208939) Block Id: 760 linp Index: 3 Attributes: 41 Size: 32 infomask: 0x2902 (HASVARLENA|XMIN_COMMITTED|XMAX_INVALID|UPDATED) These are both still valid (since their attempted deleting transactions both rolled back, according to the XMAX_INVALID bits). But how did transactions 119162725 and 119162754 both manage to commit updates of the same row, without one invalidating the other? I wonder whether this is evidence of a race condition in the concurrent-update logic. Can't tell without much more detail, though. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] FW: Duplicate oids!
-Original Message- From: Steve King Sent: 12 December 2002 11:45 To: [EMAIL PROTECTED] Subject: Duplicate oids! Forgive me if this is a previous question but I cannot find any information on it in any of the mailing lists. I have a postgres database that contains a table with two identical records including the oid. It seems as though one insert statement (intending one record to be inserted) has caused two identical records to be inserted. The insert statement was done via the c++ library. Does anyone know anything about this? My info can be supplied if this is not a known problem! P.S. I am running Postgres 7.2 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: Duplicate oids!
On Fri, 2002-12-13 at 09:27, Steve King wrote: -Original Message- From: Steve King Sent: 12 December 2002 11:45 To: [EMAIL PROTECTED] Subject:Duplicate oids! Forgive me if this is a previous question but I cannot find any information on it in any of the mailing lists. I have a postgres database that contains a table with two identical records including the oid. What about ctid's, are they also the same ? Are the tuples on the same page ? It seems as though one insert statement (intending one record to be inserted) has caused two identical records to be inserted. The insert statement was done via the c++ library. Does anyone know anything about this? My info can be supplied if this is not a known problem! P.S. I am running Postgres 7.2 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] FW: Duplicate oids!
The ctids are different however vaccum is run on this table and the record is updated. The machineid is a SERIAL and so should also never be duplicated. ctid | oid | machineid +-+--- (7,18) | 9646238 |12 (7,10) | 9646238 |12 Any help as usual is greatly appreciated. -Original Message- From: Hannu Krosing [mailto:[EMAIL PROTECTED]] Sent: 13 December 2002 11:54 To: Steve King Cc: '[EMAIL PROTECTED]' Subject: Re: [HACKERS] FW: Duplicate oids! On Fri, 2002-12-13 at 09:27, Steve King wrote: -Original Message- From: Steve King Sent: 12 December 2002 11:45 To: [EMAIL PROTECTED] Subject:Duplicate oids! Forgive me if this is a previous question but I cannot find any information on it in any of the mailing lists. I have a postgres database that contains a table with two identical records including the oid. What about ctid's, are they also the same ? Are the tuples on the same page ? It seems as though one insert statement (intending one record to be inserted) has caused two identical records to be inserted. The insert statement was done via the c++ library. Does anyone know anything about this? My info can be supplied if this is not a known problem! P.S. I am running Postgres 7.2 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] FW: Duplicate oids!
Steve King [EMAIL PROTECTED] writes: The ctids are different however vaccum is run on this table and the record is updated. It would be useful to look at xmin,xmax,cmin,cmax of these tuples too. Actually, if you don't mind grabbing a copy of pg_filedump --- see http://sources.redhat.com/rhdb/tools.html --- then a dump of page 7 of that relation would be good to look at. I am wondering about the states of the infomask bits for these tuples... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] FW: Duplicate oids!
On Fri, Dec 13, 2002 at 09:43:19AM -0500, Tom Lane wrote: Actually, if you don't mind grabbing a copy of pg_filedump --- see http://sources.redhat.com/rhdb/tools.html Has this been updated for 7.3? Last time I looked it only did 7.2, and the site shows an old date. If it hasn't, are there plans to update it sometime soon? It would be very useful to me right now... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Para tener mas hay que desear menos ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] FW: Duplicate oids!
Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Dec 13, 2002 at 09:43:19AM -0500, Tom Lane wrote: Actually, if you don't mind grabbing a copy of pg_filedump --- see http://sources.redhat.com/rhdb/tools.html Has this been updated for 7.3? Last time I looked it only did 7.2, and the site shows an old date. If it hasn't, are there plans to update it sometime soon? It would be very useful to me right now... AFAIK it has not been updated yet. Patrick, do you have any near-term plans to do so? If not, perhaps Alvaro would like to do the legwork ;-) I believe it should be possible to make a single version of pg_filedump that understands both the 7.2 and 7.3 page layouts (the version field in the page header would work for telling what you're looking at). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
