IssueTracker

Hi,

Can you provide more information about:

1. Steps to reproduce the issue.
2. If possible can you provide a sample data for reproduction. Please remove PII if there are any.

If possible can you also provide a screenshot of the error?

Thanks

Hi,

Just to confirm, are you following this guide to perform copying of data from CloudSQL Postgres to BigQuery?

The log you have posted pertains to datastream_txns_public.adjustment_adjustmentmodel and your DDL points to public.adjustment_adjustmentmodel. There seems to have a mismatch of the logs and DDL. Is this expected?

Thanks

Hi @mark.doutre,

I tried replicating your issue by following this guide but I did not encounter your issue.

Prior to this I created a table in my CloudSQL Postgres database using the DDL you have provided. See schema [1].

I created mock data using the query below:

INSERT INTO public.adjustment_adjustmentmodel (created_date,modified_date,id,adjustment_type,adjusted_item_id,line_item_id,transaction_reference_id,promotion_id) values ('2022-09-15 19:00+11:00','2022-09-16 19:00+11:00',gen_random_uuid (),1,gen_random_uuid (),gen_random_uuid (),gen_random_uuid (),gen_random_uuid ());

Postgres Data is inserted successfully as seen in [2]. I proceeded with creating the profile for both Postgres and BigQuery as seen in the guide I followed. See created stream [3] and data streamed in BigQuery [4].

Let me know if I missed anything on my reproduction steps so I can retry my replication based on the steps you have taken.

[1] adjustment_schema.png

[2] postgres_query_output.png

[3] created_stream.png

[4] bq_streamed_data.png

Hi ri..., To replicate the issue you need to add indexes that also reference the primary key column. For example, creating this table and trying to make a stream with this table will fail with the BIGQUERY_TOO_MANY_PRIMARY_KEYS error, even though it clearly only has 1 primary key with a single column id.

CREATE TABLE too_many_keys_failure (
    id int,
    created_date timestamp,
    last_modified_date timestamp,
    user_id int,
    facility_id int,
    manager_id int,
    is_available bool,
    CONSTRAINT id_pk PRIMARY KEY (id) --NOTE THAT THIS IS THE ONLY PRIMARY KEY!
);
--NOTE THE NON-PRIMARY KEY INDEXES
CREATE INDEX ON too_many_keys_failure (id, user_id, facility_id, manager_id, is_available, last_modified_date);
CREATE INDEX ON too_many_keys_failure (id, user_id, facility_id, manager_id, is_available);
CREATE INDEX ON too_many_keys_failure (id, user_id, facility_id, manager_id, last_modified_date);
CREATE INDEX ON too_many_keys_failure (id, user_id, facility_id, is_available, last_modified_date);
CREATE INDEX ON too_many_keys_failure (id, user_id, facility_id);
CREATE INDEX ON too_many_keys_failure (id, facility_id);

INSERT INTO too_many_keys_failure
VALUES
(1,current_timestamp, current_timestamp, '1','1','2',false),
(2,current_timestamp, current_timestamp, '2','1',null,false),
(3,current_timestamp, current_timestamp, '3','1',null,false);

This appears to be due to how Datastream attempts to detect primary keys in its query to PostgreSQL. I believe there is a bug in the query that was written/generated where it checks pg_index.indisprimary IS NULL instead of pg_index.indisprimary = 't'

The screenshot attached shows the failure when creating a new stream for this the table public_too_many_keys_failure. Note that the prefix of the schema public is because I chose the "single dataset for all schemas option when setting up the stream.

I've also in the stream added a second table called public_too_this_one_works that is identical to this table except without the non-primary key indexes. This one is shown in the screenshot and we can see that it wrote the 3 records.

Hi @leon.verhelst,

Thank you for providing additional details. I will provide an update on my replication and findings.

Hi,

I was able to replicate the issue. I reached out to the product team and created an internal issue about this. Please keep in mind that this issue has to be analyzed and considered by the product team and I can't provide you an ETA for it. However, you can keep track of the status by following this thread.

Hi,

I would appreciate it if you can provide your insight on this. Assuming a unique key is a must for clustering to properly load data into BigQuery, would you expect data stream to randomly choose a unique index if one such index exists and use it as the primary key in BigQuery, or look for primary keys only, and fail if none exists?

Thanks

I would expect BigQuery to respect the REPLICA IDENTITY from the source tables and act similar to PostgreSQL's rules for setting up a publication: From: https://www.postgresql.org/docs/current/logical-replication-publication.html

A published table must have a “replica identity” configured in order to be able to replicate UPDATE and DELETE operations, so that appropriate rows to update or delete can be identified on the subscriber side. By default, this is the primary key, if there is one. Another unique index (with certain additional requirements) can also be set to be the replica identity. If the table does not have any suitable key, then it can be set to replica identity “full”, which means the entire row becomes the key. This, however, is very inefficient and should only be used as a fallback if no other solution is possible. If a replica identity other than “full” is set on the publisher side, a replica identity comprising the same or fewer columns must also be set on the subscriber side. See REPLICA IDENTITY for details on how to set the replica identity. If a table without a replica identity is added to a publication that replicates UPDATE or DELETE operations then subsequent UPDATE or DELETE operations will cause an error on the publisher. INSERT operations can proceed regardless of any replica identity.

A Postgres -> BigQuery replication should use the REPLICA IDENTITY that is set on the source table, which normally is set like so:

• Use the PK if exists
• Otherwise use a specified unique index as per the table definition
• Otherwise use the full row

For information on how to set the replica identity see: https://www.postgresql.org/docs/current/sql-altertable.html#SQL-CREATETABLE-REPLICA-IDENTITY

Finding the replica identity for a table is done as described here: https://stackoverflow.com/questions/55249431/find-replica-identity-for-a-postgres-table

A fix for this bug is currently being rolled-out, and should be applied to all Google Cloud regions by the end of the week (Oct. 29).

Is this issue the same solution the following error message?

BIGQUERY_UNSUPPORTED_PRIMARY_KEY_CHANGE

Apparently there's been some regression to this issue... an updated fix is pending, and will be rolled out ASAP.
I'll update here again once the fix is in production.

For Postgres/BQ pairing, what are the steps needed to confirm this fix works? Will a running stream with a broken source table self-correct with the new code? A standard cleanup procedure would be very helpful.

1. Does the table in question need to be removed (unchecked, saved) and added again in the source configuration?
2. Does the stream need to be stopped (paused) and restarted? Deleted and recreated to pickup the new code?
3. Does the destination table need to be deleted in BQ?

The fix has been rolled out.\

To recover from this error:

1. If a table was already created in BigQuery it should be manually deleted
2. Trigger a backfill for the table in Datastream

@johan.eliasson - does the table actually have more than 4 PKs? If not, can you share the CREATE TABLE statement (you can email it to me directly instead of posting it here)?

If the table has more than 4 PK columns, then this error is currently the expected behavior, but there's a change coming to BQ which will allow more than 4 columns in the PK. I'm not able to share exact timelines for this change, but it's WIP (perhaps @nickorlove can provide more details).

This is a public thread, so I'll refrain from providing exact timelines, however please note the limit of 4 PKs is a known issue we are working hard to address.

I'll update this thread once more concrete details can be shared with the broader community.

I encoutered this issue as well, my DDL is:

CREATE TABLE public.spoon_mst ( spoon_code varchar(32) NOT NULL, qr_code varchar(32) NULL, scan_date timestamp(6) NULL, product_name varchar(128) NULL, weight varchar(16) NULL, mfg_date timestamp(6) NULL, exp_date timestamp(6) NULL, is_active bool NOT NULL DEFAULT true, status varchar(255) NULL DEFAULT 'UNUSED'::character varying, code_length int4 NULL, created_date timestamp NULL DEFAULT now(), description varchar(255) NULL, is_check bool NULL DEFAULT false, updated_date timestamp NULL, ext_id varchar(255) NULL, qr_manufacture_date timestamp NULL, is_synced bool NULL DEFAULT true, "version" int4 NOT NULL DEFAULT 0, CONSTRAINT spoon_mst_pkey PRIMARY KEY (spoon_code) );

My primary key was a random string generated from an algorithm. In case I try to sync other tables with primary key in number format (id), it works well. Is using primary key in string format causes this issue?

It looks like the issue with your DDL is around syntax and that the primary key does not match the table's clustering key.An example DDL to create a table to be used with Datastream would be like this:

CREATE TABLE customers ( ID INT64 PRIMARY KEY NOT ENFORCED, NAME STRING, SALARY INT64) CLUSTER BY ID;

FYI my earlier comment of a suggested DDL was in the frame of mind of running a DDL within BigQuery to create a BQ table which would be used as the destination for Datastream replication.

If your question was more about the syntax of running a DDL from the source database, please ignore #comment45.

Thank you so much, it worked like a charm after I created Big Query table manually with clustering key then starting a new stream.

This is now fixed. BigQuery have increased the limitation to 16 PK columns, and Datastream now aligns to this new limitation.
BigQuery still doesn't support more than four clustering columns, so when replicating a table with more than four primary key columns, Datastream uses four primary key columns as the clustering columns.