postgresql 8.1, fedora core 4
I'm trying to update a database with a few new tables and insert some
data. However, psql is refusing to insert some of the data, leading to
errors when trying to refer to the sequence in the next insert (to a
cross table).
Here are the new tables (Note that set_id() and get_id() are functions
which use the $_SHARED structure so that i can set some vars and refer
back to them later)
First, i set up the new tables, inserting some data into the look-up
table for grant types.
-- snip --
DROP TABLE funding_type CASCADE;
CREATE TABLE funding_type (
id SERIAL PRIMARY KEY,
name VARCHAR(16) NOT NULL
);
INSERT INTO funding_type (name) VALUES ('Grant');
SELECT set_id('Grant ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Award');
SELECT set_id('Award', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Residency');
SELECT set_id('Residency ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Special');
SELECT set_id('Special ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Other');
SELECT set_id('Other ', CAST(currval('funding_type_id_seq') AS INT));
-- this is the problem table. It
-- gets created, but nothing inserts
DROP TABLE arts_funder CASCADE;
CREATE TABLE arts_funder (
id SERIAL PRIMARY KEY,
name VARCHAR(256) NOT NULL,
fund_name VARCHAR(128),
funding_type_id INT4 NOT NULL,
amount TEXT,
short_description TEXT,
long_description TEXT,
eligibility TEXT,
deadline VARCHAR(256),
website VARCHAR(256),
phone VARCHAR(256),
disclaimer TEXT,
CONSTRAINT
fk_arts_funder_funding_type FOREIGN KEY (funding_type_id)
REFERENCES funding_type
ON DELETE CASCADE
);
-- this table is fine
DROP TABLE arts_funder_discipline CASCADE;
CREATE TABLE arts_funder_discipline (
arts_funder_id INT4 NOT NULL,
discipline_id INT4 NOT NULL,
CONSTRAINT
fk_arts_funder_discipline_arts_funder_id FOREIGN KEY (arts_funder_id)
REFERENCES arts_funder
ON DELETE CASCADE,
CONSTRAINT
fk_arts_funder_discipline_discipline_id FOREIGN KEY (discipline_id)
REFERENCES discipline
ON DELETE CASCADE
);
-- snip --
The inserts come next in the SQL file i'm using as input. But i should
point out this first before going on:
-- snip --
test=# \d arts_funder
Table "public.arts_funder"
Column | Type |
Modifiers
-------------------+------------------------+----------------------------------------------------------
id | integer | not null default
nextval('arts_funder_id_seq'::regclass)
body | character varying(256) | not null
fund_name | character varying(128) |
funding_type_id | integer | not null
amount | text |
short_description | text |
long_description | text |
eligibility | text |
deadline | character varying(256) |
website | character varying(256) |
phone | character varying(256) |
disclaimer | text |
Indexes:
"arts_funder_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"fk_arts_funder_funding_type" FOREIGN KEY (funding_type_id)
REFERENCES funding_type(id) ON DELETE CASCADE
-- snip --
So, looks good to me.
The inserts into both arts_funder and arts_funder_discipline look like so:
-- snip --
INSERT INTO arts_funder (name, fund_name, funding_type_id, amount,
short_description, long_description, eligibility, deadline, website,
phone, disclaimer)
VALUES ('The Canada Council for the Arts', 'Artists and Community
Collaboration Fund (ACCF)', CAST(get_id('Grant') AS INT), 'Varies',
'This program provides support for developing projects that bring
together professional artists and communities to give creative arts a
stronger presence in everyday life.', '<p>The goal of the ACCF is to
give creative arts a stronger presence in everyday life. The ACCF
provides funding for creative collaborations between communities and
artists. The collaborative process may include developing projects of a
diverse nature; projects that involve youth and arts education are
encouraged. The ACCF is implemented through the participating programs
in all sections of the Canada Council.</p>', '<p>Please contact the
Canada Council (and the program office of the discipline that interests
you) for detailed eligibility requirements.</p>', 'Varies',
'www.canadacouncil.ca', '1-800-263-5588 (toll free)<br />(613)
566-4414<br />TTY (TDD) machine for hearing-impaired callers: (613)
565-5194', 'Please contact Canada Council directly for program details.');
-- each of the inserts into arts_funder is followed by
-- one or more into arts_funder_discipline, which is just
-- a cross table. I get errors here because psql thinks
-- that the current value of arts_funder_id_seq does
-- not exist in arts_funder.
INSERT INTO arts_funder_discipline (arts_funder_id, discipline_id)
VALUES (CAST(currval('arts_funder_id_seq') AS INT), 1);
-- snip --
The problem is, the first insert *silently* fails to occur, so the next
one fails with an error:
psql:funders.sql:1169: ERROR: insert or update on table
"arts_funder_discipline" violates foreign key constraint
"fk_arts_funder_discipline_arts_funder_id"
DETAIL: Key (arts_funder_id)=(217) is not present in table "arts_funder".
But, as you can see, arts_funder_id_seq *is* incrementing, but no data
is being inserted into arts_funder. The sequence is incrementing without
an insert occurring! Huh?
-- snip --
test=# select * from arts_funder;
id | name | fund_name | funding_type_id | amount | short_description |
long_description | eligibility | deadline | website | phone | disclaimer
----+------+-----------+-----------------+--------+-------------------+------------------+-------------+----------+---------+-------+------------
(0 rows)
-- snip --
What gives??
Also, i set the output to go to an external file, only to find that psql
is not even bothering to send everything out. I ran this just before
trying to run the update with all the inserts:
test=# \o out.txt
test=# \i update.sql
Which shows:
-- snip --
DROP TABLE
CREATE TABLE
INSERT 294262 1
set_id
--------
ok
(1 row)
INSERT 294263 1
set_id
--------
ok
(1 row)
INSERT 294264 1
set_id
--------
ok
(1 row)
INSERT 294265 1
set_id
--------
ok
(1 row)
INSERT 294266 1
set_id
--------
ok
(1 row)
DROP TABLE
CREATE TABLE
DROP TABLE
CREATE TABLE
VACUUM
-- snip --
There should be a bunch of inserts (or errors, or SOMETHING) right
before the VACUUM.
Again, the tables are all created just fine. But it's as if all of the
inserts into arts_funder are commented out. psql doesn't seem to see any
of them, just the next inserts into arts_funder_discipline. HOWEVER, for
some reason, arts_funder_id_seq *is* being incremented.
Any guidance very much appreciated. Sorry for the length of this post.
brian
