Re: [GENERAL] autoanalyze criteria
On Feb 25, 2013, at 7:23, Stefan Andreatta s.andrea...@synedra.com wrote: On 02/24/2013 12:52 PM, Alban Hertroys wrote: On Feb 23, 2013, at 14:11, Stefan Andreatta s.andrea...@synedra.com wrote: And we are still missing a number for rows updated since the last analyse. In MVCC an update is an insert + delete, so you already got those numbers. Good point. But because they are an update and a delete, they cancel each other out and do not show up in pg_stat_user_tables.n_live_tup - and that's the only value for which we have a reference value from the time of the last analyze (pg_class.reltuples). I'm pretty sure that an update results in 1 live + 1 dead tuple, so they don't cancel each other out - they end up adding to different statistics. Assuming those statistics are both since last vacuum, added together they are the total number of changed records since last vacuum. What gain do you expect from a number of updated tuples? And it seems to me those numbers are since last vacuum, not since last analyse - analyse doesn't change the amount of dead tuples (it just updates them to closer match reality), but vacuum does. Disclaimer: I'm not intimately familiar with the planner statistics, but knowing what vacuum and analyse do in an MVCC database, like I described above it makes sense to me. I might be wrong though. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: [GENERAL] autoanalyze criteria
On 02/25/2013 09:00 AM, Alban Hertroys wrote: On Feb 25, 2013, at 7:23, Stefan Andreatta s.andrea...@synedra.com mailto:s.andrea...@synedra.com wrote: On 02/24/2013 12:52 PM, Alban Hertroys wrote: On Feb 23, 2013, at 14:11, Stefan Andreatta s.andrea...@synedra.com mailto:s.andrea...@synedra.com wrote: And we are still missing a number for rows updated since the last analyse. In MVCC an update is an insert + delete, so you already got those numbers. Good point. But because they are an update and a delete, they cancel each other out and do not show up in pg_stat_user_tables.n_live_tup - and that's the only value for which we have a reference value from the time of the last analyze (pg_class.reltuples). I'm pretty sure that an update results in 1 live + 1 dead tuple, so they don't cancel each other out - they end up adding to different statistics. Assuming those statistics are both since last vacuum, added together they are the total number of changed records since last vacuum. What gain do you expect from a number of updated tuples? And it seems to me those numbers are since last vacuum, not since last analyse - analyse doesn't change the amount of dead tuples (it just updates them to closer match reality), but vacuum does. Disclaimer: I'm not intimately familiar with the planner statistics, but knowing what vacuum and analyse do in an MVCC database, like I described above it makes sense to me. I might be wrong though. 1 update = 1 insert + 1 delete cancel each other out with respect to pg_stat_user_tables.n_live_tup. Naturally, they dont't cancel each other out with pg_stat_user_tables.n_tup_ins or n_tup_del - they don't even show up in those values, presumably because that's what n_tup_upd is there for. However the update adds to n_dead_tup. VACUUM does not reset *any* of the statistics values that can be accessed via pg_stat_user_tables, apart from n_dead_tup (hopefully ;-) Anyway, to estimate the autoanalyze trigger, I would need statistics that get reset by autoanalyze not autovacuum. I wrote a test script to show the behaviour. Be sure to wait a second each time before accessing pg_stat_user_tables as there is a delay in getting those data: CREATE TABLE test_stat (id BIGINT, some_number BIGINT); INSERT INTO test_stat (SELECT generate_series(1,1) AS i, random() AS r); SELECT count(*) FROM test_stat; ANALYZE test_stat; -- wait here (0.5 s) for statistics collector to catch up SELECT relname, reltuples FROM pg_class WHERE relname = 'test_stat'; SELECT relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'test_stat'; INSERT INTO test_stat (SELECT generate_series(10001,10900) AS i, random() AS r); -- wait here (0.5 s) for statistics collector to catch up SELECT relname, reltuples FROM pg_class WHERE relname = 'test_stat'; SELECT relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'test_stat'; DELETE FROM test_stat WHERE id 1; -- wait here (0.5 s) for statistics collector to catch up SELECT relname, reltuples FROM pg_class WHERE relname = 'test_stat'; SELECT relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'test_stat'; UPDATE test_stat set some_number = 1 where id 9100; -- wait here (0.5 s) for statistics collector to catch up SELECT relname, reltuples FROM pg_class WHERE relname = 'test_stat'; SELECT relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'test_stat'; ANALYZE test_stat; SELECT relname, reltuples FROM pg_class WHERE relname = 'test_stat'; SELECT relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'test_stat'; VACUUM test_stat; -- wait here (0.5 s) for statistics collector to catch up SELECT relname, reltuples FROM pg_class WHERE relname = 'test_stat'; SELECT relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'test_stat'; DROP TABLE test_stat; Output from a postgres 9.2 database: -- test=# CREATE TABLE test_stat (id BIGINT, some_number BIGINT); CREATE TABLE test=# INSERT INTO test_stat (SELECT generate_series(1,1) AS i, random() AS r); INSERT 0 1 test=# SELECT count(*) FROM test_stat; count --- 1 (1 row) test=# ANALYZE test_stat; ANALYZE test=# SELECT relname, reltuples FROM pg_class WHERE relname = 'test_stat'; relname | reltuples ---+--- test_stat | 1 (1 row) test=# SELECT relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'test_stat'; relname | n_live_tup | n_dead_tup | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd
Re: [GENERAL] Floating point error
Here is a smaller test case that does not involve Java. I guess this probably is just due to floating point error when the initial value is inserted that is too large for the field but it's still a surprise. Create a test table, insert a couple values and view the results: CREATE TABLE test ( id INTEGER PRIMARY KEY, value REAL NOT NULL ); INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885); SELECT * FROM test; id | value +- 1 | 10.3885 2 | 10.3885 (2 rows) At this point you would think you have two equal values. Now change the type: ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION; SELECT * FROM test; id | value +-- 1 | 10.3884572982788 2 | 10.388500213623 (2 rows) Values no longer equal and the first one is in fact closer to what as originally inserted. Why is this? Is this simply caused by how the initially inserted value is stored as floating point? If you create a copy of the database before changing the field type then both values get inserted as 10.3885. Changing the type then results in two equal values. Maybe this is just another pitfall of using floating point numbers and at this point I am just trying to identify exactly where our errors are being introduced so can anyone confirm the above behavior is correct? In our real world example we are not changing the type but are instead getting the second value w/id = 1 above when using JDBC to retrieve values into a Java double field. I ran the above on PostgreSQL 9.1.2 and 9.2.2 with the same results. Tom On Feb 24, 2013, at 9:17 PM, Adrian Klaver adrian.kla...@gmail.com wrote: On 02/24/2013 06:58 PM, Tom Duffey wrote: On Feb 24, 2013, at 8:44 PM, Adrian Klaver adrian.kla...@gmail.com wrote: On 02/24/2013 06:13 PM, Tom Duffey wrote: Hi Everyone, Riddle me this. I have a database column of type real that gets mapped to a Java field of type double via JDBC. We have two databases, test and production, and the test database is periodically blown away and reloaded from a copy of production. We recently noticed that some values do not match when viewed within our application on test vs. production. More specifically: - Selecting values from both test and production DBs using psql shows 10.3885 as the value - The Java app on production shows 10.3884573 while the test app shows 10.3885 I have a hunch that when the value was originally inserted into the production DB it probably contained more than the 6 digits supported by the real data type. It may have even been exactly the 10.3884573 value we see when retrieving via JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sure seems like the extra information is coming back from the JDBC driver. Are the production and test apps running on the same platform i.e. OS, bitness, etc. Yes, the production and test apps are running on the same platform. The Java apps themselves are physically on the same Linux server. The production and test databases reside within the same instance of PostgreSQL. Also, I should have mentioned up front that I am well aware of the pitfalls of using floating point values and also the fact that PostgreSQL's real data type supports 6 digits of precision. What I do not understand is why my JDBC driver is returning more information than what I receive in psql or if I operate on a copy of the database. This leads me to believe that more information was available at insertion time and is somehow being made available to my application even though the data type should only store 6 digits. Let me see if I can write a quick little test case. Well I guess you could look in the dump file and see what is recorded there. Tom -- Adrian Klaver adrian.kla...@gmail.com -- Tom Duffey tduf...@trillitech.com 414-751-0600 x102 -- 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] Floating point error
Tom Duffey wrote: Here is a smaller test case that does not involve Java. I guess this probably is just due to floating point error when the initial value is inserted that is too large for the field but it's still a surprise. Create a test table, insert a couple values and view the results: CREATE TABLE test ( id INTEGER PRIMARY KEY, value REAL NOT NULL ); INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885); SELECT * FROM test; id | value +- 1 | 10.3885 2 | 10.3885 (2 rows) SET extra_float_digits=3; SELECT * FROM test; id | value + 1 | 10.3884573 2 | 10.3885002 (2 rows) PostgreSQL by default omits the last three digits to avoid differences on different architectures (I think). When you convert to double precision, you'll see these digits. At this point you would think you have two equal values. Now change the type: ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION; SELECT * FROM test; id | value +-- 1 | 10.3884572982788 2 | 10.388500213623 (2 rows) 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] Floating point error
That's exactly what I was looking for. We use COPY to transfer data from a 1 billion+ row table to a test database and were confused why the results looked the same but were obviously not. Sounds like we need to use the extra_float_digits setting to include all the available information when transferring the data. Thanks for the explanation. Tom On Feb 25, 2013, at 8:00 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Tom Duffey wrote: Here is a smaller test case that does not involve Java. I guess this probably is just due to floating point error when the initial value is inserted that is too large for the field but it's still a surprise. Create a test table, insert a couple values and view the results: CREATE TABLE test ( id INTEGER PRIMARY KEY, value REAL NOT NULL ); INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885); SELECT * FROM test; id | value +- 1 | 10.3885 2 | 10.3885 (2 rows) SET extra_float_digits=3; SELECT * FROM test; id | value + 1 | 10.3884573 2 | 10.3885002 (2 rows) PostgreSQL by default omits the last three digits to avoid differences on different architectures (I think). When you convert to double precision, you'll see these digits. At this point you would think you have two equal values. Now change the type: ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION; SELECT * FROM test; id | value +-- 1 | 10.3884572982788 2 | 10.388500213623 (2 rows) Yours, Laurenz Albe -- Tom Duffey tduf...@trillitech.com 414-751-0600 x102 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reading an OUT parameter out of a function call
Hi, I have a simple void function: CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN pnr := 1; END; $$ LANGUAGE plpgsql; How do I access myparam? I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or inside another function? Yours, Stefan -- 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] Reading an OUT parameter out of a function call
On Mon, Feb 25, 2013 at 11:22 AM, Stefan Keller sfkel...@gmail.com wrote: Hi, I have a simple void function: CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN pnr := 1; END; $$ LANGUAGE plpgsql; How do I access myparam? I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or inside another function? that should work. what error are you getting? also, SELECT myparam FROM myfn(); 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] Reading an OUT parameter out of a function call
Hello 2013/2/25 Stefan Keller sfkel...@gmail.com: Hi, I have a simple void function: CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN pnr := 1; END; $$ LANGUAGE plpgsql; How do I access myparam? I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or inside another function? you cannot access to out parameters outside function - because they doesn't exist - postgresql cannot pass parameters by ref. your example is exactly same as int returning function - you can use it in plpgsql variable := myfn(); -- variable is scalar int type if function has more out parameters, then return type is record type. CREATE OR REPLACE FUNCTION public.f1(a integer, b integer, OUT c integer, OUT d integer) RETURNS record LANGUAGE plpgsql AS $function$ begin c := a + b; d := c * 2; end; $function$ postgres=# select f1(10,20); f1 - (30,60) (1 row) postgres=# select * from f1(10,20); c | d + 30 | 60 (1 row) create or replace function foo() returns void as $$ declare r record; begin r := f1(10,20); raise warning 'c=%, d=%', r.c, r.d; end; $$ language plpgsql; CREATE FUNCTION postgres=# select foo(); WARNING: 01000: c=30, d=60 foo - (1 row) Regards Pavel Stehule Yours, Stefan -- 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] Reading an OUT parameter out of a function call
On 02/25/2013 09:22 AM, Stefan Keller wrote: Hi, I have a simple void function: CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN pnr := 1; END; $$ LANGUAGE plpgsql; How do I access myparam? I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or inside another function? You get the above to load? I get: ERROR: pnr is not a known variable LINE 4: pnr := 1; Yours, Stefan -- Adrian Klaver adrian.kla...@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
[GENERAL] Use, Set Catalog and JDBC questions
Hi, I have an application that requires to connect to each database available in PostgreSQL. I have the following questions: 1. Is there a USE DATABASE command or something of the sort (similar to MySQL) that allows you to quickly connect to a database without having to reconnect using the username,password and database again ? In Java, we are using set catalog to do this in MySQL. 2. Based on #1 above, would the latest JDBC driver support the ability to create this type of connection? Thanks, Frank Database Administrator
Re: [GENERAL] Reading an OUT parameter out of a function call
I have a simple void function: CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN pnr := 1; END; $$ LANGUAGE plpgsql; How do I access myparam? I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or inside another function? You get the above to load? I get: ERROR: pnr is not a known variable LINE 4: pnr := 1; The following all works under 9.2: CREATE OR REPLACE FUNCTION myfn(myparam OUT int, myparam2 OUT int) AS $$ BEGIN myparam := 1; myparam2 := 2; END; $$ LANGUAGE plpgsql; select myparam from myfn(); select myparam2 from myfn(); select myfn(); select * from myfn(); -- 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] Use, Set Catalog and JDBC questions
On 2/25/2013 10:22 AM, Frank Cavaliero wrote: 1. Is there a USE DATABASE command or something of the sort (similar to MySQL) that allows you to quickly connect to a database without having to reconnect using the username,password and database again ? In Java, we are using set catalog to do this in MySQL. no, there isn't.you have to connect to the database. mysql's databases are in many ways equivalent to postgres' 'schemas' within a single database (you can move between schemas with SET SEARCH_PATH=...). -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] Use, Set Catalog and JDBC questions
On 02/25/2013 10:22 AM, Frank Cavaliero wrote: Hi, I have an application that requires to connect to each database available in PostgreSQL. I have the following questions: 1. Is there a USE DATABASE command or something of the sort (similar to MySQL) that allows you to quickly connect to a database without having to reconnect using the username,password and database again ? In Java, we are using set catalog to do this in MySQL. 2. Based on #1 above, would the latest JDBC driver support the ability to create this type of connection? Not sure if this will do what want?: http://jdbc.postgresql.org/documentation/91/datasource.html#ds-intro Thanks, Frank /Database Administrator/ -- Adrian Klaver adrian.kla...@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] Reading an OUT parameter out of a function call
Thank you Keane and all. That works for me too. Yours, Stefan 2013/2/25 Russell Keane russell.ke...@inps.co.uk: I have a simple void function: CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN pnr := 1; END; $$ LANGUAGE plpgsql; How do I access myparam? I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or inside another function? You get the above to load? I get: ERROR: pnr is not a known variable LINE 4: pnr := 1; The following all works under 9.2: CREATE OR REPLACE FUNCTION myfn(myparam OUT int, myparam2 OUT int) AS $$ BEGIN myparam := 1; myparam2 := 2; END; $$ LANGUAGE plpgsql; select myparam from myfn(); select myparam2 from myfn(); select myfn(); select * from myfn(); -- 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] Use, Set Catalog and JDBC questions
Hi Adrian, Thanks for the response. The situation is more like the following: Using the JDBC driver, I connect to database TEST1 and immediately, without having to pass username credentials again, I want to use database TEST2. In MySQL, you can simply run: use TEST2. Wondering if PostgreSQL has something similar. Thanks, Frank From: Adrian Klaver adrian.kla...@gmail.com To: Frank Cavaliero/Boston/IBM@IBMUS Cc: pgsql-general@postgresql.org, pgsql-ad...@postgresql.org Date: 02/25/2013 01:48 PM Subject: Re: [GENERAL] Use, Set Catalog and JDBC questions On 02/25/2013 10:22 AM, Frank Cavaliero wrote: Hi, I have an application that requires to connect to each database available in PostgreSQL. I have the following questions: 1. Is there a USE DATABASE command or something of the sort (similar to MySQL) that allows you to quickly connect to a database without having to reconnect using the username,password and database again ? In Java, we are using set catalog to do this in MySQL. 2. Based on #1 above, would the latest JDBC driver support the ability to create this type of connection? Not sure if this will do what want?: http://jdbc.postgresql.org/documentation/91/datasource.html#ds-intro Thanks, Frank /Database Administrator/ -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Use, Set Catalog and JDBC questions
Hi John, Thanks for the response. I will look into that as an option. Thanks, Frank From: John R Pierce pie...@hogranch.com To: pgsql-general@postgresql.org Date: 02/25/2013 01:33 PM Subject: Re: [GENERAL] Use, Set Catalog and JDBC questions Sent by: pgsql-general-ow...@postgresql.org On 2/25/2013 10:22 AM, Frank Cavaliero wrote: 1. Is there a USE DATABASE command or something of the sort (similar to MySQL) that allows you to quickly connect to a database without having to reconnect using the username,password and database again ? In Java, we are using set catalog to do this in MySQL. no, there isn't.you have to connect to the database. mysql's databases are in many ways equivalent to postgres' 'schemas' within a single database (you can move between schemas with SET SEARCH_PATH=...). -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] Use, Set Catalog and JDBC questions
On 2/25/2013 10:34 AM, Adrian Klaver wrote: Not sure if this will do what want?: http://jdbc.postgresql.org/documentation/91/datasource.html#ds-intro a connection pool is something completely different. pools are used when you have many client threads connecting and disconnecting to the same database, you can use a pool to reduce the actual number of connections by sharing a pool of ready-made connections. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Use, Set Catalog and JDBC questions
On 02/25/2013 10:57 AM, Frank Cavaliero wrote: Hi Adrian, Thanks for the response. The situation is more like the following: Using the JDBC driver, I connect to database TEST1 and immediately, without having to pass username credentials again, I want to use database TEST2. In MySQL, you can simply run: use TEST2. Wondering if PostgreSQL has something similar. You can do it in the psql client like this, though that will not help with JDBC: aklaver@ford:~$ psql -d test -U postgres psql (9.0.5) Type help for help. test=# \c production You are now connected to database production. production=# I do not use the JDBC driver much, but from what I read in the link I sent you, you can set up a non-pooling DataSource to which you can add predefined datasources and then switch as needed. Thanks, Frank -- Adrian Klaver adrian.kla...@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] Use, Set Catalog and JDBC questions
On 02/25/2013 10:59 AM, John R Pierce wrote: On 2/25/2013 10:34 AM, Adrian Klaver wrote: Not sure if this will do what want?: http://jdbc.postgresql.org/documentation/91/datasource.html#ds-intro a connection pool is something completely different. pools are used when you have many client threads connecting and disconnecting to the same database, you can use a pool to reduce the actual number of connections by sharing a pool of ready-made connections. From here: http://jdbc.postgresql.org/documentation/91/ds-ds.html Table 11.2. DataSource Implementations Pooling Implementation Class No org.postgresql.ds.PGSimpleDataSource Yes org.postgresql.ds.PGPoolingDataSource There seems to a no pooling version that allows you to aggregate datasources. -- Adrian Klaver adrian.kla...@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: [ADMIN] [GENERAL] Use, Set Catalog and JDBC questions
On Mon, Feb 25, 2013 at 01:57:11PM -0500, Frank Cavaliero wrote: Hi Adrian, Thanks for the response. The situation is more like the following: Using the JDBC driver, I connect to database TEST1 and immediately, without having to pass username credentials again, I want to use database TEST2. In MySQL, you can simply run: use TEST2. Wondering if PostgreSQL has something similar. Thanks, Frank Hi Frank, The USE xxx;' is a non-standard MySQL extension to the SQL language. This functionality is not available at the SQL layer and must be supported by the connection application itself, I think. For example, you can use \c xxx in psql to perform that function. If JDBC does not support it natively, you would need to open a new connection to the new database. Regards, Ken -- 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] Use, Set Catalog and JDBC questions
On 2/25/2013 11:04 AM, Adrian Klaver wrote: test=# \c production You are now connected to database production. and the \c metacommand in psql disconnects(closes) the current database and connects to the new one, using the same credentials as originally provided, unless you specify otherwise. not useful to the OP's problem. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Use, Set Catalog and JDBC questions
Hi Adrian, Thanks a lot!I will certainly look into the multiple datasources as an option. -Frank From: Adrian Klaver adrian.kla...@gmail.com To: Frank Cavaliero/Boston/IBM@IBMUS Cc: pgsql-ad...@postgresql.org, pgsql-general@postgresql.org Date: 02/25/2013 02:16 PM Subject: Re: [GENERAL] Use, Set Catalog and JDBC questions On 02/25/2013 10:57 AM, Frank Cavaliero wrote: Hi Adrian, Thanks for the response. The situation is more like the following: Using the JDBC driver, I connect to database TEST1 and immediately, without having to pass username credentials again, I want to use database TEST2. In MySQL, you can simply run: use TEST2. Wondering if PostgreSQL has something similar. You can do it in the psql client like this, though that will not help with JDBC: aklaver@ford:~$ psql -d test -U postgres psql (9.0.5) Type help for help. test=# \c production You are now connected to database production. production=# I do not use the JDBC driver much, but from what I read in the link I sent you, you can set up a non-pooling DataSource to which you can add predefined datasources and then switch as needed. Thanks, Frank -- Adrian Klaver adrian.kla...@gmail.com
[GENERAL] JDBC not returning update count from updateable view
Hi,
We have a table which is inserted to and update via a view (using rules /
functions).
We are trying to update this from JDBC but the view update command (on the java
side) doesn't return the count of rows updated. I assume this is because the
postgres update function actually returns a tuple rather than a single count.
Any ideas?
A simplified version of the java bit:
JdbcTemplate is: org.springframework.jdbc.core.JdbcTemplate;
public final int updateTest(final String updateSQL, final Object[] args) {
JdbcTemplate template = createJdbcTemplate();
return template.update(updateSQL, args);
}
And the postgres object creation (again simplified):
--PG START
drop table if exists msg_table cascade;
drop sequence if exists msg_seq;
drop sequence if exists msg_aud_seq;
create sequence msg_seq;
create sequence msg_aud_seq;
CREATE TABLE msg_table
(
aud_seq int default nextval('msg_aud_seq'),
status int default 1,
id int default nextval('msg_seq'),
val int
);
create or replace view msg as
select
aud_seq,
id,
status,
val
from msg_table;
-- audit the original record
CREATE OR REPLACE FUNCTION audit_original_record(msg) RETURNS void AS $$
BEGIN
UPDATE msg_table
SET
status = 2
WHERE
aud_seq = $1.aud_seq;
END;
$$ LANGUAGE plpgsql;
-- insert function
CREATE OR REPLACE FUNCTION process_insert(msg) RETURNS integer AS $body$
declare new_id integer;
BEGIN
INSERT INTO msg_table
(
val
)
SELECT
$1.val
RETURNING id INTO new_id;
return new_id;
END;
$body$ LANGUAGE plpgsql;
-- update function
CREATE OR REPLACE FUNCTION process_update(msg, msg) RETURNS void AS $body$
BEGIN
INSERT INTO msg_table
(
id,
val
)
SELECT
$1.id,
$1.val;
EXECUTE audit_original_record($2);
END;
$body$ LANGUAGE plpgsql;
-- insert to msg
create or replace rule msg__rule_ins as on insert to msg
do instead
SELECT process_insert(NEW);
-- update to msg
create or replace rule msg__rule_upd as on update to msg
do instead
SELECT
COUNT(process_update(NEW, OLD))
WHERE
NEW.status = 1;
alter sequence msg_seq restart 1;
alter sequence msg_aud_seq restart 1;
delete from msg_table;
insert into msg
(val)
values
(1),
(2),
(66);
select * from msg;
update msg
set val = 5
where id = 1;
select * from msg;
--PG END
Thanks for any help you can give me.
Regards,
Russell Keane
INPS
Follow ushttps://twitter.com/INPSnews on twitter | visit
www.inps.co.ukhttp://www.inps.co.uk/
Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.uk
The information in this internet email is confidential and is intended solely
for the addressee. Access, copying or re-use of information in it by anyone
else is not authorised. Any views or opinions presented are solely those of the
author and do not necessarily represent those of INPS or any of its affiliates.
If you are not the intended recipient please contact is.helpd...@inps.co.uk
[GENERAL] Windows build question
Hi everyone, Apologies if this is answered elsewhere (quick search didn't find anything). I have noticed that MSVC 2005 is supported for 32-bit Postgres server compilation but not 64-bit. Are there known issues limiting this, or just nobody has worked on it yet? Thanks, Shawn -- 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] Use, Set Catalog and JDBC questions
On 02/25/2013 11:21 AM, Frank Cavaliero wrote: Hi Adrian, Thanks a lot!I will certainly look into the multiple datasources as an option. Just remember, as John pointed out, a MySQL database and a Postgres database are not equivalent. You will not be able to do cross database operations(with the core tools). If you want that then you will need to use Postgres schemas instead. There is work going forward on a Postgres foreign data wrapper(http://www.postgresql.org/docs/devel/static/postgres-fdw.html) that will allow cross database operations, but it is only in the development code and is very basic at this point. There is also the dblink module(http://www.postgresql.org/docs/9.2/static/dblink.html). -Frank -- Adrian Klaver adrian.kla...@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] Floating point error
Tom Duffey tduf...@trillitech.com wrote:
CREATE TABLE test (
id INTEGER PRIMARY KEY,
value REAL NOT NULL
);
INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
SELECT * FROM test;
id | value
+-
1 | 10.3885
2 | 10.3885
(2 rows)
At this point you would think you have two equal values.
At this point, try this:
select * from test where value = '10.3885';
id | value
+-
2 | 10.3885
(1 row)
Now try this:
select * from test where value = 10.3885;
id | value
+---
(0 rows)
Always remember that floating point types are *approximate* data
types; equality often does not behave as you might expect. You're
probably aware of the below issues, but just in case:
select ''::real = (''::real + '1'::real);
?column?
--
t
(1 row)
select '.1'::real::float;
float8
---
0.10001490116
(1 row)
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
