BUG #18107: "policy ... for table ... already exists" errors when restore postgres database from dump

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #18107: "policy ... for table ... already exists" errors when restore postgres database from dump
Дата
Msg-id 18107-2668460d8c2804d2@postgresql.org
обсуждение исходный текст
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      18107
Logged by:          AntonioK
Email address:      antoniok.spb@gmail.com
PostgreSQL version: 15.4
Operating system:   Debian GNU/Linux 12 (bookworm)
Description:

I'm restoring postgresql database from dump on a fresh new server. Source
database uses pgcron extension, target server have pgcron extension
installed.

This is how I create dumps on "source" server:
$ pg_dumpall -h hostname -p 5435 -U myuser --roles-only | bzip2 -c -z >
dump-role.sql.bz2
$ pg_dump -C -h hostname -p 5435 -U myuser mydatabase | bzip2 -c -z >
dump-data.sql.bz2

This is how I restore dumps on empty "target" server:
$ grep -Eiv '(CREATE ROLE postgres|ALTER ROLE postgres .*PASSWORD)'
dump-role.sql | psql -Upostgres > /dev/null
$ psql -Upostgres -f dump-data.sql > /dev/null

(`grep` on the first command is used to left "postgres" superuser untouched
on a target server.)

When restoring from dump, I got two errors:
psql:/path/to/dump-data.sql:18831791: ERROR:  policy "cron_job_policy" for
table "job" already exists
psql:/path/to/dump-data.sql:18831798: ERROR:  policy
"cron_job_run_details_policy" for table "job_run_details" already exists

They refers to the following lines of dump:
-- Name: job cron_job_policy; Type: POLICY; Schema: cron; Owner: some_user
CREATE POLICY cron_job_policy ON cron.job USING ((username =
CURRENT_USER));
-- Name: job_run_details cron_job_run_details_policy; Type: POLICY; Schema:
cron; Owner: some_user
CREATE POLICY cron_job_run_details_policy ON cron.job_run_details USING
((username = CURRENT_USER));

I never created those POLICY by hands on "source" database. The guess is
(@see https://dba.stackexchange.com/a/331198/82463) that 'CREATE EXTENSION
... pg_cron ...' statement in the dump file is implicitly executes POLICY
creation, so 'CREATE POLICY ... ON cron...' statements in the dump file are
redundant and unnecessary.

Should we consider this as pg_dump bug?

Source server:
# grep PRETTY_NAME /etc/*rel*
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"

# psql -V
psql (PostgreSQL) 15.3 (Debian 15.3-1.pgdg120+1)

# dpkg -l | grep postgresql
ii  postgresql-15                   15.3-1.pgdg120+1               amd64
 
ii  postgresql-15-cron              1.5.2-1.pgdg120+1              amd64

ii  postgresql-client-10            10.23-1.pgdg120+1+b2           amd64
ii  postgresql-client-11            11.20-1.pgdg120+1              amd64

ii  postgresql-client-12            12.15-1.pgdg120+1              amd64

ii  postgresql-client-13            13.11-1.pgdg120+1              amd64

ii  postgresql-client-14            14.8-1.pgdg120+1               amd64

ii  postgresql-client-15            15.3-1.pgdg120+1               amd64

ii  postgresql-client-common        250.pgdg120+1                  all

ii  postgresql-common               250.pgdg120+1                  all
   
ii  postgresql-plpython3-15         15.3-1.pgdg120+1               amd64

ii  postgresql-server-dev-10        10.23-1.pgdg120+1+b2           amd64
ii  postgresql-server-dev-11        11.20-1.pgdg120+1              amd64

ii  postgresql-server-dev-12        12.15-1.pgdg120+1              amd64  
ii  postgresql-server-dev-13        13.11-1.pgdg120+1              amd64  
ii  postgresql-server-dev-14        14.8-1.pgdg120+1               amd64

ii  postgresql-server-dev-15        15.3-1.pgdg120+1               amd64

ii  postgresql-server-dev-all:amd64 250.pgdg120+1                  amd64

Target server:
# grep PRETTY_NAME /etc/*rel*
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"

# psql -V
psql (PostgreSQL) 15.4 (Debian 15.4-1.pgdg120+1)

# dpkg -l | grep postgresql
ii  postgresql-15                   15.4-1.pgdg120+1               amd64
 
ii  postgresql-15-cron              1.5.2-2.pgdg120+2              amd64

ii  postgresql-client-15            15.4-1.pgdg120+1               amd64

ii  postgresql-client-common        253.pgdg120+1                  all

ii  postgresql-common               253.pgdg120+1                  all
   
ii  postgresql-plpython3-15         15.4-1.pgdg120+1               amd64
ii  postgresql-server-dev-15        15.4-1.pgdg120+1               amd64


В списке pgsql-bugs по дате отправления:

Предыдущее
От: "Lepikhov Andrei"
Дата:
Сообщение: Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
Следующее
От: David Rowley
Дата:
Сообщение: Re: BUG #18103: bugs of concurrent merge into when use different join plan