Thread: partitioned lookup table?

partitioned lookup table?

From
Andreas Kretschmer
Date:
Hi @all,

A question, found in the german PG-Forum:

is it possible to partitionate a lookup-table? What i mean is:

test=# create table foo(i int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
test=*# create table bla ( i int references foo);
CREATE TABLE
test=*# insert into foo values (1);
INSERT 0 1
test=*# insert into bla values (1);
INSERT 0 1
test=*# create table foo_2 () inherits (foo);
CREATE TABLE
test=*# alter table foo_2 add primary key (i);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_2_pkey" for table "foo_2"
ALTER TABLE


Well, now i'd like to move data from foo into foo_2:

test=*# insert into foo_2 select * from foo;
INSERT 0 1
test=*# delete from foo;
ERROR:  update or delete on table "foo" violates foreign key constraint "bla_i_fkey" on table "bla"
DETAIL:  Key (i)=(1) is still referenced from table "bla".

Can i do that in an other way, or is it impossible? I think, it's
impossible, right?



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: partitioned lookup table?

From
Vick Khera
Date:
On Fri, Apr 16, 2010 at 10:48 AM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> is it possible to partitionate a lookup-table? What i mean is:
>
> test=# create table foo(i int primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> CREATE TABLE
> test=*# create table bla ( i int references foo);

You can have FK references from a partitioned table, but not to the
table.  That is, you cannot have this FK reference since you are
partitioning foo.  You can mimic the FK tests by writing your own
triggers that have knowledge of how you split the table, but they will
not be as efficient as a built-in FK test.

Re: partitioned lookup table?

From
Andreas Kretschmer
Date:
Vick Khera <vivek@khera.org> wrote:

> On Fri, Apr 16, 2010 at 10:48 AM, Andreas Kretschmer
> <akretschmer@spamfence.net> wrote:
> > is it possible to partitionate a lookup-table? What i mean is:
> >
> > test=# create table foo(i int primary key);
> > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> > CREATE TABLE
> > test=*# create table bla ( i int references foo);
>
> You can have FK references from a partitioned table, but not to the
> table.  That is, you cannot have this FK reference since you are
> partitioning foo.  You can mimic the FK tests by writing your own
> triggers that have knowledge of how you split the table, but they will
> not be as efficient as a built-in FK test.

Thx for the answer, and yes, this is what i had in mind.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: partitioned lookup table?

From
Martin Gainty
Date:
Andreas-

I have marched to the Oracle drumbeat of partitioning table instead of using FK constraint for years
Are there timings which show FK constraint a better alternative than partitioning?

Martin Gainty
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

 
Ez az üzenet bizalmas.  Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett.  Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs.  Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt.

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.




 
> Date: Sat, 17 Apr 2010 11:39:28 +0200
> From: akretschmer@spamfence.net
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] partitioned lookup table?
>
> Vick Khera <vivek@khera.org> wrote:
>
> > On Fri, Apr 16, 2010 at 10:48 AM, Andreas Kretschmer
> > <akretschmer@spamfence.net> wrote:
> > > is it possible to partitionate a lookup-table? What i mean is:
> > >
> > > test=# create table foo(i int primary key);
> > > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> > > CREATE TABLE
> > > test=*# create table bla ( i int references foo);
> >
> > You can have FK references from a partitioned table, but not to the
> > table. That is, you cannot have this FK reference since you are
> > partitioning foo. You can mimic the FK tests by writing your own
> > triggers that have knowledge of how you split the table, but they will
> > not be as efficient as a built-in FK test.
>
> Thx for the answer, and yes, this is what i had in mind.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail. Get busy.

Re: partitioned lookup table?

From
David Fetter
Date:
On Fri, Apr 16, 2010 at 04:48:18PM +0200, Andreas Kretschmer wrote:
> Hi @all,
>
> A question, found in the german PG-Forum:
>
> is it possible to partitionate a lookup-table? What i mean is:
>
> test=# create table foo(i int primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> CREATE TABLE
> test=*# create table bla ( i int references foo);
> CREATE TABLE
> test=*# insert into foo values (1);
> INSERT 0 1
> test=*# insert into bla values (1);
> INSERT 0 1
> test=*# create table foo_2 () inherits (foo);
> CREATE TABLE
> test=*# alter table foo_2 add primary key (i);
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_2_pkey" for table "foo_2"
> ALTER TABLE

Kinda.

http://people.planetpostgresql.org/dfetter/index.php?/archives/51-Partitioning-Is-Such-Sweet-Sorrow.html

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: partitioned lookup table?

From
Vick Khera
Date:
On Sat, Apr 17, 2010 at 9:16 AM, Martin Gainty <mgainty@hotmail.com> wrote:
> Andreas-
>
> I have marched to the Oracle drumbeat of partitioning table instead of using
> FK constraint for years
> Are there timings which show FK constraint a better alternative than
> partitioning?

How are the functions these two facilities provide similar an any way?
 THey solve entirely different problems.