Re: [SQL] after delete trigger behavior
Actually, I had a larger script that did exactly what you propose. However I started to think that a profecient where clause would do the trick. In my mapping table, a and b are primary keys. a_id, b_id, c_sort_order. a_id is the parent and b_id is the child for my purposes, so if a_id is deleted then all relations are deleted, but if b_id is deleted, then there stands a chance for an index order in c_sort_order appearing. Rather then selecting and looping, I thought I could short circut the procedure by saying update mapping set c_sort_order = c_sort_order - 1 where a_id = OLD.a_id and c_sort_order > OLD.c_sort_order. My thought was that there was no real reason to select and loop as this function would perform the resort for this series of a_id mappings. It seems to me that your code does the exact same thing, only in a longer form. Also there is no need to do anyone less then sort_order since sort_order will be 0 to n-1 where n is the total number of mappings. a_id, b_id, c_sort_order 1, 1, 0 1, 2, 1 1, 3, 2 1, 4, 3 if you delete where b_id = 1 then you want to update where b_id = 2, 3 and 4 since a_id = 1 and c_sort_order is greater then 0. Again, the issue was that postgres only executes one delete. After changing the trigger to an after delete, I was able to delete all and even delete multiple rows. I now have one small problem that I will have to test more on. Using my where statement, if i delete from table where b_id = 2 or b_id = 3, c_sort_order becomes out of sync. I will do another test and see if the select loop fairs any better. I have a real-world function like so: CREATE OR REPLACE FUNCTION cms.resort_content_flash() RETURNS TRIGGER AS ' DECLARE eachrow RECORD; innerrow RECORD; sort INT := 0; BEGIN FOR eachrow IN EXECUTE ''SELECT * FROM cms.content_flash WHERE flash_id = '' || OLD.flash_id LOOP sort := 0; FOR innerrow IN EXECUTE ''SELECT * FROM cms.content_flash WHERE content_id = '' || eachrow.content_id || '' ORDER BY sort_order'' LOOP IF innerrow.flash_id != OLD.flash_id THEN EXECUTE ''UPDATE cms.content_flash SET sort_order = '' || sort || '' WHERE content_id = '' || innerrow.content_id || '' AND flash_id = '' || innerrow.flash_id || ; sort := sort +1; END IF; END LOOP; END LOOP; RETURN OLD; END; ' language 'plpgsql'; that I will rejigger to the test table and try out. Thanks for the input. From: Tom Lane <[EMAIL PROTECTED]> To: Stephan Szabo <[EMAIL PROTECTED]> CC: Russell Simpkins <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org Subject: Re: [SQL] after delete trigger behavior Date: Wed, 22 Jun 2005 15:46:41 -0400 Stephan Szabo <[EMAIL PROTECTED]> writes: > Is there anything we have right now that will handle this kind of thing > without requiring either updating all the counts after a deletion in a > statement trigger or once per row updating all the counts for records with > the same "a" (doing something like make a sequence and using it in a > subselect matching keys)? The best thing I can think of is your first idea, ie, renumbering all the rows in a statement-level AFTER DELETE trigger. Something like (untested) DECLARE rec record; n integer := 1; BEGIN FOR rec IN SELECT * FROM table WHERE <> ORDER BY sort_order LOOP IF rec.sort_order != n THEN UPDATE table SET sort_order = n WHERE <>; END IF; n := n + 1; END LOOP; END; Ugly as this is, it's at least linear in the number of rows to be changed; the originally proposed trigger was O(N^2) in the number of rows affected, and would surely be intolerably slow for multiple deletes in a reasonably sized table. Given an index on the grouping columns plus sort_order, it could even be reasonably fast (don't forget to make the ORDER BY match the index). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] after delete trigger behavior
Stephan Szabo <[EMAIL PROTECTED]> writes: > Is there anything we have right now that will handle this kind of thing > without requiring either updating all the counts after a deletion in a > statement trigger or once per row updating all the counts for records with > the same "a" (doing something like make a sequence and using it in a > subselect matching keys)? The best thing I can think of is your first idea, ie, renumbering all the rows in a statement-level AFTER DELETE trigger. Something like (untested) DECLARE rec record; n integer := 1; BEGIN FOR rec IN SELECT * FROM table WHERE <> ORDER BY sort_order LOOP IF rec.sort_order != n THEN UPDATE table SET sort_order = n WHERE <>; END IF; n := n + 1; END LOOP; END; Ugly as this is, it's at least linear in the number of rows to be changed; the originally proposed trigger was O(N^2) in the number of rows affected, and would surely be intolerably slow for multiple deletes in a reasonably sized table. Given an index on the grouping columns plus sort_order, it could even be reasonably fast (don't forget to make the ORDER BY match the index). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] encoding question
Hi, I am trying to convert from SQL_ASCII to UNICODE. I have a program that will read from a table in one database and write to a table in a different database. I am hoping this all I need do (One data base is SQL_ASCII and the other is UNICODE). I get a byte sequence error writing. I tried using encoding =UNICODE and did not get the error, but the data looked different stored in the field, so I am guessing it messed it up. I am using NpgsqlConnection. Any one know the proper way to convert using a .net app? Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
Re: [SQL] after delete trigger behavior
I suspect that if you read the spec carefully it would want a "triggered data change violation" error raised here. My advice is not to use a BEFORE trigger for this. What would you recommend then. I am using Hibernate in my java application and if the sort_order column (in this example column.c) gets out of sync, then the Hibernate application fails. In hind site, the after delete trigger would be the better choice. The trigger works fine for one row deletes, but I noticed that you can't even do multiple row deletes with the trigger. Postgres only executes one row delete. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] after delete trigger behavior
On Wed, 22 Jun 2005, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW > >> EXECUTE > >> PROCEDURE resort_test1(); > > > I think this will work in an after delete trigger, but not in a before > > delete trigger (and seems to in my tests). I'm not sure what the spec says > > about the visibility of rows in cases like this. > > Well, the actual effect is that the first trigger's UPDATE changes all > the rows that the DELETE might later delete, thus overriding the delete. > (A query cannot modify rows already modified by commands started later > in the same transaction, such as commands issued by triggers fired by > the query itself.) > > Depending on the order that the DELETE hits the rows in, there might be > more than one row that can get processed before the UPDATEs have touched > all remaining rows, so this is all pretty messy and not to be relied on. > > I suspect that if you read the spec carefully it would want a "triggered > data change violation" error raised here. My advice is not to use a > BEFORE trigger for this. > > Even an AFTER trigger will have some pretty significant problems with > this, I'm afraid, because of the uncertainty about the order in which > the rows are deleted (and hence the order in which the trigger instances > fire). For instance, suppose you delete the rows with c=1 and c=2, and > they get visited in that order. The UPDATE for c=1 will update the row > currently having c=3 to c=2 ... whereupon that row will NOT be seen as > an update candidate by the UPDATE for c=2. (You could work around that > case by using ">= OLD.c" instead of "> OLD.c", but it could still fail > with more than 2 rows being deleted.) The proposed trigger only works > cleanly if the rows are deleted in decreasing order of c, and there's no > very easy way to guarantee that. Is there anything we have right now that will handle this kind of thing without requiring either updating all the counts after a deletion in a statement trigger or once per row updating all the counts for records with the same "a" (doing something like make a sequence and using it in a subselect matching keys)? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] after delete trigger behavior
Stephan Szabo <[EMAIL PROTECTED]> writes: >> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE >> PROCEDURE resort_test1(); > I think this will work in an after delete trigger, but not in a before > delete trigger (and seems to in my tests). I'm not sure what the spec says > about the visibility of rows in cases like this. Well, the actual effect is that the first trigger's UPDATE changes all the rows that the DELETE might later delete, thus overriding the delete. (A query cannot modify rows already modified by commands started later in the same transaction, such as commands issued by triggers fired by the query itself.) Depending on the order that the DELETE hits the rows in, there might be more than one row that can get processed before the UPDATEs have touched all remaining rows, so this is all pretty messy and not to be relied on. I suspect that if you read the spec carefully it would want a "triggered data change violation" error raised here. My advice is not to use a BEFORE trigger for this. Even an AFTER trigger will have some pretty significant problems with this, I'm afraid, because of the uncertainty about the order in which the rows are deleted (and hence the order in which the trigger instances fire). For instance, suppose you delete the rows with c=1 and c=2, and they get visited in that order. The UPDATE for c=1 will update the row currently having c=3 to c=2 ... whereupon that row will NOT be seen as an update candidate by the UPDATE for c=2. (You could work around that case by using ">= OLD.c" instead of "> OLD.c", but it could still fail with more than 2 rows being deleted.) The proposed trigger only works cleanly if the rows are deleted in decreasing order of c, and there's no very easy way to guarantee that. 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])
Re: [SQL] after delete trigger behavior
On Wed, 22 Jun 2005, Russell Simpkins wrote: > Hello, > > I have created a trigger function to update the sort_order column of a > mapping table. I have table a that has a many to many relation ship with > table b that is mapped as a_b where a_id, and b_id are the pk columns and > there is a sort_order column. Since a_b is a mapping table there are > foreign key constraints with a cascade option. So, if i delete an entry from > b, an entry in a_b is deleted. What I want though is for the sort_order > column to be updated so that all entries of a_b for a given a entry remain > in order. > > a_id, b_id, sort_order > 1, 2, 0 > 1, 3, 1 > 1, 4, 2 > 1, 7, 3 > > if I delete b_id = 4 then the b_id 7 should get a sort order of 2. I created > an after delete trigger and the trigger works just fine when i delete only > one row, but if I delete all using "delete from a_b" I am only able to > delete one row. Here is an example: > - > -- a test table > CREATE TABLE test1 ( > a int, > b int, > c int); > - > -- a resort function > CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS ' > DECLARE >eachrow RECORD; >innerrow RECORD; >sort INT := 0; > BEGIN >EXECUTE ''UPDATE portfolio.test1 set c = c - 1 where a = '' || OLD.a || > '' and c > '' || OLD.c; >RETURN OLD; > END; > ' language 'plpgsql'; > - > -- the trigger > CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE > PROCEDURE resort_test1(); I think this will work in an after delete trigger, but not in a before delete trigger (and seems to in my tests). I'm not sure what the spec says about the visibility of rows in cases like this. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] after delete trigger behavior
Hello, I have created a trigger function to update the sort_order column of a mapping table. I have table a that has a many to many relation ship with table b that is mapped as a_b where a_id, and b_id are the pk columns and there is a sort_order column. Since a_b is a mapping table there are foreign key constraints with a cascade option. So, if i delete an entry from b, an entry in a_b is deleted. What I want though is for the sort_order column to be updated so that all entries of a_b for a given a entry remain in order. a_id, b_id, sort_order 1, 2, 0 1, 3, 1 1, 4, 2 1, 7, 3 if I delete b_id = 4 then the b_id 7 should get a sort order of 2. I created an after delete trigger and the trigger works just fine when i delete only one row, but if I delete all using "delete from a_b" I am only able to delete one row. Here is an example: - -- a test table CREATE TABLE test1 ( a int, b int, c int); - -- a resort function CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS ' DECLARE eachrow RECORD; innerrow RECORD; sort INT := 0; BEGIN EXECUTE ''UPDATE portfolio.test1 set c = c - 1 where a = '' || OLD.a || '' and c > '' || OLD.c; RETURN OLD; END; ' language 'plpgsql'; - -- the trigger CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE PROCEDURE resort_test1(); -- -- dummy data insert into test1 values(1,1,0); insert into test1 values(1,2,1); insert into test1 values(1,3,2); insert into test1 values(1,4,3); insert into test1 values(1,5,4); insert into test1 values(2,1,0); insert into test1 values(2,2,1); insert into test1 values(2,3,2); insert into test1 values(2,4,3); insert into test1 values(2,5,4); -- delete that works delete from test1 where b = 3; -- review results select c from test1 where a = 1 order by c; -- delete all delete from test1; note that it will only delete one row. Is this by design? Is there something I can do to remedy this behavior? I would expect to have all rows delete and not just the first one. Any help is appreciated. Russ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Alias to a type
Keep in mind, though. Using a DOMAIN in some definition 'seals' the domain. Yo can't change the domain unless you drop all dependent objects |-Original Message- |From: Veikko Mäkinen [mailto:[EMAIL PROTECTED] |Sent: Mittwoch, 22. Juni 2005 15:14 |To: pgsql-sql@postgresql.org |Subject: Re: [SQL] Alias to a type | | |Richard Huxton wrote: |> Veikko Mäkinen wrote: |> |>> Hey, |>> |>> Is it possible to create a new type as an alias to a |pre-defined type? |>> I use "USERID varchar(20)" in almost every table I have I'd like to |>> make an alias for that type eg. |> |> |> CREATE DOMAIN username_string AS varchar(20); |> |> Test it with your client applications though, make sure they |cope. Some |> don't cope well with user-defined types. | | |Splendid, thank you all :) I'm quite sure loosely typed PHP can cope |with my user-defined types ;) | |But now I need a better modeling tool because Azzurri Clay 1.1 |(Eclipse |plug-in) doesn't let me define new types... Any suggestions? SQL DDL |generation is the only demand I really have. | | |-veikko | | |---(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 | ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Alias to a type
Richard Huxton wrote: Veikko Mäkinen wrote: Hey, Is it possible to create a new type as an alias to a pre-defined type? I use "USERID varchar(20)" in almost every table I have I'd like to make an alias for that type eg. CREATE DOMAIN username_string AS varchar(20); Test it with your client applications though, make sure they cope. Some don't cope well with user-defined types. Splendid, thank you all :) I'm quite sure loosely typed PHP can cope with my user-defined types ;) But now I need a better modeling tool because Azzurri Clay 1.1 (Eclipse plug-in) doesn't let me define new types... Any suggestions? SQL DDL generation is the only demand I really have. -veikko ---(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] Alias to a type
O Sean Davis έγραψε στις Jun 22, 2005 : > > On Jun 22, 2005, at 7:42 AM, Achilleus Mantzios wrote: > > > O Veikko MΞ΄kinen ΞΞ³ΟΞ±ΟΞ΅ ΟΟΞΉΟ Jun 22, 2005 : > > > >> Hey, > >> > >> Is it possible to create a new type as an alias to a pre-defined > >> type? I > >> use "USERID varchar(20)" in almost every table I have I'd like to make > >> an alias for that type eg. > >> > >> > >>create type myschema.useridtype as varchar(20); > >> > >> > > > > Try something like > > > > CREATE DOMAIN my_integer AS INTEGER; > > Just for my own edification, does creating a "simple" domain like this > then require a whole set of functions for indexing, etc., like other > more complex user-defined types, or will postgres "do the right thing"? In the above example you may safely consider indexing columns of type my_integer as indexing INTEGERs. > > Thanks, > Sean > > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Alias to a type
Veikko Mäkinen wrote: Hey, Is it possible to create a new type as an alias to a pre-defined type? I use "USERID varchar(20)" in almost every table I have I'd like to make an alias for that type eg. CREATE DOMAIN username_string AS varchar(20); Test it with your client applications though, make sure they cope. Some don't cope well with user-defined types. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Alias to a type
On Wed, Jun 22, 2005 at 08:04:39 -0400, Sean Davis <[EMAIL PROTECTED]> wrote: > > > Just for my own edification, does creating a "simple" domain like this > then require a whole set of functions for indexing, etc., like other > more complex user-defined types, or will postgres "do the right thing"? No, the underlying type's class is still used. You don't need to create new comparison or type conversion functions. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Alias to a type
On Jun 22, 2005, at 7:42 AM, Achilleus Mantzios wrote: O Veikko Mδkinen έγραψε στις Jun 22, 2005 : Hey, Is it possible to create a new type as an alias to a pre-defined type? I use "USERID varchar(20)" in almost every table I have I'd like to make an alias for that type eg. create type myschema.useridtype as varchar(20); Try something like CREATE DOMAIN my_integer AS INTEGER; Just for my own edification, does creating a "simple" domain like this then require a whole set of functions for indexing, etc., like other more complex user-defined types, or will postgres "do the right thing"? Thanks, Sean ---(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] Alias to a type
O Veikko Mδkinen έγραψε στις Jun 22, 2005 : > Hey, > > Is it possible to create a new type as an alias to a pre-defined type? I > use "USERID varchar(20)" in almost every table I have I'd like to make > an alias for that type eg. > > >create type myschema.useridtype as varchar(20); > > Try something like CREATE DOMAIN my_integer AS INTEGER; > I might have to alter the type some day and this way I'd have to just > re-define the alias. Can this be achieved by creating a new type with > CREATE TYPE? What are the input/output functions in the CREATE TYPE > definition? > > Thanks. > > > -veikko > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Alias to a type
Hey, Is it possible to create a new type as an alias to a pre-defined type? I use "USERID varchar(20)" in almost every table I have I'd like to make an alias for that type eg. create type myschema.useridtype as varchar(20); I might have to alter the type some day and this way I'd have to just re-define the alias. Can this be achieved by creating a new type with CREATE TYPE? What are the input/output functions in the CREATE TYPE definition? Thanks. -veikko ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
