โ10-01-2019 02:36 PM
I have a pipeline that reads from Oracle and writes to PGSQL. When inserting to PGSQL, I get this error
Batch entry 0 insert into โpg_tempโ.โcrmi002b_ut_pprs001โ (โend_dateโ, โopportunity_typeโ, โsales_personโ, โopportunity_status_typeโ, โsr_partition_idโ, โprogram_idโ, โpromotion_idโ, โcontact_idโ, โcreated_byโ, โlast_updated_byโ, โsr_checksumโ, โopportunity_idโ, โsr_modification_timeโ, โentity_codeโ, โcreated_onโ, โlast_updated_onโ, โsr_creation_timeโ, โstart_dateโ) values (cast(NULL as varchar), โLOV_OT_INTERACTIONโ, โC_NACARโ, โLOV_OS_INTERACTIONโ, โ22โ::numeric, cast(NULL as varchar), cast(NULL as varchar), โ151000000โ::numeric, โC_NACARโ, โC_NACARโ, โf1503e2c1ad94f56d9deef140da28eadโ, โ122000000โ::numeric, โ1569965343729โ::numeric, cast(NULL as varchar), cast(โ2010-12-11 10:51:24-06โ as timestamp), cast(โ2010-12-11 10:51:24-06โ as timestamp), โ1569965343729โ::numeric, cast(NULL as varchar)) was aborted: ERROR: column โend_dateโ is of type timestamp without time zone but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 364 Call getNextException to see other errors in the batch., error code: 0, SQL state: 42804
I am unsure why the snap is trying to cast NULLs as varchars for date and int fields. I assume it is because it does not see a good first example with data, so it translates properly as is done in the other fields that have data
Is there a way to bypass this casting, have this fixed? This is not the only pipeline that runs from Oracle to PGSQL, and not the first one that has null values in non-test fields. I find it hard to believe that in all other pipelines like this one the first record always have data for all fields
โ10-01-2019 02:53 PM
Unsure if this is the reason. We switched schemas from pg_temp to a regular schema, and it works fine. This is the first time we are trying to use pg_temp and it does not work with snap. Is snap trying to get the table definitions through a different connection? It would not see the table in the case if pg_temp
โ10-02-2019 09:08 AM
Itโsโฆ complicated. You should generally assume that different snaps use different connections. In rare situations it looks like a connection is passed from one snap to the next but there should be a reset between snaps. E.g., with postgresql we reset the default schemas.
Within an individual snap itโsโฆ complicated. Thereโs usually a single connection used during both validation and execution. However if the snap is at the end of a pipeline that has a long delay between launch and the first documents hitting the snap itโs possible that theyโll use a different connection. Itโs theoretically possible that a connection will be changed execution but I donโt think that happens in practice. We shouldnโt do anything during validation thatโs required during execution but something could have slipped through.
Could you write up two tickets? One with enough details about this pipeline for us to try to duplicate it, and a more general one asking for support for pg_temp? We can check a few things in the current snaps but full support for pg_temp (e.g., guaranteeing the same connection in all snaps) will require a lot of research into its feasibility.
โ10-04-2019 02:03 PM
@cesar.bolanos did you try defining TIMESTAMP without time zone
?
โ10-07-2019 09:14 AM
This also happens with int fields