[HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect, union
I just wanted to check if this has been fixed in any recent v8.1.x release, since I'm using v8.1.0 now. Backtrace: Program received signal SIGSEGV, Segmentation fault. 0x08152448 in qual_is_pushdown_safe () (gdb) bt #0 0x08152448 in qual_is_pushdown_safe () #1 0x08151e47 in set_subquery_pathlist () #2 0x08151a3c in set_base_rel_pathlists () #3 0x08151960 in make_one_rel () #4 0x0815dcaf in query_planner () #5 0x0815ea19 in grouping_planner () #6 0x0815e2e4 in subquery_planner () #7 0x0815dfaa in planner () #8 0x08197b7c in pg_plan_query () #9 0x08197c39 in pg_plan_queries () #10 0x08197e3d in exec_simple_query () #11 0x0819a6fe in PostgresMain () #12 0x08176356 in BackendRun () #13 0x08175c77 in BackendStartup () #14 0x08173ee2 in ServerLoop () #15 0x08173723 in PostmasterMain () #16 0x08139f90 in main () #17 0x400dc14f in __libc_start_main () from /lib/libc.so.6 The crashing query is below, if I remove the not is null test it doesn't crash. How to reproduce: create table snicker_whatever( id SERIAL primary key ); create table snicker ( idSERIAL primary key, name_singular text not null unique, name_plural text not null unique ); create table snicker_group ( id SERIAL primary key, title varchar(64) not null, snicker_id integer not null references snicker_whatever(id) ); create table snicker_group_mapping ( id SERIAL primary key, snicker_group_id integer not null references snicker_group(id), snicker_id integer references snicker(id) ); SELECT DISTINCT * FROM ( SELECT vtgm.snicker_id FROM snicker_group_mapping vtgm WHERE exists ( SELECT * FROM snicker_group vtg WHERE vtgm.snicker_group_id = vtg.id AND lower(vtg.title) ~* 'test' ) UNION SELECT snicker.id FROM snicker WHERE lower(snicker.name_singular) ~* 'test' OR lower(snicker.name_plural) ~* 'test' ) AS vt_id WHERE vt_id is not null; Regards, Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect,
Tom Lane wrote: Magnus Naeslund(f) [EMAIL PROTECTED] writes: SELECT DISTINCT * FROM ( SELECT vtgm.snicker_id FROM snicker_group_mapping vtgm WHERE exists ( SELECT * FROM snicker_group vtg WHERE vtgm.snicker_group_id = vtg.id AND lower(vtg.title) ~* 'test' ) UNION SELECT snicker.id FROM snicker WHERE lower(snicker.name_singular) ~* 'test' OR lower(snicker.name_plural) ~* 'test' ) AS vt_id WHERE vt_id is not null; While the crash is certainly a bug, the answer is going to be don't do that. Testing a whole record for null-ness is not meaningful. Yep, my workaround (or bugfix) was to push that null test infront of the exists. Also I might not need the surrounding distinct either, doesn't union make the result distinct? So if I would like to do the test after the union, I should add AS xxx on both union queries and then vt_id.xxx is not null, right ? Regards, Magnus ---(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] 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] 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] Is anybody out there !!!
No :) Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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])
[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 SeqNext) 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
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 SeqNext) 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
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/pid/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
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] 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
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]
[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] 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
[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
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])
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 options 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
[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 -- lotsa rows (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
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] 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] [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] [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]
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
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] 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
[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 + lengthWSSE_MAXLEN?length:WSSE_MAXLEN,
+ (0x%08X),eno);
+
+ return winsock_strerror_buf;
}
#endif
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
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] 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])
[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] 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
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] 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
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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
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] 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
