Why drop and recreate the table? Why not TRUNCATE it?

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailtogsql-novice-owner@postgresql.org] On Behalf Of Matthieu Guamis
Sent: Wednesday, September 13, 2006 6:15 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] INSERT does not finish except if it is carried out a

Hello,

PostgreSQL 8.1 is running on Ubuntu 6.06 server edition.

Please trust me, when I use DELETE/INSERT/INSERT statements the job is
done in a few seconds whereas with DROP/CREATE AS /SELECT it takes
several minutes (to achieve the SELECT statement). But in this last
case, if I wait few minutes between CREATE AS and SELECT then the
SELECT is done in a few seconds.

Sorry for previous syntax errors (I did not paste statements but wrote
them with simplified names for fields and tables... it may explain the
unmatched open parenthesis).

Could you tell me more about some possible causes of the delay?

Regards


Michael Fuhr a écrit :
> [Please don't post HTML.]
>
> On Tue, Sep 12, 2006 at 02:09:40PM +0200, Matthieu Guamis wrote:
>
>> During the execution of the following requests, INSERT does not finish
>> except if it is carried out a few minutes after the
>> creation of the table. How to explain this latency time?
>>
> [...]
>
>> insert into maTable (select * from
>> ((select a.id1 ,b.id2 ,0
>> from maTable a, maTable b
>> group by a.id1,b.id2
>> order by b.id2,a.id1)
>> EXCEPT
>> (select c.id1 ,c.id2 ,0
>> from maTable c
>> ))as tt;
>>
>
> This statement isn't syntactically correct; it has an unmatched
> open parenthesis. If I paste the statement into psql it appears
> to hang, presumably because the parser thinks it's incomplete and
> is waiting for more input. Are you sure you've diagnosed the problem
> correctly? If so then please post a test case without errors so
> others can attempt to duplicate the problem.
>
> What version of PostgreSQL are you running and on what platform?
> What client interface are you using?
>
>
>> DROP and CREATE do their job but INSERT does not finish if it is
>> carried out immediately after the CREATE. On the other hand
>> if it is carried out a few minutes (~5min) later then INSERT commits
>> in a few seconds.
>>
>
> A five-minute delay could hint at some possible causes, but first
> let's find out whether syntax is the problem.
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend