Really strange foreign key constraint problem blocking delete

[Tim Mickelson]

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".

[Vick Khera]

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

Post by Tim Mickelson
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".
--
http://www.postgresql.org/mailpref/pgsql-general

[Tim Mickelson]

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");

Post by Vick Khera
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

Post by Tim Mickelson
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".
--
http://www.postgresql.org/mailpref/pgsql-general

[Andy Colson]

Post by Tim Mickelson
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");

Post by Vick Khera
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

Post by Tim Mickelson
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".
--
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

[Tim Mickelson]

Postgresql 9.1

Post by Andy Colson

Post by Tim Mickelson
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");

Post by Vick Khera
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

Post by Tim Mickelson
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".
--
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

[Adrian Klaver]

Post by Tim Mickelson
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?

Post by Tim Mickelson

Post by Vick Khera
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

Post by Tim Mickelson
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".
--
http://www.postgresql.org/mailpref/pgsql-general

[Tim Mickelson]

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

Post by Adrian Klaver

Post by Tim Mickelson
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?

Post by Tim Mickelson

Post by Vick Khera
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

Post by Tim Mickelson
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".
--
http://www.postgresql.org/mailpref/pgsql-general

[Adrian Klaver]

Post by Tim Mickelson
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.

[Tim Mickelson]

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"

Post by Adrian Klaver

Post by Tim Mickelson
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.

[Andy Colson]

Post by Tim Mickelson
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"

Post by Adrian Klaver

Post by Tim Mickelson
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

[Adrian Klaver]

Post by Tim Mickelson
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

Post by Tim Mickelson

Post by Adrian Klaver

Post by Tim Mickelson
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.

[Andy Colson]

Post by Adrian Klaver

Post by Tim Mickelson
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"

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

[Tim Mickelson]

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.

Post by Andy Colson

Post by Adrian Klaver

Post by Tim Mickelson
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
http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html

allowing tuples to escape freezing, causing those rows to become
invisible once 2^31 transactions have elapsed
-Andy

[Adrian Klaver]

Post by Tim Mickelson
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.

Post by Tim Mickelson

Post by Andy Colson

Post by Adrian Klaver

Post by Tim Mickelson
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
http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html

allowing tuples to escape freezing, causing those rows to become
invisible once 2^31 transactions have elapsed
-Andy

[Jim Nasby]

Post by Andy Colson

Post by Adrian Klaver

Post by Tim Mickelson
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"

http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html

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.
--
Jim Nasby, Data Architect, Blue Treble
Data in Trouble? Get it in Treble! http://BlueTreble.com

[Tim Mickelson]

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"

Post by Jim Nasby

Post by Andy Colson

Post by Adrian Klaver

Post by Tim Mickelson
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
http://www.postgresql.org/docs/9.1/interactive/sql-reindex.html

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.

[Adrian Klaver]

Post by Tim Mickelson
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;

Post by Tim Mickelson
EXPLAIN ANALYZE
select * from cubesocialnetwork.tmp_autenticazionesocial where
idautenticazionesocial = 1622

[Tom Lane]

Post by Adrian Klaver
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