IF EXISTS doesn't stop errors on RDS Postgres 11.19 instance

Question

Recently one of our RDS Postgres databases was upgraded to version 11.19. Since then, we're receiving errors when running a database migration.

I can't replicate this issue locally, it appears to be restricted to the RDS instance only.

Running this query on an empty database:

ALTER TABLE IF EXISTS ONLY public.telescope_entries_tags 
   DROP CONSTRAINT IF EXISTS telescope_entries_tags_entry_uuid_foreign;

results in the following error on RDS:

Query 1 ERROR: ERROR:  relation "public.telescope_entries_tags" does not exist

However, when running this on a local database (also Postgres 11.19) I get the following response:

NOTICE:  relation "telescope_entries_tags" does not exist, skipping

Query 1 OK: ALTER TABLE

Note, this code was auto generated inside a pg_restore command, wrapped inside a laravel function, wrapped inside some CI/CD functionality. This is the simplest case I can boil it down to.

The code was generated using pg_restore (PostgreSQL) 13.11.

I've checked through the differences in all the settings that are shown when running "SHOW ALL;", and copied whatever I could to the local instance, and still can't get this code to show an error locally.

I just don't understand why this is classified as an error on RDS. To my mind it should be a notice.

Any advice would be appreciated.

Answer 1

We have observed this in PostgreSQL 14.7, specifically on AWS RDS. https://stackoverflow.com/a/76475035/13970149

It looks like some versions of PostgreSQL on AWS RDS may return an Error instead of a Notice if the relation does not exist. The expected behavior is ALTER TABLE IF EXISTS (and other similar IF EXISTS commands) should safely skip without error.

From the other StackOverflow post: """ Hello I have opened a support ticket with AWS, and confirmed this behavior is known for minor version 11.19, 12.14, 13.10 and 14.7 and was corrected in the next minor versions : 11.20, 12.15, 13.11 and 14.8. """

In your case, upgrading PostgreSQL engine minor version may resolve your problem. You could test this with a simple query like...

alter table if exists definitely_not_a_real_table ADD COLUMN IF NOT EXISTS not_going_to_get_added varchar default CURRENT_USER not null;

Expected output: NOTICE: relation "definitely_not_a_real_table" does not exist, skipping ALTER TABLE

Query returned successfully in 61 msec.

I would also recommend reaching out to AWS Support, as I could not find public documentation of the issue.