Thread: Drop table vs begin/end transaction

I have a table which I will populate every day from scratch (data coming
from a Foxpro system) and I am wandering if I could do something like:

begin transaction
drop table <name>
\i <create table script>
copy from <ascii file>
end transaction

Can this be done? In particular will the drop table honor begin/end?

My alternative is to use delete from <table>. Besides been slower I wonder
if this would not make my "vacuum analyze" run much slower.

Francisco Reyes <lists@natserv.com> writes:
> My alternative is to use delete from <table>. Besides been slower I wonder
> if this would not make my "vacuum analyze" run much slower.

Consider TRUNCATE TABLE, also.  However, that's not transactional,
so the drop/create/fill might be a better bet if you are worried
about bad data during the fill.

            regards, tom lane

On Sun, 18 Nov 2001, Tom Lane wrote:

> Francisco Reyes <lists@natserv.com> writes:
> > My alternative is to use delete from <table>. Besides been slower I wonder
> > if this would not make my "vacuum analyze" run much slower.
>
> Consider TRUNCATE TABLE

Thanks for the info. Didn't know about truncate table

> so the drop/create/fill might be a better bet if you are worried
> about bad data during the fill.

I just had such a case. The table I was importing had a '\' and postgreSQL
didn't like it. I guess it must have some special meaning.

> On Sun, 18 Nov 2001, Tom Lane wrote:
>
> > Francisco Reyes <lists@natserv.com> writes:
> > > My alternative is to use delete from <table>. Besides been slower I wonder
> > > if this would not make my "vacuum analyze" run much slower.
> >
> > Consider TRUNCATE TABLE

How about the effect of truncate/drop table vs the need to do vacuum.

For instance I was loading close to 800K records and at records 669,209
there was a bad character, '\', and the load stopped.

I was on another terminal and didn't know the load had crashed. I did a
'select count(*)' against the table and after a long while it came back
with a '0'. My concern is that all those records which were not loaded
seems to be "there" (marked deleted?).

Does this mean that failed transactions leave all inserted records
behind waiting for a vacuum? If I drop/truncate a table before my load will all
those deleted(?) records dissapear or at least not be associated with the
table anymore? Is it best just to do vacuum after the failed loads?

I plan to do a vacuum analyze when I finish merging the tables and before
I start running my reports, but wonder if just running vacuum may be a
good idea.