Skip site navigation (1) Skip section navigation (2)

BUG #6482: Service terminates executing "With ... as ... UPDATE" query

From: dzolotarevskiy(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6482: Service terminates executing "With ... as ... UPDATE" query
Date: 2012-02-22 15:12:12
Message-ID: E1S0DrY-0007GQ-9O@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6482
Logged by:          Dmitriy
Email address:      dzolotarevskiy(at)gmail(dot)com
PostgreSQL version: 9.1.2
Operating system:   Windows Server 2003 r2 32bit
Description:        


All Postgres processes terinates.
In log you can find only 
LOG: server process (PID ...) was terminated by exception OxC0000005.

All queries works fine when there is only one session. When Sessions more
then 20 it starts terminating with some probability. 
On my production server it stops about 50 times per day.
On my dev pc, with testing utility that uses 50 sessions at one time, it
falls down in about 1 minute.

Investigating problem i have found the  "fatal" query and excluding part by
part found out that new syntax produce that exception.

To reproduce create 3 tables and execute my query in at least 20
simultaneous threads, each one should use separate session.

Tables:
user_task (pk id, fk user_proxy_id)
user_proxy (pk id, fk proxy_last_id, date)
proxy_last (pk id, last_used)

Query:

with mup as
    ( 
    UPDATE user_proxy 
    SET date = now() 
    WHERE id=
       (select user_proxy_id
        from user_task 
        where id=420
        ) 
    returning proxy_last_id
    )
UPDATE proxy_last 
SET last_used = now() 
WHERE id=(select proxy_last_id 
          from mup);


I have solved the problem rewriting query to old syntax.


Responses

pgsql-bugs by date

Next:From: Sergey BurladyanDate: 2012-02-22 18:37:31
Subject: Re: BUG #6480: NLS text width problem
Previous:From: michal.rakoczyDate: 2012-02-22 12:28:38
Subject: BUG #6481: postgres shuts down unexpectedly

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group