Hi Bertrand,
24.04.2024 11:38, Bertrand Drouvot wrote:
Please find attached v2 that should not produce the issue anymore (I launched a
lot of attempts without any issues). v1 was not strong enough as it was not
always checking for the dependent object existence. v2 now always checks if the
object still exists after the additional lock acquisition attempt while
recording
the dependency.
I still need to think about v2 but in the meantime could you please also give
v2 a try on you side?
I gave more thought to v2 and the approach seems reasonable to me. Basically
what
it does is that in case the object is already dropped before we take the new
lock
(while recording the dependency) then the error message is a "generic" one
(means
it does not provide the description of the "already" dropped object). I think it
makes sense to write the patch that way by abandoning the patch's ambition to
tell the description of the dropped object in all the cases.
Of course, I would be happy to hear others thought about it.
Please find v3 attached (which is v2 with more comments).
Thank you for the improved version!
I can confirm that it fixes that case.
I've also tested other cases that fail on master (most of them also fail
with v1), please try/look at the attached script. (There could be other
broken-dependency cases, of course, but I think I've covered all the
representative ones.)
All tested cases work correctly with v3 applied — I couldn't get broken
dependencies, 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.
Best regards,
Alexander
if [ "$1" == "function-schema" ]; then
res=1
for ((i=1;i<=300;i++)); do
( { for ((n=1;n<=20;n++)); do echo "DROP SCHEMA s;"; done } | psql )
>psql1.log 2>&1 &
echo "
CREATE SCHEMA s;
CREATE FUNCTION s.func1() RETURNS int LANGUAGE SQL AS 'SELECT 1;';
CREATE FUNCTION s.func2() RETURNS int LANGUAGE SQL AS 'SELECT 2;';
CREATE FUNCTION s.func3() RETURNS int LANGUAGE SQL AS 'SELECT 3;';
CREATE FUNCTION s.func4() RETURNS int LANGUAGE SQL AS 'SELECT 4;';
CREATE FUNCTION s.func5() RETURNS int LANGUAGE SQL AS 'SELECT 5;';
" | psql >psql2.log 2>&1 &
wait
psql -c "DROP SCHEMA s CASCADE" >psql3.log 2>&1
done
echo "
SELECT pg_identify_object('pg_proc'::regclass, pp.oid, 0), pp.oid FROM pg_proc
pp
LEFT JOIN pg_namespace pn ON pp.pronamespace = pn.oid WHERE pn.oid IS NULL" |
psql
sleep 1
grep 'was terminated' server.log || res=0
fi
if [ "$1" == "function-function" ]; then
res=1
for ((i=1;i<=1000;i++)); do
( { for ((n=1;n<=20;n++)); do echo "DROP FUNCTION f();"; done } | psql )
>psql1.log 2>&1 &
( echo "
CREATE FUNCTION f() RETURNS int LANGUAGE SQL RETURN 1;
CREATE FUNCTION f1() RETURNS int LANGUAGE SQL RETURN f() + 1;
CREATE FUNCTION f2() RETURNS int LANGUAGE SQL RETURN f() + 2;
CREATE FUNCTION f3() RETURNS int LANGUAGE SQL RETURN f() + 3;
CREATE FUNCTION f4() RETURNS int LANGUAGE SQL RETURN f() + 4;
CREATE FUNCTION f5() RETURNS int LANGUAGE SQL RETURN f() + 5;
" | psql ) >psql2.log 2>&1 &
wait
echo "ALTER FUNCTION f1() RENAME TO f01; SELECT f01()" | psql 2>&1 | grep
'cache lookup failed' && { echo "on iteration $i"; res=1; break; }
psql -c "DROP FUNCTION f() CASCADE" >psql3.log 2>&1
done
grep -A2 'cache lookup failed' server.log || res=0
fi
if [ "$1" == "function-rettype" ]; then
res=0
for ((i=1;i<=5000;i++)); do
( echo "
CREATE DOMAIN id AS int;
CREATE FUNCTION f1() RETURNS id LANGUAGE SQL RETURN 1;
CREATE FUNCTION f2() RETURNS id LANGUAGE SQL RETURN 2;
CREATE FUNCTION f3() RETURNS id LANGUAGE SQL RETURN 3;
CREATE FUNCTION f4() RETURNS id LANGUAGE SQL RETURN 4;
CREATE FUNCTION f5() RETURNS id LANGUAGE SQL RETURN 5;
" | psql ) >psql1.log 2>&1 &
( echo "SELECT pg_sleep(random() / 100); DROP DOMAIN id" | psql ) >psql2.log
2>&1 &
wait
psql -c "SELECT f1()" 2>&1 | grep 'cache lookup failed' && { echo "on iteration
$i"; res=1; break; }
psql -c "DROP DOMAIN id CASCADE" >psql3.log 2>&1
done
[ $res == 1 ] && psql -c "SELECT pn.oid, proname, pronamespace, proowner,
prolang, prorettype FROM pg_proc pp INNER JOIN pg_namespace pn ON
(pp.pronamespace = pn.oid) WHERE pn.nspname='public'"
fi
if [ "$1" == "function-argtype" ]; then
res=0
for ((i=1;i<=5000;i++)); do
( echo "
CREATE DOMAIN id AS int;
CREATE FUNCTION f1(i id) RETURNS int LANGUAGE SQL RETURN 1;
CREATE FUNCTION f2(i id) RETURNS int LANGUAGE SQL RETURN 2;
CREATE FUNCTION f3(i id) RETURNS int LANGUAGE SQL RETURN 3;
CREATE FUNCTION f4(i id) RETURNS int LANGUAGE SQL RETURN 4;
CREATE FUNCTION f5(i id) RETURNS int LANGUAGE SQL RETURN 5;
" | psql ) >psql1.log 2>&1 &
( echo "SELECT pg_sleep(random() / 1000); DROP DOMAIN id" | psql ) >psql2.log
2>&1 &
wait
psql -c "SELECT f1(1)" 2>&1 | grep 'cache lookup failed' && { echo "on
iteration $i"; res=1; break; }
psql -q -c "DROP DOMAIN id CASCADE" >/dev/null 2>&1
done
grep -A1 'cache lookup failed' server.log || res=0
fi
if [ "$1" == "domain-domain" ]; then
res=0
for ((i=1;i<=30;i++)); do
{ echo "CREATE DOMAIN id AS int;"; for ((d=1;d<100;d++)); do echo "CREATE
DOMAIN id$d AS id;"; done; echo "DROP DOMAIN id CASCADE;"; } | psql >psql0.log
2>&1
{ for ((n=1;n<=100;n++)); do echo "CREATE DOMAIN id AS int;"; for
((d=1;d<100;d++)); do echo "CREATE DOMAIN id$d AS id;"; done; echo "DROP DOMAIN
id CASCADE;"; done; } | ON_ERROR_STOP=1 psql >psql1.log 2>&1 &
{ for ((n=1;n<=100;n++)); do echo "SELECT pg_sleep(random() / 100); DROP DOMAIN
id;"; done; } | psql >psql2.log 2>&1 &
wait
psql -c "CREATE TABLE t1(i id1)" 2>&1 | grep 'cache lookup failed' && { echo
"on iteration $i"; res=1; break; }
done
grep -A1 'cache lookup failed' server.log
psql -c "\\dD+ i*"
fi
if [ "$1" == "table-trigger" ]; then
res=1
for ((i=1;i<=100;i++)); do
psql -c "DROP TABLE IF EXISTS t"
psql -c "CREATE TABLE t(i int, c text)"
( { for ((n=1;n<=30;n++)); do echo "DROP FUNCTION trigger_func();"; done } |
psql ) >>psql1.log 2>&1 &
( echo "
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'',
TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;';
CREATE TRIGGER modified_c1 BEFORE UPDATE OF c ON t
FOR EACH ROW WHEN (OLD.c <> NEW.c) EXECUTE PROCEDURE trigger_func('modified_c');
CREATE TRIGGER modified_c2 BEFORE UPDATE OF c ON t
FOR EACH ROW WHEN (OLD.c <> NEW.c) EXECUTE PROCEDURE trigger_func('modified_c');
CREATE TRIGGER modified_c3 BEFORE UPDATE OF c ON t
FOR EACH ROW WHEN (OLD.c <> NEW.c) EXECUTE PROCEDURE trigger_func('modified_c');
CREATE TRIGGER modified_c4 BEFORE UPDATE OF c ON t
FOR EACH ROW WHEN (OLD.c <> NEW.c) EXECUTE PROCEDURE trigger_func('modified_c');
CREATE TRIGGER modified_c5 BEFORE UPDATE OF c ON t
FOR EACH ROW WHEN (OLD.c <> NEW.c) EXECUTE PROCEDURE trigger_func('modified_c');
" | psql ) >>psql2.log 2>&1 &
wait
psql -c "DROP FUNCTION trigger_func() CASCADE" >psql3.log 2>&1
psql -c "INSERT INTO t VALUES(1, 'a')"
psql -c "UPDATE t SET c='b'" 2>&1 | grep 'cache lookup failed' && { echo "on
iteration $i"; res=1; break; }
done
grep -A2 'cache lookup failed' server.log || res=0
fi
if [ "$1" == "table-coltype" ]; then
res=0
numclients=20
for ((i=1;i<=50;i++)); do
for ((c=1;c<=numclients;c++)); do
echo "SELECT pg_sleep(random() / 2000); DROP DOMAIN id_$c RESTRICT" | psql
>psql1-$c.log 2>&1 &
echo "
CREATE DOMAIN id_$c int;
CREATE TABLE tbl_$c (i id_$c);
" | psql >psql2-$c.log 2>&1 &
done
wait
for ((c=1;c<=numclients;c++)); do
echo "SELECT * FROM tbl_$c" | psql 2>&1 | grep 'cache lookup failed' && {
echo "on iteration $i"; res=1; break; }
echo "DROP DOMAIN id_$c CASCADE; DROP TABLE tbl_$c" | psql >psql3-$c.log 2>&1
done
[ $res == 1 ] && break;
done
grep -A2 'cache lookup failed' server.log
fi
if [ "$1" == "type-shelltype" ]; then
res=0
numclients=40
for ((i=1;i<=100;i++)); do
for ((c=1;c<=numclients;c++)); do
echo "DROP FUNCTION IF EXISTS xint8in_$c, xint8out_$c CASCADE" | psql
>psql0-$c.log 2>&1
done
for ((c=1;c<=numclients;c++)); do
echo "
CREATE TYPE xint8_$c;
CREATE FUNCTION xint8in_$c(cstring) RETURNS xint8_$c IMMUTABLE STRICT LANGUAGE
INTERNAL AS 'int8in';
CREATE FUNCTION xint8out_$c(xint8_$c) RETURNS cstring IMMUTABLE STRICT LANGUAGE
INTERNAL AS 'int8out';
CREATE TYPE xint8_$c(input = xint8in_$c, output = xint8out_$c, like = int8);
" | psql >psql1-$c.log 2>&1 &
echo "SELECT pg_sleep(random() / 1000); DROP TYPE xint8_$c CASCADE;" | psql
>psql2-$c.log 2>&1 &
done
wait
grep 'TRAP' server.log && { echo "on iteration $i"; res=1; break; }
done
fi
if [ "$1" == "type-schema" ]; then
res=0
numclients=20
for ((i=1;i<=100;i++)); do
for ((c=1;c<=numclients;c++)); do
echo "
CREATE SCHEMA s_$c;
CREATE DOMAIN s_$c.dom_$c int4;
CREATE TABLE tbl_$c (i dom_$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
fi
if [ "$1" == "ts_template-schema" ]; then
res=0
numclients=20
for ((i=1;i<=100;i++)); do
for ((c=1;c<=numclients;c++)); do
echo "
CREATE SCHEMA s_$c;
CREATE TEXT SEARCH TEMPLATE s_$c.tst_$c(lexize=dsimple_lexize);
" | 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
fi
if [ "$1" == "cast-schema" ]; then
res=0
numclients=20
for ((i=1;i<=100;i++)); do
for ((c=1;c<=numclients;c++)); do
echo "
CREATE SCHEMA s_$c;
CREATE DOMAIN s_$c.dom_$c int4;
CREATE TABLE tbl_$c (i dom_$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
fi
if [ "$1" == "cast-function" ]; then
res=0
echo "
CREATE TYPE tt;
CREATE FUNCTION tt_in(cstring) RETURNS tt AS 'textin' LANGUAGE internal STRICT
IMMUTABLE;
CREATE FUNCTION tt_out(tt) RETURNS cstring AS 'textout' LANGUAGE internal
STRICT IMMUTABLE;
CREATE TYPE tt (internallength = variable, input = tt_in, output = tt_out,
alignment = int4);
" | psql
numclients=2
for ((n=1;n<=500;n++)); do
for ((i=1;i<=$numclients;i++)); do
cat << 'EOF' | psql >psql-$i.log 2>&1 &
DROP CAST (int4 AS tt);
CREATE FUNCTION cf(int4) RETURNS tt LANGUAGE SQL AS
$$ SELECT ($1::text)::tt; $$;
CREATE CAST (int4 AS tt) WITH FUNCTION cf(int4) AS IMPLICIT;
DROP FUNCTION cf(int4) CASCADE;
EOF
done
wait
echo "SELECT 1234::int4::tt;" | psql 2>&1 | grep 'cache lookup failed for
function'
pg_dump -f db.dump || { echo "on iteration $i"; res=1; break; }
done
fi
if [ "$1" == "server-fdw_wrapper" ]; then
res=0
numclients=20
for ((i=1;i<=200;i++)); do
for ((c=1;c<=numclients;c++)); do
echo "
CREATE FOREIGN DATA WRAPPER fdw_wrapper_$c;
CREATE SERVER tst_$c FOREIGN DATA WRAPPER fdw_wrapper_$c;
" | psql >psql1-$c.log 2>&1 &
echo "DROP FOREIGN DATA WRAPPER fdw_wrapper_$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 FOREIGN DATA WRAPPER fdw_wrapper_$c CASCADE;" | psql >psql3-$c.log
2>&1
done
done
fi
