Re: Unexpected behaviour of 'DEFERRABLE INITIALLY DEFERRED'

On 11/25/2015 04:32 AM, Jong-won Choi wrote:
> Hi all,
>
> This is from my local PostgreSQL log:
>
> ====
> LOG:  statement: begin;
> LOG:  statement: INSERT INTO site_owner (email, name, pwhash, data)
> VALUES ('xyz', 'xyz', crypt('xyz', gen_salt('bf', 8)), '{}'::JSONB)
> RETURNING id;
> LOG:  statement: INSERT INTO site (owner, name, timezone, data) VALUES
> ('150c2fff-1029-47a5-aaa8-3e974e8442c6', 'sitex', 'Australia/NSW',
> '{}'::JSONB) RETURNING id;
> LOG:  statement: commit;
>
> ----
>
> LOG:  statement: BEGIN
> LOG:  execute <unnamed>: INSERT INTO site_owner (email, name, pwhash,
> data) VALUES ($1, $2, crypt($3, gen_salt('bf', 8)), $4::JSONB) RETURNING id
> DETAIL:  parameters: $1 = 'test@email6', $2 = 'Name', $3 = 'password',
> $4 = '{}'
> LOG:  execute <unnamed>: INSERT INTO site (owner, name, timezone, data)
> VALUES ($1, $2, $3, $4::JSONB) RETURNING id
> DETAIL:  parameters: $1 = '3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e', $2 =
> 'Test', $3 = 'Australia/NSW', $4 = '{}'
> ERROR:  insert or update on table "site" violates foreign key constraint
> "site_owner_fkey"
> DETAIL:  Key (owner)=(3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e) is not
> present in table "site_owner".
> LOG:  statement: ROLLBACK
> ====
>
>
> The first 'successful' part is a result of SQL statements from psql, and
> the second 'unsuccessful' part is a result of client library function
> calls from my application.
>
> It seems to me they both have the same structure, except the first part
> is consist of 'statements' and the second part is consist of
> 'execute'(prepare statements, I guess).
>
> Any hints which can make the second part successful?

First what has this to do with the subject, in particular "'DEFERRABLE
INITIALLY DEFERRED"?

The error seems fairly straight forward:

ERROR:  insert or update on table "site" violates foreign key constraint
"site_owner_fkey"
DETAIL:  Key (owner)=(3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e) is not
present in table "site_owner"

To understand more we will need to see the schema for:

site_owner
site

including the FK relationship between them.

Also what is creating the owner key in site_owner as I do not see that
field in the INSERT to site_owner.


>
> Thanks
>
> - Jong-won
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com