[DOCS] faq 4.20: pl/pgsql temporary tables create/drop
The PostgreSQL FAQ currently suggests using dynamic SQL as a workaround for the table OID caching problem of temp tables in pg/pgsql functions. While this is ok, it fails to suggest that besides the initial create/drop statements, every statement that touches the table must also be dynamic. With 8.0 comes pl/pgsql exception handlers...in the beginning of function execution one might do the following: begin begin delete from temp_table; -- temp table exception when others then perform create temp temp_table [...] end; As long as the table structure does not change between function executions, this can be a more elegant approach to dealing with this problem. Pre 8.0, I would have suggested to initialize all temporary tables in a special function, but this still requires special handling code when the connection gets broken, etc. I think it would be helpful to erstwhile pl/pgsql developers to list this alternative method here. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [DOCS] faq 4.20: pl/pgsql temporary tables create/drop
Merlin Moncure wrote: > The PostgreSQL FAQ currently suggests using dynamic SQL as a workaround > for the table OID caching problem of temp tables in pg/pgsql functions. > While this is ok, it fails to suggest that besides the initial > create/drop statements, every statement that touches the table must also > be dynamic. > > With 8.0 comes pl/pgsql exception handlers...in the beginning of > function execution one might do the following: > begin > begin > delete from temp_table; -- temp table > exception > when others then > perform create temp temp_table [...] > end; > As long as the table structure does not change between function > executions, this can be a more elegant approach to dealing with this > problem. Pre 8.0, I would have suggested to initialize all temporary > tables in a special function, but this still requires special handling > code when the connection gets broken, etc. I think it would be helpful > to erstwhile pl/pgsql developers to list this alternative method here. Uh, the FAQ reads: 4.20) Why can't I reliably create/drop temporary tables in PL/PgSQL functions? PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time. What should be changed? I see it saying "function accesses a temporary table". The word "access" suggests all access, not just create/drop. -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [DOCS] faq 4.20: pl/pgsql temporary tables create/drop
Bruce Momijan wrote: > Merlin Moncure wrote: > > The PostgreSQL FAQ currently suggests using dynamic SQL as a workaround > > for the table OID caching problem of temp tables in pg/pgsql functions. > > While this is ok, it fails to suggest that besides the initial > > create/drop statements, every statement that touches the table must also > > be dynamic. > Uh, the FAQ reads: > > 4.20) Why can't I reliably create/drop > temporary tables in PL/PgSQL functions? > > PL/PgSQL caches function scripts, and an unfortunate side effect > is that if a PL/PgSQL function accesses a temporary table, and that > table is later dropped and recreated, and the function called again, > the function will fail because the cached function contents still > point to the old temporary table. The solution is to use > EXECUTE for temporary table access in PL/PgSQL. This > will cause the query to be reparsed every time. > > What should be changed? I see it saying "function accesses a temporary > table". The word "access" suggests all access, not just create/drop. You are 100% correct. But something still doesn't feel right. Namely, the answer answers the question, "why can't I reliably access temporary tables in pg/pgsql functions?" Note that a temporary table not created in a pg/pgsql function will still have this behavior. So, really, it is the question that is misleading, not the answer. One possible re-phrasing would be: "Why do temporary tables in PL/PgSQL functions give me "missing oid" errors?" I think this matches the existing answer much better. Given further consideration, my previous suggestions regarding using exception handing to manage temporary table construction, etc. would be more appropriate in the proper documentation than in a FAQ. I will say that for most cases of usage of temporary tables for storage from within pg/pgsql functions, using dynamic sql is probably not the optimial solution unless dynamic sql is more generally preferred. Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] faq 4.20: pl/pgsql temporary tables create/drop
# [EMAIL PROTECTED] / 2005-02-04 12:30:00 -0500: > would be more appropriate in the proper documentation than in a FAQ. Every FAQ is an excuse for proper documentation. :) -- FreeBSD 4.10-STABLE 8:56PM up 13 hrs, 8 users, load averages: 0.00, 0.00, 0.00 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Instructions for Linux ipc config
Attached are my first thoughts for the amended instructions. Mark Kirkwood wrote: But to be on the safe side, it would make sense to do something similar to the BSD section, and comment about older distributions maybe needing to manipulate /proc/kernel/* directly. --- runtime.sgml.orig Fri Feb 4 22:17:45 2005 +++ runtime.sgmlFri Feb 4 22:17:57 2005 @@ -4414,37 +4414,29 @@ LinuxIPC configuration -The default shared memory limit (both -SHMMAX and SHMALL) is 32 -MB in 2.2 kernels, but it can be changed in the -proc file system (without reboot). For -example, to allow 128 MB: +The default settings are only suitable for small installations +(the default max segment size is 32 MB). However the remaining +defaults are quite generously sized, and usually do not require +changes. The max segment size can be changed via the +sysctl interface. For example, to allow 128 MB, +and explicitly set the maximum total shared memory size to 2097152 +pages (the default): -$ echo 134217728 >/proc/sys/kernel/shmall -$ echo 134217728 >/proc/sys/kernel/shmmax +$ systcl -w kernel.shmmax=134217728 +$ systcl -w kernel.shmall=2097152 -You could put these commands into a script run at boot-time. - - - -Alternatively, you can use sysctl, if -available, to control these parameters. Look for a file -called /etc/sysctl.conf and add lines -like the following to it: - -kernel.shmall = 134217728 -kernel.shmmax = 134217728 - -This file is usually processed at boot time, but -sysctl can also be called -explicitly later. +In addition these settings can be saved between reboots in +/etc/sysctl.conf. -Other parameters are sufficiently sized for any application. If -you want to see for yourself look in -/usr/src/linux/include/asm-xxx/shmparam.h -and /usr/src/linux/include/linux/sem.h. +Older distributions may not have the sysctl program, +but equivalent changes can be made by manipulating the +/proc filesystem: + +$ echo 134217728 >/proc/sys/kernel/shmmax +$ echo 2097152 >/proc/sys/kernel/shmall + ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [DOCS] faq 4.20: pl/pgsql temporary tables create/drop
Roman Neuhauser wrote: > # [EMAIL PROTECTED] / 2005-02-04 12:30:00 -0500: > > would be more appropriate in the proper documentation than in a FAQ. > > Every FAQ is an excuse for proper documentation. :) I disagree. The FAQs are really to document items where the place to a look for a solution would not be obvious. This seems like one of them. -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [DOCS] faq 4.20: pl/pgsql temporary tables create/drop
# [email protected] / 2005-02-04 17:17:47 -0500: > Roman Neuhauser wrote: > > # [EMAIL PROTECTED] / 2005-02-04 12:30:00 -0500: > > > would be more appropriate in the proper documentation than in a FAQ. > > > > Every FAQ is an excuse for proper documentation. :) > > I disagree. The FAQs are really to document items where the place to a > look for a solution would not be obvious. This seems like one of them. For questions with non-obviously placed answers, the FAQ should look like: Q: How do I ... A: See The Manual section X.Y.Z As soon as you put in anything else than just pointers at the documentation, things go awry, my favorite example is the FreeBSD Handbook chapter on disks, and the FreeBSD FAQ entry "How do I move my installation to my new disk". Frequently Asked Questions doesn't mean Unstructured Bits Of Info. That's just my .02. -- FreeBSD 4.10-STABLE 11:43PM up 15:47, 8 users, load averages: 0.08, 0.06, 0.01 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] faq 4.20: pl/pgsql temporary tables create/drop
>Roman Neuhauser > # [EMAIL PROTECTED] / 2005-02-04 12:30:00 -0500: > > would be more appropriate in the proper documentation than in a FAQ. > > Every FAQ is an excuse for proper documentation. :) > Your contributions are always welcome to any form of documentation. Proper is in the eye of the beholder; if you want it better, go for it. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
