Re: [HACKERS] Enums again
Maybe I missed it, but I didn't see any conclusion. If I want to design an Open Source system now that may be in beta in three to six months and I'd like to use enums, is this a good place to look? There's no way you're going to be using enums. I guess I'm wondering about the kit going into PgFoundry, being accepted in the main branch or being dropped. And the timeframe for all that. Time frame is more like 18 months. The kit is also very rough - not like a finished feature would be like at all. Plus, there's no guarantee the feature would ever make it into postgres. Just don't use enums - they're awful. Chris ---(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] Enums again
Just don't use enums - they're awful. In general? So, instead of using enums for order states or originating system, I'll user numbers or text? Or implement lookup tables ? ---(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] Enums again
So, instead of using enums for order states or originating system, I'll user numbers or text? Or implement lookup tables ? Use a text field and a CHECK constraint if you have just a couple of states, and a lookup table if you have many. Always use a lookup table if you plan on adding new states regularly. Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Problems with index-scan on regexp in 8.1
Am Montag, 7. November 2005 14:13 schrieb Martijn van Oosterhout: On Mon, Nov 07, 2005 at 07:50:20AM +0100, Lars Kanis wrote: We're using Postgres 8.0.2 on SuSE10.0 (64-Bit). Tests on 8.1 beta 4 have shown no problems but this one: SELECT * FROM mitglieder WHERE lower(vorname::text)='lars' does a bitmap-index-scan like this: Check your locales. For non-ASCII locales the normal shortcuts for regex optimisation can't apply. Evidently your old installation uses a different locale from your new one. You should be able to make this work by declaring your index with text_pattern_ops, like so: CREATE INDEX myindex ON mytable(mycolumn text_pattern_ops); Hope this helps, Thank you much, it helps. The initdb-locales were different. pattern_ops work quite fine with the regexps. So I don't have any complaints to 8.1. kind regards Lars Kanis pgpSc4MeqUFZ4.pgp Description: PGP signature
Re: [HACKERS] Supporting NULL elements in arrays
Hello, it's great news. My personal opinion about formating NULL values '{a,,b} -- wrong, means empty string, isn't null '{a,,b} ' -- ok, maybe not unique, '{a, NULL, b}' -- longer, clean NULL is NULL '{a, NULL, b}' -- NULL is not null varchar 'NULL' Flags for array? Maybe bit isHash? So, hash array can be in line array-null array-hash array or flag for nor regular array (sparse array), not type unique arrays (array can contains different types) Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Copy From CSV feature request?
mike wrote: I import some of my data into my postgres database, win32 platform, via the COPY table FROM with CSV. My CSV file is created from a Crystal Report (v.9). I run the report and have Crystal export the results into a CSV file (using the default settings). I have some data which looks like this when stored in the source application (m$ sql server 2000) and Crystal: Line 1000 1/1/2004 Company2 Person2 Misc Line 1001 1/1/2004 Company3 Person3 Nickname Misc Line 1002 1/1/2004 Company3 Person3 Nickname Misc Line 1003 1/1/2004 Company4 Person4 Misc Line 1004 1/1/2004 Company5 Person5 Misc When I export the report as a CSV file Crystal exports it like this: Line 1000 1/1/2004,Company2,Person2,Misc Line 1001 1/1/2004,Company3,Person3 Nickname,Misc ^^^ This is just wrong. Double-quotes inside double-quoted fields need to be escaped by doubling. As, e.g. here: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm Line 1002 1/1/2004,Company3,Person3 Nickname,Misc Line 1003 1/1/2004,Company4,Person4,Misc Line 1004 1/1/2004,Company4,Person4,Misc Now when I execute the COPY function it aborts on Line 1002 with the error extra data after last expected column. I am not sure why it does not abort on Line 1001. It's probably seeing the end-of-line as part of the last field on line 1001. If Crystal is exporting the data in the correct format should COPY FROM CSV be modified to handle an odd number of text qualifiers in a row? Does anyone know if this is a valid format (no escape character automatically inserted)? Nope - it's a bad format. Not that there is actually a standard definition of what CSV is, AFAIK. Could COPY be modified to accept the data without having to insert a someplace in the row so that the original data appears the same? How does PG determine that what you've got is an unescaped rather than a missing comma? Would it be too much of a performance hit to do this? How other applications handle it: Only Excel 2003 seems to display the data correctly. Well, it silently guesses what you want and gets it right this particular time. If I open the CSV file using OpenOffice Calc 2.0 it combines Line 1001 and Line 1002 into one row. If I import the data back into Crystal the data after Person3 does not appear. There's a sign that you've got a bug. If Crystal can't read what it writes then I'm not sure you can expect anyone else to do so reliably. If I import it into Access 2003 it ships Line 1001 and Line 1002 into an import error table. Quite right too. It's one of the areas where Access does the right thing :-) Worth adding to the TODO or not a good feature? I'm not a developer, but it strikes me as double plus ungood. -- Richard Huxton Archonet Ltd ---(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
[HACKERS] Is there any other way to compile pgsql without gmake
I want to install PostgreSQL 8.1 on FreeBSD 4.8-RELEASE . I can't make it with make command. make You must use GNU make to build PostgreSQL. *** Error code 1 Do I install gmake ?
Re: [HACKERS] parameterized limit statements
On Mon, 2005-11-07 at 18:43, Tom Lane wrote: [snip] If it doesn't have a value for the parameter, it'll assume 10% of table rows, which is what it's done for a long time if the LIMIT isn't reducible to a constant. Is 10% a reasonable guess here ? Here we use limit in combination with prepared statements to get something like less than 1% of the table. There are no exceptions to that in our code... even if the limit amount is a parameter. Furthermore, the limit amount is always a small number, usually ~ 100, but never more than 1000. So in my case, we could live with a suboptimal plan when the percentage would be more than 10%, cause then the table would be small enough not to matter that much. In turn it has a huge impact to wrongly guess 10% for a huge table... I think the best would be to guess 5% but maximum say 5000. That could work well with both small and huge tables. Maybe those values could be made configurable... just ideas, not like I could implement this... [snip] Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Hmm, did you recompile pg_sphere module for 8.1? Robert Creager wrote: When grilled further on (Mon, 7 Nov 2005 22:25:17 -0700), Robert Creager [EMAIL PROTECTED] confessed: Sorry, I'll just trickle out the information. tassiv=# \d catalog_ra_decl_index Index public.catalog_ra_decl_index Column | Type +--- loc| spherekey gist, for table public.catalog v-spl_right is address 0xbp - uninitialized? (gdb) print *v $2 = {spl_left = 0x83e1308, spl_nleft = 8, spl_ldatum = 138286880, spl_lattr = {3930298096, 3929693296, 1075344513, 3928483696, 3927878896, 50331648, 1076099872, 1076099872, 1076100640, 1076099944, 1076099872, 0, 0, 0, 1, 1076099872, 46088, 24, 138269392, 108, 8205, 1076099872, 1076097560, 1077018624, 1223005861, 2281761506, 1072462523, 8192, 1076979200, 1348122942, 3218058668, 3588489616}, spl_lattrsize = {1072628007, 1223130252, 0, -1073754968, 1223107331, -1073755008, 1196715552, 4033364, 1076979200, 8132, 32, 138269400, 58657919, 717016950, 1071875034, 1883413536, -1077677968, -817345387, 1072225709, 138175768, 138175768, 1223130252, 1223130252, -1073754936, 1223083881, 138269472, 1196715552, 138269472, 138269428, -1073754256, -1073754256, -1073754376}, spl_lisnull = ÍD#\bàÌÿ¿\000\000\000\000(Íÿ¿\2004;\b ×ÿ¿\000\000\000\000\000\000\000, spl_leftvalid = 20 '\024', spl_right = 0xdb, spl_nright = 138286924, spl_rdatum = 11, spl_rattr = {3463747944, 3883728496, 0, 3882518896, 3881914096, 1, 3221212568, 138097456, 138251092, 3878890096, 0, 0, 1222988060, 1222974760, 1222960776, 138097456, 3, 1075321604, 0, 1073825468, 1076097560, 3221212576, 3221212540, 1075326465, 3221212576, 909216680, 825503793, 0, 138251202, 1076097560, 136751593, 3221212860}, spl_rattrsize = {-1073754484, 1075303286, -1073754720, 136751593, -1073754428, 138251176, 0, -1073754560, 136027536, 1196670896, 138269580, 32, 1196670856, 138251176, 138251194, 138251202, 226, 138251008, 0, 0, 0, 7904, 1024, 138269400, 138269700, 138269688, 908, -1073754600, 13655, 138175768, 138269700, 908}, spl_risnull = \030e\b\000¼SG\001\000\000\000XÎÿ¿¤Îÿ¿\001\000\000\000 Ñÿ¿\004Ô=\b, spl_rightvalid = 108 'l', spl_idgrp = 0x83dd78c, spl_ngrp = 0x83dd378, spl_grpflag = 0x4 Address 0x4 out of bounds} When grilled further on (Mon, 7 Nov 2005 08:07:14 -0700), Robert Creager [EMAIL PROTECTED] confessed: I'm currently attached to the dead (dying) process. spl_nright seems pretty large... (gdb) print v-spl_nright $3 = 138311580 Program received signal SIGSEGV, Segmentation fault. 0x08082057 in gistUserPicksplit (r=0x48f3f1e4, entryvec=0x83e534c, v=0xbfffcbc0, itup=0x83e3454, len=227, giststate=0xbfffd120) at gistutil.c:833 833 if (v-spl_right[v-spl_nright - 1] == InvalidOffsetNumber) (gdb) bt #0 0x08082057 in gistUserPicksplit (r=0x48f3f1e4, entryvec=0x83e534c, v=0xbfffcbc0, itup=0x83e3454, len=227, giststate=0xbfffd120) at gistutil.c:833 #1 0x0807f249 in gistSplit (r=0x48f3f1e4, buffer=8917, itup=0x83e3454, len=0xbfffcea4, dist=0xbfffcea0, giststate=0xbfffd120) at gist.c:1083 #2 0x0807c8ab in gistplacetopage (state=0xbfffcf10, giststate=0xbfffd120) at gist.c:331 #3 0x0807e2cd in gistmakedeal (state=0xbfffcf10, giststate=0xbfffd120) at gist.c:878 #4 0x0807c7e1 in gistdoinsert (r=0x48f3f1e4, itup=0x83e339c, giststate=0xbfffd120) at gist.c:299 #5 0x0807c5a6 in gistbuildCallback (index=0x48f3f1e4, htup=0x83c3de8, values=0xbfffd020, isnull=0xbfffd000 , tupleIsAlive=1 '\001', state=0xbfffd120) at gist.c:207 #6 0x080cbb14 in IndexBuildHeapScan (heapRelation=0x48f3e1cc, indexRelation=0x48f3f1e4, indexInfo=0x83c3b6c, callback=0x807c4f0 gistbuildCallback, callback_state=0xbfffd120) at index.c:1573 #7 0x0807c3b5 in gistbuild (fcinfo=0xbfffe670) at gist.c:145 #8 0x08234dfd in OidFunctionCall3 (functionId=782, arg1=1223942604, arg2=1223946724, arg3=138165100) at fmgr.c:1460 #9 0x080cb8d3 in index_build (heapRelation=0x48f3e1cc, indexRelation=0x48f3f1e4, indexInfo=0x83c3b6c) at index.c:1353 #10 0x080cacdc in index_create (heapRelationId=128249, indexRelationName=0x83a0b94 catalog_ra_decl_index, indexRelationId=128443, indexInfo=0x83c3b6c, accessMethodObjectId=783, tableSpaceId=0, classObjectId=0x83c9cfc, primary=0 '\0', isconstraint=0 '\0', allow_system_table_mods=0 '\0', skip_build=0 '\0') at index.c:757 #11 0x08110671 in DefineIndex (heapRelation=0x30f, indexRelationName=0x83a0b94 catalog_ra_decl_index, indexRelationId=0, accessMethodName=0x83a0c00 gist, tableSpaceName=0x0, attributeList=0x83a0c58, predicate=0x0, rangetable=0x0, unique=0 '\0', primary=0 '\0', isconstraint=0 '\0', is_alter_table=0 '\0', check_rights=1 '\001', skip_build=0 '\0', quiet=0 '\0') at indexcmds.c:383 #12 0x081c409b in ProcessUtility (parsetree=0x83a0c74, params=0x0, dest=0x83a0cf0, completionTag=0xbfffec00 ) at utility.c:748 #13 0x081c2b84 in PortalRunUtility (portal=0x83aad14, query=0x83a0a7c, dest=0x83a0cf0, completionTag=0xbfffec00 ) at pquery.c:987 #14 0x081c2e0b
Re: [HACKERS] plperl error when making 8.2dev CVS
Jaime Casanova wrote: Hi, i was trying to compile CVS using --with-plperl (perl installed is 5.6.1) and i get this error when make go inside plperl: make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. Did you delete that file? It's part of the sources. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Is there any other way to compile pgsql without gmake
Sreejesh O S wrote: I want to install PostgreSQL 8.1 on FreeBSD 4.8-RELEASE . I can't make it with make command. make You must use GNU make to build PostgreSQL. *** Error code 1 Do I install gmake ? Yes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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
[HACKERS] Function with Variable number of parameters
Hello, Are variable number of parameters supported in the new version of Postgres? -esr- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PGXS on VPATH?
Dear Alvaro, sorry for this late response. Is PGXS on VPATH builds supported? Probably not in the way you're thinking. IIRC pgxs.mk explicitly resets VPATH, and I think it has to do that because any VPATH embedded in Makefile.global would represent what was done at the time of building the PG installation --- which has zip to do with where the PGXS-using software is located. It might work to explicitly set VPATH from the make command line: make USE_PGXS=1 VPATH=whatever as this should override the assignments in the makefiles. Indeed. There were some kind of issue with VPATH so that it was scratched when building under pgxs. ifdef PGXS # We assume that we are in src/makefiles/, so top is ... top_builddir := $(dir $(PGXS))../.. include $(top_builddir)/src/Makefile.global top_srcdir = $(top_builddir) srcdir = . VPATH = endif Maybe something clever could be done with relative VPATHs, or by trying to translate prefixes? On the other hand, I'm not a VPATH fan;-) -- Fabien. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function with Variable number of parameters
Edwin Ramirez wrote: Hello, Are variable number of parameters supported in the new version of Postgres? No, but overloading continues to be supported (which means you can create one function for each number of parameters). Functions with rowtype arguments are supported as well. True variadic functions are not supported, however. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Function with Variable number of parameters
Are variable number of parameters supported in the new version of Postgres? Hello no, there isn't way for it without modification of parser. Or you can write somethink like create function a(init, int, int, int) .. create function a(int, int, int) returns .. return a($1,$2,$3, null) Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(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] plperl error when making 8.2dev CVS
On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: Hi, i was trying to compile CVS using --with-plperl (perl installed is 5.6.1) and i get this error when make go inside plperl: fixing a bad typo in the message i try --with-perl (no --with-plperl like said above)... Just in case, someone tell me that's the error... the above was an error of typo in the message... make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. Did you delete that file? It's part of the sources. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. No... i didn't... and the file *is* in the plperl dir -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] Enums again
Christopher Kings-Lynne wrote: Maybe I missed it, but I didn't see any conclusion. If I want to design an Open Source system now that may be in beta in three to six months and I'd like to use enums, is this a good place to look? There's no way you're going to be using enums. I guess I'm wondering about the kit going into PgFoundry, being accepted in the main branch or being dropped. And the timeframe for all that. Time frame is more like 18 months. The kit is also very rough - not like a finished feature would be like at all. Plus, there's no guarantee the feature would ever make it into postgres. Just don't use enums - they're awful. Well, perhaps I should speak ;-) First, enumkit is not intended for pgfoundry or contrib. It's a byproduct of some research I was doing. It occurred to me that making what I had done generic would be very simple - the only variables in fact are the type name and the list of enumeration values. So I spent 30 minutes making it generic and enumkit was born. So of course it's rough - it is a research workproduct, not finished code. Now, my intention is to use that work product as part of allowing first class enumeration types. Currently the proposal is till in my head, but basically it would involve a fairly small set of changes. There would (probably) need to be a new unreserved keyword plus a new rule set in the grammar to allow for type creation, In the catalog, pg_type would get a new column of type text[] that would hold the list of values, and typtype would have a new possible value of 'e' for enumeration. There might be other consequential changes too, but I think that would be most of it. The only functions that actually need to have any knowledge of the enumeration strings are the input/output functions and the to/from text casts. These would get the relevant info from fcinfo.flinfo ... and then looking up the type cache - not sure yet if an extra cache operation is needed. I haven't yet worked out how to build the qsorted table that enumkit uses for bsearch lookup on input, or even whether it is worth doing. And I haven't done a line of code. Like I say it's all in my head right now. As for the timeframe - if this direction is acceptable I want to get it into 8.2. I really hope that 8.2 is not 18 months away. I think we should aim for a release cycle no longer than a year. This last cycle worked pretty well, and I think we should try to repeat it. But within 6 months ... no. So ... since Kaare asks, would a feature along the lines I outlined above be acceptable? And does anyone have an alternative proposal that they are prepared to work on? If the answer to both of these is no, then I will probably produce a patch at some stage that would be hosted on pgfoundry. But that would be far from ideal. BTW, does the standard have a way of doing this? ISTR hearing something about distinct types. If so, what should the grammar look like, and can we use it (or something close)? I'd rather not just invent syntax freely. Also, Christopher - I was somewhat motivated to work on this by your recent comment about enums being the number one demand of migrating MySQL users, so I am mildly amused by your last sentence ;-) cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Enums again
Andrew Dunstan wrote: In the catalog, pg_type would get a new column of type text[] that would hold the list of values, and typtype would have a new possible value of 'e' for enumeration. There might be other consequential changes too, but I think that would be most of it. Huh, why not have the actual values in a separate catalog like pg_enumvalues or some such? The only functions that actually need to have any knowledge of the enumeration strings are the input/output functions and the to/from text casts. These would get the relevant info from fcinfo.flinfo ... and then looking up the type cache - not sure yet if an extra cache operation is needed. It'd be interesting to measure the difference of having the cache vs. not having it. Thinking on how to pg_dump the whole thing is important too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
When grilled further on (Tue, 08 Nov 2005 15:13:32 +0300), Teodor Sigaev [EMAIL PROTECTED] confessed: Hmm, did you recompile pg_sphere module for 8.1? Yes I did. Just did it again to make sure. Is there any way I can do a make installcheck without a reconfigure/make/install of postgresql? The db is running on port 5433, not the default of 5432. If this is a PGSphere problem, should this conversation be continued there? Thanks, Rob -- 07:01:55 up 36 days, 23:36, 7 users, load average: 3.80, 3.47, 3.17 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 pgp2ezjSE3mU6.pgp Description: PGP signature
Re: [HACKERS] Enums again
Alvaro Herrera wrote: Andrew Dunstan wrote: In the catalog, pg_type would get a new column of type text[] that would hold the list of values, and typtype would have a new possible value of 'e' for enumeration. There might be other consequential changes too, but I think that would be most of it. Huh, why not have the actual values in a separate catalog like pg_enumvalues or some such? Sure, could do that. I don't have strong feelings either way. The only functions that actually need to have any knowledge of the enumeration strings are the input/output functions and the to/from text casts. These would get the relevant info from fcinfo.flinfo ... and then looking up the type cache - not sure yet if an extra cache operation is needed. It'd be interesting to measure the difference of having the cache vs. not having it. Possibly. I would expect it to make a noticeable difference. Thinking on how to pg_dump the whole thing is important too. Yes, that would certainly be part of the work. I should have mentioned that. It's not a showstopper, though - I see no reason in principal for it to be a difficulty. cheers andrew ---(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] SIGSEGV taken on 8.1 during dump/reload
Robert Creager [EMAIL PROTECTED] writes: v-spl_right is address 0xbp - uninitialized? The whole struct looks pretty uninitialized, which immediately makes me wonder whether gdb has picked up a wrong value for v. Try going down to a lower stack frame and seeing if you can access the struct from there. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [Pgsphere-dev] Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Robert Creager wrote: Yes I did. Just did it again to make sure. Is there any way I can do a make installcheck without a reconfigure/make/install of postgresql? The db is running on port 5433, not the default of 5432. export PGPORT=5433 If this is a PGSphere problem, should this conversation be continued there? PGSphere or not it's unknown for now. Can you prepare minimalist test suite reproducing problem? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Supporting NULL elements in arrays
Pavel Stehule [EMAIL PROTECTED] writes: it's great news. My personal opinion about formating NULL values '{a, NULL, b}' -- longer, clean NULL is NULL Unfortunately, that already has a meaning, and it's not that. 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] Supporting NULL elements in arrays
Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: it's great news. My personal opinion about formating NULL values '{a, NULL, b}' -- longer, clean NULL is NULL Unfortunately, that already has a meaning, and it's not that. What a pity. I don't see any alternative to the empty element proposal - it's worth the possible breakage. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
When grilled further on (Tue, 08 Nov 2005 09:20:13 -0500), Tom Lane [EMAIL PROTECTED] confessed: Robert Creager [EMAIL PROTECTED] writes: v-spl_right is address 0xbp - uninitialized? The whole struct looks pretty uninitialized, which immediately makes me wonder whether gdb has picked up a wrong value for v. Try going down to a lower stack frame and seeing if you can access the struct from there. Well, it's defined the next level up on the stack, and it's still garbage. The way I read gist.c and how it's calling gistUserPicksplit at line 1083, it's not initialized prior that else. So, FunctionCall2 in gistutil.c is supposed to fill it out? Presumably a function supplied by PGSphere in this case? (gdb) up #1 0x0807f249 in gistSplit (r=0x48df1e6c, buffer=93, itup=0x83b8e94, len=0xbfffcea4, dist=0xbfffcea0, giststate=0xbfffd120) at gist.c:1083 (gdb) print v $1 = {spl_left = 0x83bcd98, spl_nleft = 8, spl_ldatum = 138138032, spl_lattr = {138089040, 1, 1075344513, 3221212168, 134843567, 0, 1076099872, 1076099872, 1076100896, 1076099944, 1076099872, 138072532, 136595410, 138072532, 127, 64, 138072596, 137900116, 138120544, 108, 8205, 1076099872, 1076097560, 1077067776, 1222874789, 2281761506, 1072462523, 8192, 1076979200, 1348122942, 3218058668, 3588489616}, spl_lattrsize = {1072628007, 1222999180, 0, -1073754968, 1222976259, -1073755008, 1079103008, 3871912, 1076979200, 8132, 32, 138120552, 58657919, 717016950, 1071875034, 1883413536, -1077677968, -817345387, 1072225709, 138043264, 138043264, 1222999180, 1222999180, -1073754936, 1222952809, 138120624, 1079103008, 138120624, 138120580, -1073754256, -1073754256, -1073754376}, spl_lisnull = ÍD#\bàÌÿ¿\000\000\000\000(Íÿ¿0K;\b ×ÿ¿\000\000\000\000\000\000\000, spl_leftvalid = -92 '¤', spl_right = 0xdb, spl_nright = 138138076, spl_rdatum = 11, spl_rattr = {3463919764, 0, 0, 0, 0, 1, 3221212568, 138103264, 138089640, 434176, 0, 0, 1222856988, 1222843688, 1222829704, 138103264, 3, 1075321604, 0, 1073825468, 1076097560, 3221212576, 3221212540, 1075326465, 3221212576, 909186620, 825503793, 0, 138090070, 1076097560, 136751593, 3221212860}, spl_rattrsize = {-1073754484, 1075303286, -1073754720, 136751593, -1073754428, 138090044, 0, -1073754560, 136027536, 1079058352, 138120732, 32, 1079058312, 138090044, 138090062, 138090070, 226, 138089984, 0, 0, 0, 7904, 1024, 138120552, 138120852, 138120840, 908, -1073754600, 13655, 138043264, 138120852, 908}, spl_risnull = \200_:[EMAIL PROTECTED] Ñÿ¿\224\216;\b, spl_rightvalid = 108 'l', spl_idgrp = 0x83b921c, spl_ngrp = 0x83b8e08, spl_grpflag = 0x4 Address 0x4 out of bounds} (gdb) -- 07:38:26 up 37 days, 13 min, 6 users, load average: 3.28, 3.42, 3.43 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 pgpwX91hO0FtX.pgp Description: PGP signature
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Robert Creager [EMAIL PROTECTED] writes: Is there any way I can do a make installcheck without a reconfigure/make/install of postgresql? The db is running on port 5433, not the default of 5432. Sure, just export PGPORT=5433 before make installcheck. Doubt it will prove much, though, because the regression tests contain only minimal exercising of GIST. Does PGSphere itself have any regression tests? (Actually, running the contrib regression tests might be more relevant than the main PG tests, since several contrib modules with GIST opclasses have regression tests.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Enums again
Andrew Dunstan [EMAIL PROTECTED] writes: Alvaro Herrera wrote: Huh, why not have the actual values in a separate catalog like pg_enumvalues or some such? Sure, could do that. I don't have strong feelings either way. I'd vote for the separate catalog instead of bloating pg_type. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Tom Lane wrote: Robert Creager [EMAIL PROTECTED] writes: v-spl_right is address 0xbp - uninitialized? The whole struct looks pretty uninitialized, which immediately makes me wonder whether gdb has picked up a wrong value for v. Try going down to a lower stack frame and seeing if you can access the struct from there. Layout of GIST_SPLITVEC struct has been changed from 8.0, I'm afraid that old .so is used. spl_(right|left)valid fields was added to GIST_SPLITVEC. Looking into spl_leftvalid = 20 '\024', spl_right = 0xdb, spl_nright = 138286924, spl_rdatum = 11, and GIST_SPLITVEC boolspl_lisnull[INDEX_MAX_KEYS]; boolspl_leftvalid; OffsetNumber *spl_right;/* array of entries that go right */ int spl_nright; /* size of the array */ Datum spl_rdatum; /* Union of keys in spl_right */ It's very like that spl_right contains correct spl_nright value (0xdb = 219) and spl_nright contains correct spl_rdatum (pointer 138286924 = 0x83e174c) -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [Pgsphere-dev] Re: [HACKERS] SIGSEGV taken on 8.1 during
When grilled further on (Tue, 08 Nov 2005 10:06:38 -0500), Tom Lane [EMAIL PROTECTED] confessed: Does PGSphere itself have any regression tests? (Actually, running the contrib regression tests might be more relevant than the main PG tests, since several contrib modules with GIST opclasses have regression tests.) That's what I was trying to do ;-) make installcheck passes, as does make crushtest (within pg_sphere). I'll work on trying to get a small test case tonight. Otherwise, we can try SSH to my machine or a DVD. Cheers, Rob -- 08:17:03 up 37 days, 51 min, 6 users, load average: 3.70, 3.56, 3.41 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 pgpae0Rl7qQ8b.pgp Description: PGP signature
Re: [HACKERS] Enums again
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Alvaro Herrera wrote: Huh, why not have the actual values in a separate catalog like pg_enumvalues or some such? Sure, could do that. I don't have strong feelings either way. I'd vote for the separate catalog instead of bloating pg_type. Ok, consider that a done deal. Any other tweaks? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Supporting NULL elements in arrays
and so the most straightforward thing to do is define an empty element as meaning a NULL. But this might be objected to on a couple of grounds: Can you use a default to allow the user to specify the default value for an element? May look a little strange, though, if the user specifies a default array and a default element value, like: CREATE TABLE ARR(ARR CHAR(30)[] DEFAULT '{hello, good bye}' NULL); So the first default is the array default; specify NULL if you don't want one but do want an array element default; the second, if present, is the array element default. I'm not sure I like this or not, but it's an idea. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plperl error when making 8.2dev CVS
On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. Did you delete that file? It's part of the sources. No... i didn't... and the file *is* in the plperl dir Weird. So if you cd src/pl/plperl and ls, the file is there, yet it tries to make it? That's weird. Did you try a make distclean after changing your configure arguments? I've never seen this problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support i think i did... but it was 1:30 am maybe i dream it, i will try 'make distclean' and configure again at night... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl error when making 8.2dev CVS
Jaime Casanova wrote: On 11/8/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Jaime Casanova wrote: make[3]: *** No rule to make target `SPI.xs', needed by `SPI.c'. Stop. Did you delete that file? It's part of the sources. No... i didn't... and the file *is* in the plperl dir Weird. So if you cd src/pl/plperl and ls, the file is there, yet it tries to make it? That's weird. Did you try a make distclean after changing your configure arguments? I've never seen this problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Supporting NULL elements in arrays
In article [EMAIL PROTECTED], Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: it's great news. My personal opinion about formating NULL values '{a, NULL, b}' -- longer, clean NULL is NULL Unfortunately, that already has a meaning, and it's not that. What a pity. I don't see any alternative to the empty element proposal - it's worth the possible breakage. How about '{a, \N, b}'? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Supporting NULL elements in arrays
Pollard, Mike [EMAIL PROTECTED] writes: Can you use a default to allow the user to specify the default value for an element? There's no mechanism for specifying a default value for individual elements within an array; and I've never heard one asked for. Certainly it's far less interesting than allowing nulls within an array. So I don't think we need to give pride of syntax place to that. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Supporting NULL elements in arrays
and so the most straightforward thing to do is define an empty element as meaning a NULL. But this might be objected to on a couple of grounds: Can you use a default to allow the user to specify the default value for an element? May look a little strange, though, if the user specifies a default array and a default element value, like: CREATE TABLE ARR(ARR CHAR(30)[] DEFAULT '{hello, good bye}' NULL); So the first default is the array default; specify NULL if you don't want one but do want an array element default; the second, if present, is the array element default. I'm not sure I like this or not, but it's an idea. Mike Pollard SUPRA Server SQL Engineering and Support Cincom Systems, Inc ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Supporting NULL elements in arrays
Harald Fuchs [EMAIL PROTECTED] writes: Tom Lane wrote: Unfortunately, that already has a meaning, and it's not that. How about '{a, \N, b}'? That's valid syntax too, ie, adopting that syntax would break applications that are not broken today. Not to mention that it would be gratuitously different from the notation for NULLs in composite-type literals. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Teodor Sigaev [EMAIL PROTECTED] writes: Layout of GIST_SPLITVEC struct has been changed from 8.0, I'm afraid that old .so is used. spl_(right|left)valid fields was added to GIST_SPLITVEC. Does look a bit suspicious ... Robert, are you *sure* you've got the right version of pgsphere linked in? Did you compile it against the right set of Postgres header files? 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] Any advice about function caching?
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 07 November 2005 23:06 To: Mark Cave-Ayland (External) Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Any advice about function caching? (cut) If you want per-query state, keep it in a data structure linked from the fcinfo-flinfo-fn_extra field (physically, store it in fn_mcxt, or fcinfo-flinfo-create a subcontext of that if you wish). If you need to get control at query shutdown to free non-palloc'd resources, RegisterExprContextCallback may help. (I think such callbacks are only called during *successful* query shutdown, though, so if you have external library state you need to clean up anyway, you'll need some other approach to keeping track of it ... maybe a permanent data structure instead of a per-query one.) src/backend/utils/fmgr/funcapi.c and src/backend/executor/functions.c might be useful examples. Hi Tom, Thanks for the advice about state - this is definitely pointing me towards looking at the existing code for aggregates and SRFs. Incidentally I've found that attaching my cleanup memory context to PortalContext with some elogs() shows that it appears to be called correctly just before the portal is destroyed - so whatever I finally come up with is likely to be a combination of the two methods. I will dig further into the function code and see how I manage. Many thanks, Mark. WebBased Ltd 17 Research Way Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 http://www.webbased.co.uk http://www.infomapper.com http://www.swtc.co.uk This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(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
[HACKERS] [OTAnn] Feedback
I was interested in getting feedback from current mail group users.We have mirrored your mail list in a new application that provides a more aggregated and safe environment which utilizes the power of broadband.Roomity.com v 1.5 is a web 2.01 community webapp. Our newest version adds broadcast video and social networking such as favorite authors and an html editor.It?s free to join and any feedback would be appreciated.S.Broadband interface (RIA) + mail box saftey = PostgreSQL_Development_Issues_List.roomity.com*Your* clubs, no sign up to read, ad supported; try broadband internet. ~~1131471067262~~
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
On Tue, 08 Nov 2005 11:12:04 -0500 Tom Lane [EMAIL PROTECTED] wrote: Teodor Sigaev [EMAIL PROTECTED] writes: Layout of GIST_SPLITVEC struct has been changed from 8.0, I'm afraid that old .so is used. spl_(right|left)valid fields was added to GIST_SPLITVEC. Does look a bit suspicious ... Robert, are you *sure* you've got the right version of pgsphere linked in? Did you compile it against the right set of Postgres header files? I copied pg_sphere into the contrib directory in 8.1.0, which is where it was built. Last night, I executed a make clean from contrib/pg_sphere, re-built make and re-installed. I checked the pg_sphere Makefile, and it references local, not absolute paths. So, I'm as sure as I can be right now. How can I check the .so files installed by the build? Do they reference an absolute path for their dependent .so files (postgres), or will they use ld.so.conf, which might then explain the problem. My ld.so.conf still points to the 8.0.2 version, as I've not switched yet to 8.1.0. In any case, why would the make installcheck work in the pg_sphere directory? That would have to use the installed libraries. I don't have the sources with me, but I'd think an index would of been created on a spoint column, but maybe not? Cheers, Rob ---(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] Assert failure found in 8.1RC1
On Fri, Nov 04, 2005 at 08:46:27PM -0400, Marc G. Fournier wrote: On Fri, 4 Nov 2005, Jim C. Nasby wrote: For all the talk about couldn't it be part of regression, I haven't seen anyone submit a patch that would test for it ... since I believe both you and Tom have both stated that for things like race conditions, I don't know that you can create reproducable cases, can you submit a patch for how you propose this should be added to the regression tests? I have an idea, but it might be better if Robert could produce a test case since it would cover both a context storm issue as well as this race condition. Baring that, my idea was to spawn a number of processes, all of which were trying to insert/update a random value in a table using David Fetter's plpgsql code for doing a merge. This would produce a heavy workload that also used subtransactions (due to the exception handling in plpgsql). Suggestions for a better test welcome... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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
[HACKERS] lc_numeric and decimal delimiter
Hi all, I am at OpenDBCon in Germany. People are complaining about floats/numerics not accepting German/European conventions for the delimiter point -- a comma. This is hard coded into the the numeric input parser but, naturally, we use strtod() in else where. I'm not sure about the locale stuff and whether it deals with it. Are we in the wrong here? Thanks, Gavin ---(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] Assert failure found in 8.1RC1
On Tue, 08 Nov 2005 14:09:58 -0600 Jim C. Nasby [EMAIL PROTECTED] wrote: On Fri, Nov 04, 2005 at 08:46:27PM -0400, Marc G. Fournier wrote: On Fri, 4 Nov 2005, Jim C. Nasby wrote: For all the talk about couldn't it be part of regression, I haven't seen anyone submit a patch that would test for it ... since I believe both you and Tom have both stated that for things like race conditions, I don't know that you can create reproducable cases, can you submit a patch for how you propose this should be added to the regression tests? I have an idea, but it might be better if Robert could produce a test case since it would cover both a context storm issue as well as this race condition. Actually, I have a test case. I just sent it out to Tom a couple of hours ago. The quick and dirty is that it shows the problem after running for about 20 minutes on my Xenon system with 8.1.0... I cannot get it to fail on my AMD system with a much higher load... I can send it to others who are interested. The e-mail with dump, module and script is just over 1Mb. Cheers, Rob ---(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] Interval aggregate regression failure (expected seems
Gregory Maxwell wrote: On 07 Nov 2005 14:22:37 -0500, Greg Stark [EMAIL PROTECTED] wrote: IIRC, floating point registers are actually longer than a double so if the entire calculation is done in registers and then the result rounded off to store in memory it may get the right answer. Whereas if it loses the extra bits on the intermediate values (the infinite repeating fractions) that might be where you get the imprecise results. Hm. I thought -march=pentium4 -mcpu=pentium4 implies -mfpmath=sse. SSE is a much better choice on P4 for performance reasons, and never has excess precision. I'm guessing from the above that I'm incorrect, in which case we should always be compiled with -mfpmath=sse -msse2 when we are complied -march=pentium4, this should remove problems caused by excess precision. The same behavior can be had on non sse platforms with -ffloat-store. Just for the record (and those interested): using 'CFLAGS=-O2 -mcpu=pentium4 -march=pentium4 -mfpmath=sse -msse2' actually passes the regression tests. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Interval aggregate regression failure (expected seems
Tom Lane wrote: I wrote: Michael Paesold [EMAIL PROTECTED] writes: I am definatly not going to use -march=pentium4 in any production system. Should I open a bug report with RedHat (gcc vendor)? Yeah, but they'll probably want a smaller test case than Postgres fails its regression tests :-( I have just confirmed that the problem still exists in FC4's current compiler (gcc 4.0.1, gcc-4.0.1-4.fc4), which probably will boost up the priority of the complaint quite a long way in Red Hat's eyes. I've also confirmed that the problem is in interval_div; you can reproduce the failure with select '41 years 1 mon 11 days'::interval / 10; [snip] Would you mind reporting this to RedHat Bugzilla? I believe a bug report from you would have more weight then mine, because you actually understand what's going on here. :-) Otherwise I am going to do do my best... Best Regards, Michael Paesold ---(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] Exclusive lock for database rename
On Sat, Nov 05, 2005 at 11:48:56AM +0100, Martijn van Oosterhout wrote: On Sat, Nov 05, 2005 at 10:47:30AM +0100, Jochem van Dieten wrote: On 11/4/05, Jim C. Nasby wrote: I would argue that in cases like this (and 'this' means just about any DDL, for starters) that it would be better not to block everyone until work can actually be done. Or at least make that an option. Would it be possible to simulate this by manually trying to grab a lock on a relation using NOWAIT in a loop or are the locks DDL requires different from the ones acquired by the LOCK statement? What you want is probably some kind of attempt to grab lock with timeout. Ie, it tries to grab the lock but gets stuck waiting for someone else. After some timeout it fails, waits a few seconds and tries again. That few seconds allows other clients waiting for you to unstuck. Set the timeout to maybe 30 seconds. Then no query will wait for your lock for more than 30 seconds. Or maybe exponentially rising delay, otherwise you'll never guarentee completion. With notice to client what is happening, hopefully... BTW, if you come up with a working example of this it would be a great addition to the docs. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lc_numeric and decimal delimiter
Gavin Sherry [EMAIL PROTECTED] writes: I am at OpenDBCon in Germany. People are complaining about floats/numerics not accepting German/European conventions for the delimiter point -- a comma. This is hard coded into the the numeric input parser but, naturally, we use strtod() in else where. I'm not sure about the locale stuff and whether it deals with it. Are we in the wrong here? This has been proposed and rejected in the past. I don't think it's an open-and-shut decision. Given that our trend has been to tighten rather than loosen input error checking (eg, no empty strings for int4, no heuristic month/day choices for timestamps), I'd be inclined to vote against it still. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Assert failure found in 8.1RC1
On Tue, Nov 08, 2005 at 02:09:35PM -0700, Robert Creager wrote: On Tue, 08 Nov 2005 14:09:58 -0600 Jim C. Nasby [EMAIL PROTECTED] wrote: On Fri, Nov 04, 2005 at 08:46:27PM -0400, Marc G. Fournier wrote: On Fri, 4 Nov 2005, Jim C. Nasby wrote: For all the talk about couldn't it be part of regression, I haven't seen anyone submit a patch that would test for it ... since I believe both you and Tom have both stated that for things like race conditions, I don't know that you can create reproducable cases, can you submit a patch for how you propose this should be added to the regression tests? I have an idea, but it might be better if Robert could produce a test case since it would cover both a context storm issue as well as this race condition. Actually, I have a test case. I just sent it out to Tom a couple of hours ago. The quick and dirty is that it shows the problem after running for about 20 minutes on my Xenon system with 8.1.0... I cannot get it to fail on my AMD system with a much higher load... I can send it to others who are interested. The e-mail with dump, module and script is just over 1Mb. Just to clarify, did it show the assert failure, the context switch storm, or both? Yes, I'd like to take a look at this if you could send it on to me. Is there any simple way to populate the database? I doubt people would be keen on having a 1MB dump in CVS... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Assert failure found in 8.1RC1
On Tue, 08 Nov 2005 15:36:18 -0600 Jim C. Nasby [EMAIL PROTECTED] wrote: Just to clarify, did it show the assert failure, the context switch storm, or both? I didn't try for the assert after the patch. I was developing the test when I ran across the assert problem. It should trigger the assert problem. Yes, I'd like to take a look at this if you could send it on to me. Is there any simple way to populate the database? I doubt people would be keen on having a 1MB dump in CVS... Hmmm... Should be possible to populate all the data algorithmically. For the most part, the specific data doesn't matter, just the general patterns in the data. I'll re-send the e-mail to you. Cheers, Rob ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lc_numeric and decimal delimiter
Gavin Sherry wrote: I am at OpenDBCon in Germany. People are complaining about floats/numerics not accepting German/European conventions for the delimiter point -- a comma. This is hard coded into the the numeric input parser but, naturally, we use strtod() in else where. I'm not sure about the locale stuff and whether it deals with it. Are we in the wrong here? Yes. Unless you consider it reasonable to expect applications to parse numeric input from users to change the decimal and thousands separators. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Troubles with array_ref
Hi, sorry for the question but I still having serious troubles with the array_ref function. The function is not documented and I can't get a useful example inside the contrib directory. The function is defined as: Datum array_ref(ArrayType *array, int nSubscripts, int *indx, int arraylen, int elmlen, bool elmbyval, char elmalign, bool *isNull); I guess nSubscripts is the number of dimensions of the array and indx is the index number of the element I want to get; arraylen I guess is the length of the ArrayType structure and I also guess that if ArrayType is a varlena element I could get it with VARSIZE() [if that is wrong somebody could tell me how to get that info?]; elmlen I guess is the size of any of the members of the array; elmbyval and elmalign are the passed by val and align properties of each of the elements in the array; and of course isNull is just to show if the array could have null values or not. [again, if any of these asserts are false then please correct me and I will try to document it as soon as possible]. Well, anyway, this is the Stored Function I've been workin on; it simply take an array and an integer just to return this item from the array; The array could have any kind of elements so I declare it as anyarray (the parameter) and anyelement (the return value), please help me, I don't know where to get info about it. = THIS IS THE FUNCTION == PG_FUNCTION_INFO_V1(test); Datum test(PG_FUNCTION_ARGS) { ArrayType *v = PG_GETARG_ARRAYTYPE_P(1); Datum element; Oidarray_type = ARR_ELEMTYPE(PG_GETARG_ARRAYTYPE_P(1)); int16 typlen; bool typbyval; char typalign; inti = PG_GETARG_INT32(0); get_typlenbyvalalign(array_type, typlen, typbyval, typalign); element = array_ref(v, 1, i, VARSIZE(v), typlen, typbyval, typalign, false); PG_RETURN_DATUM(element); } = THIS IS THE DECLARATION IN SQL = CREATE OR REPLACE FUNCTION test(integer, anyarray) RETURNS anyelement AS 'test.so' LANGUAGE 'C' STABLE; AND THIS IS THE ERROR === SELECT test(1, array[1,2,3]); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exite d abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. Failed. Thanks a lot for your help... ---(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] Troubles with array_ref
Cristian Prieto [EMAIL PROTECTED] writes: Well, anyway, this is the Stored Function I've been workin on; it simply take an array and an integer just to return this item from the array; The array could have any kind of elements so I declare it as anyarray (the parameter) and anyelement (the return value), please help me, I don't know where to get info about it. You could save yourself a lot of time if you enabled warnings from your C compiler (eg, -Wall for gcc) and then paid some attention to them. The last parameter to array_ref is a bool *, not a bool, and I have no doubt that the backend is crashing while trying to dereference false. (Another problem is that the fourth parameter should be -1 not VARSIZE.) 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] Interval aggregate regression failure (expected seems
Michael Paesold [EMAIL PROTECTED] writes: Would you mind reporting this to RedHat Bugzilla? I believe a bug report from you would have more weight then mine, because you actually understand what's going on here. :-) Actually, given the thought that this may be an artifact of keeping an intermediate value in a wider-than-normal register rather than genuinely rearranging the computation, I'm not certain it is a compiler bug. We'd have to study it a lot more closely before filing it as one, anyway. If you accept the idea that the pentium4 answer is the right one, then what we really need to do is focus on a better rounding rule than strict truncation. I was toying with the notion of adding the equivalent of half a microsecond to the fractional-day value before truncating it to integer. But I'm not certain that that wouldn't have some bad effects in other cases. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Supporting NULL elements in arrays
I wrote: ... the most straightforward thing to do is define an empty element as meaning a NULL. But this might be objected to on a couple of grounds: I just thought of another, potentially fatal objection: it's ambiguous whether '{}'::text[] should be taken to mean an empty (zero-length) array or an array containing a single NULL element. For backwards compatibility it should mean an empty array, but then there's no way to represent ARRAY(NULL) in data dumps, which won't do either. The only workaround that comes to mind is to allow explicit specification of what's meant: '[1:1]{}' would be needed to represent the one-null case. Ugly. Ideas anyone? 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] DTrace?
There is work going on to add dtrace support to FreeBSD, which I expect could migrate to the other BSDs as well. AFAIK *BSD is actually more popular among the developers than linux, so dtrace support could well happen. On Mon, Nov 07, 2005 at 02:29:12PM -0700, Aly Dharshi wrote: From what I understand DTrace is rather tough to use. Secondly it will provide Solaris only information, so if you are suggesting helpfulness for just Solaris, then yes it would be. I don't think that DTrace is available for Solaris 8 and 9, the company I work for is still on 8 with possibly some 7's hanging around somewhere, which is where I expect alot of people to still be, Solaris 10 hasn't been adopted as widely as expected by Sun, it may gain some momentum with OpenSolaris, but we shall have to see. karen hill wrote: I skimmed the thread Spinlocks, yet again: analysis and proposed patches. Wouldn't Solaris 10's DTrace be helpful in seeing what's going on? It seems DTrace was meant for these types of problems. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Supporting NULL elements in arrays
On Tue, Nov 08, 2005 at 07:21:34PM -0500, Tom Lane wrote: I wrote: ... the most straightforward thing to do is define an empty element as meaning a NULL. But this might be objected to on a couple of grounds: I just thought of another, potentially fatal objection: it's ambiguous whether '{}'::text[] should be taken to mean an empty (zero-length) array or an array containing a single NULL element. To take another perspective on this, though - it isn't possible to have NULL array elements right now, therefore, there is no precident, and who is to say that {NULL} {}? For example: vhosts= select ('{1, 3}'::text[])[4]; text -- (1 row) vhosts= select ('{}'::text[])[4]; text -- (1 row) Perhaps NULL at end of array never needs to be stored, and arrays can be considered to have an infinite number of NULL values at the end? For array operations that require a length, such as cross-product, or whatever, the 'length' of the array, would be the number of elements before the infinite number of NULL values at the end. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Supporting NULL elements in arrays
On Tue, Nov 08, 2005 at 07:21:34PM -0500, Tom Lane wrote: I wrote: ... the most straightforward thing to do is define an empty element as meaning a NULL. But this might be objected to on a couple of grounds: I just thought of another, potentially fatal objection: it's ambiguous whether '{}'::text[] should be taken to mean an empty (zero-length) array or an array containing a single NULL element. For backwards compatibility it should mean an empty array, but then there's no way to represent ARRAY(NULL) in data dumps, which won't do either. The only workaround that comes to mind is to allow explicit specification of what's meant: '[1:1]{}' would be needed to represent the one-null case. Ugly. Instead of bending over backwards to try and support older cases, would a compatability mode be possible? Seems that would solve a lot of problems. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Enums again
Also, Christopher - I was somewhat motivated to work on this by your recent comment about enums being the number one demand of migrating MySQL users, so I am mildly amused by your last sentence ;-) They're not mutually exclusive statements :) ---(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] Supporting NULL elements in arrays
Jim C. Nasby wrote: On Tue, Nov 08, 2005 at 07:21:34PM -0500, Tom Lane wrote: ... the most straightforward thing to do is define an empty element as meaning a NULL. But this might be objected to on a couple of grounds: I just thought of another, potentially fatal objection: it's ambiguous whether '{}'::text[] should be taken to mean an empty (zero-length) array or an array containing a single NULL element. For backwards compatibility it should mean an empty array, but then there's no way to represent ARRAY(NULL) in data dumps, which won't do either. The only workaround that comes to mind is to allow explicit specification of what's meant: '[1:1]{}' would be needed to represent the one-null case. Ugly. Instead of bending over backwards to try and support older cases, would a compatability mode be possible? Seems that would solve a lot of problems. Last time I thought about this problem, that's what I concluded. I don't think there is a reasonable and backward compatible solution. I also think the best non-compatible solution is to require non-numeric elements to be delimited (double quotes, configurable?), and use NULL unadorned to represent NULL. Joe ---(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] Supporting NULL elements in arrays
I also think the best non-compatible solution is to require non-numeric elements to be delimited (double quotes, configurable?), and use NULL unadorned to represent NULL. I think the ultimate solution should have null values represented by NULL... I mean NULL is NULL :) Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] compiling on windows with mingw
I would like to compile the code on windows. I understand I need mingw. on the mingw site there are may packages to download and install. Does anyone know which ones to download install in order to compile pg. Thanx, Gevik.
Re: [HACKERS] lc_numeric and decimal delimiter
Gavin Sherry wrote: Hi all, I am at OpenDBCon in Germany. People are complaining about floats/numerics not accepting German/European conventions for the delimiter point -- a comma. This is hard coded into the the numeric input parser but, naturally, we use strtod() in else where. I'm not sure about the locale stuff and whether it deals with it. Are we in the wrong here? If we are it is apparently by design. pg_locale.c says: * The other categories, LC_MONETARY, LC_NUMERIC, and LC_TIME are also * settable at run-time. However, we don't actually set those locale * categories permanently. This would have bizarre effects like no * longer accepting standard floating-point literals in some locales. * Instead, we only set the locales briefly when needed, cache the * required information obtained from localeconv(), and set them back. * The cached information is only used by the formatting functions * (to_char, etc.) and the money type. Personally, I am inclined to think there should at least be a setting that allows input according to locale settings, even if it would disallow standard floating point strings. Maybe one of the protesting Europeans would like to make a proposal? cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Exclusive lock for database rename
On Tue, Nov 08, 2005 at 03:14:34PM -0600, Jim C. Nasby wrote: On Sat, Nov 05, 2005 at 11:48:56AM +0100, Martijn van Oosterhout wrote: On Sat, Nov 05, 2005 at 10:47:30AM +0100, Jochem van Dieten wrote: On 11/4/05, Jim C. Nasby wrote: I would argue that in cases like this (and 'this' means just about any DDL, for starters) that it would be better not to block everyone until work can actually be done. Or at least make that an option. Would it be possible to simulate this by manually trying to grab a lock on a relation using NOWAIT in a loop or are the locks DDL requires different from the ones acquired by the LOCK statement? What you want is probably some kind of attempt to grab lock with timeout. Ie, it tries to grab the lock but gets stuck waiting for someone else. After some timeout it fails, waits a few seconds and tries again. That few seconds allows other clients waiting for you to unstuck. Set the timeout to maybe 30 seconds. Then no query will wait for your lock for more than 30 seconds. Or maybe exponentially rising delay, otherwise you'll never guarentee completion. With notice to client what is happening, hopefully... I think this wait with an exponentially rising delay hurts not helps. If the stricter lock can be granted in a short time, ie the dalay could be small, then there is no problem. If the lock cannot be granted and the delay expires the stricter lock has incurred extra wait time already and allowed newer conflicting requests ahead of it possibly increasing the total wait time. As the timeout increases newer requests end up waiting for the new longer time anyway so the overall effect is to increase all lockers total wait time. -dg -- David Gould [EMAIL PROTECTED] If simplicity worked, the world would be overrun with insects. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
When grilled further on (Tue, 08 Nov 2005 11:12:04 -0500), Tom Lane [EMAIL PROTECTED] confessed: Teodor Sigaev [EMAIL PROTECTED] writes: Layout of GIST_SPLITVEC struct has been changed from 8.0, I'm afraid that old .so is used. spl_(right|left)valid fields was added to GIST_SPLITVEC. Does look a bit suspicious ... Robert, are you *sure* you've got the right version of pgsphere linked in? Did you compile it against the right set of Postgres header files? Strings on pg_sphere.so does contain /usr/local/pgsql810/lib. I've attached a small dump file that when I create an index on the table, it fails. It works on 225 entries, but failed on 250. Don't know if this is data dependent or size. Is that a page boundary? It seems to me that unless the right/left stuff doesn't come into play for all indexes, that stuff is built correctly. Dump command: /usr/local/pgsql810/bin/pg_dump -F c -p 5433 -d tassiv -t test_data -f index_problem.dump Created the table and index by: tassiv=# SELECT loc into test_data from catalog limit 250; tassiv=# create index test_data_index on test_data using gist( loc ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! tassiv=# \d test_data Table public.test_data Column | Type | Modifiers ++--- loc| spoint | Cheers, Rob -- 19:51:58 up 37 days, 12:26, 6 users, load average: 2.15, 2.39, 2.41 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 index_problem.dump Description: Binary data pgp6Rs93Q3Tpv.pgp Description: PGP signature
[HACKERS] Accessing libq functions from UDF (shared library)
Hi all, I am trying to create a User-defined function(UDF) which takes filename as input.. the function reads the file containig sql queries and executes them in background using libq functions (PGconnectDb, PQexec etc..). I was able to sucessfully compile the code and create a shared library. This is my makefile: top_builddir = ../../.. include $(top_builddir)/src/Makefile.global SERVER_INCLUDES += -I $(shell pg_config --includedir) SERVER_INCLUDES += -I $(shell pg_config --includedir-server) CFLAGS += -g $(SERVER_INCLUDES) .SUFFIXES: .so .c.so: $(CC) $(CFLAGS) -fpic -c $ $(CC) $(CFLAGS) -shared -o $@ $(basename $).o But when i tried creating a UDF using 'CREATE OR REPLACE FUNCTION' , i get the following error: -- ERROR: Could not load library: .../pgsql/lib/mylib.so : undefined symbol PGConnectdb My LD_LIBRARY_PATH is set to point to .../pgsql/lib dirctory (which contains all the libarary files needed...).. $ ldd mylib.so gives the following output: libc.so.6 = /lib/tls/libc.so.6/ /lib/ld-linux.so.2 Am i missing something? Would really appreciate any help in this regard. Thanks in advance, Gayathri TK ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
works fine contrib_regression=# select count(*) from test_data ; count --- 250 (1 row) contrib_regression=# create index test_data_index on test_data using gist( loc ); CREATE INDEX I've attached a small dump file that when I create an index on the table, it fails. It works on 225 entries, but failed on 250. Don't know if this is data dependent or size. Is that a page boundary? It seems to me that unless the right/left stuff doesn't come into play for all indexes, that stuff is built correctly. Dump command: /usr/local/pgsql810/bin/pg_dump -F c -p 5433 -d tassiv -t test_data -f index_problem.dump -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Accessing libq functions from UDF (shared library)
Gayathri TK wrote: Hi all, I am trying to create a User-defined function(UDF) which takes filename as input.. the function reads the file containig sql queries and executes them in background using libq functions (PGconnectDb, PQexec etc..). But when i tried creating a UDF using 'CREATE OR REPLACE FUNCTION' , i get the following error: Should you not be using SPI to run queries if this is inside PostgreSQL? See chapter 39 Server Programming Interface for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
So, I'm as sure as I can be right now. How can I check the .so files installed by the build? Do they reference an absolute path for their dependent .so files (postgres), or will they use ld.so.conf, which might then explain the problem. My ld.so.conf still points to the 8.0.2 version, as I've not switched yet to 8.1.0. The simplest way is just remove pg_sphere.so in 8.1 installaion (/usr/local/pgsql810/lib/pg_sphere.so) and try, for example, to create gist index on spoint. Response should be: contrib_regression=# create index test_data_index on test_data using gist( loc ); ERROR: could not access file /usr/local/pgsql/lib/pg_sphere: No such file or directory If not - 8.1 use 8.0 .so -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings