Обсуждение: foreign key from array element

Поиск
Список
Период
Сортировка

foreign key from array element

От
Rafal Pietrak
Дата:
Hello the list,

recently I'm cooking a database for an application, that I feel is best
implemented with tables containing arrays.

I have found some related info here:
http://archives.postgresql.org/pgsql-hackers/2008-09/msg01356.php

although the initial advice in that thread was: not to use arrays and:
"learning database best practice".

the thread eventually went into accepting this for implementation:
http://archives.postgresql.org/pgsql-hackers/2008-09/msg01341.php

Does anyone know if the implementation actually happened since? In which
version? How does the syntax loook like?

-R






Re: foreign key from array element

От
Craig Ringer
Дата:
On 09/17/2012 04:46 PM, Rafal Pietrak wrote:
> Hello the list,
>
> recently I'm cooking a database for an application, that I feel is best
> implemented with tables containing arrays.
>
> I have found some related info here:
> http://archives.postgresql.org/pgsql-hackers/2008-09/msg01356.php
>
> although the initial advice in that thread was: not to use arrays and:
> "learning database best practice".
>
> the thread eventually went into accepting this for implementation:
> http://archives.postgresql.org/pgsql-hackers/2008-09/msg01341.php
>
> Does anyone know if the implementation actually happened since? In which
> version? How does the syntax loook like?

There was some quite recent discussion on ELEMENT foreign keys on the
-hackers list. Try searching pgsql-hackers for ELEMENT foreign key.

See:
   https://commitfest.postgresql.org/action/patch_view?id=775
   https://commitfest.postgresql.org/action/patch_view?id=900

and the linked discussions.

--
Craig Ringer



Re: foreign key from array element

От
Rafal Pietrak
Дата:
On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote:
> On 09/17/2012 04:46 PM, Rafal Pietrak wrote:
[--------------]
> There was some quite recent discussion on ELEMENT foreign keys on the
> -hackers list. Try searching pgsql-hackers for ELEMENT foreign key.
>
> See:
>    https://commitfest.postgresql.org/action/patch_view?id=775
>    https://commitfest.postgresql.org/action/patch_view?id=900
>
Thenx.

Still, I cannot find it in the v9.2 release notes. Didn't the patch make
it to the release, or I'm just looking in wrong places
( http://www.postgresql.org/docs/9.2/static/release-9-2.html )?

-R



Re: foreign key from array element

От
Merlin Moncure
Дата:
On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote:
>> On 09/17/2012 04:46 PM, Rafal Pietrak wrote:
> [--------------]
>> There was some quite recent discussion on ELEMENT foreign keys on the
>> -hackers list. Try searching pgsql-hackers for ELEMENT foreign key.
>>
>> See:
>>    https://commitfest.postgresql.org/action/patch_view?id=775
>>    https://commitfest.postgresql.org/action/patch_view?id=900
>>
> Thenx.
>
> Still, I cannot find it in the v9.2 release notes. Didn't the patch make
> it to the release, or I'm just looking in wrong places
> ( http://www.postgresql.org/docs/9.2/static/release-9-2.html )?

It appears to be awaiting review, so the earliest possible release to
contain would be 9.3.  My entirely arbitrary (and likely incorrect!)
estimation puts at about 80% chance of making 9.3, 10% chance off some
later release, and 10% chance of rejection or getting dropped.

merlin


Re: foreign key from array element

От
Rafal Pietrak
Дата:
On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote:
> On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> > On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote:
> >> On 09/17/2012 04:46 PM, Rafal Pietrak wrote:
> > [--------------]
> >> There was some quite recent discussion on ELEMENT foreign keys on the
> >> -hackers list. Try searching pgsql-hackers for ELEMENT foreign key.
[------------]
> It appears to be awaiting review, so the earliest possible release to
> contain would be 9.3.  My entirely arbitrary (and likely incorrect!)
> estimation puts at about 80% chance of making 9.3, 10% chance off some
> later release, and 10% chance of rejection or getting dropped.

I hope it'll not end up in that 10% bin.

Who can review that patch?

-R





Re: foreign key from array element

От
"Albe Laurenz"
Дата:
Rafal Pietrak wrote:
> On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote:
> > On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> > > On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote:
> > >> On 09/17/2012 04:46 PM, Rafal Pietrak wrote:
> > > [--------------]
> > >> There was some quite recent discussion on ELEMENT foreign keys on the
> > >> -hackers list. Try searching pgsql-hackers for ELEMENT foreign key.
> [------------]
> > It appears to be awaiting review, so the earliest possible release to
> > contain would be 9.3.  My entirely arbitrary (and likely incorrect!)
> > estimation puts at about 80% chance of making 9.3, 10% chance off some
> > later release, and 10% chance of rejection or getting dropped.

> I hope it'll not end up in that 10% bin.
> 
> Who can review that patch?

You :^)

The list of patches for the commitfest is here:
https://commitfest.postgresql.org/action/commitfest_view?id=15

There is no reviewer for "Array ELEMENT Foreign Keys" yet.

Here is information about what is required:
http://wiki.postgresql.org/wiki/Reviewing_a_Patch

Remember that even a review that does not cover everything
is valuable.

Yours,
Laurenz Albe


Re: foreign key from array element

От
Chris Travers
Дата:


On Tue, Sep 18, 2012 at 12:37 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:


You :^)

The list of patches for the commitfest is here:
https://commitfest.postgresql.org/action/commitfest_view?id=15

There is no reviewer for "Array ELEMENT Foreign Keys" yet.

Silly question perhaps better saved for the review process but what does that buy us that many<->many mapping tables don't?  Are there cases where doing this with a constraint trigger is already preferable to using extra tables?

Best wishes,
Chris Travers

Re: foreign key from array element

От
Rafal Pietrak
Дата:
On Tue, 2012-09-18 at 09:37 +0200, Albe Laurenz wrote:
> Rafal Pietrak wrote:
[------------------]
> >
> > Who can review that patch?
>
> You :^)

;7

OK. (still smiling a little)

[---------------]

> Here is information about what is required:
> http://wiki.postgresql.org/wiki/Reviewing_a_Patch
>

I did have a look, and feel slightly encouraged reading: "Many people
feel that they're not qualified"; yes, that's me all right.

Serously, I will try to do by best ... by the weekend, when I get some
spare time.

> Remember that even a review that does not cover everything
> is valuable.

... and obviously this will be partial review - it's going to be my
first one. I hope it'll suffice.

And if anyone have more "freshmen" advices handy, I'll be hapy to get
them now.

-R





Re: foreign key from array element

От
Gabriele Bartolini
Дата:
Hi Rafal,

Il 18/09/12 13:00, Rafal Pietrak ha scritto:
> I did have a look, and feel slightly encouraged reading: "Many people
> feel that they're not qualified"; yes, that's me all right. Serously,
> I will try to do by best ... by the weekend, when I get some spare time.

It is such a coincidence that yesterday I had started to write this
article
(http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/)
about this feature for 9.3 and this morning I noticed your message.

It would be great if you could at least try the patch as a user and give
feedback.

Thank you.

Cheers,
Gabriele

--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it



Re: foreign key from array element

От
Rafal Pietrak
Дата:
Hi,

On Tue, 2012-09-18 at 15:12 +0200, Gabriele Bartolini wrote:
> Hi Rafal,

[------------]

>
> It is such a coincidence that yesterday I had started to write this
> article
> (http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/)
> about this feature for 9.3 and this morning I noticed your message.
>
> It would be great if you could at least try the patch as a user and give
> feedback.

OK. I tried to setup things for the weekend:

1. You say in that article, that the patch is prepared for v9.3,

2. but within the sources repository
(http://www.postgresql.org/ftp/source/), out of the most recent
releases, I can only see release v9.2.

3. so I tried to apply the patches to v9.2:
--------------------------
$ bzcat Array-ELEMENT-foreign-key-v1.patch.bz2 >array-elem-fk-init.patch
$ tar -xjf postgresql-9.2.0.tar.bz2
$ cd postgresql-9.2.0
$ patch -p1 <../array-elem-fk-init.patch
patching file doc/src/sgml/catalogs.sgml
Hunk #1 succeeded at 2036 (offset -87 lines).
Hunk #2 succeeded at 2069 (offset -87 lines).
patching file doc/src/sgml/ddl.sgml
Hunk #1 succeeded at 866 (offset -11 lines).
patching file doc/src/sgml/ref/create_table.sgml
Hunk #5 FAILED at 671.
Hunk #6 succeeded at 676 (offset -4 lines).
Hunk #7 FAILED at 693.
....
--------------------------
 neither of the two patches listed by
(https://commitfest.postgresql.org/action/commitfest_view?id=15) worked
cleanly - with the initial patch having less FAILS then the refreshed
one.

4. So I tried to locate v9.3 sources harder; I looked into the
"Developers" tab of the main website, but there is just mention of
alpla/beta ... pointing to wiki, without links to the sources.

5. where do I get current-v9.3 from?

Apparently I'll need a little help here.

-R

PS: things like location of the source-tree which is the base for the
listed patches may be obvious for the veterans, but I think, a link on
the CommitFest page would be of great help for bypassers, and the
first-timers like myself.



Re: foreign key from array element

От
"Kevin Grittner"
Дата:
Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> where do I get current-v9.3 from?

At this point 9.3 just means the HEAD of the master branch of the
git repository, which is where development for the next major
release of software is always done.  For details of the public git
repository see:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=summary

If you want to download the daily snapshot, you can find it here:

http://www.postgresql.org/ftp/snapshot/dev/

-Kevin


Re: foreign key from array element

От
Rafal Pietrak
Дата:
On Tue, 2012-09-18 at 15:38 -0500, Kevin Grittner wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> > where do I get current-v9.3 from?
>
> At this point 9.3 just means the HEAD of the master branch of the
> git repository, which is where development for the next major
> release of software is always done.  For details of the public git
> repository see:
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=summary
>

Oh. this is unexpected:

1. Got it by "git clone", the patch went-in cleanly,

2. "./configure" run just fine:
----------------
.
.
#define MEMSET_LOOP_LIMIT 1024
#define PG_VERSION_STR "PostgreSQL 9.3devel on i686-pc-linux-gnu,
compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit"
#define PG_VERSION_NUM 90300

configure: exit 0
----------------

3. But then make fails:
-----------------------
postmaster/postmaster.o: In function `PostmasterMain':^M
postmaster.c:(.text+0x4bc8): undefined reference to `optreset'^M
tcop/postgres.o: In function `process_postgres_switches':^M
postgres.c:(.text+0x1422): undefined reference to `optreset'^M
utils/misc/ps_status.o: In function `set_ps_display':^M
ps_status.c:(.text+0xd4): undefined reference to `setproctitle'^M
collect2: ld returned 1 exit status^M
make[2]: *** [postgres] Error 1^M
make[2]: Leaving directory `/home/rafal/work/postgresql/src/backend'^M
make[1]: *** [all-backend-recurse] Error 2^M
make[1]: Leaving directory `/home/rafal/work/postgresql/src'^M
make: *** [all-src-recurse] Error 2^M
-----------------------

which is interesting, because:

-----------------------
configure:22311: checking for optreset
configure:22337: gcc -o conftest -O2 -Wall -Wmissing-prototypes
-Wpointer-arith -Wdeclaration-after-statement -Wendif-labels
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
-fwrapv  -D_GNU_SOURCE    conftest.c -lz -ledit -lcrypt -ldl -lm  >&5
configure:22344: $? = 0
configure:22364: result: yes
----------------------

while "setproctitle" was used despite being detected as missing:

----------------------
configure:7627: checking for library containing setproctitle
configure:7668: gcc -o conftest -O2 -Wall -Wmissing-prototypes
-Wpointer-arith -Wdeclaration-after-statement -Wendif-labels
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
-fwrapv  -D_GNU_SOURCE    conftest.c -lm  >&5
/tmp/ccQn02qg.o: In function `main':
conftest.c:(.text+0x7): undefined reference to `setproctitle'
collect2: ld returned 1 exit status
configure:7675: $? = 1
-------------------------


-R



Re: foreign key from array element

От
Tom Lane
Дата:
Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> postmaster/postmaster.o: In function `PostmasterMain':^M
> postmaster.c:(.text+0x4bc8): undefined reference to `optreset'^M
> tcop/postgres.o: In function `process_postgres_switches':^M
> postgres.c:(.text+0x1422): undefined reference to `optreset'^M
> utils/misc/ps_status.o: In function `set_ps_display':^M
> ps_status.c:(.text+0xd4): undefined reference to `setproctitle'^M
> collect2: ld returned 1 exit status^M
> make[2]: *** [postgres] Error 1^M

We've heard of this happening as a result of libedit pulling in libbsd,
which provides (broken) versions of these symbols and thus confuses
configure into thinking they're present.  The "-ledit" in your link line
suggests that you are linking to libedit, but the rest of that is just
guesswork.  I'd suggest installing readline to see if the problem goes
away.

Here's a previous report:
http://archives.postgresql.org/pgsql-general/2011-11/msg00790.php

            regards, tom lane


Re: foreign key from array element

От
Chris Travers
Дата:


On Tue, Sep 18, 2012 at 6:12 AM, Gabriele Bartolini <gabriele.bartolini@2ndquadrant.it> wrote:
Hi Rafal,

Il 18/09/12 13:00, Rafal Pietrak ha scritto:

I did have a look, and feel slightly encouraged reading: "Many people feel that they're not qualified"; yes, that's me all right. Serously, I will try to do by best ... by the weekend, when I get some spare time.

It is such a coincidence that yesterday I had started to write this article (http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/) about this feature for 9.3 and this morning I noticed your message.

It would be great if you could at least try the patch as a user and give feedback.

Now that I have had a chance to digest the blog post, I want to offer some feedback as someone who typically pushes the boundaries a bit when it comes to object-relational features in PostgreSQL (and in fact I am still looking at building an object-oriented API for the database into the LedgerSMB PostgreSQL schema, although storage will be more relational), in the question of "do we want this?"  My answer is a cautious "yes."

My answer is cautious because I think that attempts to help developers think in objects all the way down will always end in tears.  You can see my recent blog posting about nested storage gotchas (and this is a nested storage design feature btw) at http://ledgersmbdev.blogspot.com/2012/09/or-modelling-part-5-nested-data.html and I would therefore hope people aren't using this feature frequently and only saving it for the (relatively rare) cases where it is appropriate.

The two big issues are that contention issues are far easier to solve for inserts than updates, and that if there is any real separation of concerns, then objects in the database are just different than they are in the application (and in fact I think the LSP gets turned on its head in the database).  If you are trading inserts for updates, you are trading simplicity in mapping your application for additional lock contention.  It isn't always obvious at the start of a project whether or not these will ever become issues, but they may be painful to fix down the road.  Additionally select performance is not necessarily well optimized for this, and you may have issues with being able to sufficiently index your array so that it is useful in the sorts of queries you want to do. This may force joins into bad orders, and may cause bad plans down the road.

.The second point is that if we see the database as modelling information, while the application models behavior (this avoids nasty issues of mixing transactional and non-transactional workloads and separates concerns) then basic principles of object-oriented design end up having very different implications.  For example, "a square is-a rectangle" poses no LSP issues regarding information modelling but is an LSP violation regarding behavior modelling.  Every calculation you can do on a rectangle you can do on a square, but everything not everything you can do to a rectangle without making it other than a rectangle can be done to a square.  So I think that when you get into this, you will find that there are a lot of hidden gotchas in assuming a simple equivalence between application and database classes.

So those are the cautions and why I don't think a feature like this is suitable for routine usage, but truth be told a lot of the object-relational features are definitely not for routine usage and make a mess of things if people use them just because they can.  I use table inheritance and I totally understand a lot of people's hostility towards this feature.  Again, anytime you break 1NF you should probably have a really good reason.  I don't think this changes here.

However, after thinking about the feature overnight, I can see a number of use cases for it, ranging from recording something like race results (where update contention is definitionally not an issue because the record of an event aren't supposed to change) to sanity checks in materialized views, and there are probably additional uses that are not apparent yet.  So yes, I certainly think I'd like to see this make it in.

Additionally I guess I am also somewhat eager to see more people really grapple with the current rough edges in RI and object-relational modelling.  If this highlights what PostgreSQL can do, I am even more for it.  If this helps get people involved, even better.  Finally, this may prove to be a  useful springboard towards even more capable designs regarding nested storage down the road (though this has intimidating gotchas in actual use too).  For example, if this were at some point combined with an ability to look inside composite types to find foreign keys (something like FOREIGN KEY ((country_ref).country_id) REFERENCES country (id) or the like), then row store functionality might be even more useful in terms of an intermediate insert format in some cases.

So yeah, as far as the feature goes, as documented, I haven't tried it fully yet (expect to do so this weekend), but it looks useful at least in some cases.

Best Wishes,
Chris Ttravers

Re: foreign key from array element

От
Gabriele Bartolini
Дата:
 Ciao Rafal,

 You can download the refreshed version of the patch:
 http://archives.postgresql.org/message-id/1347983571.11539.14.camel@greygoo.devise-it.lan

> 5. where do I get current-v9.3 from?

 git clone git://git.postgresql.org/git/postgresql.git
 cd postgresql
 git checkout -b aefk
 bzcat Array-ELEMENT-foreign-key-v1-refreshed.patch.bz2 | patch -p1

 Then regular configure and make procedures
 (http://www.postgresql.org/docs/current/interactive/install-procedure.html)

 Useful link too: http://wiki.postgresql.org/wiki/Developer_FAQ

 Hope this helps.

 Thanks,
 Gabriele

--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it


Re: foreign key from array element

От
Rafal Pietrak
Дата:
On Tue, 2012-09-18 at 18:39 -0400, Tom Lane wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> > postmaster/postmaster.o: In function `PostmasterMain':^M
> > postmaster.c:(.text+0x4bc8): undefined reference to `optreset'^M
> > tcop/postgres.o: In function `process_postgres_switches':^M
> > postgres.c:(.text+0x1422): undefined reference to `optreset'^M
> > utils/misc/ps_status.o: In function `set_ps_display':^M
> > ps_status.c:(.text+0xd4): undefined reference to `setproctitle'^M
> > collect2: ld returned 1 exit status^M
> > make[2]: *** [postgres] Error 1^M
>
> We've heard of this happening as a result of libedit pulling in libbsd,
> which provides (broken) versions of these symbols and thus confuses
> configure into thinking they're present.  The "-ledit" in your link line
> suggests that you are linking to libedit, but the rest of that is just
> guesswork.  I'd suggest installing readline to see if the problem goes
> away.
>
> Here's a previous report:
> http://archives.postgresql.org/pgsql-general/2011-11/msg00790.php
>
>             regards, tom lane

Thenx. The readline trail worked for me too, but in a somewhat different
way (I haven't read the full thread before trying):

1. in my case: this is debian-squeeze, not ubuntu; but this should not
really matter.

2. initially, I've pulled all the build dependencies for postgresql-8.4,
as they are in debian source package. This installed libedit-dev, and
consequently my initial failure.

3. so after having this libedit/readline hint, I've purged libedit-dev,
and installed readline6-dev instead (didn't have to build it from
sources).

4. this didn't help

5. but running ./configure --with-readline did. and the build ended up
cleanly.

What puzzles me, is that in "configure.in:675", the selection of libedit
v.s. readline is commented with a timemark dated 2004 - since then
things should have changed in those libs, shouldn't they? And then, the
comment proceeds a test which is only taken on win32 architecture, but
then follows libedit preference for everybody. Was this libedit/readline
issue just the case for win32? and is it still valid?

-R



Re: foreign key from array element

От
Gabriele Bartolini
Дата:
 Hi Chris,

    thank you very much for taking the time to read the article and get
 into the features proposed with our patch.

 On Tue, 18 Sep 2012 17:17:56 -0700, Chris Travers
 <chris.travers@gmail.com> wrote:
> So those are the cautions and why I don't think a feature like this
> is suitable for routine usage, but truth be told a lot of the
> object-relational features are definitely not for routine usage and
> make a mess of things if people use them just because they can.  I
> use table inheritance and I totally understand a lot of people's
> hostility towards this feature.  Again, anytime you break 1NF you
> should probably have a really good reason.  I don't think this
> changes here.

 I agree with you that this feature won't (and probably shouldn't)
 change modelling approaches in the majority of the cases. But will bring
 new opportunities, therefore make PostgreSQL even more versatile. I
 still believe that in some cases - not just indistinctively -
 aggregation in object oriented modelling can definitely be logically
 modelled using arrays, with referential integrity guaranteed by this
 feature.

> However, after thinking about the feature overnight, I can see a
> number of use cases for it, ranging from recording something like
> race
> results (where update contention is definitionally not an issue
> because the record of an event aren't supposed to change) to sanity
> checks in materialized views, and there are probably additional uses
> that are not apparent yet.

 I totally agree with you. This is exactly what we (as a community) need
 to do now as far as this feature is concerned. We need to have a larger
 use base and from there fully understand what the community needs. For
 instance, for 9.2 we had already developed actions on update and delete
 operations - assuming generic use cases. We have preferred for now to
 take out that part and start with a simpler patch where actions are
 forbidden. Through community feedback we found a name for the feature
 that was commonly accepted (we had called them EACH FOREIGN KEYS last
 year), and came up with an easy to understand syntax (and a better
 naming). It was important not to go too far down an unexplored
 territory. :)

> So yeah, as far as the feature goes, as documented, I haven't tried
> it fully yet (expect to do so this weekend), but it looks useful at
> least in some cases.

 Thank you. That's really much appreciated.

 Cheers,
 Gabriel
--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it


Re: foreign key from array element

От
Chris Travers
Дата:


On Thu, Sep 20, 2012 at 12:18 AM, Gabriele Bartolini <Gabriele.Bartolini@2ndquadrant.it> wrote:
Hi Chris,

   thank you very much for taking the time to read the article and get into the features proposed with our patch.

You are welcome.  Also in case there is ambiguity, the feature I was describing animosity towards was table inheritance.  I have seen people advocate getting rid of the feature altogether but it is really useful for a set of problems out there.  The problem of course is that in its current form it is a bit of a dangerous feature. 


I agree with you that this feature won't (and probably shouldn't) change modelling approaches in the majority of the cases. But will bring new opportunities, therefore make PostgreSQL even more versatile. I still believe that in some cases - not just indistinctively - aggregation in object oriented modelling can definitely be logically modelled using arrays, with referential integrity guaranteed by this feature.

BTW, I don't know if you have seen the series I have been doing on Object-Relational modelling in PostgreSQL but if you haven't, http://ledgersmbdev.blogspot.com/  (right now there are 9 posts up with an epilogue coming).

I cover a lot of "dangerous" features--- composite types in columns, non-1NF designs, table inheritance.  Particularly the nested storage post might be interesting in terms of both uses and misuses of this proposed feature.
 
In fact it occurs to me that the main thing it buys is an ability to do subset constraints on the foreign key set gracefully, for example, ensuring that there are between 5 and 10 foreign keys referenced in a specific case or the like. 


However, after thinking about the feature overnight, I can see a
number of use cases for it, ranging from recording something like race
results (where update contention is definitionally not an issue
because the record of an event aren't supposed to change) to sanity
checks in materialized views, and there are probably additional uses
that are not apparent yet.

I totally agree with you. This is exactly what we (as a community) need to do now as far as this feature is concerned. We need to have a larger use base and from there fully understand what the community needs. For instance, for 9.2 we had already developed actions on update and delete operations - assuming generic use cases. We have preferred for now to take out that part and start with a simpler patch where actions are forbidden. Through community feedback we found a name for the feature that was commonly accepted (we had called them EACH FOREIGN KEYS last year), and came up with an easy to understand syntax (and a better naming). It was important not to go too far down an unexplored territory. :)

 
I think the problem for the cascade and set null operations is determining the behavior to be defined.  would ON DELETE CASCADE delete the value from the array or would it delete the whole row?  What about ON DELETE SET NULL?  Do we change the value in the array to NULL or just remove it from the array?  So I think for now that's sane.

I think in terms of community, the object-relational features do need more exposure, and more attention generally.  Part of the reason I started blogging about them was to bring more attention to them, and try to help get more exposure to the current costs and benefits of using them.  If people are pushing the boundaries a bit more, I think a lot of things will get improved upon.

Best Wishes,
Chris Travers

Re: foreign key from array element

От
Rafal Pietrak
Дата:
On Tue, 2012-09-18 at 09:37 +0200, Albe Laurenz wrote:
> Rafal Pietrak wrote:
> > On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote:
> > > On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:

[--------]

> >
> > Who can review that patch?
>
> You :^)
>

I did what I could - the review is on the hackers list.

[------------]

> http://wiki.postgresql.org/wiki/Reviewing_a_Patch
>
> Remember that even a review that does not cover everything
> is valuable.

Should I do anything else to finilize this review, like "linking" it to
the pending patch? somehow?

-R



Re: foreign key from array element

От
Gabriele Bartolini
Дата:
Hi Rafal,

Il 24/09/12 07:54, Rafal Pietrak ha scritto:
> I did what I could - the review is on the hackers list.

Thanks! That's much appreciated.

> Should I do anything else to finilize this review, like "linking" it
> to the pending patch? somehow? -R

You should update the commitfest.postgresql.org website. For now, I have
updated that for you and the patch now reports you as a reviewer and
links the message to the patch. Here it is:
https://commitfest.postgresql.org/action/patch_view?id=900

Cheers,
Gabriele

--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it