Horton
Menu

test-broken-deps.sh.txt

text/plain

Download
Filename: test-broken-deps.sh.txt
Type: text/plain
Part: 0
Message: Re: Avoid orphaned objects dependencies, take 3 
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