[GENERAL] Re: Reply: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython language

[Raghavendra]

Foremost, its interesting, why your replies populate as distinct thread
rather than of continuing one. (I may be incorrect, are you choosing
"forward" option while replying, no ?). Request to hold the continuity of
the post instead of different chunks for each reply. :)

On Thu, Jul 18, 2013 at 12:04 PM, guxiaobo1982  wrote:

> Problem with ActivePython-2.7.2.5 is
>

No. It compiles well with this version too.

This time I have not tweaked ActivePython binary files like I did for AP
3.2, I have just enabled Active Python 2.7 in my path while compiling
source.

export PATH=/opt/ActivePython-2.7/bin:$PATH
./configure --prefix=/usr/local/pg93b2 --with-python
make shared_libpython=yes
make shared_libpython=yes install


export PYTHONHOME=/opt/ActivePython-2.7/
export PYTHONPATH=/opt/ActivePython-2.7/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pg93b2/lib
export LD_LIBRARY_PATH=/opt/ActivePython-2.7/lib:$LD_LIBRARY_PATH

-bash-4.1$ ./psql -p 
psql (9.3beta2)
Type "help" for help.

postgres=#
postgres=# create language plpython2u;
CREATE LANGUAGE

By chance if you have missed this part of the PG documentation reading,
then please go through it, it has clear instructions of how you enable
Plpython2 or Plpython3. In particular, the tip section

http://www.postgresql.org/docs/9.3/static/plpython-python23.html

"*Tip:* The built variant depends on which Python version was found during
the installation or which version was explicitly set using the PYTHON
environment
variable; see Section
15.4.
To make both variants of PL/Python available in one installation, the
source tree has to be configured and built twice."



>
[postgres@lix stado]$ psql
> psql (9.3beta2)
> Type "help" for help.
>
> postgres=# create language plpython2u;
> ERROR:  could not load library
> "/opt/PostgreSQL/93b2src/lib/postgresql/plpython2.so":
> /opt/PostgreSQL/93b2src/lib/postgresql/plpython2.so: undefined symbol:
> PyObject_Bytes
>
>
Not certain on the error. Retake the test with above steps you should
succeed.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/



> with environment variables as
>
> export PGDATA=/opt/PostgreSQL/pgdata/93b2src
> export PYTHONHOME=/opt/ActivePython-2.7
> export PYTHONPATH=/opt/ActivePython-2.7/bin
>
> export PATH=/opt/PostgreSQL/93b2src/bin:$PYTHONHOME/bin:$PATH
> export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH
>
>
>
>
>
> -- Original --
> *Sender:* "Raghavendra";
> *Send time:* Tuesday, Jul 16, 2013 4:53 PM
> *To:* "guxiaobo1982"; **
> *Cc:* "Jov"; "Michael Paquier";
> "pgsql-general"; "Asif Naeem"<
> asif.na...@enterprisedb.com>; **
> *Subject:* Re: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython
> language
>
> On Mon, Jul 15, 2013 at 7:10 PM, guxiaobo1982  wrote:
>
>> It works with ActivePython 3.2.2.3,
>>
>
> Thanks for notifying.
>
>
>> but not ActivePython 2.7.2.5.
>>
>>
> No idea... :),  I tried AP 3.2 with PG 9.2 & PG 9.3B it went fine for me.
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>
>
>
>
>
>
>
>>
>> -- Original --
>> *From: * "Raghavendra";
>> *Date: * Jul 11, 2013
>> *To: * "guxiaobo1982"; **
>> *Cc: * "Jov"; "Michael Paquier"<
>> michael.paqu...@gmail.com>; "pgsql-general";
>> "Asif Naeem"; **
>> *Subject: * Re: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython
>> language
>>
>> On Wed, Jul 10, 2013 at 7:36 AM, guxiaobo1982 wrote:
>>
>>> I am using PostgreSQL 9.3 beta 2
>>>
>>> [postgres@lix Multicorn-master]$ psql
>>> psql (9.3beta2)
>>> Type "help" for help.
>>>
>>> postgres=#
>>>
>>>
>>> It does not work with
>>> ActivePython3.2(ActivePython-3.2.2.3-linux-x86_64.tar.gz), Which version of
>>> PostgreSQL are you running?
>>>
>>>
>> Sorry for being late on this email. I overlooked the version you were
>> pointing. I didn't test on PG 9.3Beta, I did on PG 9.2 after looking your
>> error showing PG 9.2.4
>>
>> [postgres@lix PostgreSQL]$ psql
>>> Password:
>>> psql (9.2.4)
>>> Type "help" for help.
>>> postgres=# create language plpython2u;
>>> ERROR: could not access file "$libdir/plpython2": No such file or
>>> directory
>>> postgres=# create language plpython3u;
>>> ERROR: could not access file "$libdir/plpython3": No such file or
>>> directory
>>
>>
>> Later, I began testing it with PG 9.3beta and encountered same error
>> message as you have shared here.
>> Did some analysis and finally succeeded to create language plpython3u
>> with AP-3.2, however with not many tweaks in compilation.(Steps might be
>> arguing. Pardon me).
>>
>> Despite the fact that I have ActivePython-3.2 on my system, source
>> compilation was looking for shared library. Hence compiled explicitly with
>> shared_libpython=yes.
>>
>> Pre-Steps: (Assuming you have ActivePython 3.2)
>>
>> cd /opt/ActivePython-3.2/bin/
>> cp python3.2-config python-config
>> cp python3. python
>>
>> Test:
>> 

Re: [GENERAL] Re: Reply: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython language

[John R Pierce]

On 7/19/2013 12:12 AM, Raghavendra wrote:
... its interesting, why your replies populate as distinct thread 
rather than of continuing one


the mailer they are using isn't generating 'In-Reply-To' and/or 
'References' headers.



--
john r pierce  37N 122W
somewhere on the middle of the 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: [GENERAL] Re: Reply: Reply: [GENERAL] 回复： [GENERAL] Can't create plpython language

[Raghavendra]

On Fri, Jul 19, 2013 at 1:30 PM, John R Pierce  wrote:

> On 7/19/2013 12:12 AM, Raghavendra wrote:
>
>> ... its interesting, why your replies populate as distinct thread rather
>> than of continuing one
>>
>
> the mailer they are using isn't generating 'In-Reply-To' and/or
> 'References' headers.
>
>
> Oh Ok. Thank you for elucidating.

--Raghav

Re: [GENERAL] Best Postgresql books

[Pedro Costa]

Thanks Steven


Em 18-07-2013 23:20, Steven Schlansker escreveu:

PostgreSQL 9.0 High Performance: http://www.amazon.com/books/dp/184951030X

On Jul 18, 2013, at 3:11 PM, Pedro Costa  wrote:


Hi guys,

Can anyone tell me the best books about postgresql? Specialy about tunning and 
querys performances
Thanks



Enviado do meu tablet Samsung Note




--
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] FW: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure

[George Weaver]

From: Stephen Brearley
To: pgsql-general@postgresql.org
Sent: Wednesday, July 17, 2013 5:41 AM
Subject: [GENERAL] FW: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400,
re-installation failure





The Problem
On re-installing Postgres, I have not been able to get it to work. During
re-installation I get an error message to say the program exited with an
error code, but otherwise appears to be okay. However, when I go into
pgadmin and enter my password to connect to the server (any password gives
the same response), I get a popup saying 'Server doesn't listen', could
not connect, connection refused etc'. The setup defaults to port 5432
during the install process. I've checked postgresql.conf which has not
been installed, but I've looked at an old copy that I moved into the data
folder, and that looks at port 5432. Navicat also fails to connect.


I would suggest that you focus on getting PostgreSQL to install properly and 
worry about pulling in your data after this is accomplished.


One option would be to uninstall PostgreSQL, delete the PostgreSQL user, and 
delete all references to PostgreSQL from the registry.



the install program balks at having a data folder that is not empty,


Before trying to re-install PostgreSQL, rename this data directory to 
something else so that the new install can proceed with the normal install 
defaults.


Once your system is "clean", re-install PostgreSQL using the installer 
defaults.


Until you can get PostgreSQL to install correctly there's no point in 
messing around with a broken installation.


George





--
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] Hot Standby and Foreign Tables

[Tim Kane]

Brilliant. Thanks Michael. That looks great.






On Thu, Jul 18, 2013 at 11:54 PM, Michael Paquier  wrote:

>
>
> On Thu, Jul 18, 2013 at 11:15 PM, Tim Kane  wrote:
> > Hi all,
> >
> > I'm currently playing a few ideas out in my head and wanted to see if
> this
> > was feasible. There may be some limitation I am not aware of.
> >
> > I would like to create a foreign table into a hot standby instance using
> > FDW's.  I appreciate that the hot standby is always going to be
> read-only,
> > and that foreign tables are currently read-only anyway.
> >
> > Can anyone tell me if there is any reason this wouldn't work?
> Yes it will work, the only thing necessary is to create the foreign table
> on master. Then the foreign table created is propagated to the slaves.
>
> Here is the example of a foreign table referencing a table created on
> master and linked directly to the slave, using postgres_fdw. The cluster
> uses one master in sync with one slave, master listening to port 5432 and
> slave to 5532 on the same server.
> $ ps x | egrep "5432|5532"
>   787 pts/0S  0:00 /home/mpaquier/bin/pgsql/bin/postgres -D
> /home/mpaquier/bin/pgsql/master -i -p 5432
>   809 pts/0S  0:00 /home/mpaquier/bin/pgsql/bin/postgres -D
> /home/mpaquier/bin/pgsql/slave1 -i -p 5532
>
> $ psql postgres
> postgres=# SELECT pid, application_name,
> pg_xlog_location_diff(sent_location, flush_location) AS replay_delta,
> sync_state FROM pg_stat_replication;
>  pid | application_name | replay_delta | sync_state
> -+--+--+
>  821 | slave1   |0 | sync
> (1 row)
> postgres=# CREATE EXTENSION postgres_fdw;
> CREATE EXTENSION
> postgres=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw
> OPTIONS (host 'localhost', port '5532', dbname 'postgres');
> CREATE SERVER
> postgres=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS
> (password '');
> CREATE USER MAPPING
> postgres=# CREATE TABLE foo AS SELECT generate_series(1,3) AS a;
> SELECT 3
> postgres=# CREATE FOREIGN TABLE aa_foreign (a int) SERVER postgres_server
> OPTIONS (table_name 'foo');
> CREATE FOREIGN TABLE
> postgres=# select * from aa_foreign;
>  a
> ---
>  1
>  2
>  3
> (3 rows)
> In this case the process has read the data directly from table from with a
> foreign table queried on master.
>
> As the foreign table has been created on master, you can as well read the
> foreign table directly on slave (in this case the foreign table will simply
> read data on the same node as you connect to).
> $ psql -p 5532 postgres -c 'select * from aa_foreign'
>  a
> ---
>  1
>  2
>  3
> (3 rows)
>
> Of course you can as well link multiple clusters like that, etc.
>
> >
> > I'm unable to test it just yet as I've not setup replication, nor am I
> on a
> > recent enough postgres. yet. ;-)
> There are many scripts and manuals around to help you in that.
>
> > While I'm at it, is there any facility (future?) to provide a foreign
> schema
> > variant - such that I could access an entire schema using FDW's?
> No plans AFAIK.
>
> Regards,
> --
> Michael
>

Re: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

[guxiaobo1982]

There is another situation,
 
We have a demo table with about 1700 rows,  the "select count(1) from  
demotable" statement finishes with-in 3  seconds when executed directlly 
against the Greenplum database,but it takes about 230 seconds to finish when 
executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it may 
because of postgres_fdw pulling data to the PostgreSQL instance and counts the 
rows there, but I think the query optimizer should pass through the count() 
function to the Greenplum end, and gets only the result back.
 
Regards,
 
Xiaobo Gu
 




-- Original --
From:  "guxiaobo1982";
Date:  Jul 16, 2013
To:  "Tom Lane"; 
Cc:  "Jov"; "pgsql-general"; 
Subject:  Reply: Reply: [GENERAL] Can we specify transaction level when 
connectting toexternal postgresql server via postgres_fdw



Greeplum is based on 8.2.15, so we can only use the read-only option.




-- Original --
Sender: "Tom Lane";
Send time: Tuesday, Jul 16, 2013 1:57 PM
To: "guxiaobo1982"; 
Cc: "Jov"; "pgsql-general"; 
Subject: Re: Reply: [GENERAL] Can we specify transaction level when connectting 
toexternal postgresql server via postgres_fdw



"=?gb18030?B?Z3V4aWFvYm8xOTgy?="  writes:
> It works for insert and select statement under serializable level, but it 
> seems update/delete statements are not support, is that true?
> template1=# update user_info set info='1234' where id=102;
> ERROR:  DECLARE CURSOR ... FOR UPDATE/SHARE is not supported
> DETAIL:  Cursors must be READ ONLY.

The postgres_fdw documentation says

postgres_fdw can be used with remote servers dating back to
PostgreSQL 8.3. Read-only capability is available back to 8.1.

I don't recall exactly when Greenplum forked off from Postgres, but
8.1 or so wouldn't surprise me.  The quoted error message looks about
like what you'd get when trying to use postgres_fdw with a pre-8.3
remote server.

regards, tom lane
.

Re: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

[bricklen]

On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982  wrote:

> There is another situation,
>
> We have a demo table with about 1700 rows,  the "select count(1) from
> demotable" statement finishes with-in 3  seconds when executed directlly
> against the Greenplum database,but it takes about 230 seconds to finish
> when executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it
> may because of postgres_fdw pulling data to the PostgreSQL instance and
> counts the rows there, but I think the query optimizer should pass through
> the count() function to the Greenplum end, and gets only the result back.
>

Are you able to create a function in Greenplum which is a wrapper around
that count(*) and call that via the postgres_fdw?

[GENERAL] Insert result does not match record count

[Natalie Wenz]

Hi all,

I am moving some data from one table to another in 9.2.4, and keep seeing this 
strange scenario:

insert into newtable select data from oldtable where proc_date >= x and 
proc_date < y;

INSERT 0 78551642

select count(*) from newtable where proc_date >= x and proc_date < y;
   count   
---
 4373518938

select count(*) from oldtable where proc_date >= x and proc_date < y;

   count   
---
 4373518938


So, my counts from the old and new tables match, but the result returned from 
the insert statement is sometimes a completely different number. (But not 
always.) I've checked my date ranges very carefully to make sure they match. 

Has anyone else ever seen this before? Should I be concerned? 


Thanks,
Natalie

-- 
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] Insert result does not match record count

[Tom Lane]

Natalie Wenz  writes:
> I am moving some data from one table to another in 9.2.4, and keep seeing 
> this strange scenario:
> ...
> So, my counts from the old and new tables match, but the result returned from 
> the insert statement is sometimes a completely different number. (But not 
> always.) I've checked my date ranges very carefully to make sure they match. 

Perhaps you've got triggers or rules on the target table that are
suppressing some inserts?

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

Reply: [GENERAL] Can we specify transaction level

[guxiaobo1982]

what does a wrapper function mean, count is a standard function in Greenplum, 
or how to creat the wrapper function.

regards
 
On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982  wrote:
 
 There is another situation,
 
We have a demo table with about 1700 rows,  the "select count(1) from  
demotable" statement finishes with-in 3  seconds when executed directlly 
against the Greenplum database,but it takes about 230 seconds to finish when 
executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it may 
because of postgres_fdw pulling data to the PostgreSQL instance and counts the 
rows there, but I think the query optimizer should pass through the count() 
function to the Greenplum end, and gets only the result back.
 
Are you able to create a function in Greenplum which is a wrapper around that 
count(*) and call that via the postgres_fdw?

Re: Reply: [GENERAL] Can we specify transaction level

[bricklen]

On Fri, Jul 19, 2013 at 5:08 PM, guxiaobo1982  wrote:

>
> what does a wrapper function mean, count is a standard function in Greenplum, 
> or how to creat the wrapper function.
>
> regards
>
> On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982  wrote:
>
>  There is another situation,
>
>
> We have a demo table with about 1700 rows,  the "select count(1) from  
> demotable" statement finishes with-in 3  seconds when executed directlly 
> against the Greenplum database,but it takes about 230 seconds to finish when 
> executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it may 
> because of postgres_fdw pulling data to the PostgreSQL instance and counts 
> the rows there, but I think the query optimizer should pass through the 
> count() function to the Greenplum end, and gets only the result back.
>
>
> Are you able to create a function in Greenplum which is a wrapper around that 
> count(*) and call that via the postgres_fdw?
>

Create a function in Greenplum like (untested, I don't have Greenplum)

create table custom_count() returns bigint as $$
select count(*) as total from yourtable;
$$ language sql;

Then from your pg db using postgres_fdw select * from that function (which
resides in Greenplum).

No idea if that'll work or make a difference though.

Reply: [GENERAL] Can we specify transaction level

[guxiaobo1982]

I wil test next week, but the document of postgres_fdw says it is smart enough 
to push executing into the external server, I wander is this a bug or a version 
compability problem.

---原始邮件---
在 2013年07月20日 10:05:02
"bricklen" 写道：
 
On Fri, Jul 19, 2013 at 5:08 PM, guxiaobo1982  wrote:
 what does a wrapper function mean, count is a standard function in Greenplum, 
or how to creat the wrapper function.
 
regards
 
On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982  wrote:
 
 There is another situation,
 
We have a demo table with about 1700 rows,  the "select count(1) from  
demotable" statement finishes with-in 3  seconds when executed directlly 
against the Greenplum database,but it takes about 230 seconds to finish when 
executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it may 
because of postgres_fdw pulling data to the PostgreSQL instance and counts the 
rows there, but I think the query optimizer should pass through the count() 
function to the Greenplum end, and gets only the result back.
  
Are you able to create a function in Greenplum which is a wrapper around that 
count(*) and call that via the postgres_fdw? 

 

Create a function in Greenplum like (untested, I don't have Greenplum)
 

create table custom_count() returns bigint as $$

select count(*) as total from yourtable;

$$ language sql;


Then from your pg db using postgres_fdw select * from that function (which 
resides in Greenplum).
 

No idea if that'll work or make a difference though.