date:20120403

2012/4/3 Alban Hertroys 

> On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:
>
> > That is right, there is no sense to use cursors here...
>
> I think you're wrong there: The OP is querying a system table for tables
> of a certain name, which I expect can contain multiple rows for tables of
> the same name in different schema's.
>
> Of course, that may not be applicable to the her situation.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
hmm...

 if tablename variable contains schema name this function will never work,
because:
quote_ident ('aa.aaa') gives "aa.aaa" what is not proper fully qualified
name,  should be "aa"."aaa".
So, my assumption is tablename variable contains only table name. If this
is only table name, without schema name then postgre will try to truncate
table only in schema where this table could be found (according to
search_path parameter). It is not possible to have more then one table with
the same name in the same schema.

Grace wrote:
*"I tried to create function to truncate table"*
this drives me to think about one table not all of them in database, but
cursor statement could be misleading.

I think it is not a good idea to truncate all tables with the same name in
all schemas (maybe this is Grace's intention - don't know).

BTW, *tablename *column of *pg_catalog.pg_tables* view contains only table
name without schema, so this statement will NOT truncate all tables with
the same name accross all schemas because of search_path.
http://www.postgresql.org/docs/9.1/static/view-pg-tables.html

Regards,
Bartek

Re: [GENERAL] Cast timestamptz to/from integer?

I think You can use epoch
there is an example:
http://www.postgresql.org/docs/9.1/static/functions-datetime.html


SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';

Regards,
Bartek


2012/4/3 Chris Angelico 

> I work a lot with Unix times as integers, but would like to store them
> in Postgres as 'timestamp(0) with time zone' for convenience and
> readability. Unfortunately the syntax to translate between the two is
> a little cumbersome, so I'm looking at hiding it away behind a
> function - or a cast. However, the CREATE CAST docs say that I have to
> be the owner of either the source or target types, which presumably
> means I can't define a cast from timestamptz to int.
>
> Is there a workaround for this?
>
> Chris Angelico
>
> --
> 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] Cast timestamptz to/from integer?

2012-04-03 Thread Chris Angelico

On Tue, Apr 3, 2012 at 7:11 PM, Bartosz Dmytrak  wrote:
> I think You can use epoch
> there is an
> example: http://www.postgresql.org/docs/9.1/static/functions-datetime.html
>
>
> SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1
> second';

Yep, but when you do that a lot, your statement gets extremely long. I
can create a function that'll hide the mess away, but what I'm hoping
to do is simply cast:

SELECT 982384720::timestamptz;

ChrisA

-- 
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] Cast timestamptz to/from integer?

There is a build in function which encapsulates that statement:

SELECT to_timestamp (982384720);

EXPLAIN ANALYZE shows:
Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1
loops=1)

so this looks cheap

Regards,
Bartek


2012/4/3 Chris Angelico 

> On Tue, Apr 3, 2012 at 7:11 PM, Bartosz Dmytrak 
> wrote:
> > I think You can use epoch
> > there is an
> > example:
> http://www.postgresql.org/docs/9.1/static/functions-datetime.html
> >
> >
> > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1
> > second';
>
> Yep, but when you do that a lot, your statement gets extremely long. I
> can create a function that'll hide the mess away, but what I'm hoping
> to do is simply cast:
>
> SELECT 982384720::timestamptz;
>
> ChrisA
>
> --
> 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] User-defined Aggregate function and performance.

2012-04-03 Thread Ronan Dunklau

On 02/04/2012 18:06, Tom Lane wrote:
> Ronan Dunklau  writes:
>> I'm trying to define a "weighted mean" aggregate using postgresql create
>> aggregate feature.
>
>> I've been able to quickly write the required pgsql code to get it
>> working, but after testing it on a sample 1 rows table, it seems to
>> be approximately 6 to 10 times slower than pure sql.
>
> It might help to use a two-element array for the transition state,
> instead of a custom composite type.

It does not change much.
It seems that altering the transition state instead of building a new
one does help, though. When altering the state, the composite type
version seems to be faster. But it still much slower than the
hand-written sql version.

>
>> My initial implementation was in pl/pgsql, and did not mark the
>> functions as immutable. I did so after a suggestion from an irc user,
>> but it did not change anything performance wise.
>
> Those suggestions would possibly help for a function that's meant to be
> inlined into larger SQL expressions, but they won't do much for an
> aggregate support function.  I'm not real sure, but I think plpgsql
> might be faster in this context.
>
> Another thing to think about is whether you really need type numeric
> here.  float8 would be a lot faster ... though you might have roundoff
> issues.

The "hand-written" sql using only built-in functions performs really
well with numerics. Why do you suggest that it could be the bottleneck ?

I solved the problem by writing a C extension for it:

http://pgxn.org/dist/weighted_mean/1.0.0/

Regards,

-- 
Ronan Dunklau

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

***

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***

This works . Thank you very much.


***

CREATE OR REPLACE FUNCTION truncate_t (tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***
This works,too. Thank you very much.   What's the difference between ( IN
tablename text)
and ( tablename text).



-- one more questions  thanks.



After I created the function sucessfully , I want to execute the function. I
get errors as follows :

rrp=> select truncate_t(t1);
ERROR: column "t1" does not exist
LINE 1 : select truncate_t(t1);
^



rrp=> select truncate_t(rrp.t1);
ERROR:missing FROM-clause entry for table "rrp" 
LINE 1 : select truncate_t(rrp.t1);
^


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615212.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Re: Please help me to take a look of the erros in my functions. Thanks.

Bartek, Thanks. The reason I use the cursor is that I want to check the table
is in the pg_tables or not,
If it exists, the function will execute successfully, if not, it will raise 
the message that the table doesn't exist.  For the schema part, I assume the
people has set the search_path to that schema which the table is in and
because our database only has one schema. So I assume they have the right
search_path.

Thanks for your advice. It helps me  a lot.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615238.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Re: Please help me to take a look of the erros in my functions. Thanks.

Alban, Thanks.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615244.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread Adrian Klaver


On 04/03/2012 07:01 AM, leaf_yxj wrote:

***

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***

This works . Thank you very much.


***

CREATE OR REPLACE FUNCTION truncate_t (tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***
This works,too. Thank you very much.   What's the difference between ( IN
tablename text)
and ( tablename text).



-- one more questions  thanks.



After I created the function sucessfully , I want to execute the function. I
get errors as follows :

rrp=>  select truncate_t(t1);
ERROR: column "t1" does not exist
LINE 1 : select truncate_t(t1);
 ^


You need to pass in a text value:

select truncate_t('t1');








rrp=>  select truncate_t(rrp.t1);
ERROR:missing FROM-clause entry for table "rrp"
LINE 1 : select truncate_t(rrp.t1);
 ^


Same above.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615212.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.




--
Adrian Klaver
adrian.kla...@gmail.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] Problem to change COLLATE

2012-04-03 Thread BrunoSteven

Hi, 

Is possible change default COLLATE/CTYPE  to Portugese_Brazil.1252 ?  I am 
using Centos6 with Postgres 9.1 in this installation of Postgres has only these 
locales installed.
  
pt_BR
pt_BR.iso88591
pt_BR.utf8
pt_PT
pt_PT@euro
pt_PT.iso88591
pt_PT.iso885915@euro
pt_PT.utf8

Are there some diference between  Portugese_Brazil.1252 with others existent  
locales 

Thanks.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

Adrian, Thanks.  Even I try use '' to quote the character. I still get the
error as follows :

rrp=> truncate table t1;
TRUNCATE TABLE
rrp=> select truncate_t('t1');
ERROR: table "t1" does not exist

Thanks.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615292.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread Tom Lane

leaf_yxj  writes:
> ***
> CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
> RETURNS VOID
> AS
> $$
> BEGIN
>   EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';

I think you need a space there:

EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ' CASCADE;';

> EXCEPTION
>   WHEN undefined_table THEN
>   RAISE EXCEPTION 'Table "%" does not exists', tablename;

It's really a pretty bad idea to print your own message instead of using
the system's message.  In this case, you would have figured out the
problem immediately if you'd seen the real error message, which was
presumably bleating about "t1cascade".

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

Tom,

Thanks.  I found out the key issue it. It's because the truncate command
can't have the "cascade".

For the other people reference. The right funcitons are :

*** 

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text) 
RETURNS VOID 
AS 
$$ 
BEGIN 
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';'; 
EXCEPTION 
WHEN undefined_table THEN 
RAISE EXCEPTION 'Table "%" does not exists', tablename; 
END; 
$$ 
LANGUAGE plpgsql SECURITY DEFINER STRICT; 

*** 

*** 

CREATE OR REPLACE FUNCTION truncate_t (tablename text) 
RETURNS VOID 
AS 
$$ 
BEGIN 
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';'; 
EXCEPTION 
WHEN undefined_table THEN 
RAISE EXCEPTION 'Table "%" does not exists', tablename; 
END; 
$$ 
LANGUAGE plpgsql SECURITY DEFINER STRICT; 

*** 


usage : select truncate_t ('aaa');


Thanks everybody's help.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] 9.1.3: launching streaming replication

2012-04-03 Thread Welty, Richard

thanks for the suggestions. the light has gone on and i have it working as of 
about 15 minutes ago. i'm going to revisit the documentation and possibly make 
suggestions about making things a little clearer, or else issue a mea culpa 
about my reading comprehension. don't know which just yet.

richard

-Original Message-
From: Michael Nolan [mailto:htf...@gmail.com]
Sent: Mon 4/2/2012 7:19 PM
To: Welty, Richard
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 9.1.3: launching streaming replication
 
On Mon, Apr 2, 2012 at 4:21 PM, Welty, Richard  wrote:

I got similar messages the first few times I tried to start up my slave
server, I never did figure out exactly what caused it.

You can either delete all the files on the slave and try again, or do what
I did, write a script that handles transferring just the files needed to
resync the slave.

Here's the script I've been using to transfer the files between my two
servers to resync them. This is not a production-ready script.

I have a second tablespace, so there are two 'data' transfers plus the xlog
transfer.  (You may run into issues transferring the pg_tblspc directory,
as I did, hence the '-safe-links' parameter.) The '-delete' term deletes
any files on the slave that aren't on the server, unless you list them in
an '--exclude' clause.)

/usr/local/pgsql/bin/psql -c "select pg_start_backup('tardir',true)"
postgres postgres

rsync -av --exclude log.out --exclude postgresql.conf \
--exclude postgresql.pid --delete --exclude pg_hba.conf \
--exclude pg_xlog --exclude server.crt --exclude server.key \
--exclude restore.conf --exclude restore.done \
--safe-links /usr/local/pgsql/data/ postgres@xxx:/usr/local/pgsql/data

rsync -av /usr/local/pgsql/data2/ postgres@xxx:/usr/local/pgsql/data2

/usr/local/pgsql/bin/psql -c "select pg_stop_backup()" postgres postgres

rsync -av /usr/local/pgsql/data/pg_xlog postgres@xxx:/usr/local/pgsql/data/

echo "ok to start standby"

--
Mike Nolan

[GENERAL] views, queries, and locks

I have a situation that I'd like some help resolving.
Using PostgreSQL 8.4. on Linux, I have three things
coming together that cause me pain. I have a VIEW used by a bunch of
queries. Usually, these queries are fairly short (subsecond) but
sometimes they can be very long (days). I also update this view with
CREATE OR REPLACE VIEW every 15-30 minutes. What I observe, sometimes,
is this:

1. query A comes in. It's a big one.
2. another process comes along, needs to update the view definition.
It issues create or replace view. It blocks on [1].
3. queries B through N come in, are blocked by [2], which is blocked by [1].
4. pandemonium!

I can reduce (some) but not eliminate the need to update the view
multiple times a day. What might be some good ways to prevent queries
B through N blocking?

Addendum: I can work around the issue by timing out and failing the
CREATE OR REPLACE VIEW (by canceling the query) after a short
duration, but is there a better way?

-- 
Jon

-- 
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] Re: Please help me to take a look of the erros in my functions. Thanks.

I think you need a space there:
>
>EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ' CASCADE;';
>
indeed, that is my fault - sorry


>  > EXCEPTION
> >   WHEN undefined_table THEN
> >   RAISE EXCEPTION 'Table "%" does not exists', tablename;
>
> It's really a pretty bad idea to print your own message instead of using
> the system's message.  In this case, you would have figured out the
> problem immediately if you'd seen the real error message, which was
> presumably bleating about "t1cascade".
>
> Like always, it depends,  custom error message has been required by Grace

>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Regards,
Bartek

Re: [GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html

I don't remember since when, but 9.X has this option.

Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table
http://www.postgresql.org/docs/9.1/static/sql-grant.html
this is not the same as DELETE so, I think it is enough to grant this
privilage to user

Regards,
Bartek


2012/4/3 leaf_yxj 

> Tom,
>
> Thanks.  I found out the key issue it. It's because the truncate command
> can't have the "cascade".
>
> For the other people reference. The right funcitons are :
>
> ***
>
> CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
> RETURNS VOID
> AS
> $$
> BEGIN
> EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
> EXCEPTION
>WHEN undefined_table THEN
>RAISE EXCEPTION 'Table "%" does not exists', tablename;
> END;
> $$
> LANGUAGE plpgsql SECURITY DEFINER STRICT;
>
> ***
>
> ***
>
> CREATE OR REPLACE FUNCTION truncate_t (tablename text)
> RETURNS VOID
> AS
> $$
> BEGIN
> EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
> EXCEPTION
>WHEN undefined_table THEN
>RAISE EXCEPTION 'Table "%" does not exists', tablename;
> END;
> $$
> LANGUAGE plpgsql SECURITY DEFINER STRICT;
>
> ***
>
>
> usage : select truncate_t ('aaa');
>
>
> Thanks everybody's help.
>
> Regards.
>
> Grace
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> 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] views, queries, and locks

2012-04-03 Thread Merlin Moncure

On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson  wrote:
> I have a situation that I'd like some help resolving.
> Using PostgreSQL 8.4. on Linux, I have three things
> coming together that cause me pain. I have a VIEW used by a bunch of
> queries. Usually, these queries are fairly short (subsecond) but
> sometimes they can be very long (days). I also update this view with
> CREATE OR REPLACE VIEW every 15-30 minutes. What I observe, sometimes,
> is this:
>
> 1. query A comes in. It's a big one.
> 2. another process comes along, needs to update the view definition.
> It issues create or replace view. It blocks on [1].
> 3. queries B through N come in, are blocked by [2], which is blocked by [1].
> 4. pandemonium!
>
> I can reduce (some) but not eliminate the need to update the view
> multiple times a day. What might be some good ways to prevent queries
> B through N blocking?
>
> Addendum: I can work around the issue by timing out and failing the
> CREATE OR REPLACE VIEW (by canceling the query) after a short
> duration, but is there a better way?

Yeah -- this is just asking for trouble.  Why do you have to replace
the view every 30 minutes?  Your solution is probably going to involve
not doing that.

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] views, queries, and locks

On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure  wrote:
> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson  wrote:
>> I have a situation that I'd like some help resolving.
>> Using PostgreSQL 8.4. on Linux, I have three things
>> coming together that cause me pain. I have a VIEW used by a bunch of
>> queries. Usually, these queries are fairly short (subsecond) but
>> sometimes they can be very long (days). I also update this view with
>> CREATE OR REPLACE VIEW every 15-30 minutes. What I observe, sometimes,
>> is this:
>>
>> 1. query A comes in. It's a big one.
>> 2. another process comes along, needs to update the view definition.
>> It issues create or replace view. It blocks on [1].
>> 3. queries B through N come in, are blocked by [2], which is blocked by [1].
>> 4. pandemonium!
>>
>> I can reduce (some) but not eliminate the need to update the view
>> multiple times a day. What might be some good ways to prevent queries
>> B through N blocking?
>>
>> Addendum: I can work around the issue by timing out and failing the
>> CREATE OR REPLACE VIEW (by canceling the query) after a short
>> duration, but is there a better way?
>
> Yeah -- this is just asking for trouble.  Why do you have to replace
> the view every 30 minutes?  Your solution is probably going to involve
> not doing that.

As I said, I can reduce the frequency, but not eliminate it. I'm
curious - if views are little more than sql macros, by the time the
query has begun to execute why is a lock still needed on the view
definition?



-- 
Jon

-- 
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] views, queries, and locks

2012-04-03 Thread Thomas Kellerer


Jon Nelson wrote on 03.04.2012 19:01:

I also update this view with CREATE OR REPLACE VIEW every 15-30 minutes


That is a highly questionable approach.

What real problem are you trying to solve with that?
Maybe there is a better solution that does not require changing the view.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

Bartek,
Thanks for your reminding. I don't know why CASCASE doesn't work in my 
greenplum postgresql database (version 8.2.14).  I can create the function 
successfully without any errors. But when i call it, I alwasy got errors if I 
include the CASCADE. If I delete the CASCADE, it will works. I don't know why. 
 
 And I read your link. CASCADE means that the child table will be 
delete,too.  I will do a test again.  If possible , Could you help me to do a 
test of creation and usage of that function? if so, please share me your result.
 
For  the truncate and delete ,  in Oracle , the truncate table can reset the 
high water mark and the space can be reused. the delete can't reset the high 
water mark and the space can't be reused. I guess : oracle truncate= truncate + 
vacuum
 
--- I amn't sure what's differences between truncate and delete in postgresql. 
Could you do me a favour to tell me about this.
 
Thanks.
Regards.
 
Grace






At 2012-04-04 01:15:40,"Bartosz Dmytrak [via PostgreSQL]" 
 wrote:
One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html


I don't remember since when, but 9.X has this option.


Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table 
http://www.postgresql.org/docs/9.1/static/sql-grant.html
this is not the same as DELETE so, I think it is enough to grant this privilage 
to user

Regards,
Bartek



2012/4/3 leaf_yxj <[hidden email]>
Tom,

Thanks.  I found out the key issue it. It's because the truncate command
can't have the "cascade".

For the other people reference. The right funcitons are :


***

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN

   EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
   WHEN undefined_table THEN
   RAISE EXCEPTION 'Table "%" does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***


***

CREATE OR REPLACE FUNCTION truncate_t (tablename text)

RETURNS VOID
AS
$$
BEGIN

   EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
   WHEN undefined_table THEN
   RAISE EXCEPTION 'Table "%" does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***



usage : select truncate_t ('aaa');


Thanks everybody's help.

Regards.

Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general






If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615860.html
To unsubscribe from Please help me to take a look of the erros in my functions. 
Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615952.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

Hi Bartek
One more question, In oracle, when you create table using the default option,
the parent table can't be delete if there is any child table exist. Usually, I
won't use the cascade option. I will truncate or delete one by one. what is
postgresql default for these???

Thanks.

Regards.

Grace

At 2012-04-04 01:15:40,"Bartosz Dmytrak [via PostgreSQL]"
wrote:
One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html

I don't remember since when, but 9.X has this option.

Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table
http://www.postgresql.org/docs/9.1/static/sql-grant.html
this is not the same as DELETE so, I think it is enough to grant this privilage
to user

Regards,
Bartek

2012/4/3 leaf_yxj <[hidden email]>
Tom,

Thanks. I found out the key issue it. It's because the truncate command
can't have the "cascade".

For the other people reference. The right funcitons are :

***

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN

EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***

CREATE OR REPLACE FUNCTION truncate_t (tablename text)

RETURNS VOID
AS
$$
BEGIN

EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';

EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;

END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***

usage : select truncate_t ('aaa');

Thanks everybody's help.

Regards.

Grace

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615860.html
To unsubscribe from Please help me to take a look of the erros in my functions.
Thanks., click here.
NAML

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615961.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread John R Pierce


On 04/03/12 10:49 AM, leaf_yxj wrote:
--- I amn't sure what's differences between truncate and delete in 
postgresql. Could you do me a favour to tell me about this.


delete has to go through and flag each tuple for deletion so vacuum can 
eventually go through and reclaim them for reuse.   truncate wipes the 
whole table out, including 0 length the files.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.

Hi John, Thanks for your reply. Just to confirm :  so truncate table means the 
space will be reclaim for reuse ???

Thanks.

Grace

At 2012-04-04 02:01:59,"John R Pierce [via PostgreSQL]" 
 wrote:
On 04/03/12 10:49 AM, leaf_yxj wrote:
> --- I amn't sure what's differences between truncate and delete in
> postgresql. Could you do me a favour to tell me about this.

delete has to go through and flag each tuple for deletion so vacuum can
eventually go through and reclaim them for reuse.   truncate wipes the
whole table out, including 0 length the files.

--
john r pierceN 37, W 122
santa cruz ca mid-left coast

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615977.html
To unsubscribe from Please help me to take a look of the erros in my functions. 
Thanks., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5616006.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] views, queries, and locks

2012-04-03 Thread Merlin Moncure

On Tue, Apr 3, 2012 at 12:30 PM, Jon Nelson  wrote:
> On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure  wrote:
>> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson  
>> wrote:
>>> I have a situation that I'd like some help resolving.
>>> Using PostgreSQL 8.4. on Linux, I have three things
>>> coming together that cause me pain. I have a VIEW used by a bunch of
>>> queries. Usually, these queries are fairly short (subsecond) but
>>> sometimes they can be very long (days). I also update this view with
>>> CREATE OR REPLACE VIEW every 15-30 minutes. What I observe, sometimes,
>>> is this:
>>>
>>> 1. query A comes in. It's a big one.
>>> 2. another process comes along, needs to update the view definition.
>>> It issues create or replace view. It blocks on [1].
>>> 3. queries B through N come in, are blocked by [2], which is blocked by [1].
>>> 4. pandemonium!
>>>
>>> I can reduce (some) but not eliminate the need to update the view
>>> multiple times a day. What might be some good ways to prevent queries
>>> B through N blocking?
>>>
>>> Addendum: I can work around the issue by timing out and failing the
>>> CREATE OR REPLACE VIEW (by canceling the query) after a short
>>> duration, but is there a better way?
>>
>> Yeah -- this is just asking for trouble.  Why do you have to replace
>> the view every 30 minutes?  Your solution is probably going to involve
>> not doing that.
>
> As I said, I can reduce the frequency, but not eliminate it. I'm
> curious - if views are little more than sql macros, by the time the
> query has begun to execute why is a lock still needed on the view
> definition?

Generally speaking, in SQL, locks are held until the transaction
commits; there are tons of reasons why things have to work that way.
Anyways, I'm betting your requirement to have to re-CREATE the view
can be abstracted out somehow.  I'm guessing you have some type of
table rotation going on?

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] views, queries, and locks

On Tue, Apr 3, 2012 at 1:36 PM, Merlin Moncure  wrote:
> On Tue, Apr 3, 2012 at 12:30 PM, Jon Nelson  wrote:
>> On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure  wrote:
>>> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson  
>>> wrote:
 I have a situation that I'd like some help resolving.
 Using PostgreSQL 8.4. on Linux, I have three things
 coming together that cause me pain. I have a VIEW used by a bunch of
 queries. Usually, these queries are fairly short (subsecond) but
 sometimes they can be very long (days). I also update this view with
 CREATE OR REPLACE VIEW every 15-30 minutes. What I observe, sometimes,
 is this:

 1. query A comes in. It's a big one.
 2. another process comes along, needs to update the view definition.
 It issues create or replace view. It blocks on [1].
 3. queries B through N come in, are blocked by [2], which is blocked by 
 [1].
 4. pandemonium!

 I can reduce (some) but not eliminate the need to update the view
 multiple times a day. What might be some good ways to prevent queries
 B through N blocking?

 Addendum: I can work around the issue by timing out and failing the
 CREATE OR REPLACE VIEW (by canceling the query) after a short
 duration, but is there a better way?
>>>
>>> Yeah -- this is just asking for trouble.  Why do you have to replace
>>> the view every 30 minutes?  Your solution is probably going to involve
>>> not doing that.
>>
>> As I said, I can reduce the frequency, but not eliminate it. I'm
>> curious - if views are little more than sql macros, by the time the
>> query has begun to execute why is a lock still needed on the view
>> definition?
>
> Generally speaking, in SQL, locks are held until the transaction
> commits; there are tons of reasons why things have to work that way.
> Anyways, I'm betting your requirement to have to re-CREATE the view
> can be abstracted out somehow.  I'm guessing you have some type of
> table rotation going on?

Close, but not quite. It's not rotation but every N minutes a
newly-built table appears. I'd like that table to appear as part of
the view as soon as possible.

Regarding locks: I know that's how locks usually work - but what I'm
asking is if holding on to the lock for the view, once the view has
been 'expanded', is necessary at all.

-- 
Jon

-- 
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] Re: Please help me to take a look of the erros in my functions. Thanks.

If You mean parent and child tables as connected by relation (primery key -
foreign key) then child table will be truncated regardless the relation
type, if CASCADE exists.
This applies to PG 9.1.3 (I've got only this version).

Regards,
Bartek


2012/4/3 leaf_yxj 

> Hi Bartek
> One more question, In oracle, when you create table using the default
> option, the parent table can't be delete if there is any child table exist.
> Usually, I won't use the cascade option.  I will truncate or delete one by
> one. what is postgresql default for these???
>
>
> Thanks.
>
> Regards.
>
> Grace
>
>
> At 2012-04-04 01:15:40,"Bartosz Dmytrak [via PostgreSQL]" <[hidden 
> email]>
> wrote:
>
> One more thing:
> TRUNCATE has option CASCASE:
> http://www.postgresql.org/docs/9.1/static/sql-truncate.html
>
> I don't remember since when, but 9.X has this option.
>
> Another thing: Do You really need this function.
> AFAIK since 8.4 postgres has TRUNCATE privilage on Table
> http://www.postgresql.org/docs/9.1/static/sql-grant.html
> this is not the same as DELETE so, I think it is enough to grant this
> privilage to user
>
> Regards,
> Bartek
>
>
> 2012/4/3 leaf_yxj <[hidden 
> email]
> >
>
>> Tom,
>>
>> Thanks.  I found out the key issue it. It's because the truncate command
>> can't have the "cascade".
>>
>> For the other people reference. The right funcitons are :
>>
>> ***
>>
>> CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
>> RETURNS VOID
>> AS
>> $$
>> BEGIN
>>EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
>> EXCEPTION
>>WHEN undefined_table THEN
>>RAISE EXCEPTION 'Table "%" does not exists', tablename;
>> END;
>> $$
>> LANGUAGE plpgsql SECURITY DEFINER STRICT;
>>
>> ***
>>
>> ***
>>
>> CREATE OR REPLACE FUNCTION truncate_t (tablename text)
>> RETURNS VOID
>> AS
>> $$
>> BEGIN
>>EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
>> EXCEPTION
>>WHEN undefined_table THEN
>>RAISE EXCEPTION 'Table "%" does not exists', tablename;
>> END;
>> $$
>> LANGUAGE plpgsql SECURITY DEFINER STRICT;
>>
>> ***
>>
>>
>> usage : select truncate_t ('aaa');
>>
>>
>> Thanks everybody's help.
>>
>> Regards.
>>
>> Grace
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>  --
>> Sent via pgsql-general mailing list ([hidden 
>> email]
>> )
>>
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615860.html
> To unsubscribe from Please help me to take a look of the erros in my
> functions. Thanks., click here.
> NAML
>
>
>
>
> --
> View this message in context: Re:Re: Please help me to take a look of the
> erros in my functions. 
> Thanks.
>  Sent from the PostgreSQL - general mailing list 
> archiveat
>  Nabble.com.
>

Re: [GENERAL] views, queries, and locks

2012-04-03 Thread Merlin Moncure

On Tue, Apr 3, 2012 at 1:41 PM, Jon Nelson  wrote:
> On Tue, Apr 3, 2012 at 1:36 PM, Merlin Moncure  wrote:
>> Generally speaking, in SQL, locks are held until the transaction
>> commits; there are tons of reasons why things have to work that way.
>> Anyways, I'm betting your requirement to have to re-CREATE the view
>> can be abstracted out somehow.  I'm guessing you have some type of
>> table rotation going on?
>
> Close, but not quite. It's not rotation but every N minutes a
> newly-built table appears. I'd like that table to appear as part of
> the view as soon as possible.

How sophisticated are the queries that are touching this view?  How
much data in the tables?  If you don't need to push quals down into
the view, we can do a view wrapping function that can pick up the new
tables.

> Regarding locks: I know that's how locks usually work - but what I'm
> asking is if holding on to the lock for the view, once the view has
> been 'expanded', is necessary at all.

Unfortunately, it is.

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] views, queries, and locks

2012-04-03 Thread Thomas Kellerer


Jon Nelson wrote on 03.04.2012 20:41:

Close, but not quite. It's not rotation but every N minutes a
newly-built table appears. I'd like that table to appear as part of
the view as soon as possible.


Can't you use table inheritance for that?




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] CPU Load 100% when pg_dump start in Postgresql 8.4

2012-04-03 Thread Prashant Bharucha

Hello Everyone

I facing a big problem ,when pg_dump start .CPU load become 100%.

DB Size 35 GB running with e commerce web site. Insert transaction record 
successfully but Update transaction is not going through.

Could you please help to figure out where is the problem ?


Thanks
Prashant

Re: [GENERAL] views, queries, and locks

2012-04-03 Thread Scott Marlowe

On Tue, Apr 3, 2012 at 1:45 PM, Thomas Kellerer  wrote:
> Jon Nelson wrote on 03.04.2012 20:41:
>
>> Close, but not quite. It's not rotation but every N minutes a
>> newly-built table appears. I'd like that table to appear as part of
>> the view as soon as possible.
>
>
> Can't you use table inheritance for that?

It could well be that inherited tables are or at least were slower
than a view of individual tables.

-- 
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] Re: Please help me to take a look of the erros in my functions. Thanks.

2012-04-03 Thread John R Pierce


On 04/03/12 11:13 AM, leaf_yxj wrote:
Hi John, Thanks for your reply. Just to confirm :  so truncate table 
means the space will be reclaim for reuse ???


yes, all the tablespace is immediately returned to the file system when 
the transaction with the TRUNCATE statement commits.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
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] views, queries, and locks