Re: [HACKERS] Proposal: Commit timestamp
Jan Wieck wrote: This is all that is needed for last update wins resolution. And as said before, the only reason the clock is involved in this is so that nodes can continue autonomously when they lose connection without conflict resolution going crazy later on, which it would do if they were simple counters. It doesn't require microsecond synchronized clocks and the system clock isn't just used as a Lamport timestamp. Earlier you said that one assumption is that all servers in the multimaster cluster are ntp synchronized, which already rung the alarm bells in me. Now that I read this you appear to require synchronization not on the microsecond level but on some level. I think that would be pretty hard to manage for an administrator, seeing that NTP typically cannot provide such guarantees. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Dead code in _bt_split?
Bruce Momjian wrote: Heikki, did this code cleanup get included in your recent btree split fix? No. --- Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: In that case, newitemleft would be false, right? I'm saying the piece marked with X below is unreachable: Oh, I see. Hmm ... probably so, I think that chunk of code was just copied and pasted from where it occurs within the loop. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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
On Sat, 2007-02-03 at 09:43 -0800, Stephan Szabo wrote: On Sat, 3 Feb 2007, Simon Riggs wrote: On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote: No, I don't. I think knowledge of which columns are in a PK is quite a few levels away from the semantics of row locking. To point out just one problem, what happens when you add or drop a PK? Or drop and replace with a different column set? Yes, I know dropping one requires exclusive lock on the table, but the transaction doing it could hold row locks within the table, and now it's very unclear what they mean. 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. The lock check seems like a strange constraint, given that it's not necessarily going to be anything that conflicts with the row locks. I'm not sure there'd be a better idea given this sort of scheme, but it still seems strange. 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. IMHO generic column level locking would hardly ever be used. Locking for RI seems to be 99% of the use case, which means we'd be OK if we found a way of only locking an arbitary number of unique col groups. By definition, each of these column groups is covered by a unique index. 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. It also occurs to me that putting visibility in indexes doesn't prevent us from optimizing away index inserts for UPDATEs. There is no particular reason why the xmin and xmax of a unique index exactly matches the xmin and xmax of the main row. [I said the opposite to Jim Nasby a few days ago, regrettably]. The indexes would record the xmin and xmax of the row, while the main heap would have the xmin and xmax of the individual row versions. If we did both HOT + visibility in unique indexes then we would be able to eliminate the contention between INSERTs and UPDATEs with RI. As Tom pointed out this would complicate deadlock detection, but then so would any column-level locking scheme, so that isn't an argument against any one in particular. -- 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
[HACKERS] Re: [COMMITTERS] pgsql: Add documentation for Windows on how to set an environment
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... 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? //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove log segment and log_id fields from pg_controldata
On Sat, 2007-02-03 at 20:37 -0500, Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. Tom applied the patch a few months ago. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] libpq docs about PQfreemem
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? //Magnus ---(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
Magnus Hagander wrote: Bottom line: do we really want to document for people how to use Windows? No, we don't. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq docs about PQfreemem
Magnus Hagander wrote: 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. Old code assumes you can use free() to free all of these things, so it seems reasonable to give some background about why that is not the best method anymore. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] libpq docs about PQfreemem
Peter Eisentraut wrote: Magnus Hagander wrote: 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. Old code assumes you can use free() to free all of these things, so it seems reasonable to give some background about why that is not the best method anymore. Ok. So you say update it to be worded to cover the correct info about win32? Or should we change it into something that says previously you could use free(), but for portability reasons...etc? //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: Commit timestamp
On 2/4/2007 3:16 AM, Peter Eisentraut wrote: Jan Wieck wrote: This is all that is needed for last update wins resolution. And as said before, the only reason the clock is involved in this is so that nodes can continue autonomously when they lose connection without conflict resolution going crazy later on, which it would do if they were simple counters. It doesn't require microsecond synchronized clocks and the system clock isn't just used as a Lamport timestamp. Earlier you said that one assumption is that all servers in the multimaster cluster are ntp synchronized, which already rung the alarm bells in me. Now that I read this you appear to require synchronization not on the microsecond level but on some level. I think that would be pretty hard to manage for an administrator, seeing that NTP typically cannot provide such guarantees. Synchronization to some degree is wanted to avoid totally unexpected behavior. The conflict resolution algorithm itself can perfectly fine live with counters, but I guess you wouldn't want the result of it. If you update a record on one node, then 10 minutes later you update the same record on another node. Unfortunately, the nodes had no communication and because the first node is much busier, its counter is way advanced ... this would mean the 10 minutes later update would get lost in the conflict resolution when the nodes reestablish communication. They would have the same data at the end, just not what any sane person would expect. This behavior will kick in whenever the cross node conflicting updates happen close enough so that the time difference between the clocks can affect it. So if you update the logical same row on two nodes within a tenth of a second, and the clocks are more than that apart, the conflict resolution can result in the older row to survive. Clock synchronization is simply used to minimize this. The system clock is used only to keep the counters somewhat synchronized in the case of connection loss to retain some degree of last update meaning. Without that, continuing autonomously during a network outage is just not practical. 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: Commit timestamp
On Feb 4, 2007, at 10:06 AM, Jan Wieck wrote:
On 2/4/2007 3:16 AM, Peter Eisentraut wrote:
Jan Wieck wrote:
This is all that is needed for last update wins resolution. And as
said before, the only reason the clock is involved in this is so
that
nodes can continue autonomously when they lose connection without
conflict resolution going crazy later on, which it would do if they
were simple counters. It doesn't require microsecond synchronized
clocks and the system clock isn't just used as a Lamport timestamp.
Earlier you said that one assumption is that all servers in the
multimaster cluster are ntp synchronized, which already rung the
alarm bells in me. Now that I read this you appear to require
synchronization not on the microsecond level but on some level. I
think that would be pretty hard to manage for an administrator,
seeing that NTP typically cannot provide such guarantees.
Synchronization to some degree is wanted to avoid totally
unexpected behavior. The conflict resolution algorithm itself can
perfectly fine live with counters, but I guess you wouldn't want
the result of it. If you update a record on one node, then 10
minutes later you update the same record on another node.
Unfortunately, the nodes had no communication and because the first
node is much busier, its counter is way advanced ... this would
mean the 10 minutes later update would get lost in the conflict
resolution when the nodes reestablish communication. They would
have the same data at the end, just not what any sane person would
expect.
This behavior will kick in whenever the cross node conflicting
updates happen close enough so that the time difference between the
clocks can affect it. So if you update the logical same row on two
nodes within a tenth of a second, and the clocks are more than that
apart, the conflict resolution can result in the older row to
survive. Clock synchronization is simply used to minimize this.
The system clock is used only to keep the counters somewhat
synchronized in the case of connection loss to retain some degree
of last update meaning. Without that, continuing autonomously
during a network outage is just not practical.
A Lamport clock addresses this. It relies on a cluster-wise clock
tick. While it could be based on the system clock, it would not be
based on more than one clock. The point of the lamport clock is that
there is _a_ clock, not multiple ones.
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).
As the clock must be incremented clusterwide, the need for it to be
insync with the system clock (on any or all of the systems) is
obviated. In fact, as you can't guarantee the synchronicity means
that it can be confusing -- one expects a time-based clock to be
accurate to the time. A counter-based clock has no such expectations.
// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: Commit timestamp
Theo Schlossnagle [EMAIL PROTECTED] writes: As the clock must be incremented clusterwide, the need for it to be insync with the system clock (on any or all of the systems) is obviated. In fact, as you can't guarantee the synchronicity means that it can be confusing -- one expects a time-based clock to be accurate to the time. A counter-based clock has no such expectations. So if the nodes get split they can keep operating independently but clients can see that there's no guarantee of ordering against transactions from other nodes because the clock isn't advancing? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] VC2005 build and pthreads
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. 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] \copy (query) delimiter syntax error
Andrew Dunstan [EMAIL PROTECTED] writes: oh, good point. OK, I have cut this quick patch that will continue to accept the legacy syntax in psql in non-inline-query cases, but will make psql unreservedly emit new style syntax for COPY to the backend. Does that seem reasonable, or is it too much of a change for the stable branch? We've never promised that psql's backslash commands would work at all with older backends, so I think removing compatibility with pre-7.3 backends at this point isn't a problem. OTOH, going out of your way to remove compatibility seems a bit pointless, so I'd vote against the have_query parts of this patch. Just change the output format. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: Commit timestamp
On 2/4/2007 10:53 AM, Theo Schlossnagle wrote: As the clock must be incremented clusterwide, the need for it to be insync with the system clock (on any or all of the systems) is obviated. In fact, as you can't guarantee the synchronicity means that it can be confusing -- one expects a time-based clock to be accurate to the time. A counter-based clock has no such expectations. 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? 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. 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 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] Proposal: Commit timestamp
On Feb 4, 2007, at 1:36 PM, Jan Wieck wrote: On 2/4/2007 10:53 AM, Theo Schlossnagle wrote: As the clock must be incremented clusterwide, the need for it to be insync with the system clock (on any or all of the systems) is obviated. In fact, as you can't guarantee the synchronicity means that it can be confusing -- one expects a time-based clock to be accurate to the time. A counter-based clock has no such expectations. 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? That's exactly what a quorum algorithm is for. 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? 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. 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. // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.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
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 ;-) regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] \copy (query) delimiter syntax error
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: oh, good point. OK, I have cut this quick patch that will continue to accept the legacy syntax in psql in non-inline-query cases, but will make psql unreservedly emit new style syntax for COPY to the backend. Does that seem reasonable, or is it too much of a change for the stable branch? We've never promised that psql's backslash commands would work at all with older backends, so I think removing compatibility with pre-7.3 backends at this point isn't a problem. OTOH, going out of your way to remove compatibility seems a bit pointless, so I'd vote against the have_query parts of this patch. Just change the output format. 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. I don't have very strong feelings either way - anybody else have an opinion? If not, I'll go with Tom's suggestion. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] \copy (query) delimiter syntax error
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? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] \copy (query) delimiter syntax error
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. cheers andrew ---(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 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] period data type
On Feb 5, 2007, at 12:47 , Warren Turkal wrote: 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 This is an area I'm actively working on. I've worked out an example using composite types and PL/pgsql, though it's not very performant. I'm currently looking at making a contrib module, but have been slowed a bit by my lack of C experience. Regarding conformance, as there's currently no standard, I'm leary of cleaving too closely to the Temporal SQL proposal to ward of making something that would be close but not quite conformant to a future addition to the SQL standard. Also, I hope to make a more general interval/range/period constructor that would allow you to make periods of other types (say, integers) as well. I've found Date/Darwen/Lorentzos' Temporal Data and the Relational Model[1] very helpful. Interested in possibly working together? Michael Glaesemann grzm seespotcode net [1]http://books.elsevier.com/uk/mk/uk/subindex.asp?isbn=9781558608559 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Performance penalty of visibility info in indexes?
On Feb 2, 2007, at 1:41 PM, Simon Riggs wrote: On Thu, 2007-02-01 at 23:57 -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 know this wouldn't work for all cases, but ISTM there are many cases where it would be a win. It would prevent any optimization that sought to avoid inserting rows into the index each time we perform an UPDATE. Improving UPDATE performance seems more important than improving count(*), IMHO. That depends on what you're doing; a large read-mostly table would likely see a lot of benefit from being able to do covering index scans. Of course this would have to be optional; there's lots of cases where you wouldn't want the added index size. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Modifying and solidifying contrib
There was also mention of having a means to tell pg_dump not to dump extensions... On Jan 30, 2007, at 2:49 PM, Andrew Dunstan wrote: Bruce Momjian wrote: Joshua D. Drake wrote: This seems like a good first step in growing a packaging infrastructure. I'd rather grow it organically than try to design it all up front. I am in Denver and have spotty inet access so forgive me. So where does this above leave us? What are we doing? I was kind of unclear on that too. It seems we are trying to address several issues: visibility of contrib, installation of contrib, etc. We discussed whether we put the functions in public, a schema for all contrib, or a schema for each contrib module, and then there was the discussion of how to configure someone using ten /contrib modules, or at least wanting them all to be accessible. And then there was the idea of allowing schema permissions to control access, so perhaps we could install more of /contrib by default, and allow the administrator to just enable/disable them via permissions. Personally, I think that might be the best approach because it allows us to eliminate the install process, but doesn't make the database less secure --- the administrator enables/disables them at runtime, or at least could. The issues I see are: 1. the 'thing name - the only name I have not seen some objection to is extension. 2. namespace - I think the consensus is tending towards one or more per extension. 3. install/uninstall support: Tom's proposal for an extension- schema map in the catalog will deal with that nicely, I think. 4. visibility/searchpath issues. I don't think long search paths are a huge issue, but I think we can make life a bit easier by tweaking searchpath support a bit (David's clever SQL notwithstanding). 5. legacy support - we need an option to load existing extensions to the public schema as now, or support for aliases/synonyms (the latter might be good to have regardless). 6. they all need proper docs. READMEs and the like are nowhere near good enough. Richard mentioned special testing requirements, but I don't see why we can't continue to use our standard regression mechanism. Mention has also been made of autoloading extensions with initdb. A case could perhaps be made for doing it in createdb - maybe not every db needs ltree, say. OTOH, if it's sitting quietly in its own schema than it's probably not doing any harm either, so maybe initdb should just load all the extensions it finds, and as you say make one less hoop to make people jump through. If we do that I think at least we'd need an option to inhibit autoloading. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- 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] [PATCHES] Fix database is ready race condition
Hi, Tom Lane wrote: 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. Sure, I'm fine with that wording. As long as it's in the reaper() and thus the postmaster is really ready then it says so, I'm happy. Markus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Performance penalty of visibility info in indexes?
Ühel kenal päeval, P, 2007-02-04 kell 22:23, kirjutas Jim Nasby: On Feb 2, 2007, at 1:41 PM, Simon Riggs wrote: On Thu, 2007-02-01 at 23:57 -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 know this wouldn't work for all cases, but ISTM there are many cases where it would be a win. It would prevent any optimization that sought to avoid inserting rows into the index each time we perform an UPDATE. Not always. If we do in-page update and keep the unchanged index entry pointing to the first tuple inside the page, then the indexes visibility info would still be valid for that tuple and also right for that field. Improving UPDATE performance seems more important than improving count(*), IMHO. That depends on what you're doing; a large read-mostly table would likely see a lot of benefit from being able to do covering index scans. A large read-mostly table would also benefit from separating the visibility info out to a compressed visibility heap. Of course this would have to be optional; there's lots of cases where you wouldn't want the added index size. Of course. All alternative ways of storing MVCC info should be optional and user-selectable so DBA can test and select the most suitable one for each usecase. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
