Re: Unique is non unique; no nulls

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: <cbrazvan(at)laitek(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Unique is non unique; no nulls
Date: 2007-09-23 06:04:39
Message-ID: 87myvdvrwo.fsf@oxford.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<cbrazvan(at)laitek(dot)com> writes:

> Hmm... maybe this will help: in the same function (i.e. transaction), I am
> also doing an update on the same table, using suid as update key, that is:
> update migratek.mt set sdate = smth where suid = s.suid;

Do the rows in the output to the second query I sent (the one with the large
output you attached) look like the "before" and "after" for one of these
updates?

> Can this - performing an update on a table from within the (same) table
> populating transaction that was not yet commited - throw off postgress in
> some way? Especially given the large number of tuples inserted (2.7
> million)

It certainly shouldn't. I'm still a bit skeptical that you really have a
unique index on the table. Perhaps there was an error when you created it and
you didn't notice? Or perhaps the index was dropped at some point later? Can
you send the table description including the list of indexes to be sure?

> Right now, my inclination is to rewrite the function, separating the
> updates from the inserts and see if that works.

I know the pressure to get something that works can be pretty high, but in the
long term I think it would be better to know exactly what happened here.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Razvan Costea-Barlutiu 2007-09-23 11:43:38 Re: Unique is non unique; no nulls
Previous Message cbrazvan 2007-09-23 04:27:14 Re: Unique is non unique; no nulls