pg_group_name_index corrupt?
This worry anyone? :)
v7.0, compiled today based off of CVS ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
---------- Forwarded message ----------
pg_banners=> vacuum;
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
VACUUM
pg_banners=> \d pg_group
Table = pg_group
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| groname | name | 32 |
| grosysid | int4 | 4 |
| grolist | int4[] | var |
+----------------------------------+----------------------------------+-------+
Indices: pg_group_name_index
pg_group_sysid_indexpg_banners=> \d pg_group_name_index
Table = pg_group_name_index
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| groname | name | 32 |
+----------------------------------+----------------------------------+-------+
pg_banners=> drop index pg_group_name_index;
ERROR: class "pg_group_name_index" is a system catalog index
pg_banners=>
The Hermit Hacker <scrappy@hub.org> writes:
This worry anyone? :)
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
Not if you had other transactions running in parallel with the
vacuum --- if the vacuum was the only thing running then I'd want
to know what you were doing before that...
regards, tom lane
On Thu, 4 May 2000, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
This worry anyone? :)
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.Not if you had other transactions running in parallel with the
vacuum --- if the vacuum was the only thing running then I'd want
to know what you were doing before that...
I can't guarantee whether i was or not :( right now, I'm assuming that
'other transactions' would include any database on that server, not just
the database that I was vacuuming at the time, as even if I go in and do a
vacuum on 'template1', that error pops up ...
It says to 'recreate the index', but if I try to, it tells me its a system
table (of course) ... is there a way of fixing this without having to do
a dump/reload?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
This worry anyone? :)
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
It occurred to me that this would be the likely symptom if a CREATE
GROUP or ALTER GROUP command had neglected to update the indexes on
pg_group. However, I can't spot any such problem in the code nor
cause it to happen by hand. Anyone else have an idea?
BTW Marc, do you happen to know what sequence of user and/or group
create/alter/drops might have been executed on your setup?
regards, tom lane
On Thu, 4 May 2000, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
This worry anyone? :)
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.It occurred to me that this would be the likely symptom if a CREATE
GROUP or ALTER GROUP command had neglected to update the indexes on
pg_group. However, I can't spot any such problem in the code nor
cause it to happen by hand. Anyone else have an idea?BTW Marc, do you happen to know what sequence of user and/or group
create/alter/drops might have been executed on your setup?
the only group that is in pg_group is:
template1=> select * from pg_group;
groname |grosysid|grolist
-------------+--------+--------------
trends_acctng| 0|{70,1007,1008}
(1 row)
and it was added ~2 weeks ago ...
i tried deleting and re-adding it this morning, now it says (1 of 2) vs (0
of 1) ...
I had a server lock up the other day running an RC2 code base, which a
'truss -p' on postmaster produced nothing ... I did a kill on the server,
upgraded to the latest code and restarted it, after which this problem
occur'd ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
the only group that is in pg_group is:
template1=> select * from pg_group;
groname |grosysid|grolist
-------------+--------+--------------
trends_acctng| 0|{70,1007,1008}
(1 row)
and it was added ~2 weeks ago ...
i tried deleting and re-adding it this morning, now it says (1 of 2) vs (0
of 1) ...
Hmm, that's what I was going to suggest doing. I think there must be a
dead tuple in pg_group that VACUUM doesn't want to clean out for some
reason --- maybe it thinks there is still an open transaction somewhere
that could see the tuple as still live? Odd.
Did you try doing a vacuum of pg_group in between deleting and re-adding
the one valid group?
I had a server lock up the other day running an RC2 code base, which a
'truss -p' on postmaster produced nothing ... I did a kill on the server,
upgraded to the latest code and restarted it, after which this problem
occur'd ...
Wish you'd taken more careful notes when that happened. What do you
mean by lock up, exactly? Do you know what it was doing just
beforehand?
regards, tom lane
I wrote:
It occurred to me that this would be the likely symptom if a CREATE
GROUP or ALTER GROUP command had neglected to update the indexes on
pg_group. However, I can't spot any such problem in the code nor
cause it to happen by hand. Anyone else have an idea?
After further looking, I notice that users.c is one of the few places
that will drop AccessExclusiveLock at heap_close time rather than
holding it till xact commit. I wonder whether this is a bug...
it could allow another backend to get in and start vacuuming the file
before our updates have committed. I am not sure that vacuum would do
the wrong thing in that case, but maybe so. Comments anyone (Vadim?)
I also notice that there definitely is a glaring bug there:
write_password_file() leaks one kernel file descriptor each time it runs
(note the creat() call). Alter enough pg_shadow entries in one session
and your backend stops working. I think this is a "must fix" problem
--- any objections?regards, tom lane
On Thu, 4 May 2000, Tom Lane wrote:
I wrote:
It occurred to me that this would be the likely symptom if a CREATE
GROUP or ALTER GROUP command had neglected to update the indexes on
pg_group. However, I can't spot any such problem in the code nor
cause it to happen by hand. Anyone else have an idea?After further looking, I notice that users.c is one of the few places
that will drop AccessExclusiveLock at heap_close time rather than
holding it till xact commit. I wonder whether this is a bug...
it could allow another backend to get in and start vacuuming the file
before our updates have committed. I am not sure that vacuum would do
the wrong thing in that case, but maybe so. Comments anyone (Vadim?)I also notice that there definitely is a glaring bug there: write_password_file() leaks one kernel file descriptor each time it runs (note the creat() call). Alter enough pg_shadow entries in one session and your backend stops working. I think this is a "must fix" problem --- any objections?
None from me ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Thu, 4 May 2000, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
the only group that is in pg_group is:
template1=> select * from pg_group;
groname |grosysid|grolist
-------------+--------+--------------
trends_acctng| 0|{70,1007,1008}
(1 row)and it was added ~2 weeks ago ...
i tried deleting and re-adding it this morning, now it says (1 of 2) vs (0
of 1) ...Hmm, that's what I was going to suggest doing. I think there must be a
dead tuple in pg_group that VACUUM doesn't want to clean out for some
reason --- maybe it thinks there is still an open transaction somewhere
that could see the tuple as still live? Odd.Did you try doing a vacuum of pg_group in between deleting and re-adding
the one valid group?
Hadn't thought of that ... but ... and you aren't going to like this
... if I delete/vacuum/insert/vacuum ... INDEX TUPLES increases by 1, HEAP
increases by one ... I'm up to 3->4 now, and would go 4->5 if I were to do
it again ...
I had a server lock up the other day running an RC2 code base, which a
'truss -p' on postmaster produced nothing ... I did a kill on the server,
upgraded to the latest code and restarted it, after which this problem
occur'd ...Wish you'd taken more careful notes when that happened. What do you
mean by lock up, exactly? Do you know what it was doing just
beforehand?
Don't know ... one of hte problems I'm having with my FreeBSD machine
right now is that, for some reason, setproctitle() isn't working, so all
my backends look the same 'postmaster' and its start up options :(
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
Hadn't thought of that ... but ... and you aren't going to like this
... if I delete/vacuum/insert/vacuum ... INDEX TUPLES increases by 1, HEAP
increases by one ... I'm up to 3->4 now, and would go 4->5 if I were to do
it again ...
That definitely sounds like VACUUM thinks there's an old open transaction
somewhere and so it doesn't want to clean out the dead tuples.
I believe we have a mechanism for deciding that an old transaction must
have aborted (it involves looking to see if any active backend claims to
be running that transaction). But I wonder whether that mechanism is
being used when VACUUM decides whether it can clean out a dead tuple or
not. Vadim?
Don't know ... one of hte problems I'm having with my FreeBSD machine
right now is that, for some reason, setproctitle() isn't working, so all
my backends look the same 'postmaster' and its start up options :(
Now you know how the other half lives ;-).
regards, tom lane
-----Original Message-----
From: pgsql-hackers-owner@hub.org
[mailto:pgsql-hackers-owner@hub.org]On Behalf Of The Hermit HackerOn Thu, 4 May 2000, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
This worry anyone? :)
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES(0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES(0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
Not if you had other transactions running in parallel with the
vacuum --- if the vacuum was the only thing running then I'd want
to know what you were doing before that...I can't guarantee whether i was or not :( right now, I'm assuming that
'other transactions' would include any database on that server, not just
the database that I was vacuuming at the time, as even if I go in and do a
vacuum on 'template1', that error pops up ...
Why does pg_group exist under $PGDATA though the indexes exist
under each $PGDATA/base/db_name ?
Could it be consistent on all databases ?
It says to 'recreate the index', but if I try to, it tells me its a system
table (of course) ... is there a way of fixing this without having to do
a dump/reload?
Run "reindex table pg_group force;" under standalone postmaster
with options -O and -P. You must shutdown postmaster first.
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
Why does pg_group exist under $PGDATA though the indexes exist
under each $PGDATA/base/db_name ?
Could it be consistent on all databases ?
Oh my, I think you've got it! The indexes must be SharedSystemRelations!!
Backend 1 in database regression:
regression=# vacuum analyze pg_group;
VACUUM
Backend 2 in database other is also happy:
other=# vacuum analyze pg_group;
VACUUM
Now create a group with backend 1:
regression=# create group g;
CREATE GROUP
Backend 1 sees the index entries:
regression=# vacuum analyze pg_group;
VACUUM
But backend 2 doesn't:
other=# vacuum analyze pg_group;
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME
AS HEAP' (1).
Recreate the index.
VACUUM
pg_shadow would have the same problem if it had indices, which I thought
it did but they seem to have been disabled.
Can you say "one more initdb"? I knew you could...
regards, tom lane
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
Why does pg_group exist under $PGDATA though the indexes exist
under each $PGDATA/base/db_name ?
Could it be consistent on all databases ?
Oh my, I think you've got it! The indexes must be SharedSystemRelations!!
Yup, Hiroshi has spotted the problem. Turning the indexes on pg_group
into shared relations fixes the cross-database misbehavior shown in my
prior message, and I'll bet this bug explains Marc's report too.
We never had any indexes on pg_group (or any shared relation) before,
which is why we hadn't seen this kind of failure before. (Another
limitation of the regression tests exposed --- they don't test
cross-database behaviors.)
So, now what? This is a simple fix, but it will require initdb (or at
least pg_upgrade), which I'd really rather not do at this point in the
release cycle. But I'm not sure we have any choice. As it stands,
pg_group is broken.
If we are going to have to force a new initdb here, we probably ought
to reconsider a couple of recent past decisions that were put off on
grounds of "we don't want another initdb before 7.0". I'm thinking of
the remaining ODBC support functions and the new LIKE estimator in
particular. Do we want to revisit those decisions, or leave well enough
alone?
regards, tom lane
The Hermit Hacker <scrappy@hub.org> writes:
Hadn't thought of that ... but ... and you aren't going to like this
... if I delete/vacuum/insert/vacuum ... INDEX TUPLES increases by 1, HEAP
increases by one ... I'm up to 3->4 now, and would go 4->5 if I were to do
it again ...
That definitely sounds like VACUUM thinks there's an old open transaction
somewhere and so it doesn't want to clean out the dead tuples.
After looking some more at this, I'm not convinced that the problem
Hiroshi found explains everything Marc is seeing. Having multiple
copies of the pg_group indexes would certainly explain missing index
tuples in any one copy. But what about the behavior above? It still
seems that VACUUM is not cleaning out dead tuples from the table itself.
Marc, is it possible that you have any backends that are in the middle
of a transaction and have just been sitting that way for hours or days?
If so, that'd explain VACUUM's refusal to remove tuples. Another
symptom you should look for is notices like
NOTICE: RegisterSharedInvalid: SI buffer overflow
NOTICE: InvalidateSharedInvalid: cache state reset
in the postmaster log --- there'll probably be quite a few if there's
a hung backend somewhere.
regards, tom lane
On Thu, 4 May 2000, Tom Lane wrote:
But backend 2 doesn't:
other=# vacuum analyze pg_group;
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME
AS HEAP' (1).
Recreate the index.
VACUUMpg_shadow would have the same problem if it had indices, which I thought
it did but they seem to have been disabled.Can you say "one more initdb"? I knew you could...
Would a pg_upgrade fix this, or do I have to do a whole dump/reload?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Thu, 4 May 2000, Tom Lane wrote:
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
Why does pg_group exist under $PGDATA though the indexes exist
under each $PGDATA/base/db_name ?
Could it be consistent on all databases ?Oh my, I think you've got it! The indexes must be SharedSystemRelations!!
Yup, Hiroshi has spotted the problem. Turning the indexes on pg_group
into shared relations fixes the cross-database misbehavior shown in my
prior message, and I'll bet this bug explains Marc's report too.We never had any indexes on pg_group (or any shared relation) before,
which is why we hadn't seen this kind of failure before. (Another
limitation of the regression tests exposed --- they don't test
cross-database behaviors.)So, now what? This is a simple fix, but it will require initdb (or at
least pg_upgrade), which I'd really rather not do at this point in the
release cycle. But I'm not sure we have any choice. As it stands,
pg_group is broken.If we are going to have to force a new initdb here, we probably ought
to reconsider a couple of recent past decisions that were put off on
grounds of "we don't want another initdb before 7.0". I'm thinking of
the remaining ODBC support functions and the new LIKE estimator in
particular. Do we want to revisit those decisions, or leave well enough
alone?
Leave well enough alone ... this fixed, IMHO, a *very* important potential
bug, whereas the other two can be worked around. AT this *really* late
stage in the cycle, fixing one bug at least only opens us up to the
possibility of one bug ... doing the ODBC/LIKE stuff aren't mission
critical, and really only affect a relatively small group of ppl in
comparison ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Fri, 5 May 2000, Hiroshi Inoue wrote:
It says to 'recreate the index', but if I try to, it tells me its a system
table (of course) ... is there a way of fixing this without having to do
a dump/reload?Run "reindex table pg_group force;" under standalone postmaster
with options -O and -P. You must shutdown postmaster first.
Okay, just looked at the postgres man page, and found the -O option, but
no -P ... this is in the users-lounge/7.0/docs directory ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Coolness, that worked like a charm ... so I take it I have to do this for
each and every database on the system? :)
On Fri, 5 May 2000, Hiroshi Inoue wrote:
-----Original Message-----
From: pgsql-hackers-owner@hub.org
[mailto:pgsql-hackers-owner@hub.org]On Behalf Of The Hermit HackerOn Thu, 4 May 2000, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
This worry anyone? :)
NOTICE: Index pg_group_sysid_index: NUMBER OF INDEX' TUPLES(0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
NOTICE: Index pg_group_name_index: NUMBER OF INDEX' TUPLES(0) IS NOT THE SAME AS HEAP' (1).
Recreate the index.
Not if you had other transactions running in parallel with the
vacuum --- if the vacuum was the only thing running then I'd want
to know what you were doing before that...I can't guarantee whether i was or not :( right now, I'm assuming that
'other transactions' would include any database on that server, not just
the database that I was vacuuming at the time, as even if I go in and do a
vacuum on 'template1', that error pops up ...Why does pg_group exist under $PGDATA though the indexes exist
under each $PGDATA/base/db_name ?
Could it be consistent on all databases ?It says to 'recreate the index', but if I try to, it tells me its a system
table (of course) ... is there a way of fixing this without having to do
a dump/reload?Run "reindex table pg_group force;" under standalone postmaster
with options -O and -P. You must shutdown postmaster first.Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
Can you say "one more initdb"? I knew you could...
Would a pg_upgrade fix this, or do I have to do a whole dump/reload?
pg_upgrade *should* work, but if I were you I'd make a backup dump
first, just in case. I don't trust pg_upgrade very much...
regards, tom lane
The Hermit Hacker <scrappy@hub.org> writes:
If we are going to have to force a new initdb here, we probably ought
to reconsider a couple of recent past decisions that were put off on
grounds of "we don't want another initdb before 7.0". I'm thinking of
the remaining ODBC support functions and the new LIKE estimator in
particular. Do we want to revisit those decisions, or leave well enough
alone?
Leave well enough alone ... this fixed, IMHO, a *very* important potential
bug, whereas the other two can be worked around. AT this *really* late
stage in the cycle, fixing one bug at least only opens us up to the
possibility of one bug ... doing the ODBC/LIKE stuff aren't mission
critical, and really only affect a relatively small group of ppl in
comparison ...
That's a fair objection for the LIKE estimator, which after all hasn't
gotten much testing. I'll leave well enough alone there. But those
missing ODBC functions are just another dozen SQL-function entries for
pg_proc; hard to see how they can break anything else, even if (worst
case) they're wrong themselves ...
regards, tom lane
