Re: [HACKERS] strange update problem with 7.2.1
Tested it with current CVS. It works. Oleg Bartunov wrote: > Just tested with 7.2.1. It works. We have one more patch (for rtree_gist) > to submit before 7.2.2 release. > > Oleg > > On Sun, 26 May 2002, Teodor Sigaev wrote: > > >>>Yeah, but the update case is inserting more entries into the index. >>>I'm wondering if that causes the index scan's state to get corrupted >>>so that it misses scanning some entries. btree has a carefully designed >>>algorithm to cope with this, but I have no idea how gist manages it. >>> >> >>Thank you, Tom. You give me a direction for looking. Attached patch fix >>the problem with broken state. Please apply it for 7.2.2 and current cvs >>(sorry, >>but I'll have a possibility to check it on current cvs only tomorrow). >> >> >> >> >> > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > -- Teodor Sigaev [EMAIL PROTECTED] ---(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] strange update problem with 7.2.1
Oleg Bartunov wrote: > Just tested with 7.2.1. It works. We have one more patch (for rtree_gist) > to submit before 7.2.2 release. > Attached patch fix a bug with creating index. Bug was reported by Chris Hodgson <[EMAIL PROTECTED]>. Please, apply it for 7.2.2 and current CVS. -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] strange update problem with 7.2.1
Sorry, forgot a patch... Teodor Sigaev wrote: > > > Oleg Bartunov wrote: > >> Just tested with 7.2.1. It works. We have one more patch (for rtree_gist) >> to submit before 7.2.2 release. >> > > Attached patch fix a bug with creating index. Bug was reported by Chris > Hodgson <[EMAIL PROTECTED]>. Please, apply it for 7.2.2 and > current CVS. > > -- Teodor Sigaev [EMAIL PROTECTED] rtree_patch.gz Description: application/gzip ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Think I see a btree vacuuming bug
On Sat, 25 May 2002 14:21:52 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >I'm somewhat concerned that the more stringent locking will slow down >VACUUM a good deal when there's lots of concurrent activity, but I don't >see another answer. Ideas anyone? Ideas? Always! :-) Don't know if this one is so bright, but at least we have something to vote on: On leaf pages order index tuples by heap item pointer, if otherwise equal. In IndexScanDescData remember the whole index tuple (including the heap item pointer) instead of ItemPointerData. Then depending on scan direction _bt_next() would look for the first index tuple greater or less than currentItem respectively. Implications: (+) higher concurrency: normal write locks (+) robust: can always start from the root, if nothing else helps (though I can't think of a case making this necesary) (-) need heap item pointer in internal nodes (could partly be compensated by omitting unused(?) t_tid.ip_posid) (+) btinsert knows, where to insert a new tuple, even if there are lots of duplicates (no random()) (-) this could result in more half-empty leaf pages? (+) dead index tuples can be removed on the fly (?) ... Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Two smart guys are looking for contracts :-)
Hi, we ( me and Teodor) are looking for some postgresql, Web short-time contracts. If somebody have some offering, please contact for details. I estimate we'll have financial problem till autumn. Our experience: 1. Search engines - small and medium scale for dynamic sites (customized OpenFTS - openfts.sourceforge.net) 2. Full scale search engine for indexing web 3. Customized data types and indexed access 4. Dynamic web sites (mod_perl + Mason), distributive CMS with role-based authorization, versioning, staging. Proved working under high load (we did rather big informational web sites) Some information is available from http://www.xware.ru/, http://www.sai.msu.su/~megera/postgres/gist/ Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Replication status
Hi, could anyone please enlighten me about the status of replication? I do expect lots of questions about this, and I'm not really sure if I can promise it for 7.3. :-) Yes, I know it#s marked urgent in the TODO list, but no one seems to be listed as tackling this topic. Thanks a lot. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Replication status
Hi, could anyone please enlighten me about the status of replication? I do expect lots of questions about this, and I'm not really sure if I can promise it for 7.3. :-) Yes, I know it's marked urgent in the TODO list, but no one seems to be listed as tackling this topic. Thanks a lot. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Invalid length of startup packet
Hello hackers! Does anyone know what the message "invalid length of startup packet" in /var/log/messages means? It says it's "fatal" - so what is the reason for this message, what does it mean and what can I do against it? I use the latest postgresql-release on a heavily loaded dedicated pentium iv machine (redhat linux). Any help or information appreciated, thanks (this has been posted on general-list earlier today) Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany http://www.topconcepts.com Tel. +49 4141 991230 mail: [EMAIL PROTECTED] Fax. +49 4141 991233 24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc) System-Partner gesucht: http://www.franchise.city-map.de Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Replication status
Michael Meskes <[EMAIL PROTECTED]> writes: > could anyone please enlighten me about the status of replication? I do > expect lots of questions about this, and I'm not really sure if I can > promise it for 7.3. :-) Unless 7.3 slips drastically from our current intended schedule (beta in late August), I think it's pretty safe to say there will be no replication in 7.3, beyond what's already available (rserv and so forth). 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] Invalid length of startup packet
"Henrik Steffen" <[EMAIL PROTECTED]> writes: > Does anyone know what the message "invalid length of startup packet" > in /var/log/messages means? Something is connecting to your postmaster and sending invalid data. > It says it's "fatal" - so what is the reason > for this message, what does it mean and what can I do against it? In this context "fatal" just means that that connection will be dropped. 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] pgstatindex
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > Sure. In my understanding, unlike tables "free/reusable space" is > actually not reused in index. pgstatindex would be usefull to judge if > REINDEX is needed by showing the growth of physical length and > "free/reusable space". Oh. Hmm, if that's what you want then I do not think an indexscan is the way to go about it. The indexscan will only visit leaf pages (and not, for example, internal nodes of a btree). Also the free-space-counting code you're using seems pretty unworkable since the indexscan is unlikely to visit leaf pages in anything like sequential order. I think the only reasonable way to get useful statistics would be to read the index directly --- page by page, no indexscan, distinguishing leaf pages, internal pages, and overhead pages for yourself. This would require index-AM-specific knowledge about how to tell which type each page is, but I believe all the index AMs make that possible. Also, I'd suggest that visiting the heap is just useless overhead. A person who wants to know whether the heap needs to be vacuumed can get that data from pgstattuple. Reading the heap to check tuple state will make this function orders of magnitude slower, while not producing much useful info that I can see. Something else to think about is how to present the results. As soon as you release this we will have people bleating about how come their btrees always show at least 1/3rd free space :-( unless we can think of a way to highlight the fact that that's the expected loading factor for a btree... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Invalid length of startup packet
Dear Tom, I have just been talking to Hans-Juergen Schoening from the hackers-list on the telephone. I found out, that I was really using postgres 7.2-1.72, (I took this as 7.2.1 :(( ) - so I updated the server, and the webserver that's connecting to the database to the latest current rpm-release. Unfortunately I still receive the same messages... The Webserver is using latest mod_perl and Pg.pm for connecting. Could this be a problem? Thanks again for your help! Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany http://www.topconcepts.com Tel. +49 4141 991230 mail: [EMAIL PROTECTED] Fax. +49 4141 991233 24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc) System-Partner gesucht: http://www.franchise.city-map.de Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Henrik Steffen" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, May 27, 2002 7:02 PM Subject: Re: [HACKERS] Invalid length of startup packet > "Henrik Steffen" <[EMAIL PROTECTED]> writes: > > Does anyone know what the message "invalid length of startup packet" > > in /var/log/messages means? > > Something is connecting to your postmaster and sending invalid data. > > > It says it's "fatal" - so what is the reason > > for this message, what does it mean and what can I do against it? > > In this context "fatal" just means that that connection will be dropped. > > 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Think I see a btree vacuuming bug
Manfred Koizar <[EMAIL PROTECTED]> writes: > On leaf pages order index tuples by heap item pointer, if otherwise > equal. In IndexScanDescData remember the whole index tuple (including > the heap item pointer) instead of ItemPointerData. Then depending on > scan direction _bt_next() would look for the first index tuple greater > or less than currentItem respectively. Doesn't help, I fear. Finding your place again is only one part of the problem. The other part is being sure that VACUUM won't delete the heap tuple before you get to it. The interlock at the index stage is partly a proxy to protect heap tuples that are about to be visited by indexscans (ie, indexscan has read an index tuple but hasn't yet acquired pin on the referenced heap page). > (+) btinsert knows, where to insert a new tuple, even if there are > lots of duplicates (no random()) This is not a (+) but a (-), I think. Given the current CVS tip behavior it is better for a new tuple to be inserted at the front of the series of matching keys --- in unique indexes this allows repeated updates without degrading search time. We are not currently exploiting that as much as we should --- I suspect btree insertion should be more willing to split pages than it now is. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Replication status
> > > >Unless 7.3 slips drastically from our current intended schedule >(beta in late August), I think it's pretty safe to say there will >be no replication in 7.3, beyond what's already available (rserv >and so forth). > I can't speak for any of the other replication projects, but pgreplication won't be ready for 7.3. If all goes according to plan, I should have some free time over the summer months to put a good dent in the first phase, but at best it would be a very limited experimental patch. More information on pgreplication can be found @ http://gborg.postgresql.org/project/pgreplication/projdisplay.php Darren ---(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] revised sample SRF C function; proposed SRF API
Joe Conway <[EMAIL PROTECTED]> writes:
> If not, prepare an array of C strings representing the
> attribute values of your return tuple, and call:
> FUNC_BUILD_SLOT(values, funcctx);
I think that's a poor choice of abstraction, as it forces the user into
the least-efficient-possible way of building a return tuple. What if
he's already got a tuple (eg, he read it off disk), or at any rate has
datums already in internal format? I'd say make it
FUNC_RETURN_NEXT(funcctx, HeapTuple)
and let the caller worry about calling heap_formtuple or otherwise
constructing the tuple.
For similar reasons I think the initial call ought to provide a
TupleDesc structure, not a relation name (which is at least two lookups
removed from the information you actually need).
The max_calls thing doesn't seem quite right either; at least not as
something that has to be provided in the "first line after the function
declarations". It might be quite expensive to derive, and you don't
need to do so on every call. Perhaps better have the macro return a
boolean indicating whether this is the first call or not, and then
people can do
if (FUNC_MULTIPLE_RESULT(funcctx))
{
// do one-time setup here,
// including possibly computing a max_calls value;
// also find or make a TupleDesc to be stored into the
// funcctx.
}
Similarly I'm confused about the usefulness of misc_ctx if it has to be
re-provided on every call.
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] Redhat 7.3 time manipulation bug
On Thu, 2002-05-23 at 07:20, Michael Meskes wrote: > The glibc version in the soon to be released Woody > release is 2.2.5. The version in RHL7.3 is 2.2.5-34. This is not what Debian uses. Maybe you should read the changelog for the version. -- ---. ,-. 1325 Chesapeake Terrace Ulrich Drepper \,---' \ Sunnyvale, CA 94089 USA Red Hat `--' drepper at redhat.com ` signature.asc Description: This is a digitally signed message part
[HACKERS] is there any backend timeout undocumented?
Hi! I have sended the message below to pgadmin-support but receive no answers... I hope you can help me on this... Is there any server timeout that is undocumented? I've issued a query like the one below and the server timed out after 180min (+/-). The query "construct_warehouse()" can last well above the 180min because it fills a table with millions of tuples... spid=> vacuum full analyze ; select construct_warehouse() ; vacuum analyze ; NOTICE: Skipping "pg_group" --- only table or database owner can VACUUM it NOTICE: Skipping "pg_database" --- only table or database owner can VACUUM it NOTICE: Skipping "pg_shadow" --- only table or database owner can VACUUM it VACUUM server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. You are currently not connected to a database. !> \q I've searched the archives for some documented timeout but nothing... I've searched the postgresql.conf file and nothing... Can anyone help me? Thanks in advance! Note: the first time I noticed a time out was using a JDBC driver and then I've tested in the pgsql to confirm it. -- o__Bem haja, _.>/ _ NunoACHenriques (_) \(_) ~~~ http://students.fct.unl.pt/users/nuno/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] revised sample SRF C function; proposed SRF API
Joe Conway writes: > Here is a revised patch for a sample C function returning setof > composite. (Same comments as last time -- It is a clone of SHOW ALL as > an SRF. For the moment, the function is implemented as contrib/showguc, > although a few minor changes to guc.c and guc.h were required to support > it.) We need a function like this in the main line. The "show all" variety isn't top priority, but we need something that gets you the "show" result as a query output. The original idea was to make SHOW return a query result directly, but a function is fine with me too. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Redhat 7.3 time manipulation bug
On Fri, 2002-05-24 at 12:03, Peter Eisentraut wrote: > > Or does the -34 mean more than just the RedHat version number? The > > Debian version is correctly named 2.2.5-6 where the -6 means that this > > is the 6th release of glibc 2.2.5 for Debian, > > Just for general amusement: I run SuSE's glibc 2.2.5-38 which contains > neither the questionable code in the original sources nor is there any > reference to it in the patch set. Go figure. This is getting silly. Does nobody here understand that the release number is local for each distribution. Comparing them does not lead to anything. If you want to find out run rpm -q --changelog glibc | less on a RH system. Don't know what other systems provide in this direction. You'll see that the glibc in RHL7.3 contains a lot of the code from the glibc 2.3 branch. It's not named 2.2.90 because major pieces are missing. If you still don't know that version numbers are meaningless for determining feature lists you might want to consider going back to your CS101 class and revisit software configuration management. -- ---. ,-. 1325 Chesapeake Terrace Ulrich Drepper \,---' \ Sunnyvale, CA 94089 USA Red Hat `--' drepper at redhat.com ` signature.asc Description: This is a digitally signed message part
Re: [HACKERS] SRF rescan testing
Tom Lane wrote: >>3. PL/pgSQL support for returning sets -- this seems to me like an >>important item if SRFs are to be useful to the masses. Any pointers on >>how to approach this would be appreciated. > > Does Oracle's pl/sql support this? If so what does it look like? I *think* Oracle pl/sql can return (the equivilent of) setof composite using a special Oracle package (DBMS_OUTPUT, see: http://www.ora.com/catalog/oraclebip/chapter/ch06.html), but it cannot be used as a row source in a FROM clause. Hopefully an Oracle guru will correct or add to this. I know that MS SQL Server can return one *or more* result sets from a "stored procedure", however they cannot be used as FROM clause row sources either (at least not as of MSSQL 7, but I don't think that has changed in MSSQL 2000). The syntax is something like: exec sp_myprocedure It is *not* possible to define a VIEW based on a stored procedure, but many MS centric report writers allow the "exec sp_myprocedure" syntax as a row source for reports. As far as PL/pgSQL is concerned, I was thinking that a new type of RETURN (maybe "RETURN NEXT myval" ??) command could be used, which would indicate "rsi->isDone = ExprMultipleResult", and that the standard RETURN command would set "rsi->isDone = ExprEndResult", but only if "fcinfo->resultinfo != NULL". That way you could do something like: . . . FOR row IN select_query LOOP statements RETURN NEXT row; END LOOP; RETURN NULL; . . . Does this sound reasonable? Joe ---(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] SRF rescan testing
Joe Conway <[EMAIL PROTECTED]> writes: > I'm thinking about next steps for SRFs and looking for input. ... At > this point I know of several things which need to be done (or at least I > think they are desirable): > 1. Documentation -- it wasn't clear if Joel Burton was going to have > time to contribute something here, but if not, I'll start working on > this next. Any guidance as to which section of the docs this should go in? There is related material currently in the SQL-functions section of the programmer's guide. This should perhaps be moved to someplace where it's more clearly relevant to all types of functions. On the other hand it's awfully nice to be able to show simple examples, so I'm not sure we want to divorce the material from SQL functions entirely. > 3. PL/pgSQL support for returning sets -- this seems to me like an > important item if SRFs are to be useful to the masses. Any pointers on > how to approach this would be appreciated. Does Oracle's pl/sql support this? If so what does it look like? > 6. Support for named composite types that don't have a table tied to them. I agree that this is bottom priority. It doesn't really add any functionality (since a dummy table doesn't cost much of anything). And a clean solution would require major rearchitecting of the system tables --- pg_attribute rows would need to be tied to pg_type rows for composite types, not to pg_class rows. While this would be quite doable considering the backend alone, I'm not excited about the prospect of breaking every catalog-examining client in sight. Another interesting question is whether inheritance now applies to types rather than tables, and if so what does that imply? (OTOH one could make a good argument that now is the time to do it if we're ever gonna do it --- clients that are not schema-aware will be badly in need of work anyway for 7.3...) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SRF rescan testing
Joe Conway wrote: > Tom Lane wrote: >> >> Now that I think about it, it's possible that ExecFunctionReScan is >> correct now, at least given the simplistic always-materialize policy >> that we've implemented so far. But it hasn't gotten much testing. > > OK -- the attached (stand alone) test script exercises > ExecFunctionReScan, including cases with chgParam != NULL. I'll try to > come up with one or two more variants for the latter, but so far I have > not found any misbehavior. I'm thinking about next steps for SRFs and looking for input. The current status is that SRFs seem to work properly in the alway-materialize mode, for the following cases of FROM clause functions and VIEWs created based on FROM clause functions: (rehash from earlier post) LanguageRetSet RetType Status --- --- --- - C t b OK C t c Not tested C f b OK C f c Not tested SQL t b OK SQL t c OK SQL f b OK SQL f c OK PL/pgSQLt b No retset support PL/pgSQLt c No retset support PL/pgSQLf b OK PL/pgSQLf c OK - RetSet: t = function declared to return setof something RetType: b = base type; c = composite type I've also submitted a patch for a regression test (any feedback?). At this point I know of several things which need to be done (or at least I think they are desirable): 1. Documentation -- it wasn't clear if Joel Burton was going to have time to contribute something here, but if not, I'll start working on this next. Any guidance as to which section of the docs this should go in? 2. Create a sample C-function which returns setof a composite type (possibly in conjunction with #1) 3. PL/pgSQL support for returning sets -- this seems to me like an important item if SRFs are to be useful to the masses. Any pointers on how to approach this would be appreciated. 4. Non-materialize mode support for SRFs. 5. Improve the system so that lower-level plan nodes will be told whether they need to support rescan. 6. Support for named composite types that don't have a table tied to them. Have I missed anything major? Is this order of priority reasonable? Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Replication status
Tom Lane wrote: > Michael Meskes <[EMAIL PROTECTED]> writes: > > could anyone please enlighten me about the status of replication? I do > > expect lots of questions about this, and I'm not really sure if I can > > promise it for 7.3. :-) > > Unless 7.3 slips drastically from our current intended schedule > (beta in late August), I think it's pretty safe to say there will > be no replication in 7.3, beyond what's already available (rserv > and so forth). Last I talked to Darren, the replication code was modified to merge into our 7.2 tree. There are still pieces missing so it will not be functional when applied. It is remotely possible there could be master-slave in 7.3, but I doubt it. I was hoping to spend major time on it myself (and SRA/Japan has encouraged me to get involved), but have been too busy to dive in. I think once it is in CVS, it will be easier to grasp what is going on, and perhaps to move it forward. I saw a message (I think for Darrren) saying he hoped to restart on it in two weeks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] is there any backend timeout undocumented?
NunoACHenriques <[EMAIL PROTECTED]> writes: > Is there any server timeout that is undocumented? No. > spid=> vacuum full analyze ; select construct_warehouse() ; vacuum analyze ; > NOTICE: Skipping "pg_group" --- only table or database owner can VACUUM it > NOTICE: Skipping "pg_database" --- only table or database owner can VACUUM it > NOTICE: Skipping "pg_shadow" --- only table or database owner can VACUUM it > VACUUM > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. This looks like a crash to me, not a timeout. Can you provide us with a stack backtrace? Also, you'd better explain what construct_warehouse() is doing. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] NO CREATE TABLE
Please, I saw some articles about de implementation off some security rules like NO CREATE TABLE and the possibility of the implementation in version 7.2 of PostgreSQL. Could you confirm this information? Is there this implementation in 7.2? If not, what coul I do to create a user without the privilege CREATE TABLE? begin:vcard n:Abade;Marcia tel;work:3371 7132 x-mozilla-html:FALSE org:GIT/CAD ramal 7132 adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:Administradora de Banco de Dados fn:Marcia Abade end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL FILES
Olivier PRENANT wrote: > Hi every one. > > I just moved (at last!) to 7.2.1. Works like a charm... > I'm suprised though by the number of WAL files. > > I have 8 files where postgresql.conf says WAL_FILES=4. > > What did I miss ? (I have no outstanding transaction) > > FWIW, t's on UW711. No, you are fine. The current GUC params are confusing. I did update the documentation for 7.3, but I plan to reorganize those params to be more meaningful. Actually, I have in TODO: Remove wal_files postgresql.conf option because WAL files are now recycled because the param no longer controls what you think it controls. In 7.1 WAL files where not recycled, so WAL_FILES was used to pre-allocate files so there wasn't as much happening during checkpoint. Now, with recycling, there is no need. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] strange update problem with 7.2.1
Teodor Sigaev <[EMAIL PROTECTED]> writes: >> Yeah, but the update case is inserting more entries into the index. >> I'm wondering if that causes the index scan's state to get corrupted >> so that it misses scanning some entries. > Thank you, Tom. You give me a direction for looking. Attached patch fix > the problem with broken state. Hmm, is this patch really correct? Removing the gistadjscans() call from gistSplit seems wrong to me --- won't that miss reporting splits on leaf pages? Or does this not matter for some reason? 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] is there any backend timeout undocumented?
On Thu, 23 May 2002 18:36:17 +0100 (WEST), NunoACHenriques <[EMAIL PROTECTED]> wrote: >server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. >The connection to the server was lost. Attempting reset: Failed. >You are currently not connected to a database. I've seen this before. In my case it was not a timeout, but a backend crash. What version are you running? Do you find anything useful in the log file? Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] revised sample SRF C function; proposed SRF API
Peter Eisentraut wrote: > We need a function like this in the main line. The "show all" variety > isn't top priority, but we need something that gets you the "show" result > as a query output. The original idea was to make SHOW return a query > result directly, but a function is fine with me too. > Originally I wrote this as "showvars(varname)" and accepted 'all' in a similar fashion to SHOW ALL. But it seemed redundant since you can still do: test=# select * from showvars() where varname = 'wal_sync_method'; varname | varval -+--- wal_sync_method | fdatasync (1 row) but you can also do: test=# select * from showvars() where varname like 'show%'; varname | varval -+ show_executor_stats | off show_parser_stats | off show_planner_stats | off show_query_stats| off show_source_port| off (5 rows) which also seemed useful. I was thinking that if we wanted to replace SHOW X with this, it could be done in the parser by rewriting it as "SELECT * FROM showvars() WHERE varname = 'X'", or for SHOW ALL just "SELECT * FROM showvars()". In any case, I'll fit the showvars() function into the backend and submit a patch. Thanks, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] is there any backend timeout undocumented?
On Thu, 23 May 2002 18:36:17 +0100 (WEST) "NunoACHenriques" <[EMAIL PROTECTED]> wrote: > Is there any server timeout that is undocumented? Looks more like a backend crash to me. Can you look for a core file in $PGDATA/base/xxx/ (where xxx is the OID of your database)? If you don't have debugging already enabled, try rebuilding PostgreSQL with debugging support (./configure --enable-debug, or "-g" CFLAGS), and then getting a backtrace with gdb. Also, posting the source of construct_warehouse() might be helpful. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] is there any backend timeout undocumented?
NunoACHenriques <[EMAIL PROTECTED]> writes: > I've issued a query like the one below and the server timed out after > 180min (+/-). The query "construct_warehouse()" can last well above the > 180min because it fills a table with millions of tuples... > > > spid=> vacuum full analyze ; select construct_warehouse() ; vacuum analyze ; > NOTICE: Skipping "pg_group" --- only table or database owner can VACUUM it > NOTICE: Skipping "pg_database" --- only table or database owner can VACUUM it > NOTICE: Skipping "pg_shadow" --- only table or database owner can VACUUM it > VACUUM > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > You are currently not connected to a database. As the message says the backend is not "timing out"; it's terminating *abnormally*, What's doing the construct_warehouse() function?, It's written in C?, Could you send the backtrace from the core file? Regards, Manuel. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Re : Solaris Performance - 64 bit puzzle
TODO updated: Add BSD-licensed qsort() for 32-bit Solaris --- Andrew Sullivan wrote: > On Tue, Apr 30, 2002 at 03:28:13PM -0400, Tom Lane wrote: > > > Do you need to profile it? It seemed that the 32-bit behavior for > > many-equal-keys was so bad that it'd be easy to tell whether it's > > fixed, just by rough overall timing of a test case... > > Sorry for taking yet again so long. Fitting in little tests of this > sort of thing can be a bit of a bear -- there's always about 50 other > things to do. Anyway, I've performed some simple timed tests that, I > think, confirm that the 64 bit library on Solaris is not so bad. > > version > - > PostgreSQL 7.2.1 on sparc-sun-solaris2.7, compiled by GCC 3.0.3 > > bin$ file postmaster > postmaster: ELF 64-bit MSB executable SPARCV9 Version 1, > dynamically linked, not stripped > > The config file is the default > > I _think_ I've captured the case that was problematic. As I > understood it, qsort was having trouble when hit with many equal > keys. I created this table: > > CREATE TABLE table1 (_date_stamp timestamp default current_timestamp, > foo text); > > The table has no index. It has 512 records; field "foo" has only > four distinct values. > > No matter whether I compiled with the system qsort or the qsort from > FreeBSD, I got roughly equivalent results running psql under time. I > know that's hardly an ideal test, but as Tom suggested, the 32-bit > case seemed to be so astonishingly bad that it should have been > enough. I ran the test repeatedly, and the results seem pretty > consistent. Here are some typical results: > > system lib: > > src$ time psql -p 12000 -o /dev/null -c "select * from table1 order > ^by foo" test1 > > real29m23.822s > user2m10.241s > sys 0m7.432s > > FreeBSD lib: > > postgresql-7.2.1$ time psql -p 12000 -o /dev/null -c "select * from > table1 order by foo" test1 > > > real29m38.880s > user2m10.571s > sys 0m8.032s > > > This example suggests the FreeBSD library is slightly worse in the > 64-bit case. That's consistently the case, but the difference is not > so great that I'd put any stock in it. > > I do not know whether there might be any trouble using the FreeBSD > library in a 64-bit configuration. I'd say, if you're going to use a > 64-bit postmaster, use the Solaris libraries. > > Hope this is helpful, > > A > > -- > > Andrew Sullivan 87 Mowat Avenue > Liberty RMS Toronto, Ontario Canada > <[EMAIL PROTECTED]> M6K 3E3 > +1 416 646 3304 x110 > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgstatindex
> Oh. Hmm, if that's what you want then I do not think an indexscan is > the way to go about it. The indexscan will only visit leaf pages > (and not, for example, internal nodes of a btree). Also the > free-space-counting code you're using seems pretty unworkable since the > indexscan is unlikely to visit leaf pages in anything like sequential > order. Oh I was not aware of this. > I think the only reasonable way to get useful statistics would be to > read the index directly --- page by page, no indexscan, distinguishing > leaf pages, internal pages, and overhead pages for yourself. This would > require index-AM-specific knowledge about how to tell which type each > page is, but I believe all the index AMs make that possible. That's what I'm afraid of. > Also, I'd suggest that visiting the heap is just useless overhead. A > person who wants to know whether the heap needs to be vacuumed can get > that data from pgstattuple. Reading the heap to check tuple state will > make this function orders of magnitude slower, while not producing much > useful info that I can see. Ok let me think about this. Thank you for the suggestion! -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] the parsing of parameters
Jan Wieck wrote: > Tom Lane wrote: > > Jan Wieck <[EMAIL PROTECTED]> writes: > > >> Hmm. So your vision of PREPARE would allow the backend to reply > > >> with a list of parameter types. How would you envision that working > > >> exactly? > > > > > I guess there's some sort of statement identifier you use to > > > refer to something you've prepared. Wouldn't a function call > > > returning a list of names or type oid's be sufficient? > > > > I was thinking of having the type names returned unconditionally, > > perhaps like a SELECT result (compare the new behavior of EXPLAIN). > > But if we assume that this won't be a commonly used feature, maybe > > a separate inquiry operation is better. > > I wouldn't mind. One way or the other is okay with me. > > Reminds me though of another feature we should have on the > TODO. INSERT/UPDATE/DELETE ... RETURNING ... TODO already has: o Allow INSERT/UPDATE ... RETURNING new.col or old.col; handle RULE cases (Philip) Do we need DELETE too? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Re : Solaris Performance - 64 bit puzzle
On Mon, 27 May 2002 21:00:43 -0400 (EDT) "Bruce Momjian" <[EMAIL PROTECTED]> wrote: > TODO updated: > > Add BSD-licensed qsort() for 32-bit Solaris Is this necessary? Didn't someone say that Sun had acknowledged the performance problem and were going to be releasing a patch for it? If that patch exists (or will exist), it would probably be better to suggest in the docs that users of 32-bit Solaris apply the patch. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Redhat 7.3 time manipulation bug
On Friday 24 May 2002 03:15 pm, Ulrich Drepper wrote: > This is getting silly. Yes, Ulrich, it is. Very silly. And Red Hat's stance is one of the silliest, IMHO. >You'll see that the glibc in RHL7.3 contains a lot of the > code from the glibc 2.3 branch. It's not named 2.2.90 because major > pieces are missing. > If you still don't know that version numbers are meaningless for > determining feature lists you might want to consider going back to your > CS101 class and revisit software configuration management. IOW, Red Hat's glibc 2.2.5 isn't really pristine glibc 2.2.5 as found straight from the GNU repository. In fact, Red Hat glibc 2.2.5 isn't really 2.2.5 -- how about 2.2.96? :-) .96 was good enough for gcc Furthermore, Red Hat glibc 2.2.5 isn't even fully compatible with GNU glibc 2.2.5 -- at least in the area of time_t stuff. In the open source world, version numbers are actually supposed to mean something -- at least for package dependencies. Of course, I also have read the kernel-2.4.18 source RPM and its 21.8MB 'ac-bits' patch. You do realize that this sort of thing doesn't help Red Hat's PR state amongst the greater open source community, right? Nor would it help Mandrake, SuSE, or any other Linux distributor (I specifically excluded Debian due to its unique community supported state). But, if you don't care about the greater open source community, well... And I say all of that while running and enjoying the greater part of Red Hat 7.3. For the most part it is extraordinarily stable. And I know that that 21.8MB kernel patch is one of the reasons it is so stable. But I still question the versioning of glibc. So, in summary, the glibc version number in any particular linux distribution is meaningless because the distributor is free to patch the bloody daylights out of it at any time. Sweet. And so standard. But, if glibc 2.3 is where this bit came from, it is just a matter of time before all Linux distributions (that aren't willing to patch away) get this braindead behavior. Oh well. The general solution will happen. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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] revised sample SRF C function; proposed SRF API
Tom Lane wrote:
> Joe Conway <[EMAIL PROTECTED]> writes:
>
>> If not, prepare an array of C strings representing the attribute
>> values of your return tuple, and call: FUNC_BUILD_SLOT(values,
>> funcctx);
>
> I think that's a poor choice of abstraction, as it forces the user
> into the least-efficient-possible way of building a return tuple.
> What if he's already got a tuple (eg, he read it off disk), or at
> any rate has datums already in internal format? I'd say make it
>
> FUNC_RETURN_NEXT(funcctx, HeapTuple)
>
> and let the caller worry about calling heap_formtuple or otherwise
> constructing the tuple.
Hmmm - well, I agree that FUNC_RETURN_NEXT(funcctx, HeapTuple) is a
better abstraction, particularly for experience backend hackers ;)
but I was trying to also make this accessable to someone writing a
custom C function that isn't necessarily very familiar with forming
their own HeapTuples manually. What if we also had something like:
FUNC_BUILD_TUPLE(values, funcctx);
which returns a tuple for the less experienced folks (or people like me
when I'm being lazy :)) It could be used when desired, or skipped
entirely if a HeapTuple is already easily available.
>
> For similar reasons I think the initial call ought to provide a TupleDesc
> structure, not a relation name (which is at least two lookups removed
> from the information you actually need).
Same comments. How about:
FUNC_BUILD_TUPDESC(_relname)
and
FUNC_MULTIPLE_RESULT(_funcctx, _tupdesc, _max_calls, _fctx)
?
Power hackers could skip FUNC_BUILD_TUPDESC if they wanted to or already
had a TupleDesc available.
Of course you would only want to build your tupdesc during the first
pass, so maybe we'd need
FUNC_IS_FIRSTPASS()
which would just check for (fcinfo->flinfo->fn_extra == NULL)
>
> The max_calls thing doesn't seem quite right either; at least not as
> something that has to be provided in the "first line after the
> function declarations". It might be quite expensive to derive, and
> you don't need to do so on every call.
I thought about that, but the value is not required at all, and you can
easily set it later when more convenient. Perhaps it should be taken out
of the initialization and we just document how it might be used?
> Perhaps better have the macro return a boolean indicating whether
> this is the first call or not, and then people can do
>
> if (FUNC_MULTIPLE_RESULT(funcctx)) { // do one-time setup here, //
> including possibly computing a max_calls value; // also find or make
> a TupleDesc to be stored into the // funcctx. }
hmm - see complete new example below.
>
> Similarly I'm confused about the usefulness of misc_ctx if it has to
> be re-provided on every call.
Like max_calls, maybe it should be taken out of the initialization and
its potential use documented.
On second thought, I think maybe I tried to do too much with
FUNC_MULTIPLE_RESULT. It does initialization during the first pass, and
then does per call setup for subsequent calls. Maybe there should be:
FUNC_FIRSTCALL_INIT
and
FUNC_PERCALL_SETUP
Then the whole API looks something like:
Datum
my_Set_Returning_Function(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
/*
* Optional - user defined code needed to be called
* on every pass
*/
if(FUNC_IS_FIRSTPASS())
{
/*
* Optional - user defined initialization which is only
* required during the first pass through the function
*/
/*
* Optional - if desired, use this to get a TupleDesc
* based on the function's return type relation
*/
FUNC_BUILD_TUPDESC(_relname);
/*
* Required - memory allocation and initialization
* which is only required during the first pass through
* the function
*/
FUNC_FIRSTCALL_INIT(funcctx, tupdesc);
/*
* optional - total number of tuples to be returned.
*
*/
funcctx->max_calls = my_max_calls;
/*
* optional - pointer to structure containing
* user defined context
*/
funcctx->fctx = my_func_context_pointer;
}
/*
* Required - per call setup
*/
FUNC_PERCALL_SETUP(funcctx)
/*
* Here we need to test whether or not we're all out
* of tuples to return. The test does not have to be
* this one, but in many cases this is probably what
* you'll want.
*/
if (call_cntr < max_calls)
{
/*
* user code to derive data to be returned
*/
/*
* Optional - build a HeapTuple given user data
* in C string form
* values is an array of C strings, one for each
* attribute of the return tuple
*/
tuple = FUNC_BUILD_TUPLE(values, funcctx);
/*
* Required - returns the tuple and notifies
* the caller that we still have more to do
*/
FU
