Thread: 8.2/8.3 incompatibility
I've found an incompatibility between PostgreSQL 8.2.4 and 8.3.0 which
is not clearly documented. Here's a short example:
CREATE TABLE t1 (
id CHAR(5) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 (id) VALUES ('t1id1');
INSERT INTO t1 (id) VALUES ('t1id2');
INSERT INTO t1 (id) VALUES ('t1id3');
CREATE TABLE t2 (
id SERIAL NOT NULL,
t1id VARCHAR(5) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t2 (t1id) VALUES ('t1id1');
INSERT INTO t2 (t1id) VALUES ('t1id2');
INSERT INTO t2 (t1id) VALUES ('t1id3');
ALTER TABLE t2
ADD CONSTRAINT t2_t1id_fk
FOREIGN KEY (t1id) REFERENCES t1 (id);
(Note the different column types.)
This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
following (somewhat misleading) error message:
ERROR: insert or update on table "t2" violates foreign key constraint "t2_t1id_fk"
DETAIL: Key (t1id)=(t1id1) is not present in table "t1".
Should this be documented explicitly?
Should the error message look different?
Harald,
>
> I've found an incompatibility between PostgreSQL 8.2.4 and 8.3.0 which
> is not clearly documented. Here's a short example:
<...>
>
> (Note the different column types.)
>
Precisely -- there are a number of casts that have been removed from this release. You can find details in the release notes. A pain, to be sure, but apparently under-the-hood this helps make things saner.
You can create your own cast or modify the DDL to be sane.
HTH,
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
"Gregory Williamson" <Gregory.Williamson@digitalglobe.com> writes:
> Harald,
>> (Note the different column types.)
> Precisely -- there are a number of casts that have been removed from
> this release.
This isn't a casting problem, I don't think. 8.3 is definitely stricter
about implicit casts with FKs, eg try adding this to the test case:
regression=# create table t3 (f1 int references t1);
ERROR: foreign key constraint "t3_f1_fkey" cannot be implemented
DETAIL: Key columns "f1" and "id" are of incompatible types: integer and character.
But it's allowing the constraint and then failing at runtime, which
seems like an outright bug, since the values *should* compare equal
AFAICS. I wonder if this is related to bug #3938.
regards, tom lane
"Harald Fuchs" <hari.fuchs@googlemail.com> writes:
> (Note the different column types.)
>
> This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
> following (somewhat misleading) error message:
>
> ERROR: insert or update on table "t2" violates foreign key constraint "t2_t1id_fk"
> DETAIL: Key (t1id)=(t1id1) is not present in table "t1".
>
> Should this be documented explicitly?
> Should the error message look different?
Hm. I think this is just a bug. This shouldn't be an error at all.
The query r_triggers.c is executing is (after simplifying a bit):
SELECT *
FROM t2 fk
LEFT OUTER JOIN t1 pk ON ( pk.id = fk.t1id::character)
WHERE pk.id IS NULL
AND fk.t1id IS NOT NULL
Note the cast. Really that cast should be char(5). It may be that we have to
carry the typmod here.
But really I don't understand why we put casts here at all. The whole point of
using the opfamily to find the operator in advance was so that we could be
sure to find the "right" operator. That means we should be able to put the
operator there without casts and be confident that it'll find the right
operator. The only case where the types might not match would be for operators
which are used for multiple types -- such as binary compatible types like
char(n)/varchar/text.
So I think the fix is just to remove the four lines responsible for putting
the casts there at all.
--- ri_triggers.c 30 Jan 2008 14:27:38 +0000 1.102
+++ ri_triggers.c 07 Feb 2008 17:51:28 +0000
@@ -2920,13 +2920,9 @@
nspname = get_namespace_name(operform->oprnamespace);
appendStringInfo(buf, " %s %s", sep, leftop);
- if (leftoptype != operform->oprleft)
- appendStringInfo(buf, "::%s", format_type_be(operform->oprleft));
appendStringInfo(buf, " OPERATOR(%s.", quote_identifier(nspname));
appendStringInfoString(buf, oprname);
appendStringInfo(buf, ") %s", rightop);
- if (rightoptype != operform->oprright)
- appendStringInfo(buf, "::%s", format_type_be(operform->oprright));
ReleaseSysCache(opertup);
}
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
On Thu, 7 Feb 2008, Harald Fuchs wrote:
> This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
> following (somewhat misleading) error message:
>
> ERROR: insert or update on table "t2" violates foreign key constraint "t2_t1id_fk"
> DETAIL: Key (t1id)=(t1id1) is not present in table "t1".
If the types were considered not comparable, you should have gotten a
message to that effect rather than a not present message. More to the
point that comparison should have succeeded I think. What do the following
give?
select * from t1 where id=CAST('t1id1' as VARCHAR(5));
select * from ONLY t2 fk LEFT OUTER JOIN ONLY t1 pk ON
(pk.id = fk.t1id) WHERE pk.id IS NULL;
Gregory Stark <stark@enterprisedb.com> writes:
> The query r_triggers.c is executing is (after simplifying a bit):
> SELECT *
> FROM t2 fk
> LEFT OUTER JOIN t1 pk ON ( pk.id = fk.t1id::character)
> WHERE pk.id IS NULL
> AND fk.t1id IS NOT NULL
> Note the cast. Really that cast should be char(5). It may be that we have to
> carry the typmod here.
Bingo --- what's happening is that that's a cast to character(1):
regression=# select 'abcde'::varchar(5)::character;
bpchar
--------
a
(1 row)
> But really I don't understand why we put casts here at all. The whole point of
> using the opfamily to find the operator in advance was so that we could be
> sure to find the "right" operator.
Yeah, we've found the "right" operator, but now we have to make sure
that the parser finds the same one. Casting ensures that there is a
unique exact match and we aren't risking an unexpected result from the
ambiguous-operator resolution heuristics.
If we weren't generating a textual representation of the query, but
building a post-parse-analysis form directly, we wouldn't need these
pushups because we could just store the desired operator's OID into the
data structure. But from a readability and maintainability standpoint,
the text format is probably safest. Anyway I certainly don't care to
risk back-patching such a large change as that would be.
> So I think the fix is just to remove the four lines responsible for putting
> the casts there at all.
I'm too lazy to generate a counterexample right now, but this *will*
break things. We need to fix the typmod issue instead.
After a few moment's thought, I'm tempted by the idea of not using
format_type_be here, but always emitting the type name in the format
"schema"."typname" --- this will guarantee that the parser doesn't
insert any default typmod associated with the weird SQL-spec rules
for certain special type names. That would fix things for character,
and also bit which has the same kind of issue, but can anyone think of
a case it would make worse?
regards, tom lane
In article <20080207095031.O22500@megazone.bigpanda.com>,
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 7 Feb 2008, Harald Fuchs wrote:
>> This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
>> following (somewhat misleading) error message:
>>
>> ERROR: insert or update on table "t2" violates foreign key constraint "t2_t1id_fk"
>> DETAIL: Key (t1id)=(t1id1) is not present in table "t1".
> If the types were considered not comparable, you should have gotten a
> message to that effect rather than a not present message.
Yes, this was really confusing.
> More to the point that comparison should have succeeded I think.
Well, it did succeed in 8.2.x, and I'm actually grateful that 8.3.0
noticed the sloppiness on my side.
> What do the following give?
> select * from t1 where id=CAST('t1id1' as VARCHAR(5));
This returns t1id1, as expected.
> select * from ONLY t2 fk LEFT OUTER JOIN ONLY t1 pk ON
> (pk.id = fk.t1id) WHERE pk.id IS NULL;
This returns an empty result set.