Re: [HACKERS] Improving NOT IN
--On Dienstag, Januar 30, 2007 23:24:40 + Simon Riggs [EMAIL PROTECTED] wrote: Basically what I see here is a whole lot of work and new executor infrastructure for something that will be a win in a very narrow use-case and a significant loss the rest of the time. I think there are more productive ways to spend our development effort. Maybe one should not aim for a special case of continuous sequences. It might be a better thing to have a fast look-up datastructure for row-existence. The optimization over the usual indices is that only existence, and no other information must be saved, thus a bit-field is really possible. Even 100 Mio rows would fit in 10 MB. So, instead of trying to find a sequence, find (or guess and later correct your bitfield) the minimum, and then set the bits as you encounter rows. During the join, test whether the bit you want to join to exists and voila, depending on whether you used IN or NOT IN, decide what to do. This datastructure could be used everywhere where only existence is important and no columns of a table are selected. Possibly, the bit-field should allow for large-gaps to be represented more efficiently, if you have an 32-bit index column, make a 256 entry top-level array pointing to bitfields representing the numbers 0x0-0x00ff, 0x0100 - 0x01ff... each such bitfield would need 2MB, the pointers are negligible. But now large holes in the sequence don't waste too much space and thus the minimum needs not to be known. Regards, Jens Schicke -- Jens Schicke [EMAIL PROTECTED] asco GmbH http://www.asco.de Mittelweg 7 Tel 0531/3906-127 38106 BraunschweigFax 0531/3906-400 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Ühel kenal päeval, T, 2007-01-30 kell 14:52, kirjutas Guido Goldstein: I've checked the patch with postgres 8.1.3 and 8.2.1 with python 2.4 and 2.5 on intel 32 bit and amd 64 bit systems; all systems running linux. *And* it's not a feature patch but a bug-fixing one! Python is a language with strong typing, so silently converting a datatype is a bug -- not a feature. Python is not that strongly typed. More it is a protocol based language, meaning that you should not relay on type of any variable, but rather see if it does what you want - so any type supporting iteration can be used if for and any thing not None, 0 or empty sequence/dict is considered to be TRUE True and False are actually 1 and 0 with different spelling ;) True+2 3 1/False Traceback (most recent call last): File stdin, line 1, in ? ZeroDivisionError: integer division or modulo by zero Btw, you'll lose the type information of boolean columns in trigger functions (NEW and OLD dicts, no explicit parameters), which does cause problems. That said, we certainly try to support a few more versions of Python [...] If you want to support python 2.3 use the attached patch, which also works for the newer python versions. The Python 2.3 branch is the oldest _officially_ supported python version. Officially by who ? 2.3 was the first version to introduce bool as a subtype of int, in 2.2.3 True and False were introduced as two variables pointing to integers 1 and 0. So to make your patch ok on all python versions, just make it conditional on python version being 2.3 or bigger, and return int for pre-2.3. Anyway, to circumvent the above mentiond point a) I herewith anncounce that the included patch might break the buildfarm. :) Cheers Guido ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Missing error message on missing ssl-key-files
On Tue, Jan 30, 2007 at 11:45:24AM -0500, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: But I guess maybe the added check has to be not just (!syslogger_started) but (!syslogger_started is_postmaster)? That would at least get you out of the problem of having to transmit the syslogger_started flag to the backends... Here's a patch that does just this. //Magnus Index: src/backend/postmaster/postmaster.c === RCS file: c:/prog/cvsrepo/pgsql/pgsql/src/backend/postmaster/postmaster.c,v retrieving revision 1.516 diff -c -r1.516 postmaster.c *** src/backend/postmaster/postmaster.c 29 Jan 2007 20:17:40 - 1.516 --- src/backend/postmaster/postmaster.c 30 Jan 2007 23:10:20 - *** *** 202,209 BgWriterPID = 0, AutoVacPID = 0, PgArchPID = 0, ! PgStatPID = 0, ! SysLoggerPID = 0; /* Startup/shutdown state */ #define NoShutdown 0 --- 202,209 BgWriterPID = 0, AutoVacPID = 0, PgArchPID = 0, ! PgStatPID = 0; ! pid_t SysLoggerPID = 0; /* Needs to be accessed from elog.c */ /* Startup/shutdown state */ #define NoShutdown 0 Index: src/backend/utils/error/elog.c === RCS file: c:/prog/cvsrepo/pgsql/pgsql/src/backend/utils/error/elog.c,v retrieving revision 1.181 diff -c -r1.181 elog.c *** src/backend/utils/error/elog.c 20 Jan 2007 21:40:25 - 1.181 --- src/backend/utils/error/elog.c 30 Jan 2007 23:16:44 - *** *** 76,81 --- 76,83 sigjmp_buf *PG_exception_stack = NULL; + extern pid_t SysLoggerPID; + /* GUC parameters */ PGErrorVerbosity Log_error_verbosity = PGERROR_VERBOSE; char *Log_line_prefix = NULL; /* format for extra log line info */ *** *** 1693,1701 * anything going there and write it to the eventlog instead. * * If stderr redirection is active, it's ok to write to stderr because !* that's really a pipe to the syslogger process. */ ! if ((!Redirect_stderr || am_syslogger) pgwin32_is_service()) write_eventlog(edata-elevel, buf.data); else #endif --- 1695,1704 * anything going there and write it to the eventlog instead. * * If stderr redirection is active, it's ok to write to stderr because !* that's really a pipe to the syslogger process. Unless we're in the ! * postmaster, and the syslogger process isn't started yet. */ ! if ((!Redirect_stderr || am_syslogger || (!IsUnderPostmaster SysLoggerPID==0)) pgwin32_is_service()) write_eventlog(edata-elevel, buf.data); else #endif ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Tom Lane wrote: Are you still concerned about the PageGetFreeSpace issue? Not anymore. The failure case I had in mind was not being able to find any valid split points when a page is full of max-sized index tuples. On a closer look, that doesn't seem to be a problem. Even though checksplitloc would incorrectly consider the split (HIKEY+item)-(HIKEY+item+item) as invalid, the split (HIKEY+item+item)-(HIKEY+item) is just as good. Similarly on the rightmost page, even if (HIKEY+item)-(item+item+item) is incorrectly considered as invalid, (HIKEY+item+item)-(item+item) is fine. There also seems to always be some slack space because of alignments. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] stack usage in toast_insert_or_update()
On 1/31/07, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: Btw, I noticed that the toast_insert_or_update() is re-entrant. toast_save_datum() calls simple_heap_insert() which somewhere down the line calls toast_insert_or_update() again. The toast code takes pains to ensure that the tuples it creates won't be subject to re-toasting. Else it'd be an infinite recursion. I think I found it. The toast_insert_or_update() function gets into an unnecessary recursion because of alignment issues. It thus toasts already toasted data. This IMHO might be causing unnecessary overheads for each toast operation. The default value of TOAST_TUPLE_THRESHOLD is 2034 (assuming 8K block size) TOAST_MAX_CHUNK_SIZE is defined as below: #define TOAST_MAX_CHUNK_SIZE(TOAST_TUPLE_THRESHOLD -\ MAXALIGN( \ MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) + \ sizeof(Oid) + \ sizeof(int32) + \ VARHDRSZ)) So the default value of TOAST_MAX_CHUNK_SIZE is set to 1994. When toast_insert_or_update() returns a tuple for the first chunk, t_len is set to 2034 (TOAST_MAX_CHUNK_SIZE + tuple header + chunk_id + chunk_seqno + VARHDRSZ) In heap_insert(), we MAXALIGN(tup-t_len) before comparing it with TOAST_TUPLE_THRESHOLD to decide whether to invoke TOAST or not. In this corner case, MAXALIGN(2034) = 2036 TOAST_TUPLE_THRESHOLD and so TOAST is invoked again. Fortunately, we don't get into infinite recursion because reltoastrelid is set to InvalidOid for toast tables and hence TOASTing is not invoked in the second call. Attached is a patch which would print the recursion depth for toast_insert_or_update() before PANICing the server to help us examine the core. Let me know if this sounds like an issue and I can work out a patch. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] stack usage in toast_insert_or_update()
On 1/31/07, Pavan Deolasee [EMAIL PROTECTED] wrote: Attached is a patch which would print the recursion depth for toast_insert_or_update() before PANICing the server to help us examine the core. Here is the attachment. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com toast.patch Description: Binary data ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Modifying and solidifying contrib
David Fetter wrote: On Tue, Jan 30, 2007 at 03:49:14PM -0500, Andrew Dunstan wrote: 4. visibility/searchpath issues. I don't think long search paths are a huge issue, but I think we can make life a bit easier by tweaking searchpath support a bit (David's clever SQL notwithstanding). The only clever bit I added was the CASE statement. Credit for the rest belongs to Andrew at Supernews. It's not a bad thing for people to keep around, either way. :) I dislike on principle things that mangle the catalogs directly. As soon as I see one I think why aren't we providing an SQL command for that? By and large, I think users should be able to work as though the catalog were not visible, or at least not directly writable. 5. legacy support - we need an option to load existing extensions to the public schema as now, or support for aliases/synonyms (the latter might be good to have regardless). Hrm. This gets tricky. When things are mandated to be in their own namespace, they need not check what everybody else's things are doing each time, whereas when they go into the public schema... :P Why is it tricky? This is for legacy only, i.e. for object we know of today. Any future objects in existing extensions, or objects in new extensions, should not have this support - they should use their own namespaces, pure and simple. Richard mentioned special testing requirements, but I don't see why we can't continue to use our standard regression mechanism. A subdirectory in src/tests/regression for each one? No. One of the reasons for us to maintain some standard extensions is to act as exemplars. You should be able to build, install and test an extension without having a complete source tree present. So each extension should keep its own sql and expected directory as now, I think. I don't think it would be too much trouble to do extensions the way we now do tables and schemas in pg_dump, i.e. with multiple possible regular expression entries like --include-extension= and --exclude-extension= where the includes get evaluated before the excludes. OK, as long as --exclude-extension has an all option, or we have a --no-extensions option also. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Hannu Krosing wrote: Officially by who ? 2.3 was the first version to introduce bool as a subtype of int, in 2.2.3 True and False were introduced as two variables pointing to integers 1 and 0. So to make your patch ok on all python versions, just make it conditional on python version being 2.3 or bigger, and return int for pre-2.3. I thought about suggesting that, but do we want plpython to have different result behavior based on the version of python used? I didn't think so. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Bruce Momjian wrote: Hannu Krosing wrote: Officially by who ? 2.3 was the first version to introduce bool as a subtype of int, in 2.2.3 True and False were introduced as two variables pointing to integers 1 and 0. So to make your patch ok on all python versions, just make it conditional on python version being 2.3 or bigger, and return int for pre-2.3. I thought about suggesting that, but do we want plpython to have different result behavior based on the version of python used? I didn't think so. The alternative would be, what, including the whole python source in our distribution? Because the Python guys themselves changed the behavior depending on the version. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Bruce Momjian schrieb: Hannu Krosing wrote: Officially by who ? 2.3 was the first version to introduce bool as a subtype of int, in 2.2.3 True and False were introduced as two variables pointing to integers 1 and 0. So to make your patch ok on all python versions, just make it conditional on python version being 2.3 or bigger, and return int for pre-2.3. I thought about suggesting that, but do we want plpython to have different result behavior based on the version of python used? I didn't think so. Why not? Python2.2 is rarely in use anymore and users of this would get the same behavior. Users of python2.3 and up would get the additionally cleaned boolean interface - also users which go the from __future__ import ... way. Thats how python works and develops forth and we should not work against that from postgres side. So I'm indeed +1 for conditional approach. Regards Tino ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Tino Wildenhain wrote: Bruce Momjian schrieb: Hannu Krosing wrote: Officially by who ? 2.3 was the first version to introduce bool as a subtype of int, in 2.2.3 True and False were introduced as two variables pointing to integers 1 and 0. So to make your patch ok on all python versions, just make it conditional on python version being 2.3 or bigger, and return int for pre-2.3. I thought about suggesting that, but do we want plpython to have different result behavior based on the version of python used? I didn't think so. Why not? Python2.2 is rarely in use anymore and users of this would get the same behavior. Users of python2.3 and up would get the additionally cleaned boolean interface - also users which go the from __future__ import ... way. Thats how python works and develops forth and we should not work against that from postgres side. So I'm indeed +1 for conditional approach. Fine if people think that is OK. Please submit a patch that is conditional on the python version. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Tino Wildenhain [EMAIL PROTECTED] writes: Bruce Momjian schrieb: I thought about suggesting that, but do we want plpython to have different result behavior based on the version of python used? I didn't think so. Why not? Indeed --- the underlying language changed, so I should think that python users would *expect* different behavior. +1 on a conditional patch (see PY_VERSION_HEX...) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Modifying and solidifying contrib
On Wed, Jan 31, 2007 at 09:31:00AM -0500, Andrew Dunstan wrote: David Fetter wrote: On Tue, Jan 30, 2007 at 03:49:14PM -0500, Andrew Dunstan wrote: 4. visibility/searchpath issues. I don't think long search paths are a huge issue, but I think we can make life a bit easier by tweaking searchpath support a bit (David's clever SQL notwithstanding). The only clever bit I added was the CASE statement. Credit for the rest belongs to Andrew at Supernews. It's not a bad thing for people to keep around, either way. :) I dislike on principle things that mangle the catalogs directly. As soon as I see one I think why aren't we providing an SQL command for that? By and large, I think users should be able to work as though the catalog were not visible, or at least not directly writable. So are you proposing user-visible functions in pg_catalog like the following? append_to_search_path(role NAME, database NAME, paths NAME[]) prepend_to_search_path(role NAME, database NAME, paths NAME[]) remove_from_search_path(role NAME, database NAME, paths NAME[]) The above is how I'm picturing how this would fit in with the TODO of allowing things to be set on a per-role-and-database basis. There could be two-argument short-cuts of each of those which would do the above for the current user. 5. legacy support - we need an option to load existing extensions to the public schema as now, or support for aliases/synonyms (the latter might be good to have regardless). Hrm. This gets tricky. When things are mandated to be in their own namespace, they need not check what everybody else's things are doing each time, whereas when they go into the public schema... :P Why is it tricky? This is for legacy only, i.e. for object we know of today. Any future objects in existing extensions, or objects in new extensions, should not have this support - they should use their own namespaces, pure and simple. OK Richard mentioned special testing requirements, but I don't see why we can't continue to use our standard regression mechanism. A subdirectory in src/tests/regression for each one? No. One of the reasons for us to maintain some standard extensions is to act as exemplars. You should be able to build, install and test an extension without having a complete source tree present. So each extension should keep its own sql and expected directory as now, I think. Right :) I don't think it would be too much trouble to do extensions the way we now do tables and schemas in pg_dump, i.e. with multiple possible regular expression entries like --include-extension= and --exclude-extension= where the includes get evaluated before the excludes. OK, as long as --exclude-extension has an all option, or we have a --no-extensions option also. While we're at it, both cases should be straight-forward to do. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] stack usage in toast_insert_or_update()
Pavan Deolasee [EMAIL PROTECTED] writes: On 1/31/07, Tom Lane [EMAIL PROTECTED] wrote: The toast code takes pains to ensure that the tuples it creates won't be subject to re-toasting. Else it'd be an infinite recursion. I think I found it. The toast_insert_or_update() function gets into an unnecessary recursion because of alignment issues. It thus toasts already toasted data. This IMHO might be causing unnecessary overheads for each toast operation. Interesting --- I'd never seen this because both of my usual development machines have MAXALIGN 8, and it works out that that makes TOAST_MAX_CHUNK_SIZE 1986, which makes the actual toasted tuple size 2030, which maxaligns to 2032, which is still less than TOAST_TUPLE_THRESHOLD. I think the coding was implicitly assuming that TOAST_TUPLE_THRESHOLD would itself be a maxalign'd value, but it's not necessarily (and in fact not, with the current page header size --- I wonder whether the bug was originally masked because the page header size was different??) We can't change TOAST_MAX_CHUNK_SIZE without forcing an initdb, but I think that it would be safe to remove the MAXALIGN'ing of the tuple size in the tests in heapam.c, that is if (HeapTupleHasExternal(tup) || (MAXALIGN(tup-t_len) TOAST_TUPLE_THRESHOLD)) heaptup = toast_insert_or_update(relation, tup, NULL); else heaptup = tup; becomes if (HeapTupleHasExternal(tup) || (tup-t_len TOAST_TUPLE_THRESHOLD)) heaptup = toast_insert_or_update(relation, tup, NULL); else heaptup = tup; which'll save a cycle or two as well as avoid this corner case. It seems like a number of the uses of MAXALIGN in tuptoaster.c are useless/bogus as well. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] fixing Makefile.shlib for solaris/gcc with -m64 flag
Peter Eisentraut [EMAIL PROTECTED] writes: Am Mittwoch, 17. Januar 2007 17:12 schrieb Tom Lane: Jignesh K. Shah [EMAIL PROTECTED] writes: simple if I use -m64 for 64 bit then all end binaries are generated 64-bit and the shared libraries are generated 32-bit and the compilation fails (ONLY ON SOLARIS) since that particular line is only for the condition Solaris AND gcc. If I use the COMPILER which is CC + CFLAGS it passes -m64 properly to it and generates shared libraries 64-bit and the compile continues.. Hmm ... I see we're doing it that way already for some other platforms, but I can't help thinking it's a kluge. Wouldn't the correct answer be that -m64 needs to be in LDFLAGS? The correct answer may be to put -m64 into CC. Did we conclude that that was a satisfactory solution, or is this still a live patch proposal? If -m64 in CC is the right solution, it should probably be mentioned in FAQ_Solaris. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] [GENERAL] 8.2.1 Compiling Error
- Forwarded message from elein [EMAIL PROTECTED] - To: pgsql-general@postgresql.org Cc: elein [EMAIL PROTECTED] Subject: [GENERAL] 8.2.1 Compiling Error Mail-Followup-To: pgsql-general@postgresql.org From: elein [EMAIL PROTECTED] Debian Linux. Have always built from scratch with no problem. This is 8.2.1 from postgresql.org. Conf line is: --prefix=/local/pgsql82 --enable-depend --enable-cassert --enable-debug --with-tcl --with-python --with-perl --with-pgport=5432 Build error is: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g -Wno-error -L../../../../src/port -Wl,-rpath,'/local/pgsql82/lib' preproc.o type.o ecpg.o ecpg_keywords.o output.o keywords.o c_keywords.o ../ecpglib/typename.o descriptor.o variable.o -lpgport -lz -lreadline -lcrypt -ldl -lm -o ecpg /usr/lib/libc_nonshared.a(elf-init.oS)(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0): In function `__i686.get_pc_thunk.bx': : multiple definition of `__i686.get_pc_thunk.bx' ../ecpglib/typename.o(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0):/home/local/src/postgresql-8.2.1/src/interfaces/ecpg/ecpglib/typename.c:18: first defined herecollect2: ld returned 1 exit status make[4]: *** [ecpg] Error 1 make[4]: Leaving directory `/home/local/src/postgresql-8.2.1/src/interfaces/ecpg/preproc' make[3]: *** [all] Error 2 make[3]: Leaving directory `/home/local/src/postgresql-8.2.1/src/interfaces/ecpg' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/local/src/postgresql-8.2.1/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/local/src/postgresql-8.2.1/src' make: *** [all] Error 2 Any ideas? elein ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings - End forwarded message - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] 8.2.1 Compiling Error
elein [EMAIL PROTECTED] writes: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g -Wno-error -L../../../../src/port -Wl,-rpath,'/local/pgsql82/lib' preproc.o type.o ecpg.o ecpg_keywords.o output.o keywords.o c_keywords.o ../ecpglib/typename.o descriptor.o variable.o -lpgport -lz -lreadline -lcrypt -ldl -lm -o ecpg /usr/lib/libc_nonshared.a(elf-init.oS)(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0): In function `__i686.get_pc_thunk.bx': : multiple definition of `__i686.get_pc_thunk.bx' ../ecpglib/typename.o(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0):/home/local/src/postgresql-8.2.1/src/interfaces/ecpg/ecpglib/typename.c:18: first defined herecollect2: ld returned 1 exit status make[4]: *** [ecpg] Error 1 Weird. None of the Debian machines in the buildfarm are failing. Is this a vanilla x86 installation? Maybe something non-default about your compiler? typename.c in 8.2 branch hasn't changed for about three years, so it's not like that code suddenly broke ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Lock compatibility matrix
Hi there, following discussion in -patches about lock compatibility matrix, posted by Teodor, we have another matrix http://mira.sai.msu.su/~megera/pgsql/lockmatrix/c2.html Besides formatting improvements, it has addtional lock with temporary name UPDATE EXCLUSIVE (UE), which is the same as EXCLUSIVE, but doesn't conflicts with SHARE UPDATE EXCLUSIVE (SUE), which aquired by VACUUM and autovacuum. The reason for this is that at present we have no lock mode, which doesn't conflicts with *vacuum. The problem was described in thread http://archives.postgresql.org/pgsql-general/2006-12/msg01476.php What is the reason why we don't have such lock ? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] May, can, might
I have made these adjustments to the documentation. Do people want the error message strings also updated? It will probably make the translation easier/clearer in the future, but it does involve some error message wording churn. CVS HEAD only, of course. --- bruce wrote: Standard English uses may, can, and might in different ways: may - permission, You may borrow my rake. can - ability, I can lift that log. might - possibility, It might rain today. Unfortunately, in conversational English, their use is often mixed, as in, You may use this variable to do X, when in fact, can is a better choice. Similarly, It may crash is better stated, It might crash. I would like to clean up our documentation to consistently use these words. Objections? (Who says were obsessive?) :-) -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Lock compatibility matrix
Oleg Bartunov oleg@sai.msu.su writes: Besides formatting improvements, it has addtional lock with temporary name UPDATE EXCLUSIVE (UE), which is the same as EXCLUSIVE, but doesn't conflicts with SHARE UPDATE EXCLUSIVE (SUE), which aquired by VACUUM and autovacuum. The reason for this is that at present we have no lock mode, which doesn't conflicts with *vacuum. The problem was described in thread http://archives.postgresql.org/pgsql-general/2006-12/msg01476.php What is the reason why we don't have such lock ? I don't think the case was made that we need one. There was certainly nothing in that thread that I found convincing. My opinion is we have too many lock modes already ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] 8.2.1 Compiling Error
On Wed, 2007-01-31 at 11:38 -0800, elein wrote: - Forwarded message from elein [EMAIL PROTECTED] - To: pgsql-general@postgresql.org Cc: elein [EMAIL PROTECTED] Subject: [GENERAL] 8.2.1 Compiling Error Mail-Followup-To: pgsql-general@postgresql.org From: elein [EMAIL PROTECTED] Debian Linux. Have always built from scratch with no problem. This is 8.2.1 from postgresql.org. Conf line is: --prefix=/local/pgsql82 --enable-depend --enable-cassert --enable-debug --with-tcl --with-python --with-perl --with-pgport=5432 Don't know if it will help, but you might take a peek at http://archives.postgresql.org/pgsql-ports/2006-09/msg5.php -- Korry
Re: [HACKERS] [GENERAL] 8.2.1 Compiling Error
On Wed, Jan 31, 2007 at 03:41:31PM -0500, Tom Lane wrote: elein [EMAIL PROTECTED] writes: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g -Wno-error -L../../../../src/port -Wl,-rpath,'/local/pgsql82/lib' preproc.o type.o ecpg.o ecpg_keywords.o output.o keywords.o c_keywords.o ../ecpglib/typename.o descriptor.o variable.o -lpgport -lz -lreadline -lcrypt -ldl -lm -o ecpg /usr/lib/libc_nonshared.a(elf-init.oS)(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0): In function `__i686.get_pc_thunk.bx': : multiple definition of `__i686.get_pc_thunk.bx' ../ecpglib/typename.o(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0):/home/local/src/postgresql-8.2.1/src/interfaces/ecpg/ecpglib/typename.c:18: first defined herecollect2: ld returned 1 exit status make[4]: *** [ecpg] Error 1 Weird. None of the Debian machines in the buildfarm are failing. Is this a vanilla x86 installation? Maybe something non-default about your compiler? Pretty darn vanilla, except for source packages from postgres. Korry suggests upgrading gcc, although I've built all of the previous versions with no problem. But I'll try that. typename.c in 8.2 branch hasn't changed for about three years, so it's not like that code suddenly broke ... regards, tom lane --elein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/pgSQL RENAME functionality in TODOs
imad [EMAIL PROTECTED] writes: OK, so renaming does not work in the same block. You can rename a vairable in a nested block and thats why it works for OLD/NEW. BTW, what is the purpose behind it? Declaring a variable in a block and quickly renaming it does not make sense to me. I agree it's pretty useless; but if we're gonna forbid it then we should throw a more sensible error than syntax error. Actually, it seems to me that it works in the nested-block case only for rather small values of work: regression=# create function foo() returns int as $$ regression$# declare regression$# x int := 1; regression$# begin regression$# x := 2; regression$# declare regression$# rename x to y; regression$# begin regression$#y := 3; regression$# end; regression$# return x; regression$# end$$ language plpgsql; CREATE FUNCTION regression=# select foo(); ERROR: column x does not exist LINE 1: SELECT x ^ QUERY: SELECT x CONTEXT: PL/pgSQL function foo line 10 at return regression=# Surely the variable's name should be x again after we're out of the nested block? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] 8.2.1 Compiling Error
elein wrote: - Forwarded message from elein [EMAIL PROTECTED] - Build error is: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g -Wno-error -L../../../../src/port -Wl,-rpath,'/local/pgsql82/lib' preproc.o type.o ecpg.o ecpg_keywords.o output.o keywords.o c_keywords.o ../ecpglib/typename.o descriptor.o variable.o -lpgport -lz -lreadline -lcrypt -ldl -lm -o ecpg /usr/lib/libc_nonshared.a(elf-init.oS)(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0): In function `__i686.get_pc_thunk.bx': : multiple definition of `__i686.get_pc_thunk.bx' ../ecpglib/typename.o(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0):/home/local/src/postgresql-8.2.1/src/interfaces/ecpg/ecpglib/typename.c:18: first defined herecollect2: ld returned 1 exit status make[4]: *** [ecpg] Error 1 make[4]: Leaving directory `/home/local/src/postgresql-8.2.1/src/interfaces/ecpg/preproc' make[3]: *** [all] Error 2 make[3]: Leaving directory `/home/local/src/postgresql-8.2.1/src/interfaces/ecpg' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/local/src/postgresql-8.2.1/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/local/src/postgresql-8.2.1/src' make: *** [all] Error 2 Any ideas? I think I encountered that error once when building some malloc-replacement library. I think I was some strange interaction of a certain libc version with a certain gcc version - I think I fixed it by upgrading both to a newer version, but I can't remeber the details.. :-( greetings, Florian Oflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Data archiving/warehousing idea
G'day hackers, I had some hand-wavy thoughts about some potential gains for postgres in the data archiving/warehousing area. I'm not able to do any work myself on this, and don't actually have a pressing need for it so I'm not requesting someone do it, but I thought it might be worth discussing (if it hasn't been already - I couldn't find anything in the mail archives, but that doesn't mean it's not there...) The main idea is that, there might be space utilisation and performance advantages if postgres had hard read-only tables, i.e. tables which were guaranteed (by postgres) to never have their data changed (insert/update/delete). This could potentially save disk space by allowing book keeping elements in the page/tuple headers to be removed, e.g. visibility information etc. Also, some indexes could potentially be packed tighter if we know the data will never change (of course this is already available using the fillfactor control). There could be performance advantages from areas like: * more efficient disk buffering due to reduced disk space requirements per above. * no need to visit tuple store for visibility info during index scan * greatly reduced or even completely removed locking. If the table is guaranteed read-only, there's no need to lock? * Planner optimisation? E.g. changing the cost of index and sequential scans for the table due to the previous points, and there might be table stats which would be very useful to the planner but which are too expensive to maintain for changing data. The idea would be to introduce a statement something like: ALTER TABLE foo SET ARCHIVE; This would tell postgres to rewrite the on-disk table to the read only format, rewrite the indexes for maximum packing and collect stats for the planner etc. Thoughts? Brickbats? Cheers, Chris. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Data archiving/warehousing idea
On Thu, 1 Feb 2007, Chris Dunlop wrote: G'day hackers, G'Day Chris, already - I couldn't find anything in the mail archives, but that doesn't mean it's not there...) There has been a lot of discussion about this kind of thing over the years. The main idea is that, there might be space utilisation and performance advantages if postgres had hard read-only tables, i.e. tables which were guaranteed (by postgres) to never have their data changed (insert/update/delete). This could potentially save disk space by allowing book keeping elements in the page/tuple headers to be removed, e.g. visibility information etc. Also, some indexes could potentially be packed tighter if we know the data will never change (of course this is already available using the fillfactor control). Well, there is also CPU overhead doing MVCC but there are a few fundamental problems that must be overcome. The most significant is that no useful table is always read only, otherwise you could never load it. What do we do in the presence of a failure during the load or a user issued ABORT? I guess we'd truncate the table... What about replay after a crash? Another way of looking at it is, we use the 'bookkeeping' information in the tuple header for concurrency and for handling the abortion of the transaction. The idea would be to introduce a statement something like: ALTER TABLE foo SET ARCHIVE; I'd not thought of that approach. There are two problems: some archive tables are so large that loading them and then reprocessing them isn't appealing. Secondly, we'd be rewriting the binary structure of the table and this does not suit the non-overwriting nature of Postgres's storage system. A different approach discussed earlier involves greatly restricting the way in which the table is used. This table could only be written to if an exclusive lock is held; on error or ABORT, the table is truncated. The problem is that a lot of this looks like a hack and I haven't seen a very clean approach which has gone beyond basic brain dump. Thanks, Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation X
Uh, where are we on this? --- Tom Lane wrote: I wrote: Michael Fuhr [EMAIL PROTECTED] writes: I've found a situation that causes DROP FUNCTION to fail (tested in 8.1.6, 8.2.1, and 8.3devel): http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php Ugh ... I haven't traced this through in detail, but I'm pretty sure the problem arises from the fact that dependency.c traces through auto/internal dependencies before actually starting to do the deletions (and thus before acquiring locks). I looked into this a bit more, and found that it's actually a pretty general issue with the dependency.c code: we delete objects depending on a target object before we delete the target itself. Which is fine, except that we don't try to take out any lock on the target object until we get to the object-type-specific subroutine that's supposed to delete it. I think we could fix this for tables by acquiring lock on a table at the instant it's put into a list for deletion inside dependency.c. That would be enough to fix Michael's problem instance, but what of other types of objects? There doesn't seem to be anything preventing somebody from, say, deleting a function at the same time someone else is creating an operator depending on the function. We mostly don't take locks on non-table objects while working with them, and for the most part this is fairly sane because those objects are defined by a single system catalog row anyway: either you see the row or you don't. But this means that the depended-on object could be gone by the time you finish adding a dependency on it. It seems a general solution would involve having dependency.c take exclusive locks on all types of objects (not only tables) as it scans them and decides they need to be deleted later. And when adding a pg_depend entry, we'd need to take a shared lock and then recheck to make sure the object still exists. This would be localized in dependency.c, but it still seems like quite a lot of mechanism and cycles added to every DDL operation. And I'm not at all sure that we'd not be opening ourselves up to deadlock problems. I'm a bit tempted to fix only the table case and leave the handling of non-table objects as is. Comments? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Data archiving/warehousing idea
G'day Gavin,
In maillist.postgres.dev, you wrote:
On Thu, 1 Feb 2007, Chris Dunlop wrote:
The main idea is that, there might be space utilisation and
performance advantages if postgres had hard read-only
tables, i.e. tables which were guaranteed (by postgres) to
never have their data changed (insert/update/delete).
This could potentially save disk space by allowing book
keeping elements in the page/tuple headers to be removed,
e.g. visibility information etc. Also, some indexes could
potentially be packed tighter if we know the data will never
change (of course this is already available using the
fillfactor control).
Well, there is also CPU overhead doing MVCC but there are a
few fundamental problems that must be overcome. The most
significant is that no useful table is always read only,
otherwise you could never load it.
Yes, that's why I was proposing ALTER TABLE... SET ARCHIVE
rather than CREATE TABLE... ARCHIVE. (Although, for
consistency, perhaps the CREATE TABLE would be allowed, it's
just that you couldn't load anything into it until you did a
ALTER TABLE... DROP ARCHIVE.)
What do we do in the presence of a failure during the load or
a user issued ABORT? I guess we'd truncate the table...
I was thinking the load simply couldn't happen if the table were
SET ARCHIVE.
What about replay after a crash?
No replay would be required on that table as it would *NOT* be
changed once an SET ARCHIVE were done (unless a DROP ARCHIVE
were done).
Another way of looking at it is, we use the 'bookkeeping'
information in the tuple header for concurrency and for
handling the abortion of the transaction.
So, unless there's something I'm missing (not completely
unlikely!), as long as the table (including it's on-disk
representation) was never changed, the bookkeeping
information wouldn't be required?
The idea would be to introduce a statement something like:
ALTER TABLE foo SET ARCHIVE;
I'd not thought of that approach. There are two problems: some
archive tables are so large that loading them and then
reprocessing them isn't appealing.
Yes - it would only work if you were prepared to wear the cost
of the SET ARCHIVE, which could certainly be considerable.
...oh, I think I see what you were getting at above: you were
thinking of loading the data into the already SET ARCHIVE table
to avoid the considerable cost of rewriting the disk format etc.
I hadn't considered that, but yes, if you were to allow that I
suppose in the presence of load errors or ABORTS etc. the table
could simply be truncated. (For whatever value of simply is
appropriate!)
Secondly, we'd be rewriting the binary structure of the table
and this does not suit the non-overwriting nature of
Postgres's storage system.
Rather than writing in-place, perhaps the SET ARCHIVE would
create a on-disk copy of the table. Of course this would demand
you have twice the disk space available which may be prohibitive
in a large warehouse. On the other hand, I'm not sure if you
would have a single humongous table that you'd SET ARCHIVE on,
you might be as likely to archive on a weekly or yearly or
whatever is manageable basis, along the lines of:
begin;
select * into foo_2006 from foo
where date_trunc('year', timestamp) = '2006-01-01';
delete from foo
where date_trunc('year', timestamp) = '2006-01-01';
alter table foo_2006 set archive;
alter table foo_2006 inherit foo;
commit;
A different approach discussed earlier involves greatly
restricting the way in which the table is used. This table
could only be written to if an exclusive lock is held; on
error or ABORT, the table is truncated.
You're talking about the no-WAL concept? Not quite the same
thing I think, but perhaps complimentary to the ARCHIVE idea: I
wouldn't expect an ARCHIVE table to need to generate any WAL
entries as it would be read only.
Cheers,
Chris.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] FOR SHARE vs FOR UPDATE locks
Added to TODO: * Fix problem when multiple subtransactions of the same outer transaction hold different types of locks, and one subtransaction aborts http://archives.postgresql.org/pgsql-hackers/2006-11/msg01011.php http://archives.postgresql.org/pgsql-hackers/2006-12/msg1.php --- Tom Lane wrote: Jim Nasby [EMAIL PROTECTED] writes: As for possibly using the in-memory store of multiple CIDs affecting a tuple, could that not work if that store contained enough information to 'rollback' the lock to it's original state when restoring to a savepoint? AFAIK other backends would only need to know what the current lock being held was, they wouldn't need to know the history of it themselves... One of the interesting problems is that if you upgrade shared lock to exclusive and then want to roll that back, you might need to un-block other processes that tried to acquire share lock after you acquired exclusive. We have no way to do that in the current implementation. (Any such processes will be blocked on your transaction ID lock, which you can't release without possibly unblocking the wrong processes.) regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_restore fails with a custom backup file
Where are we on this? --- Magnus Hagander wrote: On Tue, Dec 19, 2006 at 04:58:22PM +0100, Zeugswetter Andreas ADI SD wrote: MinGW has fseeko64 and ftello64 with off64_t. Maybe we need separate macros for MSVC and MinGW. Given the other You mean something quick and dirty like this ? That would work. Yes, except does that actually work? If so you found the place in the headers to stick it without breaking things that I couldn't find ;-) Compiles clean without warnings on MinGW, but not tested, sorry also no time. Does not compile on my MinGW - errors in the system headers (unistd.h, io.h) due to changing the argument format for chsize(). The change of off_t propagated into parts of the system headers, thus chaos was ensured. I still think we need to use a pgoff_t. Will look at combining these two approaches. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Security leak with trigger functions?
Added to TODO: * Tighten trigger permission checks http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php and: * Tighten function permission checks http://archives.postgresql.org/pgsql-hackers/2006-12/msg00568.php --- Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: Is this true for on-select rules too? In that case, couldn't any user run his code as postmaster by creating an appropriate on-select rule and waiting until somebody/cron backups the database using pg_dump? I don't see any issue for views' on-select rules; they wouldn't get executed during either dump or reload. It does seem like there are some other potential hazards once you start thinking this way: * Datatype I/O functions: the output function will be run as superuser during pg_dump, and the input function during restore. I think this is not an attack spot today because I/O functions can only be written in C, but we'd have to think about the consequences before allowing I/O functions in trusted P/L languages. (Perhaps arrange for I/O functions to be run as if setuid to their owner? Could be expensive...) * Functions associated with indexes would get run during restore: both the datatype-related index support functions, and any functions used in functional indexes. This might be OK because we require such functions to be immutable, but I do not think the link from immutable to can't write database is currently air-tight. * Functions in CHECK constraints (either table or domain constraints) would be executed during restores. There is not an immutability constraint for these currently, although arguably it'd be reasonable to require? * Trigger functions: not executed during pg_dump, nor during a full restore, but they *would* be executed during a data-only restore if you'd not used --disable-triggers. * ON INSERT rules: likewise, executed during data-only restores, possibly resulting in execution of user-defined functions. During restores, we normally set the userid to be the table owner while loading data into a particular table, which would mostly close these holes except that I think a function can revert the session authorization to be whatever the outermost user id is. Probably we need to tighten up the conditions under which a SET SESSION AUTHORIZATION can be reverted within a function. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation X
Bruce Momjian [EMAIL PROTECTED] writes: Uh, where are we on this? Still in the think-about-it mode, personally ... my proposed fix is certainly much too invasive to consider back-patching, so unless someone comes up with a way-simpler idea, it's 8.3 material at best ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] May, can, might
Bruce Momjian wrote: I have made these adjustments to the documentation. Do people want the error message strings also updated? I have no problem with that. They seem to be in pretty good shape already, so the changes should be few. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
