Hi all,
We're facing a very strange situation at my organisation where a scheduled pg_restore task will consistently fail because postgres thinks there are functions that reference some of the tables to be dropped. Thing is, these functions are temporary functions whose creators have (long) since disconnected.
I have tried to boil down the real situation to a minimal example below. The idea is we create a table and a temporary function that references the backing type by way of its return type. Then we disconnect, and when we reconnect (even much later), and try to drop the table, we get:
ERROR: cannot drop table mytable because other objects depend on it
DETAIL: function pg_temp_3.mytempfunc() depends on type mytableHINT: Use DROP ... CASCADE to drop the dependent objects too.
(Obviously in the toy example I could just follow the CASCADE hint, but in practice the table drop is being done by pg_restore, which offers no such option as far as I know.)
You will note that in my example below, I've appended a thousand commented-out lines to the end of the function definition. If I remove these, the problem doesn't appear. So somehow the length of the function is a factor. Similarly, creating a temp table is also seemingly required to elicit the bug.
Tested with Postgres 14.2 on Ubuntu 20.04 and 13.2 on CentOS 7.
Cheers,
Miles
---
#!/bin/bash
dropdb mydb
createdb mydb
( cat <<'EOF'
create table mytable ();
create function pg_temp.mytempfunc()
returns mytable language plpgsql as $$
begin return null;
end;
EOF for i in {1..1000} do echo '--____________________________________________________________________________________________________________________' done cat <<'EOF'
$$;
create temp table mytemptable();
select pg_temp.mytempfunc();
EOF
) | psql mydb;
sleep 100
psql mydb -c "drop table mytable";