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@agliodbs.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@svana.org> http://svana.org/kleptog/
Show quoted text
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.
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org on behalf of Sander Steffann
Sent: Sat 10/29/2005 1:54 PM
To: josh@agliodbs.com; Tom Lane
Cc: PostgreSQL-development
Subject: Re: [HACKERS] FKs on temp tables: hard, or just omitted?
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...
Err, no, not silly, but difficult. But the other direction would be silly which is what I think Tom meant.
Regards, Dave
Import Notes
Resolved by subject fallback
Martijn van Oosterhout <kleptog@svana.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
Import Notes
Reply to msg id not found: 002001c5dd3d928d3b4064c8a8c0@balefirehome
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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Attachments:
temp_ri.patchtext/plain; charset=us-asciiDownload
Index: doc/src/sgml/ref/create_table.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v
retrieving revision 1.94
diff -u -r1.94 create_table.sgml
--- doc/src/sgml/ref/create_table.sgml 13 Aug 2005 02:48:18 -0000 1.94
+++ doc/src/sgml/ref/create_table.sgml 31 Oct 2005 17:54:10 -0000
@@ -421,7 +421,10 @@
primary key of the <replaceable
class="parameter">reftable</replaceable> is used. The
referenced columns must be the columns of a unique or primary
- key constraint in the referenced table.
+ key constraint in the referenced table. Note that foreign key
+ constraints may not be defined between temporary tables and permanent
+ tables. This is because doing so would eliminate most of the performance
+ gains of using a temporary table.
</para>
<para>
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@pervasive.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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Attachments:
/rtmp/difftext/plainDownload
Index: doc/src/sgml/ref/create_table.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v
retrieving revision 1.94
diff -c -c -r1.94 create_table.sgml
*** doc/src/sgml/ref/create_table.sgml 13 Aug 2005 02:48:18 -0000 1.94
--- doc/src/sgml/ref/create_table.sgml 31 Oct 2005 18:11:27 -0000
***************
*** 421,427 ****
primary key of the <replaceable
class="parameter">reftable</replaceable> is used. The
referenced columns must be the columns of a unique or primary
! key constraint in the referenced table.
</para>
<para>
--- 421,429 ----
primary key of the <replaceable
class="parameter">reftable</replaceable> is used. The
referenced columns must be the columns of a unique or primary
! key constraint in the referenced table. Note that foreign key
! constraints may not be defined between temporary tables and
! permanent tables.
</para>
<para>