Thread: Weird problem - possibly a bug.

Weird problem - possibly a bug.

From
Glen Eustace
Date:
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

Re: Weird problem - possibly a bug.

From
Jan Wieck
Date:
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


Re: Weird problem - possibly a bug.

From
Glen Eustace
Date:
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

Re: Weird problem - possibly a bug.

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

Re: Weird problem - possibly a bug.

From
Jan Wieck
Date:
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