Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Hiya, As I've mentioned before, we happilly run and offer PostgreSQL and MySQL hosting to our customers. We also offer shell access which simplifies things a little. I'm a little confused as to why people find having auth control from pg_hba.conf a problem? We never use the same passwords or pam for our DBs either, since it offers a little more security should one or the other be compromised. If you use a tool like webmin, it not really any more complicated. Anyone who complains about it being "too hard" to offer PG as a shared hosting option just hasn't investigated the possibility. In my experience, many ISPs and hosts don't offer it because they beleive the ROI (time, learning, extra maintenance, patching, updates,etc) will not good. Regards Tony. Craig O'Shannessy wrote: On Sun, 30 Nov 2003, Joshua D. Drake wrote: There is another thing too-- MySQL manages connection permissions entirely within the RDBMS, while PostgreSQL relies on the pg_hba.conf. This makes managing a database server in a shared hosting environment a bit harder. While I appreciate the PostgreSQL way of doing things, I realize that it is a bit harder to make work for the average web hosting provider. I am currently looking at the possibility of building a solution, but no one has expressed interest, so I am not sure. Ahh just run different instances for each customer. This wouldn't really work for a ISP would it? A fairly low spec machine with a few hundred low-hit websites, maybe 60 of them wanting a database for their blogs? My ISP runs mysql, I don't get shell access :((, but I can remotely connect to their mysql server from home. If running sixty instances of PostgreSQL, wouldn't you have to have 60 different port numbers, not to mention a whole lot of RAM? I've asked them to put up PostgreSQL as an alternative, but they just say "too hard" and don't want to talk about it. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
I've asked them to put up PostgreSQL as an alternative, but they just say "too hard" and don't want to talk about it. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings I think we may translate 'too hard' into 'I'm too lazy to learn something different and provide better service to my customers'. Or, in the interests of simplicity, 'I'm too dumb'. Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] PostgreSQL from a newcomers perspective
New to PostgreSQL.. not to databases.. or Linux in general.. Let's asume I got convinced by your advocacy and decided to install and play a bit with PostgreSQL to discover it's features and how I can use them. Installation went fine. I decided to read the long version.. at some point I was told to create a user postgres. I didn't find recommended settings for this user like if it needs a shell and where the users homedir is supposed to be.. I mean.. I can understand that PostgreSQL would require something simular to MySQL.. if it does.. then I would expect it to be in the docs->installation->long version. Here I find myself behind a Linux box that can't connect via PHP.. solved that one too.. Found the solution quicker on PHP.net then on postgresql.org.. even searched for postmaster -i (and other keywords I could think of) on postgresql.org.. no results.. FWIW.. seaching for the keyword postmaster did turn up the app_postmaster page.. Long story short, in order to get things going a newcomer needs to search a lot (preferrable Google) and doesn't find all the information in the getting started section... or at the first place he/she looks: the docs found at postgresql.org.. I'm quite sure the information is there but the way to it is not always logical IMHO :-) I'm not assuming to be spoon fed but a more clear/accessible manual would certainly help.. Would be nice if there was a table showing the commands used in MySQL (mysql) and the commands with the same result used in PostgreSQL (psql).. Just my EUR 0,02 B. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
I'd agree that this is probably laziness, or to be fairer, a ROI issue, and again comes down to MySql having more mindshare. I was mainly saying that the statement "Ahh just run different instances for each customer." doesn't sit very well with me, and I doubt it would for any ISP. I can't see much problem with pg_hba.conf, it would make the installation automation a bit more "hacky" probably, because you'd probably write shell/sed/awk scripts to modify the pg_hba.conf, and have to SIGHUP postmaster. It'd also makes it a bit hard to query the information in said file from an automated website admin program, but doesn't seem like a biggie. I wasn't meaning to imply that *I* thought it was "too hard", just what I got told by my ISP. They could have meant many kinds of "hard", from "too little time, too much to do" through to "I'm not so bright and the database contractors too expensive". Craig. On Mon, 1 Dec 2003, Unihost Web Hosting wrote: > Hiya, > > As I've mentioned before, we happilly run and offer PostgreSQL and MySQL > hosting to our customers. We also offer shell access which simplifies > things a little. I'm a little confused as to why people find having > auth control from pg_hba.conf a problem? We never use the same > passwords or pam for our DBs either, since it offers a little more > security should one or the other be compromised. If you use a tool like > webmin, it not really any more complicated. Anyone who complains about > it being "too hard" to offer PG as a shared hosting option just hasn't > investigated the possibility. > > In my experience, many ISPs and hosts don't offer it because they > beleive the ROI (time, learning, extra maintenance, patching, > updates,etc) will not good. > > Regards > > Tony. > > Craig O'Shannessy wrote: > > >On Sun, 30 Nov 2003, Joshua D. Drake wrote: > > > > > > > >>There is another thing too-- MySQL manages connection permissions entirely > >> > >> > >> > >>>within the RDBMS, while PostgreSQL relies on the pg_hba.conf. This makes > >>>managing a database server in a shared hosting environment a bit harder. > >>>While I appreciate the PostgreSQL way of doing things, I realize that it is > >>>a bit harder to make work for the average web hosting provider. I am > >>>currently looking at the possibility of building a solution, but no one has > >>>expressed interest, so I am not sure. > >>> > >>> > >>> > >>> > >>> > >>Ahh just run different instances for each customer. > >> > >> > > > >This wouldn't really work for a ISP would it? A fairly low spec machine > >with a few hundred low-hit websites, maybe 60 of them wanting a database > >for their blogs? > > > >My ISP runs mysql, I don't get shell access :((, but I can remotely > >connect to their mysql server from home. If running sixty instances of > >PostgreSQL, wouldn't you have to have 60 different port numbers, not to > >mention a whole lot of RAM? > > > >I've asked them to put up PostgreSQL as an alternative, but they just say > >"too hard" and don't want to talk about it. > > > > > >---(end of broadcast)--- > >TIP 7: don't forget to increase your free space map settings > > > > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pam authentication for postgres
Jason Tesser wrote: Please post a comprehensive description of what you're trying to do together with the configuration files you use. I thought I did that sorry. I am trying to get Postgres to authenticate through Pam so I can authenticate to Active Directory on our network. All the steps I took are posted below. If you notice the messages I pasted from my logs you will see that winbind is authenticating ok but for some reason Postgres still denies access. How do you manage to get any pam_winbind messages into that if pam_stack is the only referenced module in your configuration? The stuff you quoted below is cut and pasted from the mail I sent to you. I suspect your actual config files look different. I suggest you follow the below description "to the point" until you have pam authentication using the "local unix" passwords (including the group membership for the postgresql user to read /etc/shadow). That is what worked for me on first try just by following that example. From that point on you know that postgres is using pam and you can muck about with the Active Directory link, because from there everything happens in the /etc/pam.d/postgresql file only. Jan here is the messages I have is the log from trying to log in Nov 26 08:55:16 localhost postgresql(pam_unix)[22693]: authentication failure; logname= uid=26 euid=26 tty= ruser= rhost= user=cherring Nov 26 08:55:16 localhost pam_winbind[22693]: user 'cherring' granted acces as you can see winbind is actually granting access but fro some reason poasgres still denies it. weird. any ideas. postgresql/linux/pam setup. 0) configure postgresql for pam, for example [root ( at ) omega tmp]# grep pam /usr/local/pgsql/data/pg_hba.conf hostall all 137.75.0.0255.255.0.0 pam 1) create a /etc/pam.d/postgresql entry, here's how i did mine [root ( at ) omega tmp]# cp /etc/pam.d/passwd /etc/pam.d/postgresql i don't know if it's the best setup, but it works! mine looks like this [root ( at ) omega tmp]# cat /etc/pam.d/postgresql #%PAM-1.0 auth required /lib/security/pam_stack.so service=system-auth accountrequired /lib/security/pam_stack.so service=system-auth password required /lib/security/pam_stack.so service=system-auth ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL from a newcomers perspective
B. van Ouwerkerk writes: > Installation went fine. I decided to read the long version.. at some point > I was told to create a user postgres. > I didn't find recommended settings for this user like if it needs a shell > and where the users homedir is supposed to be.. I mean.. I can understand > that PostgreSQL would require something simular to MySQL.. if it does.. > then I would expect it to be in the docs->installation->long version. Agreed, that could be added. > Here I find myself behind a Linux box that can't connect via PHP.. solved > that one too.. Found the solution quicker on PHP.net then on > postgresql.org.. even searched for postmaster -i (and other keywords I > could think of) on postgresql.org.. no results.. FWIW.. seaching for the > keyword postmaster did turn up the app_postmaster page.. Would you care to tell us what you had to do? Because using the standard installation I can connect via PHP just fine. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL from a newcomers perspective
> Here I find myself behind a Linux box that can't connect via PHP.. solved > that one too.. Found the solution quicker on PHP.net then on > postgresql.org.. even searched for postmaster -i (and other keywords I > could think of) on postgresql.org.. no results.. FWIW.. seaching for the > keyword postmaster did turn up the app_postmaster page.. Would you care to tell us what you had to do? Because using the standard installation I can connect via PHP just fine. I couldn't without modifying either postgresql.conf or start PostgreSQL with --i Obvious if you know it.. but not if you're new.. B. ---(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
[GENERAL] Feature Request for 7.5
Hi all; I have been looking into how to ensure that synchronous replication, etc. could best be implimented. To date, I see only two options: incorporate the replication code into the database backend or have a separate "proxy" which handles the replication. The main problem with incorporating the system into the backend process is that it limits the development to the 10-month timeframe between releases. The main advantage is that the planner could be used to spot queries which alter tuples and thus could "sort out" select queries. I am not sure if this could be done using a proxy approach if rules or user-defined functions were used. Also the proxy approach could be more flexible in that it could allow for different types of communications such as IP sharing which may not be desireable in the database backend. The easiest way of furthering the development of asynchronous replication proxies would be to break off the server-side network protocol handler into a library which would contain functions to bind to ports, listen, and pass messages back to the calling program. The library could then also be installed, but also be redistributable, so that developers could build these solutions. Also, if the protocol does not provide for "select" queries providing notification of affected tuples, an extension to handle that would be helpful and would allow for better load-ballancing. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Pg module for Perl
I think you are right! I originally went to the Perl Module list on CPAN and looked for DBD and Pg. It gave me the pgsql_perl5.1.9.0 link However, when I searched for DBD Pg on Google, it led me to a DBD-Pg-1.22 I think that is what I was looking for. Will give it a try tonight. Thanks! Barb Alex Satrapa wrote: Barbara Lindsey wrote: I have re-built Perl 5.8.2, I have installed PostgreSQL v7.4 and DBI-1.38. Then I tried installing pgsql_perl5-1.9.0 and it just wont work. Have I picked up the wrong module? Are there some specific configs required for any of these to support the Pg DBD? IIRC, the Pg module for Perl is quite distinct to DBD::Pg. You might be trying to install the wrong module. What do you mean by "it just won't work"? If, after installing pgsql_perl5-1.9.0, you get a message about not being able to find "DBD::Pg in @INC", you've probably got the Pg module installed instead. Try "man Pg", see if you've actually got the Pg module installed. HTH Alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Barbara E. Lindsey, COG RDC Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162 CONFIDENTIALITY NOTICE: The information contained in this electronic message is legally privileged and confidential and intended only for the use of the individual(s) or entity(ies) named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this email or any of it's components is strictly prohibited. If you have received this email in error, please contact the sender. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] UNICODE problem on 7.4 with COPY
When I try to import data from a unicode file into PostgreSQL 7.4 under FreeBSD it appears to now understand the Unicode file format. To demonstrate I export a set of Integers into a Unicode file from MSSQL 2000. I samba the file to a FreeBSD box and try to import from psql with COPY. It fails. Wordpad and Notepad both read the file ok, even after I bounce the file via the FreeBSD box (to test samba didn't munge it). FreeBSD 5.1-RELEASE #0 PGSql 7.4 (dl'd and compiled fri 28th Nov 2003) Dual 800MHz P3's I create a database with encoding = UNICODE. I create a table CREATE TABLE testunicode ( anum int4 ) WITHOUT OIDS; I then use psql to import the file, which is a single column of integers. copy testunicode from '/home/toby/itxt/anum.txt'; ERROR: invalid input syntax for integer: "ÿþ1" CONTEXT: COPY testunicode, line 1, column anum: "ÿþ1" When viewing the file as hex I see: FF FE 31 00 31 00 32 00 37 00 39 00 30 00 0D 00 0A 00 ÿ þ 1 . 1 . 2 . 7 . 9 . 0 . . . . . According to http://www.crispen.org/src/archive/0013.html FF FE UTF-16/UCS-2, big endian So, what is going wrong? Why can't I import this very simple unicode file? I've searched the archives and google, but to no avail. Btw, the actual stuff I want to import is larger and more complex, this little table is to demonstrate the problem. Help would be muchly appreciated. Toby ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Feature Request for 7.5
Chris Travers writes: > The easiest way of furthering the development of asynchronous replication > proxies would be to break off the server-side network protocol handler into > a library which would contain functions to bind to ports, listen, and pass > messages back to the calling program. The library could then also be > installed, but also be redistributable, so that developers could build these > solutions. You might be interested in Clustered JDBC (http://c-jdbc.objectweb.org/), which implements a proxy approach but does not require knowledge of the database-specific protocol. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL from a newcomers perspective
B. van Ouwerkerk writes: > >Would you care to tell us what you had to do? Because using the standard > >installation I can connect via PHP just fine. > > I couldn't without modifying either postgresql.conf or start PostgreSQL > with --i There must be something wrong. PHP should be able to connect via Unix-domain sockets. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL from a newcomers perspective
> Here I find myself behind a Linux box that can't connect via PHP.. solved > that one too.. Found the solution quicker on PHP.net then on > postgresql.org.. even searched for postmaster -i (and other keywords I > could think of) on postgresql.org.. no results.. FWIW.. seaching for the > keyword postmaster did turn up the app_postmaster page.. Would you care to tell us what you had to do? Because using the standard installation I can connect via PHP just fine. I couldn't without modifying either postgresql.conf or start PostgreSQL with --i Only one - This was on a default install as per instructions found on the postgresql.org website. Started as per the same instructions: http://www.postgresql.org/docs/current/static/installation.html B. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] UNICODE problem on 7.4 with COPY
Toby Doig wrote: ... So, what is going wrong? Why can't I import this very simple unicode file? I've searched the archives and google, but to no avail. try converting the file to utf-8. iconv -t utf-8 -f utf-16 < unicode-file.txt > utf-8-file.txt ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Postgresql sql query - selecting rows outside a join
Hi all, I am trying to do a query that returns all rows that are _not_ part of a join, and so far I cannot seem to find a query that doesn't take 30 minutes or more to run. The basic query is "select * from tableA where tableA_id NOT IN (select tableA_id from tableB)". Is there a more efficient way of doing this? Regards, Graham -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postgresql sql query - selecting rows outside a join
Hello On Mon, 1 Dec 2003, Graham Leggett wrote: > Hi all, > > I am trying to do a query that returns all rows that are _not_ part of > a join, and so far I cannot seem to find a query that doesn't take 30 > minutes or more to run. > > The basic query is "select * from tableA where tableA_id NOT IN (select > tableA_id from tableB)". > > Is there a more efficient way of doing this? > > Regards, > Graham > -- > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgresql sql query - selecting rows outside a join
> Graham Leggett > > Hi all, > > I am trying to do a query that returns all rows that are > _not_ part of a join, and so far I cannot seem to find a > query that doesn't take 30 minutes or more to run. > > The basic query is "select * from tableA where tableA_id NOT > IN (select tableA_id from tableB)". > > Is there a more efficient way of doing this? Mysql's version to do something like that is, afaik: SELECT * FROM tableA LEFT JOIN tableB USING(tableA_id) WHERE tableB.tableA_id IS NULL; Perhaps that makes more efficient use of your indices. Another version is something like: SELECT *, COUNT(tableB.*) FROM tableA LEFT JOIN tableB USING(tableA_id) GROUP BY columns_of_tableA HAVING count(tableB.*) = 0; And perhaps a rewrite to use EXISTS (although that is claimed to be similar in speed or even slower as of postgres 7.4) is useful: SELECT * FROM tableA WHERE NOT EXISTS(SELECT * FROM tableB WHERE tableB.tableA_id = tableA.tableA_id) There are a few others, but it all depens on your index structure and table sizes whether they work more efficient or not. Best regards, Arjen ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgresql sql query - selecting rows outside a join
Hello If you can, use PostgreSQL version 7.4. There is some optimalisation for this questions. You can change your query from select .. IN (select) to select .. exists (select). More about it you can find in FAQ. regards Pavel On Mon, 1 Dec 2003, Graham Leggett wrote: > Hi all, > > I am trying to do a query that returns all rows that are _not_ part of > a join, and so far I cannot seem to find a query that doesn't take 30 > minutes or more to run. > > The basic query is "select * from tableA where tableA_id NOT IN (select > tableA_id from tableB)". > > Is there a more efficient way of doing this? > > Regards, > Graham > -- > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql sql query - selecting rows outside a join
A suggestion is to use a left outer join and then test for rows with a right-hand null value (ones where the join failed). The following example creates 2 tables, t1 and t2. t1 has related records in t2 and the relation is indicated by t1.rel->t2.id create table t1 ( id integer, rel integer, label varchar(10) ); create table t2 ( id integer, label varchar(10) ); insert into t1 (id, rel, label) values (1, 1, 'bob'); insert into t1 (id, rel, label) values (2, 2, 'sam'); insert into t1 (id, rel, label) values (3, 0, 'alice'); insert into t2 (id, label) values (1, 'martin'); insert into t2 (id, label) values (2, 'gwen'); -- this shows you all the records select t1.*, t2.* from t1 left outer join t2 on (t1.rel = t2.id); -- this shows you those where the inner join fails (your where X NOT IN (y) stuff) -- the result should be where t1.id=3 because it has a t1.rel of 0 therefore no related -- record in t2 select t1.*, t2.* from t1 left outer join t2 on (t1.rel = t2.id) where t2.id is null Toby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Graham Leggett Sent: 01 December 2003 17:16 To: [EMAIL PROTECTED] Subject: [GENERAL] Postgresql sql query - selecting rows outside a join Hi all, I am trying to do a query that returns all rows that are _not_ part of a join, and so far I cannot seem to find a query that doesn't take 30 minutes or more to run. The basic query is "select * from tableA where tableA_id NOT IN (select tableA_id from tableB)". Is there a more efficient way of doing this? Regards, Graham -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] UNICODE problem on 7.4 with COPY
Same error as before Toby Doig Software Development Manager Vibrant Media [EMAIL PROTECTED] 0207 239 0134 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gianni Mariani Sent: 01 December 2003 16:37 To: [EMAIL PROTECTED] Subject: Re: [GENERAL] UNICODE problem on 7.4 with COPY Toby Doig wrote: ... >So, what is going wrong? Why can't I import this very simple unicode file? >I've searched the archives and google, but to no avail. > > try converting the file to utf-8. iconv -t utf-8 -f utf-16 < unicode-file.txt > utf-8-file.txt ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
> [sNip] > >> In summary, you could be charging them for some very expensive courier > >> services, if for which they don't pay then you won't deliver. =) > > > > Of course a competitor could purchase a copy or get it from a customer > > and set up shop right away selling it too. > > Ah, so even the GPL has a few loop holes! =D I don't think that is a loophole I think that is the whole point of it. rg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql sql query - selecting rows outside a join
On Dec 1, 2003, at 10:15 AM, Graham Leggett wrote: Hi all, I am trying to do a query that returns all rows that are _not_ part of a join, and so far I cannot seem to find a query that doesn't take 30 minutes or more to run. The basic query is "select * from tableA where tableA_id NOT IN (select tableA_id from tableB)". Is there a more efficient way of doing this? Regards, Graham -- This should be more efficient: select * from tableA where not exists (select null from tableB where tableA_id = tableA.tableA_id); Version 7.4 fixes the slow IN - NOT IN, but I haven't tested it myself yet to see how it compares to this. Adam Ruth ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SRPM (PGDG) will not build
On Tue, 25 Nov 2003, Lamar Owen wrote:
> Hmmm The lines of error in question:
> make[2]: Entering directory `/usr/src/redhat/BUILD/postgresql-7.4/src/port'
> gcc -O2 -march=i386 -mcpu=i686 -Wall -Wmissing-prototypes
> -Wmissing-declarations prod -I../../src/include -D_GNU_SOURCE -c -o path.o
> path.c
> gcc: cannot specify -o with -c or -S and multiple compilations
>
> Where is 'prod' coming from after -Wmissing-declarations? This is the source
> of the errorlooking...will get back shortly.
Still not sure what the problem is but I can identify it as being tied to
being in the vserver context. I compiled in the base/main server with no
problems. I'll pass this along to the vserver development team to see if
it causes a light to come on.
Rod
--
"Open Source Software - Usually you get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL from a newcomers perspective
> I couldn't without modifying either postgresql.conf or start PostgreSQL > with --i There must be something wrong. PHP should be able to connect via Unix-domain sockets. I'll be the first to admit that I might have made a mistake. This is on a box I use for testing only.. so nothing bad will happen if I muck things up.. This is the error I got in my errorlog: Unable to connect to PostgreSQL server: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket "/tmp/5432"? in /path_to/filename.php on line 61 From a little search on Google it seems a pretty common problem.. So if I made a mistake I'm probably not the first :-) B. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] XML & Postgres Functions
Hi all- I'm migrating to postgres from the MS SQL Server land, as I can see a great potential with postgres, I was wondering if anyone has experimented or started a project with XML inside user defined functions? I've seen the contrib/xml shipped with the distro, as I see it's usefulness, it's not quite what I had in mind (lacks the ablity to return recordset/table of nodes). Also, the project XpSQL seems interesting, but still not it. I'm looking for a solution similar to the OpenXML that MS implements in their SQL Server. A way to parse XML into a table and/or a temp table for use inside a function. For example, I would like to serialize my objects into XML, send them to a postgres function for saving/updating the relational data inside postgres. I envision something of the following could be useful: XML -- ... UDF -- create function somefunc(xmldata text) begin xmldoc := preparedoc(xmldata); ... update tablename set field1=x.field1, field2=x.field2, from xmltable(xmldoc, '/table/[EMAIL PROTECTED]') as x ... preform removedoc(xmldoc); end; By using XML & XPath, one could be very creative preforming updates to the tables via the xml data passed to the function. By having this functionality, one could have their applications communicate with XML structures instead of database structures and either side could change without drastically effecting the other. Also, by have the application obey XML structures, the database schema is unknown and therefore essentially hidden from the programmers as they don't need to be bothered with how the data is actually stored. While my motivation isn't to hide the database schema from programmers, but to provide simple method for having applications send data to the data tier for storage. It's much easier to define complex object structure in XML and then let the database worry about storing it relationally. I'm not sure if I'm missing the point with postgres, but we've been doing this with MS SQL Server for sometime, while it's not the most effienct method of sending data to the database, I find it most elegant from the application side, simplify code and promotes code reusablity. Has anyone done this before with postgres, if so could you share your experiences? Also, if no one has attempted this, does anyone else share the need for something like this? I'm thinking about starting this project if there is a greater need out there. Best Regards, David [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Good open source mailing list system PHP / Postgresql
On Wed, 19 Nov 2003, Randolf Richardson, DevNet SysOp 29 wrote: > > An ISP I belong to uses Majordomo for their mailing list system. I'd like > > to encourage them to move to a system that uses a database, preferably > > psql which they already run on their server. Anything out there in Php? > > I doubt there's anything in PHP since PHP is a language purely used for > the dynamic generation of web pages (and possibly other types of documents > for any other systems which are able to embed PHP on the server-side in a > similar fashion to Dynamic HTML in the way Apache HTTPd does). > > You may have more success if you search on PERL or other languages. This is simply not true. PHP comes in both a web ready embedded version, as well as a CLI version, and is quite capable, even of handling things like streams and such, and can even be used to write a daemon listening on a port quite easily. Just because it (probably) hasn't been used to write such a system doesn't mean you couldn't do it in PHP. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL from a newcomers perspective
On Mon, 2003-12-01 at 17:50, B. van Ouwerkerk wrote: > This is the error I got in my errorlog: > Unable to connect to PostgreSQL server: could not connect to server: > Connection refused > Is the server running locally and accepting > connections on Unix domain socket "/tmp/5432"? > in /path_to/filename.php on line 61 That socket path is wrong. It should be /tmp/.s.PGSQL.5432. How is PHP getting it? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Who is like unto thee, O LORD, among the gods? who is like thee, glorious in holiness, fearful in praises, doing wonders?" Exodus 15:11 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] disaster recovery
> This is only a problem for ext2. Ext3, Reiser, XFS, JFS are all fine, > though you get better performance from them by mounting them > 'writeback'. What does 'writeback' do exactly? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Undropping a column?
I just dropped a column that I wish I hadn't. Is there some simple update i could do to the pg_* tables that would undrop it? I haven't done any other updates though autovacuum may have run. Even if I just manage to extract the data then have to restore from a backup to get an uncorrupted data dictionary and reload the data it might be useful. thanks. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL from a newcomers perspective
The problem here is really on the PHP side, because the docs of PHP don't
tell you how to create a connect string that works for local Postgres
installs (well, the docs exist, but they are hard to find).
For PEAR, it's
$db = DB::connect('pgsql://[EMAIL PROTECTED](/tmp)/DBNAME');
Jon
On Mon, 1 Dec 2003, Peter Eisentraut wrote:
> B. van Ouwerkerk writes:
>
> > Installation went fine. I decided to read the long version.. at some point
> > I was told to create a user postgres.
> > I didn't find recommended settings for this user like if it needs a shell
> > and where the users homedir is supposed to be.. I mean.. I can understand
> > that PostgreSQL would require something simular to MySQL.. if it does..
> > then I would expect it to be in the docs->installation->long version.
>
> Agreed, that could be added.
>
> > Here I find myself behind a Linux box that can't connect via PHP.. solved
> > that one too.. Found the solution quicker on PHP.net then on
> > postgresql.org.. even searched for postmaster -i (and other keywords I
> > could think of) on postgresql.org.. no results.. FWIW.. seaching for the
> > keyword postmaster did turn up the app_postmaster page..
>
> Would you care to tell us what you had to do? Because using the standard
> installation I can connect via PHP just fine.
>
> --
> Peter Eisentraut [EMAIL PROTECTED]
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] disaster recovery
"Rick Gigger" <[EMAIL PROTECTED]> writes: >> This is only a problem for ext2. Ext3, Reiser, XFS, JFS are all fine, >> though you get better performance from them by mounting them >> 'writeback'. > > What does 'writeback' do exactly? AFAIK 'writeback' only applies to ext3. The 'data=writeback' setting journals metadata but not data, so it's faster but may lose file contents in case of a crash. For Postgres, which calls fsync() on the WAL, this is not an issue since when fsync() returns the file contents are commited to disk. AFAIK XFS and JFS are always in 'writeback' mode; I'm not sure about Reiser. -Doug ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Undropping a column?
Greg Stark <[EMAIL PROTECTED]> writes: > I just dropped a column that I wish I hadn't. Is there some simple update i > could do to the pg_* tables that would undrop it? I haven't done any other > updates though autovacuum may have run. Ok, I seem to have done it with this: update pg_attribute set attisdropped = 'f',atttypid= 25 where attrelid = 17839 and attname = 'pg.dropped.9'; I've now updated the table to copy this data over to the new column and redropped the old column. Have I messed up my database? -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL, MySQL, etc., was Re: PostgreSQL is much
Christopher Browne wrote: A long time ago, in a galaxy far, far away, Randolf Richardson <[EMAIL PROTECTED]> wrote: [sNip] the difference is that with mysql, nothing pushes the table out of memory; it always stays in memory. in postgresql, a big query on another tables, or perhaps a vacuum, or other highly active applications on the same server can cause the small tables to be pushed out of memory. both approches have positives and negatives, and in many cases you would probably notice no differance If this is a small heavily used table, 7.5 with the new ARC buffer management policy should do much better. Even better, the table does not actually need to be small: the heavily used portion will stay in memory where it can be very fast, and the rest will be just wait its turn on disk. Is this a configurable option by any chance? If not, then perhaps it should be on a per-table, per-index (etc.) basis. It is a MUCH BETTER thing to have policies that don't require configuration effort. One of the characteristic problems with Oracle is that you have immense numbers of "knobs" to tune. You can get it to work "just right" if you throw a large enough horde of DBAs at it. In the case of the ARC policy, what Jan is trying to do is to come up with a strategy that is an improvement irrespective of the characteristics of the table. If that works out as hoped for, there will be no need to "configure" anything in order to take advantage of it. The stuff is in CVS HEAD. Randolf, look at the README file in src/backend/storage/buffer for some explanations. Jan You'd find your applications running faster simply by installing a 7.5 server; no need to configure anything. It's like getting Pentium chip with improved execution strategies; you don't have to recompile anything (the way IA-64 mandates it); you just install the app on the new box and watch it speed up. -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] What is WAL used for?
Jonathan Bartlett wrote: > > WAL with ext3's data journaling is quite unnecessary because the WAL > > sort of IS the database's journal. > > I believe you are mistaken. ext3 data journalling only does the > filesystem. It has no concept of the structure of the database itself. > WAL is still necessary to keep consistency on the table itself. What he means is that PostgreSQL doesn't need the file contents restore pristine on crash recovery, just the directory structure and WAL can recreate the file contents. -- 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Good open source mailing list system PHP / Postgresql
On Mon, Dec 01, 2003 at 12:42:39PM -0800, Randal L. Schwartz wrote: > > "scott" == scott marlowe <[EMAIL PROTECTED]> writes: > > scott> This is simply not true. PHP comes in both a web ready > scott> embedded version, as well as a CLI version, and is quite > scott> capable, even of handling things like streams and such, and can > scott> even be used to write a daemon listening on a port quite > scott> easily. > > But PHP is where Perl was five years ago, and continually plays > catchup. If you want real work done, use the right tool. PHP is fine > for nifty web pages for smallish sites, but Perl takes over when the > real heavy lifting is needed. Someone pointed out on this list some time ago that you can work around the performance issue of starting a Perl interpreter and the compiling phase by using PersistentPerl. I have been using it since for smallish things and kinda like it but have not really had the chance to test it extensively. Apparently it can keep persistent connections reasonably well, for example (though some cruft on sub END is apparently needed ...) What's your opinion on the thing? Have you used it with PostgreSQL? Persistent DBI connections and such? Maybe you could write an article on the subject? :-) (Maybe this belongs to a Perl list, but I'm on none ... any suggestion of a better place?) -- Alvaro Herrera () "There was no reply" (Kernel Traffic) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Good open source mailing list system PHP / Postgresql
On 1 Dec 2003, Randal L. Schwartz wrote: > > "scott" == scott marlowe <[EMAIL PROTECTED]> writes: > > scott> This is simply not true. PHP comes in both a web ready > scott> embedded version, as well as a CLI version, and is quite > scott> capable, even of handling things like streams and such, and can > scott> even be used to write a daemon listening on a port quite > scott> easily. > > But PHP is where Perl was five years ago, and continually plays > catchup. If you want real work done, use the right tool. PHP is fine > for nifty web pages for smallish sites, but Perl takes over when the > real heavy lifting is needed. Bold assertion, with little to back it up. Name something Perl is so much better at than PHP, and you'll likely find that PHP now does it and does it well. We quit programming in Perl a couple years ago in my shop, as it was far easier to configure PHP on a server and have it do what we needed. > scott> Just because it (probably) hasn't been used to write such a > scott> system doesn't mean you couldn't do it in PHP. > > You could do it in assembler too. But why? Ignoring your assembler point, as it's a poor comparison, and we both know it... Why do it in PHP: Because it's a good choice for such things, having Perl's easy string handling with C's simple file interface functions. Because there's always somewhere else to go that no one else has thought of, and a different way of doing it. Because it's a good language that has a lot of people who say nebulous bad things about it but have pitiful little real experience with it? I'm not sure. > To keep from wasting precious human cycles, you need something with > the code density and flexibility of Perl or better. Python, Ruby, > that league. Not C, not Java, not PHP. Again, show me an area where PHP is actually deficient here. Something Perl or Ruby does better that would pertain to a mailing list. Don't just wave your hands around, give us a concrete example of its short comings. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL
On Mon, 1 Dec 2003, Jan Wieck wrote: > Jason Tesser wrote: > > > Quoted as gospel by various people: > >>> MySQL cannot even handle sub-queries yet. > > > >> BTW, is that really still true? I thought they had at least some > >> support for subqueries by now. > > > > yes sub queries in 4.1 which is still alpha > > "yes sub queries" is IMHO as precise as "yes foreign keys" ... look, > they have foreign key support, but do they have DEFERRED, ON DELETE SET > NULL, ON UPDATE CASCADE, all the stuff that makes it complete? They're working on those things, but as usual, MySQL got the big things mostly right, and the little things horribly wrong. If you create a table with type=innodb on a database server that isn't compiled to support innodb tables, it will silently fail, and silenly allow you to build non-existent foreign keys. sorry, but until MySQL treats errors and their reporting with such a la de da attitude, I won't trust it. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Good open source mailing list system PHP / Postgresql
On Mon, Dec 01, 2003 at 04:03:02PM -0500, Ericson Smith wrote: > PHP has long ago caught up with Perl, and I believe the the OOP features > available in PHP 5, will easily leapfrog over Perl. Having said that, we > still code a lot of Perl, simply because of inertia and an existing > codebase. Huh, when is PHP 5 due? When is Perl 6 due? Does Parrot have an working implementation? Is it useful for anything? -- Alvaro Herrera () Jude: I wish humans laid eggs Ringlord: Why would you want humans to lay eggs? Jude: So I can eat them ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Equivalent to DBMS_JOB
Arturo Pérez <[EMAIL PROTECTED]> wrote: > Oracle has a kind of cron built-in called DBMS_JOB. You can use it > to run SQL or stored procedures at certain times of day, etc. > > Does postgreSQL offer anything similar? And please don't say use > cron. Why not? > Cron has issues that I'd like to avoid. What issues are those? Please be specific. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 The laissez-faire argument relies on the same tacit appeal to perfection as does communism. George Soros ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Equivalent to DBMS_JOB
On Sun, Nov 30, 2003 at 12:19:34AM -0500, Arturo Pérez wrote: > Does postgreSQL offer anything similar? And please don't > say use cron. Cron has issues that I'd like to avoid. Huh, just curious: what issues does cron have? -- Alvaro Herrera () "Saca el libro que tu religión considere como el indicado para encontrar la oración que traiga paz a tu alma. Luego rebootea el computador y ve si funciona" (Carlos Duclós) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Good open source mailing list system PHP / Postgresql
"scott.marlowe" <[EMAIL PROTECTED]> writes: > Again, show me an area where PHP is actually deficient here. Something > Perl or Ruby does better that would pertain to a mailing list. Don't just > wave your hands around, give us a concrete example of its short comings. Error handling. The lack of exceptions is driving me absolutely insane. But what does all this all this have to do with Postgres? -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Large objects [BLOB] again - general howto
Thanks! This is exactly what I wanted to know when I first asked the question. And it is the only response that seems to make sense. Does anyone else have experiecne with this? rg < Here's a quick list of my experiences with BLOB's and such. Performance is just fine, I get about 1M hits a month and haven't had any problems. Use a BLOB if you don't need to search though the data. The main reason being that bytea and text types are parsed. To explain, your entire SQL statement has to be parsed to make sure it's valid, this includes you 100 meg jpeg. Slow, REAL slow. I have found that it is best to have a separate connection for BLOB's and one for everything else. Mind you, this is with Java, but the autocommit settings on the connection don't appear to be thread safe, so in high traffic you can accidentally cut off a transfer, or stop one before it ever starts. -jj- > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Undropping a column?
Tom Lane wrote: Greg Stark <[EMAIL PROTECTED]> writes: I just dropped a column that I wish I hadn't. Is there some simple update i could do to the pg_* tables that would undrop it? You could rsync the database back from your live off-site backup, or rifle through the piles of backups to recover from last night's archive backup :) Another alternative, if it's just the schema not the data you need back, is simply to "alter table add column ..." the column back. Note that if you want it to be "not null" or "default x" you have to add the column, update the table with the default value in each column, alter the column to be not null then alter the column to be default x. Though this probably isn't what you were looking for, it's still on the archives for someone else to stumble across c/o Google Alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] perl(Pg) (S)RPM
Any pointers as to why (really) I'm getting this error when I try to
install Pg 7.4 from a SRPM build?
perl(Pg) is needed by postgresql-contrib-7.4-0.2PGDG
I only disabled tcl, tkpkg, pltcl, and python in the SPEC file. I could
not install the contrib stuff but I really want the plperl and plperlu
languages.
Or should I take this to the ports list?
TIA,
Rod
--
"Open Source Software - Usually you get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Good open source mailing list system PHP / Postgresql
On 1 Dec 2003, Greg Stark wrote: > > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > > Again, show me an area where PHP is actually deficient here. Something > > Perl or Ruby does better that would pertain to a mailing list. Don't just > > wave your hands around, give us a concrete example of its short comings. > > Error handling. The lack of exceptions is driving me absolutely insane. Have you had a look at this: http://www.phpclasses.org/mirrors.html?page=%2Fbrowse.html%2Fpackage%2F345.html > But what does all this all this have to do with Postgres? Tangential, tangential. But it appears every time someone mentions using PHP around here he gets bashed for it. It's tiring to listen to. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] perl(Pg) (S)RPM
Roderick A. Anderson writes: > Any pointers as to why (really) I'm getting this error when I try to > install Pg 7.4 from a SRPM build? > > perl(Pg) is needed by postgresql-contrib-7.4-0.2PGDG As the message says, one package requires the other. > I only disabled tcl, tkpkg, pltcl, and python in the SPEC file. I could > not install the contrib stuff but I really want the plperl and plperlu > languages. These languages are not in the -contrib package. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] patch for pg_autovacuum
Also applied to 7.4.X. --- Brian Hirt wrote: > here's a patch that joins on pg_class.oid instead of > pg_class.relfilenode, also i have renamed the table structure from > relfilenode to relid. > [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] patch for pg_autovacuum
Patch applied. Thanks. --- Brian Hirt wrote: > here's a patch that joins on pg_class.oid instead of > pg_class.relfilenode, also i have renamed the table structure from > relfilenode to relid. > [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Good open source mailing list system PHP / Postgresql
At 04:03 PM 12/01/2003 -0500, you wrote: > and I believe the the OOP features available in PHP 5, will easily leapfrog over Perl. As my daddy used to say "twice zero is still a small number" I'm a pretty big OO fan but when I use Perl I don't bother with the OO features once I had looked at them. Comparing an OO feature set to Perl isinteresting :) Perl still seems to me to much better as a glue between stuff than as something to do the heavy stuff in it's own right. I find Python much more scalable in this respect (and Smalltalk *much* more so), both of which happen to work very well with Postgresql ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Pg module for Perl [SUCCESS]
This was perfect. I also had to set the DBI_* envs. See test results
below. I then installed and successfully ran my own test.
Thanks to all who replied!
Barb
Test Results:
$ make test
PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00basic...ok
t/01connect.ok 2/2#
# Package Version
# DBD::Pg 1.31
# DBI 1.38
# Postgres 7.4
t/01connect.ok
t/01constants...ok
t/01setup...ok
t/02prepare.ok
t/03bindok
t/04execute.ok
t/04rowsok
t/05fetch...ok
t/06disconnect..ok
t/07reuse...ok
t/08txn.ok
t/09autocommit..ok
t/10chopblanks..ok
t/11quoting.ok
t/12placeholdersok
t/13pgtype..ok
t/15column_info.ok
t/15funct...ok
t/15table_attrs.ok
t/15table_info..ok
t/16pgbooltf# DBD::Pg version: 1.31
t/16pgbooltfok
t/17arrays..ok
t/99_podskipped
all skipped: Test::Pod 1.00 required for testing POD
t/99cleanup.ok
All tests successful, 1 test skipped.
Files=25, Tests=268, 34 wallclock secs (10.72 cusr + 0.99 csys = 11.71 CPU)
:)
> On Mon, 1 Dec 2003, Barbara Lindsey wrote:
>
>> However, when I searched for DBD Pg on Google, it led me to a
>> DBD-Pg-1.22
>
> Try
>
> http://search.cpan.org/~rudy/DBD-Pg-1.31/Pg.pm
>
> Not sure if there are a lot of dependency issues going to Pg 7.4 as I'm
> in the process upgrading to 7.4 and all it's friends.
>
> One other issue that comes when installing DBI and/or DBD is the from
> the current (1.31) README file.
>
> ...
>
> INSTALLATION:
> -
>
> By default Makefile.PL uses App:Info to find the location of the
> PostgreSQL library and include directories. However, if you want to
> control it yourself, define the environment variables POSTGRES_INCLUDE
> and POSTGRES_LIB, or POSTGRES_HOME. Note that if you've compiled
> PostgreSQL with SSL support, you must define the POSTGRES_LIB
> environment varable and add "-lssl" to it, like this:
>
> export POSTGRES_LIB="/usr/local/pgsql/lib -lssl"
>
> ...
>
> Good luck to us both.
>
>
> Rod
> --
> "Open Source Software - Usually you get more than you pay for..."
>"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
I used it first because 1) someone suggested it and I didn't know any better 2) install, setup, maintanance and using it is easier than breathing. You'd be surprised how much of a difference it makes to a newbie to not have to do things like vacuum regularly and the ability to change a column type (I'm not saying this is a good idea, just that it seemed nice at the time), stuff like that. 3) their online documentation was great, learning how to do new stuff was fast and easy 4) It SEEMED to work fine (I say seemed because I never had anything happen to me like an int overflow problem) 5) For the type of work I started off with I didn't badly need the features that mysql lacks I'm betting that this is the case with many mysql users. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, November 29, 2003 10:01 AM Subject: Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments > "Rod K" <[EMAIL PROTECTED]> writes: > > Paul Thomas wrote: > >> Much of the populatity of MySQL seems to stem from PHPs out-of-the-box > >> support for it. > > > This is incorrect. The embedded mysql client library was not added until > > PHP4.0 RC1. PHP's popularity existed long before this. The real culprit > > causing the popularity of MySQL was it's ubiquity among hosting providers > > and the virtual non-existence of PG in that arena. If PG had been more > > friendly to shared hosting environments, perhaps this situation wouldn't > > have arisen. > > You are both engaging in the most blatant form of historical > revisionism. Of course PHP's support for MySQL didn't drive MySQL > adoption --- it was the other way around, PHP adapted to MySQL because > that was what was out there. I think "friendly to shared hosting > environments" is a made-up reason as well. The real reason PG lost > mindshare to MySQL in the early web days is that at the time, PG was > hard to install, somewhat buggy, and poorly documented. (Which was not > surprising considering that none of these mattered much in its original > academic environment.) MySQL didn't do much, maybe, but what it could > do it did pretty well and without install/learning curve hassles. We > had mostly caught up on those criteria by perhaps 7.1 or 7.2, but the > mindshare gap remains. > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Note: I am a php developer and I love it, but... >In dealing with web applications and frontends to database or >even just a dynamic web site PHP has every bit the power and ability that >Java does and the development time is way down. Uh, how about threads. I know that you don't need them much but it sure would be nice to be able to do background processing. >If you need more power >IMO Python is the way to go. I am not that familiar with pything, not to get off topic here but what you can do in python that you can't do in PHP? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] fmgr_info: function 7390843: cache lookup failed
I am getting this error: fmgr_info: function 7390843: cache lookup failed when trying to insert some data into a table using the tsearch2 contrib module (this is postgresql 7.3). I have the following trigger defined: BEGIN NEW.search_vector := setweight(to_tsvector(coalesce(NEW.display_text, '')), 'A') || to_tsvector(coalesce(NEW.content_desc, '')); RETURN NEW; END; Which I suspect is causing the problem. Where can I look up that function number to verify this? Any suggestions? culley ---(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
[GENERAL] Hostname of server
Is there a way to retrieve the name of the server in a query? something akin to @@SERVERNAME on mssql. toby ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL
> >If you need more power IMO Python is the way to go. > I am not that familiar with pything, not to get off topic here but what you >can do in python that you can't do in PHP? Well if they are both Turing complete, arguably not much :) It's not the 'what' it's the 'how' I'm equating Perl with PHP much in this because what little I've seen of PHP it looks a lot like Perl to me; more simularity between the two then between either and Python Python has a much simpler and more elegant object-class infrastructure than Perl. It's integrated better unto the natural feel of the language. It's easier to use (especially developing new classes) and easier to read and that makes it easier to build large (compliacted, large number of classes, etc..) programs in it. Python also reads more obviously, in that it doesn't have a lot of 'default context' ($_) and 'scalar context versus array context' and cute shortcuts and stuff floating around, which makes it easier to read, and more importantly easier for teams to read. This, again, affects the ability to build large (complex) programs because it's easier for teams of developers to work together on the same code base and understand what each other is doing. A nit of mine is languages that insist on using $ and @ and other special symbols for variable names. That's great for inline striing substitution but really annoying in a couple thousand lines of code with lots of variables Personally, I would never use Perl for anything over about one hundred lines and about one developer if I had any say in the matter. Takeing Perl and mixing Presentation (HTML) with Business Logic (embedded scripts) wihich seems to be PHPs claim to fame just really sits wrong with my software design mentatiliy ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] PG_DUMP question
Hi, is ther a way to pass a password to the pg_dump command to avoid being prompted for it. I want to execute the dump from a perl or shell script and dont want to open accees for the user in the pg_hba.conf I tried PGUSER=userid PGPASSWD=passwd pg_dump -Fc -f dump.db mydb; Thanks Alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Here is a link to the sql for smarties book:
http://www.amazon.com/exec/obidos/tg/detail/-/1558603239/102-3995931-726?v=glance
by Joe Celko
Has some cool ways of handling trees in sql
- Original Message -
From: "Chris Travers" <[EMAIL PROTECTED]>
To: "Tony" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, November 30, 2003 5:49 AM
Subject: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
> Re: [GENERAL] PostgreSQL Advocacy, Thoughts and CommentsRegarding the
> learning curve issue, maybe people can recommend their favorite books. I
> recommend "SQL Unleashed" (I forget the author), pub. Samms. "SQL For
> Smarties" also gets recommended often around here, but again, I don't know
> the author (or in this case, even the publisher). Maybe there are others
> too that people can recommend.
>
> As for the paper-- I think your title is good ("PostgreSQL - Ideal for any
> application development"), and I will have to look at how to organize it.
I
> wanted to cover the following topics:
> 1) Different types of databases, and what is meant by Object Relational
> 2) Enterprise-ready features (Views, Stored Proceedures, Subselects,
etc.)
> 3) RDBMS tasks and how these features fit in.
>
> Perhaps a followup paper could be written as a basic treatise in database
> design.
>
> The other issues I am seing here involve finding a suitable venue for
> publication. Any suggestions here are welcome also.
>
> Best Wishes,
> Chris Travers
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL
On Mon, Dec 01, 2003 at 05:18:17PM -0700, Jay O'Connor wrote: > Personally, I would never use Perl for anything over about one hundred > lines and about one developer if I had any say in the matter. Takeing > Perl and mixing Presentation (HTML) with Business Logic (embedded > scripts) wihich seems to be PHPs claim to fame just really sits wrong > with my software design mentatiliy Well, I have used Perl for a couple of thousand-lines-projects and while I can't say it's wonderful, it's usable as far as one is strict (i.e. code for strict and warnings), and use implicit things as less as possible. If you abstract things the right way it's not the nightmare you think it is. Agreed, the language grants you the power to do it "the wrong way" (write-only language), but I distrust anything that takes that power away (may I say Java?). PHP on the other hand, while I don't quite like it, is not also the nightmare you make of it. You are of course not forced to embed HTML in the logic -- in these days, doing so is folly. Most [sane] people uses templates. Same with Perl. Python may be better, but keep in mind that it _also_ grants you the power to do thing the wrong way. In fact, a colleague of mine is right now ranting about a specific project, saying that he will have to write it almost completely from scratch to be able to extract certains parts of the functionality -- the author apparently did not abstract them correctly. I have yet to use Python much, but the little I have seen I like. -- Alvaro Herrera () Al principio era UNIX, y UNIX habló y dijo: "Hello world\n". No dijo "Hello New Jersey\n", ni "Hello USA\n". ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG_DUMP question
On Tue, Dec 02, 2003 at 09:31:43AM +0900, Alex wrote: > is ther a way to pass a password to the pg_dump command to avoid being > prompted for it. > I want to execute the dump from a perl or shell script and dont want to > open accees for the user in the pg_hba.conf Use the ~/.pgpass file. It will work for any libpq program, not just pg_dump. -- Alvaro Herrera () "I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL
> "Jay" == Jay O'Connor <[EMAIL PROTECTED]> writes: Jay> Python also reads more obviously, in that it doesn't have a lot of Jay> 'default context' ($_) and 'scalar context versus array context' and Jay> cute shortcuts and stuff floating around, which makes it easier to Jay> read, and more importantly easier for teams to read. It also has some things which are functions, and others which are methods, with no rhyme or reason except for a historical basis. And that's just the beginning. (Why are tuples read only? Sorry, cheap shot.) Please don't paint Python as nice and regular. It has nearly as many odd things to know as Perl does. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <[EMAIL PROTECTED]> http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL
> python that you can't do in PHP? Python is an immensely powerful language. It is essentially the successor to both LISP and Smalltalk. It has things like closures (well, not complete, but pretty close), generators, a huge OO library, easy-to-use exceptions (i.e. - in comparison to Java), and I believe with stackless Python you can also do continuations. Also, there's some really cool add-on modules. Psyco, for instance, is a run-time optimizing compiler, that compiles several versions of your code based on the data you get. Jon > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] pg_hba.conf problem
On Nov 30, 2003, at 21:01, Tom Lane wrote: The only other idea I can think of is he's editing the wrong pg_hba.conf file; we've seen a couple of people make that mistake. (David, the right pg_hba.conf file is the one in the $PGDATA directory. If you don't see a postmaster.pid file in the same directory that appears and disappears when you start and stop the postmaster, then you're in the wrong directory ...) Ta-Da! That was it. For some reason, I had an old pgsql/data tree laying around that I was futzing with. Looking for postmaster.pid did the trick. What I can't figure out is how I was breaking "local" access by editing the non-local file. Maybe I was just too tired after 18 hours of debugging Mozilla stuff. Thanks for the help. -David ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] language war
> Well, I have used Perl for a couple of thousand-lines-projects and while > I can't say it's wonderful, it's usable as far as one is strict (i.e. > code for strict and warnings), and use implicit things as less as > possible. If you abstract things the right way it's not the nightmare Actually, Perl is wonderful for several classes of programs. I wrote a web-based application in Perl that probably would have been many times as long in any other language. On top of that, I got a full SOAP API with just a 100-line wrapper script. Perl has really powerful OO features, and makes it really easy to do things that you only wish you could do in other languages. It is especially useful when you need to come up with special-purpose templating languages. It's RegExp language is also excellent. While you can get PCRE in other languages, Perl having it built-in to the language makes them much easier to use. Jon > you think it is. Agreed, the language grants you the power to do it > "the wrong way" (write-only language), but I distrust anything that > takes that power away (may I say Java?). > > PHP on the other hand, while I don't quite like it, is not also the > nightmare you make of it. You are of course not forced to embed HTML in > the logic -- in these days, doing so is folly. Most [sane] people uses > templates. Same with Perl. > > Python may be better, but keep in mind that it _also_ grants you the > power to do thing the wrong way. In fact, a colleague of mine is right > now ranting about a specific project, saying that he will have to write > it almost completely from scratch to be able to extract certains parts > of the functionality -- the author apparently did not abstract them > correctly. I have yet to use Python much, but the little I have seen I > like. > > -- > Alvaro Herrera () > Al principio era UNIX, y UNIX habló y dijo: "Hello world\n". > No dijo "Hello New Jersey\n", ni "Hello USA\n". > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Misplaced modifier in Postgresql license
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > "Permission to use, copy, modify, and distribute this software and its > documentation for any purpose, WITHOUT FEE, and without a written > agreement is hereby granted, provided that the above copyright notice > and this paragraph and the following two paragraphs appear in all > copies." > The result of this ambiguity is that the latest CD release of OpenBSD > (3.4) no longer includes Postgresql. It certainly would have been nice if they would have consulted with us before taking such an action, or at least let us known about it themselves. Nonetheless, thank you for taking the time to mail this list. Licensing can be a touchy area, but I'll try not to shoot the messenger. > I have used CAPS to highlight the apparent error. There are two ways > to interpret this statement. One interpretation is that permission is > given and no fee will be charged for the granting of that permission. > The other is that permission is given so long as by using the software > no fee is charged to others. There are a number of issues related to this issue. Six of them, actually: 1. Is the license unclear due to that clause? 2. Should the 'misplaced' clause be of concern? 3. Should changes be made to the licensing text? 4. Can changes be made to the licensing text? 5. If so, how do we do it? 6. Who has copyright to the PostgreSQL source code? Short answers as I see them: Yes,No,No,Yes,Easily,Individual contributors. 1. Is the license unclear due to that clause? Yes, it could be construed both ways. I would not go so far as to label it as "unclear" or "ambiguous", as the actual meaning has been obvious to people who have been reading it for many, many years. However, it is technically correct that it could be read as "cannot use if you charge for it." 2. Should the 'misplaced' clause be of concern? Although it could be construed as "you cannot charge", there are compelling reasons why this is not the case. A quick recap: Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. First and foremost, the fact that there is a "provided" clause strongly suggests that the conditions, exceptions, and qualifiers are stated /after/ this clause. This is normally how things work: you lay everything out, and then add in the restrictions. Second, if we were to assume that the "without fee" applies to the "purpose", then we must also assume that the license is stating that it cannot be distributed *without* a written agreement. This seems very absurd and further weakens the "without fee" argument. Third, the fact that their exists a revised version of the license from the original authors (University of California) which clears up the misplaced modifier is an indication that the "common" interpretation of what the original writers were trying to say is the correct interpretation. One thing that that seems to be overlooked in all of this, however, is that this license has a long history of use, and this is a very, very important thing in the law. Many companies have sold software (for a fee) that included PostgreSQL, and nobody has raised this issue until now. Everyone associated with the project has always understood (and stated) that PostgreSQL can be sold for a fee. The long history of this 'contract', and the fact that nobody has ever objected to the terms of it, makes the license bulletproof in court. You cannot have a license that is interpreted one way for many years (and among many parties) and then come along and try to throw in a unique interpretation of it. 3. Should changes be made to the licensing text? Although there are different interpretations, I do not feel it that the uncommon one is strong enough to warrant any concern, either by the PostgreSQL community or by those who include PostgreSQL in products they sell for a fee (e.g. the OpenBSD CD). There would be no harm in moving to a clearer license, but neither would there be harm in keeping the current one. 4. Can changes be made to the licensing text? That's a tough one (see #6) but the quick answer is "yes". If we were to make a change that was entirely compatible with the existing license, I do not foresee anybody having a problem with it. Probably the only people who would have a standing to object would the original authors, and if we were to change it to one of their newer wordings of the same license, there should be no problems. 5. If so, how do we do it? Cut and paste. :) Probably with a post to the general list asking if anyone (especially past contributors) would have any objections, and then committing the change to the COPYRIGHT file. 6. Who has copyright to the PostgreSQL source code? An interesting question. Certainly the Regents of the Univ
