[HACKERS] Test coverage for external sorting
PostgreSQL uses two different sorting algorithms, qsort and the external sorting method in tuplesort.c. There are some possible improvements in external sorting, so I'd like to check on the solidity of the testing mechanisms. Whether external sorting can be improved upon is a different debate, though I do have reason to believe it is possible. Initially, I am interested in proving correctness of any change, though the eventual goal would be performance. I'm looking through the regression tests, but can't find anything that explicitly tests both types of sort. If you run the regression tests against an existing database instance it would be possible to run the tests with various values of work_mem so as to force the sorts to either be internal or external. ...only problem is that the largest regression test table: tenk doesn't occupy as much as 1 MB of space in total and work_mem cannot be set lower than 1 MB. Could anybody comment on whether the current tests appropriately cover the correctness of the external sorting algorithms? Will any changes to the external sort algorithms be appropriately tested by what is there currently? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] OUT parameters in PL/Java
I've read about changes in CVS head needed to accomodate OUT parameters. I tried to compile PL/Java and it fails (of course). Is there any brief text somewhere that highligts the changes that where made and explains how the new stuff works? It's hard and somewhat time consuming to try to deduct everything just by looking at the code in pl/pgsql. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ISO-8859-1 encoding not enforced?
Tom Lane said: > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> Is PostgreSQL supposed to enforce a LATIN1/ISO-8859-1 encoding if >> that's the database encoding? > > AFAIK, there are no illegal characters in 8859-1, except \0 which we do > reject. > Perhaps Chris is confusing ISO/IEC 8859-1 with ISO-8859-1 a.k.a. Latin-1. According to the wikipedia, "The IANA has approved ISO-8859-1 (note the extra hyphen), a superset of ISO/IEC 8859-1, for use on the Internet. This character map, or character set or code page, supplements the assignments made by ISO/IEC 8859-1, mapping control characters to code values 00-1F, 7F, and 80-9F. It thus provides for 256 characters via every possible 8-bit value. [snip] The name Latin-1 is an informal alias [for ISO-8859-1] unrecognized by ISO or the IANA, but is perhaps meaningful in some computer software." But let's not start accepting \0 ;-) cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] SortMem...
Hi in the function ExecChooseHashTableSize() (~/src/backend/executor/nodeHash.c) are determined the bytes of the hash table. The correspondent code is: /* * Target in-memory hashtablesize in SortMem kilobytes */ hash_table_bytes = SortMem *1024L (~/src/include/miscadmin.h) extern DLLIMPORT int SortMem; What SortMem represents? Where is evaluated and what is its value? I want to create 2 hash tables. One for the inner and one for the outer rel. What should be the hash_table_bytes in that case? Thanks in advance! -martha ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] HEAD version of initdb fails on Win32
I get the following error message when I try to do an initdb with CVS HEAD on a Win32 platform: creating configuration files ... ok creating template1 database in data-head/base/1 ... FATAL: access method "btree" does not support unique indexes child process was terminated by signal 1 Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Test coverage for external sorting
Simon Riggs <[EMAIL PROTECTED]> writes: > Could anybody comment on whether the current tests appropriately cover > the correctness of the external sorting algorithms? It's highly unlikely that the regression tests stress external sorts much, or that anyone would hold still for making them run long enough to do so ;-) It's not hard to create a stress test: just load a bunch of random numbers into a table and create a b-tree index on it. To check the correctness of the sort, you could CLUSTER on the index and then read out the table to see if it were now in sorted order. BTW, as for your original question about performance, the current external sort algorithm is mainly designed to conserve disk space, not to be as fast as possible. It could probably be a good bit faster if we didn't mind taking twice as much space (mainly because the physical disk access pattern would be a lot less random). But I know we will get push-back if we try to revert to doing that. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OUT parameters in PL/Java
Thomas Hallgren <[EMAIL PROTECTED]> writes: > I've read about changes in CVS head needed to accomodate OUT parameters. > I tried to compile PL/Java and it fails (of course). Is there any brief > text somewhere that highligts the changes that where made and explains > how the new stuff works? It's hard and somewhat time consuming to try to > deduct everything just by looking at the code in pl/pgsql. Could you give more details about what problem you are having? Simply recompiling an existing PL shouldn't fail (of course, it wouldn't know about OUT parameters either). 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] OUT parameters in PL/Java
Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: I've read about changes in CVS head needed to accomodate OUT parameters. I tried to compile PL/Java and it fails (of course). Is there any brief text somewhere that highligts the changes that where made and explains how the new stuff works? It's hard and somewhat time consuming to try to deduct everything just by looking at the code in pl/pgsql. Could you give more details about what problem you are having? Simply recompiling an existing PL shouldn't fail (of course, it wouldn't know about OUT parameters either). My compile failure was due to the change of proargtypes from Oid* to an oidvector. I initially thought that had something to do with OUT parameters. Some diffs on plperl helped me a bit. I found the new get_call_result_type() function. I've made some assumptions that I would like to verify the correctness of: - I assume that by using the get_call_result_type() PL/Java will not need any specific handling of functions returning OUT parameters since they are similar to functions returning a complex type. - The TupleDesc returned by the get_call_result_type() is not always reachable using an Oid. I.e. I cannot use TypeGetTupleDesc(oid, NIL) with the Form_pg_proc.prorettype of my function as the first argument. - The Form_pg_proc.pronargs denotes the number of IN parameters, i.e. its safe to access proargtypes.values[idx] with an idx ranging from 0 to pronargs - 1. Regards, Thomas Hallgren ---(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] HEAD version of initdb fails on Win32
Thomas Hallgren <[EMAIL PROTECTED]> writes: > I get the following error message when I try to do an initdb with CVS > HEAD on a Win32 platform: > creating configuration files ... ok > creating template1 database in data-head/base/1 ... FATAL: access > method "btree" does not support unique indexes > child process was terminated by signal 1 Try "make distclean" and a full rebuild. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] HEAD version of initdb fails on Win32
Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: I get the following error message when I try to do an initdb with CVS HEAD on a Win32 platform: creating configuration files ... ok creating template1 database in data-head/base/1 ... FATAL: access method "btree" does not support unique indexes child process was terminated by signal 1 Try "make distclean" and a full rebuild. Much better :-) Thanks! - thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OUT parameters in PL/Java
Thomas Hallgren <[EMAIL PROTECTED]> writes: > My compile failure was due to the change of proargtypes from Oid* to an > oidvector. I initially thought that had something to do with OUT parameters. No, not directly. The diffs needed for that are pretty simple though. > - I assume that by using the get_call_result_type() PL/Java will not > need any specific handling of functions returning OUT parameters since > they are similar to functions returning a complex type. If you use that, it will look just the same as the existing situation where you are declared to return RECORD and someone calls you with a column name/type list in FROM. Whether you want any additional smarts is up to you. > - The TupleDesc returned by the get_call_result_type() is not always > reachable using an Oid. I.e. I cannot use TypeGetTupleDesc(oid, NIL) > with the Form_pg_proc.prorettype of my function as the first argument. That was true before for the RECORD case. > - The Form_pg_proc.pronargs denotes the number of IN parameters, i.e. > its safe to access proargtypes.values[idx] with an idx ranging from 0 to > pronargs - 1. Right, pronargs/proargtypes still denote the call signature and thus only count IN (and INOUT) parameters. One thing to be a bit wary of is that when OUT arguments are present, subscripts in proargnames line up with proallargtypes not proargtypes. I dunno if you are using proargnames at all, but if you are, the code is likely to misbehave if it doesn't know that. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] HEAD version of initdb fails on Win32
Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: I get the following error message when I try to do an initdb with CVS HEAD on a Win32 platform: creating configuration files ... ok creating template1 database in data-head/base/1 ... FATAL: access method "btree" does not support unique indexes child process was terminated by signal 1 Try "make distclean" and a full rebuild. Incidentally, thanks to Jim Buttafuoco, we now have a Windows box (goose) on the buildfarm building every day, so when things look screwy you can check there to see if other people are having the same problem. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] HEAD version of initdb fails on Win32
Thomas Hallgren <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Try "make distclean" and a full rebuild. >> > Much better :-) Thanks! Always the first thing to try if you get a weird failure after CVS update. (Personally I never even try to do a partial rebuild after updating; I can overlap recompiling with doing other things, and the time wasted tracking even one false bug isn't worth it.) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] HEAD version of initdb fails on Win32
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Incidentally, thanks to Jim Buttafuoco, we now have a Windows box > (goose) on the buildfarm building every day, so when things look screwy > you can check there to see if other people are having the same problem. Indeed, I checked the buildfarm status before opining Thomas probably just needed to rebuild ;-) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] OUT parameters in PL/Java
Tom Lane wrote: If you use that, it will look just the same as the existing situation where you are declared to return RECORD and someone calls you with a column name/type list in FROM. Whether you want any additional smarts is up to you. - The TupleDesc returned by the get_call_result_type() is not always reachable using an Oid. I.e. I cannot use TypeGetTupleDesc(oid, NIL) with the Form_pg_proc.prorettype of my function as the first argument. That was true before for the RECORD case. PL/Java will not handle the RECORD case gracefully at present I'm afraid. The 8.0 compatible version will need some improvements. How is the TupleDesc obtained in case of RECORD in 8.0.x? Is it the same in 7.4? - The Form_pg_proc.pronargs denotes the number of IN parameters, i.e. its safe to access proargtypes.values[idx] with an idx ranging from 0 to pronargs - 1. Right, pronargs/proargtypes still denote the call signature and thus only count IN (and INOUT) parameters. One thing to be a bit wary of is that when OUT arguments are present, subscripts in proargnames line up with proallargtypes not proargtypes. I dunno if you are using proargnames at all, but if you are, the code is likely to misbehave if it doesn't know that. Thanks a lot. Now I know how to go about this. Seems pretty stright forward. Nice work! Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OUT parameters in PL/Java
Thomas Hallgren <[EMAIL PROTECTED]> writes: > PL/Java will not handle the RECORD case gracefully at present I'm > afraid. The 8.0 compatible version will need some improvements. How is > the TupleDesc obtained in case of RECORD in 8.0.x? Is it the same in 7.4? In 8.0 and before I think you have to look in fcinfo->resultinfo to see if an expectedDesc is supplied via a ReturnSetInfo. get_call_result_type() handles that case along with the OUT-parameters case and the returns-a- named-composite-type case, so it makes things a little easier and more consistent. You could do worse than to back-port get_call_result_type() into your older branches and just leave out the code for the OUT parameter case. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] System vs non-system casts
On Tue, Apr 12, 2005 at 08:39:09AM +0200, Michael Paesold wrote: > Tom Lane wrote: > >The other possible solution that comes to mind is to invent the notion > >that a cast has a specific owner (which arguably it should have anyway) > >and then say that "system casts" are those whose owner is the original > >superuser. > > Just my toughts: I believe it's better when cast selection does not depend > on the search_path. It seems dangerous for objects that you don't usually > qualify with a schema. With all other objects in schemas I can think of, > you can easily write the full-qualified name. > > So I vote for the latter. So casts created by the original superuser don't get dumped? That's not good IMHO. But yes, schema-qualifying casts seems weird: '123'::someschema.user_type Is that even accepted by the grammar? -- Alvaro Herrera (<[EMAIL PROTECTED]>) "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] System vs non-system casts
Alvaro Herrera wrote: On Tue, Apr 12, 2005 at 08:39:09AM +0200, Michael Paesold wrote: Tom Lane wrote: >The other possible solution that comes to mind is to invent the notion >that a cast has a specific owner (which arguably it should have anyway) >and then say that "system casts" are those whose owner is the original >superuser. Just my toughts: I believe it's better when cast selection does not depend on the search_path. It seems dangerous for objects that you don't usually qualify with a schema. With all other objects in schemas I can think of, you can easily write the full-qualified name. So I vote for the latter. So casts created by the original superuser don't get dumped? That's not good IMHO. Well perhaps there is an even better solution? But yes, schema-qualifying casts seems weird: '123'::someschema.user_type Is that even accepted by the grammar? It's the type you qualify here, not the cast, isn't it? Nevertheless don't only think about explicit casts. With implicit casts you will usually not write out a cast at all. Best Regards, Michael Paesold ---(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] System vs non-system casts
Alvaro Herrera <[EMAIL PROTECTED]> writes: > But yes, schema-qualifying casts seems weird: > '123'::someschema.user_type > Is that even accepted by the grammar? Yes, but it'd be taken as a qualification on the type name not the cast per se. Offhand I'm not sure where we could even put a schema name for the cast itself in the CAST syntax ... so that idea probably doesn't fly at all. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] System vs non-system casts
On Tue, Apr 12, 2005 at 06:38:41PM +0200, Michael Paesold wrote: > Alvaro Herrera wrote: > > >On Tue, Apr 12, 2005 at 08:39:09AM +0200, Michael Paesold wrote: > >>Tom Lane wrote: > > > >>>The other possible solution that comes to mind is to invent the > >>>notion that a cast has a specific owner (which arguably it should > >>>have anyway) and then say that "system casts" are those whose owner > >>>is the original superuser. > >> > >>Just my toughts: I believe it's better when cast selection does not > >>depend on the search_path. It seems dangerous for objects that you > >>don't usually qualify with a schema. With all other objects in > >>schemas I can think of, you can easily write the full-qualified > >>name. > >> > >>So I vote for the latter. > > > >So casts created by the original superuser don't get dumped? That's > >not good IMHO. > > Well perhaps there is an even better solution? What about the simple one of having a bool "pg_cast.castissystem" column, or something similar? > >But yes, schema-qualifying casts seems weird: > >'123'::someschema.user_type > > > >Is that even accepted by the grammar? > > It's the type you qualify here, not the cast, isn't it? Yes, sorry. I'm low on caffeine apparently. Point on implicit casts taken too. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "I personally became interested in Linux while I was dating an English major who wouldn't know an operating system if it walked up and bit him." (Val Henson) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Test coverage for external sorting
On Tue, 2005-04-12 at 10:04 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Could anybody comment on whether the current tests appropriately cover > > the correctness of the external sorting algorithms? > > It's highly unlikely that the regression tests stress external sorts > much, or that anyone would hold still for making them run long enough > to do so ;-) OK > It's not hard to create a stress test: just load a bunch of random > numbers into a table and create a b-tree index on it. To check the > correctness of the sort, you could CLUSTER on the index and then read > out the table to see if it were now in sorted order. Just checking. No point starting anything until a test is in place. Yes, they're fairly straightforward to do - I just didn't want to do it... > BTW, as for your original question about performance, the current > external sort algorithm is mainly designed to conserve disk space, > not to be as fast as possible. It could probably be a good bit faster > if we didn't mind taking twice as much space (mainly because the > physical disk access pattern would be a lot less random). But I know > we will get push-back if we try to revert to doing that. That's roughly what I'm looking into now: just scoping for the time being. Anything submitted would take the status quo as default and present other functionality as an option only. There's also some research into improved replacement selection algorithms that may soon be submitted/submittable. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] System vs non-system casts
On 2005-04-12, Alvaro Herrera <[EMAIL PROTECTED]> wrote: >> Well perhaps there is an even better solution? > > What about the simple one of having a bool "pg_cast.castissystem" > column, or something similar? Checking the OID might be sufficient if it were possible to make the OID counter restart at some value known to be greater than any datlastsysoid, rather than restarting at BootstrapOid. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] System vs non-system casts
Alvaro Herrera <[EMAIL PROTECTED]> writes: > What about the simple one of having a bool "pg_cast.castissystem" > column, or something similar? This one is sounding pretty good to me, though I'd be inclined to call it "castisbuiltin" or some such. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] System vs non-system casts
On Tue, Apr 12, 2005 at 05:04:03PM -, Andrew - Supernews wrote: > On 2005-04-12, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > >> Well perhaps there is an even better solution? > > > > What about the simple one of having a bool "pg_cast.castissystem" > > column, or something similar? > > Checking the OID might be sufficient if it were possible to make the OID > counter restart at some value known to be greater than any datlastsysoid, > rather than restarting at BootstrapOid. In fact AFAICT it does start at BootstrapObjectIdData, which is 16384 by default. -- Alvaro Herrera (<[EMAIL PROTECTED]>) Management by consensus: I have decided; you concede. (Leonard Liu) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] recovery from idiot delete error
Hello... I'm in rather dire-straits here. I just mistakenly deleted all the entries in a table and I need to recover them. I'm told that there's something I can do along the lines of "hacking up a PG to show deleted 'tuples'" ... Any help would be VERY much appreciated. --Joshua ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] System vs non-system casts
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Tue, Apr 12, 2005 at 05:04:03PM -, Andrew - Supernews wrote: >> Checking the OID might be sufficient if it were possible to make the OID >> counter restart at some value known to be greater than any datlastsysoid, >> rather than restarting at BootstrapOid. > In fact AFAICT it does start at BootstrapObjectIdData, which is 16384 by > default. This line of thought is pretty much a non-starter, because many "system" objects are created after the initial bootstrap phase, during standalone backend sessions that are not fundamentally different from ordinary operations. For instance I don't think we want a solution in which casts created as part of the information_schema couldn't be marked as builtins. (Right at the moment there don't seem to be any such casts, but certainly there might someday be a need for one.) Alvaro's "pg_cast.castissystem" idea can be made to work with this, if necessary by brute-force UPDATEs of pg_cast. Playing games with the values of OIDs can't ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] recovery from idiot delete error
On Tue, Apr 12, 2005 at 11:58:43AM -0400, [EMAIL PROTECTED] wrote: > Hello... > > I'm in rather dire-straits here. I just mistakenly deleted all the > entries in a table and I need to recover them. I'm told that there's > something I can do along the lines of "hacking up a PG to show deleted > 'tuples'" ... What version is this? In recents ones you can change src/backend/utils/time/tqual.c or just compile with -DMAKE_EXPIRED_TUPLES_VISIBLE in CFLAGS and *I think* you should be able to see deleted tuples. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "La vida es para el que se aventura" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Simplifying bootstrap OID assignment
Currently there are three mechanisms for assigning OIDs to system objects during initdb: 1. Manual assignment of OIDs in the include/catalog/*.h files. (We need to do this for objects that are cross-referenced in other DATA entries or that we have or might want #define macros for. So, though it's a PITA to do manual assignment of pg_proc and pg_operator OIDs, I don't really foresee getting rid of it.) 2. Automatic assignment of OIDs by genbki.sh during preparation of the postgres.bki file. This is triggered by an explicit "OID = 0" in a DATA entry, and the OID range 1-16383 is reserved for the purpose. I was a bit surprised earlier today to realize that this mechanism has been unused since 7.2. 3. Automatic assignment of an OID by heap_insert when inserting a row with no OID into a table that has OIDs. This happens e.g. when creating an index's pg_class row. Since the OID counter starts at 16384 (BootstrapObjectIdData), all such OIDs are above 16k. It strikes me that mechanism #2 is redundant and may as well be removed. I made pg_cast use it earlier today, but am thinking I should revert that change. What we should do instead is start the OID counter at 1, and then boost it up to 16k at the completion of initdb. Currently, GetNewObjectId() has hardwired logic to prevent generation of OIDs less than 16k, but we could modify that code so that the limit is 10k during bootstrap or standalone operation, and 16k in normal multiuser operation. This would have the benefit that the wraparound skip would really manage to skip over every OID assigned during initdb --- currently there are several hundred OIDs just above 16k that could conflict right after a wraparound. Comments, better ideas? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Assigning fixed OIDs to system catalogs and indexes
While thinking about the use of hand-assigned OIDs for pg_proc and pg_operator, it occurred to me to wonder why we don't have hand-assigned OIDs for all system catalogs and indexes. Currently, most of the time that the C code wants to reference a specific catalog or index, it has to reference it by name. If we had fixed OIDs for all the catalogs and indexes known to the C code, we could get rid of heap_openr, index_openr, and the index-by-name maintained inside the relcache, because *all* such accesses would go by OID. I don't have hard numbers to prove it, but I think that the aggregate overhead of doing string instead of integer comparisons during those lookups has to be nontrivial. There are other annoyances such as having to use get_system_catalog_relid() in many places where a constant would be nice to have. The code wouldn't get any less readable -- we'd just be replacing macros that expand to strings with ones that expand to numbers. Thoughts? Anyone have an argument why we should not do this? 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] Assigning fixed OIDs to system catalogs and indexes
"Tom Lane" <[EMAIL PROTECTED]> writes > While thinking about the use of hand-assigned OIDs for pg_proc and > pg_operator, it occurred to me to wonder why we don't have hand-assigned > OIDs for all system catalogs and indexes. Currently, most of the time > that the C code wants to reference a specific catalog or index, it has > to reference it by name. If we had fixed OIDs for all the catalogs and > indexes known to the C code, we could get rid of heap_openr, > index_openr, and the index-by-name maintained inside the relcache, > because *all* such accesses would go by OID. I don't have hard numbers > to prove it, but I think that the aggregate overhead of doing string > instead of integer comparisons during those lookups has to be > nontrivial. There are other annoyances such as having to use > get_system_catalog_relid() in many places where a constant would be nice > to have. So some changing-oid operations like vacuum full, reindex, etc will not affect these system catalogs? Regards, Qingqing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Assigning fixed OIDs to system catalogs and indexes
So some changing-oid operations like vacuum full, reindex, etc will not affect these system catalogs? Is it possible to cluster system tables? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ISO-8859-1 encoding not enforced?
Is PostgreSQL supposed to enforce a LATIN1/ISO-8859-1 encoding if that's the database encoding? AFAIK, there are no illegal characters in 8859-1, except \0 which we do reject. Hmmm... It turns out I was confused by the developer who reported this issue. Basically they have a requirement that they only want the parts of LATIN1 that can be converted to single byte UTF8 (ie. 7bit ascii). Only about 8 of these high bit characters existed in our database, so I replaced them and put in a CHECK constraint on a few fields like this: CHECK (description = convert(description, 'ISO-8859-1', 'UTF-8')) Can I put in a request for a '7 bit ascii' encoding for PostgreSQL :) Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Assigning fixed OIDs to system catalogs and indexes
On Wed, Apr 13, 2005 at 09:54:28AM +0800, Christopher Kings-Lynne wrote: > >So some changing-oid operations like vacuum full, reindex, etc will not > >affect these system catalogs? > > Is it possible to cluster system tables? No. And then, some catalogs are only allowed to be reindexed in place. (I think only shared catalogs.) VACUUM FULL does not change the Oid, does it? -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Assigning fixed OIDs to system catalogs and indexes
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes > Is it possible to cluster system tables? > Ooops, I guess I mixed two concepts - oid and relfilenode. Those operations change relfilenode but not oids. I don't think we could cluster system tables. template1=# select oid, relfilenode from pg_class where relname = 'pg_attribute_ relid_attnam_index'; oid | relfilenode ---+- 16688 | 17239 (1 row) template1=# reindex table pg_attribute; REINDEX template1=# select oid, relfilenode from pg_class where relname = 'pg_attribute_ relid_attnam_index'; oid | relfilenode ---+- 16688 | 17241 (1 row) template1=# cluster pg_attribute_relid_attnam_index on pg_attribute; ERROR: "pg_attribute" is a system catalog Regards, Qingqing ---(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] Assigning fixed OIDs to system catalogs and indexes
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> So some changing-oid operations like vacuum full, reindex, etc will not >> affect these system catalogs? > Is it possible to cluster system tables? No, and yes. CLUSTER, REINDEX, and similar things change the relfilenode, not the relation OID. If you are looking for things that will fundamentally break the system, try renaming one of the indexes of a core system catalog ;-). We could avoid that dependency if we went by OIDs instead. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Assigning fixed OIDs to system catalogs and indexes
"Qingqing Zhou" <[EMAIL PROTECTED]> writes:
> template1=# cluster pg_attribute_relid_attnam_index on pg_attribute;
> ERROR: "pg_attribute" is a system catalog
That error has nothing to do with any risk of reassigning OIDs. The
issue is whether we can change the index's relfilenode or not --- the
error is actually coming from here:
/*
* Disallow clustering system relations. This will definitely NOT
* work for shared relations (we have no way to update pg_class rows
* in other databases), nor for nailed-in-cache relations (the
* relfilenode values for those are hardwired, see relcache.c). It
* might work for other system relations, but I ain't gonna risk it.
*/
if (IsSystemRelation(OldHeap))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("\"%s\" is a system catalog",
RelationGetRelationName(OldHeap;
AFAIK it would work for cases not explained in the comment, but it's
not been tested.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] ISO-8859-1 encoding not enforced?
On Wed, Apr 13, 2005 at 10:10:32AM +0800, Christopher Kings-Lynne wrote: > Can I put in a request for a '7 bit ascii' encoding for PostgreSQL :) Given all the problems with unwanted recoding I've seen, I think such an encoding should be the default instead of unchecked-8-bits SQL_ASCII :-( -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Amanece. (Ignacio Reyes) El Cerro San Cristóbal me mira, cínicamente, con ojos de virgen" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ISO-8859-1 encoding not enforced?
Given all the problems with unwanted recoding I've seen, I think such an encoding should be the default instead of unchecked-8-bits SQL_ASCII :-( I agree, but that would be a nightmare of backwards compaitibility :D Chris ---(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] OUT parameters in PL/Java
Thomas Hallgren <[EMAIL PROTECTED]> writes: > ... The only thing that doesn't work > right now is a function that returns RECORD (not SETOF) since the rsinfo > in this case is NULL. Can you shed some light on that? What's the test case exactly? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] OUT parameters in PL/Java
Tom Lane wrote: You could do worse than to back-port get_call_result_type() into your older branches and just leave out the code for the OUT parameter case. Great advice! I went ahead and did just that. Now PL/Java handles IN/INOUT/OUT parameters correctly with 8.1 and it handles functions returning SETOF RECORD in all versions. The only thing that doesn't work right now is a function that returns RECORD (not SETOF) since the rsinfo in this case is NULL. Can you shed some light on that? Regards, Thomas Hallgren ---(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] OUT parameters in PL/Java
Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: ... The only thing that doesn't work right now is a function that returns RECORD (not SETOF) since the rsinfo in this case is NULL. Can you shed some light on that? What's the test case exactly? thhal=# create function javatest.recordExample(int, int) returns record as 'org.postgresql.pljava.example.ComplexReturn.complexReturn' immutable language java; CREATE FUNCTION thhal=# select * from javatest.recordExample(3, 4) as (foo int, bar int, baz timestamptz); ERROR: could not determine row description for function returning record (the error occurs since I make an attempt to fetch by Oid when the TupleDesc is NULL. Oid in this case is RECORDOID). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
