On Wed, Jan 27, 2010 at 4:42 PM, Tom Lane wrote:
> related to maximum per-process data space. I don't know BSD very well
> so I can't say if datasize is the only such value for BSD, but it'd be
> worth checking. (Hmm, on OS X which is at least partly BSDish, I see
> -m and -v in addition to -d,
i have 2 functions, naming a and b, both outputing a resultset(cursor)
and a integer.
a calls b
a:
CREATE OR REPLACE FUNCTION "public"."t_outer" (out o_rs
"pg_catalog"."refcursor", out o_i integer) RETURNS record AS
...
select t_inner(o_rs, o_i);
...
b:
CREATE OR REPLACE FUNCTION "public"."t_inne
I am getting the error:
LINE 1: select dblink_connect('dbname=postgres');
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
-
On Wed, Jan 27, 2010 at 2:14 AM, Pavel Stehule wrote:
>
> hmm ...it cannot work :(. You cannot forward FETCH ALL statement on
> server side - without programming in C
>
> in this case you need small application for reading cursor and
> transformation to CVS
If I'm understanding what you're doing
On Wed, Jan 27, 2010 at 5:45 PM, A B wrote:
> Hello there.
>
> I read http://www.postgresql.org/docs/current/static/sql-altertable.html
> and find it interesting that
>
> " Adding a column with a non-null default or changing the type of an
> existing column will require the entire table to be rewr
On Wed, Jan 27, 2010 at 6:37 PM, Mike Bresnahan
wrote:
> Greg Smith 2ndquadrant.com> writes:
> > Could you try this again with "top -c", which will label these
> > postmaster processes usefully, and include the pgbench client itself in
> > what you post? It's hard to sort out what's going on in
Sorry. Some correction.
Change
But... I am not implementing table partition
to
But... I am *now* implementing table partition
Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/28/10, Yan Cheng Cheok wrote:
> From: Yan Cheng Cheok
> Subject: Re: [GENERAL] Problem after installing triggering
Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/28/10, Alban Hertroys wrote:
> From: Alban Hertroys
> Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table
> Partition
> To: "Yan Cheng Cheok"
> Cc: pgsql-general@postgresql.org
> Date: Thursday, January 28, 2010, 2:5
Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/28/10, Tom Lane wrote:
> From: Tom Lane
> Subject: Re: [GENERAL] Problem after installing triggering function
> To: "Yan Cheng Cheok"
> Cc: pgsql-general@postgresql.org
> Date: Thursday, January 28, 2010, 12:34 AM
> Yan Cheng Cheok
> writes:
Hello there.
I read http://www.postgresql.org/docs/current/static/sql-altertable.html
and find it interesting that
" Adding a column with a non-null default or changing the type of an
existing column will require the entire table to be rewritten. This
might take a significant amount of time for a
Jeff Ross writes:
> Tom Lane wrote:
>> Better look at the "ulimit" values the postmaster is started with;
> OpenBSD makes a _postgresql user on install and it is in the daemon class
> with
> the following values:
> daemon:\
> :ignorenologin:\
> :datasize=infinity:\
>
Greg Smith 2ndquadrant.com> writes:
> Could you try this again with "top -c", which will label these
> postmaster processes usefully, and include the pgbench client itself in
> what you post? It's hard to sort out what's going on in these
> situations without that style of breakdown.
As a fur
> Could you try this again with "top -c", which will label these
> postmaster processes usefully, and include the pgbench client itself in
> what you post? It's hard to sort out what's going on in these
> situations without that style of breakdown.
I had run pgbench on a separate instance last
John R Pierce hogranch.com> writes:
> more likely, he's disk IO bound, but hard to say as that iostat output
> only showed a couple 2 second slices of work. the first output, which
> shows average since system startup, seems to show the system has had
> relatively high average wait times of 1
I have seen behavior like this in the past on EC2. I believe your
bottleneck may be pulling the data out of cache. I benchmarked this a
while back and found that memory speeds are not much faster than disk
speeds on EC2. I am not sure if that is true of Xen in general or if
its just limited t
Mike Bresnahan wrote:
top - 15:55:05 up 1:33, 2 users, load average: 2.44, 0.98, 0.44
Tasks: 123 total, 11 running, 112 sleeping, 0 stopped, 0 zombie
Cpu(s): 18.9%us, 8.8%sy, 0.0%ni, 70.6%id, 0.0%wa, 0.0%hi, 1.7%si, 0.0%st
Mem: 7348132k total, 1886912k used, 5461220k free,34
Tom Lane wrote:
Jeff Ross writes:
Running a simple select only pgbench test against it will fail with an out of
memory error as it tries to vacuum --analyze the newly created database with
750 tuples.
Better look at the "ulimit" values the postmaster is started with;
you shouldn't be get
Jim Mlodgenski gmail.com> writes:
> I have seen behavior like this in the past on EC2. I believe your bottleneck
may be pulling the data out of cache. I benchmarked this a while back and found
that memory speeds are not much faster than disk speeds on EC2. I am not sure if
that is true of Xen in g
Jeff Ross writes:
> Running a simple select only pgbench test against it will fail with an out of
> memory error as it tries to vacuum --analyze the newly created database with
> 750 tuples.
Better look at the "ulimit" values the postmaster is started with;
you shouldn't be getting that out-
I'm not getting something about the best way to set up a server using
PostgreSQL as a backend for a busy web server running drupal.
The postgresql performance folks
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
say that in a server with more that 1GB of ram
"a reasonable sta
On Wed, Jan 27, 2010 at 3:59 PM, Mike Bresnahan
wrote:
> I have deployed PostgresSQL 8.4.1 on a Fedora 9 c1.xlarge (8x1 cores)
> instance
> in the Amazon E2 Cloud. When I run pgbench in read-only mode (-S) on a
> small
> database, I am unable to peg the CPUs no matter how many clients I throw at
>
I have deployed PostgresSQL 8.4.1 on a Fedora 9 c1.xlarge (8x1 cores) instance
in the Amazon E2 Cloud. When I run pgbench in read-only mode (-S) on a small
database, I am unable to peg the CPUs no matter how many clients I throw at it.
In fact, the CPU utilization never drops below 60% idle. I also
On 1/27/2010 7:32 AM, J Scanf wrote:
Hi all. I have a problem with two tables of same structure:
'orders_stat_pre' and 'orders_stat_pre_new'.
store=# \d orders_stat_pre
Column | Type | Modifiers
++---
id
Hi!
I'm happy to facilitate this and get the details in for our
application. Seems like we have lots of things that we could get
students involved with, and of course, we tend to get interesting
projects pitched to us that we haven't thought of before.
I've attended the Mentor Summit after GSoC f
On 27 Jan 2010, at 4:22, Yan Cheng Cheok wrote:
> Hello all,
>
> I solve my problem using the following. It seems that when inherit from
> parent table, the parent table's constraint is not being carried over to
> child table.
>
> CREATE OR REPLACE FUNCTION measurement_insert_trigger()
> RETUR
On 27 Jan 2010, at 2:00, Yan Cheng Cheok wrote:
>>> However, whenever I insert row into measurement table,
>> I realize its primary key value is going from 2, 4, 6, 8,
>> 10...
>>>
>>> May I know how can I prevent this?
>>
>> Apparently nextval on that sequence gets called multiple
>> times in yo
Pavel Stehule claviota:
...
But what I would like to do is to redirect the output of the function (that
is, the 'result' cursor) to a view, which will be used in other places. I
thought something like FETCH INTO would do the trick, but it doesn't.
Also, I need, at some point, to export the outp
Pavel Stehule claviota:
...
Actually, if the small application was reading cursor, and transforming it
to a VIEW, this would solve both problems at once:
something like:
CREATE VIEW crosstabbed_thing AS
(cursor_to_dataset(SELECT do_cross_cursor(...)));
no it isn't possible. VIEW have to ha
Hi all. I have a problem with two tables of same structure:
'orders_stat_pre' and 'orders_stat_pre_new'.
store=# \d orders_stat_pre
Column | Type | Modifiers
++---
id | integer| not n
Many people have been disappointed by the lack of a PL/R Windows binary
distribution since PostgreSQL 8.3 came out. Unfortunately the switch
from MinGW to MSVC as the Windows build system in Postgres 8.3
effectively rendered Windows an unsupported platform for PL/R.
I *finally* have made significa
Osvaldo,
That did the trick! Like you said, it had to do with the composite type. I
added the column name to the row variable assignment and it works now.
Thanks for the tip and thanks to everybody else for your assistance.
Have a great day.
Regards,
Jeff Aycock
-Original Message-
On Wed, Jan 27, 2010 at 10:40:17AM -0500, Aycock, Jeff R. wrote:
> EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM
> schema_2.'||whoami||' where created_dt between $2 and $3;'
You'll also need to expand those other parameters. The code is executed
in an independent scope and hence P
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote:
Thanks for the catch.
However, this did not fix the syntax error.
Looking back at this thread I second Andreas's suggestion. It seems the
syntax is right but the names are wrong. What is the result when you do
SELECT * FROM schema_1.snapshot_ta
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote:
Thanks for the catch.
However, this did not fix the syntax error.
You are sure the function is being replaced with versions that have the
changes? In other words does \df+ show the changes?
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent v
Thanks for the catch.
However, this did not fix the syntax error.
-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
Sent: Wednesday, January 27, 2010 11:47 AM
To: Aycock, Jeff R.
Cc: Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL] syntax error d
On 01/27/2010 08:41 AM, Aycock, Jeff R. wrote:
Adrian,
I tried that as well and got the same error result.
Regards,
Jeff Aycock
I went back to the original function and assuming no cut/paste errors
there is a ';' missing after the last END.
END;
RETURN NEXT r;
E
On Wed, Jan 27, 2010 at 02:52:58PM +0600, AI Rumman wrote:
>I am using Postgresql 8.3
>
>I have two databases:
>db1
>db2
>
>db1 holds a table tab1.
>
>Is it possible to get the value of the above tab1 in db2 database?
>
>Regards
You'll need somethin
Adrian,
I tried that as well and got the same error result.
Regards,
Jeff Aycock
-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
Sent: Wednesday, January 27, 2010 11:33 AM
To: Aycock, Jeff R.
Cc: Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL]
Yan Cheng Cheok writes:
> I have a stored procedure execute the following code :
> INSERT INTO unit(fk_lot_id, cycle)
> VALUES(_lotID, _cycle) RETURNING * INTO _unit;
> raise notice 'AFTER INSERT INTO UNIT, _unit.unit_id = %', _unit.unit_id ;
> unit_id column, is an auto generated p
Aycock, Jeff R. wrote:
> Pavel,
>
> Per your suggestion I modified one line below BEGIN to look like this:
>
> EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM
> "schema_2".'||"whoami"||' where created_dt between $2 and $3;'
^^
On 01/27/2010 08:27 AM, Aycock, Jeff R. wrote:
Pavel,
Per your suggestion I modified one line below BEGIN to look like this:
EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM
"schema_2".'||"whoami"||' where created_dt between $2 and $3;'
However, it is still giving me the same
Pavel,
Per your suggestion I modified one line below BEGIN to look like this:
EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM
"schema_2".'||"whoami"||' where created_dt between $2 and $3;'
However, it is still giving me the same syntax error as before. I must be
missing some
>
> ERROR: syntax error at or near "("
>
> LINE 1: SELECT *, $1, now() INTO schema_1.(table_A) FROM schema_2.(table_A)
> where created_dt between $2 and $3;
>
>
schema_1.(table_A) is nonsense. have to be "schema_1"."table_A"
regards
Pavel Stehule
--
Sent via pgsql-general mailing list (pgsql-g
Raymond,
I tried your suggestion but the result is the same when "whoami" is
declared at the top of the function and assigned inside the loop.
Thanks for the suggestion anyway.
-Original Message-
From: Raymond O'Donnell [mailto:r...@iol.ie]
Sent: Wednesday, January 27, 2010 11:00 AM
To:
2010/1/27 Tom Lane :
> Vincenzo Romano writes:
>> But why still on separate schema?
>> I'd rather put them all in the public one, so you don't need the "pst."
>> anymore.
>> Just like (most of) all other contrib modules ...
>
> If this were to get committed, it would definitely get made to look
2010/1/27 Tom Lane :
> Vincenzo Romano writes:
>> But why still on separate schema?
>> I'd rather put them all in the public one, so you don't need the "pst."
>> anymore.
>> Just like (most of) all other contrib mudules ...
>
> If this were to get committed, it would definitely get made to look
2010/1/27 Pavel Stehule :
> 2010/1/27 Vincenzo Romano :
>> But why still on separate schema?
>> I'd rather put them all in the public one, so you don't need the "pst."
>> anymore.
>> Just like (most of) all other contrib mudules ...
>
> if you like, you can set a search_path
>
> it is cleaner tha
Vincenzo Romano writes:
> But why still on separate schema?
> I'd rather put them all in the public one, so you don't need the "pst."
> anymore.
> Just like (most of) all other contrib mudules ...
If this were to get committed, it would definitely get made to look just
like all the other contri
On 27/01/2010 15:40, Aycock, Jeff R. wrote:
> BEGIN
>
> FOR r IN SELECT * FROM schema_1.snapshot_table
>
> LOOP
>
>
>
> DECLARE whoami text := r;
I could be wrong, but I don't think that the DECLARE inside the loop is
correct. I think you have to declare "whoami" with t
Hello,
I have a function with three parameters that would populate a table in
one schema from another table of the same name in another schema. The
tables are dynamically selected at execution time.
CREATE OR REPLACE FUNCTION schema_1.getAllSnapShot(user_id text,
begin_dt date, end_dt date
2010/1/27 Vincenzo Romano :
> 2010/1/27 Pavel Stehule :
>> Hello
>>
>> I add sprintf function. Now I think, we can add new contrib module
>> (string functions) with both function - format and sprintf. These
>> functions are relative different, so they can exists separately.
>> Format is simpler and
2010/1/27 Pavel Stehule :
> Hello
>
> I add sprintf function. Now I think, we can add new contrib module
> (string functions) with both function - format and sprintf. These
> functions are relative different, so they can exists separately.
> Format is simpler and faster. Sprintf is more powerful bu
Hello,
Let's say TableA has "toastable" column, the contents of this column is
stored in let's say pg_toast_1234.
Is there a query to find which records (chunk_id, chunk_seq) in
pg_toast_1234 store data for specific record in TableA (i.e. with PK
column value eq. '567')?
Igor Neyman
Hello
I add sprintf function. Now I think, we can add new contrib module
(string functions) with both function - format and sprintf. These
functions are relative different, so they can exists separately.
Format is simpler and faster. Sprintf is more powerful but slower.
postgres=# select pst.form
On Tue, Jan 26, 2010 at 05:12:51PM -0500, Greg Smith wrote:
> Hashimoto Yuya wrote:
> > Judging from the result, I could see that stats collector process
> > caused this unusually high CPU utilization rate.
> > I found similar problem at
> > http://archives.postgresql.org/pgsql-general/2008-06/msg0
2010/1/27 Pierre Chevalier :
> Pavel Stehule claviota:
>>>
>>> ...
>>> Actually, if the small application was reading cursor, and transforming
>>> it
>>> to a VIEW, this would solve both problems at once:
>>> something like:
>>>
>>> CREATE VIEW crosstabbed_thing AS
>>> (cursor_to_dataset(SELECT do_
I have a stored procedure execute the following code :
INSERT INTO unit(fk_lot_id, cycle)
VALUES(_lotID, _cycle) RETURNING * INTO _unit;
raise notice 'AFTER INSERT INTO UNIT, _unit.unit_id = %', _unit.unit_id ;
unit_id column, is an auto generated primary key. I will always get a no
2010/1/27 Pierre Chevalier :
> Pavel Stehule claviota:
>>>
>>> ...
>>> But what I would like to do is to redirect the output of the function
>>> (that
>>> is, the 'result' cursor) to a view, which will be used in other places. I
>>> thought something like FETCH INTO would do the trick, but it doesn
Thanks Craig, this one worked!
Balazs
-Original Message-
From: Craig Ringer [mailto:cr...@postnewspapers.com.au]
Sent: Wednesday, January 27, 2010 12:34 AM
To: Keresztury Balázs
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] create role in a pl/pgsql trigger
On 27/01/2010 1:09
2010/1/27 Pierre Chevalier :
> Hello,
> Pavel Stehule claviota:
>>
>> ...
>> you cannot get crosstab via SELECT statement. There is workaround
>> http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
>>
>
> All right, I've just tried it: it works just fine in my case! Thanks a l
Hello,
Pavel Stehule claviota:
...
you cannot get crosstab via SELECT statement. There is workaround
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
All right, I've just tried it: it works just fine in my case! Thanks a lot!
Except a few things, but I am not (yet)
I am using Postgresql 8.3
I have two databases:
db1
db2
db1 holds a table tab1.
Is it possible to get the value of the above tab1 in db2 database?
Regards
62 matches
Mail list logo
