Re: cannot delete some records [9.3]

On 12/5/2013 4:05 PM, Frank Miles wrote:
> 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 SHARE OF 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?}
>
>
>

When you drop trig_credmisc_updt, you still get the error like:
> 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 SHARE OF x"

I assume that select statement is comming from function
trigonupdtcredmisc(), right?

-Andy