public inbox for [email protected]
help / color / mirror / Atom feedUNLOGGED table CREATEd on one connection not immediately visible to another connection
2+ messages / 2 participants
[nested] [flat]
* UNLOGGED table CREATEd on one connection not immediately visible to another connection
@ 2026-01-29 15:04 Geoff Winkless <[email protected]>
2026-01-29 15:09 ` Re: UNLOGGED table CREATEd on one connection not immediately visible to another connection Adrian Klaver <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Geoff Winkless @ 2026-01-29 15:04 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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?
Thanks
Geoff
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: UNLOGGED table CREATEd on one connection not immediately visible to another connection
2026-01-29 15:04 UNLOGGED table CREATEd on one connection not immediately visible to another connection Geoff Winkless <[email protected]>
@ 2026-01-29 15:09 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2026-01-29 15:09 UTC (permalink / raw)
To: Geoff Winkless <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-01-29 15:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-29 15:04 UNLOGGED table CREATEd on one connection not immediately visible to another connection Geoff Winkless <[email protected]>
2026-01-29 15:09 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox