Re: [HACKERS] Inherited constraints and search paths (was Re:
On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: Berend Tober [EMAIL PROTECTED] writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. ... Comments anyone? Best thing to do is to prevent people from creating child tables in different schemas. Or at least advise against it. Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. My partitioning efforts will eventually distinguish between inherited and non-inherited constraints, since the former are fairly useless for partition elimination. So I can't see a reason to care whether they are there or not, if the user knows better. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Notification when freespaces empty
On Fri, 2005-05-20 at 14:41 +0900, ITAGAKI Takahiro wrote: LOG: FreeSpace for public.accounts becomes empty. (stored=1, avg=159, min=128) Looks useful to me, until we patch up FSMs more fully in the future. Might need rewording. Stored - stored pages, Avg - Avg Row Length. Probably should be a DEBUG1 log line also. It would be even more useful if this was signalled (perhaps to stats) in such a way that autovacuum could act upon this knowledge. Furthermore, this patch detaches empty fsmpages then. Freespaces keep being scanned after they empties, but it seems to be bootless effort. Does that do anything useful though? I thought we don't reallocate until VACUUM time, whereupon we identify any empty slots and reuse them. Who cares whether we deallocate earlier? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.02 rpm error
Well, there's not much discussion here. Other than the fact that a few things depend on libpq.so.3. Isn't the standard to keep libpq.so.(n-1) whenever you bump the number up ? Dave Volkan YAZICI wrote: Hi, On 5/19/05, Tom Lane [EMAIL PROTECTED] wrote: 8.0.2 and up should provide/require libpq.so.4 and so on. Apparently there is something broken with this set of RPMs. For futher of the discussion: http://lists.pgfoundry.org/pipermail/pgsqlrpms-hackers/2005-April/000197.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.02 rpm error
On Friday 20 May 2005 07:55, Dave Cramer wrote: Well, there's not much discussion here. Other than the fact that a few things depend on libpq.so.3. Isn't the standard to keep libpq.so.(n-1) whenever you bump the number up ? Only because libpq versioning has always been an afterthought in the upstream release process. The RPMset has worked around this in the past by providing fake previous versions; but it is just an ugly workaround of broken upstream behavior. This is not a new issue, unfortunately. That is, symlinks were provided to the new version of the library that masqueraded as previous versions, but weren't really previous versions. That can cause it's own broken behavior. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.02 rpm error
OK, so how do we fix this ? Dave Lamar Owen wrote: On Friday 20 May 2005 07:55, Dave Cramer wrote: Well, there's not much discussion here. Other than the fact that a few things depend on libpq.so.3. Isn't the standard to keep libpq.so.(n-1) whenever you bump the number up ? Only because libpq versioning has always been an afterthought in the upstream release process. The RPMset has worked around this in the past by providing fake previous versions; but it is just an ugly workaround of broken upstream behavior. This is not a new issue, unfortunately. That is, symlinks were provided to the new version of the library that masqueraded as previous versions, but weren't really previous versions. That can cause it's own broken behavior. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Inherited constraints and search paths (was Re: [GENERAL] Preserving data after updates)
Berend Tober [EMAIL PROTECTED] writes: On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. The case in question was not one of the child table being in a different partition (do you mean schema?), although that arrangement was considered and rejected for other reasons during data base design. I should clarify: the version of the pg_dump bug that still exists in HEAD is triggered by putting the child table in a different schema than the parent. 7.3 has different behavior --- offhand I think that in 7.3 the problem can occur if the child table is created while search_path is set differently than it was when the parent was created. (Of course, across multiple pg_dump and reload cycles this may boil down to the same thing. But there are more ways to burn yourself given the 7.3 implementation.) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] patches for items from TODO list
Sergey Ten wrote: Markus, Thank you for your reply. We considered embedding of an XML schema first followed by data. We decided to stick to our current data format to make sure stateless XML parsers can process it as well. Would it be better to add an option to the COPY command, to allow embedding an XML schema, so more advanced XML parsers can take advantage of it? Current CVS has a WITH CSV HEADER option. I wonder if we should add a HEADER option to XML to output the schema --- seems to make sense. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Notification when freespaces empty
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I think that this patch is useful to decide when to vacuum. It notifies when freespace empties as follows: No, it doesn't complain that there is no freespace, it complains when a specific request can't be fulfilled; which for a large request might not mean much of anything. I haven't quite worked out what you are trying to do with the minRequest restriction but I don't think that really makes things better. Also, aren't you throwing away remaining free space in order to prevent the log message from appearing repeatedly? $ ./pgbench -n -t 1000 LOG: FreeSpace for public.accounts becomes empty. (stored=1, avg=159, min=128) LOG: FreeSpace for public.tellers becomes empty. (stored=1, avg=238, min=40) LOG: FreeSpace for public.branches becomes empty. (stored=1, avg=238, min=36) A bigger issue is what is the point of logging such a transient condition --- who's going to read it? It's possible that autovacuum would like to know about this, and after we get autovacuum integrated into the backend I'm sure we'll be taking a look at letting it use FSM information. But I can't see that anyone is going to sit and watch the postmaster log to decide to trigger vacuums. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] patches for items from TODO list
Neil Conway wrote: Sergey Ten wrote: We think that putting it in the backend will make access from other components easier. In what way? It seems to me that this can be done just as easily in a client application / library, without cluttering the backend with yet another COPY output format. It would also avoid the need to mandate a single XML schema -- different clients will likely have different requirements. Since I am skeptical of the value of this feature in the first place, I think it would do less damage if implemented outside the backend. We considered putting XML in psql or libpq in the past, but the problem is that interfaces like jdbc couldn't take advantage of it. I do think it needs to be in the backend, and I think that is the agreement we had in the past. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 8.02 rpm error
On Fri, May 20, 2005 at 09:43:50AM -0400, Dave Cramer wrote: OK, so how do we fix this ? I don't know what is Redhat's standard practice, but in other RPM based distributions what is done is to distribute each library as its own package, using the soname as part of the package name (Debian also do it this way AFAIK). So we would have a package called, say, libpq3 which would contain only libpq.so.3 (and associated files, if any). The benefit is that people who needs to upgrade to a later version of the server can have both libpq4 and libpq3 installed, with libpq4 being used for the new frontends and libpq3 is kept around for programs that are linked to it, say PHP if you don't want to recompile it. Now, the problem with 8.0.2/8.0.3 is that we forgot to bump the soname before shipping 8.0, so we shipped a bogus libpq.so.3 which is really libpq.so.4, with a wrong soname. How to fix? Maybe we should provide a libpq3 package with the libraries coming from the REL_7_4_STABLE cvs branch. -- Alvaro Herrera (alvherre[a]surnet.cl) El sudor es la mejor cura para un pensamiento enfermo (Bardia) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Learning curves and such (was Re: [HACKERS] pgFoundry)
Andrew Dunstan wrote: Tom Lane said: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: What it comes down to is that a mailing list encourages many-eyes-on- one-bug synergy, whereas Bugzilla is designed to send a bug report to just one pair of eyes, or at most a small number of eyes. I haven't used RT but I doubt it's fundamentally different. Actually RT is quite different. It's very closely tied to email. You get all the updates in email and can respond to the emails and the results are archived in the ticket. [ shrug... ] BZ sends me email too --- for the things *it* thinks I should know about. The basic point here is that these systems are designed on the assumption that there is a small, easily identified set of people who need-to-know about any given problem. We (Postgres) have done well by *not* using that assumption, and I'm not eager to adopt a tool that forces us to buy into that mindset. Actually, when BZ sends you mail, it's acting on choices that you have made, or someone at RedHat has made for you. You have a lot of control over what it sends. You want all the email? Tell BZ and you should get it. By contrast with these fine-grained controls, a mailing list offers you one choice: subscribe or don't. Right, if you classify the information coming in, you can set controls over who sees it. What we don't do now is any kind of classification. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Learning curves and such (was Re: [HACKERS] pgFoundry)
On May 20, 2005, at 11:43 PM, Bruce Momjian wrote: Andrew Dunstan wrote: Actually, when BZ sends you mail, it's acting on choices that you have made, or someone at RedHat has made for you. You have a lot of control over what it sends. You want all the email? Tell BZ and you should get it. By contrast with these fine-grained controls, a mailing list offers you one choice: subscribe or don't. Right, if you classify the information coming in, you can set controls over who sees it. What we don't do now is any kind of classification. This may be a bit off-the-wall, but I recall Joel Spolsky recently writing about using Bayesian filtering to classify mail into groups other than spam/ham. I wonder if there's any use for something like that in this case. http://www.joelonsoftware.com/articles/FogBugzII.html Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.02 rpm error
On Friday 20 May 2005 09:43, Dave Cramer wrote: Lamar Owen wrote: On Friday 20 May 2005 07:55, Dave Cramer wrote: Well, there's not much discussion here. Other than the fact that a few things depend on libpq.so.3. Isn't the standard to keep libpq.so.(n-1) whenever you bump the number up ? Only because libpq versioning has always been an afterthought in the upstream release process. OK, so how do we fix this ? Any time a change is made to libpq that changes its exported symbols or ABI a version change needs to be made. The person committing the change that impacts the ABI needs to be the person responsible for changing the version number; otherwise someone needs to monitor libpq changes coming down committers and remind people when they need to bump the libpq major or minor so version. Further, any time a release is being built one of the top things on the checklist should be 'is libpq's soname appropriate or not?' Changing past releases is impossible and must be worked around, which is quite honestly ugly to do. The cleanest thing, yet painful as it is, is to simply not provide the .3 at all and people will just have to rebuild all their clients. This gives a clean break; no, we can't fix 8.0.0, but we can fix from this point forward. People will complain, loudly, when it's broken; but which is better? Forcing a clean break with one-time pain, or having pain every single 8.0.x release? This situation should really drive home the importance of being careful! Basically it boils down to being more careful when piping out a release. PostgreSQL is no longer an island where we depend on OS services and few people depend on us; rather, libpq in particular is becoming a core component of distributions all around, and thus libpq must be very carefully maintained as a result; we have to, pardon the pun, watch our p-q's. Too many other projects are depending upon the soname in libpq to indicate the exported interface. And, where exported interfaces vary according to compile-time options, we need to change the soname based on the options (such as SSL versus non-SSL builds). And we must be consistent in the versioning; people tend to expect minor version upgrades to not break things, and 8.0.1 broke things. The discussion on why the version was bumped this time is in the archives; people are now fighting a battle with that decision. The decision to bump the version of libpq was the correct one to make; it just should have been made before 8.0 was released and not after. But all this discussion is in the archives; I would suggest browsing those threads. The fftw3 library provides a reasonable model with which to work; there are multiple compile-time options there that dramatically change the ABI (things like integer versus single-precision float versus double-precision float), and the route the fftw3 developers have taken is to make separate names for each. This way OS linking and dependency resolution isn't broken arbitrarily because otherwise there is no way of communicating to the linkloader which ABI we're exporting today. And I've made enough release piping errors to recognize one :-) -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 8.02 rpm error
Hi, -- Original Message --- snip Now, the problem with 8.0.2/8.0.3 is that we forgot to bump the soname before shipping 8.0, so we shipped a bogus libpq.so.3 which is really libpq.so.4, with a wrong soname. How to fix? Maybe we should provide a libpq3 package with the libraries coming from the REL_7_4_STABLE cvs branch. I *was* working on a compatibility RPM this week. However, I'm now on a Linux and Open Source Festival in Turkey and will be able to release the first compat RPM next week. Regards, -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com.tr http://www.gunduz.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Learning curves and such (was Re: [HACKERS] pgFoundry)
On Fri, May 20, 2005 at 11:59:00PM +0900, Michael Glaesemann wrote: Right, if you classify the information coming in, you can set controls over who sees it. What we don't do now is any kind of classification. This may be a bit off-the-wall, but I recall Joel Spolsky recently writing about using Bayesian filtering to classify mail into groups other than spam/ham. I wonder if there's any use for something like that in this case. http://www.joelonsoftware.com/articles/FogBugzII.html No, definitely not. Pseudo-bayesian classification as used by the more optimistic spam-filtering folks is pretty crappy at the best of times, and it's really unusuable for more than 3-4 categories. There are natural language analysis techniques that'll do this sort of thing, but they're in the realms of research projects, not canned tools. Cheers, Steve ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Two-phase commit issues
Tom Lane wrote: I've started to look seriously at Heikki's patch for two-phase commit. There are a few issues that probably deserve discussion: * The major missing issue that I've come across so far is that subtransaction and multixact state isn't preserved across a crash. I am a little confused by this. How does two-phase commit add extra requirements on crash recovery? I understand a crashed server might be involved in a two-phase commit, but doesn't the transaction just roll back? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Image storage questions
I asked this on general, but got no answer on this particular point, maybe someone here knows. Are blobs are stored in the shared memory cache upon retrieval? I ask because we're trying to decide whether to store an enormous number of images in PostgreSQL, and I'd be concerned that frequent retrieval of those images would effectively empty the cache of the rest of the non-image data. I watched the pg_statio* numbers during some test retrievals, and it appears the reads and cache hits on the binary image data are not being counted. I'm wondering if that is the truth of what's going on, or just an accounting oversight. Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.02 rpm error
Lamar Owen wrote: On Friday 20 May 2005 09:43, Dave Cramer wrote: Lamar Owen wrote: On Friday 20 May 2005 07:55, Dave Cramer wrote: Well, there's not much discussion here. Other than the fact that a few things depend on libpq.so.3. Isn't the standard to keep libpq.so.(n-1) whenever you bump the number up ? Only because libpq versioning has always been an afterthought in the upstream release process. OK, so how do we fix this ? Any time a change is made to libpq that changes its exported symbols or ABI a version change needs to be made. The person committing the change that impacts the ABI needs to be the person responsible for changing the version number; otherwise someone needs to monitor libpq changes coming down committers and remind people when they need to bump the libpq major or minor so version. Further, any time a release is being built one of the top things on the checklist should be 'is libpq's soname appropriate or not?' What hit us in this case is the movement of a function (get_progname()) from libpq to pgport and now that certain binary applications were pulling get_progname from libpq rather than pgport. So the API didn't really change, just the place applications looked for symbols. What also hit us is that people complaining about this during beta testing were told just to recompile (which fixed it), but we didn't realize that it prevented installs of 7.4 from working. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Two-phase commit issues
Bruce Momjian pgman@candle.pha.pa.us writes: I am a little confused by this. How does two-phase commit add extra requirements on crash recovery? Uh, that's more or less the entire *POINT*. Once an open transaction is prepared, it's supposed to survive a server crash. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Image storage questions
Ed L. [EMAIL PROTECTED] writes: I asked this on general, but got no answer on this particular point, maybe someone here knows. Are blobs are stored in the shared memory cache upon retrieval? pg_largeobject is treated exactly the same as any other table, if that's what you are asking. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Two-phase commit issues
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am a little confused by this. How does two-phase commit add extra requirements on crash recovery? Uh, that's more or less the entire *POINT*. Once an open transaction is prepared, it's supposed to survive a server crash. Wow. This is much more than I thought we were going to do. I thought if something failed after the prepare we were just going to inform the administrator and give up. Becuase you are writing status file to the disk, it seems you are trying to recover from a crash and roll forward. What cases would we actually fail to recover from a crash after a PREPARE? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Inherited constraints and search paths (was Re: [GENERAL]
Simon Riggs wrote: On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: Berend Tober [EMAIL PROTECTED] writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. ... Comments anyone? Best thing to do is to prevent people from creating child tables in different schemas. Or at least advise against it. Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. My partitioning efforts will eventually distinguish between inherited and non-inherited constraints, since the former are fairly useless for partition elimination. So I can't see a reason to care whether they are there or not, if the user knows better. The case in question was not one of the child table being in a different partition (do you mean schema?), although that arrangement was considered and rejected for other reasons during data base design. In this implementation, a function called for a table constraint was in a different schema. The function so called was defined in the public scheme because it is a generic function that can be used by different applications, and some tables are relevant only to specific applications and so have there own, application-specific schema -- but they still can make use of shared definitions, i.e., this particular function, which are defined in the public schema. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:
Simon Riggs [EMAIL PROTECTED] writes: Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. Uh, why? Arguably the constraints are as much part of the parent table definition as the columns themselves. If you had check (f1 0) in the definition of a table, wouldn't you be pretty surprised to select from it and find rows with f1 0? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table child drop constraint parent_f1_check; ALTER TABLE regression=# insert into child values(-1); INSERT 0 1 regression=# select * from parent; f1 -1 (1 row) I think a good argument can be made that the above behavior is a bug, and that the ALTER command should have been rejected. We've gone to great lengths to make sure you can't ALTER a child table to make it incompatible with the parent in terms of the column names and types; shouldn't this be true of check constraints as well? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Two-phase commit issues
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Uh, that's more or less the entire *POINT*. Once an open transaction is prepared, it's supposed to survive a server crash. Wow. This is much more than I thought we were going to do. If we tried to claim that anything less was two-phase commit, we'd be laughed off the face of the planet ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Two-phase commit issues
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Uh, that's more or less the entire *POINT*. Once an open transaction is prepared, it's supposed to survive a server crash. Wow. This is much more than I thought we were going to do. If we tried to claim that anything less was two-phase commit, we'd be laughed off the face of the planet ... Well, based on past discussions, our TODO has: * Add two-phase commit This will involve adding a way to respond to commit failure by either taking the server into offline/readonly mode or notifying the administrator As I remember, you said two-phase wasn't 100% reliable and we just needed a way to report failures. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Two-phase commit issues
Bruce Momjian pgman@candle.pha.pa.us writes: As I remember, you said two-phase wasn't 100% reliable and we just needed a way to report failures. [ Shrug... ] I remain of the opinion that 2PC is a solution in search of a problem, because it does not solve the single point of failure issue (just moves same from the database to the 2PC controller). But some people want it anyway, and they aren't going to be satisfied that we are an enterprise grade database until we can check off this particular bullet point. As long as the implementation doesn't impose any significant costs when not being used (which AFAICS Heikki's method doesn't), I think we gotta hold our noses and do it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Two-phase commit issues
Exactly. A 2PC expects every participant that makes it to the prepare to commit phase to survive a server restart, controller or otherwise. Anything less is not 2PC. Jordan Henderson On Fri, 2005-05-20 at 12:07 -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am a little confused by this. How does two-phase commit add extra requirements on crash recovery? Uh, that's more or less the entire *POINT*. Once an open transaction is prepared, it's supposed to survive a server crash. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Allow change of kerberos service name without recompilation
We have modified your patch and it will appear in 8.1. Thanks. --- Daniel Ahlin wrote: Hi This is a two part patch against 7.4.5 implementing the option of configuring what is now set using the #defined constant PG_KRB_SRVNAM (the name of the service part of the kerberos principal the server uses). On the backend it can be configured by the (new) string option krb_srvnam in postgresql.conf. On the client it can be configured by setting the environment variable PGKRBSRVNAM. The default setting (for both) is the value given by PG_KRB_SRVNAM mentioned above. Content-Description: Patch for backend [ Attachment, skipping... ] Content-Description: Interface patch [ Attachment, skipping... ] The use of this is mainly if several different users want to run their own instance of postgresql on the same machine. Regards Daniel Ahlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [HACKERS] Inherited constraints and search paths
On Fri, 2005-05-20 at 11:51 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. Uh, why? Arguably the constraints are as much part of the parent table definition as the columns themselves. If you had check (f1 0) in the definition of a table, wouldn't you be pretty surprised to select from it and find rows with f1 0? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table child drop constraint parent_f1_check; ALTER TABLE regression=# insert into child values(-1); INSERT 0 1 regression=# select * from parent; f1 -1 (1 row) I think a good argument can be made that the above behavior is a bug, and that the ALTER command should have been rejected. We've gone to great lengths to make sure you can't ALTER a child table to make it incompatible with the parent in terms of the column names and types; shouldn't this be true of check constraints as well? Thats a good case. I retract my comment on potentially annoying. If you were going to fix that by adding a column that allows me to tell the difference between inherited and non-inherited relations, that would be a very useful piece of info for partition elimination. In that case I would also retract my comment on wasted effort. :-) If you're looking for other inheritance wierdies, you may also be interested in this one. When you create a table that inherits from a master, it copies across constraints with exactly matching names. If a constraint is then added to the master, the constraint is copied across to the child but does not have the same name. So the name of inherited constraints differs depending upon whether CREATE or ALTER puts them there. FWIW, fixing either of those won't get in my way on partitioning... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:
Simon Riggs [EMAIL PROTECTED] writes: If you were going to fix that by adding a column that allows me to tell the difference between inherited and non-inherited relations, that would be a very useful piece of info for partition elimination. Inherited and non-inherited constraints you mean? Probably. I hadn't thought through the details, but certainly there would need to be some better way of telling whether a constraint was inherited. If you're looking for other inheritance wierdies, you may also be interested in this one. When you create a table that inherits from a master, it copies across constraints with exactly matching names. If a constraint is then added to the master, the constraint is copied across to the child but does not have the same name. Hmm, that's weird: if you give an explicit name (add constraint foo) then it's used, but if you let the system generate the name it's not the same. I agree that was probably unintentional. Does anyone want to argue for keeping it this way? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table parent add check (f1 100); ALTER TABLE regression=# \d parent Table public.parent Column | Type | Modifiers +-+--- f1 | integer | Check constraints: parent_f1_check CHECK (f1 0) parent_f1_check1 CHECK (f1 100) regression=# \d child Table public.child Column | Type | Modifiers +-+--- f1 | integer | Check constraints: parent_f1_check CHECK (f1 0) child_f1_check CHECK (f1 100) Inherits: parent (This is, btw, another case that would break the current code for identifying inherited constraints in pg_dump. Given a positive marker for an inherited constraint, however, we wouldn't care. So I don't think we need to consider pg_dump in debating which behavior we like.) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.02 rpm error
Now, the problem with 8.0.2/8.0.3 is that we forgot to bump the soname before shipping 8.0, so we shipped a bogus libpq.so.3 which is really libpq.so.4, with a wrong soname. How to fix? Maybe we should provide a libpq3 package with the libraries coming from the REL_7_4_STABLE cvs branch. I was just thinking about this today. Typically Redhat will have a postgresql-libs-compat which would contain libpq.so.3. They haven't had to do this in the past with PostgreSQL but they do it with others such as glibc. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] patches for items from TODO list
I've been reviewing this patch and some of the following discussion. First, postgresql patches are usually sent as context diffs. I don't object to unidiffs myself, but you should do what everybody else does. Second, it's best not to combine features in one patch. The \x escape piece should be broken out. I'm also a rather worried about COPY producing output which it can't itself parse. We can read our own binary, text and CSV formats, and I think that's a useful validation tool. I know the TODO item only mentions output, but I believe we should rethink that. In any case, if it's valid for us to hand XML to other programs why shouldn't we accept it too. This is all about playing nicely in the playground. One advantage of XML is that, being hierarchical, it can easily express nested composites (records, arrays) in a way that our present text and CSV formats really can't. But unless I missed something this patch doesn't in fact do anything to break out nested composites. Finally, I don't know if there is a standard on this, or even a convention. What do other DBs do? I'm not keen on us just inventing our own XML dialect for something that should after all be most useful in data exchange. Bottom line, much as I would like to see XML input/output, I think this needs lots more thought and discussion. cheers andrew Sergey Ten wrote: Hello all, Thank you to all who replied for suggestions and help. Enclosed please find code changes for the following items: - Allow COPY to understand \x as a hex byte, and - Add XML output to COPY The changes include implementation of the features as well as modification of the copy regression test. After a careful consideration we decided to - put XML implementation in the backend and - use XML format described below, with justification of our decision. The XML schema used by the COPY TO command was designed for ease of use and to avoid the problem of column names appearing in XML element names. XML doesn't allow spaces and punctuation in element names but Postgres does allow these characters in column names; therefore, a direct mapping would be problematic. The solution selected places the column names into attribute fields where any special characters they contain can be properly escaped using XML entities. An additional attribute is used to distinguish null fields from empty ones. The example below is taken from the test suite. It demonstrates some basic XML escaping in row 2. Row 3 demonstrates the difference between an empty string (in col2) and a null string (in col3). If a field is null it will always be empty but a field which is empty may or may not be null. Always check the value of the 'null' attribute to be sure when a field is truly null. ?xml version='1.0'? table row col name='col1' null='n'Jackson, Sam/col col name='col2' null='n'\h/col /row row col name='col1' null='n'It is quot;perfectquot;./col col name='col2' null='n'#09;/col /row row col name='col1' null='n'/col col name='col2' null='y'/col /row /table Please let us know if about any concerns, objections the proposed change may cause. Best regards, Jason Lucas, Sergey Ten SourceLabs -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 11, 2005 7:11 PM To: Sergey Ten Cc: pgsql-hackers@postgresql.org; [EMAIL PROTECTED] Subject: Re: [HACKERS] patches for items from TODO list Sergey Ten wrote: Hello all, We would like to contribute to the Postgresql community by implementing the following items from the TODO list (http://developer.postgresql.org/todo.php): . Allow COPY to understand \x as a hex byte . Allow COPY to optionally include column headings in the first line . Add XML output to COPY The changes are straightforward and include implementation of the features as well as modification of the regression tests and documentation. Before sending a diff file with the changes, we would like to know if these features have been already implemented. Please check the web site version. Someone has already implemented Allow COPY to optionally include column headings in the first line. As far as XML, there has been discussion on where that should be done? In the backend, libpq, or psql. It will need discussion on hackers. I assume you have read the developer's FAQ too. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/backend/commands/copy.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v retrieving revision
Re: [HACKERS] [GENERAL] Image storage questions
On Friday May 20 2005 10:20 am, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I asked this on general, but got no answer on this particular point, maybe someone here knows. Are blobs are stored in the shared memory cache upon retrieval? pg_largeobject is treated exactly the same as any other table, if that's what you are asking. Yes, that was my question. Are large object rows also vacuumed just as the other tables/rows? Thanks. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Two-phase commit issues
Tom Lane wrote: [ Shrug... ] I remain of the opinion that 2PC is a solution in search of a problem, because it does not solve the single point of failure issue (just moves same from the database to the 2PC controller). But some people want it anyway, and they aren't going to be satisfied that we are an enterprise grade database until we can check off this particular bullet point. As long as the implementation doesn't impose any significant costs when not being used (which AFAICS Heikki's method doesn't), I think we gotta hold our noses and do it. I thought the primary reason for having 2PC is to be able to participate in a heterogenous transaction, e.g. with a non-Postgres database/other types of resource managers? 2PC is mostly about how to make these cross-RM transactions [appear] atomic. Redundancy is not covered by 2PC protocol. -- dave ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] patches for items from TODO list
Bruce Momjian wrote: We considered putting XML in psql or libpq in the past, but the problem is that interfaces like jdbc couldn't take advantage of it. Well, you could implement it as a C UDF and use SPI. Or write it as a C client library, and use JNI. Or just provide a Java implementation -- it's not like the COPY - XML transformation is very complex. To restate the case: - I don't see how this feature is useful. Perhaps I'm mistaken, but I don't think there's a lot of user demand for it (feel free to demonstrate the contrary) - The COPY - XML transformation is trivial -- it would be easy for clients to roll their own. At the same time, there is no standard or canonical XML representation for COPY output, and I can easily imagine different clients needing different representations. So there is limited value in providing a single, inflexible backend implementation. - There's no need for it to be in the backend, anyway. Perhaps if there were overwhelming demand for this functionality, we would need to provide it for all client libraries and the easiest solution would be to put it in the backend, but I don't think that's the case. If people really think XML COPY output mode is useful, why not implement it client-side first and host it on pgfoundry? If lots of people are using the client-side code, we can consider moving it into the core distribution or the backend itself at that point. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] patches for items from TODO list
Neil Conway wrote: Bruce Momjian wrote: We considered putting XML in psql or libpq in the past, but the problem is that interfaces like jdbc couldn't take advantage of it. Well, you could implement it as a C UDF and use SPI. Or write it as a C client library, and use JNI. Or just provide a Java implementation -- it's not like the COPY - XML transformation is very complex. To restate the case: - I don't see how this feature is useful. Perhaps I'm mistaken, but I don't think there's a lot of user demand for it (feel free to demonstrate the contrary) - The COPY - XML transformation is trivial -- it would be easy for clients to roll their own. At the same time, there is no standard or canonical XML representation for COPY output, and I can easily imagine different clients needing different representations. So there is limited value in providing a single, inflexible backend implementation. - There's no need for it to be in the backend, anyway. Perhaps if there were overwhelming demand for this functionality, we would need to provide it for all client libraries and the easiest solution would be to put it in the backend, but I don't think that's the case. If people really think XML COPY output mode is useful, why not implement it client-side first and host it on pgfoundry? If lots of people are using the client-side code, we can consider moving it into the core distribution or the backend itself at that point. All I can say is that we rejected an XML in the client patch a long time ago and the discussion was that it belongs in the backend so everyone can use it. I don't use XML myself so I have no opinion. We need people who need XML output to comment in this thread. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] patches for items from TODO list
Folks, - The COPY - XML transformation is trivial -- it would be easy for clients to roll their own. At the same time, there is no standard or canonical XML representation for COPY output, and I can easily imagine different clients needing different representations. So there is limited value in providing a single, inflexible backend implementation. I'm going to second Neil here. This feature becomes useful *only* when there is a certified or de-facto universal standard XML representation for database data. Then I could see a case for it. But there isn't. Feel free to throw it on pgFoundry, though. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Two-phase commit issues
Tom, [ Shrug... ] I remain of the opinion that 2PC is a solution in search of a problem, because it does not solve the single point of failure issue (just moves same from the database to the 2PC controller). But some people want it anyway, and they aren't going to be satisfied that we are an enterprise grade database until we can check off this particular bullet point. As long as the implementation doesn't impose any significant costs when not being used (which AFAICS Heikki's method doesn't), I think we gotta hold our noses and do it. 2PC is a key to supporting 3rd-party replication tools, like C-JDBC. And is useful for some other use cases, like slow-WAN-based financial transactions. We know you don't like it, Tom. ;-) -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] patches for items from TODO list
Josh Berkus josh@agliodbs.com writes: I'm going to second Neil here. I think the same --- given the points about lack of standardization, it seems premature to put this into the backend. I'd be for it if there were a clear standard, but ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] patches for items from TODO list
I'm going to second Neil here. This feature becomes useful *only* when there is a certified or de-facto universal standard XML representation for database data. Then I could see a case for it. But there isn't. We've done it in phpPgAdmin (we made up our own standard), and a couple of people use it. I also do not think that it should be in the backend until there is a standard. Here is what phpPgAdmin produces (note NULL handling): ?xml version=1.0 encoding=UTF-8 ? data header column name=feature_id type=varchar / column name=feature_name type=varchar / column name=is_supported type=varchar / column name=is_verified_by type=varchar / column name=comments type=varchar / /header records row column name=feature_idPKG000/column column name=feature_nameCore/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=comments null=null/column /row row column name=feature_idPKG001/column column name=feature_nameEnhanced datetime facilities/column column name=is_supportedYES/column column name=is_verified_by null=null/column column name=comments null=null/column /row row column name=feature_idPKG002/column column name=feature_nameEnhanced integrity management/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=comments null=null/column /row row column name=feature_idPKG003/column column name=feature_nameOLAP facilities/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=comments null=null/column /row row column name=feature_idPKG004/column column name=feature_namePSM/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=commentsPL/pgSQL is similar./column /row row column name=feature_idPKG005/column column name=feature_nameCLI/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=commentsODBC is similar./column /row row column name=feature_idPKG006/column column name=feature_nameBasic object support/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=comments null=null/column /row row column name=feature_idPKG007/column column name=feature_nameEnhanced object support/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=comments null=null/column /row row column name=feature_idPKG008/column column name=feature_nameActive database/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=comments null=null/column /row row column name=feature_idPKG010/column column name=feature_nameOLAP/column column name=is_supportedNO/column column name=is_verified_by null=null/column column name=commentsNO/column /row /records /data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
