It seems the truncate command deletes all rows from a table even it is
referenced by another tables. TRUNCATE is not in the standard any way,
so I would not claim this is a bug. However, sometimes it would be
helpful for a user to let him notice that the table about to be
truncated is referenced by some tables. So I would propose to add
"RESTRICT" option to the command. I mean if RESTRICT is specified,
TRUNCATE will fail if the table is referenced.

BTW, the keyword "RESTRICT" is inspired by the fact that DROP TABLE
has the same option according to the standard. If a table is
referenced by some tables and the drop table command has the RESTRICT
option, it would fail. This seems to be a nice feature too.

Comments?
--
Tatsuo Ishii

Thu, 08 Jun 2000 21:24:52 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Proposal: TRUNCATE TABLE table RESTRICT
At 09:24 PM 6/8/00 +0900, Tatsuo Ishii wrote:
>It seems the truncate command deletes all rows from a table even it is
>referenced by another tables. TRUNCATE is not in the standard any way,
>so I would not claim this is a bug. However, sometimes it would be
>helpful for a user to let him notice that the table about to be
>truncated is referenced by some tables. So I would propose to add
>"RESTRICT" option to the command. I mean if RESTRICT is specified,
>TRUNCATE will fail if the table is referenced.

Shouldn't it always fail if an explicit foreign key reference
exists to the table, in much the way that delete of a referenced
row does?  If it doesn't now, I think it's a bug.

If the references are implicit (no REFERENCE or FOREIGN KEY given
to inform the db of the relationship) then a RESTRICT option to
truncate does seem useful.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 08 Jun 2000 06:49:55 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
Don Baccus <dhogaza@pacifier.com> writes:
> If the references are implicit (no REFERENCE or FOREIGN KEY given
> to inform the db of the relationship) then a RESTRICT option to
> truncate does seem useful.

Uh, if the references are implicit, how would RESTRICT know they exist?

			regards, tom lane

Thu, 08 Jun 2000 10:41:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal: TRUNCATE TABLE table RESTRICT 
Don Baccus wrote:
> 
> At 09:24 PM 6/8/00 +0900, Tatsuo Ishii wrote:
> >It seems the truncate command deletes all rows from a table even it is
> >referenced by another tables. TRUNCATE is not in the standard any way,
> >so I would not claim this is a bug. However, sometimes it would be
> >helpful for a user to let him notice that the table about to be
> >truncated is referenced by some tables. So I would propose to add
> >"RESTRICT" option to the command. I mean if RESTRICT is specified,
> >TRUNCATE will fail if the table is referenced.
> 
> Shouldn't it always fail if an explicit foreign key reference
> exists to the table, in much the way that delete of a referenced
> row does?  If it doesn't now, I think it's a bug.
> 
> If the references are implicit (no REFERENCE or FOREIGN KEY given
> to inform the db of the relationship) then a RESTRICT option to
> truncate does seem useful.
> 

Just curious, Don. But could you check to see what Oracle's
behavior is on this? That's the feature I was trying to mirror.
At the time, RI wasn't integrated so I wasn't thinking about this
issue. And the Oracle docs state that DML triggers aren't fired
when a TRUNCATE is issued, so I didn't think there would be
issues there. Could you check?

Thanks, 

Mike Mascari


> - Don Baccus, Portland OR <dhogaza@pacifier.com>
>   Nature photos, on-line guides, Pacific Northwest
>   Rare Bird Alert Service and other goodies at
>   http://donb.photo.net.

Thu, 08 Jun 2000 10:43:00 -0400
Mike Mascari <mascarm@mascari.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
At 10:41 AM 6/8/00 -0400, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> If the references are implicit (no REFERENCE or FOREIGN KEY given
>> to inform the db of the relationship) then a RESTRICT option to
>> truncate does seem useful.
>
>Uh, if the references are implicit, how would RESTRICT know they exist?

Duh, sorry, haven't had my coffee yet.  I should know better than
think about computers before coffee...got any?

OK ... then I'd suggest that allowing truncate in the face of explicit
foreign keys is a bug.  Truncate should either refuse to do so in
all cases, or follow RI rules (do ON DELETE CASCADE/SET NULL/SET DEFAULT
or refuse to do it depending on the foreign key def).  It would
presumably do so by calling the RI trigger for each row just as delete
does.

TRUNCATE's documented as being a quick alternative to delete,
so refusal is perhaps the best course.  Or the documentation
can say "it's a lot faster if there are no foreign keys referencing
it, otherwise it's the same as DELETE FROM".

But breaking RI by leaving "dangling references" is a bug, pure
and simple.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 08 Jun 2000 07:50:34 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT 
At 10:43 AM 6/8/00 -0400, Mike Mascari wrote:

>Just curious, Don. But could you check to see what Oracle's
>behavior is on this? That's the feature I was trying to mirror.
>At the time, RI wasn't integrated so I wasn't thinking about this
>issue.

Sure, I understand.

> And the Oracle docs state that DML triggers aren't fired
>when a TRUNCATE is issued, so I didn't think there would be
>issues there. Could you check?

It refuses to do the TRUNCATE, whether or not there's a
"ON DELETE CASCADE" modifier to the references.

That seems reasonable - it allows one to still say "truncate's
really fast because it doesn't scan the rows in the table",
and refuses to break RI constraints.

All that needs doing is to check for the existence of 
at least one RI trigger on the table that's being truncated,
and saying "no way, jose" if we want to mimic Oracle in
this regard.

TODO item?



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 08 Jun 2000 07:55:51 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
> All that needs doing is to check for the existence of 
> at least one RI trigger on the table that's being truncated,
> and saying "no way, jose" if we want to mimic Oracle in
> this regard.
> 
> TODO item?

OK, added:

	* Prevent truncate on table acting as foreign key

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 8 Jun 2000 11:47:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal: TRUNCATE TABLE table RESTRICT
Don Baccus wrote:
> 
> At 10:43 AM 6/8/00 -0400, Mike Mascari wrote:
...
> > And the Oracle docs state that DML triggers aren't fired
> >when a TRUNCATE is issued, so I didn't think there would be
> >issues there. Could you check?
> 
> It refuses to do the TRUNCATE, whether or not there's a
> "ON DELETE CASCADE" modifier to the references.
> 
> That seems reasonable - it allows one to still say "truncate's
> really fast because it doesn't scan the rows in the table",
> and refuses to break RI constraints.
> 
> All that needs doing is to check for the existence of
> at least one RI trigger on the table that's being truncated,
> and saying "no way, jose" if we want to mimic Oracle in
> this regard.
> 
> TODO item?

Sounds like it to me. Rats...

Mike Mascari

> 
> - Don Baccus, Portland OR <dhogaza@pacifier.com>
>   Nature photos, on-line guides, Pacific Northwest
>   Rare Bird Alert Service and other goodies at
>   http://donb.photo.net.

Thu, 08 Jun 2000 11:53:22 -0400
Mike Mascari <mascarm@mascari.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
At 11:47 AM 6/8/00 -0400, Bruce Momjian wrote:

>OK, added:
>
>	* Prevent truncate on table acting as foreign key

How about this: Prevent truncate on table referenced by foreign key



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 08 Jun 2000 10:27:45 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
At 05:20 PM 6/8/00 -0400, Bruce Momjian wrote:

>	* Prevent truncate on table with a referential integrity trigger
>
>Is that good?

It's beautiful!



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 08 Jun 2000 14:19:09 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
> At 11:47 AM 6/8/00 -0400, Bruce Momjian wrote:
> 
> >OK, added:
> >
> >	* Prevent truncate on table acting as foreign key
> 
> How about this: Prevent truncate on table referenced by foreign key
> 

Actually, I made it:

	* Prevent truncate on table with a referential integrity trigger

Is that good?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 8 Jun 2000 17:20:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal: TRUNCATE TABLE table RESTRICT
> >It seems the truncate command deletes all rows from a table even it is
> >referenced by another tables. TRUNCATE is not in the standard any way,
> >so I would not claim this is a bug. However, sometimes it would be
> >helpful for a user to let him notice that the table about to be
> >truncated is referenced by some tables. So I would propose to add
> >"RESTRICT" option to the command. I mean if RESTRICT is specified,
> >TRUNCATE will fail if the table is referenced.
> 
> Shouldn't it always fail if an explicit foreign key reference
> exists to the table, in much the way that delete of a referenced
> row does?  If it doesn't now, I think it's a bug.

That would be better. I am just wondering how the checkings hurt the
speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-).

> If the references are implicit (no REFERENCE or FOREIGN KEY given
> to inform the db of the relationship) then a RESTRICT option to
> truncate does seem useful.

Can you tell me what are the implicit references?

BTW, what do you think about DROP TABLE RESTRICT? I think this is a
nice feature and should be added to the TODO list...
--
Tatsuo IShii


Fri, 09 Jun 2000 10:07:16 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Proposal: TRUNCATE TABLE table RESTRICT
Tatsuo Ishii writes:

> That would be better. I am just wondering how the checkings hurt the
> speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-).

You can make any code arbitrarily fast if it doesn't have to behave
correctly. :-)

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Sat, 10 Jun 2000 20:08:44 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Proposal: TRUNCATE TABLE table RESTRICT
At 08:08 PM 6/10/00 +0200, Peter Eisentraut wrote:
>Tatsuo Ishii writes:
>
>> That would be better. I am just wondering how the checkings hurt the
>> speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-).
>
>You can make any code arbitrarily fast if it doesn't have to behave
>correctly. :-)

Checking for existence or absence of triggers will be fast.

Jan suggested aborting TRUNCATE if any (user or system) triggers
are on the table.  If I understood his message correctly, that is.

Oracle only aborts for foreign keys, executing TRUNCATE and ignoring
user triggers if they exist.

Any thoughts?

Rather than abort TRUNCATE due to the mere existence of a referential
integrity trigger on the table, we could be a bit more sophisicated
and only abort if an RI trigger exists where the referring table is
non-empty.  If the referring table's empty, no foreign keys will be
stored in it and you can safely TRUNCATE.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Mon, 12 Jun 2000 07:15:17 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
Don Baccus wrote:
> 
> At 08:08 PM 6/10/00 +0200, Peter Eisentraut wrote:
> >Tatsuo Ishii writes:
> >
> >> That would be better. I am just wondering how the checkings hurt the
> >> speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-).

The major performance difference between TRUNCATE and DELETE is
realized at VACUUM time.

> >
> >You can make any code arbitrarily fast if it doesn't have to behave
> >correctly. :-)
> 
> Checking for existence or absence of triggers will be fast.
> 
> Jan suggested aborting TRUNCATE if any (user or system) triggers
> are on the table.  If I understood his message correctly, that is.
> 
> Oracle only aborts for foreign keys, executing TRUNCATE and ignoring
> user triggers if they exist.
> 
> Any thoughts?

I agree with this.

> 
> Rather than abort TRUNCATE due to the mere existence of a referential
> integrity trigger on the table, we could be a bit more sophisicated
> and only abort if an RI trigger exists where the referring table is
> non-empty.  If the referring table's empty, no foreign keys will be
> stored in it and you can safely TRUNCATE.

Sorry to ask for another favor, but what does Oracle do here? If
a referring table has 1,000,000 rows in it which have been
deleted but not vacuumed, what would the performance implications
be?

Just curious, 

Mike Mascari

Mon, 12 Jun 2000 10:33:21 -0400
Mike Mascari <mascarm@mascari.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
Mike Mascari wrote:
> Sorry to ask for another favor, but what does Oracle do here? If
> a referring table has 1,000,000 rows in it which have been
> deleted but not vacuumed, what would the performance implications
> be?

    Referential integrity has no performance impact on VACUUM. If
    that's what you aren't sure about.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Mon, 12 Jun 2000 23:41:40 +0200 (MEST)
JanWieck@t-online.de (Jan Wieck)
Re: Proposal: TRUNCATE TABLE table RESTRICT
Jan Wieck wrote:
> 
> Mike Mascari wrote:
> > Sorry to ask for another favor, but what does Oracle do here? If
> > a referring table has 1,000,000 rows in it which have been
> > deleted but not vacuumed, what would the performance implications
> > be?
> 
>     Referential integrity has no performance impact on VACUUM. If
>     that's what you aren't sure about.
> 
> Jan

Actually, I was worried that if TRUNCATE were to vist all
referring tables to determine whether or not it was empty, rather
then just issuing an elog() at the first RI trigger encountered,
that it might wind up scanning a 1,000,000 tuple relation (the
referring relation) where all the rows have been marked as
deleted before determining that its okay to perform the TRUNCATE.
I was hoping that Oracle simply disallowed TRUNCATE on tables
with referring relations, regardless of whether or not there was
actually any data in them, so that PostgreSQL could do the same.
:-)

Mike Mascari

Mon, 12 Jun 2000 19:48:59 -0400
Mike Mascari <mascarm@mascari.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
At 07:48 PM 6/12/00 -0400, Mike Mascari wrote:

>Actually, I was worried that if TRUNCATE were to vist all
>referring tables to determine whether or not it was empty, rather
>then just issuing an elog() at the first RI trigger encountered,
>that it might wind up scanning a 1,000,000 tuple relation (the
>referring relation) where all the rows have been marked as
>deleted before determining that its okay to perform the TRUNCATE.
>I was hoping that Oracle simply disallowed TRUNCATE on tables
>with referring relations, regardless of whether or not there was
>actually any data in them, so that PostgreSQL could do the same.
>:-)

Well, I think we probably could do so regardless of what Oracle
does.  Proper use of "on delete cascade" and "on delete set null"
etc would seem to make it more convenient to delete rows in a
set of related tables via delete rather than running around
trying to truncate them in the right order so that you
end up with empty tables before you delete the one with the
RI triggers on it.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Mon, 12 Jun 2000 16:51:20 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Proposal: TRUNCATE TABLE table RESTRICT
Can someone comment on this?

> It seems the truncate command deletes all rows from a table even it is
> referenced by another tables. TRUNCATE is not in the standard any way,
> so I would not claim this is a bug. However, sometimes it would be
> helpful for a user to let him notice that the table about to be
> truncated is referenced by some tables. So I would propose to add
> "RESTRICT" option to the command. I mean if RESTRICT is specified,
> TRUNCATE will fail if the table is referenced.
> 
> BTW, the keyword "RESTRICT" is inspired by the fact that DROP TABLE
> has the same option according to the standard. If a table is
> referenced by some tables and the drop table command has the RESTRICT
> option, it would fail. This seems to be a nice feature too.
> 
> Comments?
> --
> Tatsuo Ishii
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 2 Oct 2000 23:21:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal: TRUNCATE TABLE table RESTRICT
> At 09:24 PM 6/8/00 +0900, Tatsuo Ishii wrote:
> >It seems the truncate command deletes all rows from a table even it is
> >referenced by another tables. TRUNCATE is not in the standard any way,
> >so I would not claim this is a bug. However, sometimes it would be
> >helpful for a user to let him notice that the table about to be
> >truncated is referenced by some tables. So I would propose to add
> >"RESTRICT" option to the command. I mean if RESTRICT is specified,
> >TRUNCATE will fail if the table is referenced.
> 
> Shouldn't it always fail if an explicit foreign key reference
> exists to the table, in much the way that delete of a referenced
> row does?  If it doesn't now, I think it's a bug.
> 
> If the references are implicit (no REFERENCE or FOREIGN KEY given
> to inform the db of the relationship) then a RESTRICT option to
> truncate does seem useful.

TODO updated:

* Prevent truncate on table with a referential integrity trigger (RESTRICT)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 9 Oct 2000 15:41:10 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal: TRUNCATE TABLE table RESTRICT