[BUGS] Cascading updates run seperately

[Allan Wang]

te cascade on delete set null, body int references 
items(itemid) on update cascade on delete set null);
NOTICE:  CREATE TABLE will create implicit sequence "players_playerid_seq1" for 
serial column "players.playerid"
CREATE TABLE
allan=# insert into players
allan=# \d players
   Table "public.players"
  Column  |  Type   |   Modifiers
--+-+
 playerid | integer | not null default 
nextval('public.players_playerid_seq1'::text)
 head | integer |
 body | integer |
Foreign-key constraints:
"players_body_fkey" FOREIGN KEY (body) REFERENCES items(itemid) ON UPDATE 
CASCADE ON DELETE SET NULL
"players_head_fkey" FOREIGN KEY (head) REFERENCES items(itemid) ON UPDATE 
CASCADE ON DELETE SET NULL

allan=# insert into players default values;
INSERT 0 1
allan=# \d items
  Table "public.items"
  Column  |  Type   | Modifiers
--+-+
 itemid   | integer | not null default nextval('public.items_itemid_seq1'::text)
 playerid | integer |
Indexes:
"items_pkey" UNIQUE, btree (itemid)

allan=# insert into items (playerid) values ((select playerid from players 
limit 1));
INSERT 0 1
allan=# insert into items (playerid) values ((select playerid from players 
limit 1));
INSERT 0 1
allan=# update players set head=(select itemid from items order by itemid asc 
limit 1);
UPDATE 1
allan=# update players set body=(select itemid from items order by itemid desc 
limit 1);
UPDATE 1
allan=# select * from players;
 playerid | head | body
--+--+--
1 |1 |2
(1 row)

allan=# select * from items;
 itemid | playerid
+--
  1 |1
  2 |1
(2 rows)

allan=# begin;
BEGIN
allan=# delete from items;
DELETE 2
allan=# rollback;
ROLLBACK
allan=# select * from players;
 playerid | head | body
--+--+--
1 |1 |2
(1 row)

allan=# begin;
BEGIN
allan=# delete from items;
DELETE 2
allan=# select * from players;
 playerid | head | body
--+--+--
1 | NULL | NULL
(1 row)

allan=# select * from items;
 itemid | playerid
+--
(0 rows)

allan=# rollback;
ROLLBACK
allan=# select * from items;
 itemid | playerid
+--
  1 |1
  2 |1
(2 rows)

allan=# select * from players
allan-# ;
 playerid | head | body
--+--+--
1 |1 |2
(1 row)

allan=# update players set bo
allan=# begin;
BEGIN
allan=# update players set body=1;
UPDATE 1
allan=# delete from items;
ERROR:  insert or update on table "players" violates foreign key constraint 
"players_body_fkey"
DETAIL:  Key (body)=(1) is not present in table "items".
CONTEXT:  SQL statement "UPDATE ONLY "public"."players" SET "head" = NULL WHERE 
"head" = $1"
allan=# rollback;l
ROLLBACK
allan-#
allan=# begin;
BEGIN
allan=# select * from items;
 itemid | playerid
+--
  1 |1
  2 |1
(2 rows)

allan=# select * from players;
 playerid | head | body
--+--+--
1 |1 |2
(1 row)

allan=# update players set head=2, body=1;
UPDATE 1
allan=# delete from items;
ERROR:  insert or update on table "players" violates foreign key constraint 
"players_head_fkey"
DETAIL:  Key (head)=(2) is not present in table "items".
CONTEXT:  SQL statement "UPDATE ONLY "public"."players" SET "body" = NULL WHERE 
"body" = $1"

Allan Wang


---(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

[BUGS] extra columns in intermediate nodes not being removed by top level of executor

[Allan Wang]

I'm using 8.1 from CVS head of about two days ago.

Extra columns seem to be on sum(plays.length), videos.path, videoid

create or replace view niceplays as SELECT count(*) AS plays,
summary("substring"(v.path, '[^/]+$'::text), 50) AS filename,
avg(p.length)::interval(0) AS avg, sum(p.length)::interval(0) AS sum,
(( SELECT now() - plays.playtimestamp
   FROM plays
  WHERE plays.videoid = v.videoid
  ORDER BY plays.playid DESC
 LIMIT 1))::interval(0) AS lastplay
   FROM plays p
   LEFT JOIN videos v USING (videoid)
  WHERE p.length <> '00:00:00'::interval AND v.path ~~ 
'/home/allan/TransGaming_Drive/libp/%'::text
  GROUP BY v.path, v.videoid
  ORDER BY sum(p.length::interval(0)) DESC;

allan=# select * from niceplays;
 plays | filename |   avg|   sum| lastplay
---+--+--+--+-
13 |  | 00:06:07 | 01:19:26 | 20 days 18:33:51
 8 |  | 00:07:18 | 00:58:23 | 17 days 23:49:38
 8 |  | 00:04:13 | 00:33:44 | 31 days 22:55:20
 3 |  | 00:10:56 | 00:32:47 | 17 days 00:04:18
 7 |  | 00:04:34 | 00:32:00 | 31 days 22:55:36
 6 |  | 00:05:04 | 00:30:22 | 17 days 23:46:32
[...]
(868 rows)

allan=# select * from niceplays order by sum desc limit 3;
 plays | filename |   avg|   sum| lastplay |  | |
---+--+--+--+--+--+-+-
13 |  | 00:06:07 | 01:19:26 | 20 days 18:35:59 | 01:19:26 | |   43
 8 |  | 00:07:18 | 00:58:23 | 17 days 23:51:47 | 00:58:23 | | 1988
 8 |  | 00:04:13 | 00:33:44 | 31 days 22:57:29 | 00:33:43 | | 1961

Allan Wang


---(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