Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^2: [OT] SQL "on duplicate key" custom action

by haukex (Archbishop)
on Jan 28, 2020 at 15:00 UTC ( [id://11111976]=note: print w/replies, xml ) Need Help??


in reply to Re: [OT] SQL "on duplicate key" custom action
in thread [OT] SQL "on duplicate key" custom action

Here's the solution I've ended up with, based on your suggestion:

CREATE FUNCTION skip_update() RETURNS trigger AS $$ BEGIN RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE FUNCTION fail_update() RETURNS trigger AS $$ BEGIN RAISE EXCEPTION 'can''t modify row values for existing primary + key'; END; $$ LANGUAGE plpgsql; CREATE TABLE Dummy_Test ( Timestamp TIMESTAMP WITH TIME ZONE PRIMARY KEY, Foo TEXT, Bar TEXT ); CREATE TRIGGER Dummy_Test_dupe BEFORE UPDATE ON Dummy_Test FOR EACH RO +W WHEN ( OLD.Timestamp IS NOT DISTINCT FROM NEW.Timestamp AND OLD.Fo +o IS NOT DISTINCT FROM NEW.Foo AND OLD.Bar IS NOT DISTINCT FROM NEW.B +ar ) EXECUTE FUNCTION skip_update(); CREATE TRIGGER Dummy_Test_modify BEFORE UPDATE ON Dummy_Test FOR EACH +ROW WHEN ( OLD.Foo IS DISTINCT FROM NEW.Foo OR OLD.Bar IS DISTINCT FRO +M NEW.Bar ) EXECUTE FUNCTION fail_update(); INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES ('2020-01-02 12:34', +'Hello','World') ON CONFLICT ON CONSTRAINT Dummy_Test_pkey DO UPDATE +SET Foo=EXCLUDED.Foo, Bar=EXCLUDED.Bar; INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES ('2020-01-02 12:34', +'Hello','World') ON CONFLICT ON CONSTRAINT Dummy_Test_pkey DO UPDATE +SET Foo=EXCLUDED.Foo, Bar=EXCLUDED.Bar; INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES ('2020-01-02 12:34', +'Hello','abcde') ON CONFLICT ON CONSTRAINT Dummy_Test_pkey DO UPDATE +SET Foo=EXCLUDED.Foo, Bar=EXCLUDED.Bar;

The last statement will fail, which is exactly what I wanted. The function FUNCTION skip_update() and TRIGGER Dummy_Test_dupe really only prevent the UPDATE from happening, they can be omitted - I haven't yet tested whether it's more performant to have the TRIGGER or the UPDATE fire. In any case, thank you very much!

Replies are listed 'Best First'.
Re^3: [OT] SQL "on duplicate key" custom action
by haukex (Archbishop) on Oct 10, 2021 at 09:47 UTC

    Just for the record, here's the same thing for SQLite. As above, the TRIGGER Dummy_Test_dupe is optional and I haven't tested the performance difference yet.

    use warnings; use strict; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", undef, undef, { RaiseError=>1, AutoCommit=>1 } ); $dbh->do(<<'ENDSQL'); CREATE TABLE Dummy_Test ( Timestamp INTEGER PRIMARY KEY, Foo TEXT, Bar TEXT ); ENDSQL $dbh->do(<<'ENDSQL'); CREATE TRIGGER Dummy_Test_dupe BEFORE UPDATE ON Dummy_Test FOR EACH RO +W WHEN ( OLD.Timestamp IS NEW.Timestamp AND OLD.Foo IS NEW.Foo AND O +LD.Bar IS NEW.Bar ) BEGIN SELECT RAISE(IGNORE); END; ENDSQL $dbh->do(<<'ENDSQL'); CREATE TRIGGER Dummy_Test_modify BEFORE UPDATE ON Dummy_Test FOR EACH +ROW WHEN ( OLD.Foo IS NOT NEW.Foo OR OLD.Bar IS NOT NEW.Bar ) BEGIN SELECT RAISE(ABORT, "same Timestamp but different values"); +END; ENDSQL my $in = $dbh->prepare(<<'ENDSQL'); INSERT INTO Dummy_Test (Timestamp,Foo,Bar) VALUES (?,?,?) ON CONFLICT (Timestamp) DO UPDATE SET Foo=EXCLUDED.Foo, Bar=EXCLUD +ED.Bar; ENDSQL $in->execute(12345,'Hello','World'); $in->execute(12345,'Hello','World'); my $e; eval { $in->execute(12345,'Hello','abcde'); 1 } or do { $e = $@ }; if ( defined $e ) { print "Third insert failed as expected: $e" } else { die "Third insert didn't fail as expected" }
Re^3: [OT] SQL "on duplicate key" custom action (updated)
by haukex (Archbishop) on Feb 24, 2020 at 17:16 UTC
    The function FUNCTION skip_update() and TRIGGER Dummy_Test_dupe really only prevent the UPDATE from happening, they can be omitted - I haven't yet tested whether it's more performant to have the TRIGGER or the UPDATE fire.

    Interestingly, it made a small but consistent difference. I imported the same dataset (73613 INSERTs) three times into a previously populated database, and without the TRIGGER, i.e. with the UPDATE, it took an average 91.540s, and with the TRIGGER, i.e. preventing the UPDATE, it took 99.368s (each of these is only +/- about 2s), i.e. a difference of roughly 0.1ms per record. Not a big deal here, and not a rigorous test, but still interesting.

    Update: Importing into a previously empty database revealed only a tiny difference: overall the import was roughly 10sec faster, again UPDATE beating TRIGGER with a difference of 0.17ms per record. Again, not really significant.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11111976]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others avoiding work at the Monastery: (4)
As of 2024-03-29 06:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found