DB Trigger in Postgres not executing

Support
#1

I have a strange requirement that I need to save records and group them by type and label them as version 1,2,3 in ascending contiguous order. I had solved this in CUBA with a trigger doing a select max query filtered by type on a before insert for a specific column and locking the table while i do this.

SELECT tbl_lock INTO varLockTable FROM my_locks WHERE tbl_lock ='target_table_to_lock' for update;

but this trigger is not fired when I insert a record on JMIX it does work if i manually add a record directly into the DB anyone has an Idea of why this happens? Strangest thing I raise a log inside of the trigger and I see it run but it does not save the calculated value if started from JMIX.

#2

When you try to insert from Jmix, and press OK on the edit screen, does the record shows in the database? This looks like as if there was a transaction or single DML started without a commit.
Did you use dataManager.save ? I think there may also be .commit or at least cuba has it.

#3

yes, using jmix the record gets stored in database but the column i want to update appears null. I use dataManager to save a context. I see the trigger fired in the console but value not stored.
if i use a pure sql statement and execute on db trigger works as expected.

#4

Can you please post the trigger definition?
(and the table too)

#5

Here is the trigger. for each row before insert it executes this function. I don’t think the table is necessary.

CREATE OR REPLACE FUNCTION "public"."generate_bp_version_number"()
  RETURNS "pg_catalog"."trigger" AS $BODY$

DECLARE
varNum INTEGER;
varLockTable VARCHAR;
BEGIN
	--LOCK DATABASE I DONT WANT TO USE A SEQUENCE I WANT A MAX +1
	SELECT tbl_lock INTO varLockTable FROM my_locks WHERE tbl_lock ='BP_DEF' for update;

	SELECT MAX(BP_DEF.bp_version) into varNum
	FROM
		BP_DEF 
	WHERE
		BP_DEF.code = NEW.code;

	IF(varNum IS NULL) THEN
		varNum = 0;
	END IF;
	
	NEW.bp_version = varNum + 1;
	--RAISE LOG 'varNum to Save: %', varNum;
	-- THE RAISE LOG APPEAR ON BOTH JMIX AND MANUAL SQL INSERT BUT JMIX LEAVES THE BP_VERSION NULL
	RAISE LOG 'Value to Save: %', NEW.bp_version;

	RETURN NEW;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
#6

Hello,
I tried to recreate your problem, but for me it seems to work.
I created your trigger function as “generate_bp_version_number2”

  • commented RAISE LOG and commented for update lock

…
– SELECT tbl_lock INTO varLockTable FROM my_locks WHERE tbl_lock =‘BP_DEF’ for update;
…
– RAISE LOG ‘Value to Save: %’, NEW.bp_version;

and a created simple table

– Table: public.bp_def

– DROP TABLE public.bp_def;

CREATE TABLE public.bp_def
(
id uuid NOT NULL,
code character varying(255) COLLATE pg_catalog.“default”,
bp_version integer,
CONSTRAINT pk_bp_def PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE public.bp_def
OWNER to eduardo;

– Trigger: BI_BP_DEF

– DROP TRIGGER “BI_BP_DEF” ON public.bp_def;

CREATE TRIGGER “BI_BP_DEF”
BEFORE INSERT
ON public.bp_def
FOR EACH ROW
EXECUTE PROCEDURE public.generate_bp_version_number2();

Then with Jmix I made default browse and edit screens, insterted a record and it worked. Trigger executes every time, correctly increments when needed for already existing code, and the value calculated by trigger is visible in Jmix table without any special annotation or refresh. If you like I can upload the project somewhere.
image

#7

Thanks for your help.
I later figured out that this value calculated from the trigger was getting overwritten. I need to use entity manager to update a second column and not use dataManager.save(entity) again.