Background:
When working with PostgreSQL it is possible to use PostgreSQL TOAST mechanism for storing big columns data (LOB), FYI - more info about: http://www.ludovicocaldara.net/dba/pgsql-lo-space-usage-part-1/).
When using TOAST for storing big columns data, the LOB data itself is not stored in the Postgre base table but rather it has an oid that refers to an internal table (pg_largeobject), where the actual data is TEXT data type and the function lo_get() is used to retrieve the raw data.
When using PostgreSQL source enpoint, if tables for replication include TEXT data type that uses PostgreSQL TOAST, by default, Replicate will replicate to the target table the oid rather than the atual LOB data.
To replicate the LOB data itself (rather than the pointer to the data) to the target endpoint , a source lookup transformation rule should be defined for the specific column. Below is an example for the source lookup transformation rule that would retrieve the text data to the target column based on the PK of the specific row:
source_lookup(10000,'TableSchema','TableName',"encode(lo_get(cast(TextColUsingTOAST as bigint)),'escape')",'PKcolName=?',$PKcolName)
and.. to retrieve the hex representation data to the target column based on the PK of the specific row the source lookup would look slightly different like the following example:
source_lookup(10000,'TableSchema','TableName',"cast(lo_get(cast(TextColUsingTOAST as bigint)),text)",'PKcolName=?',$PKcolName)
Environment
- Replicate using PostgreSQL source endpoint replicating table with column type: TEXT data type using PostgreSQL TOAST mechanism
