Content-Type: multipart/alternative;
 boundary="------------4EdpmPs9c6Wl68CboiAhjZwi"
Message-ID: <08e82e11-1018-4c01-b387-b2db5de6b455@risk-engineering.org>
Date: Fri, 21 Jun 2024 18:16:53 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Cache lookup failed for type 34813 (CREATE TYPE AS ENUM + P/B/E
 insert, processor-specific) / user error
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org
References: <2dcacb45-81c9-4c38-aa36-70e2dc8df487@risk-engineering.org>
 <595887.1718984150@sss.pgh.pa.us>
Content-Language: en-US
From: Eric Marsden <eric.marsden@risk-engineering.org>
In-Reply-To: <595887.1718984150@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/08e82e11-1018-4c01-b387-b2db5de6b455%40risk-engineering.org>
Precedence: bulk

This is a multi-part message in MIME format.
--------------4EdpmPs9c6Wl68CboiAhjZwi
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 21/06/2024 17:35, Tom Lane wrote:
> Eric Marsden<eric.marsden@risk-engineering.org>  writes:
>> I see a PostgreSQL error "cache lookup failed for type 34813" after the
>> following sequence of network requests over a single network connection
>> to PostgreSQL (which is otherwise idle):
>> CREATE TYPE rating AS ENUM('ungood', 'good')
>> CREATE TABLE act(name TEXT, value RATING)
>> INSERT INTO act VALUES('thoughtcrime', 'ungood') -- OK
>> INSERT INTO act VALUES('blackwhite', $1) -- with $1 = "good" / type oid for "rating" / text format

Thanks to very useful discussions on #postgresql (IRC), I have found out 
that this is a user error rather than a PostgreSQL bug. My PostgreSQL 
network protocol library maintains an oid <-> type-name cache that is 
populated at connection startup time. Due to improper cleanup in the 
tests, a old value for the type oid was remaining when testing on my own 
computer (but not in Docker nor for clean test runs on other machines), 
so the PostgreSQL error message is perfectly valid.

I have realized that I need to stop using this oid <-> type-name cache 
for types that are not PostgreSQL builtins, because there is no way of 
ensuring it's synchronized with the backend values.

Thanks, and apologies for the spurious report,
Eric

--------------4EdpmPs9c6Wl68CboiAhjZwi
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 21/06/2024 17:35, Tom Lane wrote:<br>
    </div>
    <blockquote type="cite" cite="mid:595887.1718984150@sss.pgh.pa.us">
      <pre class="moz-quote-pre" wrap="">Eric Marsden <a class="moz-txt-link-rfc2396E" href="mailto:eric.marsden@risk-engineering.org">&lt;eric.marsden@risk-engineering.org&gt;</a> writes:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">I see a PostgreSQL error "cache lookup failed for type 34813" after the 
following sequence of network requests over a single network connection 
to PostgreSQL (which is otherwise idle):
</pre>
      </blockquote>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">CREATE TYPE rating AS ENUM('ungood', 'good')
CREATE TABLE act(name TEXT, value RATING)
INSERT INTO act VALUES('thoughtcrime', 'ungood') -- OK
INSERT INTO act VALUES('blackwhite', $1) -- with $1 = "good" / type oid for "rating" / text format
</pre>
      </blockquote>
    </blockquote>
    <pre class="moz-signature" cols="72">
</pre>
    Thanks to very useful discussions on #postgresql (IRC), I have found
    out that this is a user error rather than a PostgreSQL bug. My
    PostgreSQL network protocol library maintains an oid &lt;-&gt;
    type-name cache that is populated at connection startup time. Due to
    improper cleanup in the tests, a old value for the type oid was
    remaining when testing on my own computer (but not in Docker nor for
    clean test runs on other machines), so the PostgreSQL error message
    is perfectly valid. <br>
    <br>
    I have realized that I need to stop using this oid &lt;-&gt;
    type-name cache for types that are not PostgreSQL builtins, because
    there is no way of ensuring it's synchronized with the backend
    values.<br>
    <br>
    Thanks, and apologies for the spurious report,<br>
    Eric<br>
    <br>
    <div id="grammalecte_menu_main_button_shadow_host"
      style="width: 0px; height: 0px;"></div>
  </body>
</html>

--------------4EdpmPs9c6Wl68CboiAhjZwi--