Here's something I don't understand.... If I'm missing an obvious, please
feel free to kick me in the right direction.
We're given two tables, linked with a foreign key. When insert is run on a
master table, everything is OK, when trying to insert into a detail table,
a strange query appears in the log (schema and log snippet attached).
In fact, there should be no problem, but if a user has no permissions to
update the master table (eg, this is a log where he can only insert, but
neither delete, nor update), then inserting into detail table fails on
permission violation at the query:
SELECT oid FROM "alpha" WHERE "id" = $1 FOR UPDATE OF "alpha"
TIA
Ed
---
Well I tried to be meek
And I have tried to be mild
But I spat like a woman
And I sulked like a child
I have lived behind the walls
That have made me alone
Striven for peace
Which I never have known
Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
create sequence seq_alpha minvalue 1 increment 1 start 1 cycle;
create sequence seq_beta minvalue 1 increment 1 start 1 cycle;
create table alpha(
id int4
constraint alpha_id
not null
unique
check( id <> 0 )
default nextval( 'seq_alpha' ),
payload text
constraint alpha_payload
not null
unique
check( payload <> '' ),
constraint alpha_pk
primary key( id )
);
create table beta(
id int4
constraint beta_id
not null
unique
check( id <> 0 )
default nextval( 'seq_beta' ),
ref int4
constraint beta_ref
not null
check( ref <> 0 ),
info text
constraint beta_info
not null
unique
check( info <> '' ),
constraint beta_pk
primary key( id ),
constraint beta_fk
foreign key( ref )
references alpha( id )
match full
on delete restrict
on update restrict
not deferrable
initially immediate
);
insert into alpha( payload ) values( 'one' );
insert into alpha( payload ) values( 'two' );
insert into beta( ref, info ) values( 1, '1.1' );
LockReleaseAll: lockmethod=1, pid=1740
LockReleaseAll: reinitializing lockQueue
LockReleaseAll: done
query: insert into alpha( payload ) values( 'one' );
after rewriting:
{ QUERY :command 3 :utility <> :resultRelation 1 :into <> :isPortal false :isBinary
false :isTemp false :unionall false :distinctClause <> :sortClause <> :rtable ({ RTE
:relname alpha :ref { ATTR :relname alpha :attrs <>} :relid 319938 :inh false
:inFromCl false :inJoinSet false :skipAcl false}) :targetlist ({ TARGETENTRY :resdom {
RESDOM :resno 2 :restype 25 :restypmod -1 :resname payload :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { CONST :consttype 25 :constlen -1
:constisnull false :constvalue 7 [ 7 0 0 0 111 110 101 ] :constbyval false }} {
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 23 :opType func
:oper { FUNC :funcid 1574 :functype 23 :funcisindex false :funcsize 0 :func_fcache @
0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname \<noname> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk fal!
se } :expr { VAR :varno -1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold -1 :varoattno 1}}) :func_planlist <>} :args ({ CONST :consttype 25 :constlen
-1 :constisnull false :constvalue 13 [ 13 0 0 0 115 101 113 95 97 108 112 104 97 ]
:constbyval false })}}) :qual <> :groupClause <> :havingQual <> :hasAggs false
:hasSubLinks false :unionClause <> :intersectClause <> :limitOffset <> :limitCount <>
:rowMark <>}
LockReleaseAll: lockmethod=1, pid=1740
LockReleaseAll: reinitializing lockQueue
LockReleaseAll: done
query: insert into alpha( payload ) values( 'two' );
after rewriting:
{ QUERY :command 3 :utility <> :resultRelation 1 :into <> :isPortal false :isBinary
false :isTemp false :unionall false :distinctClause <> :sortClause <> :rtable ({ RTE
:relname alpha :ref { ATTR :relname alpha :attrs <>} :relid 319938 :inh false
:inFromCl false :inJoinSet false :skipAcl false}) :targetlist ({ TARGETENTRY :resdom {
RESDOM :resno 2 :restype 25 :restypmod -1 :resname payload :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { CONST :consttype 25 :constlen -1
:constisnull false :constvalue 7 [ 7 0 0 0 116 119 111 ] :constbyval false }} {
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 23 :opType func
:oper { FUNC :funcid 1574 :functype 23 :funcisindex false :funcsize 0 :func_fcache @
0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname \<noname> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk fal!
se } :expr { VAR :varno -1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold -1 :varoattno 1}}) :func_planlist <>} :args ({ CONST :consttype 25 :constlen
-1 :constisnull false :constvalue 13 [ 13 0 0 0 115 101 113 95 97 108 112 104 97 ]
:constbyval false })}}) :qual <> :groupClause <> :havingQual <> :hasAggs false
:hasSubLinks false :unionClause <> :intersectClause <> :limitOffset <> :limitCount <>
:rowMark <>}
LockReleaseAll: lockmethod=1, pid=1740
LockReleaseAll: reinitializing lockQueue
LockReleaseAll: done
query: select * from alpha;
after rewriting:
{ QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary
false :isTemp false :unionall false :distinctClause <> :sortClause <> :rtable ({ RTE
:relname alpha :ref { ATTR :relname alpha :attrs <>} :relid 319938 :inh false
:inFromCl true :inJoinSet true :skipAcl false}) :targetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 25 :restypmod -1 :resname payload :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 25
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2}}) :qual <> :groupClause <>
:havingQual <> :hasAggs false :hasSubLinks false :unionClause <> :intersectClause <>
:limitOffset <> :limitCount <> :rowMark <>}
LockReleaseAll: lockmethod=1, pid=1740
LockReleaseAll: reinitializing lockQueue
LockReleaseAll: done
query: insert into beta( ref, info ) values( 1, '1.1' );
after rewriting:
{ QUERY :command 3 :utility <> :resultRelation 1 :into <> :isPortal false :isBinary
false :isTemp false :unionall false :distinctClause <> :sortClause <> :rtable ({ RTE
:relname beta :ref { ATTR :relname beta :attrs <>} :relid 319957 :inh false :inFromCl
false :inJoinSet false :skipAcl false}) :targetlist ({ TARGETENTRY :resdom { RESDOM
:resno 2 :restype 23 :restypmod -1 :resname ref :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { CONST :consttype 23 :constlen 4 :constisnull false
:constvalue 4 [ 1 0 0 0 ] :constbyval true }} { TARGETENTRY :resdom { RESDOM :resno
3 :restype 25 :restypmod -1 :resname info :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { CONST :consttype 25 :constlen -1 :constisnull false
:constvalue 7 [ 7 0 0 0 49 46 49 ] :constbyval false }} { TARGETENTRY :resdom {
RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 23 :opType func :oper!
{ FUNC :funcid 1574 :functype 23 :funcisindex false :funcsize 0 :func_fcache @ 0x0
:func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname \<noname> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :varno -1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold -1
:varoattno 1}}) :func_planlist <>} :args ({ CONST :consttype 25 :constlen -1
:constisnull false :constvalue 12 [ 12 0 0 0 115 101 113 95 98 101 116 97 ]
:constbyval false })}}) :qual <> :groupClause <> :havingQual <> :hasAggs false
:hasSubLinks false :unionClause <> :intersectClause <> :limitOffset <> :limitCount <>
:rowMark <>}
query: SELECT oid FROM "alpha" WHERE "id" = $1 FOR UPDATE OF "alpha"
after rewriting:
{ QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary
false :isTemp false :unionall false :distinctClause <> :sortClause <> :rtable ({ RTE
:relname alpha :ref { ATTR :relname alpha :attrs <>} :relid 319938 :inh false
:inFromCl true :inJoinSet true :skipAcl false}) :targetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 26 :restypmod -1 :resname oid :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno -2 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno -2}}) :qual { EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 0 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} {
PARAM :paramkind 12 :paramid 1 :paramname \<unnamed> :paramtype 23 :param_tlist <>})}
:groupClause <> :havingQual <> :hasAggs false :hasSubLinks false :unionClause <>
:intersectClause <> :limitOffset <> :limitCount <> :rowMark ({ ROWMARK :rti 1 :in!
fo 3})}
LockReleaseAll: lockmethod=1, pid=1740
LockReleaseAll: reinitializing lockQueue
LockReleaseAll: done
proc_exit(0)
shmem_exit(0)
LockReleaseAll: lockmethod=1, pid=1740
LockReleaseAll: lockmethod=2, pid=1740
exit(0)
