Hi there,

 > I agree with Tom ... even the idea of a "TRUNCATE ALL" makes me nervous.  If
 > we had such a feature, I'd advocate that it be superuser only.

This "superuser only" restriction certainly would be sensible.

 > As for "TRUNCATE CASCADE" or similar improvements, I agree that they 
could be
 > convenient ... but are easily worked around currently.

Agreed, workarounds are easy. The original suggestion of "TRUNCATE ALL"
(or "TRUNCATE table CASCADE"), however, was motivated by the search for a
simple and efficient truncation of all tables to accelerate unit-testing.
Obviously, a plain "TRUNCATE" without arguments that truncates all tables
would be dangerous, and in order to stay consistent with current psql
syntax, I would say that an efficient "TRUNCATE table [CASCADE|RESTRICT]",
with the "RESTRICT" option being the default, would be totally sufficient
to satisfy the original motivation.

Another way to specify a safe but efficient "TRUNCATE ALL" command that
might be easier to implement than above "TRUNCATE table
[CASCADE|RESTRICT]"  might be to implement the functionality of the
originally suggested "TRUNCATE ALL" through a psql meta-command. Any
suggestions for a safe syntax of such a "TRUNCATE ALL" meta-command? How
about "\rtuples *"?

 >   So I wouldn't object to putting TRUNCATE CASCADE on the todo list, but
 > would argue that it be left to the people who asked for it to implement
 > it.

Before attempting to implementing any such extension, we would like to
make sure that that extension would not be rejected by those of you who
decide what goes into future versions of postgresql.

		Yours,
			Andi.



Tue, 05 Aug 2003 10:10:22 +0200
Andreas <e9625203@student.tuwien.ac.at>
Re: "truncate all"?
Andreas <e9625203@student.tuwien.ac.at> writes:
> Agreed, workarounds are easy. The original suggestion of "TRUNCATE ALL"
> (or "TRUNCATE table CASCADE"), however, was motivated by the search for a
> simple and efficient truncation of all tables to accelerate unit-testing.

I still think the best suggestion for you is the one someone made
already: create a schema dump (pg_dump -s), then do DROP DATABASE,
CREATE DATABASE, load the schema dump.  This will be somewhat slower
than a hypothetical TRUNCATE ALL, but it has the great advantage that
your starting point is well-documented and guaranteed to be the same
on every iteration.  TRUNCATE ALL would provide no such guarantee.

And, of course, that solution exists today ...

			regards, tom lane

Tue, 05 Aug 2003 10:16:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: "truncate all"? 
Andi,

> Another way to specify a safe but efficient "TRUNCATE ALL" command that
> might be easier to implement than above "TRUNCATE table
> [CASCADE|RESTRICT]"  might be to implement the functionality of the
> originally suggested "TRUNCATE ALL" through a psql meta-command. Any
> suggestions for a safe syntax of such a "TRUNCATE ALL" meta-command? How
> about "\rtuples *"?

I'm not clear on the usefulness of this idea.   If we agree that TRUNCATE ... 
CASCADE is needed, then doing it in SQL makes sense.  Your suggested command 
would leave itself open to typo-death.

And if we have TRUNCATE ... CASCADE, then truncating all tables is a matter of 
a very simple perl or C script looping through pg_class.

The reason I'm opposed to "TRUNCATE ALL" is that it provides an opportunity 
for ghastly mistakes.   I also don't think that outside of your particular 
case that there's much demand for it; most users, I imagine, want to truncate 
a group of tables or all but 2 tables or are already used to DROP DATABASE 
and don't need it.   You could take a quick survey on PGSQL-SQL.

> Before attempting to implementing any such extension, we would like to
> make sure that that extension would not be rejected by those of you who
> decide what goes into future versions of postgresql.

That's definitely good idea ... make sure it gets on the TODO list before 
submitting.  Been around a while, have you?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 5 Aug 2003 08:55:03 -0700
Josh Berkus <josh@agliodbs.com>
Re: "truncate all"?
Hi there,

At 17:55 05.08.2003, Josh Berkus wrote:
> > Another way to specify a safe but efficient "TRUNCATE ALL" command that
> > might be easier to implement than above "TRUNCATE table
> > [CASCADE|RESTRICT]"  might be to implement the functionality of the
> > originally suggested "TRUNCATE ALL" through a psql meta-command. Any
> > suggestions for a safe syntax of such a "TRUNCATE ALL" meta-command? How
> > about "\rtuples *"?
>
>I'm not clear on the usefulness of this idea.   If we agree that TRUNCATE ...
>CASCADE is needed, then doing it in SQL makes sense.  Your suggested command
>would leave itself open to typo-death.

How about using a command name that is long enough so that mistyping 
becomes highly unlikely? The following might be exaggerated, but why not 
call it something like

   \removealltuplesofalltableswithoutcheckingreferentialintegrity

(please take it with a grain of salt 8-). Both this meta-command and the 
TRUNCATE table CASCADE command make sense separately as extensions to psql, 
as their motivations are quite different. For the purpose of unit-tests, 
only the first would make sense since there we need to truncate all tables 
anyway and as the latter would only generate unnecessary overhead. 
Unit-tests must run as fast as possible as we want to rely on them for 
every editing step.

>And if we have TRUNCATE ... CASCADE, then truncating all tables is a 
>matter of
>a very simple perl or C script looping through pg_class.

Sure, but I would imagine that a truncation of all tuples of every table in 
the db, for (1), would be very simple to implement since we can forget 
about all dependencies, and, for (2), would be decidedly faster than 
calling TRUNCATE table CASCADE repeatedly, because there is no need to 
switch languages and no need to find out about dependencies as a first step.

As an aside, I wonder whether a cascading truncate needs to be made safe 
against table-level cyclicity w.r.t. referential integrity?

>I also don't think that outside of your particular
>case that there's much demand for it;

I would like to believe that there is a need for efficient unit-testing 
(unit-tests can be seen as a kind of regression tests for applications) in 
postgresql beyond our project. If developers are not using unit-tests 
(yet), I would also like to believe that this should not be interpreted as 
an indication that they do not want to use them, but maybe that they do not 
know about them.

                 Thanks for your time,
                                         Andi. 



Wed, 06 Aug 2003 10:39:39 +0200
Andreas <e9625203@student.tuwien.ac.at>
Re: "truncate all"?
On Wed, 2003-08-06 at 04:39, Andreas wrote: 
> At 17:55 05.08.2003, Josh Berkus wrote:
> >I also don't think that outside of your particular
> >case that there's much demand for it;
> 
> I would like to believe that there is a need for efficient unit-testing 
> (unit-tests can be seen as a kind of regression tests for applications) in 
> postgresql beyond our project. If developers are not using unit-tests 
> (yet), I would also like to believe that this should not be interpreted as 
> an indication that they do not want to use them, but maybe that they do not 
> know about them.
> 

IMHO the dropdb/createdb/load schema/load data cycle provides for a
better unit test than the truncate data/load data cycle does, so while I
see this functionality as handy for development, I wouldn't use it for
unit testing.  

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


07 Aug 2003 12:51:11 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: "truncate all"?