pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan arrays reference primary keys in CREATE TABLE?
9+ messages / 6 participants
[nested] [flat]
* Can arrays reference primary keys in CREATE TABLE?
@ 2003-05-09 21:21 Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Filipe Bonjour @ 2003-05-09 21:21 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm a rather new SQL user, and I found interesting that you can
use "references" to make sure that a value inserted in a table is a
primary key from another table. Something like:
create table ta (
a_id integer primary key
);
create table tb (
a_ref integer references ta(a_id)
);
However, I'd like to define a table containing an array, and that
all elements of the array be a primary key from another table, so I
tried:
create table tb (
a_ref integer[] references ta(a_id)
);
But that doesn't work, I think because an array of integers can't
be directly compared to an integer.
Is there a way to do this? (I'm using Red Hat 9 with PostgreSQL 7.3.2,
by the way.)
Thanks in advance for any answer or for pointing our where I can find
it.
Fil
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
@ 2003-05-14 15:31 ` Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
0 siblings, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2003-05-14 15:31 UTC (permalink / raw)
To: Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote:
> Hi,
>
> I'm a rather new SQL user, and I found interesting that you can
> use "references" to make sure that a value inserted in a table is a
> primary key from another table. Something like:
Yep - referential integrity is vital.
> However, I'd like to define a table containing an array, and that
> all elements of the array be a primary key from another table, so I
> tried:
>
> create table tb (
> a_ref integer[] references ta(a_id)
> );
>
> But that doesn't work, I think because an array of integers can't
> be directly compared to an integer.
Don't use arrays, use another table, e.g.
CREATE TABLE tb_items (
tb_ref integer REFERENCE tb,
ta_ref integer REFERENCES ta,
PRIMARY KEY (tb_ref,ta_ref)
);
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
@ 2003-05-14 16:46 ` Luis Sousa <llsousa@ualg.pt>
2003-05-19 17:40 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Luis Sousa @ 2003-05-14 16:46 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
Richard Huxton wrote:
>On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote:
>
>
>>Hi,
>>
>>I'm a rather new SQL user, and I found interesting that you can
>>use "references" to make sure that a value inserted in a table is a
>>primary key from another table. Something like:
>>
>>
>
>Yep - referential integrity is vital.
>
I used a trigger to get referential integrity.
So, on update or insert, it fires the function that's going to read the
new array and check if really exists on the other table
>
>
>
>>However, I'd like to define a table containing an array, and that
>>all elements of the array be a primary key from another table, so I
>>tried:
>>
>>create table tb (
>> a_ref integer[] references ta(a_id)
>> );
>>
>>But that doesn't work, I think because an array of integers can't
>>be directly compared to an integer.
>>
>>
>
>Don't use arrays, use another table, e.g.
>
>CREATE TABLE tb_items (
> tb_ref integer REFERENCE tb,
> ta_ref integer REFERENCES ta,
> PRIMARY KEY (tb_ref,ta_ref)
>);
>
>
>
--
Luis Sousa
Especialista de Informática
Gabinete de Gestão de Informação, ext: 7837
Campus de Gambelas
Universidade do Algarve, tel: 289800900
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
@ 2003-05-19 17:40 ` Jan Wieck <JanWieck@Yahoo.com>
2003-05-22 09:10 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
0 siblings, 1 reply; 9+ messages in thread
From: Jan Wieck @ 2003-05-19 17:40 UTC (permalink / raw)
To: Luis Sousa <llsousa@ualg.pt>; +Cc: Richard Huxton <dev@archonet.com>; Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
Luis Sousa wrote:
> Richard Huxton wrote:
>
>> On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote:
>>
>>
>>> Hi,
>>>
>>> I'm a rather new SQL user, and I found interesting that you can
>>> use "references" to make sure that a value inserted in a table is a
>>> primary key from another table. Something like:
>>>
>>
>>
>> Yep - referential integrity is vital.
>>
> I used a trigger to get referential integrity.
> So, on update or insert, it fires the function that's going to read the
> new array and check if really exists on the other table
And what happens on UPDATE or DELETE to the primary key table?
You used a trigger to check something, but unless you have a full set of
said triggers and they do the locking as well, you do not have
referential integrity.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-19 17:40 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
@ 2003-05-22 09:10 ` Luis Sousa <llsousa@ualg.pt>
2003-05-22 10:56 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Luis Sousa @ 2003-05-22 09:10 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Richard Huxton <dev@archonet.com>; Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
Jan Wieck wrote:
> Luis Sousa wrote:
>
>> Richard Huxton wrote:
>>
>>> On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote:
>>>
>>>
>>>> Hi,
>>>>
>>>> I'm a rather new SQL user, and I found interesting that you can
>>>> use "references" to make sure that a value inserted in a table is a
>>>> primary key from another table. Something like:
>>>
>>>
>>>
>>> Yep - referential integrity is vital.
>>>
>> I used a trigger to get referential integrity.
>> So, on update or insert, it fires the function that's going to read
>> the new array and check if really exists on the other table
>
>
> And what happens on UPDATE or DELETE to the primary key table?
>
> You used a trigger to check something, but unless you have a full set of
> said triggers and they do the locking as well, you do not have
> referential integrity.
>
>
> Jan
>
>
I guess you are right.
I'll have to write a trigger for UPDATE or DELETE. How can I do the lock
only on that record ?
Luis Sousa
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-19 17:40 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
2003-05-22 09:10 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
@ 2003-05-22 10:56 ` Jan Wieck <JanWieck@Yahoo.com>
2003-05-23 06:55 ` Re: Can arrays reference primary keys in CREATE TABLE? Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 9+ messages in thread
From: Jan Wieck @ 2003-05-22 10:56 UTC (permalink / raw)
To: Luis Sousa <llsousa@ualg.pt>; +Cc: Richard Huxton <dev@archonet.com>; Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
Luis Sousa wrote:
> Jan Wieck wrote:
>> You used a trigger to check something, but unless you have a full set of
>> said triggers and they do the locking as well, you do not have
>> referential integrity.
>>
>>
>> Jan
>>
>>
> I guess you are right.
> I'll have to write a trigger for UPDATE or DELETE. How can I do the lock
> only on that record ?
That exactly is the problem with arrays here. You can't index an array
in a way so that you can effectively select all rows who's array
contains a particular value. Your trigger can only select all of them
and loop through the arrays - but it has to select all of them for
update (resulting effectively in a full table lock, bye bye concurrency)
because otherwise it'll see an older snapshot ignoring concurrent
transactions.
What you can do, if you don't want or can't give up the array in the
first place, set up another table that holds the primary key and the
arrays single values in rows. This table is managed by INSERT, UPDATE
and DELETE triggers of the table with the array. Now put the foreign key
constraint on that table instead and you have the desired behaviour with
real referential integrity.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-19 17:40 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
2003-05-22 09:10 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-22 10:56 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
@ 2003-05-23 06:55 ` Oleg Bartunov <oleg@sai.msu.su>
2003-05-23 15:17 ` Re: Can arrays reference primary keys in CREATE TABLE? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 9+ messages in thread
From: Oleg Bartunov @ 2003-05-23 06:55 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Luis Sousa <llsousa@ualg.pt>; Richard Huxton <dev@archonet.com>; Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
On Thu, 22 May 2003, Jan Wieck wrote:
> Luis Sousa wrote:
> > Jan Wieck wrote:
> >> You used a trigger to check something, but unless you have a full set of
> >> said triggers and they do the locking as well, you do not have
> >> referential integrity.
> >>
> >>
> >> Jan
> >>
> >>
> > I guess you are right.
> > I'll have to write a trigger for UPDATE or DELETE. How can I do the lock
> > only on that record ?
>
> That exactly is the problem with arrays here. You can't index an array
> in a way so that you can effectively select all rows who's array
> contains a particular value. Your trigger can only select all of them
there is contrib/intarray which could be used for indexed access to
array of integers. In principle, it should be not difficult to
generalize this module to support many types.
>
> Jan
>
>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-19 17:40 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
2003-05-22 09:10 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-22 10:56 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
2003-05-23 06:55 ` Re: Can arrays reference primary keys in CREATE TABLE? Oleg Bartunov <oleg@sai.msu.su>
@ 2003-05-23 15:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-23 18:09 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2003-05-23 15:17 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Luis Sousa <llsousa@ualg.pt>; Richard Huxton <dev@archonet.com>; Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
On Fri, 23 May 2003, Oleg Bartunov wrote:
> On Thu, 22 May 2003, Jan Wieck wrote:
>
> > Luis Sousa wrote:
> > > Jan Wieck wrote:
> > >> You used a trigger to check something, but unless you have a full set of
> > >> said triggers and they do the locking as well, you do not have
> > >> referential integrity.
> > >>
> > >>
> > >> Jan
> > >>
> > >>
> > > I guess you are right.
> > > I'll have to write a trigger for UPDATE or DELETE. How can I do the lock
> > > only on that record ?
> >
> > That exactly is the problem with arrays here. You can't index an array
> > in a way so that you can effectively select all rows who's array
> > contains a particular value. Your trigger can only select all of them
>
> there is contrib/intarray which could be used for indexed access to
> array of integers. In principle, it should be not difficult to
> generalize this module to support many types.
Yeah, that should get around locking all the rows. It will still mean
that deadlocks are even more likely to occur with such tables than with
the normal constraint conditions however (locking the row containing one
element we're looking for effectively locks for all of those values).
As a side note, there are some other issues with making the constraint
work for non-no action constraints. I'm not sure if SET NULL or SET
DEFAULT can be meaningfully done at all with the current array support,
nor exactly what CASCADE means for delete (does it remove the array
element or the entire row).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can arrays reference primary keys in CREATE TABLE?
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Re: Can arrays reference primary keys in CREATE TABLE? Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-19 17:40 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
2003-05-22 09:10 ` Re: Can arrays reference primary keys in CREATE TABLE? Luis Sousa <llsousa@ualg.pt>
2003-05-22 10:56 ` Re: Can arrays reference primary keys in CREATE TABLE? Jan Wieck <JanWieck@Yahoo.com>
2003-05-23 06:55 ` Re: Can arrays reference primary keys in CREATE TABLE? Oleg Bartunov <oleg@sai.msu.su>
2003-05-23 15:17 ` Re: Can arrays reference primary keys in CREATE TABLE? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-05-23 18:09 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jan Wieck @ 2003-05-23 18:09 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Oleg Bartunov <oleg@sai.msu.su>; Luis Sousa <llsousa@ualg.pt>; Richard Huxton <dev@archonet.com>; Filipe Bonjour <fbonjour@urbanet.ch>; pgsql-sql
Stephan Szabo wrote:
> On Fri, 23 May 2003, Oleg Bartunov wrote:
>>On Thu, 22 May 2003, Jan Wieck wrote:
>>>That exactly is the problem with arrays here. You can't index an array
>>>in a way so that you can effectively select all rows who's array
>>>contains a particular value. Your trigger can only select all of them
>>
>>there is contrib/intarray which could be used for indexed access to
>>array of integers. In principle, it should be not difficult to
>>generalize this module to support many types.
>
>
> Yeah, that should get around locking all the rows. It will still mean
> that deadlocks are even more likely to occur with such tables than with
> the normal constraint conditions however (locking the row containing one
> element we're looking for effectively locks for all of those values).
>
> As a side note, there are some other issues with making the constraint
> work for non-no action constraints. I'm not sure if SET NULL or SET
> DEFAULT can be meaningfully done at all with the current array support,
> nor exactly what CASCADE means for delete (does it remove the array
> element or the entire row).
Foreign keys have probably not been designed with a multi-dimensional
design in mind. Now we're talking about an array referencing, what about
arrays of arrays?
While some cases sure can be implemented or simulated, I share Stepahn's
concerns about the meaning of referential actions like ON DELETE CASCADE.
And even if we can solve this one today, tomorrow someone will ask for
an implementation where every even indexed element of the array
references table A and every odd one references table B (except it is a
multiple of 7 because those are used as flags with some totally
different meaning), and of course ON DELETE CASCADE means "remove the
element from the array" so that all following ones flip their
referencing table ... and the multiples of 7's have to leapfrog :-)
If Oracle implements it first, I might change my mind.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-05-23 18:09 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-09 21:21 Can arrays reference primary keys in CREATE TABLE? Filipe Bonjour <fbonjour@urbanet.ch>
2003-05-14 15:31 ` Richard Huxton <dev@archonet.com>
2003-05-14 16:46 ` Luis Sousa <llsousa@ualg.pt>
2003-05-19 17:40 ` Jan Wieck <JanWieck@Yahoo.com>
2003-05-22 09:10 ` Luis Sousa <llsousa@ualg.pt>
2003-05-22 10:56 ` Jan Wieck <JanWieck@Yahoo.com>
2003-05-23 06:55 ` Oleg Bartunov <oleg@sai.msu.su>
2003-05-23 15:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-23 18:09 ` Jan Wieck <JanWieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox