Re: [HACKERS] Problem with restoring a 7.1 dump
Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: [snap] > How do I get this to work? > > Chris I think i did this: CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; This might be in the docs also. Try it :) Regards, Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WIN32 native ... lets start?!?
> On Thu, 2002-05-16 at 13:47, Joerg Hessdoerfer wrote: > > So, my route would be to get it to run *somehow* without paying > > attention to speed and not to change much of the existing code, > > THEN see how we could get rid of fork() on windows. > What is the biggest problem here? The Shmem/IPC stuff, or the fork() stuff? I'm think that we could do a fork() implementation in usermode by copying the memory allocations. How fast that would be regarding the context switches, i don't know, but i'm willing to experiment some to see how feesible this is... Anyone tried this before? Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WIN32 native ... lets start?!?
Joerg Hessdoerfer <[EMAIL PROTECTED]> wrote: [snip] > The problem is not the fork() call itself, this has been done (MinGW > and cygwin I know of, possibly others) but the speed of fork() on > windows, it's creepingly slow (due to usermode copy, I assume ;-). > > IPC needs to be done, I'm just about to start... > I'm not so familiar with the win32 kernel mode stuff. But i've seen programs using .vxd (kernelmode, ring X ?) helpers for getting more privileges, maybe cross process ones. Well, i'll look into this sometime if it's possible to reduce the context switches by going vxd. There must be some way to read protection of the pages and map them as COW or RO in the new process to get rid of much of the copy, but then again, we're talking microsoft here :) I once did a .exe loader that used the MapViewOfFile (win32 mmap) of the .exe itself to accomplish shared loadable modules that worked on x86 linux and win32 without recompile (might be something like the XFree86 binary gfx card drivers). Good luck on the IPC work! Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Redhat 7.3 time manipulation bug
Tom Lane <[EMAIL PROTECTED]> wrote: [snip] > > Exactly how much work (and code bulk) would we be taking on? I've > never looked at how big the timezone databases are... > Some answers on database sizes, if this is any help... I did "du -sh /usr/share/zoneinfo/" on them all. OpenBSD 3.1, sparc64: 1.3M/usr/share/zoneinfo/ Linux, i686, oldish mandrake (6.x?), glibc 2.1.3: 478k/usr/share/zoneinfo Linux, i686, newish redhat 7.2, glibc 2.2.4: 4.9M/usr/share/zoneinfo Linux, alpha EV56, oldish redhat 6.2, glibc 2.1.3 1.4M/usr/share/zoneinfo > regards, tom lane Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] [PATCH] Win32 native fixes after SSL updates (+more)
Hello, i noticed that win32 native stopped working/compiling after the SSL merge. So i took the opportunity to fix some stuff: 1. Made the thing compile (typos & needed definitions) with the new pqsecure_* stuff, and added fe-secure.c to the win32.mak makefile. 2. Fixed some MULTIBYTE compile errors (when building without MB support). 3. Made it do that you can build with debug info: "nmake -f win32.mak DEBUG=1". 4. Misc small compiler speedup changes. The resulting .dll has been tested in production, and everything seems ok. I CC:ed -hackers because i'm not sure about two things: 1. In libpq-int.h I typedef ssize_t as an int because Visual C (v6.0) doesn't define ssize_t. Is that ok, or is there any standard about what type should be used for ssize_t? 2. To keep the .dll api consistent regarding MULTIBYTE I just return -1 in fe-connect.c:PQsetClientEncoding() instead of taking away the whole function. I wonder if i should do any compares with the conn->client_encoding and return 0 if nothing would have changed (if so how do i check that?). Regards Magnus Naeslund -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- pgsql_win32.cleanups.1.diff Description: Binary data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] [ANNOUNCE] PQNotify java listen / notify hack
I heard people asking for listen/notify support in the jdbc driver from different pgsql lists. I don't know what the status is about that, but here's one solution. I did this quick hack for our own purposes, maybe someone would like to pick this code up and use it as we do... This code is not well tested, but it will be in the near future, if anyone have any comments/spot any bugs, please tell me. The code is in c++ & java, and was tested/works on win32 and linux/alpha (64bit). It needs the jdk JNI headers, since it uses the java native interface ... Just to be clear, this way we're using one separate connection to look at notifys, and do the work (if any) in a regular JDBC connection when we get the notify. Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] [ANNOUNCE] PQNotify java listen / notify hack
... And maybe i should attach the code aswell :) I'm not subscribed to pgsql-jdbc or pgsql-announce, so please CC me if your responding... Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- PGNotify-0.1.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Vacuum from within a function crashes backend
Hello, I did a vacuum from within a function, and it went sig11 on me. Is it illegal to do that? The function: drop function xorder1_cleanup(); create function xorder1_cleanup() RETURNS integer AS ' declare x record; c integer; begin c:=0; FOR x IN SELECT order_id,count(*) as cnt FROM xorder1_updates group by order_id LOOP if x.cnt > 1 then c:=c+x.cnt; delete from xorder1_updates where order_id = x.order_id; insert into xorder1_updates(order_id) values (x.order_id); end if; END LOOP; execute ''vacuum full analyse xorder1_updates;''; return c; end; ' LANGUAGE 'plpgsql'; Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Vacuum from within a function crashes backend
Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Magnus Naeslund(f) dijo: > >> Hello, >> >> I did a vacuum from within a function, and it went sig11 on me. >> Is it illegal to do that? > > Huh... what version is this? In current sources, VACUUM cannot be > run inside a function (it will throw an ERROR). In 7.2[.1] I see > there is no protection against this. > > Maybe the fix for this should be backported to 7.2 also. Argh! Sorry i forgot the version, it's as you say 7.2.1.. Then i'll just not do that :) Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] The rh7.3 time errors
Was there a workaround for the errors in time handling for rh7.3 dist? I get there regression failures: abstime ... FAILED tinterval... FAILED test horology ... FAILED I remember the discussion about old dates, but not if there was any fix for it... Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] The rh7.3 time errors
Joe Conway <[EMAIL PROTECTED]> wrote: > Magnus Naeslund(f) wrote: >> Was there a workaround for the errors in time handling for rh7.3 >> dist? >> >> I get there regression failures: >> abstime ... FAILED >> tinterval... FAILED >> test horology ... FAILED >> >> I remember the discussion about old dates, but not if there was any >> fix for it... >> > > Tom fixed this just before we went into beta. Are you using a recent > snapshot? > > Joe As usual, i never remember to supply version information. I'm using latest stable, 7.2.2. Is there a quick workaround for this version, or must there be code ? Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] The rh7.3 time errors
Bruce Momjian <[EMAIL PROTECTED]> wrote: > > The change was to use localtime() rather than mktime() in the code. > There is no workaround available for 7.2.X, and I don't see that > anyone backpatched it to 7.2 CVS. However, we are considering a > 7.2.3 and a backpatch of that fix may be worthwhile. > That would be excellent, because it feels awkward installing stuff that doesn't pass the regression tests, as all our new linux boxes will be rh7.3. But right now in our apps we're not relying on the time being right (isn't that the issue?) only the years... If it's a simple fix, i think we should include that in the next 7.2.X . Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Damn slow query
Hello, i've got this query that's really slow... Figure this: testdb=> select now() ; select gid from bs where gid not in ( select x from z2test ); select now(); now --- 2002-10-09 22:37:21.234627+02 (1 row) gid -- (524 rows) now --- 2002-10-09 23:20:53.227844+02 (1 row) That's 45 minutes i don't wanna spend in there... I got indexes: testdb=> \d bs_gid_idx Index "bs_gid_idx" Column | Type +--- gid| character varying(16) online | smallint btree testdb=> \d z2test_x_idx; Index "z2test_x_idx" Column | Type +--- x | character varying(16) btree Rowcounts are: testdb=> select count(*) from bs ; select count(*) from z2test ; count --- 25376 (1 row) count --- 19329 (1 row) The bs table have many other columns besides the gid one, the z2test table only has the x column. How can i speed this query up? It never scans by the indexes. I know it's a lot of iterations anyway i do it, but this is too damn slow. I can't profile anything at this box, because it's in production state, but if you really want me to, i'll do it tomorrow on another box. Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Damn slow query
Joe Conway <[EMAIL PROTECTED]> wrote:
> "IN (subselect)" is notoriously slow (in fact it is an FAQ). Can you
> rewrite this as:
>
...
Stephan Szabo <[EMAIL PROTECTED]> wrote:
> Per FAQ suggestion, try something like
...
Thanks alot, below are the results on your suggestions, quite an
dramatic differance (but this is another box, faster, and running 7.3b2
so the 45 minutes doesn't hold here, but it took more than 10 minutes
before i stopped the original query).
Is this an todo item, or should every user figure this out (yeah i know
i should have read the FAQ when it went so totally bad).
The NOT IN it seems quite natural here, but then again, i don't think as
the db as you do :)
mag=> \timing
Timing is on.
mag=> explain analyze select count(gid) from bs where not exists (
select * from z2test where z2test.x=bs.gid );
Aggregate (cost=129182.18..129182.18 rows=1 width=9) (actual
time=590.90..590.90 rows=1 loops=1)
-> Seq Scan on bs (cost=0.00..129150.46 rows=12688 width=9) (actual
time=42.57..590.46 rows=524 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using z2temp_x_idx on z2test (cost=0.00..5.07
rows=1 width=9) (actual time=0.02..0.02 rows=1 loops=25376)
Index Cond: (x = $0)
Total runtime: 591.01 msec
Time: 592.25 ms
mag=> EXPLAIN analyze select count(b.gid) from bs b left join z2test z
on z.x = b.gid where z.x IS NULL;
Aggregate (cost=1703.65..1703.65 rows=1 width=18) (actual
time=370.31..370.31 rows=1 loops=1)
-> Hash Join (cost=346.61..1640.21 rows=25376 width=18) (actual
time=75.45..369.91 rows=524 loops=1)
Hash Cond: ("outer".gid = "inner".x)
Filter: ("inner".x IS NULL)
-> Seq Scan on bs b (cost=0.00..595.76 rows=25376 width=9)
(actual time=0.01..34.20 rows=25376 loops=1)
-> Hash (cost=298.29..298.29 rows=19329 width=9) (actual
time=43.82..43.82 rows=0 loops=1)
-> Seq Scan on z2test z (cost=0.00..298.29 rows=19329
width=9) (actual time=0.02..22.69 rows=19329 loops=1)
Total runtime: 370.42 msec
Time: 371.90 ms
mag=>
Magnus
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Damn slow query
Bruce Momjian <[EMAIL PROTECTED]> wrote: > > We already have a TODO item: > > * Allow Subplans to use efficient joins(hash, merge) with upper > variable Cool. One thing to note here is that the JOIN query that Joe suggested is both faster than the subselect thing (no suprise) but also don't care if z2test has an index on it or not. The subselect query started taking huge amount of time again if i dropped the z2test_x_idx ... So if the todo could somehow figure out that that subselect should be an JOIN instead of an NOT EXISTS query, that would be great, because the index on z2test isn't that super-obvious (i think, because i know the data is tiny). Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] About the Request for supported platforms
Is there i faq item or maybe some other way that we can tell users that --disable-rpath should be used when checking regressiontests if you have an install already on the box? The rpath defaults to the installdir even in the temporary install that is made when doing "gmake check", and that confuses some programs (like pg_encoding) because it loads the installed libpq. In my case that's an old install running in production state. There are tools that set the rpath on an already linked executable, but i dunno if that feels appropriate. But maybe an warning in "gmake check" would be nice. Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Request for supported platforms
Robert Treat <[EMAIL PROTECTED]> wrote: > I noticed that the supported platforms listed 7.2 for linux alpha but > with yesterdays date. I figured it was just a typo, but thought I > would try to compile myself just to be sure, but I received 4 > failures: horology,geometry,opr_sanity, and misc > > This is on alpha running debian 3 linux > uname -a = Linux usf-cf-alpha-linux-1 2.2.20 #2 Wed Mar 20 19:57:28 > EST 2002 alpha unknown > > regression output can be seen at: > http://phppgadmin.sourceforge.net/regression.diffs > http://phppgadmin.sourceforge.net/regression.out > > my alpha history is spotty at best, perhaps someone else can help me > interpret things. > > Robert Treat I think on Linux Alpha and Sparc alot has to do with the gcc compiler version. My system is handupgraded from Redhat 6.2, kernel 2.2.x to be using a much newer kernel (2.4.X-aaX). But it's been working flawlessly with almost any kernel from 2.2 up to where i am now, using postgresql from the 6.x days. What compiler version are you using? Is this Debian release a stable branch? Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for supported platforms
On an rh6.2, "Linux gsX 2.4.19-pre6 #4 Thu Apr 11 07:17:39 CEST 2002 alpha unknown" (ev56 ruffian) i get this failure. Should ./configure && gmake && gmake check be the only things i need to do? == creating database "regression" == /home/mag/d/postgresql/postgresql-7.3b3/src/test/regress/./tmp_check/ins tall//usr/local/pgsql/bin/pg_encoding: error in loading shared libraries: /home/mag/d/postgresql/postgresql-7.3b3/src/test/regress/./tmp_check/ins tall//usr/local/pgsql/bin/pg_encoding: undefined symbol: pg_char_to_encoding createdb: "SQL_ASCII" is not a valid encoding name pg_regress: createdb failed gmake[2]: *** [check] Error 2 gmake[2]: Leaving directory `/home/mag/d/postgresql/postgresql-7.3b3/src/test/regress' gmake[1]: *** [check] Error 2 gmake[1]: Leaving directory `/home/mag/d/postgresql/postgresql-7.3b3/src/test' gmake: *** [check] Error 2 Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for supported platforms
Linux alpha. An Redhat 6.2, "Linux gsX 2.4.19-pre6 #4 Thu Apr 11 07:17:39 CEST 2002 alpha unknown" (ev56 ruffian). gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) ./configure \ --prefix=/usr/local/pgsql-7.3b3 \ --with-maxbackends=512 \ --with-tcl \ --without-tk \ --with-perl\ --with-openssl \ --enable-odbc \ --with-CXX The box passes every tests except Horology. It's wintertime here aswell, as i read in some other report. It installs and runs nicely. Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] 7.3b5 Supported platforms alpha linux update
If it matters, i re-ran the regression tests on psql 7.3b5, and it worked flawlessly, all 89 tests passed. I didn't install, but it passed the last time (b3). System: Redhat Linux 6.2, Alpha EV56, handupgraded to kernel 2.4.19-pre6aa1. Options used: ./configure \ --disable-rpath \ --prefix=/usr/local/pgsql-7.3b5 \ --with-maxbackends=512 \ --with-tcl \ --without-tk \ --with-perl\ --with-openssl \ --enable-odbc \ --with-CXX Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RC1?
Tom Lane <[EMAIL PROTECTED]> wrote: [snip] > >> Note that we have *zero* reports for any flavor of NetBSD and >> OpenBSD. > > Maybe they're both dead platforms? ;-) > Well, OpenBSD isn't dead :) But i have problems compiling 7.3b5 on it (OpenBSD 3.1 i386). I figured i should give it a go, since nobody else did, but i get many regression failures. Then i tried 7.2.3, and it too gives alot of regression failures. Both were configured with: ./configure \ --with-perl\ --with-openssl \ --enable-odbc \ --with-CXX And nothing else. The regression diffs can be found at: http://gimme.smisk.nu/~mag/pgsql/ Is there some kind of gotcha with compiling pgsql on OpenBSD? I've never tried it before. Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RC1?
Magnus Naeslund(f) <[EMAIL PROTECTED]> wrote: > > Well, OpenBSD isn't dead :) > But i have problems compiling 7.3b5 on it (OpenBSD 3.1 i386). > I figured i should give it a go, since nobody else did, but i get many > regression failures. > OK OK, before anyone rubs my nose in it, i see the fork() failures :) I just sent the mail without looking. I'll see what's causing the fork() problems... Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Add OpenBSD 3.1 i386 to supported platforms (was: RC1?)
Tom Lane <[EMAIL PROTECTED]> wrote: > Too low processes-per-user limit, likely. Yes, ofcourse... This is what happens when you're in a hurry and tries to make everything happen at the same time :) Now it all passes: OpenBSD 3.1 i386 ./configure \ --with-perl\ --enable-odbc \ --with-CXX All 89 tests passed. Installation and some small testing seems to work just fine. Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Add OpenBSD 3.1 i386 to supported platforms (was: RC1?)
Bruce Momjian <[EMAIL PROTECTED]> wrote: > Ports list updated: > > http://candle.pha.pa.us/main/writings/pgsql/sgml/supported- > platforms.html > > Also, I assume the "(f)" is part of your name, right? > Cool. No the (f) part is really a mail account thing that (I/we)'ve traditionally had. If it's (w) it's posted from my webmail account, if it's (b) it's from my [EMAIL PROTECTED] account. Silly thing, but makes/made sense in our context :) Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
Mysql is planning on making this work:
SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
Do we have anything like it (After a discussion with Tom i figure no).
User variables is nice, especially in these kind of queries.
Nice would be to be able to use them as in C (almost everywhere):
SELECT id, @x FROM table_name t where (@x := date_part('days'. now() -
f.created)) > 100;
As Tom said in earlier mail, it might not be that big of a win in this
case, but if uses expensive functions, it will.
Magnus
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: >> Mysql is planning on making this work: >> >> SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY >> id. >> >> Do we have anything like it (After a discussion with Tom i figure >> no). User variables is nice, especially in these kind of queries. > > Well of course they have to make that work - they don't have > subselects :P > > Chris Yeah, but there is a point about running count(*) one time too many. Say if i would like to get a prettyprinting query like this: SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name; That would be DAMN expensive doing with a subselect: SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM table_name; I know this example suck eggs, but you get the point where it hurts, right? Magnus - sorry for the dupe, chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: > Are you sure that postgres evaluates that subselect more than once? > It looks to me like it returns a constant result for every row and > hence it will be evaluated once per statement, not once per row. I'm > no expert tho. Can someone answer this? > > And if the subselect changes for each row (ie. it's a correlated > subquery) then you cannot use the variable anyway. > > It seems to me that if postgres doesn't consider count(*) as a > constant then perhaps it should be taught to? Should be safe > shouldn't it? I guess if a function in your select statemnt is > inserting a row then there's trouble. But if there is, then the > sum/count(*) is nonsensical anyway. > > Chris > It looks like it (7.2.x): # time psql genline -c "select id from " > /dev/null real0m0.694s user0m0.147s sys 0m0.025s # time psql genline -c "select id,id||'/'||(select count(*) from ) as x from " > /dev/null real0m2.202s user0m0.263s sys 0m0.040s # time psql genline -c "select id,(select count(*) from bildsekvens) as x from " > /dev/null real0m1.479s user0m0.254s sys 0m0.047s They were taken from a busy system, but i ran the several times showing about the same result. Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Stephan Szabo <[EMAIL PROTECTED]> wrote: > > If you use a scalar subquery, yes, but I think a subselect in from > would help, maybe something like (if you want the total count) > > select table_name.id, sum(sum_col)||'/'||t2.count from table_name, > (select count(*) as count from table_name) as t2 group by > table_name.id,t2.count; > > or (if you want each count the counter per group) either > > select id, sum(sum_col)||'/'||count(*) from table_name > group by id; > > or > > select table_name.id, sum(sum_col)||'/'||t2.count from table_name, > (select id, count(*) as count from table_name group by id) as t2 where > table_name.id=t2.id group by table_name.id,t2.count; > Give it up already, i was MAKING A POINT, not trying to make an optimized count(*) thing :) There are other examples that you cannot get around, that will be evaluated more than once when a local "user variable" would make it not need to. Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote: > >> Now convert this query so that it only evaluates the date_part thing >> ONCE: > > That's not a good idea as long as t.stamp varies from row to row. ;) > Perhaps once per row, maybe... :) > I give up ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Hannu Krosing <[EMAIL PROTECTED]> wrote: > Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18: >> It looks like it (7.2.x): >> >> # time psql genline -c "select id from " > /dev/null >> real0m0.694s >> user0m0.147s >> sys 0m0.025s >> # time psql genline -c "select id,id||'/'||(select count(*) from >> ) as x from " > /dev/null >> >> real0m2.202s >> user0m0.263s >> sys 0m0.040s >> >> # time psql genline -c "select id,(select count(*) from bildsekvens) >> as x from " > /dev/null >> >> real0m1.479s >> user0m0.254s >> sys 0m0.047s > > what is the time for > > select id,x > from , >(select count(*) as x from bildsekvens) c ; > > > time psql genline -c "select id,x from , (select count(*) as x from ) c ;" > /dev/null real0m1.354s user0m0.268s sys 0m0.028s The and the other table is the same table, sorry i screwed up in the last cut'n'paste operation. As i said it's a loaded system, the figures vary a little bit between runs. Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Bruce Momjian <[EMAIL PROTECTED]> wrote:
> Magnus Naeslund(f) wrote:
>>> select id, sum(sum_col)||'/'||count(*) from table_name
>>> group by id;
>>>
>>> or
>>>
>>> select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
>>> (select id, count(*) as count from table_name group by id) as t2
>>> where table_name.id=t2.id group by table_name.id,t2.count;
>>>
>>
>> Give it up already, i was MAKING A POINT, not trying to make an
>> optimized count(*) thing :)
>> There are other examples that you cannot get around, that will be
>> evaluated more than once when a local "user variable" would make it
>> not need to.
>
> Here's an even slimmer query that makes a var:
>
> test=> select var1.* from (select 1) as var1;
> ?column?
> --
> 1
> (1 row)
Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) > 20;
I hope you all are kidding me in not seeing the real issue i'm trying to
show here.
Cheers
Magnus
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] Backend crash with tsearch
I'm evaluating tsearch contrib module, and i get a backend crash when i'm about to use a tsearch function. When i issue update things set nidx=txt2txtidx(productname), didx=txt2txtidx(longdescription); The backend dies in a segfault. The system is redhat 7.3 dual athlon w/ 1GB memory. Postgresql is compiled with -march=athlon -O3. initdb -E LATIN1 I have a huge shared buffer count (65536). I'll reinstall tsearch and try again soon. Is it necesary to install OpenFTS contrib aswell, or do i get away with only installing tsearch? Now i do both... Backtrace: #0 0x02d1 in ?? () #1 0x401faf48 in ?? () #2 0x401fb5e6 in ?? () #3 0x080d8f5c in ExecMakeFunctionResult (fcache=0x82d3710, arguments=0x82ce170, econtext=0x82d3580, isNull=0xbfffec8f "", isDone=0xbfffecd8) at execQual.c:839 #4 0x080d99a3 in ExecEvalExpr (expression=0x82ce188, econtext=0x82d3580, isNull=0xbfffec8f "", isDone=0xbfffecd8) at execQual.c:1168 #5 0x080d9d44 in ExecTargetList (targetlist=0x82ce3d8, nodomains=21, targettype=0x82cf230, values=0x82d4488, econtext=0x82d3580, isDone=0xbfffee78) at execQual.c:2058 #6 0x080da13f in ExecProject (projInfo=0x82d3b08, isDone=0xbfffee78) at execQual.c:2282 #7 0x080da229 in ExecScan (node=0x82cfeb8, accessMtd=0x80e1270 ) at execScan.c:133 #8 0x080e1093 in ExecSeqScan (node=0x82cfeb8) at nodeSeqscan.c:133 #9 0x080d7d9c in ExecProcNode (node=0x82cfeb8, parent=0x0) at execProcnode.c:291 #10 0x080d6a47 in ExecutePlan (estate=0x82d, plan=0x82cfeb8, operation=CMD_UPDATE, numberTuples=0, direction=ForwardScanDirection, destfunc=0x82d3b30) at execMain.c:954 #11 0x080d7682 in ExecutorRun (queryDesc=0x82d35f0, estate=0x82d, direction=ForwardScanDirection, count=0) at execMain.c:195 #12 0x0812a8cb in ProcessQuery (parsetree=0x82cb1c8, plan=0x82cfeb8, dest=Remote, completionTag=0xb060 "") at pquery.c:242 #13 0x08128b81 in pg_exec_query_string (query_string=0x82cb0a8, dest=Remote, parse_context=0x8291cd0) at postgres.c:838 #14 0x08129b50 in PostgresMain (argc=4, argv=0xb2e0, username=0x827ccd1 "mag") at postgres.c:2016 #15 0x0810f0c4 in DoBackend (port=0x827cba0) at postmaster.c:2293 #16 0x0810e9dc in BackendStartup (port=0x827cba0) at postmaster.c:1915 #17 0x0810de8d in ServerLoop () at postmaster.c:1000 #18 0x0810da24 in PostmasterMain (argc=1, argv=0x8245640) at postmaster.c:779 #19 0x080ea5c2 in main (argc=1, argv=0xbc74) at main.c:210 #20 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6 Magnus -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Backend crash with tsearch
Tom Lane <[EMAIL PROTECTED]> wrote: > "Magnus Naeslund(f)" <[EMAIL PROTECTED]> writes: >> The backend dies in a segfault. > >> Backtrace: > >> #0 0x02d1 in ?? () >> #1 0x401faf48 in ?? () >> #2 0x401fb5e6 in ?? () >> #3 0x080d8f5c in ExecMakeFunctionResult (fcache=0x82d3710, >> arguments=0x82ce170, econtext=0x82d3580, isNull=0xbfffec8f "", >> isDone=0xbfffecd8) at execQual.c:839 > > Did you compile tsearch with debug support? If so, the lack of any > symbolic info here must mean that gdb didn't know where to find the > tsearch .so module. You could get a more useful trace by telling > gdb > sharedlibrary /path/to/tsearch.so > > regards, tom lane > I'm working on it (--enable-debug --enable-cassert). It's either that it can't load the lib (shouldn't it complain?) or it's a bad pointer. We'll find out, i hope... Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Backend crash with tsearch
Tom Lane <[EMAIL PROTECTED]> wrote: > > Be sure to eliminate the possibility that you're loading the wrong > version of the .so (ie, loading a 7.2 tsearch.so into 7.3). People > get bit by that quite frequently right after an upgrade ... > Well, this is a clean install, so that isn't a problem. Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Backend crash with tsearch
More info, the gdb> sharedlibrary loaded some more symbols: (gdb) bt #0 0x02d1 in ?? () #1 0x401faf48 in parsetext (prs=0xbfffea60, buf=0x4277eb3c "Can - Live 1971-77", buflen=18) at txtidx.c:366 #2 0x401fb5e6 in txt2txtidx (fcinfo=0xbfffeaf0) at txtidx.c:487 #3 0x080ec45c in ExecMakeFunctionResult (fcache=0x83172bc, arguments=0x831187c, econtext=0x8317114, isNull=0xbfffec8f "", isDone=0xbfffecd8) at execQual.c:839 #4 0x080ed023 in ExecEvalExpr (expression=0x8311898, econtext=0x8317114, isNull=0xbfffec8f "", isDone=0xbfffecd8) at execQual.c:1168 #5 0x080ed3c4 in ExecTargetList (targetlist=0x8311b20, nodomains=21, targettype=0x8312b1c, values=0x83180a0, econtext=0x8317114, isDone=0xbfffee78) at execQual.c:2058 #6 0x080ed7bf in ExecProject (projInfo=0x8317f90, isDone=0xbfffee78) at execQual.c:2282 #7 0x080ed8a9 in ExecScan (node=0x8315e60, accessMtd=0x80f4fa0 ) at execScan.c:133 #8 0x080f4e73 in ExecSeqScan (node=0x8315e60) at nodeSeqscan.c:133 #9 0x080eafbc in ExecProcNode (node=0x8315e60, parent=0x0) at execProcnode.c:291 #10 0x080e99f7 in ExecutePlan (estate=0x83161ac, plan=0x8315e60, operation=CMD_UPDATE, numberTuples=0, direction=ForwardScanDirection, destfunc=0x8317fbc) at execMain.c:954 #11 0x080ea999 in ExecutorRun (queryDesc=0x831718c, estate=0x83161ac, direction=ForwardScanDirection, count=0) at execMain.c:195 #12 0x08143b9b in ProcessQuery (parsetree=0x830c8c4, plan=0x8315e60, dest=Remote, completionTag=0xb060 "") at pquery.c:242 #13 0x08141dc1 in pg_exec_query_string (query_string=0x830c79c, dest=Remote, parse_context=0x82d6e88) at postgres.c:838 #14 0x08142e1d in PostgresMain (argc=4, argv=0xb2e0, username=0x82c23a9 "mag") at postgres.c:2016 #15 0x08125544 in DoBackend (port=0x82c2278) at postmaster.c:2293 #16 0x08124e5c in BackendStartup (port=0x82c2278) at postmaster.c:1915 #17 0x0812430d in ServerLoop () at postmaster.c:1000 #18 0x08123e94 in PostmasterMain (argc=1, argv=0x8276d00) at postmaster.c:779 #19 0x080fefe2 in main (argc=1, argv=0xbc74) at main.c:210 #20 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6 Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Backend crash with tsearch
Teodor Sigaev <[EMAIL PROTECTED]> wrote:
> Does it crashed?
> # select txt2txtidx('Can - Live 1971-77');
>
>
> Line txtidx.c:366 :
> lemm = lemmatize(token, &lenlemm, type);
>
> lemmatize() is defined in morph.c. Did you use another modules for
> postgresql?
>
> It seems to me that we see a name conflict. Function lemmatize is
> defined in somewhere also.
>
>
This is what i found out aswell, but isn't lemmatize resolved at
compiletime?
The only other module is Search-OpenFTS.
I'll check if i see any conflicts in $prefix/lib
Magnus
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Backend crash with tsearch
Teodor Sigaev <[EMAIL PROTECTED]> wrote: > Pls, send backtrace... :) > I already have, twice. Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Backend crash with tsearch
Some more (useless) info. objdump -x /lib/*.so /usr/lib/*.so /lib/i686/*.so /usr/kerberos/lib/*.so /usr/local/pgsql/bin/* /usr/local/pgsql/lib/*.so | grep lemmatize reviels only one lemmatize symbol. The offending address 0x02d1 is not mapped anywhere in the address space according to /proc//maps. Nice that the coredump is 522MB ;) Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Backend crash with tsearch
Oleg Bartunov <[EMAIL PROTECTED]> wrote: > > Please, tell us postgresql version. Did you reinstall tsearch after > upgrading ? Test-suite (data, sql) demonstrated the problem would be > nice. > pgsql 7.3, about 700mb text database with product descriptions. I'm working on isolation the behavior, the tsearch make installcheck seems to be crashing aswell. Is a "psql regression < tsearch.sql" needed, or is that done automatically in the installcheck? > For contrib/tsearch you need only tsearch :) > :) I hope this is because of something silly. Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Backend crash with tsearch
Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: >> I'll reinstall tsearch and try again soon. >> Is it necesary to install OpenFTS contrib aswell, or do i get away >> with only installing tsearch? >> Now i do both... > > Can you give us the compressed text? I can try it on my installation > and see if there's the same problem? > > Chris No i can't, it's not my data to give :( But it doesn't matter since if you run "gmake installcheck" in contrib/tsearch it will explode. A funny thing is that i installed pg7.3 on an linux intel celeron system (rh8.0) w/128 mb memory, and THERE it works! Athlon dependent? (Well maybe not, the rest of 7.3 works and passes all regressiontests) Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Backend crash with tsearch
Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: > > Works on FreeBSD/Alpha for me. Maybe you've got some weirdness with > bad RAM chips or something? > > Chris Could be, but it only shows when i do this, and the server has been up for several months. If everything else failes, i'll run memtest86 on it. Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Backend crash with tsearch [NAILED][HELP!]
Ok, I nailed the bug, but i'm not sure what the correct fix is. Attached tsearch_morph.diff that remedies this problem by avoiding it. Also there's a debug aid patch if someone would like to know how i finally found it out :) There problem in the lemmatize() function is that GETDICT(...) returned a value not handled (BYLOCALE). The value (-1) and later used as an index into the dicts[] array. After that everything went berserk stack went crazy somehow so trapping the fault sent me to the wrong place, and every time i read the value it was positive ;) So now i just return the initial word passed to the lemmatize function, because i don't know what to do with it. So you tsearch guys will have to work it out :) Magnus tsearch_morph.c.diff Description: Binary data tsearch_morph.c.debugaid Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Backend crash with tsearch
Oleg Bartunov <[EMAIL PROTECTED]> wrote: > Magnus, > > what is an output of 'make installcheck' ? As i said, it segfaults: [root@fet1b tsearch]# gmake installcheck gmake -C ../../src/test/regress pg_regress gmake[1]: Entering directory `/usr/src/postgresql-7.3/src/test/regress' gmake[1]: `pg_regress' is up to date. gmake[1]: Leaving directory `/usr/src/postgresql-7.3/src/test/regress' ../../src/test/regress/pg_regress tsearch (using postmaster on Unix socket, default port) == dropping database "regression" == DROP DATABASE == creating database "regression" == CREATE DATABASE ALTER DATABASE == dropping regression test user accounts == == installing PL/pgSQL== == running regression test queries== test tsearch ... FAILED == 1 of 1 tests failed. == The differences that caused some tests to fail can be viewed in the file `./regression.diffs'. A copy of the test summary that you see above is saved in the file `./regression.out'. gmake: *** [installcheck] Error 1 The regression.diff is attached. Magnus regression.diffs Description: Binary data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Backend crash with tsearch
Oleg Bartunov <[EMAIL PROTECTED]> wrote: > Magnus, we need backtrace from 'make installcheck' > As you wish... This is a bt taken from a core file this time (the other ones were from attached processes). The whole thing has been recompiled with no additional compiler flags (i.e. removed -march=athlon -O3), but still with --enable-debug and --enable-cassert. (gdb) bt #0 0x02d1 in ?? () #1 0x401f9071 in parsetext (prs=0xbfffeb70, buf=0x82ca550 "345 [EMAIL PROTECTED] ' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.w"..., buflen=564) at txtidx.c:366 #2 0x401f93c6 in txt2txtidx (fcinfo=0xbfffebe0) at txtidx.c:487 #3 0x080e2af0 in ExecMakeFunctionResult (fcache=0x82cad44, arguments=0x82ca958, econtext=0x82cabc0, isNull=0xbfffed3f "", isDone=0xbfffed40) at execQual.c:839 #4 0x080e2fc1 in ExecEvalFunc (funcClause=0x82ca974, econtext=0x82cabc0, isNull=0xbfffed3f "", isDone=0xbfffed40) at execQual.c:1168 #5 0x080e3755 in ExecEvalExpr (expression=0x82ca974, econtext=0x82cabc0, isNull=0xbfffed3f "", isDone=0xbfffed40) at execQual.c:1715 #6 0x080e3a34 in ExecTargetList (targetlist=0x82ca9a0, nodomains=1, targettype=0x82cac0c, values=0x82cacfc, econtext=0x82cabc0, isDone=0xbfffef28) at execQual.c:2058 #7 0x080e3cc9 in ExecProject (projInfo=0x82cacd0, isDone=0xbfffef28) at execQual.c:2282 #8 0x080e9dcb in ExecResult (node=0x82ca9bc) at nodeResult.c:160 #9 0x080e17e9 in ExecProcNode (node=0x82ca9bc, parent=0x0) at execProcnode.c:280 #10 0x080e05c3 in ExecutePlan (estate=0x82caa74, plan=0x82ca9bc, operation=CMD_SELECT, numberTuples=0, direction=ForwardScanDirection, destfunc=0x82cad18) at execMain.c:954 #11 0x080dfbfd in ExecutorRun (queryDesc=0x82caa48, estate=0x82caa74, direction=ForwardScanDirection, count=0) at execMain.c:195 #12 0x08132e33 in ProcessQuery (parsetree=0x82bb344, plan=0x82ca9bc, dest=Remote, completionTag=0xb0b0 "") at pquery.c:242 #13 0x08131414 in pg_exec_query_string (query_string=0x82ba124, dest=Remote, parse_context=0x8284684) at postgres.c:838 #14 0x08132535 in PostgresMain (argc=4, argv=0xb2e0, username=0x826fe61 "root") at postgres.c:2016 #15 0x08117b34 in DoBackend (port=0x826fd30) at postmaster.c:2293 #16 0x08117486 in BackendStartup (port=0x826fd30) at postmaster.c:1915 #17 0x08116689 in ServerLoop () at postmaster.c:1000 #18 0x0811623a in PostmasterMain (argc=1, argv=0x82568a0) at postmaster.c:779 #19 0x080f3293 in main (argc=1, argv=0xbc74) at main.c:210 #20 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6 Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Backend crash with tsearch
Teodor Sigaev <[EMAIL PROTECTED]> wrote: > > Sorry, I have no any idea. Just only full reinstall (with initdb > and rm -rf /usr/local/pgsql) postgresql... > Can you give me login on you computer for a several hours? > The thing is that when i ran the thing breakpointing on parsetext() at the line of the call i could type "cont" many times. Could there be some kind of memory corruption, someone overwriting memory? Sorry, i can't hand out a login to the box :( I did a total re-install just now, and it still crashes. Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Backend crash with tsearch
Oleg Bartunov <[EMAIL PROTECTED]> wrote:
>
> So, the problem may be in rh 7.3 ?
>
Might be, i'm debugging it now, and i can see that the dicts[] array in
morph.c is beeing overwritten with junk.
I can trigger it with this query:
select txt2txtidx('Can - Live 1971-77');
Is there any good way of adding watches on any type of memory?
(I'm not that good with gdb -yet :))
Magnus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] 7.3 txt2txtidx -> crash
Yes i found this bug earlier.
There is a patch for it in the mail archives.
Magnus
Argo Priivits <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a problem with contrib/tsearch module.
>
> Simple select txt2txtidx('2-3') causes psql to crash with error:
>
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>
>
> As I found out, this happens allways when in txt2txtidx input
> parameter occurs string with pattern: "number hyphen" or "hyphen
> number" !!!
>
> pg 7.3
> redhat 7.3
>
> Thanks, any idea ?
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Is anybody out there !!!
No :) Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Error using cursors/fetch and execute
I have a problem...
We're using cursors to be able to fetch X tuples from the server.
If we would take 'em all our app would blow up because of memory
constraints.
What i would like to is something like this:
mag=# create table test (id serial unique primary key, txt text);
mag=# insert into test(txt) values('hoho1');
mag=# prepare berra (integer) as select * from test where id = $1;
mag=# declare berra_c cursor for execute berra(1);
ERROR: parser: parse error at or near "execute" at character 28
Is there any other way of fetching less than all rows at once, similar
to that of using cursors. I don't use it for any other purpose than
that.
Magnus
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Programmer/Networker [|] Magnus Naeslund
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Fw: [HACKERS] Error using cursors/fetch and execute
I forgot to reply to the list aswell... Magnus - Original Message - From: "Magnus Naeslund(f)" <[EMAIL PROTECTED]> To: "Jeroen T. Vermeulen" <[EMAIL PROTECTED]> Sent: Tuesday, January 07, 2003 3:32 PM Subject: Re: [HACKERS] Error using cursors/fetch and execute > Jeroen T. Vermeulen <[EMAIL PROTECTED]> wrote: > > begin; > > declare berra cursor for select * from test where id = 1; > > fetch 100 from berra; > > [...] > > end; > > > > Oh, i'm sorry, i'm unclear. > I mean: "is there a way of doing exactly the above using prepared > statements?". > We're doing the above currently, and that works ofcourse... > > > > > Jeroen > > Magnus > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] v7.2.4 bundled ...
Redhat 6.2 Linux gserver1 2.4.19-pre6 #4 Thu Apr 11 07:17:39 CEST 2002 alpha unknown All 79 tests passed. Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 7.1 vacuum
How does 7.1 work now with the vacuum and all? Does it go for indexes by default, even when i haven't run a vacuum at all? Does vacuum lock up postgres? It says the analyze part shouldn't, but how's that for all of the vacuum? An 7.0.3 db we have here we are forced to run vacuum every hour to get an acceptable speed, and while doing that vacuum (5-10 minutes) it totaly blocks our application that's mucking with the db. Just curious Magnus Naeslund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] PQftype()
Where do get a listing of what PQftype() can return to me? (that is what type the field/col has, need a list of Oid's i believe) Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PQftype()
From: "Tom Lane" <[EMAIL PROTECTED]> > "Magnus Naeslund\(f\)" <[EMAIL PROTECTED]> writes: > > Where do get a listing of what PQftype() can return to me? > > select oid, typname from pg_type > > regards, tom lane Does these change often? Or could i do like the ODBC driver, autogenerate a .h out of that table. Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PQftype()
From: "Tom Lane" <[EMAIL PROTECTED]> [snip] > > The system type OIDs are stable. User-defined types would probably have > a new OID after a dump and reload. > > > Or could i do like the ODBC driver, autogenerate a .h out of that table. > > I would not recommend relying on compiled-in OID knowledge for any types > other than the system-defined datatypes. If you expect to have to deal > with user-defined types, it's best to cache the results of pg_type > lookups at the client end. You need not worry about OIDs changing > during a single client connection. > > regards, tom lane > Ok, then i can use static thing for my application (for now atleast). Thanks.. Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Not scanning by index
I have a table: create table forsamling ( id SERIAL, for_id int4 unique not null, kund_flag int8 not null default 1, online smallint default 0, klarsmallint default 0, ); create index forsamling_idx on forsamling(for_id,online,klar,kund_flag); It has about 1000 entries in this table... Why doesn't it go by indexes when i search the smallints and int8s, but it works with the integer SERIAL (SERIAL creates it's own index)? What can i do to make it go by index? /Magnus explain select * from forsamling where klar = 1; NOTICE: QUERY PLAN: Seq Scan on forsamling (cost=0.00..23.50 rows=1 width=88) - explain select * from forsamling where kund_flag = 123; NOTICE: QUERY PLAN: Seq Scan on forsamling (cost=0.00..23.50 rows=1 width=88) - explain select * from forsamling where for_id = 123; NOTICE: QUERY PLAN: Index Scan using forsamling_idx on forsamling (cost=0.00..2.01 rows=1 width=88) -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] vacuumdb -a -z hangs
I have a cronjob that does a vacuumdb -a -z every night. When i came to work this morning i saw a lot of postgres processes hanging on wait. The last thing i see before it hangs is this: - NOTICE: --Relation pg_toast_1216-- NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. NOTICE: Index pg_toast_1216_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec. NOTICE: Analyzing... - is this a known problem? Postgres is version 7.1.1. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Vacuum errors
One day i found these in my logs, and the vacuum process hung, effectively locking everybody out of some tables... Version 7.1.2 VACUUM ANALYZE NOTICE: RegisterSharedInvalid: SI buffer overflow NOTICE: InvalidateSharedInvalid: cache state reset It was sleeping in semop(). Any ideas, or fixes is welcome... cheers Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Fw: [PATCHES] Re: [HACKERS] Re: WIN32 errno patch
As usual i didn't cc the list :) Magnus - Original Message - From: "Magnus Naeslund(f)" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Sent: Friday, August 17, 2001 6:55 PM Subject: Re: [PATCHES] Re: [HACKERS] Re: WIN32 errno patch > From: "Tom Lane" <[EMAIL PROTECTED]> > [snip] > > FWIW, Magnus says this works: > > > > #define SOCK_STRERROR my_sock_strerror > > > [snip] > > > > > > Anyone have any objections to it? > > > > regards, tom lane > > > > I can make that patch if you'd like, but i need to know what i should be > working on (right from CVS?). > In what context is it? (the libpq lib maybe). > And how to test it :) > > I can do the legwork. > > Magnus > > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > Programmer/Networker [|] Magnus Naeslund > PGP Key: http://www.genline.nu/mag_pgp.txt > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] [PATCH] Win32 errno a little bit safer
Hello, i just reviewed the win32 errno patch and i saw that maybe i didn't
really played it totally safe in my last suggestion, the system table might
pick up the msg but not the netmsg.dll, so better try both.
I also added a hex printout of the "errno" appended to all messages, that's
nicer.
If anyone hate my coding style, or that i'm using goto constructs, just tell
me, and i'll rework it into a nested if () thing.
Patch attached.
Magnus
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Programmer/Networker [|] Magnus Naeslund
PGP Key: http://www.genline.nu/mag_pgp.txt
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Index: fe-misc.c
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/libpq/fe-misc.c,v
retrieving revision 1.54
diff -u -r1.54 fe-misc.c
--- fe-misc.c 2001/08/21 20:39:54 1.54
+++ fe-misc.c 2001/08/23 21:12:07
@@ -855,23 +855,46 @@
#ifdef WIN32
/*
* strerror replacement for windows:
+ *
+ * We dont't know a fix for win9x yet, but this whould work for nt4 and win2k.
+ * If you can verify this working on win9x or have a solution, let us know, ok?
+ *
*/
const char*
winsock_strerror(DWORD eno)
{
- if (!FormatMessage(
- FORMAT_MESSAGE_IGNORE_INSERTS |
-FORMAT_MESSAGE_FROM_SYSTEM | /* always consider system table */
-((netmsgModule != NULL) ? FORMAT_MESSAGE_FROM_HMODULE : 0),
-netmsgModule, /* module to get message from (NULL == system) */
- eno,
-MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
-winsock_strerror_buf,sizeof(winsock_strerror_buf)-1,
-NULL
- )){
- sprintf(winsock_strerror_buf,"Unknown socket error(%u)",eno);
-}
-winsock_strerror_buf[sizeof(winsock_strerror_buf)-1]='\0';
-return winsock_strerror_buf;
+ #define WSSE_MAXLEN (sizeof(winsock_strerror_buf)-1-12) /* 12 == "(0x)" */
+ int length;
+
+ /* First try the "system table", this works on Win2k pro */
+
+ if (FormatMessage(
+ FORMAT_MESSAGE_IGNORE_INSERTS|FORMAT_MESSAGE_FROM_SYSTEM,
+ 0,eno,MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
+ winsock_strerror_buf,WSSE_MAXLEN,NULL
+))
+goto WSSE_GOODEXIT;
+
+ /* That didn't work, let's try the netmsg.dll */
+
+ if (netmsgModule &&
+ FormatMessage(
+ FORMAT_MESSAGE_IGNORE_INSERTS|FORMAT_MESSAGE_FROM_HMODULE,
+ 0,eno,MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
+ winsock_strerror_buf,WSSE_MAXLEN,NULL
+ ))
+goto WSSE_GOODEXIT;
+
+ /* Everything failed, just tell the user that we don't know the desc */
+
+ strcat(winsock_strerror_buf,"Socket error, no description available.");
+
+WSSE_GOODEXIT:
+
+ length = strlen(winsock_strerror_buf);
+ sprintf(winsock_strerror_buf + length
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
From: "Ross J. Reedstrom" <[EMAIL PROTECTED]> > On Tue, Nov 28, 2000 at 05:19:45PM +0100, Zeugswetter Andreas SB wrote: > > > I guess it depends on what you're using it for -- disk space > > > is cheap and > > > abundant anymore, I can see some advantages of having it > > > computed only once > > > rather than X times, where X is the number of SELECTs as that > > > could get > > > costly on really high traffic servers.. Costly not so much for simple > > > computations like that but more complex ones. > > > > > > As I said in my original post, my understanding of computed fields may > be in error. If they're computed at SELECT time, to avoid creating table > space, then a VIEW is exacly the right solution. However, it's easy to > come up with examples of complex calculations that it would be useful > to cache the results of, in the table. Then, computing at INSERT/UPDATE > is clearly the way to go. > > So, having _both_ is the best thing. > > Ross > I'm new at this, but the view thing? Isn't that just the same as: create table test2 ( i1 int4, i2 int4); ...insert... select i1,i2,i1+i2 from test2; Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Re: [HACKERS] beta testing version
I don't have the same luck, sorry to say! I am running Mandrake linux with OpenWall patched 2.2.17 kernel, dual p3 550Mhz, 1gb memory. It's a really busy webserver that constantly is running with 10 in load. Sometime it spikes to ~40-50 in load (the most we had was 114(!)). I am running postgresql 7.0.2 (from the Mandrake rpm's). One problem i have is that in one database we rapidly insert/delete in some tables, and to maintain a good performance on that db, i have to run a vacuum every hour(!). I think that db has excessive indexes all over the place (if that could have anything to do with it?). Another other problem that is more severe is that the database "crashes" (read: stops working), if i run psql and do a select it says "001129.07:04:15.688 [25474] FATAL 1: Memory exhausted in AllocSetAlloc()" and fails. I have a cron script that watches postgres, and restarts it if it cant get a select right. It fails this way maybe once a day or two days. I've searched the mailinglist archives for this problem, but it allways seems that my problem doesn't fit the descriptions of the other ppl's problem generating this error message. I have not found the right time to upgrade to 7.0.3 yet, and i don't know if that would solve anything. Another problem i have is that i get "001128.12:58:01.248 [23444] FATAL 1: Socket command type unknown" in my logs. I don't know if i get that from the unix odbc driver, the remote windows odbc driver, or in unix standard db connections. I get "pq_recvbuf: unexpected EOF on client connection" alot too, but that i think only indicates that the socket was closed in a not-so-nice way, and that it is no "real" error. It seems that the psql windows odbc driver is generating this. The postmaster is running with these parameters: "-N 512 -B 1024 -i -o -S 4096" But as a happy note i can tell you that we have a Linux box here (pentium 100, kernel 2.0.3x) that has near 1000 days uptime, and runs postgres 6.5.x. It has never failed, not even a single time :) Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - Original Message - From: "Mitch Vincent" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 28, 2000 19:12 Subject: Re: [HACKERS] beta testing version > This is one of the not-so-stomped boxes running PostgreSQL -- I've never > restarted PostgreSQL on it since it was installed. > > 12:03pm up 122 days, 7:54, 1 user, load average: 0.08, 0.11, 0.09 > > I had some index corruption problems in 6.5.3 but since 7.0.X I haven't > heard so much as a peep from any PostgreSQL backend. It's superbly stable on > all my machines.. > > Damn good work guys. > > -Mitch > > - Original Message - > From: "The Hermit Hacker" <[EMAIL PROTECTED]> > To: "Hannu Krosing" <[EMAIL PROTECTED]> > Cc: "xuyifeng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > "Don Baccus" <[EMAIL PROTECTED]> > Sent: Tuesday, November 28, 2000 8:53 AM > Subject: Re: [HACKERS] beta testing version > > > > On Tue, 28 Nov 2000, Hannu Krosing wrote: > > > > > xuyifeng wrote: > > > > > > > > > > I just noticed this conversation so I have not followed all of it, > > > but you seem to have strange priorities > > > > > > > I just want PG can be improved quickly, for me crash recover is very > urgent problem, > > > > > > Crash avoidance is usually much more urgent, at least on production > > > servers. > > > > Good call, but I kinda jumped to the conclusion that since PgSQL itself > > isn't that crash prone, its his OS or his hardware that was the problem :0 > > > > > > > >
Re: [HACKERS] beta testing version
From: "Nathan Myers" <[EMAIL PROTECTED]> > On Thu, Nov 30, 2000 at 07:02:01PM -0400, The Hermit Hacker wrote: > > [snip] > The logging in 7.1 protects transactions against many sources of > database crash, but not necessarily against OS crash, and certainly > not against power failure. (You might get lucky, or you might just > think you were lucky.) This is the same as for most databases; an > embedded database that talks directly to the hardware might be able > to do better. > If PG had a type of tree based logging filesystem, that it self handles, wouldn't that be almost perfectly safe? I mean that you might lose some data in an transaction, but the client never gets an OK anyways... Like a combination of raw block io and tux2 like fs. Doesn't Oracle do it's own block io, no? Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Re: [HACKERS] PostgreSQL pre-7.1 Linux/Alpha Status...
From: "Ryan Kirkpatrick" <[EMAIL PROTECTED]> > On Sat, 16 Dec 2000, Bruce Momjian wrote: > > > Here is the list of features in 7.1. > > One thing that I think ought to be added is that with 7.1, > PostgreSQL will compile out of the box (i.e. without any extra patches) > for Linux/Alpha. What patches do one need for compiling say 7.0.3 with alpha linux? Is it some kind of semaphore patch (i get those evil errors). Magnus Naeslund -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
