Can postgres insert triggers and/or check be ran without inserting

Question

I would love to be able to validate objects representing table rows using the database's existing constraints (triggers that raise exceptions and checks) without actually inserting them into the database.

Is there currently a way one could do this in postgres? At least with BEFORE INSERT triggers and CHECK, I assume it makes no sense with AFTER INSERT triggers.

The easiest way I can think or right now would be to:

Lock the table
Insert a new row
If exception raise to the API / else DELETE the row and call it valid
Unlock

But I can see several issues with this.

Insert in a transaction and then rollback. However, if you have a need to check data without actually inserting data I'd question whether these should be triggers; perhaps they should be validations on the model. — Schwern
– Schwern, Commented Apr 25, 2022 at 0:32
Consider upgrading to a current version urgently. Postgres 9.4 has reached EOL in 2020 and is hopelessly outdated. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 25, 2022 at 0:38
The transaction method is the best way to do this. For future reference though, if you are using plpgsql triggers and BEFORE INSERT you can RETURN NULL to prevent the INSERT from happening after you did a check on the incoming data. — Adrian Klaver
– Adrian Klaver, Commented Apr 25, 2022 at 15:57

Erwin Brandstetter · Accepted Answer · 2022-04-27 15:18:23Z

4

+200

A simpler way is to insert within a transaction and not commit:

BEGIN;
INSERT INTO tbl(...) VALUES (...);
-- see effects ...
ROLLBACK;

No need for additional locking. The row is never visible to any other transaction with default transacton isolation level READ COMMITTED. (You might be stalling concurrent writes that confict with the tested row.)

Notable side-effect: Sequences of serial or IDENTITY columns are advanced even if the INSERT is never committed. But gaps in sequential numbers are to be expected anyway and nothing to worry about.

Be wary of triggers with side-effects. All "transactional" SQL effects are rolled back, even most DDL commands. But some special operations (like advancing sequences) are never rolled back.

Also, DEFERRED constraints do not kick in. The manual:

DEFERRED constraints are not checked until transaction commit.

If you need this a lot, work with a copy of your table, or even your database.

Strictly speaking, while any trigger / constraint / concurrent event is allowed, there is no other way to "validate objects" than to insert them into the actual target table in the actual target database at the actual point in time. Triggers, constraints, even default values, can interact with the current state of the whole DB. The more possibilities are ruled out and requirements are reduced, the more options we might have to emulate the test.

edited Apr 27, 2022 at 15:18

answered Apr 25, 2022 at 0:35

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

George Over a year ago

Oh yeah, in hindsight this is a much better approach given that the transaction would still leave the rest of the table readable until it ends but not the newly inserted row.

George Over a year ago

Though I guess it might still be more heavy-weight than the preferred solution that never writes if one exists(?) for the aforementioned reason of triggers with side effects, though not an issue in my case per-se.

Erwin Brandstetter Over a year ago

@George: the preferred solution that never writes if one exists(?) If what exists?

George Over a year ago

if such a solution (i.e. one that doesn't write to the database) exists. Odd phrasing, sorry

Adrian Klaver · Accepted Answer · 2022-04-30 00:34:33Z

CREATE FUNCTION validate_function ( )
 RETURNS trigger LANGUAGE plpgsql
 
AS $function$
DECLARE
    valid_flag boolean := 't';
BEGIN
    --Validation code
    if valid_flag = 'f' then
        RAISE EXCEPTION 'This record is not valid id  %', id
        USING HINT = 'Please enter valid record';    
        RETURN NULL;
    else
        RETURN NEW;
    end if;
END;
$function$

CREATE TRIGGER validate_rec BEFORE INSERT OR UPDATE ON some_tbl
    FOR EACH ROW EXECUTE FUNCTION validate_function();

With this function and trigger you validate inside the trigger. If the new record fails validation you set the valid_flag to false and then use that to raise exception. The RETURN NULL; is probably redundant and I am not sure it will be reached, but if it is it will also abort the insert or update. If the record is valid then you RETURN NEW and the insert/update completes.

Collectives™ on Stack Overflow

Can postgres insert triggers and/or check be ran without inserting

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related