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>

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