Trouble with foreign key

At 11:12p -0400 on Tue, 08 Apr 2008), Otakarek wrote:

there is a problem with foreign key (PG 8.3RC2). Please, look at

^^^^^^

Before you go any further, I'd highly suggest updating. 8.3 was
released over 2 months ago, and has already progressed to 8.3.1.

Upgrade, check the release notes, then see what's up.

Kevin

<span style="font-family: Verdana"><pre><p>Hi,&nbsp;</p><p>what&#39;s the result of </p><p>&nbsp;SELECT * from tmp_stamp where _stamp = &#39;2f980de9f2297c7902f3415f6537c6be&#39;;</p><p>?</p><p>Perhaps different fieldtypes (f. e. VARCHAR in table www_es_orderhead_cs versus CHAR(n) in Table _stamp filled up with blanks)?</p><p>&nbsp;</p><p>Ludwig</p><p>&nbsp;</p><p>&nbsp;</p><p>Hi,<br /><br />there is a problem with foreign key (PG 8.3RC2). Please, look at below. I don&#39;t understand this behavior. Why this INSERT is ending with this error message. In my opinion, the key is obviously present. Thank you.<br />--<br />Otakarek<br /><br /># INSERT INTO www_es_orderhead_cs(_status,_tmp_ip,_tmp_stamp) VALUES(&#39;n&#39;,&#39;xx.121.111.31&#39;,&#39;2f980de9f2297c7902f3415f6537c6be&#39;);<br
/><br />ERROR: insert or update on table &quot;www_es_orderhead_cs&quot; violates foreign key constraint &quot;www_es_orderhead_cs__tmp_stamp_fkey&quot;<br />DETAIL: Key (_tmp_stamp)=(2f980de9f2297c7902f3415f6537c6be) is not present in table &quot;tmp_stamp&quot;.<br /><br /># SELECT * from tmp_stamp;<br /> _ip | _stamp | _expired | _var<br />------------------+----------------------------------+-------------------------------+------<br /> xx.121.111.31/32 | 2f980de9f2297c7902f3415f6537c6be | 2008-04-08 17:49:33.193914+02 |<br />(1 row)<br /><br />-- <br />Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br />To make changes to your subscription:<br /><a target="_blank"
href="/xml/deref?link=http%3A%2F%2Fwww.postgresql.org%2Fmailpref%2Fpgsql-general">http://www.postgresql.org/mailpref/pgsql-general&lt;/a&gt;&lt;/p&gt;&lt;/pre&gt;&lt;/span&gt;

How is your foreign key defined?

From a quick look it seems the column is named "_stamp" not
"_tmp_stamp" in the tmp_stamp table.

otakarek@post.cz wrote:

Hi,

there is a problem with foreign key (PG 8.3RC2). Please, look at below. I don't understand this behavior. Why this INSERT is ending with this error message. In my opinion, the key is obviously present. Thank you.
--
Otakarek

# INSERT INTO www_es_orderhead_cs(_status,_tmp_ip,_tmp_stamp) VALUES('n','xx.121.111.31','2f980de9f2297c7902f3415f6537c6be');

ERROR: insert or update on table "www_es_orderhead_cs" violates foreign key constraint "www_es_orderhead_cs__tmp_stamp_fkey"
DETAIL: Key (_tmp_stamp)=(2f980de9f2297c7902f3415f6537c6be) is not present in table "tmp_stamp".

# SELECT * from tmp_stamp;
_ip | _stamp | _expired | _var
------------------+----------------------------------+-------------------------------+------
xx.121.111.31/32 | 2f980de9f2297c7902f3415f6537c6be | 2008-04-08 17:49:33.193914+02 |
(1 row)

--

Kyle Wilcox
NOAA Chesapeake Bay Office
410 Severn Avenue
Suite 107A
Annapolis, MD 21403
office: (410) 295-3151
Kyle.Wilcox@noaa.gov

A: It takes over twice as long to understand the conversation.
Q: What's wrong with top-posting?
A: Top-posting.
Q: What's the worst thing about plain text email discussions?