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

From: "Emils Klotins" <emils(at)mail(dot)usis(dot)bkc(dot)lv>
To: pgsql-bugs(at)postgresql(dot)org
Cc: <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Backend dies upon UPDATE of DATE field with CASE WHEN, 6.5.3
Date: 1999-11-29 17:33:10
Message-ID: 15242538864873@bkc.lv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Your name : Emils Klotins
Your email address :emils(at)mail(dot)usis(dot)bkc(dot)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

Attachment Content-Type Size
unknown_filename text/plain 602 bytes
unknown_filename text/plain 1.5 KB

Browse pgsql-bugs by date

  From Date Subject
Next Message Slavica Stefic 1999-11-29 23:28:08 Intersect with null fields
Previous Message Tom Lane 1999-11-29 16:18:32 Re: Backend dies upon UPDATE of DATE field with CASE WHEN, 6.5.3