[SQL] TRIGGERed INSERTS
Howdy; I'm writing a "script" to pre-populate a database system that's already in place. The database system is in an advanced stage of development and includes many stored procedures and TRIGGERS. The "script" is really a stored procedure designed to be executed by the system's admin guy as part of "user system start-up." Within the script I'm calling stored procedures to create "items" (which in my system are "data" table records with corresponding records in an ordering/pointer table). Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in turn, insert some subordinate items into parallel data tables and the central pointer/ordering table. As part of the pre-population "script" I call "add_item" and then want to UPDATE a record in a different data table created by one of the TRIGGERS (i.e. a "sub-record" if you will). When I use a SELECT INTO within the "script" to discover the pointer record key for this new sub-record (so I can go UPDATE it), the SELECT INTO comes up with NULL. After my "script" is done, however, a SELECT from the psql command line discovers the pointer record's key value with no problem! Am I not waiting long enough within my "script" for the engine to settle out? Are the results of that transaction not available to me until I roll out of the transaction initiated by my "script" procedure? I'm somewhat baffled as to why the SELECT INTO is coming up NOT FOUND within the scope of my "script" procedure. I did a search of the mailing lists on "TRIGGERS" but didn't turn up any clues. Thanks for your help! Martin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] TRIGGERed INSERTS
Thanks Tom. The work-around was to create the "script" in SQL -- using SELECT INTO statements to capture the key values -- instead of creating the "script" as a stored procedure. Seems like a "transaction" issue. Having transactions occur at the SELECT level is very intuitive and a really nice, "protect-us-from-ourselves" feature that I'm not sure I'd want to mess with. Having the ability to execute a stored procedure outside the scope of a SELECT would ultimately resolve this. A topic for another day ... unless I missed something in an update somewhere. Anyway, thanks again. > "Martin Crundall" <[EMAIL PROTECTED]> writes: >>Some of the data tables have "AFTER INSERT" TRIGGERs on them that, >> in >> turn, insert some subordinate items into parallel data tables and the >> central pointer/ordering table. > > It looks to me like AFTER triggers are fired upon return to the main > loop in postgres.c, thus only at the end of a querystring sent by the > client. This is perhaps wrong, but I'm not sure that allowing them to > fire during plpgsql functions would be a good thing either. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] TRIGGERed INSERTS
Howdy; I'm writing a "script" to pre-populate a database system that's already in place. The database system is in an advanced stage of development and includes many stored procedures and TRIGGERS. The "script" is really a stored procedure designed to be executed by the system's admin guy as part of "user system start-up." Within the script I'm calling stored procedures to create "items" (which in my system are "data" table records with corresponding records in an ordering/pointer table). Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in turn, insert some subordinate items into parallel data tables and the central pointer/ordering table. As part of the pre-population "script" I call "add_item" and then want to UPDATE a record in a different data table created by one of the TRIGGERS (i.e. a "sub-record" if you will). When I use a SELECT INTO within the "script" to discover the pointer record key for this new sub-record (so I can go UPDATE it), the SELECT INTO comes up with NULL. After my "script" is done, however, a SELECT from the psql command line discovers the pointer record's key value with no problem! Am I not waiting long enough within my "script" for the engine to settle out? Are the results of that transaction not available to me until I roll out of the transaction initiated by my "script" procedure? I'm somewhat baffled as to why the SELECT INTO is coming up NOT FOUND within the scope of my "script" procedure. I did a search of the mailing lists on "TRIGGERS" but didn't turn up any clues. Thanks for your help! Martin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pg_dump / dates:
Question for Tom Lockhart: pg_dump is generating some dates that look like: '2002-09-09 02:11:60.00' or '2002-10-31 23:59:60.00'. psql -f doesn't like these dates. I can sed the :60 to a :59 but -- in the latter case it moves the date backwards a day, which doesn't work for my app. I understand from reading some of the list threads that there's a good reason to allow for "second" field values greater than 59, and per an email thread I had with Tom Lane, this has been addressed -- that it was a rounding error issue on some platforms. I'm running postgres 7.2.2 on slackware 8.1 (kernel 2.4.18). Is there a workaround on this? Tks and kind regards, Martin Crundall ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Postgres Date pg_dump/Import:
Tom; Not sure how to tell. I didn't explicitly specify anything like this during the ./configure step of install. Poking around in the souce directory, I pulled this out of config.cache ... configure:1243: checking whether the C compiler (gcc -O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro -fomit-frame-pointer -fno-exceptions ) works Note, I didn't use the postgresql that came with Slackware; I downloaded it from the postgres web site. Martin >> i am currently running 7.2.2 on slackware 8.1 (kernel 2.4.18) > > BTW, is it possible that your postgres was built with -ffast-math > in CFLAGS? That is known to cause symptoms like this. Unfortunately > -ffast-math is in the default CFLAGS on some Linux distros. > > regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Postgres Date pg_dump/Import:
Tom; IT WORKED! This is what I did: env | grep CFLAGS and that produced all those flags as you predicted (-O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro) unset CFLAGS make clean ./configure --enable-odbc make make install a subsequent pg_dump of the staging system's database produced NO ":60" substrings in any time stamps! Thanks much for your help! Martin p.s. interesting to note -- my dev box is actually the same distro (slackware 8.1) but running in a vmware window. there was no such CFLAGS env variable set and pg_dumps on the dev box are ok. the distro install must set the CFLAGS based on hardware (the laptop is a Compaq PIII/700, and the staging box is a P4/1.7G on an intel chipset board). Anyway, really happy this worked -- thanks again. Let me know if there's anything I can do. > "Martin Crundall" <[EMAIL PROTECTED]> writes: >> Not sure how to tell. I didn't explicitly specify anything like this >> during the ./configure step of install. Poking around in the souce >> directory, I pulled this out of config.cache ... > >> configure:1243: checking whether the C compiler (gcc -O9 >> -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro >> -march=pentiumpro > ^^^ >> -fomit-frame-pointer -fno-exceptions ) works > > Bingo: something is supplying a whole bunch of "helpful" optimization > options, including -ffast-math which is what's causing your problem. > Left to its own devices, configure would have only selected "-O2" as the > default flags for gcc. > > I suspect if you try "env | grep CFLAGS" you will find that the problem > is a CFLAGS environment variable that you're inheriting from somewhere. > If so, you could "unset CFLAGS" and then re-run configure, rebuild, > reinstall (you won't need to initdb fortunately, just reinstall the > executables). > > If it's not coming from a CFLAGS environment variable setting then > you'll need to look harder. > > regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] celko nested set functions -- tree move
I'm not sure that keying off lft is safe in a multi-user environment. I opted to create and use an objid on the tree definition table, since its identity is static. I also found that when trees get active, allowing for tree IDs increased operation speed quite a bit (i actually push this to two levels--a 'universe id' and then a 'tree id'). Here's my version. Clearly not as elegantly written, but nothing's gone awry yet. -- --- --Title: trackmyproject_tree_move() -- Function: moves a tree branch in the hierarchy from one parent to -- another. --parms: srcobj the branch/object to be moved -- newparentthe new parent for the object to be moved -- Returns: zero -- --- CREATE FUNCTION trackmyproject_tree_move( INT4, INT4 ) RETURNS INT4 AS ' DECLARE t_srcobj ALIAS FOR $1; t_newparent ALIAS FOR $2; srcspan INT4; srclft INT4; srcrgt INT4; srcuid INT4; srctid INT4; newparentrgt INT4; newparentuid INT4; newparenttid INT4; moveoffset INT4; myrec RECORD; BEGIN -- get src span info (distance between lft and rgt plus one) SELECT ((rgt - lft) + 1) INTO srcspan FROM list_objects WHERE objid_auto=t_srcobj; LOCK TABLE list_objects; -- find out where the new parent currently ends SELECT rgt, universeid, treeid INTO myrec FROM list_objects WHERE objid_auto=t_newparent; newparentrgt := myrec.rgt; newparentuid := myrec.universeid; newparenttid := myrec.treeid; -- create the gap at the bottom of the hierarchy for the -- new parent big enuf for the source object and its tree UPDATE list_objects SET lft = CASE WHEN lft > newparentrgt THEN lft + srcspan ELSE lft END, rgt = CASE WHEN rgt >= newparentrgt THEN rgt + srcspan ELSE rgt END WHERE rgt >= newparentrgt AND universeid=newparentuid AND treeid=newparenttid; -- move the object tree in to the newly created gap -- (may seem like a repetative select, but the above UPDATE -- MAY have moved the source object) SELECT lft, rgt, universeid, treeid INTO myrec FROM list_objects WHERE objid_auto=t_srcobj; srclft := myrec.lft; srcrgt := myrec.rgt; srcuid := myrec.universeid; srctid := myrec.treeid; -- this works even if we are jumping trees or moving up or down within -- the same tree moveoffset := srclft - newparentrgt; UPDATE list_objects SET lft = lft - moveoffset, rgt = rgt - moveoffset, universeid = newparentuid, treeid = newparenttid WHERE lft >= srclft AND rgt <= srcrgt AND universeid=srcuid AND treeid=srctid; -- close the gap where the source object was UPDATE list_objects SET lft = CASE WHEN lft > srclft THEN lft - srcspan ELSE lft END, rgt = CASE WHEN rgt > srclft THEN rgt - srcspan ELSE rgt END WHERE rgt >= srclft AND universeid=srcuid AND treeid=srctid; RETURN 0; END; ' LANGUAGE 'plpgsql'; > Robert Treat and I came up with a better way to move > nodes from one branch to another inside of a nested tree: ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] celko nested set functions -- tree move
Hi Robert; The math actually works either way; if it goes negative, the offset is positive, which is okay. Your selects are way more elegant. I guess I was just raising the point that using a key other than lft (which tends to move around in an active tree), is probably safer. The table lock just keeps the lft and rgt values static while the two updates are done. If I combined the two updates, I could probably loose the lock; I took the conservative route. The node list is around 10k, though using the universe id and tree id to separate trees, I try to keep trees to around 2k or thereabouts so that tree manip functions remain reasonably fast. I've found that a vacuum full analyze is needed at least once a day. Looking over the tree, I don't see too many nodes that are indented further than ten levels, although some are as deep as 20. I really like Celko's model for this app; it makes navigation a snap. Modifying the core functions to deal with sub-trees, however, was a logic nightmare for my feeble brain. Got it on the INTO clause; tks for the tip. I know I have work to do to tighten up the core code in various places. The schema's quite large; I'll post it somewhere soon. Martin > I think you should take a closer look at Greg's function. It is uses > lfts as parameters in the function mainly just to make the function > implementation independent; I was able to easily adapt it to my schema > which uses unique id's for each object in the tree hierarchy. > > After looking your function over, I also have some concerns about moving > children to new parents with lft and rgt smaller than the child, because > the math is different depending on which way your moving in the tree. > It's possible that your use of treeid's and universe id's makes up for > this, though it's a little hard to discern without seeing the schema, > perhaps you can post schema and some test data? > > I'm also curious how many nodes you have in your tree, and at how many > levels. It seems like your function would have performance issues over > large trees since it requires 3 select statements, 3 updates, and a lock > table. Compare this with Greg's function which requires 2 selects and 1 > update, with no lock. > > As a final note, you might want to rewrite your select statements like: > SELECT > rgt, universeid, treeid > FROM > list_objects > WHERE > objid_auto=t_newparent > INTO > newparentrgt, newparentuid, newparenttid; > > I think it's more readable and probably a little more efficient since > you are doing less variable assignment. > > Robert Treat > > On Tue, 2002-11-26 at 00:13, Martin Crundall wrote: >> I'm not sure that keying off lft is safe in a multi-user environment. >> I opted to create and use an objid on the tree definition table, since >> its identity is static. I also found that when trees get active, >> allowing for tree IDs increased operation speed quite a bit (i >> actually push this to two levels--a 'universe id' and then a 'tree >> id'). Here's my version. Clearly not as elegantly written, but >> nothing's gone awry yet. >> >> -- >> --- >> --Title: trackmyproject_tree_move() >> -- Function: moves a tree branch in the hierarchy from one parent to >> -- another. >> --parms: srcobj the branch/object to be moved >> -- newparentthe new parent for the object to be moved -- >> Returns: zero >> -- >> --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
