Обсуждение: FW: Duplicate oids!
> -----Original Message----- > From: Steve King > Sent: 12 December 2002 11:45 > To: pgsql-hackers@postgresql.org > 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
On Fri, 2002-12-13 at 09:27, Steve King wrote: > > -----Original Message----- > > From: Steve King > > Sent: 12 December 2002 11:45 > > To: pgsql-hackers@postgresql.org > > 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 <hannu@tm.ee>
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:hannu@tm.ee] Sent: 13 December 2002 11:54 To: Steve King Cc: 'pgsql-hackers@postgresql.org' 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: pgsql-hackers@postgresql.org > > 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 <hannu@tm.ee> ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Steve King <steve.king@ecmsys.co.uk> 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
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"
Alvaro Herrera <alvherre@dcc.uchile.cl> 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
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:tgl@sss.pgh.pa.us] Sent: 13 December 2002 14:43 To: Steve King Cc: 'Hannu Krosing'; 'pgsql-hackers@postgresql.org' Subject: Re: [HACKERS] FW: Duplicate oids! Steve King <steve.king@ecmsys.co.uk> 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
Steve King <steve.king@ecmsys.co.uk> 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
Tom Lane wrote: > > Alvaro Herrera <alvherre@dcc.uchile.cl> 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
Steve King <steve.king@ecmsys.co.uk> 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
Patrick Macdonald wrote: > > Tom Lane wrote: > > > > Alvaro Herrera <alvherre@dcc.uchile.cl> 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 rhdb@sources.redhat.com, not this list. The pg_filedump utility can be found at the Red Hat Database Project site (http://sources.redhat.com/rhdb). Cheers, Patrick