[GENERAL] Prepared Statements and Pooling
I'm working on a project for school that requires a simple java server (a ServerSocket creates Socket connections which I send JSON strings over) which then communicates with a database. Obviously I have chosen PostgreSQL. I'm using Apache DBCP for connection pooling. The DataSource is handed off to each thread. Each thread then makes a Connection and then creates statements, executes them, and then closes. I'm using prepared statements to prevent SQL injection, but I have done some reading and it seems like using Prepared statements COULD improve performance. I understand the concept, but I do not know how to implement this. JDBC has PreparedStatements. Do these correspond with the Prepared Statements at the database level in postgres? I read about the postgres prepared statements that they only work while a connection is kept open. But in my application, each thread makes it's own connection and then closes it. So I wouldn't be able to capitalize on the prepared statement. However, it has occurred to me that since I am using DBCP to pool connections, the Java Connection that I create is actually a connection that is being kept open by the pool. Does the DBCP system (poorly documented) manage the prepared statements? I have considered opening a single connection to the server and letting all threads execute statements through it, and use prepared statements (serviced by postgresql, not JDBC or DBCP). Or simply opening 50 connections and establishing prepared statements and then handing them off to the threads. Is there a reason I shouldn't do this? I read that the JDBC driver for postgres is thread safe. What's really frustrating is that I can't find anything to read about this. I set up DBCP, but how can I be sure my connection pool is working. How can I tell if prepared statements are really functioning by executing a prepared statement, as opposed to parsing and planning each time I run a query? Where can I learn more about this? Any direct answers are also very appreciated.
Re: [GENERAL] conn = PQconnectdb(conninfo);
Peter Kroon wrote: >>> I've found perhaps a bug. >>> I've narrowed down my code and the problem is indeed at: conn = >>> PQconnectdb(conninfo); >>> >>> My connection string: host=192.168.178.12 dbname=DATABASE user=foo >>> password=bar >>> >>> When I remove key/value host=xxx then everything is OK. Valgrind mentions: >>> no leaks are possible. >>> >>> When key/value host=xxx is added, not everything is freed and there are >>> tons of bytes still reachable. >>> >>> >>> ==9195== >>> ==9195== HEAP SUMMARY: >>> ==9195== in use at exit: 450,080 bytes in 2,829 blocks >>> ==9195== total heap usage: 9,476 allocs, 6,647 frees, 7,810,733 bytes >>> allocated >>> ==9195== >>> ==9195== LEAK SUMMARY: >>> ==9195==definitely lost: 0 bytes in 0 blocks >>> ==9195==indirectly lost: 0 bytes in 0 blocks >>> ==9195== possibly lost: 0 bytes in 0 blocks >>> ==9195==still reachable: 450,080 bytes in 2,829 blocks >>> ==9195== suppressed: 0 bytes in 0 blocks >>> ==9195== Rerun with --leak-check=full to see details of leaked memory >>> ==9195== >>> ==9195== For counts of detected and suppressed errors, rerun with: -v >>> ==9195== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 14 from 6) >> I tried your program and I get "still reachable" only when SSL is enabled; >> all the memory is in >> OpenSSL. Dou you use SSL? >> >> Without SSL (sslmode=disable) I get no "still reachable" memory. >> >> I don't know of reachable memory is a problem, I'd suspect not. > I'm not using ssl at the moment. > > The thing is when the reachable leak is there is just grows. It is not > constant. And at a given point > the program will break. > > I've ran the program with: valgrind --leak-check=full --show-reachable=yes > --log- > file="_pg_test_debug.log" ./_pg_test_debug > > View logfile here: http://pastebin.com/7rjBRbkD > SSL is mentioned in combination with pg objects > > Your suggestion: sslmode=disable seems to have fix my issue.. I looked into this some more, an this is expected behaviour. If you do not use sslmode=disable or sslmode=allow, PostgreSQL will first try to establish an SSL connection. This requires that the SSL library be initialized (a call to OPENSSL_config()). This is done only once and will allocate some memory that will never be deallocated. That should not be a problem, and the memory leak should not increase if more than one connection is opened. Since your code starts several threads, I believe than there is a slim chance that due to race conditions, the memory is allocated more than once. If you want more control over that, you can explicitly initialize and destroy this memory, see http://www.postgresql.org/docs/9.4/static/libpq-ssl.html#LIBPQ-SSL-INITIALIZE Sample code (untested) would look like that: #include [...] /* initialize SSL library */ OPENSSL_config(); /* tell PostgreSQL about it */ PQinitOpenSSL(0, 1); [start threads, open database connections, do some database work, close connections] /* free SSL memory */ CONF_modules_free(); 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] Postgresql 9.4.4: ERROR: bigint out of range
On 08/10/2015 06:23 AM, Arthur Chan wrote: Hi, I have installed 9.4.4 and repmgr 3.0.1 (installed by using "yum install repmgr"). From the system log, I found many errors with same type: ERROR: bigint out of range STATEMENT: INSERT INTO "repmgr_pg_cluster".repl_monitor (primary_node, standby_node, last_monitor_time, last_apply_time, last_wal_primary_location, last_wal_standby_location, replication_lag, apply_lag ) VALUES(1, 2, '2015-07-31 13:18:24.91771+08'::TIMESTAMP WITH TIME ZONE, '2015-07-31 13:18:22.73631+08'::TIMESTAMP WITH TIME ZONE, '5F/7400', '5F/737C', 8650752, 18446744073700900864) ERROR: bigint out of range .. FYI, the schema definition for repmgr_pg_cluster".repl_monitor would be helpful. http://www.postgresql.org/docs/9.4/interactive/datatype-numeric.html#DATATYPE-INT bigint 8 bytes large-range integer -9223372036854775808 to +9223372036854775807 18446744073700900864 9223372036854775807 The value being entered for apply_lag is too big for the field, Any idea how to resolve it? Find out what is generating such a big number for apply_lag. I doubt that is the real number. Probably something to ask the repmgr folks. Regards Arthur -- Adrian Klaver adrian.kla...@aklaver.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] Postgresql 9.4.4: ERROR: bigint out of range
Hi, I have installed 9.4.4 and repmgr 3.0.1 (installed by using "yum install repmgr"). From the system log, I found many errors with same type: ERROR: bigint out of range STATEMENT: INSERT INTO "repmgr_pg_cluster".repl_monitor (primary_node, standby_node, last_monitor_time, last_apply_time, last_wal_primary_location, last_wal_standby_location, replication_lag, apply_lag ) VALUES(1, 2, '2015-07-31 13:18:24.91771+08'::TIMESTAMP WITH TIME ZONE, '2015-07-31 13:18:22.73631+08'::TIMESTAMP WITH TIME ZONE, '5F/7400', '5F/737C', 8650752, 18446744073700900864) ERROR: bigint out of range .. Any idea how to resolve it? Regards Arthur
Re: [GENERAL] postgres connection
El 07/08/15 a las 07:31, Ramesh T escribió: > FATAL: syntax error in file "recovery.conf" line 2, near token "config" > < 2015-08-07 05:21:13.086 EDT >LOG: startup process (PID 6129) exited with > exit code 1 > < 2015-08-07 05:21:13.086 EDT >LOG: aborting startup due to startup > process failure What did you have on line 2 of recovery.conf (I see that this might just be a standby). > in pg_log > > I changed in Recovery.conf > > restore_command = 'cp /test/pgsql/pg_log_archive/%f %p' Is this line 2? What was it set to before? > bash-4.1$ /etc/init.d/postgresql-9.3 start > > Starting postgresql-9.3 service: [FAILED] > > bash-4.1$pg_ctl start > Starting postgresql-9.3 service: [FAILED] > bash-4.1$service postgres-9.3 start > Starting postgresql-9.3 service: [FAILED] Well, evidently your postgres server is not starting, hence those [FAILED] signs. > when i try to conect server from pgadmin3 > return error like >could not connect to the server: connection refused on host > 2.3.421.1 and accepting tcp/ip connection on port 5432. Is 2.3.421.1 the server from above in this mail? It's obviously not running. > any help to start server.. Logs from just after you tried to start the server. The mysterious line 2 of recovery.conf. Network parameters from postgresql.conf. Those 3 things could help. Regards, -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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_start_backup: file has vanished from pg_subtrans/
Op 08/08/2015 om 14:24:00 -0400, schreef David Steele: > On 8/7/15 7:01 AM, Leo Baltus wrote: > >While backing up some postgresql-8.4.2 instances using > >pg_start_backup()/pg_stop_backup() > > > >We noticed that sometimes rsync complains: > > > >file has vanished: "./data/pg_subtrans/3A37" > >rsync warning: some files vanished before they could be transferred (code > >24) at main.c(1039) [sender=3.0.6 > > It's perfectly normal for Postgres to remove files during a backup. If a > table is dropped then the files storing that table will be deleted. Files > can also be removed from pg_subtrans once they are no longer needed. > > >We already have excluded pgsql_tmp/ pg_xlog/ and pg_stat_tmp/ so I was > >tempted to exclude pg_subtrans/ as well. However some googling seems to > >suggest that this might invalidate the backup: > > I would recommend only excluding pg_xlog as recommended in the Postgres > docs. > Check. > >https://groups.google.com/forum/#!msg/pgbarman/zQxg6pOXkZ8/AmTmVtUA3dEJ > > > >I am not sure what to believe, either > > - my backup was invalid and I have to take measures to prevent this > >(and file a bug report) > > Your backup should be good so long as you've captured all the WAL. > > > - I can ignore this *and* can safely exclude pg_trans/ from my backups. > > - there is some other workaround... > > Do *not* exclude pg_subtrans. Ok, for now I will ignore rsync's exit code 24. > > You may also want to look at backup software such as pgBackRest. It > understands that files can be removed during backup and does not emit > warnings when it happens (though info messages are still logged depending on > your log level). Thanks! -- Leo Baltus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
