[GENERAL] 3A37-A32F-9E8B : REMINDER from GLOBAL

[majordomo-owner]

__ 
Some time ago, a confirmation token was sent to you because of the
following request:

  "unregister pgsql-general@postgresql.org"

It was sent for the following reason(s):

  The default rule says that the "unregister" commandmust be confirmed by 
the person affected by the command.
  

This request has neither been accepted nor rejected.  If you want this
action to be taken, please do one of the following:

1. If you have web browsing capability, visit
   

   and follow the instructions there.

2. Reply to majord...@postgresql.org
   with one of the following two commands in the body of the message:

accept
reject

   (The number 3A37-A32F-9E8B must be in the Subject header)

3. Reply to majord...@postgresql.org
   with one of the following two commands in the body of the message:

accept 3A37-A32F-9E8B
reject 3A37-A32F-9E8B

If you do not respond within 2 days, this token will expire.

If you would like to communicate with a person, 
send mail to majordomo-ow...@postgresql.org.

-- 
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] Can anyone help setting up pgbouncer?

[Nick]

Thanks Richard, I updated my users.txt file to include quotes (it
didn't) which fixed the broken auth file error, but now im getting
this...

1518 ERROR unconfigured_file: No such file or directory

which repeats over and over again when I try

pgbouncer -v pgbouncer.ini



I updated my ini file to this:

bouncer1 = host=127.0.0.1 port=5432 dbname=bouncer1
bouncer1 = host=127.0.0.1 dbname=bouncer1
bouncer1 = host=127.0.0.1 port=5432 user=nboutelier password=password

logfile = pgbouncer.log
pidfile = pgbouncer.pid
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = trust
auth_file = users.tx
admin_users = nboutelier


On Nov 13, 1:58 am, d...@archonet.com (Richard Huxton) wrote:
> Nick wrote:
> > Im trying to set up pgbouncer. Installation seemed to go well but when
> > I try...
>
> > $ pgbouncer -d pgbouncer.ini
>
> > I get an error...
>
> > 2009-11-13 02:02:35.170 7245 ERROR broken auth file
>
> It's complaining about your auth file, rather than pgbouncer.ini
>
> > Here is my pgbouncer.ini file...
> > auth_file = users.txt
>
> 1. Perhaps have a full path to the file here.
> 2. Check permissions/ownership on users.txt
> 3. Check the format of the file - each line should be  
> with space between and double-quotes around each.
>    "myuser" "mypassword"
> 4. Run with -v rather than -d and see if you get any useful info on STDERR
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general


-- 
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] postgresql installation problems

[John DeSoi]

On Nov 13, 2009, at 8:05 PM, Reno Bladergroen wrote:

> Probably the following might have been asked before, but I've been searching 
> the web for the following problem for 2 days already:
> I need to install an end-user application which demands 2 postgresql users 
> each owning its own database.
> One user+database is used for a data management system (I call this one GUS), 
> the other for the front-end application (a data-analysis program), I call 
> that QPCR.
> So I installed postgreSQL according to the documentation. A server is running 
> on port 5432 now.
> Now the question: when i try to install the application, the installation 
> demands to set the port for each user separately. When i try to use 5432 for 
> both databases an error pops up with the notification that that port is 
> already used by QPCR and therefore can't be used by GUS. How do I set a port 
> for each user separately?? Do I need to install two clusters (and how do I do 
> that?)

The requirement that each database use a different port is rather strange. A 
single cluster (which uses one port) can have a virtually unlimited number of 
users and databases. So yes, you will have to install two clusters, or 
(depending on your platform) there is probably some easy port forwarding trick 
where you can have one cluster but two possible ports. Installing two clusters 
means running initdb for two different directories and configuring them for 
different ports.



John DeSoi, Ph.D.





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

[GENERAL] postgresql installation problems

[Reno Bladergroen]

Probably the following might have been asked before, but I've been  
searching the web for the following problem for 2 days already:
I need to install an end-user application which demands 2 postgresql  
users each owning its own database.
One user+database is used for a data management system (I call this  
one GUS), the other for the front-end application (a data-analysis  
program), I call that QPCR.
So I installed postgreSQL according to the documentation. A server is  
running on port 5432 now.
Now the question: when i try to install the application, the  
installation demands to set the port for each user separately. When i  
try to use 5432 for both databases an error pops up with the  
notification that that port is already used by QPCR and therefore  
can't be used by GUS. How do I set a port for each user separately??  
Do I need to install two clusters (and how do I do that?)


I hope someone can help me with this.

Thanks already,

Reno

--
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] Customize the install directory of the postgres DB

[Greg Smith]

Tom Lane wrote:

The real problem that I think the OP hasn't considered is whether
his "bundled" RPM package isn't going to conflict with a preinstalled
postgresql RPM.  Relocating the RPM, either dynamically as you suggest
or by just changing the install paths while building it, isn't a very
palatable solution since e.g. you really want libpq.so in /usr/lib,
psql in /usr/bin/, etc.
  
It sounds like they really do want all of those things to be installed 
in a subdirectory of their app, so I don't see a problem with them being 
there instead of the standard locations.  As long as they know how to 
run psql etc., not having them in the global PATH might be a feature 
rather than a problem.  The only detail I'm aware of they may not have 
considered is that a subdirectory install has the potential for the 
binaries to not be able to find their associated libraries, so they 
might either have to add those to the system loader configuration or set 
LD_LIBRARY_PATH before calling database binaries.  Ideally you'd find 
them via rpath or something so this isn't an issue, but it's easy to 
miss that the first time you make a change like this.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.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] Customize the install directory of the postgres DB

[Thomas Kellerer]

weixiang tam wrote on 13.11.2009 10:16:

Dear all,

I am trying to install the postgres8.2.14 via the RPM; however the 
install directory is default to the following folder


* Executables : /usr/bin
* Libraries : /usr/lib
* Documentation : /usr/share/doc/postgresql-x.y.z ,
  /usr/share/doc/postgresql-x.y.z/contrib
* Contrib : /usr/share/pgsql/contrib
* Data : /var/lib/pgsql/data 

As we are bundling the postgresql db as our product release, we would 
like to keep the Postgres executable dir, Data Dir under our product 
folder. In this case, could I know whether I can customize the install 
directory when i do the RPM installation?


Why don't you use the archive binaries from EnterpriseDB?

http://www.enterprisedb.com/products/pgbindownload.do

I use the "ZIP Version" on Windows to "package" Postgresql without problems. 
I just unzip the archive, run initdb pointing to a directory which is located next to the directory where I unpack the archive. 


I then use batch files to start and stop the PG server during startup of my 
(web) application

Regards
Thomas


--
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] Customize the install directory of the postgres DB

[Tom Lane]

Greg Smith  writes:
> weixiang tam wrote:
>> As we are bundling the postgresql db as our product release, we would 
>> like to keep the Postgres executable dir, Data Dir under our product 
>> folder. In this case, could I know whether I can customize the install 
>> directory when i do the RPM installation?

> What you'd probably like to have here is what's called a relocatable 
> RPM.  The RPMs provided for PostgreSQL are not relocatable.

Moving the data directory is not that hard, you just need to add a
config file that changes the PGDATA value seen by the init script.
The real problem that I think the OP hasn't considered is whether
his "bundled" RPM package isn't going to conflict with a preinstalled
postgresql RPM.  Relocating the RPM, either dynamically as you suggest
or by just changing the install paths while building it, isn't a very
palatable solution since e.g. you really want libpq.so in /usr/lib,
psql in /usr/bin/, etc.

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] Customize the install directory of the postgres DB

[Greg Smith]

weixiang tam wrote:
As we are bundling the postgresql db as our product release, we would 
like to keep the Postgres executable dir, Data Dir under our product 
folder. In this case, could I know whether I can customize the install 
directory when i do the RPM installation?
What you'd probably like to have here is what's called a relocatable 
RPM.  The RPMs provided for PostgreSQL are not relocatable.  There's an 
intro to how to restructure and rebuild a RPM to make it relocatable at 
http://rpm.org/max-rpm/s1-rpm-reloc-building-relocatable.html ; it will 
take several hours of work (most of which are spent waiting for 
PostgreSQL to compile while re-testing) in order to do something similar 
to the PostgreSQL RPMs.


It's painful enough you may just settle for a RPM that gets built with 
the directory of your application passed in instead, which reduces the 
trouble a bit (but not completely).


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.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] PGconn gets frozen ocassionally after select() timeout

[Vick Khera]

On Fri, Nov 13, 2009 at 10:07 AM, Marek Peca  wrote:
> Dear Vick,
>
>> I have very similar code written in Perl, and I never observe failures
>> like you see after a timeout on the select call.  This code has been
>> in production on farily busy systems for several years now.
>
> can you tell me, which libpq and server versions you are running on?
> I have constant problems with libpq 8.3.7, connecting remotely to server of
> the same version.

Right now we're running Pg 8.3.7, but we have been doing this since
8.1 days if not earlier.  Everything here is on a local LAN and runs
FreeBSD.

Based on the other messages in this thread, I'll vote one of your VPN
or NAT boxes is breaking the expectations of your network stack.

-- 
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] PGconn gets frozen ocassionally after select() timeout

[Marek Peca]

On Fri, 13 Nov 2009, Tom Lane wrote:

Well, if you *never* get a failure, then yeah I think you have a broken
TCP stack.  The default timeouts on this sort of thing are annoyingly
long, but they aren't infinite.


Yes, this is the case. Both ends are running on GNU/Linux system and I 
expect that core components are OK, however, as I said, there are some 
weird VPN relays, and maybe one of them pretends, that connection is OK, 
while it is broken for a long time.


Thank you for your answers. Now I am glas I can solve the problem and 
throw away ugly workarounds. I hope my original code with NOTIFY and 
select() is all right, then.



Best regards,
Marek

--
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] PGconn gets frozen ocassionally after select() timeout

[Tom Lane]

Marek Peca  writes:
>> In any case, that complaint should be directed to your kernel vendor
>> not us.  We do not control how long the TCP stack waits before declaring
>> the connection dead.

> Well, this is what I wanted to hear. So if I call PQstatus() or PQexec(), 
> the libpq sends some data to the TCP pipe, and you expect, that my broken 
> underlying TCP/IP subsystem pretends, that the connection is OK and the 
> data have been sent. Right?

Well, if you *never* get a failure, then yeah I think you have a broken
TCP stack.  The default timeouts on this sort of thing are annoyingly
long, but they aren't infinite.

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] PGconn gets frozen ocassionally after select() timeout

[Marek Peca]

"Several hours" might be more like it.


Better than infinity, of course.


 How long have you waited?


Two days, for example.


In any case, that complaint should be directed to your kernel vendor
not us.  We do not control how long the TCP stack waits before declaring
the connection dead.


Well, this is what I wanted to hear. So if I call PQstatus() or PQexec(), 
the libpq sends some data to the TCP pipe, and you expect, that my broken 
underlying TCP/IP subsystem pretends, that the connection is OK and the 
data have been sent. Right?



Thank you very much for your explanation.
Marek

--
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] PGconn gets frozen ocassionally after select() timeout

[Tom Lane]

Marek Peca  writes:
> However: I can not get the point, why does the PQexec() (or PQstatus() at 
> least) hang, instead of returning some error? I know, that situation with 
> broken TCP connection may involve long timeouts, but it could return at 
> least after several minutes, couldn't it?

"Several hours" might be more like it.  How long have you waited?
In any case, that complaint should be directed to your kernel vendor
not us.  We do not control how long the TCP stack waits before declaring
the connection dead.

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] PGconn gets frozen ocassionally after select() timeout

[Marek Peca]

On Fri, 13 Nov 2009, Tom Lane wrote:

What that sounds like is a network-level problem.  In particular, if
there's a NAT-capable router between your client and server machines,
it's probably dropping the connection after a certain period of
inactivity.


Yes, it probably is. The connection goes through a very fancy way, 
including several NATs and VPNs and some cruel 3rd party routers. Yes, it 
seems to me, that some ugly intervention of such a router is possible. 
(Not sure about inactivity, since the traffic is constant and with delay 
no longer than 10 minutes (in practice around 10-20 sec.) between two 
NOTIFYes.)


You may be able to fix this within Postgres by adjusting the server's 
tcp_keepalives_idle setting.  If the server is on a platform that 
doesn't support changing the keepalive interval, the only recourse is to 
fix the router.


Thank you for the suggestion of tcp_keepalives_idle, I may try it.

However: I can not get the point, why does the PQexec() (or PQstatus() at 
least) hang, instead of returning some error? I know, that situation with 
broken TCP connection may involve long timeouts, but it could return at 
least after several minutes, couldn't it?


Ordinary telnet or ssh connection will tell me, that I am writing my 
characters to TCP line, which has died some time ago.



Thank you for your answers,
Marek.

--
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] Libpq binary mode SELECT ... WHERE ID IN ($1) Question

[Andrew Chernow]

Merlin Moncure wrote:

On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez  wrote:

Hello,

Suppose I have a table:
   create table foo ( id int4, name varchar(50) );

I want to prepare and execute in binary mode:
   select name from foo where id in ($1);

Execute works when I have a single value for $1, and treat it as a
normal INT4. However, when I attempt to send multiple values for the
parameter, I get no meaningful results.

My code snippets are below.

When setting up an array of numbers to pass as a parameter, is there
something special that needs to be done? The documentation is quite
vague; I did poke around the source and found in contrib some int_array
code, but don't fully understand it.

I suspect I need to do something like ...
(a) when I prepare, do something to tell postgres that I will have an
array of values, instead of a single value, and/or
(b) perhaps encapsulate the array of integers in some manner.

If I could be pointed to an example or documentation, it would be much
appreciated.

Thanks,
Matt Sanchez

The prepare code snippet:

Oid oids[1] = { 23 };   //INT4OID

result = PQprepare( pgconn, "getname",
"select name from foo where id in ($1)"
1, oids );

The execute code snippet:

int ids[4] = { 3, 5, 6, 8 };// param values we want to setup

/* convert numbers to network byte order ... */

char *  paramvalues[1] = (char *) ids;
int paramlengths[1];
int paramformats[1] = { 1 };

paramlengths[0] = = 4 * sizeof( int );

result = PQexecPrepared( pgconn,
   "getname",  // statement name
   1,  // number of params
   paramvalues,
   paramlenths,
   paramformats,
   1 );


Here is how to do it with libpqtypes (http://libpqtypes.esilo.com/).
libpqtypes will reinvent the way you use libpq.

PGint4 i;
PGarray arr;
PGparam *param;

/* One dimesional arrays do not require setting dimesion info. For
* convience, you can zero the structure or set ndims to zero.
*/
arr.ndims = 0;

/* create the param object that will contain the elements */
arr.param = PQparamCreate(conn);

/* Use PQputf(3) to put the array elements */
for(i=0; i < 1000; i++)
PQputf(arr.param, "%int4", i);

/* The PGarray must be put into a PGparam struture.  So far, only
* the array elements have been put.  'param' can continue to be
* used to pack more parameters.  The array is now a single parameter
* within 'param'.
*/
param = PQparamCreate(conn);
PQputf(param, "%int[]", &arr);

/* no longer needed */
PQparamClear(arr.param);

/* send it up :-) */
PQparamExec(conn, param, "select unnest($1)", 1);

merlin




/* For prepared statements, use this */
PQparamExecPrepared(conn, param, "getname", 1);

/* Here is another way of doing it.  You can putf more than
 * one array element at a time.  Also, PQexecf is handy
 * but has no support for prepared statements :(
 */
PGresult *res;
PGarray arr = {0};

arr.param = param = PQparamCreate(conn);
PQputf(arr.param, "%int4 %int4 %int4 %int4", 3, 5, 6, 8);

res = PQexecf(conn,
  "select name from foo where id in (%int4[])", &arr);

PQparamClear(arr.param);

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.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] PGconn gets frozen ocassionally after select() timeout

[Tom Lane]

Marek Peca  writes:
> The problem: most of time, everything works fine, hundreds of successful 
> or even timed-out selects() get handled without any problem. But time to 
> time (eg. after several hours), the select() call returns with a timeout 
> and then, a request to the opened PQconn (simple query) gets stuck, the 
> call hangs and never returns.

What that sounds like is a network-level problem.  In particular, if
there's a NAT-capable router between your client and server machines,
it's probably dropping the connection after a certain period of
inactivity.  You may be able to fix this within Postgres by adjusting
the server's tcp_keepalives_idle setting.  If the server is on a
platform that doesn't support changing the keepalive interval, the
only recourse is to fix the router.

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] [pgeu-general] pgday.eu

[Thom Brown]

2009/11/12 Dave Page 

> On Thu, Nov 12, 2009 at 12:18 AM, Thom Brown  wrote:
>
> > I second that.  I wasn't sure quite what to expect, but it was very
> > well organised and executed.  And thanks to our French hosts whose
> > hard work really paid off too!  The talks were excellent, especially
> > Gavin M. Roy's lightning talk ;)  : http://vimeo.com/7561950
>
> Meh - that one could have gone better. Possibly if I was slightly less
> hungover for example (whose idea was it to have Cognac after beer, red
> wine and scotch anyway?)
>
> :-)
>
> Do you have any more videos?
>
> -
>
With Gavin's permission, here's some of his talk from the weekend:
http://vimeo.com/7590072

And yes, I know it's over-exposed and sometimes blurry.

Thom

[GENERAL] Cache lookup error

[Gunnar Sønsteby]

Hi,

Thank you very much for your answers.
I have backups, lot of backups, so it should be possible restore the database 
on a testmachine and start digging.

Best regards
Gunnar



-- 
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] Libpq binary mode SELECT ... WHERE ID IN ($1) Question

[Merlin Moncure]

On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez  wrote:
> Hello,
>
> Suppose I have a table:
>        create table foo ( id int4, name varchar(50) );
>
> I want to prepare and execute in binary mode:
>        select name from foo where id in ($1);
>
> Execute works when I have a single value for $1, and treat it as a
> normal INT4. However, when I attempt to send multiple values for the
> parameter, I get no meaningful results.
>
> My code snippets are below.
>
> When setting up an array of numbers to pass as a parameter, is there
> something special that needs to be done? The documentation is quite
> vague; I did poke around the source and found in contrib some int_array
> code, but don't fully understand it.
>
> I suspect I need to do something like ...
> (a) when I prepare, do something to tell postgres that I will have an
> array of values, instead of a single value, and/or
> (b) perhaps encapsulate the array of integers in some manner.
>
> If I could be pointed to an example or documentation, it would be much
> appreciated.
>
> Thanks,
> Matt Sanchez
>
> The prepare code snippet:
>
> Oid oids[1] = { 23 };   //INT4OID
>
> result = PQprepare( pgconn, "getname",
> "select name from foo where id in ($1)"
> 1, oids );
>
> The execute code snippet:
>
> int     ids[4] = { 3, 5, 6, 8 };        // param values we want to setup
>
> /* convert numbers to network byte order ... */
>
> char *  paramvalues[1] = (char *) ids;
> int     paramlengths[1];
> int     paramformats[1] = { 1 };
>
> paramlengths[0] = = 4 * sizeof( int );
>
> result = PQexecPrepared( pgconn,
>        "getname",      // statement name
>        1,              // number of params
>        paramvalues,
>        paramlenths,
>        paramformats,
>        1 );

Here is how to do it with libpqtypes (http://libpqtypes.esilo.com/).
libpqtypes will reinvent the way you use libpq.

PGint4 i;
PGarray arr;
PGparam *param;

/* One dimesional arrays do not require setting dimesion info. For
* convience, you can zero the structure or set ndims to zero.
*/
arr.ndims = 0;

/* create the param object that will contain the elements */
arr.param = PQparamCreate(conn);

/* Use PQputf(3) to put the array elements */
for(i=0; i < 1000; i++)
PQputf(arr.param, "%int4", i);

/* The PGarray must be put into a PGparam struture.  So far, only
* the array elements have been put.  'param' can continue to be
* used to pack more parameters.  The array is now a single parameter
* within 'param'.
*/
param = PQparamCreate(conn);
PQputf(param, "%int[]", &arr);

/* no longer needed */
PQparamClear(arr.param);

/* send it up :-) */
PQparamExec(conn, param, "select unnest($1)", 1);

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] Cache lookup error

[Alvaro Herrera]

Richard Huxton wrote:
> Tom Lane wrote:
> > Richard Huxton  writes:
> >> Gunnar Sønsteby wrote:
> >>> 2009-11-12 06:11:51 CET INSERTERROR:  cache lookup failed for type 19218
> > 
> >> So - something is trying to access a slony type via its old OID rather
> >> than its new one (or something like that). Not sure what this would be,
> >> since the drop-schema + cascade should have removed anything that could
> >> do this.
> > 
> >> It's probably impossible to identify precisely what it was since it's
> >> not there any more.
> > 
> > Well, there's still a reference to it somewhere in the system catalogs,
> > and locating the reference would give you a pretty good clue what it
> > was --- not to mention being a prerequisite step for cleaning up.
> > I'd try searching pg_class.reltype and pg_proc.prorettype for starters.
> > If no luck there, go through all the columns that are cited as linking
> > to pg_type.oid in
> > http://www.postgresql.org/docs/8.3/static/catalogs.html
> 
> I was thinking it was a reference inside a function or similar. I'm
> guessing "compiled" plpgsql is opaque?

That doesn't persist across a restart, though.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] PGconn gets frozen ocassionally after select() timeout

[Marek Peca]

Dear Vick,


I have very similar code written in Perl, and I never observe failures
like you see after a timeout on the select call.  This code has been
in production on farily busy systems for several years now.


can you tell me, which libpq and server versions you are running on?
I have constant problems with libpq 8.3.7, connecting remotely to server 
of the same version.


Are you connecting remotely, or through failure-safe interfaces like 
localhost? I can imagine, that this kind of error can hide if the 
connection link is good.


Maybe I should try the Perl way, if it will have the same problem in my 
configuration... oh no, is there anybody familiar with this kind of error?



Many thanks,
Marek.

--
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] Cache lookup error

[Richard Huxton]

Tom Lane wrote:
> Richard Huxton  writes:
>> Gunnar Sønsteby wrote:
>>> 2009-11-12 06:11:51 CET INSERTERROR:  cache lookup failed for type 19218
> 
>> So - something is trying to access a slony type via its old OID rather
>> than its new one (or something like that). Not sure what this would be,
>> since the drop-schema + cascade should have removed anything that could
>> do this.
> 
>> It's probably impossible to identify precisely what it was since it's
>> not there any more.
> 
> Well, there's still a reference to it somewhere in the system catalogs,
> and locating the reference would give you a pretty good clue what it
> was --- not to mention being a prerequisite step for cleaning up.
> I'd try searching pg_class.reltype and pg_proc.prorettype for starters.
> If no luck there, go through all the columns that are cited as linking
> to pg_type.oid in
> http://www.postgresql.org/docs/8.3/static/catalogs.html

I was thinking it was a reference inside a function or similar. I'm
guessing "compiled" plpgsql is opaque?

-- 
  Richard Huxton
  Archonet Ltd

-- 
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] column aliases?

[Richard Broersma]

On Fri, Nov 13, 2009 at 6:33 AM, Gauthier, Dave  wrote:

> Does PG support the notion of a colum alias?

No, the closest thing would be alias names from a VIEW.

> Is there a way to assign 2 different names for the same column to support a
> situation like above?

No.

> I was thinking that a view might do this, but is not a direct table query.
> It’s also not editable.

you can make your view editable fairly easily by implementing rules.
However, there is a limitation with rules in the case of multiple
record modifications.

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
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] Cache lookup error

[Tom Lane]

Richard Huxton  writes:
> Gunnar Sønsteby wrote:
>> 2009-11-12 06:11:51 CET INSERTERROR:  cache lookup failed for type 19218

> So - something is trying to access a slony type via its old OID rather
> than its new one (or something like that). Not sure what this would be,
> since the drop-schema + cascade should have removed anything that could
> do this.

> It's probably impossible to identify precisely what it was since it's
> not there any more.

Well, there's still a reference to it somewhere in the system catalogs,
and locating the reference would give you a pretty good clue what it
was --- not to mention being a prerequisite step for cleaning up.
I'd try searching pg_class.reltype and pg_proc.prorettype for starters.
If no luck there, go through all the columns that are cited as linking
to pg_type.oid in
http://www.postgresql.org/docs/8.3/static/catalogs.html

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] PGconn gets frozen ocassionally after select() timeout

[Vick Khera]

On Fri, Nov 13, 2009 at 9:22 AM, Marek Peca  wrote:
> Please, can you tell me, what am I doing wrong?

I have very similar code written in Perl, and I never observe failures
like you see after a timeout on the select call.  This code has been
in production on farily busy systems for several years now.

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

[GENERAL] column aliases?

[Gauthier, Dave]

Hi:

Does PG support the notion of a colum alias?  I'm not talking about "select foo 
as fii", I'm thinking more along the lines of supporting 2 different names 
for the same column.   For example, a tablle has a column named 
"social_security_number".  Queries may ask for this column as defined 
("social_security_number") but some may ask for is using "ssn".

Is there a way to assign 2 different names for the same column to support a 
situation like above?

I was thinking that a view might do this, but is not a direct table query.  
It's also not editable.

Thanks for any help !!!

Re: [GENERAL] Cache lookup error

[Richard Huxton]

Gunnar Sønsteby wrote:
> Hi,
> 
> I am new to user lists, so please don't be to hard on me if I am doing
> anything wrong.
> 
> I have been running a postgres database with slony replication for some
> years now as a backend for a apache webserver and lately some mono
> (.net) programs. The database and webserver is running on a Ubuntu LTS
> server and I compile the programs myself to be reasonable updated.
> 
> Current version is postgres 8.3.7 and Slony1-2.16

OK.

> Yesterday I got a lot of errormessages in the postgres log.
> 
> 2009-11-12 06:11:51 CET INSERTERROR:  cache lookup failed for type 19218
> 2009-11-12 06:11:51 CET INSERTCONTEXT:  SQL statement "INSERT INTO
> _slony_webtrim.sl_log_1 ...

> The error came after I dropped
> (cascade) the slony schema and reinstalled it. I were not able to dump
> the database (just hanging), but dumping table by table went ok. After
> reinstalling the database (initdb) and install slony, everything seems
> ok. I have scripts for installing slony, so there should not be any
> differences in the way it is done.

Well, the error suggests something didn't get dropped/reinstalled
cleanly. It's looking up a type definition with OID 19218 and not
finding it. Typically you would get this with a function in
pl or in a trigger, which ties in with slony again.

So - something is trying to access a slony type via its old OID rather
than its new one (or something like that). Not sure what this would be,
since the drop-schema + cascade should have removed anything that could
do this.

It's probably impossible to identify precisely what it was since it's
not there any more.

Next time, perhaps try "SLONIK DROP NODE" before manually dropping the
schema.
  http://www.slony.info/documentation/dropthings.html

-- 
  Richard Huxton
  Archonet Ltd

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

[GENERAL] PGconn gets frozen ocassionally after select() timeout

[Marek Peca]

Dear programmers,

I am using the handy LISTEN/NOTIFY mechanism provided by PostgreSQL in my 
client application, written in C with libpq. It is a simple 
single-threaded single-process client (thanks to the NOTIFIcation!), 
waiting for the notification using select() call with finite timeout.

After timeout, it performs some action, after notification something else.

The problem: most of time, everything works fine, hundreds of successful 
or even timed-out selects() get handled without any problem. But time to 
time (eg. after several hours), the select() call returns with a timeout 
and then, a request to the opened PQconn (simple query) gets stuck, the 
call hangs and never returns.


The simplified code snippet is:

s = PQsocket(pg);
for (;;) {
  FD_ZERO(&input_mask);
  FD_SET(s, &input_mask);
  rc = select(s + 1, &input_mask, NULL, NULL, select_timeout);

  if (rc == 0) {
fprintf(stderr, "info: select timeout\n");
pr = PQexec(pg, "SELECT now()");  /* <= does not return /
/* etc. */

  }
  /* etc. */
}

The client is connected to remote SQL server via TCP/IP, SSL may have been 
involved in (not sure right now). I guess that the problem could be caused 
by ocassional connection failure. However, I would expect, that PQexec() 
or other functions should return an error in such a case. I tried to test 
it using PQstatus(), but no success.


The workaround, which works somehow, is to call PQreset() *always* after 
every timed-out select(). However, I am very unhappy with such a 
"solution", because after the connection is reset, I need to LISTEN again 
and I must expect, that DB state has changed during the reset. This is 
natural in error recovery case, after a connection or other failure. BUT 
since I need to reset the connection almost always, after _any_ timed-out 
select(), it is really harmful to my application.



Please, can you tell me, what am I doing wrong?
Thank you very much.


Best regards,
Marek.

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

[GENERAL] Cache lookup error

[Gunnar Sønsteby]

Hi,

I am new to user lists, so please don't be to hard on me if I am doing
anything wrong.

I have been running a postgres database with slony replication for some
years now as a backend for a apache webserver and lately some mono
(.net) programs. The database and webserver is running on a Ubuntu LTS
server and I compile the programs myself to be reasonable updated.

Current version is postgres 8.3.7 and Slony1-2.16

Yesterday I got a lot of errormessages in the postgres log.

2009-11-12 06:11:51 CET INSERTERROR:  cache lookup failed for type 19218
2009-11-12 06:11:51 CET INSERTCONTEXT:  SQL statement "INSERT INTO
_slony_webtrim.sl_log_1 (log_origin, log_xid, log_tableid,
log_actionseq, log_cmdtype, log_cmddata) VALUES (1, $1, $2,
nextval('_slony_webtrim.sl_action_seq'), $3, $4);"
2009-11-12 06:11:51 CET INSERTSTATEMENT:  INSERT INTO reservation1
(activityid, activitydate, clientno, deptno, reservations, activityname,
room, dayofweek, starttime, maingroupname, activitystartdate,
activityenddate) SELECT activityid, '2009-11-12' AS activitydate,
clientno, deptno, 1 AS reservations, activityname, room, dayofweek,
starttime, maingroupname, activitystartdate, activityenddate FROM
activity WHERE activityid = 5374

2009-11-12 06:27:48 CET UPDATEERROR:  cache lookup failed for type 19218
2009-11-12 06:27:48 CET UPDATECONTEXT:  SQL statement "INSERT INTO
_slony_webtrim.sl_log_1 (log_origin, log_xid, log_tableid,
log_actionseq, log_cmdtype, log_cmddata) VALUES (1, $1, $2,
nextval('_slony_webtrim.sl_action_seq'), $3, $4);"
2009-11-12 06:27:48 CET UPDATESTATEMENT:  UPDATE reservation2 SET
datetimereceipt = '2009-11-12 06:27:48' WHERE clientno = 342 AND refno =
945005

The applications worked happily as if everything were ok, but the data
was never written to disk and is lost. The error came after I dropped
(cascade) the slony schema and reinstalled it. I were not able to dump
the database (just hanging), but dumping table by table went ok. After
reinstalling the database (initdb) and install slony, everything seems
ok. I have scripts for installing slony, so there should not be any
differences in the way it is done.
I have had a database error once before, also after reinstalling slony.

I would appreciate very much if someone could tell me what kind of error
this is, and maybe some hints so I might avoid it in the future.

Best regards
Gunnar


-- 
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] Customize the install directory of the postgres DB

[Albe Laurenz]

weixiang tam wrote:
> I am trying to install the postgres8.2.14 via the RPM; 
> however the install directory is default to the following folder
> 
> 
> 
> * Executables : /usr/bin 
> * Libraries : /usr/lib 
> * Documentation : /usr/share/doc/postgresql-x.y.z , 
> /usr/share/doc/postgresql-x.y.z/contrib 
> * Contrib : /usr/share/pgsql/contrib 
> * Data : /var/lib/pgsql/data 
> 
> As we are bundling the postgresql db as our product release, 
> we would like to keep the Postgres executable dir, Data Dir 
> under our product folder. In this case, could I know whether 
> I can customize the install directory when i do the RPM installation?

If you mean the RPMs PostgreSQL provides, no.

What I would recommend is to take the source RPM, change the spec
in those places where these directories are defined, and build
your own RPM.

Yours,
Laurenz Albe

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

[GENERAL] XLOG's implementation details

[Ozgun Erdogan]

Hi all,

I am new to Postgres and am interested in XLOG's implementation
details. I browsed around the source code, and saw that the particular
function that appends XLOG entries is called from multiple places
(heap, indexes, transaction subsystem, and so forth). Seeing this, I
became particularly interested in the following:

How strong is the coupling between XLOG entries and database pages?
That is, assume that I am inserting one row to a database table. Is
there anyway for me to construct the related XLOG entry before calling
the heap insert function? Or, are XLOG files conceptually binary diffs
for database pages, moving forward in time?

Another question that I had relates to XLOG files and tables. If I
have two databases and one is set up as a slave to the other, is it
possible for the slave to have fewer tables than the master? If it is
possible, is that because tables don't share pages?

Again I'm new to Postgres, and am curious about how stuff works
underneath the covers. I figured asking this list would be the
simplest way to figure things out.

Thanks,

Ozgun.

-- 
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] Can anyone help setting up pgbouncer?

[Sam Jas]

Below is sample pgbouncer.ini file.

postgres = host=127.0.0.1 port=5432 dbname=postgres
postgres = host=127.0.0.1 dbname=postgres
postgres = host=127.0.0.1 port=5432 user=sam password=jas 
client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

logfile = pgbouncer.log
pidfile = pgbouncer.pid
listen_addr = *
listen_port = 6000
unix_socket_dir = /tmp
auth_type = trust
auth_file = /usr/local/pg8.3/userlist.txt
admin_users = postgres
stats_users = stats, root
pool_mode = transaction 
server_reset_query = 
server_check_query = select 1
server_check_delay = 10
max_client_conn = 100
default_pool_size = 500
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
server_idle_timeout = 60
client_idle_timeout = 60
client_login_timeout = 60


sample of auth_file::  /usr/local/pg8.3/userlist.txt
"postgres" "postgres"


& then try with below command. 

pgbouncer -d -v pgbouncer.ini


Hope above info. may help you. 


--
Thanks 
Sam Jas



--- On Fri, 13/11/09, Nick  wrote:

From: Nick 
Subject: [GENERAL] Can anyone help setting up pgbouncer?
To: pgsql-general@postgresql.org
Date: Friday, 13 November, 2009, 7:10 AM

Im trying to set up pgbouncer. Installation seemed to go well but when
I try...

$ pgbouncer -d pgbouncer.ini

I get an error...

2009-11-13 02:02:35.170 7245 ERROR broken auth file
2009-11-13 02:02:35.170 7245 LOG File descriptor limit: 1024 (H:1024),
max_client_conn: 100, max fds possible: 110

Here is my pgbouncer.ini file...

databases]
pgbouncer1 = host=127.0.0.1 port=5432 dbname=pgbouncer1
[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = md5
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = pguser1

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



  The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. 
http://in.yahoo.com/

Re: [GENERAL] Can anyone help setting up pgbouncer?

[Richard Huxton]

Nick wrote:
> Im trying to set up pgbouncer. Installation seemed to go well but when
> I try...
> 
> $ pgbouncer -d pgbouncer.ini
> 
> I get an error...
> 
> 2009-11-13 02:02:35.170 7245 ERROR broken auth file

It's complaining about your auth file, rather than pgbouncer.ini

> Here is my pgbouncer.ini file...

> auth_file = users.txt

1. Perhaps have a full path to the file here.
2. Check permissions/ownership on users.txt
3. Check the format of the file - each line should be  
with space between and double-quotes around each.
   "myuser" "mypassword"
4. Run with -v rather than -d and see if you get any useful info on STDERR

-- 
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Customize the install directory of the postgres DB

[weixiang tam]

Dear all,

I am trying to install the postgres8.2.14 via the RPM; however the install
directory is default to the following folder


   - Executables : /usr/bin
   - Libraries : /usr/lib
   - Documentation : /usr/share/doc/postgresql-x.y.z ,
   /usr/share/doc/postgresql-x.y.z/contrib
   - Contrib : /usr/share/pgsql/contrib
   - Data : /var/lib/pgsql/data

As we are bundling the postgresql db as our product release, we would like
to keep the Postgres executable dir, Data Dir under our product folder. In
this case, could I know whether I can customize the install directory when i
do the RPM installation?

Thanks!
wei xiang

[GENERAL] Can anyone help setting up pgbouncer?

[Nick]

Im trying to set up pgbouncer. Installation seemed to go well but when
I try...

$ pgbouncer -d pgbouncer.ini

I get an error...

2009-11-13 02:02:35.170 7245 ERROR broken auth file
2009-11-13 02:02:35.170 7245 LOG File descriptor limit: 1024 (H:1024),
max_client_conn: 100, max fds possible: 110

Here is my pgbouncer.ini file...

databases]
pgbouncer1 = host=127.0.0.1 port=5432 dbname=pgbouncer1
[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = md5
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = pguser1

-- 
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] pgmemcache vs pgmemcached

[Valtonen, Hannu]

On 11/13/09 5:26 AM, Juan Backson wrote:
Hi,

I had a chat with Suzuki Hironobu (pgmemcached maintainer) earlier this 
year and we pretty much decided that pgmemcache was the way to go after 
which Suzuku wrote a large patch to make it also support libmemcached 
behaviors and earlier PostgreSQL's.


So I'd recommend just going with pgmemcache.

- Hannu

ps. As a disclaimer, I'm the current pgmemcache maintainer, I've also 
cc'd Suzuki san.



I'm the current maintainer of pgmemcache


Hi,
Have anyone tried pgmemcache and pgmemcached?
What is the difference betweent he two?  They are both non-persistent,
so I have difficult time deciding which one to use.
Any suggestion?
Thanks,
jb



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