[GENERAL] Conditional table update. Left join vs NOT IN
Greetings, I have a table which I would like to (conditionally and efficiently) populate if the would be new records do not already exist in that table. So far I see that I may use either a left join with a WHERE right table key field is NULL. Or I could use a sub query and a NOT IN clause. Perhaps there another better way of doing this. Which of these options will more likely be more efficient than the other(s)? 1) INSERT INTO foo (key_field1,field2) SELECT key_fieldA,fieldB FROM foo2 a LEFT JOIN foo b ON b.key_fieldA=a.key_field1 WHERE b.key_fieldA IS NULL ; 2) INSERT INTO foo (key_field1,field2) SELECT key_fieldA,fieldB FROM foo2 a WHERE a.key_fieldA NOT IN ( SELECT a.key_field1 FROM foo a ) ; Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Mon, Aug 9, 2010 at 1:51 AM, Scott Frankel fran...@circlesfx.com wrote: On Aug 8, 2010, at 2:45 AM, Torsten Zühlsdorff wrote: Scott Frankel schrieb: On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote: John Gage schrieb: On reflection, I think what is needed is a handbook that features cut and paste code to do the things with Postgres that people do today with MySQL. Everyone of my trainees want such thing - for databases, for other programming-languages etc. It's the worst thing you can give them. The will copy, they will paste and they will understand nothing. Learning is the way to understanding, not copying. I couldn't disagree more. Presenting working code (at least snippets) should continue to be a fundamental part of any documentation project. You missunderstand me. Working code is a fundamental part of any documentation. But we talk about a handbook with code that works in PostgreSQL and does the same thinks in MySQL. This way the trainees won't learn how PostgreSQL works, the just learn the different examples. Giving them training-problems and the PostgreSQL handbook is out of my experience the best way. It tooks longer for them to solve the problems, but in this way they are able to solve problems, which are not related to the presented examples. I understand and appreciate your position. Thanks for the clarification. While I believe that this thread has, for all intents and purposes, run its course (and I look forward to reading the documentation it informs), I'm going to go out on a limb and present an additional use-case that may be unpopular, or at least controversial. There are times when a documentation's audience is not interested in taking the subject matter to expert level. (eg: informed supervisory or vendor-client relationships, proof of concept development, hobbies, c.). For those cases, a working understanding is all that's strictly necessary. Annotated, cookbook-style code reference is especially well suited for that mode of learning. Regards, Scott I think it is important to have some examples in the documentation. Some kind of balance between few examples and too many examples. From what I have seen many users in this mailing list usually do include some example code of the problem they have as an aid to explaining their current situation. This seems to me quite useful. The other day I asked for help in implementing DISTINCT ON like behaviour and was pointed correctly to the docs. The examples in DISTINCT ON documentation quickly led me to understand the working of this command. Sometime back I was learning about Window functions (rank() and so on) where I found the examples very useful and I feel it would have been a challenge for me to understand these concepts without the use of examples. There may be worry of copy and paste without proper understanding of the code and concepts but this may be mitigated IMHO by fact that it seems unlikely that when presented with a case to solve, simple copy and paste of several commands (in the right sequence) will happen and correctly solve the problem at hand without appreciation of what these commands and statements do. Also more often or not the copy and paste will only work on specific schema definitions and data used in the example, therefore reconstruction (hence understanding) of these commands is neccessary. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Mon, Aug 9, 2010 at 12:15 PM, Allan Kamau kamaual...@gmail.com wrote: There may be worry of copy and paste without proper understanding of the code and concepts but this may be mitigated IMHO by fact that it seems unlikely that when presented with a case to solve, simple copy and paste of several commands (in the right sequence) will happen and correctly solve the problem at hand without appreciation of what these commands and statements do. Also more often or not the copy and paste will only work on specific schema definitions and data used in the example, therefore reconstruction (hence understanding) of these commands is neccessary. The way I see it - for those who want to truly learn, there is the documentation. For those who dont, there are ORMs. For the rest of us, still floundering in MySQL land, please build a bridge. -Sandeep
Re: [GENERAL] MySQL versus Postgres
Sandeep Srinivasa wrote on 09.08.2010 08:54: The way I see it - for those who want to truly learn, there is the documentation. For those who dont, there are ORMs. Another of those ORM myths ;) ORMs are not an alternative to learning SQL or understand how a DBMS works. You need to be good at SQL and you need a good understanding of relational databases in order to use an ORM efficiently. One of the first sentences in the Hibernate manual is: If you have a limited knowledge of JAVA or SQL, it is advised that you start with a good introduction to that technology prior to attempting to learn Hibernate Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On 09/08/10 14:54, Sandeep Srinivasa wrote: The way I see it - for those who want to truly learn, there is the documentation. For those who dont, there are ORMs. Ha, I wish! Despite being rather comfortable with SQL I've been using the Hibernate ORM system in a project to try to reduce some of the repetitive coding, while falling back to JDBC and hand-coded SQL where Hibernate doesn't do a great job. This is my first venture into ORM-land, and may well be my last. The amount of learning required in getting the ORM to behave even vaguely sanely in anything but trivial situations is vastly greater than what's required to use plain SQL. I'm not at all sure it's worth it for anything but the hugest projects, as I've wasted way more time battling Hibernate than I would've done writing all the repetitive template classes and SQL mappings myself. All the lazy-loading stuff is useless in practice, because you're always working with detached entities by the time you need it, so it doesn't help with the problem of figuring out what data your app is going to need well before it asks for it. Additionally, ORM system authors seem to consider the database to be getting uppity and above its place if it's used for anything much more than a dumb row store. Basic database features like referential integrity constraints (especially things like ON DELETE CASCADE), in-database triggers, column privileges, etc tend to confuse it mightily, because it assumes it'll be the only thing making changes to the database. Hibernate is better than most in this regard, and way better than things like ActiveRecord (from Ruby on Rails) in that it understands most basic database features and can be told not to cache between sessions, but it still gets frustrating as soon as you try to do things like use weird data types like the native xml type in Pg, (for which I had to write a custom UserType mapping). You certainly do need to have a decent understanding of basic SQL to use an ORM reasonably efficiently, including the trade-offs of joins vs subqueries, how queries can be rewritten/replanned by the database, the cost of vast numbers of repeated small queries vs large-and-expensive one-off multi-way joins that return repetitive information, the effect of latency and planning time, the difference between prepared and one-off statements, parameter placement, etc. That said, there are large sections of the SQL language that most ORMs appear to never go anywhere near. You won't be using window functions and custom aggregates in any ORM. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Aug 8, 2010, at 2:45 AM, Torsten Zühlsdorff wrote: *snip* I understand and appreciate your position. Thanks for the clarification. While I believe that this thread has, for all intents and purposes, run its course (and I look forward to reading the documentation it informs), I'm going to go out on a limb and present an additional use- case that may be unpopular, or at least controversial. There are times when a documentation's audience is not interested in taking the subject matter to expert level. (eg: informed supervisory or vendor-client relationships, proof of concept development, hobbies, c.). For those cases, a working understanding is all that's strictly necessary. Annotated, cookbook-style code reference is especially well suited for that mode of learning. As a recent convert from MySQL (I needed PostGIS) who has also seen the benefit of Postgresql over MySQL in numerous other areas, that's exactly what I am doing for myself. I have 4x6 cards that I write the postgresql way of doing what I use to do with MySQL so that I can easily reference them when I need to. Should I sit down and read a book and go through the exercises? Yes. But I need to get stuff done now, and the cheat sheets I make for myself let me do just that. I am not a DBA - I am not even a web developer. I do both because I can't afford to hire them, and when I have used stuff created by them, very frequently their code is clearly crap and insecure and even I can see that, so unless I really want to pay the big bucks, it's better for me to do it myself and cheat sheets really help. - Michael A. Peters http://www.shastaherps.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accessing a database via AJAX scripts - solved
The solution to my problems involved both of the issues Tom identified. 1. Proc::Daemon::Init() call closed the libpq connection in the parent. Solution: deliberately close connection before call and open in the child after the call. 2. The status updates were being written inside a transaction Solution: open a second connection in the child process and use for the status updates to allow the monitor script to select them. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 4446 Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015 A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Mon, Aug 9, 2010 at 2:28 PM, Michael A. Peters mpet...@shastaherps.orgwrote: I have 4x6 cards that I write the postgresql way of doing what I use to do with MySQL so that I can easily reference them when I need to. Should I sit down and read a book and go through the exercises? Yes. But I need to get stuff done now, and the cheat sheets I make for myself let me do just that. I am not a DBA - I am not even a web developer. I do both because I can't afford to hire them, and when I have used stuff created by them, very frequently their code is clearly crap and insecure and even I can see that, so unless I really want to pay the big bucks, it's better for me to do it myself and cheat sheets really help. Could you share them ? Maybe put them up on the Postgres wiki. The other similar resource that I see is : http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL -Sandeep
[GENERAL] Can database run a script automatically at start up?
Can PostgresSQL be setup to automtatically run a script at start up? I need something to run at start up in case the database crashed or some programs did not close their databases properly on shutdown. /voipfc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can database run a script automatically at start up?
2010/8/9 Frank Church voi...@googlemail.com Can PostgresSQL be setup to automtatically run a script at start up? I need something to run at start up in case the database crashed or some programs did not close their databases properly on shutdown. /voipfc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general It can't, but you may write a script that will do that and will start the database, so you won't be starting that normally, but only with this script. regards Szymon Guz
[GENERAL] pgtune
I just found out about pgtune and am trying it out on my server. I have 2.5 questions: 1) Are these settings the maximum that the server will handle, if it is strictly dedicated to postgresql? Meaning if I am running other stuff on the server as well, this would be a bad idea. 1a) If I have some intense plpython stored procedures, do they run in the postgresql memory space (ie using the memory settings from the postgresql.conf, or do they run under their own memory space and then I would have to take that into account when allocating postgresql memory? 2) If it sets my max_connections to 80 and would like to set it at 300, what would be the appropriate setting to lower at its expense? Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] InitDB: Bad system call
Hello, i've just compiled a new Jail at my FreeBDS 7.0-STABLE machine and trying to get PostgreSQL 9.0 Beta 4 running. Compiling etc works fine. But when i call the initdb, i get Bad System Call messages. Here is the output: $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -d Running in debug mode. VERSION=9.0beta4 PGDATA=/usr/local/pgsql/data share_path=/usr/local/pgsql/share PGPATH=/usr/local/pgsql/bin POSTGRES_SUPERUSERNAME=postgres POSTGRES_BKI=/usr/local/pgsql/share/postgres.bki POSTGRES_DESCR=/usr/local/pgsql/share/postgres.description POSTGRES_SHDESCR=/usr/local/pgsql/share/postgres.shdescription POSTGRESQL_CONF_SAMPLE=/usr/local/pgsql/share/postgresql.conf.sample PG_HBA_SAMPLE=/usr/local/pgsql/share/pg_hba.conf.sample PG_IDENT_SAMPLE=/usr/local/pgsql/share/pg_ident.conf.sample The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. The default database encoding has accordingly been set to SQL_ASCII. The default text search configuration will be set to english. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) 10 selecting default shared_buffers ... Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) 400kB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... Bad system call (core dumped) child process exited with exit code 140 initdb: removing contents of data directory /usr/local/pgsql/data There is no further message in /var/log/messages. First i believed this is an error relating to SYSVSHM-, SYSVSEM-, SYSVMSG-options or User-Id (http://www.freebsddiary.org/jail-multiple.php). But the postgres-user has a user-id which is not used by other postgres-instances in other jails. And the other options are enabled in the root-instance. I also tried to build postgres from a fresh portstree, to make sure, that i have nothing miss-./configured, but there are the same problems. I have no clue, what the problem is. Any hints? Thanks, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] InitDB: Bad system call
On 9 August 2010 12:56, Torsten Zühlsdorff f...@meisterderspiele.de wrote: Hello, i've just compiled a new Jail at my FreeBDS 7.0-STABLE machine and trying to get PostgreSQL 9.0 Beta 4 running. Compiling etc works fine. But when i call the initdb, i get Bad System Call messages. Here is the output: $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -d Running in debug mode. VERSION=9.0beta4 PGDATA=/usr/local/pgsql/data share_path=/usr/local/pgsql/share PGPATH=/usr/local/pgsql/bin POSTGRES_SUPERUSERNAME=postgres POSTGRES_BKI=/usr/local/pgsql/share/postgres.bki POSTGRES_DESCR=/usr/local/pgsql/share/postgres.description POSTGRES_SHDESCR=/usr/local/pgsql/share/postgres.shdescription POSTGRESQL_CONF_SAMPLE=/usr/local/pgsql/share/postgresql.conf.sample PG_HBA_SAMPLE=/usr/local/pgsql/share/pg_hba.conf.sample PG_IDENT_SAMPLE=/usr/local/pgsql/share/pg_ident.conf.sample The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. The default database encoding has accordingly been set to SQL_ASCII. The default text search configuration will be set to english. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) 10 selecting default shared_buffers ... Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) 400kB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... Bad system call (core dumped) child process exited with exit code 140 initdb: removing contents of data directory /usr/local/pgsql/data There is no further message in /var/log/messages. First i believed this is an error relating to SYSVSHM-, SYSVSEM-, SYSVMSG-options or User-Id (http://www.freebsddiary.org/jail-multiple.php). But the postgres-user has a user-id which is not used by other postgres-instances in other jails. And the other options are enabled in the root-instance. I also tried to build postgres from a fresh portstree, to make sure, that i have nothing miss-./configured, but there are the same problems. I have no clue, what the problem is. Any hints? Thanks, Torsten -- See http://www.postgresql.org/docs/9.0/static/kernel-resources.html and the section under NetBSD/OpenBSD. -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] InitDB: Bad system call
On Mon, Aug 9, 2010 at 6:01 PM, Thom Brown t...@linux.com wrote: See http://www.postgresql.org/docs/9.0/static/kernel-resources.html and the section under NetBSD/OpenBSD. -- Thom Brown Registered Linux user: #516935 Thom Not sure if it's a typo, but shouldn't he be looking under FreeBSD section as he is running FreeBSD 7.0? Amitabh Kant
Re: [GENERAL] InitDB: Bad system call
On 9 August 2010 13:56, Amitabh Kant amitabhk...@gmail.com wrote: On Mon, Aug 9, 2010 at 6:01 PM, Thom Brown t...@linux.com wrote: See http://www.postgresql.org/docs/9.0/static/kernel-resources.html and the section under NetBSD/OpenBSD. -- Thom Brown Registered Linux user: #516935 Thom Not sure if it's a typo, but shouldn't he be looking under FreeBSD section as he is running FreeBSD 7.0? Ah yes, my bad. -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgtune
What is the name of DW in --type=DW Sorry for my English. Tuan Hoang Anh On Mon, Aug 9, 2010 at 6:21 PM, Amitabh Kant amitabhk...@gmail.com wrote: 2010/8/9 Sim Zacks s...@compulab.co.il I just found out about pgtune and am trying it out on my server. I have 2.5 questions: 1) Are these settings the maximum that the server will handle, if it is strictly dedicated to postgresql? Meaning if I am running other stuff on the server as well, this would be a bad idea. 1a) If I have some intense plpython stored procedures, do they run in the postgresql memory space (ie using the memory settings from the postgresql.conf, or do they run under their own memory space and then I would have to take that into account when allocating postgresql memory? 2) If it sets my max_connections to 80 and would like to set it at 300, what would be the appropriate setting to lower at its expense? Sim Look at the options available in pgtune -M TOTALMEMORY, --memory=TOTALMEMORY Total system memory, will attempt to detect if unspecified -T DBTYPE, --type=DBTYPE Database type, defaults to Mixed, valid options are DW, OLTP, Web, Mixed, Desktop -c CONNECTIONS, --connections=CONNECTIONS Maximum number of expected connections, default depends on database type For question 1, you can set the type of server you want. For question 2, you can pass the -c parameter and it would adjust the other parameters. Not sure of 1a though. Amitabh Kant
Re: [GENERAL] MySQL versus Postgres
It's not a requirement, just a reasonable default. On Aug 7, 2010, at 11:09 AM, Martin Gainty wrote: 3)eliminate the requirement to create a postgres user to execute the server binaries..I guess i never understood that requirement -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
Joshua == Joshua J Kugler jos...@eeinternet.com writes: Joshua I'll add in a me too only to say that I am someone that learns Joshua best by example. Keep in mind though that there are three primary learning modes: - example - concept - structure Do not overemphasize the example mode at the cost of presenting concepts or structure. You need all three. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 mer...@stonehenge.com URL:http://www.stonehenge.com/merlyn/ Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc. See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Mon, 2010-08-09 at 10:09 -0600, Scott Ribe wrote: It's not a requirement, just a reasonable default. The actual requirement is: Thou shall not use a privelaged user, e.g; Administrator or UID = 0. Not only is that a reasonable default, MySQL is broken because of theirs. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Monday 09 August 2010, Randal L. Schwartz elucidated thus: Joshua == Joshua J Kugler jos...@eeinternet.com writes: Joshua I'll add in a me too only to say that I am someone that learns Joshua best by example. Keep in mind though that there are three primary learning modes: - example - concept - structure Do not overemphasize the example mode at the cost of presenting concepts or structure. You need all three. Oh, definitely. I like reading the concepts and structure of how, say, an API works, and use it for reference, but what really cements *how* to use it is those examples. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x73B13B6A -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On Monday 09 August 2010, Joshua D. Drake elucidated thus: On Mon, 2010-08-09 at 10:09 -0600, Scott Ribe wrote: It's not a requirement, just a reasonable default. The actual requirement is: Thou shall not use a privelaged user, e.g; Administrator or UID = 0. Not only is that a reasonable default, MySQL is broken because of theirs. Joshua D. Drake Hmm...I've always seen MySQL run under the user mysql. Of course, mysqld_safe (the script that restarts mysql if it crashes) starts as root, but the actually binary runs as mysql. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0x73B13B6A -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with dumps
Hi folks, I'm building a new server with postgres/phppgadmin, and having trouble getting the dumps to work properly. This is my first time installing postgres, so I very well may have missed something. I just corrected the paths to pg_dump and pg_dumpall. By the way, what's relog mean in the error message: Export error: Failed to execute pg_dump (given path in your conf/config.inc.php : /usr/bin/pg_dump). Please, fix this path in your configuration and relog. I rebooted the server just to be sure. Unfortunately, though having the correct paths helped a fair bit, it's not working correctly when performing an export in phppgadmin. Data / Copy / Show or Download = OK Data / SQL / show or download = blank Structure / SQL / Show or download = OK Structure and data / copy /show or downlad = OK Structure and data / SQL /show or downlad = blank Anyone know what's up? Thanks! -- Bill Christensen http://greenbuilder.com/contact/ Green Building Professionals Directory: http://directory.greenbuilder.com Sustainable Building Calendar: http://Calendar.SustainableSources.com Green Real Estate: http://www.greenbuilder.com/realestate/ Straw Bale Registry: http://sbregistry.greenbuilder.com/ Books/videos/software: http://bookstore.greenbuilder.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
Joshua J. Kugler jos...@eeinternet.com writes: On Monday 09 August 2010, Joshua D. Drake elucidated thus: The actual requirement is: Thou shall not use a privelaged user, e.g; Administrator or UID = 0. Not only is that a reasonable default, MySQL is broken because of theirs. Hmm...I've always seen MySQL run under the user mysql. Of course, mysqld_safe (the script that restarts mysql if it crashes) starts as root, but the actually binary runs as mysql. That's how it's done if the user/packager knows what they're doing. The problem is that not only doesn't mysql enforce that, it isn't the default --- mysqld_safe is perfectly happy to launch the server as root if you don't tell it not to. If you dig hard enough in their manuals, you can find a recommendation to not run the server as root; but they don't exactly push you to avoid that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a way to bypass sql?
I have been digging into NoSQL of late. For navigational queries it would be great if there was a way to bypass SQL and directly pull from an identifier for a record or arbitrary byte stream. Does postgresql directly support such ability? What is the closest that you could come? - samantha
Re: [GENERAL] Is there a way to bypass sql?
hello 2010/8/9 samantha sjatk...@mac.com: I have been digging into NoSQL of late. For navigational queries it would be great if there was a way to bypass SQL and directly pull from an identifier for a record or arbitrary byte stream. Does postgresql directly support such ability? What is the closest that you could come? no, there are nothing similar - you cannot to bypass SQL. Regards Pavel Stehule - samantha -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a way to bypass sql?
On Mon, Aug 9, 2010 at 2:39 PM, samantha sjatk...@mac.com wrote: I have been digging into NoSQL of late. For navigational queries it would be great if there was a way to bypass SQL and directly pull from an identifier for a record or arbitrary byte stream. Does postgresql directly support such ability? What is the closest that you could come? You can get pretty close, depending on how you define 'bypass'. For example, it is possible to send rich data structures back and forth between the client and the server without constructing a SQL text string. Those structures still have to be strongly typed in the server unless you want to stuff everything into a bytea (which btw I think is a terrible idea for most cases). Could you describe in more detail what you'd like to do and what (if any) inefficiencies or restrictions SQL is imposing that you would like to bypass? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to reference a subquery column alias?
Hello Yesterday a list user solved me a problem with a sententence with two subqueries. The solution was this: SELECT remota_id,min(hora),max(hora), (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal, (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=max(ooo.hora)) as max_caudal from historicos AS ooo GROUP BY remota_id ORDER BY remota_id; The issue now is i want to do some calculations with the subqueries columns (min_caudal and max_caudal), for example adding them. (Get a new column with max_caudal and min_caudal (alias) added) I have tried to add in the SELECT; ,max_caudal+min_caudal as diferencia ,ooo.max_caudal+ooo.min_caudal as diferencia ,historicos.max_caudal+historicos.min_caudal as diferencia ,(SELECT max_caudal+min_caudal) as diferencia I've read the SELECT and Table Expressions documentation pages, but didn't found a solution. Can anyone tell me how to reference or make the calculation with those alias names? TIA Best, =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6) AntiVirus: ClamAV 0.95.2/11523 - Mon Aug 9 19:20:40 2010 by Markus Madlener @ http://www.copfilter.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hector Beyers wants to stay in touch on LinkedIn
LinkedIn Hector Beyers requested to add you as a connection on LinkedIn: -- Andrew, I'd like to add you to my professional network on LinkedIn. - Hector Beyers Accept invitation from Hector Beyers http://www.linkedin.com/e/v74zw8-gcns1qml-31/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2254287815_2/1BpC5vrmRLoRZcjkkZt5YCpnlOt3RApnhMpmdzgmhxrSNBszYOnPkNe3sUczgRcz99bPl3t6NAt4VRbP0Sdz8Od3oQdj4LrCBxbOYWrSlI/EML_comm_afe/ View invitation from Hector Beyers http://www.linkedin.com/e/v74zw8-gcns1qml-31/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2254287815_2/39vdj4UdPwOd3kOcAALqnpPbOYWrSlI/svi/ -- DID YOU KNOW you can use your LinkedIn profile as your website? Select a vanity URL and then promote this address on your business cards, email signatures, website, etc http://www.linkedin.com/e/v74zw8-gcns1qml-31/ewp/inv-21/ -- (c) 2010, LinkedIn Corporation
Re: [GENERAL] How to reference a subquery column alias?
Hi, Just wrap your expression with another SELECT and operate with the aliases like SELECT *, min_caudal + max_caudal AS diferencia FROM ( ...your expression... ) 2010/8/9 José María Terry Jiménez j...@tssystems.net: Hello Yesterday a list user solved me a problem with a sententence with two subqueries. The solution was this: SELECT remota_id,min(hora),max(hora), (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal, (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=max(ooo.hora)) as max_caudal from historicos AS ooo GROUP BY remota_id ORDER BY remota_id; The issue now is i want to do some calculations with the subqueries columns (min_caudal and max_caudal), for example adding them. (Get a new column with max_caudal and min_caudal (alias) added) I have tried to add in the SELECT; ,max_caudal+min_caudal as diferencia ,ooo.max_caudal+ooo.min_caudal as diferencia ,historicos.max_caudal+historicos.min_caudal as diferencia ,(SELECT max_caudal+min_caudal) as diferencia I've read the SELECT and Table Expressions documentation pages, but didn't found a solution. Can anyone tell me how to reference or make the calculation with those alias names? TIA Best, =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6) AntiVirus: ClamAV 0.95.2/11523 - Mon Aug 9 19:20:40 2010 by Markus Madlener @ http://www.copfilter.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a way to bypass sql?
On Mon, Aug 9, 2010 at 1:39 PM, samantha sjatk...@mac.com wrote: I have been digging into NoSQL of late (...) Be wary of DBAs Running with Scissors... http://www.pgcon.org/2010/schedule/attachments/141_PostgreSQL-and-NoSQL.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to reference a subquery column alias?
Hello Sergey Thanks by your answer, this worked after i add at the end an AS xxx clause, because an error telling me something about subqueries in FROM must have an alias, so i did it: SELECT *, min_caudal + max_caudal AS diferencia FROM ( ...your expression... ) AS temp and worked Best, Sergey Konoplev escribió: Hi, Just wrap your expression with another SELECT and operate with the aliases like SELECT *, min_caudal + max_caudal AS diferencia FROM ( ...your expression... ) 2010/8/9 José María Terry Jiménez j...@tssystems.net: Hello Yesterday a list user solved me a problem with a sententence with two subqueries. The solution was this: SELECT remota_id,min(hora),max(hora), (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal, (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=max(ooo.hora)) as max_caudal from historicos AS ooo GROUP BY remota_id ORDER BY remota_id; The issue now is i want to do some calculations with the subqueries columns (min_caudal and max_caudal), for example adding them. (Get a new column with max_caudal and min_caudal (alias) added) I have tried to add in the SELECT; ,max_caudal+min_caudal as diferencia ,ooo.max_caudal+ooo.min_caudal as diferencia ,historicos.max_caudal+historicos.min_caudal as diferencia ,(SELECT max_caudal+min_caudal) as diferencia I've read the SELECT and Table Expressions documentation pages, but didn't found a solution. Can anyone tell me how to reference or make the calculation with those alias names? TIA Best, =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6) AntiVirus: ClamAV 0.95.2/11523 - Mon Aug 9 19:20:40 2010 by Markus Madlener @ http://www.copfilter.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg 9.0, streaming replication, fail over and fail back strategies
Hello, I'm new to the list and not even sure if this is the right place to be posting this... I've worked through the documentation for postgres 9.0 (beta2) and have successfully set up a master and hot slave configured with streaming replication (and xlog shipping). That configuration seems to be correctly updating the slave and the slave accepts read queries and shows up to date table data (based on testing by hand with some DDL and insert queries). Now that I have that successfully configured, I have manually performed a fail over by stopping the master, moving a virtual IP address from the master to the slave, and touched the trigger file on the slave. This worked as expected and the former slave promoted itself to being a full read/write master. I went through the process of failing back manually by dumping the database on the slave, restoring it on the master, moving the VIP back and renaming the recovery.done back to recovery.conf. This took some time and required several steps, but was also successful. After I had moved the VIP from the master to the slave, I had to restart (not just reload) the postgres daemon to get it to start listening on the new ip address (it was previously listening to another IP [10.x.x.y] on the same NIC [eth0]). I have the listen_addresses configured to listen on both an internal (10.x.x.y) address as well as the vip (10.x.x.z), but the interface on the slave did not have this ip address at the time Postgres was started (so I'm not all that surprised it didn't bind to that address on becoming the master). Is there any way to get PostgreSQL to bind to a new ip address and interface without actually shutting it down? If it could, would I need to break all the current (read only) client connections to get them to reconnect and have the ability to write? (am I confused about this?) I've set up corosync (part of linux-ha) to manage the VIP, but so far not to manage postgres itself. I've set up postgres to be managed manually (start and stop). Now that the master+slave configuration is up and running again, I'm looking for advice on how to monitor for faults: I can fail over manually, which is fine for now. What aspects of the postgres system should be monitored to watch for faults and what are the kinds of faults that should lead to a fail over? The machine crashing (OS/HW) is an obvious one, which will be recognized by corosync and I can script the initiation of failover (including using ipmi to power down the master). Thank you for your time. Kyle Burton -- Twitter: @kyleburton Blog: http://asymmetrical-view.com/ Fun: http://snapclean.me/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg 9.0, streaming replication, fail over and fail back strategies
On Mon, Aug 9, 2010 at 4:10 PM, Kyle R. Burton kyle.bur...@gmail.com wrote: Hello, After I had moved the VIP from the master to the slave, I had to restart (not just reload) the postgres daemon to get it to start Not surprising as you say. Is there any way to get PostgreSQL to bind to a new ip address and interface without actually shutting it down? If it could, would I need to break all the current (read only) client connections to get them to reconnect and have the ability to write? (am I confused about this?) I wonder if you could have pg on a steady ip and use iptables to forward traffic there after a failover... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] change owner strange behavior
In order to consolidate two users, user1 and user2, in the database I changed the owner of all tables and views that user2 owned to be owned by user1. I then revoked all remaining privileges from user2 and dropped the role. During this process I changed both table1 and view1 (that selects from table1) to be owned by user1. After this process, user1 cannot select from view1 with an error saying permission denied on table1. user1 can run the query in the definition of the view without a problem. Granting select on table1 to public fixes this problem. Running create or replace on the view with the same view definition fixes this problem. Any idea what could have caused this? It's almost as if user2 still existed in some settings for the view and was only cleared out by the create or replace statement. Thanks, David -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg 9.0, streaming replication, fail over and fail back strategies
Is there any way to get PostgreSQL to bind to a new ip address and interface without actually shutting it down? If it could, would I need to break all the current (read only) client connections to get them to reconnect and have the ability to write? (am I confused about this?) I wonder if you could have pg on a steady ip and use iptables to forward traffic there after a failover... That is an excellent suggestion! It just didn't occur to me. I've tried googling how to forward a port and am not having much success (rinetd worked, but I feel like I should be able to get iptables to work - do you have any pointers I could follow?) Thanks again, this will most likely sove my vip binding issue. Kyle -- Twitter: @kyleburton Blog: http://asymmetrical-view.com/ Fun: http://snapclean.me/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] change owner strange behavior
David Galkowski david.galkow...@gmail.com writes: In order to consolidate two users, user1 and user2, in the database I changed the owner of all tables and views that user2 owned to be owned by user1. I then revoked all remaining privileges from user2 and dropped the role. During this process I changed both table1 and view1 (that selects from table1) to be owned by user1. After this process, user1 cannot select from view1 with an error saying permission denied on table1. Works for me ... what PG version are you dealing with? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg 9.0, streaming replication, fail over and fail back strategies
On Mon, 2010-08-09 at 19:32 -0400, Kyle R. Burton wrote: That is an excellent suggestion! It just didn't occur to me. I've tried googling how to forward a port and am not having much success (rinetd worked, but I feel like I should be able to get iptables to work - do you have any pointers I could follow?) This is the link that I found: http://tldp.org/HOWTO/IP-Masquerade-HOWTO/forwarders.html Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg 9.0, streaming replication, fail over and fail back strategies
On Tue, Aug 10, 2010 at 7:10 AM, Kyle R. Burton kyle.bur...@gmail.com wrote: Is there any way to get PostgreSQL to bind to a new ip address and interface without actually shutting it down? If it could, would I need to break all the current (read only) client connections to get them to reconnect and have the ability to write? (am I confused about this?) What about setting listen_addresses to '*'? If so, you would be able to connect to new master as soon as VIP has been moved to it. Now that the master+slave configuration is up and running again, I'm looking for advice on how to monitor for faults: I can fail over manually, which is fine for now. What aspects of the postgres system should be monitored to watch for faults and what are the kinds of faults that should lead to a fail over? The machine crashing (OS/HW) is an obvious one, which will be recognized by corosync and I can script the initiation of failover (including using ipmi to power down the master). Probably the crash of the postgres and corosync process, the trouble of VIP, and network outage between the master and the client should be monitored, I think. Since any of them prevents the master from running queries from the client, we should cause a failover. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Win Server 2003: postgres can't logon
We would appreciate any help in resolving this problem so we can use the minimally privileged postgres account for logging in as the PostgreSQL service under Windows. On a Windows Server 2003 server SP2 (not part of a domain), we had a PostgreSQL 8.3.1 server that was running fine. The site's IT staff (who are unavailable to us) did some kind of a security sweep that broke things so that postgres could no longer logon when the service starts. (The same thing happened on two systems at the site; I have a third system at my site that did not get this sweep, and continues to work.) The PostgreSQL 8.3.1 Service is set to be started by logon as postgres (as configured by the MS installer). The postgres does have the Can login as a service privilege. The error we get is Event 553: Reason: User not allowed to logon at this computer http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=Windows+Operating+SystemProdVer=5.2EvtID=533EvtSrc=SecurityLCID=1033 with the indication that the attempted login was as a service. The authentication is listed as Negotiate. Login fails in the same way either for an automatic start at boot or for a manual start. If we make postgres a member of the Administrators group, the service can login and starts properly (but we don't want to keep it that way permanently). (This is not a password mismatch between service login account. If that were the problem, the error is different, and you still couldn't login when postgres is added to the Adminstrators group.) We did try the login as local system account, but that caused some other errors (lack of a SYSTEM role, IIRC) so we didn't go down that path. In our struggles, we tried this: (1) Remove old postgres user; create a new one (many things needed fixing here as the owner of directories was wrong) [We confirmed that the Log on as a service uses the new postgres, not the old one.] (2) Uninstall reinstall PostgreSQL. This helped fix the broken things from (1). It preserved the database for us apparently corrected ownership (Thanks!) (3) Try to give other user rights to the postgres user. Adding ones that seemed possibly relevant didn't work. (4) We did reboot often to avoid having any cached information or running processes cause problems.
Re: [GENERAL] Win Server 2003: postgres can't logon
On 10/08/10 08:23, Mabry Tyson wrote: The PostgreSQL 8.3.1 Service is set to be started by logon as postgres (as configured by the MS installer). The postgres does have the Can login as a service privilege. The error we get is Event 553: Reason: User not allowed to logon at this computer http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=Windows+Operating+SystemProdVer=5.2EvtID=533EvtSrc=SecurityLCID=1033 with the indication that the attempted login was as a service. The authentication is listed as Negotiate. That sounds like Group Policy restrictions. You're going to have to get your IT staff to deal with it, because if the server is an AD domain member you probably don't have access to the group policy settings, and even if you do they're hellishly complicated. If they only used local policy you might be able to fix it up. The tool you want to edit local policy is gpedit.msc (execute via Run dialog). For more detail on Group Policy editing, see: http://technet.microsoft.com/en-us/library/cc736591%28WS.10%29.aspx -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Win Server 2003: postgres can't logon
On Mon, Aug 9, 2010 at 6:23 PM, Mabry Tyson ty...@ai.sri.com wrote: We would appreciate any help in resolving this problem so we can use the minimally privileged postgres account for logging in as the PostgreSQL service under Windows. On a Windows Server 2003 server SP2 (not part of a domain), we had a PostgreSQL 8.3.1 server that was running fine. The site's IT staff (who are unavailable to us) did some kind of a security sweep that broke things so that postgres could no longer logon when the service starts. (The same thing happened on two systems at the site; I have a third system at my site that did not get this sweep, and continues to work.) That's a really vague description. Is it possible to get a better one from the people who worked on your machine? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgtune
On 09-Aug-2010 6:40 PM, tuanhoanganh wrote: What is the name of DW in --type=DW Sorry for my English. Tuan Hoang Anh DW = data warehouse. I don't think you have to apologize for your English. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
