error creating table that worked under 7.0.3

From: "Kyle F(dot) Downey" <kdowney(at)amberarcher(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: error creating table that worked under 7.0.3
Date: 2001-04-05 14:16:33
Message-ID: 3ACC7E41.5060008@amberarcher.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

- Postgresql 7.1RC2
- installed from the RPM on Red Hat 7.0 on x86
- fresh databases built (initdb)
- error happens connecting with psql on UNIX domain sockets,
but there's no evidence it's exclusive to that interface

I am not sure whether this is a bug or a tightening-up of a previous
loose SQL definition.
Previously, I was able to define constraints that refer to the same
table within the table itself,
like so:

CREATE SEQUENCE incidents_id_seq;
GRANT ALL ON incidents_id_seq TO its;
CREATE TABLE incidents (
-- generated sequence ID for this incident
id INTEGER DEFAULT nextval('incidents_id_seq'),

-- if the resolution is to determine that this
-- bug is a duplicate, this will contain the
-- duplicate ID
duplicate_iid INTEGER NULL,

-- if an incident is declared as a duplicate of another
-- incident and that incident is deleted, cascade to
-- delete this one too
CONSTRAINT duplicate_iid_exists
FOREIGN KEY(duplicate_iid) REFERENCES incidents(id)
ON DELETE CASCADE
);

In 7.1rc2, this same DDL results in this error:

ERROR: UNIQUE constraint matching given keys for referenced table
"incidents" not found

(I've excerpted the table--the real one is much larger.)

Also, a minor nit. While it's fine that the default installs no longer
enable TCP/IP networking
when starting with the /etc/init.d/postgresql script, a note should be
made of this change; maybe
I missed it. I use JDBC and was surprised at first when it didn't
connect until I realized what had happened.

Thanks for a great product, folks. I use it every day. :-)

--kd

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2001-04-05 15:10:37 Re: compilation error
Previous Message Nick Wellnhofer 2001-04-05 13:50:36 backend crashes with mnogosearch