| From: | piotr(dot)findeisen(at)gmail(dot)com |
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org |
| Subject: | BUG #13269: "alter constraint child_parent deferrable initially deferred" sometimes does not make FK deferred |
| Date: | 2015-05-11 13:55:09 |
| Message-ID: | 20150511135509.1095.7355@wrigleys.postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 13269
Logged by: Piotr Findeisen
Email address: piotr(dot)findeisen(at)gmail(dot)com
PostgreSQL version: 9.4.1
Operating system: Ubuntu 14.04.1
Description:
I experienced the problem in different configurations, originally using
JDBC. I managed to repro using just psql, but it may be a bit different
problem that originally experienced in my Java program.
Below follows SQL that in my opinion should fail at 'commit', i.e. after 6 s
sleep. However, when run with psql, it fails immediately, as if FK was not
made deferred.
----------------------------
create table parent (parent_id bigint primary key);
create table child (
child_id bigint primary key,
parent_id bigint constraint child_parent references parent (parent_id)
--deferrable initially deferred
);
insert into parent (parent_id) values (1);
insert into child (child_id, parent_id) values (2, 1);
alter table child alter constraint child_parent deferrable initially
deferred;
-- commit; -- helps in pgadmin
begin transaction;
delete from parent;
select pg_sleep(6);
commit;
----------------------------
Additional note: when I run the script with pgadmin, if tails immediately
too.
However, if I ad 'commit' after 'alter table', it fails after 6 s (as
expected). Strange, as "there is no transaction in progress" warning is
issued suggesting that commit did not change anything.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2015-05-11 14:37:32 | Re: BUG #13267: Some timezones in pg_timezone_names are missing in pg_timezone_abbrevs |
| Previous Message | Thomas Munro | 2015-05-11 11:56:46 | Re: Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated) |