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

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