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.