CloudQuery config changes causing duplicate key error with deterministic_cq_id and write_mode

CloudQuery Plugins
1

I recently added deterministic_cq_id: true to my config and write_mode: overwrite and am now seeing errors:

"err": "ERROR: duplicate key value violates unique constraint \"aws_lambda_runtimes__cq_id_key\" (SQLSTATE 23505)"

Is there a way to make the change transition smoothly, or what am I misunderstanding about this error?

2

So typically when users use deterministic_cq_id: true, they also use pk_mode: cq-id-only. This enables the database to only have to care about a single PK during insert.

CloudQuery Documentation: pk_mode

What database and plugin are you using?

3

Does adding that flag affect the PK of the table, or does it simply change the CQID generation method?

Postgres DB
And AWS source

4

It impacts the PKS on the table… Rather than having a composite PK, it uses a single column _cq_id.

Are you getting that error on every single sync?

5

Yes, I am.

Well, so I’m talking specifically about the deterministic cqid and omitting the cq-id-only flag.

6

Ok. Can you open an issue on GitHub with the details of your config and we will look into it!

Open an Issue on GitHub

Yes, the deterministic_cqid flag will only change how the _cq_id is calculated; otherwise, it will be a random value.

7

So it shouldn’t impact the primary key like it appears to be.

Actually, I think that I understand the issue. Some deterministic UUID must be colliding with a previously random UUID?

8

Is there an overwrite flag for such a situation?

9

If I have overwrite delete stale on the Postgres spec, will those entries get deleted and then recreated?