Обсуждение: ERROR: missing chunk number 0 for toast value while using logical decoder.\

I am using PostgresSQL 9.5.0.
I have customized :
https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw to get
result as json. It works fine. suddenly i getting below exception while
consuming or peeking changes through that logical slot.
Does postgres removed the  rows needed for that slot??
Kindly help me to find the problem.

db=# select * from pg_logical_slot_get_changes('lslot',NULL,1);
ERROR:  missing chunk number 0 for toast value 857563 in pg_toast_782254
CONTEXT:  slot "lslot", output plugin "rep_slot", in the change callback,
associated LSN 2E/3C26D6F0


 select * from pg_class where relname like 'pg_toast_782254';
-[ RECORD 1 ]-------+----------------
relname             | pg_toast_782254
relnamespace        | 99
reltype             | 782273
reloftype           | 0
relowner            | 10
relam               | 0
relfilenode         | 782272
reltablespace       | 0
relpages            | 464
reltuples           | 2066
relallvisible       | 464
reltoastrelid       | 0
relhasindex         | t
relisshared         | f
relpersistence      | p
relkind             | t
relnatts            | 3
relchecks           | 0
relhasoids          | f
relhaspkey          | t
relhasrules         | f
relhastriggers      | f
relhassubclass      | f
relrowsecurity      | f
relforcerowsecurity | f
relispopulated      | t
relreplident        | n
relfrozenxid        | 1982173
relminmxid          | 1
relacl              |
reloptions          |



Thanks In Advance.



-----
sudalai
--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-chunk-number-0-for-toast-value-while-using-logical-decoder-tp5909194.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 6/22/2016 11:38 PM, sudalai wrote:
> I am using PostgresSQL 9.5.0.

while this likely has no bearing on your problem, you really should
upgrade to 9.5.latest, 9.5.3 is out now.    sub version upgrades like
9.5.0 to 9.5.3 are painless, just upgrade and restart the server with
the new code.


--
john r pierce, recycling bits in santa cruz

Hi john,

    PostgreSQL 9.5.0  has bug !!!,  Does 9.5.0 remove  unconsumed toast rows
need for slot??
    It is fixed in latest!!!.

-Sudalai



-----
sudalai
--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-chunk-number-0-for-toast-value-while-using-logical-decoder-tp5909194p5909201.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, Jun 23, 2016 at 3:38 PM, sudalai <sudalait2@gmail.com> wrote:
>
> I am using PostgresSQL 9.5.0.
> I have customized :
> https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw to get
> result as json. It works fine. suddenly i getting below exception while
> consuming or peeking changes through that logical slot.
> Does postgres removed the  rows needed for that slot??
> Kindly help me to find the problem.
>
> db=# select * from pg_logical_slot_get_changes('lslot',NULL,1);
> ERROR:  missing chunk number 0 for toast value 857563 in pg_toast_782254
> CONTEXT:  slot "lslot", output plugin "rep_slot", in the change callback,
> associated LSN 2E/3C26D6F0

A couple of failures in logical decoding have been fixed in 9.5.2 and
9.5.3 but this one is new, still you had better update... The error is
visibly coming from the code of your plugin per the context message.
Do you have an SQL sequence that allows to reproduce the problem? I
recall playing with a couple of data types with my own plugin but I
never noticed that.
--
Michael

Thanks for : https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw
I will upgrade my servers to PostgreSQL 9.5.3, but i want to find the
problem.
Please help me.
I don't have SQL sequence.
Does anything i can get from server, that will help us to debug the problem?

Here is logical_decoder code:  rep_slot.c
<http://postgresql.nabble.com/file/n5909207/rep_slot.c>

Thanks in Advance.

-Sudalai



-----
sudalai
--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-chunk-number-0-for-toast-value-while-using-logical-decoder-tp5909194p5909207.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, Jun 23, 2016 at 4:55 PM, sudalai <sudalait2@gmail.com> wrote:
> Thanks for : https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw
> I will upgrade my servers to PostgreSQL 9.5.3, but i want to find the
> problem.
> Please help me.
> I don't have SQL sequence.
> Does anything i can get from server, that will help us to debug the problem?
>
> Here is logical_decoder code:  rep_slot.c
> <http://postgresql.nabble.com/file/n5909207/rep_slot.c>

    if (isnull)
        appendStringInfoString(s, "NiLnUlL");   // Printing null as "NiLnUlL"
    //else if (typisvarlena && VARATT_IS_EXTERNAL_ONDISK(origval))
    //  appendStringInfoString(s, "unchanged-toast-datum")
Uncomment that.
--
Michael

Thank u.
The problem is because of the commented line.  I forgot about that.
Now, I uncommented it. It working fine.
Thank u very much.

-Sudalai




-----
sudalai
--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-chunk-number-0-for-toast-value-while-using-logical-decoder-tp5909194p5909233.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

@micheal,
    Thanks for your help. It working now.
    But, now I'm getting deadlock, when i peek_changes or get_changes, in
live system.

    db=#select count(*) from pg_logical_slot_peek_changes('slot',NULL,100);
/* pid : 25833 */
ERROR:  deadlock detected
DETAIL:  Process 25833 waits for ShareLock on relation 248486 of database
16385; blocked by process 18904.
Process 18904 waits for RowExclusiveLock on relation 257419 of database
16385; blocked by process 25833.
HINT:  See server log for query details.
CONTEXT:  slot "slot", output plugin "rep_slot", in the change callback,
associated LSN CD/EF693310

     Please help me, I am using PostgreSQL 9.5.0.
     I don't know much in postgres internals. why does peek_changes trying
to take share lock on 248486 (primary key of one table).
     Its postgres logical decoder  takes share lock or output-plugin takes
share lock ?
     Is there any way i can avoid it?  Please help me.

-Sudalai




-----
sudalai
--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-chunk-number-0-for-toast-value-while-using-logical-decoder-tp5909194p5912389.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Jul 18, 2016 at 4:31 PM, sudalai <sudalait2@gmail.com> wrote:
>      Please help me, I am using PostgreSQL 9.5.0.
>      I don't know much in postgres internals. why does peek_changes trying
> to take share lock on 248486 (primary key of one table).
>      Its postgres logical decoder  takes share lock or output-plugin takes
> share lock ?
>      Is there any way i can avoid it?  Please help me.

No way to say as that's your plugin code. I think that you should look
at your code and grep for ShareLock, trying to find out why this is
taken. My guess is that a lower level of locking could be used,
ShareLock is a strong one, taken when you want to be sure that there
are schema changes for example.
--
Michael

thank u.
why (select * from pg_logical_slot_peek_changes('slot',NULL,1) )  take share
lock ?
any  idea ?

-Sudalai



-----
sudalai
--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-chunk-number-0-for-toast-value-while-using-logical-decoder-tp5909194p5912411.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Jul 18, 2016 at 7:08 PM, sudalai <sudalait2@gmail.com> wrote:
> thank u.
> why (select * from pg_logical_slot_peek_changes('slot',NULL,1) )  take share
> lock ?
> any  idea ?

My guess is that this lock comes from your plugin code. Please double-check it.
--
Michael

Got it. Thank you very much. :)
-Sudalai



-----
sudalai
--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-chunk-number-0-for-toast-value-while-using-logical-decoder-tp5909194p5912426.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.