Thread: partitioned lookup table?
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°
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.
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°
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é
> 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.
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.
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.
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
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.