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