Re: [GENERAL] Last modification time of a database?
Erik Jones wrote: These are all client databases at the web hosting company I work at. I can't go putting triggers on all of their tables. I think I'll just start taking snapshots of pertinent data from pg_stat_activity and after I've been collecting data for a while run a report of dbs that haven't seen connections in X long since what I'm really after is inactive databases. Did you think about just setting log_connections to ON and grep'ing the server logs? Otherwise it looks like a use-case for ON CONNECT triggers, unfortunately we don't have them yet. The TODO list links that message: http://archives.postgresql.org/pgsql-hackers/2008-03/msg00451.php Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.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] Database shut down unexpectedly.
Today the database shut down unexpectedly. I have included the log file that shows the shutdown. Can anybody tell me why this happened and how I can make sure it doesn't happen again. The only thing I can think of that I did was to specify a password for the postgres user in the operating system. Here is the log file. Very strange. 2009-03-25 00:02:01 GMT LOG: incomplete startup packet 2009-03-25 00:30:05 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:30:05 GMT LOG: unexpected EOF on client connection 2009-03-25 00:30:05 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:30:05 GMT LOG: unexpected EOF on client connection 2009-03-25 00:32:15 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:32:15 GMT LOG: unexpected EOF on client connection 2009-03-25 02:41:57 GMT LOG: incomplete startup packet 2009-03-25 02:41:57 GMT LOG: received smart shutdown request 2009-03-25 02:41:57 GMT LOG: autovacuum launcher shutting down 2009-03-25 02:45:03 GMT FATAL: the database system is shutting down 2009-03-25 02:45:03 GMT FATAL: the database system is shutting down 2009-03-25 02:50:02 GMT FATAL: the database system is shutting down 2009-03-25 02:50:02 GMT FATAL: the database system is shutting down 2009-03-25 02:55:03 GMT FATAL: the database system is shutting down 2009-03-25 02:55:03 GMT FATAL: the database system is shutting down 2009-03-25 03:00:03 GMT FATAL: the database system is shutting down 2009-03-25 03:00:03 GMT FATAL: the database system is shutting down 2009-03-25 03:05:03 GMT FATAL: the database system is shutting down 2009-03-25 03:05:03 GMT FATAL: the database system is shutting down 2009-03-25 03:10:02 GMT FATAL: the database system is shutting down 2009-03-25 03:10:02 GMT FATAL: the database system is shutting down 2009-03-25 03:15:03 GMT FATAL: the database system is shutting down 2009-03-25 03:15:03 GMT FATAL: the database system is shutting down 2009-03-25 03:20:03 GMT FATAL: the database system is shutting down 2009-03-25 03:20:03 GMT FATAL: the database system is shutting down 2009-03-25 03:25:02 GMT FATAL: the database system is shutting down 2009-03-25 03:25:02 GMT FATAL: the database system is shutting down 2009-03-25 03:30:03 GMT FATAL: the database system is shutting down 2009-03-25 03:30:03 GMT FATAL: the database system is shutting down 2009-03-25 03:35:03 GMT FATAL: the database system is shutting down 2009-03-25 03:35:03 GMT FATAL: the database system is shutting down 2009-03-25 03:40:02 GMT FATAL: the database system is shutting down 2009-03-25 03:40:02 GMT FATAL: the database system is shutting down 2009-03-25 03:45:02 GMT FATAL: the database system is shutting down 2009-03-25 03:45:02 GMT FATAL: the database system is shutting down 2009-03-25 03:50:03 GMT FATAL: the database system is shutting down 2009-03-25 03:50:03 GMT FATAL: the database system is shutting down 2009-03-25 03:55:03 GMT FATAL: the database system is shutting down 2009-03-25 03:55:03 GMT FATAL: the database system is shutting down 2009-03-25 04:00:02 GMT FATAL: the database system is shutting down 2009-03-25 04:00:02 GMT FATAL: the database system is shutting down 2009-03-25 04:25:03 GMT FATAL: the database system is shutting down 2009-03-25 04:30:03 GMT FATAL: the database system is shutting down 2009-03-25 04:30:03 GMT FATAL: the database system is shutting down 2009-03-25 04:35:02 GMT FATAL: the database system is shutting down 2009-03-25 04:35:02 GMT FATAL: the database system is shutting down 2009-03-25 04:40:03 GMT FATAL: the database system is shutting down 2009-03-25 04:40:03 GMT FATAL: the database system is shutting down 2009-03-25 04:45:03 GMT FATAL: the database system is shutting down 2009-03-25 04:45:03 GMT FATAL: the database system is shutting down 2009-03-25 04:50:02 GMT FATAL: the database system is shutting down 2009-03-25 04:50:02 GMT FATAL: the database system is shutting down 2009-03-25 04:55:03 GMT FATAL: the database system is shutting down 2009-03-25 04:55:03 GMT FATAL: the database system is shutting down 2009-03-25 05:00:03 GMT FATAL: the database system is shutting down 2009-03-25 05:00:03 GMT FATAL: the database system is shutting down 2009-03-25 05:02:01 GMT LOG: incomplete startup packet 2009-03-25 05:05:02 GMT FATAL: the database system is shutting down 2009-03-25 05:05:02 GMT FATAL: the database system is shutting down 2009-03-25 05:10:02 GMT FATAL: the database system is shutting down 2009-03-25 05:10:02 GMT FATAL: the database system is shutting down 2009-03-25 05:15:03 GMT FATAL: the database system is shutting down 2009-03-25 05:15:03 GMT FATAL: the database system is shutting down 2009-03-25 05:20:03 GMT FATAL: the database system is shutting down 2009-03-25 05:20:03 GMT FATAL: the database system is shutting down 2009-03-25 05:25:02 GMT FATAL: the database system is shutting down 2009-03-25 05:25:02 GMT FATAL: the
Re: [GENERAL] Maximum transaction rate
Hi, Martijn van Oosterhout wrote: And fsync better do what you're asking (how fast is just a performance issue, just as long as it's done). Where are we on this issue? I've read all of this thread and the one on the lvm-linux mailing list as well, but still don't feel confident. In the following scenario: fsync - filesystem - physical disk I'm assuming the filesystem correctly issues an blkdev_issue_flush() on the physical disk upon fsync(), to do what it's told: flush the cache(s) to disk. Further, I'm also assuming the physical disk is flushable (i.e. it correctly implements the blkdev_issue_flush() call). Here we can be pretty certain that fsync works as advertised, I think. The unanswered question to me is, what's happening, if I add LVM in between as follows: fsync - filesystmem - device mapper (lvm) - physical disk(s) Again, assume the filesystem issues a blkdev_issue_flush() to the lower layer and the physical disks are all flushable (and implement that correctly). How does the device mapper behave? I'd expect it to forward the blkdev_issue_flush() call to all affected devices and only return after the last one has confirmed and completed flushing its caches. Is that the case? I've also read about the newish write barriers and about filesystems implementing fsync with such write barriers. That seems fishy to me and would of course break in combination with LVM (which doesn't completely support write barriers, AFAIU). However, that's clearly the filesystem side of the story and has not much to do with whether fsync lies on top of LVM or not. Help in clarifying this issue greatly appreciated. Kind Regards Markus Wanner -- 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 Windows XP Pro SP3 asn PostgreSQL 8.3.5
Hello everybody. In the last week i'm having a trouble with my PostgreSQL working on Windows XP Professional. When i boot, Postgresql works correctcly, but after about an hour it stops working, an i get timeouts when i try to connect to it. In the postgresql log, i get this message: 2009-03-25 10:52:57 CET FATAL: could not duplicate socket 1276 for use in backend: error code 10022 After this, if i go to Event Viewer, i get this warning: PostgreSQL Database Server 8.3 Service stopped. In task manager, i get two postgres.exe process running, the first one of 104 Kb, and the second about 5000 kb. If i try to start postgresql service again, I got these messages: Waiting for server startup... LOG loaded library $libdir/plugins/plugin_debugger.dll LOG could not bind IPv4 socket: No error HINT is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets The service appears as starting, but it does not start. If i kill the two postgresql porcessess the one of 5000kb stops correctly, but i get access denied for killing the one of 104 Kb. If i try to start postgresql service again, i can't, because it stills starting, and i can't do anithing to it. The only way is reboot the machine, but after about an hour, the same history. Before i detected this trouble last week, i had never got any problem any idea? King Regards -- 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] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5
you have a power saving mode enabled? of inactivity when your pc shuts down etc? King Regards -Ursprüngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Tk421 Gesendet: Mittwoch, 25. März 2009 11:32 An: pgsql-general@postgresql.org Betreff: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5 Hello everybody. In the last week i'm having a trouble with my PostgreSQL working on Windows XP Professional. When i boot, Postgresql works correctcly, but after about an hour it stops working, an i get timeouts when i try to connect to it. In the postgresql log, i get this message: 2009-03-25 10:52:57 CET FATAL: could not duplicate socket 1276 for use in backend: error code 10022 After this, if i go to Event Viewer, i get this warning: PostgreSQL Database Server 8.3 Service stopped. In task manager, i get two postgres.exe process running, the first one of 104 Kb, and the second about 5000 kb. If i try to start postgresql service again, I got these messages: Waiting for server startup... LOG loaded library $libdir/plugins/plugin_debugger.dll LOG could not bind IPv4 socket: No error HINT is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets The service appears as starting, but it does not start. If i kill the two postgresql porcessess the one of 5000kb stops correctly, but i get access denied for killing the one of 104 Kb. If i try to start postgresql service again, i can't, because it stills starting, and i can't do anithing to it. The only way is reboot the machine, but after about an hour, the same history. Before i detected this trouble last week, i had never got any problem any idea? King Regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5
Power saving mode is disabled, and i get the error even until i'm working. Best regards Benedikt Schackenberg escribió: you have a power saving mode enabled? of inactivity when your pc shuts down etc? King Regards -Ursprüngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Tk421 Gesendet: Mittwoch, 25. März 2009 11:32 An: pgsql-general@postgresql.org Betreff: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5 Hello everybody. In the last week i'm having a trouble with my PostgreSQL working on Windows XP Professional. When i boot, Postgresql works correctcly, but after about an hour it stops working, an i get timeouts when i try to connect to it. In the postgresql log, i get this message: 2009-03-25 10:52:57 CET FATAL: could not duplicate socket 1276 for use in backend: error code 10022 After this, if i go to Event Viewer, i get this warning: PostgreSQL Database Server 8.3 Service stopped. In task manager, i get two postgres.exe process running, the first one of 104 Kb, and the second about 5000 kb. If i try to start postgresql service again, I got these messages: Waiting for server startup... LOG loaded library $libdir/plugins/plugin_debugger.dll LOG could not bind IPv4 socket: No error HINT is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets The service appears as starting, but it does not start. If i kill the two postgresql porcessess the one of 5000kb stops correctly, but i get access denied for killing the one of 104 Kb. If i try to start postgresql service again, i can't, because it stills starting, and i can't do anithing to it. The only way is reboot the machine, but after about an hour, the same history. Before i detected this trouble last week, i had never got any problem any idea? King Regards -- 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] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5
In my ethernet connection I've got only one IP address, and i've tried with Static IP and with DHCP, and the error Stills. Ii've got two aditional network adapters active (created by vmware for virtualizations). May this be the cause of the error? I will try to set postgres to listen only at one IP, and test if error stills. Regards Benedikt Schackenberg escribi: how many IP addresses your computer? you will get your ip address via dhcp? it sounds to me in such a way as if the postgreSQL service is no longer an ip address can bind -Ursprngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Tk421 Gesendet: Mittwoch, 25. Mrz 2009 12:25 Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5 Power saving mode is disabled, and i get the error even until i'm working. Best regards Benedikt Schackenberg escribi: you have a power saving mode enabled? of inactivity when your pc shuts down etc? King Regards -Ursprngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Tk421 Gesendet: Mittwoch, 25. Mrz 2009 11:32 An: pgsql-general@postgresql.org Betreff: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5 Hello everybody. In the last week i'm having a trouble with my PostgreSQL working on Windows XP Professional. When i boot, Postgresql works correctcly, but after about an hour it stops working, an i get timeouts when i try to connect to it. In the postgresql log, i get this message: 2009-03-25 10:52:57 CET FATAL: could not duplicate socket 1276 for use in backend: error code 10022 After this, if i go to Event Viewer, i get this warning: PostgreSQL Database Server 8.3 Service stopped. In task manager, i get two postgres.exe process running, the first one of 104 Kb, and the second about 5000 kb. If i try to start postgresql service again, I got these messages: Waiting for server startup... LOG loaded library "$libdir/plugins/plugin_debugger.dll" LOG could not bind IPv4 socket: No error HINT is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for "*" FATAL: could not create any TCP/IP sockets The service appears as starting, but it does not start. If i kill the two postgresql porcessess the one of 5000kb stops correctly, but i get access denied for killing the one of 104 Kb. If i try to start postgresql service again, i can't, because it stills starting, and i can't do anithing to it. The only way is reboot the machine, but after about an hour, the same history. Before i detected this trouble last week, i had never got any problem any idea? King Regards
Re: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5
I've configured postgreSQL server to listen only in one IP address and the error stills. Anyone has more ideas to solve it? Best regards Benedikt Schackenberg escribió: you have a power saving mode enabled? of inactivity when your pc shuts down etc? King Regards -Ursprüngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Tk421 Gesendet: Mittwoch, 25. März 2009 11:32 An: pgsql-general@postgresql.org Betreff: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5 Hello everybody. In the last week i'm having a trouble with my PostgreSQL working on Windows XP Professional. When i boot, Postgresql works correctcly, but after about an hour it stops working, an i get timeouts when i try to connect to it. In the postgresql log, i get this message: 2009-03-25 10:52:57 CET FATAL: could not duplicate socket 1276 for use in backend: error code 10022 After this, if i go to Event Viewer, i get this warning: PostgreSQL Database Server 8.3 Service stopped. In task manager, i get two postgres.exe process running, the first one of 104 Kb, and the second about 5000 kb. If i try to start postgresql service again, I got these messages: Waiting for server startup... LOG loaded library $libdir/plugins/plugin_debugger.dll LOG could not bind IPv4 socket: No error HINT is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets The service appears as starting, but it does not start. If i kill the two postgresql porcessess the one of 5000kb stops correctly, but i get access denied for killing the one of 104 Kb. If i try to start postgresql service again, i can't, because it stills starting, and i can't do anithing to it. The only way is reboot the machine, but after about an hour, the same history. Before i detected this trouble last week, i had never got any problem any idea? King Regards -- 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 shut down unexpectedly.
In response to Tim Uckun timuc...@gmail.com: Today the database shut down unexpectedly. I have included the log file that shows the shutdown. Can anybody tell me why this happened and how I can make sure it doesn't happen again. The only thing I can think of that I did was to specify a password for the postgres user in the operating system. Not likely to cause the DB to restart ... at least not in any OS configuration that I'm aware of. However, you don't mention what OS you're running ... that might be important. Here is the log file. Very strange. 2009-03-25 00:02:01 GMT LOG: incomplete startup packet 2009-03-25 00:30:05 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:30:05 GMT LOG: unexpected EOF on client connection 2009-03-25 00:30:05 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:30:05 GMT LOG: unexpected EOF on client connection 2009-03-25 00:32:15 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:32:15 GMT LOG: unexpected EOF on client connection 2009-03-25 02:41:57 GMT LOG: incomplete startup packet 2009-03-25 02:41:57 GMT LOG: received smart shutdown request ^^^ Sure looks like someone intentionally shut the database down. Who has login access to this system with enough privs to do that? I'd start by questioning them on their activities at that time. If that doesn't answer your question, then implement OS auditing so you can catch the culprit next time. It's quite possible that someone is sending signals to the DB system without knowledge of how those signals are interpreted. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5
On Wednesday 25 March 2009 5:58:33 am Tk421 wrote: I've configured postgreSQL server to listen only in one IP address and the error stills. Anyone has more ideas to solve it? In the past week have you installed any other software or changed the behavior of software? In particular I am thinking of Anti-Virus software. The reason I ask is that as far as I can tell the error comes from the section of postmaster.c that has the following comment: /* * Duplicate a socket for usage in a child process, and write the resulting * structure to the parameter file. * This is required because a number of LSPs (Layered Service Providers) very * common on Windows (antivirus, firewalls, download managers etc) break * straight socket inheritance. */ This would seem to point another process interfering with the Postgres. Best regards Benedikt Schackenberg escribió: you have a power saving mode enabled? of inactivity when your pc shuts down etc? King Regards -Ursprüngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Tk421 Gesendet: Mittwoch, 25. März 2009 11:32 An: pgsql-general@postgresql.org Betreff: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5 Hello everybody. In the last week i'm having a trouble with my PostgreSQL working on Windows XP Professional. When i boot, Postgresql works correctcly, but after about an hour it stops working, an i get timeouts when i try to connect to it. In the postgresql log, i get this message: 2009-03-25 10:52:57 CET FATAL: could not duplicate socket 1276 for use in backend: error code 10022 After this, if i go to Event Viewer, i get this warning: PostgreSQL Database Server 8.3 Service stopped. In task manager, i get two postgres.exe process running, the first one of 104 Kb, and the second about 5000 kb. If i try to start postgresql service again, I got these messages: Waiting for server startup... LOG loaded library $libdir/plugins/plugin_debugger.dll LOG could not bind IPv4 socket: No error HINT is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets The service appears as starting, but it does not start. If i kill the two postgresql porcessess the one of 5000kb stops correctly, but i get access denied for killing the one of 104 Kb. If i try to start postgresql service again, i can't, because it stills starting, and i can't do anithing to it. The only way is reboot the machine, but after about an hour, the same history. Before i detected this trouble last week, i had never got any problem any idea? King Regards -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] MAX(ROW(...)) - feature request
Hello. Since release 8.2 row comparisons work well - thanks! It would be nice if MIN and MAX aggregate functions could operate on row values, so I could write in SQL: SELECT MIN(ROW(a, b, c, d)) FROM table to find extreme value of ordered set of columns. It should not be hard to implement, please. I'm looking forward to release 8.4 which will introduce window functions. -- 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] Single missing WAL in long sequence..
On 25/03/2009 02:21, berdam wrote: how to unsubscribe of this list?? Instructions are at the bottom of every post sent out via this mailing list. 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] text column constraint, newbie question
Daniel Verite wrote: Note that htmlentities() expects LATIN1-encoded strings and is thus unusable on UTF-8 contents. So if you end up talking UTF-8 with the database, you'll probably need to use htmlspecialchars() instead, and UTF-8 as your HTML charset. I believe you are wrong, at least the PHP documentation says otherwise and it _seems_ to work for me (http://us2.php.net/htmlentities). Maybe you are thinking about an older version? Also the iconv() function can help you convert between (some) different character encodings (http://us2.php.net/htmlentities). -- Stephen -- 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] MAX(ROW(...)) - feature request
least() greatest(). -- 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] Proper entry of polygon type data
Hi Brent, I am aware of PostGIS and already use it. My question was regarding the entry format of PostgreSQL polygon data. There is a void in the PostgreSQL documentation regarding this. Incidentally, PostGIS uses PostgreSQL polygon, point, and path data types. Using PostGIS for simple , non-geographic, polygon rules is a bit like using a tank to kill a mosquito. Peter Brent Wood wrote: Hi Peter, If you want to use Postgres to store/manage/query spatial data, I strongly recommend you look at PostGIS, not the native Postgres geometry types. Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Peter Willis pet...@borstad.com 03/24/09 10:35 AM Hello, I would like to use 'polygon' type data and am wondering about the entry format of the vertex coordinates. Are the coordinates of the polygon type to be entered one entry per polygon vertex, or one entry per polygon edge segment? For example: I have a triangle with vertex corners A, B, C. One entry per vertex format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy)) ); One entry per edge format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Bx,By),(Cx,Cy),(Cx,Cy),(Ax,Ay)) ); Which entry format is the correct one? If per vertex format is the correct one, do I need to 'close' the path by entering the first vertex again at the end of the list? ie: INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy),(Ax,Ay)) ); Thanks, Peter -- 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] Proper entry of polygon type data
On Mon, Mar 23, 2009 at 2:13 PM, Peter Willis pet...@borstad.com wrote: For example: I have a triangle with vertex corners A, B, C. One entry per vertex format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy)) ); One entry per edge format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Bx,By),(Cx,Cy),(Cx,Cy),(Ax,Ay)) ); Which entry format is the correct one? If per vertex format is the correct one, do I need to 'close' the path by entering the first vertex again at the end of the list? the documentation (http://www.postgresql.org/docs/8.3/interactive/datatype-geometric.html#AEN5582) shows that you are inserting a sequence of vertexes. Also, it is stated that the polygon is closed, which implies that you don't need to close it yourself. merlin -- 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] intermittant performance problem
Mike Charnoky wrote: Scott Marlowe wrote: On Mon, Mar 9, 2009 at 8:21 PM, Mike Charnoky n...@nextbus.com wrote: The random sampling query is normally pretty snappy. It usually takes on the order of 1 second to sample a few thousand rows of data out of a few million. The sampling is consistently quick, too. However, on some days, the sampling starts off quick, then when the process starts sampling from a different subset of data (different range of times for the same day), the sampling query takes a couple minutes. Then definitely look at saving explain plans before execution to compare fast to slow runs. This definitely sounds like ocassionally bad query plans to me so far. Tom Lane wrote: Mike Charnoky n...@nextbus.com writes: The sampling query which runs really slow on some days looks something like this: INSERT INTO sampled_data (item_name, timestmp, ... ) SELECT item_name, timestmp, ... ) FROM raw_data WHERE timestmp = ? and timestmp ? AND item_name=? AND some_data_field NOTNULL ORDER BY random() LIMIT ?; Hmph, I'd expect that that would run pretty slowly *all* the time :-(. There's no good way to optimize ORDER BY random(). However, it seems like the first thing you should do is modify the program so that it issues an EXPLAIN for that right before actually doing the query, and then you could see if the plan is different on the slow days. I'm at the end of my rope here. Tried the following: * Manually run ANALYZE on the table before running the sampling query. This does not help, there are still times when the sampling runs slower by two orders of magnitude and disk IO is through the roof. * Bump work_mem for the query to 128MB (up from 32MB). This did not help. Also, no temp files were created in $PG/data/base/pgsql_tmp/, so work_mem does not seem to be an issue. * EXPLAINs look nearly identical whether the query runs quickly or slowly The thing that gets me is, why does this query totally hose the entire database? Other clients have a very hard time writing to the db when this sampling query is running slow, disk IO is maxxed out. This just doesn't seem right. Why would a single pg backend strangle db performance to such an extent? Aren't there ways to throttle this back? Due to the nature of the sampling (need to limit using several parameters with a WHERE clause), I can't just generate random numbers to select data that I need. Looks like I am stuck using ORDER BY RANDOM(). The only other option at this point seems to be to implement TABLESAMPLE, probably starting with the academic work that Neil Conway published (http://neilconway.org/talks/hacking/) Mike -- 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] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5
No, the last week i didn't installed any software. The only possible installation may be a instalation of some windows update. I will look for it, and if there is some thing about any antivirus/firewall update. Best regards. Adrian Klaver escribi: On Wednesday 25 March 2009 5:58:33 am Tk421 wrote: I've configured postgreSQL server to listen only in one IP address and the error stills. Anyone has more ideas to solve it? In the past week have you installed any other software or changed the behavior of software? In particular I am thinking of Anti-Virus software. The reason I ask is that as far as I can tell the error comes from the section of postmaster.c that has the following comment: /* * Duplicate a socket for usage in a child process, and write the resulting * structure to the parameter file. * This is required because a number of LSPs (Layered Service Providers) very * common on Windows (antivirus, firewalls, download managers etc) break * straight socket inheritance. */ This would seem to point another process interfering with the Postgres. Best regards Benedikt Schackenberg escribi: you have a power saving mode enabled? of inactivity when your pc shuts down etc? King Regards -Ursprngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Tk421 Gesendet: Mittwoch, 25. Mrz 2009 11:32 An: pgsql-general@postgresql.org Betreff: [GENERAL] Problem with Windows XP Pro SP3 asn PostgreSQL 8.3.5 Hello everybody. In the last week i'm having a trouble with my PostgreSQL working on Windows XP Professional. When i boot, Postgresql works correctcly, but after about an hour it stops working, an i get timeouts when i try to connect to it. In the postgresql log, i get this message: 2009-03-25 10:52:57 CET FATAL: could not duplicate socket 1276 for use in backend: error code 10022 After this, if i go to Event Viewer, i get this warning: PostgreSQL Database Server 8.3 Service stopped. In task manager, i get two postgres.exe process running, the first one of 104 Kb, and the second about 5000 kb. If i try to start postgresql service again, I got these messages: Waiting for server startup... LOG loaded library "$libdir/plugins/plugin_debugger.dll" LOG could not bind IPv4 socket: No error HINT is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for "*" FATAL: could not create any TCP/IP sockets The service appears as starting, but it does not start. If i kill the two postgresql porcessess the one of 5000kb stops correctly, but i get access denied for killing the one of 104 Kb. If i try to start postgresql service again, i can't, because it stills starting, and i can't do anithing to it. The only way is reboot the machine, but after about an hour, the same history. Before i detected this trouble last week, i had never got any problem any idea? King Regards
[GENERAL] Deferrable constraints
Hi, Does anyone know if there are plans to make deferrable contraints not just limited to foreign keys? Like unique or check contraints? It would have been very useful today, but obviously we're having to come up with a less elegant solution. Thanks Thom
Re: [GENERAL] MAX(ROW(...)) - feature request
2009/3/25 Grzegorz Jaśkiewicz gryz...@gmail.com: least() greatest(). Actually, these answer a different question, OP is interested in using aggregate on composite type... 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] 8.3.7 Windows Update Error
Using the following links to get to the PostgreSQL 8.3.7 (Windows) one-click installer: http://www.postgresql.org/download/windows http://www.enterprisedb.com/products/pgdownload.do#windows and then running the update utility, I get the following error message: [Error] The existing data directory (Date/time setting: floating-point numbers) is not compatible with this server (Date/time setting: 64-bit integers). Q1) Will a fresh install with dump and reload work? Q2) Is integer timestamps becoming the new default from the previous floating point data type? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Profiling custom datatypes
Hi, I'm attempting to profile (the memory usage and CPU time of) some code I've written as part of a custom datatype. I've attempted to utilise valgrind and cachegrind, but this doesn't seem to work as expected. The following is the command used: valgrind --tool=cachegrind --trace-children=yes ./postgres -D ../data Running this and then invoking a SQL query that causes my code to execute doesn't seem to result in any output relating to my datatype, even though its code is taking the majority of the CPU time. Does anyone know what I'm doing wrong -- do I have to do something special for valgrind to inspect shared libraries? I have debug symbols compiled in everywhere. Ignoring valgrind specifically, does anyone know of any other tools that can be used to profile the memory usage and CPU time/load of a custom datatype library? Recent changes I made to client-side code resulted in an increase in the size of each instance of the type it uploads to the database, which, for reasons unknown, has caused the search time (using a custom 'match' operator) to go through the roof. My suspicions suggest the cache memory used isn't large enough to contain the entire table (though perhaps it was before the change) and because of this far more disk reads are necessary. Hopefully a decent profiler should be able to make this clear. Many thanks for any help, Will. -- 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] Proper entry of polygon type data
Mark Cave-Ayland wrote: Peter Willis wrote: Incidentally, PostGIS uses PostgreSQL polygon, point, and path data types. Errr... no it doesn't. PostGIS uses its own internal types to represent all the different geometries, although it does provide a cast between the existing PostgreSQL types as an aid for people wishing to migrate. I stand corrected I guess. The last time I looked at the actual guts of PostGIS was WY back. And that was long before I actually started using it. ...of course, my WAAA back memory may be crossed with my WAAAY back forgetfulness, there as well Peter -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about hosting and server grade
Hi. I have a questionf or people who run high traffic websites. We are considering a new dedicated server host for a set of 25 domains, about 5 of which are very high traffic (80 million clicks a day each). A lot of this is VIEW content, but there may be a million or so INSERTs and UPDATEs. I am told that the biggest speed boost and performance comes from memory and fast hard disk. So I'm looking for at least a 16GB RAM and SCSI 10k 300GB hard disks. We will use CentOS 5 with Apache 2. I am also told that PHP etc is okay, but Postgresql (the database) is the one that hogs resources after a while. So for the database server I need a high end server. My question: What's the high end recommendation? Is the following config of 4 x quadcore Dunnington Intels with 4 disks on RAID 10 be good enough for the above sites? Can I run a database on this config of servers for my kind of traffic, or do I need a separate one for PG? I suppose the traffic will grow large quite quickly so the 300GB may be low, but that we can add as we go along. Thanks for any thoughts! Quad Processor Hex Core Intel 7450 - 2.40GHz (Dunnington) - 6 x 9MB (L2) 12MB (L3) cache Second Processor Hex Core Intel 7450 - 2.40GHz (Dunnington) - 6 x 9MB (L2) 12MB (L3) Third Processor Hex Core Intel 7450 - 2.40GHz (Dunnington) - 6 x 9MB (L2) 12MB (L3) cache Fourth Processor Hex Core Intel 7450 - 2.40GHz (Dunnington) - 6 x 9MB (L2) 12MB (L3) cache 16 GB FB-DIMM Registered 533/667 1000 Mbps public uplink 1000 Mbps private uplink Disk Controller RAID 10 HD1: 300GB SA-SCSI 10K RPM HD2: 300GB SA-SCSI 10K RPM HD3: 300GB SA-SCSI 10K RPM HD4: 300GB SA-SCSI 10K RPM CentOS 5 (32 bit) -- 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] MAX(ROW(...)) - feature request
2009/3/25 Merlin Moncure mmonc...@gmail.com: Actually, these answer a different question, OP is interested in using aggregate on composite type... true, I was too quick ... :) -- GJ -- 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] 8.3.7 Windows Update Error
On Wed, 2009-03-25 at 10:25 -0700, Richard Broersma wrote: Using the following links to get to the PostgreSQL 8.3.7 (Windows) one-click installer: http://www.postgresql.org/download/windows http://www.enterprisedb.com/products/pgdownload.do#windows and then running the update utility, I get the following error message: [Error] The existing data directory (Date/time setting: floating-point numbers) is not compatible with this server (Date/time setting: 64-bit integers). Q1) Will a fresh install with dump and reload work? Yes. Q2) Is integer timestamps becoming the new default from the previous floating point data type? In 8.4 yes. Not sure why the windows installer does that now. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] 8.3.7 Windows Update Error
Richard Broersma wrote: Using the following links to get to the PostgreSQL 8.3.7 (Windows) one-click installer: http://www.postgresql.org/download/windows http://www.enterprisedb.com/products/pgdownload.do#windows and then running the update utility, I get the following error message: [Error] The existing data directory (Date/time setting: floating-point numbers) is not compatible with this server (Date/time setting: 64-bit integers). Q1) Will a fresh install with dump and reload work? Yes. Q2) Is integer timestamps becoming the new default from the previous floating point data type? My guess is that your previous install was from the community MSI installer, and not the one-click one. Could that be it? (and yes, it's becoming the new default I believe - but it doesn't (shouldn't) change between minor versions) //Magnus -- 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] MAX(ROW(...)) - feature request
On Wed, Mar 25, 2009 at 12:50:01PM -0400, Merlin Moncure wrote:
2009/3/25 Grzegorz Jaśkiewicz gryz...@gmail.com:
least()
greatest().
Actually, these answer a different question, OP is interested in using
aggregate on composite type...
I think Grzegorz was pointing out (rather too tersely in my opinion)
that it would be nice if these were supported as well. E.g.
CREATE TABLE foo ( i INT );
SELECT least('(1)'::foo,NULL);
results in an error. When this should be valid.
--
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] could not access status of transaction
Hi All, One of our databases suffered a problem yesterday during a normal update, something we have been doing for years. Near the end of the process a foreign key constraint is rebuilt on a table containing several hundred million rows. Rebuilding the constraint failed with the following message: ERROR: could not access status of transaction 4294918145 DETAIL: Could not open file pg_clog/0FFF: No such file or directory. Here's the table and constraint definitions: CREATE TABLE point_history ( point_id integer NOT NULL, value real NOT NULL, status integer NOT NULL, timestamp timestamp without time zone NOT NULL ); ALTER TABLE point_history ADD CONSTRAINT point_history_pkey PRIMARY KEY (point_id, timestamp); ALTER TABLE point_history ADD CONSTRAINT $1 FOREIGN KEY (point_id) REFERENCES point(id); I read about this and and created the pg_clog/0FFF file, filling it with 256K of zeroes and then vacuumed the database. Then I tried rebuilding the constraint and received a foreign key violation: DETAIL: Key (point_id)=(2) is not present in table point. The crappy thing about this is that there was no record in the point_history table with point_id = 2: db= select * from point_history where point_id = 2; point_id | value | status | timestamp --+---++--- (0 rows) I scratched my head for a while and decided to reload the database from a backup, which I'm still working on now. I'm wondering if anyone has any thoughts or ideas about this? I found references to similar problems but they were all for older versions of PostgreSQL. When the problem occurred we were running 8.3.6 and are now running 8.3.7. Tom -- Tom Duffey tduf...@techbydesign.com Technology by Design :: http://techbydesign.com/ p: 414.431.0800
[GENERAL] Can we load all database objects in memory?
Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak
Re: [GENERAL] Question about hosting and server grade
On Wed, Mar 25, 2009 at 11:42 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Hi. I have a questionf or people who run high traffic websites. We are considering a new dedicated server host for a set of 25 domains, about 5 of which are very high traffic (80 million clicks a day each). A lot of this is VIEW content, but there may be a million or so INSERTs and UPDATEs. Given an 8 hour day, and all million happening then, that's 100 / 8*60*60 or 34 transactions per second. That's not too bad. I am told that the biggest speed boost and performance comes from memory and fast hard disk. So I'm looking for at least a 16GB RAM and SCSI 10k 300GB hard disks. We will use CentOS 5 with Apache 2. I am also told that PHP etc is okay, but Postgresql (the database) is the one that hogs resources after a while. So for the database server I need a high end server. It's not that so much that it's hard to distribute database load across 1 server. I can build a farm with 10 PHP servers and a load balancer easy enough. Building a 10 db farm that replicate between each other is much more work, and may or may not scale particularly well. So, with a DB, you are putting more eggs in fewer baskets. My question: What's the high end recommendation? Is the following config of 4 x quadcore Dunnington Intels with 4 disks on RAID 10 be good enough for the above sites? Can I run a database on this config of servers for my kind of traffic, or do I need a separate one for PG? I suppose the traffic will grow large quite quickly so the 300GB may be low, but that we can add as we go along. I'd spend more money on your disks and RAID controllers, and less on CPUs. If you have all those cores and 16 or 32 Gig of ram, and your RAID controller / 4 disk RAID-10 is your choke point, you can't just upgrade overnight. Spend your money on more RAM, (32G isn't much more than 16G and I've seen it make a world of difference on our servers). Spend it on disks. Number of disks is often more important than RPM etc. Spend it on fast RAID controllers with battery backed cache. Then, consider upgrading your CPUs. We have 8 opteron cores in our servers, and 12 Disk RAID-10s under a very fast RAID controller, and we are still I/O not CPU bound. Move pg_xlog to its own RAID-1 set. As a minimum buy a server with enough expansion slots that you can add the disks later. The cost difference between a 4 drive 1U case and a 16 drive 3U case is not all that much, and it gives you the option of adding some drives as you go along. But all of this depends on the type of workload your db has to do. If you're running memory hungry select queries, focus on more memory. If you're running lots and lots of little queries with a mix of update, insert, delete and select, focus on the drives / controller. If you're running queries that require a lot of CPU, then focus more on that. I haven't seen a lot of workloads that tend to be cpu heavy enough to need 16 cores and only 4 drives. I have seen a lot that required 2 cores and 40+ drives to run fast. So the real answer is to test your workload on something close to what you're looking at using for a db server and look for bottlenecks. I'm betting I/O will be the biggest one once you've got enough memory. -- 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] 8.3.7 Windows Update Error
On Wed, Mar 25, 2009 at 5:59 PM, Joshua D. Drake j...@commandprompt.com wrote: Q2) Is integer timestamps becoming the new default from the previous floating point data type? In 8.4 yes. Not sure why the windows installer does that now. An error on my part - the one-click installer was originally released with integer datetimes when it shouldn't have been. Obviously I can't fix that now without breaking upgrades for users of just that package :-( So for 8.3 you *must* stick with one installer - though that is generally recommended anyway. -- 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] [SQL] Can we load all database objects in memory?
Deepak, please don't cross-post the same question to 3 different lists. The short answer is no, you cannot force PostgreSQL to load all objects into memory. However when you proper configure PostgreSQL most, if not all of your data will be cached by the OS and/or PostgreSQL shared memory system. Ries On Mar 25, 2009, at 2:20 PM, DM wrote: Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak -- 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] 8.3.7 Windows Update Error
On Wed, Mar 25, 2009 at 11:00 AM, Magnus Hagander mag...@hagander.net wrote: Q2) Is integer timestamps becoming the new default from the previous floating point data type? My guess is that your previous install was from the community MSI installer, and not the one-click one. Could that be it? Thanks guys! Yes this is correct. I am now downloading the community version. I am curious why the community version isn't the first choice listed in the PostgreSQL download page. (Not that it really matters which version is listed first.) -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] [SQL] Can we load all database objects in memory?
On Wed, Mar 25, 2009 at 1:20 PM, DM dm.a...@gmail.com wrote: Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Just replying to pgsql-general... Yeah, just select * from table for each table, then they'll be in kernel cache and ready to go. -- 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] Question about hosting and server grade
On Wed, 2009-03-25 at 23:12 +0530, Phoenix Kiula wrote: Hi. I have a questionf or people who run high traffic websites. My question: What's the high end recommendation? Is the following config of 4 x quadcore Dunnington Intels with 4 disks on RAID 10 be good enough for the above sites? Can I run a database on this config of servers for my kind of traffic, or do I need a separate one for PG? I suppose the traffic will grow large quite quickly so the 300GB may be low, but that we can add as we go along. A 4 Disk RAID 10 will give you ~ 100MBs random write per second, max. What type of IO are you using now? (This is also assumes an actual decent RAID controller). Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] 8.3.7 Windows Update Error
On Wed, Mar 25, 2009 at 7:04 PM, Richard Broersma richard.broer...@gmail.com wrote: On Wed, Mar 25, 2009 at 11:00 AM, Magnus Hagander mag...@hagander.net wrote: Q2) Is integer timestamps becoming the new default from the previous floating point data type? My guess is that your previous install was from the community MSI installer, and not the one-click one. Could that be it? Thanks guys! Yes this is correct. I am now downloading the community version. I am curious why the community version isn't the first choice listed in the PostgreSQL download page. (Not that it really matters which version is listed first.) They're both packaged by exactly the same community member. The one-click installer is listed first because it's easier to use and shields the user from many of the more advanced features in the MSI installer that some people find confusing. -- 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] [ADMIN] Can we load all database objects in memory?
Increase effective_cache_size parameter. An effective_cache_size=11GB should be more than enough. -Original Message- From: DM dm.a...@gmail.com To: pgsql-ad...@postgresql.org, pgsql-general@postgresql.org, pgsql-...@postgresql.org Subject: [ADMIN] Can we load all database objects in memory? Date: Wed, 25 Mar 2009 12:20:08 -0700 Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak
Re: [GENERAL] Profiling custom datatypes
William Harrower wjh...@doc.ic.ac.uk writes: Ignoring valgrind specifically, does anyone know of any other tools that can be used to profile the memory usage and CPU time/load of a custom datatype library? oprofile on recent Fedora (and probably other Linux distros) pretty much just works for shared libraries, though it only tells you about CPU profile not memory usage. I've never been able to get gprof to do anything useful with shlibs, on any platform :-( Recent changes I made to client-side code resulted in an increase in the size of each instance of the type it uploads to the database, which, for reasons unknown, has caused the search time (using a custom 'match' operator) to go through the roof. My suspicions suggest the cache memory used isn't large enough to contain the entire table (though perhaps it was before the change) and because of this far more disk reads are necessary. Hopefully a decent profiler should be able to make this clear. Surely just watching iostat or vmstat would prove or disprove that theory. Keep in mind also that CPU profilers aren't going to tell you much about I/O costs anyway. 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] 8.3.7 Windows Update Error
Magnus Hagander mag...@hagander.net writes: Richard Broersma wrote: Q2) Is integer timestamps becoming the new default from the previous floating point data type? My guess is that your previous install was from the community MSI installer, and not the one-click one. Could that be it? (and yes, it's becoming the new default I believe - but it doesn't (shouldn't) change between minor versions) Right, but in 8.3 and even before, different people have been putting out binary builds with different choices :-(. I'm not sure it's going to magically get better in 8.4. If Bruce gets anywhere with pg_migrator before 8.4 final, there will be a very strong temptation to stick with whatever one was using before, so as to avoid forcing a dump/reload... 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] text column constraint, newbie question
Stephen Cook wrote: Daniel Verite wrote: Note that htmlentities() expects LATIN1-encoded strings and is thus unusable on UTF-8 contents. So if you end up talking UTF-8 with the database, you'll probably need to use htmlspecialchars() instead, and UTF-8 as your HTML charset. I believe you are wrong, at least the PHP documentation says otherwise and it _seems_ to work for me (http://us2.php.net/htmlentities). Maybe you are thinking about an older version? You're right, I've missed the fact that they added support for other character sets at some point in php4. Now I know :) Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.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] Parallel Query Processing
Hello, I have a few questions related to the parallel query processing.Can you guys tell me how to implement parallel query processing in postgresql database. Thanks, Avin.
Re: [GENERAL] [SQL] Can we load all database objects in memory?
Deepak, please don't cross-post the same question to 3 different lists. The short answer is no, you cannot force PostgreSQL to load all objects into memory. However when you proper configure PostgreSQL most, if not all of your data will be cached by the OS and/or PostgreSQL shared memory system. Ries Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Weird encoding behavior
Hi, I'm experiencing a weird behavior when storing latin characters to a PostgreSQL 8.3.1.876 server. The database is Latin1 encoded, and it is working since September 2008, it wasn't updated nor replaced since its first installation. The weirdness of the problem is that sometimes the characters are stored correctly, but sometimes doesn't (allways by the same program), the field type is Varchar(30), and for example the text NUÑEZ is stored as NU?EZ. The data comes from an external application in an XML file (also Latin1), then, a Delphi service parses the XML and create the Insert/Update statements to store the data in the database. I'd try to reproduce the bug by sending XML files with 'Ñ' to the service, but it is stored correctly. Also, there's a front end that allows users to see/edit the data in a user friendlier way. Again, I checked by inserting records with 'Ñ' using this front-end, and also are stored correctly. Does anyone faced the same problem? any workaround? Thanks in advance, Leonardo -- 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] Parallel Query Processing
On Wed, Mar 25, 2009 at 2:24 PM, aravind chandu avin_frie...@yahoo.com wrote: Hello, I have a few questions related to the parallel query processing.Can you guys tell me how to implement parallel query processing in postgresql database. Do you mean one query being parallelized, or multiple queries running at once? PostgreSQL provides no capability to parallelize one query into multiple processes. Multiple Queries run in parallel automatically -- 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 shut down unexpectedly.
On Thu, Mar 26, 2009 at 2:23 AM, Bill Moran wmo...@potentialtech.comwrote: In response to Tim Uckun timuc...@gmail.com: Today the database shut down unexpectedly. I have included the log file that shows the shutdown. Can anybody tell me why this happened and how I can make sure it doesn't happen again. The only thing I can think of that I did was to specify a password for the postgres user in the operating system. Not likely to cause the DB to restart ... at least not in any OS configuration that I'm aware of. However, you don't mention what OS you're running ... that might be important. Here is the log file. Very strange. 2009-03-25 00:02:01 GMT LOG: incomplete startup packet 2009-03-25 00:30:05 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:30:05 GMT LOG: unexpected EOF on client connection 2009-03-25 00:30:05 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:30:05 GMT LOG: unexpected EOF on client connection 2009-03-25 00:32:15 GMT LOG: could not receive data from client: Connection timed out 2009-03-25 00:32:15 GMT LOG: unexpected EOF on client connection 2009-03-25 02:41:57 GMT LOG: incomplete startup packet 2009-03-25 02:41:57 GMT LOG: received smart shutdown request ^^^ Sure looks like someone intentionally shut the database down. That was most likely me (doing a /etc/init.d/postgresql restart. I thought I would restart it after changing the user name. Notice that the time on that is 02:41 GMT. The actual shutdown occured on 07:05 GMT some four hours later. Does that make sense?
[GENERAL] Announcement - PostgreSQL Performance Conference
For those who are interested in performance overall and want a good free technical conference, we're holding our first Performance Conference. Bullet points: * April 22 and 23, Santa Clara Convention Center, Santa Clara, California USA * Same time place as MySQL Conference http://www.mysqlconf.com/ * Free. No registration required (but optional registration appreciated). * Technical. Every speaker has been asked to keep it technical. * Intense. It runs all day long, and talks are short. * Not about databases, it's about performance. But we have lots of database talks on the schedule. * http://conferences.percona.com/ - schedule at http://conferences.percona.com/percona-performance-conference-2009/schedule.html - register at http://conferences.percona.com/percona-performance-conference-2009/registration.html As an example, Cary Millsap will be speaking on performance instrumentation. Cary is a leading figure in Oracle performance. His book //Optimizing Oracle Performance// should be required reading for all database developers, not just Oracle developers. And of course, there will be several names PostgreSQL folks will recognize, including Selena Deckelmann, Robert Treat, Robert Hodges, and David Fetter. I hope to see you there. Baron -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multiply count in select
Hi there, I was trying this query:
SELECT player.name, pos.position, count(event.event_id) AS APP,
count(goal.event_id) AS GOAL
FROM t_events event, t_events goal, t_players player, t_positions pos
WHERE player.position_id=pos.id
AND player.team_id=2
AND event.player_id=player.id
AND goal.player_id=player.id
AND goal.event_id=1
AND event.event_id=4
GROUP BY player.name, pos.position;
but it gave me result
name| position | app | goal
---++-+--
AdeJaWoR. | forward| 3 |3
Ronnie| defender | 18 | 18
Parciez | midfielder | 54 | 54
Trzmielu | defender | 18 | 18
_Domin_ | forward| 64 | 64
Muffin| midfielder | 30 | 30
And that wasn't my intention. I have table:
Table public.t_events
Column | Type |
Modifiers
---+--+---
id| integer | not null default
nextval('t_events_id_seq'::regclass)
player_id | integer | not null
match_id | integer | not null
event_id | integer | not null
time | smallint |
team_id | integer | not null
Others are just stuff to connect ids with real names, positions. How can I
get list of players with positions and count of goals and appearances? In
table t_events is field event_id where 1 means goal, 2 assist, 3 own goal
etc.
Re: [GENERAL] Weird encoding behavior
Hola use #209; for spanish N http://webdesign.about.com/od/localization/l/blhtmlcodes-sp.htm Saludos Cordiales desde EEUU! Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. From: martinr...@yahoo.com Subject: [GENERAL] Weird encoding behavior Date: Wed, 25 Mar 2009 15:52:55 -0300 To: pgsql-general@postgresql.org Hi, I'm experiencing a weird behavior when storing latin characters to a PostgreSQL 8.3.1.876 server. The database is Latin1 encoded, and it is working since September 2008, it wasn't updated nor replaced since its first installation. The weirdness of the problem is that sometimes the characters are stored correctly, but sometimes doesn't (allways by the same program), the field type is Varchar(30), and for example the text NUÑEZ is stored as NU?EZ. The data comes from an external application in an XML file (also Latin1), then, a Delphi service parses the XML and create the Insert/Update statements to store the data in the database. I'd try to reproduce the bug by sending XML files with 'Ñ' to the service, but it is stored correctly. Also, there's a front end that allows users to see/edit the data in a user friendlier way. Again, I checked by inserting records with 'Ñ' using this front-end, and also are stored correctly. Does anyone faced the same problem? any workaround? Thanks in advance, Leonardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Internet Explorer 8 – Now Available. Faster, safer, easier. http://clk.atdmt.com/MRT/go/141323790/direct/01/
[GENERAL] log shipping from 64 bit linux server to a 32 bit linux server
According to the documentation it's not possible to log ship from a 64 bit server to a 32 bit server. I just want to confirm that this is the case before I waste a whole lot of time trying to set it up.
Re: [GENERAL] log shipping from 64 bit linux server to a 32 bit linux server
According to the documentation it's not possible to log ship from a 64 bit server to a 32 bit server. I think the doc is quite correct. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] log shipping from 64 bit linux server to a 32 bit linux server
On Thu, Mar 26, 2009 at 2:05 PM, Tatsuo Ishii is...@postgresql.org wrote: According to the documentation it's not possible to log ship from a 64 bit server to a 32 bit server. I think the doc is quite correct. So what is the best way to accomplish a failover from a 64 bit machine to a 32 bit machine?
Re: [GENERAL] could not access status of transaction
Tom Duffey tduf...@techbydesign.com writes: One of our databases suffered a problem yesterday during a normal update, something we have been doing for years. Near the end of the process a foreign key constraint is rebuilt on a table containing several hundred million rows. Rebuilding the constraint failed with the following message: ERROR: could not access status of transaction 4294918145 DETAIL: Could not open file pg_clog/0FFF: No such file or directory. This looks like a garden-variety data corruption problem to me. Trashed rows tend to yield this type of error because the xmin transaction ID is the first field that the server can check with any amount of finesse. 4294918145 is 4001 in hex, saith my calculator, so it looks like a bunch of bits went to ones --- or perhaps more likely, the row offset in the page header got clobbered and we're looking at some bytes that never were a transaction ID at all. So I'd try looking around for flaky RAM, failing disks, loose cables, that sort of thing ... 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] log shipping from 64 bit linux server to a 32 bit linux server
On Wed, Mar 25, 2009 at 7:08 PM, Tim Uckun timuc...@gmail.com wrote: On Thu, Mar 26, 2009 at 2:05 PM, Tatsuo Ishii is...@postgresql.org wrote: According to the documentation it's not possible to log ship from a 64 bit server to a 32 bit server. I think the doc is quite correct. So what is the best way to accomplish a failover from a 64 bit machine to a 32 bit machine? slony? -- 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] log shipping from 64 bit linux server to a 32 bit linux server
slony? That sound more like a question than an answer :) Can I presume it doesn't care about the architecture of the OS?
Re: [GENERAL] log shipping from 64 bit linux server to a 32 bit linux server
According to the documentation it's not possible to log ship from a 64 bit server to a 32 bit server. I think the doc is quite correct. So what is the best way to accomplish a failover from a 64 bit machine to a 32 bit machine? slony? IMO Slony doesn't do failover. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] log shipping from 64 bit linux server to a 32 bit linux server
On Wed, Mar 25, 2009 at 8:23 PM, Tatsuo Ishii is...@postgresql.org wrote: According to the documentation it's not possible to log ship from a 64 bit server to a 32 bit server. I think the doc is quite correct. So what is the best way to accomplish a failover from a 64 bit machine to a 32 bit machine? slony? IMO Slony doesn't do failover. Not all by itself. But then neither does PITR by itself. But I'm sure you know that. :) -- 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] log shipping from 64 bit linux server to a 32 bit linux server
According to the documentation it's not possible to log ship from a 64 bit server to a 32 bit server. I think the doc is quite correct. So what is the best way to accomplish a failover from a 64 bit machine to a 32 bit machine? slony? IMO Slony doesn't do failover. Not all by itself. But then neither does PITR by itself. But I'm sure you know that. :) I know what you think:-) Problem is, he asks high availabilty, that means, no SPOF, minimum down time. For the purpose, I suppose pgpool-HA(actually heartbeat)+pgpool-II+Slony-I might work, but I'm not sure heartbeat does work with 32/64bit combo. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] could not access status of transaction
Hi Tom, On Mar 25, 2009, at 9:02 PM, Tom Lane wrote: Tom Duffey tduf...@techbydesign.com writes: One of our databases suffered a problem yesterday during a normal update, something we have been doing for years. Near the end of the process a foreign key constraint is rebuilt on a table containing several hundred million rows. Rebuilding the constraint failed with the following message: ERROR: could not access status of transaction 4294918145 DETAIL: Could not open file pg_clog/0FFF: No such file or directory. This looks like a garden-variety data corruption problem to me. Trashed rows tend to yield this type of error because the xmin transaction ID is the first field that the server can check with any amount of finesse. 4294918145 is 4001 in hex, saith my calculator, so it looks like a bunch of bits went to ones --- or perhaps more likely, the row offset in the page header got clobbered and we're looking at some bytes that never were a transaction ID at all. So I'd try looking around for flaky RAM, failing disks, loose cables, that sort of thing ... Are you aware of any issues like this related to VMWare ESX? Our PostgreSQL server is running in such an environment and I asked the guys to review your email and they thought maybe this type of corruption could happen when the virtual machine was moved from one physical server to another, which we have done once or twice in the past few months. Tom -- Tom Duffey tduf...@techbydesign.com Technology by Design :: http://techbydesign.com/ p: 414.431.0800
