Tom Lane napisał(a):
Bartosz Nowak <grubby@xxxxxx> writes:
Tom Lane napisał(a):
You could manually delete either row, probably better to zap the second
one:
delete from pg_shadow where ctid = '(1,25)';
Heh... i wish it was that easy - i tried it already :] When i delete the
'second' postgres user (with passwd set) PG is acting like there is no
postgres account at all:
...
And i cannot modify row of 'first' postgres user (without passwd set)
with or without 'second' present:
mw=> delete from pg_shadow where ctid = '(0,1)' ;
DELETE 0
Ugh. That's looking more like you have a transaction ID wraparound
problem. How long has it been since pg_shadow was last vacuumed?
You could try a "VACUUM FREEZE pg_shadow" and see if the rows act any
more normally after that. (Better take a filesystem-level backup
first, so you can get out of it if that makes things worse.)
regards, tom lane
We vacuum whole base regularly, and lately it was about week ago (then
the problem occured). Since pg_dump don't work we are making
filesystem-level backups, and still im experimenting on local copy of
this db for safety.
VACUUM FREEZE pg_shadow changed the pg_shadow table:
select ctid,xmin,xmax,cmin,xmax,* from pg_shadow where usename = 'postgres';
ctid | xmin | xmax | cmin | xmax | usename | usesysid |
usecreatedb | usesuper | usecatupd | passwd
| valuntil | useconfig
-------+------+-------+-------+-------+----------+----------+-------------+----------+-----------+-------------------------------------+----------+-----------
(0,1) | 1 | 50469 | 50469 | 50469 | postgres | 1 |
t | t | t |
| |
(1,4) | 2 | 0 | 0 | 0 | postgres | 1 |
t | t | t | md5c084502ed11efa9d3d96d29717a5e555
| |
(2 rows)
But rows still act unnormally :[, i.e.:
update pg_catalog.pg_shadow set passwd='test' where usename='postgres'
and ctid = '(0,1)';
UPDATE 0
delete from pg_shadow where ctid = '(0,1)' ;
DELETE 0
delete from pg_shadow where usesysid = 1 and passwd !=
'md5c084502ed11efa9d3d96d29717a5e555' ;
DELETE 0
delete from pg_shadow where usesysid = 1 ;
DELETE 1
select ctid,xmin,xmax,cmin,xmax,* from pg_shadow where usename =
'postgres'; ctid | xmin | xmax | cmin | xmax | usename | usesysid
| usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
-------+------+-------+-------+-------+----------+----------+-------------+----------+-----------+--------+----------+-----------
(0,1) | 1 | 50469 | 50469 | 50469 | postgres | 1 |
t | t | t | | |
(1 row)
update pg_catalog.pg_shadow set passwd='test' where usename='postgres' ;
UPDATE 0
pg_dumpall -i -U mw > test.sql
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: pg_class_aclcheck: invalid
user id 1
pg_dump: The command was: select (select usename from pg_user where
usesysid = datdba) as dba, encoding, datpath from pg_database where
datname = 'alibi'
pg_dumpall: pg_dump failed on alibi, exiting
Greetings,
Bartek
--
Pozdrawiam,
GRUbY
