Re: [GENERAL] vista
Well, we are entirely ready to accept patches from any Windows Vista beta testers who are able to find and fix portability issues. It's the folks who think that non-Windows-using developers should care about Vista that bug me. This is open-source code, people. Scratch your own itch. One issue I've found in installation is that you are unable to create the user postgresql (and yes, I have run it in Administrator mode) What error, exactly, did you get? Can you please open a bug for it on http://pgfoundry.org/projects/pginstaller, as this is clearly an installer issue and not a backend issue. Did it work well once you had created the user manually? //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] vista
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: 19 September 2006 05:43 To: Ron Johnson Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] vista Ron Johnson [EMAIL PROTECTED] writes: On 09/18/06 22:32, Joshua D. Drake wrote: John Meyer wrote: has anybody gotten postgresql to install successfully under Windows Vista? It's not out yet ;) Release Candidates are, for the very purpose of allowing ISVs to have their s/w ready on time. But then, people who use Windows are just as scurvy as those scurvy dogs who use PHP and MySQL. A. Well, we are entirely ready to accept patches from any Windows Vista beta testers who are able to find and fix portability issues. If it would run under VMWare, I would, however the last CTP wouldn't when I tested it (at the PG code sprint in fact). There is an update to VMWare that I don't have yet though, so if I get time I'll try that somewhen, but frankly it's low priority. Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] duplicate key violates unique constraint
Ron Johnson wrote:
# select * from projects;
project_id | username | project_name
- +--+--
1 | foo |
2 | bar |
(2 rows)
dupe_filenames=# insert into projects (project_name, username )
dupe_filenames-# values ('foo', 'bar');
ERROR: duplicate key violates unique constraint projects_pkey
And you didn't insert records with those numbers by hand (not using the
sequence)? It seems your sequence is a bit behind, which only happens if
you don't always use it to generate your ids.
I suggest you check your sequence values and update it to the highest
value in use if it's too low. You should only need to do that once.
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Oracle migration : size on disk of data file greater in PG
Hi, At a customer site, we've made a migration from Oracle 8.1.5 to PGSQL 8.1.1. The migration happened without any problem and now the performances are better with PG than with Ora, but the customer noticed that the size of PG on disk where much greater than the size on disk of Oracle. And I'm not able to find an easy explanation. Is it normal, due to inner data storage mecanisms differents between Oracle and PG ? Of course, we've run VACUUM on both DB before measuring the size on disk. The database is used to store statistical data by month and therefore contain dozen of tables of the same layout containing most of the time hundred of thousands records. Thank you, Benoit Gerrienne ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle migration : size on disk of data file greater in PG
Hi Benoit, Are you talking specifically the database? You can run up 100's of gigs of log files in pg_log if you're not careful (he says after doing the very same). Cheers Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 19 September 2006 10:01 To: pgsql-general@postgresql.org Subject: [GENERAL] Oracle migration : size on disk of data file greater in PG Hi, At a customer site, we've made a migration from Oracle 8.1.5 to PGSQL 8.1.1. The migration happened without any problem and now the performances are better with PG than with Ora, but the customer noticed that the size of PG on disk where much greater than the size on disk of Oracle. And I'm not able to find an easy explanation. Is it normal, due to inner data storage mecanisms differents between Oracle and PG ? Of course, we've run VACUUM on both DB before measuring the size on disk. The database is used to store statistical data by month and therefore contain dozen of tables of the same layout containing most of the time hundred of thousands records. Thank you, Benoit Gerrienne ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq - The information contained in this email is confidential and is intended for the recipient only. If you have received it in error, please notify us immediately by reply email and then delete it from your system. Please do not copy it or use it for any purposes, or disclose its contents to any other person or store or copy this information in any medium. The views contained in this email are those of the author and not necessarily those of Lorien plc. Thank you for your co-operation. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Oracle migration : size on disk of data file greater in PG
[EMAIL PROTECTED] writes: At a customer site, we've made a migration from Oracle 8.1.5 to PGSQL 8.1.1. The migration happened without any problem and now the performances are better with PG than with Ora, but the customer noticed that the size of PG on disk where much greater than the size on disk of Oracle. And I'm not able to find an easy explanation. Is it normal, due to inner data storage mecanisms differents between Oracle and PG ? To a certain degree yes. It's one of the topics under active discussion for improvement in the future. In particular you'll see a big difference if you have a lot of very small columns. You may also see some difference if you have very narrow rows because of the transaction status overhead. Of course, we've run VACUUM on both DB before measuring the size on disk. There's a couple problems here though. Firstly VACUUM doesn't usually shrink the actual size of data on disk, it just notes where the free space is so it can be reused. To shrink the actual data on disk you would need VACUUM FULL, CLUSTER, or ALTER TABLE ... ALTER COLUMN ... TYPE USING. However under normal operation Postgres expects to have some amount of free space anyways. Running VACUUM FULL is usually pointless and actually hurts performance unless you have an unusual situation such as having done large batch updates recently or not having run VACUUM regularly enough in the past. Because Postgres keeps free space around in the tables for new versions of tuples you should include Oracle's rollback segments in the comparison since that effectively corresponds to the free space Postgres keeps. Or you could do a VACUUM FULL before comparing but I do not recommend VACUUM FULL for regular operation. The database is used to store statistical data by month and therefore contain dozen of tables of the same layout containing most of the time hundred of thousands records. You may find it makes more sense to store this all in one table or in tables that are children of the same table such as described in: http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html -- greg ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] vista
It's the folks who think that non-Windows-using developers should care about Vista that bug me. This is open-source code, people. Scratch your own itch. The scratch your own itch line can only be pushed so far, if it is being said by a developer who works on a project that desires to be taken seriously by professionals in industry. For minor features, yes, perhaps it could be argued that the core team could ignore certain issues, and just wait for a patch. For something like Vista compatibility, if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user), scratch your own itch is not really going to cut it, IMHO. I'm used to getting that line when talking to 2 developer obscure projects that have a userbase of a half a dozen, but for a project like PostgreSQL, the they tell you to do it yourself brush is one we do NOT want to get tarred with. If we don't have the resources to cope with a Vista port immediately then so be it. If it's low priority, so be it. However, lets not appear to deride as unnecessary that which we cannot immediately provide a solution to. That's small time project mentality. - Naz. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vista
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Naz Gassiep Sent: 19 September 2006 12:26 To: Tom Lane Cc: Ron Johnson; pgsql-general@postgresql.org Subject: Re: [GENERAL] vista The scratch your own itch line can only be pushed so far, if it is being said by a developer who works on a project that desires to be taken seriously by professionals in industry. For minor features, yes, perhaps it could be argued that the core team could ignore certain issues, and just wait for a patch. For something like Vista compatibility, if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user), scratch your own itch is not really going to cut it, IMHO. I'm used to getting that line when talking to 2 developer obscure projects that have a userbase of a half a dozen, but for a project like PostgreSQL, the they tell you to do it yourself brush is one we do NOT want to get tarred with. So what do you suggest? -core vote and order someone to do the work? Postgresql.org isn't a business and doesn't employ any developer - we only have the option of accepting patches from people/companies with itches. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] vista
So what do you suggest? -core vote and order someone to do the work? Postgresql.org isn't a business and doesn't employ any developer - we only have the option of accepting patches from people/companies with itches. I don't suggest any chance to any structures in place, it's a purely PR point. That's important and we acknowledge the need. Even in the absence of any progress on that item, a statement like this sounds better to PHBs than If you need it, submit a patch. Regards, - Naz. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vista
# [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vista
-Original Message- From: Naz Gassiep [mailto:[EMAIL PROTECTED] Sent: 19 September 2006 14:06 To: Dave Page Cc: Tom Lane; Ron Johnson; pgsql-general@postgresql.org Subject: Re: [GENERAL] vista That's important and we acknowledge the need. Even in the absence of any progress on that item, a statement like this sounds better to PHBs than If you need it, submit a patch. In which case there's a good chance no-one will do the work. An awful lot of the features in and surrounding PostgreSQL only got developed because someone couldn't find the feature they wanted and were encouraged to work on it themselves. pgAdmin got started in almost exactly that way for example. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vista
On 9/19/06, Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. His point makes sense, Roman. While pg is not a company or a company product, it certainly needs to play nice with business in order for it to *have* (a significent number of) users. As much as I dislike it (probably the euphemism of the year), Windows is a fact on most development machines and on a substantial number of servers. In the case of Vista, even if none of the core commiters plan to support it, it makes a lot more sense to store the RFE and say it'll have to wait, than to say you got a problem? go fix it. Both attitudes reflect the same reality, but the fact that one is positive and one negative is obvious. Cheers, t.n.a. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Upgrading schemas
Hi, I have an application using Postgresql with a large (100+) number of tables. Are there any free/open source tools that can ease the process of updating the schema. Specifically I would like to compare a source and a target, determine the differences and the best way to update the target to be equivalent to the source - tables, columns, views, etc. thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Access to databas from the Internet
Hello, I would like to install a PostgreSQL. I know how to manage the database itself, creae databases, user, groups and so on. But I don't know how to allow other users, who are outside LAN to connect to database through Internet. For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the computer with yyy.yyy... What and where I need to configure to have access to my database from Internet? Regards, Lukasz ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] unique key issue
HI I have a table with a unique key constraint on col1,col2. The dattabase allowed an multiple inserts. here is how col1 col2 'abc' empty space 'abc' empty space I am not sure how this can happen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?
The solution you need all depends on the problem you are having. If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better answer! Regards, Ben Najib Abi Fadel [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Robin Ericsson [EMAIL PROTECTED] wrote: On 9/18/06, Najib Abi Fadel wrote: Hi, i was searching for a load balancing solution for postgres, I found some ready to use software like PGCluster, Slony, pgpool and others. It would really be nice if someone knows which one is the best taking in consideration that i have an already running application that i need to load balance. There isn't one tool that is the best, all three work very good based on where they are used and what they are used for. -- regards, Robin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Did you try them or have any experience with them. I need them for load balancing my database and thus making the queries faster. I have a web application heavely using a postgres database. Hundreds of users can connect at the same time to my web application. Thanks in advance for any help. Najib. How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Upgrading schemas
The free, open-source SchemaCrawler tool will compare the schema and data. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs) in the same plain-text formats. You can use a standard diff program to diff the current output with a reference version of the output. SchemaCrawler can be run either from the command line, or as an ant task. A lot of examples are available with the download to help you get started. SchemaCrawler is free, open-source, cross-platform (operating system and database) tool, written in Java, that is available at SourceForge: http://schemacrawler.sourceforge.net/ You will need to provide a JDBC driver for your database. No other third-party libraries are required. Sualeh Fatehi. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] vista
Dave Page wrote: because someone couldn't find the feature they wanted and were encouraged to work on it themselves. pgAdmin got started in almost ^^ That's the key word here, encouraged, not discouraged. IMHO telling a Windows user to go do it himself is discouraging. Quite impolite too. It is not much harder to say We currently don't have the resources to look into that, if you could be so kind to experiment a bit and see if you can get it to work It might even invite other readers of this ML to look into it instead. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] vista
Tomi NA wrote: On 9/19/06, Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. His point makes sense, Roman. While pg is not a company or a company product, it certainly needs to play nice with business in order for it to *have* (a significent number of) users. As much as I dislike it (probably the euphemism of the year), Windows is a fact on most development machines and on a substantial number of servers. In the case of Vista, even if none of the core commiters plan to support it, it makes a lot more sense to store the RFE and say it'll have to wait, than to say you got a problem? go fix it. Both attitudes reflect the same reality, but the fact that one is positive and one negative is obvious. The response is not because it's Windows. The response 'to provide a patch' is made to anyone who would like to see a particular functionality in the application that the core group is not working on or is not on their radar. This is being made into a 'Windows vs..' thing and that's just not the case. I've seen folks suggest someone provide a patch for non-windows environments more so then windows environments. Although Tom's response may have seemed to be negative towards windows, the original posting had a bit of an attitude to start with. 'if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user)' -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] unique key issue
Junkone wrote: HI I have a table with a unique key constraint on col1,col2. The dattabase allowed an multiple inserts. here is how col1 col2 'abc' empty space 'abc' empty space I suppose the values in col2 are NULL values? You cannot compare NULL values. NULL == NULL evaluates to NULL (not true or false), you have no way of knowing those two records are equal. PostgreSQL (among others) assumes NULL values to always be different. The meaning and interpretation of NULL is a frequent topic of discussion. I am not sure how this can happen You can solve your problem by creating 2 unique constraints: CREATE UNIQUE INDEX idx1 ON table (col1, col2) WHERE col2 IS NOT NULL; CREATE UNIQUE INDEX idx2 ON table (col1) WHERE col2 IS NULL; Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Limits of arrays
I found a rather strange but working method to compare the contents of two tables in different databases: select 'otformularfeld' as which, md5(array_to_string(array( select md5(id_pkff||id_formular||id_formfeld||id_bf) from otformularfeld where quarant=0 order by id_pkff ),'')) So: - basically I take the relevant columns from a table - cast them to text and concattenate the strings - find the md5 hash of this row - then take the md5 of all rows, concattenate them to string - and finally find the md5 of this string That works surprisingly fast and gives a quick check data in those 2 tables is the same or not. Now, the maximum rowcount in one table so far is 18; and still there is no crash. Are there limits for the maximum rows in one arrays? Limits the maximum length of one string? The only near information I could find in the documentations was 1 GB per field, which will propably be the size limit for the array and the string. Are there more limits? Or is that md5 / concattenation process done in an iterative manner, that is: all the intermediate results are consumed and not cached in memory? Harald -- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all. -- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] vista
In response to Tomi NA [EMAIL PROTECTED]: On 9/19/06, Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. His point makes sense, Roman. While pg is not a company or a company product, it certainly needs to play nice with business in order for it to *have* (a significent number of) users. As much as I dislike it (probably the euphemism of the year), Windows is a fact on most development machines and on a substantial number of servers. In the case of Vista, even if none of the core commiters plan to support it, it makes a lot more sense to store the RFE and say it'll have to wait, than to say you got a problem? go fix it. Both attitudes reflect the same reality, but the fact that one is positive and one negative is obvious. To take a step back ... I think PostgreSQL is suffering from popularity. I remember when I first tried to get it up and running in the last 90s, and failed. There were some post-installation steps that I couldn't figure out at that time, so I jumped on the MySQL bandwagon for a few years. Nowadays, getting PostgreSQL running on FreeBSD is as easy as make install. Now that you have a Windows installer, it's even easier. This means there's an influx of a new type of people. Back in the late 90s, the only people using PostgreSQL were those with enough smarts and patience to figure out how to get it running. But this new influx isn't just less knowledgeable people -- by making PostgreSQL available on Windows, we've crossed a cultural barrier. People in the Windows world think very differently than those from the OSS world (in general). Some specific cultural differences I see: Business: How much to get feature X implemented? OSS: How much are you willing to donate, and I'll do what I can. Business: Please give me a timeline for the when X will be done. OSS: It'll be done when we know it's right. Business: Who can I hire to write feature X? OSS: It's not interesting, if you want it, go ahead and do it. Business: If I pay someone to write X, will you include it in the main tree? OSS: We'll include any code in the tree, if it's _good_. There are some subtle differences in the way things are approached there, but they can be showstoppers when it comes to OSS and business working together. And the simple fact is that Windows is business, not software. If you can solve the communication problems, everything else will just happen. Just my opinions from observing this and other similar conversations. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] unique key issue
On Mon, Sep 18, 2006 at 03:54:05PM -0700, Junkone wrote: HI I have a table with a unique key constraint on col1,col2. The dattabase allowed an multiple inserts. here is how col1 col2 'abc' empty space 'abc' empty space Depends on what you mean by empty space. If you mean NULL, then it's according to the SQL standard. NULL NULL so those rows are not equal. If you mean some real value, then yes, that's wierd. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Access to databas from the Internet
Hello Lukasz! You need some port forwarding onto your router/firewall. You have to forward incoming connections on port 5432 (or the port postmaster is listening on) from IP xxx.xxx.xxx.xxx to IP yyy.yyy.yyy.yyy with the according port. If your router is a Linux machine, take a look into the iptables rules and also the HowTos at http://www.netfilter.org/ how to create a port forwarding. Be aware that this will also attackers from the internet will enable them to use exploits onto your server! So make sure, that this machine is properly secured. Best regards, Matthias -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lukasz Sent: Tuesday, September 19, 2006 3:11 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Access to databas from the Internet Hello, I would like to install a PostgreSQL. I know how to manage the database itself, creae databases, user, groups and so on. But I don't know how to allow other users, who are outside LAN to connect to database through Internet. For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the computer with yyy.yyy... What and where I need to configure to have access to my database from Internet? Regards, Lukasz ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] vista
I think the itch and scratch line is entirely appropriate. This is open source software, not prepackaged code guaranteed to work on the newest platforms. People who are trying it out on Vista are developers, not non-technical end-users. A developer who wants an open source product to work on a new platform should at least see what the problems are and then ask for help in fixing it, if he can't fix it himself. One of the big advantages of open source software is that business can see that if someone wants it to work on Vista, they can pay a programmer to get it to work on Vista and then submit the patch so that the rest of the community benefits as well. I would guess (being that it works fine in Windows XP), though I haven't even seen Vista yet, that the problem is relatively minor and going through the code with a debugger would probably allow the app to be installed within a couple hours. There may be a polite way of saying it, but you use that polite voice when talking to an end-user. When you're talking to a developer, I think you should say it like it is. Tomi NA wrote: On 9/19/06, Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. His point makes sense, Roman. While pg is not a company or a company product, it certainly needs to play nice with business in order for it to *have* (a significent number of) users. As much as I dislike it (probably the euphemism of the year), Windows is a fact on most development machines and on a substantial number of servers. In the case of Vista, even if none of the core commiters plan to support it, it makes a lot more sense to store the RFE and say it'll have to wait, than to say you got a problem? go fix it. Both attitudes reflect the same reality, but the fact that one is positive and one negative is obvious. Cheers, t.n.a. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Access to databas from the Internet
You have to either configure your router to redirect the public port to the private port. (By default 5432). Or put your database server in the DMZ (not recommended). Lukasz wrote: Hello, I would like to install a PostgreSQL. I know how to manage the database itself, creae databases, user, groups and so on. But I don't know how to allow other users, who are outside LAN to connect to database through Internet. For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the computer with yyy.yyy... What and where I need to configure to have access to my database from Internet? Regards, Lukasz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vista
On Tue, Sep 19, 2006 at 03:34:47PM +0200, Alban Hertroys wrote: It is not much harder to say We currently don't have the resources to look into that, if you could be so kind to experiment a bit and see if you can get it to work Except that would be a lie. Perhaps: Postgresql developers do not have the ability to force anyone to do this. The best idea is if you could be so kind to experiment a bit and see if you can get it to work We have no resources to direct, or to look into things. Only individual developers (or their employers) can direct their own resources. If Vista is so important, why aren't seeing a rash of installation reports about it working (or not). Why hasn't someone offered to setup a buildfarm machine? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] vista
John Meyer wrote: has anybody gotten postgresql to install successfully under Windows Vista? ---(end of broadcast)--- TIP 6: explain analyze is your friend BTW, here's the issue at the pgFoundary http://pgfoundry.org/tracker/index.php?func=detailaid=1000733group_id=107atid=126 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] vista
Although Tom's response may have seemed to be negative towards windows, the original posting had a bit of an attitude to start with. Actually the original poster asked has anybody gotten postgresql to install successfully under Windows Vista? To which the answer should have been, no. Send us the patch when you get it working. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Access to databas from the Internet
On 19/9/2006 22:41, Lukasz [EMAIL PROTECTED] wrote: Hello, I would like to install a PostgreSQL. I know how to manage the database itself, creae databases, user, groups and so on. But I don't know how to allow other users, who are outside LAN to connect to database through Internet. For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the computer with yyy.yyy... What and where I need to configure to have access to my database from Internet? I will assume that you want to allow normal psql client access and not through a web server. There is two places you will need to configure. One is your router - you will need to setup port forwarding . The default port for connecting to the PostgreSQL server is 5432 so the router will need to forward any incoming requests on tcp port 5432 to tcp port 5432 at server address yyy.yyy.yyy.yyy (your PostgreSQL server address) If you have configured a different port then adjust accordingly. This is a common configuration option and shouldn't be hard to find. Second you will need to configure PostgreSQL to accept connections from outside your network. This is done in pg_hba.conf which is in your data folder by default. If you currently connect to the server from another machine on your network you will have a line similar to hostall all yyy.yyy.yyy.yyy/24 md5 To allow PostgreSQL to accept outside connections you will add another line such as hostall all zzz.zzz.zzz.zzz/32 md5 Where zzz.zzz.zzz.zzz is the ip address of the remote computer that wants to connect. The pg_hba.conf file has notes explaining these entries or you can read up the docs at http://www.postgresql.org/docs/8.1/static/client-authentication.html Basically this entry says you are willing to accept network connections from another computer and how they are allowed to connect and which databases they can connect to. If you allow connections from anywhere then anyone has the chance of getting into your database. It is preferable to only allow connections from a specific ip address but if they don't have a static ip address you won't be able to do that. If they have a dynamic ip address one suggestion is try and limit them to connections from the isp they are connecting from instead of just any computer in the world. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vista
-Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: 19 September 2006 15:10 To: Alban Hertroys Cc: Dave Page; Naz Gassiep; Tom Lane; Ron Johnson; pgsql-general@postgresql.org Subject: Re: [GENERAL] vista If Vista is so important, why aren't seeing a rash of installation reports about it working (or not). Why hasn't someone offered to setup a buildfarm machine? Actually I did, but the most recent CTP didn't run under VMWare as I mentioned earlier. Still, the show stopper on the first release which I *really* briefly tested it on was that the installer couldn't create a service user account as I think started this thread. This is most likely Vista's tightened security model - the easy answer to which will probably be to simply run as the installing user, Administrator or better yet, Network Service (or whatever it's called). Now that we dump all privileges on startup it's less of an issue if we cannot create our own account. Regards, Dave. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Access to databas from the Internet
am Tue, dem 19.09.2006, um 6:11:12 -0700 mailte Lukasz folgendes: Hello, I would like to install a PostgreSQL. I know how to manage the database itself, creae databases, user, groups and so on. But I don't know how to allow other users, who are outside LAN to connect to database through Internet. For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the Can you connect from outside to you external IP with SSH? If yes, then you can use a so called 'SSH-Tunnel'. See: http://pgedit.com/, Chapter 'SSH Tunneling' HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] vista
Naz Gassiep [EMAIL PROTECTED] writes: For something like Vista compatibility, if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user), scratch your own itch is not really going to cut it, IMHO. I was responding to someone who is obviously a Windows beta tester and therefore presumably has more clue than the average Windows-oid --- asking for a patch didn't seem unreasonable. But for arguments like the above, I will happily say apparently you've confused me with someone who gives a damn about Windows. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL installed in non-standard Directory
Hi Martijn, Am 2006-09-17 12:22:42, schrieb Martijn van Oosterhout: Do you really want this? Compiling static is not straightforward, if all the libraries actually exist in static form anyway. Would it not be easier to install the system in a chroot, then you can use all the normal system libs and standard install packages... I will try it. But sometimes I have gotten strange results using chroots on foreign systems (specialy if I must install 100 or more packages) Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] vista
taken seriously by professionals in industry. For minor features, yes, perhaps it could be argued that the core team could ignore certain issues, and just wait for a patch. For something like Vista compatibility, if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user), scratch your own itch is not really going to cut it, Then they can purchase MSSQL. No sweat off my back. IMHO. I'm used to getting that line when talking to 2 developer obscure projects that have a userbase of a half a dozen, but for a project like PostgreSQL, the they tell you to do it yourself brush is one we do NOT want to get tarred with. If we don't have the resources to cope with a Vista port immediately then so be it. If it's low priority, so be it. However, lets not appear to deride as unnecessary that which we cannot immediately provide a solution to. That's small time project mentality. Asking someone to submit a patch is completely acceptable in this project. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vista
Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? Well I can tell you that lots of people run PostgreSQL on Windows. :). 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. Tom RedHat Lane... H ;) Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] vista
Perhaps many of the core developers don't care if windows users take the project seriously? On Sep 19, 2006, at 4:26 AM, Naz Gassiep wrote: It's the folks who think that non-Windows-using developers should care about Vista that bug me. This is open-source code, people. Scratch your own itch. The scratch your own itch line can only be pushed so far, if it is being said by a developer who works on a project that desires to be taken seriously by professionals in industry. For minor features, yes, perhaps it could be argued that the core team could ignore certain issues, and just wait for a patch. For something like Vista compatibility, if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user), scratch your own itch is not really going to cut it, IMHO. I'm used to getting that line when talking to 2 developer obscure projects that have a userbase of a half a dozen, but for a project like PostgreSQL, the they tell you to do it yourself brush is one we do NOT want to get tarred with. If we don't have the resources to cope with a Vista port immediately then so be it. If it's low priority, so be it. However, lets not appear to deride as unnecessary that which we cannot immediately provide a solution to. That's small time project mentality. - Naz. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] vista
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ben Sent: 19 September 2006 17:03 To: Naz Gassiep Cc: Tom Lane; Ron Johnson; pgsql-general@postgresql.org Subject: Re: [GENERAL] vista Perhaps many of the core developers don't care if windows users take the project seriously? Somehow I doubt that given that I was invited to join core precisely because of my work on the Windows distro. I would think that implies that at least 50% of the other members think the port is important. Regards, Dave ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] vista
On Tue, Sep 19, 2006 at 05:11:54PM +0100, Dave Page wrote: Perhaps many of the core developers don't care if windows users take the project seriously? Somehow I doubt that given that I was invited to join core precisely because of my work on the Windows distro. I would think that implies that at least 50% of the other members think the port is important. Indeed. The people in core are not going to stand in the way of a good patch that will fix a problem for windows. But neither are they going to spend their own time debugging a platform they have no experience with. At the end of the day any problems with Vista are going to have to be fixed by people with the OS, compiler, debugging expertise and time to do it. The fact is that most people here don't have that. The same criteria applies to every other platform. So basically, anyone out there who meets the above criteria? Now is the time to show it. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] vista
Ben wrote: Perhaps many of the core developers don't care if windows users take the project seriously? Well that may or may not be true, but isn't really relevant. What is relevant is that *any* community user would have been told the *exact* same thing. Regardless of OS. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Odd behavior observed
Hello -
I am observing odd behavior that I am wondering if anyone here may have an
idea of how better to debug. I am suspecting a bug in the pgsql code, but
would be happy to find it is my error.
My specific situation is that I am using version 8.1.4 on a FreeBSD 6.1
AMD-64 system. I have a table with about 15000 records in it, which I
would like to add a new column to. The alter command shows success.
However, testing inserts reveals that the data for the new column is never
stored. Running the same exercise against the identical schema but with
only a few records finds that the test succeeds, and hence the problem is
not easily recreated. Experimentation has shown that the type of the
column doesn't matter. If the column has NOT NULL DEFAULT {value} added,
then it magically works.
A trivial example of the exercise is shown here:
create table foo (id bigserial);
insert into foo (id) values (8);
alter table foo add source_record bigint;
insert into foo (id,source_record) values (10,20);
select * from foo;
id | source_record
+---
8 |
10 |20
If I populate the table foo above with 15000 records, the exercise still
works OK. The only difference that I and others staring at this problem
see is that the real-world table contains a more complex definition,
included here for reference:
Column |Type |
Modifiers
+-+-
id | bigint | not null default
nextval('audit_logs_id_seq'::regclass)
timestamp | timestamp without time zone | not null default now()
notify_at | timestamp without time zone |
audit_log_type_id | bigint | not null
sdp_id | bigint |
customer_id| bigint |
customer_region_id | integer |
audit_format_id| bigint | not null
msg_args | text[] |
arg_names | text[] |
source_record | bigint |
Indexes:
audit_logs_pkey PRIMARY KEY, btree (id)
Foreign-key constraints:
audit_logs_audit_format_id_fkey FOREIGN KEY (audit_format_id) REFERENCES
audit_formats(id) ON DELETE RESTRICT
audit_logs_audit_log_type_id_fkey FOREIGN KEY (audit_log_type_id)
REFERENCES audit_log_types(id) ON DELETE RESTRICT
audit_logs_audit_log_type_id_fkey1 FOREIGN KEY (audit_log_type_id)
REFERENCES audit_log_types(id) ON DELETE RESTRICT
audit_logs_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES
customers(id) ON DELETE RESTRICT
audit_logs_customer_region_id_fkey FOREIGN KEY (customer_region_id)
REFERENCES customer_regions(id) ON DELETE RESTRICT
audit_logs_sdp_id_fkey FOREIGN KEY (sdp_id) REFERENCES sdps(id) ON DELETE
RESTRICT
Triggers:
audit_log_delete_trigger BEFORE DELETE ON audit_logs FOR EACH ROW EXECUTE
PROCEDURE audit_log_delete_restrict()
Does anybody have a suggestion about how to debug this?
Thanks in advance - Marc
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vista
[EMAIL PROTECTED] (Naz Gassiep) writes: It's the folks who think that non-Windows-using developers should care about Vista that bug me. This is open-source code, people. Scratch your own itch. The scratch your own itch line can only be pushed so far, if it is being said by a developer who works on a project that desires to be taken seriously by professionals in industry. For minor features, yes, perhaps it could be argued that the core team could ignore certain issues, and just wait for a patch. For something like Vista compatibility, if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user), scratch your own itch is not really going to cut it, IMHO. I'm used to getting that line when talking to 2 developer obscure projects that have a userbase of a half a dozen, but for a project like PostgreSQL, the they tell you to do it yourself brush is one we do NOT want to get tarred with. If we don't have the resources to cope with a Vista port immediately then so be it. If it's low priority, so be it. However, lets not appear to deride as unnecessary that which we cannot immediately provide a solution to. That's small time project mentality. Well, the same issue has come up with the subproject that I work on, namely Slony-I, and the nature of things seems much the same. *I* don't use Windows, haven't got any relevant build environment, and, organizationally, really couldn't care less if PostgreSQL or Slony-I runs on Windows or not, as Windows isn't a relevant platform. Asking me about Windows support in *any* context is pretty much useless; as far as I'm concerned, Windows support requires finding someone who has that particular itch. It turns out that there are people with a Windows itch, and I haven't turned away patches to provide Windows support due to its irrelevance to me. No, I'm pleased enough to see that come in. But if you present Windows-related issues to me, I see nothing improper in saying scratch your own itch. I'm *not* the right one to help, and the community is large enough that I don't see any problem with that. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://linuxfinances.info/info/advocacy.html Rules of the Evil Overlord #196. I will hire an expert marksman to stand by the entrance to my fortress. His job will be to shoot anyone who rides up to challenge me. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Odd behavior observed
Marc Evans [EMAIL PROTECTED] writes: ... I have a table with about 15000 records in it, which I would like to add a new column to. The alter command shows success. However, testing inserts reveals that the data for the new column is never stored. What do you mean by that exactly? The example you showed seems to be doing just what it's supposed to. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Odd behavior observed
On Tue, 19 Sep 2006, Tom Lane wrote: Marc Evans [EMAIL PROTECTED] writes: ... I have a table with about 15000 records in it, which I would like to add a new column to. The alter command shows success. However, testing inserts reveals that the data for the new column is never stored. What do you mean by that exactly? The example you showed seems to be doing just what it's supposed to. In the failure mode, the source_record column always has the value NULL, regardless of any value specified in an insert. If I use UPDATE to change a record, the value is properly stored. - Marc ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Odd behavior observed
Marc Evans [EMAIL PROTECTED] writes: On Tue, 19 Sep 2006, Tom Lane wrote: What do you mean by that exactly? The example you showed seems to be doing just what it's supposed to. In the failure mode, the source_record column always has the value NULL, regardless of any value specified in an insert. What insert command is being issued exactly, and from what source? I'm speculating about issues like stale plans or metadata caches, but you're not providing any information about where to look. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Odd behavior observed
On Tue, 19 Sep 2006, Tom Lane wrote: Marc Evans [EMAIL PROTECTED] writes: On Tue, 19 Sep 2006, Tom Lane wrote: What do you mean by that exactly? The example you showed seems to be doing just what it's supposed to. In the failure mode, the source_record column always has the value NULL, regardless of any value specified in an insert. What insert command is being issued exactly, and from what source? I'm speculating about issues like stale plans or metadata caches, but you're not providing any information about where to look. Sorry for being terse Tom. Here is the exact commands and responses: insert into audit_logs (audit_log_type_id,source_record,audit_format_id) values (3,20,71); INSERT 0 1 select * from audit_logs order by timestamp desc; id | timestamp | notify_at | audit_log_type_id | sdp_id | customer_id | customer_region_id | audit_format_id |msg_args | arg_names | source_record ---+++---++-++-+-+---+--- 15646 | 2006-09-19 17:18:10.074006 | 2006-09-19 17:18:10.190341 | 3 || | | 71 | | | update audit_logs set source_record = 10101 where id = 15646; UPDATE 1 select * from audit_logs where id=15646; id | timestamp | notify_at | audit_log_type_id | sdp_id | customer_id | customer_region_id | audit_format_id | msg_args | arg_names | source_record ---+++---++-++-+--+---+--- 15646 | 2006-09-19 17:18:10.074006 | 2006-09-19 17:18:10.190341 | 3 || || 71 | | | 10101 Does that help any? - Marc ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Odd behavior observed
Marc Evans [EMAIL PROTECTED] writes: On Tue, 19 Sep 2006, Tom Lane wrote: What insert command is being issued exactly, and from what source? I'm speculating about issues like stale plans or metadata caches, but you're not providing any information about where to look. Sorry for being terse Tom. Here is the exact commands and responses: Hmph. You got any ON INSERT triggers or rules on that table? I can't think of anything else that would interfere with data getting stored. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Initializing Datums for use with SPI_execute_plan
On 9/18/06, Jack Orenstein [EMAIL PROTECTED] wrote: -- Forwarded message -- From: Andrew - Supernews [EMAIL PROTECTED] ... Jack I have an int8 that I need as a Datum for use with Jack SPI_execute_plan. Int64GetDatum(your_variable) which then has to be pfree'd, correct? Jack ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Odd behavior observed
On Tue, 19 Sep 2006, Tom Lane wrote: Marc Evans [EMAIL PROTECTED] writes: On Tue, 19 Sep 2006, Tom Lane wrote: What insert command is being issued exactly, and from what source? I'm speculating about issues like stale plans or metadata caches, but you're not providing any information about where to look. Sorry for being terse Tom. Here is the exact commands and responses: Hmph. You got any ON INSERT triggers or rules on that table? I can't think of anything else that would interfere with data getting stored. No INSERT triggers. I do have a BEFORE DELETE trigger, and a pile of FOREIGN KEY items (which work kinda like an INSERT trigger). - Marc ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] vista
So... If you're not a PostgreSQL Win32 port dev, and *don't know* what they're up to as far as Vista, why respond to the Q? Or why respond fix it yourself instead of ask this guy or nobody here will know yet or post your query on -ports or -hackers. Otherwise it's as useful as saying http://justgoogleit.com/; or check the man pages. While technically a correct response, it's not a very useful one and certainly not what the poster was asking, yes? It's like a SELECT * ... statement returning a single row with an asterisk in it. Gee, thanks for the tautology. Heck, even check CVS change logs would be more useful. Presumably *a* person on the dev team will handle it eventually. And it's not unreasonable to expect that somebody, somewhere has asked the same question to the dev team and that they *might* know something about the state of PG on that platform. Frankly, I too could care less about PG on Vista. Longhorn isn't due until Vista SP1, so PG support has a long time to go before it's a real concern. But then I didn't try to answer the question. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Browne Sent: Tuesday, September 19, 2006 12:16 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] vista [EMAIL PROTECTED] (Naz Gassiep) writes: It's the folks who think that non-Windows-using developers should care about Vista that bug me. This is open-source code, people. Scratch your own itch. The scratch your own itch line can only be pushed so far, if it is being said by a developer who works on a project that desires to be taken seriously by professionals in industry. For minor features, yes, perhaps it could be argued that the core team could ignore certain issues, and just wait for a patch. For something like Vista compatibility, if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user), scratch your own itch is not really going to cut it, IMHO. I'm used to getting that line when talking to 2 developer obscure projects that have a userbase of a half a dozen, but for a project like PostgreSQL, the they tell you to do it yourself brush is one we do NOT want to get tarred with. If we don't have the resources to cope with a Vista port immediately then so be it. If it's low priority, so be it. However, lets not appear to deride as unnecessary that which we cannot immediately provide a solution to. That's small time project mentality. Well, the same issue has come up with the subproject that I work on, namely Slony-I, and the nature of things seems much the same. *I* don't use Windows, haven't got any relevant build environment, and, organizationally, really couldn't care less if PostgreSQL or Slony-I runs on Windows or not, as Windows isn't a relevant platform. Asking me about Windows support in *any* context is pretty much useless; as far as I'm concerned, Windows support requires finding someone who has that particular itch. It turns out that there are people with a Windows itch, and I haven't turned away patches to provide Windows support due to its irrelevance to me. No, I'm pleased enough to see that come in. But if you present Windows-related issues to me, I see nothing improper in saying scratch your own itch. I'm *not* the right one to help, and the community is large enough that I don't see any problem with that. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://linuxfinances.info/info/advocacy.html Rules of the Evil Overlord #196. I will hire an expert marksman to stand by the entrance to my fortress. His job will be to shoot anyone who rides up to challenge me. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Odd behavior observed
Marc Evans [EMAIL PROTECTED] writes: On Tue, 19 Sep 2006, Tom Lane wrote: Hmph. You got any ON INSERT triggers or rules on that table? I can't think of anything else that would interfere with data getting stored. No INSERT triggers. I do have a BEFORE DELETE trigger, and a pile of FOREIGN KEY items (which work kinda like an INSERT trigger). Hard to see how those could be related --- but it's even harder to credit that the INSERT would get past the parser with an explicit reference to the new column and then not store it. I think maybe something is applying an UPDATE to the row and losing the new value at that point. Are any of the FKs non-default actions (ON ... SET NULL or some such that would try to alter data instead of just erroring)? Also, can you check the cmin field of that row and see if it's greater than zero? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Odd behavior observed
On Tue, 19 Sep 2006, Tom Lane wrote: Marc Evans [EMAIL PROTECTED] writes: On Tue, 19 Sep 2006, Tom Lane wrote: Hmph. You got any ON INSERT triggers or rules on that table? I can't think of anything else that would interfere with data getting stored. No INSERT triggers. I do have a BEFORE DELETE trigger, and a pile of FOREIGN KEY items (which work kinda like an INSERT trigger). Hard to see how those could be related --- but it's even harder to credit that the INSERT would get past the parser with an explicit reference to the new column and then not store it. I think maybe something is applying an UPDATE to the row and losing the new value at that point. Are any of the FKs non-default actions (ON ... SET NULL or some such that would try to alter data instead of just erroring)? Also, can you check the cmin field of that row and see if it's greater than zero? It is zero for the inserted row. Other rows often have a value of 11. The complete FK and TRIGGER list is shown here: Foreign-key constraints: audit_logs_audit_format_id_fkey FOREIGN KEY (audit_format_id) REFERENCES audit_formats(id) ON DELETE RESTRICT audit_logs_audit_log_type_id_fkey FOREIGN KEY (audit_log_type_id) REFERENCES audit_log_types(id) ON DELETE RESTRICT audit_logs_audit_log_type_id_fkey1 FOREIGN KEY (audit_log_type_id) REFERENCES audit_log_types(id) ON DELETE RESTRICT audit_logs_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT audit_logs_customer_region_id_fkey FOREIGN KEY (customer_region_id) REFERENCES customer_regions(id) ON DELETE RESTRICT audit_logs_sdp_id_fkey FOREIGN KEY (sdp_id) REFERENCES sdps(id) ON DELETE RESTRICT Triggers: audit_log_delete_trigger BEFORE DELETE ON audit_logs FOR EACH ROW EXECUTE PROCEDURE audit_log_delete_restrict() Any suggested tricks for seeing additional debug information or even roaming through gdb, to try to figure this out? Thanks again - Marc ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Odd behavior observed
Marc Evans [EMAIL PROTECTED] writes: Any suggested tricks for seeing additional debug information or even roaming through gdb, to try to figure this out? Is the problem reproducible enough that you can watch it under gdb? In that case it shouldn't be that hard to narrow it down. I gathered from your original statement that you couldn't reproduce it reliably. I'd start with ExecInsert() and see if it's being passed correct info or not, then work backwards or forwards depending. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Odd behavior observed
On Tue, 19 Sep 2006, Tom Lane wrote: Marc Evans [EMAIL PROTECTED] writes: Any suggested tricks for seeing additional debug information or even roaming through gdb, to try to figure this out? Is the problem reproducible enough that you can watch it under gdb? In that case it shouldn't be that hard to narrow it down. I gathered from your original statement that you couldn't reproduce it reliably. I'd start with ExecInsert() and see if it's being passed correct info or not, then work backwards or forwards depending. Yes, 100% reproducable within the context of that I have only seen it when I have a pile of records. I have not come up with a simple-case reproduction yet (the foo table for example), though it appears that you may have. I will dive into gdb and see what I can find. Thanks! - Marc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Load a csv file into a pgsql table
Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? Thanks a lot! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Load a csv file into a pgsql table
Define 'quick'. You could write a script that would transform a .csv file into an INSERT statement and save it to an .sql file. Or I suppose you could do silly ODBC stuff with MS Access. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Tuesday, September 19, 2006 2:15 PM To: PgSQL General Subject: [GENERAL] Load a csv file into a pgsql table Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? Thanks a lot! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Load a csv file into a pgsql table
On Sep 19, 2006, at 11:15 AM, Emi Lu wrote: Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? There are several bulk loaders, but I believe they all use COPY behind the scenes. If copy isn't an option then your best bet will be many inserts in a transaction, but that'll be significantly slower. You could cobble together a loader using perl, DBI and one of the CPAN CSV modules fairly easily. (But I can't think of any reason why you wouldn't use copy, so you must have some constraint you haven't mentioned - can you expand on why copy isn't an option?) Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Load a csv file into a pgsql table
On Tue, 2006-09-19 at 13:27, Brandon Aiken wrote:
Define 'quick'.
You could write a script that would transform a .csv file into an INSERT
statement and save it to an .sql file.
Or I suppose you could do silly ODBC stuff with MS Access.
--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu
Sent: Tuesday, September 19, 2006 2:15 PM
To: PgSQL General
Subject: [GENERAL] Load a csv file into a pgsql table
Greetings,
*Except* copy command, are there other quick ways to load data from a
csv file into a pgsql table please?
Haven't seen the OP go by, but here's the one of the simplest csv
loaders ever created. No guarantees to suitability implied or
otherwise.
#!/usr/bin/php -q
?php
$tablename = $argv[1];
$filename = $argv[2];
if ($argc!=3){
echo Usage:\n\n loadpg tablename filename\n;
exit;
}
if (!file_exists($filename)){
die (given filename doesn't exist\n);
}
print copy $tablename from stdin;\n;
$fp = fopen($filename,r);
while(!feof($fp)){
$line = fgetcsv($fp,4096);
if (strlen($line)==0) continue(1);
print implode(\t,$line);
print \n;
}
print '\.';
print \n;
?
Note that you just redirect the output to psql and off you go.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Load a csv file into a pgsql table
Thank you for all the inputs.
Actually, I am reluctant to do the update line by line.
I plan to use a shell script to
. replace all characters such as ' to \'
. update each line to insert into
. call -c query load the file into db
In java, call this shell script, after data populated into tables, will
do other data comparison based on this table then.
You could write a script that would transform a .csv file into an INSERT
statement and save it to an .sql file.
Or I suppose you could do silly ODBC stuff with MS Access.
--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu
Sent: Tuesday, September 19, 2006 2:15 PM
To: PgSQL General
Subject: [GENERAL] Load a csv file into a pgsql table
Greetings,
*Except* copy command, are there other quick ways to load data from a
csv file into a pgsql table please?
Haven't seen the OP go by, but here's the one of the simplest csv
loaders ever created. No guarantees to suitability implied or
otherwise.
#!/usr/bin/php -q
?php
$tablename = $argv[1];
$filename = $argv[2];
if ($argc!=3){
echo Usage:\n\n loadpg tablename filename\n;
exit;
}
if (!file_exists($filename)){
die (given filename doesn't exist\n);
}
print copy $tablename from stdin;\n;
$fp = fopen($filename,r);
while(!feof($fp)){
$line = fgetcsv($fp,4096);
if (strlen($line)==0) continue(1);
print implode(\t,$line);
print \n;
}
print '\.';
print \n;
?
Note that you just redirect the output to psql and off you go.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Load a csv file into a pgsql table
Emi Lu wrote: Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? Quick? No. Joshua D. Drake Thanks a lot! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Initializing Datums for use with SPI_execute_plan
On Tue, Sep 19, 2006 at 01:27:56PM -0400, Jack Orenstein wrote: On 9/18/06, Jack Orenstein [EMAIL PROTECTED] wrote: -- Forwarded message -- From: Andrew - Supernews [EMAIL PROTECTED] ... Jack I have an int8 that I need as a Datum for use with Jack SPI_execute_plan. Int64GetDatum(your_variable) which then has to be pfree'd, correct? Possibly, it probably depends on the architechture. The memory is being allocated in a per-call context IIRC so it'll be freed at the end of the function anyway. I wouldn't worry about it. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Initializing Datums for use with SPI_execute_plan
On 9/19/06, Martijn van Oosterhout kleptog@svana.org wrote: On Tue, Sep 19, 2006 at 01:27:56PM -0400, Jack Orenstein wrote: On 9/18/06, Jack Orenstein [EMAIL PROTECTED] wrote: -- Forwarded message -- From: Andrew - Supernews [EMAIL PROTECTED] ... Jack I have an int8 that I need as a Datum for use with Jack SPI_execute_plan. Int64GetDatum(your_variable) which then has to be pfree'd, correct? Possibly, it probably depends on the architechture. The memory is being allocated in a per-call context IIRC so it'll be freed at the end of the function anyway. I wouldn't worry about it. Can you provide some guidance (or point to some documentation) on how to manage memory? Is the idea that I should (must?) not pfree palloc'ed memory from Int64GetDatum, but I should free anything I allocate myself using palloc? Or not even that? The C extension I'm writing, which uses the SPI, will be called thousands or millions of times as part of a data conversion -- I do have to worry about memory leaks. Once the conversion completes, I won't use the function any longer. But I'd rather not leak memory and have to do something drastic to reclaim it, such as bouncing postgresql. Jack ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Load a csv file into a pgsql table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/19/06 13:26, Steve Atkins wrote: On Sep 19, 2006, at 11:15 AM, Emi Lu wrote: Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? There are several bulk loaders, but I believe they all use COPY behind the scenes. If copy isn't an option then your best bet will be many inserts in a transaction, but that'll be significantly slower. You could cobble together a loader using perl, DBI and one of the CPAN CSV modules fairly easily. (But I can't think of any reason why you wouldn't use copy, so you must have some constraint you haven't mentioned - can you expand on why copy isn't an option?) COPY has great facilities for specifying the physical layout of the CSV file, but is otherwise limited. Facilities that I miss are: statistics: loaded 1 rows, loaded 2 rows, ... etc. skip: if the COPY dies (or is killed after 10Mn rows have been loaded, it's so useful to be able to add --skip=10425000 to the command and have the bulk loaded quickly scan to that record. Yes, tail(1) can slice off the unloaded records, but that means that now you have 2 files. Messy. transactions: goes hand-in-glove with statistics and skip. exceptions file: if you have a unique index on the table, and one of the input records is a duplicate, kick it out to an exceptions file, note it to stderr and keep on loading. A fields option would also be handy. This is for when the number of fields in the input file does not equal those in the table. Just MHO, of course. - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFEFPsS9HxQb37XmcRAkxpAJ9czWEjP+lYDInS8dVeN9OLYY865wCfU0Fm /Z3FxL6o5XCU3SivPFQDVEc= =K438 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Initializing Datums for use with SPI_execute_plan
Jack Orenstein [EMAIL PROTECTED] writes: The C extension I'm writing, which uses the SPI, will be called thousands or millions of times as part of a data conversion -- I do have to worry about memory leaks. Not if it's called in a short-lived context, which should ordinarily be the case. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] duplicate key violates unique constraint
Hey, I've just find out what's happening. The problem is the serial datatype creates a sequence in the background (project_id_seq). If the sequence current numeber is 1, and I manually insert a new entry whit ID=2, the sequence doesn't know it. So when I try the INSERT statement, the next value in sequence is 2, and I get the error. The thing is, I'm migrating my system from mysql to postgresql, and that's why I was inserting directely the numbers (importing the .sql file), without respecting the backgroud sequence. Thanks a lot for those who answered me. Regards, Verônica ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] statistics buffer is full on heavily loaded 8.1.4 db.
We have a 4x 2.4ghz Opteron box w/ 8 gigs of ram running a very busy pg 8.1.4 server. Upon startup we see around 15-20 statistics buffer is full messages and they repeat at random times throughout the day. During peak times the box sometimes seems to grind to a halt. Any thoughts? - E ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] statistics buffer is full on heavily loaded 8.1.4 db.
Do you have stats_command_string=onin postgresql.conf as that might cause this error.If it is truned on please turn it off and then try starting your server.Thanks,-- Shoaib MirEnterpriseDB ( www.enterprisedb.com)On 9/20/06, Eci Souji [EMAIL PROTECTED] wrote: We have a 4x 2.4ghz Opteron box w/ 8 gigs of ram running a very busy pg8.1.4 server.Upon startup we see around 15-20 statistics buffer isfull messages and they repeat at random times throughout the day. During peak times the box sometimes seems to grind to a halt.Any thoughts?- E---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] statistics buffer is full on heavily loaded 8.1.4 db.
Eci Souji wrote: We have a 4x 2.4ghz Opteron box w/ 8 gigs of ram running a very busy pg 8.1.4 server. Upon startup we see around 15-20 statistics buffer is full messages and they repeat at random times throughout the day. During peak times the box sometimes seems to grind to a halt. Any thoughts? Do you have stats_command_string on? Joshua D. Drake - E ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] FileMakerPro to postgreSQL
[EMAIL PROTECTED] writes: ... My biggest concern is the existing FileMakerPro clients. How much work would it be to connect the existing FMP clients to the postgreSQL database using ODBC? Is this a viable temporary step or could it be permanent? And Tom Lane replies: If they can speak ODBC, then in theory you can easily connect them to PG that way. There's a fair-size gap between theory and practice, however And Shane Ambler wrote: Yes filemaker can use ODBC but not as a live data source as you would use access as a frontend. You can use ODBC to import the data to a Filemaker database and display/edit it there and then export back through ODBC. If the client wants to go forward on this I'm leaning toward moving away from FileMakerPro completely and starting from scratch. In the long run it'll probably be less work and result in a better end product anyway. Thanks for your thoughs and info, Tom and Shane. brew == Strange Brew ([EMAIL PROTECTED]) Check out my Stock Option Covered Call website http://www.callpix.com and my Musician's Online Database Exchange http://www.TheMode.com == ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] statistics buffer is full on heavily loaded 8.1.4 db.
Oops, knew I forgot something. Here are the settings from our postgresql.conf I beilive it's set to allow autovac to work and nothing else. # - Query/Index Statistics Collector - stats_start_collector = on stats_command_string = off stats_block_level = off stats_row_level = on #stats_reset_on_server_start = off - E Joshua D. Drake wrote: Eci Souji wrote: We have a 4x 2.4ghz Opteron box w/ 8 gigs of ram running a very busy pg 8.1.4 server. Upon startup we see around 15-20 statistics buffer is full messages and they repeat at random times throughout the day. During peak times the box sometimes seems to grind to a halt. Any thoughts? Do you have stats_command_string on? Joshua D. Drake - E ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] vista
So... If you're not a PostgreSQL Win32 port dev, and *don't know* what they're up to as far as Vista, why respond to the Q? Or why respond fix it yourself instead of ask this guy or nobody here will know yet or post your query on -ports or -hackers. Precisely. My point is not that people *should* care about Win32, or that some coercive system should be put into place to force devs onto RFEs, or even that we should commit to having something ready at all ever. It's a point of apparent attitude. Responding with a useless answer is *worse* than simply ignoring the question. I am not a Win32 user (at least not on servers), but if I wanted to know if Vista compatibility was being worked on getting if you want it, go do it would be even less useful than an effort to convince me to run the DB on a *nix back end. I'd rather have someone tell me how and why to migrate to a better OS. Frankly, I too could care less about PG on Vista. Longhorn isn't due until Vista SP1, so PG support has a long time to go before it's a real concern. But then I didn't try to answer the question. Same here. It's a matter of apparent attitude of the community to outsiders or new users. Even with something like future Win32 support, I'd rather see people being told that issue is not important to our project because x, y and z than go do it yourself. Anyway, I seem to have kicked up a bit of a hornet's nest here so I'll shut up now. - Naz. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] postgresql rising
I have seen a steady progressive rise in the number of postgresql related jobs and the quality of those jobs. Major companies are apparently rolling out critical infrastructure on postgresql...Vonage is one example: (http://jobsearch.monster.com/getjob.asp?JobID=47975237AVSDM=2006%2D09%2D15+13%3A07%3A10Logo=1JobTitle=PostgreSQL+Databa%2E%2E%2Eq=postgresqlcy=usJSNONREG=1Image1.x=0Image1.y=0dcjvlid=380). Salaries for a capable pg dba are really attractive, I have seen several in the 6 figure range. If you are reading this list and you like making money, this is amazing news folks. I am seeing a confluence of many factors leading to serious penetration into the enterprise market. Around 5 years ago after being mostly a c/c++ developer I decided postgresql was where it was at. Learning the database and becoming productive with it has been professionally rewarding on many levels. It's really exciting watching the community evolve. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL slammed by PHP creator
In article [EMAIL PROTECTED], [EMAIL PROTECTED] (Robert Treat) wrote: On Thursday 14 September 2006 12:19, Joshua D. Drake wrote: Arturo Perez wrote: Hi all, Any response to this: http://www.internetnews.com/dev-news/article.php/3631831 Well first, your subject line is very incendiary and not necessary... I Well, sorry about that. The article, as written, made it sound like he said, First, dump PostgreSQL to increase your performance then... That said, in case anyone needs it, here is a benchmark showing postgresql scalability vs mysql. interestingly enough it isnt really a database benchmark, whether this adds or detracts from it's creditability is up to you... http://tweakers.net/reviews/638/4 That's scalability, not speed. Don't know that the PHP/MySQL crowd have ever crowed about anything except speed. For those not knowing the difference, scalability is we can handle 5000 concurrent transactions with an average response time of 10ms while speed is we can make this transaction complete in 10ms. The difference being that something fast may fall over if too many transactions occur within too small a window (deadlocking, thread synchronization overhead, table/row locking, etc) so that at 5000 concurrent transactions the speed may be reduced to significantly (as shown in the tweakers.net pages). However, he does carry some umpf in certain circles. Perhaps we should prove him wrong? I'd like to see you do that... here are the slides from his recent talk showing why he came up with the statements he made (http://talks.php.net/show/oscon06/1). Please post the info when you get comparable performance running from PostgreSQL... That's what I'd really like to know. Honestly, how does one make pgSQL go really really fast on a vanilla machine that one is likely to encounter at a hosting facility? As a pgSQL enthusiast, I want to see all the things needed to get pgSQL to the 1500 tps level. Unfortunately, we don't know anything about the database requirements other than the one query shown in the source examples. I'll have to look at the system I recently converted from MySQL to PostgreSQL and see what I can find... -arturo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Clustering
Hi Can somebody point me to some articles/how-to's on postgres clustering and maybe comparisons to MySQL. (Recently saw an article on scaling MySQL - up to 16 nodes) Alex ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Odd behavior observed
I wrote: ... I think maybe something is applying an UPDATE to the row and losing the new value at that point. Are any of the FKs non-default actions (ON ... SET NULL or some such that would try to alter data instead of just erroring)? I've been able to reproduce a problem that may or may not be Marc's problem, but it's definitely a bug: regression=# create table foo(f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE regression=# create table bar(f1 int references foo on delete set null); CREATE TABLE regression=# insert into foo values(1); INSERT 0 1 regression=# insert into bar values(1); INSERT 0 1 regression=# delete from foo; DELETE 1 regression=# select * from bar; f1 (1 row) regression=# alter table bar add column f2 int; ALTER TABLE regression=# insert into foo values(1); INSERT 0 1 regression=# insert into bar values(1,2); INSERT 0 1 regression=# select * from bar; f1 | f2 + | 1 | 2 (2 rows) regression=# delete from foo; DELETE 1 regression=# select * from bar; f1 | f2 + | | (2 rows) regression=# f2 should clearly not have gotten set to null there. I believe the problem is that we have a stale cached plan for the ON DELETE SET NULL referential action. Still another reason why we need a plan invalidation mechanism :-( regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
