Re: [GENERAL] database corruption
This thread is a top posting mess. I'll try to rearrange: Jeff Brenton wrote: REINDEX INDEX testrun_log_pkey; ERROR: could not write block 1832079 of temporary file: No space left on device HINT: Perhaps out of disk space? There is currently 14GB free on the disk that postgres is installed on. Does anyone know what I can do to get the db up and running again? [...] /dev/amrd2s1d663G596G 14G98%/db I guess the first question is, does the db have permissions(access) to all that space? There are no filesystem level content size restrictions that I am aware of on this system. The user pgsql should have full access to the filesystems indicated except for the root filesystem. Inodes? There are 9 miilion inodes free on /db. All other partitions have at least 1/2 million free. Assuming that this is ext3 on Linux, it could be space reserved for root. What do you get if you run the following as root: dumpe2fs /dev/amrd2s1d | grep 'Reserved block count' Yours, Laurenz Albe -- 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] database corruption
On Wed, 8 Apr 2009 22:14:38 -0400 Jeff Brenton jbren...@sandvine.com wrote: There are no filesystem level content size restrictions that I am aware of on this system. The user pgsql should have full access to the filesystems indicated except for the root filesystem. finished inodes? A lot of small files (even empty) somewhere? It happened to me when I was running a spider using curl and the spiders where not exiting properly when another process was killing them... -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres: Starting Server in background mode
Hi, To start the postgres server in background mode in windows, i executed the following command: *runas /user:postgres D:\postgres_new\pgsql\bin\pg_ctl.exe -W start -D D:\postgres_new\pgsql\data -l D:\postgres_new\pgsql\data\logfile* The postgres starts up all fine and logs get redirected.However, a command window gets opened which says server starting How do i disable this command window ? Thanks !
Re: [GENERAL] Postgres: Starting Server in background mode
Hi, I do not want start postgres as a service.Postgres will bundled along with my application and i am only looking at starting it only from cmd line.If there are any options to disable this cmd window which appears after executing the pg_ctl.exe start, that would be great ! Thanks. On Thu, Apr 9, 2009 at 3:52 PM, Craig Ringer cr...@postnewspapers.com.auwrote: CM J wrote: Hi, To start the postgres server in background mode in windows, i executed the following command: *runas /user:postgres D:\postgres_new\pgsql\bin\pg_ctl.exe -W start -D D:\postgres_new\pgsql\data -l D:\postgres_new\pgsql\data\logfile* The postgres starts up all fine and logs get redirected.However, a command window gets opened which says server starting How do i disable this command window ? Run Pg as a service, and use the net command or the services.msc snapin to start/stop the service. The GUI installer sets Pg up as a service by default. Why not use that? -- Craig Ringer
Re: [GENERAL] Are there performance advantages in storing bulky field in separate table?
Ivan Sergio Borgonovo wrote: On Wed, 8 Apr 2009 17:39:02 +0100 Sam Mason s...@samason.me.uk wrote: who num mails of total Tom Lane 1,9358.0% Scott Marlowe 1,0774.5% Alvaro Herrera 5212.2% Joshua Drake4681.9% Richard Huxton 4321.8% Craig Ringer3381.4% Ivan Sergio Borgonovo 3141.3% I just wrote privately to Tom that I'm ashamed I ask so much and answer so few. But well I'm an exception ;) I'm the top of non-contributors. Not so fast there citizen. I'll thank you to note that I've not contributed any code either, and for a significant number of years too :-) -- Richard Huxton Archonet Ltd -- 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] Postgres: Starting Server in background mode
On Thu, Apr 09, 2009 at 04:53:06PM +0530, CM J wrote: I do not want start postgres as a service.Postgres will bundled along with my application and i am only looking at starting it only from cmd line.If there are any options to disable this cmd window which appears after executing the pg_ctl.exe start, that would be great ! I think you can pass options to CreateProcess that will cause it to hide the window; not sure if this is what you want but I've not programmed under Windows for a long time so can't suggest more. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Will PostgreSQL 8.4 allow having different encoding databases on a same PostgreSQL server ?
Hello ! Currently, I have several PostgreSQL databases, some of them are using LATIN1 encoding, some of them are using UTF-8 encoding. In order to have theses two encoding, we had to install two PostgreSQL server on two different ports. One is for LATIN1 databases and one is for UTF-8 databases. (I known there is a workaround which allows to mix several databases encoding them on a same PostgreSQL server, by specifying C locale to initdb). I've heard some rumors on freenode stating that PostgreSQL 8.4. will allow to have several databases encoding. Did I understand right ? Thanks in advance for any informations about that feature ! Regards, -- Bruno Baguette - bruno.bague...@gmail.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] Will PostgreSQL 8.4 allow having different encoding databases on a same PostgreSQL server ?
Hi, Le jeudi 09 avril 2009 à 14:03:28, Bruno Baguette a écrit : [...] Currently, I have several PostgreSQL databases, some of them are using LATIN1 encoding, some of them are using UTF-8 encoding. In order to have theses two encoding, we had to install two PostgreSQL server on two different ports. One is for LATIN1 databases and one is for UTF-8 databases. (I known there is a workaround which allows to mix several databases encoding them on a same PostgreSQL server, by specifying C locale to initdb). You can do better already on 8.3. You create all your databases with UTF-8 and add client_encoding with LATIN1 for the ones that need this. A simple: ALTER DATABASE dbX SET client_encoding TO latin1; should work. I've heard some rumors on freenode stating that PostgreSQL 8.4. will allow to have several databases encoding. Did I understand right ? You'll have the possibility to specify lc_ctype and lc_collate. See http://developer.postgresql.org/pgdocs/postgres/sql-createdatabase.html for more informations. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger function cost
Hi Chaps, Can anyone point me to docs for trigger function estimated cost? I see that when I create a volatile plpgsql trigger function it gets given a cost of 100 and a c function gets given a cost of 1. Is there any reason to mess with this? Thanks Glyn -- 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] Are there performance advantages in storing bulky field in separate table?
On Wednesday 08 April 2009 18:25:25 Ron Mayer wrote: Robert Treat wrote: You can be sure that discussion of this topic in this forum will soon be visited by religious zealots, but the short answer is nulls are bad, mmkay. A slightly longer answer would be that, as a general rule, attributes of your relations that only apply to 1% of the rows are better represented as a one To fulfill your prophecy of zealotry, I've got a number of tables with columns that are mostly null that I can't think of that nice a way of refactoring. I'd love ideas to improve the design, though. One example's an address table. Most addresses have a few fields that are typically present (building number, city, state, etc). Others, as described in various government's address standards, are fields that are typically absent. For example in US addressing rules, the Urbanization Name line: http://www.usps.com/ncsc/addressstds/addressformats.htm MRS MARIA SUAREZ Name URB LAS GLADIOLAS Urbanization name 150 CALLE A House no. and st. name SAN JUAN PR 00926-3232City, state, and ZIP+4 Similarly sparse columns in my address tables are, titles, division/department Names and mailstop codes. (described here: http://pe.usps.gov/text/pub28/pub28c3_011.htm) While I realize I could stick in some string (empty string, or some other magic string like urbanization name doesn't apply to this address) into a table, it sure is convenient to put nulls in those columns. I'm quite curious what you'd suggest a well-designed address table would look like without nulls. The decision here would depend on your perticular sect of the anti-null religion, but you have a couple of choices: 1) Break these fields out into one or more tables, containing entries only for those address that have the additional information. Ideally you might be able to do something like extended_address_info where all of these fields could be kept, all of them being non-null. I suspect you can't do the ideal, so you'd end up with a bunch of tables. This would be used by the normalization trumps nullification sect 2) Given that all of these columns have an authoritarian source of what should be allowed, you could use the magic string approach without requiring too much magic, and these columns could even be a foriegn key into a table containing the authoritarian options. This could be justified by the all nulls are bad sect, but might also be used by a null using crowd who take a strict approach to nulls meaning unknown value, since here it isn't that the value is unknown; there isn't a valid value for these columns. (Adding the magic string to your FK table creates a valid reference value for those entries that would otherwise not match) Personally, if you force me into a well-designed address table *without* nulls decision, I would take this latter approach. HTH -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Will PostgreSQL 8.4 allow having different encoding databases on a same PostgreSQL server ?
On Thu, Apr 9, 2009 at 6:03 AM, Bruno Baguette bruno.bague...@gmail.com wrote: Hello ! Currently, I have several PostgreSQL databases, some of them are using LATIN1 encoding, some of them are using UTF-8 encoding. In order to have theses two encoding, we had to install two PostgreSQL server on two different ports. One is for LATIN1 databases and one is for UTF-8 databases. (I known there is a workaround which allows to mix several databases encoding them on a same PostgreSQL server, by specifying C locale to initdb). I think you are misinformed. With pgsql 8.3: smarlowe=# show lc_collate ; lc_collate - en_US.UTF-8 \l List of databases Name| Owner | Encoding ---+--+-- postgres | postgres | UTF8 smarlowe | smarlowe | UTF8 create database test with encoding 'SQL_ASCII'; \l List of databases Name| Owner | Encoding ---+--+--- postgres | postgres | UTF8 smarlowe | smarlowe | UTF8 test | smarlowe | SQL_ASCII -- 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] Are there performance advantages in storing bulky field in separate table?
If I may, I got an instance once, where table with bytea field was pretty slow. Turned out, that queries modified everything apart from bytea bit. moving it to separate table actually helped performance. But that only will happen providing that you have the bytea/text/whatever that won't change, once inserted. -- 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] Postgres: Starting Server in background mode
CM J wrote: Hi, To start the postgres server in background mode in windows, i executed the following command: *runas /user:postgres D:\postgres_new\pgsql\bin\pg_ctl.exe -W start -D D:\postgres_new\pgsql\data -l D:\postgres_new\pgsql\data\logfile* The postgres starts up all fine and logs get redirected.However, a command window gets opened which says server starting How do i disable this command window ? Run Pg as a service, and use the net command or the services.msc snapin to start/stop the service. The GUI installer sets Pg up as a service by default. Why not use that? -- 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] Will PostgreSQL 8.4 allow having different encoding databases on a same PostgreSQL server ?
Nevermind, I'm an idiot. yeah, for latin1 you'd need locale=C On Thu, Apr 9, 2009 at 6:24 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Apr 9, 2009 at 6:03 AM, Bruno Baguette bruno.bague...@gmail.com wrote: Hello ! Currently, I have several PostgreSQL databases, some of them are using LATIN1 encoding, some of them are using UTF-8 encoding. In order to have theses two encoding, we had to install two PostgreSQL server on two different ports. One is for LATIN1 databases and one is for UTF-8 databases. (I known there is a workaround which allows to mix several databases encoding them on a same PostgreSQL server, by specifying C locale to initdb). I think you are misinformed. With pgsql 8.3: smarlowe=# show lc_collate ; lc_collate - en_US.UTF-8 \l List of databases Name | Owner | Encoding ---+--+-- postgres | postgres | UTF8 smarlowe | smarlowe | UTF8 create database test with encoding 'SQL_ASCII'; \l List of databases Name | Owner | Encoding ---+--+--- postgres | postgres | UTF8 smarlowe | smarlowe | UTF8 test | smarlowe | SQL_ASCII -- When fascism comes to America, it will be the intolerant selling it as diversity. -- 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] Postgres: Starting Server in background mode
CM J, 09.04.2009 13:23: I do not want start postgres as a service.Postgres will bundled along with my application and i am only looking at starting it only from cmd line.If there are any options to disable this cmd window which appears after executing the pg_ctl.exe start, that would be great ! Thanks. If you are running this from a batch file, try start pg_ctl That might get rid of the window (you'll still have the initial Window that is shown when you run the batch file, but that is then closed) 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] Trigger function cost
Glyn Astill glynast...@yahoo.co.uk writes: I see that when I create a volatile plpgsql trigger function it gets given a cost of 100 and a c function gets given a cost of 1. Is there any reason to mess with this? No. The planner doesn't actually bother to figure the cost of triggers anyway, since presumably every correct plan will fire the same set of triggers. So even if you had a more accurate cost estimate than that one, it wouldn't get used for anything. Now, for ordinary non-trigger functions, it might be worth paying some attention to the cost estimate. 1 is intended to denote the cost of a reasonably simple C function, so PL functions should pretty much always have costs that are large multiples of that. 100 is a reasonable default, but if you know better you can put something else. 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] complicated query (newbie..)
Hello everyone, I've got 3 tables: hosts (with host.id column) and reservation (with reservation.id column) in many-to-many relation, and reservation_hosts which is an association table (with reservation_id and host_id columns). So I've got this query which selects hosts and reservations under certain conditions: SELECT * FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id INNER JOIN (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date = 2009-04-10 AND r.start_date 2009-04-09) OR (r.start_date = 2009-04-09 AND r.end_date = 2009-04-10) OR r.start_date 2009-04-09 )) GROUP BY rh.host_id) AS min_date(host_id, start_date) ON hosts.id = min_date.host_id AND reservation.start_date = min_date.start_date ORDER BY hosts.id, reservation.start_date Great. But I need to add to this table *hosts which have no reservations at all* as well. If I change INNER JOIN to LEFT OUTER JOIN, it adds the hosts I need, but it also lists every reservation, not just those from the subquery. I tried using another LEFT OUTER JOIN with additional query (which is some arbitrary host selection that will do for the moment) but it doesn't work: SELECT hosts.id AS hosts_id, hosts.ip AS hosts_ip, hosts.hostname AS hosts_hostname, hosts.location AS hosts_location, hosts.architecture_id AS hosts_architecture_id, hosts.os_kind_id AS hosts_os_kind_id, hosts.os_version_id AS hosts_os_version_id, hosts.additional_info AS hosts_additional_info, hosts.column_12 AS hosts_column_12, hosts.column_13 AS hosts_column_13, hosts.username AS hosts_username, hosts.password AS hosts_password, hosts.alias AS hosts_alias, hosts.virtualization_id AS hosts_virtualization_id, hosts.shareable AS hosts_shareable, hosts.shareable_between_projects AS hosts_shareable_between_projects, hosts.notes AS hosts_notes, hosts.cpu AS hosts_cpu, hosts.ram AS hosts_ram, hosts.column_24 AS hosts_column_24, hosts.batch AS hosts_batch, hosts.asset AS hosts_asset, hosts.owner AS hosts_owner, hosts.ssh_key_present AS hosts_ssh_key_present, hosts.machine_type_model AS hosts_machine_type_model, hosts.mac_address_eth_0 AS hosts_mac_address_eth_0, hosts.physical_box AS hosts_physical_box, hosts.up_n_running AS hosts_up_n_running, hosts.available AS hosts_available, hosts.project_id AS hosts_project_id, reservation.id AS reservation_id, reservation.start_date AS reservation_start_date, reservation.end_date AS reservation_end_date, reservation.status AS reservation_status, reservation.businessneed AS reservation_businessneed, reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id AS reservation_email_id, reservation.project_id AS reservation_project_id FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id LEFT OUTER JOIN (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date = 2009-04-10 AND r.start_date 2009-04-09) OR (r.start_date = 2009-04-09 AND r.end_date = 2009-04-10) OR r.start_date 2009-04-09 )) GROUP BY rh.host_id) AS min_date(host_id, start_date) ON hosts.id = min_date.host_id AND reservation.start_date = min_date.start_date LEFT OUTER JOIN (SELECT hosts.id FROM hosts WHERE hosts.id IN (10, 11, 12)) AS nullresv(host_id) ON hosts.id = nullresv.host_id ORDER BY hosts.id, reservation.start_date Regards, mk -- 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] complicated query (newbie..)
Hi, I've got 3 tables: hosts (with host.id column) and reservation (with reservation.id column) in many-to-many relation, and reservation_hosts which is an association table (with reservation_id and host_id columns). So I've got this query which selects hosts and reservations under certain conditions: SELECT * FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id INNER JOIN (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date = 2009-04-10 AND r.start_date 2009-04-09) OR (r.start_date = 2009-04-09 AND r.end_date = 2009-04-10) OR r.start_date 2009-04-09 )) GROUP BY rh.host_id) AS min_date(host_id, start_date) ON hosts.id = min_date.host_id AND reservation.start_date = min_date.start_date ORDER BY hosts.id, reservation.start_date Great. But I need to add to this table *hosts which have no reservations at all* as well. If I change INNER JOIN to LEFT OUTER JOIN, it adds the hosts I need, but it also lists every reservation, not just those from the subquery. Do you need a MIN(start_date) for each host you get from the query before last join? I think you can solve this with sub-select like this: select hosts.*, reservation_hosts.*, reservation.*, (select MIN(r.start_date) FROM reservation AS r INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id where rh.host_id = hosts.id and /*date condition here*/) FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id ORDER BY hosts.id, reservation.start_date Note: sub-select must return exactly one row! -- Aurimas -- 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] Will PostgreSQL 8.4 allow having different encoding databases on a same PostgreSQL server ?
Bruno Baguette wrote: Currently, I have several PostgreSQL databases, some of them are using LATIN1 encoding, some of them are using UTF-8 encoding. In order to have theses two encoding, we had to install two PostgreSQL server on two different ports. One is for LATIN1 databases and one is for UTF-8 databases. (I known there is a workaround which allows to mix several databases encoding them on a same PostgreSQL server, by specifying C locale to initdb). I've heard some rumors on freenode stating that PostgreSQL 8.4. will allow to have several databases encoding. Did I understand right ? You can already do that, just use CREATE DATABASE dbname ENCODING=anyencoding Yours, Laurenz Albe -- 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] Postgres: Starting Server in background mode
CM J wrote: Hi, I do not want start postgres as a service.Postgres will bundled along with my application and i am only looking at starting it only from cmd line.If there are any options to disable this cmd window which appears after executing the pg_ctl.exe start, that would be great ! The problem here is, Postgres is not an embedded database but really a database management system. Therefore trying to bundle it with a desktop application will usually cause more headaches. I'd suggest installing Postgres as central service (as you know with web servers, application servers, mail servers... ) and connect your clients to it or use a desktop/linkable database for example firebird or whatever. Regards Tino -- 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] complicated query (newbie..)
Hello Aurimas, Thanks for answer! Do you need a MIN(start_date) for each host you get from the query before last join? Yes, I really do - the idea is that from several reservations fulfilling the dates condition the earliest reservation has to be selected (i.e. the one with minimum start date). I edited your code slightly to allow for changed column names and missing 'hosts' table in the subquery (there were syntax errors otherwise): select hosts.*, reservation_hosts.*, reservation.*, (select MIN(r.start_date) FROM hosts, reservation AS r INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id where rh.host_id = hosts.id ) FROM hosts LEFT OUTER JOIN reservation_hosts ON hosts.id = reservation_hosts.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts.reservation_id ORDER BY hosts.id, reservation.start_date But it still doesn't work, i.e. it produces every host/reservation combination (on top of listing hosts with no reservations and NULL in place of reservation_id, which is fine). I checked that subquery does indeed return exactly one row, although I'm not sure why this has meaning. Regards, mk -- 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] complicated query (newbie..)
On Thu, Apr 09, 2009 at 04:47:32PM +0200, Marcin Krol wrote: I've got 3 tables: hosts (with host.id column) and reservation (with reservation.id column) in many-to-many relation, and reservation_hosts which is an association table (with reservation_id and host_id columns). So I've got this query which selects hosts and reservations under certain conditions: If you could describe what you want in words it would help more. I think you want something like I was a list of all hosts and their first reservation that doesn't cover some specific date. If that's correct; you've got a couple of choices, either turn the inner join into an outer join and move it up to join onto the hosts, or get rid of it completely and use the DISTINCT ON clause. -- Sam http://samason.me.uk/ -- 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] complicated query (newbie..)
Sam Mason wrote: On Thu, Apr 09, 2009 at 04:47:32PM +0200, Marcin Krol wrote: I've got 3 tables: hosts (with host.id column) and reservation (with reservation.id column) in many-to-many relation, and reservation_hosts which is an association table (with reservation_id and host_id columns). So I've got this query which selects hosts and reservations under certain conditions: If you could describe what you want in words it would help more. I think you want something like I was a list of all hosts and their first reservation that doesn't cover some specific date. It's somewhat complicated: What I'm trying to accomplish is producing list of hosts available within a specified timeframe. What I have is a table of hosts, table of reservations (containing id, start_date and end_date) and an association table reservation_hosts. I need a list of hosts, with accompanying reservations fulfilling certain (date-related) conditions. But there are two twists: - if host has reservation(s), but those do not fulfill the date conditions (the host is not available within a specified timeframe), the host obviously should NOT be listed - if host has no reservations at all, it obviously is available, so it should be listed If that's correct; you've got a couple of choices, either turn the inner join into an outer join and move it up to join onto the hosts, or get rid of it completely and use the DISTINCT ON clause. I'll try doing smth with it.. Regards, mk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ON condition in LEFT OUTER JOIN doesn't work?!
Hello everyone, I've got this query: SELECT hosts.id, MIN(reservation.start_date) FROM hosts LEFT OUTER JOIN reservation_hosts ON reservation_hosts.host_id = hosts.id LEFT OUTER JOIN reservation ON (reservation_hosts.reservation_id = reservation.id AND reservation.start_date 2009-04-09) GROUP BY hosts.id ORDER BY hosts.id It selects the hosts with reservation.start_date = 2009-04-09 ! Regards, mk -- 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] complicated query (newbie..)
On Thu, Apr 09, 2009 at 06:08:04PM +0200, Marcin Krol wrote:
What I'm trying to accomplish is producing list of hosts available
within a specified timeframe.
What I have is a table of hosts, table of reservations (containing id,
start_date and end_date) and an association table reservation_hosts.
I need a list of hosts, with accompanying reservations fulfilling
certain (date-related) conditions.
But there are two twists:
- if host has reservation(s), but those do not fulfill the date
conditions (the host is not available within a specified timeframe), the
host obviously should NOT be listed
- if host has no reservations at all, it obviously is available, so it
should be listed
I think the following should do what you want.
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS (${window_start},${window_end})
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS
(${requested_start},${requested_end})
ORDER BY h.id, r.start_date)
The formatting is somewhat grim, but I think it should do what you want.
--
Sam http://samason.me.uk/
--
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] ON condition in LEFT OUTER JOIN doesn't work?!
On Thu, Apr 09, 2009 at 06:34:27PM +0200, Marcin Krol wrote: SELECT hosts.id, MIN(reservation.start_date) FROM hosts LEFT OUTER JOIN reservation_hosts ON reservation_hosts.host_id = hosts.id LEFT OUTER JOIN reservation ON (reservation_hosts.reservation_id = reservation.id AND reservation.start_date 2009-04-09) GROUP BY hosts.id ORDER BY hosts.id It selects the hosts with reservation.start_date = 2009-04-09 ! You are putting quotes in there, and not comparing to the number 1996 ((2009 - 4) - 9), aren't you? Old versions of PG would let this sort of thing through I think, but newer versions will give an error. -- Sam http://samason.me.uk/ -- 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] Trigger function cost
From: Tom Lane t...@sss.pgh.pa.us Is there any reason to mess with this? No. The planner doesn't actually bother to figure the cost of triggers anyway, since presumably every correct plan will fire the same set of triggers. So even if you had a more accurate cost estimate than that one, it wouldn't get used for anything. Excellent, that's good with me. Now, for ordinary non-trigger functions, it might be worth paying some attention to the cost estimate. 1 is intended to denote the cost of a reasonably simple C function, so PL functions should pretty much always have costs that are large multiples of that. 100 is a reasonable default, but if you know better you can put something else. Cool, I'll leave it alone for now then, interesting stuff, thanks Tom. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Storing HTML: HTML entities being rendered in that raw form
Hi all, I have stored HTML in a text field that I subsequently render on the web. However when I retrieve and render this data on the web I am getting the entities being rendered in their raw form, ie, instead of getting the '' symbol when 'amp;' is stored I'm getting the 'raw' 'amp;'. I would be grateful if anyone can point out how I can get around this. Best -- 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] ON condition in LEFT OUTER JOIN doesn't work?!
On 09/04/2009 17:34, Marcin Krol wrote: reservation.start_date 2009-04-09) You need to phrase it like this: ... reservation.start_date '2009-04-09'::date ... Try this and see what happens. :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Storing HTML: HTML entities being rendered in that raw form
On 09/04/2009 17:27, linnewbie wrote: I have stored HTML in a text field that I subsequently render on the web. However when I retrieve and render this data on the web I am getting the entities being rendered in their raw form, ie, instead of getting the '' symbol when 'amp;' is stored I'm getting the 'raw' 'amp;'. Can you show us how you're storing the HTML? If you're sending the ampersands into the database as 'amp;', then that's what's going to get stored, and so that's what you're going to get back. Postgres doesn't change 'amp;' - '', you'll need to do that yourself before sending it to the database. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Some suggestions for the non Linux installers
Hi, I have been actively recommending Postgres in my company but had now three people coming back to me because they couldn't manage to install Postgres on Windows or MacOS. The common sympton is always that the installer (the PG installer as well as the EnterpriseDB installers) suggests to place the datadir into the same directory as the Postgres binaries (the default for Windows is c:\Program Files\Postgres\8.3\data). Now when doing this on Windows this is *bound* to fail because the Program Files are usually not writeable for non-admin users. The directory is created during installation by the user running the installation (which is usually an admin user). The PG service runs under a regular user account and thus fails to write to the data directory. The sympton is that the service simply fails, but you can't see any errors because PG can't write to the log file as well. I have never used a Mac but from the description of two of my co-workers it seems that under MacOS the same is happening. I would suggest that the installers (PGinstaller _and_ EnterpriseDB) are changed so that the default for the data directory is /outside/ the Postgres installation path. If it's hard to find a good default for the datadir I suggest to leave it completely empty and actively ask the user to specify a directory and give a big hint that this directory must be writeable for the postgres user account. Additionally when creating the data dir from within the installer, it should set the permissions of that directory to make sure the postgres user can really write to it. Most of the people that asked me for help, could distinguish between the installer and the base product and they didn't judge the book by its cover, but there were some comments like Installing MySQL is a lot easier (well I know that it is easier due to lack of security concerns in MySQL, but that doesn't matter in that discussion) After all the installer is the first impression a new user gets, and if it's too complicated he might not even take a second look. The installer(s) should also ask some question for the default configuration so that pg_hba.conf and postgres.conf can be pre-configured to e.g. accept connections from other computers. A simple checkbox during the installation wizard would be enough for this, which would then adjust listen_address in postgresql.conf and the host entries in pg_hba.conf Just to give you guys some impression on how things show up for not-so-advanced users: http://www.dbforums.com/postgresql/1640752-installation-wont-work-postgresql.html So I think with just a little tweaking, the installation experience for those trying out Postgres could be made a lot better. 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] complicated query (newbie..)
Hello Sam,
Thanks a million for reply! I'm so frustrated with this..
Sam Mason wrote:
On Thu, Apr 09, 2009 at 06:08:04PM +0200, Marcin Krol wrote:
What I'm trying to accomplish is producing list of hosts available
within a specified timeframe.
What I have is a table of hosts, table of reservations (containing id,
start_date and end_date) and an association table reservation_hosts.
I need a list of hosts, with accompanying reservations fulfilling
certain (date-related) conditions.
But there are two twists:
- if host has reservation(s), but those do not fulfill the date
conditions (the host is not available within a specified timeframe), the
host obviously should NOT be listed
- if host has no reservations at all, it obviously is available, so it
should be listed
I think the following should do what you want.
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS (${window_start},${window_end})
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS
(${requested_start},${requested_end})
ORDER BY h.id, r.start_date)
The formatting is somewhat grim, but I think it should do what you want.
Well it almost works: I see that it selects out the host ids whose date
conditions are not met (while adding those that have no reservations),
but why it produces nothing but NULLs in place of values, even for hosts
who do have reservations but ones ?!
id id start_date end_date
4
NULLNULLNULL
5
NULLNULLNULL
6
NULLNULLNULL
7
NULLNULLNULL
8
NULLNULLNULL
9
NULLNULLNULL
10
NULLNULLNULL
11
NULLNULLNULL
12
NULLNULLNULL
13
NULLNULLNULL
I had to edit it a bit: it seems there was one parentheses missing after
first subquery:
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id AND r.start_date 2009-04-09)
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND r.start_date 2009-04-09
ORDER BY h.id, r.start_date)
Two things:
- If I quote date values like '2009-04-09' it doesn't work again! I.e.
result set includes one host id that should have been excluded (bc it
has reservation whose date doesn't match the condition)
- I have replaced OVERLAPS with explicit date condition bc PG complained:
ERROR: function pg_catalog.overlaps(date, date, integer, integer) does
not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
--
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] Storing HTML: HTML entities being rendered in that raw form
On Apr 9, 2009, at 9:27 AM, linnewbie wrote: Hi all, I have stored HTML in a text field that I subsequently render on the web. However when I retrieve and render this data on the web I am getting the entities being rendered in their raw form, ie, instead of getting the '' symbol when 'amp;' is stored I'm getting the 'raw' 'amp;'. I would be grateful if anyone can point out how I can get around this. It's a problem in your code, not the database. You're probably escaping it one time to many or unescaping it one time too few. Cheers, Steve -- 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] complicated query (newbie..)
On 09/04/2009 18:03, Marcin Krol wrote: - If I quote date values like '2009-04-09' it doesn't work again! I.e. result set includes one host id that should have been excluded (bc it has reservation whose date doesn't match the condition) - I have replaced OVERLAPS with explicit date condition bc PG complained: ERROR: function pg_catalog.overlaps(date, date, integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. I'd imagine this is the same problem as in your other post - if you don't quote the dates, PG thinks each is an integer expression. The error says it's looking for an OVERLAPS function that takes two dates and two integers, which of course doesn't exist. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] complicated query (newbie..)
On Thu, Apr 09, 2009 at 07:03:55PM +0200, Marcin Krol wrote: - If I quote date values like '2009-04-09' it doesn't work again! I.e. result set includes one host id that should have been excluded (bc it has reservation whose date doesn't match the condition) You *need* those quotes in there; you need to figure out what's going on there first before going any further. Maybe the reservation dates for that entry are confused for some reason, or you've got the date in the wrong format or something (i.e. you're expecting dd/mm/ and you're getting mmm/dd/ or something else). - I have replaced OVERLAPS with explicit date condition bc PG complained: ERROR: function pg_catalog.overlaps(date, date, integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. This is a big hint that things are going wrong. You need those quotes in there, an integer is a plain number and not a date. -- Sam http://samason.me.uk/ -- 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] complicated query (newbie..)
Sam Mason wrote:
This is a big hint that things are going wrong. You need those quotes
in there, an integer is a plain number and not a date.
This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date))
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)
Regards,
mk
--
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] complicated query (newbie..)
could provide greater assistance if you could post the database schema you're using cheers (from across the pond) Martin GMT+5(this week) __ Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Thu, 9 Apr 2009 18:08:04 +0200 From: mrk...@gmail.com To: s...@samason.me.uk CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] complicated query (newbie..) Sam Mason wrote: On Thu, Apr 09, 2009 at 04:47:32PM +0200, Marcin Krol wrote: I've got 3 tables: hosts (with host.id column) and reservation (with reservation.id column) in many-to-many relation, and reservation_hosts which is an association table (with reservation_id and host_id columns). So I've got this query which selects hosts and reservations under certain conditions: If you could describe what you want in words it would help more. I think you want something like I was a list of all hosts and their first reservation that doesn't cover some specific date. It's somewhat complicated: What I'm trying to accomplish is producing list of hosts available within a specified timeframe. What I have is a table of hosts, table of reservations (containing id, start_date and end_date) and an association table reservation_hosts. I need a list of hosts, with accompanying reservations fulfilling certain (date-related) conditions. But there are two twists: - if host has reservation(s), but those do not fulfill the date conditions (the host is not available within a specified timeframe), the host obviously should NOT be listed - if host has no reservations at all, it obviously is available, so it should be listed If that's correct; you've got a couple of choices, either turn the inner join into an outer join and move it up to join onto the hosts, or get rid of it completely and use the DISTINCT ON clause. I'll try doing smth with it.. Regards, mk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Rediscover Hotmail®: Get e-mail storage that grows with you. http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009
Re: [GENERAL] complicated query (newbie..)
On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:
Sam Mason wrote:
This is a big hint that things are going wrong. You need those quotes
in there, an integer is a plain number and not a date.
This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!
Yes, it would do.
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date))
The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)
these dates are OK.
As a minor point, you shouldn't need to put the ::date in unless
you're feeling pedantic, PG should figure that out for itself. I put
them in if I'm unsure of what's going on but most of my queries won't
have them in.
--
Sam http://samason.me.uk/
--
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] complicated query (newbie..)
Martin Gainty wrote:
could provide greater assistance if you could post the database schema
you're using
Not sure what you mean by schema (I'm really new to DB world), if you
mean table descriptions from psql, here it is:
reservations=# \d hosts
Table public.hosts
Column | Type|
Modifiers
+---+
id | integer | not null default
nextval('hosts_id_seq'::regclass)
ip | character varying |
hostname | character varying |
location | character varying |
architecture_id| integer |
os_kind_id | integer |
os_version_id | integer |
additional_info| character varying |
column_12 | character varying |
column_13 | character varying |
username | character varying |
password | character varying |
alias | character varying |
virtualization_id | integer |
shareable | boolean |
shareable_between_projects | boolean |
notes | character varying |
cpu| character varying |
ram| character varying |
column_24 | character varying |
batch | character varying |
asset | character varying |
owner | character varying |
ssh_key_present| character varying |
machine_type_model | character varying |
mac_address_eth_0 | character varying |
physical_box | boolean |
up_n_running | boolean |
available | boolean |
project_id | integer |
Indexes:
hosts_pkey PRIMARY KEY, btree (id)
Foreign-key constraints:
hosts_architecture_id_fkey FOREIGN KEY (architecture_id)
REFERENCES architecture(id)
hosts_os_kind_id_fkey FOREIGN KEY (os_kind_id) REFERENCES os_kind(id)
hosts_os_version_id_fkey FOREIGN KEY (os_version_id) REFERENCES
os_version(id)
hosts_project_id_fkey FOREIGN KEY (project_id) REFERENCES project(id)
hosts_virtualization_id_fkey FOREIGN KEY (virtualization_id)
REFERENCES virtualization(id)
reservations=#
reservations=# \d reservation
Table public.reservation
Column| Type|Modifiers
--+---+--
id | integer | not null default
nextval('reservation_id_seq'::regclass)
start_date | date |
end_date | date |
status | character varying |
businessneed | character varying |
notetohwrep | character varying |
email_id | integer |
project_id | integer |
Indexes:
reservation_pkey PRIMARY KEY, btree (id)
Foreign-key constraints:
reservation_email_id_fkey FOREIGN KEY (email_id) REFERENCES email(id)
reservation_project_id_fkey FOREIGN KEY (project_id) REFERENCES
project(id)
reservations=#
reservations=# \d reservation_hosts
Table public.reservation_hosts
Column | Type | Modifiers
+-+---
reservation_id | integer |
host_id| integer |
Foreign-key constraints:
reservation_hosts_host_id_fkey FOREIGN KEY (host_id) REFERENCES
hosts(id)
reservation_hosts_reservation_id_fkey FOREIGN KEY
(reservation_id) REFERENCES reservation(id)
cheers (from across the pond)
Martin
GMT+5(this week)
__
Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
Date: Thu, 9 Apr 2009 18:08:04 +0200
From: mrk...@gmail.com
To: s...@samason.me.uk
Re: [GENERAL] Some suggestions for the non Linux installers
On Thu, Apr 9, 2009 at 6:02 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Hi, I have been actively recommending Postgres in my company but had now three people coming back to me because they couldn't manage to install Postgres on Windows or MacOS. The common sympton is always that the installer (the PG installer as well as the EnterpriseDB installers) suggests to place the datadir into the same directory as the Postgres binaries (the default for Windows is c:\Program Files\Postgres\8.3\data). Now when doing this on Windows this is *bound* to fail because the Program Files are usually not writeable for non-admin users. The directory is created during installation by the user running the installation (which is usually an admin user). The PG service runs under a regular user account and thus fails to write to the data directory. The sympton is that the service simply fails, but you can't see any errors because PG can't write to the log file as well. It's not bound to fail because part of the installation process is to grant the require permissions on whatever directory is chosen for the data. In the case of the one-click installers, this is tested extensively before every release on a range of clean and dirty virtual machines including XP, Vista, 2K3 and 2K8, and on the Mac on Leopard, Tiger and Leopard Server. Given that, and the thousands of downloads per week we get without problems being reported, I suspect if you are seeing multiple failures then it is likely a side effect of a local security policy or similar. If you can supply an installation log that would help diagnose the problem. I have never used a Mac but from the description of two of my co-workers it seems that under MacOS the same is happening. Unlikely to be the same as the installation process is quite different. I would suggest that the installers (PGinstaller _and_ EnterpriseDB) are changed so that the default for the data directory is /outside/ the Postgres installation path. If it's hard to find a good default for the datadir I suggest to leave it completely empty and actively ask the user to specify a directory and give a big hint that this directory must be writeable for the postgres user account. In the majority of installations the postgres account is created by the installer so it's not feasible to grant the permissions in advance. Additionally when creating the data dir from within the installer, it should set the permissions of that directory to make sure the postgres user can really write to it. As I said above, they all do. I can point you at the code if you're interested. After all the installer is the first impression a new user gets, and if it's too complicated he might not even take a second look. The installer(s) should also ask some question for the default configuration so that pg_hba.conf and postgres.conf can be pre-configured to e.g. accept connections from other computers. A simple checkbox during the installation wizard would be enough for this, which would then adjust listen_address in postgresql.conf and the host entries in pg_hba.conf Well, we intentionally don't do that in the one-click installers for precisely the reason you give above! pgInstaller on Windows does have an option to enable listenaddresses = * though - we recommend that for advanced users that want the extra flexibility. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] Some suggestions for the non Linux installers
Now when doing this on Windows this is *bound* to fail because the Program Files are usually not writeable for non-admin users. The directory is created during installation by the user running the installation (which is usually an admin user). The PG service runs under a regular user account and thus fails to write to the data directory. The sympton is that the service simply fails, but you can't see any errors because PG can't write to the log file as well. It's not bound to fail because part of the installation process is to grant the require permissions on whatever directory is chosen for the data. In the case of the one-click installers, this is tested extensively before every release on a range of clean and dirty virtual machines including XP, Vista, 2K3 and 2K8, and on the Mac on Leopard, Tiger and Leopard Server. OK, that's good news, but then those failures need to have a different source. But interesting enough the problems were all caused by wrong permissions (just look at the thread I pointed to in dbforums). If you can supply an installation log that would help diagnose the problem. I'll ask them, but I doubt I can get hold of them. Where would the log be stored? If it's hard to find a good default for the datadir I suggest to leave it completely empty and actively ask the user to specify a directory and give a big hint that this directory must be writeable for the postgres user account. In the majority of installations the postgres account is created by the installer so it's not feasible to grant the permissions in advance. Yes, but the data dir is created *after* the acount is created, correct? But still: I think it is a much better strategy to *not* put the data dir into the program directory. Additionally when creating the data dir from within the installer, it should set the permissions of that directory to make sure the postgres user can really write to it. As I said above, they all do. I can point you at the code if you're interested. I believe you :) The installer(s) should also ask some question for the default configuration so that pg_hba.conf and postgres.conf can be pre-configured to e.g. accept connections from other computers. A simple checkbox during the installation wizard would be enough for this, which would then adjust listen_address in postgresql.conf and the host entries in pg_hba.conf Well, we intentionally don't do that in the one-click installers for precisely the reason you give above! You mean for security reasons? Hmm. But isn't that essential to a DBMS to be able to be contacted from the outside? Thanks for the feedback. I finally see some people understanding the advantages Postgres has over other choices, but these situations surely weren't ideal to promote Postgres which is a bit sad.. Cheers 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] complicated query (newbie..)
Sam Mason wrote:
On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:
Sam Mason wrote:
This is a big hint that things are going wrong. You need those quotes
in there, an integer is a plain number and not a date.
This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!
Yes, it would do.
Well it does for selecting hosts, but I also want to select the nearest
reservation using r.id like you specified in 'SELECT h.id, r.id,
r.start_date, r.end_date'. I can't do this if r.id is NULL.
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date))
The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.
Not backwards, but forward into some reasonable range, like 3 months (I
want the user to see the nearby reservation in future).
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)
these dates are OK.
As a minor point, you shouldn't need to put the ::date in unless
you're feeling pedantic, PG should figure that out for itself. I put
them in if I'm unsure of what's going on but most of my queries won't
have them in.
Oops! My PG (ver 8.1) does need this ::date suffix!
Regards,
mk
--
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] Some suggestions for the non Linux installers
On Thu, Apr 9, 2009 at 6:59 PM, Thomas Kellerer spam_ea...@gmx.net wrote: OK, that's good news, but then those failures need to have a different source. But interesting enough the problems were all caused by wrong permissions (just look at the thread I pointed to in dbforums). Probably because on Windows they filesystem permissions have a million and one ways to be configured. If you can supply an installation log that would help diagnose the problem. I'll ask them, but I doubt I can get hold of them. Where would the log be stored? /tmp on Mac, %TEMP% for the installing user on windows. Well, we intentionally don't do that in the one-click installers for precisely the reason you give above! You mean for security reasons? No, I mean for simplicity. Hmm. But isn't that essential to a DBMS to be able to be contacted from the outside? Not at all. Developer workstations, and even fairly busy websites might be confined to a single machine. I would wager that such installations might account for 50% of our installations. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Minimizing Recovery Time (wal replication)
I have two hot-spare databases that use wal archiving and continuous recovery mode. I want to minimize recovery time when we have to fail over to one of our hot spares. Right now, I'm seeing the following behavior which makes a quick recovery seem problematic: (1) hot spare applies 70 to 75 wal files (~1.1g) in 2 to 3 min period (2) hot spare pauses for 15 to 20 minutes, during this period pdflush consumes 99% IO (iotop). Dirty (from /proc/meminfo) spikes to ~760mb, remains at that level for the first 10 minutes, and then slowly ticks down to 0 for the second 10 minutes. (3) goto 1 My concern is that if the database has been in recovery mode for some time, even if it's caught up, if I go live sometime in (1) I can face a recovery time of upwards of 20 minutes. We've experienced delays during fail over in the past (not 20 minutes, but long enough to make me second guess what we are doing). I want to better understand what is going on so that I can determine what I can do (if anything) to minimize down time when we fail over to one of our hot spares. Here are my current settings: postgres (v8.3.7): shared_buffers = 2GB (15GB total) effective_cache_size = 12GB (15GB total) checkpoint_segments = 10 checkpoint_completion_target = 0.7 (other checkpoint/bgwriter settings left at default values) sysctl: kernel.shmmax = 2684354560 vm.dirty_background_ratio = 1 vm.dirty_ratio = 5 Thanks, Bryan -- 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] Some suggestions for the non Linux installers
On Thu, Apr 9, 2009 at 7:59 AM, Thomas Kellerer spam_ea...@gmx.net wrote: But still: I think it is a much better strategy to *not* put the data dir into the program directory. Microsoft abandoned that model after W95/98/(ME?). A new model came out in Win2000, but rarely followed. MS got out the big ruler and whacked peoples knuckles in Vista. That's why Vista is giving everyone fits. %ALLUSERSPROFILE% or %APPDATA% gives the proper starting locations for storing data. Unfortunately it seems like most Windows developers didn't get the memo, so it's not a unique issue to pgsql. -- 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] Some suggestions for the non Linux installers
On Thu, 9 Apr 2009, Dave Page wrote: I suspect if you are seeing multiple failures then it is likely a side effect of a local security policy or similar. If you can supply an installation log that would help diagnose the problem. If you look at the forum post Thomas referenced, the errors all look like variations on this during initdb: creating directory C:/Archivos de programa/PostgreSQL/8.3/data ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers/max_fsm_pages ... 400kB/2 creating configuration files ... ok creating template1 database in C:/Archivos de programa/PostgreSQL/8.3/data/base/1 ... child process exited with exit code 1 I'm not sure whether the install log will give you any additional detail beyond that, or if this is an installer issue at all. Looks to me like initdb is presuming the subdirectories it creates inside of PGDATA will inherit the permissions of the parent directory, but that isn't the case on this particular Windows system. I know it's possible to play with inheritance in ways that would give unexpected behavior (for those used to the UNIX model) on NTFS; see Inherited vs. Explicit Permissions at http://www.windowsecurity.com/articles/Understanding-Windows-NTFS-Permissions.html for background. While whether supporting odd configurations like that is deemed worthwhile or not, I think improving on only getting child process exited in this situation would help a lot of people out. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Some suggestions for the non Linux installers
On Thu, Apr 9, 2009 at 8:37 PM, Greg Smith gsm...@gregsmith.com wrote: On Thu, 9 Apr 2009, Dave Page wrote: I suspect if you are seeing multiple failures then it is likely a side effect of a local security policy or similar. If you can supply an installation log that would help diagnose the problem. If you look at the forum post Thomas referenced, the errors all look like variations on this during initdb: I'm not interested in that case, but Thomas'. The forum post concerned some *very* different installer designs all of which gave the same initdb errors from what I can see, indicating an initdb issue. I'm interested in Thomas' case because he has multiple machines showing the same symptoms and I'm in contact with him so may be able to get more information and actually have a chance of figuring out what's going on. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] Some suggestions for the non Linux installers
Greg Smith gsm...@gregsmith.com writes: If you look at the forum post Thomas referenced, the errors all look like variations on this during initdb: creating directory C:/Archivos de programa/PostgreSQL/8.3/data ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers/max_fsm_pages ... 400kB/2 creating configuration files ... ok creating template1 database in C:/Archivos de programa/PostgreSQL/8.3/data/base/1 ... child process exited with exit code 1 I'm not sure whether the install log will give you any additional detail beyond that, or if this is an installer issue at all. Looks to me like initdb is presuming the subdirectories it creates inside of PGDATA will inherit the permissions of the parent directory, but that isn't the case on this particular Windows system. I think we've seen failures just like that in the buildfarm, so it's not by any means an issue only for the installer. Have we determined why nothing useful in the way of an error message gets printed? 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] Some suggestions for the non Linux installers
On Thu, Apr 9, 2009 at 8:34 PM, Russell Hltn russellh...@gmail.com wrote: On Thu, Apr 9, 2009 at 7:59 AM, Thomas Kellerer spam_ea...@gmx.net wrote: But still: I think it is a much better strategy to *not* put the data dir into the program directory. Microsoft abandoned that model after W95/98/(ME?). A new model came out in Win2000, but rarely followed. MS got out the big ruler and whacked peoples knuckles in Vista. That's why Vista is giving everyone fits. %ALLUSERSPROFILE% or %APPDATA% gives the proper starting locations for storing data. Unfortunately it seems like most Windows developers didn't get the memo, so it's not a unique issue to pgsql. There are potential problems with storing data in such locations - they may be on network shares, or be roaming directories for example. It also makes it difficult for other users to edit the config files if they're tucked away in the service accounts profile directory. I imagine it is for similar reasons that Microsoft SQL Server also defaults to storing its data under the installation directory. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] complicated query (newbie..)
Hi, Do you need a MIN(start_date) for each host you get from the query before last join? Yes, I really do - the idea is that from several reservations fulfilling the dates condition the earliest reservation has to be selected (i.e. the one with minimum start date). I edited your code slightly to allow for changed column names and missing 'hosts' table in the subquery (there were syntax errors otherwise): The hosts table was not missing in the subquery! It meant to take host-id from current row: of main select. That subquery should work alone only by replacing host.id by constant value. select hosts.*, reservation_hosts.*, reservation.*, (select MIN(r.start_date) FROM hosts, reservation AS r INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id where rh.host_id = hosts.id ) FROM hosts LEFT OUTER JOIN reservation_hosts ON hosts.id = reservation_hosts.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts.reservation_id ORDER BY hosts.id, reservation.start_date But it still doesn't work, i.e. it produces every host/reservation combination (on top of listing hosts with no reservations and NULL in place of reservation_id, which is fine). Check the main select without the subquery. Does it return the rows you want? If not - its wrong! If yes, than choose *any* host id from main select's result and write a query, that would return a min(start_date) for *that* host. That query should not need hosts table at all since you have a constant host id. Now just place the second query as subquery into the first one, replacing a constant host id by hosts.id. It should work. I checked that subquery does indeed return exactly one row, although I'm not sure why this has meaning. I was a bit wrong. Subquery must return 0 or 1 row, but NOT MORE. I hope it's clear now. -- Aurimas -- 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] complicated query (newbie..)
On Thu, Apr 09, 2009 at 08:16:55PM +0200, Marcin Krol wrote:
Sam Mason wrote:
On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date))
The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.
Not backwards, but forward into some reasonable range, like 3 months (I
want the user to see the nearby reservation in future).
I'm not sure if this is a question or something else. If you're
wondering how to do this just use a range of ('2009-01-01'::date,
'2009-12-31'::date) to show all entries for this year.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Evidently no support for the mmddyyyy date format
Today I tried every permutation of the DateStyle parameter I could find, and still cannot get PostgreSQL 8.3 to accept dates in the format mmdd. I tried setting this in the postgresql.conf file, and also in psql using set datestyle to style. So, am I correct to conclude that it is not possible to configure to accept dates on the mmdd format? I know I can probably use the to_date function, but this would involve changing a multitude of queries, which is what I'm trying to avoid. -Thanks -- 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] Evidently no support for the mmddyyyy date format
Bernard Barton bf...@comcast.net writes: Today I tried every permutation of the DateStyle parameter I could find, and still cannot get PostgreSQL 8.3 to accept dates in the format mmdd. I tried setting this in the postgresql.conf file, and also in psql using set datestyle to style. So, am I correct to conclude that it is not possible to configure to accept dates on the mmdd format? If you mean eightdigitswithoutanypunctuation, I think that's correct. 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] Minimizing Recovery Time (wal replication)
On Thu, 9 Apr 2009, Bryan Murphy wrote: (1) hot spare applies 70 to 75 wal files (~1.1g) in 2 to 3 min period Yeah, if you ever let this many files queue up you're facing a long recovery time. You really need to get into a position where you're applying WAL files regularly enough that you don't ever fall this far behind. (2) hot spare pauses for 15 to 20 minutes, during this period pdflush consumes 99% IO (iotop). Dirty (from /proc/meminfo) spikes to ~760mb, remains at that level for the first 10 minutes, and then slowly ticks down to 0 for the second 10 minutes. What does vmstat say about the bi/bo during this time period? It sounds like the volume of random I/O produced by recovery is just backing up as expected. Some quick math: 15GB RAM * 5% dirty_ratio = 750MB ; there's where your measured 760MB bottleneck is coming from. 750MB / 10 minutes = 1.25MB/s ; that's in the normal range for random writes with a single disk Therefore my bet is that vmstat 1 will show bo~=1250 the whole time you're waiting there, with matching figures from the iostat to the database disk during that period. Basically your options here are: 1) Decrease the maximum possible segment backlog so you can never get this far behind 2) Increase the rate at which random I/O can be flushed to disk by either a) Improving things with a [better] battery-backed controller disk cache b) Stripe across more disks -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Minimizing Recovery Time (wal replication)
On Thu, Apr 9, 2009 at 6:38 PM, Greg Smith gsm...@gregsmith.com wrote: What does vmstat say about the bi/bo during this time period? It sounds like the volume of random I/O produced by recovery is just backing up as expected. Some quick math: I'll have to capture this, unfortunately I won't be able to do that until tomorrow. This machine I was looking at is already failed over and I'm currently creating a new snapshot. I won't have a new hot spare to replace it until the morning. 15GB RAM * 5% dirty_ratio = 750MB ; there's where your measured 760MB bottleneck is coming from. That was what I thought, good to have it confirmed by somebody else. 750MB / 10 minutes = 1.25MB/s ; that's in the normal range for random writes with a single disk Yes, this is an interesting problem I'm having, more on it below... Therefore my bet is that vmstat 1 will show bo~=1250 the whole time you're waiting there, with matching figures from the iostat to the database disk during that period. Basically your options here are: 1) Decrease the maximum possible segment backlog so you can never get this far behind I understand conceptually what you are saying, but I don't know how to practically realize this. :) Do you mean lower checkpoint_segments? 2) Increase the rate at which random I/O can be flushed to disk by either a) Improving things with a [better] battery-backed controller disk cache b) Stripe across more disks This is the problem that has been my nightmare for the past few months. It actually is an 8 drive raid 10, BUT, it's on virtualized infrastructure up in Amazon's cloud running on 8 EBS volumes. I've found performance to be... inconsistent at best. Sometimes it's great, sometimes it's not so great. We have a legacy database (~120gb) which grew in our old data center on very powerful hardware. We moved it up to Amazon's cloud a few months ago, and have been scrambling ever since. I wouldn't change what we're doing, the benefits so far have outweighed the pain, and we're actively working on the software to make better use of the cloud infrastructure (i.e. many smaller databases instead of one big database, lots of caching, the usual stuff). Unfortunately, that takes time and I'm trying to limp along as best I can with the legacy database until we can get everything migrated. So, to recap, I've raided up the volumes, thrown as much RAM and CPU at the process as is available and just can't seem to tease any more performance out. Thanks, Bryan -- 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] Minimizing Recovery Time (wal replication)
On Thu, 9 Apr 2009, Bryan Murphy wrote: 1) Decrease the maximum possible segment backlog so you can never get this far behind I understand conceptually what you are saying, but I don't know how to practically realize this. :) Do you mean lower checkpoint_segments? Theoretically, every time the archive_command writes a new segment out you can immediately move that to your standby, and setup the standby to regularly look for those and apply them as they come in. The fact that you're getting so many of them queued up suggests there's something in that path that isn't moving that pipeline along aggressively enough, without knowing more about what you're doing it's hard to say where that is. It actually is an 8 drive raid 10, BUT, it's on virtualized infrastructure up in Amazon's cloud running on 8 EBS volumes. I've found performance to be... inconsistent at best. Yeah, EBS is not exactly a high-performance or predictable database storage solution, particularly when you get to where you're calling fsync a lot--which is exactly what is happening during the period you note your system is frozen. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Storing HTML: HTML entities being rendered in that raw form
On Apr 9, 1:00 pm, st...@blighty.com (Steve Atkins) wrote: On Apr 9, 2009, at 9:27 AM, linnewbie wrote: Hi all, I have stored HTML in a text field that I subsequently render on the web. However when I retrieve and render this data on the web I am getting the entities being rendered in their raw form, ie, instead of getting the '' symbol when 'amp;' is stored I'm getting the 'raw' 'amp;'. I would be grateful if anyone can point out how I can get around this. It's a problem in your code, not the database. You're probably escaping it one time to many or unescaping it one time too few. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general To clarify, I am not escaping the string in any way. Say the page I am saving the database is the about us page form a company website. First, make a from to create the about us page in a text area field, then I copy the html from my text editor and past it ino this text area from. I then have a cgi script which takes the contents from the text area field and stores it in the database. What I have on disk would be: .. p Bonnie amp; Clyde/p which would usually be rendered as: Bonnie Clype but this is not happening, it's being rendered as: Bonnie amp; Clyde -- 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] Minimizing Recovery Time (wal replication)
On Thu, Apr 9, 2009 at 7:33 PM, Greg Smith gsm...@gregsmith.com wrote: 1) Decrease the maximum possible segment backlog so you can never get this far behind I understand conceptually what you are saying, but I don't know how to practically realize this. :) Do you mean lower checkpoint_segments? Theoretically, every time the archive_command writes a new segment out you can immediately move that to your standby, and setup the standby to regularly look for those and apply them as they come in. The fact that you're getting so many of them queued up suggests there's something in that path that isn't moving that pipeline along aggressively enough, without knowing more about what you're doing it's hard to say where that is. This is our archiving command: #!/bin/bash echo archiving $2.bz2 bzip2 -k -9 -c $1 /srv/pg_logs/archive/$2.bz2.tmp || exit $? mv /srv/pg_logs/archive/$2.bz2.tmp /srv/pg_logs/archive/$2.bz2 || exit $? scp /srv/pg_logs/archive/$2.bz2 w.x.y.z:/srv/logs/$2.bz2.tmp || exit $? ssh w.x.y.z mv /srv/logs/$2.bz2.tmp /srv/logs/$2.bz2 || exit $? rm /srv/pg_logs/archive/$2.bz2 || exit $? And this is our restoring command: #!/bin/bash if [ $1 == ] || [ $2 == ]; then echo dbrestore [source] [destination] exit 1 fi echo `date`: restoring $1 while true do if [ -f $1.bz2 ]; then echo `date`: restore $1.bz2 - $2 bunzip2 -d -c $1.bz2 $2.tmp mv $2.tmp $2 exit 0 fi if [[ $1 =~ .history ]]; then echo `date`: skipping $1 exit 1 fi if [ -f /tmp/golive ]; then echo `date`: going live rm -f /tmp/golive exit 2 fi sleep 5s done Essentially, what we do is bzip2 the file, scp it to the backup server, and then ssh rename it. The bzip2 is legacy from when we were uploading to Amazon via the public internet and can go away now. The rename can happen in the restore script, and is something I probably should change anyway, one less thing for the master database to do. We create file system snapshots of the hot spares, and I periodically purge the old log files after I've verified that we can bring the most recent snapshot live. We've used NFS in the past, but we're currently investigating other distribution alternatives (primarily londiste and pgpool2). We've used slony in the past, but find it introduces too much administrative overhead and is too brittle for our tastes. Thanks again! Bryan -- 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] Postgres: Starting Server in background mode
Tino Wildenhain wrote: The problem here is, Postgres is not an embedded database but really a database management system. Therefore trying to bundle it with a desktop application will usually cause more headaches. I'd suggest installing Postgres as central service Note that Windows is designed to allow applications to create services, start them, stop them, etc. You should have *NO* problems having your application install PostgreSQL as a service, and start/stop it on demand. You can do this through the command line (net.exe), the Services snap-in (services.msc), or via Win32 API calls from your application. Doing anything else is trying to re-invent the Windows service mechanism - poorly - and is really just NOT a good idea. -- 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] Anyone testing changes to libpq/bcc32.mak?
br...@momjian.us wrote: Magnus Hagander wrote: I don't know that anybody does. We usually get a report a couple of minor versions in and fix it then, which backs that guess. It's also not tested by the buildfarm. So I think you can call it semi- maintained at best. So if you want to become the maintainer and test/send patches at an earlier stage, please go right ahead! Yep, send us the changes and we will blindly apply them. ;-) I'll post a patch that restores libpq building with BCC. But, to repeat from a thread back in Jan 2007, I only test the resulting blibpq.dll through pgtcl-ng and its test suite. It works for that, but may not work for anything else. At one point I tried to also build psql with BCC; it built but didn't work, and I see you removed bin/psql/bcc32.mak now. So I think it's best to continue to call libpq/bcc32.mak semi-maintained at best. -- 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] Postgres: Starting Server in background mode
On Thu, Apr 9, 2009 at 8:18 PM, Craig Ringer cr...@postnewspapers.com.au wrote: Tino Wildenhain wrote: The problem here is, Postgres is not an embedded database but really a database management system. Therefore trying to bundle it with a desktop application will usually cause more headaches. I'd suggest installing Postgres as central service Note that Windows is designed to allow applications to create services, start them, stop them, etc. You should have *NO* problems having your application install PostgreSQL as a service, and start/stop it on demand. You can do this through the command line (net.exe), the Services snap-in (services.msc), or via Win32 API calls from your application. Note that if one is going to do this, it's probably a good idea to install your private pgsql into a different default directory and have it answer on a different port than the 5432 one, so that if the user has or will install their own pgsql version your customer version won't get in the way. -- 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] indirect membership in group roles
On Apr 2, 6:48 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: Kev kevinjamesfi...@gmail.com writes: For some reason, which I couldn't see spelled out very well in the docs for GRANT ROLE and SET ROLE,indirectmembership in the group user doesn't give one its privileges unless you SET ROLE user first, even if all roles involved have INHERIT set. Really? Works for me: regression=# create group student inherit; CREATE ROLE regression=# create group employee inherit; CREATE ROLE regression=# create group user; CREATE ROLE regression=# grant user to student; GRANT ROLE regression=# grant user to employee; GRANT ROLE regression=# create user joe inherit; CREATE ROLE regression=# grant student to joe; GRANT ROLE regression=# create table mytable (f1 int); CREATE TABLE regression=# grant select on mytable to user; GRANT regression=# \c - joe psql (8.4devel) You are now connected to database regression as user joe. regression= select * from mytable; f1 (0 rows) I suspect you forgot to attach the inherit property to the intermediate-level group. regards, tom lane That's interesting... This is what I'm showing in pgAdmin3: CREATE ROLE employee NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; ALTER ROLE employee SET search_path=public; GRANT user TO employee; CREATE ROLE user NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; ...you know, I wonder if it's only within the context of how I'm connecting, which is to connect as a superuser and then SET SESSION AUTHORIZATION to the selected user. Sorry, I should've mentioned that. Although, now it seems to be working. That makes my head hurt, because I have logs full of this: DBD::Pg::db selectrow_array failed: ERROR: permission denied for relation my_table ...and I remember going through and testing and reading up on it until I figured out the SET ROLE thing. Gosh. Well, sorry to waste your time, I have no idea how all this was possible. I guess I'll log my testing a lot more verbosely next time. Thanks for humouring me. Kev -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
