Re: FKs on temp tables: hard, or just omitted?

Folks,

Are foreign keys on temp tables not allowed just because nobody requested
them, or because they're hard to do?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Are foreign keys on temp tables not allowed just because nobody requested
> them, or because they're hard to do?

You can have foreign keys between temp tables, just not between temp and
permanent tables. The latter case is either fairly silly, or
technically hard, depending on which direction you have in mind.

regards, tom lane

> You can have foreign keys between temp tables, just not between temp and
> permanent tables. The latter case is either fairly silly, or
> technically hard, depending on which direction you have in mind.

A temp table referencing a permanent table wouldn't be very silly IMHO...
Sander.

On Sat, Oct 29, 2005 at 02:54:31PM +0200, Sander Steffann wrote:
> >You can have foreign keys between temp tables, just not between temp and
> >permanent tables. The latter case is either fairly silly, or
> >technically hard, depending on which direction you have in mind.
>
> A temp table referencing a permanent table wouldn't be very silly IMHO...
> Sander.

Ok, say someone in another backend deletes a row from the parmanent
table, how do they check that no appropriate rows exist in the temp
table? A foreign keys involves triggers on both the source and target
tables.

You solve it by allowing other backends to lock and examine your
temporary tables. But AIUI temporary tables are not stored in shared
memory so how do you get a consistant view of it?

Not unsolvable, but very tricky.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> You solve it by allowing other backends to lock and examine your
> temporary tables. But AIUI temporary tables are not stored in shared
> memory so how do you get a consistant view of it?

> Not unsolvable, but very tricky.

Right, the problem isn't that "it can't be done", it's that "it can't be
done without giving up most of the performance advantages of temp tables".
Which seems like a bad tradeoff, at least to me ...

regards, tom lane

Folks,

Thanks, all! Now, if only I could remember who asked me the question ...

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Sun, Oct 30, 2005 at 05:31:07PM -0800, Josh Berkus wrote:
> Folks,
>
> Thanks, all! Now, if only I could remember who asked me the question ...

ISTM we should add a note about this to the docs...

Here's a patch for create_table.sgml, though there's probably some other
places this could go...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

I have applied a more limited patch that mentions this. I do not want
to mention _why_ we do not implement it because it is partly performance
and partly complexity, I think, and some combinations make no sense,
like temporary primary and non-temp foreign.

---------------------------------------------------------------------------

Jim C. Nasby wrote:
> On Sun, Oct 30, 2005 at 05:31:07PM -0800, Josh Berkus wrote:
> > Folks,
> >
> > Thanks, all! Now, if only I could remember who asked me the question ...
>
> ISTM we should add a note about this to the docs...
>
> Here's a patch for create_table.sgml, though there's probably some other
> places this could go...
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073