On 8 April 2011 05:06, Scott Marlowe scott.marl...@gmail.com wrote:
On Thu, Apr 7, 2011 at 2:59 PM, Szymon Guz mabew...@gmail.com 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
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
On Thu, Apr 7, 2011 at 4:59 PM, Szymon Guz mabew...@gmail.com 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
On Fri, Apr 8, 2011 at 4:00 AM, Gipsz Jakab clausewit...@gmail.com 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
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 |
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
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
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
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
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
On Fri, Apr 8, 2011 at 3:00 AM, Gipsz Jakab clausewit...@gmail.com 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
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:
On Fri, Apr 8, 2011 at 7:43 AM, Shianmiin shianm...@gmail.com 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
Hello
2011/4/8 salah jubeh s_ju...@yahoo.com:
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
On Thu, Apr 7, 2011 at 9:59 PM, Szymon Guz mabew...@gmail.com 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
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
On Fri, Apr 8, 2011 at 5:57 AM, gmb gmbou...@gmail.com 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:
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
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
2011/4/8 Merlin Moncure mmonc...@gmail.com:
On Fri, Apr 8, 2011 at 5:57 AM, gmb gmbou...@gmail.com 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
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
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
On Fri, Apr 8, 2011 at 9:53 AM, Gipsz Jakab clausewit...@gmail.com 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
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?
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
On Fri, Apr 8, 2011 at 1:42 PM, Carlos Mennens carlos.menn...@gmail.comwrote:
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
On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz dsch...@gmail.com 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
Ok Vick, thanks, and sorry for the off-list message.
regards,
Carl
2011/4/8 Vick Khera vi...@khera.org
On Fri, Apr 8, 2011 at 9:53 AM, Gipsz Jakab clausewit...@gmail.comwrote:
Thanks Vick, I'll try it tonight. I will give 1024 shared_buffers and
maintenance_work_mem, and 102 MB of
On Fri, Apr 8, 2011 at 2:21 PM, Carlos Mennens carlos.menn...@gmail.comwrote:
On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz dsch...@gmail.com 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
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
On Fri, Apr 8, 2011 at 12:42 PM, Carlos Mennens carlos.menn...@gmail.comwrote:
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
Carlos Mennens carlos.menn...@gmail.com 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
Jason Long mailing.li...@octgsoftware.com 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
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
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
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
On Fri, Apr 8, 2011 at 10:30 AM, Shianmiin shianm...@gmail.com 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
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
* 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
Jason Long mailing.li...@octgsoftware.com 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
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
On Fri, 2011-04-08 at 14:45 -0400, Tom Lane wrote:
Jason Long mailing.li...@octgsoftware.com 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,
Jack Christensen ja...@hylesanderson.edu 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
On Fri, Apr 8, 2011 at 2:01 PM, Adrian Klaver adrian.kla...@gmail.com 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
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,
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:
On Fri, Apr 8, 2011 at 2:00 PM, Shianmiin shianm...@gmail.com 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
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 UNIONs
Merlin Moncure-2 wrote:
On Fri, Apr 8, 2011 at 2:00 PM, Shianmiin lt;shianm...@gmail.comgt;
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
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_
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
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
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
Carlos Mennens carlos.menn...@gmail.com 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
On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane t...@sss.pgh.pa.us 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
Carlos Mennens wrote:
On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane t...@sss.pgh.pa.us 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
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
Dah, left out the port.
On Fri, Apr 8, 2011 at 10:36 PM, Yang Zhang yanghates...@gmail.com 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
58 matches
Mail list logo
