0

I want to drop a field from a partitioned table, POSTGRES claims there are dependencies and proposes using CASCADE. How can I check that nothing important will be deleted before risking DROP with CASCADE?

My table is defined as (edited for brevity) -

CREATE TABLE public.positionfix
(
    fixid3 integer,
    fixid bigint NOT NULL DEFAULT nextval('positionfix_fixid_seq'::regclass),
    messageid bigint NOT NULL,
    fixtime timestamp with out timezone NOT NULL
) PARTITION BY RANGE (fixtime) 
WITH (
    OIDS = FALSE
)
TABLESPACE fastspace;

I'm trying to remove the field 'fixId3' using the command -

ALTER TABLE positionfix  DROP COLUMN fixId3;

The error messages I get are -

ERROR:  cannot drop table positionfix column fixid3 because other objects depend on it
DETAIL:  default for table positionfix column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_201909 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190926 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190927 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190928 column fixid depends on sequence positionfix_fixid_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
SQL state: 2BP01

All the tables mentioned in the errors are partitions of the parent table. The issue is that the error message mentions other objects dependent on "fixId3", but the detail messages refer to a different field "fixId"

There should be nothing that's dependent on "fixId3".

My questions are

1) Based on the messages above, which additional objects will postgres delete if I attempt to drop "fixId3" with the cascade option? Field "fixId"? the sequence? both? Something else?

2) why is postgres listing a dependency between "fixId" and the sequence "positionfix_fixid_seq" when the field being deleted is "fixId3"?

3) how do I remove "fixId3" without risking accidentally removing "fixId" instead. (I am reluctant to try CASCADE before I understand the consequences.

1

Ok, got an answer from a related question I raised a year ago. Although the sequence is used by 'fixId' it is owned by 'fixId3'

So by running this command.....

ALTER SEQUENCE POSITIONFIX_FIXID_SEQ owned by positionFix.fixId;

I can then run the DROP command without any dependency issues.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.