Thread: 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/ > 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
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
Hi, > 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 ... Ah, now I understand the problem :-) And I think you are right. It would be a very bad tradeoff. Sander
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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461