Scenario

I have a two tables, foo and bar. bar references a field in foo (a foreign key relationship).

Quick preview to "meeting me where I am": I'm finally starting to realize that "referencing" actually might mean "a pointer to". In other words, it's more than just "I promise to have a matching value", but rather, bar hosts a pointer to a record in foo.

Inserting records into foo and bar "simultaneously"

The inserts will be "simultaneous" to the user by way of a single function call to make all of this happen. In the body of the function, there is a strict sequence that must be followed. The sequence of inserts is set by the fact that "bar references foo"; so foo needs to be "in place" first.

However, inserting a matching value in bar e.g., insert into bar (column_1) values (same_value_used_for_foo) does not work despite my using the same value used to create the foo record (literally the same parameter value in the function I'm using to complete this combination of inserts). This is likely obvious to most as to why this was sure to fail given bar references foo.

Notwithstanding, the nice thing about this approach is that I was thinking I could use matching values to avoid worrying about the minutiae of when the foo record is ready to be "referenced". This is where it started to sink in: references actually means... well "references" more in the spirit of "points to" (if not precisely "pointer to") rather than "matches" (where a copy of the value may well have worked).

Next naive iteration, would be to select the newly inserted value from foo into bar:

insert into bar (x) values( (select foo.x from foo where ...whatever it takes) )

This will work because postgres will create a reference to, not a copy of the value returned in the select statement.

The most "idiomatic", robust and expeditious is likely going to be in my referencing what postgres returns following the insert into foo.

The question

This is where I started to ask the question,

... at what point can I reference the foo record in order to insert a record into bar without "betraying" the foreign key constraint?

More concretely, ** does the insert statement into foo need to be "commit;" prior to my referencing it? **

I could have iterated on the code to find a solution, but in the spirit of "once and for all" my better understanding what it really means to reference a column value in another table, I suspect an answer to this question will help.

Finally, to understand in context of postgres security and permission, given the distinct permissions for references x separate from select (x), is it right to model this as postgres preventing a "dereferencing x" when a person only has a references x permission?

Addendum - iteration on the question

With the input received from both Adrian Klaver and Daniel Verite, I suspect the answer is going to lie in either my table view/insert/update/delete granted permissions or my row-level policies.

I am getting a "foreign key violation error" that relies on the presence of a record in bazzz; a third table.

The violation error

ERROR:  insert or update on table "bar" violates foreign key constraint "fk_bar_link_bazzz"
DETAIL:  Key is not present in table "bazzz".
CONTEXT:  SQL statement "insert into core.bar(
            owner_id,
            provider,
            project_id
            ) values (
            _current_user,
            input.provider,
            input.project_id
        ) on conflict do nothing"

The constraint being violated:

constraint fk_bar_link_bazzz
    foreign key (owner_id, project_id) references bazzz(user_id, project_id)

-- where as an aside, 
-- the bazz.user_id and bazz.project_id reference the users and
-- projects table id fields respectively

However, prior to drafting this post, I can confirm that in fact, the bazzz record does exist by "putting eyes" on the project_id value that is understood by postgres to be "missing" (confirmed by way of a view):

-- pgTap test that passes
-- role webuser

    return next is(
        (permission::text, project_id),
        ('owner'::text, test_project_id),
        '✅ with the expected project_id and owner permission')
        from api.bazzz
        where bazzz.project_id = test_project_id;

-- role api, 
-- I can confirm the presence of the expected owner_id and project_id
-- values.

I also made sure to run the function using the security definer and furthermore, assert current_user = api.

   ASSERT current_user::text = 'api',
          'Unexpected current_user: %', current_user;

Without much of a reason, my next move is to review the row-level policies; what might otherwise prevent this transaction from moving forward?

CodePudding user response：

... at what point can I reference the foo record in order to insert a record into bar without "betraying" the foreign key constraint?

More concretely, ** does the insert statement into foo need to be "commit;" prior to my referencing it?

From the same session (that is, through the same connection to the database), it can be referenced immediately.

From another session, it cannot be seen until the inserting session has committed.

However, inserting a matching value in bar e.g., insert into bar (column_1) values (same_value_used_for_foo) does not work

It does work.

As an example assuming you have these tables

create table foo (id serial primary key, something text);

create table bar(some_text text, foo_id int references foo(id));

a typical two-table insert sequence looks like the following.

begin;

insert into foo(something) values('abcd') returning id;
 id 
----
  1

insert into bar values('ghij', 1);

commit;

And if you tried to insert a non-existing value on the referencing side, it would have failed that way:

insert into bar values('ghij', 2);
ERROR:  insert or update on table "bar" violates foreign key constraint "bar_foo_id_fkey"
DETAIL:  Key (foo_id)=(2) is not present in table "foo".

CodePudding user response：

An example in a single transaction:

create table parent (id integer, fld_1 varchar unique);
create table child (id integer primary key, parent_fk varchar references parent(fld_1), child_fld varchar);
BEGIN;
insert into parent values (1, 'dog');
insert into child values(1, 'dog', 'ranger');
COMMIT;
select * from parent;
 id | fld_1 
---- -------
  1 | dog
select * from child;
 id | parent_fk | child_fld 
---- ----------- -----------
  1 | dog       | ranger

Different concurrent sessions(transactions):

--Session one
BEGIN;
insert into parent values (2, 'cat');
INSERT 0 1

--Session two
BEGIN;
insert into child values (2, 'cat', 'mayhem');
ERROR:  insert or update on table "child" violates foreign key constraint "child_parent_fk_fkey"
DETAIL:  Key (parent_fk)=(cat) is not present in table "parent".

--Session one
COMMIT;

--Session two
ROLLBACK;
BEGIN;
insert into child values (2, 'cat', 'mayhem');
INSERT 0 1
COMMIT;

So you can do multiple inserts/updates in a single transaction in a session. If you want the child table(s) in other session/transactions to see the referenced value in the parent table then the insert or update on the parent table needs to be committed.

Your conception of how the Foreign Key is a little off. FK's in Postgres are handled using a system constraint trigger on the FK that checks for matches. This can be seen in ALTER TABLE where you can disable the triggers if needed, though not advised. You can also change the behavior of the FK trigger by deferring it's application per DEFERRABLE here CREATE TABLE.