Re: [SQL] Is there any way to stop triggers from cycling?
Josh Berkus wrote: Folks, I'm experimenting with a set of triggers to automagically maintain ltrees-organized tables. I almost have it working, except for a pesky problem with re-ordering groups. The idea is that I want to set up a set of triggers such that: a) If the user moves item (1) to item (3), then the existing items (2) and (3) will be "bumped down" to (1) and (2), or b) if the user moves item (3) to item (1) then the existing items (1) and (2) will be "bumped up". (btw, the reason I want to use triggers and not data-push functions is that the triggers are *much* more reliable for maintaining the tree fields) I have a set of triggers that are working except for a problem with cycling. What I'm looking for is a reliable, elegant way to make sure that the trigger is executed for each row only once. Currently I'm doing this by only cascade-updating the row adjacent to the one I'm moving. However, this is resulting in a cycle, and I don't see how to break it. Namely: Given: A 1 B 2 C 3 If I move A --> 3 then: One more addition to Rod/Chester's comments... It strikes me that the root of this problem is that you're trying to maintain the condition that sortorder is unique while breaking that condition by setting A=>3 while C=>3. Hence Rod's delete/insert matches what you're doing (delete, shuffle up to fill gap, insert with A=>3). If you counted sortorder in steps (e.g. 10,20,30) then you could set A=35 and it would be clear what order you wanted. Since the shuffled row is "unusual" (it isn't divisible by 10) you then can do one of two things: 1. If NEW.sortorder % 10 <> 0 THEN ... Compare OLD.sortorder, NEW.sortorder and shuffle rows between to fill the gap. Then alter your NEW.sortorder to be a "rounded" number (30). 2. A post-update statement trigger could do the whole thing by looking at the table as a whole. Might be useful if you do multiple re-ordering on a small table. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Problems with disabling triggers in Postgres 7.3.9
Hi all, I have a problem in a trigger that disable all the triggers of a table. This error occurs randomly and my guess is that occurs when i have a lot of concurrents inserts in the table participation. The error is : RelationBuildTriggers: 2 record(s) not found for rel participation I search all the web and not found a solution... Please help me... This is my trigger code : CREATE OR REPLACE FUNCTION buy4tickets() RETURNS "trigger" AS ' DECLARE mysequence bigint; idticket2 bigint; idticket3 bigint; idticket4 bigint; idticketservice bigint; idticketservice1 bigint; idticketservice2 bigint; idticketservice3 bigint; idticketservice4 bigint; tablename varchar; ticketnumberid2 varchar; ticketnumberid3 varchar; ticketnumberid4 varchar; BEGIN idticketservice1 := 15; idticketservice2 := 16; idticketservice3 := 17; idticketservice4 := 18; tablename := ''participation''; RAISE NOTICE ''idticketservice1 = % '', idticketservice1; RAISE NOTICE ''idticketservice2 = % '', idticketservice2; RAISE NOTICE ''idticketservice3 = % '', idticketservice3; RAISE NOTICE ''idticketservice4 = % '', idticketservice4; IF NEW.fk_id_ticket IS NOT NULL THEN SELECT INTO idticketservice, mysequence fk_id_ticket, sequence FROM ticket WHERE id = NEW.fk_id_ticket; RAISE NOTICE ''idticketservice = % '', idticketservice; IF idticketservice = idticketservice1 THEN RAISE NOTICE ''idticketservice1 = idticketservice ''; RAISE NOTICE ''mysequence = % '', mysequence; SELECT INTO idticket2,ticketnumberid2 id,numberid FROM ticket WHERE fk_id_ticket = idticketservice2::int8 AND sequence = mysequence::int8; RAISE NOTICE ''idticket2 = % '', idticket2; SELECT INTO idticket3,ticketnumberid3 id,numberid FROM ticket WHERE fk_id_ticket = idticketservice3::int8 AND sequence = mysequence::int8; RAISE NOTICE ''idticket3 = % '', idticket3; SELECT INTO idticket4,ticketnumberid4 id,numberid FROM ticket WHERE fk_id_ticket = idticketservice4::int8 AND sequence = mysequence::int8; RAISE NOTICE ''idticket4 = % '', idticket4; EXECUTE ''update pg_class set reltriggers=0 where relname = '' || quote_literal(tablename); IF idticket2 IS NOT NULL THEN EXECUTE ''insert into participation'' || ''(ani, dnis, fk_id_participation, fk_id_ticket, fk_id_service, datetimepart, status, ticketsequence, fk_id_ticketservice, ticketnumberid)'' || '' values( '' || quote_literal(NEW.ani) || '','' || quote_literal(NEW.dnis) || '','' || NEW.fk_id_participation || '',''|| idticket2 || '','' || quote_literal(NEW.fk_id_service) || '','' || quote_literal(NEW.datetimepart) || '','' || NEW.status|| '',''|| mysequence || '','' || idticketservice2 ||'','' || quote_literal(ticketnumberid2) ||'')''; END IF; IF idticket3 IS NOT NULL THEN EXECUTE ''insert into participation'' || ''(ani, dnis, fk_id_participation, fk_id_ticket, fk_id_service, datetimepart, status, ticketsequence, fk_id_ticketservice, ticketnumberid)'' || '' values( '' || quote_literal(NEW.ani) || '','' || quote_literal(NEW.dnis) || '','' || NEW.fk_id_participation || '',''|| idticket3 || '','' || quote_literal(NEW.fk_id_service) || '','' || quote_literal(NEW.datetimepart) || '','' || NEW.status || '',''|| mysequence || '','' || idticketservice3 ||'','' || quote_literal(ticketnumberid3) ||'')''; END IF; IF idticket4 IS NOT NULL THEN EXECUTE ''insert into participation'' || ''(ani, dnis, fk_id_participation, fk_id_ticket, fk_id_service, datetimepart, status, ticketsequence, fk_id_ticketservice, ticketnumberid)'' || '' values( '' || quote_literal(NEW.ani) || '','' || quote_literal(NEW.dnis) || '','' || NEW.fk_id_participation || '',''|| idticket4 || '','' || quote_literal(NEW.fk_id_service) || '','' || quote_literal(NEW.datetimepart) || '','' || NEW.status || '',''|| mysequence || '','' || idticketservice4 ||'','' || quote_literal(ticketnumberid4) ||'')''; E
[SQL]
Hi all. I am new to postgres, so I am still learning the basics. In Sequel Server, one can set up a function to return a table eg: CREATE FUNCTION [dbo].[AuthCodes] (@CLIENTID INT) RETURNS @AuthCodes TABLE ( [ID] INT, AUTHCODE VARCHAR(100), ISSUEDATE DATETIME, ISSUEDBY VARCHAR(100), RECIPIENT VARCHAR(100), EMAILID VARCHAR(100) ) and then use select into or insert to populate the table. I am really battling to figure out how to do the same in a postgres function. It seems like one would have to create a user defined type (myType for eg), and then set the return type of the function to be a set of myType, but I can’t seem to make it work. I am guessing that I am on the wrong track…does anyone have any suggestions, or examples I could follow?
Re: [SQL]
On Thursday 09 March 2006 14:35, Klay Martens wrote: >Hi all. > > >I am new to postgres, so I am > still learning the basics. > > >In Sequel Server, one can set > up a function to return a table eg: > > > > > >CREATE FUNCTION > [dbo].[AuthCodes] (@CLIENTID INT) > > > >RETURNS @AuthCodes > TABLE > > >( > > > style=""> > [ID] INT, > > > style=""> > AUTHCODE VARCHAR(100), > > > style=""> > ISSUEDATE DATETIME, > > > style=""> > ISSUEDBY VARCHAR(100), > > > style=""> > RECIPIENT VARCHAR(100), > > > style=""> > EMAILID VARCHAR(100) > > > >) > > > > > >and then use select into or > insert to populate the table. > > >I am really battling to figure > out how to do the same in a postgres function. > > >It seems like one would have > to create a user defined type (myType for eg), and then set the > return type of the function to be a set of myType, but I can’t seem > to make it work. I am guessing that I am on the wrong track…does > anyone have any suggestions, or examples I could > follow? Please don't post HTML messages to mail lists. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL]
am 09.03.2006, um 15:35:45 +0200 mailte Klay Martens folgendes: > > > Hi all. > > > I am new to postgres, so I am still > learning the basics. Please, write in plain ASCII. Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problems with disabling triggers in Postgres 7.3.9
Flávio Suguimoto wrote: > Hi all, > > I have a problem in a trigger that disable all the triggers of a table. This > error occurs randomly and my guess is that occurs when i have a lot of > concurrents inserts in the table participation. > > The error is : RelationBuildTriggers: 2 record(s) not found for rel > participation I don't know what's involved in this particular bug, but the short answer is: don't update system catalogs directly. Have your triggers cope with the situation where you don't want to fire them in certain cases. System catalogs are delicate stuff; there are some caches that must be maintained in a coherent manner. Usually the catalogs do not follow MVCC rules to the letter. The "UPDATE pg_class" was used by pg_dump at some point, but it was only a hack and I wouldn't expect it to work correctly when multiple processes are involved. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problems with disabling triggers in Postgres 7.3.9
Thanks Alvaro, I know that is a hack but i don't have (that i know) other way to disable a trigger. The reason that i need to disable is that trigger will be called recursively. Let explain better my case, the trigger that i sent the code is called after inserts in table participation but itself insert 3 more records in the participation table. If i don't disable the trigger it will be calling recursively... There is another walk-around to avoi it? regards, Flávio Suguimoto -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Alvaro Herrera Sent: Thursday, March 09, 2006 10:56 AM To: Flávio Suguimoto Cc: [email protected] Subject: Re: [SQL] Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto wrote: > Hi all, > > I have a problem in a trigger that disable all the triggers of a table. This > error occurs randomly and my guess is that occurs when i have a lot of > concurrents inserts in the table participation. > > The error is : RelationBuildTriggers: 2 record(s) not found for rel > participation I don't know what's involved in this particular bug, but the short answer is: don't update system catalogs directly. Have your triggers cope with the situation where you don't want to fire them in certain cases. System catalogs are delicate stuff; there are some caches that must be maintained in a coherent manner. Usually the catalogs do not follow MVCC rules to the letter. The "UPDATE pg_class" was used by pg_dump at some point, but it was only a hack and I wouldn't expect it to work correctly when multiple processes are involved. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL]
Klay Martens wrote: Hi all. I am new to postgres, so I am still learning the basics. In Sequel Server, one can set up a function to return a table eg: CREATE FUNCTION [dbo].[AuthCodes] (@CLIENTID INT) RETURNS @AuthCodes TABLE [snip] and then use select into or insert to populate the table. I am really battling to figure out how to do the same in a postgres function. It seems like one would have to create a user defined type (myType for eg), and then set the return type of the function to be a set of myType, but I can’t seem to make it work. I am guessing that I am on the wrong track…does anyone have any suggestions, or examples I could follow? You've got the right idea. If there's not an existing table, define a type, return SETOF my_type and use RETURN NEXT inside the function to return each row. You call the function as: "SELECT * FROM myfunc()", that is - treat it as a table. There's an item on set-returning functions here: http://techdocs.postgresql.org/ and here http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problems with disabling triggers in Postgres 7.3.9
Hi Richard, I have that trigger running on AFTER INSERT of participation table. That trigger inserts 3 new record for each line i inserted in participation. I guess the problem is in these two statement: EXECUTE ''update pg_class set reltriggers=0 where relname = '' || quote_literal(tablename); EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname = '' || quote_literal(tablename); These statement is a walk-around to disable and enable the trigger on a table and i use this to avoid the trigger be called recursively. My question is there is another to do this trigger avoiding need to disable/enable the triggers? Or there is an way to solve this problem with RelationBuildTriggers? regards, Flávio Suguimoto -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 10:56 AM To: Flávio Suguimoto Cc: [email protected] Subject: Re: [SQL] Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto wrote: > Hi all, > > I have a problem in a trigger that disable all the triggers of a table. This > error occurs randomly and my guess is that occurs when i have a lot of > concurrents inserts in the table participation. > > The error is : RelationBuildTriggers: 2 record(s) not found for rel > participation Well, at the very least you should get an exclusive write-lock on the table "participation" before turning its triggers off. However, I'm doubtful that you really want to do that in any case. Can I ask what problem you are trying to solve? Oh, and upgrade to 7.3.14 too - you're missing 5 sets of bug-fixes. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problems with disabling triggers in Postgres 7.3.9
Flávio Suguimoto wrote: Hi Richard, I have that trigger running on AFTER INSERT of participation table. That trigger inserts 3 new record for each line i inserted in participation. Yes, but WHY? What problem are you trying to solve. Presumably there is some difference between the first "participation" row and the other 3 - the status, the ticket-number, something. Test for that difference and you'll know whether you'll need to insert those 3 extra rows or not. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problems with disabling triggers in Postgres 7.3.9
Thanks Richard, Until the last version of my application i couldn't know which record is different of another. But i saw that someone created a new column and i guess that i could know if its the first one or ther other 3... Thank you very much... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Richard Huxton Sent: Thursday, March 09, 2006 11:42 AM To: Flávio Suguimoto Cc: [email protected] Subject: Re: [SQL] Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto wrote: > Hi Richard, > > I have that trigger running on AFTER INSERT of participation table. That > trigger inserts 3 new record for each line i inserted in participation. Yes, but WHY? What problem are you trying to solve. Presumably there is some difference between the first "participation" row and the other 3 - the status, the ticket-number, something. Test for that difference and you'll know whether you'll need to insert those 3 extra rows or not. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problems with disabling triggers in Postgres 7.3.9
=?iso-8859-1?Q?Fl=E1vio_Suguimoto?= <[EMAIL PROTECTED]> writes: > EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger where > pg_class.oid=tgrelid and relname = '' || quote_literal(tablename); This command is just plain wrong, because the aggregation is done across uncertain scope. Something like update pg_class set reltriggers = (select count(*) from pg_trigger where pg_class.oid=tgrelid) where relname = 'foo' would at least not run the risk of assigning wrong counts. You still have the issue that the commands will touch every table with a given name; there needs to be some thought about schemas here. In general though I agree with Alvaro's comment that touching system catalogs directly is bad practice. You should update to a PG version that has ALTER TABLE DISABLE TRIGGER, and use that. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
