Re: [HACKERS] Proposal: Commit timestamp
One concept is to have a univeral clock that ticks forward (like
every second) and each node orders all their transactions inside the
second-granular tick. Then each commit would be like: {node,
clocksecond, txn#} and each time the clock ticks forward, txn# is
reset to zero. This gives you ordered txns that windowed in some
cluster-wide acceptable window (1 second). However, this is totally
broken as NTP is entirely insufficient for this purpose because of a
variety of forms of clock skew. As such, the timestamp should be
incremented via cluster consensus (one token ring or the pulse
generated by the leader of the current cluster membership quorom).
I think you are completely ignoring practicability. Or are you saying,
that such a system exists and works for e.g. a loosly connected group of
laptop field agents that only sporadically have a connection to the
cluster.
I think Jan's definition gives a pragmatic solution to the problem,
and will be able to give good automatic conflict resolution.
It has downsides he stated, and cannot guarantee 100% correct automatic
conflict
resolution in case of connection loss, but I am quite sure you are not
able to do
better, without loosing yourself in theory.
e.g. assume all clocks vary by no more than 30 seconds when
disconnected, you can
require manual (or rule based) resolution to all conflicts that vary by
less than
1 minute.
Andreas
---(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] [PATCHES] Fix database is ready race condition
On Sun, 2007-02-04 at 14:15 -0500, Tom Lane wrote: Markus Schiltknecht [EMAIL PROTECTED] writes: is there a good reason to print the database system is ready message in StartupXLOG() in xact.c? It has a) nothing to do with xlog and b) opens a small race condition: the message gets printed, while it still take some CPU cycles until the postmaster really gets the SIGCHLD signal and sets StartupPID = 0. If you (or rather: an automated test program) try to connect within this timespan, you get a database is starting up error, which clearly contradicts the is ready message. I don't think there's any compelling reason for having that log message in its current form. What about redefining it to mean postmaster is ready to accept connections --- either with that wording, or keeping the old wording? Then we could just put it in one place in postmaster.c and be done. I think your proposed patch is overcomplicated by trying to have it still come out in bootstrap/standalone cases. For a standalone backend, getting a prompt is what tells you it's ready ;-) I'm OK with moving the message to be executed from another place, but I have some comments on the changed wording. Firstly, Database system is great general wording. Postmaster only means something if you know the architecture of PostgreSQL, which most people don't. If we did change the wording, I'd want to have separate messages for the two events of - database can now accept connections - recovery is complete One of the TODO items is to allow the dbserver to be available for read-only queries while still recovering, so any change to the wording should be made with that in mind, so we don't need to change it too often. My suggestions would be 1. Database system has completed recovery and 2. Database system is ready to accept connections Currently those messages would occur in that order and be issued by StartupXLOG() for (1) and postmaster for (2). In the future they may be issued in a different order. If we stick with only a single message, we should keep it the same as now, wherever the code and whatever the exact timing of its execution. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SRF optimization question
On Sat, 2007-02-03 at 15:51 -0800, Jeremy Drake wrote: I am writing a set returning function in C. There are cases where I can know definitively, upfront, that this function will only return one row. I have noticed, through happenstance of partially converted function, that I can mark a normal, non-set returning function as returning SETOF something, while not utilizing the SRF macros and using PG_RETURN_DATUM, and it still works as returning one row. I am wondering, if it is an acceptable optimization that if I know up-front that a function will only return one row, to avoid all of the SRF overhead of setting up a new memory context, and a function context struct, and requiring an extra call to my function to tell Postgres that I am done sending rows, to simply not use the SRF stuff and interact with Postgres as though I was not returning SETOF? Is this a sane idea, or did I just stumble into an accidental feature when I changed my CREATE FUNCTION statement without changing my C code? Well, I'd say its either an SRF or its not. If you want to do select * from myfunc(), then it has to be an SRF. You *can* have a function that returns a composite type, but that is executed in a slightly different manner. e.g. select myfunc() from oneRowTable; Either way you have the overhead of the scan, so I see no optimization by trying to remove the SRF stuff. So I think you've found a minor bug, not a feature. But how do we check for SRF macros? With difficulty or overhead, one of the two. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compacting a relation
On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: vacuumlazy.c contains a hint Consider compacting this relation but AFAICT, there is no indication anywhere how compacting is supposed to be achieved. I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be processed effectively by a user. So change it ... New message is: errhint(Consider using VACUUM FULL on this relation or increasing the configuration parameter \max_fsm_pages\.))); The change of wording may be appropriate, but it is triggered when if (vacrelstats-tot_free_pages MaxFSMPages) So if you VACUUM a 15+GB table and it has only 1% freespace then it will still generate this message. Hopefully you'd agree that the message would be inappropriate in that case. It's also inappropriate because this message is generated *prior* to doing lazy_truncate_heap(), which could easily remove lots of empty pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so it can currently be triggered in wholly inappropriate situations. So I suggest that we move this wording after lazy_truncate_heap() in lazy_vacuum_rel() *and* we alter the hint so that it only suggests VACUUM FULL if the table has 20% fragmentation, whatever its size. Happy to drop a patch for this, if people agree. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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] [PATCHES] Fix database is ready race condition
Simon Riggs [EMAIL PROTECTED] writes: My suggestions would be 1. Database system has completed recovery and 2. Database system is ready to accept connections The second was in fact the wording I had in mind, sorry for not being clear. As to the first, the question is whether a log message at that specific point has any use. It's not completion of recovery, exactly, since we go through that spot whether it's a normal startup or recovery (and there is another log message when we actually do any WAL replay). AFAICS it's a fairly randomly chosen place in a long sequence of Things That Must Happen. Part of the reason Markus is seeing a race condition is that this isn't the last thing done before the startup subprocess exits --- see BootstrapMain. So I'm for just getting rid of it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VC2005 build and pthreads
Martijn van Oosterhout wrote: -- Start of PGP signed section. On Sun, Jan 28, 2007 at 02:05:41PM +0100, Magnus Hagander wrote: Anyway. We hard-code thread-safety to on for Win32, because win32 is a threaded platform in general - almost everything can be exposed to threading even if they don't want it. For example a VB program that has no threading at all can still run with multiple threads because of the framework. It'd be nice if we could do the same for some Unix platofrms like Linux. The C library uses threads internally, and there's no actual downside to enabling thread safety there, except removing a few failure modes. I was not aware this was true on Linux. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] \copy (query) delimiter syntax error
Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: The consequence will be, though, that psql will accept a syntax for \copy (query) ... that the corresponding backend command would reject were we not transforming it. That strikes me as potentially confusing. Perhaps. What about plan B: remove the legacy syntax support in \copy? IIRC it has not been documented since 7.2, so maybe we can finally throw it overboard. Thoughts? I like it for 8.3 - but maybe my present patch would be better for 8.2, as it involves less behaviour change. While we decide this issue, which can be worked around in any case, I am going to commit the part of the patch that nobody has objected to (and which will fix Michael's original complaint), on HEAD and 8.2 stable, so we can get some testing going. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] buildfarm fail cardinal
Hi, I would like to know why the test stats on pgbuildfarm/cardinal fails? Regards, Gevik xml ... ok test stats... FAILED test tablespace ... ok ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] buildfarm fail cardinal
Gevik Babakhani wrote: Hi, I would like to know why the test stats on pgbuildfarm/cardinal fails? Regards, Gevik xml ... ok test stats... FAILED test tablespace ... ok If you look in the log it tells you. This looks like pilot error. http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=cardinaldt=2007-02-05%2005:45:01 has these lines in the section with the postmaster log: LOG: could not resolve localhost: Name or service not known LOG: disabling statistics collector for lack of working socket cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] buildfarm fail cardinal
Gevik Babakhani [EMAIL PROTECTED] writes: I would like to know why the test stats on pgbuildfarm/cardinal fails? Looks like DNS and/or /etc/hosts misconfiguration to me: == pgsql.10164/src/test/regress/log/postmaster.log === LOG: could not resolve localhost: Name or service not known LOG: disabling statistics collector for lack of working socket regards, tom lane ---(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] Performance penalty of visibility info in indexes?
On Thu, Feb 01, 2007 at 11:57:41PM -0600, Jim Nasby wrote: Has anyone actually measured the performance overhead of storing visibility info in indexes? I know the space overhead sounds daunting, but even if it doubled the size of the index in many cases that'd still be a huge win over having to scan the heap as well as the index (esp. for things like count(*)). There would also be overhead from having to update the old index tuple, but for the case of updates you're likely to need that page for the new index tuple anyway. I thought the main problem was locking. If you change the visibility of an existing row, you have to update the index in a way that won't kill concurrant scans, either by returning the row twice, or skipping it. I think it hinges on what exactly falls under visibility info. Maybe with the page-at-a-time index scans, the problem is easier now. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] psql possible TODO
Bruce Momjian wrote: Added to TODO: o Add \# to list command history like \s, but with line numbers http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php Humm, this is not what we agreed. --- Joshua D. Drake wrote: Hello, O.k. this is my currently thinking: \# will list just like \s except it will include line numbers: 1 SELECT * FROM FOO; (not a tab of course) 2 UPDATE bar SET foo = 6; \# 2will execute query number 2 \#e 2 will open the numbered query in $EDITOR I would love to figure out a way to auto explain these queries without obnoxious syntax. Any ideas? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] VC2005 build and pthreads
Bruce Momjian [EMAIL PROTECTED] writes: Martijn van Oosterhout wrote: It'd be nice if we could do the same for some Unix platofrms like Linux. The C library uses threads internally, and there's no actual downside to enabling thread safety there, except removing a few failure modes. I was not aware this was true on Linux. It uses threads at least for the POSIX AIO calls--I'm not sure what else. -Doug ---(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] VC2005 build and pthreads
Douglas McNaught [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: Martijn van Oosterhout wrote: It'd be nice if we could do the same for some Unix platofrms like Linux. The C library uses threads internally, and there's no actual downside to enabling thread safety there, except removing a few failure modes. I was not aware this was true on Linux. It uses threads at least for the POSIX AIO calls--I'm not sure what else. I think the real point is that you get the same C library whether you ask for thread safety or not, and it does internal locking to protect itself against multi threads anyway. So arguably there's no point in building a thread-unsafe version of libpq. But having said that, 99.99% of Linux use is based on pre-built RPMs, and the RPM packagers all understand how to make this decision, so it's really not our problem to fix. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] VC2005 build and pthreads
Douglas McNaught [EMAIL PROTECTED] writes: It uses threads at least for the POSIX AIO calls--I'm not sure what else. On that tangent, is that still true or is it only for older kernels that it's true? I was under the impression newer kernels implemented the aio interface but others seem to think it's not the case and glibc still emulates it with threads. Looking up the truth of the situation has been on my mental todo list for a while. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Recursive query syntax ambiguity
On Mon, Jan 29, 2007 at 01:38:02PM +, Gregory Stark wrote: Instead I suggest we create one type of reentrant node, which memoizes its output. It would be a kind of on-demand Materialize. It would be paired with a second node type which would be the only type of node which can invoke it. This RecursiveCall node would invoke the Memoize node using a special interface in which it passes enough state for the Memoize node to seek to the correct place in its output. That I beleive is the right approach. I think an equivalent way of looking at it is a Loop node with an InitPlan and a StepPlan. Initially, the Loop node executes the InitPlan to get it's initial set of tuples, storing them like a Materialize node does. After that it keeps calling the StepNode, where somewhere inside the it has a node that extracts a row from the aforementioned tuplestore. It stores these returned tuples in the tuplestore also, thus giving you recursion. snip (I've convinced myself that that's true but I should probably work out a good proof of it before I make all this depend on it.) Yeah, proving it is going to be tricky, I'm not sure what the standard says about infinite recursion. There are three general cases of the Memoize node. Breadth-first, Depth-first, and non-linearly-recursive. I think the the only difference between depth and bredth-first searches is (if you consider the tuplesort to be a queue) whether the new tuples go to the front or the back of the list. But a data structures and algorithms book will know this. There are a few open issues to consider. Namely, how to cost a RecursiveCall node. One note: if you know that if you get p tuples out for every tuple in (where p1) then the asymptotic result of 1 + p + p*p+ ... is 1/(1-p) However, I don't know it matters. You only need to cost the plan if there are alternate paths and given the plan structure is strongly constrained, I'm not sure how much it matters. Also, if a subplan has exactly one call-site we really ought to inline it as it will get much more reliable plans. Similarly if there are two call sites we should consider inlining it if the cost of materializing the results (and reading them back) is more than n-call-sites x the cost of executing the query. I would expect That would happen with plain sequential scans for example. In the case where the subplan has side-effects, you can't optimise at all. In the case of read-committed mode, will two seq-scans always return the same result? Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Recursive query syntax ambiguity
Martijn van Oosterhout kleptog@svana.org writes: However, I don't know it matters. You only need to cost the plan if there are alternate paths and given the plan structure is strongly constrained, I'm not sure how much it matters. It does, since the whole thing could be a subquery, in which case there could be options available at the outer level. I doubt we'll be able to be really smart, but that doesn't mean we can just punt. In the case of read-committed mode, will two seq-scans always return the same result? They definitely should, since we'll be using the same snapshot throughout the query. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dead code in _bt_split?
Heikki Linnakangas wrote: Bruce Momjian wrote: Heikki, did this code cleanup get included in your recent btree split fix? No. OK, would you please send a patch to remove the unused code. Thanks. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Referential Integrity and SHARE locks
On Sat, 3 Feb 2007, Simon Riggs wrote: There are issues, yes. Dropping PKs is a very irregular occurrence nor is it likely to be part of a complex transaction. It wouldn't bother me to say that if a transaction already holds a RowExclusiveLock or a RowShareLock it cannot upgrade to an AccessExclusiveLock. Actually, since rearranging PKs is such a drastic change I would expect them only to be part of a large complex transaction. I know for apps I work on it would be part of a single transaction script that updated whole chunks of data and schema. Kris Jurka ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Configuring BLCKSZ and XLOGSEGSZ (in 8.3)
Patch already applied by Tom. Removed from queue. --- Simon Riggs wrote: On Tue, 2006-12-05 at 17:26 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-12-05 at 16:24 -0500, Tom Lane wrote: Sure, what would happen is that every backend passing through this code would execute the several lines of computation needed to decide whether to call RequestCheckpoint. Right, but the calculation uses RedoRecPtr, which may not be completely up to date. So presumably you want to re-read the shared memory value again to make sure we are exactly accurate and allow only one person to call checkpoint? Either way we have to take a lock. Insert lock causes deadlock, so we would need to use infolock. Not at all. It's highly unlikely that RedoRecPtr would be so out of date as to result in a false request for a checkpoint, and if it does, so what? Worst case is we perform an extra checkpoint. On its own, I wouldn't normally agree... Also, given the current structure of the routine, this is probably not the best place for that code at all --- it'd make more sense for it to be in the just-finished-a-segment code stretch, which would ensure that it's only done by one backend once per segment. But thats a much better plan since it requires no locking. There's a lot more changes there for such a simple fix though and lots more potential bugs, but I've coded it as you suggest and removed the fields from pg_control. Patch passes make check, applies cleanly on HEAD. pg_resetxlog and pgcontroldata tested. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] VC2005 build and pthreads
On Mon, Feb 05, 2007 at 11:09:06AM -0500, Tom Lane wrote: I think the real point is that you get the same C library whether you ask for thread safety or not, and it does internal locking to protect itself against multi threads anyway. So arguably there's no point in building a thread-unsafe version of libpq. The underlying issue is that there is no way to be sure a program will not have threads. Just because you didn't compile against pthreads, don't mean there won't be any threads. An example being a gethostbyname() that loads a threaded version of an LDAP library, for example. For programs it doesn't matter, but for shared-libraries you never know whether you're going to be called from the main thread of execution or not, and if you're not you're buggered. But having said that, 99.99% of Linux use is based on pre-built RPMs, and the RPM packagers all understand how to make this decision, so it's really not our problem to fix. That's true, but I think it would be worthwhile to invert the switch to be --disable-thread-safety, since the number of people who don't understand the problem far outweigh the cost of the switch. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] VC2005 build and pthreads
Martijn van Oosterhout kleptog@svana.org writes: That's true, but I think it would be worthwhile to invert the switch to be --disable-thread-safety, since the number of people who don't understand the problem far outweigh the cost of the switch. I'd vote against that unless it were done only for Linux, and personally I think a platform-dependent default for this might be unreasonably confusing. regards, tom lane ---(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] psql possible TODO
Alvaro Herrera wrote: Bruce Momjian wrote: Added to TODO: o Add \# to list command history like \s, but with line numbers http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php Humm, this is not what we agreed. Actually to be fair, there was no agreement. However, I do think it is not worthwhile to be a TODO (thank you anyway Bruce). I am not going to be spending my time on it and I doubt anyone else will. Joshua D. Drake --- Joshua D. Drake wrote: Hello, O.k. this is my currently thinking: \# will list just like \s except it will include line numbers: 1 SELECT * FROM FOO; (not a tab of course) 2 UPDATE bar SET foo = 6; \# 2will execute query number 2 \#e 2 will open the numbered query in $EDITOR I would love to figure out a way to auto explain these queries without obnoxious syntax. Any ideas? -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
On Fri, 2007-02-02 at 15:11 -0500, Tom Lane wrote: 2. Fix TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET to be correctly calculated (properly allowing for line pointers) and to be MAXALIGN multiples. The threshold value should be exactly the size of the largest tuple that you can put four of onto one page. Fix TOAST_MAX_CHUNK_SIZE so that it is *not* necessarily a MAXALIGN multiple, but rather causes the total length of a toast tuple to come out that way. This guarantees minimum space wastage on toast pages. Jan suggested to me a while back that having a configurable toast threshold would be a useful thing, when that table is also updated reasonably frequently. ISTM like a good idea, so a prototype has been written - nothing to do with Pavan's comments though. As you might expect, it does help in cases where we would otherwise produce lots of UPDATEd versions of a 1000 byte row, as well as on MIS queries that often don't pay much attention to text strings. This then allows the user some control over how much data gets toasted out of the main row. Many applications have long text fields of 100s of characters, for example a customer's stated, cleaned and previous addresses might together be VARCHAR(750), yet we may also want to UPDATE them regularly to store their current_balance. TOAST_MAX_CHUNK_SIZE can be fixed, though TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET could be settable for a table using a WITH parameter. It would seem like overkill to allow the threshold and target to differ when setting the parameter. If configurable, only MAXALIGNed values would be allowed. Sounds like a good time to suggest making these values configurable, within certain reasonable bounds to avoid bad behaviour. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql possible TODO
Alvaro Herrera wrote: Bruce Momjian wrote: Added to TODO: o Add \# to list command history like \s, but with line numbers http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php Humm, this is not what we agreed. Are you saying the URL is wrong or the description? I have updated the item description to: o Add \# to list and execute command history -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] psql possible TODO
Joshua D. Drake wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Added to TODO: o Add \# to list command history like \s, but with line numbers http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php Humm, this is not what we agreed. Actually to be fair, there was no agreement. However, I do think it is not worthwhile to be a TODO (thank you anyway Bruce). I am not going to be spending my time on it and I doubt anyone else will. Really, I thought there were a number of people who liked it. New text is: o Add \# to list and execute command history Are you sure you want it removed? --- Joshua D. Drake --- Joshua D. Drake wrote: Hello, O.k. this is my currently thinking: \#will list just like \s except it will include line numbers: 1 SELECT * FROM FOO; (not a tab of course) 2 UPDATE bar SET foo = 6; \# 2 will execute query number 2 \#e 2 will open the numbered query in $EDITOR I would love to figure out a way to auto explain these queries without obnoxious syntax. Any ideas? -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
Simon Riggs [EMAIL PROTECTED] writes: Sounds like a good time to suggest making these values configurable, within certain reasonable bounds to avoid bad behaviour. Actually, given what we've just learned --- namely that choosing these values at random is a bad idea --- I'd want to see a whole lot of positive evidence before adding such a configuration knob. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Referential Integrity and SHARE locks
On Sun, 2007-02-04 at 09:38 +, Simon Riggs wrote: The TODO I was requesting you consider was this: Develop non-conflicting locking scheme to allow RI checks to co-exist peacefully with non-PK UPDATEs on the referenced table. That is, IMHO, a general statement of an important unresolved issue with our Referential Integrity implementation. That is in no way intended as any form of negative commentary on the excellent detailed work that has got us so far already. Well, if we really want to solve that completely then we really need column locking, or at least locking at the level of arbitrary (possibly overlapping) unique constraints, not just the PK because foreign keys don't necessarily reference the primary key. But the PK case is certainly the most common and it'd certainly be nice to cover that case. ... It occurs to me that if we had visibility in unique indexes, this would allow the index rows to be separately lockable to the main row. That's exactly what we need here. I've implemented a work-around using this principle, utilising RULEs and a duplicated PK column-only table. This still allows FK checks to work correctly, yet doesn't require the backend hack Csaba mentioned. My feeling is that more work in this area is required, even if we can't yet agree a TODO item. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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] psql possible TODO
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Added to TODO: o Add \# to list command history like \s, but with line numbers http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php Humm, this is not what we agreed. Are you saying the URL is wrong or the description? I have updated the item description to: o Add \# to list and execute command history I guess what I am saying is that I asked you two times to add the TODO entry that was agreed and you ignored me. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Compacting a relation
Simon Riggs wrote: On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: vacuumlazy.c contains a hint Consider compacting this relation but AFAICT, there is no indication anywhere how compacting is supposed to be achieved. I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be processed effectively by a user. So change it ... New message is: errhint(Consider using VACUUM FULL on this relation or increasing the configuration parameter \max_fsm_pages\.))); The change of wording may be appropriate, but it is triggered when if (vacrelstats-tot_free_pages MaxFSMPages) So if you VACUUM a 15+GB table and it has only 1% freespace then it will still generate this message. Hopefully you'd agree that the message would be inappropriate in that case. Interesting. So if you have 1% free on a 15GB table, and that doesn't fit into the free space, we emit the message. I would think the hint is accurate, though. Are you saying they should increase FSM and not do VACUUM FULL in those cases? Should we recommend the fsm increase before the VACUUM FULL? It's also inappropriate because this message is generated *prior* to doing lazy_truncate_heap(), which could easily remove lots of empty pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so it can currently be triggered in wholly inappropriate situations. Yes, we should move the test if there is a better place. So I suggest that we move this wording after lazy_truncate_heap() in lazy_vacuum_rel() *and* we alter the hint so that it only suggests VACUUM FULL if the table has 20% fragmentation, whatever its size. Interesting. OK, so we have two message, one recommends both, and the other just FSM increase. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] psql possible TODO
Alvaro Herrera wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Added to TODO: o Add \# to list command history like \s, but with line numbers http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php Humm, this is not what we agreed. Are you saying the URL is wrong or the description? I have updated the item description to: o Add \# to list and execute command history I guess what I am saying is that I asked you two times to add the TODO entry that was agreed and you ignored me. I did not ignore you --- I replied I was getting to it, and now you are not telling me what the TODO wording should be, and now Joshua Drake doesn't want it. I see this email you sent me: - Allow psql to display item numbers along each history item, depending on a \set variable So that is what we agreed to? OK, old item removed, and this added: o Add a \set variable to control whether \s displays line numbers Also, Alvaro, feel free to add TODO items yourself. There is nothing special about me adding a TODO item except that I update the HTML, but if I see a commit to TODO, I update TODO.html. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] psql possible TODO
I am not going to be spending my time on it and I doubt anyone else will. Really, I thought there were a number of people who liked it. New text is: o Add \# to list and execute command history Are you sure you want it removed? Well let me put it this way. I think my idea was good, I came up with it ;). There are others that felt it was good to. On reviewing the thread, you are correct and it should probably remain a TODO. I just won't get to it before feature freeze. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql possible TODO
Joshua D. Drake wrote: I am not going to be spending my time on it and I doubt anyone else will. Really, I thought there were a number of people who liked it. New text is: o Add \# to list and execute command history Are you sure you want it removed? Well let me put it this way. I think my idea was good, I came up with it ;). There are others that felt it was good to. On reviewing the thread, you are correct and it should probably remain a TODO. I just won't get to it before feature freeze. OK, merged TODO: o Add a \set variable to control whether \s displays line numbers Another option is to add \# which lists line numbers, and allows command execution. http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Modifying and solidifying contrib
Jim Nasby wrote: There was also mention of having a means to tell pg_dump not to dump extensions... What's the use case for that? What will we do if there are db objects that depend on some extensions? Given that there will be some uninstall support, this one seems less necessary. I really think we should approach this by not trying to do everything at once. cheers andrew ---(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] Proposed adjustments in MaxTupleSize andtoastthresholds
On Mon, 2007-02-05 at 11:52 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Sounds like a good time to suggest making these values configurable, within certain reasonable bounds to avoid bad behaviour. Actually, given what we've just learned --- namely that choosing these values at random is a bad idea --- I'd want to see a whole lot of positive evidence before adding such a configuration knob. Sure. My understanding of the process we'd like to follow on this sort of thing is: 1. make proposal, test for unseen negative effects or basic rejections 2. code performance prototype 3. assemble performance evidence 4. debate utility 5. complete coding 6. further review Step 3 is always there for performance work, so even if you don't mention it, I'll assume everybody wants to see that as soon as possible before we progress. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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] VC2005 build and pthreads
On Mon, Feb 05, 2007 at 11:34:23AM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: That's true, but I think it would be worthwhile to invert the switch to be --disable-thread-safety, since the number of people who don't understand the problem far outweigh the cost of the switch. I'd vote against that unless it were done only for Linux, and personally I think a platform-dependent default for this might be unreasonably confusing. Well, it's already platform specific, Win32 removes the option altogether (apparently). Which would be another option. However, it only trips people up occasionally, so I'm not going to push it. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Modifying and solidifying contrib
On Mon, Feb 05, 2007 at 12:19:51PM -0500, Andrew Dunstan wrote: Jim Nasby wrote: There was also mention of having a means to tell pg_dump not to dump extensions... What's the use case for that? What will we do if there are db objects that depend on some extensions? Given that there will be some uninstall support, this one seems less necessary. Well, the use case is someone using tsearch2 on version A and wants to a do a dump to restore into later version B. It would be helpful if pg_dump compacted the whole tsearch2 infrastrcutre into a single INSTALL tsearch2 command. Obviously, the tsearch2 uninstall script for version B isn't going to work properly for a database restore from version A. And this way a dump/restore will pickup any new features added in the later version. I really think we should approach this by not trying to do everything at once. That's true, but it's something to keep in mind. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Modifying and solidifying contrib
Martijn van Oosterhout wrote: On Mon, Feb 05, 2007 at 12:19:51PM -0500, Andrew Dunstan wrote: Jim Nasby wrote: There was also mention of having a means to tell pg_dump not to dump extensions... What's the use case for that? What will we do if there are db objects that depend on some extensions? Given that there will be some uninstall support, this one seems less necessary. Well, the use case is someone using tsearch2 on version A and wants to a do a dump to restore into later version B. It would be helpful if pg_dump compacted the whole tsearch2 infrastrcutre into a single INSTALL tsearch2 command. Obviously, the tsearch2 uninstall script for version B isn't going to work properly for a database restore from version A. And this way a dump/restore will pickup any new features added in the later version. And if there's an API change everything will blow up. I would suggest we start with what is (I think) simplest and clearest: . catalog support via a simple extension-schema(s) map . initdb installs standard extensions if it finds them, unless told not to . support for adjusting search path. If that gets done nicely for 8.3 we'll be doing well. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VC2005 build and pthreads
Martijn van Oosterhout wrote: On Mon, Feb 05, 2007 at 11:34:23AM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: That's true, but I think it would be worthwhile to invert the switch to be --disable-thread-safety, since the number of people who don't understand the problem far outweigh the cost of the switch. I'd vote against that unless it were done only for Linux, and personally I think a platform-dependent default for this might be unreasonably confusing. Well, it's already platform specific, Win32 removes the option altogether (apparently). Which would be another option. Visual C build only, not the MingW build. It still has the option. The win32 binary distribution sets it though. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying and solidifying contrib
On 2/5/07, Andrew Dunstan [EMAIL PROTECTED] wrote: [...] I would suggest we start with what is (I think) simplest and clearest: . catalog support via a simple extension-schema(s) map . initdb installs standard extensions if it finds them, unless told not to . support for adjusting search path. Why adjusting search_path is needed at all? -- Best regards, Nikolay ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] 10 weeks to feature freeze (Pending Work)
On Jan 26, 2:38 pm, [EMAIL PROTECTED] (Tom Lane) wrote: Rick Gigger [EMAIL PROTECTED] writes: I thought that the following todo item just barely missed 8.2: Allow a warm standby system to also allow read-only statements [pitr] No, it's a someday-wishlist item; the work involved is not small. Slony1 has supported log-shipping replication for about a year now. It provides similar functionality. Andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Referential Integrity and SHARE locks
Simon Riggs wrote: It occurs to me that if we had visibility in unique indexes, this would allow the index rows to be separately lockable to the main row. That's exactly what we need here. I've implemented a work-around using this principle, utilising RULEs and a duplicated PK column-only table. This still allows FK checks to work correctly, yet doesn't require the backend hack Csaba mentioned. My feeling is that more work in this area is required, even if we can't yet agree a TODO item. OK, please propose some wording so at least we can get agreement on that. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add documentation for Windows on how to set an environment
Magnus Hagander wrote: Bruce Momjian wrote: Log Message: --- Add documentation for Windows on how to set an environment variable. Backpatch to 8.2.X. Modified Files: -- pgsql/doc/src/sgml: libpq.sgml (r1.224 - r1.225) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/libpq.sgml.diff?r1=1.224r2=1.225) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster The exact naming and placing of the buttons you click is version dependent. For example, if you use Classic Folders, it's not there. For that one, the common ground is to right-click and select properties instead, works for both. And the dialogs look completely different between Win2000 and XP/2003. Luckily in this case the button is named the same, but the placement is completely different. I don't have a Vista around right now, but given how much else they've changed, it wouldn't surprise me if this has also changed... OK, text trimmed down to a hint: Environment variables on Windows are set as a property of literalMy Computer/. Bottom line: do we really want to document for people how to use Windows? I don't see us documenting how to set an environment variable in Unix... And *if* we want to do it, might it live better in the Windows FAQ? I think environment variables are used rarely enough on Win32 that we should supply a hint. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] libpq docs about PQfreemem
Magnus Hagander wrote: have this about PQfreemem(): Frees memory allocated by applicationlibpq/, particularly functionPQescapeByteaConn/function, functionPQescapeBytea/function, functionPQunescapeBytea/function, and functionPQnotifies/function. It is needed by Microsoft Windows, which cannot free memory across DLLs, unless multithreaded DLLs (option/MD/option in VC6) are used. On other platforms, this function is the same as the standard library function functionfree()/. /para That's also a very old comment, dating back to when we could build libpq with VC6 only and nothing else. Now we can build with MinGW, Borland, VC2005 and I think also VC2003. Which would add the note that this is also depending on *which compiler* and *which compiler version*. The /MD mention is just one of several different options to select the runtime libraries, so it seems really misplaced. Now, there are two options for this. Either we fix it (I can put together a patch), or we remove it altogether. To me, it seems to be just an implementation detail and some kind of explanation why we're doing it - which would live better in a source code comment than in the docs. This includes the part about how it's just the same as free() on other platforms. That's just an implementation detail, and I assume we don't want people to rely on that - in case we ever want to change it in the future for some reason. (the doc for the other functions say you have to use PQfreemem without mentioning any exceptions) Thoughts? Rip out or update? Are you saying that almost all Win32 binaries and libraries now can free across DLLs? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Re: [COMMITTERS] pgsql: Add documentation for Windows on how to set an environment
Bruce Momjian wrote: OK, text trimmed down to a hint: Environment variables on Windows are set as a property of literalMy Computer/. Bottom line: do we really want to document for people how to use Windows? I don't see us documenting how to set an environment variable in Unix... And *if* we want to do it, might it live better in the Windows FAQ? I think environment variables are used rarely enough on Win32 that we should supply a hint. I think every Windows administrator who is not totally clueless knows how to set the environment. Maybe home users don't use it much, but admins certainly need to know about it. cheers andrew ---(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] Proposal: Commit timestamp
On Sun, Feb 04, 2007 at 01:36:03PM -0500, Jan Wieck wrote: For the fourth time, the clock is in the mix to allow to continue during a network outage. All your arguments seem to assume 100% network uptime. There will be no clusterwide clock or clusterwide increment when you lose connection. How does your idea cope with that? I'm wondering whether a combined approach is needed. This makes things more complicated, but what if you somehow co-ordinate local counters with shared clock ticks? When you get a failure on your talk to the shared clock, you regard yourself as in some sort of failure (you're going to need softfails and that sort of thing, and yes, I'm flapping my hands in the air at the moment). At rejoin to the cluster, you need some sort of way to publish here's the counter and the last global time I had and here's my current counter. You can publish local time with this too, I guess, to solve for conflict cases, but that seems like the sort of decision that needs to be pushed down to policy level. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(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] Re: [COMMITTERS] pgsql: Add documentation for Windows on how to set an environment
Andrew Dunstan [EMAIL PROTECTED] writes: Bruce Momjian wrote: I think environment variables are used rarely enough on Win32 that we should supply a hint. I think every Windows administrator who is not totally clueless knows how to set the environment. Maybe home users don't use it much, but admins certainly need to know about it. Another argument against it is that the libpq documentation is an entirely random place to discuss it, as libpq is hardly the only part of Postgres that responds to environment variables. I liked the idea of mentioning it in the Windows FAQ, instead. regards, tom lane ---(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] Re: [COMMITTERS] pgsql: Add documentation for Windows on how to set an environment
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Bruce Momjian wrote: I think environment variables are used rarely enough on Win32 that we should supply a hint. I think every Windows administrator who is not totally clueless knows how to set the environment. Maybe home users don't use it much, but admins certainly need to know about it. Another argument against it is that the libpq documentation is an entirely random place to discuss it, as libpq is hardly the only part of Postgres that responds to environment variables. I liked the idea of mentioning it in the Windows FAQ, instead. OK, sure. Magnus. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Referential Integrity and SHARE locks
On Mon, 5 Feb 2007, Simon Riggs wrote: On Sun, 2007-02-04 at 09:38 +, Simon Riggs wrote: The TODO I was requesting you consider was this: Develop non-conflicting locking scheme to allow RI checks to co-exist peacefully with non-PK UPDATEs on the referenced table. That is, IMHO, a general statement of an important unresolved issue with our Referential Integrity implementation. That is in no way intended as any form of negative commentary on the excellent detailed work that has got us so far already. Well, if we really want to solve that completely then we really need column locking, or at least locking at the level of arbitrary (possibly overlapping) unique constraints, not just the PK because foreign keys don't necessarily reference the primary key. But the PK case is certainly the most common and it'd certainly be nice to cover that case. ... It occurs to me that if we had visibility in unique indexes, this would allow the index rows to be separately lockable to the main row. That's exactly what we need here. I've implemented a work-around using this principle, utilising RULEs and a duplicated PK column-only table. This still allows FK checks to work correctly, yet doesn't require the backend hack Csaba mentioned. My feeling is that more work in this area is required, even if we can't yet agree a TODO item. I actually like the general idea your TODO item had, although I would say non-referenced column update rather than non-PK update. Even if we put it far out due to questions about what would be acceptable implementation. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Referential Integrity and SHARE locks
Bruce Momjian [EMAIL PROTECTED] writes: OK, please propose some wording so at least we can get agreement on that. How about something open-ended like arrange for updates that do not update columns referenced by foreign keys from other tables to avoid being blocked by locks from concurrent RI checks -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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] Referential Integrity and SHARE locks
Gregory Stark [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: OK, please propose some wording so at least we can get agreement on that. How about something open-ended like arrange for updates that do not update columns referenced by foreign keys from other tables to avoid being blocked by locks from concurrent RI checks Hum. Reading back in the thread it seems what I wrote is basically equivalent to the wording Simon originally proposed. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Logging functions executed by queries in 8.2?
Hackers, In recent versions, we've changed the logging of function executions so that only the function call is logged, and not any of the queries which it may execute internally. While most of the time this method is superior for performance analysis, in applications with extensive multi-line stored procedures sometimes you want to log each individual query. While it's kind of possible to do this via RAISE NOTICE, that doesn't accurately get you execution times, let alone allow you do to thinks like log query plans.This is seriously hampering our ability to performance-optimize for TPCE. Questions: 1) Is there any workaround for 8.2 which would allow us to log the function queries and plans? 2) Would it be reasonable to add a log_function_bodies option for 8.3? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
On 2/5/2007 11:52 AM, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Sounds like a good time to suggest making these values configurable, within certain reasonable bounds to avoid bad behaviour. Actually, given what we've just learned --- namely that choosing these values at random is a bad idea --- I'd want to see a whole lot of positive evidence before adding such a configuration knob. Some of the evidence is TOAST itself. Every time you do not SET a column that has been toasted into external storage during an UPDATE, you win because the columns data isn't read during the scan for the row to update, it isn't read during heap_update(), it isn't actually updated at all (the toast reference is copied as is and the external value reused), and not a single byte of the external data is bloating WAL. If someone knows that 99% of their updates will not hit certain text columns in their tables, actually forcing them to be compressed no matter what and to be stored external if they exceed 100 bytes will be a win. Of course, this is a bit different from Simon's approach. What I describe here is a per pg_attribute configuration to enforce a certain new toaster behavior. Since we already have something that gives the toaster a per column cluestick (like not to bother trying to compress), it might be much easier to implement then Simon's proposal. It would require that the toaster goes over the initial heap tuple for those specially configured columns even if the tuple is below the toast threshold, which suggests that a pg_class.relhasspecialtoastneeds could be useful. But I think as for fine tuning capabilities, a column insensitive maximum tuple size is insufficient anyway. 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Logging functions executed by queries in 8.2?
Hackers, In recent versions, we've changed the logging of function executions so that only the function call is logged, and not any of the queries which it may execute internally. While most of the time this method is superior for performance analysis, in applications with extensive multi-line stored procedures sometimes you want to log each individual query. Actually, it's even more twisted now; if I turn on debug_print_plan, I can get the *plan* for queries executed by a function, but I can't get their durations. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Logging functions executed by queries in 8.2?
Josh Berkus josh@agliodbs.com writes: In recent versions, we've changed the logging of function executions so that only the function call is logged, and not any of the queries which it may execute internally. While most of the time this method is superior for performance analysis, in applications with extensive multi-line stored procedures sometimes you want to log each individual query. ISTM that the wave of the future for this is an instrumentation plug-in, not further kluging of the query logging functionality. I had the impression that Korry and EDB had some prototype capability in that direction already, and surely it shouldn't be that hard to write if not. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposed adjustments in MaxTupleSize andtoastthresholds
Simon Riggs [EMAIL PROTECTED] wrote: Actually, given what we've just learned --- namely that choosing these values at random is a bad idea --- I'd want to see a whole lot of positive evidence before adding such a configuration knob. 3. assemble performance evidence Step 3 is always there for performance work, so even if you don't mention it, I'll assume everybody wants to see that as soon as possible before we progress. There was a performance evidence using TOAST in order to partial updates. It added a flag of force toasting. The toast threshold suggested now is more flexible than it, but I think it is one of the evidences. Vertical Partitioning with TOAST http://archives.postgresql.org/pgsql-hackers/2005-12/msg00013.php Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] period data type
Hello, Is anyone working on a period data type as described in Dr. Richard Snodgrass' book _Developing Time-Oriented Database Applications in SQL_[1]? I did not see a relevant project listed in the TODO. I would like to contribute (possible funding and/or coding) the development of a conforming implementation of this data type. [1]http://www.cs.arizona.edu/~rts/tdbbook.pdf Thanks, wt -- Warren Turkal (w00t) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Dirty pages in freelist cause WAL stuck
I think there's improvement to be made in how we track buffer usage in general. Seqscans still hold the same weight as any other operation, the freelist is of questionable value, and there's a lot of work done to find a free buffer out of the pool, for example. On Feb 2, 2007, at 8:08 PM, Bruce Momjian wrote: Is this a TODO item? -- - ITAGAKI Takahiro wrote: Simon Riggs [EMAIL PROTECTED] wrote: I think what you are saying is: VACUUM places blocks so that they are immediately reused. This stops shared_buffers from being polluted by vacuumed-blocks, but it also means that almost every write becomes a backend dirty write when VACUUM is working, bgwriter or not. That also means that we flush WAL more often than we otherwise would. That's right. I think it's acceptable that vacuuming process writes dirty buffers made by itself, because only the process slows down; other backends can run undisturbedly. However, frequent WAL flushing should be avoided. I found the problem when I ran VACUUM FREEZE separately. But if there were some backends, dirty buffers made by VACUUM would be reused by those backends, not by the vacuuming process. From above my thinking would be to have a more general implementation: Each backend keeps a list of cache buffers to reuse in its local loop, rather than using the freelist as a global list. That way the technique would work even when we have multiple Vacuums working concurrently. It would also then be possible to use this for the SeqScan case as well. Great idea! The troubles are in the usage of buffers by SeqScan and VACUUM. The former uses too many buffers and the latter uses too few buffers. Your cache-looping will work around both cases. Another connected thought is the idea of a having a FullBufferList - the opposite of a free buffer list. When VACUUM/INSERT/COPY fills a block we notify the buffer manager that this block needs writing ahead of other buffers, so that the bgwriter can work more effectively. That seems like it would help with both this current patch and the additional thoughts above. Do you mean that bgwriter should take care of buffers in freelist, not only ones in the tail of LRU? We might need activity control of bgwriter. Buffers are reused rapidly in VACUUM or bulk insert, so bgwriter is not sufficient if its settings are same as usual. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
On Feb 5, 2007, at 10:45 AM, Simon Riggs wrote: Jan suggested to me a while back that having a configurable toast threshold would be a useful thing, when that table is also updated reasonably frequently. While we're in there it probably makes sense to allow a configurable value for when to compress as well. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Patch queue
On 1/30/07, Bruce Momjian [EMAIL PROTECTED] wrote: FYI, I have been working all January to process 8.3 held patches/ideas, plus process the items arriving during the month. While I have been able to make some progress, there are still a significant number of items for me to address. I will keep working on it and try to complete it by mid-February. i think this does not belong to any queue ;) http://momjian.us/mhonarc/patches/msg6.html at http://momjian.postgresql.org/cgi-bin/pgpatches -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Patch queue
Jaime Casanova wrote: On 1/30/07, Bruce Momjian [EMAIL PROTECTED] wrote: FYI, I have been working all January to process 8.3 held patches/ideas, plus process the items arriving during the month. While I have been able to make some progress, there are still a significant number of items for me to address. I will keep working on it and try to complete it by mid-February. i think this does not belong to any queue ;) http://momjian.us/mhonarc/patches/msg6.html at http://momjian.postgresql.org/cgi-bin/pgpatches Wow, good one. I was keeping that for posterity, and put it in the wrong file. Thanks. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] period data type
Warren, Is anyone working on a period data type as described in Dr. Richard Snodgrass' book _Developing Time-Oriented Database Applications in SQL_[1]? I did not see a relevant project listed in the TODO. I would like to contribute (possible funding and/or coding) the development of a conforming implementation of this data type. You should start with a basic spec of what you want to do in plain-text e-mail format. It should answer the following questions: a) What will be the functionality of the new type? b) What need does it fill that current TIMESTAMP and INTERVAL do not? c) Provide some sample syntax and queries which would show off the new type. d) What dependencies would the new type create? Index types? Syntax extensions? Libraries? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Commit timestamp
Hi, Theo Schlossnagle wrote: On Feb 4, 2007, at 1:36 PM, Jan Wieck wrote: Obviously the counters will immediately drift apart based on the transaction load of the nodes as soon as the network goes down. And in order to avoid this clock confusion and wrong expectation, you'd rather have a system with such a simple, non-clock based counter and accept that it starts behaving totally wonky when the cluster reconnects after a network outage? I rather confuse a few people than having a last update wins conflict resolution that basically rolls dice to determine last. If your cluster partition and you have hours of independent action and upon merge you apply a conflict resolution algorithm that has enormous effect undoing portions of the last several hours of work on the nodes, you wouldn't call that wonky? You are talking about different things. Async replication, as Jan is planning to do, is per se wonky, because you have to cope with conflicts by definition. And you have to resolve them by late-aborting a transaction (i.e. after a commit). Or put it another way: async MM replication means continuing in disconnected mode (w/o quorum or some such) and trying to reconciliate later on. It should not matter if the delay is just some milliseconds of network latency or three days (except of course that you probably have more data to reconciliate). For sane disconnected (or more generally, partitioned) operation in multi-master environments, a quorum for the dataset must be established. Now, one can consider the database to be the dataset. So, on network partitions those in the quorum are allowed to progress with data modification and others only read. You can do this to *prevent* conflicts, but that clearly belongs to the world of sync replication. I'm doing this in Postgres-R: in case of network partitioning, only a primary partition may continue to process writing transactions. For async replication, it does not make sense to prevent conflicts when disconnected. Async is meant to cope with conflicts. So as to be independent of network latency. However, there is no reason why the dataset _must_ be the database and that multiple datasets _must_ share the same quorum algorithm. You could easily classify certain tables or schema or partitions into a specific dataset and apply a suitable quorum algorithm to that and a different quorum algorithm to other disjoint data sets. I call that partitioning (among nodes). And it's applicable to sync as well as async replication, while it makes more sense in sync replication. What I'm more concerned about, with Jan's proposal, is the assumption that you always want to resolve conflicts by time (except for balances, for which we don't have much information, yet). I'd rather say that time does not matter much if your nodes are disconnected. And (especially in async replication) you should prevent your clients from committing to one node and then reading from another, expecting to find your data there. So why resolve by time? It only makes the user think you could guarantee that order, but you certainly cannot. Regards Markus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
