View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005355SymmetricDSBugpublic2022-07-11 15:212022-10-03 14:38
Reporterpmarzullo Assigned Topmarzullo  
Prioritynormal 
Status assignedResolutionopen 
Product Version3.9.13 
Summary0005355: Sybase ASE Small Int not able to be converted to Postgres boolean without transformation or casting
DescriptionWe have a table in a Sybase database with a column defined as smallint. It either contains 0 or 1, always. On the target side, the data type was changed to boolean. There is no transformation setup as the source/target tables are identical (except for minor data type changes).

The message returned is:

2020-08-28 12:55:11,045 ERROR [SPIN_SYBASE-SPIN_SYB] [AcknowledgeService] [spin_sybase-spin_syb-push-MF_PG_CLI_Q-4] The outgoing batch MM_PG-679286 failed: ERROR: column "row_del_from_src_ind" is of type boolean but expression is of type smallint

  Hint: You will need to rewrite or cast the expression.

  Position: 242

That seems like a SDS-generated message, not from the Postgres server. Postgres is very lenient as to what value can be used to populate a boolean column. It allows, 0/1, Y/N, yes/no, … The complete list is in this page: https://www.postgresql.org/docs/11/datatype-boolean.html

Would it be possible for SDS to pass all values from the source as is and let the Postgres server generate the error if it is not valid?

This error is new in 3.12.2. Under 3.9.13, 0/1 was correctly handled (Y/N was not). So it was handled correctly for some values in 3.9.13.
Additional InformationThis is a database server error, because Postgres implements a strict datatype adherence.

I can think of two ways to deal with this.

1. Use Symmetric transforms.
2. Create a Postgres database server CREATE CAST object, that casts a smallint to a boolean. The type of CAST needs to be a ASSIGNMENT type, so that no CAST() call needs to be added to the SQL statement.
   This is also known as an assignment cast.
Here is a link that describes how to create one:
https://www.postgresql.org/docs/11/sql-createcast.html
Tagsdialect: postgresql, dialect: sybase

Activities

pmarzullo

2022-07-11 15:22

developer   ~0002091

In 3.9.13, a Sybase source column defined as a tinyint w/ values of 0/1 is loaded into a Postgres boolean column w/out transformations or cast. See trigger ID "pold1_pm_schedule.dbo.asset_repair_schedule" (The column name is row_del_from_src_ind).

Are you saying, in the new version, this behavior is no longer working?

In addition, if the source column is varchar with a value of "yes" for example, SDS generates an error. Such insert works fine in Postgres.
create table enterprise.cxv_test3 (
row_num int not null primary key,
col2 boolean not null,
col3 boolean null);

insert into enterprise.cxv_test3 ("row_num", "col2", "col3") values (4,'yes',null), (5,'No',null);
select * from enterprise.cxv_test3
row_num col2 col3
---------- ------- -------
4 true (null)
5 false (null)

SDS generates this error when the data is pushed from a source server:
Failed sql was: insert into "enterprise"."cxv_test3" ("row_num", "col2", "col3") values (?,?,?) on conflict do nothing
Failed row data was: "2","yes",
 StackTraceKey [RuntimeException:297740645]
2020-09-18 16:06:56,487 INFO [MM_POSTGRES-MM_PG] [DefaultDatabaseWriter] [mm_postgres-mm_pg-dataloader-1177] Failed to process insert event in batch SPIN_SYB-724426 on channel 'default'.
Failed row data was: "2","yes",
 StackTraceKey [RuntimeException:297740645]
2020-09-18 16:06:56,491 ERROR [MM_POSTGRES-MM_PG] [ManageIncomingBatchListener] [mm_postgres-mm_pg-dataloader-1177] Failed to load batch SPIN_SYB-724426 StackTraceKey [RuntimeException:297740645]
2020-09-18 16:06:56,494 ERROR [MM_POSTGRES-MM_PG] [DataLoaderService] [qtp1072377306-819023] Failed to process batch StackTraceKey [RuntimeException:297740645]
2020-09-18 16:06:56,517 ERROR [SPIN_SYBASE-SPIN_SYB] [AcknowledgeService] [spin_sybase-spin_syb-push-default-4] The outgoing batch MM_PG-724426 failed: For input string: "yes"

Shouldn't it be allowed w/out transformations or cast?
MM_POSTGRES-MM_PG-20200828162046.zip (926,465 bytes)

pmarzullo

2022-07-11 15:25

developer   ~0002093

CREATE TABLE enterprise.ata_related_activity
(
    repair_schedule_id integer NOT NULL,
    pm_interval_id integer NOT NULL,
    ata_ctgy_cd character(2) COLLATE pg_catalog."default" NOT NULL,
    ata_sub_ctgy_cd character(6) COLLATE pg_catalog."default" NOT NULL,
    repair_schedule_actvty_from_dt timestamp without time zone NOT NULL,
    ata_related_actvty_from_dt timestamp without time zone NOT NULL,
    dependent_ata_ctgy_cd character(2) COLLATE pg_catalog."default" NOT NULL,
    dependent_ata_sub_ctgy_cd character(6) COLLATE pg_catalog."default" NOT NULL,
    vma_repair_cd character(10) COLLATE pg_catalog."default" NOT NULL,
    ata_related_actvty_to_dt timestamp without time zone,
    spin_audit_insert_dt timestamp without time zone NOT NULL,
    spin_audit_update_dt timestamp without time zone,
    src_extract_dt timestamp without time zone NOT NULL,
    row_del_from_src_ind boolean NOT NULL,
    CONSTRAINT ata_related_activity_pk PRIMARY KEY (repair_schedule_id, pm_interval_id, ata_ctgy_cd, ata_sub_ctgy_cd, repair_schedule_actvty_from_dt, ata_related_actvty_from_dt, row_del_from_src_ind)
        USING INDEX TABLESPACE mm_data_tbs12
)
WITH (
    OIDS = FALSE
)
TABLESPACE mm_data_tbs21;

ALTER TABLE enterprise.ata_related_activity
    OWNER to role_replication_admin;

GRANT ALL ON TABLE enterprise.ata_related_activity TO role_da;

GRANT SELECT ON TABLE enterprise.ata_related_activity TO role_managed_maint_dml;

GRANT SELECT ON TABLE enterprise.ata_related_activity TO role_managed_maint_ro;

GRANT ALL ON TABLE enterprise.ata_related_activity TO role_managed_maint_test;

GRANT ALL ON TABLE enterprise.ata_related_activity TO role_replication_admin;

GRANT SELECT ON TABLE enterprise.ata_related_activity TO role_sym_trg;

COMMENT ON TABLE enterprise.ata_related_activity
    IS 'SPIN schema representation';

pmarzullo

2022-07-11 15:28

developer   ~0002094

pmarzullo

2022-07-11 15:30

developer   ~0002095

CREATE TABLE enterprise.ata_related_activity
(
    repair_schedule_id integer NOT NULL,
    pm_interval_id integer NOT NULL,
    ata_ctgy_cd character(2) COLLATE pg_catalog."default" NOT NULL,
    ata_sub_ctgy_cd character(6) COLLATE pg_catalog."default" NOT NULL,
    repair_schedule_actvty_from_dt timestamp without time zone NOT NULL,
    ata_related_actvty_from_dt timestamp without time zone NOT NULL,
    dependent_ata_ctgy_cd character(2) COLLATE pg_catalog."default" NOT NULL,
    dependent_ata_sub_ctgy_cd character(6) COLLATE pg_catalog."default" NOT NULL,
    vma_repair_cd character(10) COLLATE pg_catalog."default" NOT NULL,
    ata_related_actvty_to_dt timestamp without time zone,
    spin_audit_insert_dt timestamp without time zone NOT NULL,
    spin_audit_update_dt timestamp without time zone,
    src_extract_dt timestamp without time zone NOT NULL,
    row_del_from_src_ind boolean NOT NULL,
    CONSTRAINT ata_related_activity_pk PRIMARY KEY (repair_schedule_id, pm_interval_id, ata_ctgy_cd, ata_sub_ctgy_cd, repair_schedule_actvty_from_dt, ata_related_actvty_from_dt, row_del_from_src_ind)
        USING INDEX TABLESPACE mm_data_tbs12
)
WITH (
    OIDS = FALSE
)
TABLESPACE mm_data_tbs21;

ALTER TABLE enterprise.ata_related_activity
    OWNER to role_replication_admin;

GRANT ALL ON TABLE enterprise.ata_related_activity TO role_da;

GRANT SELECT ON TABLE enterprise.ata_related_activity TO role_managed_maint_dml;

GRANT SELECT ON TABLE enterprise.ata_related_activity TO role_managed_maint_ro;

GRANT ALL ON TABLE enterprise.ata_related_activity TO role_managed_maint_test;

GRANT ALL ON TABLE enterprise.ata_related_activity TO role_replication_admin;

GRANT SELECT ON TABLE enterprise.ata_related_activity TO role_sym_trg;

COMMENT ON TABLE enterprise.ata_related_activity
    IS 'SPIN schema representation';

pmarzullo

2022-07-11 15:37

developer   ~0002096

pmarzullo

2022-07-11 15:53

developer   ~0002097

SPIN_SYBASE-SPIN_SYB-20200828162026.zip (1,319,951 bytes)

Issue History

Date Modified Username Field Change
2022-07-11 15:21 pmarzullo New Issue
2022-07-11 15:21 pmarzullo Status new => assigned
2022-07-11 15:21 pmarzullo Assigned To => pmarzullo
2022-07-11 15:21 pmarzullo Tag Attached: dialect: postgresql
2022-07-11 15:21 pmarzullo Tag Attached: dialect: sybase
2022-07-11 15:22 pmarzullo Note Added: 0002091
2022-07-11 15:22 pmarzullo File Added: MM_POSTGRES-MM_PG-20200828162046.zip
2022-07-11 15:25 pmarzullo Note Added: 0002093
2022-07-11 15:28 pmarzullo Note Added: 0002094
2022-07-11 15:30 pmarzullo Note Added: 0002095
2022-07-11 15:37 pmarzullo Note Added: 0002096
2022-07-11 15:53 pmarzullo Note Added: 0002097
2022-07-11 15:53 pmarzullo File Added: SPIN_SYBASE-SPIN_SYB-20200828162026.zip
2022-07-25 16:52 elong Target Version 3.14.0 => 3.14.1
2022-08-24 18:56 elong Target Version 3.14.1 => 3.14.2
2022-10-03 14:38 elong Target Version 3.14.2 =>