pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSometimes referential integrity seems not to work
10+ messages / 6 participants
[nested] [flat]
* Sometimes referential integrity seems not to work
@ 2004-01-26 17:51 Enio Schutt Junior <enio@pmpf.rs.gov.br>
0 siblings, 1 reply; 10+ messages in thread
From: Enio Schutt Junior @ 2004-01-26 17:51 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-01-31 20:45 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Enio Schutt Junior <enio@pmpf.rs.gov.br>
0 siblings, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2004-01-31 20:45 UTC (permalink / raw)
To: Enio Schutt Junior <enio@pmpf.rs.gov.br>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-01-31 23:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2004-01-31 23:40 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-02-01 05:48 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2004-02-01 05:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-02-02 16:54 Jan Wieck <JanWieck@Yahoo.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jan Wieck @ 2004-02-02 16:54 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-02-02 18:40 scott.marlowe <scott.marlowe@ihs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 1 reply; 10+ messages in thread
From: scott.marlowe @ 2004-02-02 18:40 UTC (permalink / raw)
To: Jan Wieck <JanWieck@yahoo.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-02-02 18:44 Rod Taylor <pg@rbt.ca>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: Rod Taylor @ 2004-02-02 18:44 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-02-03 13:32 Jan Wieck <JanWieck@Yahoo.com>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jan Wieck @ 2004-02-03 13:32 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-02-03 16:27 scott.marlowe <scott.marlowe@ihs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: scott.marlowe @ 2004-02-03 16:27 UTC (permalink / raw)
To: Jan Wieck <JanWieck@yahoo.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
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 | 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sometimes referential integrity seems not to work
@ 2004-02-03 16:31 scott.marlowe <scott.marlowe@ihs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: scott.marlowe @ 2004-02-03 16:31 UTC (permalink / raw)
To: Jan Wieck <JanWieck@yahoo.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Tom Lane <tgl@sss.pgh.pa.us>; Enio Schutt Junior <enio@pmpf.rs.gov.br>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-02-03 16:31 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-26 17:51 Sometimes referential integrity seems not to work Enio Schutt Junior <enio@pmpf.rs.gov.br>
2004-01-31 20:45 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-01-31 23:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-01 05:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-02 16:54 ` Jan Wieck <JanWieck@Yahoo.com>
2004-02-02 18:40 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-03 13:32 ` Jan Wieck <JanWieck@Yahoo.com>
2004-02-03 16:27 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-03 16:31 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-02 18:44 ` Rod Taylor <pg@rbt.ca>
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