Re: [HACKERS] Standby Mode
On Wed, 2006-08-02 at 18:49 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: [I have an outstanding question on how to include LWlock support into the archiver, required to flesh out the feature set, and of course assuming these patches being accepted.] The archiver is deliberately designed not to be connected to shared memory. If you want to change that you'll have to make a very strong case why we should give up the safety and security advantages of it. We should let the user decide. If archiver_timeout is a server start GUC then we can attach to shared memory if it is set, if not we avoid that. If they are in a position to want that functionality they can make that trade-off. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Standby Mode
Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2006-08-02 at 18:49 -0400, Tom Lane wrote: The archiver is deliberately designed not to be connected to shared memory. If you want to change that you'll have to make a very strong case why we should give up the safety and security advantages of it. We should let the user decide. Really? The way we let the user decide whether to run as root or not? I don't think we make security-related decisions that way. You haven't actually explained what you want this for, so the entire discussion is operating in a vacuum ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] VALUES clause memory optimization (was: Values list-of-targetlists patch...)
Joe Conway [EMAIL PROTECTED] writes: In transformExpr the comment implies that it should be safe to reapply to an already transformed expression. What if we free the raw_parser expression list/cells/nodes and replace it with the as-transformed one? How are you going to do the replace bit? The entire problem is that you don't know where are all the down-links leading to the subexpression you are currently working on. The reason we could safely list_free inside transformInsertRow is that we know all its callers have just built the passed-in list and so there are no other pointers to it. That doesn't apply in the general case of grammar output. I think in the long run we probably ought to fix things so that the grammar never outputs any multiply-linked trees; that little shortcut has been a continuing source of grief for many reasons. I can't see doing that for 8.2 though. My advice is to get that low-hanging fruit in transformInsertRow and leave the other ideas for 8.3. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_terminate_backend
Andreas Pflug [EMAIL PROTECTED] writes: utils/adt/misc.c says: //* Disabled in 8.0 due to reliability concerns; FIXME someday *// Datum *pg_terminate_backend*(PG_FUNCTION_ARGS) Well, AFAIR there were no more issues raised about code paths that don't clean up correctly, so can we please remove that comment and make the function live finally? No, you have that backwards. The burden of proof is on those who want it to show that it's now safe. The situation is not different than it was before, except that we can now actually point to a specific bug that did exist, whereas the original concern was just an unfocused one that the code path hadn't been adequately exercised. That concern is now even more pressing than it was. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Rebuilding DB from broken hardrive.
Yoon [EMAIL PROTECTED] writes: It would be nice to know how each directories are related to each other or at least a pointer to where I should look. http://www.postgresql.org/docs/8.1/static/storage.html 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] 8.2 feature set
Joshua D. Drake [EMAIL PROTECTED] writes: Well if an initdb was not required, I think that would be a huge feature ;) (I know it may not work release over release) If someone had started working on pg_upgrade six months ago, we might have that for 8.2 ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ecpg test suite
Hi, I just committed some changes by Joachim that should reduce the problems and the differences by a large margin. Could you please rerun the test and send us the regression.diff? Thanks a lot in advance. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_terminate_backend
Andrew Dunstan wrote: Andreas Pflug wrote: Since I have a stuck backend without client again, I'll have to kill -SIGTERM a backend. Fortunately, I do have console access to that machine and it's not win32 but a decent OS. You do know that on Windows you can use pg_ctl to send a pseudo SIGTERM to a backend, don't you? The main issue still is that console access id required, on any OS. Regards, Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ecpg test suite
Michael Meskes [EMAIL PROTECTED] writes: I just committed some changes by Joachim that should reduce the problems and the differences by a large margin. Could you please rerun the test and send us the regression.diff? Thanks a lot in advance. While init.pgc no longer fails outright, it still generates a pile of unsightly compiler warnings, eg on Fedora 5 (gcc 4.1.1) dyntest.pgc:66: WARNING: nullable is always 1 dyntest2.pgc:72: WARNING: nullable is always 1 init.pgc:8: warning: no previous prototype for 'fa' init.pgc:15: warning: no previous prototype for 'fb' init.pgc:22: warning: no previous prototype for 'fc' init.pgc:28: warning: no previous prototype for 'fd' init.pgc:34: warning: no previous prototype for 'fe' init.pgc:40: warning: no previous prototype for 'sqlnotice' init.pgc: In function 'main': init.pgc:76: warning: unused variable 'f' init.pgc:73: warning: unused variable 'iax' init.pgc:72: warning: unused variable 'iay' init.pgc:71: warning: unused variable 'h' init.pgc:70: warning: unused variable 'c' init.pgc:69: warning: unused variable 'e' init.pgc:67: warning: unused variable 'j' init.pgc:66: warning: unused variable 'i' init.pgc:65: warning: unused variable 'g' init.pgc:64: warning: unused variable 'd' init.pgc:63: warning: unused variable 'b2' init.pgc:62: warning: unused variable 'b' init.pgc:61: warning: unused variable 'a' init.pgc:69: warning: 'y' is used uninitialized in this function test_informix.pgc: In function 'main': test_informix.pgc:20: warning: implicit declaration of function 'exit' test_informix.pgc:20: warning: incompatible implicit declaration of built-in function 'exit' I find this really unacceptable. There is no other part of the Postgres tree besides ecpg that generates any warnings at all. As for the actual test, I get: $ make check ... if [ all = clean ]; then rm -f results/*.stdout results/*.stderr results/*.c; rm -rf tmp_check/; rm -f log/*.log; rm -f pg_regress.inc.sh regression.diff; fi sh ./pg_regress.sh --dbname=regress1 --debug --temp-install --top-builddir=../. ./../.. --temp-port=55444 --listen-on-tcp --multibyte=SQL_ASCII --load-language= plpgsql == creating temporary installation== == initializing database system == == starting postmaster== running on port 55444 with pid 10754 == creating database regress1 == CREATE DATABASE == installing plpgsql == == creating database connectdb == CREATE DATABASE == installing plpgsql == == running regression test queries== /home/tgl/pgsql/src/interfaces/ecpg/test/./tmp_check/install//home/tgl/testversion/bin/createuser -R -S -D -q regressuser1 /home/tgl/pgsql/src/interfaces/ecpg/test/./tmp_check/install//home/tgl/testversion/bin/createuser -R -S -D -q connectuser testing connect/test1.pgc ... FAILED (log, output, source) testing connect/test2.pgc ... FAILED (log, output, source) testing connect/test3.pgc ... FAILED (log, output, source) testing connect/test4.pgc ... FAILED (log, output, source) testing compat_informix/test_informix.pgc ... FAILED (log, output, source) testing compat_informix/test_informix2.pgc ... FAILED (log, output, source) testing complex/test1.pgc ... FAILED (log, output, source) testing complex/test2.pgc ... FAILED (log, output, source) testing complex/test3.pgc ... FAILED (log, output, source) testing complex/test4.pgc ... FAILED (log, output, source) testing complex/test5.pgc ... FAILED (log, output, source) testing errors/init.pgc... FAILED (log, output, source) testing pgtypeslib/dt_test.pgc ... FAILED (log, output, source) testing pgtypeslib/dt_test2.pgc... FAILED (log, output, source) testing pgtypeslib/num_test.pgc... FAILED (log, output, source) testing sql/code100.pgc... FAILED (log, output, source) testing sql/copystdout.pgc ... FAILED (log, output, source) testing sql/define.pgc ... FAILED (log, output, source) testing sql/desc.pgc ... FAILED (log, output, source) testing sql/dynalloc.pgc ... FAILED (log, output, source) testing sql/dynalloc2.pgc ... FAILED (log, output, source) testing sql/dyntest.pgc... FAILED (log, output, source) testing sql/dyntest2.pgc ... FAILED (log, output, source) testing sql/func.pgc ... FAILED (log, output, source) testing sql/indicators.pgc ... FAILED (log,
Re: [HACKERS] pg_terminate_backend
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: utils/adt/misc.c says: //* Disabled in 8.0 due to reliability concerns; FIXME someday *// Datum *pg_terminate_backend*(PG_FUNCTION_ARGS) Well, AFAIR there were no more issues raised about code paths that don't clean up correctly, so can we please remove that comment and make the function live finally? No, you have that backwards. The burden of proof is on those who want it to show that it's now safe. The situation is not different than it was before, except that we can now actually point to a specific bug that did exist, whereas the original concern was just an unfocused one that the code path hadn't been adequately exercised. That concern is now even more pressing than it was. If the backend's stuck, I'll have to SIGTERM it, whether there's pg_terminate_backend or not. Ultimately, if resources should remain locked, there's no chance except restarting the whole server anyway. SIGTERM gives me a fair chance (90%) that it will work without restart. The persistent refusal of supporting the function makes it more painful to execute, but not less necessary. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_terminate_backend
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: utils/adt/misc.c says: //* Disabled in 8.0 due to reliability concerns; FIXME someday *// Datum *pg_terminate_backend*(PG_FUNCTION_ARGS) Well, AFAIR there were no more issues raised about code paths that don't clean up correctly, so can we please remove that comment and make the function live finally? No, you have that backwards. The burden of proof is on those who want it to show that it's now safe. The situation is not different than it was before, except that we can now actually point to a specific bug that did exist, whereas the original concern was just an unfocused one that the code path hadn't been adequately exercised. That concern is now even more pressing than it was. I am not sure how you prove the non-existance of a bug. Ideas? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] ecpg test suite
On Thu, Aug 03, 2006 at 09:47:27AM -0400, Tom Lane wrote: While init.pgc no longer fails outright, it still generates a pile of unsightly compiler warnings, eg on Fedora 5 (gcc 4.1.1) ... I find this really unacceptable. There is no other part of the Postgres tree besides ecpg that generates any warnings at all. Tom, keep in mind that we are working on this. The tests were originally just some files I used to develop with. We are now making them become part of the source tree. The warnings should be gone by now, except for the ECPG warning that is supposed to come out. Maybe we remove that line. Joachim didn't want me to commit his SoC stuff before he finishes work, but I felt this is the better way because we get some testing on other architectures/OSes so everything should be up and running come release time. diff: `-3' option is obsolete; omit it diff: Try `diff --help' for more information. Strange, works well on my Linux system. However, I tried correcting the option but I'm unsure if it works for you now since both versions worked for me. Regression.diffs is empty, possibly because of the incorrect diff invocation hinted at by the last message, but looking into the results directory makes it look like you've not got everything on the same page about which port number to use: [NO_PID]: connect: could not open database connectdb on localhost port 55432 for user connectuser in line 41 could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 55432? That's not the port the temp postmaster is listening on; I suspect you've got some hard-wired assumption in there that the user hasn't specified a nonstandard --port option to configure. I find it disturbing that the regression test script doesn't mention having shut down the temp postmaster, too. No idea. Joachim? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_terminate_backend
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: No, you have that backwards. The burden of proof is on those who want it to show that it's now safe. The situation is not different than it was before, except that we can now actually point to a specific bug that did exist, whereas the original concern was just an unfocused one that the code path hadn't been adequately exercised. That concern is now even more pressing than it was. I am not sure how you prove the non-existance of a bug. Ideas? What I'm looking for is some concentrated testing. The fact that some people once in a while SIGTERM a backend doesn't give me any confidence in it. 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] pg_terminate_backend
What I'm looking for is some concentrated testing. The fact that some people once in a while SIGTERM a backend doesn't give me any confidence in it. Now wait a minute, is there some risk of lockup if I kill a backend ? Cause I do that relatively often (say 20 times a day, when some web users time out but their query keeps running). Should I rather not do it ? Thanks, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ecpg test suite
On Thu, Aug 03, 2006 at 04:54:35PM +0200, Michael Meskes wrote: diff: `-3' option is obsolete; omit it diff: Try `diff --help' for more information. Strange, works well on my Linux system. However, I tried correcting the option but I'm unsure if it works for you now since both versions worked for me. This got introduced by Rocco's Makefile patch, it worked for me, so I thought it's fine. Rocco, your AIX box will work with only diff -c as well, won't it? [NO_PID]: connect: could not open database connectdb on localhost port 55432 for user connectuser in line 41 could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 55432? That's not the port the temp postmaster is listening on; I suspect you've got some hard-wired assumption in there that the user hasn't specified a nonstandard --port option to configure. I find it disturbing that the regression test script doesn't mention having shut down the temp postmaster, too. No idea. Joachim? Yes, it's hardcoded but in just one file. Only one of the connect-Tests does tcp/ip connects. This can't be changed by a simple #define nor exec sql define, so I added a template file and replaced the port number with sed. Michael, in a few minutes I'll send you a patch that fixes all of Tom's suggestions (however you might have done parts of it already by yourself, like the diff options and the warnings...). Joachim -- Joachim Wieland [EMAIL PROTECTED] GPG key available ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] GIN vs. statistics collector
Andreas Seltenreich [EMAIL PROTECTED] writes: I think there's a call to pgstat_count_index_scan missing in GIN. Currently, the idx_scan column of pg_stat_*_indexes is stuck at zero for GIN indexes. Patch attached. Looks correct to me --- applied. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ecpg test suite
Joachim Wieland [EMAIL PROTECTED] writes: diff: `-3' option is obsolete; omit it diff: Try `diff --help' for more information. This got introduced by Rocco's Makefile patch, it worked for me, so I thought it's fine. Rocco, your AIX box will work with only diff -c as well, won't it? The spelling we've used for many years is diff -w -C3 Is there a reason to change from that? Yes, it's hardcoded but in just one file. Only one of the connect-Tests does tcp/ip connects. This can't be changed by a simple #define nor exec sql define, so I added a template file and replaced the port number with sed. At least from my perspective, it would be good if there were a way to run the regression tests without any use of TCP ports. The problem is that Red Hat's build system tends to try to build 32-bit and 64-bit variants of the same architecture concurrently in different chroots on the same machine. Tests using unix sockets work fine in this environment, tests using TCP sockets conflict and fail. If there's no way to run an ecpg test without TCP then I'll never be able to enable ecpg regression tests in Red Hat RPMs. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_terminate_backend
Csaba Nagy [EMAIL PROTECTED] writes: Now wait a minute, is there some risk of lockup if I kill a backend ? Cause I do that relatively often (say 20 times a day, when some web users time out but their query keeps running). Should I rather not do it ? statement_timeout is your friend. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Standby Mode
Tom Lane [EMAIL PROTECTED] writes: Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2006-08-02 at 18:49 -0400, Tom Lane wrote: The archiver is deliberately designed not to be connected to shared memory. If you want to change that you'll have to make a very strong case why we should give up the safety and security advantages of it. We should let the user decide. Really? The way we let the user decide whether to run as root or not? I don't think we make security-related decisions that way. Well there is also precedent the other way, namely fsync. I think the key factor is, is it a decision the user may know more about than we do. In the case of fsync the user may well know that the data isn't important (yet) such as in the case of an initial database load. In general I would say security decisions are more prone rather than less to having this property. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Patch to allow C extension modules to initialize/finish
PostgreSQL provides a way to load C extension modules with its internal FMGR. Unfortunately there is no portable way for an extension module to initialize (directly after the pg_dlopen() of the DSO) and to finish (directly before the pg_dlclose() of the DSO). This way it is mostly impossible to write a more complex extension module in a portable way. The only to me known workarounds are either to call an own initialization function at the start of _EVERY_ exported function manually (works, but is ugly and especially doesn't work for the finishing function!) or to leverage some platform specific hacks like the implicitly called _init and _fini functions (is what the ODBC extension module currently does, but is horribly platform specific and not portable). Hence I propose the patch below (applies to PostgreSQL 8.1.4) which mimics the dlopen(3) and dlclose(3) behaviour of some Unix platforms and resolves and calls _PG_init and _PG_fini functions of an extension module right after/before the pg_dlopen/pg_dlclose calls in the FMGR. This is both a fully portable solution and fully backward compatible to existing and forthcoming extension modules (except they really would have _PG_init and _PG_fini functions already defined). Ralf S. Engelschall [EMAIL PROTECTED] www.engelschall.com Index: src/backend/utils/fmgr/dfmgr.c --- src/backend/utils/fmgr/dfmgr.c.orig 2005-10-15 04:49:32 +0200 +++ src/backend/utils/fmgr/dfmgr.c 2006-08-02 20:48:48 +0200 @@ -60,6 +60,10 @@ static char *expand_dynamic_library_name(const char *name); static char *substitute_libpath_macro(const char *name); +/* types for PostgreSQL-specific DSO init/fini functions */ +typedef void (*PG_init_t)(void); +typedef void (*PG_fini_t)(void); + /* * Load the specified dynamic-link library file, and look for a function * named funcname in it. (funcname can be NULL to just load the file.) @@ -82,6 +86,7 @@ char *load_error; struct stat stat_buf; char *fullname; + PG_init_t *PG_init; fullname = expand_dynamic_library_name(filename); if (!fullname) @@ -146,6 +151,13 @@ fullname, load_error))); } + /* optionally give the DSO a chance to initialize by calling a + PostgreSQL-specific (and this way portable) _PG_init function + similar to what dlopen(3) implicitly does with _init on some + Unix platforms. */ + if ((PG_init = (PG_init_t *)pg_dlsym(file_scanner-handle, _PG_init)) != NULL) + (*PG_init)(); + /* OK to link it into list */ if (file_list == NULL) file_list = file_scanner; @@ -192,6 +204,7 @@ *nxt; struct stat stat_buf; char *fullname; + PG_fini_t *PG_fini; fullname = expand_dynamic_library_name(filename); if (!fullname) @@ -224,6 +237,14 @@ else file_list = nxt; clear_external_function_hash(file_scanner-handle); + + /* optionally give the DSO a chance to finish by calling + a PostgreSQL-specific (and this way portable) _PG_fini + function similar to what dlopen(3) implicitly does with + _fini on some Unix platforms. */ + if ((PG_fini = (PG_init_t *)pg_dlsym(file_scanner-handle, _PG_fini)) != NULL) + (*PG_fini)(); + pg_dlclose(file_scanner-handle); free((char *) file_scanner); /* prv does not change */ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Hash indexes (was: On-disk bitmap index patch)
On Tue, Aug 01, 2006 at 02:26:18PM -0700, [EMAIL PROTECTED] wrote: Kenneth Marshall wrote: On Fri, Jul 28, 2006 at 12:14:49PM -0500, Jim C. Nasby wrote: On Thu, Jul 27, 2006 at 01:46:01PM -0400, Alvaro Herrera wrote: Jim Nasby wrote: On Jul 25, 2006, at 3:31 PM, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: What would be the use-case for hash indexes ? And what should be done to make them faster than btree ? If we knew, we'd do it ;-) But no one's put enough effort into it to find out. Do they use the same hash algorithm as hash joins/aggregation? If so, wouldn't hash indexes be faster for those operations than regular indexes? The main problem doesn't seem to be in the hash algorithm (which I understand to mean the hashing function), but in the protocol for concurrent access of index pages, and the distribution of keys in pages of a single hash key. This is described in a README file or a code comment somewhere in the hash AM code. Someone needs to do some profiling to find out what the bottleneck really is, and ideally find a way to fix it. What I'm getting at is that I've never seen any explanation for the theoretical use cases where a hash index would outperform a btree. If we knew what kind of problems hash indexes were supposed to solve, we could try and interest people who are solving those kinds of problems in fixing hash indexes. The big win for hash indexes is the idea that searching for a single value should only take 1 I/O operation in a perfect world. Btree can not do that. Hash indexes stored on disk still need a level of indirection -- you've got to look up what range of blocks contains your hash value. How big your table of ranges is depends on how big the hash index is and how big your ranges are. Almost always you can fit that table into a block cached in memory. But, the root of a BTree is often cached in memory too. So there's no advantage for a hash index over a BTree index until the BTree needs to grow to three levels deep, what is that, usually 10,000 or 100,000 records. Beyond that, you're right, the BTree slowly grows deeper while the hash index doesn't. I have seen some clever hash techniques that used knowledge ofo the file and directory structure to avoid the indirection allowing a single I/O operation to retrieve the value of the index without needing another layer of indirection. So it is possible given appropriate constraints. Of course, postgresql would need to check for tuple validity unless that could be incorporated into the index in some fashion. Ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 feature set
[EMAIL PROTECTED] (Tom Lane) writes: Joshua D. Drake [EMAIL PROTECTED] writes: Well if an initdb was not required, I think that would be a huge feature ;) (I know it may not work release over release) If someone had started working on pg_upgrade six months ago, we might have that for 8.2 ... Someone brought the absence of that up in a LUG context this week, so it's certainly the sort of feature that would be worth making prominent... -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://cbbrowne.com/info/spreadsheets.html I think you ought to know I'm feeling very depressed -- Marvin the Paranoid Android ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] tg_trigtuple/tg_newtuple settings in AFTER triggers
Michael Fuhr [EMAIL PROTECTED] writes: Set tg_trigtuple/tg_newtuple in AFTER triggers according to whether old and new tuples were supplied rather than blindly setting them according to the event type. Per discussion in pgsql-hackers. Looks good, applied. 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: GENERATED ... AS IDENTITY, Was: Re: [HACKERS] Feature Freeze
Rod Taylor írta:
For db restoration (pg_dump), how do you restore to the same values as
previously if it is always regenerated? By making ALWAYS a suggestion
for some users instead of always enforced and providing an override
mechanism for it. I assume it only works for relation owners but I've
not figured out how the spec does permissions.
override clause ::=
OVERRIDING USER VALUE
| OVERRIDING SYSTEM VALUE
In short, pg_dump should append OVERRIDING SYSTEM VALUE to any insert or
copy for relations with an GENERATED ALWAYS identity column and the
backend will need to respect that.
Aren't INSERT and COPY distinguished in code paths?
Yes, they are separate but they also use the same permission set.
Any user can copy into a structure at any time and virtually every
restriction will be applied normally (CHECK, DEFAULT, etc.). Copy
bypasses Rules, significant parsing overhead since there is no need to
look for subselects, and possibly some triggers are bypassed. I'm fairly
sure that foreign key triggers fire.
In short, COPY doesn't bypass enforcement. GENERATED ALWAYS is part of
that enforcement and should be included in that.
If it is not included, we cannot recommend GENERATED ALWAYS for uses
like recording CURRENT_USER in an audit log since the data could be
fudged.
OK. So COPY needs an OVERRIDING close, too, not just INSERT.
In the meantime I implemented the discussed restrictions on
UPDATE for GENERATED ALWAYS columns, allowing
UPDATE tab SET col = default only for
GENERATED ALWAYS AS. I also implemented
INSERT ... OVERRIDING { SYSTEM | USER } VALUE.
If I got it correctly, OVERRIDING USER VALUE
seems to be the same as omitting the OVERRIDING clause...
ALWAYS is really only enforced for anyone who doesn't have permission to
specify otherwise.
Another one that got me is what do you do if you do this:
CREATE TABLE tab (col integer);
INSERT INTO tab VALUES (10);
ALTER TABLE tab ALTER col GENERATED ALWAYS AS IDENTITY;
What is the value for tab.col? It would seem that the table should
be rewritten with all values for col recalculated -- thus it would be
'1'. But wait! Can we add the override clause here too to keep the old
values and change the enforcement for new tuples only?
I don't think we should rewrite existing rows because
when it was inserted, the stored value was valid
according to the rules at that time. What if you
have more than one rows in that table?
SERIAL has, until recently, been described as a macro. A tool for
setting things up quickly but many parts of which can be changed by hand
after-ward. It's not exactly a good source for information on how this
structure should work. For one, you can easily override the suggested
default a serial gives at any time as any user. The intention of ALWAYS
is to prevent exactly that behaviour.
I don't have an opinion on ALTER TABLE changes for this one way or the
other. It was my intention to advise that a group decision is required
and some research into what other databases do in this case. I believe
MSSQL and DB2 both implement this functionality.
I see.
I looked a bit into the TODO entry that's about
ALTER TABLE tab ALTER col RENAME newcol
should also rename the sequence. My question is:
is it legal to call pg_get_serial_sequence() from
src/backen/parser/analyze.c:transformAlterTableStmt()?
This would be the easiest way to issue an
ALTER TABLE oldseq RENAME newseq
command automatically.
And I think I found a bug in PostgreSQL.
If I do this:
create table tab1 (id serial, t text); -- creates tab1_id_seq
create table tab2 (id serial, t text) inherits (tab1); -- creates
tab2_id_seq
drop table tab1 cascade;
then tab1_id_seq gets also dropped but tab2_id_seq doesn't.
Both 8.1.4 and current 8.2CVS do this.
Oh, and one more item. These expressions have the same abilities as a
CHECK constraint for referencing other columns.
This example comes from an IBM Guide:
CREATE TABLE T1(c1 INT, c2 DOUBLE,
c3 DOUBLE GENERATED ALWAYS AS (c1 + c2),
c4 SMALLINT GENERATED ALWAYS AS
(CASE
WHEN c1 c2 THEN 1
ELSE NULL
END)
);
For this to work, we need to lift the restriction
on DEFAULT so cother columns can appear in the
expression. Dependencies must be tracked between
columns so GENERATED ALWAYS columns on UPDATE
and DEFAULT/ GENERATED ALWAYS columns on
INSERT get their computed values. Circular dependencies
must be avoided, etc. Hm.
Here is what IBM has to say about ALTER TABLE and GENERATED ALWAYS:
GENERATED
Specifies that DB2 generates values for the column.
ALWAYS
Specifies that DB2 will always generate
a value for the
Re: [HACKERS] Replication Documentation
Markus Schiltknecht wrote: Hi, Andrew Hammond wrote: I can see value in documenting what replication systems are known to work (for some definition of work) with a given release in the documentation for that release. Five years down the road when I'm trying to implement replication for a client who's somehow locked into postgres 8.2 (for whatever reason), it would be very helpful to know that slony1.2 is an option. I don't know if this is sufficient justification. Please keep in mind, that most replication solutions (that I know of) are quite independent from the PostgreSQL version used. Thus, documenting which version of PostgreSQL can be used with which version of a replication system should better be covered in the documentation of the replication system. I would agree to this with the caveat that there needs to be something in the postgres documentation that points people to the various replication systems available. Otherwise you would have to update the PostgreSQL documentation for new releases of your favorite replication system - which seems to lead to confusion. Yeah, updating the docs based on other software releases would suck. How about what works with a given release at the time of the release? Perhaps this could be limited to a pointer to the docs for such replication systems, and maybe a very brief description (based on Chris' taxonomy)? Including a separate page on the history of postgres replication to date also makes some sense, at least to me. It should be relatively easy to maintain. I agree that having such a 'replication guide for users of PostgreSQL' is a good thing to have. But I think not much of that should be part of the official PostgreSQL documentation - mainly because the replication solutions are not part of PostgreSQL. Arguably, neither are most of the procedural languages in the Server Programming section of the documentation, and yet they're included. I agree that it's improtant to keep the documentation from getting cluttered up with stuff that's not part of PostgreSQL. However, I think the very fact so many people assume that there's no replication for PostgreSQL simply because it's not mentioned in the documentation shows that for many people replication is precieved as part of the dbms. Even a single page in the documentation wich consists of something along the lines of the following would help these folks find what they're looking for. There are a number of different approaches to solving the problem of replication, each with strengths and weaknesses. As a result, there are a number of different replication solutions available for PostgreSQL. To find out more, please refer to the website. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] unsubscribe
unsubscribe -- Wade Klaver Wavefire Technologies Corporation GPG Public Key at http://archeron.wavefire.com /\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ - ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_terminate_backend
You didn't answer the original question: is killing SIGTERM a backend known/suspected to be dangerous ? And if yes, what's the risk (pointers to discussions would be nice too). statement_timeout is your friend. I know, but unfortunately I can't use it. I did try to use statement_timeout and it worked out quite bad (due to our usage scenario). Some of the web requests which time out on the web should still go through... and we have activities which should not observe statement timeout at all, i.e. they must finish however long that takes. I know it would be possible to use a different user with it's own statement timeout for those requests, but that means we have to rewrite a lot of code which is not possible immediately, and our admins would resist to add even more configuration (additional users=additional connection pool+caches and all to be configured). We also can fix the queries so no timeout happens in the first place, but that will take us even more time. Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_terminate_backend
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: No, you have that backwards. The burden of proof is on those who want it to show that it's now safe. If the backend's stuck, I'll have to SIGTERM it, whether there's pg_terminate_backend or not. Stuck? You have not shown us a case where SIGTERM rather than SIGINT is necessary or appropriate. It seems to me the above is assuming the existence of unknown backend bugs, exactly the same thing you think I shouldn't be assuming ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Patch to allow C extension modules to initialize/finish
On Wed, Aug 02, 2006 at 09:04:11PM +0200, Ralf S. Engelschall wrote: PostgreSQL provides a way to load C extension modules with its internal FMGR. Unfortunately there is no portable way for an extension module to initialize (directly after the pg_dlopen() of the DSO) and to finish (directly before the pg_dlclose() of the DSO). [...] Cool, but... [...] + + /* optionally give the DSO a chance to finish by calling +a PostgreSQL-specific (and this way portable) _PG_fini +function similar to what dlopen(3) implicitly does with +_fini on some Unix platforms. */ + if ((PG_fini = (PG_init_t *)pg_dlsym(file_scanner-handle, _PG_fini)) != NULL) ^ + (*PG_fini)(); + pg_dlclose(file_scanner-handle); free((char *) file_scanner); /* prv does not change */ shouldn't that be PG_fini_t? (yeah, those nitpickers, especially those who are mostly silent bystanders ;) But I'd support the idea myself. Thanks -- tomas signature.asc Description: Digital signature
Re: [HACKERS] pg_terminate_backend
On Thu, 2006-08-03 at 18:10, Csaba Nagy wrote: You didn't answer the original question: is killing SIGTERM a backend ^^^ Nevermind, I don't do that. I do 'kill backend_pid' without specifying the signal, and I'm sufficiently unfamiliar with the unix signal names to have confused them. Is a plain kill still dangerous ? Thanks, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ecpg test suite
On Thu, Aug 03, 2006 at 11:36:22AM -0400, Tom Lane wrote: The spelling we've used for many years is diff -w -C3 I found only -w, but will append -C3 as well. Is there a reason to change from that? No. At least from my perspective, it would be good if there were a way to run the regression tests without any use of TCP ports. It's not necessary, ecpglib uses libpq as any other program, however it does its own parsing of the connect options and there are quite a few different formats you could use so it would be nice to cover that by a few small tests. Do you see a possibility to select what test should be run? Maybe no tcp connections by default but with an additional make-target checktcp? Joachim -- Joachim Wieland [EMAIL PROTECTED] GPG key available ---(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] pg_terminate_backend
Stuck? You have not shown us a case where SIGTERM rather than SIGINT is necessary or appropriate. It seems to me the above is assuming the existence of unknown backend bugs, exactly the same thing you think I shouldn't be assuming ... I do know a case where a plain kill will seem to be stucked: on vacuum of a big table. I guess when it starts an index's cleanup scan it will insist to finish it before stopping. I'm not sure if that's the cause, but I have seen delays of 30 minutes for killing a vacuum... it's true that finally it always did die... but it's also true that I have 'kill -9'-ed it before because I thought it's stucked. Cheers, Csaba. ---(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] ecpg test suite
Joachim Wieland [EMAIL PROTECTED] writes: On Thu, Aug 03, 2006 at 11:36:22AM -0400, Tom Lane wrote: At least from my perspective, it would be good if there were a way to run the regression tests without any use of TCP ports. Do you see a possibility to select what test should be run? Maybe no tcp connections by default but with an additional make-target checktcp? That would work for me. Note there are other reasons besides my Red-Hat-specific problem for not wanting to enable TCP connections during regression tests, for instance * on some platforms they will fail due to aggressive kernel packet filtering * one might not care to expose a postmaster running with auth-method trust to the network, even for just a few seconds. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ecpg test suite
Joachim Wieland [EMAIL PROTECTED] writes: On Thu, Aug 03, 2006 at 11:36:22AM -0400, Tom Lane wrote: The spelling we've used for many years is diff -w -C3 I found only -w, but will append -C3 as well. Careful, there are two different usages: we use -C3 to generate the pretty report to regression.diffs, but not in the preliminary testing step. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_terminate_backend
Csaba Nagy [EMAIL PROTECTED] writes: I do know a case where a plain kill will seem to be stucked: on vacuum of a big table. I guess when it starts an index's cleanup scan it will insist to finish it before stopping. We've fixed a few cases of missing CHECK_FOR_INTERRUPTS lately, and will fix more if you can point them out. Note though that SIGTERM is just as vulnerable to that as SIGINT. 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] pg_terminate_backend
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: No, you have that backwards. The burden of proof is on those who want it to show that it's now safe. If the backend's stuck, I'll have to SIGTERM it, whether there's pg_terminate_backend or not. Stuck? You have not shown us a case where SIGTERM rather than SIGINT is necessary or appropriate. Last night, I had a long-running query I launched from pgAdmin. It was happily running and completing on the server (took about 2 hours), and the backend went back to IDLE. pgAdmin didn't get back a response, assuming the query was still running. Apparently, the VPN router had interrupted the connection silently without notifying either side of the tcp connection. Since the backend is IDLE, there's no query to cancel and SIGINT won't help. So Stuck for me means a backend *not* responding to SIGINT. BTW, there's another scenario where SIGINT won't help. Imagine an app running wild hammering the server with queries regardless of query cancels (maybe some retry mechanism). You'd like to interrupt that connection, i.e. get rid of the backend. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_terminate_backend
Csaba Nagy wrote: On Thu, 2006-08-03 at 18:10, Csaba Nagy wrote: You didn't answer the original question: is killing SIGTERM a backend ^^^ Nevermind, I don't do that. I do 'kill backend_pid' without specifying the signal, and I'm sufficiently unfamiliar with the unix signal names to have confused them. Is a plain kill still dangerous ? SIGTERM is the default kill parameter, so you do exactly what I'm talking about. Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_terminate_backend
Csaba Nagy [EMAIL PROTECTED] writes: On Thu, 2006-08-03 at 18:10, Csaba Nagy wrote: You didn't answer the original question: is killing SIGTERM a backend ^^^ Nevermind, I don't do that. I do 'kill backend_pid' without specifying the signal, man kill says the default is SIGTERM. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_terminate_backend
Bruce Momjian wrote: I am not sure how you prove the non-existance of a bug. Ideas? Would be worth at least the Nobel prize :-) Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_terminate_backend
man kill says the default is SIGTERM. OK, so that means I do use it... is it known to be dangerous ? I thought till now that it is safe to use. What about select pg_cancel_backend() ? Thanks, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_terminate_backend
Csaba Nagy wrote: man kill says the default is SIGTERM. OK, so that means I do use it... is it known to be dangerous ? I thought till now that it is safe to use. Apparently you never suffered any problems from that; neither did I. What about select pg_cancel_backend() That's the function wrapper around kill -SIGINT, which is probably the way you could safely stop your queries most of the time. Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Replication Documentation
Andrew Hammond wrote: How about what works with a given release at the time of the release? We just threw that idea out in the context of the procedural language discussion because we do not have the resources to check what works. Arguably, neither are most of the procedural languages in the Server Programming section of the documentation, and yet they're included. That is false. The documentation documents exactly those pieces of code that we distribute. There are a number of different approaches to solving the problem of replication, each with strengths and weaknesses. As a result, there are a number of different replication solutions available for PostgreSQL. To find out more, please refer to the website. Well, that's what I've been talking about all along, and it has also been the resolution at the Toronto meeting. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] pg_terminate_backend
I am not sure how you prove the non-existance of a bug. Ideas? I do that by deleting all of my code (usually by accident :-) No code, no bugs! -- Korry
Re: [HACKERS] Replication Documentation
There are a number of different approaches to solving the problem of replication, each with strengths and weaknesses. As a result, there are a number of different replication solutions available for PostgreSQL. To find out more, please refer to the website. Well, that's what I've been talking about all along, and it has also been the resolution at the Toronto meeting. Great. Is the above text sufficient for the documentation then, or does anyone have a suggestion on how to say this better? Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] LWLock statistics collector
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Here is a patch to collect statistics of LWLocks. This seems fairly invasive, as well as confused about whether it's an #ifdef'able thing or not. You can't have system views and pg_proc entries conditional on a compile-time #ifdef, so in a default build we would have a lot of nonfunctional cruft exposed to users. Do we really need this compared to the simplistic dump-to-stderr counting support that's in there now? That stuff doesn't leave any cruft behind when not enabled, and it has at least one significant advantage over your proposal, which is that it's possible to get per-process statistics when needed. If I thought that average users would have a need for LWLock statistics, I'd be more sympathetic to expending effort on a nice frontend for viewing the statistics, but this is and always will be just a concern for hardcore hackers ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] WIP archive_timeout patch
Simon Riggs [EMAIL PROTECTED] writes: WIP archive_timeout. All we need to do is add LWLock support to archiver. Thoughts/ideas/hints welcome. Hint: this isn't the archiver's problem, and so you don't need to get the archiver involved in the solution. I'd suggest bgwriter as a reasonably appropriate place instead. 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] [PATCHES] WIP archive_timeout patch
On Thu, 2006-08-03 at 13:38 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: WIP archive_timeout. All we need to do is add LWLock support to archiver. Thoughts/ideas/hints welcome. Hint: this isn't the archiver's problem, and so you don't need to get the archiver involved in the solution. I'd suggest bgwriter as a reasonably appropriate place instead. OK -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User-defined typle similar to char(length) varchar(length)
or so timeframe ... but feel free to improve it if you can. I'm not very familiar with yacc/bison, so pls, review attached patch. I may miss something... It's based on ideas in previous discussions: http://www.pgsql.ru/db/mw/msg.html?mid=1995063 http://www.pgsql.ru/db/mw/msg.html?mid=2091842 Patch adds support of typmod to any type, support of typmod to format_type(). I partially make typename and function name as different set except forms like select TYPE 'asd' or select TYPE(N) 'asd'. Type modifier can be only one integer =0, however grammar rules allow it to be list of expressions. It was done for simplify far future :) -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ user_defined_typmod.gz Description: Unix tar archive ---(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] ecpg test suite
From: Joachim Wieland [mailto:[EMAIL PROTECTED] Sent: Thursday, August 03, 2006 11:23 AM To: Tom Lane; Michael Meskes; Rocco Altier; PostgreSQL Hacker Subject: Re: [HACKERS] ecpg test suite On Thu, Aug 03, 2006 at 04:54:35PM +0200, Michael Meskes wrote: diff: `-3' option is obsolete; omit it diff: Try `diff --help' for more information. Strange, works well on my Linux system. However, I tried correcting the option but I'm unsure if it works for you now since both versions worked for me. This got introduced by Rocco's Makefile patch, it worked for me, so I thought it's fine. Rocco, your AIX box will work with only diff -c as well, won't it? I had used -c to replace the -u. The '-c3' does not work on my machine, but '-C3' does, so I think we should go with that. Thanks, -rocco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug with initDB under windows 2003
Hi James, I just wanted to inform you all that I solvethe issue, it was indeed the nul device as James and Martijn mention. I have change the source to redirect the output to a log file, to which I gave permission to the "postgres" user. The file (currently) is created at the temp folder. This is critical bug due to the fact that on more and more win2003 machines the postgres installation failed to initialize the DB. In any case, I am about to push this fix to be part of the formal postgres source.Is anyone know ,who should I write to in order to test and checked this fix to the CSV. Regards DrorExpress yourself instantly with Windows Live Messenger! Windows Live Messenger!
Re: [HACKERS] Bug with initDB under windows 2003
On Thu, Aug 03, 2006 at 06:49:31PM +, dror wrote: Hi James, I just wanted to inform you all that I solve the issue, it was indeed the nul device as James and Martijn mention. I have change the source to redirect the output to a log file, to which I gave permission to the postgres user. The file (currently) is created at the temp folder. This is critical bug due to the fact that on more and more win2003 machines the postgres installation failed to initialize the DB. To be honest, this is the kind of crap that bugs me about Windows. It happens all the time that you want to dump the output of a program to nowhere. And then they make it so only admins can use it? Writing it to a file is a hack, you don't want the output, that why you send it to the NUL device. Have you been able to determine *why* Microsoft made this braindead decision? Or where it's documented? Their own knowledgebase is filled with examples of using the device, so I imagine they'll have to post a workaround somewhere... In any case, I am about to push this fix to be part of the formal postgres source.Is anyone know ,who should I write to in order to test and checked this fix to the CSV. Submit a patch to the patches list, where it will be reveiwed by the relevenet people. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] User-defined typle similar to char(length) varchar(length)
I'm surprised you got the patch so small. Mind you, you didn't do any folding in the productions for NUMERIC and CHAR which in the long term would probably need to be done. Also, there's the issue of converting the arguments to a typmod, in the long term it'd have to be user-defined per type. Still, it looks good so far, just some way to go still... Have a nice day, On Thu, Aug 03, 2006 at 10:24:43PM +0400, Teodor Sigaev wrote: or so timeframe ... but feel free to improve it if you can. I'm not very familiar with yacc/bison, so pls, review attached patch. I may miss something... It's based on ideas in previous discussions: http://www.pgsql.ru/db/mw/msg.html?mid=1995063 http://www.pgsql.ru/db/mw/msg.html?mid=2091842 Patch adds support of typmod to any type, support of typmod to format_type(). I partially make typename and function name as different set except forms like select TYPE 'asd' or select TYPE(N) 'asd'. Type modifier can be only one integer =0, however grammar rules allow it to be list of expressions. It was done for simplify far future :) -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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 -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] User-defined typle similar to char(length) varchar(length)
Martijn van Oosterhout kleptog@svana.org writes: I'm surprised you got the patch so small. Mind you, you didn't do any folding in the productions for NUMERIC and CHAR which in the long term would probably need to be done. Yeah, the patch ought to be making the grammar smaller not bigger. Also, there's the issue of converting the arguments to a typmod, in the long term it'd have to be user-defined per type. I think we could legislate that the stored typmod is the same as what the user sees (and can't be negative). The fact that it's different for some of the built-in types is a historical artifact that I'd love to get rid of. 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] O_NOATIME
Ron Mayer [EMAIL PROTECTED] writes: Would people be interested in a trivial patch that adds O_NOATIME to open() for platforms that support it? (apparently Linux 2.6.8 and better). Isn't that usually, and more portably, handled in the filesystem mount options? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] User-defined typle similar to char(length) varchar(length)
On Thu, Aug 03, 2006 at 04:18:53PM -0400, Tom Lane wrote: Also, there's the issue of converting the arguments to a typmod, in the long term it'd have to be user-defined per type. I think we could legislate that the stored typmod is the same as what the user sees (and can't be negative). The fact that it's different for some of the built-in types is a historical artifact that I'd love to get rid of. But that makes NUMERIC(x,y) impossible to represent. That probably ok I guess. I was just wondering if it would be reasonable to allow users to create a currency type whose precision can be specified the same way as for numeric. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] O_NOATIME
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Would people be interested in a trivial patch that adds O_NOATIME to open() for platforms that support it? (apparently Linux 2.6.8 and better). Isn't that usually, and more portably, handled in the filesystem mount options? Yes to both. I could imagine that for small systems/workstations you might have some files that want access time, and others that wanted NOATIME -- it seems the new flag lets you choose on a file-by-file bases. That's why I asked. I imagine it won't help on any well-administered production server since they'd probably mount the whole filesystem that way; but might help a bit on out-of-the-box-default-config benchmarks done by naive users who don't tweak filesystem settings. Don't know if we'd care about such an audience or not. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] tg_trigtuple/tg_newtuple settings in AFTER triggers
On Thu, Aug 03, 2006 at 12:05:23PM -0400, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: Set tg_trigtuple/tg_newtuple in AFTER triggers according to whether old and new tuples were supplied rather than blindly setting them according to the event type. Per discussion in pgsql-hackers. Looks good, applied. Thanks. Alvaro made the following suggestion but didn't copy the list -- shall I do what he suggested and submit the changes as another patch? Alvaro Herrera wrote: I'd add an Assert() on the second hunk to make sure newtuple is only set in UPDATE. And also a comment on top of the if to explain why. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] User-defined typle similar to char(length) varchar(length)
Martijn van Oosterhout kleptog@svana.org writes: On Thu, Aug 03, 2006 at 04:18:53PM -0400, Tom Lane wrote: I think we could legislate that the stored typmod is the same as what the user sees (and can't be negative). The fact that it's different for some of the built-in types is a historical artifact that I'd love to get rid of. But that makes NUMERIC(x,y) impossible to represent. Well, we have to special-case INTERVAL anyway (because its cramming some truly bizarre things into typmod), and it wouldn't bother me too much to special-case NUMERIC as well. Another option is to agree on some simple rule for cramming two values into one typmod, like first one in the low half and second in the high half, and then user types could have either one or two typmod values --- but I can imagine some pretty bizarre behavior if the type is expecting one value and you enter two or vice versa. NUMERIC can finesse this because the default for scale is zero, but in the general case that wouldn't work so well. Does anyone have examples of real user-defined types that would need two fields? If not it may not be worth spending time on. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] O_NOATIME
Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: Isn't that usually, and more portably, handled in the filesystem mount options? Yes to both. I could imagine that for small systems/workstations you might have some files that want access time, and others that wanted NOATIME -- it seems the new flag lets you choose on a file-by-file bases. Personally, if I were an admin who wanted access times, I'd regard the existence of such a flag as a security hole. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] tg_trigtuple/tg_newtuple settings in AFTER triggers
Michael Fuhr [EMAIL PROTECTED] writes: Thanks. Alvaro made the following suggestion but didn't copy the list -- shall I do what he suggested and submit the changes as another patch? Alvaro Herrera wrote: I'd add an Assert() on the second hunk to make sure newtuple is only set in UPDATE. And also a comment on top of the if to explain why. Can't get excited about that. Will you also have asserts to complain if the wrong combinations of tuples are supplied for the other cases? Is this really likely to catch anything? It's not like this function is called from a variety of places. While I was applying the patch I considered changing the if (LocTriggerData.tg_trigtuple != NULL) to if ((event-ate_event TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_UPDATE) but this didn't seem to be an improvement on the whole, as it effectively provides two ways to get it wrong (wrong tuple args OR wrong event) instead of only one. I think driving the setup of the tuple fields entirely off the provided tuple args is logically cleaner. 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] [BUGS] Patch to allow C extension modules to initialize/finish
Ralf S. Engelschall [EMAIL PROTECTED] writes: Hence I propose the patch below (applies to PostgreSQL 8.1.4) which mimics the dlopen(3) and dlclose(3) behaviour of some Unix platforms and resolves and calls _PG_init and _PG_fini functions of an extension module right after/before the pg_dlopen/pg_dlclose calls in the FMGR. This seems like a reasonably good idea, and we have got uses for at least the init case in most or all of our PLs. It's nominally too late for 8.2 feature freeze, but I said just a couple days ago that we shouldn't take a very hard line on that. Does anyone object to considering this for 8.2? One question I have is whether it really works as expected in all cases. In particular what if the library is preloaded into the postmaster? Both plpgsql and plperl seem to think they might need to make a distinction between things to do at library load time and things to do per-backend ... and yet, neither of them *actually* have anything they need to do per-backend. Also, what about Windows? I assume that DSOs don't remain attached across the pseudo-fork/exec, will that mess anything up? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] O_NOATIME
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: Isn't that usually, and more portably, handled in the filesystem mount options? Yes to both. I could imagine that for small systems/workstations you might have some files that want access time, and others that wanted NOATIME -- it seems the new flag lets you choose on a file-by-file bases. Personally, if I were an admin who wanted access times, I'd regard the existence of such a flag as a security hole. I'm not sure I see that. I'd have thought since postgresql already caches stuff in shared buffers, the atime of a postgresql file isn't reliable anyway; and outside of postgresql O_NOATIME doesn't seem to me to affect admins any worse the existence of utime(). OTOH, I'm not going to argue for the patch either. I think it'd be fair to say adding a linuxism and only benefiting novice/casual users isn't that exciting. ---(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] Patch to allow C extension modules to initialize/finish
On Thu, Aug 03, 2006 at 05:30:48PM -0400, Tom Lane wrote: Ralf S. Engelschall [EMAIL PROTECTED] writes: Hence I propose the patch below (applies to PostgreSQL 8.1.4) which mimics the dlopen(3) and dlclose(3) behaviour of some Unix platforms and resolves and calls _PG_init and _PG_fini functions of an extension module right after/before the pg_dlopen/pg_dlclose calls in the FMGR. This seems like a reasonably good idea, and we have got uses for at least the init case in most or all of our PLs. It's nominally too late for 8.2 feature freeze, but I said just a couple days ago that we shouldn't take a very hard line on that. Does anyone object to considering this for 8.2? Nope :) One question I have is whether it really works as expected in all cases. In particular what if the library is preloaded into the postmaster? Both plpgsql and plperl seem to think they might need to make a distinction between things to do at library load time and things to do per-backend ... and yet, neither of them *actually* have anything they need to do per-backend. I'm not sure quite what you mean here, but PL/PerlU functions can use() modules, and those are called per-backend, i.e. when the function is invoked. There's also some possibility that something might go into %_SHARED. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Patch to allow C extension modules to initialize/finish
David Fetter [EMAIL PROTECTED] writes: On Thu, Aug 03, 2006 at 05:30:48PM -0400, Tom Lane wrote: One question I have is whether it really works as expected in all cases. In particular what if the library is preloaded into the postmaster? I'm not sure quite what you mean here, but PL/PerlU functions can use() modules, and those are called per-backend, i.e. when the function is invoked. There's also some possibility that something might go into %_SHARED. Well, the point is that you could have a scenario where the PG_init function is executed in the postmaster, the process image is duplicated via fork(), and then in a specific backend a LOAD command is executed causing the PG_fini function to be called. Is it likely that anything would get confused by PG_init and PG_fini getting called by different processes? Also, if we do this we probably ought to remove the special-purpose hack for preload_libraries to specify an init function --- it should just happen by default. Any objections to simplifying that? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Patch to allow C extension modules to initialize/finish
Tom Lane wrote: Ralf S. Engelschall [EMAIL PROTECTED] writes: Hence I propose the patch below (applies to PostgreSQL 8.1.4) which mimics the dlopen(3) and dlclose(3) behaviour of some Unix platforms and resolves and calls _PG_init and _PG_fini functions of an extension module right after/before the pg_dlopen/pg_dlclose calls in the FMGR. This seems like a reasonably good idea, and we have got uses for at least the init case in most or all of our PLs. It's nominally too late for 8.2 feature freeze, but I said just a couple days ago that we shouldn't take a very hard line on that. Does anyone object to considering this for 8.2? I don't. We've been porous in the past and I think we should be prepared to be a bit lenient again, especially since this release is not hugely feature rich. One question I have is whether it really works as expected in all cases. In particular what if the library is preloaded into the postmaster? Both plpgsql and plperl seem to think they might need to make a distinction between things to do at library load time and things to do per-backend ... and yet, neither of them *actually* have anything they need to do per-backend. I have longterm plans for plperl concerning preloading perl modules, which might involve the preloaded lib. At the moment it's just a thought in my head, though. Also, what about Windows? I assume that DSOs don't remain attached across the pseudo-fork/exec, will that mess anything up? Good question. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ecpg test suite
Here is my updated regression.diff. Like Tom, I was running with my server configured to run on 5678, instead of 5432, so it seems like the test is using a wrong port number somewhere. I changed my local pg_regress.sh to use -C3 on the diffs, until we figure out what the final form of that will be. BTW, I do have --enable-integer-datetimes configured for this machine, which might explain the timestamp differences. Thanks, -rocco -Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED] Sent: Thursday, August 03, 2006 9:12 AM To: Rocco Altier Cc: Michael Meskes; PostgreSQL Hacker; [EMAIL PROTECTED] Subject: Re: [HACKERS] ecpg test suite Hi, I just committed some changes by Joachim that should reduce the problems and the differences by a large margin. Could you please rerun the test and send us the regression.diff? Thanks a lot in advance. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! regression.diff Description: regression.diff ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ecpg test suite
On Thu, Aug 03, 2006 at 11:36:22AM -0400, Tom Lane wrote: The spelling we've used for many years is diff -w -C3 Is there a reason to change from that? This was my fault. When I changed the options I mixed upper and lowercase and used lowercase 'c' instead of uppercase 'C'. That should be fixed now. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Forcing current WAL file to be archived
Simon Riggs [EMAIL PROTECTED] writes: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. Just to be clear --- does this fully supersede your draft patch of 27-July, or is that still on the table too? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_terminate_backend
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: No, you have that backwards. The burden of proof is on those who want it to show that it's now safe. The situation is not different than it was before, except that we can now actually point to a specific bug that did exist, whereas the original concern was just an unfocused one that the code path hadn't been adequately exercised. That concern is now even more pressing than it was. I am not sure how you prove the non-existance of a bug. Ideas? What I'm looking for is some concentrated testing. The fact that some people once in a while SIGTERM a backend doesn't give me any confidence in it. OK, here is an opportunity for someone to run tests to get this into 8.2. The code already exists in CVS, but we need testing to enable it. I would think running a huge workload and killing it over and over again would be a good test. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] LWLock statistics collector (was: CSStorm occurred again by postgreSQL8.2)
Katsuhiko Okano [EMAIL PROTECTED] writes: (A) The algorithm which replaces a buffer is bad. A time stamp does not become new until swapout completes the swapout page. If access is during swap at other pages, the swapout page will be in the state where it is not used most, It is again chosen as the page for swapout. (When work load is high) The following is the patch. I'm confused ... is this patch being proposed for inclusion? I understood your previous message to say that it didn't help much. The patch is buggy as posted, because it will try to do this: if (shared-page_status[bestslot] == SLRU_PAGE_CLEAN) return bestslot; while bestslot could still be -1. I see your concern about multiple processes selecting the same buffer for replacement, but what will actually happen is that all but the first will block for the first one's I/O to complete using SimpleLruWaitIO, and then all of them will repeat the outer loop and recheck what to do. If they were all trying to swap in the same page this is actually optimal. If they were trying to swap in different pages then the losing processes will again try to initiate I/O on a different buffer. (They will pick a different buffer, because the guy who got the buffer will have done SlruRecentlyUsed on it before releasing the control lock --- so I don't believe the worry that we get a buffer thrash scenario here. Look at the callers of SlruSelectLRUPage not just the function itself.) It's possible that letting different processes initiate I/O on different buffers would be a win, but it might just result in excess writes, depending on the relative probability of requests for the same page vs. requests for different pages. Also, I think the patch as posted would still cause processes to gang up on the same buffer, it would just be a different one from before. The right thing would be to locate the overall-oldest buffer and return it if clean; otherwise to initiate I/O on the oldest buffer that isn't either clean or write-busy, if there is one; otherwise just do WaitIO on the oldest buffer. This would ensure that different processes try to push different buffers to disk. They'd still go back and make their decisions from the top after doing their I/O. Whether this is a win or not is not clear to me, but at least it would attack the guessed-at problem correctly. 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] [BUGS] Patch to allow C extension modules to initialize/finish
Tom Lane wrote: Also, if we do this we probably ought to remove the special-purpose hack for preload_libraries to specify an init function --- it should just happen by default. Any objections to simplifying that? The original idea of using the init function with preload_libraries was to eliminate library startup that was expensive and only needed once. Specifically in the case of libR (and presumably other libraries as well), the init time was much greater than the actual library load time. If it is removed from preload_libraries, then we'll pay that price for every backend startup, no? Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_terminate_backend
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: What I'm looking for is some concentrated testing. The fact that some people once in a while SIGTERM a backend doesn't give me any confidence in it. OK, here is an opportunity for someone to run tests to get this into 8.2. The code already exists in CVS, but we need testing to enable it. I would think running a huge workload and killing it over and over again would be a good test. Big multiprocess workload and you kill individual processes at random while letting the rest run. It probably needs to be something that stresses more of the code than pgbench would, too. (For instance, it'd be a good idea if some of the workload involved having a few 2PC transactions getting prepared and then either committed or rolled back ... SIGTERM during a COMMIT PREPARED strikes me as the sort of corner case that's probably never been exercised.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_terminate_backend
Thanks. Good plan. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: What I'm looking for is some concentrated testing. The fact that some people once in a while SIGTERM a backend doesn't give me any confidence in it. OK, here is an opportunity for someone to run tests to get this into 8.2. The code already exists in CVS, but we need testing to enable it. I would think running a huge workload and killing it over and over again would be a good test. Big multiprocess workload and you kill individual processes at random while letting the rest run. It probably needs to be something that stresses more of the code than pgbench would, too. (For instance, it'd be a good idea if some of the workload involved having a few 2PC transactions getting prepared and then either committed or rolled back ... SIGTERM during a COMMIT PREPARED strikes me as the sort of corner case that's probably never been exercised.) 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 5: don't forget to increase your free space map settings
Re: GENERATED ... AS IDENTITY, Was: Re: [HACKERS] Feature Freeze
Hi,
next version follows. Changes:
- Supports OVERRIDING { USER | SYSTEM } VALUE syntax
not yet documented, I have doubts about USER variant
- UPDATES is forbidden entirely on GENERATED ALWAYS
AS IDENTITY columns, UPDATE tab SET col = DEFAULT is
allowed on GENERATED ALWAYS AS ( expr ) columns
- ALTER TABLE tab ALTER col RESTART [WITH] N and
ALTER TABLE tab ALTER col SET identity_options are supported
but not yet documented
- extended the test case but the expected .out wasn't updated
so 1 out of 101 tests fail.
After exercising with the last one, ALTER tab RENAME to newtab
and ALTER tab RENAME col TO newcol should be easy.
With the introduced infrastructure to correctly support
the first two changes (new column attribute: attidentity)
it is be easy to implement checks to disallow
ALTER TABLE tab DROP DEFAULT on IDENTITY columns.
Best regards,
Zoltán Böszörményi
Zoltan Boszormenyi írta:
Rod Taylor írta:
For db restoration (pg_dump), how do you restore to the same values as
previously if it is always regenerated? By making ALWAYS a suggestion
for some users instead of always enforced and providing an override
mechanism for it. I assume it only works for relation owners but I've
not figured out how the spec does permissions.
override clause ::=
OVERRIDING USER VALUE
| OVERRIDING SYSTEM VALUE
In short, pg_dump should append OVERRIDING SYSTEM VALUE to any
insert or
copy for relations with an GENERATED ALWAYS identity column and the
backend will need to respect that.
Aren't INSERT and COPY distinguished in code paths?
Yes, they are separate but they also use the same permission set.
Any user can copy into a structure at any time and virtually every
restriction will be applied normally (CHECK, DEFAULT, etc.). Copy
bypasses Rules, significant parsing overhead since there is no need to
look for subselects, and possibly some triggers are bypassed. I'm fairly
sure that foreign key triggers fire.
In short, COPY doesn't bypass enforcement. GENERATED ALWAYS is part of
that enforcement and should be included in that.
If it is not included, we cannot recommend GENERATED ALWAYS for uses
like recording CURRENT_USER in an audit log since the data could be
fudged.
OK. So COPY needs an OVERRIDING close, too, not just INSERT.
In the meantime I implemented the discussed restrictions on
UPDATE for GENERATED ALWAYS columns, allowing
UPDATE tab SET col = default only for
GENERATED ALWAYS AS. I also implemented
INSERT ... OVERRIDING { SYSTEM | USER } VALUE.
If I got it correctly, OVERRIDING USER VALUE
seems to be the same as omitting the OVERRIDING clause...
ALWAYS is really only enforced for anyone who doesn't have
permission to
specify otherwise.
Another one that got me is what do you do if you do this:
CREATE TABLE tab (col integer);
INSERT INTO tab VALUES (10);
ALTER TABLE tab ALTER col GENERATED ALWAYS AS IDENTITY;
What is the value for tab.col? It would seem that the table should
be rewritten with all values for col recalculated -- thus it
would be
'1'. But wait! Can we add the override clause here too to keep
the old
values and change the enforcement for new tuples only?
I don't think we should rewrite existing rows because
when it was inserted, the stored value was valid
according to the rules at that time. What if you
have more than one rows in that table?
SERIAL has, until recently, been described as a macro. A tool for
setting things up quickly but many parts of which can be changed by hand
after-ward. It's not exactly a good source for information on how this
structure should work. For one, you can easily override the suggested
default a serial gives at any time as any user. The intention of ALWAYS
is to prevent exactly that behaviour.
I don't have an opinion on ALTER TABLE changes for this one way or the
other. It was my intention to advise that a group decision is required
and some research into what other databases do in this case. I believe
MSSQL and DB2 both implement this functionality.
I see.
I looked a bit into the TODO entry that's about
ALTER TABLE tab ALTER col RENAME newcol
should also rename the sequence. My question is:
is it legal to call pg_get_serial_sequence() from
src/backen/parser/analyze.c:transformAlterTableStmt()?
This would be the easiest way to issue an
ALTER TABLE oldseq RENAME newseq
command automatically.
And I think I found a bug in PostgreSQL.
If I do this:
create table tab1 (id serial, t text); -- creates tab1_id_seq
create table tab2 (id serial, t text) inherits (tab1); -- creates
tab2_id_seq
drop table tab1 cascade;
then tab1_id_seq gets also dropped but tab2_id_seq doesn't.
Both 8.1.4 and current 8.2CVS do this.
Oh, and one more item. These expressions have the same abilities as a
CHECK constraint for referencing other columns.
This example comes from an IBM Guide:
CREATE TABLE T1(c1 INT, c2 DOUBLE, c3
DOUBLE
Re: [HACKERS] [BUGS] Patch to allow C extension modules to initialize/finish
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: Also, if we do this we probably ought to remove the special-purpose hack for preload_libraries to specify an init function --- it should just happen by default. Any objections to simplifying that? The original idea of using the init function with preload_libraries was to eliminate library startup that was expensive and only needed once. Specifically in the case of libR (and presumably other libraries as well), the init time was much greater than the actual library load time. If it is removed from preload_libraries, then we'll pay that price for every backend startup, no? No, my thought is that you'd rename PL/R's init function to PG_init, and then it'd get called automagically without needing to assume that the DBA remembers to specify it in preload_libraries. If there's a reason *not* to do that then it'd be a strike against this whole proposal, methinks. Oh, well that sounds perfect to me. At least in the case of a procedural language handler you can easily initialize and cache anything that must be done per-backend anyway. It's the expensive but must be done at least once stuff that was a problem. As long as that happens, I'm happy. And if we eliminate a dba dependency, so much the better. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User-defined typle similar to char(length) varchar(length)
On Thu, Aug 03, 2006 at 05:04:47PM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: On Thu, Aug 03, 2006 at 04:18:53PM -0400, Tom Lane wrote: I think we could legislate that the stored typmod is the same as what the user sees (and can't be negative). The fact that it's different for some of the built-in types is a historical artifact that I'd love to get rid of. But that makes NUMERIC(x,y) impossible to represent. Well, we have to special-case INTERVAL anyway (because its cramming some truly bizarre things into typmod), and it wouldn't bother me too much to special-case NUMERIC as well. Another option is to agree on some simple rule for cramming two values into one typmod, like first one in the low half and second in the high half, and then user types could have either one or two typmod values --- but I can imagine some pretty bizarre behavior if the type is expecting one value and you enter two or vice versa. NUMERIC can finesse this because the default for scale is zero, but in the general case that wouldn't work so well. Does anyone have examples of real user-defined types that would need two fields? If not it may not be worth spending time on. I can think of histograms as a data type which may take more than one argument, maybe even an array for boundary information. I think the direction *in the long term* should be to allow multiple arguments (as a ROW type?) and other base or complex types as arguments. The value would be a type itself and the datatype must do the right thing regarding it. This may not be practical for short-term, but would open up initialization parameters for user-defined typed. --elein regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] LWLock statistics collector
Tom Lane wrote: If I thought that average users would have a need for LWLock statistics, I'd be more sympathetic to expending effort on a nice frontend for viewing the statistics, but this is and always will be just a concern for hardcore hackers ... That may be true of the output, but that's not a very strong argument against making it much easier to gather and display the LWLock statistics. I can easily imagine the patch be a useful performance troubleshooting tool in a high load environment. Depends on how easy/intrusive it is to enable/use the stderr method on a production system, though, as well as how much of a performance impact the measurements have on overall operation... -- Kevin Brown [EMAIL PROTECTED] ---(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] [PATCHES] [DOCS] Values list-of-targetlists patch for comments (was Re:
Gavin Sherry [EMAIL PROTECTED] writes: On Fri, 4 Aug 2006, Michael Glaesemann wrote: On Aug 3, 2006, at 23:58 , Tom Lane wrote: Should we give VALUES its own reference page? That doesn't quite seem helpful either. I think we should go for a separate reference page, as VALUES appears to be expanding quite a bit. ... with update? I associate it very closely with INSERT. After all, INSERT is the only statement where we've had VALUES as part of the grammar. True, but I think that's just a historical artifact. If you look at the SQL spec, INSERT ... VALUES and INSERT ... SELECT are not distinct constructs: they fall out of the fact that VALUES and SELECT are allowed interchangeably. insert statement ::= INSERT INTO table name insert columns and source insert columns and source ::= [ left paren insert column list right paren ] query expression | DEFAULT VALUES insert column list ::= column name list and when you trace down query expression you find the SELECT and VALUES options entering at exactly the same place ... I'd like to see us refactor the docs as necessary to reflect that idea. Peter is right that this needs some discussion in syntax.sgml as well as in the reference pages --- but I'm still not very clear on how the presentation should go. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] New ecpg warnings
I am seeing two new warnings from ecpg: dyntest.pgc:66: WARNING: nullable is always 1 dyntest2.pgc:72: WARNING: nullable is always 1 Are they to be expected? I looked at where they are being generated but didn't understand it. -- 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
[HACKERS] 8.2 features status
I'm not clear on why there's all this doom and gloom about how 8.2 will be merely a performance-oriented release, with few new features, eg http://archives.postgresql.org/pgsql-hackers/2006-07/msg00111.php Certainly there's been a ton of effort spent on high-end performance issues. But a quick troll through the CVS logs shows a fair number of features that could be considered killer must-have things by their respective target audiences: multi-row VALUES, not only for INSERT but everywhere SELECT is allowed pg_dump multiple -n and -t options, regex patterns for object names multi-argument aggregates, including SQL2003-standard statistical aggregates fully configurable timezone abbreviations (no more 'australian_timezones' hack) allow full zic timezone names in datetime input values support comparisons like if row(new.*) is distinct from row(old.*) DROP ... IF EXISTS numerous tsearch2 improvements, eg thesaurus GIN index opclass GRANT CONNECT ON DATABASE support SSL Certificate Revocation List (CRL) files plpython supports named parameters, composite-type results, more result-set options plperl prepared queries domain constraint checks are now applied everywhere better psql multiline command handling error cursor position displayed for many parse-analysis errors standard_conforming_strings can be turned on (HUGE deal for some people) initdb and pg_ctl can safely start from an admin account on Windows display multiline values nicely in psql support SQL-compliant row comparisons; they can be indexscan quals DROP OWNED, REASSIGN OWNED for dealing with removal of a user null elements in arrays There are also some commits that are mere performance tweaks, and yet we should not understate their importance because they could make the difference between usability and non-usability in many applications: lazy vacuums are ignored by other processes; improves behavior of concurrent vacuums add index and table storage options (currently only FILLFACTOR) stats_command_string overhead reduced to near zero, now on by default reduce locking involved in DATABASE commands, eg CREATE DATABASE no longer blocks incoming connections constraint exclusion works for UPDATE and DELETE constraint exclusion works for UNION ALL views, not only inheritance trees planner can rearrange join order for many common OUTER JOIN scenarios And that's not counting some pretty significant submitted-but-not-yet- reviewed patches (sure, some of these may get rejected, but they're all open possibilities today): online index builds bitmap index AM updatable views PL plugin patch (plpgsql debugger infrastructure) restartable recovery (allow checkpoints for a hot-standby server) INSERT/UPDATE RETURNING Not that there's anything wrong with a performance-oriented release ... but if you think that 8.2 is short on features, you'd better get ready to be disappointed by every future release. There's not all that much stuff left to do in terms of raw language features. (Of course the SQL committee keeps inventing a ton of new stuff every few years, but how much of that do you really care about?) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.2 features status
My outlook is that it isn't a lot of _new_ things that you couldn't do before, but rather improvements of existing functionality. --- Tom Lane wrote: I'm not clear on why there's all this doom and gloom about how 8.2 will be merely a performance-oriented release, with few new features, eg http://archives.postgresql.org/pgsql-hackers/2006-07/msg00111.php Certainly there's been a ton of effort spent on high-end performance issues. But a quick troll through the CVS logs shows a fair number of features that could be considered killer must-have things by their respective target audiences: multi-row VALUES, not only for INSERT but everywhere SELECT is allowed pg_dump multiple -n and -t options, regex patterns for object names multi-argument aggregates, including SQL2003-standard statistical aggregates fully configurable timezone abbreviations (no more 'australian_timezones' hack) allow full zic timezone names in datetime input values support comparisons like if row(new.*) is distinct from row(old.*) DROP ... IF EXISTS numerous tsearch2 improvements, eg thesaurus GIN index opclass GRANT CONNECT ON DATABASE support SSL Certificate Revocation List (CRL) files plpython supports named parameters, composite-type results, more result-set options plperl prepared queries domain constraint checks are now applied everywhere better psql multiline command handling error cursor position displayed for many parse-analysis errors standard_conforming_strings can be turned on (HUGE deal for some people) initdb and pg_ctl can safely start from an admin account on Windows display multiline values nicely in psql support SQL-compliant row comparisons; they can be indexscan quals DROP OWNED, REASSIGN OWNED for dealing with removal of a user null elements in arrays There are also some commits that are mere performance tweaks, and yet we should not understate their importance because they could make the difference between usability and non-usability in many applications: lazy vacuums are ignored by other processes; improves behavior of concurrent vacuums add index and table storage options (currently only FILLFACTOR) stats_command_string overhead reduced to near zero, now on by default reduce locking involved in DATABASE commands, eg CREATE DATABASE no longer blocks incoming connections constraint exclusion works for UPDATE and DELETE constraint exclusion works for UNION ALL views, not only inheritance trees planner can rearrange join order for many common OUTER JOIN scenarios And that's not counting some pretty significant submitted-but-not-yet- reviewed patches (sure, some of these may get rejected, but they're all open possibilities today): online index builds bitmap index AM updatable views PL plugin patch (plpgsql debugger infrastructure) restartable recovery (allow checkpoints for a hot-standby server) INSERT/UPDATE RETURNING Not that there's anything wrong with a performance-oriented release ... but if you think that 8.2 is short on features, you'd better get ready to be disappointed by every future release. There's not all that much stuff left to do in terms of raw language features. (Of course the SQL committee keeps inventing a ton of new stuff every few years, but how much of that do you really care about?) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] 8.2 features status
On Fri, 4 Aug 2006, Bruce Momjian wrote: My outlook is that it isn't a lot of _new_ things that you couldn't do before, but rather improvements of existing functionality. It seems as though the majority of things on Tom's list are new things you couldn't do (at all easily) before. Gavin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
Gavin Sherry wrote: On Fri, 4 Aug 2006, Bruce Momjian wrote: My outlook is that it isn't a lot of _new_ things that you couldn't do before, but rather improvements of existing functionality. It seems as though the majority of things on Tom's list are new things you couldn't do (at all easily) before. To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] VALUES clause memory optimization
Tom Lane wrote:
The reason we could safely list_free inside transformInsertRow is that
we know all its callers have just built the passed-in list and so there
are no other pointers to it. That doesn't apply in the general case of
grammar output.
What about for the specific case of an InsertStmt? It seems that we
could at least get away with freeing the raw-expression list in that case.
In terms of freeing an entire arbitrary node, could we create a
backend/nodes/freefuncs.c file that does a recursive freeObject()
similar to the way copyObject() does in backend/nodes/copyfuncs.c?
My advice is to get that low-hanging fruit
in transformInsertRow and leave the other ideas for 8.3.
OK. This should be safe also, correct?
Thanks,
Joe
8
diff -c -r1.341 analyze.c
*** src/backend/parser/analyze.c2 Aug 2006 01:59:46 - 1.341
--- src/backend/parser/analyze.c2 Aug 2006 05:13:20 -
***
*** 2191,2196
--- 2196,2202
for (i = 0; i sublist_length; i++)
{
coltypes[i] = select_common_type(coltype_lists[i], VALUES);
+ list_free(coltype_lists[i]);
}
newExprsLists = NIL;
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.2 features status
On Fri, 4 Aug 2006, Bruce Momjian wrote: Gavin Sherry wrote: On Fri, 4 Aug 2006, Bruce Momjian wrote: My outlook is that it isn't a lot of _new_ things that you couldn't do before, but rather improvements of existing functionality. It seems as though the majority of things on Tom's list are new things you couldn't do (at all easily) before. To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? Well, GIN and some of the unreviewed stuff (bitmaps, plpgsql debugger, updateable views) are in the same league as the stuff in 8.0 in terms of user demand and catching up with competitors, I think. A lot of the things on Tom's list are new bits of functionality to things added around 8.0 and 8.1 (major enhancements to the usability of constraint exclusion, for example). We knew then that these needed additional functionality to fill them out and make them useful to a wide range of people. Ideally we'd have both at each release but reality doesn't work like that. Thanks, Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] request: support of array in plperl OUT arguments
Seems Pavel has submitted the patch now, and I place it in the patch
queue.
---
David Fetter wrote:
On Fri, Jul 28, 2006 at 10:42:49AM +0200, Pavel Stehule wrote:
Hello,
I miss better support OUT arguments in plerlu:
create or replace function foo(out p varchar[]) as $$ return { p = [pavel,
jana] }; $$ language plperlu;
postgres=# select foo();
ERROR: array value must start with { or dimension information
postgres=#
I starting work on it. I hope It will be done before current feature freeze.
Regards
Pavel Stehule
It seems Pavel missed sending the preliminary patch, so here it is :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(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] Let psql process files with 4,294,967,295 lines
[ Tom's include adjustment added.] Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- David Fetter wrote: On Sun, Jul 30, 2006 at 05:40:16PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: David Fetter wrote: This patch changes the data type from unsigned int to unsigned long long, which is probably not the correct thing in order to get 64-bit arithmetic, but I figure it's good enough to get a discussion started. The only thing I can tell you is that you should use INT64_FORMAT instead of %lld. And the datatype should be declared int64, not long long which doesn't exist everywhere. Actually you probably want uint64 and UINT64_FORMAT... regards, tom lane I think this fixes it, but I'm unsure how to test it. Two of the methods mentioned in IRC, attaching with gdb and setting to a value 2^32, and setting it directly in some code, seem like OK approaches. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! [ Attachment, skipping... ] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- 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] 8.2 features status
Gavin Sherry wrote: On Fri, 4 Aug 2006, Bruce Momjian wrote: Gavin Sherry wrote: On Fri, 4 Aug 2006, Bruce Momjian wrote: My outlook is that it isn't a lot of _new_ things that you couldn't do before, but rather improvements of existing functionality. It seems as though the majority of things on Tom's list are new things you couldn't do (at all easily) before. To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? Well, GIN and some of the unreviewed stuff (bitmaps, plpgsql debugger, updateable views) are in the same league as the stuff in 8.0 in terms of user demand and catching up with competitors, I think. A lot of the things on Tom's list are new bits of functionality to things added around 8.0 and 8.1 (major enhancements to the usability of constraint exclusion, for example). We knew then that these needed additional functionality to fill them out and make them useful to a wide range of people. Ideally we'd have both at each release but reality doesn't work like that. Yes, that is my point. It is a usability release. Nothing wrong with that. In fact, some people asked me if we were still doing things for ordinary users rather than just doing enterprise functionality. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] 8.2 features status
Bruce Momjian [EMAIL PROTECTED] writes: To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? [ shrug... ] Five out of your six items have no basis in the SQL spec. So it's not clear to me what your definition of major feature is, unless maybe it's anything except what we did for 8.2. Can you enumerate ten things you would consider comparable to the above features that aren't done yet? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 features status
Bruce Momjian wrote: Gavin Sherry wrote: On Fri, 4 Aug 2006, Bruce Momjian wrote: My outlook is that it isn't a lot of _new_ things that you couldn't do before, but rather improvements of existing functionality. It seems as though the majority of things on Tom's list are new things you couldn't do (at all easily) before. To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? Well to be honest, the things that are coming in 8.2 more people will use then any of the things you just mentioned. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? [ shrug... ] Five out of your six items have no basis in the SQL spec. So it's not clear to me what your definition of major feature is, unless maybe it's anything except what we did for 8.2. Can you enumerate ten things you would consider comparable to the above features that aren't done yet? No, I cannot. I do think our missing list is shrinking. My point is that you really couldn't easily work around the 8.0/8.1 items I listed if they were missing, while the 8.2 items could be more easily worked-around. Again, nothing wrong with that. -- 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] 8.2 features status
Joshua D. Drake wrote: Bruce Momjian wrote: Gavin Sherry wrote: On Fri, 4 Aug 2006, Bruce Momjian wrote: My outlook is that it isn't a lot of _new_ things that you couldn't do before, but rather improvements of existing functionality. It seems as though the majority of things on Tom's list are new things you couldn't do (at all easily) before. To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? Well to be honest, the things that are coming in 8.2 more people will use then any of the things you just mentioned. Right, hence usability, not new enterprise features. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] VALUES clause memory optimization
Joe Conway [EMAIL PROTECTED] writes: What about for the specific case of an InsertStmt? It seems that we could at least get away with freeing the raw-expression list in that case. Not sure ... what about rules, BETWEEN, yadda yadda? In terms of freeing an entire arbitrary node, could we create a backend/nodes/freefuncs.c file that does a recursive freeObject() similar to the way copyObject() does in backend/nodes/copyfuncs.c? We got rid of freefuncs.c years ago, for good and sufficient reasons that have not gone away. The problem is exactly that you don't know whether any shortcuts were taken in constructing the node tree: multiple links, pointers to constants, pointers to stuff that wasn't supposed to be freed are all severe hazards. My advice is to get that low-hanging fruit in transformInsertRow and leave the other ideas for 8.3. OK. This should be safe also, correct? Yes, but what's your point? The case that seems worth trying to optimize is INSERT INTO foo VALUES ... real long list Certainly the MySQL crowd is not going to be stressing transformValuesClause, because they don't know it exists. $ mysql test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 5.0.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql values (1),(2); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values (1),(2)' at line 1 mysql select * from (values (1),(2)) as x(y); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values (1),(2)) as x(y)' at line 1 mysql select * from foo where x in (values (1),(2)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1),(2))' at line 1 mysql mysql shortcomings aside, I don't really see the use-case for enormously long VALUES lists anywhere except the bulk-data-load scenario, ie, exactly INSERT ... VALUES. So I don't feel a need to be real tense in transformValuesClause. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.2 features status
It seems as though the majority of things on Tom's list are new things you couldn't do (at all easily) before. To me new things are like PITR, Win32, savepoints, two-phase commit, partitioned tables, tablespaces. These are from 8.0 and 8.1. What is there in 8.2 like that? Well to be honest, the things that are coming in 8.2 more people will use then any of the things you just mentioned. Right, hence usability, not new enterprise features. O.k. I buy that. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
