Обсуждение: Violation of non existing reference

Hi,

I encountered an odd behaviour when I tried to delete a record.
I have two tables "z_base" and "z_ul". z_base's primary key is "isin"
which is referenced by z_ul.

select count(*) from z_base where isin = 'DE000DB3BTR9';
 count
-------
     1

select count(*) from z_ul where isin = 'DE000DB3BTR9';
 count
-------
     0

So there is no record in z_ul that references z_base with isin
'DE000DB3BTR9', but when I do:

delete from z_base where isin = 'DE000DB3BTR9';
ERROR:  update or delete on table "z_base" violates foreign key
constraint "z_ul_isin_fkey" on table "z_ul"
DETAIL:  Key (isin)=(DE000DB3BTR9) is still referenced from table
"z_ul".

Has anyone an idea how this could happen?

Jan

js@deriva.de writes:
> So there is no record in z_ul that references z_base with isin
> 'DE000DB3BTR9', but when I do:

> delete from z_base where isin = 'DE000DB3BTR9';
> ERROR:  update or delete on table "z_base" violates foreign key
> constraint "z_ul_isin_fkey" on table "z_ul"
> DETAIL:  Key (isin)=(DE000DB3BTR9) is still referenced from table
> "z_ul".

> Has anyone an idea how this could happen?

What PG version is this?  Are the two columns of identical datatypes?
(I'm wondering about possible funny effects from blank-padding rules,
for example, if one is char and the other is text or varchar.)
You might also try REINDEXing both tables just in case the problem is a
corrupt index.

            regards, tom lane

It's Version 8.3.
Both columns are of a user defined datatype which is a varchar(12)
with a special check.
I already did a REINDEX but it didn't help.


On 6 Mrz., 18:27, t...@sss.pgh.pa.us (Tom Lane) wrote:
> What PG version is this?  Are the two columns of identical datatypes?
> (I'm wondering about possible funny effects from blank-padding rules,
> for example, if one is char and the other is text or varchar.)
> You might also try REINDEXing both tables just in case the problem is a
> corrupt index.

js@deriva.de writes:
> It's Version 8.3.
> Both columns are of a user defined datatype which is a varchar(12)
> with a special check.

You mean a DOMAIN, or you mean a special datatype with custom C code?
If the latter, I'd suspect the C code.  8.3 has more stringent coding
rules for variable-length datatypes than prior releases did.

            regards, tom lane

Sorry, I was wrong. It's a charachter(12) not a varchar and it's a
domain.
The isins in z_ul either start with 'DE000' or with 'CH003'. PG seems
to compare only the first few charachters because when I set the
reference to CASCADE all z_ul entries that start with 'DE000' are
deleted. I tested it in another 8.3 database with the same effect but
it works well with our old 8.2.6 db.


On 6 Mrz., 22:54, t...@sss.pgh.pa.us (Tom Lane) wrote:
> You mean a DOMAIN, or you mean a special datatype with custom C code?
> If the latter, I'd suspect the C code.  8.3 has more stringent coding
> rules for variable-length datatypes than prior releases did.

js@deriva.de writes:
> Sorry, I was wrong. It's a charachter(12) not a varchar and it's a
> domain.
> The isins in z_ul either start with 'DE000' or with 'CH003'. PG seems
> to compare only the first few charachters because when I set the
> reference to CASCADE all z_ul entries that start with 'DE000' are
> deleted. I tested it in another 8.3 database with the same effect but
> it works well with our old 8.2.6 db.

Can you put together a self-contained test case?

            regards, tom lane