Handling triggers in Cockroach DB

I am having some triggers in my postgres DB. At a high level my trigger does the following -

  1. Insert a value in a table config_str
  2. If the insert in #1 in successful then make an entry in config_version table.

Since cockroachDB does not have trigger at this moment. I tried to handle this via transaction. But my basic perf tests showing a big difference in performance for single postgres node with trigger and single node cockroach with transaction (about magnitude of 9-10x).

Can someone let me know what is the recommended way of handling this use case in cockroach DB?

Postgres Schema

create TABLE config_str (CustomerId int NOT NULL,
                         ObjectType int NOT NULL,
                         ObjectId bigint NOT NULL,
                         PropertyName text NOT NULL,
                         TimeInstant bigint NOT NULL,
                         UpdateTimestamp timestamp with time zone DEFAULT current_timestamp,
                         Value text,
                         PRIMARY KEY (CustomerId, ObjectType, ObjectId, PropertyName, TimeInstant));

create TRIGGER record_object_property_version AFTER INSERT OR UPDATE ON config_str
    FOR EACH ROW EXECUTE PROCEDURE record_config_version();

create table config_versions (CustomerId int NOT NULL,
                              ObjectType int NOT NULL,
                              ObjectId bigint NOT NULL,
                              PropertyName text NOT NULL,
                              TimeInstant bigint NOT NULL,
                              UpdateTimestamp timestamp with time zone DEFAULT current_timestamp,
                              PRIMARY KEY (CustomerId, ObjectType, ObjectId, PropertyName, TimeInstant, UpdateTimestamp));
create INDEX config_versions_by_version on config_versions(UpdateTimestamp);

CREATE FUNCTION record_config_version() RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO config_versions
            VALUES
            (NEW.customerId, NEW.objecttype, NEW.objectid, NEW.propertyname, NEW.timeinstant, NEW.updateTimestamp);
        RETURN NEW;
    END;
    $$ language plpgsql;

Cockroach Schema

CREATE TABLE config_versions (
    customerid integer NOT NULL,
    objecttype integer NOT NULL,
    objectid bigint NOT NULL,
    propertyname text NOT NULL,
    timeinstant bigint NOT NULL,
    updatetimestamp timestamp with time zone DEFAULT now() NOT NULL,
    PRIMARY KEY (customerid, objecttype, objectid, propertyname, timeinstant, updatetimestamp),
    INDEX config_versions_by_version (updatetimestamp)
);

CREATE TABLE config_str (
    customerid integer NOT NULL,
    objecttype integer NOT NULL,
    objectid bigint NOT NULL,
    propertyname text NOT NULL,
    timeinstant bigint NOT NULL,
    updatetimestamp timestamp with time zone DEFAULT now(),
    value text,
    PRIMARY KEY (customerid, objecttype, objectid, propertyname, timeinstant)
);

BEGIN
INSERT INTO config_str (customerId, objecttype, objectid, propertyname, timeinstant, value) VALUES (1, 2, '3'::int8, 'body', '2121'::int8, 'head');
INSERT INTO config_versions (customerId, objecttype, objectid, propertyname, timeinstant)  VALUES (1, 2, '3'::int8, 'body', '2121'::int8)"
COMMIT

Hi tuk!

thank you for your question. You can check that an insert is successful and perform another INSERT by using the RETURNING syntax and the [...] cockroachDB extension:

INSERT INTO config_version (...) SELECT ... FROM [INSERT/UPSERT INTO config_str(...) ... RETURNING ...]

Would this help? I could perhaps make the example more precise if you would tell us the definition of your record_config_version() procedure.

@knz Thanks for replying. It is there in my first question you can scroll the code. Anyways posting that again

CREATE FUNCTION record_config_version() RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO config_versions
            VALUES
            (NEW.customerId, NEW.objecttype, NEW.objectid, NEW.propertyname, NEW.timeinstant, NEW.updateTimestamp);
        RETURN NEW;
    END;
    $$ language plpgsql;

It will help me if you can make it little more precise.

Yes then it’s as I explained earlier:

  • for fresh inserts into config_str:
INSERT INTO config_versions(CustomerId, ObjectType, ObjectId, PropertyName, TimeInstant, UpdateTimestamp)
   SELECT *
     FROM [INSERT INTO config_str(...) ...
           RETURNING CustomerId, ObjectType, ObjectId, PropertyName, TimeInstant, UpdateTimestamp]
  • for updates of config_str:
INSERT INTO config_versions(CustomerId, ObjectType, ObjectId, PropertyName, TimeInstant, UpdateTimestamp)
   SELECT *
     FROM [UPDATE config_str SET ... WHERE ...
           RETURNING CustomerId, ObjectType, ObjectId, PropertyName, TimeInstant, UpdateTimestamp]

This way you propagate every insert/update to config_str as a new record into config_versions. There is no BEGIN/COMMIT needed and yet the update is transactional automatically.

Did I understand your need well?

@knz - I tried the below query you suggested

INSERT INTO config_versions (customerId, objecttype, objectid, propertyname, timeinstant) 
	SELECT * 
		FROM	[INSERT INTO config_str (customerId, objecttype, objectid, propertyname, timeinstant, value) VALUES (1, 1, '5787634245046922574'::int8, 'prop_2', '10'::int8, '1') 		
				RETURNING customerId, objecttype, objectid, propertyname, timeinstant];  

But it is failing with the below error -

pq: internal/client/txn.go:887: attempting to use transaction with wrong status or finalized: COMMITTED true

This is resulting in a row being inserted in config_str but no row in config_versions.

Hi @tuk. The “attempting to use transaction with wrong status or finalized:
COMMITTED true” error is returned when a transaction that has already been
committed is being reused, which is invalid in SQL. Try running this
statement in a new transaction.

@dan - I did not get you. I am not running anything in transaction. Just ran the above command by logging into cockroach cli

INSERT INTO config_versions (customerId, objecttype, objectid, propertyname, timeinstant) 
	SELECT * 
		FROM	[INSERT INTO config_str (customerId, objecttype, objectid, propertyname, timeinstant, value) VALUES (1, 1, '5787634245046922574'::int8, 'prop_2', '10'::int8, '1') 		
				RETURNING customerId, objecttype, objectid, propertyname, timeinstant];  

You can also reproduce this on your end. Also the worst part is even though the above query is failing it is doing the insertion in config_str. If the transaction is failing it should fail as a whole.

I think this is some bug on cockroach side.

Hi

Is this some issue in the cockroach side or I am doing something wrong?

@tuk please accept my apologies, I brought you against this known issue: https://github.com/cockroachdb/cockroach/issues/18806

The workaround to make it work is to enclose the whole between BEGINCOMMIT. I understand this is what you wanted to get away from initially, but I believe doing so will still be better (faster) than two separate statements. Can you try this?