Re: error updating a tuple after promoting a standby

On 12/21/2016 10:06 AM, Tom DalPozzo wrote:
> Is there an index on this table?
>
>
> Have you tried a REINDEX on it?
>
> yes there is an index on id field. I tried REINDEX. Nothing changes but
> I notice now (but perhaps it was like that even before reindexing) that
> every time I issue that UPDATE query, the number of the block it can't
> read increases by one. Now, after some attempts: ERROR: could not read
> block 12289 in file "base/16384/29153": read only 0 of 8192 bytes.

> Unfortunately I don't remember every step as I was focused on completely
> other things... Anyway, in synthesis:
> 1 pg_basebackup on primary and added, to the just created backup pg_xlog
> dir, the needed WAL files according to the .label file (I'm trying
> without archiving) .

If it where me I would use one of the -X methods:

https://www.postgresql.org/docs/9.5/static/app-pgbasebackup.html

> A new thing:
> I noticed that, always restarting from the corrupted cluster (without
> reindex I mean), if I update the row id=409 with few data (3 bytes),
> then it works and after that, even updating with that long data works.

To me that looks like an issue with the associated TOAST table. I do not
have a suggestion at this time. Maybe this rings a bell with someone else.

>
> Regards
> Pupillo
>
>
>
>
>

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