Re: cannot delete some records [9.3]

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?}

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

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

> The table schema is {\d
> credmisc}:
> And this is all owned by: {\dp credmisc}
>

You have a table credmisc, in schema credmisc, owned by credmisc?
It could be a path problem. Maybe trigger should be:

trig_credmisc_updt BEFORE UPDATE ON credmisc.credmisc FOR EACH ROW
EXECUTE PROCEDURE credmisc.trigonupdtcredmisc()

> 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()
>

>
> Access privileges
> Schema | Name | Type | Access privileges | Column access
> privileges
> --------+----------+-------+-------------------+--------------------------
> public | credmisc | table | fpm=ardxt/fpm +|
> | | | bioeng=r/fpm |
>

Could we see the permissions on the functions too?

-Andy

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.

On Thu, 5 Dec 2013, Andy Colson wrote:

> On 12/5/2013 4:05 PM, Frank Miles wrote:
>
>> The table schema is {\d
>> credmisc}:
>> And this is all owned by: {\dp credmisc}
> You have a table credmisc, in schema credmisc, owned by credmisc?
> It could be a path problem. Maybe trigger should be:

Sorry for the perhaps overly compact way that I was describing
how I recovered the schema (by executing \d credmisc) and ownership
(\dp credmisc). It's owned by 'fpm'.

> trig_credmisc_updt BEFORE UPDATE ON credmisc.credmisc FOR EACH ROW EXECUTE
> PROCEDURE credmisc.trigonupdtcredmisc()
>
>> 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()
>>
>
>>
>> Access privileges
>> Schema | Name | Type | Access privileges | Column access
>> privileges
>> --------+----------+-------+-------------------+--------------------------
>> public | credmisc | table | fpm=ardxt/fpm +|
>> | | | bioeng=r/fpm |
>>
>
>
> Could we see the permissions on the functions too?
>
> -Andy

As a trigger, can it be 'owned'? And since the problem occurs even
when the trigger is dropped, it seems ultimately not involved.

Thanks for trying, though!
-Frank