[BUGS] update on unique constraint

[Theo Kramer]

postgresql 7.0.3 on RedHat 7.0
--

create table whois (domain text, version integer);
CREATE
theo=# create unique index whois_idx on whois (domain,version);
CREATE
theo=# insert into whois values ('mydomain.co.za', 0);
INSERT 18829 1
theo=# insert into whois values ('mydomain.co.za', 1);
INSERT 18830 1
theo=# insert into whois values ('mydomain.co.za', 2);
INSERT 18831 1
theo=# update whois set version = version + 1 where domain = 'mydomain.co.za';
ERROR:  Cannot insert a duplicate key into unique index whois_idx

Oracle 8.1.6 on RedHat 6.2
--
SQL> create table whois (domain varchar2(64), version integer);

Table created.

SQL> create unique index whois_idx on whois (domain,version);

Index created.

SQL> insert into whois values ('mydomain.co.za', 0);
1 row created.

SQL> insert into whois values ('mydomain.co.za', 1);

1 row created.

SQL> insert into whois values ('mydomain.co.za', 2);

1 row created.

SQL> update whois set version = version + 1 where domain = 'mydomain.co.za';

3 rows updated.

SQL> select * from whois;

DOMAIN   VERSION
 --
mydomain.co.za   1
mydomain.co.za   2
mydomain.co.za   3

SQL>

Ideas/thoughts?

I think oracle is correct - no contraints are violated and the update
should be atomic.

Regards
Theo

[BUGS] PQexecPrepared - PostgreSQL 7.4.1

[Theo Kramer]

Hi

I am having a problem with PQexecPrepared() as follows (derived from
testlibpq3.c)

PQexec(conn, 
   "PREPARE S123_000 (text) AS SELECT * from test1 WHERE t = $1");

When invoking PQexecPrepared() I get the following message from the
backend

ERROR:  prepared statement "S123_" does not exist


Yet if I use 

PQexec(conn, "EXECUTE S123_ ('ho there')");

instead of PQexecPrepared() then all works perfectly.

Sounds like a bug to me...?

Regards
Theo


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [BUGS] BUG #1178: PQexecPrepared - PostgreSQL 7.4.1

[Theo Kramer]

On Tue, 2004-06-22 at 19:23, Tom Lane wrote:
> "PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes:
> > PQexec(conn, 
> >"PREPARE S123_000 (text) AS SELECT * from test1 WHERE t = $1");
> > When invoking PQexecPrepared() I get the following message from the backend
> > ERROR:  prepared statement "S123_" does not exist
> 
> Case sensitivity.  PQexecPrepared takes its name argument literally,
> but anything inside a SQL command will get downcased unless quoted.
> So you actually prepared "s123_000".
> 

Many thanks - perhaps a gotcha in the documentation for PQexecPrepared()
would useful...

Regards
Theo


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly