Thanks a lot for the hint Tom! I've replaced deletes with TRUNCATE and it
gave a performance of 50.950 sec which is twice as fast as the drop temp
table method, with the added benefit of not having to raise the
max_locks_per_transaction.
I also think I can't see the performance decrease pattern an
Thank you very much, I will have a look.
-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, May 21, 2014 3:20 PM
To: Khangelani Gama; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2
On 05/21/2014 02:54 AM,
Just for the record, I've increased the data volume X10 and observed only
quite small performance drop: average time per inner function call
increased from 12.6 ms to 13.3 ms.
Regards
Seref
On Wed, May 21, 2014 at 5:19 PM, Seref Arikan <
serefari...@kurumsalteknoloji.com> wrote:
> Thanks a lot
I came across http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/
which seems to indicate so.
When I run the following test script, having 50 foreign keys takes
about twice as long to do the update. Is there a reason for that?
Seems like the RI triggers wouldn't have to run on updates if t
On 20/05/2014 11:39, Khangelani Gama wrote:
> Sorry
>
> I found the problem, it’s data problem in another database, it’s trying
> to convert numeric null but of which it’s not a null data, it’s a blank
> data.
Hi there,
I'm glad you were able to find the solution to your problem. I have to
say,
On Wed, May 21, 2014 at 1:11 PM, Joe Van Dyk wrote:
> I came across http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/
> which seems to indicate so.
>
> When I run the following test script, having 50 foreign keys takes
> about twice as long to do the update. Is there a reason for that?
Hello all,
I've been seeing lots of this processes in my database DEALLOCATE
pdo_stmt_0001 with idle state.
Executing *select * from pg_stat_activity where query like
'DEALLOCATE%';*I can see some of them are idle for +/- 30 minutes, and
there is one idle
for 4 days.
When I checked one of th
Could any one please tell me why my system is waiting to get lock for an
INSERT statement?
2014-05-21 07:52:49.965 PDT [9-1]LOG: process 31407 acquired ExclusiveLock
on extension of relation 429298276 of database 21497 after 3219.963 ms
2014-05-21 07:52:49.965 PDT [10-1]STATEMENT: INSERT INTO ta
On Wed, May 21, 2014 at 3:14 PM, AI Rumman wrote:
> Could any one please tell me why my system is waiting to get lock for an
> INSERT statement?
>
> 2014-05-21 07:52:49.965 PDT [9-1]LOG: process 31407 acquired
> ExclusiveLock on extension of relation 429298276 of database 21497 after
> 3219.963
Got it.
Thanks.
Any special parameter to tune it? Like wal_buffers or shared_buffers?
On Wed, May 21, 2014 at 3:28 PM, Jeff Janes wrote:
> On Wed, May 21, 2014 at 3:14 PM, AI Rumman wrote:
>
>> Could any one please tell me why my system is waiting to get lock for an
>> INSERT statement?
>>
>>
Laurentius Purba wrote
> Hello all,
>
> I've been seeing lots of this processes in my database DEALLOCATE
> pdo_stmt_0001 with idle state.
>
> Executing *select * from pg_stat_activity where query like
> 'DEALLOCATE%';*I can see some of them are idle for +/- 30 minutes, and
> there is one idl
On Wednesday, May 21, 2014, AI Rumman wrote:
> Got it.
> Thanks.
> Any special parameter to tune it? Like wal_buffers or shared_buffers?
>
Possible but unlikely. You probably just need better hardware (or more
patience). How fast is the database growing? How much is actually being
written (sa
On Wednesday, May 21, 2014, Jeff Janes wrote:
>
> On Wed, May 21, 2014 at 1:11 PM, Joe Van Dyk
>
> > wrote:
>
>> I came across http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/
>> which seems to indicate so.
>>
>> When I run the following test script, having 50 foreign keys takes
>> a
I have an extension which, after installation, creates triggers and their
associated functions dynamically and adds the functions to the extension so
that they will be dropped with the extension.
However, there is no way to add the trigger to the extension, so pg_dump's
output still includes the d
Hi,
I am designing backup strategy for a PostgreSQL database (v9.3). I have a
scenario for recovery of tablespaces:
1. Backup of whole database (including individual tablespaces which are
stored on different disks) has been taken at 11AM
2. My disk which stores tablespace- tblspc1 crashed at 2:0
We routinely use plantuner. http://www.sai.msu.su/~megera/wiki/plantuner
On Tue, May 20, 2014 at 12:38 PM, Steve Crawford
wrote:
> Is there a way to force a specific index to be removed from consideration in
> planning a single query?
>
> Specifically, on a 60-million-row table I have an index
Hi
I have a postgres 9 database, inside this database I need to create a new
table called *center_changed* that gets inserted by any change that take
place in a table called *center*. So I need to create trigger to do this.
*Example: *
Table name*: center*(c_cde, c_dsc, ops_cde, grp_cde);
> Original Message
> Subject: Re: [GENERAL] LDAP authentication not working
> Resent-From:
> Date: Wed, 14 May 2014 06:47:45 -1000
> From: Stephan Fabel
> To: Magnus Hagander
> CC: Postgres List , Jürgen Fuchsberger
>
>
>
>
> On May 14, 2014 12:56 AM
On 05/21/2014 02:54 AM, Khangelani Gama wrote:
Hi
I have a postgres 9 database, inside this database I need to create a
new table called *center_changed* that gets inserted by any change that
take place in a table called *center*. So I need to create trigger to do
this.
*Example: *
Table name*
Hi.
I've got some boxes with 128GB of RAM and up to 750 connections, just
upgraded to 9.3 so I'm revising my tuning. I'm getting a
recommendation from pgtune to bump my shared_buffers up to 30GB and
work_mem to 80MB. Is a shared_buffers this high now sane?
The PostgreSQL reference doesn't make re
On Wed, 21 May 2014 21:39:05 +0700 Stuart Bishop
wrote:
>
> I've got some boxes with 128GB of RAM and up to 750 connections, just
> upgraded to 9.3 so I'm revising my tuning. I'm getting a
> recommendation from pgtune to bump my shared_buffers up to 30GB and
> work_mem to 80MB. Is a shared_buffe
>
> a generic ETL (extract, transform, load) tool that supports XML and
> postgresql should be able to do it, but its not something built into
> postgresql as-is.I can't recommend any specific ETL tool to do this as
> I've never needed to do this exactly.
Thanks. That at least tells me th
On 05/21/2014 08:03 AM, Aram Fingal wrote:
a generic ETL (extract, transform, load) tool that supports XML and postgresql
should be able to do it, but its not something built into postgresql as-is.
I can't recommend any specific ETL tool to do this as I've never needed to do
this exactly.
Greetings,
I have a function that loops over a set of ids, calls a function inside the
loop using the current id as the parameter, and adds the result of the
function call to a temp table.
When the loop terminates, the temp table has all the rows from the function
call(s) and it is returned.
I've
Seref Arikan writes:
> What may be building up here? I suspect deleting all rows from the temp
> tables is not really deleting them since this is all happening in a
> transaction, but it is my uneducated guess only.
I suspect you suspect correctly. Autovacuum does not touch temp tables,
so it wo
Hi Tom,
Thanks for the feedback. I've moved the temp tables to internal function,
increased max_locks_per_transaction and dropped the tables instead of
deleting them.
The performance drop is till there, but it is much, much less then the
previous case. Previously the whole execution took 04:36:14
26 matches
Mail list logo
