cannot delete some records [9.3] - Mailing list pgsql-general
From | Frank Miles |
---|---|
Subject | cannot delete some records [9.3] |
Date | |
Msg-id | alpine.LRH.2.01.1312051348590.18101@homer01.u.washington.edu Whole thread Raw |
Responses |
Re: cannot delete some records [9.3]
Re: cannot delete some records [9.3] |
List | pgsql-general |
I'm in the process of moving from a server running postgresql-8.4 (Debian-oldstable) to a newer machine running postgresql-9.3. The dumpall-restore process seemed to go perfectly. In running my self-test script, I discovered that one of the tables couldn't be cleared of some unit-test entries. The table schema is {\d credmisc}: Table "public.credmisc" Column | Type | Modifiers ----------+------------------+---------------------------------------------------------- cm_id | integer | not null default nextval('credmisc_cm_id_seq'::regclass) crtype | character(1) | not null ref_id | integer | not null raw_amt | double precision | not null resolved | boolean | not null default false dtwhen | date | not null default ('now'::text)::date explan | text | not null Indexes: "credmisc_pkey" PRIMARY KEY, btree (cm_id) Check constraints: "credmisc_crtype_check" CHECK (crtype = 'b'::bpchar OR crtype = 'p'::bpchar) "credmisc_raw_amt_check" CHECK (raw_amt >= 0.02::double precision) Referenced by: TABLE "credtrans" CONSTRAINT "credtrans_cm_id_fkey" FOREIGN KEY (cm_id) REFERENCES credmisc(cm_id) Triggers: trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE PROCEDURE trigoninscredmisc() trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE PROCEDURE trigonupdtcredmisc() And this is all owned by: {\dp credmisc} Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+----------+-------+-------------------+-------------------------- public | credmisc | table | fpm=ardxt/fpm +| | | | bioeng=r/fpm | Yet when I try to delete some records: delete from credmisc where cm_id < -100 and ref_id < 0; what I get back is: ERROR: permission denied for relation credmisc CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."credmisc" x WHERE "cm_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHAREOF x" Neither dropping the <trig_credmisc_updt> trigger nor performing the 'delete' operation as user 'postgres' changes anything. There's nothing different in the logs. It works perfectly fine in 8.4. And most of the other dozens of tables don't have this problem. Selecting the data looks fine. Anyone have a clue as to what I'm missing? TIA! -Frank {p.s. yes, cm_id won't normally be negative... some negative values were inserted as part of the unit testing, which avoids confusion with existing positive value. That shouldn't be a problem, right?}
pgsql-general by date: