[GENERAL] libpq Windows 7 64

[Bret Stern]

Got a new fancy pants Dell T3500 Windows 7 Ultimate 64 bit machine.
PostgreSql 9.1 

Trying to port a 32-bit XP libpq console app with Codeblocks.

Seems like the libpq.lib isn't linking quite right as the PQ_Connectdb,
PQstatus etc lib functions are reported as undefined.

Anyone out there get Windows 7 (64) libpq working?

PGadmin works great on my machine. It uses libpq.

Any and all help appreciated.


-- 
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] Procedural Languages

[Chris Travers]

Minor correction

On Thu, May 31, 2012 at 1:57 PM, John Townsend
 wrote:

> Fortran was the first computer language for me. (I guess that reveals my age
> :-) )

Fortran was my second computer language, but I hated it.
>
> PL/pgSQL is easy to learn for me since it is pascal like. It appears this is
> the one to use, if you want to write maintainable code for others. (Next
> choice might be Perl*).
>
> PL/pgSQL was the choice by Postbooks. Almost 1300 functions (counting
> triggers) are used for the open source addition. It's a good example of how
> to do build a nice PG app.
>
> But I don't like the environment, namely using Qt and C++. Of course, if you
> have this environment already setup, and you are a good C++ programmer, then
> it will not be easy.
>
> *Perl was used in SMB Ledger.

Minor correction:

LedgerSMB uses Perl in the middle layer around the database.  All our
stored procedures are in SQL or PL/PGSQL depending on complexity of
operations and inputs.

Best Wishes,
Chris Travers

-- 
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] Procedural Languages

[Chris Travers]

On Thu, May 31, 2012 at 7:36 AM, John Townsend
 wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.
>
> Which ones do you use and why?

Virtually all the time I use PL/PGSQL.  The reason is that I think
that the primary purpose of a stored procedure language is to
encapsulate database functionality inside the database.  Secondarily I
write functions in plain old SQL.  With SQL becoming more
full-featured (CTE's etc) the use cases I have for PL/PGSQL are
actually shrinking.  The major reasons I use PL/PGSQL as opposed to
SQL are actually shrinking.  The major reasons I use it today are:

1)  Exception handling and triggers
2)  There are a few cases where logic is sufficiently complex that the
procedural extensions are really helpful.
3)  Backwards-compatibility with older PostgreSQL versions (won't use
writeable CTE's for a while)
4)  named input arguments, so if there are more than a few arguments,
I will use PL/PGSQL just because I think it leads to more readable
code.

My view is that PL/PGSQL rocks.  Code written in PL/PGSQL is clear,
readable, and database-friendly.  While there may be niches for other
languages but for db stuff, it is my workhorse.

BTW, I second the point about listen/notify.  I have some sample code
there I can share.  It's not perfect by any means and would probably
cause annoyances if used as is in production but you can find it at
https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.3/utils/notify_short/

Best Wishes,
Chris Travers

-- 
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] Procedural Languages

[Joshua Tolley]

On Fri, Jun 01, 2012 at 08:13:28AM +1200, Mike Toews wrote:
> On 1 June 2012 02:36, John Townsend  wrote:
> > There are least 10 Procedural Languages available for PostGreSQL. The one
> > that comes with the installation is PL/pgSQL.
> 
> The count looks closer to 18
> http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
> but I doubt some get much use (PL/LOLCODE anyone?).

I use PL/LOLCODE... in its test suite, which runs on the rare occasion I make
changes to the language.

For whatever it's worth, PL/LOLCODE wasn't written as a language people would
use in production, but rather as a teaching tool. It taught me rather nicely,
too. :)

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


pgpc2KfpMpMGx.pgp
Description: PGP signature

[GENERAL] autovacuum running for a long time on a new table with 1 row

[Lonni J Friedman]

Running 9.1.3 on Linux-x86_64.  I'm seeing autovacuum running for the
past 6 hours on a newly created table that only has 1 row of data in
it.  This table did exist previously, but was dropped & recreated.
I'm not sure if that might explain this behavior.  When I strace the
autovacuum process, I see the following scrolling by non-stop (with no
changes to the file referenced):
select(0, NULL, NULL, NULL, {0, 21000}) = 0 (Timeout)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
select(0, NULL, NULL, NULL, {0, 21000}) = 0 (Timeout)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)
open("base/16412/214803_vm", O_RDWR)= -1 ENOENT (No such file or directory)

Is this normal/expected?

thanks

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

[John R Pierce]

On 05/31/12 2:57 AM, R, Abhilash (HP Software) wrote:
2012-05-31 03:15:54 IST LOG:  server process (PID 49972) was 
terminated by exception 0xC12D


Out of virtual memory

Error exception 0xC12D
  Your system is low on virtual memory. To ensure that Windows runs 
correctly,

  increase the size of your virtual memory paging file.
  For more information, see Help.


thats the description of that fault code from MS Windows.so, 
something you did in server process 49972 ate up more memory than was 
available.a query that returns a huge result set can do this as the 
result set has to be collected in memory before it can be returned to 
the client.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

[Jeff Davis]

On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote:
> I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3.  Here's
> the error:

Please send /srv/pg_upgrade_dump_globals.sql

Also, can you restart the old system (by removing the ".old" suffix, as
the message suggests), and then do a "SELECT oid,* FROM pg_authid" and
send the output along?

Regards,
Jeff Davis




-- 
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] Procedural Languages

[Jeff Davis]

On Thu, 2012-05-31 at 11:23 -0700, Darren Duncan wrote:
> Michael Nolan wrote:
> > PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily 
> > to launch shell scripts from triggers, for example to update an external 
> > website when a row in a table has been inserted, deleted or updated.
> 
> There is also another way to do what you describe that might be more secure.
> 
> Rather than having the DBMS launch shell scripts directly, instead use 
> LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an 
> ordinary client script listening for them, and the client script launches the 
> shell scripts when it gets a message.
> 
> This way, you need a persistent client script, but you don't need to invoke 
> the 
> shell in the DBMS ... or use the untrusted version of PL/Perl if that's all 
> it 
> was for.

An additional advantage is that if you issue NOTIFY with exactly the
same message many times in one transaction, the LISTENer only gets the
message once.

In other words, a big update won't case a million rebuilds of the static
pages.

Regards,
Jeff Davis


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

[Merlin Moncure]

On Thu, May 31, 2012 at 4:57 AM, R, Abhilash (HP Software)
 wrote:
> PostgreSQL 9.0.4 is crashing intermittently in Windows 2008 R2 environment
> with following error in log file.
>
>
>
> 2012-05-31 03:15:54 IST LOG:  server process (PID 49972) was terminated by
> exception 0xC12D
>
> 2012-05-31 03:15:54 IST HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
>
> 2012-05-31 03:15:54 IST LOG:  terminating any other active server processes
>
> 2012-05-31 03:15:54 IST WARNING:  terminating connection because of crash of
> another server process
>
> 2012-05-31 03:15:54 IST DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
>
> 2012-05-31 03:15:54 IST HINT:  In a moment you should be able to reconnect
> to the database and repeat your command.
>
> 2012-05-31 03:15:54 IST WARNING:  terminating connection because of crash of
> another server process
>
>
>
> I could find a related, known issue with 8.3.9 with ref. 5965; Is  this
> issue fixed in 9.0.4?

After some googling I'm seeing some hints that you may have
overcommitted virtual memory.  How much memory do you have and what's
shared buffers set to?  Is this 32/64 bit?

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] Procedural Languages

[Scott Marlowe]

On Thu, May 31, 2012 at 2:13 PM, Mike Toews  wrote:
> On 1 June 2012 02:36, John Townsend  wrote:
>> There are least 10 Procedural Languages available for PostGreSQL. The one
>> that comes with the installation is PL/pgSQL.
>
> The count looks closer to 18
> http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
> but I doubt some get much use (PL/LOLCODE anyone?).

And don't be surprised if you find one not listed there.

For instance, my entire production system runs entirely on pl/bf

https://github.com/mikejs/pl-bf

It's really the only logical choice for critical and complex financial
analysis work.

-- 
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] Procedural Languages

[John Townsend]

On 5/31/2012 3:13 PM, Mike Toews wrote:

On 1 June 2012 02:36, John Townsend  wrote:

There are least 10 Procedural Languages available for PostGreSQL. The one
that comes with the installation is PL/pgSQL.

The count looks closer to 18
http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
but I doubt some get much use (PL/LOLCODE anyone?).

PL/R opens some good functionality not offered by any other language,
and is particularly useful for statistics, GIS and other science
applications. R itself is often a wrapper to dusty FORTRAN subroutines
published in statistics journals decades ago.

-Mike


Thanks.

Fortran was the first computer language for me. (I guess that reveals my 
age :-) )


PL/pgSQL is easy to learn for me since it is pascal like. It appears 
this is the one to use, if you want to write maintainable code for 
others. (Next choice might be Perl*).


PL/pgSQL was the choice by Postbooks 
. Almost 1300 functions (counting 
triggers) are used for the open source addition. It's a good example of 
how to do build a nice PG app.


But I don't like the environment, namely using Qt and C++. Of course, if 
you have this environment already setup, and you are a good C++ 
programmer, then it will not be easy.


*Perl was used in SMB Ledger .

John

[GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

[Bryan Murphy]

I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3.  Here's the
error:

psql:/srv/pg_upgrade_dump_globals.sql:54: ERROR:  duplicate key value
violates unique constraint "pg_authid_oid_index"
DETAIL:  Key (oid)=(10) already exists.

Any ideas what I'm doing wrong?

Here's the verbose output from pg_upgrade:

$ /opt/postgresql-9.1/bin/pg_upgrade --link --verbose
--old-datadir=/srv/postgresql/pg_data --new-datadir=/srv/postgres-9.1
--old-bindir=/opt/postgresql-9.0/bin --new-bindir=/opt/postgresql-9.1/bin
Running in verbose mode
Performing Consistency Checks
-
Checking current, bin, and data directories ok
Checking cluster versions   ok
"/opt/postgresql-9.0/bin/pg_ctl" -w -l "/dev/null" -D
"/srv/postgresql/pg_data" -o "-p 5432 -c autovacuum=off -c
autovacuum_freeze_max_age=20" start >> "/dev/null" 2>&1
Checking database user is a superuser   ok
Checking for prepared transactions  ok
Checking for reg* system oid user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Creating catalog dump
"/opt/postgresql-9.1/bin/pg_dumpall" --port 5432 --username "postgres"
--schema-only --binary-upgrade > "/srv/pg_upgrade_dump_all.sql"
ok
"/opt/postgresql-9.0/bin/pg_ctl" -w -l "/dev/null" -D
"/srv/postgresql/pg_data"  stop >> "/dev/null" 2>&1
"/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
-o "-p 5432 -b" start >> "/dev/null" 2>&1
Checking for prepared transactions  ok
Checking for presence of required libraries ok

| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from /srv/postgresql/pg_data/global/pg_control.old.

Performing Upgrade
--
Adding ".old" suffix to old global/pg_control   ok
Analyzing all rows in the new cluster
"/opt/postgresql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all
--analyze >> "/dev/null" 2>&1
ok
Freezing all rows on the new cluster
 "/opt/postgresql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all
--freeze >> "/dev/null" 2>&1
ok
"/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
 stop >> "/dev/null" 2>&1
Deleting new commit clogs   ok
Copying old commit clogs to new server  cp -Rf
"/srv/postgresql/pg_data/pg_clog" "/srv/postgres-9.1/pg_clog"
ok
Setting next transaction id for new cluster
"/opt/postgresql-9.1/bin/pg_resetxlog" -f -x 743542427 "/srv/postgres-9.1"
> /dev/null
ok
Resetting WAL archives
 "/opt/postgresql-9.1/bin/pg_resetxlog" -l 1,829,15 "/srv/postgres-9.1" >>
"/dev/null" 2>&1
ok
"/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
-o "-p 5432 -b" start >> "/dev/null" 2>&1
Setting frozenxid counters in new cluster   ok
Creating databases in the new cluster
"/opt/postgresql-9.1/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc --port
5432 --username "postgres" -f "/srv/pg_upgrade_dump_globals.sql" --dbname
template1 >> "/dev/null"
psql:/srv/pg_upgrade_dump_globals.sql:54: ERROR:  duplicate key value
violates unique constraint "pg_authid_oid_index"
DETAIL:  Key (oid)=(10) already exists.

There were problems executing "/opt/postgresql-9.1/bin/psql" --set
ON_ERROR_STOP=on --no-psqlrc --port 5432 --username "postgres" -f
"/srv/pg_upgrade_dump_globals.sql" --dbname template1 >> "/dev/null"
Failure, exiting
"/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
-m fast stop >> "/dev/null" 2>&1

Thanks,
Bryan

Re: [GENERAL] Procedural Languages

[Mike Toews]

On 1 June 2012 02:36, John Townsend  wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.

The count looks closer to 18
http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
but I doubt some get much use (PL/LOLCODE anyone?).

PL/R opens some good functionality not offered by any other language,
and is particularly useful for statistics, GIS and other science
applications. R itself is often a wrapper to dusty FORTRAN subroutines
published in statistics journals decades ago.

-Mike

-- 
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] Procedural Languages

[Darren Duncan]

Michael Nolan wrote:

On Thu, May 31, 2012 at 2:23 PM, Darren Duncan  wrote:
Michael Nolan wrote:

PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu
primarily to launch shell scripts from triggers, for example to
update an external website when a row in a table has been
inserted, deleted or updated.

There is also another way to do what you describe that might be more
secure.

Rather than having the DBMS launch shell scripts directly, instead
use LISTEN/NOTIFY messaging, where the trigger posts a message, and
you have an ordinary client script listening for them, and the
client script launches the shell scripts when it gets a message.

This way, you need a persistent client script, but you don't need to
invoke the shell in the DBMS ... or use the untrusted version of
PL/Perl if that's all it was for.

Anybody have examples of a persistent client script?


Not a whole one.  But you can make a simple daemon in Perl, either by hand or 
using some CPAN module.


The Postgres-related part though, see 
http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_notifies for how the client script 
talks to Postgres for this message passing.


Hopefully that'll answer the more interesting part for you.

-- Darren Duncan

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

Fwd: [GENERAL] Procedural Languages

[Michael Nolan]

-- Forwarded message --
From: Michael Nolan 
Date: Thu, May 31, 2012 at 2:49 PM
Subject: Re: [GENERAL] Procedural Languages
To: Darren Duncan 




On Thu, May 31, 2012 at 2:23 PM, Darren Duncan wrote:

> Michael Nolan wrote:
>
>> PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily
>> to launch shell scripts from triggers, for example to update an external
>> website when a row in a table has been inserted, deleted or updated.
>>
>
> There is also another way to do what you describe that might be more
> secure.
>
> Rather than having the DBMS launch shell scripts directly, instead use
> LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an
> ordinary client script listening for them, and the client script launches
> the shell scripts when it gets a message.
>
> This way, you need a persistent client script, but you don't need to
> invoke the shell in the DBMS ... or use the untrusted version of PL/Perl if
> that's all it was for.
>
> -- Darren Duncan
>

Anybody have examples of a persistent client script?
--
Mike Nolan

Re: [GENERAL] Procedural Languages

[Jeff Davis]

On Thu, 2012-05-31 at 11:52 -0600, David Salisbury wrote:
> I've often wondered how these "external" languages perform, figuring
> that using a native language would perform better.

One language isn't more "native" than another, really. SQL is a bit more
native in the sense that it might be inlined, and C is more native in
the sense that it is native code.

But PL/pgSQL just happens to be a good language when you are doing
mostly SQL with some procedural aspects, it doesn't really have an
inherent performance advantage over external PLs. There may be some
implementation quality differences, however.

> If I'm executing say a PL/Perl procedure, once I've executed it the first
> time, can I take it the interpreter is now resident withing the PG footprint?

Yes.

Regards,
Jeff Davis



-- 
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] Procedural Languages

[Darren Duncan]

Michael Nolan wrote:
PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily 
to launch shell scripts from triggers, for example to update an external 
website when a row in a table has been inserted, deleted or updated.


There is also another way to do what you describe that might be more secure.

Rather than having the DBMS launch shell scripts directly, instead use 
LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an 
ordinary client script listening for them, and the client script launches the 
shell scripts when it gets a message.


This way, you need a persistent client script, but you don't need to invoke the 
shell in the DBMS ... or use the untrusted version of PL/Perl if that's all it 
was for.


-- Darren Duncan

--
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] Procedural Languages

[David Salisbury]

On 5/31/12 8:36 AM, John Townsend wrote:

  There are least 10 Procedural Languages
 available for PostGreSQL. The
one that comes with the installation is PL/pgSQL.

Which ones do you use and why?



I've often wondered how these "external" languages perform, figuring
that using a native language would perform better.

If I'm executing say a PL/Perl procedure, once I've executed it the first
time, can I take it the interpreter is now resident withing the PG footprint?
Is it analogous to say Apache and mod_perl?  The module/library has been loaded
and is now ready?  Or is it more along the lines of the one single PL/perl
function is now cached, and any other function executed afterward will need to
be brought in but the cached procedure is now "in ram"?  Or.. is the function
code just passed off to the system in a new process?  I.e.. how do these hooks 
work?

Thanks,

-ds

--
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] Procedural Languages

[Michael Nolan]

On Thu, May 31, 2012 at 10:36 AM, John Townsend <
jtowns...@advancedformulas.com> wrote:

>  There are least 10 Procedural 
> Languagesavailable for PostGreSQL. The 
> one that comes with the installation is
> PL/pgSQL.
>
> Which ones do you use and why?
>
> Thanks,
>
> John Townsend
>

PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily to
launch shell scripts from triggers, for example to update an external
website when a row in a table has been inserted, deleted or updated.
--
Mike Nolan

Re: [GENERAL] Procedural Languages

[Leif Biberg Kristensen]

 Torsdag 31. mai 2012 17.07.19 skrev Merlin Moncure :
> pl/pgsql is unique in that it has 'first class queries' -- sql is
> intermixed freely with procedural code and it uses the same type
> system and error handling mechanisms  (although the syntax is
> different).   this directly translates into direct, impactful coding
> as long as you are not trying to do things that are awkward for the
> language like heavy computation or string processing.

I'm using plpgsql for string processing all the time, mostly with regexes, and 
don't find it particularly awkward. That may of course be an example of the "If 
all you've got is a hammer, all problems look like nails" syndrome. But I've 
never felt a need for installing another pl language.

regards, Leif

-- 
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] Procedural Languages

[Merlin Moncure]

On Thu, May 31, 2012 at 9:36 AM, John Townsend
 wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.
>
> Which ones do you use and why?

pl/pgsql is unique in that it has 'first class queries' -- sql is
intermixed freely with procedural code and it uses the same type
system and error handling mechanisms  (although the syntax is
different).   this directly translates into direct, impactful coding
as long as you are not trying to do things that are awkward for the
language like heavy computation or string processing.

the other languages have various pros and cons but at the end are
typically going to be judged by your familiarity with the particular
language.  I have a special fondness for pl/sh for example.

merlin

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

[R, Abhilash (HP Software)]

PostgreSQL 9.0.4 is crashing intermittently in Windows 2008 R2 environment with 
following error in log file.

2012-05-31 03:15:54 IST LOG:  server process (PID 49972) was terminated by 
exception 0xC12D
2012-05-31 03:15:54 IST HINT:  See C include file "ntstatus.h" for a 
description of the hexadecimal value.
2012-05-31 03:15:54 IST LOG:  terminating any other active server processes
2012-05-31 03:15:54 IST WARNING:  terminating connection because of crash of 
another server process
2012-05-31 03:15:54 IST DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another server 
process exited abnormally and possibly corrupted shared memory.
2012-05-31 03:15:54 IST HINT:  In a moment you should be able to reconnect to 
the database and repeat your command.
2012-05-31 03:15:54 IST WARNING:  terminating connection because of crash of 
another server process

I could find a related, known issue with 8.3.9 with ref. 5965; Is  this issue 
fixed in 9.0.4?

Please help.

Regards,
Abhilash R

[GENERAL] Procedural Languages

[John Townsend]

There are least 10 Procedural Languages 
 available for PostGreSQL. The 
one that comes with the installation is PL/pgSQL.


Which ones do you use and why?

Thanks,

John Townsend

Re: [GENERAL] Change request - log line prefix

[Stuart Bishop]

On Thu, May 31, 2012 at 12:19 PM, Chris Angelico  wrote:
> On Thu, May 31, 2012 at 2:05 PM, Evan Rempel  wrote:
>> Even when the wrap column is set to a very large value (32k) STATEMENT lines 
>> still wrap according to the line breaks in
>> the original SQL statement.
>
> The problem isn't so much the wrapping, it seems, as that your
> statements' line breaks are being propagated through. So as a possible
> alternative solution, perhaps there could be an option to replace
> newlines with spaces before the line goes to the log?

I'd certainly like to see this or similar (encode the querys into a
single line of ascii, lossy is ok). I like my logs both readable and
greppable.

-- 
Stuart Bishop 
http://www.stuartbishop.net/

-- 
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] Postgres no longer starts

[Adrian Klaver]

On 05/31/2012 12:45 AM, Bart Lateur wrote:

Alan Hodgson  writes:

On Wednesday, May 30, 2012 08:22:58 PM Bart Lateur wrote:

Luckily this is a development machine, but as we don't know what
causes the problem we fear we might one day face the exact same
problem where it does matter: on a production machine. So we'd like
to know exactly what went wrong..



Change selinux to permissive instead of enforcing and see if
PostgreSQL then runs. If it does, you can look at the selinux logging
to see what would have been denied in enforcing mode, and hopefully fix

it from there.


Yeah, I concur that this smells like a selinux issue.  Most likely, the

software update you did messed up the selinux "context" settings for some
files.  restorecon should be able to fix it for you, if so.

As Alan says, the kernel log (or separate avc log, depending on how your

system is set up) should show evidence of the problem if this is where it
is.

I thought the same, and I was happy to go in and fix it, but then I found
that SElinux was not even enabled.

Hunting around for more logs I finally found a recently updated log file in
the subdirectory /var/lib/pgsql/data/pg_log/. And there I found the message
that pg_hba.conf could not be loaded due to a syntax error in it. Weird, it
was running before...?

It's suboptimal that starting Postgres fails silently. It's also less than
optimal that the location of the log files is a bit of a secret. It's also
suboptimal that Postgres refuses to run because it doesn't understand 1 line
in pg_hba.conf. After all, it's just a data grid, not prose...


Actually it did not fail silently, it recorded it to the log. The 
location of the log file is no great secret, open postgresql.conf(the 
server confog file) and look for log_directory. pg_hba.conf is access 
control and the first line of defense in security. I would prefer that 
it fail closed instead of open.




Anyway, hint for Postgres newbies (or at least, people who don't spend whole
days administering Postgres, which is about everybody, I guess): find the
logs. They're in the subdirectory pg_log and they have names like
"postgresql-DDD.log" where DDD is the three letter name of the day. The
currently active log is one of them.


Not necessarily. It depends on how Postgres was packaged. See hint above 
about looking in postgresql.conf.




Thanks a lot, all of you who replied.




--
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] problem with restoring on an other server

[Adrian Klaver]

On 05/31/2012 06:33 AM, J.H. TREEP wrote:

Machine: SuSe linux 64
Postgres 9.0
Postgis 1-5

pg_restore: [archiver (db)] could not execute query: ERROR:  could not
access file "$libdir/postgis-1.5": No such file or directory


Either the user running the pg_restore does not have permissions for 
that directory or Postgis 1.5 does not exist in that directory.




Does someone knows how to solve this issue?

Johan.




--
Adrian Klaver
adrian.kla...@gmail.com

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

[GENERAL] problem with restoring on an other server

[J.H. TREEP]

Machine: SuSe linux 64Postgres 9.0Postgis 1-5pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-1.5": No such file or directoryDoes someone knows how to solve this issue?Johan.

Re: [GENERAL] Change request - log line prefix

[hubert depesz lubaczewski]

On Wed, May 30, 2012 at 09:05:23PM -0700, Evan Rempel wrote:
> I am part of a team that fills an operational roll administering 1000+ 
> servers and
> 100's of applications. Of course we need to "read" all of our logs, and must 
> use computers to
> help us. In filtering postgreSQL logs there is one thing that makes life 
> difficult for us admins.

consider using pg.grep:
http://www.depesz.com/2012/01/23/some-new-tools-for-postgresql-or-around-postgresql/

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] Postgres no longer starts

[Bart Lateur]

> Alan Hodgson  writes:
> > On Wednesday, May 30, 2012 08:22:58 PM Bart Lateur wrote:
> >> Luckily this is a development machine, but as we don't know what 
> >> causes the problem we fear we might one day face the exact same 
> >> problem where it does matter: on a production machine. So we'd like 
> >> to know exactly what went wrong..
> 
> > Change selinux to permissive instead of enforcing and see if 
> > PostgreSQL then runs. If it does, you can look at the selinux logging 
> > to see what would have been denied in enforcing mode, and hopefully fix
it from there.
> 
> Yeah, I concur that this smells like a selinux issue.  Most likely, the
software update you did messed up the selinux "context" settings for some
files.  restorecon should be able to fix it for you, if so.
> As Alan says, the kernel log (or separate avc log, depending on how your
system is set up) should show evidence of the problem if this is where it
is.

I thought the same, and I was happy to go in and fix it, but then I found
that SElinux was not even enabled.

Hunting around for more logs I finally found a recently updated log file in
the subdirectory /var/lib/pgsql/data/pg_log/. And there I found the message
that pg_hba.conf could not be loaded due to a syntax error in it. Weird, it
was running before...?

It's suboptimal that starting Postgres fails silently. It's also less than
optimal that the location of the log files is a bit of a secret. It's also
suboptimal that Postgres refuses to run because it doesn't understand 1 line
in pg_hba.conf. After all, it's just a data grid, not prose...

Anyway, hint for Postgres newbies (or at least, people who don't spend whole
days administering Postgres, which is about everybody, I guess): find the
logs. They're in the subdirectory pg_log and they have names like
"postgresql-DDD.log" where DDD is the three letter name of the day. The
currently active log is one of them.

Thanks a lot, all of you who replied.

-- 
Bart Lateur.



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