Feedback-ID: i76984098:Fastmail
Message-ID: <b5778c67-0a7a-4da8-bc17-e4343b2b10cb@aklaver.com>
Date: Thu, 29 Jan 2026 07:09:42 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: UNLOGGED table CREATEd on one connection not immediately visible
 to another connection
To: Geoff Winkless <pg@ukku.uk>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CAEzk6ff5yHiJWDCoLcmqwC36O=VqLEHjbU2pcpYpOvdScM3t5g@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEzk6ff5yHiJWDCoLcmqwC36O=VqLEHjbU2pcpYpOvdScM3t5g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/b5778c67-0a7a-4da8-bc17-e4343b2b10cb%40aklaver.com>
Precedence: bulk

On 1/29/26 07:04, Geoff Winkless wrote:
> Hi
> 
> In our application we have a situation where once a day one process
> CREATEs an UNLOGGED table and INSERTs several hundred records using
> individual queries (no explicit transactions) all of which return
> successfully. We then send the ID of the table that we have created
> over a TCP socket to a second process, which runs a query that JOINs
> against that new table.
> 
> Unfortunately quite often the second process is getting a
> PGRES_FATAL_ERROR with
> 
> Primary: relation "qreftmp750" does not exist
> 
> Now (and this is very important) this appears to be a race condition,
> because when that process immediately retries the same query (which we
> do when we get FATAL_ERROR) it sometimes works on the second or third
> (or even 11th) attempt.
> 
> If we were somehow failing to create the table then the retries would
> never work, and we absolutely don't send the qreftmp ID to the second
> process until we've successfully INSERTed all of the records, so the
> race isn't on the application side. There's no explicit transactions
> in either process involved, they all just use implicit autocommit, so
> I don't see that this can be a DDL versioning issue.
> 
> I'm loathe to point the finger at PG because I'm sure that if this
> were a real issue it would have been flagged up well before now, but
> I've been staring at our code for days and I'm stumped. Any
> suggestions?

Provide the code for the procedure(s) that create the table and send the 
ID to the other process.

Question, why is this not run in a single process?

> 
> Thanks
> 
> Geoff
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com