Re: [HACKERS] Updatable views/with check option parsing
Ühel kenal päeval, R, 2006-05-26 kell 22:50, kirjutas Tom Lane: I wrote: We can use the same technique that we used for UNION JOIN, but instead join, say, WITH and TIME into one token and make the time datatype productions look for TIME WITHTIME ZONE and so on. (I propose this rather than putting the ugliness into WITH CHECK OPTION, because this way we should only need one merged token and thus only one case to check in the filter function; AFAICS we'd need three cases if we merge tokens on that end of it.) On investigation that turns out to have been a bad idea: if we do it that way, it becomes necessary to promote WITH to a fully reserved word. The counterexample is CREATE VIEW v AS SELECT * FROM foo WITH ... Is WITH an alias for foo (with no AS), or is it the start of a WITH CHECK OPTION? No way to tell without lookahead. While I don't think that making WITH a fully reserved word would cause any great damage, I'm unwilling to do it just to save a couple of lines of code. I think we should go on and do promote WITH to a reserved keyword now because eventually we have to do it anyway. It is needed for recursive queries as well. I don't pretend to be an expert bison coder, but I was unable to define a grammar for SQL-standard recursive queries without making WITH a reserved keyword. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Inefficient bytea escaping?
On 5/27/06, Tom Lane [EMAIL PROTECTED] wrote: I wrote: I'm off for a little visit with oprofile... It seems the answer is that fwrite() does have pretty significant per-call overhead, at least on Fedora Core 4. That may be because of the locking ritual all stdio functions like to do, even without _REENTRANT. If you want to use fwrite as string operator, then maybe should replace it with fwrite_unlocked? -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] XLogArchivingActive
Jim C. Nasby wrote: Also, regarding needing to place an archiver command in pg_start_backup_online, another option would be to depend on the filesystem backup to copy the WAL files, and just let them pile up in pg_xlog until pg_stop_backup_online. Of course, that would require a two-step filesystem copy, since you'd need to first copy everything in $PGDATA, and then copy $PGDATA/pg_xlog after you have that. Sounds fine. This solves the problem to insure that all required wal files are actually copied to the wal archive. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Inefficient bytea escaping?
Tom Lane wrote: I wrote: I'm off for a little visit with oprofile... It seems the answer is that fwrite() does have pretty significant per-call overhead, at least on Fedora Core 4. The patch I did yesterday still ended up making an fwrite() call every few characters when dealing with bytea text output, because it'd effectively do two fwrite()s per occurrence of '\' in the data being output. I've committed a further hack that buffers a whole data row before calling fwrite(). Even though this presumably is adding one extra level of data copying, it seems to make things noticeably faster: (semi-OT) This recoding seems like a perfect preparation for a third COPY format, compressed. Let me know what this does on your Debian machine ... Takes a while, need a different kernel booted because the current isn't oprofile ready. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Inserting Picture to Bytea
Hello, I'm new to pgsql. I could not find documentation regarding inserting of picture(.jpg) to BYTEA using Visual Basic (ADO connection). my code for MSSQL will not work. i'm using pgsql 8.1.4 i also tried this link but an error occur about LO. http://archives.postgresql.org/pgsql-odbc/2001-10/msg00066.php Thank you for your Help. mel
Re: [HACKERS] Inserting Picture to Bytea
Gera Mel Handumon wrote: Hello, I'm new to pgsql. I could not find documentation regarding inserting of picture(.jpg) to BYTEA using Visual Basic (ADO connection). my code for MSSQL will not work. i'm using pgsql 8.1.4 i also tried this link but an error occur about LO. http://archives.postgresql.org/pgsql-odbc/2001-10/msg00066.php http://archives.postgresql.org/pgsql-odbc/2001-10/msg00066.php This is not the correct list to ask this question on. Please ask on the list you referred to above, namely pgsql-odbc cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Updatable views/with check option parsing
Hannu Krosing [EMAIL PROTECTED] writes: I think we should go on and do promote WITH to a reserved keyword now because eventually we have to do it anyway. It is needed for recursive queries as well. I'm unconvinced. Recursive queries have WITH at the front, not the back, so the parsing issues are entirely different. If we do find that, we can easily adjust this code to simplify the filter function at that time. But I don't agree with reserving words just because we might need them for patches that don't exist yet. 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] Inefficient bytea escaping?
Marko Kreen [EMAIL PROTECTED] writes: If you want to use fwrite as string operator, then maybe should replace it with fwrite_unlocked? ISTM that in a single-threaded application such as the backend, it should be libc's responsibility to avoid such overhead, not ours. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
Martijn van Oosterhout kleptog@svana.org writes: On Fri, May 26, 2006 at 11:38:41AM -0500, Jim C. Nasby wrote: Also, might a bitmap scan be a win for the %string case? Presumably it's much faster to find matching rows via an index and then go back into the heap for them; unless you're matching a heck of a lot of rows. This is an interesting thought. Currently, AFAICS, the bitmap-scan code only considers operators that are indexable, just like for narmal index scans. However, in this case the query could scan the entire index, apply the LIKE to each one and produce a bitmap of possible matches. Then do a bitmap scan over the table to check the results. Not just LIKE could use this, but any function marked STABLE. You'd have to weigh up the cost of scanning the *entire* index (because we don't have any actual restriction clauses) against avoiding a full table scan. I've been thinking about this. The general case is that you could have some auxiliary conditions (arbitrary nonvolatile expressions using only columns present in the index) as well as the regular index qualification conditions (possibly zero of these). AFAICS it wouldn't matter if the indexscan is bitmap or regular. It seems fairly doable, and would have some nice side effects --- for example, the ancient bugaboo that foo IS NULL isn't an indexable operator would be partially assuaged. But there are a couple of gotchas: * It doesn't work for indexes that store compressed keys instead of the original column value; which lets out GiST and GIN, at least with some opclasses. I'd be inclined to just implement it for btree and maybe hash, rather than bothering with checking opclasses. (I don't think we have any official way for a GiST/GIN opclass to show whether it does any key compression, anyhow.) * Up to now, the only functions directly invoked by an index AM were members of index opclasses; and since opclasses can only be defined by superusers, there was at least some basis for trusting the functions to behave sanely. But if an index AM is going to invoke arbitrary user-defined expressions then more care is needed. What's particularly bothering me is the notion of executing arbitrary functions while holding a buffer lock on an index page. If the arbitrary functions go off and scan other tables (or even the same table) I think it wouldn't be too hard to get into deadlock situations, especially across multiple backends. And deadlocks on LWLocks are really nasty: there's no deadlock detection, no timeout, and no way out short of SIGQUIT/SIGKILL. That would make it a security hole, even if the conditions needed to trigger it are so bizarre they'd never arise in normal usage. Given that btree now works page-at-a-time in all cases, we could imagine fixing this by postponing checks of auxiliary conditions until after we release the buffer lock. This would require making copies of all index tuples that pass the regular index quals as we scan the page (needing at most BLCKSZ workspace), releasing the buffer lock, and then applying the auxiliary conditions to filter out tuples we don't want to return. The extra data-copying is annoying but probably still beats a trip to the heap. It might be best to just copy the whole page out of shared buffers and into a local page, release the lock immediately, and then go on with checking both indexquals and auxiliary conditions in one pass. This would be more data-copying but the improvement in locality of access to the shared buffer might repay that. I don't recall the locking rules for hash in any detail, but probably something similar would work for hash, assuming anyone even wants to bother with it. Comments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Inefficient bytea escaping?
On 5/27/06, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: If you want to use fwrite as string operator, then maybe should replace it with fwrite_unlocked? ISTM that in a single-threaded application such as the backend, it should be libc's responsibility to avoid such overhead, not ours. Obviously, except glibc guys seems to be philosophically opposed to this, so apps need to work around it. AFAIK at least *BSDs have got this right, don't know about others. -- marko ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
On Sat, May 27, 2006 at 10:57:05AM -0400, Tom Lane wrote: * Up to now, the only functions directly invoked by an index AM were members of index opclasses; and since opclasses can only be defined by superusers, there was at least some basis for trusting the functions to behave sanely. But if an index AM is going to invoke arbitrary user-defined expressions then more care is needed. What's particularly bothering me is the notion of executing arbitrary functions while holding a buffer lock on an index page. Actually, for a first pass I was considering doing it within the nodeIndexScan.c/nodeBitmapScan.c and not within the AM at all. But I just remembered, the index interface has no way to return the actual values in the index, so you can't do that :( So other than being careful with locking, you don't see any objections? How about the suggestion of using a sequential index scan like the recent changes to VACUUM in the case that there are no regular index quals? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
Martijn van Oosterhout kleptog@svana.org writes: How about the suggestion of using a sequential index scan like the recent changes to VACUUM in the case that there are no regular index quals? Nonstarter (hint: the solution we found for VACUUM assumes there can be only one). 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] Inefficient bytea escaping?
On Sat, May 27, 2006 at 06:36:15PM +0300, Marko Kreen wrote: ISTM that in a single-threaded application such as the backend, it should be libc's responsibility to avoid such overhead, not ours. Obviously, except glibc guys seems to be philosophically opposed to this, so apps need to work around it. AFAIK at least *BSDs have got this right, don't know about others. Given there is no way to know if you're running single threaded or not, I don't think glibc can take chances like that. In any case, this isn't the issue here. Glibc doesn't do any locking unless pthread is linked in. Ofcourse, it takes a few cycles to determine that, but I don't think that'd cause a major slowdown. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Inefficient bytea escaping?
On 5/27/06, Martijn van Oosterhout kleptog@svana.org wrote:
Given there is no way to know if you're running single threaded or not,
I don't think glibc can take chances like that.
There's CPP symbol _REENTRANT for that and in run time,
libc can detect call to pthread_create [1].
In any case, this isn't the issue here. Glibc doesn't do any locking
unless pthread is linked in. Ofcourse, it takes a few cycles to
determine that, but I don't think that'd cause a major slowdown.
You are conflicting with your previous paragraph :)
Otherwise you are right - that how a libc obviously should work, right?
http://marc.theaimsgroup.com/?l=glibc-alpham=100775741325472w=2
http://marc.theaimsgroup.com/?l=glibc-alpham=112110641923178w=2
I did a small test that does several fputc calls to /dev/null,
with various workarounds:
* lock.enabled is standard app.
* lock.disabled calls __fsetlocking(FSETLOCKING_BYCALLER),
as suggested by Ulrich Drepper.
* lock.unlocked calls fputc_unlocked
lock.enabled 48s
lock.disabled 28s
lock.unlocked 25s
I attached the test, you can measure yourself.
So I prepared a patch that calls __fsetlocking() in AllocateFile.
Andreas, Tom could you measure if it makes any difference?
--
marko
[1] In the first thread I linked, there was very clever
optimisation proposed using this function, that would
quarantee thread-safety even without _REENTRANT.
Unfortunately, event if U. Drepper changes his mind someday
and fixes the locking for singe-threaded apps, it would
very likely break binary compatibility with old apps,
so it wont happen in the near future.
test-locking.tgz
Description: GNU Zip compressed data
Index: src/backend/storage/file/fd.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/storage/file/fd.c,v
retrieving revision 1.127
diff -u -r1.127 fd.c
--- src/backend/storage/file/fd.c 5 Mar 2006 15:58:37 - 1.127
+++ src/backend/storage/file/fd.c 27 May 2006 16:54:36 -
@@ -46,6 +46,10 @@
#include unistd.h
#include fcntl.h
+#ifdef __GLIBC__
+#include stdio_ext.h
+#endif
+
#include miscadmin.h
#include access/xact.h
#include storage/fd.h
@@ -1258,6 +1262,11 @@
{
AllocateDesc *desc = allocatedDescs[numAllocatedDescs];
+#ifdef __GLIBC__
+ /* disable glibc braindamaged locking */
+ __fsetlocking(file, FSETLOCKING_BYCALLER);
+#endif
+
desc-kind = AllocateDescFile;
desc-desc.file = file;
desc-create_subid = GetCurrentSubTransactionId();
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] error in compilation!
Hi All,
I am constantly getting this error:
make -C pl all
make[2]: Entering directory `/d/Dev/postgres/pgsql_tip/src/pl'
make[3]: Entering directory `/d/Dev/postgres/pgsql_tip/src/pl/plpgsql'
make -C src all
make[4]: Entering directory `/d/Dev/postgres/pgsql_tip/src/pl/plpgsql/src'
dlltool --export-all --output-def plpgsql.def pl_gram.o pl_handler.o
pl_comp.o pl_exec.o pl_funcs.o
dllwrap -o libplpgsql.dll --dllname libplpgsql.dll --def plpgsql.def
pl_gram.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o
-L../../../../src/backend -L../../../../src/port -lpostgres
Info: resolving _standard_conforming_strings by linking to
__imp__standard_conforming_strings (auto-import)
fu01.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname'
fu02.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname'
nmth00.o(.idata$4+0x0): undefined reference to
`_nm__standard_conforming_strings'
collect2: ld returned 1 exit status
D:\msys\1.0\mingw\bin\dllwrap.exe: D:\msys\1.0\mingw\bin\gcc exited
with status 1
make[4]: *** [libplpgsql.a] Error 1
make[4]: Leaving directory `/d/Dev/postgres/pgsql_tip/src/pl/plpgsql/src'
make[3]: *** [all] Error 2
make[3]: Leaving directory `/d/Dev/postgres/pgsql_tip/src/pl/plpgsql'
make[2]: *** [all] Error 1
make[2]: Leaving directory `/d/Dev/postgres/pgsql_tip/src/pl'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/d/Dev/postgres/pgsql_tip/src'
make: *** [all] Error 2
I tried make clean, make distclean and even removing {scan.c,
gram.c and parse.h} by hand from src/backend/parse!!! But the error
keeps coming back.
Grep shows that it is even compiled into SUBSYS.o:
$ grep standard_conforming_strings *
grep: CVS: Invalid argument
Binary file SUBSYS.o matches
Binary file gram.o matches
scan.c:bool standard_conforming_strings = false;
scan.c: if (standard_conforming_strings)
scan.l:bool standard_conforming_strings = false;
scan.l: if (standard_conforming_strings)
All this started after when I did 'make unistall' and updated the
tree to HEAD. Any help will be appreciated.
Thanks,
Gurjeet.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] error in compilation!
Gurjeet Singh [EMAIL PROTECTED] writes: dllwrap -o libplpgsql.dll --dllname libplpgsql.dll --def plpgsql.def pl_gram.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o -L../../../../src/backend -L../../../../src/port -lpostgres Info: resolving _standard_conforming_strings by linking to __imp__standard_conforming_strings (auto-import) Lack of DLLIMPORT. However, the correct fix is that plpgsql has no business depending on the setting of standard_conforming_strings here anyway (because the constructed string might be used later after a change to standard_conforming_strings). Guess I'd better go review Bruce's recent patch. 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] error in compilation!
Tom Lane wrote: Gurjeet Singh [EMAIL PROTECTED] writes: dllwrap -o libplpgsql.dll --dllname libplpgsql.dll --def plpgsql.def pl_gram.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o -L../../../../src/backend -L../../../../src/port -lpostgres Info: resolving _standard_conforming_strings by linking to __imp__standard_conforming_strings (auto-import) Lack of DLLIMPORT. However, the correct fix is that plpgsql has no business depending on the setting of standard_conforming_strings here anyway (because the constructed string might be used later after a change to standard_conforming_strings). Guess I'd better go review Bruce's recent patch. I am thinking it is best to always use E'' in that case. OK? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] anoncvs still slow
anoncvs (svr4, 66.98.251.159) is still slow responding to cvs update; it's been spotty for about a week now. Tcpdump shows connections being established but then long delays for ACKs, sometimes long enough for cvs to time out. Any updates on what's going on? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] error in compilation!
Bruce Momjian pgman@candle.pha.pa.us writes: I am thinking it is best to always use E'' in that case. OK? I'm planning to revert it to the previous logic: E if there's any backslash. I think we have to do likewise in quote_literal() for much the same reason: insufficient confidence that we know how the result will be used. (Note dblink uses quote_literal for strings it will send to the other database.) Currently looking through the rest of the patch. I'm wondering about appendStringLiteral: maybe we should kill that entirely in favor of using PQescapeStringConn? It's not nearly bright enough about encoding for instance (and it *will* be used in client-only encodings). 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] error in compilation!
Any ideas how I can revert back to compilable code? On 5/28/06, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am thinking it is best to always use E'' in that case. OK? I'm planning to revert it to the previous logic: E if there's any backslash. I think we have to do likewise in quote_literal() for much the same reason: insufficient confidence that we know how the result will be used. (Note dblink uses quote_literal for strings it will send to the other database.) Currently looking through the rest of the patch. I'm wondering about appendStringLiteral: maybe we should kill that entirely in favor of using PQescapeStringConn? It's not nearly bright enough about encoding for instance (and it *will* be used in client-only encodings). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Better to dump tabs as tabs, or \t?
Historically pg_dump has taken pains to dump ASCII control characters as backslash constructs, for instance \t for tab. I am thinking this is not such a great idea, and that it'd be more portable rather than less so if we got rid of that logic and just dumped tab as tab, etc. In particular, making this play nice with standard_conforming_strings seems unpleasant: we'll have to emit E'' strings which are certainly not portable, not even to older PG releases. The only good argument I can see for the current behavior is that it makes the dump file somewhat more robust against whitespace-mashing filters like typical email programs. But I wouldn't count on a dump file to come through such a thing completely unscathed anyway. Thoughts? 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] Better to dump tabs as tabs, or \t?
On 5/27/06, Tom Lane [EMAIL PROTECTED] wrote: Historically pg_dump has taken pains to dump ASCII control characters as backslash constructs, for instance \t for tab. I am thinking this is not such a great idea, and that it'd be more portable rather than less so if we got rid of that logic and just dumped tab as tab, etc. In particular, making this play nice with standard_conforming_strings seems unpleasant: we'll have to emit E'' strings which are certainly not portable, not even to older PG releases. Could we just give a switch to pg_dump, which toggles between standard_confirming_strings and old escaped strings? IMHO this decision is similar to COPY/INSERT decision - it depends what the admin plans to with the dump, what tools are user on it, whether there is need to reload on older postgres, etc - and all of them are things that the postgres tools cannot deduce. By default, pg_dump should output standard_conforming_strings, that being in sync with policy to move to standard SQL quoting. And when the switch is given, pg_dump should put SET at the start of the dump, not use E'' stings, so giving option for being backwards compatible. Such option would considerably lower the pain of migrating data between versions. -- marko ---(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] anoncvs still slow
Michael Fuhr [EMAIL PROTECTED] writes: anoncvs (svr4, 66.98.251.159) is still slow responding to cvs update; it's been spotty for about a week now. Tcpdump shows connections being established but then long delays for ACKs, sometimes long enough for cvs to time out. Any updates on what's going on? Magnus apparently knows what the problem is: http://archives.postgresql.org/pgsql-hackers/2006-05/msg01002.php but I haven't seen any of the other mails he mentioned. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Better to dump tabs as tabs, or \t?
Marko Kreen [EMAIL PROTECTED] writes: On 5/27/06, Tom Lane [EMAIL PROTECTED] wrote: Historically pg_dump has taken pains to dump ASCII control characters as backslash constructs, for instance \t for tab. I am thinking this is not such a great idea, and that it'd be more portable rather than less so if we got rid of that logic and just dumped tab as tab, etc. In particular, making this play nice with standard_conforming_strings seems unpleasant: we'll have to emit E'' strings which are certainly not portable, not even to older PG releases. Could we just give a switch to pg_dump, which toggles between standard_confirming_strings and old escaped strings? The plan is that it'll dump according to what it finds as the standard_conforming_strings setting on the source server. If you feel a need to override that setting, you can use PGOPTIONS or the other usual ways to set a GUC variable for a program. However, my thought on the point at hand is to just go over to dumping control characters literally in either case. This is backwards-compatible to all PG versions and I don't know of a reason to think it wouldn't work (at least as well as the backslash constructs anyway) for portability to other databases. Note: this only affects strings dumped as part of SQL commands; COPY data isn't at issue, since we're not planning to change the semantics of that. COPY has always dumped tab as \t and I don't intend to change it. But pg_dump --inserts would be affected, also strings appearing in view definitions and such. We have some precedent for this in that pg_dump has by default dumped function definitions as $$ literals for a release or two now, and no one's complained of whitespace getting munged in function definitions. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_proc probin misuse
On Fri, May 26, 2006 at 11:21:32PM -0400, Tom Lane wrote: James William Pye [EMAIL PROTECTED] writes: So is this fix your broken PL or pg_dump should only be doing that for C language functions? Offhand it seems to me that pg_dump is behaving reasonably: it's storing probin if it sees something there to be stored. The asymmetry is in the backend, specifically functioncmds.c's interpret_AS_clause(): it has a hardwired assumption that probin is only relevant to C functions. Feel free to propose a saner definition. AFAICS the current coding makes probin useless for all except C functions, so I think it could be improved. I guess there are two ways to go about it. Simply remove the assumption that probin is only relevant to C functions; perhaps allowing a hardwired exception for builtin languages where allowing probin to be set would be deemed unsightly (ie, the easy way ;). Or, add a column to pg_language that specifies the language's probin usage so that pg_dump and the backend have an idea of how to handle these things for the given language(the takes a bit more work way). [I imagine the former could gracefully lead into the latter as well.] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] anoncvs still slow
On Sat, 27 May 2006, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: anoncvs (svr4, 66.98.251.159) is still slow responding to cvs update; it's been spotty for about a week now. Tcpdump shows connections being established but then long delays for ACKs, sometimes long enough for cvs to time out. Any updates on what's going on? Magnus apparently knows what the problem is: http://archives.postgresql.org/pgsql-hackers/2006-05/msg01002.php but I haven't seen any of the other mails he mentioned. svr4 / anoncvs needs a major upgrade ... the problem is that the only part of that vServer that I know nothing about is the bittorrent stuff, which, in itself, needs an upgrade ... I sent a note to Magnus that, whenever he's ready with the bittorrent stuff, I can do the rest of the upgrade, so its in his court right now :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] error in compilation!
I wrote: Currently looking through the rest of the patch. I'm wondering about appendStringLiteral: maybe we should kill that entirely in favor of using PQescapeStringConn? It's not nearly bright enough about encoding for instance (and it *will* be used in client-only encodings). We could make an appendStringLiteralConn, which would do this correctly for most of the utility programs. However there's a problem for pg_restore: it doesn't necessarily have a PGconn at all. (Consider the case of pg_restore producing text output.) It seems that the alternatives are to export PQescapeStringInternal from libpq, or to duplicate its functionality in appendStringLiteral. Don't much like either, but perhaps the second is less bad. Any opinions? 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] Inefficient bytea escaping?
On Sat, May 27, 2006 at 08:23:35PM +0300, Marko Kreen wrote: On 5/27/06, Martijn van Oosterhout kleptog@svana.org wrote: Given there is no way to know if you're running single threaded or not, I don't think glibc can take chances like that. There's CPP symbol _REENTRANT for that and in run time, libc can detect call to pthread_create [1]. There are a number of way to create threads, not all of which involve pthread_create. I think my point is that you are not required to declare _REENTRANT to get reentrant functions and there is no _NOTREENTRANT symbol you can define. I did a small test that does several fputc calls to /dev/null, with various workarounds: All your test proved was that it took 20 nanoseconds in each call to fputc to determine no locking was required. I don't know how fast your machine is, but thats probably just a few cycles. A better example would be if there was actually some locking going on, i.e. add -lpthread to the compile line. On my machine I get: No -lpthread lock.enabled 91s lock.disabled 50s lock.unlocked 36s With -lpthread lock.enabled 323s lock.disabled 50s lock.unlocked 36s So yes, if you can guarentee no locking is required and tell glibc that, you get optimal performace. But the *default* is to play it safe and take a few extra cycles to check if locking is required at all. Better than locking all the time wouldn't you agree? Just because your app didn't declare _REENTRANT doesn't mean any of the libraries it uses didn't. The crux of the matter is though, if you're calling something a million times, you're better off trying to find an alternative anyway. There is a certain amount of overhead to calling shared libraries and no amount of optimisation of the library is going save you that. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] osprey buildfarm member has been failing for a long while
osprey hasn't been able to build HEAD since the GIN code was added. I'm not sure that GIN is really to blame though, as the error looks like an out-of-memory problem while trying to link the backend: ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g -L../../src/port -Wl,-R'/data/postgresql/buildfarm/workdir/HEAD/inst/lib' -Wl,-E access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o ../../src/port/libpgport_srv.a -lintl -lcrypt -lm -o postgres ld in malloc(): error: brk(2) failed [internal error] gcc: Internal error: Abort trap (program ld) Please submit a full bug report. See URL:http://www.netbsd.org/Misc/send-pr.html for instructions. gmake[2]: *** [postgres] Error 1 Perhaps the swap space or ulimit setting on the box needs to be raised? 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] osprey buildfarm member has been failing for a long while
Tom Lane wrote: osprey hasn't been able to build HEAD since the GIN code was added. I'm not sure that GIN is really to blame though, as the error looks like an out-of-memory problem while trying to link the backend: ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g -L../../src/port -Wl,-R'/data/postgresql/buildfarm/workdir/HEAD/inst/lib' -Wl,-E access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o ../../src/port/libpgport_srv.a -lintl -lcrypt -lm -o postgres ld in malloc(): error: brk(2) failed [internal error] gcc: Internal error: Abort trap (program ld) Please submit a full bug report. See URL:http://www.netbsd.org/Misc/send-pr.html for instructions. gmake[2]: *** [postgres] Error 1 Perhaps the swap space or ulimit setting on the box needs to be raised? Or maybe ccache is the culprit - there have been suspicions before that ccache is responsible for errors, but it's never been confirmed. Remi, can you try turning it off and see what happens? just comment out the CC = cache gcc line in the config file. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] error in compilation!
Confirmation: The patch rollback in src/pl/plpgsql/src/gram.y resolved the issue. On 5/28/06, Tom Lane [EMAIL PROTECTED] wrote: I wrote: Currently looking through the rest of the patch. I'm wondering about appendStringLiteral: maybe we should kill that entirely in favor of using PQescapeStringConn? It's not nearly bright enough about encoding for instance (and it *will* be used in client-only encodings). We could make an appendStringLiteralConn, which would do this correctly for most of the utility programs. However there's a problem for pg_restore: it doesn't necessarily have a PGconn at all. (Consider the case of pg_restore producing text output.) It seems that the alternatives are to export PQescapeStringInternal from libpq, or to duplicate its functionality in appendStringLiteral. Don't much like either, but perhaps the second is less bad. Any opinions? 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 ---(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] error in compilation!
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am thinking it is best to always use E'' in that case. OK? I'm planning to revert it to the previous logic: E if there's any backslash. I think we have to do likewise in quote_literal() for much the same reason: insufficient confidence that we know how the result will be used. (Note dblink uses quote_literal for strings it will send to the other database.) Good point. Good thing only dblink and /contrib/tablefunc use quote_literal().. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] error in compilation!
Tom Lane wrote: I wrote: Currently looking through the rest of the patch. I'm wondering about appendStringLiteral: maybe we should kill that entirely in favor of using PQescapeStringConn? It's not nearly bright enough about encoding for instance (and it *will* be used in client-only encodings). We could make an appendStringLiteralConn, which would do this correctly for most of the utility programs. However there's a problem for pg_restore: it doesn't necessarily have a PGconn at all. (Consider the case of pg_restore producing text output.) It seems that the alternatives are to export PQescapeStringInternal from libpq, or to duplicate its functionality in appendStringLiteral. Don't much like either, but perhaps the second is less bad. Any opinions? I like the export idea myself. The less duplicate code the better. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Better to dump tabs as tabs, or \t?
COPY wants \r and \n to be used because it checks for line endings, but your change is only for the SQL strings, and you are right, it is more porable to dump as actual bytes than backslashes. --- Tom Lane wrote: Marko Kreen [EMAIL PROTECTED] writes: On 5/27/06, Tom Lane [EMAIL PROTECTED] wrote: Historically pg_dump has taken pains to dump ASCII control characters as backslash constructs, for instance \t for tab. I am thinking this is not such a great idea, and that it'd be more portable rather than less so if we got rid of that logic and just dumped tab as tab, etc. In particular, making this play nice with standard_conforming_strings seems unpleasant: we'll have to emit E'' strings which are certainly not portable, not even to older PG releases. Could we just give a switch to pg_dump, which toggles between standard_confirming_strings and old escaped strings? The plan is that it'll dump according to what it finds as the standard_conforming_strings setting on the source server. If you feel a need to override that setting, you can use PGOPTIONS or the other usual ways to set a GUC variable for a program. However, my thought on the point at hand is to just go over to dumping control characters literally in either case. This is backwards-compatible to all PG versions and I don't know of a reason to think it wouldn't work (at least as well as the backslash constructs anyway) for portability to other databases. Note: this only affects strings dumped as part of SQL commands; COPY data isn't at issue, since we're not planning to change the semantics of that. COPY has always dumped tab as \t and I don't intend to change it. But pg_dump --inserts would be affected, also strings appearing in view definitions and such. We have some precedent for this in that pg_dump has by default dumped function definitions as $$ literals for a release or two now, and no one's complained of whitespace getting munged in function definitions. 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 -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] RELKIND_SPECIAL
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: How about we remove RELKIND_SPECIAL? It was there only to support the XactLockTable hack, but we don't need that anymore. Go for it. Don't forget to remove the documentation mentions (catalog.sgml at least). Might be a good idea to leave the code for the case in psql/describe.c, though, just so psql doesn't choke if run on an old database? Done. I didn't find any other mentions in the documentation; I grepped for special and relkind. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Schema Limitations ?
Hello Hackers, I have the following questions, after reading this FAQ (http:// www.postgresql.org/docs/faqs.FAQ.html#item4.4) are there statistics around the max number of schemas in a database, max number of tables In a schema, and max number of tables in a database (number that spans schemas) ? Are the only limitations based on disk ram/swap ? Does anybody have a rough ballpark figures of the largest install base on those questions? I'm curious about these stats, because I'm debating on how best to break up data, between schemas, physical separate databases, and the combination of the two. Thanks In Advanced. Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
