Strange behavior of the option ignore

[Napolskih]

Good day

I'm interested in is described in the documentation (http://skytools.projects.postgresql.org/skytools-3.0/pgq/files/triggers-sql.html) option IGNORE.

This option allows you to update the expressions do not look at these columns. That is, If the query involves only columns ignored - the event is not created.

The problem is that the event is ignored updates only if the column value is changed (old is not equal to new)
That, in my opinion is not logical, and all the profit from the opportunities lost.
A feeling that the code just a mistake!
Ideally, it should ignore the update expression in that case if it involves only the columns from the list, ignore list, and all, in spite of everything else.

I would like that the event would be ignored and if the column is not updated from the ignore list, but the value does not change! (This is a dream, it is not declared and not implemented)

Test from the developers did not reveal the problem. Because there are updates on new meaning. And if it's an update statement run a second time, the event will be generated.
https://github.com/markokr/skytools/blob/master/sql/pgq/sql/logutriga.sql

create table udata (
id serial primary key,
txt text,
bin bytea
);

create trigger utest after insert or update or delete on udata
for each row execute procedure pgq.logutriga ('udata_que', 'backup', 'ignore = bin');

insert into udata values ​​(1, 'txt', 'bin');
NOTICE: insert_event (udata_que, I: id, id = 1 & txt = txt, public.udata)
update udata set txt = 'txt';
NOTICE: insert_event (udata_que, U: id, id = 1 & txt = txt, public.udata)
update udata set txt = 'txt2', bin = 'bin2';
NOTICE: insert_event (udata_que, U: id, id = 1 & txt = txt2, public.udata)
update udata set bin = 'bin2'
no events!
update udata set bin = 'bin2'
NOTICE: insert_event (udata_que, U: id, id = 1 & txt = txt, public.udata) #!!!??? bug?

I do not understand this error, omission, or I would not have understood correctly stated functionality.

[markokr]

As no value changed, the trigger has no idea which column you used in UPDATE statement.

Such case falls under another rule of "Do propagate empty updates" as they might be significant.

Eg other side acting on it, or simply adding update time, or whatever.

It is related to usual Postgres logic that on UPDATE a new row version is added to table, thus also to indexes.
And Postgres does not check whether any value changed or not.

So if such event is actually insignificant, then user should not do it in first place...

Although there are probably cases where doing such check before UPDATE not worth it. So we could change trigger:

1. Have a flag "ignore_empty_update"
2. If "ignore" param is present, then skip empty update, assuming it was the to-be-ingored column that got same value.

Although it does raise question if empty updates are OK on master server, why do you need to avoid them so hard in slave?

[Napolskih]

Is the trigger level is not clear what the columns participated in the update and what does not?
A trigger can compare the old and the new column value, it is already doing this in the function of validating whether the event is interesting.

Such case falls under another rule of "Do propagate empty updates" as they might be significant.

I can not and do not want to control the sql-expression, which runs the application. The system is large, it was established long ago. And it's not usually taken to check whether you need to actually do an update or correct information already.

I'm just, to a large existing database, add new functionality, based on pgq. I just need a change event defined tables. And not entirely, and some of the columns in them.
It turns out not too many required events, because tables are wide, and the columns are interested in well-defined, and in the case of real change.

Of course, I decide this issue at the level of subscribers, but it is not correct. Why does not generate a large amount is not required events.

Although it does raise question if empty updates are OK on master server, why do you need to avoid them so hard in slave?

I do not do replication, I just need change event data in the table (some columns).

I suggested the interesting possibility PG - http://www.postgresql.org/docs/9.1/interactive/functions-trigger.html
This trigger is easy to solve both problems, I doubt they will use. But, in my opinion, it would be very nice if PGQ was able to filter updates are not necessary and update the column without changing the meaning.

[markokr]

Changing the behavior based on whether "ignore" is present seems bad idea - too much magic. So addition trigger flag, like "skip_empty_update" might be ok.

But seems suppress_redundant_updates_trigger() is even better as that avoids update on master table too, so why not always use that if skipping is required?

[Napolskih]

Yes, I will use suppress_redundant_updates_trigger.
I was just wondering whether the error is not, this behavior options ignore.
And suppress_redundant_updates_trigger affects the entire database, which is sometimes not desirable.