[GENERAL] boolean short-circuiting in plpgsql
Hi everyone,
I may be missing something obvious, but it seems like the advice in
4.2.12 on http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html
doesn't seem to apply in plpgsql.
I have a table that I want to use a trigger on when either a new row
is inserted or at least one of two particular columns is updated.
This fails on insert:
begin
if TG_OP = 'INSERT' or (new.sortnum != old.sortnum or new.parent !=
old.parent) then
perform recalc_sortnumpath(new.id);
end if;
return new;
end;
...because 'old' doesn't exist and the latter argument of the 'or'
gets evaluated despite the TG_OP being 'INSERT'. According to the
docs I should change that line to:
if (select case when TG_OP = 'UPDATE' then (new.sortnum != old.sortnum
or new.parent != old.parent) else 't' end) then
...because the case should force it to only evaluate 'old' when TG_OP
= 'UPDATE' and otherwise ('INSERT') skip through to 't'. But this
causes the same error on insert. I suspect it's because the select
query gets parameterized and at that point the 'old' is missing,
before the case even gets to be parsed. How do I get around this
without having two 'perform' statements? Is there no short-circuit
option in plpgsql?
Thanks,
Kev
--
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] Clone a database to other machine
Hi , Actually I was looking for some method / tool (utility) which keeps both the database on different servers in sync automatically. Looking for some features into postgres.conf file if possible. Thanks and regards, Manjit Garg -Original Message- From: Raymond O'Donnell [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 30, 2008 8:22 PM To: Garg, Manjit Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org Subject: Re: [GENERAL] Clone a database to other machine On 28/07/2008 18:32, Garg, Manjit wrote: But, actually I want to keep both the Databse in Sync. I want clone db to get the data from Master in certain intervals. DB dump size is 3 GB. In that case, a cron job which dumps the data from the master and reloads it on the other machine may be your best bet. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] boolean short-circuiting in plpgsql
Kev [EMAIL PROTECTED] writes:
...because the case should force it to only evaluate 'old' when TG_OP
= 'UPDATE' and otherwise ('INSERT') skip through to 't'. But this
causes the same error on insert. I suspect it's because the select
query gets parameterized and at that point the 'old' is missing,
before the case even gets to be parsed.
Got it in one.
How do I get around this
without having two 'perform' statements?
What you need is two nested IF statements. The PERFORM in your example
is not relevant to the problem.
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] archive_timeout, checkpoint_timeout
On Wed, 30 Jul 2008, Rob Adams wrote: Could someone please explain in layman's terms the implications of using a checkpoint_timeout of ~1min as well? Is it a bad idea? Lowering checkpoint_timeout makes checkpoints more frequent, causing the database to go through WAL segments (at 16MB each) more often. Since those get reused as needed, the peak disk usage footprint of your server shouldn't be any higher. However, churning through that extra disk space and doing the checkpoint bookkeeping so often can cause your server performance to suffer a bit during heavy activity. Make sure to watch what the server looks like under peak load, you may discover that lowering these timeouts so much can cause it to have more trouble keeping up. That's the usual trade-off here; the more often you want to ship useful copies of things to another server, the more processing and particularly disk overhead goes along with that. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] Clone a database to other machine
Garg, Manjit wrote: Hi , Actually I was looking for some method / tool (utility) which keeps both the database on different servers in sync automatically. Looking for some features into postgres.conf file if possible. There's no built in replication for postgres at the moment. Check out slony (http://slony.info/) - it's a master-multiple slave replication system and seems to work pretty well. -- Postgresql php tutorials http://www.designmagick.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] Statistics Data archiving with Postgres
Hello We are developing an application and would like to compute statistics on it in order: - to have a better understanding of what is used mostly in our application to model at best our load test scenarios. - to get information on the usage of the application for other departments. The problem is that our application is currently read mostly while statistics logging is more a log write mostly process. And stats collect will generate a huge volume of data (because a very low granularity is mandatory). We would like to avoid as much as possible any interference of the stats collecting with the main application. We have looked in the Postgres documentation and we have found several ideas: - We have decided to isolate stats in a specific schema. - We have looked at polymorphism in order to split our stat tables in smallest ones that we could detach when they are old. - We have looked at fsync tuning or better at asynchronous commit as these data are not critical. But we have been facing several questions/problems: Polymorphism and ORM question: - First as we are using an ORM tool around PG access, the rule we defined in the Polymorphism returned 0 after an insert because the last rule was generally not the one that made the insert. In our case we know that only a single rule will match, so we made a hack setting the active rule name with a zzz but that is very hacky. in that case anyway Hibernate is happy. One or several databases, one or several servers ? - In such a case could we store both our application content and stats in the same database ? Should we better use two databases in the same cluster or should we even have to different dedicated servers ? - If we want to use fsync, I suppose we need two separated servers. I read that asynchronous commit can be set for a transaction. Is there a way to say that a given cluster or tables are in asynchronous commit by default, perhaps with triggers We would like to archive old data collected in slow file storage in any case but would like to avoid having our database reaching Tb only for data collecting concerns. May be this is a bad idea. Anyway if this is not so bad, we have again questions: With polymorphism we can dump some tables regularly. But polymorphism has been seen a bit complex and we were studying a simpler way to and we also have to study other ways with simpler but larger stats tables. We have studied the simple pg_dump command with only the data but we would need to dump only a part of the table. Thus we have looked at the COPY command which seems interesting in our case. Are there experience or any feedback on that command. Sorry, there are many questions, our problem is a bit wide because there are several concerns: - Polymorphism or not - One or several DB clusters or servers - Fsync/asynchronous problem - Rule limitations - Use of COPY But to sum up we would like to collect statistics (write mostly tables, high volume generation, data not critical) on an application usage on a read mostly DB with the least impact on this DB perfs. ANn we would also like to be able to archive outside the DB, the old collected data. Thanks for any help! Pascal -- 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] Statistics Data archiving with Postgres
Pascal Cohen wrote: But to sum up we would like to collect statistics (write mostly tables, high volume generation, data not critical) on an application usage on a read mostly DB with the least impact on this DB perfs. ANn we would also like to be able to archive outside the DB, the old collected data. [Just throwing ideas around - there's probably a much better and already well established to do all this]: Does the stats data need to be handled within the same transactional scope as the real app data? In other words, do you care if a stats entry is generated for a transaction that then rolls back? If you're OK with that, then another possible area to investigate is the use of IPC messaging to a separate stats collector daemon outside of, and running asynchronously with, PostgreSQL. Many of the PostgreSQL procedural languages can communicate outside the server with mechanisms like UNIX sockets, IP networking, signals, named pipes, and perhaps even things like shared memory if the Pg backend's use of it doesn't interfere. Alternately, you could write your stats collector client as a C add-in to Pg, which would probably let you minimize it's performance cost in exchange for more development and debugging time, plus a higher risk to server stability. Ideally the part of the logging/stats code running inside the Pg backend would do as little work as possible to record the message for later processing. The external stats collector/processor would asynchronously process messages it receives from all Pg backends and record it in your preferred format (perhaps inserting it into a separate write-optimised Pg database on another host). Come to think of it, having stats recorded for transactions that roll back is probably desirable, rather than any sort of downside. -- 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
[GENERAL] Weird pg_ctl behaviour via ssh
Hello, I am fiddling around with pgpool-II and online recovery. Recovery depends on remote starting of a cluster. This means I need to ssh into a box, start clustern (with PITR recovery) and terminate that ssh connection. If I use the following script: ssh -T remote export LD_LIBRARY_PATH=/opt/postgres-8.3.3/lib; nohup /opt/postgres-8.3.3/bin/pg_ctl -w -D /data/pg833-data start /dev/null 21;exit; the script terminates earlier than the DB is up: /opt/postgres-8.3.3/bin/psql -h remote -p postgres psql: FATAL: the database system is starting up which is problem for pgpool. But if I use command: ssh -T remote export LD_LIBRARY_PATH=/opt/postgres-8.3.3/lib; nohup /opt/postgres-8.3.3/bin/pg_ctl -w -D /data/pg833-data start 21;exit; the ssh never terminates. Which is, again problem for pg_pool. The outoput will be as bellow. How can I terminate the script really at the moment when DB is up? Thank you, Bohdan ... .FATAL: the database system is starting up .scp: /data/archive_log/0004.history: No such file or directory could not start server scp: /data/archive_log/0005.history: No such file or directory scp: /data/archive_log/0006.history: No such file or directory scp: /data/archive_log/0007.history: No such file or directory scp: /data/archive_log/0008.history: No such file or directory scp: /data/archive_log/0009.history: No such file or directory scp: /data/archive_log/000A.history: No such file or directory scp: /data/archive_log/000B.history: No such file or directory scp: /data/archive_log/000C.history: No such file or directory scp: /data/archive_log/000D.history: No such file or directory scp: /data/archive_log/000E.history: No such file or directory scp: /data/archive_log/000F.history: No such file or directory scp: /data/archive_log/0010.history: No such file or directory scp: /data/archive_log/0011.history: No such file or directory LOG: selected new timeline ID: 17 scp: /data/archive_log/0001.history: No such file or directory LOG: archive recovery complete LOG: autovacuum launcher started LOG: database system is ready to accept connections -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
Florence Cousin 02 51 78 38 46 [EMAIL PROTECTED] - Ce qui se conçoit bien s'énonce clairement. Et ce qui va sans dire va mieux en le disant. Anonyme (inspiré par Boileau) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rappel :
Cousin Florence souhaite rappeler le message «».
Re: [GENERAL] Weird pg_ctl behaviour via ssh
On Thu, Jul 31, 2008 at 11:24:35AM +0200, Bohdan Linda wrote: /opt/postgres-8.3.3/bin/psql -h remote -p postgres psql: FATAL: the database system is starting up I am attaching additional info. The /dev/null is understable, but what I am worried is that if I query status of a server via: ssh -T remote export LD_LIBRARY_PATH=/opt/postgres-8.3.3/lib; nohup /opt/postgres-8.3.3/bin/pg_ctl -w -D /data/pg833-data status 21 I get: pg_ctl: server is running (PID: 14478) /opt/postgres-8.3.3/bin/postgres -D /data/pg833-data But still psql is returning: psql: FATAL: the database system is starting up Why we have such inconsistency? How to avoid it? Thank you, Bohdan -- 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] Shared object libpq.so.3 not found
On Jul 31, 1:34 am, [EMAIL PROTECTED] (Tom Lane) wrote: marko [EMAIL PROTECTED] writes: I'm running FreeBSD 6.2 and I have Postgresql 8.2.4 installed with Perl v5.8.8. I'm trying to test DBD-Pg-2.8.7 after compilation and I get this error after 'make test': # Error: Can't load '/usr/home/markuser/DBD-Pg-2.8.7/blib/arch/ auto/DBD/Pg/Pg.so' for module DBD::Pg: Shared object libpq.so.3 not found, required by Pg.so at /usr/local/lib/perl5/5.8.8/mach/ DynaLoader.pm line 230. libpq.so.3 corresponds to the libpq version that was shipped in PG release series 7.3.x and 7.4.x. 8.2 provides libpq.so.5. Your subsequent comments make it pretty clear that you've got (at least portions of) both 7.x and 8.x PG installations on your machine. I'd suggest flushing all traces of the older one and then rebuilding DBD::Pg from a clean start. Somehow it's been seizing on the older PG installation as the one to link to... regards, tom lane OK... I can't say that I remember installing a 7.x PG version. I wanted to make sure there wasn't some FreeBSD bug out there relative to these packages. 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] Shared object libpq.so.3 not found
On Jul 31, 1:34 am, [EMAIL PROTECTED] (Tom Lane) wrote: marko [EMAIL PROTECTED] writes: I'm running FreeBSD 6.2 and I have Postgresql 8.2.4 installed with Perl v5.8.8. I'm trying to test DBD-Pg-2.8.7 after compilation and I get this error after 'make test': # Error: Can't load '/usr/home/markuser/DBD-Pg-2.8.7/blib/arch/ auto/DBD/Pg/Pg.so' for module DBD::Pg: Shared object libpq.so.3 not found, required by Pg.so at /usr/local/lib/perl5/5.8.8/mach/ DynaLoader.pm line 230. libpq.so.3 corresponds to the libpq version that was shipped in PG release series 7.3.x and 7.4.x. 8.2 provides libpq.so.5. Your subsequent comments make it pretty clear that you've got (at least portions of) both 7.x and 8.x PG installations on your machine. I'd suggest flushing all traces of the older one and then rebuilding DBD::Pg from a clean start. Somehow it's been seizing on the older PG installation as the one to link to... regards, tom lane -- Sent via pgsql-general mailing list ([EMAIL PROTECTED]) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general locate libpq.so returned: /usr/home/markuser/postgresql-8.2.4/src/interfaces/libpq/libpq.so /usr/home/markuser/postgresql-8.2.4/src/interfaces/libpq/libpq.so.5 /usr/local/lib/libpq.so /usr/local/lib/libpq.so.3 /usr/local/pgsql/lib/libpq.so /usr/local/pgsql/lib/libpq.so.5 I simply deleted /usr/local/lib/libpq.so /usr/local/lib/libpq.so.3 and now after building DBD::Pg and testing it, I get: PGINITDB=/usr/local/pgsql/bin/initdb PERL_DL_NONLAZY=1 /usr/bin/perl -MExtUtils::Command::MM -e test_harness(0, 'blib/lib', 'blib/ arch') t/*.t t/00-signature..skipped all skipped: Set the environment variable TEST_SIGNATURE to enable this test t/00basic...ok 1/3 # Failed test 'use DBD::Pg;' t/00basic...NOK 2# in t/00basic.t at line 14. # Tried to use 'DBD::Pg'. # Error: Can't load '/usr/home/markuser/DBD-Pg-2.8.7/blib/arch/ auto/DBD/Pg/Pg.so' for module DBD::Pg: /usr/home/markuser/DBD-Pg-2.8.7/ blib/arch/auto/DBD/Pg/Pg.so: Undefined symbol BIO_new_mem_buf at / usr/local/lib/perl5/5.8.8/mach/DynaLoader.pm line 230. # at (eval 8) line 2 # Compilation failed in require at (eval 8) line 2. # BEGIN failed--compilation aborted at t/00basic.t line 14. FAILED--Further testing stopped: Cannot continue without DBD::Pg *** Error code 2 -- Does anyone know which library I'm missing or how I can find that out? Googling 'Undefined symbol BIO_new_mem_buf' leads to rather ambiguous results... -- 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] interesting trigger behaviour in 8.3
On Tue, Jul 29, 2008 at 7:52 PM, Tom Lane [EMAIL PROTECTED] wrote: Ivan Zolotukhin [EMAIL PROTECTED] writes: In pseudo code it looks like the following. There are 2 tables, empty abstract_table with 3 columns (id, col1, col2) and many tables (e.g. inherited_table1_with_data) that inherit abstract_table. Constraint_exclusion is set up on id column and works perfectly. So we've got update like this UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345; I bet it does not *really* look like that, but has a parameterized WHERE clause. As per the fine manual: Constraint exclusion only works when the query's WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know which partitions the parameter value might select at run time. For the same reason, stable functions such as CURRENT_DATE must be avoided. Thank you Tom for your remark. I just missed this point from the docs. -- Regards, Ivan -- 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] Shared object libpq.so.3 not found
locate libpq.so returned: /usr/home/markuser/postgresql-8.2.4/src/interfaces/libpq/libpq.so /usr/home/markuser/postgresql-8.2.4/src/interfaces/libpq/libpq.so.5 /usr/local/lib/libpq.so /usr/local/lib/libpq.so.3 /usr/local/pgsql/lib/libpq.so /usr/local/pgsql/lib/libpq.so.5 from the location of the source it seems you aren't building from ports or using the builtin package system. Been using postgres on FreeBSD and updating from source from 2001 or so without a single glitch. As FreeBSD has one of the best infrastructures for building interlinked binaries from source, I seriously suggest using it instead of trying to reinvent the wheel ;) -Reko -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How do I specify intervals in functions?
Greetings! In the help file under date and time functions, I see that intervals can be specified as interval '3 hours' . In a PgAdmin SQL window, I can enter select interval '3 hours' , and it will return me 03:00:00, as expected. I can also enter select '3 hours'::interval, and get the same result. Yet neither syntax works inside a function. declare ThreeHours interval; begin ThreeHours = interval '3 hours'; -- throws a syntax error ThreeHours = '3 hours'::interval; -- also throws a syntax error end; So how do I specify an interval in a function? Specifically, I'm trying to do something like the following: if NewRevisionTime PredictedEndTime - '08:00:00'::interval then Since both of the shown forms give syntax errors, how do I subtract eight hours from a time??? Thank you very much. RobR, who posted this on the novice list but got no answers.
Re: [GENERAL] How do I specify intervals in functions?
On 2008-07-31, at 8:36 AM, Rob Richardson wrote: declare ThreeHours interval; begin ThreeHours = interval '3 hours'; -- throws a syntax error ThreeHours = '3 hours'::interval; -- also throws a syntax error end; So how do I specify an interval in a function? Works for me: CREATE FUNCTION three_hours() RETURNS interval STABLE STRICT LANGUAGE plpgsql AS $body$ declare ThreeHours interval; begin ThreeHours = interval '3 hours'; -- throws a syntax error ThreeHours = '3 hours'::interval; -- also throws a syntax error RETURN ThreeHours; end $body$; CREATE FUNCTION test=# select three_hours(); three_hours - 03:00:00 (1 row) test=# select version(); version - PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2) (1 row) I can't tell as you haven't provided a complete example (always helpful when debugging), but are you sure you're specifying the correct language type (plpgsql in your case)? Michael Glaesemann [EMAIL PROTECTED] -- 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 do I specify intervals in functions?
Rob Richardson [EMAIL PROTECTED] writes:
So how do I specify an interval in a function?
Does this help?
([EMAIL PROTECTED]:5432/test) [2008-07-31 15:49:54]
# CREATE OR REPLACE FUNCTION time_cmp_with_1w_offset
(_l_ts timestamp, _r_ts timestamp)
RETURNS boolean AS $$
DECLARE
_offset interval;
BEGIN
_offset = '1 week';
RETURN (_l_ts _r_ts - _offset);
END;
$$ LANGUAGE plpgsql;
([EMAIL PROTECTED]:5432/test) [2008-07-31 15:49:28]
# SELECT time_cmp_with_1w_offset(CAST('2008-07-10' AS timestamp),
]CAST('2008-07-31' AS timestamp));
time_cmp_with_1w_offset
-
t
(1 row)
Regards.
--
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 do I specify intervals in functions?
My thanks to all for the quick replies. Now I can't get it not to work! I guess the computer gremlins that all of us are paid to deny they exist were playing games with me. RobR
Re: [GENERAL] boolean short-circuiting in plpgsql
Kev [EMAIL PROTECTED] writes:
...because the case should force it to only evaluate 'old' when
TG_OP
= 'UPDATE' and otherwise ('INSERT') skip through to 't'. But this
causes the same error on insert. I suspect it's because the select
query gets parameterized and at that point the 'old' is missing,
before the case even gets to be parsed.
Got it in one.
Thanks. Shouldn't there be some way around this then?
How do I get around this
without having two 'perform' statements?
What you need is two nested IF statements. The PERFORM in your
example
is not relevant to the problem.
regards, tom lane
Well, sure, in one sense, but I am actually trying to make it look
neater. Unless I'm missing something (quite possible...) the two
nested IF statements end up having two PERFORM statements:
if TG_OP = 'INSERT' then
perform recalc_sortnumpath(new.id);
else
if (new.sortnum != old.sortnum or new.parent != old.parent) then
perform recalc_sortnumpath(new.id);
end if;
end if;
...is there some way to boil this down using nested IF statements that
only has one PERFORM? (I mean, besides inverting it and having three
return statements and one perform.)
Thanks,
Kev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why handling of input arrays in plperl is unusable?
I tried to write a variadic function with pl/perl, but apparently arrays are passed to pl/perl in their textout format. Parsing it in pl/perl is possible but *very* cumbersome. Is there any plan to add some sanity to passing arrays to plperl function? I can already return [1,2,3], so maybe such transition on input wouldn't be impossible? Best regards, depesz -- Linked in: http://www.linkedin.com/in/depesz jid/gtalk: [EMAIL PROTECTED] aim: depeszhdl skype: depesz_hdl -- 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 do I specify intervals in functions?
I found my problem. I trimmed down the function I was having trouble with to the following: CREATE OR REPLACE FUNCTION recalculate_heating_time(int4) RETURNS int4 AS $BODY$ declare ChargeNum ALIAS for $1; ChargeReccharge%rowtype; HeatingTime int4; IntervalMinutes float4; NewRevisionTime timestamp; PredictedEndTime timestamp; Interval interval; PredictedSpan interval; Message varchar; EightHours interval; begin Message = '07:00:00'::varchar; EightHours = '08:00:00'::interval; return 1; end; This gave me the following error message: ERROR: syntax error at or near $1 at character 22 QUERY: SELECT '08:00:00':: $1 CONTEXT: SQL statement in PL/PgSQL function recalculate_heating_time near line 15 I stripped out all the declarations before Message, and the function loaded successfully. I'm primarily a C++/C# developer, and in those languages, there's no problem differentiating between Interval and interval. In the Postgres SQL dialect (and probably in all other SQL variants), the two words are treated identically. The line where I declared a variable named Interval of type interval screwed everything up. RobR Using PostGreSQL 8.1 under Windows XP Pro From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Richardson Sent: Thursday, July 31, 2008 8:37 AM To: pgsql-general@postgresql.org Subject: [GENERAL] How do I specify intervals in functions? Greetings! In the help file under date and time functions, I see that intervals can be specified as interval '3 hours' . In a PgAdmin SQL window, I can enter select interval '3 hours' , and it will return me 03:00:00, as expected. I can also enter select '3 hours'::interval, and get the same result. Yet neither syntax works inside a function. declare ThreeHours interval; begin ThreeHours = interval '3 hours'; -- throws a syntax error ThreeHours = '3 hours'::interval; -- also throws a syntax error end; So how do I specify an interval in a function? Specifically, I'm trying to do something like the following: if NewRevisionTime PredictedEndTime - '08:00:00'::interval then Since both of the shown forms give syntax errors, how do I subtract eight hours from a time??? Thank you very much. RobR, who posted this on the novice list but got no answers.
Re: [GENERAL] How do I specify intervals in functions?
One thing I left out of my last post: Thanks to all of you for your assitance. RobR
Re: [GENERAL] Shared object libpq.so.3 not found
marko [EMAIL PROTECTED] writes: # Error: Can't load '/usr/home/markuser/DBD-Pg-2.8.7/blib/arch/ auto/DBD/Pg/Pg.so' for module DBD::Pg: /usr/home/markuser/DBD-Pg-2.8.7/ blib/arch/auto/DBD/Pg/Pg.so: Undefined symbol BIO_new_mem_buf at / usr/local/lib/perl5/5.8.8/mach/DynaLoader.pm line 230. BIO_new_mem_buf is an OpenSSL function, so apparently you've got a problem with linking to libssl.so. Not too familiar with how FreeBSD handles this, but maybe you failed to teach the dynamic linker where libssl is? 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] archive_timeout, checkpoint_timeout
Greg Smith wrote: On Wed, 30 Jul 2008, Rob Adams wrote: Could someone please explain in layman's terms the implications of using a checkpoint_timeout of ~1min as well? Is it a bad idea? Lowering checkpoint_timeout makes checkpoints more frequent, causing the database to go through WAL segments (at 16MB each) more often. There's something not being told here, which is the effect that full page writes have on WAL traffic. The more frequent checkpoints are, more I/O traffic you have caused by those. If WAL output gets high, it could mean _more_ segments being created due to a checkpoint not having time to finish while new WAL space needs to be used for concurrent operation. Remember we have to keep all segments since the previous-to-last checkpoint. Since those get reused as needed, the peak disk usage footprint of your server shouldn't be any higher. However, churning through that extra disk space and doing the checkpoint bookkeeping so often can cause your server performance to suffer a bit during heavy activity. Make sure to watch what the server looks like under peak load, you may discover that lowering these timeouts so much can cause it to have more trouble keeping up. That's the usual trade-off here; the more often you want to ship useful copies of things to another server, the more processing and particularly disk overhead goes along with that. If you just want to ship segments to a standby server on a timely basis, the setting to tune should be archive_timeout, no? -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] Doing an LDAP lookup from a SQL SELECT
I have an LDAP directory that contains contact information and a PostgreSQL table that has contact names. What I want to do is write a SELECT that will join the names in the table with the data in the LDAP directory (such as phone number, e-mail address, etc). Is this possible? Has anyone done this before? TNX in advance. David -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 -- 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] Doing an LDAP lookup from a SQL SELECT
David R Robison wrote: I have an LDAP directory that contains contact information and a PostgreSQL table that has contact names. What I want to do is write a SELECT that will join the names in the table with the data in the LDAP directory (such as phone number, e-mail address, etc). Is this possible? Has anyone done this before? Take a look at dblink-ldap, available on pgFoundry. It's not exactly polished :-), but it works for a lot of cases. I've used it for doing exactly what you're trying to do here. //Magnus -- 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] archive_timeout, checkpoint_timeout
Hello, If you just want to ship segments to a standby server on a timely basis, the setting to tune should be archive_timeout, no? just curious, how would the stand-by DB process the segments? Regards, Bohdan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Copy fails
I am loading a huge file using C, STDIN The program fails immediately on canceling statement due to statement timeout Any idea? Thanks Danny -- 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] archive_timeout, checkpoint_timeout
Bohdan Linda wrote: Hello, If you just want to ship segments to a standby server on a timely basis, the setting to tune should be archive_timeout, no? just curious, how would the stand-by DB process the segments? You mean this? http://www.postgresql.org/docs/8.3/static/pgstandby.html -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] Cursor Error
Could somebody translate this error message for me?? Bob cursor unnamed portal 1 is not simply updateable scan of table p_id -- 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] Cursor Error
Bob Pawley [EMAIL PROTECTED] writes: Could somebody translate this error message for me?? cursor unnamed portal 1 is not simply updateable scan of table p_id You're trying to do an UPDATE WHERE CURRENT OF cursor, right? What it means is that the cursor definition is too complicated for Postgres to figure out which row to update. Without seeing the cursor definition it's hard to say more. 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] Cursor Error
Right. This is the cursor statement. Open procgraphic for select p_id.p_id.process_id from p_id.p_id, processes_count where p_id.p_id.p_id_id = processes_count.p_id_id; If process_total = 1 Then Fetch first from procgraphic into process_id; Update p_id.p_id set proc_graphic_position = '1' where current of procgraphic; I get the same error message when I define the cursor with the same select. I am not sure how to make the query simpler and still have it access the right row on fetch. Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: PostgreSQL pgsql-general@postgresql.org Sent: Thursday, July 31, 2008 9:30 AM Subject: Re: [GENERAL] Cursor Error Bob Pawley [EMAIL PROTECTED] writes: Could somebody translate this error message for me?? cursor unnamed portal 1 is not simply updateable scan of table p_id You're trying to do an UPDATE WHERE CURRENT OF cursor, right? What it means is that the cursor definition is too complicated for Postgres to figure out which row to update. Without seeing the cursor definition it's hard to say more. 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] Cursor Error
Bob Pawley [EMAIL PROTECTED] writes: Right. This is the cursor statement. Open procgraphic for select p_id.p_id.process_id from p_id.p_id, processes_count where p_id.p_id.p_id_id = processes_count.p_id_id; Sorry, we're not bright enough to handle WHERE CURRENT OF on a join --- per the fine manual, The cursor must be a simple (non-join, non-aggregate) query on the UPDATE's target table. I don't recall offhand whether there's some deep technical reason for the restriction against joins, or we just didn't get around to it. In any case, you'll need to change the cursor to return the table's primary key and use that to target the UPDATE. 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] Doing an LDAP lookup from a SQL SELECT
Thanks, I found the extension but no documentation on how to install it or use it. Can you give me some pointers? TNX David Magnus Hagander wrote: David R Robison wrote: I have an LDAP directory that contains contact information and a PostgreSQL table that has contact names. What I want to do is write a SELECT that will join the names in the table with the data in the LDAP directory (such as phone number, e-mail address, etc). Is this possible? Has anyone done this before? Take a look at dblink-ldap, available on pgFoundry. It's not exactly polished :-), but it works for a lot of cases. I've used it for doing exactly what you're trying to do here. //Magnus -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately.
[GENERAL] Partitioned tables and views
I have several tables that we have partitioned by physical location. This seems to give us the best overall performance when doing location specific queries. I have a few questions. 1. Is the planner/optimizer intelligent enough to know when we are not doing a query based on location? For example we might have a trailer that is used by multiple locations and we need to know all of the locations where that trailer has been used. Other queries might look for a specific work order that could only be in one of the partitions. 2. How are views handled with partitioned tables? I don't find anything in the documentation that tells me how views are handled. Depending on the view will it only use the partitioned table or will it use the master table? Best Regards Michael Gould
[GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
My apologies if this is in the docs and I missed it, but is there a PL/pgSQL function equivalent for the pglib function PQtransactionStatus (i.e., a way to find out if we're in an open transaction block, and if that transaction is in an error status)? -- 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] Partitioned tables and views
On 12:54 pm 07/31/08 Mike Gould [EMAIL PROTECTED] wrote: 1. Is the planner/optimizer intelligent enough to know when we are not doing a query based on location? In short yes. If the DB doesn't see the condition by which your tables are partitioned it will search all the partitions. 2. How are views handled with partitioned tables? Same as with regular queries. A view is just a conveniently stored query. In other words, the plan for the view will be the same plan as the plan for the query that you made the view from. -- 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] PL/pgSQL equivalent to PQtransactionStatus?
Christophe wrote: My apologies if this is in the docs and I missed it, but is there a PL/pgSQL function equivalent for the pglib function PQtransactionStatus (i.e., a way to find out if we're in an open transaction block, and if that transaction is in an error status)? A pl/pgsql function *always* executes within a transaction. If an error occurs while that function is executing you can catch the exception (see the Trapping Errors section of the pl/pgsql docs). If an error occurs before the function executes then no other statements will be executed. HTH -- Richard Huxton Archonet Ltd -- 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] Doing an LDAP lookup from a SQL SELECT
I copied the file to the lib directory under the PostgreSQL install and ran the SQL command: CREATE FUNCTION ldap_search(text, text, text, text,text,text) RETURNS SETOF RECORD AS 'libdblink-ldap.dll', 'pg_ldap_search' LANGUAGE C STABLE; it gave me the following error: ERROR: incompatible library C:/Program Files/PostgreSQL/8.2/lib/libdblink-ldap.dll: missing magic block SQL state: XX000 Hint: Extension libraries are required to use the PG_MODULE_MAGIC macro. any thoughts? TNX David David R Robison wrote: Thanks, I found the extension but no documentation on how to install it or use it. Can you give me some pointers? TNX David Magnus Hagander wrote: David R Robison wrote: I have an LDAP directory that contains contact information and a PostgreSQL table that has contact names. What I want to do is write a SELECT that will join the names in the table with the data in the LDAP directory (such as phone number, e-mail address, etc). Is this possible? Has anyone done this before? Take a look at dblink-ldap, available on pgFoundry. It's not exactly polished :-), but it works for a lot of cases. I've used it for doing exactly what you're trying to do here. //Magnus -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately. -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately.
Re: [GENERAL] Cursor Error
Is it allowed to declare a cursor in this manner?? Declare procgraphic cursor for select p_id.p_id.process_id from p_id.p_id, processes_count where p_id.p_id.p_id_id = processes_count.p_id_id; Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: PostgreSQL pgsql-general@postgresql.org Sent: Thursday, July 31, 2008 9:50 AM Subject: Re: [GENERAL] Cursor Error Bob Pawley [EMAIL PROTECTED] writes: Right. This is the cursor statement. Open procgraphic for select p_id.p_id.process_id from p_id.p_id, processes_count where p_id.p_id.p_id_id = processes_count.p_id_id; Sorry, we're not bright enough to handle WHERE CURRENT OF on a join --- per the fine manual, The cursor must be a simple (non-join, non-aggregate) query on the UPDATE's target table. I don't recall offhand whether there's some deep technical reason for the restriction against joins, or we just didn't get around to it. In any case, you'll need to change the cursor to return the table's primary key and use that to target the UPDATE. 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 -- 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] Doing an LDAP lookup from a SQL SELECT
IIRC the binary release that's on there is too old to be supported on recent versions of PostgreSQL. You will need to build it from source, which should be patched with the magic block. //Magnus David R Robison wrote: I copied the file to the lib directory under the PostgreSQL install and ran the SQL command: CREATE FUNCTION ldap_search(text, text, text, text,text,text) RETURNS SETOF RECORD AS 'libdblink-ldap.dll', 'pg_ldap_search' LANGUAGE C STABLE; it gave me the following error: ERROR: incompatible library C:/Program Files/PostgreSQL/8.2/lib/libdblink-ldap.dll: missing magic block SQL state: XX000 Hint: Extension libraries are required to use the PG_MODULE_MAGIC macro. any thoughts? TNX David David R Robison wrote: Thanks, I found the extension but no documentation on how to install it or use it. Can you give me some pointers? TNX David Magnus Hagander wrote: David R Robison wrote: I have an LDAP directory that contains contact information and a PostgreSQL table that has contact names. What I want to do is write a SELECT that will join the names in the table with the data in the LDAP directory (such as phone number, e-mail address, etc). Is this possible? Has anyone done this before? Take a look at dblink-ldap, available on pgFoundry. It's not exactly polished :-), but it works for a lot of cases. I've used it for doing exactly what you're trying to do here. //Magnus -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately. -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately. -- 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] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: A pl/pgsql function *always* executes within a transaction. Indeed so. What I'm looking for is a way of detecting if a transaction block has been opened (i.e., we're within a BEGIN). -- 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] PL/pgSQL equivalent to PQtransactionStatus?
Christophe wrote: On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: A pl/pgsql function *always* executes within a transaction. Indeed so. What I'm looking for is a way of detecting if a transaction block has been opened (i.e., we're within a BEGIN). Why does it matter? -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: Why does it matter? I'm attempting to clean out a connection that is in an unknown state (along the lines of what pgpool does when reusing an open connection). Of course, I could just fire an ABORT down, but it seems nicer to avoid doing so if no transaction block is open. -- 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] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: Why does it matter? Ah, I see, deep confusing on my part regarding PL/pgSQL and tranasctions! Ignore question. :) -- 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] PL/pgSQL equivalent to PQtransactionStatus?
Christophe wrote: On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: A pl/pgsql function *always* executes within a transaction. Indeed so. What I'm looking for is a way of detecting if a transaction block has been opened (i.e., we're within a BEGIN). There is no difference between a transaction explicitly started with BEGIN...COMMIT and one wrapping a single statement. What were you planning to do differently if a BEGIN was issued? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using PostGres general distribution
We currently use SQL Anywhere 9.0.2 as our database in our current product. The main reason is the low maintenance that is required and the installation is a breeze. All we need to do is to ship 3 dll's and a db and log file. I understand that with PostGres that the installation will end up being much more complex, however that doesn't really worry me as much as how much administration of the database is needed. SQL Anywhere has an event processor built in to make doing database backups while the system is online very easy. We also are able to do certain types of maintenance such as selective reorganize of tables, automatically adding additional free space at night so that it doesn't affect performance during processing hours and many other functions. If we had 1500 customers running our system with PostGres and we have little control over the server hardware, the OS the customer would be running the db on, is Postgres the appropriate choice or is this going to be a maintenance nightmare? How self sufficient is Postgres? Best Regards, Michael Gould, Manager Information Technology All Coast Intermodal Services, Inc. First Coast Intermodal Services, Inc. First Coast Logistical Services, LLC. 904-226-0978
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
Christophe wrote: On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: Why does it matter? I'm attempting to clean out a connection that is in an unknown state (along the lines of what pgpool does when reusing an open connection). Of course, I could just fire an ABORT down, but it seems nicer to avoid doing so if no transaction block is open. Maybe DISCARD ALL does what you want? http://www.postgresql.org/docs/8.3/static/sql-discard.html -- 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] How do I set up automatic backups?
I created this script sometime ago and it works fine for me and others.. Maybe it might work for you http://www.zeroaccess.org/postgresql-backup 1.0RC1 is pretty stable I have ran it for 3 weeks without any problems Quoting Rob Richardson [EMAIL PROTECTED]: Greetings again! A few days ago, I visited a customer's site to talk about administering our system, which is developed around a PostGres database. One of the topics was how to back up the database. I described the process of using PgAdmin to back up and restore a database, and I said a backup should be done every night. I was asked how to automate the procedure, and I couldn't answer. A database administrator said, There's got to be a way. Otherwise, PostGres wouldn't have survived. I agree with him. The only answers I've found on the Internet involve creating a password-less account and using that to run pg_dump. What is the official best way to automatically back up a PostGres database? Thank you very much. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general This message was sent using IMP, the Internet Messaging Program. -- 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] Doing an LDAP lookup from a SQL SELECT
I cannot seem to create an environment to recompile the DLL. Does anyone have a compiled version of the DLL that is compatible with PostgreSQL 8.2 that they can share with me? TNX David Magnus Hagander wrote: IIRC the binary release that's on there is too old to be supported on recent versions of PostgreSQL. You will need to build it from source, which should be patched with the magic block. //Magnus David R Robison wrote: I copied the file to the lib directory under the PostgreSQL install and ran the SQL command: CREATE FUNCTION ldap_search(text, text, text, text,text,text) RETURNS SETOF RECORD AS 'libdblink-ldap.dll', 'pg_ldap_search' LANGUAGE C STABLE; it gave me the following error: ERROR: incompatible library C:/Program Files/PostgreSQL/8.2/lib/libdblink-ldap.dll: missing magic block SQL state: XX000 Hint: Extension libraries are required to use the PG_MODULE_MAGIC macro. any thoughts? TNX David David R Robison wrote: Thanks, I found the extension but no documentation on how to install it or use it. Can you give me some pointers? TNX David Magnus Hagander wrote: David R Robison wrote: I have an LDAP directory that contains contact information and a PostgreSQL table that has contact names. What I want to do is write a SELECT that will join the names in the table with the data in the LDAP directory (such as phone number, e-mail address, etc). Is this possible? Has anyone done this before? Take a look at dblink-ldap, available on pgFoundry. It's not exactly polished :-), but it works for a lot of cases. I've used it for doing exactly what you're trying to do here. //Magnus -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately. -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately. -- David R Robison Open Roads Consulting, Inc. 708 S. Battlefield Blvd., Chesapeake, VA 23322 phone: (757) 546-3401 e-mail: [EMAIL PROTECTED] web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/book_detail.php?id=2579 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately.
[GENERAL] CAST(integer_field AS character) truncates trailing zeros
I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? -- Thanks, Warren Bell -- 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] CAST(integer_field AS character) truncates trailing zeros
On Thu, Jul 31, 2008 at 4:03 PM, Warren Bell [EMAIL PROTECTED] wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? Please supply the exact syntax that you're using to do the cast. -Doug -- 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] CAST(integer_field AS character) truncates trailing zeros
On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell [EMAIL PROTECTED] wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? Here is what I get when I try: postgres=# select cast( cast( 1000 as integer ) as char ); bpchar 1 (1 row) postgres=# select cast( cast( 1000 as integer ) as char(10) ); bpchar 1000 (1 row) -- 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] CAST(integer_field AS character) truncates trailing zeros
--- On Thu, 7/31/08, Warren Bell [EMAIL PROTECTED] wrote:
From: Warren Bell [EMAIL PROTECTED]
Subject: [GENERAL] CAST(integer_field AS character) truncates trailing zeros
To: pgsql-general@postgresql.org
Date: Thursday, July 31, 2008, 8:03 PM
I am trying to cast an int to a character. The int is the
number 1000 it
gets cast down to 1 and not 1000.
How do I cast from int to
character without loosing the trailing zeros?
--
Thanks,
Warren Bell
work to me
template1=# select cast('1000' as varchar);
varchar
-
1000
(1 fila)
template1=#
--
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] CAST(integer_field AS character) truncates trailing zeros
On Thu, Jul 31, 2008 at 4:17 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell [EMAIL PROTECTED] wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? Here is what I get when I try: postgres=# select cast( cast( 1000 as integer ) as char ); bpchar 1 (1 row) AFAIK, CHAR means CHAR(1), so there are not enough characters to contain the result and it gets truncated. Maybe TEXT should be used instead, or VARCHAR? -Doug -- 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] CAST(integer_field AS character) truncates trailing zeros
I guess it would help if I cast to the correct type. I was doing cast( cast( 1000 as integer ) as char ) instead of character varying, char(n) or text. Thanks, Warren Warren Bell wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? -- Thanks, Warren Bell 909-645-8864 [EMAIL PROTECTED] -- 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] Using PostGres general distribution
On Thursday 31 July 2008 15:28:14 Mike Gould wrote: We currently use SQL Anywhere 9.0.2 as our database in our current product. The main reason is the low maintenance that is required and the installation is a breeze. All we need to do is to ship 3 dll's and a db and log file. I understand that with PostGres that the installation will end up being much more complex, however that doesn't really worry me as much as how much administration of the database is needed. IIRC, SQL Anywhere is designed to work as an embedded database, with particular points towards zero administrative overhead. That's not the design goal of PostgreSQL, though it can (and is) used for the underlying piece of many products. SQL Anywhere has an event processor built in to make doing database backups while the system is online very easy. you can do backups of postgres under normal operations using pg_dump. you'll need to craft your own scheduler for this, as postgres doesn't include one. We also are able to do certain types of maintenance such as selective reorganize of tables, automatically adding additional free space at night so that it doesn't affect performance during processing hours and many other functions. Most DML operations in postgres can be run inside a transaction, and postgres is not in the habit of creating artificial constraints for index/tablespace size as some other databases. The main key for you will probably be to turn the autovacuum daemon on, which will do most of the cleanup work you would need to have done regularly automagically. If we had 1500 customers running our system with PostGres and we have little control over the server hardware, the OS the customer would be running the db on, is Postgres the appropriate choice or is this going to be a maintenance nightmare? How self sufficient is Postgres? I know postgres is used in many kiosk type systems, where there is little to no ongoing maintenance for those machines which sound similar to what you might be looking at. One of the clients my company works with is doing something similar to this, distributing a postgres backed application designed to be run with little/no postgres maintenance overhead. It's more like a data appliance than an embedded app (it deals with 100's GB of data), but seems so far has been very doable. Most of the trouble scenarios that are involved are when you have no control over usage patterns... Ie. someone has direct access to the database, and they do massive dumps and reloads of data you haven't designed into your original expectations; but if you have controlled access to the server, it's likely postgres can work in that scenario. (The other problem spots is server upgrades, but you can probably go years on a particular version before that becomes really problematic, it just depends on what your applications lifecycle looks like) -- Robert Treat Database Architect http://www.omniti.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] eliminating records not in (select id ... so SLOW?
I'm doing something like: delete from table1 where id not in (select id from table2). both id are indexed. table1 contains ~1M record table2 contains ~ 600K record and id is unique. The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram. Default debian etch setup. It has been working for over 2h now. Is it normal? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] eliminating records not in (select id ... so SLOW?
--- On Thu, 7/31/08, Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: From: Ivan Sergio Borgonovo [EMAIL PROTECTED] Subject: [GENERAL] eliminating records not in (select id ... so SLOW? To: PostgreSQL pgsql-general@postgresql.org Date: Thursday, July 31, 2008, 9:45 PM I'm doing something like: delete from table1 where id not in (select id from table2). both id are indexed. table1 contains ~1M record table2 contains ~ 600K record and id is unique. The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram. Default debian etch setup. It has been working for over 2h now. Is it normal? -- Ivan Sergio Borgonovo http://www.webthatworks.it you recently run vacuum ? -- 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
[GENERAL] hibernate nativequery and uuid
I'm currently using JPA with Hibernate as my ORM and have been able to convince hibernate to play nicely with the Postgresql UUID. Most of my queries have been in EJBQL using the JPA entity manager's createQuery. However when I try to do a UNION, JPA only returned the results of the first query, and ignored the other UNION queries, which is in line with what I have read online, in that JPA does not support UNIONS. So I'm currently attempting to go via a createNativeQuery call. The keys on the target tables are PostgreSQL UUID data types. When doing so I get the following error: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying The only relevant thing I have been able to find relating to it is http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which suggests adding a ::uuid cast to the parameter. However, when doing that, hibernate thinks that it is a named parameter and complains. org.hibernate.QueryException: Not all named parameters have been set Hibernate's functionality to declare an escape character for a LIKE clause does not apply to this. But in attempting to escape the colons, getting various other hibernate parsing errors, so my attempts down this path has not been of help. Has anyone else run into this issue and been able to resolve it? I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the Postgresql 8.3-603 JDBC4 driver. Thanks, 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] hibernate nativequery and uuid
On Thu, Jul 31, 2008 at 5:57 PM, Andrew [EMAIL PROTECTED] wrote: The only relevant thing I have been able to find relating to it is http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which suggests adding a ::uuid cast to the parameter. However, when doing that, hibernate thinks that it is a named parameter and complains. org.hibernate.QueryException: Not all named parameters have been set Have you tried using the alternative (and more standard) CAST syntax instead? Perhaps that won't confuse Hibernate. -Doug -- 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] eliminating records not in (select id ... so SLOW?
On Thu, 31 Jul 2008 14:59:29 -0700 (PDT) Lennin Caro [EMAIL PROTECTED] wrote: The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram. Default debian etch setup. you recently run vacuum ? The tables are pretty stable. I think no more than 20 records were modified (update/insert/delete) during the whole history of the 2 tables. autovacuum is running regularly. The actual query running is: begin; create index catalog_categoryitem_ItemsID_index on catalog_categoryitem using btree (ItemID); delete from catalog_categoryitem where ItemID not in (select ItemID from catalog_items); commit; That's what came back Timing is on. BEGIN Time: 0.198 ms CREATE INDEX Time: 3987.991 ms The query is still running... As a reminder catalog_categoryitem should contain less than 1M record. catalog_items should contain a bit more than 600K record where ItemID is unique (a pk actually). PostgreSQL comes from the default install from Debian etch (8.1.X). It's configuration hasn't been modified. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] hibernate nativequery and uuid
Yeah, tried that, but get the following: org.hibernate.MappingException: No Dialect mapping for JDBC type: Thanks for the suggestion though. Douglas McNaught wrote: On Thu, Jul 31, 2008 at 5:57 PM, Andrew [EMAIL PROTECTED] wrote: The only relevant thing I have been able to find relating to it is http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which suggests adding a ::uuid cast to the parameter. However, when doing that, hibernate thinks that it is a named parameter and complains. org.hibernate.QueryException: Not all named parameters have been set Have you tried using the alternative (and more standard) CAST syntax instead? Perhaps that won't confuse Hibernate. -Doug
Re: [GENERAL] hibernate nativequery and uuid
I have given it consideration, but haven't tried it as I have concluded that I would still have the same issue. The problem occurs with the client attempting to apply a UUID value to filter the result set. So I would still have a datatype mismatch if I were to use a view. I am confident that I can get it to work if I were to remove Hibernate and make a direct JDBC call, as I have tested that approach previously. That is a fall back position. But I would like if I can to keep to a single approach with my DB access for this application if I can. The suggestion was appreciated though. A.M. wrote: On Jul 31, 2008, at 5:57 PM, Andrew wrote: I'm currently using JPA with Hibernate as my ORM and have been able to convince hibernate to play nicely with the Postgresql UUID. Most of my queries have been in EJBQL using the JPA entity manager's createQuery. However when I try to do a UNION, JPA only returned the results of the first query, and ignored the other UNION queries, which is in line with what I have read online, in that JPA does not support UNIONS. So I'm currently attempting to go via a createNativeQuery call. The keys on the target tables are PostgreSQL UUID data types. When doing so I get the following error: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying The only relevant thing I have been able to find relating to it is http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which suggests adding a ::uuid cast to the parameter. However, when doing that, hibernate thinks that it is a named parameter and complains. org.hibernate.QueryException: Not all named parameters have been set Hibernate's functionality to declare an escape character for a LIKE clause does not apply to this. But in attempting to escape the colons, getting various other hibernate parsing errors, so my attempts down this path has not been of help. Has anyone else run into this issue and been able to resolve it? I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the Postgresql 8.3-603 JDBC4 driver. Could you use a view to hide the UNION? Cheers, M No virus found in this incoming message. Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database: 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM -- 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] hibernate nativequery and uuid
Oh, I see what you mean. Use EJBQL on a view. That would probably work. Have to get going, so will try that when I get back in several hours. I'll let you know how I go. Andrew wrote: I have given it consideration, but haven't tried it as I have concluded that I would still have the same issue. The problem occurs with the client attempting to apply a UUID value to filter the result set. So I would still have a datatype mismatch if I were to use a view. I am confident that I can get it to work if I were to remove Hibernate and make a direct JDBC call, as I have tested that approach previously. That is a fall back position. But I would like if I can to keep to a single approach with my DB access for this application if I can. The suggestion was appreciated though. A.M. wrote: On Jul 31, 2008, at 5:57 PM, Andrew wrote: I'm currently using JPA with Hibernate as my ORM and have been able to convince hibernate to play nicely with the Postgresql UUID. Most of my queries have been in EJBQL using the JPA entity manager's createQuery. However when I try to do a UNION, JPA only returned the results of the first query, and ignored the other UNION queries, which is in line with what I have read online, in that JPA does not support UNIONS. So I'm currently attempting to go via a createNativeQuery call. The keys on the target tables are PostgreSQL UUID data types. When doing so I get the following error: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying The only relevant thing I have been able to find relating to it is http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which suggests adding a ::uuid cast to the parameter. However, when doing that, hibernate thinks that it is a named parameter and complains. org.hibernate.QueryException: Not all named parameters have been set Hibernate's functionality to declare an escape character for a LIKE clause does not apply to this. But in attempting to escape the colons, getting various other hibernate parsing errors, so my attempts down this path has not been of help. Has anyone else run into this issue and been able to resolve it? I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the Postgresql 8.3-603 JDBC4 driver. Could you use a view to hide the UNION? Cheers, M No virus found in this incoming message. Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database: 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM -- 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] eliminating records not in (select id ... so SLOW?
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: I'm doing something like: delete from table1 where id not in (select id from table2). table1 contains ~1M record table2 contains ~ 600K record and id is unique. That's going to pretty much suck unless you've got work_mem set high enough to allow a hashed subplan plan --- which is likely to require tens of MB for this case, I don't recall exactly what the per-row overhead is. Experiment until EXPLAIN tells you it'll use a hashed subplan. BTW, don't bother with creating the index, it doesn't help for this. 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] Declaring constants in SQL
On Jul 30, 10:53 pm, [EMAIL PROTECTED] (Richard Broersma)
wrote:
On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
[EMAIL PROTECTED] wrote:
Is there any means like (#define or DECLARE ) where I can write SQL like
(...)
CREATE VIEW primary_colors_foos AS
SELECT * FROM foo
WHERE color = ANY( SELECT colorid
FROM Colors
WHERE colorname = ANY( 'red', 'blue', 'yellow' ));
Or even:
CREATE VIEW primary_color_foos AS
SELECT foo.* FROM foo JOIN color c USING (color_id)
WHERE c.colorname IN ('red', 'blue', 'yellow' );
If you have some constant values you need all over the place, you can
also resort to functions, which you can use much like CONSTANTs:
CREATE FUNCTION my_val()
RETURNS integer AS
$BODY$
BEGIN
RETURN 21;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
Or, for the case at hand, an example in sql:
SELECT * FROM foo WHERE foo_id myval();
CREATE FUNCTION my_colors()
RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
LANGUAGE 'sql' IMMUTABLE;
Use it like this:
SELECT * FROM foo WHERE color = ANY(myval());
Regards
Erwin
--
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] Declaring constants in SQL
The last part got scrambled, should read like this: (...) Use it like this: SELECT * FROM foo WHERE foo_id myval(); Or, for the case at hand, an example in sql: CREATE FUNCTION my_colors() RETURNS text[] AS $$ SELECT ARRAY['red','green','blue'] $$ LANGUAGE 'sql' IMMUTABLE; Use it like this: SELECT * FROM foo WHERE color = ANY(my_colors()); Regards Erwin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
