Re: [HACKERS] are there any cons to linking libstdc++ with
On Thu, Nov 18, 2004 at 04:17:31AM +0100, Palle Girgensohn wrote: > Does the same arguments apply for linking with libc_r (pthreads)? > > It is needed by plpython, at least on FreeBSD 4.10 (probably all versions). There are problem with libc_r on FreeBSD: due to initializing of red zone for initial thread's stack, postgresql stack gets limited to just 1M. There are patch in queue for increasing initial stack to 8/32/xxxM, but official reaction was: "create new thread with right stack size and use it for main loop" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] are there any cons to linking libstdc++ with postgresql?
Palle Girgensohn schrieb: I'm not a linking guru... Is there a penalty for setting LDFLAGS+= -lstdc++ when building postgresql? Postgis includes a bunch of useful functions for manipulating spatial data. Some of them are provided by geos, a separate c++ library, with postgis providing wrappers. According to postgis docs, postgresql _must_ be configured with LDFLAGS containing -lstdc++ for this to work. I can confirm this. The postgis port provides the WITH_GEOS tunable, but it has no effect unless the above adjustment is made to postgresql. The port makes no mention of this. Is there a penalty in just leaving LDFLAGS+= -lstdc++ in the postgresql port Makefile? Bad idea? What do you think? I'd rather use a libgeos wrapper using just extern "C" entry points, not the C++ mangled entries. Haven't checked yet how much trouble this may cause on geos, and if it will work with the exceptions. And if a simple .def with aliases would be enough. libgeos is huge. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] patch: plpgsql - access records with rec.(expr)
Hi,
I got extremely frustrated with having to create a temp table every time
I wanted to access an arbitrary column from a record plpgsql. After
seeing a note on the developers TODO about accessing plpgsql records
with a 'dot bracket' notation I started digging into the plpgsql source.
My diff (against 8beta4) is attached.
Warning: I Am Not a C Programmer! I haven't even written a hello world
in C before, and I knew nothing about Flex before yesterday. It was fun
figuring stuff out, I'm amazed it mostly works, but I'm really hoping
someone can point out my mistakes.
Goal:
Enable users to access fields in record variables using the following
syntax like the following:
rec.(1)
rec.('foo')
rec.(myvar::int)
rec.(myvar || '_id')
Files changed:
plpgsql.h
- added 'expr' member to PLpgSQL_recfield type for holding the
PLpgSQL_expr structure.
scan.l
- added match for {identifier}{space}*\. AFAIK this should only match
if a longer expression doesn't?
pl_comp.c
- added plpgsql_parse_wordexpr() function called by above match. Ripped
off code from plpgsql_parse_word that deals with arg_v[expr] to find our
expression. Prob a dumb name for the function!
pl_exec.c
- hacked exec_assign_value() and exec_eval_datum() to use the expression
to get the field name/number.
Stuff I need help with:
1. It should recognise OLD.(1) as a field number, not a column name. I
think I've got to check the returned type from exec_eval_expr() then
exec_simple_cast_value() on it, but that seems beyond me.
2. Freeing stuff. As I explained, this is all pretty new to me, and the
comments about it around exec_eval_expr() et al just confused me :(
Please give some hints about what needs freeing!
3. Would probably be good to add check in pl_comp.c to see if the
expression actually needs to be evaluated at runtime (ie isn't just a
field name/number). How?
4. Make this also work for row.(expr), label.record.(expr) and
label.row.(expr) - but want to get the basics working first!
5. Because of the way the expression is parsed (looking for closing
parenth), this will choke if you try and put a function in there. Would
it be better to use curly braces '{expr}' or another character to mark
the expression?
I hope at this eventually leads to some really useful extra
functionality, particularly for writing generic triggers. And it's a
tribute to the existing code that a complete newbie can cut-and-paste
their way to a halfarsed solution in a (rather long) night!
Regards,
Matt
diff -u src/pl_comp.c src.mk/pl_comp.c
--- src/pl_comp.c 2004-09-13 21:09:20.0 +0100
+++ src.mk/pl_comp.c 2004-11-18 12:59:25.825372489 +
@@ -3,7 +3,7 @@
* procedural language
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.82 2004/09/13 20:09:20 tgl Exp $
+ * $PostgreSQL: pgsql-server/src/pl/plpgsql/src/pl_comp.c,v 1.82 2004/09/13 20:09:20 tgl Exp $
*
* This software is copyrighted by Jan Wieck - Hamburg.
*
@@ -783,6 +783,75 @@
return T_WORD;
}
+/* --
+ * plpgsql_parse_wordexpr Same lookup for word followed by dot.
+ * Should only get here if it wasn't followed by
+ * an identifier.
+ * --
+ */
+int
+plpgsql_parse_wordexpr(char *word)
+{
+ PLpgSQL_nsitem *ns;
+ char *cp[1];
+int save_spacescanned = plpgsql_SpaceScanned;
+
+ /* Do case conversion and word separation */
+ /* add fake bit after dot to keep converter happy */
+word[strlen(word) - 1] = '\0';
+plpgsql_convert_ident(word, cp, 1);
+
+/* Make sure we've got an open parenthesis */
+
+/*
+ * Do a lookup on the compilers namestack
+ */
+ ns = plpgsql_ns_lookup(cp[0], NULL);
+ if (ns == NULL)
+ {
+ pfree(cp[0]);
+ return T_ERROR;
+ }
+switch (ns->itemtype)
+{
+ case PLPGSQL_NSTYPE_REC:
+ {
+/*
+ * First word is a record name, so expression refers to
+ * field in this record.
+ */
+PLpgSQL_recfield *new;
+
+new = malloc(sizeof(PLpgSQL_recfield));
+memset(new, 0, sizeof(PLpgSQL_recfield));
+
+if (plpgsql_yylex() != '(')
+plpgsql_yyerror("expected identifier or \"(\"");
+new->expr = plpgsql_read_expression(')', ")");
+new->recparentno = ns->itemno;
+/* just to be sure - we'll test on this later */
+new->fieldname = '\0';
+new->dtype = PLPGSQL_DTYPE_RECFIELD;
+
+plpgsql_adddatum((PLpgSQL_datum *) new);
+
+plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+
+plpgsql_SpaceScanned = save_spacescanned;
+
+pfree(cp[0]);
+return T_SCALAR;
+ }
+
+/* TODO: deal with rows, too */
+
+default:
+ break;
+
+}
+ pfree(cp[0]);
+ return T_ERROR;
+}
/* --
* plpgsql_parse_dblword Same lookup for two words
diff -u src/pl_exec.c src.mk/pl_exec.c
--- src/pl_exec.c 2004-09-16
[HACKERS] plpgsql on 8.0b4 bug?
It seems that 8.0B4's plpgsql does not diagnose 'ELSIF' being misspelled as 'ELSEIF' nearly as readily as did 7.4.x. 7.4 emits a compile error at the right place, whereas 8.0b4 seems to treat misspelled 'ELSEIF' blocks as unreachable code, making it quite hard to diagnose what is actually amiss. If not plpgsql coding often, the difference in spelling can be rather hard to see even when staring at the docs. Attached is a sample script used to massage data in a table from a bad representation to a little better one (with misspellings in place). Here's a minimal table definition to run it: create table unit ( id int8 not null primary key, pets boolean not null, petscondition text ); insert into unit values (1, true, 'Outside Only'); 8.0B4 results: [dynamic-94:~/cvs/social/misc-db-utils] jlrobins% psql < pets.sql BEGIN ALTER TABLE CREATE FUNCTION ERROR: No code determined for unit 1, t, "Outside Only" ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK 7.4.6 results: xs2 /tmp> psql ssc_sav < pets.sql BEGIN ALTER TABLE CREATE FUNCTION ERROR: syntax error at or near "ELSEIF" at character 1 CONTEXT: PL/pgSQL function "fix_pets" line 16 at SQL statement ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block COMMIT Many thanks in advance, James pets.sql Description: Binary data James Robinson Socialserve.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] invalid page header
Guys, Sorry for writing here. The point is that google does not helps very much with this error message, and the lists also, cause they throw different posible diagnosis for the same problem. And I think that the creators of the "beast" will further know what it's going on, or at least give me an approach/howto. PostgreSQL 7.4.2 Intel Xeon 2.8 * 8 Kernel 2.4.24-ck1 #5 SCSI disk. 4 gb RAM. The message: ERROR: invalid page header in block 90259 of relation "dat_cc_fail_auths" When? With almost any operation involving the relation "dat_cc_fail_auths" This relation was created yesterday, droped cause I've found this error, and recreated again (also, a message pointing to a log file not found, or alike [050F, wall?], was printed yesterday), but the message still remains the same. I am worry about a hardware problem. Other synthomas. 1) 15 days ago, a vmtstat command segfaulted several times. 2) other relations, in other db's, began throwing messages, like the above, solved by a reindex force or recreate of the table. (not a good bussinnes, prd box) 3) top command died also, dunno why yet. But...I haven't receive any other alerts or messages in log files (system logs reviewed) pointing me to problems. Above errors could not be so, and be just a random error going arround, coincidence and nothing else. Dunno. So, I am not sure about this, I meant, I don't have a real pointer to a real problem. The message printed by postgres, invalid page..., seems to be ambigous when speaking about the root of the problem (an abnormal shutdown could lead into an error like this, a hardware problem could lead into this, and further circunstances, yah? well..wich ones?) My point, I would like to design a plan in order to find the real problem and minimize the eventual downtime. As told earlier, this is a prd box. The following snapshot is a top, while a reindex is running. 2:23pm up 16 days, 17:19, 5 users, load average: 2.18, 2.36, 2.00 96 processes: 90 sleeping, 2 running, 0 zombie, 4 stopped CPU0 states: 0.0% user, 2.0% system, 0.0% nice, 97.0% idle CPU1 states: 0.0% user, 0.1% system, 0.0% nice, 99.0% idle CPU2 states: 79.0% user, 2.0% system, 0.0% nice, 17.0% idle CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU4 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU5 states: 0.1% user, 0.1% system, 0.0% nice, 98.0% idle CPU6 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU7 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle Mem: 3624156K av, 3526480K used, 97676K free, 0K shrd, 568K buff Swap: 4192912K av, 89404K used, 4103508K free 3264864K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 6979 postgres 25 0 93364 91M 83464 R81.8 2.5 26:18 postmaster 11164 postgres 16 0 1064 1064 840 R 2.9 0.0 0:00 top 19 root 15 0 00 0 SW0.9 0.0 58:33 kswapd 7126 postgres 18 0 84604 82M 83420 D 0.9 2.3 1:58 postmaster an explain of a simple query (couldn't vacuum this table, due to this problem on the page header). mis_logdata=# select count(*) from dat_cc_fail_auths; ERROR: invalid page header in block 90259 of relation "dat_cc_fail_auths" mis_logdata=# explain select count(*) from dat_cc_fail_auths; QUERY PLAN -- Aggregate (cost=10022.50..10022.50 rows=1 width=0) -> Seq Scan on dat_cc_fail_auths (cost=1.00..10020.00 rows=1000 width=0) (2 rows) mis_logdata=# I've found a tool, pgfsck, but could not use it, the author forgot to upgrade the script to make it compatible with the actual postgres versions. Other usefull tools? Best wishes, and thanks in advance. Guido. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] patch: plpgsql - access records with rec.(expr)
> 5. Because of the way the expression is parsed (looking for closing
> parenth), this will choke if you try and put a function in there. Would
> it be better to use curly braces '{expr}' or another character to mark
> the expression?
I lie! pgpgsql_read_expression() is smarter than that!
However, I do have another problem. If the value of the expr changes
inside a loop to a fieldname of a different type, it dies with the "type
of \"%s\" does not match that when preparing the plan" message, which is
quite true: it obviously doesn't.
Just setting expectedtypeoid to InvalidOid bombs the whole thing :(
Hrm the "best made plans" and all that...
Matt
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] invalid page header
G u i d o B a r o s i o <[EMAIL PROTECTED]> writes: >I am worry about a hardware problem. I think you're right. badblocks (for disk) and memtest86 (for RAM) are the tools I've seen recommended most often for narrowing down what's gone flaky. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] plpgsql on 8.0b4 bug?
James Robinson <[EMAIL PROTECTED]> writes: > 7.4.6 results: > xs2 /tmp> psql ssc_sav < pets.sql > BEGIN > ALTER TABLE > CREATE FUNCTION > ERROR: syntax error at or near "ELSEIF" at character 1 > CONTEXT: PL/pgSQL function "fix_pets" line 16 at SQL statement Um ... I get the "no code determined" message in 7.4.6 too, and indeed in every version of plpgsql (ok, I didn't try before 7.2). Given the way plpgsql works, it'd be hard to make it do anything else. It thinks that ELSEIF (or any other unrecognized keyword) is a SQL command keyword. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql on 8.0b4 bug?
Hmm. Teach me to not send in the exact script / dataset that caused the issue. Indeed, I must have (luckily) had a separate syntax error in the version that caused the 7.4.6 parser to trip up on the ELSEIF line (it did happen else I'd still be staring at the thing). Humble apologies and thanks. James Robinson Socialserve.com ---(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] Timing of pgstats updates
"David Parker" <[EMAIL PROTECTED]> writes: > What I think is happening with the missing pg_statistic entries: > The install of our application involves a lot of data importing (via > JDBC) in one large transaction, which can take up to 30 minutes. (I > realize I left out this key piece of info in my original post...) > The pg_autovacuum logic is relying on data from pg_stat_all_tables to > make the decision about running analyze. As far as I can tell, the data > in this view gets updated outside of the transaction, because I saw the > numbers growing while I was importing. I saw pg_autovacuum log messages > for running analyze on several tables, but no statistics data showed up > for these, I assume because the actual data in the table wasn't yet > visible to pg_autovacuum because the import transaction had not finished > yet. > When the import finished, not all of the tables affected by the import > were re-visited because they had not bumped up over the threshold again, > even though the analyze run for those tables had not generated any stats > because of the still-open transaction. Bingo. The per-table activity stats are sent to the collector whenever the backend waits for a client command. Given a moderately long transaction block doing updates, it's not hard at all to imagine that autovacuum would kick off vacuum and/or analyze while the updating transaction is still in progress. The resulting operation is of course a waste of time. It'd be trivial to adjust postgres.c so that per-table stats are only transmitted when we exit the transaction (basically move the pgstat_report_tabstat call down a couple lines so it's not called if IsTransactionOrTransactionBlock). This seems like a good change to me. Does anyone not like it? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4.5 / 7.4.6 crash (pg_hba.conf issue)
Tom Lane wrote: > Devrim GUNDUZ <[EMAIL PROTECTED]> writes: > >>>Off-by-one memory allocation problem --- it only bites you if the string >>>lengths are just right, which probably explains the lack of prior >>>reports even though the bug has been there since 7.3. > > >>Is this worth new dot releases? > > > I'd say not. At my knowledge Postgres can read that file even if it's writable by anyone ( I can not test right now or look at the code), if this is the case then this is a sort of serious bug :-( Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plpgsql on 8.0b4 bug?
James Robinson <[EMAIL PROTECTED]> writes: > Hmm. Teach me to not send in the exact script / dataset that caused the > issue. Indeed, I must have (luckily) had a separate syntax error in the > version that caused the 7.4.6 parser to trip up on the ELSEIF line (it > did happen else I'd still be staring at the thing). It occurs to me that one simple thing we could do is make plpgsql accept both ELSIF and ELSEIF as legal spellings of the keyword. This seems a bit ugly but I can't think of any really good objections. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] plpgsql on 8.0b4 bug?
Between ugly #1 and ugly #2, I'd think that a wart teaching it that 'ELSEIF' is not a valid manner to start a statement (i.e. following a semicolon) would be preferable. Allowing us hacks to write functions containing both spellings makes the language look poor since it ought to slap us into formal shape. This isn't [insert a lossy SQL implementation or slop-inspiring scripting language here]. We're typesafe and syntax checking! On Nov 18, 2004, at 12:09 PM, Tom Lane wrote: It occurs to me that one simple thing we could do is make plpgsql accept both ELSIF and ELSEIF as legal spellings of the keyword. This seems a bit ugly but I can't think of any really good objections. James Robinson Socialserve.com ---(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] invalid page header
On Thu, Nov 18, 2004 at 10:45:53 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > G u i d o B a r o s i o <[EMAIL PROTECTED]> writes: > >I am worry about a hardware problem. > > I think you're right. badblocks (for disk) and memtest86 (for RAM) > are the tools I've seen recommended most often for narrowing down > what's gone flaky. smartmontools can also be useful for checking disks. Doing regular selftests will be less resource intensive then doing read checks with badblocks. ---(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] OpenBSD/Sparc status
The fix for unflushed changed to pg_database records seems to have fixed the problem we were seeing on spoonbill ... but it is now seeing problems with the seg module: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=spoonbill&dt=2004-11-18%2016:02:58 cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-hackers] Timing of pgstats updates
Tom, > This seems like a good change to me. ÂDoes anyone not like it? +1 -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Trouble with plpgsql on 7.4.6
Has anyone else had any problem installing plpgsql into a 7.4.6 database? When I try (on NetBSD 2.0RC5) I get the following message: createlang: language installation failed: server closed the connection unexpectedly This does not happen on 7.4.3, the other system that I have installed. It doesn't happen if I use 7.4.6 to access a database built by 7.4.3. It happens on 7.4.6 if I initdb a new database and simply run the createlang command against it. Here is a test script which I ran in a scratch directory. initdb . pg_ctl -D . start sleep 3 createlang plpgsql template1 The closest thing I could find in the archives is the message about 7.4.5 in http://archives.postgresql.org/pgsql-hackers/2004-09/msg00915.php. I don't think there is a connection but I could be wrong. I turned on a bunch of logging and this is what gets spit out into the log: Nov 18 11:49:18 panther cert198[21983]: [47-1] LOG: 0: server process (PID 7864) was terminated by signal 6 Nov 18 11:49:18 panther cert198[21983]: [47-2] LOCATION: LogChildExit, postmaster.c:2087 Nov 18 11:49:18 panther cert198[21983]: [48-1] LOG: 0: terminating any other active server processes Nov 18 11:49:18 panther cert198[21983]: [48-2] LOCATION: CleanupProc, postmaster.c:2008 Nov 18 11:49:18 panther cert198[21983]: [49-1] LOG: 0: all server processes terminated; reinitializing Nov 18 11:49:18 panther cert198[21983]: [49-2] LOCATION: reaper, postmaster.c:1920 Nov 18 11:49:18 panther cert198[1332]: [52-1] LOG: 0: database system was interrupted at 2004-11-18 11:43:53 EST Nov 18 11:49:18 panther cert198[1332]: [52-2] LOCATION: StartupXLOG, xlog.c:2610 Nov 18 11:49:18 panther cert198[1332]: [53-1] LOG: 0: checkpoint record is at 0/9F5B40 Nov 18 11:49:18 panther cert198[1332]: [53-2] LOCATION: StartupXLOG, xlog.c:2628 Nov 18 11:49:18 panther cert198[1332]: [54-1] LOG: 0: redo record is at 0/9F5B40; undo record is at 0/0; shutdown TRUE Nov 18 11:49:18 panther cert198[1332]: [54-2] LOCATION: StartupXLOG, xlog.c:2653 Nov 18 11:49:18 panther cert198[1332]: [55-1] LOG: 0: next transaction ID: 574; next OID: 74486 Nov 18 11:49:18 panther cert198[1332]: [55-2] LOCATION: StartupXLOG, xlog.c:2656 Nov 18 11:49:18 panther cert198[1332]: [56-1] LOG: 0: database system was not properly shut down; automatic recovery in progress Nov 18 11:49:18 panther cert198[1332]: [56-2] LOCATION: StartupXLOG, xlog.c:2705 Nov 18 11:49:19 panther cert198[1332]: [57-1] LOG: 0: redo starts at 0/9F5B80 Nov 18 11:49:19 panther cert198[1332]: [57-2] LOCATION: StartupXLOG, xlog.c:2733 Nov 18 11:49:19 panther cert198[1332]: [58-1] LOG: 0: invalid magic number in log file 0, segment 0, offset 10477568 Nov 18 11:49:19 panther cert198[1332]: [58-2] LOCATION: ValidXLOGHeader, xlog.c:2048 Nov 18 11:49:19 panther cert198[1332]: [59-1] LOG: 0: redo done at 0/9FBCAC Nov 18 11:49:19 panther cert198[1332]: [59-2] LOCATION: StartupXLOG, xlog.c:2765 Nov 18 11:49:21 panther cert198[1332]: [60-1] LOG: 0: database system is ready Nov 18 11:49:21 panther cert198[1332]: [60-2] LOCATION: StartupXLOG, xlog.c:2946 -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Trouble with plpgsql on 7.4.6
"D'Arcy J.M. Cain" <[EMAIL PROTECTED]> writes: > Has anyone else had any problem installing plpgsql into a 7.4.6 > database? When I try (on NetBSD 2.0RC5) I get the following message: > createlang: language installation failed: server closed the connection > unexpectedly Probably indicates a failure to load the plpgsql shared library. > I turned on a bunch of logging and this is what gets spit out into the > log: > Nov 18 11:49:18 panther cert198[21983]: [47-1] LOG: 0: server > process (PID 7864) was terminated by signal 6 There wasn't anything interesting just before that? Signal 6 is SIGABRT (at least on my machine) so something decided to abort() in that backend. Postgres would abort() on assertion failure, but it would have logged a message first. My guess is that the shared library loader did the abort(), and probably it griped on stderr rather than to syslog. Fix your postmaster startup arrangement so that stderr goes someplace useful instead of to /dev/null, so you can see what it's complaining about. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timing of pgstats updates
On 11/18/2004 11:43 AM, Tom Lane wrote: "David Parker" <[EMAIL PROTECTED]> writes: What I think is happening with the missing pg_statistic entries: The install of our application involves a lot of data importing (via JDBC) in one large transaction, which can take up to 30 minutes. (I realize I left out this key piece of info in my original post...) The pg_autovacuum logic is relying on data from pg_stat_all_tables to make the decision about running analyze. As far as I can tell, the data in this view gets updated outside of the transaction, because I saw the numbers growing while I was importing. I saw pg_autovacuum log messages for running analyze on several tables, but no statistics data showed up for these, I assume because the actual data in the table wasn't yet visible to pg_autovacuum because the import transaction had not finished yet. When the import finished, not all of the tables affected by the import were re-visited because they had not bumped up over the threshold again, even though the analyze run for those tables had not generated any stats because of the still-open transaction. Bingo. The per-table activity stats are sent to the collector whenever the backend waits for a client command. Given a moderately long transaction block doing updates, it's not hard at all to imagine that autovacuum would kick off vacuum and/or analyze while the updating transaction is still in progress. The resulting operation is of course a waste of time. It'd be trivial to adjust postgres.c so that per-table stats are only transmitted when we exit the transaction (basically move the pgstat_report_tabstat call down a couple lines so it's not called if IsTransactionOrTransactionBlock). This seems like a good change to me. Does anyone not like it? regards, tom lane Sounds reasonable here. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Trouble with plpgsql on 7.4.6
On Thu, 18 Nov 2004 14:25:41 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > "D'Arcy J.M. Cain" <[EMAIL PROTECTED]> writes: > > Has anyone else had any problem installing plpgsql into a 7.4.6 > > database? When I try (on NetBSD 2.0RC5) I get the following > > message: > > > createlang: language installation failed: server closed the > > connection unexpectedly > > Probably indicates a failure to load the plpgsql shared library. I considered that but the file is where I expect it to be. I did this: [EMAIL PROTECTED]:/data/cert198] $ pg_config --libdir /usr/pkg/lib The file is actually in /usr/pkg/lib/postgresql/plpgsql.so. I wasn't sure if the postgresql was added so I made a symlink to assure that it was available in both. locations. > > I turned on a bunch of logging and this is what gets spit out into > > the log: > > > Nov 18 11:49:18 panther cert198[21983]: [47-1] LOG: 0: server > > process (PID 7864) was terminated by signal 6 > > There wasn't anything interesting just before that? Not that I can see. I have attached the entire output of the capture from the start. Here are the diffs from the postgresql.conf created by initdb. --- postgresql.conf.orig2004-11-18 15:33:03.0 -0500 +++ postgresql.conf 2004-11-18 15:33:17.0 -0500 @@ -136,24 +136,24 @@ # - Syslog - -#syslog = 0# range 0-2; 0=stdout; 1=both; 2=syslog -#syslog_facility = 'LOCAL0' -#syslog_ident = 'postgres' +syslog = 1 # range 0-2; 0=stdout; 1=both; 2=syslog +syslog_facility = 'LOCAL4' +syslog_ident = 'postgres' # - When to Log - -#client_min_messages = notice # Values, in order of decreasing detail: +client_min_messages = debug5 # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error -#log_min_messages = notice # Values, in order of decreasing detail: +log_min_messages = debug5 # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic -#log_error_verbosity = default # terse, default, or verbose messages +log_error_verbosity = verbose # terse, default, or verbose messages -#log_min_error_statement = panic # Values in order of increasing severity: +log_min_error_statement = debug5 # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) @@ -162,7 +162,7 @@ # milliseconds. Zero prints all queries. # Minus-one disables. -#silent_mode = false# DO NOT USE without Syslog! +silent_mode = false # DO NOT USE without Syslog! # - What to Log - @@ -170,11 +170,11 @@ #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false -#log_connections = false -#log_duration = false -#log_pid = false -#log_statement = false -#log_timestamp = false +log_connections = true +log_duration = true +log_pid = true +log_statement = true +log_timestamp = true #log_hostname = false #log_source_port = false > Signal 6 is SIGABRT (at least on my machine) so something decided to Same on mine. Thanks for the help. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. OUT Description: Binary data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] invalid page header
Thanks all for the quick reply. Cheers, Regards, and muchas gracias!! Guido. > On Thu, Nov 18, 2004 at 10:45:53 -0500, > Tom Lane <[EMAIL PROTECTED]> wrote: > > G u i d o B a r o s i o <[EMAIL PROTECTED]> writes: > > >I am worry about a hardware problem. > > > > I think you're right. badblocks (for disk) and memtest86 (for RAM) > > are the tools I've seen recommended most often for narrowing down > > what's gone flaky. > > smartmontools can also be useful for checking disks. Doing regular > selftests will be less resource intensive then doing read checks > with badblocks. > > ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Test database for new installs?
Folks, Some issues have come up repeatedly on IRC with new users, enough so that they might be worth addressing in the code: a) new users try just to "psql" as postgres, and get a "no such database postgres"; b) new users use template1 as a testing database, and then have to re-initdb to clean it up. Both of these things could be solved by creating an additional, non-template database called "postgres" at initdb.For security reasons, this db would be set up in pg_hba.conf as accessable only by postgres via local. It might not seem like it to experienced programmers, but having a "sandbox" database which lets you get used to PG commands would be a boon to people how are new to both Postgres and SQL databases in general. The only reason not to do it is space; each database takes up about 5mb. That's nothing to most users but could be a problem for a few. Also, it would create a minor burden on the fsm to track an extra set of relations. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Test database for new installs?
> The only reason not to do it is space; each database takes up about 5mb. > That's nothing to most users but could be a problem for a few. Also, it > would create a minor burden on the fsm to track an extra set of relations. Perhaps it could have an initdb flag to turn it off and be easily dropped via drop database? Then it's not such a big deal. As a side note, the database shouldn't be "postgres" but representative of the username they're installing with. pgsql is another popular username. -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Test database for new installs?
Josh Berkus <[EMAIL PROTECTED]> writes: > Some issues have come up repeatedly on IRC with new users, enough so that > they > might be worth addressing in the code: > a) new users try just to "psql" as postgres, and get a "no such database > postgres"; > b) new users use template1 as a testing database, and then have to re-initdb > to clean it up. I think this is a documentation thing as much as anything else. We could just suggest that the first move after starting the postmaster be createdb (they don't even need to give it an argument ... how painless can you get?) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Tom, > I think you are right that these reflect heap or btree-index extension > operations. Those do not actually take locks on the *table* however, > but locks on a single page within it (which are completely orthogonal to > table locks and don't conflict). The pg_locks output leaves something > to be desired, because you can't tell the difference between table and > page locks. Aside from foriegn keys, though, is there any way in which INSERT page locks could block other inserts?I have another system (Lyris) where that appears to be happening with 32 concurrent INSERT streams.It's possible that the problem is somewhere else, but I'm disturbed by the possibility. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OpenBSD/Sparc status
Andrew Dunstan <[EMAIL PROTECTED]> writes: > The fix for unflushed changed to pg_database records seems to have fixed > the problem we were seeing on spoonbill ... but it is now seeing > problems with the seg module: > http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=spoonbill&dt=2004-11-18%2016:02:58 Don't tell me that just started happening? We haven't touched seg in weeks... I'm unsure how this could fail when float4 passes, because it's using float4in to convert the strings. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Josh Berkus <[EMAIL PROTECTED]> writes: > Aside from foriegn keys, though, is there any way in which INSERT page locks > could block other inserts? Not for longer than the time needed to physically add a tuple to a page. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] OpenBSD/Sparc status
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: The fix for unflushed changed to pg_database records seems to have fixed the problem we were seeing on spoonbill ... but it is now seeing problems with the seg module: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=spoonbill&dt=2004-11-18%2016:02:58 Don't tell me that just started happening? We haven't touched seg in weeks... I'm unsure how this could fail when float4 passes, because it's using float4in to convert the strings. We're only seeing it now because up to now the run on this platform was bombing out on the error you so brilliantly fixed last night. You might recall I wanted to patch contrib/Makefile to force installcheck on all modules regardless of error - if we had that we'd have seen this before. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Test database for new installs?
On Thu, 18 Nov 2004, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > Some issues have come up repeatedly on IRC with new users, enough so that > > they > > might be worth addressing in the code: > > a) new users try just to "psql" as postgres, and get a "no such database > > postgres"; > > b) new users use template1 as a testing database, and then have to re-initdb > > to clean it up. > > I think this is a documentation thing as much as anything else. We > could just suggest that the first move after starting the postmaster be > createdb > (they don't even need to give it an argument ... how painless can you > get?) Perhaps initdb could spit out a line saying 'to create a test database for the current user, issue /path/to/createdb'. *thinks* I don't think it solves the problem Josh and others are seeing on IRC though. From my experience, at least with reference to (a) above, the user's OS comes with PostgreSQL installed or allows it to be installed in some semi-automated way. Generally, the installation process runs initdb in the background (which is a broken idea in my opinion). An run level init script brings up the server and the user wants to connect. It seems as though, if the distribution wants to make it this simple for a user to get at an SQL console, then they should also create default databases for users. My personal opinion is, however, that the administrator of the machine should be forced to initdb which will force he or her to read at least some of the manual. Gavin ---(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] OpenBSD/Sparc status
Andrew Dunstan <[EMAIL PROTECTED]> writes: > We're only seeing it now because up to now the run on this platform was > bombing out on the error you so brilliantly fixed last night. Consistently? I'd have thought that problem would only fail once in a while. It's hard to believe the timing would work out to make it a 100% failure. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Simon Riggs <[EMAIL PROTECTED]> writes: > The main problem on INSERTs is that it is usually the same few pages: > the lead data block and the lead index block. There are ways of > spreading the load out across an index, but I'm not sure what happens on > the leading edge of the data relation, but I think it hits the same > block each time. FSM does what it can to spread the insertion load across multiple pages, but of course this is not going to help much unless your table has lots of embedded free space. I think it would work pretty well on a table with lots of update turnover, but not on an INSERT-only workload. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
On Thu, 2004-11-18 at 22:12, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > Aside from foriegn keys, though, is there any way in which INSERT page > > locks > > could block other inserts? > > Not for longer than the time needed to physically add a tuple to a page. The main problem on INSERTs is that it is usually the same few pages: the lead data block and the lead index block. There are ways of spreading the load out across an index, but I'm not sure what happens on the leading edge of the data relation, but I think it hits the same block each time. Only an issue if you have more than one CPU... -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Simon, Tom, > The main problem on INSERTs is that it is usually the same few pages: > the lead data block and the lead index block. There are ways of > spreading the load out across an index, but I'm not sure what happens on > the leading edge of the data relation, but I think it hits the same > block each time. I actually have several test cases for this, can you give me a trace or profile suggestion that would show if this is happening? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Josh Berkus <[EMAIL PROTECTED]> writes: >> The main problem on INSERTs is that it is usually the same few pages: >> the lead data block and the lead index block. There are ways of >> spreading the load out across an index, but I'm not sure what happens on >> the leading edge of the data relation, but I think it hits the same >> block each time. > I actually have several test cases for this, can you give me a trace or > profile suggestion that would show if this is happening? If it is a problem, the LockBuffer calls in RelationGetBufferForTuple would be the places showing contention delays. It could also be that the contention is for the WALInsertLock, ie, the right to stuff a WAL record into the shared buffers. This effect would be the same even if you were inserting into N separate tables. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] OpenBSD/Sparc status
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: We're only seeing it now because up to now the run on this platform was bombing out on the error you so brilliantly fixed last night. Consistently? I'd have thought that problem would only fail once in a while. It's hard to believe the timing would work out to make it a 100% failure. You can see the history of the latest build runs here: http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=spoonbill&br=HEAD cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
On Thu, 2004-11-18 at 22:51, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > The main problem on INSERTs is that it is usually the same few pages: > > the lead data block and the lead index block. There are ways of > > spreading the load out across an index, but I'm not sure what happens on > > the leading edge of the data relation, but I think it hits the same > > block each time. > > FSM does what it can to spread the insertion load across multiple pages, > but of course this is not going to help much unless your table has lots > of embedded free space. I think it would work pretty well on a table > with lots of update turnover, but not on an INSERT-only workload. OK, thats what I thought. So with a table with an INSERT-only workload, the FSM is always empty, so there only ever is one block that gets locked. That means we can't ever go faster than 1 CPU can go - any other CPUs will just wait for the block lock. [In Josh's case, 32 INSERT streams won't go significantly faster than about 4 streams, allowing for some overlap of other operations] Would it be possible to: when a new block is allocated from the relation file (rather than reused), we check the FSM - if it is empty, then we allocate 8 new blocks and add them all to the FSM. The next few INSERTers will then use the FSM blocks normally. Doing that will definitely speed up DBT-2 and many other workloads. Many tables have SERIAL defined, or use a monotonically increasing unique key. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Simon Riggs <[EMAIL PROTECTED]> writes: > Would it be possible to: when a new block is allocated from the relation > file (rather than reused), we check the FSM - if it is empty, then we > allocate 8 new blocks and add them all to the FSM. The next few > INSERTers will then use the FSM blocks normally. Most likely that would just shift the contention to the WALInsertLock. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OpenBSD/Sparc status
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Consistently? I'd have thought that problem would only fail once in a >> while. It's hard to believe the timing would work out to make it a 100% >> failure. > You can see the history of the latest build runs here: > http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=spoonbill&br=HEAD Remarkable. There is one run (2004-11-15) where it got past the rtree test (and did indeed fail at seg) but the failure rate is certainly upwards of 90%. Curious. There must be some effect that is synchronizing the bgwriter's actions with the test sequence. Back at the ranch, I am even more surprised to note that the bogus seg output in the 11-15 run is different from what it is in today's. There's not much I can do about it without access to a machine where it's failing though. Can we get personal accounts on the buildfarm machines? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OpenBSD/Sparc status
Tom Lane wrote: Can we get personal accounts on the buildfarm machines? That's up to the owner of each machine - it's a distributed system. I've sent email to the owner of this one. When I get a few minutes soon I hope to start some discussion on -hackers about what members we want in the buildfarm and what our expectations are about help with solving problems. cheers andrew ---(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: [Testperf-general] Re: [HACKERS] ExclusiveLock
On Thu, 2004-11-18 at 23:19, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Would it be possible to: when a new block is allocated from the relation > > file (rather than reused), we check the FSM - if it is empty, then we > > allocate 8 new blocks and add them all to the FSM. The next few > > INSERTers will then use the FSM blocks normally. > > Most likely that would just shift the contention to the WALInsertLock. Well, removing any performance bottleneck shifts the bottleneck to another place, though that is not an argument against removing it. Can we subdivide the WALInsertLock so there are multiple entry points to wal_buffers, based upon hashing the xid? That would allow wal to be written sequentially by each transaction though slightly out of order for different transactions. Commit/Abort would all go through the same lock to guarantee serializability. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Simon Riggs <[EMAIL PROTECTED]> writes: > Can we subdivide the WALInsertLock so there are multiple entry points to > wal_buffers, based upon hashing the xid? I don't think so; WAL is inherently a linear log. (Awhile ago there was some talk of nonlinear log writing to get around the one-commit-per- disk-revolution syndrome, but the idea basically got rejected as unworkably complicated.) What's more, there are a lot of entries that must remain time-ordered independently of transaction ownership. Consider btree index page splits and sequence nextvals for two examples. Certainly I'd not buy into any such project without incontrovertible proof that it would solve a major bottleneck --- and right now we are only speculating with no evidence. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Can postgresql accept mutliple connections in the same instance?
I am running of postgresql database servers with generally 30-50 users at a time per server. I have noticed one thing for web based databases that they fail to initialse a pg_connection connection every now and again and return no error message at all. Though one of hte developers tells me that sometimes it gets a link resource allocation error on opening connections to the database server which i dont know if that is related. Max connections is set to 500, the webpage for the server code initalises and then closes the connection when its done. Using netstat on open postgresql connections seems to match with what apache is showing as current connections. This seems to happen sometimes when the backups are going, which are done every hour though not always. Users notice this a lot between the hours of 6am to 10am which 1 in 20 connections failing to be established. Does pg_dumpall or pg_dump lock the database block access to the database so you cant create connections? Could it be possible postgresql can only create one connection at a time and if it gets two requests for a connection at the same time it fails? Do you have any ideas how I can test the database in a way to find out what could be causing this problem? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Can postgresql accept mutliple connections in the
On Fri, 2004-11-19 at 16:17 +1300, Dru wrote: > Though one of hte developers > tells me that sometimes it gets a link resource allocation error on > opening connections to the database server which i dont know if that is > related. I'm not sure what you mean by a "link resource allocation error". Can you provide the exact error message you get when a connection is refused? (Try checking the PostgreSQL logfile.) > Max connections is set to 500, the webpage for the server code > initalises and then closes the connection when its done. It might be worth considering connection pooling or persistent connections, although that shouldn't be related to the specific problem you're having. > Does pg_dumpall or pg_dump lock the database block access > to the database so you cant create connections? No. > Could it be possible > postgresql can only create one connection at a time and if it gets two > requests for a connection at the same time it fails? No. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [Plperlng-devel] Re: [HACKERS] Concern about new PL/Perl
Andrew Dunstan wrote:
Peter Eisentraut wrote:
It seems that in the new PL/Perl, the result of the spi_exec_query
function changes in meaning depending on the command. For a SELECT,
the value of
$res->{rows}
is a reference to an array of the result rows.
For a different command
$res->{rows}
is a scalar containing the number of affected rows. I think this is
a poor design. Couldn't we have a different result field that always
contains the number of rows?
I don't recall seeing any reply to this, but I'm inclined to agree
with it.
Joshua, any comment from CP?
I would agree that seems a little odd ;). Would this be something we
want done for 8.0?
Sincerely,
Joshua D. Drake
cheers
andrew
___
Plperlng-devel mailing list
[EMAIL PROTECTED]
http://pgfoundry.org/mailman/listinfo/plperlng-devel
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [Plperlng-devel] Re: [HACKERS] Concern about new PL/Perl
Josh, > I would agree that seems a little odd ;). Would this be something we > want done for 8.0? I think we'd better. Otherwise, people will get used to the broken syntax. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Can postgresql accept mutliple connections in the same instance?
Dru <[EMAIL PROTECTED]> writes: > I am running of postgresql database servers with generally 30-50 users > at a time per server. I have noticed one thing for web based databases > that they fail to initialse a pg_connection connection every now and > again and return no error message at all. That's fairly hard to believe. I don't know of any failure paths that won't log some traceable result *somewhere* --- if nothing gets returned to the client, try looking in syslog or stderr output (you're not sending postmaster stderr to /dev/null I hope), or at worst look for a core dump file. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Plperlng-devel] Re: [HACKERS] Concern about new PL/Perl
Josh Berkus <[EMAIL PROTECTED]> writes: >> I would agree that seems a little odd ;). Would this be something we >> want done for 8.0? > I think we'd better. Otherwise, people will get used to the broken syntax. Agreed. Someone's going to step up and patch this, no? (Not me --- I've already wasted more hours than I could afford this week on plperl.) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Can postgresql accept mutliple connections in the same
Neil Conway wrote: On Fri, 2004-11-19 at 16:17 +1300, Dru wrote: Though one of hte developers tells me that sometimes it gets a link resource allocation error on opening connections to the database server which i dont know if that is related. I'm not sure what you mean by a "link resource allocation error". Can you provide the exact error message you get when a connection is refused? (Try checking the PostgreSQL logfile.) I'll get the developer to write down the exact error when it happens again. I think he only gets it once or twice a week when using the database server and grown accustomed to it. I myself when testing the database server for errors dont get any at all when connections fail when they shouldnt fail :( . Max connections is set to 500, the webpage for the server code initalises and then closes the connection when its done. It might be worth considering connection pooling or persistent connections, although that shouldn't be related to the specific problem you're having. I was using persistent connections initally and was getting this problem so switched to non-persistant and made sure i was cleaning them up afterwards in case it helped solved this problem. I've replaced the db server and webserver also to try and resolve it but that had no effect. I initally thought it might be something to do with a kernel limit on sockets or something to that effect. These connections are all TCP/IP based. Does pg_dumpall or pg_dump lock the database block access to the database so you cant create connections? No. Ok that rules out that possibility then. Could it be possible postgresql can only create one connection at a time and if it gets two requests for a connection at the same time it fails? No. Ok rules out that possibility also. Is there any stress testing software for postgresql to find out how and when it breaks? I wrote a simple script to simulate 300 concurrent users on the webfrontend which breaks the website real quick with 1 in 10 connections to db failing with no error returned by connect just a dead connection. The website uses php, the problem could be in the wrapper code for PHP though. I havnt got much luck asking php developers about the problem though. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Plperlng-devel] Re: [HACKERS] Concern about new PL/Perl
Agreed. Someone's going to step up and patch this, no? (Not me --- I've already wasted more hours than I could afford this week on plperl.) We can do it, but it will have to be after thanksgiving. J regards, tom lane -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Can postgresql accept mutliple connections in the same
Tom Lane wrote: Dru <[EMAIL PROTECTED]> writes: I am running of postgresql database servers with generally 30-50 users at a time per server. I have noticed one thing for web based databases that they fail to initialse a pg_connection connection every now and again and return no error message at all. That's fairly hard to believe. I don't know of any failure paths that won't log some traceable result *somewhere* --- if nothing gets returned to the client, try looking in syslog or stderr output (you're not sending postmaster stderr to /dev/null I hope), or at worst look for a core dump file. regards, tom lane I've spent ages going though logs and turning debugging to max. There is no error message returned at all. The connection handle is returned as NULL. This is in the php functions though so their pg_last_error() function may not be catching all error messages but it seems it should pass on every error. It is just really weird. The weirder thing i find is the fact it is doing this a lot more often while backups are running than when they arn't running. 80% of the time when it is reported by users a backup is running at the same time. ---(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] Can postgresql accept mutliple connections in
On Fri, 2004-11-19 at 17:15 +1300, Dru wrote: > Ok rules out that possibility also. Is there any stress testing > software for postgresql to find out how and when it breaks? Try contrib/pgbench. > The website uses php, > the problem could be in the wrapper code for PHP though. I havnt > got much luck asking php developers about the problem though. pgbench uses libpq (i.e. the native C client interface to PostgreSQL) -- if you encounter connection failures using it, that will narrow down the set of possible culprits. Since you don't get an error message in the PostgreSQL logs when a connection is refused, it seems that the connection attempt doesn't even make it as far as the postmaster, so I would be skeptical of the software between the client and the backend (e.g. PHP, perhaps some kernel/TCP weirdness, etc.). -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can postgresql accept mutliple connections in the same
I've spent ages going though logs and turning debugging to max. There is no error message returned at all. The connection handle is returned as NULL. This is in the php functions though so their pg_last_error() function may not be catching all error messages but it seems it should pass on every error. It is just really weird. The weirder thing i find is the fact it is doing this a lot more often while backups are running than when they arn't running. 80% of the time when it is reported by users a backup is running at the same time. Could it be that your load is getting driven to high by lack of IO bandwidth during a backup and your PHP connection is timing out? J ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(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] Can postgresql accept mutliple connections in the same
On Fri, Nov 19, 2004 at 05:15:54PM +1300, Dru wrote: > I'll get the developer to write down the exact error when it happens again. It would be better to cut and paste the error message instead of writing it down. What people think they see doesn't always match what's on the screen. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [Plperlng-devel] Re: [HACKERS] Concern about new PL/Perl
On Thu, 18 Nov 2004, Joshua D. Drake wrote: Agreed. Someone's going to step up and patch this, no? (Not me --- I've already wasted more hours than I could afford this week on plperl.) We can do it, but it will have to be after thanksgiving. Humor the Canadian ... when is Thanksgiving? :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_resetxlog options
>> Some other time maybe. Meanwhile, this patch ought to make it compile >> more cleanly on Windows - not sure why I get errors there but not >> Linux. > > Because getopt() is normally declared in unistd.h, not getopt.h (Windows > being an exception?). getopt is not in any standard Windows headers. The getopt.h header is from mingw to assist porting (don't know why they didn't put it in unistd.h ?). Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
