Обсуждение: Catching unique_violation exception on specific column/index

Hi there,

I have a table with several UNIQUE and CHECK constraints.  One of these
UNIQUE constraints actually *can* be violated -- not on the table level,
of course, but on the application level -- meaning, if the entry with
particular foo_key is already in there, do not throw an exception, just
silently do nothing.

The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
not really cut it because I want to catch unique_violation only when it
happens on "foo_key", and still rightfully complain on others.  However,
there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
Is there a way to do this without using triggers and in a less ugly way
than the code below?

    IF SQLERRM = 'duplicate key value violates unique constraint' ||
          ' "foo_key"' THEN
        RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
    ELSE
        RAISE EXCEPTION '%: %', SQLSTATE, SQLERRM;
    END IF;

./danfe

Alexey Dokuchaev schrieb am 11.06.2018 um 12:10:
> I have a table with several UNIQUE and CHECK constraints.  One of these
> UNIQUE constraints actually *can* be violated -- not on the table level,
> of course, but on the application level -- meaning, if the entry with
> particular foo_key is already in there, do not throw an exception, just
> silently do nothing.
> 
> The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
> not really cut it because I want to catch unique_violation only when it
> happens on "foo_key", and still rightfully complain on others.  However,
> there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
> Is there a way to do this without using triggers and in a less ugly way
> than the code below?
> 
>     IF SQLERRM = 'duplicate key value violates unique constraint' ||
>           ' "foo_key"' THEN
>         RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
>     ELSE
>         RAISE EXCEPTION '%: %', SQLSTATE, SQLERRM;
>     END IF;
> 
> ./danfe
 
What's wrong  with: 

  INSERT ...
  ON CONFLICT (foo_key) DO NOTHING


 

On Mon, Jun 11, 2018 at 05:10:33PM +0700, Alexey Dokuchaev wrote:
> The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
> not really cut it because I want to catch unique_violation only when it
> happens on "foo_key", and still rightfully complain on others.  However,
> there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
> Is there a way to do this without using triggers and in a less ugly way
> than the code below?

Oh, I completely forgot about rules:

    CREATE OR REPLACE RULE ignore_duplicate_foo AS
      ON INSERT TO table_bar WHERE EXISTS (
        SELECT 1 FROM table_bar WHERE foo = NEW.foo)
      DO INSTEAD NOTHING;

Sorry for the noise.

./danfe

Am 11.06.2018 um 12:58 schrieb Alexey Dokuchaev:
>> What's wrong with:
>>
>>    INSERT ...
>>    ON CONFLICT (foo_key) DO NOTHING
> Nothing I guess, except that it is available since 9.5 (right?), and I try
> to stay compatible with 9.3.  Sorry for not saying this in the first place.
>
> ./danfe
>

... 9.3 will be out of support soon


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

On Mon, Jun 11, 2018 at 12:30:13PM +0200, Thomas Kellerer wrote:
> Alexey Dokuchaev schrieb am 11.06.2018 um 12:10:
> > I have a table with several UNIQUE and CHECK constraints.  One of these
> > UNIQUE constraints actually *can* be violated -- not on the table level,
> > of course, but on the application level -- meaning, if the entry with
> > particular foo_key is already in there, do not throw an exception, just
> > silently do nothing.
>  
> What's wrong with:
> 
>   INSERT ...
>   ON CONFLICT (foo_key) DO NOTHING

Nothing I guess, except that it is available since 9.5 (right?), and I try
to stay compatible with 9.3.  Sorry for not saying this in the first place.

./danfe

Alexey Dokuchaev schrieb am 11.06.2018 um 12:58:
>>> I have a table with several UNIQUE and CHECK constraints.  One of these
>>> UNIQUE constraints actually *can* be violated -- not on the table level,
>>> of course, but on the application level -- meaning, if the entry with
>>> particular foo_key is already in there, do not throw an exception, just
>>> silently do nothing.
>>  
>> What's wrong with:
>>
>>   INSERT ...
>>   ON CONFLICT (foo_key) DO NOTHING
> 
> Nothing I guess, except that it is available since 9.5 (right?), and I try
> to stay compatible with 9.3.  Sorry for not saying this in the first place.

Andreas already mentioned that 9.3 will be EOL soon (3 months from now), but 
the performance and efficiency for concurrent execution of ON CONFLICT is 
much better than anything you can implement yourself. 

If that functionality is an important part of your code, you should consider 
upgrading to 10 (or 9.6 if your are really conservative) rather sooner
than later. 

On Mon, Jun 11, 2018 at 01:26:16PM +0200, Thomas Kellerer wrote:
> If that functionality is an important part of your code, you should
> consider upgrading to 10 (or 9.6 if your are really conservative)
> rather sooner than later.

Oh well, fair enough.  As much as I'd love to stick to the lowest
supported (and sometimes even unsupported) versions, ON CONFLICT is
indeed very handy, esp. since I have a few UPSERT's implemented the
old way already (via catching the "unique_violation" exception).

Shall I update to 9.6/10, I have a bit off-topic (to the original
subject) question: right now, when I need to get the length of an
array (never multidimensional), I do this:

    coalesce(array_length(foo, 1), 0);

In 9.4+, I can call cardinality().  I'm a bit hesitant: is doing so
semantically correct, or should I still do coalesce(..., 0) in this
case?  This is not about the outcome, it's whether cardinality() is
semantically correct to obtain the number of the array items, or it
was introduced for other means?

./danfe

On 06/11/2018 11:21 AM, Alexey Dokuchaev wrote:
> On Mon, Jun 11, 2018 at 01:26:16PM +0200, Thomas Kellerer wrote:
>> If that functionality is an important part of your code, you should
>> consider upgrading to 10 (or 9.6 if your are really conservative)
>> rather sooner than later.
> 
> Oh well, fair enough.  As much as I'd love to stick to the lowest
> supported (and sometimes even unsupported) versions, ON CONFLICT is
> indeed very handy, esp. since I have a few UPSERT's implemented the
> old way already (via catching the "unique_violation" exception).
> 
> Shall I update to 9.6/10, I have a bit off-topic (to the original
> subject) question: right now, when I need to get the length of an
> array (never multidimensional), I do this:
> 
>      coalesce(array_length(foo, 1), 0);
> 
> In 9.4+, I can call cardinality().  I'm a bit hesitant: is doing so
> semantically correct, or should I still do coalesce(..., 0) in this
> case?  This is not about the outcome, it's whether cardinality() is
> semantically correct to obtain the number of the array items, or it
> was introduced for other means?

https://www.postgresql.org/docs/10/static/arrays.html

8.15.3. Accessing Arrays

"... cardinality returns the total number of elements in an array across 
all dimensions. It is effectively the number of rows a call to unnest 
would yield: ..."


> 
> ./danfe
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com