Content-Type: multipart/alternative;
 boundary="------------Im8xBPm3G0bHLDTpGEgVT8RQ"
Message-ID: <403075e1-4d60-09e2-a06a-749cd9d88f66@gmail.com>
Date: Thu, 25 Apr 2024 09:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Avoid orphaned objects dependencies, take 3
Content-Language: en-US
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
References: <ZiYjn0eVc7pxVY45@ip-10-97-1-34.eu-west-3.compute.internal>
 <b206ef05-275d-7306-9ee2-ece738beb371@gmail.com>
 <ZiZBZSgWuSC0C1RJ@ip-10-97-1-34.eu-west-3.compute.internal>
 <a0e850b9-31c6-86cd-351d-6b8a7a1d6bf7@gmail.com>
 <ZidAHRJyMzPZ5JtJ@ip-10-97-1-34.eu-west-3.compute.internal>
 <Ziff3vGRKv1LUjY7@ip-10-97-1-34.eu-west-3.compute.internal>
 <ZijE6UBCo02U2TXJ@ip-10-97-1-34.eu-west-3.compute.internal>
 <62310067-6dc4-97ab-6724-7d3602b5056d@gmail.com>
 <ZinjZmaMEEkOSfZ0@ip-10-97-1-34.eu-west-3.compute.internal>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <ZinjZmaMEEkOSfZ0@ip-10-97-1-34.eu-west-3.compute.internal>
Archived-At: <https://www.postgresql.org/message-id/403075e1-4d60-09e2-a06a-749cd9d88f66%40gmail.com>
Precedence: bulk

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

Hi,

25.04.2024 08:00, Bertrand Drouvot wrote:
>
>> though concurrent create/drop operations can still produce
>> the "cache lookup failed" error, which is probably okay, except that it is
>> an INTERNAL_ERROR, which assumed to be not easily triggered by users.
> I did not see any of those "cache lookup failed" during my testing with/without
> your script. During which test(s) did you see those with v3 applied?

You can try, for example, table-trigger, or other tests that check for
"cache lookup failed" psql output only (maybe you'll need to increase the
iteration count). For instance, I've got (with v4 applied):
2024-04-25 05:48:08.102 UTC [3638763] ERROR:  cache lookup failed for function 20893
2024-04-25 05:48:08.102 UTC [3638763] STATEMENT:  CREATE TRIGGER modified_c1 BEFORE UPDATE OF c ON t
         FOR EACH ROW WHEN (OLD.c <> NEW.c) EXECUTE PROCEDURE trigger_func('modified_c');

Or with function-function:
2024-04-25 05:52:31.390 UTC [3711897] ERROR:  cache lookup failed for function 32190 at character 54
2024-04-25 05:52:31.390 UTC [3711897] STATEMENT:  CREATE FUNCTION f1() RETURNS int LANGUAGE SQL RETURN f() + 1;
--
2024-04-25 05:52:37.639 UTC [3720011] ERROR:  cache lookup failed for function 34465 at character 54
2024-04-25 05:52:37.639 UTC [3720011] STATEMENT:  CREATE FUNCTION f1() RETURNS int LANGUAGE SQL RETURN f() + 1;

> Attached v4, simply adding more tests to v3.

Thank you for working on this!

Best regards,
Alexander
--------------Im8xBPm3G0bHLDTpGEgVT8RQ
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Hi, <br>
      <br>
      25.04.2024 08:00, Bertrand Drouvot wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:ZinjZmaMEEkOSfZ0@ip-10-97-1-34.eu-west-3.compute.internal"><br>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">though concurrent create/drop operations can still produce
the "cache lookup failed" error, which is probably okay, except that it is
an INTERNAL_ERROR, which assumed to be not easily triggered by users.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
I did not see any of those "cache lookup failed" during my testing with/without
your script. During which test(s) did you see those with v3 applied?</pre>
    </blockquote>
    <br>
    You can try, for example, table-trigger, or other tests that check
    for<br>
    "cache lookup failed" psql output only (maybe you'll need to
    increase the<br>
    iteration count). For instance, I've got (with v4 applied):<br>
    2024-04-25 05:48:08.102 UTC [3638763] ERROR:  cache lookup failed
    for function 20893<br>
    2024-04-25 05:48:08.102 UTC [3638763] STATEMENT:  CREATE TRIGGER
    modified_c1 BEFORE UPDATE OF c ON t<br>
            FOR EACH ROW WHEN (OLD.c &lt;&gt; NEW.c) EXECUTE PROCEDURE
    trigger_func('modified_c');<br>
    <br>
    Or with function-function:<br>
    2024-04-25 05:52:31.390 UTC [3711897] ERROR:  cache lookup failed
    for function 32190 at character 54<br>
    2024-04-25 05:52:31.390 UTC [3711897] STATEMENT:  CREATE FUNCTION
    f1() RETURNS int LANGUAGE SQL RETURN f() + 1;<br>
    --<br>
    2024-04-25 05:52:37.639 UTC [3720011] ERROR:  cache lookup failed
    for function 34465 at character 54<br>
    2024-04-25 05:52:37.639 UTC [3720011] STATEMENT:  CREATE FUNCTION
    f1() RETURNS int LANGUAGE SQL RETURN f() + 1;<span
      style="font-family:monospace"><span
        style="color:#000000;background-color:#ffffff;"><br>
        <br>
      </span></span>
    <blockquote type="cite"
      cite="mid:ZinjZmaMEEkOSfZ0@ip-10-97-1-34.eu-west-3.compute.internal">
      <pre class="moz-quote-pre" wrap="">Attached v4, simply adding more tests to v3.
</pre>
    </blockquote>
    <br>
    Thank you for working on this!<br>
    <br>
    Best regards,<br>
    Alexander<br>
  </body>
</html>

--------------Im8xBPm3G0bHLDTpGEgVT8RQ--