[GENERAL] Syntax error in spi_prepare usage

[dipti shah]

I am using SPI_Prepare from trigger like below. I have some minor issue with
$query syntax. I have tried almost all combination but I am not able to
resolve. Could anyone please help me out.

CREATE OR REPLACE FUNCTION insert_history_info()
RETURNS VOID AS
$BODY$
 my $query = (< there is some issue here.
  EXCEPT
  SELECT (id, txid, txtime)
  FROM changelogtest
  WHERE id = \$1
  AND txid = \$2
  AND txtime = \$3;
 ENDQUERY

 # Always use the prepared query if available
 if (not exists($_SHARED{$query})) {
  $_SHARED{$query} = spi_prepare($query, 'INTEGER', 'INTEGER', 'TIMESTAMP');
 }

 spi_exec_prepared($_SHARED{$query}, 5, 123, now());

 return;
$BODY$
  LANGUAGE 'plperl' VOLATILE SECURITY DEFINER

techdb=# SELECT insert_history_info();
ERROR:  error from Perl function "insert_history_info": INSERT has more
target columns than expressions at line 15.

If I remove the braces from (\$1, \$2, \$3) then it gives below error.
 my $query = (<

[GENERAL] Catch exception from plpy

[dario....@libero.it]

Hello,

Could someone show me how to catch exceptions generated by plpy.execute()?
>From the documentation and other posts I understand that you need to call plpy.
error() but I still cannot figure out how to use it.

For example, say I need a function that creates table foo if it doesn't 
exists, otherwise returns the rows in foo. Initially I thought the following 
shouldl work... but it doesn't!

--
create or replace function test_tryex() returns void AS $$
try:
plpy.execute('create table tmp_foo (v1 int);')
except:
plpy.execute('select * from tmp_foo;')
$$
language plpythonu;
--
-- If tmp_foo already exists I get:
select test_tryex();

** Error **

ERROR: relation "tmp_foo" already exists
SQL state: 42P07
Context: SQL statement "create table tmp_foo (v1 int);"


Many thanks in advance
Dario

-- 
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] Catch exception from plpy

[Martin Gainty]

use try with exception e.g.

create or replace function test_tryex() returns void AS
'
try:
plpy.execute('create table tmp_foo (v1 int);
plpy.execute('select * from tmp_foo);
except Exception, ex:
plpy.notice("FUBAR!-- %s" % str(ex))
return "FUBAR!"
return "test_tryex function has succeeded"'
'
LANGUAGE plpythonu;

Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




> Date: Sat, 24 Apr 2010 14:48:33 +0200
> From: dario@libero.it
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Catch exception from plpy
> 
> Hello,
> 
> Could someone show me how to catch exceptions generated by plpy.execute()?
> From the documentation and other posts I understand that you need to call 
> plpy.
> error() but I still cannot figure out how to use it.
> 
> For example, say I need a function that creates table foo if it doesn't 
> exists, otherwise returns the rows in foo. Initially I thought the following 
> shouldl work... but it doesn't!
> 
> --
> create or replace function test_tryex() returns void AS $$
> try:
> plpy.execute('create table tmp_foo (v1 int);')
> except:
> plpy.execute('select * from tmp_foo;')
> $$
> language plpythonu;
> --
> -- If tmp_foo already exists I get:
> select test_tryex();
> 
> ** Error **
> 
> ERROR: relation "tmp_foo" already exists
> SQL state: 42P07
> Context: SQL statement "create table tmp_foo (v1 int);"
> 
> 
> Many thanks in advance
> Dario
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
  
_
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1

[GENERAL] How to allow PostgreSQL to accept remote connection?

[Andre Lopes]

Hi,

I have a virtual machine with VMWARE, and I need to connect from outside the
virtual machine to PostgreSQL.

How can I configure the PostgreSQL to accept outside connections?

Best Regards,

Re: [GENERAL] How to allow PostgreSQL to accept remote connection?

[Raymond O'Donnell]

On 24/04/2010 15:58, Andre Lopes wrote:
> Hi,
> 
> I have a virtual machine with VMWARE, and I need to connect from outside
> the virtual machine to PostgreSQL.
> 
> How can I configure the PostgreSQL to accept outside connections?

It should be just the same as a "real" machine put the IP address of
the VM's network interface in "listen_addresses" in postgresql.conf.

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

Re: [GENERAL] How to allow PostgreSQL to accept remote connection?

[Andre Lopes]

Thanks for the reply's,

How can I configure pg_hba.conf to accept connections from all IP's

What I have in this file is:

#ipv4
host all all 127.0.0.1/32 md5

#ipv6
host all all ::1/128 md5

What I need to change?


Best Regards,


On Sat, Apr 24, 2010 at 4:15 PM, Fredric Fredricson <
fredric.fredric...@bonetmail.com> wrote:

> Raymond O'Donnell wrote:
>
>> On 24/04/2010 15:58, Andre Lopes wrote:
>>
>>
>>> Hi,
>>>
>>> I have a virtual machine with VMWARE, and I need to connect from outside
>>> the virtual machine to PostgreSQL.
>>>
>>> How can I configure the PostgreSQL to accept outside connections?
>>>
>>>
>>
>> It should be just the same as a "real" machine put the IP address of
>> the VM's network interface in "listen_addresses" in postgresql.conf.
>>
>>
> You will probably also have to edit pg_hba.conf file (chaper 20.1 in the
> manual).
> /Fredric
>
>> Ray.
>>
>>
>>
>>
>
>

[GENERAL] ISP provider with postgres and perl dbi

[John Gage]

I know this is perhaps an inappropriate question (and to some extent I  
am repeating myself), but I now need to get my website up and running.


Would anyone be willing to suggest an ISP that offers virtual machines  
running postgres and perl 5.8.8 or above with perl dbi?  The ideal  
candidate would offer something reasonably priced with some level of  
support.


The hub.org site is fairly opaque about its offerings these days, or,  
given its heritage, I would go there immediately.  If M. Fournier is  
listening, perhaps he can send a précis.


I apologize in advance for this intrusion.

John Gage
--
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] ISP provider with postgres and perl dbi

[Steve Atkins]

On Apr 24, 2010, at 8:40 AM, John Gage wrote:

> I know this is perhaps an inappropriate question (and to some extent I am 
> repeating myself), but I now need to get my website up and running.
> 
> Would anyone be willing to suggest an ISP that offers virtual machines 
> running postgres and perl 5.8.8 or above with perl dbi?  The ideal candidate 
> would offer something reasonably priced with some level of support.

If an ISP is offering virtual private servers (where you get full access to 
your own virtual machine) then installing postgresql and perl on them will be 
trivial (just one command on popular linux distributions). So if you're wanting 
to run the database in your VM then most anyone offering VPS hosting will have 
what you need. You're unlikely to get perl or postgresql specific support - but 
your ISP isn't where you'd usually look for that.

If you want somewhere that offers both virtual machines and managed postgresql 
hosting that's tougher, but 
http://www.postgresql.org/support/professional_hosting is probably a good place 
to start.

Cheers,
  Steve


-- 
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] Syntax error in spi_prepare usage

[Alban Hertroys]

> If I remove the braces from (\$1, \$2, \$3) then it gives below error.
> 
>  my $query = (<   INSERT INTO changelogtest(id, txid, txtime)
>   SELECT  \$1, \$2, \$3
>   EXCEPT
>   SELECT (id, txid, txtime)
>   FROM changelogtest
>   WHERE id = \$1
>   AND txid = \$2
>   AND txtime = \$3;
>  ENDQUERY
>  
> techdb=# SELECT insert_history_info();
> ERROR:  error from Perl function "insert_history_info": each EXCEPT query 
> must have the same number of columns at line 15.

Yes of course, your select lists are different. Try:
>  my $query = (<   INSERT INTO changelogtest(id, txid, txtime)
>   SELECT  \$1, \$2, \$3
>   EXCEPT
>   SELECT id, txid, txtime
>   FROM changelogtest
>   WHERE id = \$1
>   AND txid = \$2
>   AND txtime = \$3;
>  ENDQUERY

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bd3201310412109115467!



-- 
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] Invalid objects

[Oliver Kohll - Mailing Lists]

Scott,

I implemented a system exactly like this for the app in my signature below. 
Basically the system stores metadata containing each view definition and the 
joins between them. When someone needs to alter a view, say to remove a column, 
a DROP CASCADE is performed then each view is recreated in turn *in the correct 
order*. Everything's in a transaction, so a failure of recreation will roll 
back to the original state. This can of course happen if for example a column 
is deleted which other views use. This method is only used if the original 
attempt fails due to dependency errors - some view updates can work just fine 
anyway.

I can point you to the relevant code in GitHub if you're interested (it's Java).

Regards
Oliver Kohll

oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company


On 24 Apr 2010, at 13:01, Scott Bailey  wrote:

> Using views in Postgres can be a painful process. Changing a column in a base 
> table will require you to drop all views that depend on it, and all views 
> that depend on those views and so on.
> 
> My coworker was complaining this morning that he now has a bunch of queries 
> where a view is joined back on the original table to add a column that was 
> missing from the view. It was easier to do this than to drop the view and all 
> of it's dependencies and then find all the source code and rebuild all of the 
> views in the correct order.
> 
> So my thought was to create an invalid objects table to store the source and 
> dependencies (and possibly permissions) when a DDL change invalidates a view 
> or a function. And later you can call a procedure that (tries to) rebuild 
> those invalid objects.
> 
> My initial plan of attack is to just create a function that stores the 
> information required to rebuild the dependencies before dropping them. 
> Something like:
>  store_and_drop('my_view_name')
> 
> I'm thinking that ultimately it would be nice if postgres could do this 
> automatically. Maybe:
>  DROP my_view_name CASCADE WITH RESTORE
> 
> So before I begin, has anyone already done this? And does anyone have any 
> advice as to how it may best be done?
> 
> Thanks
> 
> Scott Bailey

[GENERAL] Lock table, best option?

[Andre Lopes]

Hi,

I need to do a SELECT and an UPDATE, but I will have concurrent processes
doing the same task.

How can I prevent that the concurrent task don't have the same results in
the SELECT? Locking a table? How can I do that?

Best Regards,

[GENERAL] Help me stop postgres from crashing.

[Sam]

Hi,

I am a web developer, I've been using postgesql for a few years but
administratively I am a novice.

A particular web application I am working has a staging version
running one a vps, and a production version running on another vps.
They both get about the same usage, but the production version keeps
crashing and has to be re-started daily for the last couple days.  The
log file at the time of crash looks like this:

LOG:  could not accept new connection: Cannot allocate memory
LOG:  select() failed in postmaster: Cannot allocate memory
FATAL:  semctl(2457615, 0, SETVAL, 0) failed: Invalid argument
LOG:  logger shutting down
LOG:  database system was interrupted at 2010-04-24 09:33:39 PDT

It ran out of memory.

I am looking for a way to track down what is actually causing the
memory shortage and how to prevent it or increase the memory
available.

The vps in question is a media temple DV running CentOS and postgres
8.1.18

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] Help me stop postgres from crashing.

[Thom Brown]

On 24 April 2010 18:48, Sam  wrote:

> Hi,
>
> I am a web developer, I've been using postgesql for a few years but
> administratively I am a novice.
>
> A particular web application I am working has a staging version
> running one a vps, and a production version running on another vps.
> They both get about the same usage, but the production version keeps
> crashing and has to be re-started daily for the last couple days.  The
> log file at the time of crash looks like this:
>
> LOG:  could not accept new connection: Cannot allocate memory
> LOG:  select() failed in postmaster: Cannot allocate memory
> FATAL:  semctl(2457615, 0, SETVAL, 0) failed: Invalid argument
> LOG:  logger shutting down
> LOG:  database system was interrupted at 2010-04-24 09:33:39 PDT
>
> It ran out of memory.
>
> I am looking for a way to track down what is actually causing the
> memory shortage and how to prevent it or increase the memory
> available.
>
> The vps in question is a media temple DV running CentOS and postgres
> 8.1.18
>
>
> Could you provide some more information?  What do you get if you run
"sysctl -a | grep kernel.shm" and "sysctl -a | grep sem"? And what are you
developing in which connects to the database?  Are you using persistent
connections?  And how many connections to you estimate are in use?  What
have you got max_connections and shared_buffers in your postgresql.conf
file?  And how much memory does your VPS have?

Thom

Re: [GENERAL] How to allow PostgreSQL to accept remote connection?

[Fredric Fredricson]

Andre Lopes wrote:

Thanks for the reply's,

How can I configure pg_hba.conf to accept connections from all IP's

What I have in this file is:

#ipv4|host all all 127.0.0.1/32  md5
#ipv6
||host all all ::1/128 md5|

What I need to change?
||According to 
http://www.postgresql.org/docs/8.2/interactive/auth-pg-hba-conf.html you 
should add a line

host all all 0.0.0.0/0 md5
/Fredric
PS. When everything else fails, read the manual.



Best Regards,


On Sat, Apr 24, 2010 at 4:15 PM, Fredric Fredricson 
> wrote:


Raymond O'Donnell wrote:

On 24/04/2010 15:58, Andre Lopes wrote:
 


Hi,

I have a virtual machine with VMWARE, and I need to
connect from outside
the virtual machine to PostgreSQL.

How can I configure the PostgreSQL to accept outside
connections?
   



It should be just the same as a "real" machine put the IP
address of
the VM's network interface in "listen_addresses" in
postgresql.conf.
 


You will probably also have to edit pg_hba.conf file (chaper 20.1
in the manual).
/Fredric

Ray.


 






<>
-- 
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 me stop postgres from crashing.

[Tom Lane]

Sam  writes:
> A particular web application I am working has a staging version
> running one a vps, and a production version running on another vps.
> They both get about the same usage, but the production version keeps
> crashing and has to be re-started daily for the last couple days.  The
> log file at the time of crash looks like this:

> LOG:  could not accept new connection: Cannot allocate memory
> LOG:  select() failed in postmaster: Cannot allocate memory

This looks like a system-level memory shortage.  You might find useful
information in the kernel log.  I'd suggest enabling timestamps in the
PG log (see log_line_prefix) so that you can correlate events in the
two log files.

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] Identical command-line command will not work with \i metacommand and filename

[Bruce Momjian]

John Gage wrote:
> > http://en.wikipedia.org/wiki/Byte_order_mark
> >
> > Tends to get added if you go through a Windows system. Useless for  
> > utf-8 afaik. Confuse the hell out of you because various tools parse  
> > and hide them then you pipe the file to a script and everything  
> > falls over.
> >
> > Bunch of scripts available here to remove them:
> >  http://www.xs4all.nl/~mechiel/projects/bomstrip/
> 
> Correct.  I found the following via Google.
> "I created a file utf8.rb with this content: C:\>ruby -e "p  
> File.read('utf8.rb')" "\357\273\277puts \"Hello World\""
> The "\357\273\277" part is the Byte Order Mark for UTF-8, my editor  
> automatically put it at the beginning of the file, because I saved it  
> as UTF-8."
> At least it isn't some evil virus.  Have to do Mr. WorkAround now.

FYI, this is fixed in Postgres 9.0:

 Ignore leading UTF-8-encoded Unicode byte-order marker in
 psql (Itagaki Takahiro)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.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 compiling function with BEGIN WORK; COMMIT WORK;

[Andre Lopes]

Hi,

I need to use the BEGIN WORK; and COMMIT WORK; to lock a table when I'am
doing a SELECT and UPDATE operation.

The code is not compiling, the error is:

[error]ERROR:  syntax error at or near "work" at character 1
QUERY:  work
CONTEXT:  SQL statement in PL/PgSQL function "apr_apanhar_ownership_email"
near line 7
 [/error]

And the code is:

[code]
CREATE OR REPLACE FUNCTION "public"."apr_apanhar_ownership_email" (ppid
int4, out ppid_email_envio int4) RETURNS integer AS
$body$
DECLARE
pPID alias for $1;
vID_EMAIL_ENVIOint4;

BEGIN

begin work;
lock table atem_emails_envios in access exclusive mode;

select id_email_envio from atem_emails_envios
where dat_sended is null
and (i_started is null or i_started < (current_timestamp - '2
hours'::interval))
and (pid is null or pid = pPID)
order by dat_inserted asc
limit 1
into vID_EMAIL_ENVIO;

update atem_emails_envios
set
i_started = current_timestamp,
pid = pPID
where id_email_envio = vID_EMAIL_ENVIO;
commit work;

ppid_email_envio := vID_EMAIL_ENVIO;

END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
[/code]

What is wrong here? Can someone give me a clue.

Best Regards,

Re: [GENERAL] how to set CACHEDEBUG ?

[Bruce Momjian]

sunpeng wrote:
> I noticed there is a piece of code:
> #ifdef CACHEDEBUG
> #define InitCatCache_DEBUG2 \
> do { \
> elog(...
> } while(0)
> #else
> #define InitCatCache_DEBUG2
> #endif
> 
> Now I'd like to set CACHEDEBUG, how to set up it ? where ?
> thanks

You compile with that flag, like:

cc -DCACHEDEBUG

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.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 compiling function with BEGIN WORK; COMMIT WORK;

[Craig Ringer]

On 25/04/2010 9:07 AM, Andre Lopes wrote:

Hi,

I need to use the BEGIN WORK; and COMMIT WORK; to lock a table when I'am
doing a SELECT and UPDATE operation.


PostgreSQL's server-side functions do *not* support transaction 
management. They're functions that're used inside an existing transaction.


However, if you do not explcitly BEGIN a transaction before calling your 
function, the statement your function runs in will start and stop its 
own transaction. In other words, these two things are equivalent:


BEGIN;
SELECT my_function();
COMMIT;

and

SELECT my_function();

(outside an existing transaction)



Because your function is *always* inside a transaction, it can always 
acquire locks and the like. It doesn't need to explicitly start a 
transaction first.


--
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: [GENERAL] ISP provider with postgres and perl dbi

[John Gage]

Thank you for your reply and the reference.  Excellent.

On Apr 24, 2010, at 5:55 PM, Steve Atkins wrote:



On Apr 24, 2010, at 8:40 AM, John Gage wrote:

I know this is perhaps an inappropriate question (and to some  
extent I am repeating myself), but I now need to get my website up  
and running.


Would anyone be willing to suggest an ISP that offers virtual  
machines running postgres and perl 5.8.8 or above with perl dbi?   
The ideal candidate would offer something reasonably priced with  
some level of support.


If an ISP is offering virtual private servers (where you get full  
access to your own virtual machine) then installing postgresql and  
perl on them will be trivial (just one command on popular linux  
distributions). So if you're wanting to run the database in your VM  
then most anyone offering VPS hosting will have what you need.  
You're unlikely to get perl or postgresql specific support - but  
your ISP isn't where you'd usually look for that.


If you want somewhere that offers both virtual machines and managed  
postgresql hosting that's tougher, but http://www.postgresql.org/support/professional_hosting 
 is probably a good place to start.


Cheers,
 Steve


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



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