Skip site navigation (1) Skip section navigation (2)

transaction block causing trouble

From: Fabian <Fabian(dot)Groffen(at)cwi(dot)nl>
To: pgsql-sql(at)postgresql(dot)org
Subject: transaction block causing trouble
Date: 2004-02-20 09:56:05
Message-ID: 4035D9B5.1060200@cwi.nl (view raw or flat)
Thread:
Lists: pgsql-sql
Hey I hope someone can help me here.

I'm doing some little test on PostgreSQL 7.3.5 and am experiencing some 
weird behaviour.

I create some tables and add values to it using insert into select from. 
After I am done with that, I create unique constraints on the tables. 
The reason to do that afterwards was to speed up the process a bit, but 
that doesn't seem to help anything. Anywayz, all goes well if I don't 
mention anything about transactions in my file and feed it to pgsql, 
which will autocommit after each line, if I'm not mistaken. However if I 
put begin; and commit; before and after the complete bunch of lines, 
pgsql suddenly encounters an error:

psql:test.sql:1346: NOTICE:  ALTER TABLE / ADD UNIQUE will create 
implicit index 'rkb_unique_head' for table 'rkb'
psql:test.sql:1346: ERROR:  Cannot create unique index. Table contains 
non-unique values
psql:test.sql:1350: ERROR:  current transaction is aborted, queries 
ignored until end of transaction block
psql:test.sql:1356: ERROR:  current transaction is aborted, queries 
ignored until end of transaction block
COMMIT

The test.sql file contains:

begin;
drop table rka;
drop table rkb;
drop table tapestry;
commit;
begin;
create table RKA( head int, tail int);
create table RKB( head int, tail int);
create table tapestry( attr0 int, attr1 int);

... some 1024 inserts like insert into RKA values (0, 726);

insert into RKB
select head+0, tail+0
from RKA;
insert into RKB
select head+8192, tail+8192
from RKA;

... this continued till there are 102400 rows in RKB

update RKB set tail=(tail*37) % 102400;
update RKB set tail=(tail*31) % 102400;
update RKB set tail=(tail*29) % 102400;
update RKB set tail=(tail*23) % 102400;
update RKB set tail=(tail*19) % 102400;
update RKB set tail=(tail*17) % 102400;
update RKB set tail=(tail*13) % 102400;
update RKB set tail=(tail*11) % 102400;
update RKB set tail=(tail*7) % 102400;

-- build unique constraints
alter table RKA add constraint rka_unique_head unique (head);
alter table RKB add constraint rkb_unique_head unique (head);
 

insert into tapestry
     select R0.head, R0.tail
from RKB R0;
 

-- the insert will only take place if the above unique constraint was
-- satisfied
-- build unique constraint
alter table tapestry add constraint tapestry_unique_attr0 unique
(attr0);
 

commit;


Why does the whole thing run luckily without transactions, and complains 
about a duplicate entry when in an transaction block? Also when using 
less insert statements for RKB the transaction does not succeed.
When specifying unique when creating the tables, and skipping the add 
constraint part at the bottom, the whole transaction does succeed.

my file can be found gzipped at http://www.cwi.nl/~fabian/test.sql.gz

Any help is appreciated for I'm clueless about this issue :)

pgsql-sql by date

Next:From: Plant ThomasDate: 2004-02-20 10:07:22
Subject: Problem with FOR SELECT in plpgsql function
Previous:From: Sumita BiswasDate: 2004-02-20 08:43:55
Subject: Re: Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group