Thread: "truncate all"?

"truncate all"?

From
Andreas
Date:
Hi!
Would it be possible to implement a "truncate all" that purges all tuples 
from *all* tables, without taking account any rules or triggers, but 
leaving all table structures and rules, triggers, functions, etc intact 
(sequences do not need to reinitialized)?

As far as I understand, the "no truncate if table is referenced" change was 
introduced to ensure database integrity. However, if the referencing table 
is truncated, too, there should be no problem as far as foreign keys are 
concerned, correct?

The rationale behind this suggestion is that in our project we need a 
*quick* way to get rid of all the tuples in all tables in order to 
accelerate the reinitialization of the database for our unit tests. This 
needs to be done fairly often, and so the quicker the unit tests run, the 
easier it will be to include many unit tests in our project, thus ensuring 
that we can develop efficiently and safely in postgresql.

If you know of some other *quick* way to truncate all tables, please let us 
know. BTW: Starting and later rolling back a transaction will not work, as 
we also need to check whether the correct exceptions are raised. This "all" 
option to "truncate" would really help to accelerate unit tests (besides of 
making them more readable) and should be fairly easy to implement, but I am 
not sure how to change the relevant postgresql code. I would be glad if 
someone could suggest some easy way to do it, and what needs to be observed 
to implement such an extension of postgresql. I also hope that such an 
extension could be included in future version of postgresql, but for now 
some easy patch to the latest version would do as well.
    Thanks for your time,                Andi.




Re: "truncate all"?

From
"Shridhar Daithankar"
Date:
On 4 Aug 2003 at 11:25, Andreas wrote:
>     Would it be possible to implement a "truncate all" that purges all tuples 
> from *all* tables, without taking account any rules or triggers, but 
> leaving all table structures and rules, triggers, functions, etc intact 
> (sequences do not need to reinitialized)?
> 
> As far as I understand, the "no truncate if table is referenced" change was 
> introduced to ensure database integrity. However, if the referencing table 
> is truncated, too, there should be no problem as far as foreign keys are 
> concerned, correct?
> 
> The rationale behind this suggestion is that in our project we need a 
> *quick* way to get rid of all the tuples in all tables in order to 
> accelerate the reinitialization of the database for our unit tests. This 
> needs to be done fairly often, and so the quicker the unit tests run, the 
> easier it will be to include many unit tests in our project, thus ensuring 
> that we can develop efficiently and safely in postgresql.
> 
> If you know of some other *quick* way to truncate all tables, please let us 
> know. BTW: Starting and later rolling back a transaction will not work, as 

As a workaround, I would dump the schema to a file using pg_dump, drop the 
database and recreate it from schema.

Will that do for you? Unfortunately that is not transaction safe and any 
clients connected at that time needs to disconnect first. Hopefully you can do 
that in the test environment.

HTH

ByeShridhar

--
Bubble Memory, n.:    A derogatory term, usually referring to a person's 
intelligence.    See also "vacuum tube".



Re: "truncate all"?

From
Robert Treat
Date:
On Mon, 2003-08-04 at 05:40, Shridhar Daithankar wrote:
> On 4 Aug 2003 at 11:25, Andreas wrote:
> >     Would it be possible to implement a "truncate all" that purges all tuples 
> > from *all* tables, without taking account any rules or triggers, but 
> > leaving all table structures and rules, triggers, functions, etc intact 
> > (sequences do not need to reinitialized)?
> > 
> > As far as I understand, the "no truncate if table is referenced" change was 
> > introduced to ensure database integrity. However, if the referencing table 
> > is truncated, too, there should be no problem as far as foreign keys are 
> > concerned, correct?
> > 
> > The rationale behind this suggestion is that in our project we need a 
> > *quick* way to get rid of all the tuples in all tables in order to 
> > accelerate the reinitialization of the database for our unit tests. This 
> > needs to be done fairly often, and so the quicker the unit tests run, the 
> > easier it will be to include many unit tests in our project, thus ensuring 
> > that we can develop efficiently and safely in postgresql.
> > 
> > If you know of some other *quick* way to truncate all tables, please let us 
> > know. BTW: Starting and later rolling back a transaction will not work, as 
> 
> As a workaround, I would dump the schema to a file using pg_dump, drop the 
> database and recreate it from schema.
> 
> Will that do for you? Unfortunately that is not transaction safe and any 
> clients connected at that time needs to disconnect first. Hopefully you can do 
> that in the test environment.
> 

Truncate isn't transaction safe either, so that shouldn't be a problem. 

Proper syntax for his feature would seem like: 
truncate table [cascade|restrict] ?


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



Re: "truncate all"?

From
Rod Taylor
Date:
> Truncate isn't transaction safe either, so that shouldn't be a problem.

Actually, it is in 7.4

> Proper syntax for his feature would seem like:
> truncate table [cascade|restrict] ?

Agreed.

Re: "truncate all"?

From
Robert Treat
Date:
On Mon, 2003-08-04 at 10:28, Rod Taylor wrote:
> > Truncate isn't transaction safe either, so that shouldn't be a
> problem. 
> 
> Actually, it is in 7.4

yeah i know, but I assumed he wasn't doing his production unit testing
against 7.4. Course if he is all the better I suppose... :-)

> 
> > Proper syntax for his feature would seem like: 
> > truncate table [cascade|restrict] ?
> 
> Agreed.

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



Re: "truncate all"?

From
Bruce Momjian
Date:
This this a TODO?  Keep in mind if we follow the syntax of VACUUM and
(7.4) CLUSTER, that the all-database truncate would just be "TRUNACATE".
That seems very risky to me.  I wonder if the risk is worth adding this
feature.

---------------------------------------------------------------------------

Robert Treat wrote:
> On Mon, 2003-08-04 at 05:40, Shridhar Daithankar wrote:
> > On 4 Aug 2003 at 11:25, Andreas wrote:
> > >     Would it be possible to implement a "truncate all" that purges all tuples 
> > > from *all* tables, without taking account any rules or triggers, but 
> > > leaving all table structures and rules, triggers, functions, etc intact 
> > > (sequences do not need to reinitialized)?
> > > 
> > > As far as I understand, the "no truncate if table is referenced" change was 
> > > introduced to ensure database integrity. However, if the referencing table 
> > > is truncated, too, there should be no problem as far as foreign keys are 
> > > concerned, correct?
> > > 
> > > The rationale behind this suggestion is that in our project we need a 
> > > *quick* way to get rid of all the tuples in all tables in order to 
> > > accelerate the reinitialization of the database for our unit tests. This 
> > > needs to be done fairly often, and so the quicker the unit tests run, the 
> > > easier it will be to include many unit tests in our project, thus ensuring 
> > > that we can develop efficiently and safely in postgresql.
> > > 
> > > If you know of some other *quick* way to truncate all tables, please let us 
> > > know. BTW: Starting and later rolling back a transaction will not work, as 
> > 
> > As a workaround, I would dump the schema to a file using pg_dump, drop the 
> > database and recreate it from schema.
> > 
> > Will that do for you? Unfortunately that is not transaction safe and any 
> > clients connected at that time needs to disconnect first. Hopefully you can do 
> > that in the test environment.
> > 
> 
> Truncate isn't transaction safe either, so that shouldn't be a problem. 
> 
> Proper syntax for his feature would seem like: 
> truncate table [cascade|restrict] ?
> 
> 
> Robert Treat
> -- 
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: "truncate all"?

From
"scott.marlowe"
Date:
I agree, a plain truncate blasting a whole database is a very bad thing.

however, "truncate with cascade" would be quite useful.

On Mon, 4 Aug 2003, Bruce Momjian wrote:

> 
> This this a TODO?  Keep in mind if we follow the syntax of VACUUM and
> (7.4) CLUSTER, that the all-database truncate would just be "TRUNACATE".
> That seems very risky to me.  I wonder if the risk is worth adding this
> feature.
> 
> ---------------------------------------------------------------------------
> 
> Robert Treat wrote:
> > On Mon, 2003-08-04 at 05:40, Shridhar Daithankar wrote:
> > > On 4 Aug 2003 at 11:25, Andreas wrote:
> > > >     Would it be possible to implement a "truncate all" that purges all tuples 
> > > > from *all* tables, without taking account any rules or triggers, but 
> > > > leaving all table structures and rules, triggers, functions, etc intact 
> > > > (sequences do not need to reinitialized)?
> > > > 
> > > > As far as I understand, the "no truncate if table is referenced" change was 
> > > > introduced to ensure database integrity. However, if the referencing table 
> > > > is truncated, too, there should be no problem as far as foreign keys are 
> > > > concerned, correct?
> > > > 
> > > > The rationale behind this suggestion is that in our project we need a 
> > > > *quick* way to get rid of all the tuples in all tables in order to 
> > > > accelerate the reinitialization of the database for our unit tests. This 
> > > > needs to be done fairly often, and so the quicker the unit tests run, the 
> > > > easier it will be to include many unit tests in our project, thus ensuring 
> > > > that we can develop efficiently and safely in postgresql.
> > > > 
> > > > If you know of some other *quick* way to truncate all tables, please let us 
> > > > know. BTW: Starting and later rolling back a transaction will not work, as 
> > > 
> > > As a workaround, I would dump the schema to a file using pg_dump, drop the 
> > > database and recreate it from schema.
> > > 
> > > Will that do for you? Unfortunately that is not transaction safe and any 
> > > clients connected at that time needs to disconnect first. Hopefully you can do 
> > > that in the test environment.
> > > 
> > 
> > Truncate isn't transaction safe either, so that shouldn't be a problem. 
> > 
> > Proper syntax for his feature would seem like: 
> > truncate table [cascade|restrict] ?
> > 
> > 
> > Robert Treat
> > -- 
> > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> > 
> 
> 



Re: "truncate all"?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> This this a TODO?  Keep in mind if we follow the syntax of VACUUM and
> (7.4) CLUSTER, that the all-database truncate would just be "TRUNACATE".
> That seems very risky to me.  I wonder if the risk is worth adding this
> feature.

I wouldn't care for that either.  The prior suggestion of "TRUNCATE tab
CASCADE" (to truncate any tables with FK dependencies on the original
target, instead of failing) seems more reasonable.
        regards, tom lane


Re: "truncate all"?

From
"Shridhar Daithankar"
Date:
On 4 Aug 2003 at 10:03, scott.marlowe wrote:

> I agree, a plain truncate blasting a whole database is a very bad thing.
> 
> however, "truncate with cascade" would be quite useful.

If we could get something simple as getting schema of a table, dropping the 
table and recreating empty table, then it should be easy to emulate truncate..

Or is it done that way already?

ByeShridhar

--
Either one of us, by himself, is expendable.  Both of us are not.        -- Kirk, 
"The Devil in the Dark", stardate 3196.1



Re: "truncate all"?

From
Josh Berkus
Date:
Guys,

> I wouldn't care for that either.  The prior suggestion of "TRUNCATE tab
> CASCADE" (to truncate any tables with FK dependencies on the original
> target, instead of failing) seems more reasonable.

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.

As for "TRUNCATE CASCADE" or similar improvements, I agree that they could be 
convenient ... but are easily worked around currently.   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.

As far as 

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: "truncate all"?

From
Robert Treat
Date:
On Mon, 2003-08-04 at 12:19, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > This this a TODO?  Keep in mind if we follow the syntax of VACUUM and
> > (7.4) CLUSTER, that the all-database truncate would just be "TRUNACATE".
> > That seems very risky to me.  I wonder if the risk is worth adding this
> > feature.
> 
> I wouldn't care for that either.  The prior suggestion of "TRUNCATE tab
> CASCADE" (to truncate any tables with FK dependencies on the original
> target, instead of failing) seems more reasonable.
> 

Actually there seems to be an ancillary issue here:

21809=# truncate exception;
ERROR:  TRUNCATE cannot be used as table exception_notice_map references
this one via foreign key constraint $1
21809=# TRUNCATE exception_notice_map ;
TRUNCATE TABLE
21809=# truncate exception;
ERROR:  TRUNCATE cannot be used as table exception_notice_map references
this one via foreign key constraint $1
21809=# select count(*) from exception_notice_map;count 
-------    0
(1 row)

21809=# 

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



Re: "truncate all"?

From
Andreas
Date:
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
thatit 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
whoasked 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.




Re: "truncate all"?

From
Tom Lane
Date:
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


Re: "truncate all"?

From
Josh Berkus
Date:
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


Re: "truncate all"?

From
Andreas
Date:
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. 




Re: "truncate all"?

From
Robert Treat
Date:
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



Re: "truncate all"?

From
Bruce Momjian
Date:
Added to TODO:
* Add TRUNCATE ... CASCADE


---------------------------------------------------------------------------

Josh Berkus wrote:
> Guys,
> 
> > I wouldn't care for that either.  The prior suggestion of "TRUNCATE tab
> > CASCADE" (to truncate any tables with FK dependencies on the original
> > target, instead of failing) seems more reasonable.
> 
> 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.
> 
> As for "TRUNCATE CASCADE" or similar improvements, I agree that they could be 
> convenient ... but are easily worked around currently.   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.
> 
> As far as 
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: "truncate all"?

From
Bruce Momjian
Date:
Is this a bug?

---------------------------------------------------------------------------

Robert Treat wrote:
> On Mon, 2003-08-04 at 12:19, Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > This this a TODO?  Keep in mind if we follow the syntax of VACUUM and
> > > (7.4) CLUSTER, that the all-database truncate would just be "TRUNACATE".
> > > That seems very risky to me.  I wonder if the risk is worth adding this
> > > feature.
> > 
> > I wouldn't care for that either.  The prior suggestion of "TRUNCATE tab
> > CASCADE" (to truncate any tables with FK dependencies on the original
> > target, instead of failing) seems more reasonable.
> > 
> 
> Actually there seems to be an ancillary issue here:
> 
> 21809=# truncate exception;
> ERROR:  TRUNCATE cannot be used as table exception_notice_map references
> this one via foreign key constraint $1
> 21809=# TRUNCATE exception_notice_map ;
> TRUNCATE TABLE
> 21809=# truncate exception;
> ERROR:  TRUNCATE cannot be used as table exception_notice_map references
> this one via foreign key constraint $1
> 21809=# select count(*) from exception_notice_map;
>  count 
> -------
>      0
> (1 row)
> 
> 21809=# 
> 
> Robert Treat
> -- 
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: "truncate all"?

From
Stephan Szabo
Date:
On Sun, 17 Aug 2003, Bruce Momjian wrote:

> Is this a bug?

I don't think so.  I'd say this is the expected behavior. Part of the
point is that it fails without checking for matching rows.

> Robert Treat wrote:
> > On Mon, 2003-08-04 at 12:19, Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > This this a TODO?  Keep in mind if we follow the syntax of VACUUM and
> > > > (7.4) CLUSTER, that the all-database truncate would just be "TRUNACATE".
> > > > That seems very risky to me.  I wonder if the risk is worth adding this
> > > > feature.
> > >
> > > I wouldn't care for that either.  The prior suggestion of "TRUNCATE tab
> > > CASCADE" (to truncate any tables with FK dependencies on the original
> > > target, instead of failing) seems more reasonable.
> > >
> >
> > Actually there seems to be an ancillary issue here:
> >
> > 21809=# truncate exception;
> > ERROR:  TRUNCATE cannot be used as table exception_notice_map references
> > this one via foreign key constraint $1
> > 21809=# TRUNCATE exception_notice_map ;
> > TRUNCATE TABLE
> > 21809=# truncate exception;
> > ERROR:  TRUNCATE cannot be used as table exception_notice_map references
> > this one via foreign key constraint $1
> > 21809=# select count(*) from exception_notice_map;
> >  count
> > -------
> >      0
> > (1 row)



Re: "truncate all"?

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Sun, 17 Aug 2003, Bruce Momjian wrote:
>> Is this a bug?

> I don't think so.  I'd say this is the expected behavior. Part of the
> point is that it fails without checking for matching rows.

To do anything else, you'd have to solve some locking and/or
race-condition problems: rows could be inserted in the other table
while the TRUNCATE runs.
        regards, tom lane


Re: "truncate all"?

From
Robert Treat
Date:
On Sun, 2003-08-17 at 00:42, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Sun, 17 Aug 2003, Bruce Momjian wrote:
> >> Is this a bug?
> 
> > I don't think so.  I'd say this is the expected behavior. Part of the
> > point is that it fails without checking for matching rows.
> 
> To do anything else, you'd have to solve some locking and/or
> race-condition problems: rows could be inserted in the other table
> while the TRUNCATE runs.
> 

Seems like you'll have that issue with truncate all wont you? I guess
we'll assume that if you use the cascade statement you understand these
risks and accept them.

Really my previous email was simply to point out to anyone implementing
the truncate cascade that truncate currently doesn't care if there is
really any data in the dependent tables, just that there are dependent
tables. 

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



Re: "truncate all"?

From
Tom Lane
Date:
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Sun, 2003-08-17 at 00:42, Tom Lane wrote:
>> To do anything else, you'd have to solve some locking and/or
>> race-condition problems: rows could be inserted in the other table
>> while the TRUNCATE runs.

> Seems like you'll have that issue with truncate all wont you? I guess
> we'll assume that if you use the cascade statement you understand these
> risks and accept them.

Yeah.  A TRUNCATE ALL would need exclusive lock on every table.  If
there are any other transactions running, the odds of getting all those
locks without deadlocking are pretty low.  TRUNCATE CASCADE would also
have a risk of failing due to deadlock (but with fewer tables in play
it'd have a smaller risk).  TRUNCATE RESTRICT should *not* create a
deadlock risk IMHO, and that means it can't lock other tables.
        regards, tom lane


Re: "truncate all"?

From
Bruce Momjian
Date:
TODO updated:
* Allow TRUNCATE ... CASCADE/RESTRICT

---------------------------------------------------------------------------

Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > On Sun, 2003-08-17 at 00:42, Tom Lane wrote:
> >> To do anything else, you'd have to solve some locking and/or
> >> race-condition problems: rows could be inserted in the other table
> >> while the TRUNCATE runs.
> 
> > Seems like you'll have that issue with truncate all wont you? I guess
> > we'll assume that if you use the cascade statement you understand these
> > risks and accept them.
> 
> Yeah.  A TRUNCATE ALL would need exclusive lock on every table.  If
> there are any other transactions running, the odds of getting all those
> locks without deadlocking are pretty low.  TRUNCATE CASCADE would also
> have a risk of failing due to deadlock (but with fewer tables in play
> it'd have a smaller risk).  TRUNCATE RESTRICT should *not* create a
> deadlock risk IMHO, and that means it can't lock other tables.
> 
>             regards, tom lane
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073