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] 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: [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: [HACKERS] PostgreSQL and SOAP, suggestions?
Hannu Krosing wrote: mlw kirjutas T, 01.04.2003 kell 15:29: Hannu Krosing wrote: [EMAIL PROTECTED] kirjutas E, 31.03.2003 kell 19:52: Actually, as far as I am aware, the header is for metadata, i.e. it is the place to describe the data being returned. Did you read the SOAP spec ? yes ??? What you have come up with _is_not_ a SOAP v1.1 message at all. It does use some elements with similar names but from different namespace. the SOAP Envelope, Header and Body elemants must be from namespace http://schemas.xmlsoap.org/soap/envelope/ [snip] Hmm, I read SHOULD and MAY in the spec, assuming that it was not MUST are you saying it is invalid if I do not use the SOAP URIs for the name spaces? If so, no big deal, I'll change them. As for defining the namespaces, yea that's easy enough, just tack on an attribute. I still don't see where putting the field definitions in the soap header is an invalid use of that space. ---(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
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: I know nothing in contrib should be GPL, I have no problem with that. The question is the requirement of a GPL library to build a contrib project. My SOAP/XML function will probably require my LGPL library as there is a lot of code I have written that I would need to implement it. If it won't work without your library then there's not much point in putting it into contrib. Might as well just put it in your library and distribute same as you have been doing. I'm a little put off by this attitude, are you saying there are no LGPL dependencies in PostgreSQL or /contrib? If that is a real objective, I'm surprised. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL and SOAP, suggestions?
Hannu Krosing wrote: mlw kirjutas K, 02.04.2003 kell 15:56: Hannu Krosing wrote: What you have come up with _is_not_ a SOAP v1.1 message at all. It does use some elements with similar names but from different namespace. the SOAP Envelope, Header and Body elemants must be from namespace http://schemas.xmlsoap.org/soap/envelope/ [snip] Hmm, I read SHOULD and MAY in the spec, assuming that it was not MUST are you saying it is invalid if I do not use the SOAP URIs for the name spaces? If so, no big deal, I'll change them. AFAICS you can _leave_out_ the namespace, but not put in another, nonconforming namespace. [snip] 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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL and SOAP, suggestions?
Hannu Krosing wrote: [EMAIL PROTECTED] kirjutas E, 31.03.2003 kell 19:52: Actually, as far as I am aware, the header is for metadata, i.e. it is the place to describe the data being returned. Did you read the SOAP spec ? yes The description of the fields isn't the actual data retrieved, so it doesn't belong in the body, so it should go into the header. That is logical, but this is not what the spec tells. This is exactly what the spec calles for. The spec, at least 1.1, says very little about what should not be in the header. For an XML request, it should carry. It is very particular about soap header attributes, but header contents is very flexable. Also the spec requires immediate child elements of SOAP:Header to have full namespace URI's. Yup, that was a bug. And another question - why do you have the namespace MWSSQL defined but never used ? That was part of the same bug as above, it now outputs this: ?xml version = 1.0? mwssql:Envelope xmlns:mwssql=http://www.mohawksoft.com/mwssql/envelope; mwssql:Header exec:sqlupdate cgrpairs set ratio=0 where srcitem=100098670/exec:sql exec:affected2657/exec:affected qry:sqlselect * from ztitles limit 2/qry:sql qry:ROWSET qry:ROW columns=28 t:acdundefined/t:acd t:muzenbrundefined/t:muzenbr t:cat2undefined/t:cat2 t:cat3undefined/t:cat3 t:cat4undefined/t:cat4 t:performerundefined/t:performer t:performer2undefined/t:performer2 t:titleundefined/t:title t:artist1undefined/t:artist1 t:engineerundefined/t:engineer t:producerundefined/t:producer t:labelnameundefined/t:labelname t:catalogundefined/t:catalog t:distributundefined/t:distribut t:releasedundefined/t:released t:origrelundefined/t:origrel t:nbrdiscsundefined/t:nbrdiscs t:sparundefined/t:spar t:minutesundefined/t:minutes t:secondsundefined/t:seconds t:monostereoundefined/t:monostereo t:studioliveundefined/t:studiolive t:availableundefined/t:available t:previewsundefined/t:previews t:pnotesundefined/t:pnotes t:artistidundefined/t:artistid t:datasrcundefined/t:datasrc t:extidundefined/t:extid /qry:ROW /qry:ROWSET /mwssql:Header mwssql:Body ROWSET columns=28 rows=2 ROW ROWID=0 acdP/acd muzenbr68291/muzenbr cat2Performer/cat2 cat3Jazz Instrument/cat3 cat4Guitar/cat4 performerSteve Khan/performer performer2Khan, Steve/performer2 titleEvidence/title artist1/artist1 engineer/engineer producer/producer labelnameNovus/labelname catalog3074/catalog distributBMG/distribut released02/13/1990/released origreln/a/origrel nbrdiscs1/nbrdiscs sparn/a/spar minutes/minutes seconds/seconds monostereoStereo/monostereo studioliveStudio/studiolive availableN/available previews/previews pnotes/pnotes artistid100025343/artistid datasrc1/datasrc extid68291/extid /ROW ROW ROWID=1 acdP/acd muzenbr67655/muzenbr cat2Collection/cat2 cat3Jazz Instrument/cat3 cat4/cat4 performerVarious Artists/performer performer2Various Artists/performer2 titleMetropolitan Opera House Jam Session/title artist1/artist1 engineer/engineer producer/producer labelnameJazz Anthology/labelname catalog550212/catalog distributn/a/distribut released1992/released origreln/a/origrel nbrdiscs1/nbrdiscs sparn/a/spar minutes/minutes seconds/seconds monostereoMono/monostereo studioliveLive/studiolive availableN/available previews/previews pnotes/pnotes artistid100050450/artistid datasrc1/datasrc extid67655/extid /ROW /ROWSET /mwssql:Body /mwssql:Envelope ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] contrib and licensing
I know nothing in contrib should be GPL, I have no problem with that. The question is the requirement of a GPL library to build a contrib project. My SOAP/XML function will probably require my LGPL library as there is a lot of code I have written that I would need to implement it. Is there any sort of philosophical problem with that? ---(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?
That function looks great, but what happens if you need to return 1
million records? Wouldn't you exhaust all the memory in the server? Or
can you stream it somehow?
I have an actual libpq program which performs a query against a server,
and will stream out the XML, so the number of records has very little
affect on efficiency. I think the table2xml function is great for 99% of
all the queries, but for those huge resultsets, I think it may be
problematic.
What do you think?
BTW, I routinely have queries that return millions of rows.
Peter Eisentraut wrote:
mlw writes:
Given a HTTP formatted query:
GET http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2;
The output is entered below.
That looks a lot like the SQL/XML-style output plus a SOAP header. Below
is the output that I get from the SQL/XML function that I wrote. A simple
XSLT stylesheet should do the trick for you.
Btw., I also have an XSLT stylesheet that can make an HTML table out of
this output and I have a table function that can generate a virtual table
from this output.
= select table2xml('select * from products');
?xml version='1.0'?
table
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xsi:noNamespaceSchemaLocation='#' !-- XXX this needs to be fixed --
xsd:schema
xmlns:xsd='http://www.w3.org/2001/XMLSchema'
xmlns:sqlxml='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'
xsd:import
namespace='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'
schemaLocation='http://www.iso-standards.org/mra/9075/2001/12/sqlxml.xsd' /
xsd:simpleType name='peter.pg_catalog.text'
xsd:restriction base='xsd:string'
xsd:maxLength value='MLIT' / !-- XXX needs actual value --
/xsd:restriction
/xsd:simpleType
xsd:simpleType name='INTEGER'
xsd:restriction base='xsd:integer'
xsd:maxInclusive value='2147483647'/
xsd:minInclusive value='-2147483648'/
/xsd:restriction
/xsd:simpleType
xsd:simpleType name='NUMERIC'
xsd:restriction base='xsd:decimal'
xsd:totalDigits value='PLIT'/ !-- XXX needs actual values --
xsd:fractionDigits value='SLIT'/
/xsd:restriction
/xsd:simpleType
xsd:complexType name='RowType'
xsd:sequence
xsd:element name='name' type='peter.pg_catalog.text' nillable='true'/xsd:element
xsd:element name='category' type='INTEGER' nillable='true'/xsd:element
xsd:element name='price' type='NUMERIC' nillable='true'/xsd:element
/xsd:sequence
/xsd:complexType
xsd:complexType name='TableType'
xsd:sequence
xsd:element name='row' type='RowType' minOccurs='0' maxOccurs='unbounded' /
/xsd:sequence
/xsd:complexType
xsd:element name='table' type='TableType' /
/xsd:schema
row
namescrewdriver/name
category3/category
price7.99/price
/row
row
namedrill/name
category9/category
price12.49/price
/row
/table
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL and SOAP, suggestions?
Given a HTTP formatted query: GET http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2; The output is entered below. Questions: Is there a way, without spcifying a binary cursor, to get the data types associated with columns? Right now I am just using undefined, as the ODBC version works. Anyone see any basic improvements needed? ?xml version = 1.0? soap:Envelope xmlns:MWSSQL=http://www.mohawksoft.com/MWSSQL/envelope; soap:Header !-- Fields in set -- Columns count=9 muzenbrundefined/muzenbr discundefined/disc trkundefined/trk songundefined/song artistidundefined/artistid acdundefined/acd trackidundefined/trackid datasrcundefined/datasrc extidundefined/extid /Columns /soap:Header soap:Body ROWSET columns=9 rows=2 ROW ROWID=0 muzenbr424965/muzenbr disc1/disc trk5/trk songWrite My Name In The Groove/song artistid100021391/artistid acdA/acd trackid203429573/trackid datasrc1/datasrc extid203429573/extid /ROW ROW ROWID=1 muzenbr177516/muzenbr disc1/disc trk1/trk songPapa Was A Rolling Stone/song artistid10411/artistid acdP/acd trackid2/trackid datasrc1/datasrc extid2/extid /ROW /ROWSET /soap:Body /soap:Envelope Steve Wampler wrote: On Fri, 2003-03-28 at 14:39, mlw wrote: I was thinking of using SOAP over HTTP as the protocol, and a minimalist version at best. If the people want more let them add it. I have an HTTP service class in my open source library. It would br trivial to accept a SQL query formatted as a GET request, and then execute the query and, using libpq, format the result as XML. It should be simple enough to do. It would be easy. I've done something similar (using ODBC to get to PostgreSQL) - but using a language none of the rest of you are likely to be interested in (Unicon). Works just fine, though the implementation (deliberately, by personal preference) avoids accepting arbitrary SQL statements from SOAP clients, instead forcing the clients to use an RPC interface so I can do sanity checking in the Unicon [which I know better than I know PostgreSQL...] SOAP servers. I, too, opted for a 'minimal-SOAP' implementation. A 'real' implementation boggles the mind. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostgreSQL and SOAP, version 7.4/8.0
I have been working on moving some of my software to a more SOAP
compatible interface. As I was doing it, it occured to me that a generic
function could be written, in PostgreSQL's new function manager that
allows multiple columns to be returned, that is a generic SOAP interface.
All one would need do is define what is expected from the SOAP call in
the CREATE FUNCTION statement. Then the generic SOAP function could
then read what is expected and return the XML/SOAP data as a set of
results as if it were a subquery.
What is needed is an efficient way to find the data types and names from
the functions definition. Does anyone know how to do that?
A small program could also parse a WSDL file and write a CREATE
FUNCTION script for the XML as well.
On the flip side, I am also working on a PostgreSQL SOAP interface,
where one does this:
http://somehost/postgresql?query=select * from table
And a SOAP compatible resultset is returned.
On a more advanced horizon, one should be able to do this:
select * from localtable,
mysoap('http://remotehost/postgresql?query=select * from foo') as soap
where soap.field = localtable.field;
If we can do that, PostgreSQL could fit into almost ANY service
environment. What do you guys think? Anyone want to help out?
---(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, version 7.4/8.0
Jason M. Felice wrote:
First, a SOAP query should be posted in SOAP message format, not using the
query string as you do. Second, I like the idea of calling external SOAP
services, but consider creating a language 'soap' you could do with a CREATE
FUNCTION type thing. e.g.
CREATE FUNCTION "foo" (TEXT) RETURNS INTEGER AS
'http://somewhere.com/path/to/.wsdl', 'foo'
LANGUAGE 'soap';
(hmm, it is unclear if this is what you are suggesting or not...)
Second, I hate SOAP because it is too bloated (have you read the spec(s)?).
If you can support xmlrpc instead, you'll save yourself a lot of headaches.
If you got SOAP working, though, I'd use it. It's more an implementation
thing.
Here's the thing, yes I know there are a "lot" of alternatives to SOAP, all
with varying levels of "being better than SOAP." It still stands that a SOAP
interface would be useful for people.
On Fri, Mar 28, 2003 at 09:01:08AM -0500, mlw wrote:
I have been working on moving some of my software to a more SOAP
compatible interface. As I was doing it, it occured to me that a generic
function could be written, in PostgreSQL's new function manager that
allows multiple columns to be returned, that is a generic SOAP interface.
All one would need do is define what is expected from the SOAP call in
the "CREATE FUNCTION" statement. Then the generic SOAP function could
then read what is expected and return the XML/SOAP data as a set of
results as if it were a subquery.
What is needed is an efficient way to find the data types and names from
the functions definition. Does anyone know how to do that?
A small program could also parse a WSDL file and write a "CREATE
FUNCTION" script for the XML as well.
On the flip side, I am also working on a PostgreSQL SOAP interface,
where one does this:
http://somehost/postgresql?query="select * from table"
And a SOAP compatible resultset is returned.
On a more advanced horizon, one should be able to do this:
select * from localtable,
mysoap('http://remotehost/postgresql?query=select * from foo') as soap
where soap.field = localtable.field;
If we can do that, PostgreSQL could fit into almost ANY service
environment. What do you guys think? Anyone want to help out?
I have no time to volunteer for projects, but what the hell...! It's too
cool. I can't spend much time on it but bounce things off me and I'll
do whatever hacking I can squeeze in. What soap implementation would you
use for the PostgreSQL plugin? libsoap, last I checked, is a wee bit
out of date. And not documented.
I was thinking of using SOAP over HTTP as the protocol, and a minimalist
version at best. If the people want "more" let them add it.
I have an HTTP service class in my open source library. It would br trivial
to accept a SQL query formatted as a GET request, and then execute the query
and, using libpq, format the result as XML. It should be simple enough to
do.
-Jason
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
Justin Clift wrote: Hi everyone, Thinking about the numbering further. Would it be cool to decide on the version numbering of our next release like this: + If it looks like we'll have Win32 and/or PITR recovery in time for the next release, we call it PostgreSQL 8.0 + If not, we call it 7.4 Win32 and PITR are great big features that will take us a long way to the goal of Enterprise suitability. They're worth making some specific marketing/branding efforts about and making a big fuss, that why I'd like to see them in an 8.0 release. Sound feasible? Sounds reasonable, but from a change perspective, the FE/BE protocol, Win32, and PITR, I would say that this is a new PostgreSQL, thus should be 8.0. I thought when WAL was added that warrented a different major version, but hey, that's me. But, if the decision is to go for an 8.0, then it should be reasonable to be a little bit more aggresive about adding features and perhaps a few wish list items. What I mean is, if it is just a minor release, one just expects minor improvements and bug fixes. If it is a major release, then one expects an update of the PostgreSQL vision. So, if the decision is to go with an 8.0, what would you guys say to having a roll call about stuff that is possible and practical and really design PostgreSQL 8.0 as something fundimentally newer than 7.x. 8.0 could get the project some hype. It has been 7x for so many years. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Case insensitivity, and option?
I was at a client office reviewing some code. They use MSSQL and I noticed that: select * from table where field = 'blah'; gave the same results as: select * from table where field = 'BLah'; I was shocked. (a) because I know a lot of my code could be easier to write, and (b) that their code would break on every other database I am aware of. Does anyone know about this? Is it practical/desirable for PostgreSQL to have this as a configuration setting? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] talking to postgresql from C/C++
I read your post, and I am struck by a few things, I am not sure I will answer all your points, but maybe a discussion is in order. I use PostgreSQL with C++ all the time. I actually have a SQL class that abstracts libpq and ODBC, so I'm pretty much past a lot of the how I want to use it stuff. Your first question, how many parameters can you pass in a function? The answer is as many as you have room on your stack. The C and C++ stack frames are simple push based. There are no hard set limits. Your desired structure based API is all well and good, but it is largely impractical. One would need a SQL aware preprocessor that parses the SQL query, interprets the results, and creates a conversion routine for the structure you wish to use, and warns you when it can't work. If you have to specify the conversion routine for each structure, I bet you would end up doing more work for your easier API. Yes, SQL database interfacing is tedious. There is some plain and simple drudgery involved with communicating with one. Whether it is ODBC, libpq, Oracle OCI, it doesn't matter, you just got to do some of the interface coding yourself. It is yucky but it is the nature of the beast. Your concern about going from ASCII to binary and back are valid in a sense, but not too critical. SQL databases are not fast. The time spent going from ASCII to binary at the server for an insert is fairly trivial when compared to the transactional overhead of the insert. On a query, one can use a binary cursor. If you want to load a lot of data into the database in one function call, look at PostgreSQL's COPY command. It is almost trivial to write a routine that sets up a copy, reads from a file, translates accordingly, and writes it to the database. Aside from the aesthetic objections, do you have any real can't do issues with PostgreSQL, or SQL in general? [EMAIL PROTECTED] wrote: I've been trying to get information on a programming interface to the database. Over the last while Dave Page and I have emailed each other. Dave has suggested I join the hacker maillist. Thus I am forwarding the communication I sent to Dave. I hope this is approriated in this channel. If not please advise where I should go. I'll look forward to some clarification about what is or is not available by way of talking to the database from languages like C. Thanks. Terrell Larson --3Pql8miugIZX0722 Content-Type: message/rfc822 Content-Disposition: inline Date: Fri, 7 Mar 2003 04:18:45 -0700 From: terr To: Dave Page [EMAIL PROTECTED] Subject: Re: Fwd: ODBC docs Message-ID: [EMAIL PROTECTED] References: [EMAIL PROTECTED] Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: [EMAIL PROTECTED]; from [EMAIL PROTECTED] on Fri, Mar 07, 2003 at 08:49:00AM - Over at M$, I see the following: * SQQLAllocConnect Function * SQLAllocEnv Function * SQLAllocHandle Function * SQLAllocStmt Function * SQLBindCol Function * SQLBindParameter Function * SQLBrowseConnect Function * SQLBulkOperations Function * SQLCancel Function * SQLCloseCursor Function * SQLColAttribute Function * SQLColAttributes Function * SQLColumnPrivileges Function * SQLColumns Function * SQLConnect Function * SQLCopyDesc Function * SQLDataSources Function * SQLDescribeCol Function * SQLDescribeParam Function * SQLDisconnect Function * SQLDriverConnect Function * SQLDrivers Function * SQLEndTran Function * SQLError Function * SQLExecDirect Function * SQLExecute Function * SQLExtendedFetch Function * SQLFetch Function * SQLFetchScroll Function * SQLForeignKeys Function * SQLFreeConnect Function * SQLFreeEnv Function * SQLFreeHandle Function * SQLFreeStmt Function * SQLGetConnectAttr Function * SQLGetConnectOption Function * SQLGetCursorName Function * SQLGetData Function * SQLGetDescField Function * SQLGetDescRec Function * SQLGetDiagField Function * SQLGetDiagRec Function * SQLGetEnvAttr Function * SQLGetFunctions Function * SQLGetInfo Function * SQLGetStmtAttr Function * SQLGetStmtOption Function * SQLGetTypeInfo Function * SQLMoreResults Function * SQLNativeSql Function * SQLNumParams Function * SQLNumResultCols Function * SQLParamData Function * SQLParamOptions Function * SQLPrepare Function * SQLPrimaryKeys Function * SQLProcedureColumns Function * SQLProcedures Function * SQLPutData Function * SQLRowCount Function * SQLSetConnectAttr Function * SQLSetConnectOption Function * SQLSetCursorName Function * SQLSetDescField Function * SQLSetDescRec Function * SQLSetEnvAttr Function * SQLSetParam Function * SQLSetPos Function * SQLSetScrollOptions Function * SQLSetStmtAttr Function * SQLSetStmtOption Function *
[HACKERS] What's up with www.postgresql.org?
I haven't been able to get to it all morning. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Aggregate rollup
Merlin Moncure wrote:
-Original Message-
From: mlw [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 3:47 PM
To: [EMAIL PROTECTED]
Subject: [HACKERS] Aggregate rollup
I had written a piece of code about two years ago that used the
aggregate feature of PostgreSQL to create an array of integers from an
aggregate, as:
select int_array_aggregate( column ) from table group by column
Do I understand correctly that this still follows the normal rules for
grouping, so that only like values are put in the array?
Example: column has values 1,1,1,2,2 spread over 5 rows.
Your query returns two rows with row1={1,1,1} and row2 = {2,2}...is this
correct?
Actually, it is more intended to put all the entries in a one to many
table in a single column, as:
create table classic_one_to_many
(
leftsideinteger,
rightsideinteger
);
create table fast_lookup as select leftside,
int_array_aggregate(rightside) from classic_one_to_many group by leftside;
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] analyze after a database restore?
I just dumped and restored a rather large database, I upgraded from 7.2.x to 7.3.x. When I went to test my application against the new database, it was dog slow. It had all the indexes, and looked fine. Then it dawned on me, Doh! ANALYZE! Should pg_dump appened an ANALYZE for each table? On small tables, this shouldn't take too long. On large tables, you're gonna have to do it anyway. I guess it could be an option as well. It just seems like on of the tasks that is required for a restored database to work properly, and as such, should probably be specified in the backup procedure. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] analyze after a database restore?
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: Should pg_dump appened an ANALYZE for each table? A single ANALYZE at the end of the script would be sufficient. I'm not sure that pg_dump should do this automatically though. If you're not done restoring then it's mostly a waste of cycles, and how is pg_dump to know that? I do note that the docs are rather stingy with this important bit of knowhow :-( Neither of the obvious places that I looked in (pg_dump reference page and admin guide's backup/restore chapter) mention the need to issue an ANALYZE after completing a restore. I'm pretty sure it is mentioned *somewhere* ;-) ... but it needs to be more prominent. While these are all comforting points, I *know* about analyze and I occasionally forget. It just seems like a nessisary step after restoring a backup. Conceptually, one could consider it just as important as an index, i.e. the system will perform poorly without it. From an ease of use perspective, it would be one less step. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] analyze after a database restore?
D'Arcy J.M. Cain wrote: On Thursday 27 February 2003 13:12, mlw wrote: Tom Lane wrote: A single ANALYZE at the end of the script would be sufficient. I'm not sure that pg_dump should do this automatically though. If you're not done restoring then it's mostly a waste of cycles, and how is pg_dump to know that? [...] From an ease of use perspective, it would be one less step. Why not have pg_dump emit a friendly reminder? The reminder won't work, because the backup may be happening in an automatic fashion, and anything but error messages will be lost. I dislike having to have an expert be present at the database restore phase of operation. Suppose a company loses the PG admin and a reasonably experienced person takes his or her place temporarily, This scenario happens all the time with all sorts of projects. A reasonably experienced person will be able to accomplish a DB restore but will probably not know about performing an analyze. Under the pressure of restoring after a crash on a live system, even a reasonably experienced PG admin may forget, hell I forgot and I've been using PG since 1997. The correct view of a database backup should be to include the statistics of the database as it existed at the time backup, these statistics are part of this state snapshot because the directly affect the operation of the database. I do not want to evoke the name of Larry's evil product, but it saves its statistics when the data is exported. Short of including the relevant statistics, there should be an option on pg_dump to emit an ANALYZE; at the end of a database dump. This will allow a knowledgeable admin to selectively add the vacuum so that someone possibly less qualified than he can do the restore. Does anyone disagree that a query's explain should look the same or better after a successful restore? From a product QA point of view, if a valid backup set, when restored, does not recreate the system in a state at least as efficient and workable as the system when it was backed up, you did not have a successful restore. Any QA department would rate this as a serious bug. Are there any reasons why it should not be an option on pg_dump? ---(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] ILIKE
I don't understand why you would want to remove a working feature. Even if they are features which you do not like, why remove them? One of the things about the PostgreSQL core team that troubles me is a fairly arbitrary feature selection process. It seems a feature has to be liked by someone for inclusion. I am often taken by surprise by how you guys judge what the PostgreSQL usership wants or needs based on your own perspective, and if someone uses it differently, the reaction is fierce resistance. The issue seems to be that there is some sort of feature phobia. Why remove ILIKE? Why not just document an alternative for higher performance? Why can't you guys allow features even though you don't necessarily agree? Yes, absolutely, assure the quality and accuracy of the feature, but just ease up on the resistance. Allow things even though you don't see the usefulness. Keep features even though you don't agree with them. One of the benefits of open source is the inclusiveness of contribution. The plurality of development. The ability to harness the experience and work of people around the world. People with different objectives and perspectives than yours. In Open Source, the attitude should not be do we want this feature? but can we add/keep this without affecting anything else? The first argument is based on the assumption you know what everyone wants or needs, which is preposterous, the second argument is based on how well you know the PostgreSQL code and structure, which is a far more reasonable position. ---(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] ILIKE
I am not familiar with ILIKE, but I suspect that if people are moving from a platfrom on which it exists, or even creatingmulti-platform applications, there may be a substancial amount of code that may use it. Peter Eisentraut wrote: AFAICT, ILIKE cannot use an index. So why does ILIKE even exist, when lower(expr) LIKE 'foo' provides a solution that can use an index and is more standard, too? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] The last configuration file patch (I hope!) This one does it all.
== NULL)
diff -u -r postgresql-7.3.2/src/backend/postmaster/postmaster.c
postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c
--- postgresql-7.3.2/src/backend/postmaster/postmaster.cWed Jan 15 19:27:17
2003
+++ postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c Tue Feb 18 18:38:11
2003
@@ -361,6 +361,7 @@
int status;
charoriginal_extraoptions[MAXPGPATH];
char *potential_DataDir = NULL;
+ char *potential_pidfile = NULL;
*original_extraoptions = '\0';
@@ -421,7 +422,7 @@
opterr = 1;
- while ((opt = getopt(argc, argv, A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) != -1)
+ while ((opt = getopt(argc, argv, A:a:B:b:C:c:D:d:Fh:ik:lm:MN:no:p:R:Ss-:))
!= -1)
{
switch (opt)
{
@@ -441,6 +442,9 @@
case 'b':
/* Can no longer set the backend executable file to
use. */
break;
+ case 'C': // MLW
+ explicit_pgconfig = optarg;
+ break;
case 'D':
potential_DataDir = optarg;
break;
@@ -505,6 +509,11 @@
case 'p':
SetConfigOption(port, optarg, PGC_POSTMASTER,
PGC_S_ARGV);
break;
+ case 'R': /*
+* Write PID to a file for FHS compatibility
+*/
+ potential_pidfile = optarg;
+ break;
case 'S':
/*
@@ -564,14 +573,40 @@
ExitPostmaster(1);
}
+ if(explicit_pgconfig)
+ {
+ ProcessConfigFile(PGC_POSTMASTER);
+ if(!potential_DataDir pgdatadir)
+ potential_DataDir = pgdatadir;
+ checkDataDir(potential_DataDir);/* issues error messages */
+ SetDataDir(potential_DataDir);
+ }
+ else
+ {
+ /*
+* Now we can set the data directory, and then read postgresql.conf.
+*/
+ checkDataDir(potential_DataDir);/* issues error messages */
+ SetDataDir(potential_DataDir);
+ ProcessConfigFile(PGC_POSTMASTER);
+ }
+
/*
-* Now we can set the data directory, and then read postgresql.conf.
+* Write Postmaster's PID for this systems that
+* want it.
*/
- checkDataDir(potential_DataDir);/* issues error messages */
- SetDataDir(potential_DataDir);
-
- ProcessConfigFile(PGC_POSTMASTER);
-
+ if(potential_pidfile == NULL)
+ potential_pidfile = runtime_pidfile;
+
+ if(potential_pidfile)
+ {
+ FILE *fpidfile = fopen(potential_pidfile, w);
+ if(fpidfile)
+ {
+ fprintf(fpidfile, %d\n, MyProcPid);
+ fclose(fpidfile);
+ }
+ }
/*
* Check for invalid combinations of GUC settings.
*/
diff -u -r postgresql-7.3.2/src/backend/utils/misc/guc-file.c
postgresql-7.3.2.ec/src/backend/utils/misc/guc-file.c
--- postgresql-7.3.2/src/backend/utils/misc/guc-file.c Mon Feb 3 15:22:34 2003
+++ postgresql-7.3.2.ec/src/backend/utils/misc/guc-file.c Mon Feb 17 15:05:58
2003
@@ -2,7 +2,6 @@
/* Scanner skeleton version:
* $Header: /home/daffy/u0/vern/flex/RCS/flex.skl,v 2.91 96/09/10 16:58:48 vern Exp $
- * $FreeBSD: src/usr.bin/lex/flex.skl,v 1.4 1999/10/27 07:56:44 obrien Exp $
*/
#define FLEX_SCANNER
@@ -10,6 +9,7 @@
#define YY_FLEX_MINOR_VERSION 5
#include stdio.h
+#include unistd.h
/* cfront 1.2 defines c_plusplus instead of __cplusplus */
@@ -23,7 +23,6 @@
#ifdef __cplusplus
#include stdlib.h
-#include unistd.h
/* Use prototypes in function declarations. */
#define YY_USE_PROTOS
@@ -443,7 +442,7 @@
char *GUC_scanstr(char *);
#define YY_NEVER_INTERACTIVE 1
#define YY_NO_UNPUT 1
-#line 447 lex.GUC_yy.c
+#line 446 lex.GUC_yy.c
/* Macros after this point can all be overridden by user definitions in
* section 1.
@@ -591,13 +590,13 @@
YY_DECL
{
register GUC_yy_state_type GUC_yy_current_state;
- register char *GUC_yy_cp, *GUC_yy_bp;
+ register char *GUC_yy_cp = NULL, *GUC_yy_bp = NULL;
register int GUC_yy_act;
#line 71 guc-file.l
-#line 601 lex.GUC_yy.c
+#line 600 lex.GUC_yy.c
if ( GUC_yy_init )
{
@@ -738,7 +737,7 @@
#line 86 guc-file.l
ECHO;
YY_BREAK
-#line 742 lex.GUC_yy.c
+#line 741 lex.GUC_yy.c
case YY_STATE_EOF(INITIAL):
GUC_yyterminate();
@@ -1302,11
[HACKERS] new Configuration patch, implements 'include'
This is a patch that allows PostgreSQL to use a configuration
file that is outside the main database directory.
It adds one more command line parameter, -C which
specifies the location of the postgres configuration file.
A patched version of PostgreSQL will function as:
postmaster -C /etc/postgres/postgresql.conf
This will direct the postmaster program to use the
configuration file /etc/postgres/postgresql.conf
Within this file are four additional parameters: include,
hba_conf,ident_conf, and data_dir.
They are used as:
include = '/etc/postgres/debug.conf'
data_dir = '/vol01/postgres'
hba_conf = '/etc/postgres/pg_hba_conf'
ident_conf = '/etc/postgres/pg_ident.conf'
The -D option on the command line overrides the data_dir
in the configuration file.
If no hba_conf and/or ident_conf setting is specified, the default
$PGDATA/pg_hba.conf and/or $PGDATA/pg_ident.conf will be used.
This patch is intended to move the PostgreSQL configuration out of the
data directory so that it can be modified and backed up.
This patch is also useful for running multiple servers with the same
parameters:
postmaster -C /etc/postgres/postgresql.conf -D /VOL01/postgres -p 5432
postmaster -C /etc/postgres/postgresql.conf -D /VOL02/postgres -p 5433
To apply the patch, enter your PostreSQL source directory, and run:
cat pgec-PGVERSON.patch | patch -p 1
diff -u -r postgresql-7.3.2/src/backend/libpq/hba.c
postgresql-7.3.2.ec/src/backend/libpq/hba.c
--- postgresql-7.3.2/src/backend/libpq/hba.cSat Dec 14 13:49:43 2002
+++ postgresql-7.3.2.ec/src/backend/libpq/hba.c Mon Feb 17 09:30:15 2003
@@ -35,6 +35,7 @@
#include miscadmin.h
#include nodes/pg_list.h
#include storage/fd.h
+#include utils/guc.h
#define IDENT_USERNAME_MAX 512
@@ -837,10 +838,20 @@
if (hba_lines)
free_lines(hba_lines);
- /* Put together the full pathname to the config file. */
- bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char);
- conf_file = (char *) palloc(bufsize);
- snprintf(conf_file, bufsize, %s/%s, DataDir, CONF_FILE);
+ /* Explicit HBA in config file */
+ if(explicit_hbafile strlen(explicit_hbafile))
+ {
+ bufsize = strlen(explicit_hbafile)+1;
+ conf_file = (char *) palloc(bufsize);
+ strcpy(conf_file, explicit_hbafile);
+ }
+ else
+ {
+ /* put together the full pathname to the config file */
+ bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char);
+ conf_file = (char *) palloc(bufsize);
+ snprintf(conf_file, bufsize, %s/%s, DataDir, CONF_FILE);
+ }
file = AllocateFile(conf_file, r);
if (file == NULL)
@@ -979,10 +990,20 @@
if (ident_lines)
free_lines(ident_lines);
- /* put together the full pathname to the map file */
- bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char);
- map_file = (char *) palloc(bufsize);
- snprintf(map_file, bufsize, %s/%s, DataDir, USERMAP_FILE);
+ /* Explicit IDENT in config file */
+ if(explicit_identfile strlen(explicit_identfile))
+ {
+ bufsize = strlen(explicit_identfile)+1;
+ map_file = (char *) palloc(bufsize);
+ strcpy(map_file, explicit_identfile);
+ }
+ else
+ {
+ /* put together the full pathname to the map file */
+ bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char);
+ map_file = (char *) palloc(bufsize);
+ snprintf(map_file, bufsize, %s/%s, DataDir, USERMAP_FILE);
+ }
file = AllocateFile(map_file, r);
if (file == NULL)
diff -u -r postgresql-7.3.2/src/backend/postmaster/postmaster.c
postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c
--- postgresql-7.3.2/src/backend/postmaster/postmaster.cWed Jan 15 19:27:17
2003
+++ postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c Mon Feb 17 09:30:15
+2003
@@ -421,7 +421,7 @@
opterr = 1;
- while ((opt = getopt(argc, argv, A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) != -1)
+ while ((opt = getopt(argc, argv, A:a:B:b:C:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) !=
+-1)
{
switch (opt)
{
@@ -441,6 +441,9 @@
case 'b':
/* Can no longer set the backend executable file to
use. */
break;
+ case 'C': // MLW
+ explicit_pgconfig = optarg;
+ break;
case 'D':
potential_DataDir = optarg;
break;
@@ -564,13 +567,23 @@
ExitPostmaster(1);
}
- /*
-* Now we can set the data directory, and then read postgresql.conf.
-*/
- checkDataDir
Re: [HACKERS] new Configuration patch, implements 'include'
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: If no hba_conf and/or ident_conf setting is specified, the default $PGDATA/pg_hba.conf and/or $PGDATA/pg_ident.conf will be used. Doesn't anybody see the (a) inconsistency and (b) uselessness of this? If you are trying to keep your config files out of the data directory, it's hardly sensible to default to finding two out of three there. We should have a -C that specifies a *directory*, and all three config files should be sought therein. The argument that that somehow forces people to use symlinks doesn't convince me at all. But I've grown tired of arguing, because it's clear that I'm making no impact whatever :-(. I'm done with this thread. Tom, I don't know why you are arguing at all. One thing I wish to impress on you, I think it is a point of view you are missing. It isn't about something being easier as much as it is about being flexable enough to fit into the deployment strategy of the admin or vendor. Sometimes you make things more difficult when you make it more standardized. When I setup a system with Apache, PHP, PostgreSQL, named, et al, I am always just irritated that PostgreSQL's configuration parameters can not be stored with all the others. I usually make one install tarball or zip that contains all the binaries and configuration. I can't do that with PostgreSQL. I don't like the idea of specifying a directory, per se' because if you have multiple database installations, how would you share the configuration without symlinks? I will modify my patch to check if the configuration parameter is a directory. If it is, it will make the default filenames within the directory and post it when it is gone. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: I would favor a setup that allows a -C *directory* (not file) to be specified as a postmaster parameter separately from the -D directory; A directory is not going to satisfy people. Why not? Who won't it satisfy, and what's their objection? AFAICS, you can either set -C to /etc if you want your PG config files loose in /etc, or you can set it to /etc/postgresql/ if you want them in a privately-owned directory. Which other arrangements are needed? The idea of using a directory puts us back to using symlinks to share files. While I know the core development teams thinks that symlinks are a viable configuration option, most admins, myself included, do not like to use symlinks because they do not have the ability to carry documentation, i.e. comments in a configuration file, and are DANGEROUS in a production environment. Any configuration strategy that depends on symlinks is inadequate and poorly designed. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: The idea of using a directory puts us back to using symlinks to share files. So? If you want to share files, you're probably sharing all three config files and don't need a separate directory at all. This is not a sufficient argument to make me buy into the mess of letting people choose nonstandard configuration file names --- especially when most of the opposite camp seems to be more interested in choosing *standard* names for things. Why does that policy stop short at the directory name? symlinks suck. Sorry Tom, but they are *BAD* in a production server. You can not add comments to symlinks. Most of the admins I know, myself included, HATE symlinks and use them as a last resort. Requiring symlinks is just pointless, we are talking about a few lines of code hat has nothing to do with performance. The patch that I submitted allows PostgreSQL to work as it always has, but adds the ability for a configuration file to do what is normally done with fixed names in $PGDATA. I have said before, I do not like policy, I like flexibility, forcing a directory is similarly restricting as requiring the files in $PGDATA. Why is this such a problem? MANY people want to configure PostgreSQL this way, but the patch I submitted allows it, but does not force anything. Any configuration solution that requires symlinks is flawed. ---(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] location of the configuration files
Martin Coxall wrote: On Thu, 2003-02-13 at 20:28, Steve Crawford wrote: I don't see why we can't keep everyone happy and let the users choose the setup they want. To wit, make the following, probably simple, changes: 1) Have postgresql default to using /etc/postgresql.conf /etc/postgres/postgresql.conf, if we want to be proper FHS-bitches. 2) Add a setting in postgresql.conf specifying the data directory 3) Change the meaning of -D to mean use this config file 4) In the absence of a specified data directory in postgresql.conf, use the location of the postgresql.conf file as the data directory Shouldn't it in that case default to, say /var/lib/postgres? I would really like to push back this whole discussion to adding the ability the flexibility to configure PostgreSQ as opposed to determining a specific configuration strategy. Adding the ability is easy. Let the distros determine their strategy. Trying to enforce one way over another will make this continue on forever and will never be solved. ---(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] location of the configuration files
Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Curt Sampson) wrote: On Wed, 12 Feb 2003, Peter Bierman wrote: What do you gain by having the postmaster config and the database data live in different locations? You can then standardize a location for the configuration files. Everybody has room in /etc for another 10K of data. Where you have room for something that might potentially be a half terrabyte of data, and is not infrequently several gigabytes or more, is pretty system-depenendent. Ah, but this has two notable problems: 1. It assumes that there is "a location" for "the configuration files for /the single database instance./" If I have a second database instance, that may conflict. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. These conditions have both been known to occur... These are not issues at all. You could put the configuration file anywhere, just as you can for any UNIX service. postmaster --config=/home/myhome/mydb.conf I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. Configuring postgresql via a configuration file which specifies all the data, i.e. data directory, name of other configuration files, etc. is the right way to do it. Even if you have reasons against it, even if you think it is a bad idea, a bad standard is almost always a better solution than an arcane work of perfection. Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. I just don't understand what the resistance is, it makes no sense.
Re: [HACKERS] location of the configuration files
Robert Treat wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. find / -name postgresql.conf -print LOL, That is NOT an option. It can take hours on some systems. Specifically, one of the systems is freedb server, it has over 300,000 files in a directory tree.
Re: [HACKERS] location of the configuration files
Bruno Wolff III wrote: On Thu, Feb 13, 2003 at 09:23:20 -0500, mlw [EMAIL PROTECTED] wrote: Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. If you are interested in reading a contrary position, you can read Berstein's arguments for his recommended way to install services at: http://cr.yp.to/unix.html Where, specificaly are his arguements against a configuration file methodology?
Re: [HACKERS] location of the configuration files
Stephan Szabo wrote: On Thu, 13 Feb 2003, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 09:23, mlw wrote: I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. You realize that the actual code feature doesn't necessarily help this case, right? Putting configuration in /etc and having a configuration file option on the command line are separate concepts. I think the feature is worthwhile, but I have some initial condition functionality questions that may have been answered in the previous patch, but I don't remember at this point. Mostly these have to deal with initial creation. Does the user specify an output location to initdb, do they just specify a data dir as now where the configuration goes but then they need to move it somewhere, does initdb now do nothing relating to configuration file and the user should make one on his own. Related, is the admin expected to have already made (say) /etc/postgresql to stick the config in and set the permissions correctly (since initdb doesn't run as root)? My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' The command line option is a capital 'C,' as in: postmaster -C /etc/postgresql.conf I have no problem leaving the default configuration files remaining in the data directory as sort of a maintenance / boot strap sort of thing, so I don't see any reason to alter the installation. As for this feature helping or not, I think it will. I think it accomplishes two things: (1) Separates configuration from data. (2) Allows an administrator to create a convention across multiple systems regardless of the location and mount points of the database storage. (3) Lastly, it is a familiar methodology to DBAs not familiar with PostgreSQL. Again, I don't see a valid reason for not including the patch. Yes, if you don't want to configure PostgreSQL that way, then so be it, but why not add the functionality for those who do? I can envision the configuration file methodology of managing a database becoming the "preferred" approach over time as it is a more familiar and standard way of configuring servers on UNIX.
Re: [HACKERS] location of the configuration files
scott.marlowe wrote: These are not issues at all. You could put the configuration file anywhere, just as you can for any UNIX service. postmaster --config=/home/myhome/mydb.conf I deal with a number of PG databases on a number of sites, and it is a real pain in the ass to get to a PG box and hunt around for data directory so as to be able to administer the system. What's really annoying is when you have to find the data directory when someone else set up the system. Really? I would think it's easier to do this: su - pgsuper cd $PGDATA pwd Than to try to figure out what someone entered when they ran ./configure --config=... Why do you think PGDATA would be set for root? Configuring postgresql via a configuration file which specifies all the data, i.e. data directory, name of other configuration files, etc. is the right way to do it. Even if you have reasons against it, even if you think it is a bad idea, a bad standard is almost always a better solution than an arcane work of perfection. Wrong, I strongly disagree with this sentament. Conformity to standards for simple conformity's sake is as wrong as sticking to the old way because it's what we're all comfy with. It isn't conformity for conformitys sake. It is following an established practice, like driving on the same side of the road or stopping at red lights. Personally, however, I think the configuration issue is a no-brainer and I am amazed that people are balking. EVERY other service on a UNIX box is configured in this way, why not do it this way in PostgreSQL? The patch I submitted allowed the configuration to work as it currently does, but allowed for the more standard configuration file methodology. If I do a .tar.gz install of apache, I get /usr/local/apache/conf, which is not the standard way you're listing. If I install openldap from .tar.gz, I get a /usr/local/etc/openldap directory, close, but still not the same. The fact is, it's the packagers that put things into /etc and whatnot, and I can see the postgresql RPMs or debs or whatever having that as the default, but for custom built software, NOTHING that I know of builds from source and uses /etc without a switch to tell it to, just like postgresql can do now. You are confusing the default location of a file with the ability to use the file. The default I have proposed all along was to use the existing practice of keeping everything in the $PGDATA directory. The change I wish to make to the code allows this to be changed. Most admins want configuration and data separate. Most admins do not want to use symlinks because they are dangerous in a production environment. I would rather have a simpler solution sooner than a perfect solution never.
[HACKERS] Configuration file patch
This patch allows using PostgreSQL with a command line configuration
file parameter instead of the data directory.
If no configuration is specified, postmaster operates as it always has.
The configuration file is specified with the -C parameter, as:
postmaster -C /somepath/somefile.conf
Within the configuration file, there are three new parameters: data_dir,
hba_conf, and ident_conf. They are used as:
hba_conf = 'pathanme_to_pg_hba.conf'
ident_conf='pathname_to_pg_ident.conf'
data_dir='path_to_data'
If the above parameters are not specified, then the default is to look
for these file in the PGDATA directory.
Command line arguments take precedent over configuration file.
This patch is not a be-all end-all of configuration. It should be able
to fit PostgreSQL into a FHS with the exception of the '/var/run'
requirement.
diff -u -r postgresql-7.3.2/src/backend/libpq/hba.c
postgresql-7.3.2.ec/src/backend/libpq/hba.c
--- postgresql-7.3.2/src/backend/libpq/hba.cSat Dec 14 13:49:43 2002
+++ postgresql-7.3.2.ec/src/backend/libpq/hba.c Thu Feb 13 12:15:16 2003
@@ -35,6 +35,7 @@
#include miscadmin.h
#include nodes/pg_list.h
#include storage/fd.h
+#include utils/guc.h
#define IDENT_USERNAME_MAX 512
@@ -837,10 +838,20 @@
if (hba_lines)
free_lines(hba_lines);
- /* Put together the full pathname to the config file. */
- bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char);
- conf_file = (char *) palloc(bufsize);
- snprintf(conf_file, bufsize, %s/%s, DataDir, CONF_FILE);
+ /* Explicit HBA in config file */
+ if(explicit_hbafile strlen(explicit_hbafile))
+ {
+ bufsize = strlen(explicit_hbafile)+1;
+ conf_file = (char *) palloc(bufsize);
+ strcpy(conf_file, explicit_hbafile);
+ }
+ else
+ {
+ /* put together the full pathname to the config file */
+ bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char);
+ conf_file = (char *) palloc(bufsize);
+ snprintf(conf_file, bufsize, %s/%s, DataDir, CONF_FILE);
+ }
file = AllocateFile(conf_file, r);
if (file == NULL)
@@ -979,10 +990,20 @@
if (ident_lines)
free_lines(ident_lines);
- /* put together the full pathname to the map file */
- bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char);
- map_file = (char *) palloc(bufsize);
- snprintf(map_file, bufsize, %s/%s, DataDir, USERMAP_FILE);
+ /* Explicit IDENT in config file */
+ if(explicit_identfile strlen(explicit_identfile))
+ {
+ bufsize = strlen(explicit_identfile)+1;
+ map_file = (char *) palloc(bufsize);
+ strcpy(map_file, explicit_identfile);
+ }
+ else
+ {
+ /* put together the full pathname to the map file */
+ bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char);
+ map_file = (char *) palloc(bufsize);
+ snprintf(map_file, bufsize, %s/%s, DataDir, USERMAP_FILE);
+ }
file = AllocateFile(map_file, r);
if (file == NULL)
diff -u -r postgresql-7.3.2/src/backend/postmaster/postmaster.c
postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c
--- postgresql-7.3.2/src/backend/postmaster/postmaster.cWed Jan 15 19:27:17
2003
+++ postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c Thu Feb 13 22:53:08
+2003
@@ -421,7 +421,7 @@
opterr = 1;
- while ((opt = getopt(argc, argv, A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) != -1)
+ while ((opt = getopt(argc, argv, A:a:B:b:C:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) !=
+-1)
{
switch (opt)
{
@@ -441,6 +441,9 @@
case 'b':
/* Can no longer set the backend executable file to
use. */
break;
+ case 'C': // MLW
+ explicit_pgconfig = optarg;
+ break;
case 'D':
potential_DataDir = optarg;
break;
@@ -564,13 +567,23 @@
ExitPostmaster(1);
}
- /*
-* Now we can set the data directory, and then read postgresql.conf.
-*/
- checkDataDir(potential_DataDir);/* issues error messages */
- SetDataDir(potential_DataDir);
-
- ProcessConfigFile(PGC_POSTMASTER);
+ if(explicit_pgconfig)
+ {
+ ProcessConfigFile(PGC_POSTMASTER);
+ if(!potential_DataDir pgdatadir)
+ potential_DataDir = pgdatadir;
+ checkDataDir(potential_DataDir);/* issues error messages */
+ SetDataDir(potential_DataDir);
+ }
+ else
Re: [HACKERS] location of the configuration files
Bruce Momjian wrote: Robert Treat wrote: IIRC the postmaster.pid file should be in /var/run according to FHS, I'm not sure about postmaster.opts though... Again, if we're going to make a change, let's make sure we think it through. Can non-root write to /var/run? Shouldn't be able too
Re: [HACKERS] location of the configuration files
Robert Treat wrote: On Thu, 2003-02-13 at 14:51, mlw wrote: Robert Treat wrote: On Thu, 2003-02-13 at 12:13, mlw wrote: My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' If we're going to do this, I think we need to account for all of the files in the directory including PG_VERSION, postmaster.opts, postmaster.pid. In the end if we can't build so that we are either fully FHS compliant and/or LSB compliant, we've not done enough work on it. postmaster.opts, PG_VERSION, and postmaster.pid are not configuration parameters. So? I'm not saying they all have to be moved, just they all need to be accounted for. OK, what was the point? PG_VERSION is VERY important, it is how you know the version of the database. Postmaster.pid is a postgres writable value AFAIK, postmaster.opts is also a postgres writable value. IIRC the postmaster.pid file should be in /var/run according to FHS, I'm not sure about postmaster.opts though... Again, if we're going to make a change, let's make sure we think it through. I'm not a big fan of the "/var/run" directory convention, especially when we expect multiple instances of the server to be able to run concurrently. I suppose it can be a parameter in both the configuration file and command line.
Re: [HACKERS] location of the configuration files
Bruce Momjian wrote: Oliver Elphick wrote: On Thu, 2003-02-13 at 17:52, Vince Vielhaber wrote: Seems to me that if FHS allows such a mess, it's reason enough to avoid compliance. Either that or those of you who build for distributions are making an ill advised change. Simply because the distribution makes the decision to add PostgreSQL, or some other package, to it's distribution doesn't make it a requirement to change the location of the config files. Debian (and FHS) specifically requires that. All configuration files MUST be under /etc; the reason is to make the system administrator's job easier. Part of the raison d'etre of a distribution is to rationalise the idiosyncrasies of individual projects. The locations used by locally-built packages are up to the local administrator, but they really should not be in /etc and are recommended to be under /usr/local. I really don't see why there is such a not-invented-here mentality about this issue. I say again, standards-compliance is the best way. It makes life easier for everyone if standards are followed. Don't we pride ourselves on being closer to the SQL spec than other databases? Any way, if PostgreSQL stays as it is, I will continue to have to ensure that initdb creates symlinks to /etc/postgresql/, as happens now. It doesn't have anything to do with "not-invented-here", which is a common refrain by people who don't like our decisions, like "Why don't you use mmap()? Oh, it's because I thought of it and you didn't". Does anyone seriously believe that is the motiviation of anyone in this project! I certainly don't. Now, on to this configuration discussion. Seems moving the config file out of $PGDATA requies either: 1) we specifiy both the config directory and the data directory on postmaster start 2) we specify the pgdata directory inside postgresql.conf or other config file Is this accurate? The patch that I have adds three settings to postgresql.conf and one command line parameter. hba_conf = 'filename' ident_conf='filename' data_dir='path' The command linae parameter is -C, used as: postmaster -C /usr/local/etc/postgresql.conf I think this will help administrators. Bruce, can you shed some light as to why this is being so strongly rejected. I just don't see any downside. I just don't get it. I will be resubmitting my patch for the 7.3.2 tree.
Re: [HACKERS] location of the configuration files
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: Here is the test, configure a server, with sendmail, named, apache, and PostgreSQL. Tell me which of these systems doesn't configure right. AFAIK, only one of those four is designed to support multiple instances running on a single machine. This is not unrelated. While I will agree with you on sendmail and named, Apache is often run more than once with different options. Furthermore, I hate to keep bringing it up, Oracle does use the configuration file methodology. Tom, I just don't understand why this is being resisted so vigorously. What is wrong with starting PostgreSQL as: postmaster -C /etc/postgresql.conf UNIX admins would love to have this as a methodology, I don't think you can deny this, can you? I, as a long term PG user, really really want this, because in the long run, it makes PostgreSQL easier to administer. If a patch allows PG to function as it does, but also allows a configuration file methodology, why not?
Re: [HACKERS] location of the configuration files
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: Here is the test, configure a server, with sendmail, named, apache, and PostgreSQL. Tell me which of these systems doesn't configure right. AFAIK, only one of those four is designed to support multiple instances running on a single machine. This is not unrelated. Also, using an explicit configuration file will also help you run multiple postgresql's on the same machien in a consistent manner, for instance: postmaster -C /etc/postgres/common.conf -D /RAID0/postgres -p 5432 postmaster -C /etc/postgres/common.conf -D /RAID1/postgres -p 5433 Please, Tom, tell me why this is such a bad idea? I will make the patch, I will submit it, will you guys put it in? If not, why?
Re: [HACKERS] location of the configuration files
Bruce Momjian wrote: Well, in a sense, it trades passing one parameter, PGDATA, for another. I see your point that we should specify configuration first, and let everything pass from there. However, it does add extra configuration parameters, and because you still need to specify/create pgdata, it adds an extra level of abstraction to setting up the server. While this is true, it is not uncommon, and it is more or less expected by most UNIX admins. Also, there is nothing preventing someone from symlinking the configuration files from pgdata to somewhere else. Stop!!! symlinks are not sufficient. When happens when a native Win32 version comes out? there are no symlinks. Also, most of the admins I know don't like to use simlinks as they are not self documenting. Symlinks are bad. I don't think separate params for each config file is good. At the most, I think we will specify the configuration _directory_ for all the config files, perhaps pgsql/etc, and have pgdata default to ../data, or honor $PGDATA. That might be the cleanest. The problem with that is that you are back to symlinking shared files. Symlinks are a kludge. Of course, that now gives us $PGCONFIG and $PGDATA, and possible intraction if postgresql.conf specifies a different pgdata from $PGDATA. As you can see, it could get messy. I don't see it as very messy, for instance: postmaster -C /etc/postgres/postgresql.conf -D /RAID0/postgres -p 5432 postmaster -C /etc/postgres/postgresql.conf -D /RAID1/postgres -p 5433 That looks like a real clean way to run multiple PostgreSQL servers on the same box using the same configuration files. And, if you specify pgdata in postgresql.conf, it prevents you from using that file by different postmasters. Not true, command line parameters, as a rule, override configuration file defaults. My best guess would be to not specify pgdata in postgresql.conf, and have a new $PGCONFIG param to specify the configuration directory, but if we do that, $PGDATA/postgresql.conf becomes meaningless, which could also be confusing. Maybe we don't allow those files to exist in $PGDATA if $PGCONFIG is used, _and_ $PGCONFIG is not the same as $PGDATA. See, I am getting myself confused. :-) I think you are making it too complicated. I wouldn't remove the default configration set, it would be useful as a failsafe or maintainence feature. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
Robert Treat wrote: On Thu, 2003-02-13 at 12:13, mlw wrote: My patch only works on the PostgreSQL server code. No changes have been made to the initialization scripts. The patch declares three extra configuration file parameters: hbafile= '/etc/postgres/pg_hba.conf' identfile='/etc/postgres/pg_ident.conf' datadir='/RAID0/postgres' If we're going to do this, I think we need to account for all of the files in the directory including PG_VERSION, postmaster.opts, postmaster.pid. In the end if we can't build so that we are either fully FHS compliant and/or LSB compliant, we've not done enough work on it. Robert Treat postmaster.opts, PG_VERSION, and postmaster.pid are not configuration parameters. PG_VERSION is VERY important, it is how you know the version of the database. Postmaster.pid is a postgres writable value AFAIK, postmaster.opts is also a postgres writable value.
Re: [HACKERS] location of the configuration files
Peter Eisentraut wrote: mlw writes: AFAIK it wasn't actually done. It was more of a, "we should do something different" argument. At one point it was talked about rewriting the configuration system to allow "include" and other things. The core of the problem was, and continues to be, this: If you move postgresql.conf somewhere else, then someone else will also want to move pg_hba.conf and all the rest. And that opens up a number of security and cumbersome-to-install problems. Just having an option that says, the configuration file is "there", is a first step but not a complete solution. The location of pg_hba.conf and pg_ident.conf can be specified within the postgresql.conf file if desired. I don't understand the security concerns, what security issues can there be?
Re: [HACKERS] location of the configuration files
Peter Bierman wrote: At 12:31 AM -0500 2/13/03, mlw wrote: The idea that a, more or less, arbitrary data location determines the database configuration is wrong. It should be obvious to any administrator that a configuration file location which controls the server is the right way to do it. Isn't the database data itself a rather significant portion of the 'configuration' of the database? What do you gain by having the postmaster config and the database data live in different locations? While I don't like to use another product as an example, I think amongst the number of things Oracle does right is that it has a fairly standard way for an admin to find everything. All one needs to do is find the ORACLE_HOME directory, and everything can be found from there. If, assume, PostgreSQL worked like every other system. It would have either an entry in /etc or some other directory specified by configure. Somene please tell me how what I'm proposing differs from things like sendmail, named, or anyother standards based UNIX server? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
Tom Lane wrote: Kevin Brown [EMAIL PROTECTED] writes: I assume $PGDATA was around long before GUC? Yes, it was. But I have not yet seen an argument here that justifies why $SOMECONFIGDIRECTORY/postgresql.conf is better than $PGDATA/postgresql.conf. The latter keeps all the related files together. The former seems only to introduce unnecessary complexity. You can only justify it as simpler if you propose hardwiring a value for $SOMECONFIGDIRECTORY ... which is a proposal that will not fly with any of the core developers, because we all run multiple versions of Postgres on our machines so that we can deal with back-version bug reports, test installations, etc. It is unlikely to fly with any of the RPM packagers either, due to the wildly varying ideas out there about the One True Place where applications should put their config files. (This point was pretty much why mlw's previous proposal was rejected, IIRC.) I wasn't talking about a "default directory" I was talking about configuring a database in a configuration file. While I accept that the PostgreSQL group can not be playing catch-up with other databases, this does not preclude the notion accepting common practices and adopting them. Understand, I really like PostgreSQL. I like it better than Oracle, and it is my DB of choice. That being said, I see what other DBs do right. Putting the configuration in the data directory is "wrong," no other database or service under UNIX or Windows does this, Period. Does the PostgreSQL team know better than the rest of the world? The idea that a, more or less, arbitrary data location determines the database configuration is wrong. It should be obvious to any administrator that a configuration file location which controls the server is the "right" way to do it. Regardless of where ever you choose to put the default configuration file, it is EASIER to configure a database by using a file in a standard configuration directory (/etc, /usr/etc, /usr/local/etc, /usr/local/pgsql/conf or what ever). The data directory should not contain configuration data as it is typically dependent on where the admin chooses to mount storage. I am astounded that this point of view is missed by the core group. Mark.
Re: [HACKERS] location of the configuration files
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: The idea that a, more or less, arbitrary data location determines the database configuration is wrong. It should be obvious to any administrator that a configuration file location which controls the server is the "right" way to do it. I guess I'm just dense, but I entirely fail to see why this is the One True Way To Do It. What you seem to be proposing (ignoring syntactic-sugar issues) is that we replace "postmaster -D /some/data/dir" by "postmaster -config /some/config/file". I am not seeing the nature of the improvement. It looks to me like the sysadmin must now grant the Postgres DBA write access on *two* directories, viz /some/config/ and /wherever/the/data/directory/is. How is that better than granting write access on one directory? Given that we can't manage to standardize the data directory location across multiple Unixen, how is it that we will be more successful at standardizing a config file location? All I see here is an arbitrary break with our past practice. I do not see any net improvement. There is a pretty well understood convention that a configuration file will be located in some standard location depending on your distro. Would you disagree with that? There is also a convention that most servers are configured by a configuration file, located in a central location. Look at sendmail, named,, et al. Here is the test, configure a server, with sendmail, named, apache, and PostgreSQL. Tell me which of these systems doesn't configure right.
Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy]
Tom Lane wrote: "Merlin Moncure" [EMAIL PROTECTED] writes: May I make a suggestion that maybe it is time to start thinking about tuning the default config file, IMHO its just a little bit too conservative, It's a lot too conservative. I've been thinking for awhile that we should adjust the defaults. One of the things I did on my Windows install was to have a number of default configuration files, postgresql.conf.small, postgresql.conf.medium, postgresql.conf.large. Rather than choose one, in the "initdb" script, ask for or determine the mount of shared memory, memory, etc. Another pet peeve I have is forcing the configuration files to be in the database directory. We had this argument in 7.1 days, and I submitted a patch that allowed a configuration file to be specified as a command line parameter. One of the things that Oracle does better is separating the "configuration" from the data. It is an easy patch to allow PostgreSQL to use a separate configuration directory, and specify the data directory within the configuration file (The way any logical application works), and, NO, symlinks are not a solution, they are a kludge.
Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy]
Greg Copeland wrote: I'd personally rather have people stumble trying to get PostgreSQL running, up front, rather than allowing the lowest common denominator more easily run PostgreSQL only to be disappointed with it and move on. After it's all said and done, I would rather someone simply say, it's beyond my skill set, and attempt to get help or walk away. That seems better than them being able to run it and say, it's a dog, spreading word-of-mouth as such after they left PostgreSQL behind. Worse yet, those that do walk away and claim it performs horribly are probably doing more harm to the PostgreSQL community than expecting someone to be able to install software ever can. RANT And that my friends is why PostgreSQL is still relatively obscure. This attitude sucks. If you want a product to be used, you must put the effort into making it usable. It is a no-brainer to make the default configuration file suitable for the majority of users. It is lunacy to create a default configuration which provides poor performance for over 90% of the users, but which allows the lowest common denominator to work. A product must not perform poorly out of the box, period. A good product manager would choose one of two possible configurations, (a) a high speed fairly optimized system from the get-go, or (b) it does not run unless you create the configuration file. Option (c) out of the box it works like crap, is not an option. This is why open source gets such a bad reputation. Outright contempt for the user who may not know the product as well as those developing it. This attitude really sucks and it turns people off. We want people to use PostgreSQL, to do that we must make PostgreSQL usable. Usability IS important. /RANT ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy]
Apology After Mark calms down and, in fact, sees that Greg was saying the right thing after all, chagrin is the only word. I'm sorry. Greg Copeland wrote: On Tue, 2003-02-11 at 11:23, mlw wrote: Greg Copeland wrote: I'd personally rather have people stumble trying to get PostgreSQL running, up front, rather than allowing the lowest common denominator more easily run PostgreSQL only to be disappointed with it and move on. After it's all said and done, I would rather someone simply say, "it's beyond my skill set", and attempt to get help or walk away. That seems better than them being able to run it and say, "it's a dog", spreading word-of-mouth as such after they left PostgreSQL behind. Worse yet, those that do walk away and claim it performs horribly are probably doing more harm to the PostgreSQL community than expecting someone to be able to install software ever can. RANT And that my friends is why PostgreSQL is still relatively obscure. This attitude sucks. If you want a product to be used, you must put the effort into making it usable. Ah..okay It is a no-brainer to make the default configuration file suitable for the majority of users. It is lunacy to create a default configuration which provides poor performance for over 90% of the users, but which allows the lowest common denominator to work. I think you read something into my email which I did not imply. I'm certainly not advocating a default configuration file assuming 512M of share memory or some such insane value. Basically, you're arguing that they should keep doing exactly what they are doing. It's currently known to be causing problems and propagating the misconception that PostgreSQL is unable to perform under any circumstance. I'm arguing that who cares if 5% of the potential user base has to learn to properly install software. Either they'll read and learn, ask for assistance, or walk away. All of which are better than Jonny-come-lately offering up a meaningless benchmark which others are happy to eat with rather large spoons. A product must not perform poorly out of the box, period. A good product manager would choose one of two possible configurations, (a) a high speed fairly optimized system from the get-go, or (b) it does not run unless you create the configuration file. Option (c) out of the box it works like crap, is not an option. That's the problem. Option (c) is what we currently have. I'm amazed that you even have a problem with option (a), as that's what I'm suggesting. The problem is, potentially for some minority of users, it may not run out of the box. As such, I'm more than happy with this situation than 90% of the user base being stuck with a crappy default configuration. Oddly enough, your option (b) is even worse than what you are ranting at me about. Go figure. This is why open source gets such a bad reputation. Outright contempt for the user who may not know the product as well as those developing it. This attitude really sucks and it turns people off. We want people to use PostgreSQL, to do that we must make PostgreSQL usable. Usability IS important. /RANT There is no contempt here. Clearly you've read your own bias into this thread. If you go back and re-read my posting, I think it's VERY clear that it's entirely about usability. Regards,
[HACKERS] location of the configuration files
The debate on the configuration file sparked a memory of an old patch I submitted in 7.1 days. One of the things I do not like about PostgreSQL is, IMHO, is a backwards configuration process. Rather than specify a data directory, the administrator should specify a database configuration file. Within the configuration file is the location and names of the data directory and other information. Most admins want a central location for this information. One of the things that is frustrating to me, is to have to hunt down where the data directory is so that I can administrate a DB. It can be anywhere, in any directory on any volume. If you had, say, a /usr/local/pgsql/admin, then you could have mydb.conf which could then be checked in to CVS. A standard location for configuration files is a more normal process as the location of the data directory is less so. I just don't think the PG data directory should not contain configuration information. The original patch allowed a user to specify the location of the postgresql.conf file, rather than assuming it lived in $PGDATA Also included in that patch, was the ability to specify the location of the PGDATA directory as well as the names of the pg_hba.conf and other configuration files. It also allowed the user to use PG as it has always worked, The patch was not applied because a better solution was supposedly coming, but that was two major revisions ago. I would still like to see this functionality. Would anyone else? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
Robert Treat wrote: I'm going to be lazy and ask if you can post what the better solution that was coming was (or a link to the thread). While I'll grant you that the it's coming argument is pretty weak after two releases, that fact that it may have been a better solution could still hold up. Robert Treat AFAIK it wasn't actually done. It was more of a, we should do something different argument. At one point it was talked about rewriting the configuration system to allow include and other things. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[Fwd: Re: [mail] Re: [HACKERS] Windows Build System]
Original Message Subject: Re: [mail] Re: [HACKERS] Windows Build System Date: Fri, 31 Jan 2003 15:46:20 -0500 From: mlw [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] CC: Curtis Faith [EMAIL PROTECTED], 'Al Sutton' [EMAIL PROTECTED], 'Bruce Momjian' [EMAIL PROTECTED], [EMAIL PROTECTED] References: 002101c2c625$e3204e30$a200a8c0@curtislaptop [EMAIL PROTECTED] Tom Lane wrote: Curtis Faith writes: If a developer can simply download the source, click on the Visual C++ project in the win32 directory and then build PostgreSQL, and they can see that Windows is not the poor stepchild because the VC project is well laid out, they will be more likely to use it for Windows projects than MySQL which requires the CygWin tools (this means really a Unix product to Windows developers). In all honesty, I do not *want* Windows people to think that they're not running on the poor stepchild platform.If we go down that path, they'll start trying to run production databases on Windows, and then we'll get blamed for the instability of the platform, not to mention the likelihood that it ignores Unix semantics for fsync() and suchlike critical primitives. I have no objection to there being a Windows port that people can use to do SQL-client development on their laptops. But let us please not confuse this with an industrial-strength solution; nor give any level of support that might lead others to make such confusion. The MySQL guys made the right choice here: they don't want to buy into making Windows a grade-A platform, either. OK, I have to weigh in here. I have been a Windows application and kernel driver developer since version 1.0. I have also worked on UNIX since the original Sun machines. Yes, the DOS version of Windows, i.e win95/98/ME is pure unmitigated crap. No doubt. The NT version of Windows, NT/2K/XP has a very well designed kernel. It is more or less based on OpenVMS. To say it is a poor stepchild shows a lack of imagination on your part. The NT lineage of Windows is usable as a production server. I think PostgreSQL using the most pedestrian Win32 API entry points will perform just fine. The core disk I/O subsystem and NTFS are very stable. The scheduler is not great, but is usable. The VM system is probably better than most UNIX environments, including FreeBSD and Linux. The always interruptable always reentrant device driver design could crank out some serious performance on a busy server. That being said, the kernel level GUI of Windows is a dangerous risk. Many of the changes made since the original NT (3.x) do reduce stability in a desktop environment. However, a server environment, such as PG, which does not perform any graphic interactions should be stable enough. If rebooted once a every month or two, the system should never experience data loss and windows admins are used to doing periodic reboots. One last, IMHO very important point, A LOT OF PEOPLE USE WINDOWS! Every effort should be made to support it. Yea, we all have our favorite environments. I choose Linux, others choose a *BSD, some use HPUX, Solaris or whatever. The point is a lot people choose Windows. It is possible to make a stable environment on this platform. Would I choose it? No, but some people do. Don't you think it makes sense to provide a good solution on Windows, and if they run into the inherent limitations of that platform be able to say, Windows has some serious design flaws, but you can upgrade to Solaris or HPUX if you need and getting the user, instead of saying, Windows sucks, use a real platform and losing them? I think it is a AWESOME story to say, Build your app using PG. Start with Windows, if you like, we don't care, if you grow beyond the capabilities of Windows, just upgrade your server, no need to change anything else. Just my $0.02 Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [mail] Re: [HACKERS] Windows Build System
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: Like it or not, if PG releases a very good Win32 port, ALL the unixoids combined will be out numbered by the windoze users. A lot of us are *not* looking forward to that prospect. regards, tom lane No doubt to that, but, depending on how good the PG guys are, it is either a blessing or a curse. I think that PG has a REAL chance to be one of THE breakthrough open source technologies. With the exception of OpenOffice, I don't think there is a more important open source project than PG. Simply because SQL databases are a cooperative monopoly. MS, Oracle, and DB2 are like the record companies. They have a cooperative monopoly. Yea, they will seem to compete on price, but none of them really whant to know how low the other will go. Some may argue that Apache or PHP may take second place, but I submit that Apache and PHP are, by and large, much less expensive and much less generic products as an ACID compliant SQL databases. That being said, if a good Win32 port is made, AND it becomes common knkowledge, the use count may square. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [mail] Re: [HACKERS] Windows Build System
Dann Corbit wrote: -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 8:24 PM To: mlw Cc: Lamar Owen; Dann Corbit; PostgresSQL Hackers Mailing List Subject: Re: [mail] Re: [HACKERS] Windows Build System mlw [EMAIL PROTECTED] writes: Like it or not, if PG releases a very good Win32 port, ALL the unixoids combined will be out numbered by the windoze users. A lot of us are *not* looking forward to that prospect. In all seriousness, it may be a good idea to create a special list server group for that exact audience. Call it Win32 PostgreSQL Users or something like that. That way, then can help each other. And the experienced PG users that can stand the noise can pop over to help from time to time. You might have one million PG users in 6 months time. Literally. no doubt ---(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, NetBSD and NFS
D'Arcy J.M. Cain wrote: On Thursday 30 January 2003 14:02, mlw wrote: Forgive my stupidity, are you running PostgreSQL with the data on an NFS share? Yes, sorry. PostgreSQL is running from the local disk but the data is on the mounted drive. I'm not sure, I guess it could work, but NFS is a pretty poor file system. There are always issues with file locking across various platforms. I recall reading about mmap issues across NFS a while ago (forget the platform, sorry). Depending on the NFS server, there may be problems there. The NFS client may also have isses with locking, fsync, and mmap. If possible, look for a network block device protocol. The file level NFS is probably inadequate for PostgreSQL. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [mail] Re: [HACKERS] Windows Build System
Tom Lane wrote: Curtis Faith [EMAIL PROTECTED] writes: If a developer can simply download the source, click on the Visual C++ project in the win32 directory and then build PostgreSQL, and they can see that Windows is not the poor stepchild because the VC project is well laid out, they will be more likely to use it for Windows projects than MySQL which requires the CygWin tools (this means really a Unix product to Windows developers). flame on In all honesty, I do not *want* Windows people to think that they're not running on the poor stepchild platform.If we go down that path, they'll start trying to run production databases on Windows, and then we'll get blamed for the instability of the platform, not to mention the likelihood that it ignores Unix semantics for fsync() and suchlike critical primitives. I have no objection to there being a Windows port that people can use to do SQL-client development on their laptops. But let us please not confuse this with an industrial-strength solution; nor give any level of support that might lead others to make such confusion. The MySQL guys made the right choice here: they don't want to buy into making Windows a grade-A platform, either. flame off OK, I have to weigh in here. I have been a Windows application and kernel driver developer since version 1.0. I have also worked on UNIX since the original Sun machines. Yes, the DOS version of Windows, i.e win95/98/ME is pure unmitigated crap. No doubt. The NT version of Windows, NT/2K/XP has a very well designed kernel. It is more or less based on OpenVMS. To say it is a poor stepchild shows a lack of imagination on your part. The NT lineage of Windows is usable as a production server. I think PostgreSQL using the most pedestrian Win32 API entry points will perform just fine. The core disk I/O subsystem and NTFS are very stable. The scheduler is not great, but is usable. The VM system is probably better than most UNIX environments, including FreeBSD and Linux. The always interruptable always reentrant device driver design could crank out some serious performance on a busy server. That being said, the kernel level GUI of Windows is a dangerous risk. Many of the changes made since the original NT (3.x) do reduce stability in a desktop environment. However, a server environment, such as PG, which does not perform any graphic interactions should be stable enough. If rebooted once a every month or two, the system should never experience data loss and windows admins are used to doing periodic reboots. One last, IMHO very important point, A LOT OF PEOPLE USE WINDOWS! Every effort should be made to support it. Yea, we all have our favorite environments. I choose Linux, others choose a *BSD, some use HPUX, Solaris or whatever. The point is a lot people choose Windows. It is possible to make a stable environment on this platform. Would I choose it? No, but some people do. Don't you think it makes sense to provide a good solution on Windows, and if they run into the inherent limitations of that platform be able to say, Windows has some serious design flaws, but you can upgrade to Solaris or HPUX if you need and getting the user, instead of saying, Windows sucks, use a real platform and losing them? I think it is a AWESOME story to say, Build your app using PG. Start with Windows, if you like, we don't care, if you grow beyond the capabilities of Windows, just upgrade your server, no need to change anything else. Just my $0.02 Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL, NetBSD and NFS
Forgive my stupidity, are you running PostgreSQL with the data on an NFS share? D'Arcy J.M. Cain wrote: I have posted before about this but I am now posting to both NetBSD and PostgreSQL since it seems to be some sort of interaction between the two. I have a NetAPP filer on which I am putting a PostgreSQL database. I run PostgreSQL on a NetBSD box. I used rsync to get the database onto the filer with no problem whatsoever but as soon as I try to open the database the NFS mount hangs and I can't do any operations on that mounted drive without hanging. Other things continue to run but the minute I do a df or an ls on that drive that terminal is lost. On the NetBSD side I get a server not responding error. On the filer I see no problems at all. A reboot of the filer doesn't correct anything. Since NetBSD works just fine with this until I start PostgreSQL and PostgreSQL, from all reports, works well with the NetApp filer, I assume that there is something out of the ordinary about PostgreSQL's disk access that is triggering some subtle bug in NetBSD. Does the shared memory stuff use disk at all? Perhaps that's the difference between PostgreSQL and other applications. The NetApp people are being very helpful and are willing to follow up any leads people might have and may even suggest fixes if necessary. I have Bcc'd the engineer on this message and will send anything I get to them. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [CYGWIN] Have a PG 7.3.1 Windows (cygwin) easy installer... now
I thought I sent off an email about how to do it, OK here goes:
Create a working minimal cygwin with postgres install.
Delete unwanted utilities and files.
Test if you can start, stop, initdb, and query Postgres.
Use the attached pgsql.iss script to make the install.
Notes:
My version of the console program may be different than what is up on
winmaster. I sent all my changes to Igor, but I have never been able to
compile what he sends back. I'm not sure what compiler he is using, I am
building mine with gcc and MSVC.
The registry entries are very important, make sure you don't mess with
them too much.
You asked how I managed the run as service issue. I handled it by
ignoring it. I do not setup the run as service option because, IMHO, it
is too hard to ensure that the setup works without any problems. I
modified the console program to take care of the issues of running
PostgreSQL under cygwin, including cygipc. If I were to add run as a
service I would write a service program that wrapped the cygwin and
cygipc details in much the same way as the console program does.
Further thoughts about run as service. My install is aimed at Windows
power users, not back office guys. PostgreSQL with cygwin is not ready
for the back office, the biggest problems are the limit of concurrent
connections and performance. I don't trust cygwin as a reliable service,
so adding the option run as a service may just encourage them to do
so. I think that would do more harm than not having the option. When
PostgreSQL has a native Windows version, I'll add it. Until then, I
think of it more as a desktop version for small offices and
developers. The server version currently only runs on UNIX
Justin Clift wrote:
mlw wrote:
Sorry, I think there was a misunderstanding. What were you looking for?
Sorry Mark, I just thought you were busy.
Was wondering if you were going to make a project of it somewhere, so
we can get things together and have a really decent release for
Windows when 7.4 comes out. :)
I used inno setup as well. If you want I can send my install script.
That would be really cool. :)
How did you handle the user and Log on as a service aspects of it?
:)
I thought I was being very forth coming.
Yep, you 100% have a really good attitude, that's why I thought you
were busy.
:)
I even help out on the Windows PG console window.
Took a look at it, and the three buttons seem permanently greyed out
in the download from the WinMaster project. Wasn't sure if it was a
configuration issue on my part, or if the code hadn't been fleshed out
yet.
Interested in making a project on GBorg or something for the complete
Windows installer as a place to work out of?
:-)
Regards and best wishes,
Justin Clift
;PostgreSQL for Windows by Mohawk Software
;Copyright (C) 2002-2003 Mark L. Woodward
;
;This file is free software; you can redistribute it and/or
;modify it under the terms of the GNU General Public
;License as published by the Free Software Foundation; either
;version 2 of the License, or (at your option) any later version.
;
;This file is distributed in the hope that it will be useful,
;but WITHOUT ANY WARRANTY; without even the implied warranty of
;MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
;Library General Public License for more details.
;
;You should have received a copy of the GNU General Public
;License along with this file; if not, write to the Free
;Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
;MA 02111-1307, USA
;
;If you want support or to commercially license this file, the author
;can be reached at [EMAIL PROTECTED]
[Setup]
AppName=PostgreSQL
AppVerName=PostgreSQL 7.3 for Windows
AppPublisher=Mohawk Software
AppPublisherURL=http://www.mohawksoft.com
AppSupportURL=http://www.mohawksoft.com
AppUpdatesURL=http://www.mohawksoft.com
DefaultDirName={sd}\PostgreSQL
DisableStartupPrompt=yes
DefaultGroupName=PostgreSQL
AllowNoIcons=yes
SourceDir=e:\pginstall\source
OutputBaseFilename=pgsetup
OutputDir=e:\pginstall\PostgreSQL
[Registry]
Root: HKLM; Subkey: SOFTWARE\Cygnus Solutions; Flags: uninsdeletekeyifempty
Root: HKLM; Subkey: SOFTWARE\Cygnus Solutions\Cygwin; Flags: uninsdeletekeyifempty
Root: HKLM; Subkey: SOFTWARE\Cygnus Solutions\Cygwin\mounts v2; Flags:
uninsdeletekeyifempty
Root: HKLM; Subkey: SOFTWARE\Cygnus Solutions\Cygwin\mounts v2; ValueType: String;
ValueName: cygdrive prefix; ValueData: /disks; Flags: uninsdeletekeyifempty
Root: HKLM; Subkey: SOFTWARE\Cygnus Solutions\Cygwin\mounts v2; ValueType: dword;
ValueName: cygdrive flags; ValueData: 34; Flags: uninsdeletekeyifempty
Root: HKLM; Subkey: SOFTWARE\Cygnus Solutions\Cygwin\mounts v2\/; ValueType: String;
ValueName: native; ValueData: {app}; Flags: uninsdeletekeyifempty
Root: HKLM; Subkey: SOFTWARE\Cygnus Solutions\Cygwin\mounts v2\/; ValueType: dword;
ValueName: flags; ValueData: $0A
Re: [HACKERS] [CYGWIN] Have a PG 7.3.1 Windows (cygwin) easy installer... now
Sorry, I think there was a misunderstanding. What were you looking for? I used inno setup as well. If you want I can send my install script. I thought I was being very forth coming. I even help out on the Windows PG console window. Justin Clift wrote: Hi everyone, Mark (mlw) put together a PostgreSQL installer for Windows (cygwin version) a little while ago, but he hasn't been responding to requests for feedback regarding it (probably busy). As we're going to be releasing a native Windows version of PostgreSQL 7.4 in a few months, it seems appropriate that we practise first to get the hang of making packages on Windows, plus encourage anyone with graphical talent to make attractive icon's for menu options, etc. Anyway, spent the last two days making a brand new PostgreSQL 7.3.1 Proof of Concept for Windows Alpha 1 easy-installer (11,161KB) using a product called Inno Setup (very nice) and have a pretty good result. It looks and feels *really* professional, and if people didn't know that it was using cygwin, they'd probably never guess. Am reckoning that the best thing to do for this is to create a project on GBorg of some name, upload it, and everyone who is interested can take it from there. Does that sound like the best approach, and does anyone have good suggestions for a project name? :-) Regards and best wishes, Justin Clift ---(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] Threads
Greg Copeland wrote: On Thu, 2003-01-23 at 09:12, Steve Wampler wrote: On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote: Also remember that in even well developed OS's like FreeBSD, all a process's threads will execute only on one CPU. I doubt that - it certainly isn't the case on Linux and Solaris. A thread may *start* execution on the same CPU as it's parent, but native threads are not likely to be constrained to a specific CPU with an SMP OS. You are correct. When spawning additional threads, should an idle CPU be available, it's very doubtful that the new thread will show any bias toward the original thread's CPU. Most modern OS's do run each thread within a process spread across n-CPUs. Those that don't are probably attempting to modernize as we speak AFAIK, FreeBSD is one of the OSes that are trying to modernize. Last I looked it did not have kernel threads.
[HACKERS] Win32 Build Environment
I have been following the debate about the Windows build environment. I would like to say that the build environment is not a real issue for Windows developers. For the most part Windows developers will be happy with a working binary and an interface library. The one is savvy enough to want to modify the source, they will be comfortable with installing cygwin. Does anyone see this as unreasonable? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can we revisit the thought of PostgreSQL 7.2.4?
This is an interesting thought. My gut tells me it is a viable opportunity for the corporate entities that offer support and wish to have 'VAR' status. This is just my opinion, but I view the core development group as pure development, and the various people that resell or distribute PostgreSQL as a for-profit business as those responsible for maintaining backward support. Maybe RedHat or PostgreSQL Inc can do this? It is a really good message, The best of open source, with on going support. And not to re-open a can of worms, but if PostgreSQL could upgrade without having to do a dump and restore, then this wouldn't really be an issue. Justin Clift wrote: Hi everyone, Over the last few days we've had patches submitted for 7.2.3 that address a couple of things, both the WAL Recovery Bug that Tom has developed a patch for, and a couple of buffer overflows that have been widely reported. Although we haven't wanted to release a 7.2.4, and have instead encouraged people to upgrade to 7.3.x, there are places out there who's applications aren't compatible with 7.3.x and would also need to upgrade them as well. It might be a really good idea if we re-visit the thought of 7.2.4 and have something that people running the 7.2.x series can use safely until they are able to move to 7.3.x or above. What would it take, and apart from patches for the buffer overflows and the WAL recovery bug, should anything else be included to ensure safety and stability? :-) Regards and best wishes, Justin Clift ---(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] Indexes
Does anyone think it would be a good idea, or is it even practical, to have a 'indx' subdirectory along side of the 'base' directory? I was thinking that, if it were an easy modification, that it could be an easy way to separate data and indexes to different hard disks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS]
I don't think a semicolon is a comment. It causes the execution of the previous statement. Christopher Kings-Lynne wrote: Hi, In postgresql 7.3.1, if I do pg_dumpall -c, at the top of the dump file is this: DROP DATABASE au_shipping ;CREATE DATABASE au_shipping WITH OWNER = auadmin TEMPLATE = template0 ENCODING = 'SQL_ASCII'; DROP DATABASE au_test ;CREATE DATABASE au_test WITH OWNER = chriskl TEMPLATE = template0 ENCODING = 'SQL_ASCII'; DROP DATABASE australia ;CREATE DATABASE australia WITH OWNER = auadmin TEMPLATE = template0 ENCODING = 'SQL_ASCII'; DROP DATABASE geeklog ;CREATE DATABASE geeklog WITH OWNER = chriskl TEMPLATE = template0 ENCODING = 'SQL_ASCII'; DROP DATABASE keystone ;CREATE DATABASE keystone WITH OWNER = chriskl TEMPLATE = template0 ENCODING = 'SQL_ASCII'; Why are all the CREATE DATABASE statements commented out? Surely that will make the restore fail? Chris ---(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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle rant
Peter Eisentraut wrote: Adrian 'Dagurashibanipal' von Bidder writes: - postgres should auto-tune itself - the *cost could perhaps be adjusted after some statistics have been collected, and there should be some sensible way to determine an optimal setting for the famous shared_buffers (and the default should be something that gets reasonable performance on common cases) Over the last couple of years PostgreSQL has transformed from hardly configurable to fully configurable. Currently we're in a mode where we add new configuration parameters whenever there's a degree of uncertainty. Sooner rather than later we need to shift to the next phase, which is as you say autoconfiguration, because ease of administration is one of the great advantages of PostgreSQL I think the idea of adding a parameter when ever you are not sure, is a great idea. That does preclude, however, the ability for a process within PostgreSQL from analyzing the metrics and updating the parameter file or table.
[HACKERS] Oracle rant
I just wanted to post this note. I have been in Oracle hell for four days now, and in between the 5 minutes of work and the hours of watings, dealing with table spaces, extents, and all that, I just keep thinking about how much easier PostgreSQL is to work with. We all may bitch and moan about bugs and stuff, but my project would have been easier with PostgreSQL. Has anyone ever noticed that Oracle has all these nice little arcane ways to fail? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle rant
Gavin Sherry wrote: On Wed, 15 Jan 2003, mlw wrote: I just wanted to post this note. I have been in Oracle hell for four days now, and in between the 5 minutes of work and the hours of watings, dealing with table spaces, extents, and all that, I just keep thinking about how much easier PostgreSQL is to work with. We all may bitch and moan about bugs and stuff, but my project would have been easier with PostgreSQL. Has anyone ever noticed that Oracle has all these nice little arcane ways to fail? Yes. I was doing some work with a company. I wanted to introduce Postgres. They're traditionally an oracle shop. "Our Oracle DBAs don't know Postgres, we're going to have to employ *another* DBA". No they don't. :-) This is the truth, we have had an oracle box for two and a half years, we have had 4 PostgreSQL boxes with it. The Oracle system is on a 4 CPU Sun box. The PostgreSQL systems are on 2 CPU PIII boxes. We had "certified oracle DBA"s setup the oracle box. I setup the PostgreSQL boxes. The PostgreSQL boxes NEVER had an unscheduled interruption in service. The Oracle system stops from time to time because of various arcane reasons. You get the error message, look it up on alltheweb.com, and fix it. The whole system is bogus. It DEMANDS a full time DBA. PostgreSQL does not.
[HACKERS] PostgreSQL site, put up or shut up?
This is a serious inquiry, very serious. People are complaining about ads. What do we need in the form of equipment, bandwidth, etc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New Portal in Place, DNS switched ...
Tom Lane wrote: "Marc G. Fournier" [EMAIL PROTECTED] writes: Please understand something here ... a large portion of the banner ads are *not* paid ... they are recognition of the many mirror sites that are supporting the project by reducing the amount of bandwidth that is required on the central server ... While the mirror sites deserve some recognition, I'm not convinced that that should translate to banner ads on the main portal. Could we set things up so that when you actually go to a mirror site, you see some discreet notice about "this mirror sponsored by so-and-so"? And I'm definitely not happy with reading Help Support This Site Post your Ad Here Pay Only for Visitors As Low As $0.10cdn per click-thru Hub.Org on the main site. That's several shades too mercenary for my taste. I think banner ads that build on PostgreSQL's message is a good thing. A RedHat ad, maybe IBM, etc. Companies with a related purpose to the PostgreSQL mission will offset some of the cost and help build the cedibility of the site. Hotel ads and sweepstakes are a bad idea, though.
Re: [HACKERS] PostgreSQL and memory usage
Dann Corbit wrote: Message I have a machine with 4 CPU's and 2 gigabytes of physical ram. I would like to get PostgreSQL to use as much memory as possible. I can't seem to get PostgreSQL to use more than 100 megabytes or so. How can I optimize the use of PostgreSQL to get the maximum throughput in a configuration like that? Are there any memory usage/tuning documents I can read? I'm not sure if there is a document, but there are some things you can do. First, size the shared memory pool. I've been told by Tom that too much is actually slower, but making it MUCH bigger than the default does help a lot. shared_buffer=2048 (Maybe larger, experiment) Sort memory, this is useful for large queries that do sorting, it is allocated as needed on a per process basis. If you run large queries that sort, this can speed you up instead of sorting to disk. sort_mem=16384 OK, lastly, do not dispare if PostgreSQL seems not to be using as much memory as is in your system. Don't forget the OS disk cache is important too.
Re: [HACKERS] New Portal in Place, DNS switched ...
I'll do it on my site. Marc G. Fournier wrote: Any volunteers to act as a tertiary? :) We're actually working on adding a new server online that is offshore, which will also give us another subnet to work off of ... but having a third-party secondary server wouldn't hurt, you are right ... On Sun, 5 Jan 2003 [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Speaking of DNS, we should probably not put all of our eggs in one basket (subnet): $ whois postgresql.org ... Domain servers in listed order: NS.HUB.ORG 64.49.215.5 NS2.HUB.ORG 64.49.215.6 It would be nice if one or more nameservers were added that were not in the same subnet, especially because we have so many mirrors (subdomains) that are scattered all over the globe. Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200301051008 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+GE5lvJuQZxSWSsgRAteAAJ9YQF/eOpS+bZl84HOT84HAiaRQtQCfawbI VpEZSB8oXoO3ycza4g6h5Hg= =19gB -END PGP SIGNATURE- ---(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] New Portal in Place, DNS switched ...
The site looks fantastic! Great work! Marc G. Fournier wrote: I'm just announcing here, since I'd like to see some ppl testing this out and let us know if there are any problems ... DNS is going to take a little while to propogate, so the old site may still come up in the interium ... another reason not to announce it right away :) ---(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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New Portal in Place, DNS switched ...
Marc G. Fournier wrote: Any volunteers to act as a tertiary? :) We're actually working on adding a new server online that is offshore, which will also give us another subnet to work off of ... but having a third-party secondary server wouldn't hurt, you are right ... OK, add 64.46.156.80 as a slave to your master, and NSC.INSPECTHOUSE.COM will provide DNS services. On Sun, 5 Jan 2003 [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Speaking of DNS, we should probably not put all of our eggs in one basket (subnet): $ whois postgresql.org ... Domain servers in listed order: NS.HUB.ORG 64.49.215.5 NS2.HUB.ORG 64.49.215.6 It would be nice if one or more nameservers were added that were not in the same subnet, especially because we have so many mirrors (subdomains) that are scattered all over the globe. Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200301051008 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+GE5lvJuQZxSWSsgRAteAAJ9YQF/eOpS+bZl84HOT84HAiaRQtQCfawbI VpEZSB8oXoO3ycza4g6h5Hg= =19gB -END PGP SIGNATURE- ---(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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New Portal in Place, DNS switched ...
Neil Conway wrote: On Sun, 2003-01-05 at 17:15, Dave Page wrote: There were always ads there Yes -- but AFAIK there were in the process of being phased out (furthermore, the old site only had ads on the initial mirror page, whereas they are much more widespread on the new site). they help pay for the boxes. Obviously, but it's VERY unprofessional for us to show ads to users on our website. It goes without saying, but pretty much every other non-trivial OSS project doesn't have ads on their main website. Displaying ads makes us look more like a Geocities site than a legitimate competitor to Oracle/DB2/etc. In fact, there are several groups that provide free hosting for OSS projects, without requiring them to display ads on their webpages (e.g. SourceForge, Savannah, etc.) Cheers, Neil The PHP site shows adds.
Re: [HACKERS] New Portal in Place, DNS switched ... Banner Adds
I know I participate on this group periodically, but my last position was CTO at a company, and I currently run my own consulting company. I feel I have a pretty neutral perspective. I don't see what the fuss is all about. Banner adds are good, if the PostgreSQL can get some good RELEVANT adds on its site, (a) shows viability, and (b) that there is some sort of revenue. Oracle and Microsoft "rape" their customers, of course they have no 3rd party banner adds, but make no mistake, there are adds for other products by the respective companies. I think that, if you guys want banner adds, it is not a sin, nor is it unprofessional. My only suggestion is to make sure they are relevant to the database market. Adds that are relevant to your core competency enhance your message. Of course, if you post sweepstakes adds, then you blow it. Marc G. Fournier wrote: On Mon, 6 Jan 2003, Neil Conway wrote: On Mon, 2003-01-06 at 13:26, Marc G. Fournier wrote: On Mon, 6 Jan 2003, mlw wrote: The PHP site shows adds. Ok -- but the vast majority (say, 95%) of OSS sites don't show ads. Guess that makes us part of the elite 5% that do, eh? You had me worried for awhile there that we were totally exclusive in this *wipe brow*
Re: [HACKERS] Upgrading rant.
Bruce Momjian wrote: pg_upgrade does work, assuming there are no changes to the index or heap file formats. (However, I now need to update it for schemas.) However, the last time I worked on it for 7.2, no one was really interested in testing it, so it never got done. In fact, there was a bug in the handling of clog or wal files, but I didn't find out about it until long after 7.2 because no one was using it. Is pg_upgrade too hard to run? Is no one really interested in it? As far as I've seen, it is a cool idea, but I can't trust it. I have the USA tiger census data in a database, it is over 60G with indexes, 30G+ of just data. Do you know how long that will take to dump and restore? Making one index on some of the tables takes 20 minutes. IMHO: (1) The PostgreSQL core development team has to commit to an in place upgrade. I think the in-place upgrade strategy is very important, and it will take an effort and commitment from the core development team. I doubt seriously it can be done in a robust and safe way if the feature is not a stated design goal. (2) Upgrade HAS HAS HAS to be fool proof. No one is going to use it if you say, backup your data just in case. It should be as trust worthy as postgresql itself. If it can't be that it is not a valid tool. Anything less will not be used by professionals and wouldn't be worth the effort. (3) It should be able to span more than one version. If I upgrade from two versions back, it should work. It should not balk. The above is simply my opinion, and probably not possible with previous versions, but moving forward, it should be, if it is a priority. If it is not a priority, then it is not worth doing. Again, just my opinion. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Upgrading rant.
Tom Lane wrote: Well, this is exactly the issue: someone would have to put substantial amounts of time into update mechanisms and/or maintenance of obsolete versions, as opposed to features, performance improvements, or bug fixes. Personally, I feel that if we weren't working as hard as we could on features/performance/bugfixes, the upgrade issue would be moot because there wouldn't *be* any reason to upgrade. So I'm not planning to redirect my priorities. But this is an open source project and every developer gets to set their own priorities. If you can persuade someone to put their time into that, go for it. Do not under estimate the upgrade issue. I think it is huge and a LOT of people have problems with it. Personally, I never understood why the dump/restore needed to happen in the first place. Can't the data and index file format be more rigidly defined and stuck too? Can't there just be some BKI process to add new data entries? I had the same issues with 7.1 and 7.2, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Threads
Please no threading threads!!! Has anyone calculated the interval and period of PostgreSQL needs threads posts? The *ONLY* advantage threading has over multiple processes is the time and resources used in creating new processes. That being said, I admit that creating a threaded program is easier than one with multiple processes, but PostgreSQL is already there and working. Drawbacks to a threaded model: (1) One thread screws up, the whole process dies. In a multiple process application this is not too much of an issue. (2) Heap fragmentation. In a long uptime application, such as a database, heap fragmentation is an important consideration. With multiple processes, each process manages its own heap and what ever fragmentation that exists goes away when the connection is closed. A threaded server is far more vulnerable because the heap has to manage many threads and the heap has to stay active and unfragmented in perpetuity. This is why Windows applications usually end up using 2G of memory after 3 months of use. (Well, this AND memory leaks) (3) Stack space. In a threaded application they are more limits to stack usage. I'm not sure, but I bet PostgreSQL would have a problem with a fixed size stack, I know the old ODBC driver did. (4) Lock Contention. The various single points of access in a process have to be serialized for multiple threads. heap allocation, deallocation, etc all have to be managed. In a multple process model, these resources would be separated by process contexts. (5) Lastly, why bother? Seriously? Process creation time is an issue true, but its an issue with threads as well, just not as bad. Anyone who is looking for performance should be using a connection pooling mechanism as is done in things like PHP. I have done both threaded and process servers. The threaded servers are easier to write. The process based severs are more robust. From an operational point of view, a select foo from bar where x y will take he same amount of time. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Upgrading rant.
Hannu Krosing wrote: I don't think the main issues are with file _formats_ but rather with system file structures - AFAIK it is a fundamental design decision (arguably a design flaw ;( ) that we use system tables straight from page cache via C structure pointers, even though there seems to be a layer called storage Manager which should hide the on-disk format completely. I don't think that is a big issue, no one is saying the file format should change or be used any differently, just that the structures be more rigid, and anyone wishing to make a change, had better also have an upgrade strategy. Perhaps there could be a review/document phase for 7.4 where the structures are cleaned up, checked, and perhaps have a couple reserved entries added. (As governed by efficiecy) and have one last speak now, or forever hold your peace and cast them in stone. I think, as professional standards go, this is probably LONG over due. ---(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] Threads
Greg Copeland wrote: Of course that gives up the hope of faster connection startup that has always been touted as a major reason to want Postgres to be threaded... regards, tom lane Faster startup, should never be the primary reason as there are many ways to address that issue already. Connection pooling and caching are by far, the most common way to address this issue. Not only that, but by definition, it's almost an oxymoron. If you really need high performance, you shouldn't be using transient connections, no matter how fast they are. This, in turn, brings you back to persistent connections or connection pools/caches. Connection time should *never* be in the critical path. There, I've said it!! People who complain about connection time are barking up the wrong tree. Regardless of the methodology, EVERY OS has issues with thread creation, process creation, the memory allocation, and system manipulation required to manage it. Under load this is ALWAYS slower. I think that if there is ever a choice, "do I make startup time faster?" or "Do I make PostgreSQL not need a dump/restore for upgrade" the upgrade problem has a much higher impact to real PostgreSQL sites.
Re: [HACKERS] PostgreSQL Password Cracker
Tom Lane wrote: Devrim GUNDUZ [EMAIL PROTECTED] writes: I had no time to search throug the code; but as far as I understood, it *attacks* the database servers with TCP/IP on, right? No, the program itself simply takes an MD5 hash value and does a brute-force search for a password that generates that MD5 string. The comments at the top suggest sniffing a Postgres session startup exchange in order to see the MD5 value that the user presents; which the attacker would then give to this program. (Forget it if the session is Unix-local rather than TCP, or if it's SSL-encrypted...) This is certainly a theoretically possible attack against someone who has no clue about security, but I don't put any stock in it as a practical attack. For starters, if you are talking to your database across a network that is open to hostile sniffers, you should definitely be using SSL. This is absolutely correct, shouldn't this be in the FAQ?
Re: [HACKERS] PostgreSQL Password Cracker
Bruce Momjian wrote: mlw wrote: The comments at the top suggest sniffing a Postgres session startup exchange in order to see the MD5 value that the user presents; which the attacker would then give to this program. (Forget it if the session is Unix-local rather than TCP, or if it's SSL-encrypted...) This is certainly a theoretically possible attack against someone who has no clue about security, but I don't put any stock in it as a practical attack. For starters, if you are talking to your database across a network that is open to hostile sniffers, you should definitely be using SSL. This is absolutely correct, shouldn't this be in the FAQ? Well, this is a pretty rare issue, so it doesn't seem like an FAQ. People need to understand the ramifications of the various pg_hba.conf settings, and I think our documentation does that. A good DBA will probably read the docs, a bad DBA will probably not, and it is the bad DBA that needs to be guided the most. Maybe not FAQ, but is the a short page of "dos and don'ts?
Re: [HACKERS] Suggestion; WITH VACUUM option
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: How hard would it be to add a WITH (VACUUM) option to UPDATE and DELETE queries? This option would cause the regular vacuum activity -- purging the dead tuple and its index references -- to be done immediately, as part of the statement, instead of being deferred. Easy? Hard? Insane? What do you think? Impossible. You can't vacuum a tuple until the last open transaction that can see it is gone. It is therefore *impossible* for a transaction to vacuum away its own detritus; until the transaction commits, you can't even start to wonder whether other open transactions see it or not. Vacuuming has to be done later, and that being the case, I don't see any real advantage to altering the background vacuum design we have. This does raise an interresting question, and I understand that it is *impossible* to do with PostgreSQL as it currently exists, however, let me just toss this out there: Suppose you do this: update largetable set foo=bar; Lets also assume that largetable has tens of millions of rows. I have databases like this, and I sometimes do operations like this. I have found it more efficient to break up the update into a series of: update largetable set foo=bar where somefield a; vacuum update largetable set foo=bar where somefield b; vacuum update largetable set foo=bar where somefield c; vacuum update largetable set foo=bar where not foo = bar; vacuum On some of my databases a statement which updates all the rows is unworkable in PostgreSQL, on Oracle, however, there is no poblem. For my use, it is a pain in the neck to deal with, but not unworkable. For some other users, it may be a bigger problem. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Password security question
Christopher Kings-Lynne wrote: Hi guys, Just a thought - do we explicitly wipe password strings from RAM after using them? I just read an article (by MS in fact) that illustrates a cute problem. Imagine you memset the password to zeros after using it. There is a good chance that the compiler will simply remove the memset from the object code as it will seem like it can be optimised away... Just wondering... Chris Could you post that link? That seems wrong, an explicit memset certainly changes the operation of the code, and thus should not be optimized away. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Password security question
Greg Copeland wrote: On Tue, 2002-12-17 at 10:49, mlw wrote: Christopher Kings-Lynne wrote: Hi guys, Just a thought - do we explicitly wipe password strings from RAM after using them? I just read an article (by MS in fact) that illustrates a cute problem. Imagine you memset the password to zeros after using it. There is a good chance that the compiler will simply remove the memset from the object code as it will seem like it can be optimised away... Just wondering... Chris Could you post that link? That seems wrong, an explicit memset certainly changes the operation of the code, and thus should not be optimized away. I'd like to see the link too. I can imagine that it would be possible for it to optimize it away if there wasn't an additional read/write access which followed. In other words, why do what is more or less a no-op if it's never accessed again. It has been my experience that the MSC optimizer uses a patented Heisenberg optimizer. :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Password security question
Ken Hirsch wrote: http://msdn.microsoft.com/library/en-us/dncode/html/secure10102002.asp Well, OK, that isn't as bizarre as one could have expected. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] PostgreSQL Global Development Group Announces
Peter Eisentraut wrote: Marc G. Fournier writes: It isn't, but those working on -advocacy were asked to help come up with a stronger release *announcement* then we've had in the past ... Consider that a failed experiment. PostgreSQL is driven by the development group and, to some extent, by the existing user base. The last thing we need is a marketing department in that mix. I am a long term user of PostgreSQL and I think it suffers from a lack of a marketing department. If you have the best restaurant in town, but no one eats there, what's the point? We all correspond and work on PostgreSQL to make it the best we can. To create something good that people can use. One of the prime parts of that sentence is people can use. Like it or not, that means getting the word out. MySQL is an appalling database, but people use it, a lot! Why? Because they really market it. They push it. They craft deceptive benchmarks which show it is better. PostgreSQL doesn't even need to be deceptive. My company is working on a Suite of applications and PostgreSQL is a key component. We will be doing our own local marketing, but it it would help if the PostgreSQL core understood that a clean professional looking website, geared toward end users would make a big difference. Furthermore, I think it would be very rewarding for everyone involved if we could get some of the street cred that MySQL has. PostgreSQL *is* a better database in almost every way. If MySQL virtually owns the open source mind share for SQL databases, it is our fault. Peter, Tom, Bruce, et al. you guys do a great job, IMHO PostgreSQL isn't lacking in anything technical, as of 7.2, with non-locking vacuum, I would consider it a viable database with no caveats. 7.3 is superior. A pure Win32 version would be awesome. I just think that if we could get people equally talented at spreading the word and making the noise, it would make a big difference in the number of users. More users eventually translates to more funding or development. Wouldn't you like to say to someone: I contribute the PostgreSQL project and have them say Cool instead of What's that? ---(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] Logging Feature
Is there a way to get pg logging of plans to be produced in the terse format like when a user types explain select * from foo where bar = x The plan logging is very verbose. Having a lighter version of the logging would be helpful in pinpointing troublesome queries without slogging through pages of explain info. Am I out to lunch? Is there a way to do this (I haven't found it yet, and I have tried several permutations of log parameters) If not, does anyone else see the value? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Shrinkwrap Windows Product, any issues? Anyone? (postmaster windows
Hey this is a cool project. I have been thinking doing the exact ame thing, the console Window of 2K/XP just kills the daemon, yuck. What can I do to help? Igor Georgiev wrote: I am working on getting a shrink-wrapped version of PostgreSQL for Windows Currently it installs a customized version of Cygwin, PostgreSQL 7.2.3, cygipc, psqlodbc, and pgadminII I currently have the setup done. Cool :) I'm now working on postmaster windows shell. It's not finished yet but main things work. Funcionality implemented now is : Console redirection for capture output from postmaster Starting-stoping postmaster Choose for shutdown mode System tray icon Postmaster options are read from registry -postmaster path -datadir -additional options Funcionality not implemented yet, but planned : Writing captured output from postmaster to log file Options setup dialog Edit pg_hba.conf ??? Application is MFC free pure windows API (compiler:gcc-mingw, Dev-C++ IDE) . Here is the screenshot I also be GLAD to read about plans for native windows port in 7.4. If anyone is interested i can post source code, or maybe this firrst steps can go to gborg as a separate project i'm not sure yet. PS: Excuse me for my english, I'm better in C :)
Re: [HACKERS] Shrinkwrap Windows Product, any issues? Anyone? (postmaster
Justin Clift wrote: Igor Georgiev wrote: snip I also be GLAD to read about plans for native windows port in 7.4. If anyone is interested i can post source code, or maybe this firrst steps can go to gborg as a separate project i'm not sure yet. Hi Igor, This would be a really good thing to get into GBorg as a project, so people could work on this through CVS. Would you like to register it as a project? Mark, do you feel it would be better to put your installer plus this together into one project on GBorg too? Not sure, it's just a thought. The installer is simply a script, the ino installer, and a strategy. I install enough Cygwin to run PostgreSQL, and a few batch files, I then compile that into an install file. No biggie. Igor's console program is cool, I was thinking of writing something just like it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Shrinkwrap Windows Product, any issues? Anyone? (postmaster
Justin Clift wrote: Hi Igor, This would be a really good thing to get into GBorg as a project, so people could work on this through CVS. Would you like to register it as a project? Mark, do you feel it would be better to put your installer plus this together into one project on GBorg too? Not sure, it's just a thought. I just want to say, the Windows installer was pretty easy once you decide that you are not going to give the user who installs the system the infinite range of options that they would have if they installed cygwin and went from there. We decided on a good middle of the road installation that would work for advanced users. If they want an enterprise server, they will have to modify the installation themselves. I have developed Windows programs since version 1,x, what I see as one of the bigger hurdles in providing UNIX products on Windows is that the UNIX philosophy is that of Capability not Policy. Windows demands a Policy, i.e. when the install is done, they should be able to press start and use it. To do that with postgresql, you have to create an install that will work for most of the people that will want to use it, out of the box, with no fuss. I know I am pontificating, but I do think there is a HUGE market for PostgreSQL on Windows, we just have to figure out how to get it. What I want, is an install that application developers can use as the basis for their ODBC or SQL based projects, instead of MSSQL. Sort of like a Developers version of PostgreSQL. Once we do that, the we have the hook for more reliable and powerful systems. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Shrinkwrap Windows Product, any issues? Anyone? (postmaster
Justin: Are you involved with gborg? I have been thinking about Igor's console and my installer. I think there is a good enough need to host a project that contains HOWTOs, scripts, and tools to make PostgreSQL easy for Windows deployment. I am working on a HOWTO, a set of Windows batch files, and the install scripts I would be glad to post, and I would be very glad to include Igor's console in the install. It would make a cool offering. Justin Clift wrote: mlw wrote: snip Once we do that, the we have the hook for more reliable and powerful systems. Yep, I pretty much agree. :-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Shrinkwrap Windows Product, any issues? Anyone?
[EMAIL PROTECTED] wrote: I've looked long and hard and can't find any license issues. Does anyone know of any that I may have missed? As far as I can see, as long as I maintain GPL restrictions, I should be fine. PostgreSQL isn't licensed under the GPL, so it sounds to me as though you're confused about the licensing issues. I'm not confused about the licensing issues. PostgreSQL is less restrictive than is GPL. Maybe I should have phrased it as the most restrictive license is GPL, so as long as I maintain GPL restrictions I should be fine. -- (concatenate 'string cbbrowne @cbbrowne.com) http://www3.sympatico.ca/cbbrowne/lsf.html My mom said she learned how to swim. Someone took her out in the lake and threw her off the boat. That's how she learned how to swim. I said, 'Mom, they weren't trying to teach you how to swim.' -- Paula Poundstone ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Shrinkwrap Windows Product, any issues? Anyone?
Dave Page wrote: -Original Message- From: mlw [mailto:[EMAIL PROTECTED]] Sent: 03 December 2002 06:17 To: psqlodbc; [EMAIL PROTECTED] Subject: [HACKERS] Shrinkwrap Windows Product, any issues? Anyone? I am working on getting a shrink-wrapped version of PostgreSQL for Windows Currently it installs a customized version of Cygwin, PostgreSQL 7.2.3, cygipc, psqlodbc, and pgadminII I currently have the setup done. The target audience is not the enterprise, it is aimed at people using Access wanting to upgrade. I've looked long and hard and can't find any license issues. Does anyone know of any that I may have missed? As far as I can see, as long as I maintain GPL restrictions, I should be fine. No problems with pgAdmin, though I do have to wonder how you've done the installer? Regards, Dave. Brute force, of course! Seriously, I have hidden the cygwin environment and simply called it PostgreSQL. I am managing all he environment variables in my installer, and I am using Windows batch files to start bash which executes the PostgreSQL comands. I am using PGAdmin as the administration tool It really looks slick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Shrinkwrap Windows Product, any issues? Anyone?
I am working on getting a shrink-wrapped version of PostgreSQL for Windows Currently it installs a customized version of Cygwin, PostgreSQL 7.2.3, cygipc, psqlodbc, and pgadminII I currently have the setup done. The target audience is not the enterprise, it is aimed at people using Access wanting to upgrade. I've looked long and hard and can't find any license issues. Does anyone know of any that I may have missed? As far as I can see, as long as I maintain GPL restrictions, I should be fine. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] contrib/ intarray, ltree, intagg broken(?) by array
This built and worked on my system. famous last words, huh? Bruce Momjian wrote: Can someone address the intagg issue here, or is the code OK? --- Tom Lane wrote: Joe Conway and I have just committed some changes in the internal representation of Postgres arrays: an element-type-OID field is added to the array header, and alignment calculations are now done the same way as in ordinary tuple storage, instead of taking shortcuts. I believe that these changes need to be reflected into the intarray, ltree, and intagg contrib modules. intarray and ltree both seem to be mapping their own declarations onto arrays using largely-similar code. But while intarray fails its regression test, I find ltree still passes. So I'm confused about what that code is really doing and don't want to touch it. I tried to fix intagg, but since there is no regression test for it I'm unsure whether it's okay. Could you folks take a look at CVS tip and see what changes are needed, if any? In the longer run, it might be possible to improve these routines to be array-type-polymorphic using the new features. But with the 7.3 beta date nearly upon us, I'd counsel first making the existing functionality work again... 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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ? int_aggregate.sql ? intagg.patch ? intagg_test.sql Index: int_aggregate.c === RCS file: /projects/cvsroot/pgsql-server/contrib/intagg/int_aggregate.c,v retrieving revision 1.4 diff -u -r1.4 int_aggregate.c --- int_aggregate.c 2002/08/30 00:28:40 1.4 +++ int_aggregate.c 2002/08/30 15:22:03 @@ -11,7 +11,8 @@ * This file is the property of the Digital Music Network (DMN). * It is being made available to users of the PostgreSQL system * under the BSD license. - * + * + * NOTE: This module requires sizeof(void *) to be the same as sizeof(int) */ #include postgres.h @@ -37,6 +38,9 @@ #include utils/lsyscache.h +/* Uncomment this define if you are compiling for postgres 7.2.x */ +/* #define PG_7_2 */ + /* This is actually a postgres version of a one dimensional array */ typedef struct @@ -96,7 +100,9 @@ p-a.size = cb; p-a.ndim = 0; p-a.flags = 0; +#ifndef PG_7_2 p-a.elemtype = INT4OID; +#endif p-items = 0; p-lower= START_NUM; } @@ -149,7 +155,9 @@ pnew-a.size = cb; pnew-a.ndim=1; pnew-a.flags = 0; +#ifndef PG_7_2 pnew-a.elemtype = INT4OID; +#endif pnew-lower = 0; } else Index: int_aggregate.sql.in === RCS file: /projects/cvsroot/pgsql-server/contrib/intagg/int_aggregate.sql.in,v retrieving revision 1.1 diff -u -r1.1 int_aggregate.sql.in --- int_aggregate.sql.in2002/02/25 03:45:27 1.1 +++ int_aggregate.sql.in2002/08/30 15:22:03 @@ -1,7 +1,7 @@ -- Drop functions +drop aggregate int_array_aggregate(int4); drop function int_agg_state (int4, int4); drop function int_agg_final_array (int4); -drop aggregate int_array_aggregate(int4); drop function int_array_enum (int4[]); @@ -9,14 +9,14 @@ -- Is called for each item in an aggregation create function int_agg_state (int4, int4) returns int4 - as 'MODULE_FILENAME','int_agg_state' + as 'MODULE_PATHNAME','int_agg_state' language 'c'; -- Internal function for the aggregate -- Is called at the end of the aggregation, and returns an array. create function int_agg_final_array (int4) returns int4[] - as 'MODULE_FILENAME','int_agg_final_array' + as 'MODULE_PATHNAME','int_agg_final_array' language 'c'; -- The aggration funcion. @@ -35,6 +35,6 @@ -- as a row. create function int_array_enum(int4[]) returns setof integer - as 'MODULE_FILENAME','int_enum' + as 'MODULE_PATHNAME','int_enum' language 'c'; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Jason Tishler wrote: On Sun, Jun 02, 2002 at 09:33:57PM -0400, mlw wrote: Bruce Momjian wrote: mlw wrote: Like I told Marc, I don't care. You spec out what you want and I'll write it for Windows. That being said, a SysV IPC interface for native Windows would be kind of cool to have. I am wondering why we don't just use the Cygwin shm/sem code in our project, or maybe the Apache stuff; why bother reinventing the wheel. but! in the course of testing some code, I managed to gain some experience with cygwin. I have seen fork() problems with a large number of processes. Since Cygwin's fork() is implemented with WaitForMultipleObjects(), it has a limitation of only 63 children per parent. Also, there can be DLL base address conflicts (causing Cygwin fork() to fail) that are avoidable by rebasing the appropriate DLLs. AFAICT, Cygwin PostgreSQL is currently *not* affected by this issue where as other Cygwin applications such as Python and Apache are. Why would not PostgreSQL be affected by this? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Bruce Momjian wrote: mlw wrote: Like I told Marc, I don't care. You spec out what you want and I'll write it for Windows. That being said, a SysV IPC interface for native Windows would be kind of cool to have. I am wondering why we don't just use the Cygwin shm/sem code in our project, or maybe the Apache stuff; why bother reinventing the wheel. I have not been participating on the list, I don't know why I'm still receiving mail. but! in the course of testing some code, I managed to gain some experience with cygwin. I have seen fork() problems with a large number of processes. For PostgreSQL to be as good on Windows as it is on UNIX, it has to be a native program without cygwin. The shared memory and semaphore management should be done with the postmaster process. The apache stuff is OK, it is just as good as anything else. You may be able to use critical sections in shared memory to implement a fast semaphore, but that would take a bit experimentation. I think what Tom had in mind is to take out the SysV and various OS specific APIs and replace them with a more generic one, behind which, you guys can tune the implementation. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Global Variables (Was: Re: Discontent with development
Marc G. Fournier wrote:
Mark (mlw) ... could you generate a listing of those variables you feel
would need to be moved to a 'global structure' and post that to the list?
That would at least give us a starting point, instead of both sides
guessing at what is/would be involved ...
(1) All the configuration info.
(2) All the globals in postmaster.c
(3) Make sure that memory contexts are initialized correctly.
(4) Exception handling.
(5) Make sure that the statistics and other child processes work too.
In BackendStartup(), rather than pid = fork(); You should split the routine
at that point, one end will be called for error and successful exec of child
process, another will be called for the child.
On UNIX, it will merely be a slight rearrangement of the code. On Windows it
will represent a different function which will copy the globals from the
parent, and call in. Think of it like this:
Currently it looks something like this:
BackendStartup(port)
{
pid = fork();
if( pid 0)
// error
else if(pid )
// Still in Parent
else
// Do child
}
This would have to change to this:
BackendStartup(port)
{
...
pid = StartBackendProcess(port);
if(pid 0)
// Error
else if(pid)
// Still in Parent
else
exit(DoBackend()); // Will never happen on Windows.
}
#ifdef WIN32
StartBackendProcess(port)
{
HANDLE hprocess= CreateProcess(./postgres, );
(initialize process here)
return hprocess;
}
#endif
#ifdef HAS_FORK
StartBackendProcess(port)
{
return fork();
}
#endif
In the main code (src/backend/main), you would have to pass a parameter to the
backend to inform it that is being started as a child of the postmaster, and to
call DoBackend() under windows. MPI does this sort of thing.
I see the whole thing as fairly straight forward. Fork is nothing more than a
copy. We should be able to identify what postmaster does prior to the fork of a
backend. The tricks are to handle the shared memory and semaphores, but those
are easy too. We could create a DLL for Postgres which has implicitly shared
data amongst the processes, and make sure that Postmaster updates all the
shared data prior to entering its server loop. That way the backends are only
reading data from a shared resource.
Once the Windows version of PostgreSQL is able to exec the child, I think the
areas where there are things that were missed should be pretty obvious.
It should take a pretty good engineer a few (full time, 40+ hours) weeks. It
should be mostly done the first week, the last two weeks would be chasing bugs
created by variables that were not initialized. This assumes, of course, that
you are using a cygwin build environment without the cygwin or cygipc dlls. If
we were to use MS C/C++ it would take a much longer time, although ultimately
that may be the desired direction.
P.S.
I have unsubscribed from the hackers list, if you wish to contact me, use my
email address directly.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pgaccess - the discussion is over
Iavor Raytchev wrote: If and when patches for pgaccess appear in significant numbers and for some reason, which I cannot imagine, this procedure doesn't end up being practical, we can consider the alternatives. But before you spend a lot of time building a new infrastructure, let's see some code. -- Peter Eisentraut [EMAIL PROTECTED] We are working on it, because we have some code. Don't you believe us, or do you think we have a lot of free time to waste? We - Chris, Bartus, Boyan and myself, have enough patches we want to merge. And we do not feel like asking for permisson to do it. We sent them to Teo and we were asked by Teo to meet and see what we can do with our patches. And we were nice enough to tell the world about this. I do not feel neither like 'asking for permisson', nor like 'proving' anything. If somebody wants to help - is welcome. I find that this group is frustrating to work with. They seem very intolerant of the plurality. I did a configuration patch several months ago. I liked it, as did some others. It did not affect any existing behavior, but added the ability to store configuration information in a different location than the data, and share files between multiple PostgreSQL instances. Rather than evaluate the patch, and say it needs these changes, or simply applying it, you know, working with the contributor's to make a better project, they ranted and raved how they didn't like it, how they wanted something better, etc. No good technical reasons were given, mind you, just I don't like this. So, I did the work, for what? Nothing. It is pointless for me to make the changes for each release. Fortunately it wasn't too much work. So, my experience tells me that unless the work you do is something they want, you are wasting your time. If you try to get some feedback from them about an approach you wish to take, so you don't waste your time, they flame you and tell you to put up or shut up. If you intend to undertake a major work on PostgreSQL, it had better be for something other than contribution back to the group, otherwise, there is a good possibility that you are going to waste your time. I do not get paid to work on PostgreSQL, the time I spend on it is either my own or for a project I am working on. I am finding it very unsatisfying. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
