Обсуждение: Cascading deletions does not seem to work inside PL/PGSQL functions.

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
returnsrecord.        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.

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).

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.

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.

 > 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 themaster 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.

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.