Обсуждение: spooky refusal to insert

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

That should be version 8.1.4 -- sorry

brian

brian wrote:
> 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).
>

Sure, less than ten minutes since i sent out my plea, i figured it out
for myself. For the curious:

>
> -- 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));
>

Note the extra spaces after the variable names i'm using:

set_id('Grant ', ...

Because psql was not writing the errors to the file, i was relying on
what i saw in my terminal. The very last insert into arts_funder was
followed by 10 subsequent inserts into arts_funder_discipline. I'd
missed the very first error:

psql:funders.sql:1171: ERROR:  null value in column "funding_type_id"
violates not-null constraint

Which is the error on insert into arts_funder. The construct
CAST(get_id('Grant') AS INT) was returning NULL because of the
whitespace, above.

I'm still confused as to why errors are not written to the output file
(\o out.txt). There must be some way to capture these, aside from a
quickly scrolling terminal window.

brian

On 1/2/07, brian <brian@zijn-digital.com> wrote:
Hi Brian,

    Can you please explain the $_SHARED structure you mention here? I am not able to find documentation for it!!

    If I understand it correctly, it probably is a structure stored in the backend, and the plpgsql functions can use it to store values across multiple calls; like C global variables.

Regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

Gurjeet Singh wrote:
> On 1/2/07, brian <brian@zijn-digital.com> wrote:
>
>>
>> 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)
>>
>>
> Hi Brian,
>
>    Can you please explain the $_SHARED structure you mention here? I am not
> able to find documentation for it!!
>
>    If I understand it correctly, it probably is a structure stored in the
> backend, and the plpgsql functions can use it to store values across
> multiple calls; like C global variables.
>

Certainly. I'd been about to explain it further, however my initial cry
for help was already dragging on quite a bit so i left it at that.

$_SHARED is a global hash in PL/Perl. I found these two functions in the
docs:

39.4. Global Values in PL/Perl
http://www.postgresql.org/docs/8.2/static/plperl-global.html

You can find more info about using $_SHARED to communicate between
functions here:
http://www.oreillynet.com/pub/a/databases/2006/05/25/the-future-of-perl-in-postgresql.html

With these two functions, you pass the hash key you want to use, along
with the value you wish to store. The if() test is assigning it first,
so if, for some reason it cannot be set, if() will fail. You can, of
course, change the return value to anything you'd like. The original is
fine for my needs.

I generally use these to grab the current value of the sequence for some
look-up table as it is being added to. Things like sports teams,
countries, etc. Generally, tables with data that doesn't change a whole
lot, and are used primarily for relating between other tables.

So, i can use that later when i need to cross-reference some other
value. In this case, it was relating the arts_funders with specific arts
disciplines, as well as with funding types, as i added them to the database.

If you'd like a more concrete example, i'd be happy to send that.

/**
  * Store IDs from lookup tables
  *
  * @param  text    name  the hash key (lookup table name entry)
  * @param  int  val  the lookup table row id
  * @returns  text  success or not
  **/
CREATE OR REPLACE FUNCTION set_id(name TEXT val INT4) RETURNS TEXT AS
$$
if ($_SHARED{$_[0]} = $_[1])
{
   return 'ok';
}
else
{
   return "can't set shared variable $_[0] to $_[1]";
}
$$ LANGUAGE plperl;


/**
  * Retrieve ID from lookup table
  *
  * @param  text    name  the hash key
  * @returns int the hash value
  **/
CREATE OR REPLACE FUNCTION get_id(name text) RETURNS INT4 IMMUTABLE AS
$$
   return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

Note that this last function will return NULL if the key does not exist
(which was the root of my problem, as the keys initially had extra
whitespace--oops!)

regards,
brian