Horton

Re: more ALTER .. DEPENDS ON EXTENSION fixes

Alvaro Herrera <alvherre@2ndquadrant.com>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: ahsan hadi <ahsan.hadi@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Date: 2020-02-28T13:44:51Z
Lists: pgsql-hackers 
On 2020-Feb-28, ahsan hadi wrote:


> Tested the pg_dump patch for dumping "ALTER .. DEPENDS ON EXTENSION" in case of indexes, functions, triggers etc. The "ALTER .. DEPENDS ON EXTENSION" is included in the dump. However in some case not sure why "ALTER INDEX.....DEPENDS ON EXTENSION" is repeated several times in the dump?

Hi, thanks for testing.

Are the repeated commands for the same index, same extension?  Did you
apply the same command multiple times before running pg_dump?

There was an off-list complaint that if you repeat the ALTER .. DEPENDS
for the same object on the same extension, then the same dependency is
registered multiple times.  (You can search pg_depend for "deptype = 'x'"
to see that).  I suppose that would lead to the line being output
multiple times by pg_dump, also.  Is that what you did?

If so: Patch 0002 is supposed to fix that problem, by raising an error
if the dependency is already registered ... though it occurs to me now
that it would be more in line with custom to make the command a silent
no-op.  In fact, doing that would cause old dumps (generated with
databases containing duplicated entries) to correctly restore a single
entry, without error.  Therefore my inclination now is to change 0002
that way and push and backpatch it ahead of 0001.

I realize just now that I have failed to verify what happens with
partitioned indexes.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. Add tab-completion for ALTER INDEX .. [NO] DEPENDS ON

  2. Add ALTER .. NO DEPENDS ON

  3. Add pg_dump support for ALTER obj DEPENDS ON EXTENSION

  4. Avoid duplicates in ALTER ... DEPENDS ON EXTENSION