Re: [HACKERS] PostgreSQL and SOAP, suggestions?
[EMAIL PROTECTED] kirjutas N, 03.04.2003 kell 02:01: mlw wrote: I think you are interpreting the spec a bit too restrictively. The syntax is fairly rigid, but the spec has a great degree of flexibility. I agree that, syntactically, it must work through a parser, but there is lots of room to be flexible. This is /exactly/ the standard problem with SOAP. There is enough flexibility that there are differing approaches associated, generally speaking, with IBM versus Microsoft whereby it's easy to generate SOAP requests that work fine with one that break with the other. Do you know of some: a) standard conformance tests b) recommended best practices for being compatible with all mainstream implementations (I'd guess a good approach would be to generate very strictly conformant code but accept all that you can, even if against pedantic reading of the spec) - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] contrib and licensing
Marc G. Fournier wrote: On Wed, 2 Apr 2003, scott.marlowe wrote: If that is a real objective, I'm surprised. The base source tree has always been as BSD pure as we can make it ... its never been kept a secret ... True. But not linking to LGPLd libs would be a bit extreme there. Correct, we've always had libreadline support, as a compile option, but libreadline is not part of the distribution, only the hooks to it are ... and, just recently, libedit(?) support was added as well, so that a non-GPL licensed alternative is available for those wishing to distribute the software ... GPL vs. LGPL vs. BSD vs. MyFu**inLicense the next round ... man is this annoying. I think with this new incarnation of the License war it's a good time to give a real example what dragging our attention to licensing leads to. Libedit might not be as good ... so be it. Who cares about people who choose their database system by the color of the splash screen? We have a pure BSD alternative that we could even ship with our distro, time to retire the libreadline hooks. 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: 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] PostgreSQL and SOAP, suggestions?
Hannu Krosing wrote: [EMAIL PROTECTED] kirjutas N, 03.04.2003 kell 02:01: mlw wrote: I think you are interpreting the spec a bit too restrictively. The syntax is fairly rigid, but the spec has a great degree of flexibility. I agree that, syntactically, it must work through a parser, but there is lots of room to be flexible. This is /exactly/ the standard problem with SOAP. There is enough flexibility that there are differing approaches associated, generally speaking, with IBM versus Microsoft whereby it's easy to generate SOAP requests that work fine with one that break with the other. Do you know of some: a) standard conformance tests Off the top of my head, no, but I bet it is a goole away. If you know any good links, I'd love to know. I have been working off the W3C spec. b) recommended best practices for being compatible with all mainstream implementations (I'd guess a good approach would be to generate very strictly conformant code but accept all that you can, even if against pedantic reading of the spec) I have been planning to test the whole thing with a few .NET applications. I am currently using expat to parse the output to ensure that it all works correcty. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib and licensing
Tom Lane wrote:
On other Unixoid systems you can link against BSD-license libc code, or
some-random-proprietary-license code from HP or Sun or whomever. glibc
doesn't have a monopoly in that sphere. But mlw is offering code that
will *only* run against a single implementation that is LGPL licensed.
That makes it effectively LGPL.
Here is my vision for lack of a better term.
Server 'A' runs a web services version of a PostgreSQL server, (or any
soap server) I have a working prototype that works.
Server 'B' runs a different instance of PostgreSQL.
With the ability to return multiple columns in a set of rows from a
function, it should be possible to do this:
select foo.a, bar.b from foo,
soapexec('http://somehost/pgsql?query=select+b+from+bar') as bar where
foo.b = bar.b;
(or something to that effect, the SQL may not be perfect.)
To be able to do that, we need:
some HTTP request code
a solid XML/SOAP parser.
The soapexec function needs to be able to do a few things:
Return more than one column in a multirow set.
Find out the field names that are expected.
Find out the datatypes that are expected to be returned to the query.
Tom, when one creates a function, can the function tell, in an efficient
way, what data types and names may be expected?
I have been talking about adding this feature to a few developers not
involved with PostgreSQL, and they are finatic about the idea. As far as
I can tell no other DB does this.
---(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] PostgreSQL and SOAP, suggestions?
[EMAIL PROTECTED] kirjutas N, 03.04.2003 kell 02:01: mlw wrote: I think you are interpreting the spec a bit too restrictively. The syntax is fairly rigid, but the spec has a great degree of flexibility. I agree that, syntactically, it must work through a parser, but there is lots of room to be flexible. This is /exactly/ the standard problem with SOAP. There is enough flexibility that there are differing approaches associated, generally speaking, with IBM versus Microsoft whereby it's easy to generate SOAP requests that work fine with one that break with the other. Do you know of some: a) standard conformance tests b) recommended best practices for being compatible with all mainstream implementations (I'd guess a good approach would be to generate very strictly conformant code but accept all that you can, even if against pedantic reading of the spec) The problem with a) is that SOAP, unlike CORBA, doesn't have the notion of standardized language bindings. That makes it tough to be sure that your implementation is standard in any meaningful way in the first place. The best practices have involved scripting up interoperability tests where they construct sets of functions with varying data types and verify that my client implementation can talk to your server implementation, and vice-versa. And when you run into problems, you chip off bits of code until the block of stone starts looking like an elephant. In order to have confidence of interoperability, you have to test your client library against all the servers you care about, or vice-versa. That's definitely not the same thing as being a conformance test. Trying to be really strict doesn't seem to be a viable strategy, as far as I can see... -- (concatenate 'string cbbrowne @ntlug.org) http://www3.sympatico.ca/cbbrowne/wp.html The cost of living has just gone up another dollar a quart. -- W.C. Fields ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL and SOAP, suggestions?
I have been planning to test the whole thing with a few .NET applications. I am currently using expat to parse the output to ensure that it all works correcty. That, unfortunately, probably implies that your implementation is almost totally non-interoperable. You should put out of your mind the notion of being correct. Being correct is pretty irrelevant if 80% of the requests that come from a VB.NET client fail because Microsoft implemented part of their request differently than what you interpreted as correct. The point is that correctness isn't the thing you need to aim for; what you should aim for is interoperability with the important client implementations. SOAP::Lite, .NET, probably some Java ones, C++ ones, and such. Nobody does correctness testing; they do interoperability tests where they try to submit requests to Apache AXIS, .NET, WebSphere, and the lot of other important implementations. If you're testing a server (as is the case here), then the point is to run tests with a bunch of clients. Head to the SOAP::Lite and Axis projects; you'll see matrices describing this sort of thing... -- (reverse (concatenate 'string ac.notelrac.teneerf@ 454aa)) http://www.ntlug.org/~cbbrowne/advocacy.html Fear leads to anger. Anger leads to hate. Hate leads to using Windows NT for mission-critical applications. --- What Yoda *meant* to say ---(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] contrib and licensing
Jan Wieck wrote: Marc G. Fournier wrote: On Wed, 2 Apr 2003, scott.marlowe wrote: If that is a real objective, I'm surprised. The base source tree has always been as BSD pure as we can make it ... its never been kept a secret ... True. But not linking to LGPLd libs would be a bit extreme there. Correct, we've always had libreadline support, as a compile option, but libreadline is not part of the distribution, only the hooks to it are ... and, just recently, libedit(?) support was added as well, so that a non-GPL licensed alternative is available for those wishing to distribute the software ... GPL vs. LGPL vs. BSD vs. MyFu**inLicense the next round ... man is this annoying. I think with this new incarnation of the License war it's a good time to give a real example what dragging our attention to licensing leads to. Libedit might not be as good ... so be it. Who cares about people who choose their database system by the color of the splash screen? We have a pure BSD alternative that we could even ship with our distro, time to retire the libreadline hooks. I certainly didn't want to open up this can of worms, that's for sure. I have an amount of code that is LGPL, I would rather use it than write the bits again or try to extract them from the whole. The actual extension would be BSD, but it would need to link with my library. I made the library LGPL (from GPL) for the PHP group who have similar restrictions. Thus this discussion. I don't know what the answer is, but to say NO LGPL seems a bit extream, especially if you already have such dependencies. Then if you conclude you do allow LGPL libraries, but then only allow some libraries, not all, then what is the criteria for choosing which libraries get blessed. Is it purely popularity? Do you guys really think that a contrib function should not be allowed to require code which may not be on a common UNIX/BSD/Linux box? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] contrib and licensing
mlw wrote: Jan Wieck wrote: [...] screen? We have a pure BSD alternative that we could even ship with our distro, time to retire the libreadline hooks. I certainly didn't want to open up this can of worms, that's for sure. I have an amount of code that is LGPL, I would rather use it than write the bits again or try to extract them from the whole. The actual extension would be BSD, but it would need to link with my library. I made the library LGPL (from GPL) for the PHP group who have similar restrictions. Thus this discussion. If it is your library as you've said multiple times now, there is nothing that can stop you from relicensing it. Give me one good reason why you have to keep your library under the LGPL, or better why it cannot be relicensed under BSD. I don't know what the answer is, but to say NO LGPL seems a bit extream, especially if you already have such dependencies. Then if you conclude you do allow LGPL libraries, but then only allow some libraries, not all, then what is the criteria for choosing which libraries get blessed. Is it purely popularity? Not more extreme than I am the only true license, you shall not have any other licenses beside me, which is my personal interpretation of the entire FSF attempt. Do you guys really think that a contrib function should not be allowed to require code which may not be on a common UNIX/BSD/Linux box? Your library does appear in what Unix distributions? And in which of them is it installed by default like the libreadline? Yes, that certainly has to do with popularity. 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: 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] contrib and licensing
Lamar Owen [EMAIL PROTECTED] writes: And its stubs are in the backend, of all places. Really? I must have missed that. On Linux as compiled in Red Hat 9, at least: [EMAIL PROTECTED] lowen]$ ldd /usr/bin/postgres libreadline.so.4 = /usr/lib/libreadline.so.4 (0x401c6000) That's because our build mechanism links *all* needed libraries in *all* executables, rather than trying to distinguish which ones are actually used by each executable. The ldd indication is the only connection to libreadline --- if it had been a statically-linked situation, you'd find no trace of readline (nor several other of these libraries, I suspect) in the backend executable. 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])
[HACKERS] can't create database
on one of the AIX4.3.3, the 7.1.3 pgsql is installed by root on the system, then I tried to install 7.3.1/or 7.3.2 under another non-root user, I can run make, make check, make install, postmaster can start without errors, but when I try to createdb, here're some errors - createdb emrxdbs ERROR: 'autocommit' is not a valid option name createdb: database creation failed then I issued, postgre7.3.2psql template1 ERROR: parser: parse error at or near . Welcome to psql 7.3.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1= select version(); version PostgreSQL 7.1.3 on powerpc-ibm-aix4.3.3.0, compiled by cc (1 row) template1= it picks the older version and always having a parser error!! Any hints? thanks. johnl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] can't create database
on one of the AIX4.3.3, the 7.1.3 pgsql is installed by root on the system, then I tried to install 7.3.1/or 7.3.2 under another non-root user, I can run make, make check, make install, postmaster can start without errors, but when I try to createdb, here're some errors - createdb emrxdbs ERROR: 'autocommit' is not a valid option name createdb: database creation failed then I issued, postgre7.3.2psql template1 ERROR: parser: parse error at or near . Welcome to psql 7.3.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1= select version(); version PostgreSQL 7.1.3 on powerpc-ibm-aix4.3.3.0, compiled by cc (1 row) template1= it picks the older version and always having a parser error!! Any hints? You'll probably need to set a second PGPORT to make two versions run. See the docs. Regards, Christoph ---(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] can't create database
I already set the 2nd port for the new installation, otherwise, postmaster will not start up ... johnl -Original Message- From: Christoph Haller [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:05 AM To: John Liu Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] can't create database on one of the AIX4.3.3, the 7.1.3 pgsql is installed by root on the system, then I tried to install 7.3.1/or 7.3.2 under another non-root user, I can run make, make check, make install, postmaster can start without errors, but when I try to createdb, here're some errors - createdb emrxdbs ERROR: 'autocommit' is not a valid option name createdb: database creation failed then I issued, postgre7.3.2psql template1 ERROR: parser: parse error at or near . Welcome to psql 7.3.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1= select version(); version PostgreSQL 7.1.3 on powerpc-ibm-aix4.3.3.0, compiled by cc (1 row) template1= it picks the older version and always having a parser error!! Any hints? You'll probably need to set a second PGPORT to make two versions run. See the docs. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] more contrib: log rotator
Since now is the time for contrib/ flamewars, this seemed a good time to suggest this. My colleague, Sorin Iszlai, wrote us a little program for rotating our Postgres logs. It reads stdout and stderr, and sends them to different files (and rotates them as necessary). It is currently hand-configureable (i.e. by altering some variables at the top of the script), and is more or less designed for use in our own environment. Tom Lane recently mentioned to me that a common complaint is that postgres doesn't have its own log rotator. There are, of course, plenty of good ones, and syslog itself works pretty well for most people. But there are still complaints from time to time about the lack of a built in log rotator. We'd be happy to release our rotator under the PostgreSQL BSD license, if it would be of use to people. I was thinking that perhaps contrib/ would be a good place for it, since the idea is to reduce complaints that there's no log rotator included. Is anyone interested in having pglog-rotator? A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] can't create database
John Liu [EMAIL PROTECTED] writes: I already set the 2nd port for the new installation, otherwise, postmaster will not start up ... But your client-side programs are still talking to the port where the old postmaster is. You could set PGPORT in their environment to fix that. What I find more convenient, though, when I intend to run multiple installations on one machine, is to build each one with --with-pgport= added to the configure switches, choosing a different default port for each. Then you don't have to worry about switching PGPORT environment (only about putting the right version of psql first in your path ...) 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] PostgreSQL and SOAP, suggestions?
On Thu, Apr 03, 2003 at 07:54:13AM -0500, [EMAIL PROTECTED] wrote: I have been planning to test the whole thing with a few .NET applications. I am currently using expat to parse the output to ensure that it all works correcty. That, unfortunately, probably implies that your implementation is almost totally non-interoperable. You should put out of your mind the notion of being correct. Being correct is pretty irrelevant if 80% of the requests that come from a VB.NET client fail because Microsoft implemented part of their request differently than what you interpreted as correct. The point is that correctness isn't the thing you need to aim for; what you should aim for is interoperability with the important client implementations. SOAP::Lite, .NET, probably some Java ones, C++ ones, and such. Nobody does correctness testing; they do interoperability tests where they try to submit requests to Apache AXIS, .NET, WebSphere, and the lot of other important implementations. If you're testing a server (as is the case here), then the point is to run tests with a bunch of clients. Head to the SOAP::Lite and Axis projects; you'll see matrices describing this sort of thing... Hmmm. Can I reiterate my support of XML-RPC here? g -Jay 'Eraserhead' Felice -- (reverse (concatenate 'string ac.notelrac.teneerf@ 454aa)) http://www.ntlug.org/~cbbrowne/advocacy.html Fear leads to anger. Anger leads to hate. Hate leads to using Windows NT for mission-critical applications. --- What Yoda *meant* to say ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] contrib and licensing
mlw wrote: Jan Wieck wrote: [...] screen? We have a pure BSD alternative that we could even ship with our distro, time to retire the libreadline hooks. I certainly didn't want to open up this can of worms, that's for sure. I have an amount of code that is LGPL, I would rather use it than write the bits again or try to extract them from the whole. The actual extension would be BSD, but it would need to link with my library. I made the library LGPL (from GPL) for the PHP group who have similar restrictions. Thus this discussion. If it is your library as you've said multiple times now, there is nothing that can stop you from relicensing it. Give me one good reason why you have to keep your library under the LGPL, or better why it cannot be relicensed under BSD. I do not want to get in to a my license theology is better or more correct than yours discussion. Can we not do that? I have chosen to make the library open source and LGPL. Respect that. I respect that the PG group wants BSD, and everything that I would contribute to PG will be BSD. This issue is the requirement of a third party LGPL library. Ignore for the moment that it is mine. If I find a wiz-bang library that allows me to do something cool very easily, and I write a some code that would be good for postgresql's contrib, are you saying that it would not be usable because of the requirement of the library that is not included on standard system installations? I don't know what the answer is, but to say NO LGPL seems a bit extream, especially if you already have such dependencies. Then if you conclude you do allow LGPL libraries, but then only allow some libraries, not all, then what is the criteria for choosing which libraries get blessed. Is it purely popularity? Not more extreme than I am the only true license, you shall not have any other licenses beside me, which is my personal interpretation of the entire FSF attempt. Let's not bring this into an argument about BSD vs [L]GPL, it isn't about that. Do you guys really think that a contrib function should not be allowed to require code which may not be on a common UNIX/BSD/Linux box? Your library does appear in what Unix distributions? And in which of them is it installed by default like the libreadline? Yes, that certainly has to do with popularity. Did the tools required to build PostgreSQL *always* come standard on systems? Could I currently build all the contrib directories on a RH 6.2 system without any extra libraries? The issue is: Is the requirement of an LGPL library that is more than likely not already on your system a disqualification for a contrib function? This is NOT a BSD vs [L]GPL argument, so lets not make it one. The actual code for the function *will* be BSD or any license you want. ---(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] contrib and licensing
[EMAIL PROTECTED] writes: If I find a wiz-bang library that allows me to do something cool very easily, and I write a some code that would be good for postgresql's contrib, are you saying that it would not be usable because of the requirement of the library that is not included on standard system installations? The issue here is whether PG's contrib directory is the most appropriate distribution mechanism for such code. There are at least two other paths for distribution of PG add-ons: you can make a gborg project, or you can distribute the add-on along with the wiz-bang library it depends on (assuming you can interest the developers of libwizbang, which in this case is presumably not a problem). In either of those cases there's no problem at all with LGPL or GPL license terms. We have taken a policy decision to keep the PG core distribution (including contrib) straight BSD license --- and in my mind that definitely includes not depending on any outside functionality that is both (a) essential and (b) not available anywhere as BSD-license code. It should be possible to build a PG installation that is pure BSD. Whether people actually choose to do so is not the point. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib and licensing
[EMAIL PROTECTED] wrote: The issue is: Is the requirement of an LGPL library that is more than likely not already on your system a disqualification for a contrib function? Yes. Because the requirement of something that is more likely not found on usual installations TOGETHER WITH that it cannot be included per se in a (not necessarily our) PostgreSQL distribution IMNSVHO disqualifies from being added to the official PostgreSQL release. As Tom pointed out, we aren't there yet with the cleanup. But we certainly don't want to add more candidates of that category to contrib. Everything that falls out of contrib can be added to gborg. 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: 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] more contrib: log rotator
Andrew Sullivan [EMAIL PROTECTED] writes: Is anyone interested in having pglog-rotator? FWIW, I saw an early version of pglog-rotator about a year and a half ago (while consulting for LibertyRMS), and thought at the time that it was pretty cool. So I'm for including it ... maybe even as mainstream instead of contrib. 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] contrib and licensing
[EMAIL PROTECTED] writes: We have taken a policy decision to keep the PG core distribution (including contrib) straight BSD license --- and in my mind that definitely includes not depending on any outside functionality that is both (a) essential and (b) not available anywhere as BSD-license code. It should be possible to build a PG installation that is pure BSD. Whether people actually choose to do so is not the point. OK, that is more or less the answer to the question I originally posed. Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib and licensing
On Thursday 03 April 2003 09:29, Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: And its stubs are in the backend, of all places. Really? I must have missed that. On Linux as compiled in Red Hat 9, at least: [EMAIL PROTECTED] lowen]$ ldd /usr/bin/postgres libreadline.so.4 = /usr/lib/libreadline.so.4 (0x401c6000) That's because our build mechanism links *all* needed libraries in *all* executables, rather than trying to distinguish which ones are actually used by each executable. The ldd indication is the only connection to libreadline --- if it had been a statically-linked situation, you'd find no trace of readline (nor several other of these libraries, I suspect) in the backend executable. As I said, its 'stub' is there. But it is in (and used by) psql (as of 7.3.2). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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] contrib and licensing
Lamar Owen [EMAIL PROTECTED] writes: But it is in (and used by) psql (as of 7.3.2). Certainly. I don't see a problem with that as far as the source distribution goes; you can build it with readline, libedit, or neither. Binary distributions are another matter. I think a pretty good case could be made for switching the RPMs to use libedit, if one wants to be morally pristine about license issues. 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
[HACKERS] SQL Query to get Column constraints
Hi there, I have been through the postgres faq's, and read through the describe.c file. I can't seem to solve my problem. I am building an automatic file writer, that gives me information about each of my tables. What I need to know, is how to find out if a column is a primary, or part of a primary key. I need to do this using sql. I am used to oracle, whose structure is different. Can anyone help me. Whiel I am at it, If I can dynamically find out if a column has a sequence attached to it, that would also be useful. Regards Steve ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] gmake does not finish on default slackware 9 (or 8.1) install
Hi all, I have just tried this with the same result on default full install of slackware 8.1 and 9.0. After installation (and download of pgsql 7.3.2) i ran these commands : tar -xzvf postgresql-7.3.2.tar.gz cd postgresql-7.3.2 ./configure (worked fine) gmake gmake then ran into a infinite loop i have piped gmake to a text file which shows where it is looping etc. this can be found at http://www.stratocom.net/imos/pgsql-gmakelog.txt I hope this is able to help someone , any questions, please email me at this address, [EMAIL PROTECTED] yours, Andrew Gemmell __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://platinum.yahoo.com ---(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] Newbie: problem Connecting to Server
what command name would it being running under in my boxes service tree. [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Ferindo Middleton Jr [EMAIL PROTECTED] wrote: I'm running Redhat Linux 8. I have registration to the Redhat Network so I'm probably running the latest version of postgresql available. I also have Redhat Databse v2.1 installed, but whenever I try to start a session, I get the following error message: psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Please help me configure my system so that I can connect and begin to use postgresql. Have you checked to see if it is actually running? I rather expect that it isn't. I think RHAT distributes GUIfied tools to manage services started via init.d, so that even if you are afraid of Unix, you should be able to see what it's doing. And it's quite unlikely that RHAT has packaged the very latest version; the fact that they are numbering things independently is quite irritating as it makes it more difficult to ascertain what version it actually is. -- output = (cbbrowne @acm.org) http://www3.sympatico.ca/cbbrowne/rdbms.html ...while I know many people who emphatically believe in reincarnation, I have never met or read one who could satisfactorily explain population growth. -- Spider Robinson ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] config file, where is it
where is the configuration file located. I'm running redhat linux 8 ---(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] Changing behavior of BEGIN...sleep...do something...COMMIT
Hi, Tom Lane wrote: It seems to me that it'd be fairly easy to make BEGIN cause only a local state change in the backend; the actual transaction need not start until the first subsequent command is received. It's already true that the transaction snapshot is not frozen at BEGIN time, but only when the first DML or DDL command is received; so this would have no impact on the client-visible semantics. But a BEGIN-then- sleep-for-awhile client wouldn't interfere with VACUUM anymore. As I can remember, already, when autocommit off transaction begin with first DML or DDL command. May be better change client to use autocommit off mode? -- Olleg Samojlov ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Newbie: problem Connecting to Server
I'm running Redhat Linux 8. I have registration to the Redhat Network so I'm probably running the latest version of postgre sql available. I also have Redhat Databse v2.1 installed, but whenever I try to start a session, I get the following error message: psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Please help me configure my system so that I can connect and begin to use postgresql. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] contrib and licensing
On Thu, 3 Apr 2003 [EMAIL PROTECTED] wrote: If I find a wiz-bang library that allows me to do something cool very easily, and I write a some code that would be good for postgresql's contrib, are you saying that it would not be usable because of the requirement of the library that is not included on standard system installations? My two-bits here, but we're trying to *move* things out of contrib to gborg, like we have for most of the language interfaces ... why is/should this be any different? I don't care what licence it falls under, new contrib's should be going into gborg, not into the distribution itself ... ---(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] Changing behavior of BEGIN...sleep...do something...COMMIT
Olleg Samojlov [EMAIL PROTECTED] writes: As I can remember, already, when autocommit off transaction begin with first DML or DDL command. May be better change client to use autocommit off mode? We've been waiting for those clients to get fixed for a long while. Waiting for them to adopt autocommit-off seems about as hopeless as waiting for them to postpone BEGIN :-( Also, per other discussions, we are removing backend autocommit support in 7.4. It was the wrong way to do it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] more contrib: log rotator
Would the plan be to add it to pg_ctl? Andrew Sullivan [EMAIL PROTECTED] writes: Is anyone interested in having pglog-rotator? FWIW, I saw an early version of pglog-rotator about a year and a half ago (while consulting for LibertyRMS), and thought at the time that it was pretty cool. So I'm for including it ... maybe even as mainstream instead of contrib. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL Query to get Column constraints
Steve [EMAIL PROTECTED] writes: What I need to know, is how to find out if a column is a primary, or part of a primary key. I need to do this using sql. In 7.3 this is pretty easy: look in the pg_constraint table for a 'p' constraint for the table. If you find one, 'conkey' lists the column numbers of the key columns. In prior versions you'd need to look in pg_index instead. (Actually the pg_index method still works, but it's messier.) See the System Catalogs chapter of the documentation. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] gmake does not finish on default slackware 9 (or 8.1) install
Andrew Gemmell [EMAIL PROTECTED] writes: gmake then ran into a infinite loop Check your system clock. I get the impression that the timestamp of the configure file must be in the future compared to what your system thinks the time is. So, every time it rebuilds config.status, it still finds it to be out-of-date compared to configure ... 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] Detecting corrupted pages earlier
Tom Lane writes: Andrew Sullivan expressed concern about this, too. The thing could be made a little more failsafe if we made it impossible to set ZERO_DAMAGED_PAGES to true in postgresql.conf, or by any means other than an actual SET command --- whose impact would then be limited to the current session. This is kind of an ugly wart on the GUC mechanism, but I think not difficult to do with an assign_hook (it just has to refuse non-interactive settings). Fighting against people who randomly change settings without being informed about what they do is pointless. It's like trying to prevent 'rm -rf /*'. And it's not like you can easily set anything in postgresql.conf by accident. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] gmake does not finish on default slackware 9 (or 8.1)
Andrew Gemmell writes: gmake then ran into a infinite loop i have piped gmake to a text file which shows where it is looping etc. this can be found at http://www.stratocom.net/imos/pgsql-gmakelog.txt Looks like the clock on your system or file system has a serious problem. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] more contrib: log rotator
Jim Buttafuoco [EMAIL PROTECTED] writes: Would the plan be to add it to pg_ctl? You would not actually have to: you could just pipe pg_ctl's output to pglog-rotator. But I think it'd be cool if pg_ctl had an option to use pglog-rotator, or maybe even adopt it as standard behavior. I think we would have to make the rotator script be mainstream rather than contrib if we wanted pg_ctl to use it directly. That was why I was thinking maybe mainstream ... Andrew, could you toss up the script on pgsql-patches just so people can take a look? Then we could think more about where to go with it. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] more contrib: log rotator
Does this log rotator do something that apache's doesn't? Dave On Thu, 2003-04-03 at 13:41, Tom Lane wrote: Jim Buttafuoco [EMAIL PROTECTED] writes: Would the plan be to add it to pg_ctl? You would not actually have to: you could just pipe pg_ctl's output to pglog-rotator. But I think it'd be cool if pg_ctl had an option to use pglog-rotator, or maybe even adopt it as standard behavior. I think we would have to make the rotator script be mainstream rather than contrib if we wanted pg_ctl to use it directly. That was why I was thinking maybe mainstream ... Andrew, could you toss up the script on pgsql-patches just so people can take a look? Then we could think more about where to go with it. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Detecting corrupted pages earlier
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Andrew Sullivan expressed concern about this, too. The thing could be made a little more failsafe if we made it impossible to set ZERO_DAMAGED_PAGES to true in postgresql.conf, or by any means other than an actual SET command --- whose impact would then be limited to the current session. This is kind of an ugly wart on the GUC mechanism, but I think not difficult to do with an assign_hook (it just has to refuse non-interactive settings). Fighting against people who randomly change settings without being informed about what they do is pointless. If you don't want an active defense, how about a passive one --- like just not listing zero_damaged_pages in postgresql.conf.sample? We already have several variables deliberately not listed there ... 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: [PERFORM] [HACKERS] OSS database needed for testing
On Thu, Apr 03, 2003 at 13:26:01 -0500, [EMAIL PROTECTED] wrote: I don't know that it meets your criteria, but. I have a set of scripts and a program that will load the US Census TigerUA database into PostgreSQL. The thing is absolutely freak'n huge. I forget which, but it is either 30g or 60g of data excluding indexes. Are the data model or the loading scripts available publicly? I have the tiger data and a program that uses it to convert addresses to latitude and longitude, but I don't really like the program and was thinking about trying to load the data into a database and do queries against the database to find location. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] more contrib: log rotator
On Thu, Apr 03, 2003 at 01:41:08PM -0500, Tom Lane wrote: You would not actually have to: you could just pipe pg_ctl's output to pglog-rotator. But I think it'd be cool if pg_ctl had an option to use pglog-rotator, or maybe even adopt it as standard behavior. It's currently built to call a program, and read its stdout and stderr, rather than acting as a pipe. I guess it shouldn't be too hard to modify, though. We actually call the postmaster directly with it, so we use it as a replacement for pg_ctl at startup. Andrew, could you toss up the script on pgsql-patches just so people can take a look? Then we could think more about where to go with it. Ok, I sent it. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] more contrib: log rotator
On Thu, Apr 03, 2003 at 02:12:03PM -0500, Dave Cramer wrote: Does this log rotator do something that apache's doesn't? Probably not. This was just easier for us. A little information might be handy here: we run postgres nder a hosted environment, and we do not have root on the relevant boxes. So installing anything even a little complicated means building everything ourselves. As a result, we end up re-creating plenty of functionality just to make it easy to install. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Stuff that doesn't work yet in IPv6 patch
The IPv6 patch seems to still be a few bricks shy of a load. Grepping for places that handle AF_INET but not AF_INET6 revealed these unimplemented features: 1. IDENT authorization. Fails if either local or remote address is IPv6. 2. SSL. Postmaster allows SSL for AF_INET but not AF_INET6. 3. Client address display in backend's ps display seems to be v4 only. 4. pgstat code can only bind to 127.0.0.1 (v4 loopback). On a v6-only machine this would not exist, would it? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Detecting corrupted pages earlier
On Thu, Apr 03, 2003 at 02:39:17PM -0500, Tom Lane wrote: just not listing zero_damaged_pages in postgresql.conf.sample? We already have several variables deliberately not listed there ... Hey, that might be a good solution. Of course, it doesn't solve the doomsday device problem, but nobody who uses it can complain that they didn't know what the thing would do. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Detecting corrupted pages earlier
On 2003-04-02 16:18:33 -0500, Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
Hmm...I don't know that I'd want to go that far -- setting this
variable could be regarded as a policy decision. Some shops may have
very good reason for running with ZERO_DAMAGED_PAGES enabled all the
time, but I don't know what those reasons might be.
I would buy this argument if I could imagine even a faintly plausible
reason for doing that ... but I can't.
regards, tom lane
I've been following this discussion with great interest, because I actually
have a situation where running with ZERO_DAMAGED_PAGES on all the time would
be somewhat plausible.
We use a PostgreSQL database purely for caching pages for a very busy website.
A user changes some stuff which causes a page on the site to change, the HTML
for the new page gets generated using the data from another database
(containing all the actual data) and the generated HTML is inserted into this
PG database. When a page is requested that isn't cached yet, it'll be
generated and inserted too. This makes it possible to invalidate the
cache-version of a large amount of pages by simply deleting the relevant rows
and not spending the time to regenerate all that data immediately (and it
makes crashrecovery more robust).
We can afford to lose all the data in the cache DB, because it's all generated
by using other data anyway. But losing all data would be bad from a
performance/uptime perspective, as all the cached data would need to be
regenerated (which takes a few days). Also, making backups once a day and
restoring such a backup when something goes wrong is also impractical, because
in our situation old data is much worse than no data at all. I'm working on a
script to detect old data and delete it so a new page will be generated, but
that isn't finished yet.
Two weeks ago the server running this database screwed up (it crashes pretty
badly) and made some data unreadable. Although I was running with fsync on on
an ext3 partition (with data=writeback, linux 2.4.20, PG 7.2) some of the PG
datafiles got damaged anyway (I blame IDE disks). The damage seemed light
enough to keep running with this dataset (it occasionally borked with
'heap_delete: (am)invalid tid', but since our application attempts a delete
followed by an insert of newly generated data in case of a db error it would
repair itself most of the time). Two crashes later (weirdly patched kernels
hooray) the errors got progressively worse ('missing chunk number 0 for toast
value 79960605' and stuff like that) so we were forced to shut the website
down, dump all the data we could dump (not everything), initdb and restore
that dump. This cost us about 10 hours downtime. If I'd had the option I just
would've set ZERO_DAMAGED_PAGES to true and let it run for a few days to sort
itself out. Alternatively an ALTER TABLE foo ZERO DAMAGED PAGES; would've
worked as well, although that would create a small downtime too.
I know I'm doing a lot of weird things, and that I could avoid a lot of the
problems listed here were I to do things differently, but the fact remains
that I actually have a real-life situation where running (for a while at
least) with ZERO_DAMAGED_PAGES on makes some kind of sense.
Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Detecting corrupted pages earlier
Vincent van Leeuwen [EMAIL PROTECTED] writes: ... This cost us about 10 hours downtime. If I'd had the option I just would've set ZERO_DAMAGED_PAGES to true and let it run for a few days to sort itself out. Yikes. If I understand this correctly, you had both critical data and cache data in the same database. As for the cache stuff, a few quick TRUNCATE TABLE commands would have gotten you out of the woods. As for the critical data (the stuff you actually needed to dump and restore), do you really want ZERO_DAMAGED_PAGES on for that? It's a heck of a blunt tool. 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] Detecting corrupted pages earlier
On 2003-04-03 18:40:54 -0500, Tom Lane wrote: Vincent van Leeuwen [EMAIL PROTECTED] writes: ... This cost us about 10 hours downtime. If I'd had the option I just would've set ZERO_DAMAGED_PAGES to true and let it run for a few days to sort itself out. Yikes. If I understand this correctly, you had both critical data and cache data in the same database. As for the cache stuff, a few quick TRUNCATE TABLE commands would have gotten you out of the woods. As for the critical data (the stuff you actually needed to dump and restore), do you really want ZERO_DAMAGED_PAGES on for that? It's a heck of a blunt tool. regards, tom lane No, it wasn't that bad :) The REAL data is on a different server which hasn't let us down so far (and has reliable hardware and software, and backups :)). Only the cache database was hurt. The problem with truncating everything was that rebuilding the cache would cost about 48 hours downtime, as there is A LOT of data to rebuild. This really is an interim solution, things will be constructed much better and more reliable in the future, but for now it's there. Another reason we went for the dump/restore is that we upgraded to 7.3.2 at the same time, which we were postponing because weren't looking forward to that downtime :) Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(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] more contrib: log rotator
On Thu, Apr 03, 2003 at 01:41:08PM -0500, Tom Lane wrote: Andrew, could you toss up the script on pgsql-patches just so people can take a look? Then we could think more about where to go with it. Ok, the first try failed (of course) because I wasn't subscribed. Should be there now, though. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4devel auth failed
Tom Lane wrote: I've applied a patch to fix this, but can't try it out here for lack of any IPv6 infrastructure ... please check it. regards, tom lane I tried it, and it works. Regards, Andreas ---(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] [GENERAL] Solution to UPDATE...INSERT problem
At 05:28 PM 3/27/03 +0800, Christopher Kings-Lynne wrote: There's no select * from table where pkey=x for insert; which would block on uncommitted inserts/updates of pkey=x and other selects for insert/update. How about user locks? Isn't there something in contrib/ for that??? I could do a userlock on the primary key, whether it existed or not? Depends on your case, whether you can correctly convert your potential primary keys into integers to be locked on. It still requires full cooperation by all relevant apps/clients. Actually select ... for updates also require cooperation, but it's a standard way of doing things, so apps that don't cooperate can be said to be broken :). Is there a standard for select ... for insert? Or lock table for insert where pkey=x? Regards, Link. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Solution to UPDATE...INSERT problem
so the only real solution to this now is in application code outside of a transatction, i.e. PHP,Perl,VB,C,Python, etc, right? Lincoln Yeoh wrote: AFAIK the except select won't see other inserts in uncommitted transactions. If those transactions are committed you will end up with the same problem. You can try it yourself, by manually doing two separate transactions in psql. You either have to lock the whole table, or lock at the application layer. Some time back I suggested a lock on arbitrary string feature for postgresql for this and various other purposes, but that feature probably wouldn't scale in terms of management (it requires 100% cooperation amongst all apps/clients involved). There's no select * from table where pkey=x for insert; which would block on uncommitted inserts/updates of pkey=x and other selects for insert/update. In contrast select ... for update blocks on committed stuff. Regards, Link. At 09:55 AM 3/27/03 +0800, Christopher Kings-Lynne wrote: Hi Guys, I just thought I'd share with you guys a very clever solution to the old 'update row. if no rows affected, then insert the row' race condition problem. A guy at my work came up with it. We were discussing this earlier on -hackers, but no-one could find a solution that didn't involve locking the entire table around the update...insert commands. The problem is that sometimes the row will be inserted by another process between your update and insert, causing your insert to fail with a unique constraint violation. So, say this is the insert: INSERT INTO table VALUES (1, 'foo'); // 1 is in the primary key column Rewrite it like this: INSERT INTO table SELECT 1, 'foo' EXCEPT SELECT 1, 'foo' FROM table WHERE pkcol=1; See? So now that INSERT statement will insert the row if it doesn't exist, or insert zero rows if it does. You are then guaranteed that your transaction will not fail and rollback, so you can repeat your update, or do the insert first and then the update, etc. Hope that's handy for people, Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] [GENERAL] Solution to UPDATE...INSERT problem
AFAIK the except select won't see other inserts in uncommitted transactions. If those transactions are committed you will end up with the same problem. You can try it yourself, by manually doing two separate transactions in psql. You either have to lock the whole table, or lock at the application layer. Some time back I suggested a lock on arbitrary string feature for postgresql for this and various other purposes, but that feature probably wouldn't scale in terms of management (it requires 100% cooperation amongst all apps/clients involved). There's no select * from table where pkey=x for insert; which would block on uncommitted inserts/updates of pkey=x and other selects for insert/update. In contrast select ... for update blocks on committed stuff. Regards, Link. At 09:55 AM 3/27/03 +0800, Christopher Kings-Lynne wrote: Hi Guys, I just thought I'd share with you guys a very clever solution to the old 'update row. if no rows affected, then insert the row' race condition problem. A guy at my work came up with it. We were discussing this earlier on -hackers, but no-one could find a solution that didn't involve locking the entire table around the update...insert commands. The problem is that sometimes the row will be inserted by another process between your update and insert, causing your insert to fail with a unique constraint violation. So, say this is the insert: INSERT INTO table VALUES (1, 'foo'); // 1 is in the primary key column Rewrite it like this: INSERT INTO table SELECT 1, 'foo' EXCEPT SELECT 1, 'foo' FROM table WHERE pkcol=1; See? So now that INSERT statement will insert the row if it doesn't exist, or insert zero rows if it does. You are then guaranteed that your transaction will not fail and rollback, so you can repeat your update, or do the insert first and then the update, etc. Hope that's handy for people, Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] OSS database needed for testing
Josh Berkus wrote: 1) At least one main table with 12+ columns and 100,000+ rows (each). 2) At least 10-12 additional tables of assorted sizes, at least half of which should have Foriegn Key relationships to the main table(s) or each other. 3) At least one large text or varchar field among the various tables. In addition, the following items would be helpful, but are not required: 4) Views, triggers, and functions built on the database 5) A query log of database activity to give us sample queries to work with. 6) Some complex data types, such as geometric, network, and/or custom data types. Might I recommend the FCC database of transmitters. Its publicly available via anonymous FTP, medium largish with tables running 100k - 1m+ records, and demonstrates many interesting test cases. For example, lat/lon spatial queries (RTree vs. GIST) can be tested with a decent volume. Also it demonstrates a good example of the use of schemas. Email me if you want info. Format is pipe delimited (non quoted), and data turnover is 1% a week. Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] OSS database needed for testing
Folks, Please pardon the cross-posting. A small group of us on the Performance list were discussing the first steps toward constructing a comprehensive Postgresql installation benchmarking tool, mostly to compare different operating systems and file systemsm but later to be used as a foundation for a tuning wizard. To do this, we need one or more real (not randomly generated*) medium-large database which is or can be BSD-licensed (data AND schema). This database must have: 1) At least one main table with 12+ columns and 100,000+ rows (each). 2) At least 10-12 additional tables of assorted sizes, at least half of which should have Foriegn Key relationships to the main table(s) or each other. 3) At least one large text or varchar field among the various tables. In addition, the following items would be helpful, but are not required: 4) Views, triggers, and functions built on the database 5) A query log of database activity to give us sample queries to work with. 6) Some complex data types, such as geometric, network, and/or custom data types. Thanks for any leads you can give me! (* To forestall knee-jerk responses: Randomly generated data does not look or perform the same as real data in my professional opinion, and I'm the one writing the test scripts.) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [HACKERS] OSS database needed for testing
Bruno Wolff III wrote: On Thu, Apr 03, 2003 at 13:26:01 -0500, [EMAIL PROTECTED] wrote: I don't know that it meets your criteria, but. I have a set of scripts and a program that will load the US Census TigerUA database into PostgreSQL. The thing is absolutely freak'n huge. I forget which, but it is either 30g or 60g of data excluding indexes. Are the data model or the loading scripts available publicly? I have the tiger data and a program that uses it to convert addresses to latitude and longitude, but I don't really like the program and was thinking about trying to load the data into a database and do queries against the database to find location. I have a set of scripts, SQL table defs, a small C program, along with a set of field with files that loads it into PGSQL using the copy from stdin It works fairly well, but takes a good long time to load it all. Should I put it in the download section of my website? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] [HACKERS] OSS database needed for testing
On Thu, Apr 03, 2003 at 17:19:13 -0500, mlw [EMAIL PROTECTED] wrote: I have a set of scripts, SQL table defs, a small C program, along with a set of field with files that loads it into PGSQL using the copy from stdin It works fairly well, but takes a good long time to load it all. Should I put it in the download section of my website? Yes. I would be interested in looking at it even if I don't use exactly the same way to do stuff. Taking a logn time to load the data into the database isn't a big deal for me. reading through the tiger (and FIPS) data documentation it seemed like there might be some gotchas in unusual cases and I am not sure the google contest program really handled things right so I would like to see another implementation. I am also interested in the data model as that will save me some time. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dangling backends on win32 7.2.1 port (peerdirect).
Jan Wieck wrote:PeerDirect tried to contribute the Win32 port portion of their work to the open source project. The PostgreSQL global development team has notyet made any final decision if or what parts of that code will or willnot become part of the regular PostgreSQL distribution Bruce Momjian wrote:Actually, I believe we have decided to use the PeerDirect code to do anative Win32 port for 7.4. I just posted my first patch, and will spendmost of the next two months completing the job. Merlin Moncure wrote:There is at least one show-stopping bug which I believe is related tothe shared memory emulation. I am almost 100% sure that the bug is inthe win32 side and if still present will carry over to the latestrelease.I offered to help find and catch it: but before I spend the time to dothat I'd like to at least know if the problem has already beenaddressed. I was responding to the call for capable developers to helptest upcoming patch, posted on this very list about two weeks ago. We already use Psql on Linux via ODBC as a "big compatible MS Access RDBMS", with appropriate functions and operators (great!!). I think that a Win version of Postgres could easily replace this MS product for professional usages, and I can test it to do that. Many Win developers could receive benefits, couldapproach the OS world and Postgres could become more used than MySQL... (thank God!) I renew my aid as beta tester for a Win32 version, when it will be placed on the CVS repository, as already posted. Let me know...
