Message-ID: <d815344e-a6a2-617a-d35f-012e5e89f29f@gmail.com>
Date: Tue, 30 Apr 2024 20: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>
 <403075e1-4d60-09e2-a06a-749cd9d88f66@gmail.com>
 <ZioEJafHC95wjfMk@ip-10-97-1-34.eu-west-3.compute.internal>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <ZioEJafHC95wjfMk@ip-10-97-1-34.eu-west-3.compute.internal>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d815344e-a6a2-617a-d35f-012e5e89f29f%40gmail.com>
Precedence: bulk

Hi Bertrand,

25.04.2024 10:20, Bertrand Drouvot wrote:
> postgres=# CREATE FUNCTION f() RETURNS int LANGUAGE SQL RETURN f() + 1;
> ERROR:  cache lookup failed for function 16400
>
> This stuff does appear before we get a chance to call the new depLockAndCheckObject()
> function.
>
> I think this is what Tom was referring to in [1]:
>
> "
> So the only real fix for this would be to make every object lookup in the entire
> system do the sort of dance that's done in RangeVarGetRelidExtended.
> "
>
> The fact that those kind of errors appear also somehow ensure that no orphaned
> dependencies can be created.

I agree; the only thing that I'd change here, is the error code.

But I've discovered yet another possibility to get a broken dependency.
Please try this script:
res=0
numclients=20
for ((i=1;i<=100;i++)); do
for ((c=1;c<=numclients;c++)); do
   echo "
CREATE SCHEMA s_$c;
CREATE CONVERSION myconv_$c FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
ALTER CONVERSION myconv_$c SET SCHEMA s_$c;
   " | psql >psql1-$c.log 2>&1 &
   echo "DROP SCHEMA s_$c RESTRICT;" | psql >psql2-$c.log 2>&1 &
done
wait
pg_dump -f db.dump || { echo "on iteration $i"; res=1; break; }
for ((c=1;c<=numclients;c++)); do
   echo "DROP SCHEMA s_$c CASCADE;" | psql >psql3-$c.log 2>&1
done
done
psql -c "SELECT * FROM pg_conversion WHERE connamespace NOT IN (SELECT oid FROM pg_namespace);"

It fails for me (with the v4 patch applied) as follows:
pg_dump: error: schema with OID 16392 does not exist
on iteration 1
   oid  | conname  | connamespace | conowner | conforencoding | contoencoding |      conproc      | condefault
-------+----------+--------------+----------+----------------+---------------+-------------------+------------
  16396 | myconv_6 |        16392 |       10 |              8 |             6 | iso8859_1_to_utf8 | f

Best regards,
Alexander