PostgreSQL logical decoder output plugin - unchanged toast data
Hi. I have a question about PostgreSQL logical decoder output plugin. I am not specialist in Postgres at all, so maybe I miss some very basic point. In the plugin, I want to always get all the values (even those that are unchanged toast data) When I try to get the datum that is internal on disk (here is code) ``` struct varlena *s = (struct varlena *)DatumGetPointer(origval); struct varlena * ret = heap_tuple_untoast_attr(s); Datum result = PointerGetDatum(PG_DETOAST_DATUM(ret)); ``` it fails with no known snapshots error (from heap_tuple_untoast_attr). My question is why is it that. So, even theoretically it is not possible to the the varlena on disk from logical replication plugin? Mit freundlichen Grüßen, Georgy Buranov
Re: PostgreSQL logical decoder output plugin - unchanged toast data
Ok, thank you very much for your explanation, maybe I need something else in my case. As far as I understand, "On-disk toasted data for tuples from the WAL are not guaranteed in any way to be retain", but still, the LATEST value for the same cell should exist in postgres (in on-disk toast if it is huge, or not). If I cannot get access to the on-disk toasted data for tuple from the WAL, can I have the access to the _latest_ value in this case (hopefully I describe it correct) > Yes, that's not possible in general. On-disk toasted data for tuples > from the WAL are not guaranteed in any way to be retained. If that > weren't the case database tables would bloat while logical replication > is behind, and the sequential reads (i.e. fast) reads of logical > decoding would turn into random IO. Mit freundlichen Grüßen, Georgy Buranov
Re: PostgreSQL logical decoder output plugin - unchanged toast data
> Again, you can set REPLICA IDENTITY to FULL and it'll be there. > So, why I think this is complicated * We use primary keys for all tables, so we do not need REPLICA IDENTITY full actually. As far as I understand, it will make master/slave replication ineffective as well * I need the information about this primary key in rd_replidindex of relation (to send it later to kafka). As far as I understand, with REPLICA IDENTITY FULL the rd_replidindex will not be the primary key
Re: PostgreSQL logical decoder output plugin - unchanged toast data
Ok, I got false understanding that REPLICA IDENTITY is used for something more than a WAL. This is basically not true. So, what I can do * Set the REPLICA IDENTITY to full, and in this case I can still get the pk from rd_pkindex. In this case the WAL will be bigger, but we will have all the values in it. * Other solution is basically (since we know exact primary key) - get the row, and take the latest value from there using SQL query on the client side (other part of kafka) Thank you very much fro your help > What do you mean with "ineffective"? That there's more data in the WAL? > Sure. Otherwise I don't know what you could mean. > > There's no free lunch :/
Re: PostgreSQL logical decoder output plugin - unchanged toast data
Hi Andres. Thank you very much for your help. I tried the following solution and I have some problems. * I have 9.6 postgres and I do not have separate field for rd_pkindex * All I have is rd_replidindex field. Usually (when REPLICA IDENTITY is NOT FULL), it still contains the primary key * But in the case of REPLICA IDENTITY FULL - rd_replidindex is NULL and rd_pkindex does not exist So, is there a way to get the primary key on 9.6 postgres? > If you want the pkey, that's in rd_pkindex. rd_replidindex will only > differ if the identity is manually set to another candidate key > (possibly because there's no pkey). >
