[SQL] after delete trigger behavior

[Russell Simpkins]

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] after delete trigger behavior

[Russell Simpkins]

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

[Russell Simpkins]

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

[Russell Simpkins]

After delete worked and the a foreach execute update seems to work best. 
Below is a satisfactory test set.


-- create test table
CREATE TABLE test1 (
a int,
b int,
c int);
-- create resort function
CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS '
DECLARE
  eachrow RECORD;
  innerrow RECORD;
  sort INT := 0;
BEGIN
   sort := 0;
  FOR eachrow IN EXECUTE ''SELECT * FROM portfolio.test1 WHERE a = '' || 
OLD.a LOOP

  IF eachrow.b != OLD.b THEN
  EXECUTE ''UPDATE portfolio.test1 SET c = '' || sort || '' WHERE a 
= '' || eachrow.a || '' AND b = '' || eachrow.b || ;

  sort := sort +1;
  END IF;
  END LOOP;
  RETURN OLD;
END;
' language 'plpgsql';

-- create trigger
CREATE TRIGGER u_test1 AFTER DELETE ON portfolio.test1 FOR EACH ROW EXECUTE 
PROCEDURE resort_test1();


-- sample 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);

-- test delete
delete from test1 where b = 2 or b = 4;
-- view test results
select * from test1 order by a, b, c;



---(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] Grouping Too Closely

[Russell Simpkins]

I'm not sure if this is the best thing to do in all occasions, but I have 
found a great speed increase using unions over group by.


select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 = ( select 
min(seq1) from mytable);

union
select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 = ( select 
min(seq1) from mytable);

order by fkey, uid, seq2;

the union clause with remove your duplicates for you as you were doing with 
your group by.


using min on large tables can cause problems. you may want to do your select 
min(seq1) from mytable or even have a trigger function after insert/update 
that checks the new value against the current lowest stored in another 
table.


not sure if this helps, but i hope it does.

russ



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] ORDER records based on parameters in IN clause

[Russell Simpkins]

>> when I say
>> select * from table where id IN (2003,1342,799, 1450)
>> I would like the records to be ordered as 2003, 1342, 799, 1450.
>Just say:
>select * from table where id IN (2003,1342,799, 1450) ORDER BY id;
>If that doesn't work, you will have to be more specific and send us the
exact query.

Order by id will not do what you want, but this should.
Select * from table where id = 2003;
Union all
Select * from table where id = 1342;
Union all
Select * from table where id = 799;
Union all
Select * from table where id = 1450;

---(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] ORDER records based on parameters in IN clause

[Russell Simpkins]

fair enough. but a simple order by id would never work.


From: Michael Fuhr <[EMAIL PROTECTED]>
To: Russell Simpkins <[EMAIL PROTECTED]>
CC: pgsql-sql@postgresql.org
Subject: Re: [SQL] ORDER records based on parameters in IN clause
Date: Wed, 29 Jun 2005 05:57:23 -0600

On Wed, Jun 29, 2005 at 07:19:22AM -0400, Russell Simpkins wrote:
>
> Order by id will not do what you want, but this should.
> Select * from table where id = 2003;
> Union all
> Select * from table where id = 1342;
> Union all
> Select * from table where id = 799;
> Union all
> Select * from table where id = 1450;

Note that the semicolons should be omitted everywhere except for
at the end of the entire query.  Also, although the implementation
might happen to return rows in that order, the documentation states
that it's not guaranteed to:

http://www.postgresql.org/docs/8.0/static/queries-union.html

"UNION effectively appends the result of query2 to the result of
query1 (although there is no guarantee that this is the order in
which the rows are actually returned)."

As the documentation states elsewhere and as the SQL standard says,
without ORDER BY rows are returned in an unspecified order.  The
above query works by accident, not by design, so although it might
work today there's no guarantee that it'll work tomorrow.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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




---(end of broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Number of rows in a cursor ?

[Russell Simpkins]

[EMAIL PROTECTED] wrote:

You cannot count the number of rows in a cursor, unfortunately. I recently 
ran in to this problem.


How sad, then I have to repeat the query, first for counting and last for 
data fetch :-(


/BL



If you need a count, why not just execute one of the methods to get a count. 
i.e.e select count(id) ... 


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] SQLException - SET AUTOCOMMIT TO OFF is no longer supported

[Russell Simpkins]

 

  
  16:09:37,093 WARN  
  [TransactionImpl] XAException: tx=TransactionImpl:XidImpl [FormatId=257, 
  GlobalId=dinesh//1, BranchQual=] 
  errorCode=XA_UNKNOWN(0)
  org.jboss.resource.connectionmanager.JBossLocalXAException: 
  Error trying to start local tx: ; - nested throwable: 
  (org.jboss.resource.JBossResourceException: 
  SQLException; 
  - nested throwable: (java.sql.SQLException: ERROR:  SET AUTOCOMMIT TO OFF 
  is no longer supported
  ))
      
  at 
  org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.start(TxConnectionManager.java:654)
      
  at 
  org.jboss.tm.TransactionImpl.startResource(TransactionImpl.java:1196)
   
      
  at 
  org.jboss.tm.TransactionImpl.enlistResource(TransactionImpl.java:649)
   
  ThanksDinesh 
  Pandey

Re: [SQL] SQLException - SET AUTOCOMMIT TO OFF is no longer supported

[Russell Simpkins]

 

  
  I have created datasource but 
  getting this error on JBOSS startup.
   
  Using: 
  PostgreSQL 8.0
  JBOSS: 
  JBOSS-3.2.6
   
  16:09:37,093 WARN  
  [TransactionImpl] XAException: tx=TransactionImpl:XidImpl [FormatId=257, 
  GlobalId=dinesh//1, BranchQual=] 
  errorCode=XA_UNKNOWN(0)
  org.jboss.resource.connectionmanager.JBossLocalXAException: 
  Error trying to start local tx: ; - nested throwable: 
  (org.jboss.resource.JBossResourceException: 
  SQLException; 
  - nested throwable: (java.sql.SQLException: ERROR:  SET AUTOCOMMIT TO OFF 
  is no longer supported
  ))
      
  at 
  org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.start(TxConnectionManager.java:654)
      
  at 
  org.jboss.tm.TransactionImpl.startResource(TransactionImpl.java:1196)
   
      
  at 
  org.jboss.tm.TransactionImpl.enlistResource(TransactionImpl.java:649)
   
  ThanksDinesh 
  Pandey
I think you need to get a 
more up to date version of your JDBC 
Driver

Re: [SQL] Tidying values on variable instantiation

[Russell Simpkins]

Desired Outcome(s):
* I would like to have the convenience of declaring a column that obeys
 a constraint (similar to using a domain), but allows a "tidy-up" as the
 value is created BEFORE asserting the constraint.  This *might* be
 termed a "domain trigger".  (Perhaps even a WORM is possible!).
* I would like to able to declare columns as
 "trimmed_varchar(n)".
* I'd like to be able to use the same approach for other "weak domains".


I'm not sure these any easier way to do this than with tirggers.

If the daunting task would writing a large number of triggers, I would write 
sql or php to generate all the triggers. Remember that you can introspect 
the db using the system catalogs. I've had a lot of good results generating 
triggers and sql using Middlegen and Velocity.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Help with multistage query

[Russell Simpkins]

 

  
  I have a perl script that issues a 
  series of SQL statements to perform some queries.  The script works, but 
  I believe there must be a more elegant way to do this.
   
  The simplified queries look like 
  this:
   
  SELECT id FROM t1 WHERE 
  condition1;   ;returns about 2k records which are stored in 
  @idarray
   
  foreach $id (@idarray) 
  {
     SELECT x FROM t2 
  WHERE id=$id;   ; each select returns about 100 records which are 
  saved in a perl variable
  }

how about 
select t1.id from t1, t2 where t1.id = t2.id and 
t2.id = x

Re: [SQL] Primary and Foreign Key?

[Russell Simpkins]

This is valid ddl to accomplish what you wish.

create table peoplegroups {
peopleid int not null,
groupid int not null,
primary key (peopleid, groupid),
foreign key (peopleid) references people,
foreign key (groupid) references group
}

Check the docs for other options etc.


From: "Announce" <[EMAIL PROTECTED]>
To: "PostgreSQL SQL List" 
Subject: [SQL] Primary and Foreign Key?
Date: Thu, 22 Sep 2005 20:13:35 -0500

I have the following related tables:

PEOPLE
--
peopleid pkey,
name,
etc


GROUPS
-
groupid pkey,
description,
etc


PEOPLEGROUPS
---
peopleid pkey/fkey,
groupid pkey/fkey


What is the CORRECT way (in Postgres) to define the PEOPLEGROUPS table so
that it has both the double primary key AND still acts as a foreign key for
people.peopleid and groups.groupid? Can i specify both or is this not
necessary? Any suggestions would be appreciated.

Thanks!

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005


---(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] Scripting GRANT on functions

[Russell Simpkins]

You could continue with this function, with an 
additional cursor to get the parameters for the function. If this is a one off 
thing, that you just need to do once, you could use pg_dump to get the create 
function statements and then simply alter them with an re in your favorite 
editor. 
 
You should consider using groups. Then you could 
just add the new user to the existing group that has execute 
access.
 
hope that helps
 
Russ

  - Original Message - 
  From: 
  Stewart Ben (RBAU/EQS4) * 
  To: pgsql-sql@postgresql.org 
  Sent: Thursday, October 06, 2005 1:27 
  AM
  Subject: [SQL] Scripting GRANT on 
  functions
  Is there any easy way to script granting privileges to a number 
  offunctions? I've got as far as the following code before realising 
  thatI'll need to pass in the arguments, and the arguments are stored as 
  OIDsin pg_proc.Is there any easy way, such as GRANT  FUNCTION 
  OID 12345?---CODE---DECLARE  curs 
  REFCURSOR;  funcname VARCHAR;BEGIN  OPEN foo 
  FOR    SELECT proname FROM 
  pg_proc WHERE proname LIKE 
  'tr\\_%'    OR proname LIKE 
  'tt\\_%'    OR proname LIKE 
  'v\\_%'    OR proname LIKE 
  'vui\\_%';  FETCH curs INTO funcname;  WHILE FOUND 
  LOOP    FETCH curs INTO funcname;    
  EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO myuser';  
  END LOOP;  CLOSE curs;END;---END 
  CODE---Best regards,Ben Stewart--Robert Bosch 
  (Australia) Pty. Ltd.Engineering Quality Services, Software Engineer 
  (RBAU/EQS4)Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIAmailto:[EMAIL PROTECTED]http://www.bosch.com.au/---(end 
  of broadcast)---TIP 4: Have you searched our list 
  archives?   
  http://archives.postgresql.org

Re: [SQL] Design problemi : using the same primary keys for inherited objects.

[Russell Simpkins]

- Original Message - 

  i've got 
  a strange design question to ask you.It's something I couldn't answer to 
  while feeling confusely it was anabsolutely BAD thing to do.For 
  our application, we have developed our own framework which sits ontop of 
  PostgreSQL. It uses object programming and implements heritage.Inherited 
  objects use heritage links in the framework and relation linksin 
  PostgreSQL (Search me why it doesn't use heritage in PostgreSQL 
  !?).I've got this thing :An object A inherits from an object B, 
  which inherits from a object C,which inherits from an object D.One of 
  my colleagues proposed that we don't use serial (integer +sequence) 
  primary keys for these objects, but that we use the very sameinteger 
  primary keys.That is : the instance A would use the id 12343, and the 
  instance B thesame id 12343 and the instance C the same id 12343 and the D 
  instance thesame id 12343.It's possible as two instances of an 
  object never inherit from a sameinstance of another object.The id 
  seems to me absolutely bad, but I wouldn't know how to 
  phrasewhy.Any suggestion ?Thanks in 
  advance,David.

Most of the inheritance i've seen done in databases 
retain the parent primary as a foreign key and a primary key. That being 
said, only you and your team can decide if more than one object will extend a 
base class. If you were doing something more like this
 
person -> sweepstakes entry
 
to model a sweepsakes entry is a person, and 
you allow a person to enter a sweepstakes more than once, but to enter a contest 
the user must provide a unique email address, then you could not just use a 
foreign key as the primary key in sweepstakes, since the primary key would 
disallow multiple entries in sweepstakes entry, you would then use a serial 
data type in both person and sweepstakes along with the foriegn key in 
sweepstakes from person.
 
The answer depends on the need. Hope that 
helps.
 
Russ

Re: [SQL] Design problem : using the same primary keys for inherited objects.

[Russell Simpkins]

Thanks Russ, but well...
It doesn't help me a lot. Our needs seem to allow that we use an id as
primary key and foreign key at the same time.
What i fear more is that it be against a good database design practice,
because leading to potential problems.

I give a clearer example :

CREATE TABLE actor (
id_actor serial PRIMARY KEY,
arg1 type1,
arg2 type2
)

CREATE TABLE person (
id_person INTEGER PRIMARY KEY REFERENCES actor,
arg3 type3,
arg4 type4
)

Don't you think it is a BAD design ?
If it isn't, well, it will expand my database practices.


That is perfectly valid. Only, I would argue that an actor is a person.

What I was offering was dealing with issues where more then one actor could 
be the same person. Given your design, a person could only be one actor. If 
that is true, no more discussion is needed. If that is not true, then one 
way to deal with that is to make compound primary keys in your actor table.


table person (
person_id serial primary key,
name varchar(20));

table actor(
person_id foreign key references person,
role varchar(20),
primary key ( person_id, role )
);

would then allow a person to be more then on actor based on role.



---(end of broadcast)---
TIP 1: 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