[GENERAL] CREATE DATABASE Š [ TEMPLATE [=] template ] [ TABLESPACE [=] tablespace_name ]

[Tim Kane]

I have a question regarding the behaviour of CREATE DATABASE when used with
TEMPLATE and TABLESPACE options together.


The documentation describes the tablespace parameter as:
The name of the tablespace that will be associated with the new database, or
DEFAULT to use the template database's tablespace. This tablespace will be
the default tablespace used for objects created in this database. See CREATE
TABLESPACE 
  for
more information.

I would take that to mean that all objects from the template will be created
in the new database within the specified tablespace.

However it seems this is not the case, and that the newly specified
tablespace will only apply to objects that do not already have a tablespace
defined in the templated database.

Is this a documentation issue, or a behavioural one?  It isn't clear to me.

[GENERAL] Re: CREATE DATABASE Š [ TEMPLATE [=] template ] [ TABLESPACE [=] tablespace_name ]

[David Johnston]

Tim Kane wrote
> The documentation describes the tablespace parameter as:
> The name of the tablespace that will be associated with the new database,
> or
> DEFAULT to use the template database's tablespace. This tablespace will be
> the default tablespace used for objects created in this database. See
> CREATE
> TABLESPACE 
> ; 
> for
> more information.
> 
> Is this a documentation issue, or a behavioural one?  It isn't clear to
> me.

The operative sentence is:

"This tablespace will be the default tablespace used for [new] objects
created in this database."

The implied "new" (inserted because of the word "default" preceding) means
that any existing objects in the template database will be assigned the same
tablespace onto which they are currently attached.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/CREATE-DATABASE-TEMPLATE-template-TABLESPACE-tablespace-name-tp5772451p5772464.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: CREATE DATABASE Š [ TEMPLATE [=] template ] [ TABLESPACE [=] tablespace_name ]

[David Johnston]

Tim Kane wrote
> However it seems this is not the case, and that the newly specified
> tablespace will only apply to objects that do not already have a
> tablespace
> defined in the templated database.

Note that all pre-existing objects have a tablespace by definition.  If one
is not explicitly provided during the object's creation then the database
default tablespace is assigned and permanently affixed.  Thus objects in the
tempate's default tablespace should not (in theory) be affected by changing
the default tablespace of the newly created database - those default
tablespace template objects will still be in the template's default
tablespace - or whichever tablespace was default when the object was created
(since the defaults can be changed).

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/CREATE-DATABASE-TEMPLATE-template-TABLESPACE-tablespace-name-tp5772451p5772466.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] CREATE DATABASE Š [ TEMPLATE [=] template ] [ TABLESPACE [=] tablespace_name ]

[Giuseppe Broccolo]

Il 26/09/2013 13:27, Tim Kane ha scritto:


I have a question regarding the behaviour of CREATE DATABASE when used 
with TEMPLATE and TABLESPACE options together.



The documentation describes the tablespace parameter as:

The name of the tablespace that will be associated with the new
database, or DEFAULT to use the template database's tablespace.
This tablespace will be the default tablespace used for objects
created in this database. See CREATE TABLESPACE
 for
more information.


I would take that to mean that all objects from the template will be 
created in the new database within the specified tablespace.

This is possible setting the parameter 'default_tablespace':

SET default_tablespace = space1;

in this way, an implicit TABLESPACE clause is supplied in the objects 
creation, independently it's done from a template or not.


Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it

Re: [GENERAL] postgres FDW doesn't support sequences?

[Merlin Moncure]

On Wed, Sep 25, 2013 at 4:47 PM, Tom Lane  wrote:
> Lonni J Friedman  writes:
>> If I INSERT a new row into the local table (not the foreign table
>> version), without specifying the 'id' column explicitly, it
>> automatically is assigned the nextval in the sequence counter.
>> However, if I attempt to run the same INSERT using the foreign table,
>> it always fails complaining that null value in column "id" violates
>> not-null constraint.  It seems like the FDW is somehow ignoring the
>> existence of the sequence default value, and rewriting the SQL query
>> to explicitly attempt to insert a NULL value.
>
> Yeah, there was quite a bit of discussion about that back in February or
> so.  The short of it is that column default values that are defined on the
> foreign server are not respected by operations on a foreign table; rather,
> you have to attach a DEFAULT specification to the foreign table definition
> if you want inserts into the foreign table to use that default.
>
> The default expression is executed locally, too, which means that if you'd
> like it to read like "nextval('some_seq')" then some_seq has to be a local
> sequence, not one on the foreign server.
>
> I realize that this isn't ideal for serial-like columns, but honoring
> default expressions that would execute on the foreign server turned out
> to be a huge can of worms.  We might figure out how to fix that some day;
> but if we'd insisted on a solution now, there wouldn't be writable foreign
> tables at all in 9.3.

This situation as well as a lot of other things be worked around if
there was a mechanic to pass SQL (or whatever language the foreign
server accepts) directly through to the foreign server, especially if
it could be optionally parameterized.  Until that happens for most
real world work you're better off using dblink for postgres to
postgres communication.

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] partitioned table + postgres_FDW not working in 9.3

[Lonni J Friedman]

Hi Shigeru,
Thanks for your reply.  This sounds like a relatively simple
workaround, so I'll give it a try.  Is the search_path of the remote
session that postgres_fdw forces considered to be intentional,
expected behavior, or is it a bug?

thanks!

On Wed, Sep 25, 2013 at 7:13 PM, Shigeru Hanada
 wrote:
> Hi Lonni,
>
> 2013/9/25 Lonni J Friedman :
>> The problem that I'm experiencing is if I attempt to perform an INSERT
>> on the foreign nppsmoke table on cluster a, it fails claiming that the
>> table partition which should hold the data in the INSERT does not
>> exist:
>>
>> ERROR:  relation "nppsmoke_2013_09" does not exist
>> CONTEXT:  Remote SQL command: INSERT INTO public.nppsmoke(id,
>> date_created, last_update, build_type, current_status, info, cudacode,
>> gpu, subtest, os, osversion, arch, cl, dispvers, branch, pass, fail,
>> oldfail, newfail, failureslog, totdriver, ddcl, buildid, testdcmd,
>> pclog, filtercount, filterlog, error) VALUES ($1, $2, $3, $4, $5, $6,
>> $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
>> $21, $22, $23, $24, $25, $26, $27, $28)
>> PL/pgSQL function public.nppsmoke_insert_trigger() line 30 at SQL statement
>
> I could reproduce the problem.
>
>> If I run the same exact SQL INSERT on cluster b (not using the foreign
>> table), then it works.  So whatever is going wrong seems to be related
>> to the foreign table.  Initially I thought that perhaps the problem
>> was that I needed to create all of the partitions as foreign tables on
>> cluster a, but that doesn't help.
>>
>> Am I hitting some kind of foreign data wrapper limitation, or am I
>> doing something wrong?
>
> The cause of the problem is search_path setting of remote session.
> For some reasons, postgres_fdw forces the search_path on the remote
> side to be 'pg_catalog', so all objects used in the session
> established by postgres_fdw have to be schema-qualified.  Trigger
> function is executed in such context, so you need to qualify all
> objects in your trigger function with schema name, like
> 'public.nppsmoke_2013_09'.


-- 
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] partitioned table + postgres_FDW not working in 9.3

[Tom Lane]

Lonni J Friedman  writes:
> Thanks for your reply.  This sounds like a relatively simple
> workaround, so I'll give it a try.  Is the search_path of the remote
> session that postgres_fdw forces considered to be intentional,
> expected behavior, or is it a bug?

It's intentional.

Possibly more to the point, don't you think your trigger function is
rather fragile if it assumes the caller has provided a particular
search path setting?

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

Re: [GENERAL] partitioned table + postgres_FDW not working in 9.3

[Lonni J Friedman]

On Thu, Sep 26, 2013 at 8:52 AM, Tom Lane  wrote:
> Lonni J Friedman  writes:
>> Thanks for your reply.  This sounds like a relatively simple
>> workaround, so I'll give it a try.  Is the search_path of the remote
>> session that postgres_fdw forces considered to be intentional,
>> expected behavior, or is it a bug?
>
> It's intentional.
>
> Possibly more to the point, don't you think your trigger function is
> rather fragile if it assumes the caller has provided a particular
> search path setting?

To be honest, I don't have much experience with functions, and was
using the trigger function from the official documentation:
http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html


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

[GENERAL] How do I find a trigger function that is raising notices?

[Rob Richardson]

Greetings!

I've got a query that is behaving strangely, but that's not the reason for this 
question.  The update results in a trigger being fired, but I don't know which 
one.  I thought I disabled all of the triggers on the table being updated.  So, 
I wanted to find the trigger function that contains one of the words in the 
notice message.  A long time ago, someone told me I can search for a function 
containing a given word using this query:

Select * from pg_proc where lower(prosrc) = '%%'

But that's not returning any rows.  Is there a different table that contains 
source for trigger functions?  How can I find out what trigger function raised 
these messages?

Thanks very much!

RobR

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

[GENERAL] pg_basebackup: ERROR: could not find any WAL files (9.3)

[Lonni J Friedman]

Greetings,
I've recently pushed a new postgres-9.3 (Linux-x86_64/RHEL6) cluster
into production, with one master, and two hot standby streaming
replication slaves.  Everything seems to be working ok, however
roughly half of my pg_basebackup attempts are failing at the very end
with the error:

pg_basebackup: could not get transaction log end position from server:
ERROR:  could not find any WAL files

I should note that I'm running pg_basebackup on one of the two slaves,
and not the master.  However, I've got an older, separate 9.3 cluster
with the same setup, and pg_basebackup never fails there.

I thought that the WAL files in question were coming from the pg_xlog
subdirectory.  But I don't see any lack of files there on the server
running pg_basebackup.  They are being generated continuously (as
expected), before, during & after the pg_basebackup.  I scanned the
source ( http://doxygen.postgresql.org/basebackup_8c_source.html ),
and it seems to backup my understanding of the expected behavior:

306  /*
307  * There must be at least one xlog file in the pg_xlog directory,
308  * since we are doing backup-including-xlog.
309  */
310  if (nWalFiles < 1)
311  ereport(ERROR,
312  (errmsg("could not find any WAL files")));

However, what I see on the server conflicts with the error.
pg_basebackup was invoked on Thu Sep 26 01:00:01 PDT 2013, and failed
on Thu Sep 26 02:09:12 PDT 2013.  In the pg_xlog subdirectory, I see
lots of WAL files present, before, during & after pg_basebackup was
run:
-rw--- 1 postgres postgres 16777216 Sep 26 00:38 0001208A00E3
-rw--- 1 postgres postgres 16777216 Sep 26 00:43 0001208A00E4
-rw--- 1 postgres postgres 16777216 Sep 26 00:48 0001208A00E5
-rw--- 1 postgres postgres 16777216 Sep 26 00:53 0001208A00E6
-rw--- 1 postgres postgres 16777216 Sep 26 00:58 0001208A00E7
-rw--- 1 postgres postgres 16777216 Sep 26 01:03 0001208A00E8
-rw--- 1 postgres postgres 16777216 Sep 26 01:08 0001208A00E9
-rw--- 1 postgres postgres 16777216 Sep 26 01:14 0001208A00EA
-rw--- 1 postgres postgres 16777216 Sep 26 01:19 0001208A00EB
-rw--- 1 postgres postgres 16777216 Sep 26 01:24 0001208A00EC
-rw--- 1 postgres postgres 16777216 Sep 26 01:29 0001208A00ED
-rw--- 1 postgres postgres 16777216 Sep 26 01:34 0001208A00EE
-rw--- 1 postgres postgres 16777216 Sep 26 01:38 0001208A00EF
-rw--- 1 postgres postgres 16777216 Sep 26 01:43 0001208A00F0
-rw--- 1 postgres postgres 16777216 Sep 26 01:48 0001208A00F1
-rw--- 1 postgres postgres 16777216 Sep 26 01:53 0001208A00F2
-rw--- 1 postgres postgres 16777216 Sep 26 01:58 0001208A00F3
-rw--- 1 postgres postgres 16777216 Sep 26 02:03 0001208A00F4
-rw--- 1 postgres postgres 16777216 Sep 26 02:08 0001208A00F5
-rw--- 1 postgres postgres 16777216 Sep 26 02:14 0001208A00F6


Thanks in advance for any pointers.


-- 
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] How do I find a trigger function that is raising notices?

[bricklen]

On Thu, Sep 26, 2013 at 9:04 AM, Rob Richardson wrote:

> Select * from pg_proc where lower(prosrc) = '%%'



A slight revision should work:

select distinct proname from pg_proc where prosrc ilike '%%';

[GENERAL] Blowfish Encrypted String

[Craig Boyd]

Hello All,

I have a string in a program that I have encrypted using Blowfish and I am
now trying to figure out the best way to store that in PostgreSQL so that I
can store it and retrieve it later for decryption.  I have searched around
and have not found some good examples of how to go about this.

So my first questions is: Can someone point me to a tutorial or posting
that shows how one might do that?

Failing that:
What data type should I use to store this?
What does the SQL look like to INSERT/SELECT the field?

Thanks,

Craig

Re: [GENERAL] How do I find a trigger function that is raising notices?

[Rob Richardson]

Thanks very much.  I was searching for a string containing an upper-case letter 
without remembering that I used lower().  And your suggestion of ilike is much 
better than like here.

RobR

From: bricklen [mailto:brick...@gmail.com]
Sent: Thursday, September 26, 2013 12:05 PM
To: Rob Richardson
Cc: pgsql-general
Subject: Re: [GENERAL] How do I find a trigger function that is raising notices?


On Thu, Sep 26, 2013 at 9:04 AM, Rob Richardson 
mailto:rdrichard...@rad-con.com>> wrote:
Select * from pg_proc where lower(prosrc) = '%%'

A slight revision should work:

select distinct proname from pg_proc where prosrc ilike '%%';

Re: [GENERAL] Blowfish Encrypted String

[Dmitriy Igrishin]

2013/9/26 Craig Boyd 

> Hello All,
>
> I have a string in a program that I have encrypted using Blowfish and I am
> now trying to figure out the best way to store that in PostgreSQL so that I
> can store it and retrieve it later for decryption.  I have searched around
> and have not found some good examples of how to go about this.
>
> So my first questions is: Can someone point me to a tutorial or posting
> that shows how one might do that?
>
> Failing that:
> What data type should I use to store this?
>
I believe that you should use bytea datatype.

> What does the SQL look like to INSERT/SELECT the field?
>
Just like any other INSERT/SELECT query.


-- 
// Dmitriy.

[GENERAL] Trouble installing psycopg2

[Laura Tateosian]

Hi,  I'm trying to install psycopg2 on a Centos 5, 64-bit machine.   I have
both 2.4 and 2.7 Python versions on this machine.  I attempted to install
using
easy-install2.7 psycopg2

The install is not working.  (I can't import psycopg2 in a python 2.7
session).   yum doesn't list it as installed.  But when I try to install it
again, easy_install responds that it's already installed.  That's the short
version of the problem, more details below that may or may not be helpful.

Thanks,
LT




At first, easy-install2.7 psycopg2 gave me similar errors to what's
described on this page:
http://stackoverflow.com/questions/9600111/install-pg-config-in-centos-5-5-without-yum(something
to do with pg_config not found).  So I followed the advice of
installing devel

yum install postgresql84-devel

...succeeded.

Then, I  "easy-install2.7 psycopg2" again.  In fact I piped the output to a
file and got what I have below, but it also flashed up an error message
which didn't get into this file and I can't see on the history.  But from
that second  that I saw it, I know it had __FILE__ in it.   Now I'm in this
installed/but not installed kind of limbo.
...
Excluding Packages in global exclude list
Finished
Available Packages
python-psycopg2.x86_64  2.0.14-2.el5
epel
python-psycopg2-doc.x86_64  2.0.14-2.el5
epel
python-psycopg2-zope.x86_64 2.0.14-2.el5
epel
root@server [/opt/bin]# easy_install-2.7 psycopg2
Searching for psycopg2
Searching for psycopg2
Reading http://pypi.python.org/simple/psycopg2/
Reading http://initd.org/projects/psycopg2
Best match: psycopg2 2.5.1
Downloading
https://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.5.1
.tar.gz#md5=1b433f83d50d1bc61e09026e906d84c7
Processing psycopg2-2.5.1.tar.gz
Writing /tmp/easy_install-UYNn1g/psycopg2-2.5.1/setup.cfg
Running psycopg2-2.5.1/setup.py -q bdist_egg --dist-dir
/tmp/easy_install-UYN
n1g/psycopg2-2.5.1/egg-dist-tmp-FLKSTs
Adding psycopg2 2.5.1 to easy-install.pth file

Installed
/usr/local/lib/python2.7/site-packages/psycopg2-2.5.1-py2.7-linux-x
86_64.egg
Processing dependencies for psycopg2
Finished processing dependencies for psycopg2

Re: [GENERAL] Blowfish Encrypted String

[Craig Boyd]

Dmitriy,

Thank you very much for the reply!

Right...got the the data type...but how do I actually insert a binary
string into the column?  What does the SQL look like?
For the moment assume I have the following bit of binary: 4PO„âÔ™ä²

What does the SQL look like to insert that into the bytea column?
 (FYI...most of my binary data would be only slightly bigger than what I
just pasted here.  I am not storing Word documents or images, just some
encrypted strings of test that are not more than 100 characters long and
typically about 16 to 20.

Thanks,

Craig Boyd


On Thu, Sep 26, 2013 at 11:46 AM, Dmitriy Igrishin wrote:

>
>
>
> 2013/9/26 Craig Boyd 
>
>> Hello All,
>>
>> I have a string in a program that I have encrypted using Blowfish and I
>> am now trying to figure out the best way to store that in PostgreSQL so
>> that I can store it and retrieve it later for decryption.  I have searched
>> around and have not found some good examples of how to go about this.
>>
>> So my first questions is: Can someone point me to a tutorial or posting
>> that shows how one might do that?
>>
>> Failing that:
>> What data type should I use to store this?
>>
> I believe that you should use bytea datatype.
>
>> What does the SQL look like to INSERT/SELECT the field?
>>
> Just like any other INSERT/SELECT query.
>
>
> --
> // Dmitriy.
>
>

Re: [GENERAL] Trouble installing psycopg2

[David Kerr]

On Thu, Sep 26, 2013 at 01:01:54PM -0400, Laura Tateosian wrote:
- Hi,  I'm trying to install psycopg2 on a Centos 5, 64-bit machine.   I have
- both 2.4 and 2.7 Python versions on this machine.  I attempted to install
- using
- easy-install2.7 psycopg2
- 
- The install is not working.  (I can't import psycopg2 in a python 2.7
- session).   yum doesn't list it as installed.  But when I try to install it
- again, easy_install responds that it's already installed.  That's the short
- version of the problem, more details below that may or may not be helpful.
- 
- Thanks,
- LT
- 
- 
- 
- 
- At first, easy-install2.7 psycopg2 gave me similar errors to what's
- described on this page:
- 
http://stackoverflow.com/questions/9600111/install-pg-config-in-centos-5-5-without-yum(something
- to do with pg_config not found).  So I followed the advice of
- installing devel
- 
- yum install postgresql84-devel
- 
- ...succeeded.
- 
- Then, I  "easy-install2.7 psycopg2" again.  In fact I piped the output to a
- file and got what I have below, but it also flashed up an error message
- which didn't get into this file and I can't see on the history.  But from
- that second  that I saw it, I know it had __FILE__ in it.   Now I'm in this
- installed/but not installed kind of limbo.
- ...
- Excluding Packages in global exclude list
- Finished
- Available Packages
- python-psycopg2.x86_64  2.0.14-2.el5
- epel
- python-psycopg2-doc.x86_64  2.0.14-2.el5
- epel
- python-psycopg2-zope.x86_64 2.0.14-2.el5
- epel
- root@server [/opt/bin]# easy_install-2.7 psycopg2
- Searching for psycopg2
- Searching for psycopg2
- Reading http://pypi.python.org/simple/psycopg2/
- Reading http://initd.org/projects/psycopg2
- Best match: psycopg2 2.5.1
- Downloading
- https://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.5.1
- .tar.gz#md5=1b433f83d50d1bc61e09026e906d84c7
- Processing psycopg2-2.5.1.tar.gz
- Writing /tmp/easy_install-UYNn1g/psycopg2-2.5.1/setup.cfg
- Running psycopg2-2.5.1/setup.py -q bdist_egg --dist-dir
- /tmp/easy_install-UYN
- n1g/psycopg2-2.5.1/egg-dist-tmp-FLKSTs
- Adding psycopg2 2.5.1 to easy-install.pth file
- 
- Installed
- /usr/local/lib/python2.7/site-packages/psycopg2-2.5.1-py2.7-linux-x
- 86_64.egg
- Processing dependencies for psycopg2
- Finished processing dependencies for psycopg2


So easy_install isn't going to intall the yum version, and it may install it 
somewhere
that the system python isn't looking for it.

That's generally why you  don't want to use easy_install or pip if you can 
avoid it.
If you DO use them you should use virtualenv and virtualenvwrapper.

Since you have an OS package, Your best bet is to do 'yum install 
python-psycopg2' 
and see if that works for you.



-- 
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] Trouble installing psycopg2

[Chris Curvey]

On Thu, Sep 26, 2013 at 1:01 PM, Laura Tateosian  wrote:

> Hi,  I'm trying to install psycopg2 on a Centos 5, 64-bit machine.   I
> have both 2.4 and 2.7 Python versions on this machine.  I attempted to
> install using
> easy-install2.7 psycopg2
>
> The install is not working.  (I can't import psycopg2 in a python 2.7
> session).   yum doesn't list it as installed.
>

Yum won't show it.  (Yum is an OS-level package manager, easy_install is a
python-level package manager)


> But when I try to install it again, easy_install responds that it's
> already installed.  That's the short version of the problem, more details
> below that may or may not be helpful.
>

Hmm.  If you're absolutely sure that it's python2.7 that  you're running, I
would get out the big hammer.  Got to
/usr/local/lib/python2.7/site-packages, and rm(dir) anything related to
psycopg2.  Then look in any "*.pth" files and delete lines related to
psycopg2.  Then try re-running easy_install.

You would definitely need the -devel packages for PG because easy_install
is trying to compile a C module.  But I've done the same thing before
(tried to install psycopg2 w/o the devel package installed), and just an
ordinary re-run fixed it for me.

Re: [GENERAL] Trouble installing psycopg2

[Augori]

Thanks, for the replies Chris and David.

Chris, I couldn't find any psycopg files under my Python installs, so I
decided to try David's advice and yum install python-psycopg2
It reported success, but it installed it under
../usr/lib64/python2.4/site-packages

I'm working with Python2.7.5, so psycopg2 won't import.   It believe that
psycopg2 is not written for 2.4 or below.  That's why I installed 2.7.5.
So my question now is, how can I get yum to install it in the 2.7
site-packages?

Thanks.

PS.  About the virtualenv suggestion, I had tried using use virtualenv and
virtualenvwrapper, following the instructions on this nice site.
http://bda.ath.cx/blog/2009/04/08/installing-python-26-in-centos-5-or-rhel5/
But, I couldn't get the bashrc to run without error, after adding this
update

source /opt/bin/virtualenvwrapper_bashrc

So, I thought I'd throw caution to the wind and skip that part.
(that's how I got to easy_intall, which at least allowed me to specify
which python version)

[GENERAL] ENUM drop label workaround

[Sergey Konoplev]

Hi,

AIU we have no ability to drop a label from ENUM currently, and there
are no plans to add this feature in the nearest future.

I came to a workaround via DOMAIN, here it is:

CREATE TYPE ref AS ENUM ('aaa', 'bbb');

CREATE DOMAIN refdom AS ref DEFAULT 'aaa';

ALTER TABLE table1 ADD refdom_column refdom;

ALTER DOMAIN refdom ADD CONSTRAINT refdom_deleted_chk
CHECK (VALUE NOT IN ('bbb'));

UPDATE table1 SET refdom_column = 'bbb';
ERROR:  value for domain refdom violates check constraint "refdom_deleted_chk"

UPDATE table1 SET refdom_column = 'ccc';
ERROR:  invalid input value for enum ref: "ccc"

Are there any caveats of this solution and may be there is a better one?

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@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

Re: [GENERAL] Trouble installing psycopg2

[David Kerr]

On Thu, Sep 26, 2013 at 02:56:14PM -0400, Augori wrote:
- Thanks, for the replies Chris and David.
- 
- Chris, I couldn't find any psycopg files under my Python installs, so I
- decided to try David's advice and yum install python-psycopg2
- It reported success, but it installed it under
- ../usr/lib64/python2.4/site-packages
- 
- I'm working with Python2.7.5, so psycopg2 won't import.   It believe that
- psycopg2 is not written for 2.4 or below.  That's why I installed 2.7.5.
- So my question now is, how can I get yum to install it in the 2.7
- site-packages?
- 
- Thanks.
- 
- PS.  About the virtualenv suggestion, I had tried using use virtualenv and
- virtualenvwrapper, following the instructions on this nice site.
- http://bda.ath.cx/blog/2009/04/08/installing-python-26-in-centos-5-or-rhel5/
- But, I couldn't get the bashrc to run without error, after adding this
- update
- 
- source /opt/bin/virtualenvwrapper_bashrc
- 
- So, I thought I'd throw caution to the wind and skip that part.
- (that's how I got to easy_intall, which at least allowed me to specify
- which python version)

Looking at the easy_install on my system it uses python26
head -1 /usr/bin/easy_install
#!/usr/bin/python2.6

so if you use easy_install you probably just installed it to the 2.6 stuff.

If you need 2.7 for your stuff then you'll definitely want to get virtualenv
working. If you can live with 2.6 then get rid of 2.7 on your machine
and work with the yum packages.

A better virtualenvwrapper setup doc is here (not from 2009)
http://virtualenvwrapper.readthedocs.org/en/latest/



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

[GENERAL] JDBC driver for Postgres 9.3

[Kohler Manuel]

Hi,
we are developing a Java based software with Postgres as a DB.
Could someone tell me if there will be a JDBC driver for 9.3 out soon or
is it safe and recommended to use the latest JDBC driver available?
Currently we are using:
PostgreSQL 9.2-1000 JDBC4 (build 1000)

Kind regards
Manuel

-- 
Manuel Kohler
Center for Information Sciences and Databases (C-ISD)
Quantitative Genomics Facility (QGF)
Department of Biosystems Science & Engineering (D-BSSE)
SIB Swiss Institute of Bioinformatics
ETH Zurich, Mattenstr. 26 (1078 1.02), CH-4058 Basel, +41 61 387 3132







-- 
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] Trouble installing psycopg2

[Adrian Klaver]

On 09/26/2013 11:56 AM, Augori wrote:

Thanks, for the replies Chris and David.

Chris, I couldn't find any psycopg files under my Python installs, so I
decided to try David's advice and yum install python-psycopg2
It reported success, but it installed it under
../usr/lib64/python2.4/site-packages


The easy_install version was not under?:

/usr/local/lib/python2.7/site-packages/


--
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

Re: [GENERAL] Trouble installing psycopg2

[Augori]

Okay, I'm sorry for the questions.  But I'm really confused about where
things are being installed.  I know that yum installed it under 2.4
site-packages because I checked for it inside there before and after I ran
the yum command.

There is an easy_install.pth file under /usr/local/lib/python2.7/site-
packages/ as well, but would that be invoked when I call easy_install when
I'm logged in as root?

As for 2.6, that would be fine, but I don't seem to have a
/user/bin/python2.6 (whereas, I do have a  /user/bin/python2.4). Maybe it's
because I'm on CentOS5, which comes with 2.4.

I tried to use the rpm --prefix flag to move the rpm, but to no avail.


On Thu, Sep 26, 2013 at 5:02 PM, Adrian Klaver wrote:

> On 09/26/2013 11:56 AM, Augori wrote:
>
>> Thanks, for the replies Chris and David.
>>
>> Chris, I couldn't find any psycopg files under my Python installs, so I
>> decided to try David's advice and yum install python-psycopg2
>> It reported success, but it installed it under
>> ../usr/lib64/python2.4/site-**packages
>>
>
> The easy_install version was not under?:
>
> /usr/local/lib/python2.7/site-**packages/
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>

[GENERAL] truncate/rotate pgbouncer log using .ini settings

[Venkat Kaushik]

I have setup pgbouncer successfully on CentOS6, PG9.2
pgbouncer.x86_64  1.5.4-1.rhel6 @pgdg92

I have a question regarding logging:
Currently I have 17MB of log and its growing.

17M -rw-rw-r--. 1 pgbouncer pgbouncer 17M Sep 26 12:17
/var/log/pgbouncer/pgbouncer.log

a) Is there a way (from pgbouncer.ini) to curtail/truncate/rotate logs?
b) If answer to (a) is NO, then I will come up with alternate solution,
but wanted to ask.

Thanks,
Venkat

Re: [GENERAL] Trouble installing psycopg2

[Adrian Klaver]

On 09/26/2013 02:14 PM, Augori wrote:

Okay, I'm sorry for the questions.  But I'm really confused about where
things are being installed.  I know that yum installed it under 2.4
site-packages because I checked for it inside there before and after I
ran the yum command.

There is an easy_install.pth file under /usr/local/lib/python2.7/site-
packages/ as well, but would that be invoked when I call easy_install
when I'm logged in as root?

As for 2.6, that would be fine, but I don't seem to have a
/user/bin/python2.6 (whereas, I do have a  /user/bin/python2.4). Maybe
it's because I'm on CentOS5, which comes with 2.4.

I tried to use the rpm --prefix flag to move the rpm, but to no avail.



Well you said you have both Python 2.4 and 2.7 installed on the machine.

Were they both installed via Centos package management?

If so Python 2.4 is probably set as the default and if you install the 
package for Python it will install for 2.4. You probably need to specify 
the python version. Not sure how Centos names its packages, but I am 
using openSUSE and the default package(where the default Python is 2.7) 
is python-psycopg2 and the Python 3 version(which I also have installed) 
is python3-psycopg2



--
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

Re: [GENERAL] Blowfish Encrypted String

[Steven Schlansker]

On Sep 26, 2013, at 10:55 AM, Craig Boyd  wrote:

> Dmitriy,
> 
> Thank you very much for the reply!
> 
> Right...got the the data type...but how do I actually insert a binary string 
> into the column?  What does the SQL look like?
> For the moment assume I have the following bit of binary: 4PO„âÔ™ä²
> 
> What does the SQL look like to insert that into the bytea column?  
> (FYI...most of my binary data would be only slightly bigger than what I just 
> pasted here.  I am not storing Word documents or images, just some encrypted 
> strings of test that are not more than 100 characters long and typically 
> about 16 to 20.
> 

http://www.postgresql.org/docs/devel/static/datatype-binary.html

Most client libraries will have functionality to do this nicely (e.g. sending a 
Java byte[] to a Postgres bytea); it sucks somewhat to do it by hand in SQL.

> Thanks,
> 
> Craig Boyd
> 
> 
> On Thu, Sep 26, 2013 at 11:46 AM, Dmitriy Igrishin  wrote:
> 
> 
> 
> 2013/9/26 Craig Boyd 
> Hello All,
> 
> I have a string in a program that I have encrypted using Blowfish and I am 
> now trying to figure out the best way to store that in PostgreSQL so that I 
> can store it and retrieve it later for decryption.  I have searched around 
> and have not found some good examples of how to go about this.
> 
> So my first questions is: Can someone point me to a tutorial or posting that 
> shows how one might do that?
> 
> Failing that: 
> What data type should I use to store this?
> I believe that you should use bytea datatype. 
> What does the SQL look like to INSERT/SELECT the field?
> Just like any other INSERT/SELECT query.
> 
> 
> -- 
> // Dmitriy.
> 
> 



-- 
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] JDBC driver for Postgres 9.3

[Steven Schlansker]

On Sep 26, 2013, at 6:35 AM, "Kohler  Manuel"  
wrote:

> Hi,
> we are developing a Java based software with Postgres as a DB.
> Could someone tell me if there will be a JDBC driver for 9.3 out soon or
> is it safe and recommended to use the latest JDBC driver available?
> Currently we are using:
> PostgreSQL 9.2-1000 JDBC4 (build 1000)
> 

I believe the 9.3 JDBC driver is coming soon, but the latest driver works just 
fine (we use it in production)



-- 
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] Trouble installing psycopg2

[Augori]

I think Python 2.4 was installed with the OS.  I installed Python 2.7 from
an .egg file that I downloaded.  I'm  a bit new to this terminology, so not
sure if that considered Centos package management.

I think you're right about Python2.4 being set as the default.  Does anyone
know how to persuade it otherwise for installation purposes?

Thanks.


On Thu, Sep 26, 2013 at 5:30 PM, Adrian Klaver wrote:

> On 09/26/2013 02:14 PM, Augori wrote:
>
>> Okay, I'm sorry for the questions.  But I'm really confused about where
>> things are being installed.  I know that yum installed it under 2.4
>> site-packages because I checked for it inside there before and after I
>> ran the yum command.
>>
>> There is an easy_install.pth file under /usr/local/lib/python2.7/site-
>> packages/ as well, but would that be invoked when I call easy_install
>> when I'm logged in as root?
>>
>> As for 2.6, that would be fine, but I don't seem to have a
>> /user/bin/python2.6 (whereas, I do have a  /user/bin/python2.4). Maybe
>> it's because I'm on CentOS5, which comes with 2.4.
>>
>> I tried to use the rpm --prefix flag to move the rpm, but to no avail.
>>
>>
> Well you said you have both Python 2.4 and 2.7 installed on the machine.
>
> Were they both installed via Centos package management?
>
> If so Python 2.4 is probably set as the default and if you install the
> package for Python it will install for 2.4. You probably need to specify
> the python version. Not sure how Centos names its packages, but I am using
> openSUSE and the default package(where the default Python is 2.7) is
> python-psycopg2 and the Python 3 version(which I also have installed) is
> python3-psycopg2
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>

Re: [GENERAL] Trouble installing psycopg2

[John R Pierce]

On 9/26/2013 7:07 PM, Augori wrote:
I think you're right about Python2.4 being set as the default.  Does 
anyone know how to persuade it otherwise for installation purposes?




path.

absolutely do NOT replace the OS distribution python unless you want to 
break lots of stuff (for instance, yum on rhel/centos is written in python)




--
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

Re: [GENERAL] Building with MinGW issue

[Muhammad Bashir Al-Noimi]

On 09/24/2013 02:57 AM, Adrian Klaver wrote:
Sort of late in the thread to ask this, but any reason you are not 
using the precompiled binaries? 
I couldn’t find Postgresql binaries built by MinGW... Do you know if 
these binaries already exist?


--
Best Regards,
Muhammad Bashir Al-Noimi



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

[GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.

[tuanhoanganh]

Hello all.

I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 bit.
But my plpython function have "ERROR: ImportError: DLL load failed: A
dynamic link library (DLL) initialization routine failed." after import
pyodbc
My Python version is ActivePython-3.2.2.3-win64-x64, pyodbc version
is pyodbc-3.0.2.win-amd64-py3.2.
On windows 7 SP1 64bit everything run fine.
Please help me. Thanks in advance.

Tuan Hoang Anh

Re: [GENERAL] Building with MinGW issue

[John R Pierce]

On 9/26/2013 7:53 PM, Muhammad Bashir Al-Noimi wrote:

On 09/24/2013 02:57 AM, Adrian Klaver wrote:
Sort of late in the thread to ask this, but any reason you are not 
using the precompiled binaries? 
I couldn’t find Postgresql binaries built by MinGW... Do you know if 
these binaries already exist? 


why does the compiler matter for a precompiled binary?



--
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

Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.

[John R Pierce]

On 9/26/2013 8:00 PM, tuanhoanganh wrote:


I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64 
bit. But my plpython function have "ERROR: ImportError: DLL load 
failed: A dynamic link library (DLL) initialization routine failed." 
after import pyodbc
My Python version is ActivePython-3.2.2.3-win64-x64, pyodbc version 
is pyodbc-3.0.2.win-amd64-py3.2.

On windows 7 SP1 64bit everything run fine.
Please help me. Thanks in advance.


I just heard from a developer friend that a recent windows update broke 
all KINDA dll-related things with Windows.   maybe thats what is 
happening here.  maybe not.   he hasn't gotten to the bottom of it yet.


--
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

Re: [GENERAL] Trouble installing psycopg2

[Adrian Klaver]

On 09/26/2013 07:07 PM, Augori wrote:

I think Python 2.4 was installed with the OS.  I installed Python 2.7
from an .egg file that I downloaded.  I'm  a bit new to this
terminology, so not sure if that considered Centos package management.


No.



I think you're right about Python2.4 being set as the default.  Does
anyone know how to persuade it otherwise for installation purposes?


Well it seems you already have psycopg2 installed for Python 2.7, it 
just cannot find it. Here is a possible solution.


Open the Python 2.7 shell

import sys

then

sys.path

This will show a list of paths that Python 2.7 knows about. Look to see 
if /usr/local/lib/python2.7/site-packages/ is listed.


If not, you can temporarily include it by doing:

sys.path.append('/usr/local/lib/python2.7/site-packages/')

and then

import psycopg2

If it is indeed installed in /usr/local/lib/python2.7/site-packages/ it 
should load.


To make the change 'permanent' create a  *.pth file in one of the 
directories shown when you first did sys.path, preferably a 
site-packages one. In the file  put 
'/usr/local/lib/python2.7/site-packages/ . Then name the file, say 
usr_local.pth. Python adds directories in *.pth files to the sys.path.



Thanks.




--
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

Re: [GENERAL] PlPython with pyodbc error DLL load failed: A dynamic link library (DLL) initialization routine failed.

[tuanhoanganh]

If i run import pyodbc from python command and run pyodbc sql command,
everything work well.
Please help me.

Tuan Hoang Anh


On Fri, Sep 27, 2013 at 10:05 AM, John R Pierce  wrote:

> On 9/26/2013 8:00 PM, tuanhoanganh wrote:
>
>>
>> I updated my laptop to windows 8.1, my PostgreSQL version is 9.2.3 64
>> bit. But my plpython function have "ERROR: ImportError: DLL load failed: A
>> dynamic link library (DLL) initialization routine failed." after import
>> pyodbc
>> My Python version is ActivePython-3.2.2.3-win64-**x64, pyodbc version is
>> pyodbc-3.0.2.win-amd64-py3.2.
>> On windows 7 SP1 64bit everything run fine.
>> Please help me. Thanks in advance.
>>
>
> I just heard from a developer friend that a recent windows update broke
> all KINDA dll-related things with Windows.   maybe thats what is happening
> here.  maybe not.   he hasn't gotten to the bottom of it yet.
>
> --
> 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] Trying to create DB / user to import some data

[mdr]

I am new to Postgres but not to DBMS.
Running on Postgres 9.1 Ubuntu 13.04.

I log in as Postgres user: psql -h localhost -U postgres
and then I create a db and user and allow grants to the user like this:

create user import_dbms_user with password 'import_dbms';
create database import_dbms_db;
grant all privileges on database import_dbms_db to import_dbms_user;

\du shows:
 import_dbms_user || {}
 postgres | Superuser, Create role, Create DB, Replication | {}

In the pg_hba.conf I set as:
# local DATABASE USER METHOD [OPTIONS]
local import_dbms_db import_dbms_user md5
and restart Postgres.

However when I try to run psql from the command line:
psql -h localhost -U import_dbms_user -WI enter password when prompted
Password for user import_dbms_user: 
psql: FATAL:  database "import_dbms_user" does not exist

But I get the error as above.

Trying to understand what I may be doing wrong.

Do I need to assign some kind of login role to import_dbms_user or such?

i assumed in the pg_hba.conf the line above is all I needed to log in
locally?
local import_dbms_db import_dbms_user md5

And instead of local I would enter IP address of machine from which I would
want to log in to the server?
192.168.1.10 import_dbms_db import_dbms_user md5

I am running on a VM and am trying to run some python scripts with psycopg2
to load some data.

So that script cannot log in either. Do I need to create any special role /
priviledge for that user to login from that script as well?

Thanks for your help!

Mono






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Trying-to-create-DB-user-to-import-some-data-tp5772568.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] Trying to create DB / user to import some data

[Steven Schlansker]

On Sep 26, 2013, at 10:28 PM, mdr  wrote:
> 
> create user import_dbms_user with password 'import_dbms';
> create database import_dbms_db;
> grant all privileg

> However when I try to run psql from the command line:
> psql -h localhost -U import_dbms_user -WI enter password when prompted
> Password for user import_dbms_user: 
> psql: FATAL:  database "import_dbms_user" does not exist
> 
> But I get the error as above.

By default, psql will try to use the username as the database name if it is not 
specified.
Since your database name is different from the user, this does not work.

You could either name both your user and db "import_dbms" and have the default 
work, or specify the database explicitly with "-d import_dbms_db"

Hope that helps.



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