[GENERAL] searchable database

Dear Friends, I need help from you. We have more than thousand electronic journals. I want to make a searchable database for easy access. Is there any light wight database available for that. Please provide me the details for the same. If you have any questions please mail me. Looking forward t

Re: [GENERAL] .pgpass not working?

Dah, left out the port. On Fri, Apr 8, 2011 at 10:36 PM, Yang Zhang wrote: > I'm using the postgresql 8.4.7 in Ubuntu 10.04, and I'm trying to use > .pgpass documented here: > > http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html > > I have a ~/.pgpass with 600 perms containing: > >

[GENERAL] .pgpass not working?

I'm using the postgresql 8.4.7 in Ubuntu 10.04, and I'm trying to use .pgpass documented here: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html I have a ~/.pgpass with 600 perms containing: myhostname.com:yang:yang:mypassword However, it doesn't seem to get picked up by psql -h

Re: [GENERAL] Rename or Re-Create Constraints?

Carlos Mennens wrote: On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: I believe you can rename the underlying indexes and the constraints will follow them. (This works in HEAD anyway, not sure how far back.) I'm sorry but I don't understand what that means or how to relate that to

Re: [GENERAL] Rename or Re-Create Constraints?

On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: > I believe you can rename the underlying indexes and the constraints will > follow them.  (This works in HEAD anyway, not sure how far back.) I'm sorry but I don't understand what that means or how to relate that to a SQL command to rename the cons

Re: [GENERAL] Rename or Re-Create Constraints?

Carlos Mennens writes: > 1. Do I need to remove all the table constraints or is there a way to > rename them? I believe you can rename the underlying indexes and the constraints will follow them. (This works in HEAD anyway, not sure how far back.) > 2. When renaming the table, is there a way to

[GENERAL] Rename or Re-Create Constraints?

I've searched and really can't find a definitive example or someone renaming a constraint. I renamed a table yesterday and noticed that the constraint name was still named the old table name: inkpress=# ALTER TABLE accounts RENAME TO fashion; ALTER TABLE inkpress=# \d fashion Table "pu

Re: [GENERAL] Deferred foreign key constraint downsides

On Fri, 2011-04-08 at 14:08 -0500, Jack Christensen wrote: > Are there any other downsides to just setting all my foreign keys to > initially deferred? It may consume memory resources until the transaction is complete. Also, when it's possible to write the SQL in an order that always maintains t

[GENERAL] Critical fix for pg_upgrade/pg_migrator users

Critical Fix for pg_upgrade/pg_migrator Users - A bug has been discovered in all released versions of pg_upgrade and (formerly) pg_migrator. Anyone who has used pg_upgrade or pg_migrator should take the following corrective actions as so

Re: [GENERAL] Howto sort the result of UNION (without modifying its type)?

On 04/08/2011 03:44 PM, Clemens Eisserer wrote: Hi, I have a query where I UNION several select statements which return IDs of type INTEGER. This works well, if the IDs don't need to be sorted: SELECT id FROM table WHERE id IN ((select id FROM table WHERE ...) UNION (SELECT id FROM table_ WH

Re: [GENERAL] PostgreSQL backend process high memory usage issue

Merlin Moncure-2 wrote: > > On Fri, Apr 8, 2011 at 2:00 PM, Shianmiin > wrote: >> Further clarification, >> >> if I run two concurrent threads >> >> pgbench memoryusagetest -c 2 -j 2 -T180 -f test.sql >> >> both backend processes uses 1.5GB and result in 3GB in total. > > y

[GENERAL] Howto sort the result of UNION (without modifying its type)?

Hi, I have a query where I UNION several select statements which return IDs of type INTEGER. This works well, if the IDs don't need to be sorted: > SELECT id FROM table WHERE id IN ((select id FROM table WHERE ...) UNION > (SELECT id FROM table_ WHERE )) However I need the result the UNION

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Fri, Apr 8, 2011 at 2:00 PM, Shianmiin wrote: > Further clarification, > > if I run two concurrent threads > > pgbench memoryusagetest -c 2 -j 2 -T180 -f test.sql > > both backend processes uses 1.5GB and result in 3GB in total. yes. could you please post a capture of top after running the mod

Re: [GENERAL] PostgreSQL backend process high memory usage issue

Further clarification, if I run two concurrent threads pgbench memoryusagetest -c 2 -j 2 -T180 -f test.sql both backend processes uses 1.5GB and result in 3GB in total. Samuel -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-backend-process-high-memory-usag

Re: [GENERAL] PostgreSQL backend process high memory usage issue

No I didn't configured 1.5GB shared memory. For this test I recreated a database cluster and leave everything in the configuration as default. As in the original post, when the connection was first established, the memory usage of backend process showed in top was VIRT = 182MB, RES = 6240K, SHR=

Re: [GENERAL] Changed SSL Certificates

On Fri, Apr 8, 2011 at 2:01 PM, Adrian Klaver wrote: > Per here: > http://www.postgresql.org/docs/8.4/static/ssl-tcp.html > File    Contents        Effect > server.crt      server certificate      requested by client > server.key      server private key      proves server certificate sent by > own

Re: [GENERAL] Deferred foreign key constraint downsides

Jack Christensen writes: > I recently had cause to use a deferred foreign key constraint for the > first time. I like it. It seems it could make life simpler, especially > when an obstinate ORM insists on doing things in the wrong order. > > The only downside I can see is it may be harder to trac

Re: [GENERAL] comma vs cross join question

On Fri, 2011-04-08 at 14:45 -0400, Tom Lane wrote: > Jason Long writes: > > I am using 9.0.3 and the only setting I have changed is > > geqo_effort = 10 > > > One of the joins is a view join. > > Ah. The explain shows there are actually nine base tables in that > query, which is more than

[GENERAL] Deferred foreign key constraint downsides

I recently had cause to use a deferred foreign key constraint for the first time. I like it. It seems it could make life simpler, especially when an obstinate ORM insists on doing things in the wrong order. The only downside I can see is it may be harder to track down where a violation occurre

Re: [GENERAL] comma vs cross join question

Jason Long writes: > I am using 9.0.3 and the only setting I have changed is > geqo_effort = 10 > One of the joins is a view join. Ah. The explain shows there are actually nine base tables in that query, which is more than the default join_collapse_limit. Try cranking up both join_colla

Re: [GENERAL] Count for pagination

* Jason Long (ja...@octgsoftware.com) wrote: > The main search screen of my application has pagination. http://www.depesz.com/index.php/2007/08/29/better-results-paging-in-postgresql-82/ Thanks, Stephen signature.asc Description: Digital signature

[GENERAL] Count for pagination

The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. <300 ms 2. Subset of the total records for one page. a. 1-2 sec 3. Count of the tot

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Fri, Apr 8, 2011 at 10:30 AM, Shianmiin wrote: > > Shianmiin wrote: >> >> Hi Merlin, >> >> I revised the test code with attached files and use pgbench to send the >> test queries. >> >>  http://postgresql.1045698.n5.nabble.com/file/n4290723/dotest dotest >>  http://postgresql.1045698.n5.nabble.

Re: [GENERAL] PostgreSQL backend process high memory usage issue

No. The highmemory usage issueis stll there. We could change select count(*) to select * or select 1 if you like. Therre is no data in the tables anyway. Sent from my iPad On 2011-04-08, at 8:25 AM, "Merlin Moncure-2 [via PostgreSQL]" < ml-node+4290983-1196677718-196...@n5.nabble.com> wrote: On

Re: [GENERAL] PostgreSQL backend process high memory usage issue

Shianmiin wrote: > > Hi Merlin, > > I revised the test code with attached files and use pgbench to send the > test queries. > > http://postgresql.1045698.n5.nabble.com/file/n4290723/dotest dotest > http://postgresql.1045698.n5.nabble.com/file/n4290723/initialize.sql > initialize.sql > http

[GENERAL] Count for pagination

The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. <300 ms 2. Subset of the total records on that page. a. 1-2 sec 3. Count of the tot

Re: [GENERAL] comma vs cross join question

Jason Long writes: > I recently upgraded to JBoss AS 6.0.0.Final which includes a newer > version of Hibernate. > Previously the Postgres dialect was using a comma, but now is is using > cross join. > With the cross join this query never completes. With the comma the > query is identical to what

Re: [GENERAL] Changed SSL Certificates

Carlos Mennens writes: > I had self signed SSL certificates on my database server but since > then removed them and received updated certificates from the security > team. I removed (backedup) the old server.crt & server.key and now > have db1_ssl.crt & db1_ssl.key in the identical location as the

Re: [GENERAL] Changed SSL Certificates

On Fri, Apr 8, 2011 at 12:42 PM, Carlos Mennens wrote: > ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt > ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key > > I then restarted PostgreSQL and got the following error: > > 2011-04-08 09:54:34 EDT FATAL: could not load server certificate file > "server.c

Re: [GENERAL] Changed SSL Certificates

On 04/08/2011 09:42 AM, Carlos Mennens wrote: I had self signed SSL certificates on my database server but since then removed them and received updated certificates from the security team. I removed (backedup) the old server.crt& server.key and now have db1_ssl.crt& db1_ssl.key in the identical

Re: [GENERAL] Changed SSL Certificates

On Fri, Apr 8, 2011 at 2:21 PM, Carlos Mennens wrote: > On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz wrote: > > Hi, > > When linking to the certificate and key you should specify the full path. > > ln -s /etc/ssl/certs/db1_ssl.crt /full/path/to/db1_ssl.crt > > ln -s /etc/ssl/private/db1_ssl.

Re: [GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

Ok Vick, thanks, and sorry for the off-list message. regards, Carl 2011/4/8 Vick Khera > > On Fri, Apr 8, 2011 at 9:53 AM, Gipsz Jakab wrote: > >> Thanks Vick, I'll try it tonight. I will give 1024 shared_buffers and >> maintenance_work_mem, and 102 MB of work_mem. >> >> A question: I didn't us

Re: [GENERAL] Changed SSL Certificates

On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz wrote: > Hi, > When linking to the certificate and key you should specify the full path. > ln -s /etc/ssl/certs/db1_ssl.crt      /full/path/to/db1_ssl.crt > ln -s /etc/ssl/private/db1_ssl.key   /full/path/to/db1_ssl.key Thanks for the quick reply Diego

Re: [GENERAL] Changed SSL Certificates

On Fri, Apr 8, 2011 at 1:42 PM, Carlos Mennens wrote: > I had self signed SSL certificates on my database server but since > then removed them and received updated certificates from the security > team. I removed (backedup) the old server.crt & server.key and now > have db1_ssl.crt & db1_ssl.key i

Re: [GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

I think, the main problem is the following: all of the user are autheticated in the psql with the same username, and the thing, that you've mentioned, the locks (I will talk with the programmer, or create new users in the psql, and modify the ODBC settings at the client side). How can I setup a sho

Re: [GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

Thanks Vick, I'll try it tonight. I will give 1024 shared_buffers and maintenance_work_mem, and 102 MB of work_mem. A question: I didn't use (it's marked with #) the effective_planner (or any other planner method or config option). Is it ok, when I turn it on with that parameter: 1036MB? DROP/ADD

Re: [GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

On Fri, Apr 8, 2011 at 9:53 AM, Gipsz Jakab wrote: > Thanks Vick, I'll try it tonight. I will give 1024 shared_buffers and > maintenance_work_mem, and 102 MB of work_mem. > > A question: I didn't use (it's marked with #) the effective_planner (or any > other planner method or config option). Is i

[GENERAL] comma vs cross join question

I recently upgraded to JBoss AS 6.0.0.Final which includes a newer version of Hibernate. Previously the Postgres dialect was using a comma, but now is is using cross join. In order do to the migration I had to override the cross join operator to a comma in HIbernate so it would generate the same

[GENERAL] Changed SSL Certificates

I had self signed SSL certificates on my database server but since then removed them and received updated certificates from the security team. I removed (backedup) the old server.crt & server.key and now have db1_ssl.crt & db1_ssl.key in the identical location as the old SSL certificates. I then we

Re: [GENERAL] Using Function returning setof record in JOIN

2011/4/8 Merlin Moncure : > On Fri, Apr 8, 2011 at 5:57 AM, gmb wrote: >> Hi >> >> Is it possible to do a JOIN between a VIEW and the output of a FUNCTION? > > yes. yes, it is possible. Just I am not sure if original query wasn't directed to >>lateral<< feature. Pavel > > merlin > > -- > Sent

Re: [GENERAL] why autocommit mode is slow?

On Thu, Apr 07, 2011 at 10:59:50PM +0200, Szymon Guz wrote: > Hi, > this is maybe a stupid question, but I don't know how to explain to my > coworkers why performing many inserts in autocommit mode is so much slower > than making all of them in one transaction. > > Why is that so slow? If you do

[GENERAL] Surge 2011 CFP Deadline Extended

OmniTI is pleased to announce that the CFP deadline for Surge 2011, the Scalability and Performance Conference, (Baltimore: Sept 28-30, 2011) has been extended to 23:59:59 EDT, April 17, 2011. The event focuses upon case studies that demonstrate successes (and failures) in Web applications and Inte

Re: [GENERAL] Using Function returning setof record in JOIN

On Fri, Apr 8, 2011 at 5:57 AM, gmb wrote: > Hi > > Is it possible to do a JOIN between a VIEW and the output of a FUNCTION? yes. 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] Using Function returning setof record in JOIN

I do not know the answer but it isn't that difficult to use trial-and-error to check and see whether the TWO most logical forms would work and then ask for further assistance if they do not. Just pretend you have a view with the same name as your function (though you will need to add the parenthes

Re: [GENERAL] why autocommit mode is slow?

On Thu, Apr 7, 2011 at 9:59 PM, Szymon Guz wrote: > this is maybe a stupid question, but I don't know how to explain to my > coworkers why performing many inserts in autocommit mode is so much slower > than making all of them in one transaction. > Why is that so slow? There's a performance bug t

Re: [GENERAL] cast list of oid

Hello 2011/4/8 salah jubeh : > is it possible to cast a list of  oids  . i.e something like below. Or I > need to write a procedure > > SELECT groname, grolist::regclass::text FROM pg_group; > what is list? Is it a array? you can use a unnest and array() postgres=# select array(select unnest(ar

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Fri, Apr 8, 2011 at 7:43 AM, Shianmiin wrote: > Hi Merlin, > > I revised the test code with attached files and use pgbench to send the test > queries. > > http://postgresql.1045698.n5.nabble.com/file/n4290723/dotest dotest > http://postgresql.1045698.n5.nabble.com/file/n4290723/initialize.sql >

Re: [GENERAL] PostgreSQL backend process high memory usage issue

Thanks. Probably, but that's not the point here. The issue here is how PostgreSQL backend process uses memory and I wonder if there any way to configure it. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-backend-process-high-memory-usage-issue-tp4289407p428955

Re: [GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

On Fri, Apr 8, 2011 at 3:00 AM, Gipsz Jakab wrote: > After the settings in the postgresql.conf our system is much faster, and no > more error messages in the postgres.log, but If I try to drop a table, or > add a new one, our system is stopping, until I kill the process, which is > dropping or add

Re: [GENERAL] PostgreSQL backend process high memory usage issue

if we go with single-db-multiple-schema model, either our data access layer will need to ensure qualifying all the database objects with proper schema name, or with postgresql, just to change the search path while the connection passed from pool to app code. Another model under evaluation is singl

Re: [GENERAL] PostgreSQL backend process high memory usage issue

Good point. Thanks. The tests we did in house is all from client site and definitely not in a single transaction. I just found this simplified test case can reproduce the same memory usage issue and didn't pay too much attention to it. If we repeatedly doing smaller batches, we can still see the

[GENERAL] cast list of oid

is it possible to cast a list of oids . i.e something like below. Or I need to write a procedure SELECT groname, grolist::regclass::textFROM pg_group; Regards Best Regard Eng. Salah Al Jubeh PalestinePolytechnic University College of Applied Science Computer Science P.O. Box 198 Mobi

[GENERAL] Index scan vs table inheritance

Hi All, Now I have PostgreSQL 8.3.4 and next problem: I have hierarchy of tables: Master table (empty, has not data, indexes and over). Generally it is empty, but in production it may have some data or indexes and I have to select from it for backward compatibility. Child tables inherited from

Re: [GENERAL] PostgreSQL backend process high memory usage issue

Hi Merlin, I revised the test code with attached files and use pgbench to send the test queries. http://postgresql.1045698.n5.nabble.com/file/n4290723/dotest dotest http://postgresql.1045698.n5.nabble.com/file/n4290723/initialize.sql initialize.sql http://postgresql.1045698.n5.nabble.com/file/n

[GENERAL] Using Function returning setof record in JOIN

Hi Is it possible to do a JOIN between a VIEW and the output of a FUNCTION? e.g. I have a function returning a SETOF records (using OUT parameters) with the following output: testdb=# SELECT * FROM myfunc( 'AAA1' ); -- returns calculcated values for all orders for account 'AAA1' _acc | _

Re: [GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

On Fri, Apr 8, 2011 at 4:00 AM, Gipsz Jakab wrote: > My question is the following: if this is a dedicated database server, with > maximum 30 users (but they are using ODBC with Microsoft Acces, and each of > them generating 4-6 connection at the same time), and other 200 people will > use this se

Re: [GENERAL] why autocommit mode is slow?

On Thu, Apr 7, 2011 at 4:59 PM, Szymon Guz wrote: > this is maybe a stupid question, but I don't know how to explain to my > coworkers why performing many inserts in autocommit mode is so much slower > than making all of them in one transaction. as others have said, there is overhead in each tr

[GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

Hi, I've got an IBM x3200 server, with 1,8 Ghz dual core processor, and with 4 GB RAM. I've installed a FreeBSD 8.2, PostgreSQL 9.0.3, Apache22, with php5.3.5 and extensions for postgre, session,pdf and others. After the install, I recieved lot of "too many user" in the postgresql.log, and after

Re: [GENERAL] why autocommit mode is slow?

On 8 April 2011 05:06, Scott Marlowe wrote: > On Thu, Apr 7, 2011 at 2:59 PM, Szymon Guz wrote: > > Hi, > > this is maybe a stupid question, but I don't know how to explain to my > > coworkers why performing many inserts in autocommit mode is so much > slower > > than making all of them in one t