Re: [HACKERS] 8.2 features status
On Aug 5, 2006, at 10:48 PM, Christopher Browne wrote: Quoth [EMAIL PROTECTED] (David Fetter): On Fri, Aug 04, 2006 at 02:37:56PM -0700, Neil Conway wrote: On Fri, 2006-08-04 at 12:40 -0700, David Fetter wrote: While I am not going to reopen the can of worms labeled 'bug tracker', I think it would be good to have a little more formality as far as claiming items goes. What say? I think this is a good plan for adding additional process overhead, and getting essentially nothing of value in return. I'm not convinced there's a problem in need of solving here... Perhaps you'd like to explain how big a burden on the developer it is to send an once a week, that being what I'm proposing here. As far as the "problem in need of solving," it's what Andrew Dunstan referred to as "splendid isolation," which is another way of saying, "letting the thing you've taken on gather dust while people think you're working on it." It seems to me once a week is a bit too often to demand, particularly when trying to "herd cats." A burden of once a month may seem more reasonable. One of the problems is that CVS branching is rather painful and some contributors can't commit. If there were some place where one could maintain a publicly-visible development branch just for feature X, that would make the work open source and trackable instead of "open-source-once-I'm-done". -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Progress bar updates
Why make it so complicated? There could be a guc to indicate that the client is interested in progress updates. For the execution phase, elog(INFO,...) could be emitted for each major plan node. (The client would probably run the explain plan beforehand or it would be embedded in the elog). During the downloading of the rows, the client would display the bar relative to the number of estimated rows returned. -M On Jul 18, 2006, at 2:35 PM, Gregory Stark wrote: Has anyone looked thought about what it would take to get progress bars from clients like pgadmin? (Or dare I even suggest psql:) My first thought would be a message like CancelQuery which would cause the backend to peek into a static data structure and return a message that the client could parse and display something intelligent. Various commands would then stuff information into this data structure as they worked. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SPI Elections and mailing list
Sorry- perhaps I misunderstand the purpose of your group, but how can you claim to be making decisions on "software in the public interest" on a private, paid-member mailing list? -M On Jul 16, 2006, at 2:10 PM, Josh Berkus wrote: Folks, Hopefully by now a bunch of you have joined as Software in the Public Interest Contributing members per my earlier e-mail and are aware that the SPI annual board election has started. If you are a registered contributing member with SPI, elections are at: http://members.spi-inc.org/vote/ and candidate statements are at: http://www.spi-inc.org/secretary/votes/vote5/ Voting closes July 28th. If you did not already register as an SPI contributing member, it is too late for this year. Please also note that the current volume of e-mail on the spi-private mailing list is due entirely to the election and is not at all typical of the list. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Online index builds
A great first step would be to add elog(INFO,...) in some standardized format over the wire so that clients can tell what's going on. It could be triggered by a GUC which is off by default. -M On Jul 15, 2006, at 9:10 PM, Greg Stark wrote: Hannu Krosing <[EMAIL PROTECTED]> writes: Maybe we can show progress indicators in status line (either pg_stat_activity.current_query or commandline shown in ps), like WAITING TO START PHASE 1 - WAITING FOR TRANSACTION XXX TO COMPLETE or INSERTING INDEX ENTRY N OF M changing every few seconds. Hm. That would be very interesting. I'll say that one of the things that impressed me very much with Postgres moving from Oracle was the focus on usability. Progress indicators would be excellent for a lot of operations. That said I'm not sure how much I can do here. For a substantial index we should expect most of the time will be spent in the tuplesort. It's hard to see how to get any sort of progress indicator out of there and as long as we can't it's hard to see the point of getting one during the heap scan or any of the other i/o operations. I think it does make sense to put something in current_query indicating when it's waiting for transactions to end and when it's past that point. That's something the DBA should be aware of. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] 10th Anniversary Conference
Dear Hackers, I would like to thank all of you for organizing, hosting, and attending the 10th Anniversary PostgreSQL Conference last weekend. I was especially interested in future PostgreSQL directions and that was definitely the conference's theme. It was great to meet the community's big wigs, too. Thanks especially to Josh, Gavin, and the other main organizers for making sure everything ran smoothly. I would most certainly be interested in future annual meetings. If any of you are ever in the Boston area, drop me a line for a free beer.* And, as mentioned by Bruce Momjian during his keynote, thanks for making a great database product that allows so many of us to pay the bills! Best regards, M *Offer void where prohibited. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] binds only for s,u,i,d?
Why are only select, insert, update, and delete supported for $X binds? Why can't preparation be used as a global anti-injection facility? Example using the backend protocol for binds: PREPARE TRANSACTION $1; bind $1 ['text'] -->syntax error at $1 Why am I able to prepare statements with the backend protocol that I can't prepare with PREPARE: agentm=# prepare gonk as prepare transaction $1; ERROR: syntax error at or near "prepare" at character 17 LINE 1: prepare gonk as prepare transaction $1; whereas the backend protocol only emits an error when the statement is executed [and the binds are ignored]. -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] vacuum, performance, and MVCC
On Jun 22, 2006, at 9:56 PM, Christopher Kings-Lynne wrote: The example is a very active web site, the flow is this: query for session information process HTTP request update session information This happens for EVERY http request. Chances are that you won't have concurrent requests for the same row, but you may have well over 100 HTTP server processes/threads answering queries in your web server farm. You're crazy :) Use memcache, not the DB :) Still, the database is the one central location that the apaches can connect too- postgres already has a lot of application platform features- locking synchronization, asynchronous notifications, arbitrary pl code. Personally, I think that a special non-MVCC table type could be created- the catalogs are similarly flat. What I envision is a table type that can only be accessed "outside" transactions (like AutoCommit mode)- this is already possible to implement in plperl for a single session. It would be more efficient to have something like a global temp table hanging around... Just some random ideas... -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] That EXPLAIN ANALYZE patch still needs work
It's worth noting that on Darwin (on Apple hardware) gettimeofday is never a syscall whereas on Linux (AFAIK), it always is. On Jun 8, 2006, at 7:58 PM, Mark Kirkwood wrote: Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Wow, that is slow. Maybe a problem in the kernel? Perhaps something similar to this: http://www.ussg.iu.edu/hypermail/linux/kernel/0603.2/index.html#1282 Yeah, that's a pretty interesting thread. I came across something similar on a Red Hat internal list. It seems there are three or four different popular standards for clock hardware in the Intel world, and some good implementations and some pretty bad implementations of each. So the answer may well boil down to "if you're using cheap junk PC hardware then gettimeofday will be slow". OS seems to matter as well - I've got two identical Supermicro P3TDER dual intel boxes. 1 running FreeBSD 6.1, one running Gentoo Linux 2.6.16. Doing the 'select count(*) vs explain analyze select count(*) on 10 row table gives: Freebsd : select 108 ms explain analyze 688 ms Linux : select 100 ms explain analyze 196 ms Both systems have ACPI enabled in BIOS (which means there is a better timecounter than 'i8254' available (FreeBSD says its using 'ACPI-safe' - not sure how to check on Linux). ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Protocol Message Graph
On Apr 23, 2006, at 6:43 PM, Alvaro Herrera wrote: Agent M wrote: I have created a directed graph using graphviz that shows the message flow/event stream. Perhaps this will be helpful to someone. Of course, corrections are also welcome. Interesting. I'm not sure how to read the graph however -- for example what does it mean to have ReadyForQuery in both a rectangle and an ellipse? What do the standalone boxes mean? (e.g. the SSLRequest and CancelRequest? and why are they wrapped in two boxes? why Close/CloseComplete appear from nowhere and they don't lead anywhere?) Maybe it would make more sense to have a graph of states, and the edges would indicate what messages are sent to change from one state to another. I tried to group the messages by functionality: asynchronous, simple, extended, copy, function, etc. Since ReadyForQuery shows up in multiple modes, I put it in its own mode (hence the additional bounding box). I agree that the graph isn't perfect, but I also ran up against certain limitations in graphiz itself (e.g., only one level of subgraph is supported). So it's useful as a guide, but it clearly isn't a state machine. -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Protocol Message Graph
I have created a directed graph using graphviz that shows the message flow/event stream. Perhaps this will be helpful to someone. Of course, corrections are also welcome. http://www.themactionfaction.com/pg/PGXProtocol.dot http://www.themactionfaction.org/pg/PGXProtocol.svg http://www.themactionfaction.org/pg/PGXProtocol.pdf (Messages in ellipses are sent from the server, rectangle message are sent from the client.) -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Speaking of pgstats
The general idea would be to still use UDP backend->stats but get rid of the pipe part (emulated by standard tcp sockets on win32), so we'd still have the "lose packets instead of blocking when falling behind". Right. Please correct me if I am wrong, but using UDP logging on the same computer is a red herring. Any non-blocking I/O would do, no? If the buffer is full, then the non-blocking I/O send function will fail and the message is skipped. Has anyone observed UDP ever drop *written* packets on loopback? Looking at the Darwin 8 sources, it appears that the loopback streams all converge to the same stream code, which makes sense... If a kernel is too busy to handle I/O, doesn't it have higher priorities than switching to a user context? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] control pg_hba.conf via SQL
But there is still no way to verify that the information in the file is what postgres saw last. DBAs make mistakes too. A simple way to view the current access state would be much appreciated. On Apr 1, 2006, at 1:01 PM, Andrew Dunstan wrote: Er, how can the file be changed behind the scenes? Only if you have opened up permission to the directory to someone else, or you don't trust your sysadmins. Either way you would then have much bigger problems than this. cheers andrew ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] control pg_hba.conf via SQL
Unfortunately, there is still one serious deficiency with the solution below- it may not be the actual information postgresql is currently using to determine who can log in and how- the file can be easily changed behind the scenes and there is currently no way to know. I (speaking as a DBA) would still very much appreciate a static, frozen table view accessible from SQL. On Mar 30, 2006, at 3:05 PM, David Fetter wrote: On Thu, Mar 30, 2006 at 10:43:31AM -0500, Andrew Dunstan wrote: A.M. wrote: Could postgres offer at least a read-only view of the data in the interim? Ordering could be controlled by line number. You can get the contents as a single text field like this: | select pg_read_file|('pg_hba.conf', 0, 50*1024); Writing a plperl function that would strip comments and blank lines and return the rest as a numbered set of lines would be fairly trivial. You don't even need PL/Perl :) SELECT * FROM ( SELECT s.t AS "Ordering", (string_to_array(pg_read_file( 'pg_hba.conf', 0, (pg_stat_file('pg_hba.conf')).size ), '\n'))[s.t] AS "Line" FROM generate_series( 1, array_upper( string_to_array(pg_read_file( 'pg_hba.conf', 0, (pg_stat_file('pg_hba.conf')).size ), '\n'), 1 ) ) AS s(t) ) AS foo WHERE "Line" !~ '^#' AND "Line" !~ '^\s*$' ; Cheers, D ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] listen not schema-aware
Why is the schema ignored entirely when using listen/notify? I couldn't find any mention of this in the documentation. Ideally, it should support schemas (and store any string it takes) but it should at least throw an error when a schema is prepended. I guess the workaround is to simply delete the period. client 1: listen schema1.msg; client 2: notify schema1.msg; notify schema2.msg; client 1: Asynchronous notification "msg" received from server process with PID X. Asynchronous notification "msg" received from server process with PID X. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] UTF8 or Unicode
On Feb 14, 2005, at 9:27 PM, Abhijit Menon-Sen wrote: I know UTF8 is a type of unicode but do we need to rename anything from Unicode to UTF8? I don't know. I'll go through the documentation to see if I can find anything that needs changing. It's not the documentation that is wrong. Specifying the database "encoding" as "Unicode" is simply a bug (see initdb). What if postgresql supports UTF-16 in the future? What would you call it? Also, the backend protocol also uses "UNICODE" when specifying the encoding. All the other encoding names are specified correctly AFAICS. I brought this up before: http://archives.postgresql.org/pgsql-hackers/2004-10/msg00811.php We could make UTF8 the canonical form in the aliasing mechanism, but beta 4 is a bit late to come up with this kind of idea. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] "UNICODE" encoding
Would the version bump be a good time to fix the "UNICODE" encoding misnomer in database creation and in the backend param status? I assume it should be "UTFx". ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org