Re: TRUNCATE - Mailing list pgsql-hackers

I still highly recommend that it be a drop foreign key, grab data,
truncate, import data, reapply foreign key (which will double check
your work) as I believe data and schema integrity should be high goals
of Postgresql (myself anyway).

However, I'd like to know what your doing.  ie.  Why is this method
the fastest and easiest way.

Given a dataset, how much (%age wise) do you generally modify when you
clean it up? And what is the general dataset size (half million
records?).

I'm making the assumption you almost never delete data (primary key
wise), otherwise foreign keyd data may no longer align.  I'm also
making the assumption your either the sole user of the database, or
have a long period where the database is not in use (overnight?).

What do you use to clean it up?  Custom script for each job?  Regular
expressions?  Simple spreadsheet like format filling in numbers?
Complete dump and replace of the data?


Lastly, would a data diff make it easier?  Compare the data between
the table (based on the primary key) and your working copy then update
old records as necessary to bring them up to date and insert new
records?

--
Rod
----- Original Message -----
From: "Joel Burton" <joel@joelburton.com>
To: "Rod Taylor" <rbt@zort.ca>; "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Hackers List" <pgsql-hackers@postgresql.org>
Sent: Monday, May 13, 2002 12:12 AM
Subject: Re: [HACKERS] TRUNCATE


> > >From my limited understanding of truncate in Oracle is it
requires the
> > user to first disable integrity constraints on the table before
> > truncate will run.
> >
> > In SQL Server that truncate will not allow truncate if foreign key
> > constraints exist, but does not execute user delete triggers.
> >
> > Can't remember nor confirm either of these now.  But, for
consistency
> > sake we should enforce the foreign key case.  But I really think
it
> > should apply to all constraints, system or user enforced (rules,
user
> > written triggers).
> >
> > Besides that, theres always Codds twelfth rule which I've always
> > liked:
> > The nonsubversion rule: If low-level access is permitted it should
not
> > bypass security or integrity rules.
>
> Dare I go against Codd, but, really, I've found it very convenient
to be
> able to export a single table, TRUNCATE it, clean up the data in
another
> program, and pull it back in. It's much more of a pain to have to
dump the
> whole db (neccessary or at least sanity preserving if there are lots
of
> complicated foreign key or trigger rules) or to drop/recreate the
> triggers/rules.
>
> The security issue is important, though: it's very likely that I
might want
> to let an certain class of user DELETE a record (with all the usual
> rules/triggers/RI applying), but not let them bypass all that to
TRUNCATE.
>
> But I still wouldn't want to see hassle-free truncation disappear in
the
> name of security or idiot-proofing, if there are reasonable
compromises.
>
> - J.
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim:
wjoelburton
> Knowledge Management & Technology Consultant
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>