============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Thomas Reinke
Your email address : reinke@e-softinc.com
Category : unknown
Severity : non-critical
Summary: SQL Insert result code inconsistent with JDBC
System Configuration
- --------------------
Operating System : Linux 2.0.30 RedHat
PostgreSQL version : 6.3
Compiler used : gcc 2.7.1
Hardware:
- ---------
Pentium, 128 Meg
Linux www4 2.0.30 #1 Tue Apr 22 10:49:45 EDT 1997 i686 unknown
Versions of other tools:
- ------------------------
gmake
- --------------------------------------------------------------------------
Problem Description:
- --------------------
An attempt to add a row into a table with a unique index,
for which the primary key already exists in the table,
results in an ERROR. Realistically, this is not an
application error - it is a legitimate condition that
occurs in the real world. How does an application
distinguish (in any realistic way) between unable to
insert due to duplicate key, and unable to insert
due to (you pick the error - out of memory, etc.)
JDBC's documentation indicates that INSERT, UPDATE and
DELETE statements should return a count on the number
of row's affected, and throw an SQL exception on errors.
Would this particular scenario not be a case where
the INSERT count would be returned as zero?
- --------------------------------------------------------------------------
Test Case:
- ----------
create table test (fld1 varchar(10));
insert unique index test_pkey on test(fld1);
insert into test values('line1');
- --> INSERT TUPLEID 1
insert into test values ('line1');
ERROR: Cannot insert a duplicate key into a unique index
- --------------------------------------------------------------------------
Solution:
- ---------
Apply the same heuristic to INSERTs as exists for
update's, delete's.
E.g. Update, Delete with invalid table name returns
error, so insert with invalid table name should
provide error.
E.g. Update, Delete with valid table name but no rows
affected returns count of zero, so insert that
cannot be done due to db rules should return
count of zero.
- --------------------------------------------------------------------------
