X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 60DDDD1B917 for ; Tue, 3 Feb 2004 16:35:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 07047-02 for ; Tue, 3 Feb 2004 12:35:47 -0400 (AST) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 2BED3D1B83D for ; Tue, 3 Feb 2004 12:35:46 -0400 (AST) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id i13GVAd1024236; Tue, 3 Feb 2004 09:31:10 -0700 (MST) Date: Tue, 3 Feb 2004 09:27:13 -0700 (MST) From: "scott.marlowe" To: Jan Wieck Cc: Stephan Szabo , Tom Lane , Enio Schutt Junior , Subject: Re: Sometimes referential integrity seems not to work In-Reply-To: <401FA2EC.2090308@Yahoo.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-IHS-MailScanner: Found to be clean X-IHS-MailScanner-SpamCheck: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/33 X-Sequence-Number: 16724 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 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 | 1 def | 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.