unique amount more than one table

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

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 <pedzsan@gmail.com> 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
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...)

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