Horton

[BUG] pg_dump does not properly deal with BEGIN ATOMIC function

Sami Imseih <simseih@amazon.com>

View thread View on postgr.es
From: "Imseih (AWS), Sami" <simseih@amazon.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-05-31T21:51:25Z
Lists: pgsql-hackers

Attachments

Hi,

What appears to be a pg_dump/pg_restore bug was observed with the new
BEGIN ATOMIC function body syntax introduced in Postgres 14.

Dependencies inside a BEGIN ATOMIC function cannot be resolved
if those dependencies are dumped after the function body. The repro
case is when a primary key constraint is used in a ON CONFLICT ON CONSTRAINT
used within the function.

With the attached repro, pg_restore fails with

pg_restore: error: could not execute query: ERROR:  constraint "a_pkey" for table "a" does not exist
Command was: CREATE FUNCTION public.a_f(c1_in text, c2 integer DEFAULT 60) RETURNS void


I am not sure if the answer if to dump functions later on in the process.

Would appreciate some feedback on this issue.

Regards,

Sami Imseih
Amazon Web Services (AWS)

Commits

  1. Doc: explain about dependency tracking for new-style SQL functions.

  2. Fix pg_dump's failure to honor dependencies of SQL functions.

  3. Fix misuse of pg_log_info() for details/hints.