Re: [GENERAL] query on query

On Fri, Jul 5, 2013 at 5:09 AM, Jayadevan M wrote: > > So each student may get counted many times, someone with 99 will be counted > 10 times. Possible to do this with a fat query? The table will have many > thousands of records. > Not sure I got the point, but I guess this is a good candidate

[GENERAL] ?????? [GENERAL] Can't create plpython language

I tried pgdg-centos92-9.2-6.noarch.rpm following http://www.postgresonline.com/journal/archives/203-postgresql90-yum.html, I installed "postgresql92-plpython.x86_64 9.2.4-1PGDG.rhel5 installed," but it still does not work [postgres@lix PostgreSQL]$ psql Password: ps

[GENERAL] query on query

Hi, I have a table like this - postgres=# \d m Table "public.m" Column | Type | Modifiers +-+--- id | integer | marks | integer | postgres=# select * from m; id | marks +--- 1 |27 2 |37 3 |17 4 |27 5 |18 (5 rows) I wante

[GENERAL] decrease my query duration

Hello All I am newbie in this forum that's why I will be very grateful if would be able to help me and if you need some additional information please let me know. I have a query that I would like decrease its duration SELECT s0.SEARCH AS c0, s0.id AS c0c1, s0.id AS c1, s1.nam

Re: [GENERAL] decrease my query duration

On Fri, Jul 5, 2013 at 10:04 AM, bricklen wrote: > On Thu, Jul 4, 2013 at 5:26 PM, David Carpio > wrote: > Also, can you supply the EXPLAIN (ANALYZE, BUFFERS) plan instead of the > simple EXPLAIN plan? Then it might be interesting that you scan what is wrong with this utility: http://explain.depe

Re: [GENERAL] decrease my query duration

On Thu, Jul 4, 2013 at 5:26 PM, David Carpio wrote: > Hello All > > I am newbie in this forum that's why I will be very grateful if would be > able to help me and if you need some additional information please let me > know. > I have a query that I would like decrease its duration > Have a loo

[GENERAL] Re: How to create a cursor that is independent of transactions and doesn't calculated when created ?

boraldomaster wrote > > David Johnston wrote >> Your original examples only create the cursor and do not actually use it. >> You should be comparing how long it takes both examples to fetch the >> first 10 pages of records to get a meaningful comparison. It won't >> matter if the DECLARE only ta

[GENERAL] decrease my query duration

Hello All I am newbie in this forum that's why I will be very grateful if would be able to help me and if you need some additional information please let me know. I have a query that I would like decrease its duration SELECT s0.SEARCH AS c0,s0.id AS c0c1,s0.id AS c1,s1.name AS c2,s1.subtypei

Re: [GENERAL] Can't create plpython language

try the postgresql official rpm package for centos 5： http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm or build your own from source. Jov blog: http:amutu.com/blog 2013/7/5 Michael Paquier > On Thu, Jul 4, 2013 at 6:26 PM, guxiaobo1982

Re: [GENERAL] Can't create plpython language

On Thu, Jul 4, 2013 at 6:26 PM, guxiaobo1982 wrote: > Hi, > > I am running PostgreSQL 9.2.4.1 (from EnterpriseDB) on CENTOS 5.9 X64 > server, I got the following errors when trying to create the python language > handler, can you help with this, thanks in advance. If this is PPAS, as the version n

Re: [GENERAL] Efficiency of materialized views refresh in 9.3

On Fri, Jul 5, 2013 at 6:10 AM, Joe Van Dyk wrote: > Hi, > > Is refreshing a materialized view in 9.3 basically: > > delete from mat_view; > insert into mat_view select * from base_view; Nope. Here is some documentation: http://www.postgresql.org/docs/devel/static/rules-materializedviews.html

[GENERAL] Efficiency of materialized views refresh in 9.3

Hi, Is refreshing a materialized view in 9.3 basically: delete from mat_view; insert into mat_view select * from base_view; Or is it more efficient? If no rows have changed, will new tuples be written on a refresh? Joe

Re: [GENERAL] Cannot connect to remote postgres database

On 07/03/2013 11:10 PM, John R Pierce wrote: On 7/3/2013 10:51 PM, Pavel Stehule wrote: 2013/7/4 Stephen Carville: On 07/03/2013 01:27 PM, Andrew Sullivan wrote: Nothin' for nothin', but . . . On Wed, Jul 03, 2013 at 01:11:35PM -0700, Stephen Carville wrote: I have the software (v 8.4.13) i

Re: [GENERAL] unable to call a function

Hello 2013/7/4 Adrian Klaver : > On 07/04/2013 10:14 AM, giozh wrote: >> >> something gone wrong the same... >> >> REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name >> character, table_column character) >>RETURNS boolean AS >> $BODY$ >> >> DECLARE res BOOLEAN; >> >> BEGIN >>

Re: [GENERAL] unable to call a function

On 07/04/2013 10:14 AM, giozh wrote: something gone wrong the same... REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name character, table_column character) RETURNS boolean AS $BODY$ DECLARE res BOOLEAN; BEGIN EXECUTE 'SELECT EXISTS(SELECT * FROM'||table_name||

Re: [GENERAL] unable to call a function

On 07/04/2013 10:14 AM, giozh wrote: something gone wrong the same... REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name character, table_column character) RETURNS boolean AS $BODY$ DECLARE res BOOLEAN; BEGIN EXECUTE 'SELECT EXISTS(SELECT * FROM'||table_name||

Re: [GENERAL] unable to call a function

something gone wrong the same... REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name character, table_column character) RETURNS boolean AS $BODY$ DECLARE res BOOLEAN; BEGIN EXECUTE 'SELECT EXISTS(SELECT * FROM'||table_name|| 'WHERE'||table_column||'='||$

Re: [GENERAL] unable to call a function

On 07/04/2013 09:33 AM, giozh wrote: ok, i've modify mi function, but now i'm not able to execute it: SELECT check_if_exist(10, table, col); ERROR: column "table" does not exist test=> select check_if_if_exist(1, 'int_test', 'i'); check_if_if_exist --- t (1 row) You nee

Re: [GENERAL] unable to call a function

ok, i've modify mi function, but now i'm not able to execute it: SELECT check_if_exist(10, table, col); ERROR: column "table" does not exist -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590p5762600.html Sent from the PostgreSQL -

Re: [GENERAL] unable to call a function

ok, i've modify mi function, but now i'm not able to execute it: SELECT check_if_exist(10, table, col); ERROR: column "table" does not exist -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590p5762599.html Sent from the PostgreSQL - ge

Re: [GENERAL] unable to call a function

On 07/04/2013 08:53 AM, giozh wrote: i've write this function that search if inside a specified table there's a specified value: CREATE FUNCTION check_if_if_exist(id INTEGER, table_name character(50), table_column character(20) ) RETURNS BOOLEAN AS $$ BEGIN RETURN EXECUTE 'SELECT EXISTS

Re: [GENERAL] unable to call a function

You are passing the literal value "table_name" as the table, and "column_name" as the column. You need to concatenate the substituted values onto the string with the || operator: return execute 'select exists(select * from ' || quote_ident(table_name) || ' where ' || quote_ident(table_column) || '

[GENERAL] Re: How to create a cursor that is independent of transactions and doesn't calculated when created ?

David Johnston wrote > Your original examples only create the cursor and do not actually use it. > You should be comparing how long it takes both examples to fetch the first > 10 pages of records to get a meaningful comparison. It won't matter if > the DECLARE only takes 3ms in the non-hold case

[GENERAL] unable to call a function

i've write this function that search if inside a specified table there's a specified value: CREATE FUNCTION check_if_if_exist(id INTEGER, table_name character(50), table_column character(20) ) RETURNS BOOLEAN AS $$ BEGIN RETURN EXECUTE 'SELECT EXISTS(SELECT * FROM table_name WHERE table

Re: [GENERAL] Can't create plpython language

On 07/04/2013 02:26 AM, guxiaobo1982 wrote: Hi, I am running PostgreSQL 9.2.4.1 (from EnterpriseDB) on CENTOS 5.9 X64 server, I got the following errors when trying to create the python language handler, can you help with this, thanks in advance. Did you install the plpython language as part

Re: [GENERAL] Best Table to find Query Logs.

On 07/04/2013 12:51 AM, G N wrote: Dear All, Hope you are doing well. I wanted to find out the suspicious queries run by users. Can you please suggest the best system table in PG/ Greenplum to find out such ? Appreciate your quick response. http://www.postgresql.org/docs/9.2/interactive/moni

[GENERAL] Best Table to find Query Logs.

Dear All, Hope you are doing well. I wanted to find out the suspicious queries run by users. Can you please suggest the best system table in PG/ Greenplum to find out such ? Appreciate your quick response. Regards, Girish

[GENERAL] Re: How to create a cursor that is independent of transactions and doesn't calculated when created ?

boraldomaster wrote > But if held cursor was created as fast as unheld - I could change my > opinion. > I don't understand why is this really impossible. > When I create unheld cursor - it takes 1 ms. Why cannot held cursor do the > same (but store in session - or even better in whole db - anything

Re: [GENERAL] odd locking behaviour

Confirmed reproducible on version 9.1 as well. Very odd. On Wed, Jul 3, 2013 at 1:30 PM, pg noob wrote: > > Hi all, > > I am trying to understand some odd locking behaviour. > I apologize in advance if this is a basic question and should be widely > understood but > I don't see it described in

[GENERAL] Re: How to create a cursor that is independent of transactions and doesn't calculated when created ?

My use-case is just creating paginated list for a large table. The first obvious option is offset limit but it works too slow for great offset. A lot of topics propose using cursors for that, so I am learning this possibility. You will say that there are other possibilities. Yes - but I am trying t

[GENERAL] Can't create plpython language

Hi, I am running PostgreSQL 9.2.4.1 (from EnterpriseDB) on CENTOS 5.9 X64 server, I got the following errors when trying to create the python language handler, can you help with this, thanks in advance. [postgres@lix ~]$ psql Password: psql.bin (9.2.4) Type "help" for help. No entry for t