Thanks Tom,
Le 26/11/2016 à 01:59, Tom Lane a écrit :
> phb07@apra.asso.fr writes:
>> Let's create a table with a serial column (outside any extension).
>> Then let's link the table with its sequence to a new extension using ALTER
>> EXTENSION ADD ... in the extension script.
>> Even though the documentation is not clear on that point, it is necessary to
>> explicitly link the sequence to the extension, not to be in trouble with
>> pg_dump for instance.
>> So far so good. Everything reacts as if the table had been created inside
>> the extension.
>> But an error occurs when I try to drop this table in the next extension
>> upgrade script:
>> ERROR: cannot drop table old_tbl1 because other objects depend on it
>> DETAIL: extension ext1 depends on table old_tbl1
>> HINT: Use DROP ... CASCADE to drop the dependent objects too.
>> As a workaround, the sequence can be unlinked from the extension just before
>> dropping the table.
> Don't see why that's a "workaround". You added the extension membership
> for the sequence explicitly, why wouldn't you expect to need to drop it
> explicitly? Seems to me the system is behaving properly.
Because depending on the way a table has been included in the extension
(either directly created inside the extension or first created outside
and then linked to the extension) the procedure to drop it some versions
later would be different.
Of course it would be normal to get this error if the sequence would
have been linked to another extension. But my feeling is that dropping a
table that generates a sequence drop, both belonging to the same
extension, shouldn't be refused.
If the current behaviour is considered as "normal", then the error
message looks strange anyway. In particular the DETAIL output doesn't
reflect the real issue as it doesn't mention the faulting sequence. And
the DROP TABLE ... CASCADE suggested in the HINT doesn't help too. And a
short sentence in the documentation (in the ALTER EXTENSION page for
instance) would also probably help.
> If you want to drop the table but keep the sequence, you need to unlink
> the sequence from the table, a la
>
> alter sequence old_tbl1_col1_seq owned by none;
>
> before dropping the table.
OK, but in this case, I didn't want to keep the sequence.
> regards, tom lane
>
Regards. Philippe Beaudoin
