Re: error updating a tuple after promoting a standby

On 12/21/2016 01:51 AM, Tom DalPozzo wrote:
> Hi,
> I was doing some tests with backup, replication, standby. After
> promoting a standby server, I found my db in a condition that raises me
> an error while trying to update a particular tuple.
> Below here you can se my UPDATE statment and the error raised.
> The select * from stato where id=409; executed immediately after worked
> well however.
> I checked the file and it's readable.
> Before my standby promotion test I performed millions of this UPDATE
> statments without problem on my db.
> I can not reproduce the issue.
>
> Perhaps I did something wrong during my test but I don't know what. I
> didn't touch any file in base directory however.
> Anyway I'd like to know if in your opinion it's possible that this error
> was caused by something wrong done by me or if it should never happen as
> the file is perfectly readable.
>
> Regards
> Pupillo
>
>
>
>
> psql (9.5.4)
> Type "help" for help.
>
> ginopino=# UPDATE stato SET
> dati='\x5353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353535353'
> WHERE id=409;
> ERROR: could not read block 12281 in file "base/16384/29153": read only

First I would find what base/16384/29153 actually is. So in the database
where stato is:

select relname from pg_class where relfilenode = 29153;

> 0 of 8192 bytes
> ginopino=# select * from stato where id=409; <<< IT WORKS FINE

But does it have the updated info?

>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com