[GENERAL] Division by zero
In other SQL programs a division by zero is solved by transforming the result to NULL. How can I make postgres have the same behaviour without using CASE ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL Question
From: Alex [EMAIL PROTECTED] - How can i select only the newest record for each ProdId ? 100| 2005-04-01 200| 2005-04-01 DISTINCT ON was made for this and on the similar tables I have performs rather more efficiently than using a subquery. select distinct on (ProdId) ProdId , LastUpdate from produpdate order by ProdId , LastUpdate desc; - How can i select to retrieve the last 2 dates in record 100| 2005-04-01 | 2005-03-01 200| 2005-04-01 | 2005-03-01 To get the previous one, my first thought is something like: select distinct on (ProdId) ProdId , LastUpdate from produpdate p1 where LastUpdate ( select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId ) order by ProdId , LastUpdate desc ; but there may be a much more efficient way of getting the nth result in general. Julian Scarfe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Loosing connection with the database
Poul Mller Hansen wrote: I'm using Postgresql version 7.4.7 and jdbc driver version pg74.215.jdbc3.jar. Do you have a clue on what's going on ? No, I don't. Do you have any more information? What is your code doing when it fails? Just issuing a regular query? Are you using any of the less common driver features: Large objects, fastpath api, a COPY patch? If the driver had a protocol problem I would expect it to be rather repeatable. If the driver had a synchronization problem it should have disappeared when you moved to a single thread model. I've attached the test script I've used to try and beat on the driver. Kris Jurka Thanks, your application runs without any problems, so it can't provoke the error. I'm only using plain sql insert and update statements, the only special use I can think of, is that I'm using triggers in the tables, but I can't imagine they can cause it. I have now added extensive logging to the application, but so far the problem hasn't appeared. Poul
Re: [GENERAL] Division by zero
On Sat, Apr 16, 2005 at 10:15:55AM +0300, Costin Manda wrote: In other SQL programs a division by zero is solved by transforming the result to NULL. The SQL standards state that If the value of a divisor is zero, then an exception condition is raised: data exception -- division by zero. Databases that silently convert this exception to NULL (e.g., MySQL) are violating standards. How can I make postgres have the same behaviour without using CASE ? Why don't you want to use CASE? Because it's unwieldy? You could wrap CASE in a function and call that function instead of using the / operator. I'd avoid any temptation to change the behavior of the operator itself because that could cause problems in other code that isn't expecting it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Division by zero
Hi Costin I am very experienced with MS-SQL and have had very basic training on Oracle and both of these raise an exception as well. I would suggest that a RDBMS that automatically converts to null is way off the standards. Craig - Original Message - From: Costin Manda [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Saturday, April 16, 2005 9:15 AM Subject: [GENERAL] Division by zero In other SQL programs a division by zero is solved by transforming the result to NULL. How can I make postgres have the same behaviour without using CASE ? ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pgpool with PostgreSQL 7.4
Tatsuo, I'm excited that pgpool is working for us now. We plan to start using it in the near future on our production applications (hundreds of thousands of DB transactions per day). I have just a couple follow-up questions: 1. When we use pgpool, should we still use a database connection pool? Or should we have the application share only one connection (that pgpool then transforms into many simulatenous connections)? I'm afraid I don't understand what you are saying. Maybe you mean num_init_chidlren = 1 ? 2. We have some SELECT statements that are for reporting, and we would like them only to get load balanced to the slave. Is there any way to force a query to be sent to the slave (e.g. using comments at the beginning of the query)? If not, is this something you would consider adding to pgpool? Sounds nice and it should not be super hard to implement. I'm looking for adding this to pgpool. 3. Do you plan on adding support for more than one slave in the future? Is this feasible? Yes, it's on my TODO list. -- Tatsuo Ishii Thank you for your time, Kevin Tatsuo Ishii [EMAIL PROTECTED] wrote: What are client_encoding values on these two servers? It seems they do not match... -- Tatsuo Ishii Sure, here is the debug output when I run the following command.. -bash-2.05b$ psql -p -c 'show pool_status' template1 psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Debug output from pgpool: 2005-04-14 10:23:55 DEBUG: pid 5557: I am 5557 accept fd 6 2005-04-14 10:23:55 DEBUG: pid 5557: Protocol Major: 3 Minor: 0 database: template1 user: postgres 2005-04-14 10:23:55 DEBUG: pid 5557: connecting postmaster Unix domain socket: /tmp/.s.PGSQL.5432 2005-04-14 10:23:55 DEBUG: pid 5557: connected to postmaster Unix domain socket: /tmp/.s.PGSQL.5432 fd: 7 2005-04-14 10:23:55 DEBUG: pid 5557: read_message_length: lenghth: 8 2005-04-14 10:23:55 DEBUG: pid 5557: read_message_length: lenghth: 30 2005-04-14 10:23:55 ERROR: pid 5557: read_message_length: length does not match between backends master(30) secondary(27) -- Kevin Tatsuo Ishii wrote: Can you show me debug messages by starting pgpool with -d option? -- Tatsuo Ishii Hello, I'm having trouble getting pgpool to work in master/slave mode. I'm planning on using something like Slony-I for replication, and use pgpool only to load balance the queries across the master and slave, as well as provide failover if the master goes down. I configured the settings so replication_mode = false, load_balance_mode = true, and master_slave_mode = true. I also set secondary_backend_host_name and secondary_backend_port to refer to the slave. Here's what happens when I try to connect to the database to show the status: -bash-2.05b$ psql -p -c 'show pool_status' template1 psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The strange thing is that if I change the configuration so master_slave_mode = false, it seems that the failover works but load balancing does not. When master/slave is disabled, I'm able to show the status and run queries against the master. If I use pgpool -s s switch to perform a manual switchover, I am then able to run queries against the slave. But as soon as I try running pgpool with master_slave_mode = true, it stops working and when I try to connect I get the error above (server closed the connection unexpectedly). Am I doing something wrong? Below are my configuration settings when I receive the error. I'm using the latest stable build of pgpool (2.5.1). The master is PG 7.4.7, the slave is PG 7.4. [EMAIL PROTECTED] etc]# more pgpool.conf # # pgpool configuration file sample # $Header: /cvsroot/pgpool/pgpool/pgpool.conf.sample,v 1.1.1.1 2005/02/14 14:52:21 t-ishii Exp $ # host name or IP address to listen on: '*' for all, '' for no TCP/IP connections listen_addresses = '*' # port number for pgpool port = # Unix domain socket path. Debian package default to /var/run/postgresql! socket_dir = '/tmp' # host name where PostgreSQL server is running on. '' means localhost using UNIX # domain socket backend_host_name = '' # port number PostgreSQL server is running on. backend_port = 5432 # Unix domain socket path for the backend. Debian package default to /var/run/postgresql! backend_socket_dir = '/tmp' # host name where secondary PostgreSQL server is running on. '' means localhost using UNIX # domain socket secondary_backend_host_name = '192.168.0.153' # port number secondary PostgreSQL server is running on. # 0 means no secondrary PostgreSQL secondary_backend_port = 5432
[GENERAL] Announcment: PG Lighting Admin Pre-Release 6 available
Check it out here: http://www.amsoftwaredesign.com/ This release adds built in SSH tunnel support right from the server registration dialog along with the ability to execute a database dump from the GUI.(restore coming soon). This release also properly exports text fields with CRLF in them (strips them out and replaces with a space) (pretty hard to import into other systems when a text field causes one row to become many :-) Not to mention lots of other nice features like: Printing Tabbed Enterprise Manager Code completion in the function and query editors, type the name of a schema and period and the code completion displays all objects, or hit ctrl space to see all the SQL Key words and built in functions and types. Function version control Ability to drag and drop object names from the tree view to the function and query editors. Copy field and params names/types to the clipboard Export all or selected rows from a result set to the clipboard or to a file. Query editor caches the contents of the editor when you close it, then the next time you open the editor for a DB you can see the last things you did. I could go on and on. PG Lightning Admin has been in development since Nov 2004 and is built with Borland Delphi 2005. You can check it out for free for 30 days and if you like it it will only cost you 15 US dollars and even less if you use Euros :-) Thanks, AM Software Design ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Overload
Hi, I'd like to implement a system to prevent a PostgreSQL database from being overloaded by delaying queries when the database is already highly loaded. I.e. the the sum of the execution costs of queries currently in the database is already near a certain threshold and executing the next query would cause the execution costs to pass this threshold. Limiting the number of queries concurrently in the database to a fixed number n is out of question since - in my opinion - n simple SELECT c FROM t WHERE c=... would generally produce a much lower workload than n complex queries. So, the goal is some more dynamic approach. But my problem is to measure the execution costs of a query. My first thought was to use the estimates of the optimizer but these estimates only give the time needed to execute the query. I know that the term execution costs is somewhat imprecise. Ideally, the value for the execution costs is a value that merges the I/O and the CPU usage used by the query (to be more precise: estimates about the I/O and CPU usage for the query). I've read the developer manuals but I didn't find any information on this. Does PostgreSQL offer information on the additional workload (execution costs) caused by a query? In case it does not: Does anybody have an idea how I get an estimate for the execution costs before executing a query? Thanks in advance Stefan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Overload
Stefan Krompass wrote: Does PostgreSQL offer information on the additional workload (execution costs) caused by a query? In case it does not: Does anybody have an idea how I get an estimate for the execution costs before executing a query? I cant add to you question, but two nightly thoughts: i) if you SQL-server is tortured by some application, its very likely that you have only a limited range of different select-types. You could measure the exact costs manually and use this values for your problem. ii) The workload might depend on you specific system, on your RAM, harddisk etc. On one system CPU-speed might be the bottleneck, on others the RAM and so on. And the cost for a query on your system is not always the same. Depending on swap, cache and so on. Maybe you should consider having a second SQL-server to lower load. best, peter -- mag. peter pilsl goldfisch.at IT-management tel +43 699 1 3574035 fax +43 699 4 3574035 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
