Re: [GENERAL] Getting Out Parameter in the application using libpq

Ehsan Haq wrote: >I still don't get. How can I get the varchar OUT parameter > in the application? For Example > > CREATE OR REPLACE > Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER > IS > BEGIN >outvarchar:='This is Out String'; >RETURN 1; > END getOutVarchar; > > iri

Re: [GENERAL] Installing postgresql on Debian Lenny-->my /etc/apt/sources.list.

Le vendredi 11 septembre 2009, Ricky Tompu Breaky a écrit : > Dear Peter Eisentraut. > > My aptitude still can not find it. you didn't search with aptitude but tried to install. My guess is that it is already installed. try a 'dpkg -l postgresql-8.3' it will output something like : [...] ii p

Re: [GENERAL] Regarding initdb & pg_ctl

On Sat, Sep 12, 2009 at 05:23, Vikram Patil wrote: > Hello Folks, > > > >    So I have following problem as I logged in Windows 2003 with > domain\user. I am basically trying to create installer for postgres. So I > need to keep in mind that  person who is going to install can be the one who >

[GENERAL] Bulk read

In the context of writing to and reading from a postgres DB from a client application, I've spent quite a while looking through the postgres manual, these mailing lists and the internet, and there is quite a bit of information available about bulk-loading. The favoured strategy seems to be to:

[GENERAL] Cartesian product not correct

Hi Given the sql below shouldn't I get the 3 records back? create table t1 (attribute text); insert into t1 values ('cars'); insert into t1 values ('trucks'); insert into t1 values ('bikes'); create table t2 (id serial, category text, attribute text, val integer); insert into t2(category, attri

Re: [GENERAL] Postgresql Hardware

Hi, Thanks for your help. I will take some measurements and analyze this graphs. Does anyone know where i can get a book about Postgresql Tuning and hardware impact on performance? Thanks Best regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] postgresql.key secure storage

>a separate application server Well this can be a solution in a trustworthy and friendly environment, on which I can't count. I would have been more at ease if libpq could manage a PKCS12 cert. or some secure wallet/keystore that contains both the public and private keys for SSL traffic. Neith

[GENERAL] Checkpoint request failed, permission denied

When I try and manually perform a checkpoint with version 8.3 on CentOS 5 I get this error: ERROR: could not link file "pg_xlog/0001" to "pg_xlog/00010002" (initialization of log file 0, segment 2): Permission denied ERROR: could not link file "pg_xlog/000

Re: [GENERAL] postgresql.key secure storage

>a separate application server Well this can be a solution in a trustworthy and friendly environment, on which I can't count. I would have been more at ease if libpq could manage a PKCS12 cert. or some secure wallet/keystore that contains both the public and private keys for SSL traffic. Neith

Re: [GENERAL] Cartesian product not correct

Angus Miller writes: > Given the sql below shouldn't I get the 3 records back? Try 8.4.1 --- I think you got bit by the semijoin ordering bugs in 8.4.0. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subsc

Re: [GENERAL] Cartesian product not correct

In response to Angus Miller : > Hi > > Given the sql below shouldn't I get the 3 records back? No, why? > > create table t1 (attribute text); > insert into t1 values ('cars'); > insert into t1 values ('trucks'); > insert into t1 values ('bikes'); > > create table t2 (id serial, category text,

Re: [GENERAL] postgresql.key secure storage

"Saleem Edah-Tally" writes: > I would have been more at ease if libpq could manage a PKCS12 cert. or some > secure wallet/keystore that contains both the public and private keys for SSL > traffic. Neither the end user nor any admin would have to provide the > password > to access the keys insi

Re: [GENERAL] Postgresql Hardware

Thanks,Greg! I will read your articles. This is a great help to understand what HW and how to tune my postgresql. Best regards. On 14 Set, 16:46, scott.marl...@gmail.com (Scott Marlowe) wrote: > On Mon, Sep 14, 2009 at 9:30 AM, Greg Smith wrote: > > On Mon, 14 Sep 2009, Psicopunk wrote: > >

[GENERAL] Locks in postgres causing system load and crash.

We have a situation where the database locks escalate and load causes problems or the system crashes in some circumstances. We have munin installed and notice that the locks (access share locks) climbed to 2.7k. I'm wondering what or how I can get a snapshot of the table(s) and perhaps t

[GENERAL] Reverse-engineering table creation statements

Is there a simple way of generating a creation statement for a table without using psql or pgAdmin. Basically I'd like to create what pgAdmin III shows in the SQL pane when you click on a table. MySQL appears to have an equivalent which is SHOW CREATE table [tablename]. Thanks Thom

Re: [GENERAL] Reverse-engineering table creation statements

pg_dump --schema-only --schema=SCHEMA --table=TABLE produces creation script. Chris http://www.postgresql.org/docs/8.0/interactive/backup.html From: thombr...@gmail.com Date: Mon, 14 Sep 2009 16:15:23 +0100 Subject: [GENERAL] Reverse-engineering table creation statements To: pgsql-

Re: [GENERAL] Postgresql Hardware

On Mon, Sep 14, 2009 at 9:30 AM, Greg Smith wrote: > On Mon, 14 Sep 2009, Psicopunk wrote: > >> I will take some measurements and analyze this graphs. > > A snapshot of "vmstat 1" data from when something like your real app is > running is far more useful at figuring out where you should allocate

Re: [GENERAL] Reverse-engineering table creation statements

On Sep 14, 2009, at 10:27 AM, Thom Brown wrote: Erk... I forgot to mention I don't wish to use command-line tools either. I would like to submit something as a query in PHP and get back a result with the creation script in. This is to modify some existing code which is currently only sup

Re: [GENERAL] Checkpoint request failed, permission denied

On Mon, Sep 14, 2009 at 8:52 AM, Cory Isaacson wrote: > [r...@ittdev1 data]# ls -l pg_xlog > total 16416 > -rw--- 1 postgres postgres 16777216 Sep 13 23:16 > 0001 > drwx-- 2 postgres postgres     4096 Sep 13 22:19 archive_status What odes ls -ld pg_xlog say? -- Sent

Re: [GENERAL] Checkpoint request failed, permission denied

Here are the permissions on pg_xlog: drwx-- 3 postgres postgres 4096 Sep 13 22:19 pg_xlog And here is ls -ld: [r...@ittdev1 data]# ls -ld pg_xlog drwx-- 3 postgres postgres 4096 Sep 13 22:19 pg_xlog Thanks, Cory > From: Tom Lane > Date: Mon, 14 Sep 2009 11:59:21 -0400 > To: Cory Is

Re: [GENERAL] Reverse-engineering table creation statements

Erk... I forgot to mention I don't wish to use command-line tools either. I would like to submit something as a query in PHP and get back a result with the creation script in. This is to modify some existing code which is currently only supporting MySQL. Thom 2009/9/14 Chris Barnes > pg_dump

Re: [GENERAL] Cartesian product not correct

Thanks Tom I can confirm this passed in 8.4.1 Tom Lane wrote: Angus Miller writes: Given the sql below shouldn't I get the 3 records back? Try 8.4.1 --- I think you got bit by the semijoin ordering bugs in 8.4.0. regards, tom lane -- Sent via pgsql-gene

Re: [GENERAL] postgresql.key secure storage

On Mon, Sep 14, 2009 at 09:40:47AM +0200, Saleem Edah-Tally wrote: > >a separate application server > > Well this can be a solution in a trustworthy and friendly environment, on > which I can't count. There must be some mis-communication going on; the above is how things tend to be done on the I

Re: [GENERAL] postgresql.key secure storage

On Mon, Sep 14, 2009 at 12:17:55PM -0400, Tom Lane wrote: > Sam Mason writes: > > On Mon, Sep 14, 2009 at 05:45:14PM +0200, Saleem EDAH-TALLY wrote: > >> How can a user extract data from a container, by whatever > >> name we call it, if he does not have the key to open it ? > > > Exactly the same

Re: [GENERAL] Checkpoint request failed, permission denied

Cory Isaacson writes: > I think you may be right. There were some audit access denied messages. I > had SELinux in permissive mode, but its tricky to work with. > I generated a new SELinux rule using audit2allow, here is what it looks like > now. Do you think this is adequate? If you're keeping

Re: [GENERAL] Checkpoint request failed, permission denied

Cory Isaacson writes: > Here are the permissions on pg_xlog: > drwx-- 3 postgres postgres 4096 Sep 13 22:19 pg_xlog Well, that certainly looks right. I'm back to suspecting selinux ... have you tried "ls -Z"? I'm not totally sure about RHEL5, but in recent Fedora it should look like drwx-

Re: [GENERAL] Checkpoint request failed, permission denied

Cory Isaacson writes: > They look right to me. Below are the permissions and process list. I ended > up rebuilding the data directory since it was just a test database, so far > so good. The permissions and setup were exactly the same before I did this. > [r...@ittdev1 data]# ls -l pg_xlog This

Re: [GENERAL] Locks in postgres causing system load and crash.

On Mon, Sep 14, 2009 at 8:58 AM, Chris Barnes wrote: > We have a situation where the database locks escalate and load causes > problems or the system crashes in some circumstances. > > We have munin installed and notice that the locks (access share locks) > climbed to 2.7k. > > I'm wondering what

Re: [GENERAL] postgresql.key secure storage

On Mon, Sep 14, 2009 at 05:45:14PM +0200, Saleem EDAH-TALLY wrote: > Le Monday 14 September 2009 16:13:45, vous avez écrit : > > "Secure > > wallet" is an exercise in self-delusion. > > Not really. How can a user extract data from a container, by whatever > name we call it, if he does not have the

Re: [GENERAL] postgresql.key secure storage

Le Monday 14 September 2009 16:13:45, vous avez écrit : > "Secure > wallet" is an exercise in self-delusion. Not really. How can a user extract data from a container, by whatever name we call it, if he does not have the key to open it ? Could you please instruct how to achieve this ? -- Sent

Re: [GENERAL] Postgresql Hardware

On Mon, 14 Sep 2009, Psicopunk wrote: I will take some measurements and analyze this graphs. A snapshot of "vmstat 1" data from when something like your real app is running is far more useful at figuring out where you should allocate your hardware resources for than any theoretical planning

Re: [GENERAL] Checkpoint request failed, permission denied

Cory Isaacson writes: > When I try and manually perform a checkpoint with version 8.3 on CentOS 5 I > get this error: > ERROR: could not link file "pg_xlog/0001" to > "pg_xlog/00010002" (initialization of log file 0, segment > 2): Permission denied > Any idea

Re: [GENERAL] Checkpoint request failed, permission denied

I should note that this came up when I tried to drop a database. It was not allowed with the checkpoint failed message. Cory From: Cory Isaacson Date: Sun, 13 Sep 2009 21:57:50 -0600 To: Subject: [GENERAL] Checkpoint request failed, permission denied When I try and manually perform a checkpo

Re: [GENERAL] Locks in postgres causing system load and crash.

Thanks Scott, How were you able to determine the resource that was causing it. There must be a way of comparing the information to a table? Chris > Date: Mon, 14 Sep 2009 09:53:08 -0600 > Subject: Re: [GENERAL] Locks in postgres causing system load and crash. > From: scott.marl...

[GENERAL] unable to (re-)install

s and protection ID"). Any idea of what I'm doing wrong? Luca. -- Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it: http://www.email.it/f Sponsor: Anteprima nuova Volkswagen Polo, scoprila adesso Clicca qui: http:/

Re: [GENERAL] Cartesian product not correct

A. Kretschmer wrote: > In response to Angus Miller : > > Hi > > > > Given the sql below shouldn't I get the 3 records back? > > No, why? Oh,... sorry, right. 8.1 returns 3 rows, see Tom's answer. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintention

Re: [GENERAL] postgresql.key secure storage

OK guys, I would never have thought about modifying libpq to steal confidential data, and I have never used debuggers in this respect at all. So super gurus can yet do the bad thing. Nevertheless 99% of users are not super gurus who could do such nasty things but a few of them could use an une

Re: [GENERAL] Locks in postgres causing system load and crash.

Yes, you can join pg_locks to pg_stat_activity and look for waiting queries, and what they're waiting on in locks. On Mon, Sep 14, 2009 at 11:20 AM, Chris Barnes wrote: > > > Thanks Scott, > > How were you able to determine the resource that was causing it. There must > be a way of comparing the

Re: [GENERAL] Checkpoint request failed, permission denied

They look right to me. Below are the permissions and process list. I ended up rebuilding the data directory since it was just a test database, so far so good. The permissions and setup were exactly the same before I did this. I was repeatedly dropping and rebuilding this database, could that have

[GENERAL] SAS70 audit + postgres

anyone pass a SAS70 audit with postgres? Our security expert has a lot of concerns due to the lack of user audit logging that's provided. especally for logging superuser / DBA actions. Of course, my stance is that you need to trust your DBAs, but I don't know if SAS70 shares my belief. Thanks

Re: [GENERAL] postgresql.key secure storage

Sam Mason writes: > On Mon, Sep 14, 2009 at 05:45:14PM +0200, Saleem EDAH-TALLY wrote: >> Le Monday 14 September 2009 16:13:45, vous avez écrit : >>> "Secure wallet" is an exercise in self-delusion. >> >> Not really. How can a user extract data from a container, by whatever >> name we call it, if

Re: [GENERAL] SAS70 audit + postgres

Yeah, I question the intelligence of your security expert in this situation. As the superuser, I can do nearly anything I please, it's kind of the point. Now, if he wants you to setup non-superuser roles to do other stuff, I can understand, but there are some things only the superuser can do, and

Re: [GENERAL] SAS70 audit + postgres

Right, I agree there are things I can do to minimize impact, but If SAS70 or similar comes in and says w/o superuser auditing we're not giving you the certification, then that still causes us a problem. I don't think it does though, I've gone through SOX and all they require is "controlled" super

Re: [GENERAL] SAS70 audit + postgres

Had a similar thing when I was in Chicago about Oracle. Whie oracle has some form of auditing, the fact is that any resourceful DBA with root access can cover their tracks if they want. Best of luck. On Mon, Sep 14, 2009 at 1:45 PM, David Kerr wrote: > Right, I agree there are things I can do t

[GENERAL] PG 8.3 - Recovering Data Directory

Hello everyone, I am trying to recover an entire database cluster (/data/ directory) by pasting the folder into a new installation (same major version) of Postgres. However whenever I try to start the postmaster service after replacing the directory, it hangs. I have checked folder permissions

Re: [GENERAL] Checkpoint request failed, permission denied

Tom, I think you may be right. There were some audit access denied messages. I had SELinux in permissive mode, but its tricky to work with. I generated a new SELinux rule using audit2allow, here is what it looks like now. Do you think this is adequate? Thanks, Cory [r...@ittdev1 data]# ls -Z p

Re: [GENERAL] SAS70 audit + postgres

=) yeah, same. Thanks Dave On Mon, Sep 14, 2009 at 01:54:25PM -0600, Scott Marlowe wrote: - Had a similar thing when I was in Chicago about Oracle. Whie oracle - has some form of auditing, the fact is that any resourceful DBA with - root access can cover their tracks if they want. Best of luck.

Re: [GENERAL] PG 8.3 - Recovering Data Directory

Lawson Bradley writes: > I am trying to recover an entire database cluster (/data/ directory) > by pasting the folder into a new installation (same major version) of > Postgres. > However whenever I try to start the postmaster service after > replacing the directory, it hangs. I have checked fo

[GENERAL] syntax troubles using "deferrable initially deferred"

create table templates ( template text, constraint template_not_null_check (template is nit null) deferrable initially deferred); ERROR syntax error at or near "DEFERRABLE" LINE 4: ...nt template_not_null_check (template is not null) DEFERRABLE... Without the "deferrable initially deferred"

[GENERAL] PostgreSQL + 64 bit + performance

Are there any links to benchamrks between 32 and 64 bit postgres? My oracle experience tells me that I want to go with 64 bit postgres so that i can have faster disk and memory access. Has anyone run the numbers? Thanks Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] syntax troubles using "deferrable initially deferred"

"Gauthier, Dave" writes: > create table templates > ( > template text, > constraint template_not_null_check (template is nit null) deferrable > initially deferred); > ERROR syntax error at or near "DEFERRABLE" > LINE 4: ...nt template_not_null_check (template is not null) DEFERRABLE... Sor

[GENERAL] PostgreSQL on Windows Server 2003 for Moodle

Hi I'm new to PostgreSQL and I'm looking for management guides tutorials etc. for running PostgreSQL on Windows Server 2003. We will be using PostgreSQL is to run Moodle, so if anyone can point me in the right direction for setup or recommended configuration of PostgreSQL for Moodle that

Re: [GENERAL] Regarding initdb & pg_ctl

Thanks Magnus for help. Is there any way to start pg_ctl without creating postgres user? I am trying to use same logged in user to run initdb and start service. As per documentation it seems to be allowed after 8.3. We tried to use it the way I describe on Windows XP and it worked without any p

Re: [GENERAL] syntax troubles using "deferrable initially deferred"

I believe I can "set constraints" to achieve the same thing? I just don't want the check to happen until commit time. The users may make many row changes, the later ones "fixing" what the earlier ones would otherwise flag as problematic with the check. -Original Message- From: Tom La

Re: [GENERAL] syntax troubles using "deferrable initially deferred"

"Gauthier, Dave" writes: > I believe I can "set constraints" to achieve the same thing? No, you can't. There are no deferrable check constraints in Postgres. > I just don't want the check to happen until commit time. The users may make > many row changes, the later ones "fixing" what the earl