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.

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.

Truncate should probably check if all referencing tables are empty
and fail if not. Truncate should imho not lead to a violated constraint
situation.
Strictly speaking the current situation is more or less a bug.

Andreas

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

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

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.

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.

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

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

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.

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.

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

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

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?

No, I think that is only one point. I think you also need to
check if tables that are referencing this table are empty.

Andreas

Zeugswetter Andreas SB wrote:

Truncate should probably check if all referencing tables are empty
and fail if not. Truncate should imho not lead to a violated constraint
situation.
Strictly speaking the current situation is more or less a bug.

Not anything is possible with RI, so the DB schema might use
regular triggers and/or rules as well.

Why not reject TRUNCATE at all if the relation has
rules/triggers? IMHO the only safe way.

Jan

--

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

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

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.

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

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 #

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

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.

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

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

TODO updated:

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

I think this was solved in current with a better approach
(checks if referenced table is empty).

Andreas

[ Charset ISO-8859-1 unsupported, converting... ]

TODO updated:

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

I think this was solved in current with a better approach
(checks if referenced table is empty).

Removed from TODO.

-- 
  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