"...integer[] references..." = error

On Thu, 5 Sep 2002, Joel Rodrigues wrote:

Hello,

Does anyone know why trying to create this column:

"role INTEGER[] REFERENCES role (roleid)"

returns this error:

ERROR: Unable to identify an operator '=' for types 'integer[]'
and 'integer'
You will have to retype this query using an explicit cast

psql:individual.sql:22: ERROR: Unable to identify an
operator '=' for types 'integer[]' and 'integer'
You will have to retype this query using an explicit cast

Because int[] and int are not comparable types (in this case meaning
that there is not an = operator between them) which is one of the
requirements (IIRC) for making a foreign key between two columns.

I could imagine that it might be reasonable to do all elements must
exist for array type -> base type, although I'd think it might get
wierd for multidimensional arrays, and without an intelligent index
on that column the reverse checks for update/delete on the pk table
would probably be rather painful.

In general the workaround is to normalize with a details table.

On Thu, 5 Sep 2002, Joel Rodrigues wrote:

Hello,

Does anyone know why trying to create this column:

"role INTEGER[] REFERENCES role (roleid)"

returns this error:

ERROR: Unable to identify an operator '=' for types 'integer[]'
and 'integer'
You will have to retype this query using an explicit cast

psql:individual.sql:22: ERROR: Unable to identify an
operator '=' for types 'integer[]' and 'integer'
You will have to retype this query using an explicit cast

Let me guess, roleid is a plain integer?

If I remove the array "[]", it works fine, but I need the array
to refer to more than one "roleid".

Sounds like it.

Well I believe there is something in contrib, intarr may be, that provides a
set of operators for integer arrays. If so I think I also saw mention on the
list that it's unstable in 7.3beta.

However, I'm thinking you've got an entirely different problem since you don't
need an equality operator defined you need something entirely different that
defined like an int[]/int equality operator but knows to check each element
against the referenced column. Best solution would seem to be a custom
function. As there obviously isn't such an equality operator already defined
won't cause problems elsewhere, however, it would probably have to be
specialised to your exact situation, although...

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

On Thu, 5 Sep 2002, Nigel J. Andrews wrote:

On Thu, 5 Sep 2002, Joel Rodrigues wrote:

Hello,

Does anyone know why trying to create this column:

"role INTEGER[] REFERENCES role (roleid)"

returns this error:

ERROR: Unable to identify an operator '=' for types 'integer[]'
and 'integer'
You will have to retype this query using an explicit cast

psql:individual.sql:22: ERROR: Unable to identify an
operator '=' for types 'integer[]' and 'integer'
You will have to retype this query using an explicit cast

Let me guess, roleid is a plain integer?

If I remove the array "[]", it works fine, but I need the array
to refer to more than one "roleid".

Sounds like it.

Well I believe there is something in contrib, intarr may be, that provides a
set of operators for integer arrays. If so I think I also saw mention on the
list that it's unstable in 7.3beta.

contrib/intarray is your friend.
it's broken (in 7.3 beta1) due to some changes in main source tree.
We hope we'll submit a fix next week. But 7.2.X are solid in used for
long time in many project.

However, I'm thinking you've got an entirely different problem since you don't
need an equality operator defined you need something entirely different that
defined like an int[]/int equality operator but knows to check each element
against the referenced column. Best solution would seem to be a custom
function. As there obviously isn't such an equality operator already defined
won't cause problems elsewhere, however, it would probably have to be
specialised to your exact situation, although...

He needs contains operator. Something like
select message.mid from message where message.sections @ '{1,2}';

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

On Friday, September 6, 2002, at 12:08 , Oleg Bartunov wrote:

On Thu, 5 Sep 2002, Nigel J. Andrews wrote:

On Thu, 5 Sep 2002, Joel Rodrigues wrote:

Hello,

Does anyone know why trying to create this column:

"role INTEGER[] REFERENCES role (roleid)"

returns this error:

ERROR: Unable to identify an operator '=' for types 'integer[]'
and 'integer'
You will have to retype this query using an explicit cast

psql:individual.sql:22: ERROR: Unable to identify an
operator '=' for types 'integer[]' and 'integer'
You will have to retype this query using an explicit cast

Let me guess, roleid is a plain integer?

If I remove the array "[]", it works fine, but I need the array
to refer to more than one "roleid".

Sounds like it.

Well I believe there is something in contrib, intarr may be,
that provides a
set of operators for integer arrays. If so I think I also saw
mention on the
list that it's unstable in 7.3beta.

contrib/intarray is your friend.
it's broken (in 7.3 beta1) due to some changes in main source tree.
We hope we'll submit a fix next week. But 7.2.X are solid in used for
long time in many project.

However, I'm thinking you've got an entirely different problem
since you don't
need an equality operator defined you need something entirely
different that
defined like an int[]/int equality operator but knows to check
each element
against the referenced column. Best solution would seem to be a custom
function. As there obviously isn't such an equality operator
already defined
won't cause problems elsewhere, however, it would probably have to be
specialised to your exact situation, although...

He needs contains operator. Something like
select message.mid from message where message.sections @ '{1,2}';

Regards,
Oleg

Thanks for the info & hints guys. Sad really that the most
obvious construct does not work. So, though I can get away with
skipping the REFERENCES bit by using a VIEW. I'd still like to
have some sort of referential integrity checking. I'm puzzled
about how to achieve this. I thought I'd do a CHECK with a
subquery expression, but, "Currently, CHECK expressions cannot
contain subselects". And it's not even on the TODO list. Foiled
again !

A bit of searching on Google Groups reveals that at least a few
people have attempted to use "...integer[] references...". Hate
to use the "o" word again, but it is really such an obvious
construct both in it's conception and (optimistic)
implementation.

I'll do a bit more thinking now. Any hints welcome.

- Joel

On Fri, 6 Sep 2002, Joel Rodrigues wrote:

Thanks for the info & hints guys. Sad really that the most
obvious construct does not work. So, though I can get away with
skipping the REFERENCES bit by using a VIEW. I'd still like to
have some sort of referential integrity checking. I'm puzzled
about how to achieve this. I thought I'd do a CHECK with a
subquery expression, but, "Currently, CHECK expressions cannot
contain subselects". And it's not even on the TODO list. Foiled
again !

A bit of searching on Google Groups reveals that at least a few
people have attempted to use "...integer[] references...". Hate
to use the "o" word again, but it is really such an obvious
construct both in it's conception and (optimistic)
implementation.

Not really. There are some performance issues and such surrounding
it. Arrays are positional and not multisets, so you get some
wierdness at the conversion.

For example:
I have (3,4) in an array. I update it to (4). For foreign
key purposes, is this effectively a delete of 3 (ie no
check required) or a delete of 4 and a change of 3->4.
If the array has 100 elements and I remove the first one,
do I do 99 foreign key checks or do I try to determine
that's all that happened. What if the rest of the elements
were randomly assorted?

On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote:

On Fri, 6 Sep 2002, Joel Rodrigues wrote:

Thanks for the info & hints guys. Sad really that the most
obvious construct does not work. So, though I can get away with
skipping the REFERENCES bit by using a VIEW. I'd still like to
have some sort of referential integrity checking. I'm puzzled
about how to achieve this. I thought I'd do a CHECK with a
subquery expression, but, "Currently, CHECK expressions cannot
contain subselects". And it's not even on the TODO list. Foiled
again !

A bit of searching on Google Groups reveals that at least a few
people have attempted to use "...integer[] references...". Hate
to use the "o" word again, but it is really such an obvious
construct both in it's conception and (optimistic)
implementation.

Not really. There are some performance issues and such surrounding
it. Arrays are positional and not multisets, so you get some
wierdness at the conversion.

For example:
I have (3,4) in an array. I update it to (4). For foreign
key purposes, is this effectively a delete of 3 (ie no
check required) or a delete of 4 and a change of 3->4.
If the array has 100 elements and I remove the first one,
do I do 99 foreign key checks or do I try to determine
that's all that happened. What if the rest of the elements
were randomly assorted?

Hello Stephan, I understand what you're saying. I didn't see
that. Thanks. But would there be any problem with allowing
subselects within CHECK expressions ?

- Joel

On Sat, 7 Sep 2002, Joel Rodrigues wrote:

On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote:

Not really. There are some performance issues and such surrounding
it. Arrays are positional and not multisets, so you get some
wierdness at the conversion.

For example:
I have (3,4) in an array. I update it to (4). For foreign
key purposes, is this effectively a delete of 3 (ie no
check required) or a delete of 4 and a change of 3->4.
If the array has 100 elements and I remove the first one,
do I do 99 foreign key checks or do I try to determine
that's all that happened. What if the rest of the elements
were randomly assorted?

Hello Stephan, I understand what you're saying. I didn't see
that. Thanks. But would there be any problem with allowing
subselects within CHECK expressions ?

You might want to read past discussions. The big problem is
that check constraints with subselects act as constraints on
the tables mentioned in the subselects as well and we currently
have no good way to manage that automatically.

Imagine a check constraint like:

create table table1 (
a int check ((select max(col1) from table2)>a);
);

What can make that check constraint violated?
Adding a new row to table1 with a>=max(col1),
Updating table1 row to a>=max(col1),
Deleting the max row from table2 such that a>=max(col1)
Updating table2's max row such that a>=max(col1).

The first two are easy, it's the second two that are
wierd. Note that in this case inserting to table2
I don't believe can violate the constraint, but switching
the > to a < would make delete safe and insert possibly
violate.

If you don't care about the second pair of constraints
there, you can hide alot of the functionality in a
trigger or function.

Could someone tell me what the following item in the TODO list
for PostgreSQL refers to ?

(under Referential Integrity)

"Change foreign key constraint for array -> element to mean
element in array"

Is this related to what I want/need to do - make sure entries in
an array of type INTEGER[] refer to entries that actually exist
in another table.

- Joel

On Sat, 7 Sep 2002, Joel Rodrigues wrote:

Could someone tell me what the following item in the TODO list
for PostgreSQL refers to ?

(under Referential Integrity)

"Change foreign key constraint for array -> element to mean
element in array"

Is this related to what I want/need to do - make sure entries in
an array of type INTEGER[] refer to entries that actually exist
in another table.

Yep. Despite any of the difficulties, it's in the long range goals
for the foreign key stuff, but in general the locking issues and probably
inheritance are higher up on the stuff that needs to get done (the
array stuff is an extension and those two are more bug fixes).

On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote:

On Fri, 6 Sep 2002, Joel Rodrigues wrote:

Thanks for the info & hints guys. Sad really that the most
obvious construct does not work. So, though I can get away with
skipping the REFERENCES bit by using a VIEW. I'd still like to
have some sort of referential integrity checking. I'm puzzled
about how to achieve this. I thought I'd do a CHECK with a
subquery expression, but, "Currently, CHECK expressions cannot
contain subselects". And it's not even on the TODO list. Foiled
again !

A bit of searching on Google Groups reveals that at least a few
people have attempted to use "...integer[] references...". Hate
to use the "o" word again, but it is really such an obvious
construct both in it's conception and (optimistic)
implementation.

Not really. There are some performance issues and such surrounding
it. Arrays are positional and not multisets, so you get some
wierdness at the conversion.

For example:
I have (3,4) in an array. I update it to (4). For foreign
key purposes, is this effectively a delete of 3 (ie no
check required) or a delete of 4 and a change of 3->4.
If the array has 100 elements and I remove the first one,
do I do 99 foreign key checks or do I try to determine
that's all that happened. What if the rest of the elements
were randomly assorted?

Forgot to comment:

By "not really", you're referring to implementing the feature in
PostgreSQL, right ? Because what I referred to by "obvious" is
the need for this feature. And thinking about what you elaborate
on in your example, it seems to me that some restricted use of
such a construct (...integer[] references someID...) should be
included in the feature set. And yes I think the best and most
sensible solution would be to do in your example of a 100
element array, would be to do 99 foreign key checks. I don't see
what the problem could be, other than a minuscule performance
hit perhaps depending on the machine you're running on.

By the same token, CHECK could be allowed some restricted use of
subselects. But that item on the TODO list probably does deal
with this issue - I'll ask hackers about that.

Anyway, I'll mention this on pgsql-hackers, and shift to trying
to find a fudge for achieving some kind of referential
integerity/ constraint checking within a stock PostgreSQL 7.2.2
install, i.e. no contrib modules.

Cheers,
Joel

On Sun, 8 Sep 2002, Joel Rodrigues wrote:

Forgot to comment:

By "not really", you're referring to implementing the feature in
PostgreSQL, right ? Because what I referred to by "obvious" is
the need for this feature. And thinking about what you elaborate
on in your example, it seems to me that some restricted use of
such a construct (...integer[] references someID...) should be
included in the feature set. And yes I think the best and most
sensible solution would be to do in your example of a 100
element array, would be to do 99 foreign key checks. I don't see
what the problem could be, other than a minuscule performance
hit perhaps depending on the machine you're running on.

Mostly on implementation, however there are some specification
level things as yet left unsaid dealing with arrays of arrays
and multi-column keys. I'm not sure whether
{{1,2},{3,4}} -> {2,1},{3,4} (two rows in the pk table,
one in the fk) satisfies the constraint or not. As arrays
not really, but since we're already treating an array
as a multiset here, do we do that on both sides? Also,
for multi-column foreign keys say a key like ({1,2},{3,4})
is that 2 combinations or 4? If 4 then the do all the checks
become problematic as it does a number of checks equal to all of
the array lengths (well, distinct values) multiplied together if there
were multiple arrays and doing the more intelligent check (or
deciding to do it for arrays over a certain length or something) could
lower that greatly. It's likely to be done, probably not for a few
versions as for many cases there is a workaround (using a details
table) and there are bugs or at least misfeatures to kill first.

By the same token, CHECK could be allowed some restricted use of
subselects. But that item on the TODO list probably does deal
with this issue - I'll ask hackers about that.

For some subset, you can get the general effect using trigger
functions. You'd need to determine which actions on the tables
involved could violate and write the appropriate functions.