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
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
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
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
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
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
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
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
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
Cousin Florence souhaite rappeler le message «».
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:
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
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
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
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
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
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:
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
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
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
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
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
One thing I left out of my last post:
Thanks to all of you for your assitance.
RobR
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.
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
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
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
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
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:
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
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
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
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 =
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
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
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
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
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
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
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
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
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
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
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
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
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:
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
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
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
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
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
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
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
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=#
--- 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
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
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
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
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
--- 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
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
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
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)
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
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
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
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
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
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
69 matches
Mail list logo
