Greetings,
I'm not sure what the correct behavior is here but the observed
behavior seems "wrong" (or at least undesirable).
I have a few tables and a view on one of the tables selecting
entries that may be purged.
My delete statement uses the view to delete data from one of the
tables. Like so:
delete from tab1 where id1 in ( select id from view1 );
Assume that the view doesn't have a field named "id". The select
statement alone would cause an error. However, in this context it
doesn't and the delete statement deletes everything from tab1.
Is this a bug in PostgreSQL or an "As Designed" feature?
Best Regards,
sidster
--
They who would sacrifice freedom for security will have neither.
-Ben Franklin
Working example (with comments) follows:
<example>
<seen_on>
<postgresql version="7.1.2" />
<postgresql version="7.1.3" />
<comments>
I don't yet have access to a newer PostgreSQL build.
</comments>
</seen_on>
<sql>
begin;
create table
ttab_title
(
title_id int4 primary key,
can_delete bool
);
create sequence tseq_title_id;
insert into ttab_title values ( nextval( 'tseq_title_id' ), false );
insert into ttab_title values ( nextval( 'tseq_title_id' ), false );
insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able
insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able
insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able
insert into ttab_title values ( nextval( 'tseq_title_id' ), false );
insert into ttab_title values ( nextval( 'tseq_title_id' ), false );
insert into ttab_title values ( nextval( 'tseq_title_id' ), false );
insert into ttab_title values ( nextval( 'tseq_title_id' ), false );
create view
tview_title
as
select ttab_title.title_id as title_number
from ttab_title
where ttab_title.can_delete = true
;
--
-- Notice the column/field rename from title_id to title_number
create table
ttab_title_selection
(
title_id int4 references ttab_title( title_id ),
ranking int4
-- some other fields ...
);
create sequence tseq_title_rank;
insert into ttab_title_selection
select ttab_title.title_id, nextval( 'tseq_title_rank' ) from ttab_title;
end;
-- Now lets look at this delete statement.
delete
from ttab_title_selection
where title_id in ( select title_id
from tview_title );
--
-- Notice how instead of title_number we made the mistake and used
-- title_id.
--
-- We intended to only delete titles with ids: 3, 4 and 5 but this
-- delete statement deletes all 9 titles!
-- Drop statements for clean up
/*
drop table ttab_title_selection;
drop sequence tseq_title_rank;
drop view tview_title;
drop sequence tseq_title_id;
drop table ttab_title;
*/
--
-- Local variables:
-- c-basic-offset: 2
-- indent-tabs-mode: nil
-- End:
--
-- ex: ai et sw=2 ts=2
</sql>
</example>
---------------------------(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
