Обсуждение: Really strange foreign key constraint problem blocking delete
Even if I try to delete the data entry in the table channel_mapping with idaut 1622, it is not possible to delete from the table tmp_autenticazionesocial with the error below. How is this even possible since there is no channel_mapping with idaut 1622 any more?? I tried this in Java under a transaction, but also in pgAdmin III. ERROR: update or delete on table "tmp_autenticazionesocial" violates foreign key constraint "channel_mapping_idaut_fkey" on table "channel_mapping" DETAIL: Key (idautenticazionesocial)=(1622) is still referenced from table "channel_mapping".
Using my magick powers of mind reading, I will guess you made circular dependencies. On Fri, Oct 3, 2014 at 12:17 PM, Tim Mickelson <tim_mickelson@bigfoot.com> wrote: > Even if I try to delete the data entry in the table channel_mapping with > idaut 1622, it is not possible to delete from the table > tmp_autenticazionesocial with the error below. How is this even possible > since there is no channel_mapping with idaut 1622 any more?? I tried > this in Java under a transaction, but also in pgAdmin III. > > > ERROR: update or delete on table "tmp_autenticazionesocial" violates > foreign key constraint "channel_mapping_idaut_fkey" on table > "channel_mapping" > DETAIL: Key (idautenticazionesocial)=(1622) is still referenced from > table "channel_mapping". > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
But I don't think so, but here are the table defenitions:
CREATE TABLE bulldog.channel_mapping
(
idchannel integer NOT NULL,
idaut integer NOT NULL,
CONSTRAINT channel_mapping_pk PRIMARY KEY (idchannel, idaut),
CONSTRAINT channel_mapping_idaut_fkey FOREIGN KEY (idaut)
REFERENCES cubesocialnetwork.tmp_autenticazionesocial
(idautenticazionesocial) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT channel_mapping_idchannel_fkey FOREIGN KEY (idchannel)
REFERENCES bulldog.social_channel (idchannel) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE bulldog.channel_mapping
OWNER TO postgres;
-- Table: cubesocialnetwork.tmp_autenticazionesocial
-- DROP TABLE cubesocialnetwork.tmp_autenticazionesocial;
CREATE TABLE cubesocialnetwork.tmp_autenticazionesocial
(
idautenticazionesocial serial NOT NULL,
contratto text NOT NULL,
idlocation numeric NOT NULL,
textuser text,
textpassword text,
datacrea timestamp without time zone NOT NULL DEFAULT now(),
idsocial numeric NOT NULL,
location text,
username text,
link_foto text,
valid text,
link_profilo text,
tweetmonitored boolean DEFAULT false,
idutente text,
tipologia text,
api_key text,
api_secret text,
CONSTRAINT tmp_autenticazionesocial_pkey PRIMARY KEY
(idautenticazionesocial),
CONSTRAINT tipo_social_fk FOREIGN KEY (idsocial)
REFERENCES cubesocialnetwork.tipo_social (cd_social) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE cubesocialnetwork.tmp_autenticazionesocial
OWNER TO postgres;
-- Index: cubesocialnetwork.indice_tmp_autenticazione
-- DROP INDEX cubesocialnetwork.indice_tmp_autenticazione;
CREATE INDEX indice_tmp_autenticazione
ON cubesocialnetwork.tmp_autenticazionesocial
USING btree
(textpassword COLLATE pg_catalog."default");
-- Index: cubesocialnetwork.indicetextuser
-- DROP INDEX cubesocialnetwork.indicetextuser;
CREATE INDEX indicetextuser
ON cubesocialnetwork.tmp_autenticazionesocial
USING btree
(textuser COLLATE pg_catalog."default");
-- Index: cubesocialnetwork.indicidentificativosocial
-- DROP INDEX cubesocialnetwork.indicidentificativosocial;
CREATE INDEX indicidentificativosocial
ON cubesocialnetwork.tmp_autenticazionesocial
USING btree
(idsocial);
-- Index: cubesocialnetwork.tmpautenticazione
-- DROP INDEX cubesocialnetwork.tmpautenticazione;
CREATE INDEX tmpautenticazione
ON cubesocialnetwork.tmp_autenticazionesocial
USING btree
(idautenticazionesocial);
-- Index: cubesocialnetwork.tmpautenticazionecontrattoidlocation
-- DROP INDEX cubesocialnetwork.tmpautenticazionecontrattoidlocation;
CREATE INDEX tmpautenticazionecontrattoidlocation
ON cubesocialnetwork.tmp_autenticazionesocial
USING btree
(contratto COLLATE pg_catalog."default", idlocation);
-- Index: cubesocialnetwork.tmpauteticazionesocial
-- DROP INDEX cubesocialnetwork.tmpauteticazionesocial;
CREATE INDEX tmpauteticazionesocial
ON cubesocialnetwork.tmp_autenticazionesocial
USING btree
(username COLLATE pg_catalog."default");
On 03/10/2014 20:38, Vick Khera wrote:
> Using my magick powers of mind reading, I will guess you made circular
> dependencies.
>
> On Fri, Oct 3, 2014 at 12:17 PM, Tim Mickelson
> <tim_mickelson@bigfoot.com> wrote:
>> Even if I try to delete the data entry in the table channel_mapping with
>> idaut 1622, it is not possible to delete from the table
>> tmp_autenticazionesocial with the error below. How is this even possible
>> since there is no channel_mapping with idaut 1622 any more?? I tried
>> this in Java under a transaction, but also in pgAdmin III.
>>
>>
>> ERROR: update or delete on table "tmp_autenticazionesocial" violates
>> foreign key constraint "channel_mapping_idaut_fkey" on table
>> "channel_mapping"
>> DETAIL: Key (idautenticazionesocial)=(1622) is still referenced from
>> table "channel_mapping".
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
On 10/03/2014 10:45 PM, Tim Mickelson wrote: > But I don't think so, but here are the table defenitions: > > > CREATE TABLE bulldog.channel_mapping > ( > idchannel integer NOT NULL, > idaut integer NOT NULL, > CONSTRAINT channel_mapping_pk PRIMARY KEY (idchannel, idaut), > CONSTRAINT channel_mapping_idaut_fkey FOREIGN KEY (idaut) > REFERENCES cubesocialnetwork.tmp_autenticazionesocial > (idautenticazionesocial) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT channel_mapping_idchannel_fkey FOREIGN KEY (idchannel) > REFERENCES bulldog.social_channel (idchannel) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE bulldog.channel_mapping > OWNER TO postgres; > > > > > > -- Table: cubesocialnetwork.tmp_autenticazionesocial > > -- DROP TABLE cubesocialnetwork.tmp_autenticazionesocial; > > CREATE TABLE cubesocialnetwork.tmp_autenticazionesocial > ( > idautenticazionesocial serial NOT NULL, > contratto text NOT NULL, > idlocation numeric NOT NULL, > textuser text, > textpassword text, > datacrea timestamp without time zone NOT NULL DEFAULT now(), > idsocial numeric NOT NULL, > location text, > username text, > link_foto text, > valid text, > link_profilo text, > tweetmonitored boolean DEFAULT false, > idutente text, > tipologia text, > api_key text, > api_secret text, > CONSTRAINT tmp_autenticazionesocial_pkey PRIMARY KEY > (idautenticazionesocial), > CONSTRAINT tipo_social_fk FOREIGN KEY (idsocial) > REFERENCES cubesocialnetwork.tipo_social (cd_social) MATCH FULL > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH ( > OIDS=FALSE So channel_mapping references autenticazionesocial. The error message you gave previously indicates that there is more than one entry in channel_mapping referencing idautenticazionesocial=1622 in autenticazionesocial. So are you positive channel_mapping has no entries left that have idaut=1622? Also, to help with troubleshooting, what version of Postgres are you using? > > On 03/10/2014 20:38, Vick Khera wrote: >> Using my magick powers of mind reading, I will guess you made circular >> dependencies. >> >> On Fri, Oct 3, 2014 at 12:17 PM, Tim Mickelson >> <tim_mickelson@bigfoot.com> wrote: >>> Even if I try to delete the data entry in the table channel_mapping with >>> idaut 1622, it is not possible to delete from the table >>> tmp_autenticazionesocial with the error below. How is this even possible >>> since there is no channel_mapping with idaut 1622 any more?? I tried >>> this in Java under a transaction, but also in pgAdmin III. >>> >>> >>> ERROR: update or delete on table "tmp_autenticazionesocial" violates >>> foreign key constraint "channel_mapping_idaut_fkey" on table >>> "channel_mapping" >>> DETAIL: Key (idautenticazionesocial)=(1622) is still referenced from >>> table "channel_mapping". >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/04/2014 12:45 AM, Tim Mickelson wrote: > But I don't think so, but here are the table defenitions: > > > CREATE TABLE bulldog.channel_mapping > ( > idchannel integer NOT NULL, > idaut integer NOT NULL, > CONSTRAINT channel_mapping_pk PRIMARY KEY (idchannel, idaut), > CONSTRAINT channel_mapping_idaut_fkey FOREIGN KEY (idaut) > REFERENCES cubesocialnetwork.tmp_autenticazionesocial > (idautenticazionesocial) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT channel_mapping_idchannel_fkey FOREIGN KEY (idchannel) > REFERENCES bulldog.social_channel (idchannel) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE bulldog.channel_mapping > OWNER TO postgres; > > > > > > -- Table: cubesocialnetwork.tmp_autenticazionesocial > > -- DROP TABLE cubesocialnetwork.tmp_autenticazionesocial; > > CREATE TABLE cubesocialnetwork.tmp_autenticazionesocial > ( > idautenticazionesocial serial NOT NULL, > contratto text NOT NULL, > idlocation numeric NOT NULL, > textuser text, > textpassword text, > datacrea timestamp without time zone NOT NULL DEFAULT now(), > idsocial numeric NOT NULL, > location text, > username text, > link_foto text, > valid text, > link_profilo text, > tweetmonitored boolean DEFAULT false, > idutente text, > tipologia text, > api_key text, > api_secret text, > CONSTRAINT tmp_autenticazionesocial_pkey PRIMARY KEY > (idautenticazionesocial), > CONSTRAINT tipo_social_fk FOREIGN KEY (idsocial) > REFERENCES cubesocialnetwork.tipo_social (cd_social) MATCH FULL > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE cubesocialnetwork.tmp_autenticazionesocial > OWNER TO postgres; > > -- Index: cubesocialnetwork.indice_tmp_autenticazione > > -- DROP INDEX cubesocialnetwork.indice_tmp_autenticazione; > > CREATE INDEX indice_tmp_autenticazione > ON cubesocialnetwork.tmp_autenticazionesocial > USING btree > (textpassword COLLATE pg_catalog."default"); > > -- Index: cubesocialnetwork.indicetextuser > > -- DROP INDEX cubesocialnetwork.indicetextuser; > > CREATE INDEX indicetextuser > ON cubesocialnetwork.tmp_autenticazionesocial > USING btree > (textuser COLLATE pg_catalog."default"); > > -- Index: cubesocialnetwork.indicidentificativosocial > > -- DROP INDEX cubesocialnetwork.indicidentificativosocial; > > CREATE INDEX indicidentificativosocial > ON cubesocialnetwork.tmp_autenticazionesocial > USING btree > (idsocial); > > -- Index: cubesocialnetwork.tmpautenticazione > > -- DROP INDEX cubesocialnetwork.tmpautenticazione; > > CREATE INDEX tmpautenticazione > ON cubesocialnetwork.tmp_autenticazionesocial > USING btree > (idautenticazionesocial); > > -- Index: cubesocialnetwork.tmpautenticazionecontrattoidlocation > > -- DROP INDEX cubesocialnetwork.tmpautenticazionecontrattoidlocation; > > CREATE INDEX tmpautenticazionecontrattoidlocation > ON cubesocialnetwork.tmp_autenticazionesocial > USING btree > (contratto COLLATE pg_catalog."default", idlocation); > > -- Index: cubesocialnetwork.tmpauteticazionesocial > > -- DROP INDEX cubesocialnetwork.tmpauteticazionesocial; > > CREATE INDEX tmpauteticazionesocial > ON cubesocialnetwork.tmp_autenticazionesocial > USING btree > (username COLLATE pg_catalog."default"); > > > On 03/10/2014 20:38, Vick Khera wrote: >> Using my magick powers of mind reading, I will guess you made circular >> dependencies. >> >> On Fri, Oct 3, 2014 at 12:17 PM, Tim Mickelson >> <tim_mickelson@bigfoot.com> wrote: >>> Even if I try to delete the data entry in the table channel_mapping with >>> idaut 1622, it is not possible to delete from the table >>> tmp_autenticazionesocial with the error below. How is this even possible >>> since there is no channel_mapping with idaut 1622 any more?? I tried >>> this in Java under a transaction, but also in pgAdmin III. >>> >>> >>> ERROR: update or delete on table "tmp_autenticazionesocial" violates >>> foreign key constraint "channel_mapping_idaut_fkey" on table >>> "channel_mapping" >>> DETAIL: Key (idautenticazionesocial)=(1622) is still referenced from >>> table "channel_mapping". >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> > > > What version of PG are you on? I kinda recall one of the updates really wanting you to rebuild indexes or something likethat. -Andy
Postgresql 9.1 On 04/10/2014 16:06, Andy Colson wrote: > On 10/04/2014 12:45 AM, Tim Mickelson wrote: >> But I don't think so, but here are the table defenitions: >> >> >> CREATE TABLE bulldog.channel_mapping >> ( >> idchannel integer NOT NULL, >> idaut integer NOT NULL, >> CONSTRAINT channel_mapping_pk PRIMARY KEY (idchannel, idaut), >> CONSTRAINT channel_mapping_idaut_fkey FOREIGN KEY (idaut) >> REFERENCES cubesocialnetwork.tmp_autenticazionesocial >> (idautenticazionesocial) MATCH SIMPLE >> ON UPDATE NO ACTION ON DELETE NO ACTION, >> CONSTRAINT channel_mapping_idchannel_fkey FOREIGN KEY (idchannel) >> REFERENCES bulldog.social_channel (idchannel) MATCH SIMPLE >> ON UPDATE NO ACTION ON DELETE NO ACTION >> ) >> WITH ( >> OIDS=FALSE >> ); >> ALTER TABLE bulldog.channel_mapping >> OWNER TO postgres; >> >> >> >> >> >> -- Table: cubesocialnetwork.tmp_autenticazionesocial >> >> -- DROP TABLE cubesocialnetwork.tmp_autenticazionesocial; >> >> CREATE TABLE cubesocialnetwork.tmp_autenticazionesocial >> ( >> idautenticazionesocial serial NOT NULL, >> contratto text NOT NULL, >> idlocation numeric NOT NULL, >> textuser text, >> textpassword text, >> datacrea timestamp without time zone NOT NULL DEFAULT now(), >> idsocial numeric NOT NULL, >> location text, >> username text, >> link_foto text, >> valid text, >> link_profilo text, >> tweetmonitored boolean DEFAULT false, >> idutente text, >> tipologia text, >> api_key text, >> api_secret text, >> CONSTRAINT tmp_autenticazionesocial_pkey PRIMARY KEY >> (idautenticazionesocial), >> CONSTRAINT tipo_social_fk FOREIGN KEY (idsocial) >> REFERENCES cubesocialnetwork.tipo_social (cd_social) MATCH FULL >> ON UPDATE NO ACTION ON DELETE NO ACTION >> ) >> WITH ( >> OIDS=FALSE >> ); >> ALTER TABLE cubesocialnetwork.tmp_autenticazionesocial >> OWNER TO postgres; >> >> -- Index: cubesocialnetwork.indice_tmp_autenticazione >> >> -- DROP INDEX cubesocialnetwork.indice_tmp_autenticazione; >> >> CREATE INDEX indice_tmp_autenticazione >> ON cubesocialnetwork.tmp_autenticazionesocial >> USING btree >> (textpassword COLLATE pg_catalog."default"); >> >> -- Index: cubesocialnetwork.indicetextuser >> >> -- DROP INDEX cubesocialnetwork.indicetextuser; >> >> CREATE INDEX indicetextuser >> ON cubesocialnetwork.tmp_autenticazionesocial >> USING btree >> (textuser COLLATE pg_catalog."default"); >> >> -- Index: cubesocialnetwork.indicidentificativosocial >> >> -- DROP INDEX cubesocialnetwork.indicidentificativosocial; >> >> CREATE INDEX indicidentificativosocial >> ON cubesocialnetwork.tmp_autenticazionesocial >> USING btree >> (idsocial); >> >> -- Index: cubesocialnetwork.tmpautenticazione >> >> -- DROP INDEX cubesocialnetwork.tmpautenticazione; >> >> CREATE INDEX tmpautenticazione >> ON cubesocialnetwork.tmp_autenticazionesocial >> USING btree >> (idautenticazionesocial); >> >> -- Index: cubesocialnetwork.tmpautenticazionecontrattoidlocation >> >> -- DROP INDEX cubesocialnetwork.tmpautenticazionecontrattoidlocation; >> >> CREATE INDEX tmpautenticazionecontrattoidlocation >> ON cubesocialnetwork.tmp_autenticazionesocial >> USING btree >> (contratto COLLATE pg_catalog."default", idlocation); >> >> -- Index: cubesocialnetwork.tmpauteticazionesocial >> >> -- DROP INDEX cubesocialnetwork.tmpauteticazionesocial; >> >> CREATE INDEX tmpauteticazionesocial >> ON cubesocialnetwork.tmp_autenticazionesocial >> USING btree >> (username COLLATE pg_catalog."default"); >> >> >> On 03/10/2014 20:38, Vick Khera wrote: >>> Using my magick powers of mind reading, I will guess you made circular >>> dependencies. >>> >>> On Fri, Oct 3, 2014 at 12:17 PM, Tim Mickelson >>> <tim_mickelson@bigfoot.com> wrote: >>>> Even if I try to delete the data entry in the table channel_mapping >>>> with >>>> idaut 1622, it is not possible to delete from the table >>>> tmp_autenticazionesocial with the error below. How is this even >>>> possible >>>> since there is no channel_mapping with idaut 1622 any more?? I tried >>>> this in Java under a transaction, but also in pgAdmin III. >>>> >>>> >>>> ERROR: update or delete on table "tmp_autenticazionesocial" violates >>>> foreign key constraint "channel_mapping_idaut_fkey" on table >>>> "channel_mapping" >>>> DETAIL: Key (idautenticazionesocial)=(1622) is still referenced from >>>> table "channel_mapping". >>>> >>>> >>>> >>>> -- >>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> >> > > What version of PG are you on? I kinda recall one of the updates > really wanting you to rebuild indexes or something like that. > > -Andy >
I can guarantee that there is no entry in channel_mapping with idaut = 1622, it is first deleted. I've double checked, also because the error is indicating precisely this. The Postgresql Version is 9.1 On 04/10/2014 16:00, Adrian Klaver wrote: > On 10/03/2014 10:45 PM, Tim Mickelson wrote: >> But I don't think so, but here are the table defenitions: >> >> >> CREATE TABLE bulldog.channel_mapping >> ( >> idchannel integer NOT NULL, >> idaut integer NOT NULL, >> CONSTRAINT channel_mapping_pk PRIMARY KEY (idchannel, idaut), >> CONSTRAINT channel_mapping_idaut_fkey FOREIGN KEY (idaut) >> REFERENCES cubesocialnetwork.tmp_autenticazionesocial >> (idautenticazionesocial) MATCH SIMPLE >> ON UPDATE NO ACTION ON DELETE NO ACTION, >> CONSTRAINT channel_mapping_idchannel_fkey FOREIGN KEY (idchannel) >> REFERENCES bulldog.social_channel (idchannel) MATCH SIMPLE >> ON UPDATE NO ACTION ON DELETE NO ACTION >> ) >> WITH ( >> OIDS=FALSE >> ); >> ALTER TABLE bulldog.channel_mapping >> OWNER TO postgres; >> >> >> >> >> >> -- Table: cubesocialnetwork.tmp_autenticazionesocial >> >> -- DROP TABLE cubesocialnetwork.tmp_autenticazionesocial; >> >> CREATE TABLE cubesocialnetwork.tmp_autenticazionesocial >> ( >> idautenticazionesocial serial NOT NULL, >> contratto text NOT NULL, >> idlocation numeric NOT NULL, >> textuser text, >> textpassword text, >> datacrea timestamp without time zone NOT NULL DEFAULT now(), >> idsocial numeric NOT NULL, >> location text, >> username text, >> link_foto text, >> valid text, >> link_profilo text, >> tweetmonitored boolean DEFAULT false, >> idutente text, >> tipologia text, >> api_key text, >> api_secret text, >> CONSTRAINT tmp_autenticazionesocial_pkey PRIMARY KEY >> (idautenticazionesocial), >> CONSTRAINT tipo_social_fk FOREIGN KEY (idsocial) >> REFERENCES cubesocialnetwork.tipo_social (cd_social) MATCH FULL >> ON UPDATE NO ACTION ON DELETE NO ACTION >> ) >> WITH ( >> OIDS=FALSE > > So channel_mapping references autenticazionesocial. The error message > you gave previously indicates that there is more than one entry in > channel_mapping referencing idautenticazionesocial=1622 in > autenticazionesocial. > > So are you positive channel_mapping has no entries left that have > idaut=1622? > > Also, to help with troubleshooting, what version of Postgres are you > using? > >> >> On 03/10/2014 20:38, Vick Khera wrote: >>> Using my magick powers of mind reading, I will guess you made circular >>> dependencies. >>> >>> On Fri, Oct 3, 2014 at 12:17 PM, Tim Mickelson >>> <tim_mickelson@bigfoot.com> wrote: >>>> Even if I try to delete the data entry in the table channel_mapping >>>> with >>>> idaut 1622, it is not possible to delete from the table >>>> tmp_autenticazionesocial with the error below. How is this even >>>> possible >>>> since there is no channel_mapping with idaut 1622 any more?? I tried >>>> this in Java under a transaction, but also in pgAdmin III. >>>> >>>> >>>> ERROR: update or delete on table "tmp_autenticazionesocial" violates >>>> foreign key constraint "channel_mapping_idaut_fkey" on table >>>> "channel_mapping" >>>> DETAIL: Key (idautenticazionesocial)=(1622) is still referenced from >>>> table "channel_mapping". >>>> >>>> >>>> >>>> -- >>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> >> > >
On 10/05/2014 04:03 AM, Tim Mickelson wrote: > I can guarantee that there is no entry in channel_mapping with idaut = > 1622, it is first deleted. I've double checked, also because the error > is indicating precisely this. The Postgresql Version is 9.1 FYI, when supplying the version number please include the third number as it represents the bug fix release. This would help narrow the search for possible causes of your issue. Also please do not top post it makes it difficult to follow the thread. > > -- Adrian Klaver adrian.klaver@aklaver.com
Sorry about that, the precise version is: "PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit" On 05/10/2014 15:54, Adrian Klaver wrote: > On 10/05/2014 04:03 AM, Tim Mickelson wrote: >> I can guarantee that there is no entry in channel_mapping with idaut = >> 1622, it is first deleted. I've double checked, also because the error >> is indicating precisely this. The Postgresql Version is 9.1 > > FYI, when supplying the version number please include the third number > as it represents the bug fix release. This would help narrow the > search for possible causes of your issue. Also please do not top post > it makes it difficult to follow the thread. > >> >> > >
On 10/05/2014 09:37 AM, Tim Mickelson wrote: > Sorry about that, the precise version is: "PostgreSQL 9.1.9 on > x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat > 4.4.7-3), 64-bit" > > > On 05/10/2014 15:54, Adrian Klaver wrote: >> On 10/05/2014 04:03 AM, Tim Mickelson wrote: >>> I can guarantee that there is no entry in channel_mapping with idaut = >>> 1622, it is first deleted. I've double checked, also because the error >>> is indicating precisely this. The Postgresql Version is 9.1 >> >> FYI, when supplying the version number please include the third number >> as it represents the bug fix release. This would help narrow the >> search for possible causes of your issue. Also please do not top post >> it makes it difficult to follow the thread. >> >>> >>> >> >> > > > You might wanna review the patches from 9.1.9 to 9.1.14 to see if any sound important: http://www.postgresql.org/docs/9.1/static/release.html Also, please don't post at the top. -Andy
On 10/05/2014 07:37 AM, Tim Mickelson wrote: > Sorry about that, the precise version is: "PostgreSQL 9.1.9 on > x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat > 4.4.7-3), 64-bit" Well 9.1 is at .14 now, so on general principles it would be a good idea to upgrade. That being said I do not see anything in the release notes from .10 to .14 that applies. Though to be truthful I did not read every line. Before upgrading you could try what Andy suggested which is to REINDEX(tmpautenticazione). See here for the REINDEX caveats, and a way to INDEX CONCURRENTLY: http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html > > > On 05/10/2014 15:54, Adrian Klaver wrote: >> On 10/05/2014 04:03 AM, Tim Mickelson wrote: >>> I can guarantee that there is no entry in channel_mapping with idaut = >>> 1622, it is first deleted. I've double checked, also because the error >>> is indicating precisely this. The Postgresql Version is 9.1 >> >> FYI, when supplying the version number please include the third number >> as it represents the bug fix release. This would help narrow the >> search for possible causes of your issue. Also please do not top post >> it makes it difficult to follow the thread. >> >>> >>> >> >> > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/05/2014 10:00 AM, Adrian Klaver wrote: > On 10/05/2014 07:37 AM, Tim Mickelson wrote: >> Sorry about that, the precise version is: "PostgreSQL 9.1.9 on >> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat >> 4.4.7-3), 64-bit" > > Well 9.1 is at .14 now, so on general principles it would be a good idea to upgrade. That being said I do not see anythingin the release notes from .10 to .14 that applies. Though to be truthful I did not read every line. Before upgradingyou could try what Andy suggested which is to REINDEX(tmpautenticazione). See here for the REINDEX caveats, anda way to INDEX CONCURRENTLY: > > http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html > >> >> I thought .11 sounded like a good candidate. Especially the part: allowing tuples to escape freezing, causing those rows to become invisible once 2^31 transactions have elapsed -Andy
The administors (that are not from my company) are strongly against changing the Postgresql version :( so if this is a bug from Postgresql they want me to show a documentation that guarantees them that it will be fixed on an upgrade. On 05/10/2014 17:06, Andy Colson wrote: > On 10/05/2014 10:00 AM, Adrian Klaver wrote: >> On 10/05/2014 07:37 AM, Tim Mickelson wrote: >>> Sorry about that, the precise version is: "PostgreSQL 9.1.9 on >>> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat >>> 4.4.7-3), 64-bit" >> >> Well 9.1 is at .14 now, so on general principles it would be a good >> idea to upgrade. That being said I do not see anything in the release >> notes from .10 to .14 that applies. Though to be truthful I did not >> read every line. Before upgrading you could try what Andy suggested >> which is to REINDEX(tmpautenticazione). See here for the REINDEX >> caveats, and a way to INDEX CONCURRENTLY: >> >> http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html >> >>> >>> > > I thought .11 sounded like a good candidate. Especially the part: > > allowing tuples to escape freezing, causing those rows to become > invisible once 2^31 transactions have elapsed > > -Andy
On 10/5/14, 10:06 AM, Andy Colson wrote: > On 10/05/2014 10:00 AM, Adrian Klaver wrote: >> On 10/05/2014 07:37 AM, Tim Mickelson wrote: >>> Sorry about that, the precise version is: "PostgreSQL 9.1.9 on >>> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat >>> 4.4.7-3), 64-bit" >> >> Well 9.1 is at .14 now, so on general principles it would be a good idea to upgrade. That being said I do not see anythingin the release notes from .10 to .14 that applies. Though to be truthful I did not read every line. Before upgradingyou could try what Andy suggested which is to REINDEX(tmpautenticazione). See here for the REINDEX caveats, anda way to INDEX CONCURRENTLY: >> >> http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html >> >>> >>> > > I thought .11 sounded like a good candidate. Especially the part: > > allowing tuples to escape freezing, causing those rows to become invisible once 2^31 transactions have elapsed Those rows should then be invisible to the FK checks as well, so I don't think that's it. My guess is also on a corrupted index. A quick test would be to disable index scans and try the delete again (use EXPLAINANALYZE to make sure the delete is using a sequential scan). If that doesn't work, post EXPLAIN ANALYZE output forthe commands you're running that generate this error. -- Jim Nasby, Data Architect, Blue Treble Data in Trouble? Get it in Treble! http://BlueTreble.com
What should I disable? Corrupt index sounds like a possible case, but how do I fix this? EXPLAIN ANALYZE select * from cubesocialnetwork.tmp_autenticazionesocial where idautenticazionesocial = 1622 "Index Scan using tmpautenticazione on tmp_autenticazionesocial (cost=0.00..8.27 rows=1 width=530) (actual time=0.078..0.081 rows=1 loops=1)" " Index Cond: (idautenticazionesocial = 1622)" "Total runtime: 0.128 ms" On 07/10/2014 00:15, Jim Nasby wrote: > On 10/5/14, 10:06 AM, Andy Colson wrote: >> On 10/05/2014 10:00 AM, Adrian Klaver wrote: >>> On 10/05/2014 07:37 AM, Tim Mickelson wrote: >>>> Sorry about that, the precise version is: "PostgreSQL 9.1.9 on >>>> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red >>>> Hat >>>> 4.4.7-3), 64-bit" >>> >>> Well 9.1 is at .14 now, so on general principles it would be a good >>> idea to upgrade. That being said I do not see anything in the >>> release notes from .10 to .14 that applies. Though to be truthful I >>> did not read every line. Before upgrading you could try what Andy >>> suggested which is to REINDEX(tmpautenticazione). See here for the >>> REINDEX caveats, and a way to INDEX CONCURRENTLY: >>> >>> http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html >>> >>>> >>>> >> >> I thought .11 sounded like a good candidate. Especially the part: >> >> allowing tuples to escape freezing, causing those rows to become >> invisible once 2^31 transactions have elapsed > Those rows should then be invisible to the FK checks as well, so I > don't think that's it. > > My guess is also on a corrupted index. A quick test would be to > disable index scans and try the delete again (use EXPLAIN ANALYZE to > make sure the delete is using a sequential scan). If that doesn't > work, post EXPLAIN ANALYZE output for the commands you're running that > generate this error. >
On 10/06/2014 08:25 AM, Tim Mickelson wrote: > The administors (that are not from my company) are strongly against > changing the Postgresql version :( so if this is a bug from Postgresql > they want me to show a documentation that guarantees them that it will > be fixed on an upgrade. You might want to point them at the release notes that show quite a few bugs are fixed between 9.1.9 and 9.1.14, not limited to this: http://www.postgresql.org/docs/9.1/interactive/release-9-1-11.html However, this release corrects a number of potential data corruption issues. See the first two changelog entries below to find out whether your installation has been affected and what steps you can take if so. > > > On 05/10/2014 17:06, Andy Colson wrote: >> On 10/05/2014 10:00 AM, Adrian Klaver wrote: >>> On 10/05/2014 07:37 AM, Tim Mickelson wrote: >>>> Sorry about that, the precise version is: "PostgreSQL 9.1.9 on >>>> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat >>>> 4.4.7-3), 64-bit" >>> >>> Well 9.1 is at .14 now, so on general principles it would be a good >>> idea to upgrade. That being said I do not see anything in the release >>> notes from .10 to .14 that applies. Though to be truthful I did not >>> read every line. Before upgrading you could try what Andy suggested >>> which is to REINDEX(tmpautenticazione). See here for the REINDEX >>> caveats, and a way to INDEX CONCURRENTLY: >>> >>> http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html >>> >>>> >>>> >> >> I thought .11 sounded like a good candidate. Especially the part: >> >> allowing tuples to escape freezing, causing those rows to become >> invisible once 2^31 transactions have elapsed >> >> -Andy > > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/06/2014 11:29 PM, Tim Mickelson wrote: > What should I disable? Corrupt index sounds like a possible case, but > how do I fix this? > http://www.postgresql.org/docs/9.1/static/runtime-config-query.html enable_indexscan (boolean) Enables or disables the query planner's use of index-scan plan types. The default is on. SET enable_indexscan=off; EXPLAIN ANALYZE DELETE query SET enable_indexscan=on; > > EXPLAIN ANALYZE > select * from cubesocialnetwork.tmp_autenticazionesocial where > idautenticazionesocial = 1622 > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> SET enable_indexscan=off;
> EXPLAIN ANALYZE DELETE query
> SET enable_indexscan=on;
Note that you'd probably best do this in a fresh session, since the
supposed problem is being tickled by a foreign-key check. I think
the plans for those get cached, so if you'd already tried the same
case earlier in the session, changing enable_indexscan wouldn't
change the plan used.
regards, tom lane