Thread: FW: Duplicate oids!

FW: Duplicate oids!

From
Steve King
Date:

>  -----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


Re: FW: Duplicate oids!

From
Hannu Krosing
Date:
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>


Re: FW: Duplicate oids!

From
Steve King
Date:
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


Re: FW: Duplicate oids!

From
Tom Lane
Date:
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


Re: FW: Duplicate oids!

From
Alvaro Herrera
Date:
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"


Re: FW: Duplicate oids!

From
Tom Lane
Date:
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


Re: FW: Duplicate oids!

From
Steve King
Date:
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


Re: FW: Duplicate oids!

From
Tom Lane
Date:
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


Re: FW: Duplicate oids!

From
Patrick Macdonald
Date:
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


Re: FW: Duplicate oids!

From
Tom Lane
Date:
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


Re: FW: Duplicate oids!

From
Patrick Macdonald
Date:
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