Re: cannot delete some records [9.3] - Mailing list pgsql-general

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.