Re: [GENERAL] Looking for auto starting procedures
Hello !
With server start, I mean the start of the postgresql engine, not the
machine itself.
I thought about a stored procedure to come into a LISTEN in a loop, but the
loop
must be started anyway [avoid a separate daemon {now, I know, I can use a
pgsql script}].
LISTEN looks great to me [not tried yet]. Thanks for the pointers, I have
yet not
heard about PGQ and it looks like something I missed on Sql Server! Will
study
this!
For logging:I think, I have all rights, but not postgresql configured to
log to syslog.
Much thanks!
br++mabra
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Cédric Villemain
Sent: Saturday, December 04, 2010 3:12 PM
To: ma...@manfbraun.de
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Looking for auto starting procedures
2010/12/2 ma...@manfbraun.de:
Hello !
And thanks to all, for answers.
Naturally, cron does not operate on DB tables and if I add/remove/change
a line in a control table dynamically, cron would not notice that ;-)
So I had to write a daemon, which acts on that table. This might happen
about ~5 - 20 seconds and cron is operates on minutes only.
Perhaps you are trying to solve something with SQL server behavior and
you may find more interesting ways à la PostgreSQL.
I wonder if you are not looking after something like PgQ[1] or LISTEN/NOTIFY
[2]
An auto running stored procedure would solve the problem, if therewould be
a way to run this procedure on server startup automatically
[as it looks, write another daemon for this].
on server start ?! no daemon is necesary.
The usual notification from postgreSQL does not allow to write
an [own, better to evaluate] identifier in the syslog. This is
not a matter of the syslog daemon, its on the program which logs.
May be, I have just not found this.
Maybe you need to adjust your 'roles' [3] so that log lines can be
identified to your role.
Maybe you'll have better answers if you define your objectives.
[1] http://wiki.postgresql.org/wiki/PGQ_Tutorial
[2] http://www.postgresql.org/docs/current/interactive/sql-notify.html
[3] http://www.postgresql.org/docs/current/interactive/sql-createrole.html
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
--
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
Re: [GENERAL] Looking for auto starting procedures
2010/12/2 ma...@manfbraun.de: Hello ! And thanks to all, for answers. Naturally, cron does not operate on DB tables and if I add/remove/change a line in a control table dynamically, cron would not notice that ;-) So I had to write a daemon, which acts on that table. This might happen about ~5 - 20 seconds and cron is operates on minutes only. Perhaps you are trying to solve something with SQL server behavior and you may find more interesting ways à la PostgreSQL. I wonder if you are not looking after something like PgQ[1] or LISTEN/NOTIFY [2] An auto running stored procedure would solve the problem, if therewould be a way to run this procedure on server startup automatically [as it looks, write another daemon for this]. on server start ?! no daemon is necesary. The usual notification from postgreSQL does not allow to write an [own, better to evaluate] identifier in the syslog. This is not a matter of the syslog daemon, its on the program which logs. May be, I have just not found this. Maybe you need to adjust your 'roles' [3] so that log lines can be identified to your role. Maybe you'll have better answers if you define your objectives. [1] http://wiki.postgresql.org/wiki/PGQ_Tutorial [2] http://www.postgresql.org/docs/current/interactive/sql-notify.html [3] http://www.postgresql.org/docs/current/interactive/sql-createrole.html -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- 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] Looking for auto starting procedures
Hello ! Yes, thanks, I am just trying to lern the difference. I am comin from Sql Server, but I am not a experienced db developer. But in Sql Server, you may hack your tests just in a direct statement and if it runs, wrap/put it in the function or procedure. Will need some time ;-) Thanks a lot! br++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alvaro Herrera Sent: Friday, December 03, 2010 1:22 AM To: mabra Cc: pgsql-general Subject: Re: [GENERAL] Looking for auto starting procedures Excerpts from mabra's message of jue dic 02 20:04:36 -0300 2010: I've just not understood, when I have to write a function to test sql code and when I can do it interactively. In Postgres, PL/pgSQL and SQL are two different languages. There are things in PL/pgSQL that you cannot do in pure SQL. RAISE is one of them. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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
Re: [GENERAL] Looking for auto starting procedures
Le 03/12/2010 21:22, manfred.braun a écrit : [...] Yes, thanks, I am just trying to lern the difference. I am comin from Sql Server, but I am not a experienced db developer. But in Sql Server, you may hack your tests just in a direct statement and if it runs, wrap/put it in the function or procedure. You can use the DO command in 9.0 to do just that. -- Guillaume http://www.postgresql.fr http://dalibo.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] Looking for auto starting procedures
On 2010-12-01 16.16, ma...@manfbraun.de wrote: Hello ! I am coming from Sql Server right now and have to learn about the infrastructure. What I missed first, is, to execute procedures regularly/repeatedly on a given time. I want to prevent my to write a lot external programs und use cron :-( cron is the standard way of scheduling reoccurring jobs on *nix systems. It's preferable to having each daemon implement scheduling on its own. The othing thing is, that I need some internally running procedures, which do some work. On Sql Server, I can use auto-start stored procedures. Is there anything like this in postgresql, or what can I do? Is this long running processes, i.e daemons, or is it scheduled processes? For long running I would recommend writing a proper daemon. For scheduled I'd recommend cron or at. And, is it just possible, to put a message to the syslog and with my own identification string? I'm pretty sure you can accomplish this in configuration if you're using syslog-ng. Other syslog alternatives probably has similar possibilities. -- Regards, Robert roppert Gravsjö -- 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] Looking for auto starting procedures
Hello ! And thanks to all, for answers. Naturally, cron does not operate on DB tables and if I add/remove/change a line in a control table dynamically, cron would not notice that ;-) So I had to write a daemon, which acts on that table. This might happen about ~5 - 20 seconds and cron is operates on minutes only. An auto running stored procedure would solve the problem, if therewould be a way to run this procedure on server startup automatically [as it looks, write another daemon for this]. The usual notification from postgreSQL does not allow to write an [own, better to evaluate] identifier in the syslog. This is not a matter of the syslog daemon, its on the program which logs. May be, I have just not found this. Wished, my migration would a bit easier ;-) br++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Robert Gravsjö Sent: Thursday, December 02, 2010 1:24 PM To: ma...@manfbraun.de Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Looking for auto starting procedures On 2010-12-01 16.16, ma...@manfbraun.de wrote: Hello ! I am coming from Sql Server right now and have to learn about the infrastructure. What I missed first, is, to execute procedures regularly/repeatedly on a given time. I want to prevent my to write a lot external programs und use cron :-( cron is the standard way of scheduling reoccurring jobs on *nix systems. It's preferable to having each daemon implement scheduling on its own. The othing thing is, that I need some internally running procedures, which do some work. On Sql Server, I can use auto-start stored procedures. Is there anything like this in postgresql, or what can I do? Is this long running processes, i.e daemons, or is it scheduled processes? For long running I would recommend writing a proper daemon. For scheduled I'd recommend cron or at. And, is it just possible, to put a message to the syslog and with my own identification string? I'm pretty sure you can accomplish this in configuration if you're using syslog-ng. Other syslog alternatives probably has similar possibilities. -- Regards, Robert roppert Gravsjö -- 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
Re: [GENERAL] Looking for auto starting procedures
On Dec 2, 2010, at 11:32 AM, ma...@manfbraun.de ma...@manfbraun.de wrote: The usual notification from postgreSQL does not allow to write an [own, better to evaluate] identifier in the syslog. This is not a matter of the syslog daemon, its on the program which logs. May be, I have just not found this. Am I missing something you require, or would this do it: raise notice 'mynotice: %', some_id; An auto running stored procedure would solve the problem, if therewould be a way to run this procedure on server startup automatically [as it looks, write another daemon for this]. I've actually wanted that as well ;-) But it's not that hard to arrange for your script that starts the PG server to also run some SQL after the server launch. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Looking for auto starting procedures
Hello ! a) The idea with to test a RAISE statement with an prefix, could be good. But I am just that new, I could not make it working. I have not understand, where I can issue direct sql statements and it looks like, the RAISE is not possible with plSql: mbtest=# RAISE NOTICE 'mynotice: %', 'hello'; ERROR: syntax error at or near RAISE LINE 1: RAISE NOTICE 'mynotice: %', 'hello'; b) Yes, good idea to modify the startup script! Regards++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Ribe Sent: Thursday, December 02, 2010 8:16 PM To: ma...@manfbraun.demabra@manfbraun.de Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Looking for auto starting procedures On Dec 2, 2010, at 11:32 AM, ma...@manfbraun.de ma...@manfbraun.de wrote: The usual notification from postgreSQL does not allow to write an [own, better to evaluate] identifier in the syslog. This is not a matter of the syslog daemon, its on the program which logs. May be, I have just not found this. Am I missing something you require, or would this do it: raise notice 'mynotice: %', some_id; An auto running stored procedure would solve the problem, if therewould be a way to run this procedure on server startup automatically [as it looks, write another daemon for this]. I've actually wanted that as well ;-) But it's not that hard to arrange for your script that starts the PG server to also run some SQL after the server launch. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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
Re: [GENERAL] Looking for auto starting procedures
On 12/02/2010 12:27 PM, ma...@manfbraun.de wrote: Hello ! a) The idea with to test a RAISE statement with an prefix, could be good. But I am just that new, I could not make it working. I have not understand, where I can issue direct sql statements and it looks like, the RAISE is not possible with plSql: mbtest=# RAISE NOTICE 'mynotice: %', 'hello'; ERROR: syntax error at or near RAISE LINE 1: RAISE NOTICE 'mynotice: %', 'hello'; It is available in plpgsql: http://www.postgresql.org/docs/9.0/interactive/plpgsql-errors-and-messages.html If you are running 9.0+ you can use DO to run it at the psql prompt: http://www.postgresql.org/docs/9.0/interactive/sql-do.html -- 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] Looking for auto starting procedures
Hello ! Rellay good;I see, the track is not wong ;-) But currently, I stuck in 8.4 [debian/squeeze]. I'll try to write a function and leran if and how I can call it. Thanks a lot! br++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver Sent: Thursday, December 02, 2010 9:35 PM To: ma...@manfbraun.de Cc: @manfbraun.dema...@manfbraun.de'; Subject: Re: [GENERAL] Looking for auto starting procedures On 12/02/2010 12:27 PM, ma...@manfbraun.de wrote: Hello ! a) The idea with to test a RAISE statement with an prefix, could be good. But I am just that new, I could not make it working. I have not understand, where I can issue direct sql statements and it looks like, the RAISE is not possible with plSql: mbtest=# RAISE NOTICE 'mynotice: %', 'hello'; ERROR: syntax error at or near RAISE LINE 1: RAISE NOTICE 'mynotice: %', 'hello'; It is available in plpgsql: http://www.postgresql.org/docs/9.0/interactive/plpgsql-errors-and-messages.h tml If you are running 9.0+ you can use DO to run it at the psql prompt: http://www.postgresql.org/docs/9.0/interactive/sql-do.html -- 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 -- 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] Looking for auto starting procedures
On Dec 2, 2010, at 1:27 PM, ma...@manfbraun.de ma...@manfbraun.de wrote:
I have not understand, where I can issue direct sql statements
and it looks like, the RAISE is not possible with plSql:
Right, it's not actually SQL, so you can't use it in plain SQL. It is part of
the plpgsql procedural language.
So you could easily create a small stored procedure, for example:
create function myraise(msg varchar, id varchar) returns void as $$ begin
raise notice '%: %', msg, id;
end; $$ language plpgsql;
and call that from SQL:
select myraise ('mymsg', '1234');
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
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] Looking for auto starting procedures
Hello !
Very good, thanks !
I've just not understood, when I have to write a function to test sql code
and when I can do it interactively.
Your shown function compiles and works, but I do not have the
result in the logs [altough I see th executing function with
my settings to 'debug' ;-) ].
Will just configure the logging tomorrow, that way, that the
stronger ones are going to the syslog.
br++mabra
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Ribe
Sent: Thursday, December 02, 2010 11:22 PM
To: ma...@manfbraun.demabra@manfbraun.de
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Looking for auto starting procedures
On Dec 2, 2010, at 1:27 PM, ma...@manfbraun.de ma...@manfbraun.de wrote:
I have not understand, where I can issue direct sql statements
and it looks like, the RAISE is not possible with plSql:
Right, it's not actually SQL, so you can't use it in plain SQL. It is part
of the plpgsql procedural language.
So you could easily create a small stored procedure, for example:
create function myraise(msg varchar, id varchar) returns void as $$ begin
raise notice '%: %', msg, id;
end; $$ language plpgsql;
and call that from SQL:
select myraise ('mymsg', '1234');
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
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
Re: [GENERAL] Looking for auto starting procedures
Excerpts from mabra's message of jue dic 02 20:04:36 -0300 2010: I've just not understood, when I have to write a function to test sql code and when I can do it interactively. In Postgres, PL/pgSQL and SQL are two different languages. There are things in PL/pgSQL that you cannot do in pure SQL. RAISE is one of them. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Looking for auto starting procedures
Hello ! I am coming from Sql Server right now and have to learn about the infrastructure. What I missed first, is, to execute procedures regularly/repeatedly on a given time. I want to prevent my to write a lot external programs und use cron :-( The othing thing is, that I need some internally running procedures, which do some work. On Sql Server, I can use auto-start stored procedures. Is there anything like this in postgresql, or what can I do? And, is it just possible, to put a message to the syslog and with my own identification string? Thanks so far! br++mabra -- 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] Looking for auto starting procedures
On 12/01/2010 11:16 PM, ma...@manfbraun.de wrote: The othing thing is, that I need some internally running procedures, which do some work. On Sql Server, I can use auto-start stored procedures. Is there anything like this in postgresql, or what can I do? There is no built-in scheduler at present. Use PgAgent. There's been talk of integrating PgAgent into core, or at least some enhancements to core to let it run without needing an external daemon. And, is it just possible, to put a message to the syslog and with my own identification string? I'm not aware of a syslog module, but it'd be handy to have and it'd be pretty simple to write as a C extension. Usually people use 'RAISE NOTICE' to report to the database's logging. -- 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
