Hi, I was wondering if there is a cach or anything. After I clear the table using "Delete from" and reinsert the same set of rows, I often get the error on totally unique record. I was wondering if there was any cache or anything.
CREATE MULTISET TABLE SANDBOX.MERCHANT_SETTLEMENT ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
settlement_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
booking_num INTEGER NOT NULL,
settlement_source INTEGER NOT NULL,
CONSTRAINT MRCHSET_SOURCE_ENUM CHECK ( settlement_source = 1 ),
CONSTRAINT MERCH_SETTLEMENT_PK PRIMARY KEY ( settlement_id ))
PRIMARY INDEX ( booking_num );
No there is no cache .... have you checked if there are actually full-row duplicates coming from source?
No, there is no duplicates. Actually, this happened on different records and same time they all succeeded without any issue. The latter usually happens after waiting for a few hours. BTW, I am using the vm 14 on VMWare running on my mac. The settlement_id jumps around like the following:
AFAIK, The identity value is cached in PE, and also saved in some table, So it will start from 1 only when you recreate the the table, other wise the number might be repeated. you have defined PK on Settlement_id, PK is implemented as USI, so it will check for dups. I think you should add drop and create to you script in order to avoid this error.
Jumps around .... ? Can you share complete DDLs and the SQL you are using?
Thanks Khurram, seems you are right. My test never fails if the table is dropped and recreated. I am really surprised that Teradata has this kind of behavior.
This jump around is normal, AFAIk TD generates id with gaps. So dont worry about that, but as long as the table is being deleted and reinserted the issue can occure.
with your ddl for "identity" usage, you will always get totally unique records(as long as [min, max] range), regardless of that you delete or not or keep inserting your same data, because "identity" will always generate you a unique value within the range.
This is due to the fact that sequence number is generated on AMP-local basis.
It is not called a sequence for a reason, but it will surely not repeat the values.
A sequence GENERATED ALWAYS AS IDENTITY with NO CYCLE should never return a duplicate value (based on that you don't need to define a PK on settlement_id).
What's your client and your TD release?