Thread

Backend dies upon UPDATE of DATE field with CASE WHEN, 6.5.3

Emils Klotins <emils@mail.usis.bkc.lv> — 1999-11-29T17:33:10Z

==============================================
                       POSTGRESQL BUG REPORT TEMPLATE
==============================================

Your name		:	Emils Klotins
Your email address	:emils@mail.usis.bkc.lv


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	: 
Intel Pentium II, 128MB RAM

  Operating System (example: Linux 2.0.26 ELF) 	: 
Linux 2.0.36 ELF, RedHat 5.2 distribution

  PostgreSQL version (example: PostgreSQL-6.5.3):   
PostgreSQL-6.5.3 

  Compiler used (example:  gcc 2.8.0)		:
gcc 2.7.2.3

Please enter a FULL description of your problem:
------------------------------------------------

1. I have a table with the following structure:

CREATE TABLE "authors" (
        "author_id" int4 DEFAULT nextval ( 'author_ids' ) NOT 
NULL,
        "firstname" text NOT NULL,
        "firstname_original" text,
        "lastname" text NOT NULL,
        "lastname_original" text,
        "alias1" text,
        "alias1_original" text,
        "birthdate" date,
        "birthplace" text,
        "birthplace_original" text,
        "deathdate" date,
        "deathplace" text,
        "deathplace_original" text,
        "commentary" text,
        "address_id" int4);

2. Attached is the authors table dump I got from:
pg_dump -a -t authors mydb > auth.data (approx 0.5KB)

3. If I try the following update in the psql monitor:

SET DATESTYLE TO 'German';
UPDATE authors set firstname='dasd',
lastname='asdadasd', 
birthdate=(CASE WHEN (text '12-12-1956')=(text '') THEN 
NULL ELSE '12-12-1956' END), 
birthplace='asdasd',commentary='', 
lastname_original='asdasd', firstname_original='asdfg', 
birthplace_original='' WHERE author_id=56

I get UPDATE 1, however, on all subsequent UPDATEs for that 
row, backend dies with the following error message.

> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated 
abnormally
>         before or while processing the request.
> We have lost the connection to the backend, so further 
processing
> is impossible.  Terminatin g.

4. I also get the same error if after UPDATE I try to SELECT 
the address_id field from that row (including of course trying to 
"SELECT *" ). If I try to select birthdate from that row, I will get 
a date of '01-15-2000' no matter what the UPDATE statement 
should have made it. (Before the update birthdate was NULL)

5. Postmaster remains alive,  and I can immediately 
reconnect. This error by the way, disconnects ALL backends 
active at that moment (maybe it should be that way, I am just 
noting it FYI).

6. The above error does NOT happen if the result of the above 
query inserts a NULL into the date field. Ie the query:
"UPDATE authors set firstname='dasd',
lastname='asdadasd', 
birthdate=(CASE WHEN (text '')=(text '') THEN NULL ELSE '' 
END), 
birthplace='asdasd',commentary='', 
lastname_original='asdasd', firstname_original='asdfg', 
birthplace_original='' WHERE author_id=56"

works fine.

7. The problem can be repeated with the table in question and 
is irrelevant of specific rows (author_ids).

8. In case of interest, why should I use such a CASE statement: this query is executed via a web server 
and the actual query form is:

<sqlquery quote=+ query="SET DATESTYLE TO 'German';UPDATE authors set firstname='#firstname#',
lastname='#lastname#', birthdate=CASE WHEN (text '#birthdate#')=(text '') THEN NULL ELSE '#birthdate#'  END, 
birthplace='#birthplace#',commentary='#commentary#', lastname_original='#lastname_original#', 
firstname_original='#firstname_original#', birthplace_original='#birthplace_original#' WHERE author_id=#id#">

8.1. I thought that maybe webserver added some garbage to 
the query being passed to the backend -- it was known to add 
0x00 to the end of the values, so I copied & pasted the actual 
query, manually reentered the date values and ran it via psql. 
Result did not change.

9. Attached in file pg.core is the output of the gdb 'bt' 
command from the core, as suggested by Mr. Tom Lane.

I shall be happy to answer any additional questions and/or 
send any additional info/db structure/contents, if that would be 
of any help.

Best regards,

Emils Klotins