Re: cannot delete some records [9.3]

On Thu, 5 Dec 2013, Andy Colson wrote:

> On 12/5/2013 4:05 PM, Frank Miles wrote:

[snip]
>> 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

I can't see how - there's nothing in the trigger like that, and I still
get the same message even when the trigger is dropped.