Re: BUG #14209: Rules don't validate duplicated keys

On Wed, Jun 22, 2016 at 6:40 PM, <juniorperezpy@gmail.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      14209
> Logged by:          Abdel Perez
> Email address:      juniorperezpy@gmail.com
> PostgreSQL version: 9.5.1
> Operating system:   Window 7 Professional
> Description:
>
> I've a list with duplicates values I use like pk and I create a rule like
> this:
>
> CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
>     ON INSERT TO products
>    WHERE (EXISTS ( SELECT 1
>            FROM products
>           WHERE products.id =3D new.id AND
> btrim(products.description::text) =3D
> btrim(new.description::text))) DO INSTEAD NOTHING;
>
> when I insert individually the rule works and its says me he ignores the
> duplicate row, but when insert this way:
>
> insert into products (id, description) values (1, 'product1'), (1,
> 'producto1);
>
> he inserts anyway the two rows.
>
> I didn't see in the documentation that the rule must ignore this cases.
>
>
=E2=80=8BMaybe someone more knowledgeable will jump in with specifics (or p=
ropose a
-doc patch with them incorporated) since its not self-evident how a
multi-value insert resolves in this setup.  Not that rules get much lovin'
now-a-days; you should consider (even if this did work) alternatives.

Before the rule is evaluated there are zero records with id=3D1; Only after
the rule (and any other) is fully resolved are any of the output =E2=80=8Bq=
uery
trees executed.  Thus the WHERE EXISTS does not evaluate any data that has
been inserted via the query it is rewriting.  Therefore DO INSTEAD NOTHING
either applies to both records (if id=3D1 exists previously) or neither.

Whether the generated query tree is a single tree that gets fed two rows
during execution, or a pair of trees each being fed a single row during
execution, I do not know and could not discover from a quick scan of the
documentation.  Either way a violation of the UNIQUE constraint on the id
column is going to happen.

David J.