Re: [GENERAL] Trigger problems/questions

[Jim Fulton]

On Thu, Jun 15, 2017 at 1:56 PM, Tom Lane  wrote:

> Jim Fulton  writes:
> > I have an object database that's mirrored to a table with data in a JSONB
> > column.  Data are organized into "communities".  Community ids aren't
> > stored directly in content but can be found by recursively following
> > __parent__ properties. I want to be able to index content records on
> their
> > community ids.
>
> > (I originally tried to index functions that got ids, but apparently lying
> > about immutability is a bad idea and I suffered the consequences. :-])
>
> > I tried creating a trigger to populate a community_zoid property with a
> > community id when a record is inserted or updated.  The trigger calls a
> > recursive functions to get the community id.
> > ...
> > This scheme succeeds most of the time, but occasionally, it fails.
>
> Since your original idea failed, I suppose that the parent relationships
> are changeable?


Good question.

A few kinds of objects can, rarely, move in the hierarchy, and, they never
move between communities, so their community id never changes.

IDK WTF my indexing attempt.  I could build the index, then add an object
to the tree and it wouldn't be indexed.  This was in a staging database
where there were no other changes.


> What mechanism have you got in place to propagate a
> relationship change back down to the child records?
>

This is a non-issue, at least WRT community ids.  If I were, for example,
to index paths, it would be an issue for some objects, but I'm not at that
point yet.


> Also, this looks to have a race condition: if you search for a record's
> community id at about the same time that someone else is changing the
> parent linkage, you may get the old answer, but by the time you commit the
> record update that answer may be obsolete.  This is a problem because even
> if you had another trigger that was trying (in the someone else's session)
> to propagate new community ids back to affected records, it wouldn't think
> that the record you're working on needs a change, because it would also
> see the old version of that record.
>
> Solutions to the race problem usually involve either SELECT FOR UPDATE
> to lock rows involved in identifying the target record's community ID,
> or use of SERIALIZABLE to cause the whole transaction to fail if its
> results might be inconsistent.  Either one will add some complexity
> to your application code.
>

There's a global lock around all of the updates to the table.  (This isn't
as unreasonable as it sounds :), but anyway, that's outside the scope of
this discussion.)

Even if there was some kind of race, I'd still get a community id set, it
might be wrong, but it would be set.

regards, tom lane
>

Thanks.

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] Trigger problems/questions

[Tom Lane]

Jim Fulton  writes:
> I have an object database that's mirrored to a table with data in a JSONB
> column.  Data are organized into "communities".  Community ids aren't
> stored directly in content but can be found by recursively following
> __parent__ properties. I want to be able to index content records on their
> community ids.

> (I originally tried to index functions that got ids, but apparently lying
> about immutability is a bad idea and I suffered the consequences. :-])

> I tried creating a trigger to populate a community_zoid property with a
> community id when a record is inserted or updated.  The trigger calls a
> recursive functions to get the community id.
> ...
> This scheme succeeds most of the time, but occasionally, it fails.

Since your original idea failed, I suppose that the parent relationships
are changeable?  What mechanism have you got in place to propagate a
relationship change back down to the child records?

Also, this looks to have a race condition: if you search for a record's
community id at about the same time that someone else is changing the
parent linkage, you may get the old answer, but by the time you commit the
record update that answer may be obsolete.  This is a problem because even
if you had another trigger that was trying (in the someone else's session)
to propagate new community ids back to affected records, it wouldn't think
that the record you're working on needs a change, because it would also
see the old version of that record.

Solutions to the race problem usually involve either SELECT FOR UPDATE
to lock rows involved in identifying the target record's community ID,
or use of SERIALIZABLE to cause the whole transaction to fail if its
results might be inconsistent.  Either one will add some complexity
to your application code.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trigger problems/questions

[Jim Fulton]

On Thu, Jun 15, 2017 at 1:44 PM, Adrian Klaver 
wrote:

> On 06/15/2017 10:23 AM, Jim Fulton wrote:
>

...


> I do not pretend to fully understand what the the triggers/functions are
> really doing,

but I did notice this:
>
> create or replace function populate_community_zoid_triggerf()
>
> ...
>
> new_zoid := NEW.state ->> 'community_zoid';
> zoid := find_community_zoid(
> NEW.zoid, NEW.class_name, NEW.state)::text;
> if zoid is null then
> if new_zoid is not null then
> NEW.state := NEW.state - 'community_zoid';
> end if;
> else
> if new_zoid is null or zoid != new_zoid then
> NEW.state :=
> NEW.state || ('{"community_zoid": ' || zoid || '}')::jsonb;
> end if;
> end if;
>
> ...
>
>
> Not sure what happens if zoid is null and new_zoid is null also?
>

Then then no change is needed and nothing happens, which is correct.


> Or if it matters?


Nope.

Thanks for looking.

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] Trigger problems/questions

[Adrian Klaver]

On 06/15/2017 10:23 AM, Jim Fulton wrote:
I have an object database that's mirrored to a table with data in a 
JSONB column.  Data are organized into "communities".  Community ids 
aren't stored directly in content but can be found by recursively 
following __parent__ properties. I want to be able to index content 
records on their community ids.


(I originally tried to index functions that got ids, but apparently 
lying about immutability is a bad idea and I suffered the consequences. :-])


I tried creating a trigger to populate a community_zoid property with a 
community id when a record is inserted or updated.  The trigger calls a 
recursive functions to get the community id.


Trigger: 
https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L261 



Trigger procedure: 
https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L236 



Function to find a community id: 
https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L209 



This scheme succeeds most of the time, but occasionally, it fails.

I can find records where it has failed with a query like:

   select zoid
   from newt
   where find_community_zoid(zoid, class_name, state) is not null
 and not state ? 'community_zoid';

If I update the records where it has failed:

   update newt set class_name=class_name
   where find_community_zoid(zoid, class_name, state) is not null
  and not state ? 'community_zoid';

Then retry the query above, I get 0 rows back. This would seem to 
indicate that the trigger is logically correct.


Questions:

  * Am I doing it wrong?  For example, is there some limitation on
trigger procedures that I'm violating?
  * If a trigger procedure errors, is the transaction aborted? (I would
hope so.)
  * Should I expect triggers to be reliable and rely on them for
database integrity?
  * Any suggestions on how to debug this?


I do not pretend to fully understand what the the triggers/functions are 
really doing, but I did notice this:


create or replace function populate_community_zoid_triggerf()

...

new_zoid := NEW.state ->> 'community_zoid';
zoid := find_community_zoid(
NEW.zoid, NEW.class_name, NEW.state)::text;
if zoid is null then
if new_zoid is not null then
NEW.state := NEW.state - 'community_zoid';
end if;
else
if new_zoid is null or zoid != new_zoid then
NEW.state :=
NEW.state || ('{"community_zoid": ' || zoid || '}')::jsonb;
end if;
end if;

...


Not sure what happens if zoid is null and new_zoid is null also?
Or if it matters?




Jim

--
Jim Fulton
http://jimfulton.info



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Trigger problems/questions

[Jim Fulton]

I have an object database that's mirrored to a table with data in a JSONB
column.  Data are organized into "communities".  Community ids aren't
stored directly in content but can be found by recursively following
__parent__ properties. I want to be able to index content records on their
community ids.

(I originally tried to index functions that got ids, but apparently lying
about immutability is a bad idea and I suffered the consequences. :-])

I tried creating a trigger to populate a community_zoid property with a
community id when a record is inserted or updated.  The trigger calls a
recursive functions to get the community id.

Trigger: https://github.com/karlproject/karl/blob/master/
karl/scripts/pgevolve.py#L261

Trigger procedure: https://github.com/karlproject/karl/blob/master/
karl/scripts/pgevolve.py#L236

Function to find a community id: https://github.com/
karlproject/karl/blob/master/karl/scripts/pgevolve.py#L209

This scheme succeeds most of the time, but occasionally, it fails.

I can find records where it has failed with a query like:

  select zoid
  from newt
  where find_community_zoid(zoid, class_name, state) is not null
and not state ? 'community_zoid';

If I update the records where it has failed:

  update newt set class_name=class_name
  where find_community_zoid(zoid, class_name, state) is not null
 and not state ? 'community_zoid';

Then retry the query above, I get 0 rows back. This would seem to indicate
that the trigger is logically correct.

Questions:

   - Am I doing it wrong?  For example, is there some limitation on trigger
   procedures that I'm violating?
   - If a trigger procedure errors, is the transaction aborted? (I would
   hope so.)
   - Should I expect triggers to be reliable and rely on them for database
   integrity?
   - Any suggestions on how to debug this?

Jim

-- 
Jim Fulton
http://jimfulton.info

Re: [GENERAL] Trigger Problems

[gnari]

From: "Jamie Deppeler" <[EMAIL PROTECTED]>


> Hi , i have designed a trigger function called test2(Integer)

the trigger function must be declared without arguments

http://scripts.postgresql.org/docs/7.4/static/triggers.html#TRIGGER-DEFINITI
ON

gnari





---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Trigger Problems

[Jim C. Nasby]

That is probably because you created test2(INTEGER) and you're trying to
call test2(TEXT).

On Wed, Dec 01, 2004 at 11:22:41AM +1100, Jamie Deppeler wrote:
> Hi , i have designed a trigger function called test2(Integer)
> 
> im trying to use it with a trigger but get errors that function cannot 
> be found
> 
> Trigger statement is
> 
> CREATE TRIGGER "new_trigger566" BEFORE INSERT
> ON "customer" FOR EACH ROW
> EXECUTE PROCEDURE test2("primary");
> 
> 
> get the error test2() cannot be found
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>   http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Trigger Problems

[Jamie Deppeler]

Hi , i have designed a trigger function called test2(Integer)
im trying to use it with a trigger but get errors that function cannot 
be found

Trigger statement is
CREATE TRIGGER "new_trigger566" BEFORE INSERT
ON "customer" FOR EACH ROW
EXECUTE PROCEDURE test2("primary");
get the error test2() cannot be found
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Trigger Problems?

[GH]

On Thu, Dec 07, 2000 at 03:40:00PM -0600, some SMTP stream spewed forth: 
> Ok, so I'm not sure why this is the way it is, but I figured out why the backend was 
>failing every time I tried to change the data on the existing five tables.  When I 
>dropped the five unused tables, I assumed that the DROP function would also remove 
>the foreign key constraint triggers that were associated with the dropped tables.  
>But, in fact, they were left in the db.  Therefore, every time any update or delete 
>was performed on the existing tables, the constraint trigger fired and failed.  I 
>don't know if this was just my stupidity and I should have know to delete these 
>triggers or if this is a bug.  Either way, I learned something new.

Other than that, why would the connection die?
It seems like an error would be better.

gh

> 
> Thanks,
> Darrin
> -Original Message-
> From: Darrin Ladd <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
> Date: Wednesday, December 06, 2000 12:03 PM
> Subject: [GENERAL] Trigger Problems?
> 
> 
> Hello,
>  
> I sent an e-mail to the mailing list yesterday, but then realized that I sent it 
>using my other account, which may not be able to post to the list.
>  
> It seems that I have found myself in a little bit of trouble.  I was wondering 
>if there is anyone out there that can help me.  Unfortunately, I am quite sure that I 
>will not provide enough information to diagnose the problem, because I don't really 
>know where to start.  But, here is what I do know:  Yesterday, I decided that it was 
>time to get rid of five tables in my db that were not being used.  I proceeded to 
>drop all five tables.  I then remembered that there were insert triggers on existing 
>tables which called a function which updated the dropped tables.  So then I dropped 
>the function and the triggers.  All of this seemed to be successful, but then I hit a 
>serious problem.  Any attempt to manipulate data (anything but a select) in the still 
>existing tables which had the triggers removed from them aborts with the following 
>error:
>  
> pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !#
>  
> Obviously, this is not good.  I have vacuum analyzed the db and that seemed to 
>have processed fine.  If someone could point me in a good direction to figure out 
>what is causing this, I would greatly appreciate it.  I am not opposed to dropping 
>the bad tables and restoring from backups, but I am still interested in finding out 
>what is wrong and what I did wrong so that I don't do it again.
>  
> Thank you very much for your help!
> Darrin

Re: [GENERAL] Trigger Problems?

[Darrin Ladd]

Ok, so I'm not sure why this is the way it is, 
but I figured out why the backend was failing every time I tried to change the 
data on the existing five tables.  When I dropped the five unused tables, I 
assumed that the DROP function would also remove the foreign key constraint 
triggers that were associated with the dropped tables.  But, in fact, they 
were left in the db.  Therefore, every time any update or delete was 
performed on the existing tables, the constraint trigger fired and failed.  
I don't know if this was just my stupidity and I should have know to delete 
these triggers or if this is a bug.  Either way, I learned something 
new.
 
Thanks,
Darrin

-Original Message-From: 
Darrin Ladd <[EMAIL PROTECTED]>To: 
[EMAIL PROTECTED] 
<[EMAIL PROTECTED]>Date: 
Wednesday, December 06, 2000 12:03 PMSubject: [GENERAL] 
Trigger Problems?
Hello,
 
I sent an e-mail to the mailing list 
yesterday, but then realized that I sent it using my other account, which 
may not be able to post to the list.
 
It seems that I have found myself in a 
little bit of trouble.  I was wondering if there is anyone out there 
that can help me.  Unfortunately, I am quite sure that I will not 
provide enough information to diagnose the problem, because I don't really 
know where to start.  But, here is what I do know:  Yesterday, I 
decided that it was time to get rid of five tables in my db that were not 
being used.  I proceeded to drop all five tables.  I then 
remembered that there were insert triggers on existing tables which called a 
function which updated the dropped tables.  So then I dropped the 
function and the triggers.  All of this seemed to be successful, but 
then I hit a serious problem.  Any attempt to manipulate data (anything 
but a select) in the still existing tables which had the triggers removed 
from them aborts with the following error:
 
pqReadData() -- backend closed the channel 
unexpectedly.    This probably 
means the backend terminated 
abnormally    before or while 
processing the request.The connection to the server was lost. Attempting 
reset: Failed.!#
 
Obviously, this is not good.  I have 
vacuum analyzed the db and that seemed to have processed fine.  If 
someone could point me in a good direction to figure out what is causing 
this, I would greatly appreciate it.  I am not opposed to dropping the 
bad tables and restoring from backups, but I am still interested in finding 
out what is wrong and what I did wrong so that I don't do it 
again.
 
Thank you very much for your 
help!
Darrin