Re: [RESOLVED] INSERT does not finish except if it is carried

Hi all,

If I use "VACUUM ANALYSE maTable" after CREATE AS of the DROP/CREATE
AS/INSERT statements then INSERT commits in a few seconds.
Documentation says :"VACUUM ANALYZE: Updates statistics used by the
planner to determine the most efficient way to execute a query."

I don't understand really why it is necessary to use it but it works.

Mat

Matthieu Guamis a écrit :
> 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.
>>
>>
>