On 2/2/20 1:24 AM, Condor wrote:
Hello,
I'm using PostgreSQL 12.1 and trying to avoid update on table when data
is the same. I read somewhere if UPDATE is with the same data SQL server
on system level does not do update on table but don't know if that is
true or not. If that is not true I do:
First I create a function that should update data:
CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE status_table SET status0 = NEW.status0, lastchage =
CURRENT_TIMESTAMP WHERE rowid = OLD.rowid;
RETURN NEW;
END
$$;
then create table:
CREATE TABLE status_table (
rowid INTEGER,
status0 INTEGER,
lastchage TIMESTAMP(0) WITHOUT TIME ZONE
);
attach trigger:
Why the DROP TRIGGER on card_sync_tbl?
More below.
DROP TRIGGER last_changes ON card_sync_tbl;
CREATE TRIGGER last_changes
BEFORE UPDATE ON status_table
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_last_chaged();
insert first data:
INSERT INTO status_table (rowid, status0) VALUES (11, 1);
INSERT INTO status_table (rowid, status0) VALUES (12, 2);
and check do everything work fine:
UPDATE status_table SET status0 = 1 WHERE rowid = 11;
UPDATE status_table SET status0 = 4 WHERE rowid = 12;
I receive something on rowid 12 that probably is error:
SQL statement "UPDATE status_table SET status0 = NEW.status0, lastchage
= CURRENT_TIMESTAMP WHERE rowid = OLD.rowid"
PL/pgSQL function log_last_chaged() line 3 at SQL statement
After quick look on duckduckgo I change the function to this:
CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.lastchage := CURRENT_TIMESTAMP;
RETURN NEW;
END
$$;
and everything seems work now, but that break the idea update not to hit
table if data is the same.
Some changes based on:
https://www.postgresql.org/docs/12/sql-createtrigger.html
"In a BEFORE trigger, the WHEN condition is evaluated just before the
function is or would be executed, so using WHEN is not materially
different from testing the same condition at the beginning of the
trigger function. Note in particular that the NEW row seen by the
condition is the current value, as possibly modified by earlier triggers. "
https://www.postgresql.org/docs/12/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER
"Row-level triggers fired BEFORE can return null to signal the trigger
manager to skip the rest of the operation for this row (i.e., subsequent
triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for
this row)."
CREATE OR REPLACE FUNCTION public.log_last_chaged()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF OLD.* IS DISTINCT FROM NEW.* THEN
RAISE NOTICE 'UPDATE';
NEW.lastchage := CURRENT_TIMESTAMP;
RETURN NEW;
ELSE
RETURN NULL;
END IF;
END
$function$
CREATE TRIGGER last_changes
BEFORE UPDATE ON status_table
FOR EACH ROW
EXECUTE FUNCTION log_last_chaged();
test=> INSERT INTO status_table (rowid, status0) VALUES (11, 1);
INSERT 0 1
test=> INSERT INTO status_table (rowid, status0) VALUES (12, 2);
INSERT 0 1
test=> select ctid, * from status_table ;
ctid | rowid | status0 | lastchage
-------+-------+---------+-----------
(0,1) | 11 | 1 |
(0,2) | 12 | 2 |
(2 rows)
test=> UPDATE status_table SET status0 = 1 WHERE rowid = 11;
UPDATE 0
test=> select ctid, * from status_table ;
ctid | rowid | status0 | lastchage
-------+-------+---------+-----------
(0,1) | 11 | 1 |
(0,2) | 12 | 2 |
(2 rows)
NOTE: UPDATE 0 and no change in ctid
test=> UPDATE status_table SET status0 = 4 WHERE rowid = 12;
NOTICE: UPDATE
UPDATE 1
test=> select ctid, * from status_table ;
ctid | rowid | status0 | lastchage
-------+-------+---------+---------------------
(0,1) | 11 | 1 |
(0,3) | 12 | 4 | 02/02/2020 13:03:21
(2 rows)
NOTE: UPDATE 1 and ctid change.
Any body can help with some hint ? Also I want to know why my first
function does not work, probably loop is happened if trigger does not
stop update to be sent to table on rowid 12 or syntax error.
Regards,
HS
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
