[GENERAL] Dynamic SQL with pgsql, how to?

[Andre Lopes]

Hi,

I need to write some dynamic SQL in pgsql.

I have to do something like this:

[code=SQL Server]
SET @STRINGN = @STRINGN + ' AND A.' + @CAMPOFECINI + ' IN (SELECT
MAX(B.' + @CAMPOFECINI + ')
FROM ' + @TABLA + ' B
WHERE B.ID_SOCIEDAD = A.ID_SOCIEDAD
AND B.ID_EMPREGAD = A.ID_EMPREGAD'
IF @F_ALTA IS NOT NULL
SET @STRINGN = @STRINGN + ' AND B.DAT_INI_ACT_EMP =
A.DAT_INI_ACT_EMP'
SET @STRINGN = @STRINGN + ')'
   EXEC sp_executesql @STRINGN,
N'@FINI2 datetime out, @FFIN2 datetime out, @CAMP2
varchar(50) out',
@FINI out, @FFIN out , @CAMP out
[/code]

There is documentation on how can I do this in pgsql?

Best Regards,

Re: [GENERAL] Dynamic SQL with pgsql, how to?

[Jorge Arevalo]

On Mon, May 3, 2010 at 10:44 AM, Andre Lopes  wrote:
> Hi,
>
> I need to write some dynamic SQL in pgsql.
>
> I have to do something like this:
>
> [code=SQL Server]
>     SET @STRINGN = @STRINGN + ' AND A.' + @CAMPOFECINI + ' IN (SELECT
> MAX(B.' + @CAMPOFECINI + ')
>             FROM ' + @TABLA + ' B
>             WHERE B.ID_SOCIEDAD = A.ID_SOCIEDAD
>             AND B.ID_EMPREGAD = A.ID_EMPREGAD'
>     IF @F_ALTA IS NOT NULL
>         SET @STRINGN = @STRINGN + ' AND B.DAT_INI_ACT_EMP =
> A.DAT_INI_ACT_EMP'
>     SET @STRINGN = @STRINGN + ')'
>        EXEC sp_executesql @STRINGN,
>                 N'@FINI2 datetime out, @FFIN2 datetime out, @CAMP2
> varchar(50) out',
>                 @FINI out, @FFIN out , @CAMP out
> [/code]
>
> There is documentation on how can I do this in pgsql?
>
> Best Regards,
>

Hi Andre,

This may helps

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best regards,
Jorge

-- 
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] Dynamic SQL with pgsql, how to?

[A. Kretschmer]

In response to Andre Lopes :
> Hi,
> 
> I need to write some dynamic SQL in pgsql.
> There is documentation on how can I do this in pgsql?

Sure,
http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

[GENERAL] Opened ports vs. Packages...

[Durumdara]

Hi!

I want to ask about PGSQL-crack/hack-web-database.

So:

We have a web site with python.  The DB engine is PGSQL.

The first theory was that PGSQL is locally used, and each of other
operations are realized with encoded XML packages.

These operations are: fill up partner data, fill up offer data.

The client application is Delphi based. That was a little hard to convert
local data (DataSets, etc) to XML, and recode in the server side (in
python).

The new theory is need more flexibility: we can fillup more data and read
some (long) query result.

The main problem that I need to open the PGSQL's port to the net with login
possibility.

1.) Auth. - password trying.
The clients are access PGSQL by Zeos, or by PGDAC. I don't know what auth.
methods they are support, but I think that md5 and plain text is not enough
here...

2.) Opened port -  PGSQL hack/crack possibilty.
I don't know about PGSQL hack/crack on ports, but everything is possible. If
they are hack the PGDB without knowing password (with some special code
injection), we are in problem...

3.) Server overloading with DOS. (Example: many-many requests to login)

4.) Lost connections? How to handle when connection lost on wrong web, or
temp. down?

If we don't want to open the port, we need to make a complete interface that
know same thing as Zeos.

Zeos (as I know) supports data packages - the data loaded in packages, and
if you scroll to the end of the table, you can get the next package.

We need to develop same thing to load all info we want.
Need to make an update mechanism to reflect changes, etc.

We need to create a mechanism that read Queries, process them, and rewrite
the result packages...

And these infos are changing - because if I open a query with web server at
X, and got a new request at Y to make next package, may the query data
changes...

For example:
select * from data
1.) AD (select * from partner top 0 range 1000)
2.) C...F  (select * from partner top 1001 range 2000)

because between these requests we have a big modification... (in the
webserver all request a new cursor).

This is hard work.

So... what is the magic we can use? And what you heard about opened
ports/hack/connections?

Thanks:
   dd

Fwd: [GENERAL] Tablespace Problem

[Kalai R]

Hi,


Sir,

I have been checked pg_tblspc/86014 directory have no permission to create
new folder.
I am using Windows XP Os and VB.Net frontend. I created the tablespace
folder by programaticaly using vb.net.

I tried to chang the  user permission for that folder by programatically
and manualy using properties of the folder in windows explorer but folder
permission not changed.
Is there any option to solve this problem via postgersql?

If not , Should I change the folder permission for which user? current user
or service account for postgresql.

Please help to solve my problem.

Thank You.

Regards
Gloier

On Wed, Apr 21, 2010 at 8:37 PM, Tom Lane  wrote:

> Kalai R  writes:
> > I have found a problem in my database. The following error occurs
> sometime
> > not any specific database operation.
>
> > ERROR:XX000:count not create temporary file
> > "pg_tblspc/86014/pgsql_tmp/pgsql_tmp3568.0":No
> >  such file or directory.
>
> Does that pgsql_tmp directory exist?  If not, does its parent directory
> have the right ownership and permissions to let the server create it?
>
> Note that pg_tblspc/86014 is presumably a symlink --- you need to look
> at the directory the symlink points to.
>
>regards, tom lane
>

Re: [GENERAL] Opened ports vs. Packages...

[Craig Ringer]

On 3/05/2010 7:58 PM, Durumdara wrote:


1.) Auth. - password trying.
The clients are access PGSQL by Zeos, or by PGDAC. I don't know what
auth. methods they are support, but I think that md5 and plain text is
not enough here...


Just make sure you have an ssl certificate for your server, and require 
clients to validate it. Otherwise anyone malicious on the network path 
(or able to steal packets - think wifi or arp spoofing) can steal the 
password and/or your data.


You should configure the client to verify the server against a CA cert 
or a pre-installed copy of the server cert. Unfortunately, by default 
psql seems to accept self-signed certificates as trustworthy, but that's 
really not a good idea.



2.) Opened port -  PGSQL hack/crack possibilty.
I don't know about PGSQL hack/crack on ports, but everything is
possible. If they are hack the PGDB without knowing password (with some
special code injection), we are in problem...


Remote security holes are rarely reported for PostgreSQL. That doesn't 
mean they aren't there.



3.) Server overloading with DOS. (Example: many-many requests to login)


Unless I'm badly mistaken PostgreSQL starts a backend to do auth, which 
is an expensive process. A continuous load-based DoS would therefore 
probably be very easy to do with relatively little bandwidth.


It's fairly easy with linux iptables and the like to make the database 
port inaccessible to a particular IP until the application successfully 
accesses some other more lightweight service. That might help if you're 
really worried.


Personally I'm not bothering in my deployment. I run Pg on a 
non-standard port and require ssl connections with an authenticated 
*client* certificate, but don't worry too much about DoS. I don't have 
the capacity to resist an even vaguely determined DoS anyway, and Pg 
won't make that any worse.



4.) Lost connections? How to handle when connection lost on wrong web,
or temp. down?


Use tcp keepalives to work around braindead nat routers. Set short 
timeouts (see postgresql.conf tcp_ settings) so the server notices dead 
connections quickly. Be robust in the face of dangling dead connections.


--
Craig Ringer

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

Re: Fwd: [GENERAL] Tablespace Problem

[jus...@magwerks.com]

 Message from mailto:softlinne...@gmail.com Kalai R  softlinne...@gmail.com 
at 05-03-2010 05:41:24 PM --



Hi,
Sir,
I have been checked pg_tblspc/86014 directory have no permission to  create new 
folder.
I am using Windows XP Os and VB.Net frontend. I created the  tablespace folder 
by programaticaly using http://vb.net/ vb.net.


Itried to chang the user permission for that folder by  programatically 
andmanualy using properties of the folder in windows  explorer butfolder 
permission not changed.

Is there any option to solve this problem via postgersql?

If not , Should I change the folder permissionfor which user?  current user or 
service account for postgresql.

***snip***
the Postgresql account needs to have full control over all its directories

No other account needs access to postgresql directories.

If security does not read as postgres account having full control and you can't 
change security, You need to take ownership of the directories first then you 
can change security.

To take ownership you have to be admin on that computer. Then put postgres 
account as full control.





All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Re: [GENERAL] Native DB replication for PG

[Gauthier, Dave]

So, back to the base note, as far as native replication support, I should wait 
for v9 whic'll probably be out by the end of the summer?

-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com] 
Sent: Saturday, May 01, 2010 12:16 PM
To: Tom Lane
Cc: Greg Smith; j...@commandprompt.com; Gauthier, Dave; r...@iol.ie; 
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Native DB replication for PG

On Sat, May 1, 2010 at 9:27 AM, Tom Lane  wrote:
>
> I'm curious to get to the bottom of Scott's report.  It's possible that
> he hit one of the two or three 8.4-only crashes we fixed since 8.4.1;
> or the bug may still be lurking.

I'll definitely be testing it this summer to see if it triggers a
crash on the latest 8.4 and possibly 9.0 as well.  The stats db is the
ne place I can run bleeding edge releases since the data there isn't
really critical for daily operations, especially the data created
during the summer lull.

-- 
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] User defined type - analyze problem

[Tom Lane]

Carsten Kropf  writes:
> I read somewhere, that this can be solved by preparing a operator class for 
> btree and passing a comparison function for equality there. I did this with 
> the following lines:
> CREATE OPERATOR CLASS pointnd_btree_class
>   DEFAULT FOR TYPE pointnd USING btree AS
>   OPERATOR3   =(pointnd, pointnd)
> ;

You need an actual, usable btree operator class, not one-sixth of one ...
this is lacking the other four operators and the support function.

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] Native DB replication for PG

[Raymond O'Donnell]

On 30/04/2010 21:30, Scott Marlowe wrote:
> On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
>  wrote:
>> I believe v9 will have native DB master/slave DB replication (correct if
>> wrong).  If so, what’s the best guess on when will v9 be released?
> 
> If I had to plan server deployments for the next year (and I do) I'd
> be sticking with pg 8.3 and a proven replication engine.  Next summer

Surely you mean 8.4? :-)

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

[GENERAL] Latest source RPMs for 8.1.20

[Justin Pasher]

I'm looking for the latest source RPMs for Postgres 8.1.20 on RHEL. I 
can see 8.1.19 here:


http://yum.pgsqlrpms.org/srpms/8.1/redhat/rhel-4-i386/repoview/postgresql.html

Would it be safe to say that I can download the 8.1.19 source RPM, 
replace the postgresql-8.1.19.tar.bz2 tarball with the 
postgresql-8.1.20.tar.bz2 tarball, update the versions in the spec file, 
then build the RPM? I noticed there are other patch files installed by 
the source RPM, so I didn't know if I would be missing any other 
potential patch files.


Thanks.

--
Justin Pasher

--
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] Latest source RPMs for 8.1.20

[Vincenzo Romano]

2010/5/3 Justin Pasher :
> I'm looking for the latest source RPMs for Postgres 8.1.20 on RHEL. I can
> see 8.1.19 here:
>
> http://yum.pgsqlrpms.org/srpms/8.1/redhat/rhel-4-i386/repoview/postgresql.html
>
> Would it be safe to say that I can download the 8.1.19 source RPM, replace
> the postgresql-8.1.19.tar.bz2 tarball with the postgresql-8.1.20.tar.bz2
> tarball, update the versions in the spec file, then build the RPM? I noticed
> there are other patch files installed by the source RPM, so I didn't know if
> I would be missing any other potential patch files.
>
> Thanks.

Just curiosity: why are you using  8.1?

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] Latest source RPMs for 8.1.20

[Justin Pasher]

- Original Message -

From: Vincenzo Romano 
Date: Mon, 3 May 2010 17:59:10 +0200
Subject: Re: Latest source RPMs for 8.1.20
To: Justin Pasher 
CC: pgsql-general@postgresql.org

2010/5/3 Justin Pasher :
  

I'm looking for the latest source RPMs for Postgres 8.1.20 on RHEL. I can
see 8.1.19 here:


Just curiosity: why are you using  8.1?
  


Older app that has not been tested for 8.3+ compatibility (e.g. stricter 
type casting). It's not a super high priority for the developers. We 
even have some legacy sites running 7.4. I'd love to get everything 
moved to a more recent version...


--
Justin Pasher

--
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] Latest source RPMs for 8.1.20

[Vincenzo Romano]

2010/5/3 Vincenzo Romano :
> 2010/5/3 Justin Pasher :
>> - Original Message -
>>>
>>> From: Vincenzo Romano 
>>> Date: Mon, 3 May 2010 17:59:10 +0200
>>> Subject: Re: Latest source RPMs for 8.1.20
>>> To: Justin Pasher 
>>> CC: pgsql-general@postgresql.org
>>>
>>> 2010/5/3 Justin Pasher :
>>>

 I'm looking for the latest source RPMs for Postgres 8.1.20 on RHEL. I can
 see 8.1.19 here:

>>>
>>> Just curiosity: why are you using  8.1?
>>>
>>
>> Older app that has not been tested for 8.3+ compatibility (e.g. stricter
>> type casting). It's not a super high priority for the developers. We even
>> have some legacy sites running 7.4. I'd love to get everything moved to a
>> more recent version...
>
>
> Try here:
> http://rpm.pbone.net/index.php3/stat/4/idpl/13560313/dir/redhat_el_4/com/postgresql-libs-8.1.20-1PGDG.rhel4.i386.rpm.html

Nope. Binaries only.
I think the evolution of the species made that release starve and disappear. :-(

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] Latest source RPMs for 8.1.20

[Vincenzo Romano]

2010/5/3 Justin Pasher :
> - Original Message -
>>
>> From: Vincenzo Romano 
>> Date: Mon, 3 May 2010 17:59:10 +0200
>> Subject: Re: Latest source RPMs for 8.1.20
>> To: Justin Pasher 
>> CC: pgsql-general@postgresql.org
>>
>> 2010/5/3 Justin Pasher :
>>
>>>
>>> I'm looking for the latest source RPMs for Postgres 8.1.20 on RHEL. I can
>>> see 8.1.19 here:
>>>
>>
>> Just curiosity: why are you using  8.1?
>>
>
> Older app that has not been tested for 8.3+ compatibility (e.g. stricter
> type casting). It's not a super high priority for the developers. We even
> have some legacy sites running 7.4. I'd love to get everything moved to a
> more recent version...


Try here:
http://rpm.pbone.net/index.php3/stat/4/idpl/13560313/dir/redhat_el_4/com/postgresql-libs-8.1.20-1PGDG.rhel4.i386.rpm.html

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] information_schema.parameters

[Peter Eisentraut]

On fre, 2010-04-30 at 17:36 +0100, Grzegorz Jaśkiewicz wrote:
> why specific_name column on that view contains also OID ?
> This makes two databases that are identical, have different values
> there. Is there any specific reason for that ?

It was a convenient choice.  You could propose a different method for
generating the specific routine name, but given that it has to fit into
an identifier and has to allow for function overloading, some kind of
number makes the most sense, in absence of any other requirements.


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

[GENERAL] pg9 beta1, make check fails

[Andy Colson]

$make check
... snip lots of output...
./pg_regress --inputdir=. --dlpath=. --multibyte=SQL_ASCII 
--temp-install=./tmp_check --top-builddir=../../.. 
--schedule=./parallel_schedule

== removing existing temp installation==
== creating temporary installation==
== initializing database system   ==
== starting postmaster==

pg_regress: postmaster did not respond within 60 seconds
Examine 
/home/andy/gis/postgresql-9.0beta1/src/test/regress/log/postmaster.log 
for the reason

make[2]: *** [check] Error 2
make[2]: Leaving directory 
`/home/andy/gis/postgresql-9.0beta1/src/test/regress'

make[1]: *** [check] Error 2
make[1]: Leaving directory `/home/andy/gis/postgresql-9.0beta1/src/test'
make: *** [check] Error 2

$cat  /home/andy/gis/postgresql-9.0beta1/src/test/regress/log/postmaster.log
LOG:  database system was shut down at 2010-05-03 12:39:58 CDT
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

--
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] Native DB replication for PG

[Merlin Moncure]

On Mon, May 3, 2010 at 9:29 AM, Gauthier, Dave  wrote:
> So, back to the base note, as far as native replication support, I should 
> wait for v9 whic'll probably be out by the end of the summer?

for 'native' replication waiting for 9.0 is your only option, because
all of the other replication options available today are 3rd party
unless you count warm standby.

the various 3rd party replication solutions have pros and cons.
speaking generally, they are more complex to set up and administer
than hs/sr but also more flexible.

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] pg9 beta1, make check fails

[Tom Lane]

Andy Colson  writes:
> $make check
> ... snip lots of output...
> ./pg_regress --inputdir=. --dlpath=. --multibyte=SQL_ASCII 
> --temp-install=./tmp_check --top-builddir=../../.. 
> --schedule=./parallel_schedule
> == removing existing temp installation==
> == creating temporary installation==
> == initializing database system   ==
> == starting postmaster==

> pg_regress: postmaster did not respond within 60 seconds

Hmm, apparently some sort of communication problem, since the postmaster
seems to think everything's fine.  Maybe a firewall blocking packets
to the port that got selected?  Another possibility is that you have
PGPORT or related variables set in your shell environment, and that's
leading libpq to try to connect to the wrong place.  (I thought we had
pg_regress try to clear those, but maybe it doesn't.)  What platform
is this anyway?

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] pg9 beta1, make check fails

[Andy Colson]

On 5/3/2010 12:59 PM, Tom Lane wrote:

Andy Colson  writes:

$make check
... snip lots of output...
./pg_regress --inputdir=. --dlpath=. --multibyte=SQL_ASCII
--temp-install=./tmp_check --top-builddir=../../..
--schedule=./parallel_schedule
== removing existing temp installation==
== creating temporary installation==
== initializing database system   ==
== starting postmaster==



pg_regress: postmaster did not respond within 60 seconds


Hmm, apparently some sort of communication problem, since the postmaster
seems to think everything's fine.  Maybe a firewall blocking packets
to the port that got selected?  Another possibility is that you have
PGPORT or related variables set in your shell environment, and that's
leading libpq to try to connect to the wrong place.  (I thought we had
pg_regress try to clear those, but maybe it doesn't.)  What platform
is this anyway?

regards, tom lane


Its an internal only box (no outside connections), so no iptables.

Box is running Slackware 64:

# uname -a
Linux mapper 2.6.32.7 #1 SMP Fri Jan 29 21:04:54 CST 2010 x86_64 AMD 
Athlon(tm) II X2 245 Processor AuthenticAMD GNU/Linux


I dont have PGPORT env set, or any env starting with PG*  (not sure what 
other env var's to check for).


I do have a .pgpass in ~, but no entries for this box, only to remote box.

no other .pg* files.

I did notice when I tried to backup my 8.4 db using the 9.0 pg_dump I 
got errors:


$  ~/gis/postgresql-9.0beta1/src/bin/pg_dump/pg_dump -Fc -f gis.bak gis
/home/andy/gis/postgresql-9.0beta1/src/bin/pg_dump/pg_dump: symbol 
lookup error: 
/home/andy/gis/postgresql-9.0beta1/src/bin/pg_dump/pg_dump: undefined 
symbol: PQconnectdbParams



Was not sure if that was a problem (maybe 'make check' was finding 8.4 
.so's or something...).


-Andy

--
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] pg9 beta1, make check fails

[Andy Colson]

On 5/3/2010 12:59 PM, Tom Lane wrote:

Andy Colson  writes:

$make check
... snip lots of output...
./pg_regress --inputdir=. --dlpath=. --multibyte=SQL_ASCII
--temp-install=./tmp_check --top-builddir=../../..
--schedule=./parallel_schedule
== removing existing temp installation==
== creating temporary installation==
== initializing database system   ==
== starting postmaster==



pg_regress: postmaster did not respond within 60 seconds



Ok, not sure what order its supposed to be run in, but I had done:

make
make check

which fails.

But if I do:

make
make install
make check

it works and passes.

-Andy


--
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] pg9 beta1, make check fails

[Tom Lane]

Andy Colson  writes:
> Ok, not sure what order its supposed to be run in, but I had done:

> make
> make check

> which fails.

> But if I do:

> make
> make install
> make check

> it works and passes.

That looks like a shared-library problem --- did the "make install"
overwrite an older version of libpq.so?

I'd have thought that pg_regress would have a more obvious failure if it
was trying to use an old libpq.so version though --- it should have
looked similar to what you referenced for pg_dump, for instance.

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] pg9 beta1, make check fails

[Andy Colson]

On 5/3/2010 1:21 PM, Tom Lane wrote:

Andy Colson  writes:

Ok, not sure what order its supposed to be run in, but I had done:



make
make check



which fails.



But if I do:



make
make install
make check



it works and passes.


That looks like a shared-library problem --- did the "make install"
overwrite an older version of libpq.so?

I'd have thought that pg_regress would have a more obvious failure if it
was trying to use an old libpq.so version though --- it should have
looked similar to what you referenced for pg_dump, for instance.

regards, tom lane


I'm guessing so.. I'd renamed my install dir from pgsql (which is in the 
path) to pg84, then done a 'make install'.


As a test I renamed my pgsql (which contains 9) to pg9 (so its not in 
the path) then did a make check and it worked ok.


Then I renamed pg84 to pgsql (so 8.4 is in the path) and 'make check' 
fails again.


-Andy

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

Re: Fwd: [GENERAL] Tablespace Problem

[Kalai R]

Sir,

I have tried to change the ownership of the postgres directories to
postgresql account.
But in windows , the posgresql account has no permission to take ownership
of any directory.
Because the postgresql service account is not under any group and not as a
logon account.
How can I set ownership for postgres account?

Thanks in advance.
On Mon, May 3, 2010 at 6:49 PM, jus...@magwerks.com wrote:

>
>  Message from Kalai R at 
> 05-03-2010 05:41:24 PM --
>
>  Hi,
>  Sir,
>  I have been checked pg_tblspc/86014 directory have no permission to
> create  new folder.
>  I am using Windows XP Os and VB.Net frontend. I created the tablespace
> folder by programaticaly using vb.net.
>
> I tried to chang the  user permission for that folder by programatically
> and manualy using properties of the folder in windows explorer but folder
> permission not changed.
>  Is there any option to solve this problem via postgersql?
>
> If not , Should I change the folder permission for which user? current user
> or service account for postgresql.
>
>
> ***snip***
> the Postgresql account needs to have full control over all its directories
>
> No other account needs access to postgresql directories.
>
> If security does not read as postgres account having full control and you
> can't change security, You need to take ownership of the directories first
> then you can change security.
>
> To take ownership you have to be admin on that computer.  Then put postgres
> account as full control.
>
>
> All legitimate Magwerks Corporation quotations are sent in a .PDF file
> attachment with a unique ID number generated by our proprietary quotation
> system. Quotations received via any other form of communication will not be
> honored.
>
> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
> legally privileged, confidential or other information proprietary to
> Magwerks Corporation and is intended solely for the use of the individual to
> whom it addresses. If the reader of this e-mail is not the intended
> recipient or authorized agent, the reader is hereby notified that any
> unauthorized viewing, dissemination, distribution or copying of this e-mail
> is strictly prohibited. If you have received this e-mail in error, please
> notify the sender by replying to this message and destroy all occurrences of
> this e-mail immediately.
> Thank you.
>

Re: [GENERAL] Native DB replication for PG

[Scott Marlowe]

On Mon, May 3, 2010 at 7:29 AM, Gauthier, Dave  wrote:
> So, back to the base note, as far as native replication support, I should 
> wait for v9 whic'll probably be out by the end of the summer?

Yep.  You should really start testing now though if you're gonna use it.

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

[GENERAL] GeSHi module for Postgresql?

[Leif Biberg Kristensen]

GeSHi  is a system for 
highlighting code. I've just installed the WP-Syntax plugin, which utilizes 
GeSHi, in my WP blog because I like code highlighting and am tired of doing it 
more or less by hand. However, I was disappointed when I didn't find PostgreSQL 
on the list of supported languages.

I wonder if any of you have at least started writing a PostgreSQL module, in 
which case I'll be happy to contribute. From a peek at the plsql module, it 
doesn't look like rocket science. If I have to write one from scratch, I 
wonder if there' s a compiled list somewhere of just the PostgreSQL keywords 
and function names. That would be of great help.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

[Leif Biberg Kristensen]

On Monday 3. May 2010 22.49.21 Leif Biberg Kristensen wrote:
> GeSHi  is a system for 

Sorry about that link. It's of course .

*blush*

-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

[Richard Broersma]

On Mon, May 3, 2010 at 1:49 PM, Leif Biberg Kristensen
 wrote:
> I
> wonder if there' s a compiled list somewhere of just the PostgreSQL keywords
> and function names. That would be of great help.

These are what I know of:

http://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html
http://www.postgresql.org/docs/9.0/static/functions.html

http://www.postgresql.org/docs/9.0/static/reference.html

-- 
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] GeSHi module for Postgresql?

[Leif Biberg Kristensen]

On Monday 3. May 2010 23.05.54 Richard Broersma wrote:
> On Mon, May 3, 2010 at 1:49 PM, Leif Biberg Kristensen
>  wrote:
> > I
> > wonder if there' s a compiled list somewhere of just the PostgreSQL 
keywords
> > and function names. That would be of great help.
> 
> These are what I know of:

Richard, thank you.
 
> http://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html

That list is a table with descriptions etc, but I can extraxt the keywords 
with a little Perl magic.

> http://www.postgresql.org/docs/9.0/static/functions.html

That's not very useful. I see that I can get a list of the functions in public 
with a \df, put can I get a corresponding one for the system functions?

> http://www.postgresql.org/docs/9.0/static/reference.html

That list is probably superfluous, as I suspect those words are already in the 
keywords list.

But thanks anyway. I've also received a private message from a guy with an 
«under work» project, and along with your hints it's a good start.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

[Christophe Chauvet]

Le 03/05/10 22:54, Leif Biberg Kristensen a écrit :
> On Monday 3. May 2010 22.49.21 Leif Biberg Kristensen wrote:
>> GeSHi  is a system for 
> 
> Sorry about that link. It's of course .
> 
> *blush*
> 
hi

i have begin this work, you can find it at

http://svn.postgresqlfr.org/repos/tools/geshi/trunk/

Regards,

-- 
Christophe Chauvet
Président d'Ornix (http://ornix.org)
Membres et Administrateur de PostgreSQLFr (http://postgresql.fr)

-- 
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] GeSHi module for Postgresql?

[Leif Biberg Kristensen]

On Monday 3. May 2010 23.02.05 Christophe Chauvet wrote:

> hi
> 
> i have begin this work, you can find it at
> 
> http://svn.postgresqlfr.org/repos/tools/geshi/trunk/

Christophe, thank you very much!

I've started to look at it, and have already taken your postgresql.php for a 
spin. You can see the first test at my blog under the title «Regular expression 
fun in PostgreSQL». I don't want to post the direct link here.

As soon as I've got some real progress, I'll send you an updated file.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] Dynamic SQL with pgsql, how to?

[Andre Lopes]

Thanks for the reply's,

I need to do a Dynamic SELECT INTO. There is a way of doing it?

Best Regards,


On Mon, May 3, 2010 at 10:05 AM, A. Kretschmer <
andreas.kretsch...@schollglas.com> wrote:

> In response to Andre Lopes :
> > Hi,
> >
> > I need to write some dynamic SQL in pgsql.
> > There is documentation on how can I do this in pgsql?
>
> Sure,
>
> http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> 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] GeSHi module for Postgresql?

[Alvaro Herrera]

Leif Biberg Kristensen wrote:
> On Monday 3. May 2010 23.05.54 Richard Broersma wrote:
> > On Mon, May 3, 2010 at 1:49 PM, Leif Biberg Kristensen
> >  wrote:
> > > I
> > > wonder if there' s a compiled list somewhere of just the PostgreSQL 
> keywords
> > > and function names. That would be of great help.
> > 
> > These are what I know of:
> 
> Richard, thank you.
>  
> > http://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html
> 
> That list is a table with descriptions etc, but I can extraxt the keywords 
> with a little Perl magic.

Why wouldn't you simply ask Postgres?

select * from pg_get_keywords();

-- 
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] Help with tracking!

[Shoaib Mir]

2010/4/19 Đỗ Ngọc Trí Cường 

> Dear all,
>
> I've a problem but I search all the help file and can't find the solution.
>
> I want to track all action of a specify role on all or one schema in
> database.
>
> Can you help me?
>
> Thanks a lot and Best Regard,
>


Setup your log_line_prefix and log_statement setting properly in
postgresql.conf so that you get the user info and other appropriate details.
After that you can tail the DB server log file and grep for that specific
user to get what sort of SQL is been executed.

-- 
Shoaib Mir
http://shoaibmir.wordpress.com/

Re: [GENERAL] pg9 beta1, make check fails

[Tom Lane]

I wrote:
> I'd have thought that pg_regress would have a more obvious failure if it
> was trying to use an old libpq.so version though --- it should have
> looked similar to what you referenced for pg_dump, for instance.

I think I see what's going on here.  pg_regress itself doesn't link to
libpq at all.  psql uses some of the new functions that were added to
libpq in 9.0, so psql is guaranteed to fail if the linker tries to link
it to an 8.4 version of libpq, which is probably what was happening
before you did "make install".  The reason that this led to the observed
behavior is that pg_regress does this to see if the postmaster is
running yet:

if (system("psql ... 2>/dev/null") == 0)
// postmaster is ready
else
// keep waiting

So psql failed, spewed something to stderr that went right into the bit
bucket, and pg_regress just saw that as an expected failure and kept
waiting.

I'm not immediately seeing a simple way to improve this.  It'd be nice
if we didn't hide "unexpected" errors like the link failure with libpq.
On the other hand we surely don't want to show the expected connection
failures until the postmaster is up.  Maybe psql should have a "really
quiet" mode that doesn't print anything even on errors, and then we
wouldn't have to route its stderr to /dev/null?  But given how seldom
this sort of thing comes up (I don't recall any similar previous reports,
actually) maybe it's not worth the trouble.

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] Latest source RPMs for 8.1.20

[Devrim GÜNDÜZ]

On Mon, 2010-05-03 at 10:49 -0500, Justin Pasher wrote:
> I'm looking for the latest source RPMs for Postgres 8.1.20 on RHEL

http://yum.pgrpms.org/srpms/8.1/redhat/rhel-5Server-x86_64/repoview/postgresql.html

Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


-- 
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] GeSHi module for Postgresql?

[Leif Biberg Kristensen]

On Monday 3. May 2010 23.50.55 Alvaro Herrera wrote:

> Why wouldn't you simply ask Postgres?

Because I didn't know how :D That's why we've got this wonderful list, isn't 
it?
 
> select * from pg_get_keywords();

Cool! Thank you! I really only need the 'word' column. I'll separate the data 
types from the rest of the keywords, though.

Now I only need a list of the built-in functions. There doesn't seem to be a 
pg_get functions()?

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

[Yeb Havinga]

Leif Biberg Kristensen wrote:
Now I only need a list of the built-in functions. There doesn't seem to be a 
pg_get functions()?
  

Try psql -E postgres
(-E shows all queries psql does on the catalog)

then \df

regards,
Yeb Havinga


--
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] Dynamic SQL with pgsql, how to?

[Maximilian Tyrtania]

Am 03.05.2010 um 23:50 schrieb Andre Lopes:

> Thanks for the reply's,
> 
> I need to do a Dynamic SELECT INTO. There is a way of doing it?

Yes. Plpgsql supports this:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

See http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html

Max

Maximilian Tyrtania Software-Entwicklung
Dessauer Str. 6-7
10969 Berlin
Tel.:++49/30/48827-952
Mobil: 0152/292 707 36
email: maximilian.tyrta...@byte-employer.de
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general