Home > mailing lists

Thread: Unique values on multiple tables

Unique values on multiple tables

From

"Sterpu Victor"

Date:

28 March 2016, 09:32:28

Hello

I have 2 tables and I must make asure unique values like this.

table1

nr - integer

table2

id_table1 - FK in Table 1

valid_from - timestamp

There must be unique values for:

- nr - from table1

and

- YEAR(MIN(valid_from)) from table 2

I already made this with a function but in rare cases the function fails: when the insert time is very close for 2 inserts the check will fail and I will have 2 numbers on the same year.

How should I fix this?

This is the function:

CREATE OR REPLACE FUNCTION table2_check_uni_func() RETURNS trigger AS
$BODY$
DECLARE
    currenr_nr_fo integer;
    current_id integer;
BEGIN
    IF( (SELECT id FROM table2 WHERE id_table1=NEW.id_table1 ORDER BY valid_from ASC LIMIT 1) = NEW.id )  THEN /*IF the first valid from is edited*/
             SELECT INTO currenr_nr_fo f.nrfo FROM table1 f WHERE f.id = NEW.id_table1;
             IF( (SELECT count(*) as nr
                  FROM table1 f
                  JOIN table2 fd1 ON (fd1.id_table1 = f.id AND to_char(fd1.valid_from, 'YYYY')=TO_CHAR(NEW.valid_from, 'YYYY'))
                  LEFT JOIN table2 fd2 ON (fd2.id_table1 = f.id AND fd2.valid_from<fd1.valid_from)
                  WHERE f.nr = currenr_nr_fo AND f.id!=NEW.id_table1 AND fd2.id IS NULL) > 0 ) THEN  RAISE EXCEPTION 'Nr % already used', currenr_nr_fo ;
             ELSE
                  RETURN NEW;
             END IF;
     ELSE
          RETURN NEW;
    END IF;
END;

Thank you.

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.

Re: Unique values on multiple tables

From

"Sterpu Victor"

Date:

28 March 2016, 09:55:58

I think I fixed the problem by executing the function AFTER insert or update but I'm not sure.

Until now the execution was before insert or update.

------ Original Message ------

From: "Sterpu Victor" <victor@caido.ro>

To: "PostgreSQL General" <pgsql-general@postgresql.org>

Sent: 28/3/2016 9:32:17 AM

Subject: [GENERAL] Unique values on multiple tables

Hello

I have 2 tables and I must make asure unique values like this.

table1
id
nr - integer

table2
id
id_table1 - FK in Table 1
valid_from - timestamp

There must be unique values for:
- nr - from table1
and
- YEAR(MIN(valid_from)) from table 2

I already made this with a function but in rare cases the function fails: when the insert time is very close for 2 inserts the check will fail and I will have 2 numbers on the same year.
How should I fix this?

This is the function:

CREATE OR REPLACE FUNCTION table2_check_uni_func() RETURNS trigger AS
$BODY$
DECLARE
    currenr_nr_fo integer;
    current_id integer;
BEGIN
    IF( (SELECT id FROM table2 WHERE id_table1=NEW.id_table1 ORDER BY valid_from ASC LIMIT 1) = NEW.id )  THEN /*IF the first valid from is edited*/
             SELECT INTO currenr_nr_fo f.nrfo FROM table1 f WHERE f.id = NEW.id_table1;
             IF( (SELECT count(*) as nr
                  FROM table1 f
                  JOIN table2 fd1 ON (fd1.id_table1 = f.id AND to_char(fd1.valid_from, 'YYYY')=TO_CHAR(NEW.valid_from, 'YYYY'))
                  LEFT JOIN table2 fd2 ON (fd2.id_table1 = f.id AND fd2.valid_from<fd1.valid_from)
                  WHERE f.nr = currenr_nr_fo AND f.id!=NEW.id_table1 AND fd2.id IS NULL) > 0 ) THEN  RAISE EXCEPTION 'Nr % already used', currenr_nr_fo ;
             ELSE
                  RETURN NEW;
             END IF;
     ELSE
          RETURN NEW;
    END IF;
END;

Thank you.

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.

Re: Unique values on multiple tables

From

Emre Hasegeli

Date:

28 March 2016, 12:06:28

> I have 2 tables and I must make asure unique values like this.
>
> table1
> id
> nr - integer
>
> table2
> id
> id_table1 - FK in Table 1
> valid_from - timestamp
>
> There must be unique values for:
> - nr - from table1
> and
> - YEAR(MIN(valid_from)) from table 2

In situations like this, I add the required column to the other table
with a foreign key.  Assuming that (id) is the primary key of table1,
you would need another unique key on (nr, id).  Than you can add nr
column to table2 by changing the foreign key to (nr, id_table1)
references table1 (nr, id).

Obviously, its not an efficient solution.  It requires an additional
unique key and more storage on the referencing table.  Though, I
believe it is a safe one.  It doesn't allow the duplicated column to
be inconsistent.  There are many things that can go wrong under
concurrency with a trigger like you posted.

Re: Unique values on multiple tables

From

"Sterpu Victor"

Date:

28 March 2016, 13:16:39

------ Original Message ------
From: "Emre Hasegeli" <emre@hasegeli.com>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 28/3/2016 12:06:23 PM
Subject: Re: [GENERAL] Unique values on multiple tables

>>  I have 2 tables and I must make asure unique values like this.
>>
>>  table1
>>  id
>>  nr - integer
>>
>>  table2
>>  id
>>  id_table1 - FK in Table 1
>>  valid_from - timestamp
>>
>>  There must be unique values for:
>>  - nr - from table1
>>  and
>>  - YEAR(MIN(valid_from)) from table 2
>
>In situations like this, I add the required column to the other table
>with a foreign key. Assuming that (id) is the primary key of table1,
>you would need another unique key on (nr, id). Than you can add nr
>column to table2 by changing the foreign key to (nr, id_table1)
>references table1 (nr, id).
>
>Obviously, its not an efficient solution. It requires an additional
>unique key and more storage on the referencing table. Though, I
>believe it is a safe one. It doesn't allow the duplicated column to
>be inconsistent. There are many things that can go wrong under
>concurrency with a trigger like you posted.

This wouldn't work in my case because I need unique values only for the
smallest valid_from.
Example:
- table1 has row id=1, nr=100
- table2 has row1 id=5, id_table1=1, valid_from=2015-12-01
                   row2 id=6, id_table1=1, valid_from=2016-01-01
Then unique values must be assured only for (100, 2015-12-01), ignorig
the second valid_from(2016-01-01)

I changed the execution time of the function from BEFORE to AFTER and I
hope this will solve the problem.
I don't know how postgres works behind this code but I hope that this
will solve the problem.
Could I use lock tables to fix this? Is postgres automaticaly locking a
table while running a trigger on that table?

Re: Unique values on multiple tables

From

Emre Hasegeli

Date:

28 March 2016, 13:45:02

> Could I use lock tables to fix this? Is postgres automaticaly locking a
> table while running a trigger on that table?

You can use LOCK TABLE. See the documentation:

http://www.postgresql.org/docs/current/static/explicit-locking.html

Re: Unique values on multiple tables

From

Jehan-Guillaume de Rorthais

Date:

28 March 2016, 16:17:51

Le Mon, 28 Mar 2016 12:44:51 +0200,
Emre Hasegeli <emre@hasegeli.com> a écrit :

> > Could I use lock tables to fix this? Is postgres automaticaly locking a
> > table while running a trigger on that table?
>
> You can use LOCK TABLE. See the documentation:
>
> http://www.postgresql.org/docs/current/static/explicit-locking.html

Use advisory locks for better performance. See the following article (at
least the two lasts chapters) for some examples:

http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

Cheers,

Re: Unique values on multiple tables

From

John Turner

Date:

29 March 2016, 02:27:22

On Mon, Mar 28, 2016 at 2:32 AM, Sterpu Victor <victor@caido.ro> wrote:

table1
id
nr - integer

table2
id
id_table1 - FK in Table 1
valid_from - timestamp

There must be unique values for:
- nr - from table1
and
- YEAR(MIN(valid_from)) from table 2

Just trying to understand your goal here -

Is 'nr' supposed to be a count of records from table2 by 'id_table1' column?

And every single value for 'nr' within table1 must be unique - i.e., you need a table of unique counts?

I think I fixed the problem by executing the function AFTER insert or update but I'm not sure.
Until now the execution was before insert or update.

That doesn't sound right, I believe you would want to stick with execution of the DML to your presumable 'fact' table (table2) using a BEFORE trigger, *especially* if it's intended to enforce constraints.

It would be helpful if you could:

clarify/elaborate on how these 2 tables are supposed to relate to each other
post your actual trigger
provide us with some example inserts/updates

- John