Re: [GENERAL] JDBC prepared statement is not treated as prepared statement
Hello: Thanks to Laurenz. Your information is very helpful for me. I change my Java program by adding the following: org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst; pgt.setPrepareThreshold(1); I can see an entry is in pg_prepared_statements now. But the hyperlink's documentation made me a little confused. I also wonder why the threshold option is designed . The document said: - The PostgreSQL™ server allows clients to compile sql statements that are expected to be reused to avoid the overhead of parsing and planning the statement for every execution. This functionality is available at the SQL level via PREPARE and EXECUTE beginning with server version 7.3 … An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements. … --- What does < clients to compile sql statements > mean? I think that maybe the document just want to say: - Before PG import support for prepared statement, PG server must parse and plan statement every time when the client send a request. Even when the same statement will be executed many times. After PG import support for prepared statement, When using those statement which is expected reused, by using prepared statement mechanism, PG server can avoid overhead of parsing and planning again and again. But in order to use prepared statement, The client also must do something: When using psql, we need to use Prepare command When using java, we use java.sql.preparedstatement, but it is not engouth: we also need to use org.postgresql.PGStatement 's setthreshold method to let PG server know. The client must do something to let PG server realize that client want PG server to use prepared statement. That is why the docmument say "clients to compile sql statements". And for the threshold, If the threshold has not reached, PG server will consider the sql statement a common one, and will parse and plan for it every time. Only when the threshold is reached, PG server will realize that client need it to hold the statement as prepared ,then parsed it and hold the plan. - Is my understanding right? Thanks 2013/6/17 Albe Laurenz > 高健 wrote: > > I have one question about prepared statement. > > I use Java via JDBC, then send prepared statement to execute. > > I thought that the pg_prepared_statments view will have one record > after my execution. > > But I can't find. > > > > Is the JDBC's prepared statement differ from SQL execute by prepare > command ? > > http://www.postgresql.org/docs/current/static/sql-prepare.html > > > > My simple java program is the following: > > > > import java.sql.*; > > > > public class Test01 { > > public static void main(String argsv[]){ > > try > > { > >Class.forName("org.postgresql.Driver").newInstance(); > >String url = "jdbc:postgresql://localhost:5432/postgres" ; > >Connection con = > DriverManager.getConnection(url,"postgres","postgres" ); > >///Phase 1:-Select data from > table--- > >System.out.println("Phase 1start"); > >String strsql = " select * from customers where cust_id = ?"; > >PreparedStatement pst=con.prepareStatement(strsql); > >pst.setInt(1,3); //find the customer with cust_id of 3. > >ResultSet rs = pst.executeQuery(); > >while (rs.next()) > > { > >System.out.print("cust_id:"+rs.getInt( "cust_id")); > >System.out.println("...cust_name:"+rs.getString( > "cust_name" )); > >} > > > >System.out.println("Phase 1end\n"); > > > > > > > >///Phase 2:-Use connection again,to select data > from data dictionary--- > > > >System.out.println("Phase 2start"); > >strsql = "select * from pg_prepared_statements"; > >pst=con.prepareStatement(strsql); > >rs = pst.executeQuery(); > >while (rs.next()) > >{ > > System.out.println("statement:"+rs.getString( > "statement")); > >} > >System.out.println("Phase 2end\n"); > > > > > > > >///Phase 3:-Use connection again,to select data > from table- > > -- > >System.out.println("Phase 3start"); > >strsql = "select * from customers"; > >
Re: [GENERAL] Getting permission denied after grant
El 17/06/13 17:08, François Beausoleil escribió: I have a problem granting permissions. The end result I'm looking for is: Dustin and Pablo are data analysts. When either creates a table, the table must be created outside of public, and both must be able to delete the table when their work is finished. I would prefer that the tables they create be owned by the dataanalysts role, but that's not required. They should have read-only access to all tables in public. If a new table is created in public, they should automatically receive read-only access. Here's my implementation of the requirements: -- Create both users CREATE USER dustin WITH LOGIN; CREATE USER pablo WITH LOGIN; -- Both belong to the same role/group CREATE USER dataanalysts WITH NOLOGIN; GRANT dataanalysts TO pablo; GRANT dataanalysts TO dustin; -- Common schema for both CREATE SCHEMA dataanalysts; ALTER SCHEMA dataanalysts SET OWNER TO dataanalysts; Wrong syntax: ALTER SCHEMA dataanalysts OWNER TO dataanalysts; No SET there. -- Whenever a data analyst creates a table, prefer the dataanalysts schema ALTER USER pablo SET search_path = dataanalysts, public; ALTER USER dustin SET search_path = dataanalysts, public; -- When pablo creates a table, allow any data analyst to query / update / delete the table ALTER DEFAULT PRIVILEGES FOR USER pablo IN SCHEMA dataanalysts GRANT ALL PRIVILEGES ON TABLES TO dataanalysts; -- When dustin creates a table, allow any data analyst to query / update / delete the table ALTER DEFAULT PRIVILEGES FOR USER dustin IN SCHEMA dataanalysts GRANT ALL PRIVILEGES ON TABLES TO dataanalysts; Here you change the default privileges for user pablo and dustin, but... And the default privileges in this database are: svanalytics_production=# \ddp Default access privileges Owner |Schema| Type | Access privileges --+--+---+--- dataanalysts | dataanalysts | table | dataanalysts=arwdDxt/dataanalysts svanalytics | public | table | dataanalysts=r/svanalytics I believe the first line means "if a data analyst creates a table, grant all privileges to dataanalysts". The 2nd line means "when svanalytics creates a table in public, grant select to dataanalysts". Which are the defaults for pablo and dustin? If the ALTER DEFAULT PRIVILEGES would have passed, you would see one line for each the two users you created: # \ddp Default access privileges Owner |Schema| Type | Access privileges +--+---+- dustin | dataanalysts | table | dataanalysts=arwdDxt/dustin pablo | dataanalysts | table | dataanalysts=arwdDxt/pablo This is the output I see after executing the DDL from above. Did I miss anything? What did I do wrong? Why can't a dataanalyst view a table's contents? Not sure. Looks like ALTER DEFAULT PRIVILEGES didn't pass for some reason. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade only to 9.0 ?
On Mon, Jun 17, 2013 at 10:44:44AM -0700, jmfox180 wrote: > hello i'm having just a similiar problem, could you tell me what part of the > instructions you were missing? > > im trying to upgrade from 8.3 to 9.2.4 but i get this error: > > "this utility can only upgrade to postgresql version 9.2." You have to use the pg_upgrade version that matches the new server version. I am confused by your report if you are upgrading to 9.0 or 9.2. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade only to 9.0 ?
hello i'm having just a similiar problem, could you tell me what part of the instructions you were missing? im trying to upgrade from 8.3 to 9.2.4 but i get this error: "this utility can only upgrade to postgresql version 9.2." -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-upgrade-only-to-9-0-tp4383292p5759508.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] CASE Statement - Order of expression processing
On 2013-06-17 22:17 +02:00, Andrea Lombardoni wrote: I observed the following behaviour (I tested the following statements in 9.0.4, 9.0.5 and 9.3beta1): $ psql template1 template1=# SELECT CASE WHEN 0=0 THEN 0 ELSE 1/0 END; case -- 0 (1 row) template1=# SELECT CASE WHEN 1=0 THEN 0 ELSE 1/0 END; ERROR: division by zero In this case the CASE behaves as expected. But in the following expression: template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END; ERROR: division by zero (Just to be sure, a "SELECT (SELECT 0)=0;" returns true) It seems that when the "CASE WHEN expression" is a query, the evaluation order changes. According to the documentation, this behaviour is wrong. http://www.postgresql.org/docs/9.0/static/sql-expressions.html (4.2.13. Expression Evaluation Rules): "When it is essential to force evaluation order, a CASE construct (see Section 9.16) can be used. " http://www.postgresql.org/docs/9.0/static/functions-conditional.html (9.16.1. CASE): "If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed." "A CASE expression does not evaluate any subexpressions that are not needed to determine the result." Did I miss anything? Or is this really a bug? with psql v9.2.4: pg924=# SELECT CASE WHEN (SELECT 0) = 0 THEN 0 END; case -- 0 (1 row) is like documented. pg924=# SELECT CASE WHEN (SELECT 0) != 0 THEN 0 END; case -- (1 row) also like documented "If no match is found, the result of the ELSE clause (or a null value) is returned." pg924=# SELECT CASE WHEN (SELECT 0) != 0 THEN 0 ELSE 1 END; case -- 1 (1 row) also ok, now it returns the result of the ELSE clause. So maybe "The data types of all the result expressions must be convertible to a single output type. See Section 10.5 for more details." The checking of convertibility is eagerly tried in case there is a SELECT expression to be evaluated in the condition? A simple arithmetic expression does not trigger this: pg924=# SELECT CASE WHEN (0+0) != 0 THEN 1/0 ELSE 1 END; case -- 1 (1 row) Now is a subquery "(SELECT 1) != 1" a valid expression for a condition :-?) or does it trigger some unwanted checking: pg924=# SELECT CASE WHEN (SELECT 1) != 1 THEN 1/0 END; ERROR: division by zero A subquery inside a "matched" ELSE clause (e.g.) does not trigger evaluation of the 1/0 inside the unmatched WHEN clause: pg924=# SELECT CASE WHEN 1 != 1 THEN 1/0 ELSE ((SELECT 1)=1)::integer END; case -- 1 (1 row) here the 1/0 is happily ignored. So it's us two already with a blind spot, or it's a bug. All the best, 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] PSA: If you are running Precise/12.04 upgrade your kernel.
On 06/17/2013 01:34 PM, Stuart Bishop wrote: I've since heard that 3.4 also fixes this issue as well. What are you using for your IO on these boxes? I was able to demonstrate it over iSCSI to a Nimble Storage SAN as well as DAS with 2 drive RAID 1 for xlogs and 8 drive RAID 10 for data (DL385 G7). This might sound familiar: http://postgresql.1045698.n5.nabble.com/Ubuntu-12-04-3-2-Kernel-Bad-for-PostgreSQL-Performance-td5735284.html tl;dr for that thread seems to be a driver problem (fusionIO?), I'm unsure if Ubuntu specific or in the upstream kernel. If it is a driver problem, then two different drivers were buggy the Nimble Storage San driver (iSCSI) as well as the DL385 DAS (LSI). Anyway the upgrade to 3.9 makes the problem disappear. There are other insights in the comments of the blog post. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- 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] PSA: If you are running Precise/12.04 upgrade your kernel.
On Fri, Jun 7, 2013 at 5:51 AM, Joshua D. Drake wrote: > > On 06/06/2013 03:48 PM, Scott Marlowe wrote: >> >> >> On Thu, Jun 6, 2013 at 4:35 PM, Joshua D. Drake >> wrote: >>> >>> I had the distinct displeasure of staying up entirely too late with a >>> customer this week because they upgraded to 12.04 and immediately >>> experienced a huge performance regression. In the process they also >>> upgraded >>> to PostgreSQL 9.1 from 8.4. There were a lot of knobs to >>> change/fix/modify >>> because of this. However, nothing I did fixed the problem. Until... I >>> upgraded the kernel. >>> >>> Upgrading from 3.2Precise to the 3.9.4 kernel produced the following >>> results: >> >> >> I've since heard that 3.4 also fixes this issue as well. >> >> What are you using for your IO on these boxes? > > I was able to demonstrate it over iSCSI to a Nimble Storage SAN as well as > DAS with 2 drive RAID 1 for xlogs and 8 drive RAID 10 for data (DL385 G7). This might sound familiar: http://postgresql.1045698.n5.nabble.com/Ubuntu-12-04-3-2-Kernel-Bad-for-PostgreSQL-Performance-td5735284.html tl;dr for that thread seems to be a driver problem (fusionIO?), I'm unsure if Ubuntu specific or in the upstream kernel. -- Stuart Bishop http://www.stuartbishop.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] CASE Statement - Order of expression processing
I observed the following behaviour (I tested the following statements in 9.0.4, 9.0.5 and 9.3beta1): $ psql template1 template1=# SELECT CASE WHEN 0=0 THEN 0 ELSE 1/0 END; case -- 0 (1 row) template1=# SELECT CASE WHEN 1=0 THEN 0 ELSE 1/0 END; ERROR: division by zero In this case the CASE behaves as expected. But in the following expression: template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END; ERROR: division by zero (Just to be sure, a "SELECT (SELECT 0)=0;" returns true) It seems that when the "CASE WHEN expression" is a query, the evaluation order changes. According to the documentation, this behaviour is wrong. http://www.postgresql.org/docs/9.0/static/sql-expressions.html (4.2.13. Expression Evaluation Rules): "When it is essential to force evaluation order, a CASE construct (see Section 9.16) can be used. " http://www.postgresql.org/docs/9.0/static/functions-conditional.html(9.16.1. CASE): "If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed." "A CASE expression does not evaluate any subexpressions that are not needed to determine the result." Did I miss anything? Or is this really a bug? Thanks, Andrea Lombardoni
[GENERAL] Getting permission denied after grant
I have a problem granting permissions. The end result I'm looking for is: Dustin and Pablo are data analysts. When either creates a table, the table must be created outside of public, and both must be able to delete the table when their work is finished. I would prefer that the tables they create be owned by the dataanalysts role, but that's not required. They should have read-only access to all tables in public. If a new table is created in public, they should automatically receive read-only access. Here's my implementation of the requirements: -- Create both users CREATE USER dustin WITH LOGIN; CREATE USER pablo WITH LOGIN; -- Both belong to the same role/group CREATE USER dataanalysts WITH NOLOGIN; GRANT dataanalysts TO pablo; GRANT dataanalysts TO dustin; -- Common schema for both CREATE SCHEMA dataanalysts; ALTER SCHEMA dataanalysts SET OWNER TO dataanalysts; -- Whenever a data analyst creates a table, prefer the dataanalysts schema ALTER USER pablo SET search_path = dataanalysts, public; ALTER USER dustin SET search_path = dataanalysts, public; -- When pablo creates a table, allow any data analyst to query / update / delete the table ALTER DEFAULT PRIVILEGES FOR USER pablo IN SCHEMA dataanalysts GRANT ALL PRIVILEGES ON TABLES TO dataanalysts; -- When dustin creates a table, allow any data analyst to query / update / delete the table ALTER DEFAULT PRIVILEGES FOR USER dustin IN SCHEMA dataanalysts GRANT ALL PRIVILEGES ON TABLES TO dataanalysts; -- Existing tables in public are read-only for all dataanalysts GRANT SELECT ON ALL TABLES IN SCHEMA public TO dataanalysts; -- There were already existing tables in schema dataanalysts, so grant everything to all data analysts GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA dataanalysts TO dataanalysts; Now, the problem is whenever dustin or pablo connect, they don't seem to have usage privilege on schema dataanalysts: $ psql -U pablo svanalytics_production psql (9.1.9) Type "help" for help. svanalytics_production => select count(*) from dataanalysts."CFM"; ERROR: permission denied for schema dataanalysts LINE 1: select count(*) from dataanalysts."CFM"; Logging in as the DB superuser, I can list the permissions on the schema: List of schemas Name|Owner | Access privileges | Description +--+--+-- dataanalysts | dataanalysts | dataanalysts=UC/dataanalysts | public | postgres | postgres=UC/postgres+| standard public schema According to my understanding, UC means: USAGE and CREATE privileges are granted to dataanalysts. They can list the contents of the schema, and the schema of the tables, but can't access the data. As the DB superuser, checking privileges on CFM says: svanalytics_production=# \dp dataanalysts."CFM" Access privileges Schema| Name | Type | Access privileges | Column access privileges --+--+---+--+-- dataanalysts | CFM | table | dataanalysts=arwdDxt/dataanalysts +| | | | svanbatch=arwdDxt/dataanalysts +| | | | svaninteractive=arwdDxt/dataanalysts+| | | | svaninject=r/dataanalysts| Which again means to me "dataanalysts have all privileges", and dustin and pablo are part of dataanalysts, as evidenced here: svanalytics_production=# \dg List of roles Role name| Attributes | Member of -++ dataanalysts| Cannot login | {} dustin || {dataanalysts} pablo || {dataanalysts} postgres| Superuser, Create role, Create DB, Replication | {} And the default privileges in this database are: svanalytics_production=# \ddp Default access privileges Owner |Schema| Type | Access privileges --+--+---+--- dataanalysts | dataanalysts | table | dataanalysts=arwdDxt/dataanalysts svanalytics | public | table | dataanalysts=r/svanalytics I believe the first line means "if a data analyst creates a table, grant all privileges to dataanalysts". The 2nd line means "when svanalytics creates a table in public, grant select to dataanalysts". Did I miss anything? What did I do wrong? Why can't a dataanalyst view a table's contents? Thanks, François Beausoleil smime.p7s Description: S/MIME cryptographic signature
[GENERAL] UTC houroffset -> days_start AT TIME ZONE x
Hello, I have time columns, whereas the time ist stored as houroffset in epoch. e.g 36089 => select '19700101 00:01:00 GMT'::timestamptz + interval '360089' hours' => 2011-01-29 18:01:00+01 Now I want an aggregation that sum my values on the day start in a given time zone. The function below works, but is slow. Any way to build an equivalent function with better performances ? Thanks, Marc Mamin CREATE FUNCTION houroffset_to_daystart (p_houroffset int, p_tz varchar) returns int AS $$ DECLARE daystart int; BEGIN EXECUTE 'select EXTRACT (''epoch'' FROM date_trunc(''day'',(''19700101 00:01:00 GMT''::timestamptz + interval '''||p_houroffset||' hours'') AT TIME ZONE '''||p_tz||''') )/3600' INTO daystart; RETURN daystart; END; $$ LANGUAGE plpgsql IMMUTABLE;
[GENERAL] Better dual WAL shipping/streaming integration?
Hi. I currently have a number of servers using both streaming replication and WAL shipping in the standard setup. Occasionally, the primaries get large load spikes. Load climbs up, things slow down. So much so that streaming replication starts lagging because the WAL sender is being starved for resources. Eventually, the slave disconnects. At which point it happily starts consuming the shipped files and catches up again. All this is working just fine and as designed (apart from the load spikes, but that is my problem). However, I was thinking that it would be better streaming replication did not attempt to pull down WAL files that had already been shipped and were available locally. This would save resources when you need them most - the hot standbys have fallen behind. -- Stuart Bishop http://www.stuartbishop.net/ -- 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] WIN1251 localization
On Sat, Jun 15, 2013 at 11:04:28PM +0400, Yuriy Rusinov wrote: > Postgres 9.1 > > utf-8 Is this the system locale or the pgcluster encoding? What is the output of the pg_lsclusters? Consider the utf8 convertion of those initial scripts if this is not an option, then just create new cluster with cp1251 encoding. Regards -- Petko Godev -- 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] JDBC prepared statement is not treated as prepared statement
高健 wrote: > I have one question about prepared statement. > I use Java via JDBC, then send prepared statement to execute. > I thought that the pg_prepared_statments view will have one record after my > execution. > But I can't find. > > Is the JDBC's prepared statement differ from SQL execute by prepare command > ? > http://www.postgresql.org/docs/current/static/sql-prepare.html > > My simple java program is the following: > > import java.sql.*; > > public class Test01 { > public static void main(String argsv[]){ > try > { >Class.forName("org.postgresql.Driver").newInstance(); >String url = "jdbc:postgresql://localhost:5432/postgres" ; >Connection con = > DriverManager.getConnection(url,"postgres","postgres" ); >///Phase 1:-Select data from > table--- >System.out.println("Phase 1start"); >String strsql = " select * from customers where cust_id = ?"; >PreparedStatement pst=con.prepareStatement(strsql); >pst.setInt(1,3); //find the customer with cust_id of 3. >ResultSet rs = pst.executeQuery(); >while (rs.next()) > { >System.out.print("cust_id:"+rs.getInt( "cust_id")); >System.out.println("...cust_name:"+rs.getString( "cust_name" > )); >} > >System.out.println("Phase 1end\n"); > > > >///Phase 2:-Use connection again,to select data from > data dictionary--- > >System.out.println("Phase 2start"); >strsql = "select * from pg_prepared_statements"; >pst=con.prepareStatement(strsql); >rs = pst.executeQuery(); >while (rs.next()) >{ > System.out.println("statement:"+rs.getString( "statement")); >} >System.out.println("Phase 2end\n"); > > > >///Phase 3:-Use connection again,to select data from > table- > -- >System.out.println("Phase 3start"); >strsql = "select * from customers"; >pst=con.prepareStatement(strsql); >rs = pst.executeQuery(); >while (rs.next()) >{ > System.out.print("cust_id:"+rs.getInt( "cust_id")); > System.out.println("...cust_name:"+rs.getString( "cust_name" )); > } > System.out.println("Phase 3end\n"); > rs.close(); > pst.close(); > con.close(); >} > catch (Exception ee) > { >System.out.print(ee.getMessage()); >} > } > } > > > > The result of it's execution is: > > Phase 1start > > cust_id:3...cust_name:Taylor > > Phase 1end > > > > Phase 2start > > Phase 2end > > > > Phase 3start > > cust_id:1...cust_name:Smith > > cust_id:2...cust_name:Brown > > cust_id:3...cust_name:Taylor > > Phase 3end > > > > That is to say: my prepared statement is not cached by PG? > > Then how to write a java program to made it's prepared statement realized by > PG to treat it as a > "prepared statement"? > > Thank you. See http://jdbc.postgresql.org/documentation/head/server-prepare.html Set the prepare threshold of a PreparedStatement and use the statement at least as many times. Then you should see an entry in pg_prepared_statements. In your example, no PreparedStatement is used more than once. 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