Re: [BUGS] backend closed the channel unexpectedly?!?

[Sean Kelly]

On Wed, 08 Nov 2000 16:54:40 +0200, Martti Hertzen said:

>  testsqc=> DELETE FROM henkilo WHERE id = 12;
>  pqReadData() -- backend closed the channel unexpectedly.
>  This probably means the backend terminated abnormally
>  before or while processing the request.
>  The connection to the server was lost. Attempting reset: Failed.
>  !>
>  
>  Here's the output of the debug channel:
>  
>  001108.16:31:16.753 [31195] StartTransactionCommand
>  001108.16:31:16.753 [31195] query: DELETE FROM henkilo WHERE id = 12;
>  001108.16:31:16.840 [31195] ProcessQuery
>  postmaster: reaping dead processes...
>  postmaster: CleanupProc: pid 31195 exited with status 139
>  Server process (pid 31195) exited with status 139 at Wed Nov  8 16:31:17
>  2000
>  Terminating any active server processes...
>  Server processes were terminated at Wed Nov  8 16:31:17 2000
>  Reinitializing shared memory and semaphores
>  001108.16:31:17.822 [30871] shmem_exit(0)
>  binding ShmemCreate(key=52e4b5, size=1104896)

I had this problem after renaming OLD_TABLE to NEW_TABLE
after creating foreign keys in OLD_TABLE referencing MASTER_TABLE.

Listen to Tom ... he knows his stuff ;)

-- 
Sean Kelly <[EMAIL PROTECTED]>
"If 99% is good enough, then gravity will not work for 14 minutes
 every day."

[BUGS] index(fct(primary key)) kills INSERTs

[Frank Miles]

see attached bug-report


To report any other bug, fill out the form below and e-mail it to
[EMAIL PROTECTED]


POSTGRESQL BUG REPORT TEMPLATE



Your name   :   Frank Miles
Your email address  :   [EMAIL PROTECTED]


System Configuration
-
  Architecture (example: Intel Pentium) : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : Linux 2.2.17 ELF

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2

  Compiler used (example:  gcc 2.8.0)   : 2.95.2


Please enter a FULL description of your problem:


If an index is created based on a function of the primary key,
you cannot insert new entries into the database.
(this occurs for pl/pgsl , anyway)


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
--

/*  fun_test.sqlTest table-function insert problem
 */

BEGIN;

CREATE TABLE test_table (
tt_id   serial  PRIMARY KEY,
tt_descrtextNOT NULL
);

INSERT INTO test_table (tt_descr) VALUES ('first test record');

SELECT * FROM test_table;

CREATE FUNCTION tt_dep(int) RETURNS text AS '
DECLARE
dum_int ALIAS FOR   $1;
precrecord;
ttmptext;
j2  int;
BEGIN
SELECT INTO prec * FROM test_table WHERE tt_id = dum_int;
IF NOT FOUND THEN
RAISE EXCEPTION ''project % not found'', dum_int;
END IF;
ttmp := CAST(prec.tt_id AS TEXT);
j2= char_length(ttmp);
RETURN rpad(''Prefix'',j2,ttmp);
END;
' LANGUAGE 'plpgsql';

-- demonstrate simple function
SELECT tt_dep(1);

-- insert still works
INSERT INTO test_table (tt_descr) VALUES ('second test record');

SELECT * FROM test_table;

-- add problematic index
CREATE INDEX demo_index ON test_table ( tt_dep(tt_id) );

-- function still works
SELECT tt_dep(2);

-- insert will die
INSERT INTO test_table (tt_descr) VALUES ('third - will fail');

COMMIT;

/*  end of fun_test.sql
 */

Clearly the above function is a contrived example.  In the original problem
I had a function that was far more complex, depending on other columns
in the table as well as the primary key.

My humble apologies if I've missed documentation relating to this apparent
deficiency.

Hope this is an easy one!

If you know how this problem might be fixed, list the solution below:
-

sorry...

Re: [BUGS] index(fct(primary key)) kills INSERTs

[Tom Lane]

Frank Miles <[EMAIL PROTECTED]> writes:
> If an index is created based on a function of the primary key,
> you cannot insert new entries into the database.

I think the critical point here is that your "function of the primary
key" is actually doing a SELECT from the table:

SELECT INTO prec * FROM test_table WHERE tt_id = dum_int;
IF NOT FOUND THEN
RAISE EXCEPTION ''project % not found'', dum_int;
END IF;

When I try your example, I get

play=> INSERT INTO test_table (tt_descr) VALUES ('third - will fail');
ERROR:  project 3 not found

which surprises me not at all, because at the point where this function
is invoked, the new record with tt_id 3 hasn't been entered into the
table yet.

I'm not sure what you are really trying to accomplish here --- as you
say, it's a stripped-down example and not too intelligible.  As far
as the example goes, you could skip the SELECT and just use the
passed-in parameter value.  What was the original goal that made you
feel you needed to SELECT the about-to-be-inserted row?

regards, tom lane