Postgres Pro
Downloads
Home   >   mailing lists

Thread: huh!?

huh!?

From
"Vlad Marchenko"
Date:
Good day. Please reveiew log below.

test=# \d subscribers;
                                  Table "subscribers"
  Column  |          Type          |                     Modifiers
----------+------------------------+----------------------------------------
-----------
 sid      | bigint                 | not null default
nextval('subscribers_sid'::text)
 email    | character varying(128) | not null default ''
 domain   | character varying(128) | not null default ''
 status   | smallint               | default 0
 password | character varying(128) | not null default ''
 signup   | date                   | default '2002-01-01'
 name     | character varying(128) | default ''
 sex      | character varying(1)   | default ''
 address  | character varying(128) | default ''
 city     | character varying(128) | default ''
 state    | character varying(2)   | default ''
 zip      | character varying(16)  | not null default ''
 country  | character varying(2)   | default ''
 phone    | character varying(128) | not null default ''
Indexes: subs_domain_idx
Primary key: subscribers_pkey
Unique keys: subscribers_email_key
Triggers: RI_ConstraintTrigger_63057,
          RI_ConstraintTrigger_63063,
          RI_ConstraintTrigger_63069,
          RI_ConstraintTrigger_63161,
          RI_ConstraintTrigger_63163

test=# select count(*) from subscribers;
 count
-------
 96856
(1 row)

test=# select * from subscribers where email~'domain4';
 sid | email | domain | status | password | signup | name | sex | address |
city | state | zip | country | phone
-----+-------+--------+--------+----------+--------+------+-----+---------+-
-----+-------+-----+---------+-------
(0 rows)

test=# INSERT INTO subscribers(email) SELECT (random()||'@domain4.com') from
subscribers;
ERROR:  Cannot insert a duplicate key into unique index
subscribers_email_key
test=#


I assume that this is because of pure transaction data isolation?

---
 Best Regards,

 Vlad Marchenko

 -------------------------------------------------------
 TrackingSoft LLC
 http://trackingsoft.com/  http://affiliatetracking.net/

Re: huh!?

From
"Vlad Marchenko"
Date:
Ops.. I've got it, disregard my email - random() generates duplicate values
on 100000  long sequence
:-)

sorry.
--
 Best Regards,
 Vlad Marchenko

----- Original Message -----
From: "Vlad Marchenko" <vlad@infonet.com.ua>
To: <pgsql-bugs@postgresql.org>
Sent: Friday, March 29, 2002 2:18 PM
Subject: huh!?


| Good day. Please reveiew log below.
|
| test=# \d subscribers;
|                                   Table "subscribers"
|   Column  |          Type          |                     Modifiers
| ----------+------------------------+--------------------------------------
--
| -----------
|  sid      | bigint                 | not null default
| nextval('subscribers_sid'::text)
|  email    | character varying(128) | not null default ''
|  domain   | character varying(128) | not null default ''
|  status   | smallint               | default 0
|  password | character varying(128) | not null default ''
|  signup   | date                   | default '2002-01-01'
|  name     | character varying(128) | default ''
|  sex      | character varying(1)   | default ''
|  address  | character varying(128) | default ''
|  city     | character varying(128) | default ''
|  state    | character varying(2)   | default ''
|  zip      | character varying(16)  | not null default ''
|  country  | character varying(2)   | default ''
|  phone    | character varying(128) | not null default ''
| Indexes: subs_domain_idx
| Primary key: subscribers_pkey
| Unique keys: subscribers_email_key
| Triggers: RI_ConstraintTrigger_63057,
|           RI_ConstraintTrigger_63063,
|           RI_ConstraintTrigger_63069,
|           RI_ConstraintTrigger_63161,
|           RI_ConstraintTrigger_63163
|
| test=# select count(*) from subscribers;
|  count
| -------
|  96856
| (1 row)
|
| test=# select * from subscribers where email~'domain4';
|  sid | email | domain | status | password | signup | name | sex | address
|
| city | state | zip | country | phone
| -----+-------+--------+--------+----------+--------+------+-----+---------
+-
| -----+-------+-----+---------+-------
| (0 rows)
|
| test=# INSERT INTO subscribers(email) SELECT (random()||'@domain4.com')
from
| subscribers;
| ERROR:  Cannot insert a duplicate key into unique index
| subscribers_email_key
| test=#
|
|
| I assume that this is because of pure transaction data isolation?
|
| ---
|  Best Regards,
|
|  Vlad Marchenko
|
|  -------------------------------------------------------
|  TrackingSoft LLC
|  http://trackingsoft.com/  http://affiliatetracking.net/
|

Re: huh!?

From
Tom Lane
Date:
"Vlad Marchenko" <vlad@infonet.com.ua> writes:
> test=# select count(*) from subscribers;
>  count
> -------
>  96856
> (1 row)

> test=# select * from subscribers where email~'domain4';
>  sid | email | domain | status | password | signup | name | sex | address |
> city | state | zip | country | phone
> -----+-------+--------+--------+----------+--------+------+-----+---------+-
> -----+-------+-----+---------+-------
> (0 rows)

> test=# INSERT INTO subscribers(email) SELECT (random()||'@domain4.com') from
> subscribers;
> ERROR:  Cannot insert a duplicate key into unique index
> subscribers_email_key
> test=#

> I assume that this is because of pure transaction data isolation?

No, this just says that when you generated 96856 random() values, you got
at least one pair of duplicates.

            regards, tom lane