Thread: Sometimes referential integrity seems not to work
In a database I am working, I sometimes have to delete all the records in some tables. According to the referential integrity defined in the creation of the tables, postmaster should not delete the records, but it does. I have used the following commands: "delete from table_1" and "truncate table_1". When deleting records individually, referential integrity works (postmaster does not allows deletion of records which are referred by another table). Is there some difference between deleting records individually or at once? Is there any possibility of the user who does this deletion (postgres, normal users) affects the behaviour of referential integrity in the tables? That is, can the postgres user delete records despite referential integrity? Thanks.
On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > In a database I am working, I sometimes have to delete all the records in > some tables. According to the referential integrity defined in the creation > of the tables, postmaster should not delete the records, but it does. I have > used the following commands: "delete from table_1" and "truncate table_1". > When deleting records individually, referential integrity works (postmaster > does not allows deletion of records which are referred by another table). > Is there some difference between deleting records individually or at once? > Is there any possibility of the user who does this deletion (postgres, normal > users) affects the behaviour of referential integrity in the tables? That is, > can the postgres user delete records despite referential integrity? It shouldn't. Can you give your version information and a complete standalone example?
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Mon, 26 Jan 2004, Enio Schutt Junior wrote:
>> In a database I am working, I sometimes have to delete all the records in
>> some tables. According to the referential integrity defined in the creation
>> of the tables, postmaster should not delete the records, but it does. I have
>> used the following commands: "delete from table_1" and "truncate table_1".
>> ...
>> can the postgres user delete records despite referential integrity?
> It shouldn't. Can you give your version information and a complete
> standalone example?
I think the first PG release or two that had TRUNCATE TABLE would allow
you to apply it despite the existence of foreign-key constraints on the
table. Recent releases won't though.
regards, tom lane
On Sat, 31 Jan 2004, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> In a database I am working, I sometimes have to delete all the records in > >> some tables. According to the referential integrity defined in the creation > >> of the tables, postmaster should not delete the records, but it does. I have > >> used the following commands: "delete from table_1" and "truncate table_1". > >> ... > >> can the postgres user delete records despite referential integrity? > > I think the first PG release or two that had TRUNCATE TABLE would allow > you to apply it despite the existence of foreign-key constraints on the > table. Recent releases won't though. Yeah, truncate didn't worry me much, but the implication that delete from table_1; worked did.
Stephan Szabo wrote: > On Sat, 31 Jan 2004, Tom Lane wrote: > >> Stephan Szabo <sszabo@megazone.bigpanda.com> writes: >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: >> >> In a database I am working, I sometimes have to delete all the records in >> >> some tables. According to the referential integrity defined in the creation >> >> of the tables, postmaster should not delete the records, but it does. I have >> >> used the following commands: "delete from table_1" and "truncate table_1". >> >> ... >> >> can the postgres user delete records despite referential integrity? >> >> I think the first PG release or two that had TRUNCATE TABLE would allow >> you to apply it despite the existence of foreign-key constraints on the >> table. Recent releases won't though. > > Yeah, truncate didn't worry me much, but the implication that delete from > table_1; worked did. TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks for foreign keys. So I guess Enio is getting but ignoring the error message when trying the delete, but then the truncate does the job in his pre-7.3 database. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > for foreign keys. So I guess Enio is getting but ignoring the error In 7.4 truncate is transaction safe.
On Mon, 2 Feb 2004, Jan Wieck wrote: > Stephan Szabo wrote: > > > On Sat, 31 Jan 2004, Tom Lane wrote: > > > >> Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> In a database I am working, I sometimes have to delete all the records in > >> >> some tables. According to the referential integrity defined in the creation > >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> ... > >> >> can the postgres user delete records despite referential integrity? > >> > >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> you to apply it despite the existence of foreign-key constraints on the > >> table. Recent releases won't though. > > > > Yeah, truncate didn't worry me much, but the implication that delete from > > table_1; worked did. > > TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > for foreign keys. So I guess Enio is getting but ignoring the error > message when trying the delete, but then the truncate does the job in > his pre-7.3 database. Yes it can. I think it was starting in 7.3. => select * from test2; info -------------abc'123 123 (2 rows) => begin; BEGIN => truncate test2; TRUNCATE TABLE => rollback; ROLLBACK => select * from test2; info -------------abc'123 123
scott.marlowe wrote: > On Mon, 2 Feb 2004, Jan Wieck wrote: > >> Stephan Szabo wrote: >> >> > On Sat, 31 Jan 2004, Tom Lane wrote: >> > >> >> Stephan Szabo <sszabo@megazone.bigpanda.com> writes: >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: >> >> >> In a database I am working, I sometimes have to delete all the records in >> >> >> some tables. According to the referential integrity defined in the creation >> >> >> of the tables, postmaster should not delete the records, but it does. I have >> >> >> used the following commands: "delete from table_1" and "truncate table_1". >> >> >> ... >> >> >> can the postgres user delete records despite referential integrity? >> >> >> >> I think the first PG release or two that had TRUNCATE TABLE would allow >> >> you to apply it despite the existence of foreign-key constraints on the >> >> table. Recent releases won't though. >> > >> > Yeah, truncate didn't worry me much, but the implication that delete from >> > table_1; worked did. >> >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks >> for foreign keys. So I guess Enio is getting but ignoring the error >> message when trying the delete, but then the truncate does the job in >> his pre-7.3 database. > > Yes it can. I think it was starting in 7.3. Okay, so you're the third one correcting me on this. Now can any of you violate a foreign key constraint with anything else than using truncate in a pre-7.3 database? Because I can't do that and that was the original problem. Jan > > => select * from test2; > info > ------------- > abc'123 > 123 > (2 rows) > > => begin; > BEGIN > => truncate test2; > TRUNCATE TABLE > => rollback; > ROLLBACK > => select * from test2; > info > ------------- > abc'123 > 123 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Tue, 3 Feb 2004, Jan Wieck wrote: > scott.marlowe wrote: > > > On Mon, 2 Feb 2004, Jan Wieck wrote: > > > >> Stephan Szabo wrote: > >> > >> > On Sat, 31 Jan 2004, Tom Lane wrote: > >> > > >> >> Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> >> In a database I am working, I sometimes have to delete all the records in > >> >> >> some tables. According to the referential integrity defined in the creation > >> >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> >> ... > >> >> >> can the postgres user delete records despite referential integrity? > >> >> > >> >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> >> you to apply it despite the existence of foreign-key constraints on the > >> >> table. Recent releases won't though. > >> > > >> > Yeah, truncate didn't worry me much, but the implication that delete from > >> > table_1; worked did. > >> > >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > >> for foreign keys. So I guess Enio is getting but ignoring the error > >> message when trying the delete, but then the truncate does the job in > >> his pre-7.3 database. > > > > Yes it can. I think it was starting in 7.3. > > Okay, so you're the third one correcting me on this. Now can any of you > violate a foreign key constraint with anything else than using truncate > in a pre-7.3 database? Because I can't do that and that was the original > problem. Our production machine is running 7.2, and I get this: begin; BEGIN =# truncate test; ERROR: TRUNCATE TABLE cannot run inside a transaction block =# commit; COMMIT =# select * from test;info | id ------+----abc | 1def | 2 (2 rows) So, at least in 7.2, it won't let me truncate. I'm not running any 7.3 boxes, just 7.4 and 7.2, so I can't test it on 7.3.
On Tue, 3 Feb 2004, Jan Wieck wrote: > scott.marlowe wrote: > > > On Mon, 2 Feb 2004, Jan Wieck wrote: > > > >> Stephan Szabo wrote: > >> > >> > On Sat, 31 Jan 2004, Tom Lane wrote: > >> > > >> >> Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> >> In a database I am working, I sometimes have to delete all the records in > >> >> >> some tables. According to the referential integrity defined in the creation > >> >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> >> ... > >> >> >> can the postgres user delete records despite referential integrity? > >> >> > >> >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> >> you to apply it despite the existence of foreign-key constraints on the > >> >> table. Recent releases won't though. > >> > > >> > Yeah, truncate didn't worry me much, but the implication that delete from > >> > table_1; worked did. > >> > >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > >> for foreign keys. So I guess Enio is getting but ignoring the error > >> message when trying the delete, but then the truncate does the job in > >> his pre-7.3 database. > > > > Yes it can. I think it was starting in 7.3. > > Okay, so you're the third one correcting me on this. Now can any of you > violate a foreign key constraint with anything else than using truncate > in a pre-7.3 database? Because I can't do that and that was the original > problem. OK, I just tested the truncate foreign key truncate on 7.2, and other than truncate, I've not found any way to delete the fk data from the parent table.