Thread: Unexpected behaviour of 'DEFERRABLE INITIALLY DEFERRED'

Unexpected behaviour of 'DEFERRABLE INITIALLY DEFERRED'

From
Jong-won Choi
Date:
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?

Thanks

- Jong-won


Re: Unexpected behaviour of 'DEFERRABLE INITIALLY DEFERRED'

From
Adrian Klaver
Date:
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