Re: [HACKERS] Missing file from CVS?
Joe, I've been told by Tom Lane that the problem is related to having Perl working, so I'm assuming theres a change that needs to go into the win32 makefile that builds this file using perl. I'm going to have a go at finding the relevant commands and create a patch. I've also attached the output of a CVS update and a compile so you can see where my problem comes from. The compile error is near the bottom of this text and reads; help.c(31) : fatal error C1083: Cannot open include file: 'sql_help.h': No such file or directory Regards, Al. C:\Projects\pgsql\srccvs update -dP cvs server: Updating . cvs server: Updating backend cvs server: Updating backend/access cvs server: Updating backend/access/common cvs server: Updating backend/access/gist cvs server: Updating backend/access/hash cvs server: Updating backend/access/heap cvs server: Updating backend/access/index cvs server: Updating backend/access/nbtree cvs server: Updating backend/access/rtree cvs server: Updating backend/access/transam cvs server: Updating backend/bootstrap cvs server: Updating backend/catalog cvs server: Updating backend/commands cvs server: Updating backend/commands/_deadcode cvs server: Updating backend/executor cvs server: Updating backend/executor/_deadcode cvs server: Updating backend/include cvs server: Updating backend/lib cvs server: Updating backend/libpq cvs server: Updating backend/main cvs server: Updating backend/nodes cvs server: Updating backend/optimizer cvs server: Updating backend/optimizer/geqo cvs server: Updating backend/optimizer/path cvs server: Updating backend/optimizer/path/_deadcode cvs server: Updating backend/optimizer/plan cvs server: Updating backend/optimizer/prep cvs server: Updating backend/optimizer/prep/_deadcode cvs server: Updating backend/optimizer/util cvs server: Updating backend/parser cvs server: Updating backend/po cvs server: Updating backend/port cvs server: Updating backend/port/BSD44_derived cvs server: Updating backend/port/aix cvs server: Updating backend/port/alpha cvs server: Updating backend/port/beos cvs server: Updating backend/port/bsdi cvs server: Updating backend/port/bsdi_2_1 cvs server: Updating backend/port/common cvs server: Updating backend/port/darwin cvs server: Updating backend/port/dgux cvs server: Updating backend/port/dynloader cvs server: Updating backend/port/hpux cvs server: Updating backend/port/i386_solaris cvs server: Updating backend/port/irix5 cvs server: Updating backend/port/linux cvs server: Updating backend/port/linux/asm cvs server: Updating backend/port/linux_alpha cvs server: Updating backend/port/linux_i386 cvs server: Updating backend/port/linuxalpha cvs server: Updating backend/port/next cvs server: Updating backend/port/nextstep cvs server: Updating backend/port/qnx cvs server: Updating backend/port/qnx4 cvs server: Updating backend/port/sco cvs server: Updating backend/port/sparc cvs server: Updating backend/port/sparc_solaris cvs server: Updating backend/port/sunos4 cvs server: Updating backend/port/svr4 cvs server: Updating backend/port/tas cvs server: Updating backend/port/ultrix4 cvs server: Updating backend/port/univel cvs server: Updating backend/port/win32 cvs server: Updating backend/port/win32/regex cvs server: Updating backend/port/win32/sys cvs server: Updating backend/postmaster cvs server: Updating backend/regex cvs server: Updating backend/rewrite cvs server: Updating backend/storage cvs server: Updating backend/storage/buffer cvs server: Updating backend/storage/file cvs server: Updating backend/storage/freespace cvs server: Updating backend/storage/ipc cvs server: Updating backend/storage/large_object cvs server: Updating backend/storage/lmgr cvs server: Updating backend/storage/page cvs server: Updating backend/storage/smgr cvs server: Updating backend/tcop cvs server: Updating backend/tioga cvs server: Updating backend/utils cvs server: Updating backend/utils/adt cvs server: Updating backend/utils/cache cvs server: Updating backend/utils/error cvs server: Updating backend/utils/fmgr cvs server: Updating backend/utils/hash cvs server: Updating backend/utils/init cvs server: Updating backend/utils/mb cvs server: Updating backend/utils/mb/Unicode cvs server: Updating backend/utils/mb/conversion_procs cvs server: Updating backend/utils/mb/conversion_procs/ascii_and_mic cvs server: Updating backend/utils/mb/conversion_procs/cyrillic_and_mic cvs server: Updating backend/utils/mb/conversion_procs/euc_cn_and_mic cvs server: Updating backend/utils/mb/conversion_procs/euc_jp_and_sjis cvs server: Updating backend/utils/mb/conversion_procs/euc_kr_and_mic cvs server: Updating backend/utils/mb/conversion_procs/euc_tw_and_big5 cvs server: Updating backend/utils/mb/conversion_procs/latin2_and_win1250 cvs server: Updating backend/utils/mb/conversion_procs/latin_and_mic cvs server: Updating backend/utils/mb/conversion_procs/utf8_and_ascii cvs server: Updating backend/utils/mb/conversion_procs/utf8_and_big5 cvs server: Updating
Re: [HACKERS] SQL99 compatibility list
Christopher Kings-Lynne writes: Seems there's a few errors in the SQL99 compatibility list. For one, it says we support WITH CHECK OPTION on views which I'm pretty sure we don't. I've gone through the list and made some corrections. It makes for a nice to-do list now (with the possible exception of the embedded COBOL interface). -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] pg_stat_database shows userid as OID
Hello hackers, In the pg_stat_activity view, the usesysid is shown as having type Oid. However pg_shadow says it's an integer. Is there a reason? Looks like a bug. This patch seems to corrects this issue, but I don't know if there's something else involved. Index: src/include/catalog/pg_proc.h === RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v retrieving revision 1.276 diff -c -r1.276 pg_proc.h *** src/include/catalog/pg_proc.h 2002/11/08 17:27:03 1.276 --- src/include/catalog/pg_proc.h 2002/11/16 23:18:44 *** *** 2738,2744 DESCR(Statistics: PID of backend); DATA(insert OID = 1938 ( pg_stat_get_backend_dbidPGNSP PGUID 12 f f t f s 1 26 23 pg_stat_get_backend_dbid - _null_ )); DESCR(Statistics: Database ID of backend); ! DATA(insert OID = 1939 ( pg_stat_get_backend_userid PGNSP PGUID 12 f f t f s 1 26 23 pg_stat_get_backend_userid - _null_ )); DESCR(Statistics: User ID of backend); DATA(insert OID = 1940 ( pg_stat_get_backend_activityPGNSP PGUID 12 f f t f s 1 25 23 pg_stat_get_backend_activity - _null_ )); DESCR(Statistics: Current query of backend); --- 2738,2744 DESCR(Statistics: PID of backend); DATA(insert OID = 1938 ( pg_stat_get_backend_dbidPGNSP PGUID 12 f f t f s 1 26 23 pg_stat_get_backend_dbid - _null_ )); DESCR(Statistics: Database ID of backend); ! DATA(insert OID = 1939 ( pg_stat_get_backend_userid PGNSP PGUID 12 f f t f s 1 23 23 pg_stat_get_backend_userid - _null_ )); DESCR(Statistics: User ID of backend); DATA(insert OID = 1940 ( pg_stat_get_backend_activityPGNSP PGUID 12 f f t f s 1 25 23 pg_stat_get_backend_activity - _null_ )); DESCR(Statistics: Current query of backend); Index: src/backend/utils/adt/pgstatfuncs.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/pgstatfuncs.c,v retrieving revision 1.8 diff -c -r1.8 pgstatfuncs.c *** src/backend/utils/adt/pgstatfuncs.c 2002/08/20 04:47:52 1.8 --- src/backend/utils/adt/pgstatfuncs.c 2002/11/16 23:18:44 *** *** 272,278 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! PG_RETURN_OID(beentry-userid); } --- 272,278 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) PG_RETURN_NULL(); ! PG_RETURN_INT32(beentry-userid); } -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El miedo atento y previsor es la madre de la seguridad (E. Burke) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Debian build prob
On Thu, Nov 14, 2002 at 08:55:22PM +, Patrick Welche wrote: Believe it or not, I'm trying to compile today's cvs pgsql on a Debian 2.2.19 system. Compilation dies while compiling pg_dump with ../../../src/interfaces/libpq/libpq.so: undefined reference to `atexit' In the mail archives there is a mention of upgrading libc to libc6-dev_2.2.5-3_i386.deb. As far as I can tell, that should read libc6_2.2.5-3_i386.deb, and again AFAICT this system already has libc6_2.2.5-6_i386.deb on it. I can see atexit is undefined in libpq, and it is defined in /usr/lib/libc.a. For some reason /lib/libc*.so are stripped, so it is hard to tell, but I assume it must be the same as for /usr/lib/libc.a. Have any of you managed to compile postgresql on an oldstable Debian system? Adam Buraczewski tells me its a linux i386 gcc=2.95.3 problem. Upgrading gcc to gcc version 2.95.4 20011002 (Debian prerelease) yielded a working postgresql! PostgreSQL 7.4devel on i686-pc-linux-gnu, compiled by GCC 2.95.4 Cheers, Patrick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_stat_database shows userid as OID
Alvaro Herrera [EMAIL PROTECTED] writes: In the pg_stat_activity view, the usesysid is shown as having type Oid. However pg_shadow says it's an integer. Is there a reason? There's been disagreement for a long time over whether userids should be OIDs or ints. If you want to introduce consistency then it's going to take a lot more than a one-line patch. (First you'll need to convince the partisans involved which answer is the right one.) Looks like a bug. Not as long as OID is 4 bytes. I'd recommend not making any piecemeal changes, especially not when there's not yet a consensus which way to converge. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] btree shrinking again
Alvaro Herrera [EMAIL PROTECTED] writes: + Deletions are handled by getting a super-exclusive lock on the target page, so that no other backend has a pin on the page when the deletion starts. This means no scan is pointing at the page. This is OK for deleting leaf items, probably not OK for deleting internal nodes; will need to think harder when it's time to support index compaction. In what cases is not OK to delete an item from an internal node, holding a super-exclusive lock? I believe the thing I was worried about when I wrote that note was the stack of ancestor pointers maintained by an insert operation: the insert will not have pins on those pages, but might try to return to them later (to service a page split). A simple-minded solution might be to keep the pins until the insert is done, but you'd have to think about possible deadlock conditions as well as loss of concurrency. I'd prefer to find a solution that didn't require that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] DECLARE CURSOR
snpe [EMAIL PROTECTED] writes: When I call DECLARE CURSOR out of transaction command success, but cursor is not created Reference manual say that this get error : ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks Oops. I removed that test on 21-Oct as part of this fix: 2002-10-21 18:06 tgl * src/: backend/access/transam/xact.c, backend/catalog/heap.c, backend/catalog/index.c, backend/commands/dbcommands.c, backend/commands/indexcmds.c, backend/commands/tablecmds.c, backend/commands/vacuum.c, backend/parser/analyze.c, include/access/xact.h: Fix places that were using IsTransactionBlock() as an (inadequate) check that they'd get to commit immediately on finishing. There's now a centralized routine PreventTransactionChain() that implements the necessary tests. My reasons for removing it were (a) it was in the wrong place (analyze.c is not the right place to test execution-time constraints), and (b) it was the wrong test: the test as written was just IsTransactionBlock(), which is wrong in the case of autocommit-off, since a DECLARE CURSOR will start a new transaction perfectly well. Another objection is that inside a function call, it ought to be legal to do DECLARE CURSOR even if we're not in a transaction block, since the function might intend to use the cursor itself before returning. I think I had intended to put together an alternative test that only complained about interactive DECLARE CURSOR and understood about autocommit, but I forgot. At this point we can either add the fixed-up error check (meaning RC1 won't be the release after all), or change the documentation. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Getting float8 data into cube?
For 7.4 I would like to add a function for importing float8 values into cube. But because the cube data type is variable length I am not sure what a good approach would be. Currently this can be poorly done using text as an intermediate type. As far as I can tell functions can't take sets as arguments. Arrays seem to suffer from similar problems as the cube type, so that preloading an array with the output of a few calculations isn't particularly easy unless you use text as an intermediate type. One possibility would be to have a function that adds one dimension on to an existing cube. This could be used recursively to build up a cube with desired number of dimensions. It may not gain much in speed, but would be more accurrate without having to adjust extra_float_digits. Is there some better approach that I have overlooked? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_stat_database shows userid as OID
On Sun, Nov 17, 2002 at 01:16:29PM -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: In the pg_stat_activity view, the usesysid is shown as having type Oid. However pg_shadow says it's an integer. Is there a reason? There's been disagreement for a long time over whether userids should be OIDs or ints. If you want to introduce consistency then it's going to take a lot more than a one-line patch. (First you'll need to convince the partisans involved which answer is the right one.) Oh, I see. I wasn't aware of this. I don't really know which answer is the right one. I don't care a lot about this thing either, but I'll keep it into my list of amusements, and will probably even dig into the archives sometime. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Criptografía: Poderosa técnica algorítmica de codificación que es empleada en la creación de manuales de computadores. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Getting float8 data into cube?
Bruno Wolff III [EMAIL PROTECTED] writes: For 7.4 I would like to add a function for importing float8 values into cube. But because the cube data type is variable length I am not sure what a good approach would be. I'm not clear on what you want to accomplish. How are you expecting the source data to be structured? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_stat_database shows userid as OID
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: In the pg_stat_activity view, the usesysid is shown as having type Oid. However pg_shadow says it's an integer. Is there a reason? There's been disagreement for a long time over whether userids should be OIDs or ints. If you want to introduce consistency then it's going to take a lot more than a one-line patch. (First you'll need to convince the partisans involved which answer is the right one.) Looks like a bug. Not as long as OID is 4 bytes. I'd recommend not making any piecemeal changes, especially not when there's not yet a consensus which way to converge. Well, seems we should make it consistent at least. Let's decide and make it done. I think some wanted it to be an int so they could use the same unix uid for pg_shadow, but I think we aren't using that idea much anymore. However, right now, it looks like the super user is '1', and other users start numbering from 100. That at least suggests int rather than oid. I am not particular in what we choose, but I do think there is a good argument to make it consistent. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] CLUSTER ALL syntax
In looking at the CLUSTER ALL patch I have applied, I am now wondering why the ALL keyword is used. When we do VACUUM, we don't use ALL. VACUUM vacuums all tables. Shouldn't' CLUSTER alone do the same thing. And what about REINDEX? That seems to have a different syntax from the other two. Seems there should be some consistency. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_stat_database shows userid as OID
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I'd recommend not making any piecemeal changes, especially not when there's not yet a consensus which way to converge. Well, seems we should make it consistent at least. I think the original argument stemmed from the idea that we ought to use pg_shadow's OID column as the user identifier (eliminating usesysid per se). This seems like a good idea at first but I think it has a couple of fatal problems: * disappearance of pg_shadow.usesysid column will doubtless break some applications * if we use OID then it's much more difficult to support explicit assignment of userid I think some wanted it to be an int so they could use the same unix uid for pg_shadow, but I think we aren't using that idea much anymore. I don't think anyone worries about making usesysid match /etc/passwd anymore, but nonetheless CREATE USER WITH SYSID is still an essential capability. What if you drop a user accidentally while he still owns objects? You *must* be able to recreate him with the same sysid as before. pg_depend cannot save us from this kind of mistake, either, since users span databases. So it seems to me that we must keep pg_shadow.usesysid as a separate column and not try to make it the OID of pg_shadow. Given that decision, the argument for making it be type OID seems very weak, so I'd lean to the use int4 camp myself. But I'm not sure everyone agrees. I think Peter was strongly in favor of OID when he was revising the session-authorization code (that's why it all uses OID for user IDs...) As far as the actual C code goes, I'd lean to creating new typedefs UserId and GroupId (or some such names) and making all the routine and variable declarations use those, and not either OID or int4. But I'm not excited about promoting these typedefs into actual SQL types, as was done for TransactionId and CommandId; the payback seems much less than the effort needed. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CLUSTER ALL syntax
Bruce Momjian [EMAIL PROTECTED] writes: In looking at the CLUSTER ALL patch I have applied, I am now wondering why the ALL keyword is used. When we do VACUUM, we don't use ALL. VACUUM vacuums all tables. Shouldn't' CLUSTER alone do the same thing. I agree, lose the ALL. And what about REINDEX? That seems to have a different syntax from the other two. Seems there should be some consistency. We don't have a REINDEX ALL, and I'm not in a hurry to invent one. (Especially, I'd not want to see Alvaro spending time on that instead of fixing the underlying btree-compaction problem ;-)) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CLUSTER ALL syntax
On Sun, Nov 17, 2002 at 04:42:01PM -0500, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: In looking at the CLUSTER ALL patch I have applied, I am now wondering why the ALL keyword is used. When we do VACUUM, we don't use ALL. VACUUM vacuums all tables. Shouldn't' CLUSTER alone do the same thing. I agree, lose the ALL. Well, in my original patch (the one submitted just when 7.3 was going into beta) there was no ALL. I decided to put it in for subsequent patches for no good reason. And what about REINDEX? That seems to have a different syntax from the other two. Seems there should be some consistency. We don't have a REINDEX ALL, and I'm not in a hurry to invent one. (Especially, I'd not want to see Alvaro spending time on that instead of fixing the underlying btree-compaction problem ;-)) Actually, I'm planning to do the freelist thing, then the btree compaction and then replace the current REINDEX code with the compaction code, probably including some means to do REINDEX ALL. It makes me really proud to hear such a note of confidence in my work. Thank you very much. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, Gee, Officer Krupke) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] CLUSTER ALL syntax
Alvaro Herrera [EMAIL PROTECTED] writes: Actually, I'm planning to do the freelist thing, then the btree compaction and then replace the current REINDEX code with the compaction code, probably including some means to do REINDEX ALL. Uh ... no. The primary purpose of REINDEX is to recover from corrupted indexes, so it has to be based on a rebuild strategy not a compaction strategy. If you want to add a REINDEX ALL for completeness, go ahead, but I think the need for it will be vanishingly small once vacuum compacts btrees properly. regards, tom lane ---(end of broadcast)--- TIP 3: 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_stat_database shows userid as OID
I totally agree with what you have said. Peter, can you clarify your reasoning for OID for user/group id? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I'd recommend not making any piecemeal changes, especially not when there's not yet a consensus which way to converge. Well, seems we should make it consistent at least. I think the original argument stemmed from the idea that we ought to use pg_shadow's OID column as the user identifier (eliminating usesysid per se). This seems like a good idea at first but I think it has a couple of fatal problems: * disappearance of pg_shadow.usesysid column will doubtless break some applications * if we use OID then it's much more difficult to support explicit assignment of userid I think some wanted it to be an int so they could use the same unix uid for pg_shadow, but I think we aren't using that idea much anymore. I don't think anyone worries about making usesysid match /etc/passwd anymore, but nonetheless CREATE USER WITH SYSID is still an essential capability. What if you drop a user accidentally while he still owns objects? You *must* be able to recreate him with the same sysid as before. pg_depend cannot save us from this kind of mistake, either, since users span databases. So it seems to me that we must keep pg_shadow.usesysid as a separate column and not try to make it the OID of pg_shadow. Given that decision, the argument for making it be type OID seems very weak, so I'd lean to the use int4 camp myself. But I'm not sure everyone agrees. I think Peter was strongly in favor of OID when he was revising the session-authorization code (that's why it all uses OID for user IDs...) As far as the actual C code goes, I'd lean to creating new typedefs UserId and GroupId (or some such names) and making all the routine and variable declarations use those, and not either OID or int4. But I'm not excited about promoting these typedefs into actual SQL types, as was done for TransactionId and CommandId; the payback seems much less than the effort needed. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CLUSTER ALL syntax
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: In looking at the CLUSTER ALL patch I have applied, I am now wondering why the ALL keyword is used. When we do VACUUM, we don't use ALL. VACUUM vacuums all tables. Shouldn't' CLUSTER alone do the same thing. I agree, lose the ALL. Good. I can take care of that or someone can submit a patch. And what about REINDEX? That seems to have a different syntax from the other two. Seems there should be some consistency. We don't have a REINDEX ALL, and I'm not in a hurry to invent one. (Especially, I'd not want to see Alvaro spending time on that instead of fixing the underlying btree-compaction problem ;-)) My point for REINDEX was a little different. The man pages shows: REINDEX { DATABASE | TABLE | INDEX } replaceable class=PARAMETERname/replaceable [ FORCE ] where we don't have ALL but we do have DATABASE. Do we need that tri-valued secodn field for reindex because you can reindex a table _or_ and index, and hence DATABASE makes sense? I am just asking. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] DECLARE CURSOR
Let's just fix it and roll an RC2 with the fix. If not, we can just fix it in 7.3.1 but I see little problem in rolling an RC2. --- Tom Lane wrote: snpe [EMAIL PROTECTED] writes: When I call DECLARE CURSOR out of transaction command success, but cursor is not created Reference manual say that this get error : ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks Oops. I removed that test on 21-Oct as part of this fix: 2002-10-21 18:06 tgl * src/: backend/access/transam/xact.c, backend/catalog/heap.c, backend/catalog/index.c, backend/commands/dbcommands.c, backend/commands/indexcmds.c, backend/commands/tablecmds.c, backend/commands/vacuum.c, backend/parser/analyze.c, include/access/xact.h: Fix places that were using IsTransactionBlock() as an (inadequate) check that they'd get to commit immediately on finishing. There's now a centralized routine PreventTransactionChain() that implements the necessary tests. My reasons for removing it were (a) it was in the wrong place (analyze.c is not the right place to test execution-time constraints), and (b) it was the wrong test: the test as written was just IsTransactionBlock(), which is wrong in the case of autocommit-off, since a DECLARE CURSOR will start a new transaction perfectly well. Another objection is that inside a function call, it ought to be legal to do DECLARE CURSOR even if we're not in a transaction block, since the function might intend to use the cursor itself before returning. I think I had intended to put together an alternative test that only complained about interactive DECLARE CURSOR and understood about autocommit, but I forgot. At this point we can either add the fixed-up error check (meaning RC1 won't be the release after all), or change the documentation. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] [GENERAL] DECLARE CURSOR
Bruce Momjian [EMAIL PROTECTED] writes: Let's just fix it and roll an RC2 with the fix. If not, we can just fix it in 7.3.1 but I see little problem in rolling an RC2. Since Marc hasn't yet announced RC1, I think we could get away with just a quick fix and re-roll of RC1 ... regards, tom lane --- Tom Lane wrote: snpe [EMAIL PROTECTED] writes: When I call DECLARE CURSOR out of transaction command success, but cursor is not created Reference manual say that this get error : ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks Oops. I removed that test on 21-Oct as part of this fix: 2002-10-21 18:06 tgl * src/: backend/access/transam/xact.c, backend/catalog/heap.c, backend/catalog/index.c, backend/commands/dbcommands.c, backend/commands/indexcmds.c, backend/commands/tablecmds.c, backend/commands/vacuum.c, backend/parser/analyze.c, include/access/xact.h: Fix places that were using IsTransactionBlock() as an (inadequate) check that they'd get to commit immediately on finishing. There's now a centralized routine PreventTransactionChain() that implements the necessary tests. My reasons for removing it were (a) it was in the wrong place (analyze.c is not the right place to test execution-time constraints), and (b) it was the wrong test: the test as written was just IsTransactionBlock(), which is wrong in the case of autocommit-off, since a DECLARE CURSOR will start a new transaction perfectly well. Another objection is that inside a function call, it ought to be legal to do DECLARE CURSOR even if we're not in a transaction block, since the function might intend to use the cursor itself before returning. I think I had intended to put together an alternative test that only complained about interactive DECLARE CURSOR and understood about autocommit, but I forgot. At this point we can either add the fixed-up error check (meaning RC1 won't be the release after all), or change the documentation. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] DECLARE CURSOR
Bruce Momjian [EMAIL PROTECTED] writes: Let's just fix it and roll an RC2 with the fix. If not, we can just fix it in 7.3.1 but I see little problem in rolling an RC2. Here is the patch I am testing (in current sources; I don't think it needs any adjustments for REL7_3, but haven't tried to apply it yet). Basically it moves the test that was originally done in parse/analyze.c into the execution-time setup of a cursor, and enlarges the test to understand about autocommit-off and inside-a-function exceptions. Anyone see a problem? regards, tom lane *** src/backend/access/transam/xact.c.orig Wed Nov 13 10:51:46 2002 --- src/backend/access/transam/xact.c Sun Nov 17 19:10:20 2002 *** *** 1488,1493 --- 1488,1537 } } + /* + *RequireTransactionChain + * + *This routine is to be called by statements that must run inside + *a transaction block, because they have no effects that persist past + *transaction end (and so calling them outside a transaction block + *is presumably an error). DECLARE CURSOR is an example. + * + *If we appear to be running inside a user-defined function, we do not + *issue an error, since the function could issue more commands that make + *use of the current statement's results. Thus this is an inverse for + *PreventTransactionChain. + * + *stmtNode: pointer to parameter block for statement; this is used in + *a very klugy way to determine whether we are inside a function. + *stmtType: statement type name for error messages. + * + */ + void + RequireTransactionChain(void *stmtNode, const char *stmtType) + { + /* +* xact block already started? +*/ + if (IsTransactionBlock()) + return; + /* +* Are we inside a function call? If the statement's parameter block +* was allocated in QueryContext, assume it is an interactive command. +* Otherwise assume it is coming from a function. +*/ + if (!MemoryContextContains(QueryContext, stmtNode)) + return; + /* +* If we are in autocommit-off mode then it's okay, because this +* statement will itself start a transaction block. +*/ + if (!autocommit !suppressChain) + return; + /* translator: %s represents an SQL statement name */ + elog(ERROR, %s may only be used in begin/end transaction blocks, +stmtType); + } + /* * transaction block support *** /home/postgres/pgsql/src/backend/tcop/pquery.c.orig Wed Sep 4 17:30:43 2002 --- /home/postgres/pgsql/src/backend/tcop/pquery.c Sun Nov 17 19:10:26 2002 *** *** 161,166 --- 161,168 /* If binary portal, switch to alternate output format */ if (dest == Remote parsetree-isBinary) dest = RemoteInternal; + /* Check for invalid context (must be in transaction block) */ + RequireTransactionChain((void *) parsetree, DECLARE CURSOR); } else if (parsetree-into != NULL) { *** /home/postgres/pgsql/src/include/access/xact.h.orig Wed Nov 13 10:52:07 2002 --- /home/postgres/pgsql/src/include/access/xact.h Sun Nov 17 19:10:13 2002 *** *** 115,120 --- 115,121 extern void UserAbortTransactionBlock(void); extern void AbortOutOfAnyTransaction(void); extern void PreventTransactionChain(void *stmtNode, const char *stmtType); + extern void RequireTransactionChain(void *stmtNode, const char *stmtType); extern void RecordTransactionCommit(void); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CLUSTER ALL syntax
On Sun, Nov 17, 2002 at 06:43:38PM -0500, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: And what about REINDEX? That seems to have a different syntax from the other two. Seems there should be some consistency. We don't have a REINDEX ALL, and I'm not in a hurry to invent one. (Especially, I'd not want to see Alvaro spending time on that instead of fixing the underlying btree-compaction problem ;-)) My point for REINDEX was a little different. The man pages shows: REINDEX { DATABASE | TABLE | INDEX } replaceable class=PARAMETERname/replaceable [ FORCE ] where we don't have ALL but we do have DATABASE. Do we need that tri-valued secodn field for reindex because you can reindex a table _or_ and index, and hence DATABASE makes sense? I am just asking. REINDEX DATABASE is for system indexes only, it's not the same that one would think of REINDEX alone (which is all indexes on all tables, isn't it?). What I don't understand is what are the parameters in the ReindexDatabase function for. For example, the boolean all is always false in tcop/utility.c (and there are no other places that the function is called). Also, the database name is checked to be equal to a constant value, the database name that the standalone backend is connected to. Why are those useful? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No renuncies a nada. No te aferres a nada ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CLUSTER ALL syntax
Alvaro Herrera [EMAIL PROTECTED] writes: What I don't understand is what are the parameters in the ReindexDatabase function for. For example, the boolean all is always false in tcop/utility.c (and there are no other places that the function is called). Also, the database name is checked to be equal to a constant value, the database name that the standalone backend is connected to. Why are those useful? Well, passing all=true would implement REINDEX ALL ... As for the database name, we could perhaps change the syntax to just REINDEX DATABASE; not sure if it's worth the trouble. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CLUSTER ALL syntax
In looking at the CLUSTER ALL patch I have applied, I am now wondering why the ALL keyword is used. When we do VACUUM, we don't use ALL. VACUUM vacuums all tables. Shouldn't' CLUSTER alone do the same thing. And what about REINDEX? That seems to have a different syntax from the other two. Seems there should be some consistency. Yeah - I agree! Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CLUSTER ALL syntax
Alvaro Herrera wrote: (B (B On Sun, Nov 17, 2002 at 06:43:38PM -0500, Bruce Momjian wrote: (B Tom Lane wrote: (B Bruce Momjian [EMAIL PROTECTED] writes: (B (BAnd what about REINDEX? That seems to have a different (Bsyntax from the other two. Seems there should be some consistency. (B (B We don't have a REINDEX ALL, and I'm not in a hurry to invent one. (B (Especially, I'd not want to see Alvaro spending time on that (B instead of fixing the underlying btree-compaction problem ;-)) (B (B My point for REINDEX was a little different. The man pages shows: (B (BREINDEX { DATABASE | TABLE | INDEX } replaceable (Bclass="PARAMETER"name/replaceable [ FORCE ] (B (B where we don't have ALL but we do have DATABASE. Do we need that (B tri-valued secodn field for reindex because you can reindex a (B table _or_ and index, and hence DATABASE makes sense? I am just (B asking. (B (B REINDEX DATABASE is for system indexes only, it's not the same that one (B would think of REINDEX alone (which is all indexes on all tables, isn't (B it?). (B (BProbably You don't understand the initial purpose of REINDEX. (BIt isn't an SQL standard at all and was intended to recover (Bcorrupted system indexes. It's essentially an unsafe operation (Band so the operation was inhibited other than under standalone (Bpostgres. I also made the command a little hard to use to avoid (Bunexpected invocations e.g. REINDEX DATABASE requires an unnecessary (Bdatabase name parameter or FORCE is still needed though it's a (Brequisite parameter now. (B (BREINDEX is also used to compact indexes now. It's good but (Bthe purpose is different from the initial one and we would (Bhave to reorganize the functionalities e.g. the table data (Bisn't needed to compact the indexes etc. (B (B What I don't understand is what are the parameters in the (B ReindexDatabase function for. For example, the boolean all (B is always false in tcop/utility.c (and there are no other (B places that the function is called). (B (BI intended to implement the *true* case also then (Bbut haven't done it yet, sorry. (B (Bregards, (BHiroshi Inoue (Bhttp://w2422.nsk.ne.jp/~inoue/ (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] DECLARE CURSOR
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Let's just fix it and roll an RC2 with the fix. If not, we can just fix it in 7.3.1 but I see little problem in rolling an RC2. Since Marc hasn't yet announced RC1, I think we could get away with just a quick fix and re-roll of RC1 ... Once Marc puts it on FTP: -rw-r--r-- 1 70 70 1073151 Nov 16 20:01 postgresql-test-7.3rc1.tar.gz I think he likes to create a new release to avoid confusion. Stamping RC2 now. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal of hierarchical queries, a la Oracle
Was there supposed to be a patch attached to this email? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Evgen Potemkin Sent: Friday, 15 November 2002 5:38 PM To: [EMAIL PROTECTED] Subject: [HACKERS] Proposal of hierarchical queries, a la Oracle Hi there! I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B I've seen this type of queries often made by adding a new type, which stores position of row in the tree. But sorting such tree are very tricky (i think). Patch allows result tree to be sorted, i.e. subnodes of each node will be sorted by ORDER BY clause. with regards, evgen --- .evgen ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FW: PostgreSQL 7.3 Platform Testing
Ports list updated: http://candle.pha.pa.us/main/writings/pgsql/sgml/supported-platforms.html --- Christopher Kings-Lynne wrote: This is a successful report for OpenBSD 3.2 on sparc and i386 -Original Message- From: bpalmer [mailto:[EMAIL PROTECTED]] Sent: Monday, 18 November 2002 2:14 AM To: Christopher Kings-Lynne Subject: Re: PostgreSQL 7.3 Platform Testing Sorry for taking so long to get back to you, getting everything working on obsd took a while. My sun is a 60mhz deal and it's a bit slow. Anywho: Kernel tweaks are needed: edited: /etc/login.conf Changed: default:\ :maxproc-max=128:\ :maxproc-cur=64:\ :openfiles-cur=64:\ to: default:\ :maxproc-max=256:\ :maxproc-cur=256:\ :openfiles-cur=256:\ Kernel settings needed: option SEMMNI=256 option SEMMNS=2048 option SEMMAXPGS=4096 Once that was done, however (and it's always been needed afaik) $ uname -an OpenBSD incelous.crimelabs.net 3.2 incelous#0 i386 == All 89 tests passed. == 356.52s real18.22s user15.92s system $ uname -an OpenBSD blackwidow.crimelabs.net 3.2 blackwidow#0 sparc == All 89 tests passed. == 1311.48s real 134.86s user 127.44s system - Brandon -- -- c: 917-697-8665h: 201-798-4983 b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] FW: PostgreSQL 7.3 Platform Testing
Ports list updated: Sure? Still says 7.2 for openbsd and has old submission date... http://candle.pha.pa.us/main/writings/pgsql/sgml/supported-platforms.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FW: PostgreSQL 7.3 Platform Testing
It updates every 15 minutes. You have to give it time. :-) --- Christopher Kings-Lynne wrote: Ports list updated: Sure? Still says 7.2 for openbsd and has old submission date... http://candle.pha.pa.us/main/writings/pgsql/sgml/supported-platforms.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster