Обсуждение: Trying to understand odd trigger behavior

I think I know what is happening, but I wanted to see if my understanding 
is correct.

I have a perl after insert trigger for a table with a non-null column element 
and I am getting an occasional error when the trigger executes for 
printing a null value which is $TD->{new}{element}. However, I do the 
insert with an on conflict that converts it into an update. And I have 
a before update trigger that blocks changing the record, but returns null 
if the old and new records are the same.

My theory is that the insert trigger is firing even though the action 
got changed to an update and that because the update trigger cancelled 
the update, there is no new record. So I should just test for $TD->{new} 
being doing before doing stuff.

Does that sound correct?

Bruno Wolff III wrote:
> I think I know what is happening, but I wanted to see if my understanding 
> is correct.
> 
> I have a perl after insert trigger for a table with a non-null column element 
> and I am getting an occasional error when the trigger executes for 
> printing a null value which is $TD->{new}{element}. However, I do the 
> insert with an on conflict that converts it into an update. And I have 
> a before update trigger that blocks changing the record, but returns null 
> if the old and new records are the same.
> 
> My theory is that the insert trigger is firing even though the action 
> got changed to an update and that because the update trigger cancelled 
> the update, there is no new record. So I should just test for $TD->{new} 
> being doing before doing stuff.
> 
> Does that sound correct?

Absolutely, but it should be easy to run a few tests with only a single row
insert that confirms your theory.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Fri, Jun 15, 2018 at 08:54:52 +0200,
  Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
>Absolutely, but it should be easy to run a few tests with only a single row
>insert that confirms your theory.

Thanks.

On Fri, Jun 15, 2018 at 08:54:52 +0200,
  Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>Bruno Wolff III wrote:
>> I think I know what is happening, but I wanted to see if my understanding
>> is correct.
>>
>> I have a perl after insert trigger for a table with a non-null column element
>> and I am getting an occasional error when the trigger executes for
>> printing a null value which is $TD->{new}{element}. However, I do the
>> insert with an on conflict that converts it into an update. And I have
>> a before update trigger that blocks changing the record, but returns null
>> if the old and new records are the same.
>>
>> My theory is that the insert trigger is firing even though the action
>> got changed to an update and that because the update trigger cancelled
>> the update, there is no new record. So I should just test for $TD->{new}
>> being doing before doing stuff.
>>
>> Does that sound correct?
>
>Absolutely, but it should be easy to run a few tests with only a single row
>insert that confirms your theory.

It looks like something else is going on. I tried checking if 
$_TD->{new}{element} was defined and print some extra information if it 
wasn't to the local syslog service instead of to the remote elastic 
server and I was still seeing the issue occasionally without getting the 
extra info. Also reinserting duplicate data doesn't seem to be a reliable 
way to reproduce the problem. I didn't see it at all doing it by hand to 
a small number of records. I only see this on the trigger that 
sends data to the elastic server and not the one that sends it to the 
local syslog server. The if the issue is data dependent it would always 
hit the elastic server trigger first. So that doesn't rule out both triggers 
from having the same issue, but does suggest that I may want to look at 
stuff specific to the elastic trigger with extra scrutiny.

I probably need to narrow things down myself as looking at the whole system 
is going to be too much effort when this might be user error and not a bug. 
And it isn't self contained as it is part of a system that pulls log data 
from a third party. That would make it hard for someone else to test.

On Thu, Jun 14, 2018 at 23:04:24 -0500,
  Bruno Wolff III <bruno@wolff.to> wrote:
>
>I have a perl after insert trigger for a table with a non-null column 
>element and I am getting an occasional error when the trigger executes 
>for printing a null value which is $TD->{new}{element}. However, I do 
>the insert with an on conflict that converts it into an update. And I 
>have a before update trigger that blocks changing the record, but 
>returns null if the old and new records are the same.

I think I misunderstood the error I was getting. I don't think the value 
being printed is null, but rather the socket instance I am trying to 
print to. I haven't tested this yet, but it makes sense and my other theory 
didn't match later evidence.