Обсуждение: Weird problem - possibly a bug.
I have a single row table that I use to hold various data, this table is only ever updated by my programs, none of them ever do an insert ( I set the values initially using psql. I have not had cause to look in the table until today when a client notified me of a database error occuring in one of my programs. The table had 4 rows, all with the same OID. ( It is only supposed to have 1 !!) admin=# select oid,* from control; oid | next_client | next_dialin | next_domain | next_invnum | next_rctnum | next_creditcard | open_period | rebuild_mail_config | rebuild_http_config -------+-------------+-------------+-------------+-------------+-------------+-----------------+----- --------+---------------------+--------------------- 37238 | 10285 | 1032 | 10288 | 1011804 | 2011237 | 141 | 200112 | f | f 37238 | 10285 | 1032 | 10288 | 1011804 | 2011237 | 141 | 200112 | f | f 37238 | 10285 | 1032 | 10288 | 1011804 | 2011251 | 141 | 200112 | f | f 37238 | 10285 | 1032 | 10288 | 1011804 | 2011251 | 141 | 200112 | f | f (4 rows) It would appear that on a number of occassions, something has gone wrong. I am using 7.1.3 on RH7.1 Finding these extra rows, with duplicate oids has destroyed my faith in the integrity of the rest of this and other databases. Comments appreciated. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
Aha,
so I wasn't halluzinating at all, and Tom was right that it
probably doesn't have to do with the lazy vacuum by itself.
But anyway, "HAL, we have a problem". Seems we have some
longer standing bug here, causing a row to survive an UPDATE.
Jan
Glen Eustace wrote:
> I have a single row table that I use to hold various data, this table is only
> ever updated by my programs, none of them ever do an insert ( I set the
> values initially using psql. I have not had cause to look in the table until
> today when a client notified me of a database error occuring in one of my
> programs.
>
> The table had 4 rows, all with the same OID. ( It is only supposed to have 1
> !!)
>
> admin=# select oid,* from control;
> oid | next_client | next_dialin | next_domain | next_invnum | next_rctnum
> | next_creditcard | open_period | rebuild_mail_config | rebuild_http_config
> -------+-------------+-------------+-------------+-------------+-------------+-----------------+-----
> --------+---------------------+---------------------
> 37238 | 10285 | 1032 | 10288 | 1011804 | 2011237
> | 141 | 200112 | f | f
> 37238 | 10285 | 1032 | 10288 | 1011804 | 2011237
> | 141 | 200112 | f | f
> 37238 | 10285 | 1032 | 10288 | 1011804 | 2011251
> | 141 | 200112 | f | f
> 37238 | 10285 | 1032 | 10288 | 1011804 | 2011251
> | 141 | 200112 | f | f
> (4 rows)
>
> It would appear that on a number of occassions, something has gone wrong. I
> am using 7.1.3 on RH7.1
>
> Finding these extra rows, with duplicate oids has destroyed my faith in the
> integrity of the rest of this and other databases.
>
> Comments appreciated.
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Glen and Rosanne Eustace,
> GodZone Internet Services, a division of AGRE Enterprises Ltd.,
> P.O. Box 8020, Palmerston North, New Zealand 5301
> Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Tuesday 11 December 2001 10:41, Jan Wieck wrote: > But anyway, "HAL, we have a problem". Seems we have some > longer standing bug here, causing a row to survive an UPDATE. If it is any help, I do a 'vacuum analyze' on all our databases every night. What concerns me, is whether this is a more wide spread issue. Any hints about what I can do to restore my confidence that I don't have the same problem in other tables and databases. Glen -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
Glen Eustace <geustace@godzone.net.nz> writes:
> The table had 4 rows, all with the same OID. ( It is only supposed to have 1
> !!)
The existence of multiple rows with the same OID is not in itself a bug,
unless you have a unique index on OID on that table.
Jan evidently thinks that you have gotten into this state because a few
UPDATEs failed to invalidate the old copy of the row. That's one
possible scenario but it's somewhat difficult to credit. I'm wondering
about VACUUM dropping the ball while moving the row across page
boundaries, myself.
Can you provide:
1. samples of all the query sequences that update this table. (I'm
particularly interested in whether you use SELECT FOR UPDATE.)
2. A dump of the table including all system columns (ctid, oid, xmin,
cmin, xmax, cmax) as well as the user data. Also, what's the exact
schema declaration of the table? Are there any rules, triggers, foreign
key constraints attached to it?
Also, have you suffered any system crashes that might have interrupted
updates of this table?
regards, tom lane
Tom Lane wrote:
> Can you provide:
>
> 1. samples of all the query sequences that update this table. (I'm
> particularly interested in whether you use SELECT FOR UPDATE.)
That is at least true for when it happened to me. What do you
know about SELECT FOR UPDATE that I can't see up to now ...
hmmmm ...
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com