postgreSQL uuid generation

Question

select uuid_generate_v4() as one, uuid_generate_v4() as two;

"one" uuid and "two" uuid are equal!

CREATE TABLE "TB"
(
  "Id" uuid NOT NULL DEFAULT uuid_generate_v4(),
  "Title" character varying NOT NULL,
   CONSTRAINT "TB_Class_ID" PRIMARY KEY ("Id")
);

postgresql 9.0 pgAdmin 1.12.3

insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');

or

insert into "TB" ("Title") values ('111');
insert into "TB" ("Title") values ('111');
insert into "TB" ("Title") values ('111');

result:

ERROR:  duplicate key value violates unique constraint "TB_Class_ID"
DETAIL:  Key ("Id")=(12ab6634-995a-4688-9a9a-ee8c3fe24395) already exists.

whereas

postgreSQL maestro 9.2.0.4

insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');

result: 1 rows affected;

I understand that maestro added records one by one, but why uuid_generate_v4() returns the same value after two calls? (In pgAdmin case).

And how can I add several rows by one request?

Answer 1

At some point in the past, the uuid_generate_* functions were erroneously marked as IMMUTABLE, which would result in the behavior you show. This has been fixed in all the latest minor versions, but you have to re-run the installation script (uuid-ossp.sql) to get the updated function definitions. (You can also look into the installation script to verify that you have an up-to-date version. The functions should be marked VOLATILE.)

Answer 2

Within a given transaction, the function uuid_generate_v4() returns the same value.

When statements are grouped together and run as "one command", there is one transaction, so every call to uuid_generate_v4() will return the same value.

The two ways to "fix" this are:

1. Make separate database calls every time you use the function (this is easiest)
2. Use a non-auto commit connection where you control the transactions and separate each usage within a BEGIN; COMMIT pair (this is a hassle - don't do this unless you have to)