pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCascading deletions does not seem to work inside PL/PGSQL functions.
6+ messages / 2 participants
[nested] [flat]
* Cascading deletions does not seem to work inside PL/PGSQL functions.
@ 2002-07-16 11:25 Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-16 16:11 ` Re: Cascading deletions does not seem to work inside PL/PGSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-16 11:25 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
This problem has been troubling me for quite sometime and
I would be very thankful for your help.
I have included the complete commented script to recreate the problem in
question.
The problem is inside a plpgsql function i do not see the records in the slave tables
getting deleted when i delete the corresponing referenced record from the master table.
But things as expected inside a Transaction at the PSQL prompt.
any help is very much appreciated.
regds
mallah.
-------------------- stuff in test_case.sql -----------------------
-- W A R N I N G
--- scripts will delete tables t_master and t_slave and a function t_test()
---
DROP TABLE t_master; --clean up stuff first.
DROP TABLE t_slave;
CREATE TABLE t_master (id integer primary key);
CREATE TABLE t_slave (id integer references t_master
on delete cascade
on update cascade unique
);
INSERT INTO t_master values (1);
INSERT INTO t_slave values (1);
-- Demonstrate that record in salve table do get
-- deleted when the master record is deleted.
BEGIN work;
delete from t_master where id=1;
select id from t_slave where id=1; -- <-- this selects returns no record.
ROLLBACK;
-- Same stuff tried inside a PL/PGSQL function...
DROP FUNCTION t_test();
CREATE OR REPLACE FUNCTION t_test () RETURNS integer AS '
DECLARE rec RECORD;
BEGIN
DELETE FROM t_master where id=1;
SELECT INTO rec id from t_slave where id=1; -- <-- this selects returns record.
RAISE NOTICE ''id in slave table is %'' , rec.id ;
RETURN 1;
END;
' LANGUAGE 'plpgsql' ;
select t_test();
--------------------------- the end -----------------------------------
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
Attachments:
test_case.sql (text/x-diff, 1.0K) download
-- W A R N I N G
--- scripts will delete tables t_master and t_slave and a function t_test()
---
DROP TABLE t_master; --clean up stuff first.
DROP TABLE t_slave;
CREATE TABLE t_master (id integer primary key);
CREATE TABLE t_slave (id integer references t_master
on delete cascade
on update cascade unique
);
INSERT INTO t_master values (1);
INSERT INTO t_slave values (1);
-- Demonstrate that record in salve table do get
-- deleted when the master record is deleted.
BEGIN work;
delete from t_master where id=1;
select id from t_slave where id=1; -- <-- this selects returns no record.
ROLLBACK;
-- Same stuff tried inside a PL/PGSQL function...
DROP FUNCTION t_test();
CREATE OR REPLACE FUNCTION t_test () RETURNS integer AS '
DECLARE rec RECORD;
BEGIN
DELETE FROM t_master where id=1;
SELECT INTO rec id from t_slave where id=1; -- <-- this selects returns record.
RAISE NOTICE ''id in slave table is %'' , rec.id ;
RETURN 1;
END;
' LANGUAGE 'plpgsql' ;
select t_test();
* Re: Cascading deletions does not seem to work inside PL/PGSQL
2002-07-16 11:25 Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-07-16 16:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-17 07:12 ` Re: Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2002-07-16 16:11 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
On Tue, 16 Jul 2002, Rajesh Kumar Mallah. wrote:
> Hi folks,
>
> This problem has been troubling me for quite sometime and
> I would be very thankful for your help.
>
> I have included the complete commented script to recreate the problem in
> question.
>
> The problem is inside a plpgsql function i do not see the records in the slave tables
> getting deleted when i delete the corresponing referenced record from the master table.
>
> But things as expected inside a Transaction at the PSQL prompt.
It should get deleted, but it won't be deleted until the end of the
user's sql statement (ie, not until after the function has finished).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cascading deletions does not seem to work inside PL/PGSQL functions.
2002-07-16 11:25 Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-16 16:11 ` Re: Cascading deletions does not seem to work inside PL/PGSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-07-17 07:12 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-17 17:09 ` Re: Cascading deletions does not seem to work inside PL/PGSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-17 07:12 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
On Tuesday 16 July 2002 21:41, you wrote:
> On Tue, 16 Jul 2002, Rajesh Kumar Mallah. wrote:
> > Hi folks,
> >
> > This problem has been troubling me for quite sometime and
> > I would be very thankful for your help.
> >
> > I have included the complete commented script to recreate the problem in
> > question.
> >
> > The problem is inside a plpgsql function i do not see the records in the
> > slave tables getting deleted when i delete the corresponing referenced
> > record from the master table.
> >
> > But things as expected inside a Transaction at the PSQL prompt.
>
> It should get deleted, but it won't be deleted until the end of the
> user's sql statement (ie, not until after the function has finished).
Hi thanks for the reply,
Is it a bug? or is it expected ,
what if i do not want to fire sperate delete SQLs for the slave
tables ?
regds
mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cascading deletions does not seem to work inside PL/PGSQL
2002-07-16 11:25 Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-16 16:11 ` Re: Cascading deletions does not seem to work inside PL/PGSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-17 07:12 ` Re: Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-07-17 17:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2002-07-17 17:09 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
On Wed, 17 Jul 2002, Rajesh Kumar Mallah. wrote:
> On Tuesday 16 July 2002 21:41, you wrote:
> > On Tue, 16 Jul 2002, Rajesh Kumar Mallah. wrote:
> > > Hi folks,
> > >
> > > This problem has been troubling me for quite sometime and
> > > I would be very thankful for your help.
> > >
> > > I have included the complete commented script to recreate the problem in
> > > question.
> > >
> > > The problem is inside a plpgsql function i do not see the records in the
> > > slave tables getting deleted when i delete the corresponing referenced
> > > record from the master table.
> > >
> > > But things as expected inside a Transaction at the PSQL prompt.
> >
>
> > It should get deleted, but it won't be deleted until the end of the
> > user's sql statement (ie, not until after the function has finished).
>
> Hi thanks for the reply,
>
> Is it a bug? or is it expected ,
Well, we're not 100% sure it's correct, but it's the expected behavior
unless someone can point to something in SQL99 to make us change it. :)
> what if i do not want to fire sperate delete SQLs for the slave
> tables ?
I'm not sure what you mean by this.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cascading deletions does not seem to work inside PL/PGSQL functions.
@ 2002-07-18 05:42 Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-18 05:42 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
> what if i do not want to fire sperate delete SQLs for the slave
>
> > tables ?
Hi ,
what is mean is that I a have many tables(x,y,z...) which reference the
master table (M).
I want that the records from the tables (x,y,z ect) automatically get deleted
in the function when i delete the recored in the master (M). so that i do not
have to explicitly delete from each of the tables x,y,z, etc.
actually i want to reinsert the records in the table x,y,z as well as M
with some modification.
sorry for my english.
regds
mallah.
> I'm not sure what you mean by this.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cascading deletions does not seem to work inside PL/PGSQL functions.
@ 2002-07-18 06:39 Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-18 06:39 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Thanks for your reply. Stephan.
On Thursday 18 July 2002 12:01, you wrote:
> On Thu, 18 Jul 2002, Rajesh Kumar Mallah. wrote:
> > > what if i do not want to fire sperate delete SQLs for the slave
> > >
> > > > tables ?
> >
> > Hi ,
> >
> > what is mean is that I a have many tables(x,y,z...) which reference the
> > master table (M).
> >
> > I want that the records from the tables (x,y,z ect) automatically get
> > deleted in the function when i delete the recored in the master (M). so
> > that i do not have to explicitly delete from each of the tables x,y,z,
> > etc.
> >
> > actually i want to reinsert the records in the table x,y,z as well as M
> > with some modification.
>
> So you want to do something like:
> delete from M
> ...
> Insert into M,x,y,...
>
> inside the function with the x,y, etc rows
> going away between those two?
>
> Hmm, yeah, that's a case that I don't think you can
> do currently using the on delete cascade and would
> require separate deletes. It'd make sense that you should
> be able to do that, however... It doesn't really work with
> how they're implemented currently, so it'd certainly be
> a while before it'd change in any case.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-07-18 06:39 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-16 11:25 Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-16 16:11 ` Re: Cascading deletions does not seem to work inside PL/PGSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-17 07:12 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-17 17:09 ` Re: Cascading deletions does not seem to work inside PL/PGSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-18 05:42 Re: Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-18 06:39 Re: Cascading deletions does not seem to work inside PL/PGSQL functions. Rajesh Kumar Mallah. <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox