I have five tables each with a "name" field. Due to limitations in my user interface, I want a name to be unique amoung these five tables.

I thought I could first create a view with something like:

SELECT name, 'table1' as type from table1
UNION ALL
SELECT name, 'table2' as type from table2
UNION ALL
SELECT name, 'table3' as type from table3
...

I called this view xxx (I'm just experimenting right now).

I then created a function:

CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$
SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1;
$$ LANGUAGE SQL;

Next I added a check constraint with:

ALTER TABLE table1 ADD CHECK ( unique_xxx() );

A test shows:

select unique_xxx();
unique_xxx
------------
t
(1 row)

After I insert a row that I want to be rejected, I can do:

select unique_xxx();
unique_xxx
------------
f
(1 row)

but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road?

Thank you for your time,
pedz

Search Discussions

  • Jeff Davis at Apr 5, 2011 at 10:28 pm

    On Tue, 2011-04-05 at 17:02 -0500, Perry Smith wrote:
    CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$
    SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1;
    $$ LANGUAGE SQL;

    Next I added a check constraint with:

    ALTER TABLE table1 ADD CHECK ( unique_xxx() ); ...
    After I insert a row that I want to be rejected, I can do:

    select unique_xxx();
    unique_xxx
    ------------
    f
    (1 row)

    but the insert was not rejected. I'm guessing because the check constraint runs before the insert?
    Yes. But even if it ran afterward, there is still a potential race
    condition, because the query in the CHECK constraint doesn't see the
    results of concurrent transactions.

    To make this work, you should be using LOCK TABLE inside of a trigger
    (probably a BEFORE trigger that locks the table, then looks to see if
    the value exists in the view already, and if so, throws an exception).
    CHECK is not the right place for this kind of thing.

    Keep in mind that the performance will not be very good, however. There
    is not a good way to make this kind of constraint perform well,
    unfortunately. But that may not be a problem in your case -- try it and
    see if the performance is acceptable.

    Regards,
    Jeff Davis
  • David Johnston at Apr 5, 2011 at 10:44 pm
    You can try restricting all name insertions (on any of the tables) to go through one or more functions that serialize amongst themselves. Basically lock a common table and check the view for the new name before inserting.
    On Apr 5, 2011, at 18:02, Perry Smith wrote:

    I have five tables each with a "name" field. Due to limitations in my user interface, I want a name to be unique amoung these five tables.

    I thought I could first create a view with something like:

    SELECT name, 'table1' as type from table1
    UNION ALL
    SELECT name, 'table2' as type from table2
    UNION ALL
    SELECT name, 'table3' as type from table3
    ...

    I called this view xxx (I'm just experimenting right now).

    I then created a function:

    CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$
    SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1;
    $$ LANGUAGE SQL;

    Next I added a check constraint with:

    ALTER TABLE table1 ADD CHECK ( unique_xxx() );

    A test shows:

    select unique_xxx();
    unique_xxx
    ------------
    t
    (1 row)

    After I insert a row that I want to be rejected, I can do:

    select unique_xxx();
    unique_xxx
    ------------
    f
    (1 row)

    but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road?

    Thank you for your time,
    pedz


    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
  • Rob Sargent at Apr 5, 2011 at 10:50 pm

    On 04/05/2011 04:02 PM, Perry Smith wrote:
    I have five tables each with a "name" field. Due to limitations in my user interface, I want a name to be unique amoung these five tables.

    I thought I could first create a view with something like:

    SELECT name, 'table1' as type from table1
    UNION ALL
    SELECT name, 'table2' as type from table2
    UNION ALL
    SELECT name, 'table3' as type from table3
    ...

    I called this view xxx (I'm just experimenting right now).

    I then created a function:

    CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$
    SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1;
    $$ LANGUAGE SQL;

    Next I added a check constraint with:

    ALTER TABLE table1 ADD CHECK ( unique_xxx() );

    A test shows:

    select unique_xxx();
    unique_xxx
    ------------
    t
    (1 row)

    After I insert a row that I want to be rejected, I can do:

    select unique_xxx();
    unique_xxx
    ------------
    f
    (1 row)

    but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road?

    Thank you for your time,
    pedz
    You might try making a separate name table and having a unique index
    there and make the other users of name refer to the new table's name
    field. (I would stick on id on the new name table...)
  • Perry Smith at Apr 5, 2011 at 11:34 pm

    On Apr 5, 2011, at 5:50 PM, Rob Sargent wrote:


    On 04/05/2011 04:02 PM, Perry Smith wrote:
    I have five tables each with a "name" field. Due to limitations in my user interface, I want a name to be unique amoung these five tables.

    I thought I could first create a view with something like:

    SELECT name, 'table1' as type from table1
    UNION ALL
    <snip>
    f
    (1 row)

    but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road?

    Thank you for your time,
    pedz
    You might try making a separate name table and having a unique index there and make the other users of name refer to the new table's name field. (I would stick on id on the new name table...)
    Thanks to all.

    I think this is the way I'm going to go. I'll have an id, name, and type to tell me which of the other tables owns it. Most of the other tables don't need to exist even.

    Thank you again,
    pedz

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedApr 5, '11 at 10:02p
activeApr 5, '11 at 11:34p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase