Ow Mun Heng wrote:
Okay.. I tried that, but it seems like there's an issue w/ the CASE
statements.
When I tried the prepare w/
AND (CASE WHEN $3 IS NULL THEN true else d.code = any ($3) END)
AND (CASE WHEN $4 IS NULL THEN TRUE else D.id = any($4) END)
it ERRORs w/ could not
On Mon, 2007-09-24 at 02:31 -0400, Alvaro Herrera wrote:
Ow Mun Heng wrote:
Okay.. I tried that, but it seems like there's an issue w/ the CASE
statements.
When I tried the prepare w/
AND (CASE WHEN $3 IS NULL THEN true else d.code = any ($3) END)
AND (CASE WHEN $4 IS NULL THEN
I am using PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special) on Windows 2003 server
While I am trying to update a prmary key It is failing with the
following message ERROR: duplicate key violates unique constraint
master_pkey
Can anybody explain why this
Hi,
With the option -s and \timing, I can get the statistics info
from the postmaster process and query time from the psql process. My
question is that how can I get the total statistics info and query time for
many queries, such as 100 search query?
Any help would be
As per as I think this is not bug,
in first case
when u update the first row
In first case:
M1= 1+1 =2 conflict with the second row.
in second case:
M1=3+1=4 no conflict with second row
I hope this will help.
With reagrds
Ashish
- Original Message -
From: Anoo Sivadasan
I notice that section 9.9 of the manual has examples including both HOUR and
HOURS, but the list of reserved words includes only the former.
Am I correct in assuming that
select time '11:00' - interval '3 hour';
is preferred to
select time '11:00' - interval '3 hours';
In a
Hi All,
I'm getting a lot of this sort of thing in my console when postgresql is
being contacted
message type 0x54 arrived from server while idle
message type 0x44 arrived from server while idle
message type 0x43 arrived from server while idle
message type 0x5a arrived from server while idle
Can someone please tell How a simple transaction goes through various steps for
completion in PostgreSQL. Thanks.
Regards,
Farhan
-
Yahoo! Answers - Get better answers from someone who knows. Tryit now.
Hi Ashish,
Do you mean to say that UPDATE command will work depending on the order
in which data is inserted?
I mean,
The following command will not work
CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES
Hi,
I had the very bad idea of removing the superuser flag from the
pgsql role. Now each time I try to set it back, with:
ALTER ROLE pgsql
SUPERUSER;
... I get:
ERROR: must be superuser to alter superusers
SQL state: 42501
Since there is no superuser in the database engine anymore, what
Hi,
I am getting the error mentioned in the subject ('pg_dump: schema
with OID 1515546 does not exist') when I try to back up one database
out of a cluster of half a dozen or so on our server. This is a
production DB and the database in question _seems_ to be working OK,
other than the
On 9/24/07, Philippe Lang [EMAIL PROTECTED] wrote:
Hi,
I had the very bad idea of removing the superuser flag from the
pgsql role. Now each time I try to set it back, with:
That's kind of the equivalent of locking your keys in the car...
Since there is no superuser in the database engine
Hello, I see that I can modify system tables even though I have
not set allow_system_table_mods... Is this a feature or a bug?
Self contained code
postgres=# SELECT version();
version
On 5 apr 2006, at 18.37, Jonas F. Henriksen wrote:
ehh, sorry, yes, I also want to retrieve other values from the table,
but I left them out for clarity (which made it maby less clear...).
Maybe it makes more sense if you define the table as
CREATE TABLE testtable (
depth integer,
On 9/24/07, Mark Morgan Lloyd [EMAIL PROTECTED] wrote:
In a similar vein, are fractional hours best specified as
select time '11:00' - interval '3 hour 45 minute';
or as
select time '11:00' - interval '3.75 hour';
or with the interval converted to an integer number of
On 9/24/07, Farhan Mughal [EMAIL PROTECTED] wrote:
Can someone please tell How a simple transaction goes through various steps
for completion in PostgreSQL. Thanks.
Maybe you'll find something here:
http://www.postgresql.org/docs/8.2/static/internals.html
---(end of
On Mon, 2007-09-24 at 14:44 +0200, Dawid Kuroczko wrote:
Hello, I see that I can modify system tables even though I have
not set allow_system_table_mods... Is this a feature or a bug?
allow_system_table_mods allows you to modify the structure, not just the
data, i.e. add additional columns to
On Mon, 2007-09-24 at 10:35 +0100, Farhan Mughal wrote:
Can someone please tell How a simple transaction goes through various
steps for completion in PostgreSQL. Thanks.
You might mean the execution steps. You can view these using EXPLAIN.
Ow Mun Heng wrote:
On Mon, 2007-09-24 at 02:31 -0400, Alvaro Herrera wrote:
it ERRORs w/ could not determine data type of parameter $3
Cast it to the correct type.
how do I cast a NULL? Is it Varchar?
I didn't say pick a random datatype, I said the correct type, which
in this case
David Brain [EMAIL PROTECTED] writes:
I am getting the error mentioned in the subject ('pg_dump: schema
with OID 1515546 does not exist') when I try to back up one database
out of a cluster of half a dozen or so on our server.
Well, for starters, troll through the system catalogs in that
Simon Riggs [EMAIL PROTECTED] writes:
On Mon, 2007-09-24 at 14:44 +0200, Dawid Kuroczko wrote:
Hello, I see that I can modify system tables even though I have
not set allow_system_table_mods... Is this a feature or a bug?
allow_system_table_mods allows you to modify the structure, not just
On Sep 18, 2007, at 1:14 AM, Joshua D. Drake wrote:
Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
reindex/cluster indexes?
If you overrun your max_fsm_pages, no:
else yes;
my algorithm is: if (true) then yes;
my FSM is way bigger than I ever use (vacuum never
On Sunday 23 September 2007 23:43, Ow Mun Heng wrote:
On Sat, 2007-09-22 at 16:05 -0400, Robert Treat wrote:
On Friday 21 September 2007 22:43, Merlin Moncure wrote:
On 9/21/07, Robert Treat [EMAIL PROTECTED] wrote:
On Thursday 20 September 2007 18:38, Merlin Moncure wrote:
On
What about Perl? DBI? Not sure if it supports PervasiveSQL
The OP stated he already had ODBC connectors setup.
Yes, I already have ODBC setup. I'm basically just writing a little
something myself that uses the existing Pervasive and Postgres tools as
much as possible.
On a
Trying to format our pg_dump files so that we can take advantage of
rsync delta copies and the problem seems to be the sequences and maybe
very active tables not allowing much matched data in the transfer. I
have a 3GB dump file in plain text and broke off the head of two dump
files into their own
Hi,
This is PG version 8.2.3.
The select returns:
temptrans,1515546,1515548,16398,0,1515547,0,133873,2.0234e
+06,1515549,0,f,f,r,24,0,0,0,0,0,f,f,f,f,195484336,,
What other catalogs would be worth looking in to? By the way the
table name here does sort of make sense as to something that
On 24/09/2007, Vivek Khera [EMAIL PROTECTED] wrote:
my FSM is way bigger than I ever use (vacuum never reports shortage)
and I still get bloat that needs to be purged out with a reindex on
occasion.
Vivek,
I feel your pain. But I seem to have (mostly) solved my problem in three ways:
1.
David Brain [EMAIL PROTECTED] writes:
This is PG version 8.2.3.
The select returns:
temptrans,1515546,1515548,16398,0,1515547,0,133873,2.0234e
+06,1515549,0,f,f,r,24,0,0,0,0,0,f,f,f,f,195484336,,
What other catalogs would be worth looking in to? By the way the
table name here does
pg_dump --help says:
-b, --blobs include large objects in dump
What is the definition of large object? Is it a certain set of types
(e.g. text)? Long values stored in these types? What qualifies as
long? In general, how can I tell if I need the -b flag (postgres 7.4).
Morris Goldstein
Hi,
First, thank you, things now do seem to be working correctly.
Hmm, do you rely on DROP SCHEMA foo CASCADE to make the contained
objects go away? We have seen a few reports suggesting that that
sometimes misses some contained objects. The mechanism for this has
not been identified for
On Sep 24, 2007, at 12:48 PM, Morris Goldstein wrote:
pg_dump --help says:
-b, --blobs include large objects in dump
What is the definition of large object? Is it a certain set of types
(e.g. text)? Long values stored in these types? What qualifies as
long? In general, how can I tell
On Sep 24, 2007, at 12:00 PM, Phoenix Kiula wrote:
I feel your pain. But I seem to have (mostly) solved my problem in
three ways:
My particular usage pattern (add data continuously, purge *some* of
the data once per week or every other week. The purge is what seems
to kill it. Last
Morris Goldstein escribió:
pg_dump --help says:
-b, --blobs include large objects in dump
What is the definition of large object?
The kind you create with lo_open(), etc. If you don't do that, you
don't need it.
--
Alvaro Herrera
Vivek Khera [EMAIL PROTECTED] writes:
On Sep 24, 2007, at 12:00 PM, Phoenix Kiula wrote:
I feel your pain. But I seem to have (mostly) solved my problem in three
ways:
My particular usage pattern (add data continuously, purge *some* of the data
once per week or every other week. The
Large objects are defined in the DDL as type 'OID'. Those with various
drivers may not know the lo_* calls are being used, since in Java this
takes place if you use the SQL BLOB type in JDBC and the field itself is
defined as 'OID'.
David
Alvaro Herrera wrote:
Morris Goldstein escribió:
Hi,
I'm trying to give access to *some* data to a third-party user.
I want to create a view with a choice of columns and a reduced
set of rows (via a where clause as part of the view definition), and
give this user access to that *and only that* --- where the and
only that goes in the most
On 9/24/07, Tom Lane [EMAIL PROTECTED] wrote:
Simon Riggs [EMAIL PROTECTED] writes:
On Mon, 2007-09-24 at 14:44 +0200, Dawid Kuroczko wrote:
Hello, I see that I can modify system tables even though I have
not set allow_system_table_mods... Is this a feature or a bug?
I have a very old postgres server that I am trying to move the data off
of. It is running 7.1 and has been trouble free for 6 plus years.
I am trying to move the data base off to a server running 8.1.
I have managed to back up the data using PG_Dump using like this.
pg_dump -b -Fc -h
Ottavio Campana wrote:
2) how can I speed it up? by using indexes? or by changing the query?
Do you have indices on mytable.id and copy_mytable.id?
Does using NOT EXISTS get you any better results?
db=# EXPLAIN ANALYZE select * from mytable where id not in (select id
from copy_mytable);
Hi all,
How can one make a set-returning function behave like
generate_series(). If I cobble up an srf with plpgsql i have to do
select * from mysrf().
But i can do select generate_series(1,10) or select this, that,the
other,generate_series(1,5) from sometable. Essentially I would like to
be
You might take a look at BtSearch at www.nssdd.com. It will allow you
to export all the Btrieve files in a DDF to CSV format.
Gil
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Wednesday 19 September 2007, Chester wrote:
Hi
I have a question regarding foreign keys, I just cannot get it to create
them for meI must be doing something wrong but I have no idea what
that might be :)
I have a table clients
clientID (primary)
ticode
Firstname
SecondName
I am trying to create an expression which
- always yield true if the incomming array is NULL
- yields true if a given value is in the array, otherwise yields false
I thought this should work:
Select 'target'=ANY(COALESCE('{indata1, indata2}','{target}'))
but I get an ERROR: op ANY/ALL (array)
Hi,
I found out this morning that I cannot get pg_dump to work at all on
my database. It refuses to create a dump and instead just freezes.
When using the verbose option (-v) i get the following output and then
it stops (it at one point ran for days on end before i even noticed)
pg_dump: saving
=== PROBLEM SOLVED ===
I found for what I was looking
http://www.postgresql.org/docs/8.0/static/functions-array.html
Function : array_to_string (anyarray, text)
Return type : text
Description : concatenates array elements using provided delimiter
Example : array_to_string(array[1, 2, 3], '~^~')
On 9 21 , 10 04 , Charles.Hou [EMAIL PROTECTED] wrote:
On 9 20 , 3 11 , Hannes Dorbath [EMAIL PROTECTED] wrote:
On 20.09.2007 04:34, Charles.Hou wrote:
i have installed thepgpoolin the server and start it. but how can i
know it's working? i means that the clients connected to the
On Mon, 2007-09-24 at 12:50 +0530, Anoo Sivadasan Pillai wrote:
CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate
On 9 20 , 3 11 , Hannes Dorbath [EMAIL PROTECTED] wrote:
On 20.09.2007 04:34, Charles.Hou wrote:
i have installed thepgpoolin the server and start it. but how can i
know it's working? i means that the clients connected to the database
by thepgpool.
Well, read the documentation,
On Wednesday 19 September 2007, Bjørn T Johansen wrote:
It's a Dell server with the following spec:
PE2950 Quad-Core Xeon E5335 2.0GHz, dual
4GB 667MHz memory
3 x 73GB SAS 15000 rpm disk
PERC 5/I Integrated controller card (8 ports, 256MB cache, battery backup) x
6 backplane
Asking is
i have installed the pgpool in the server and start it. but how can i
know it's working? i means that the clients connected to the database
by the pgpool.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
is there any way to know the database loading? because i want to know
the performance after i use the pgpool.
how can i know the perofrmance have been increased?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Hello Rhys
its not mystery
create or replace function mysrf(a integer)
returns integer
as $$
begin
for i in 1..a loop
return next i; -- important
end loop;
return;
end;
$$ language plpgsql;
you can call select * from mysrf(10);
Regards
Pavel Stehule
2007/9/21, Rhys Stewart [EMAIL
David Siebert [EMAIL PROTECTED] writes:
I have a very old postgres server that I am trying to move the data off
of. It is running 7.1 and has been trouble free for 6 plus years.
I am trying to move the data base off to a server running 8.1.
I have managed to back up the data using PG_Dump
On 9/24/07, Pavel Stehule [EMAIL PROTECTED] wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:
select myfunc(10);
---(end of broadcast)---
TIP 9: In versions below 8.0, the
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/24/07, Pavel Stehule [EMAIL PROTECTED] wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:
select myfunc(10);
Amigos tengo el siguiente problema, soy nuevo en postgres y no
comprendo muy bien, bueno en realidad para nada como hacer
procedimientos almacenados en postgres que me devuelvan un dataset
Friends, I'm new in postgress and dont know at all anything about
stored procedures in postgresql,
can u
On 9/23/07, Tom Lane [EMAIL PROTECTED] wrote:
I wrote:
Nikita The Spider The Spider [EMAIL PROTECTED] writes:
Thanks for your help! Given that this problem seems to be triggered by
a sort of edge case and the fix is non-trivial, I guess I should not
expect a new version of pg_dump soon?
I was following the posts from a month or two ago about reordering
columns using syntax similar to MySQL's:
ALTER TABLE tbl ALTER COL col1 AFTER/BEFORE col2;
I have been working on a new project that adds some functionality to
an existing database schema, which caused some tables to expand with
I am trying to create an expression which
- always yield true if the incomming array is NULL
- yields true if a given value is in the array, otherwise yields false
I thought this should work:
Select 'target'=ANY(COALESCE('{indata1, indata2}','{target}'))
but I get an ERROR: op ANY/ALL
On 9/24/07, genesis [EMAIL PROTECTED] wrote:
Amigos tengo el siguiente problema, soy nuevo en postgres y no
comprendo muy bien, bueno en realidad para nada como hacer
procedimientos almacenados en postgres que me devuelvan un dataset
Friends, I'm new in postgress and dont know at all
Josh Trutwin [EMAIL PROTECTED] writes:
A couple things would have saved me lots of time and head scratching:
Being able to execute COPY TABLE where table name is a view - not
sure why this isn't possible.
Being able to COPY TABLE with the result of a SELECT command where I
can specify the
On 25/09/2007, Vivek Khera [EMAIL PROTECTED] wrote:
Recommending I run vacuum intermixed with the data purge is a non-
starter; the vacuum on these tables takes a couple of hours. I'd
never finish purging my data with that kind of delay.
...
I will investigate the fill-factor. That seems
i have installed the pgpool in the server and start it. but how can i
know it's working? i means that the clients connected to the database
by the pgpool.
Enable log_connections on your PostgreSQL server(s)'s configuration
and see if incoming connections come from pgpool.
--
Tatsuo Ishii
SRA
i have installed the pgpool in the server and start it. but how can i
know it's working? i means that the clients connected to the database
by the pgpool.
Enable log_connections on your PostgreSQL server(s)'s configuration
and see if incoming connections come from pgpool.
--
Tatsuo Ishii
SRA
Hi.
Sorry, very late reaction...
- Original Message -
From: Robert Treat [EMAIL PROTECTED]
On Friday 21 September 2007 22:43, Merlin Moncure wrote:
On 9/21/07, Robert Treat [EMAIL PROTECTED] wrote:
On Thursday 20 September 2007 18:38, Merlin Moncure wrote:
On 9/20/07, Collin
Hi,
I am not using any sequences, The following batch can reproduce the
behaviour.
CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
INSERT INTO master VALUES ( 1, 1 ) ;
INSERT INTO master VALUES ( 2, 2) ;
UPDATE master SET m2 = m2 + 1;
With Cheers,
Anoo S
From:
Hi,
On further testing I found the same behaviour in Unique keys too, The following
batch can reproduce the behaviour.
CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
INSERT INTO master VALUES ( 1, 1 ) ;
INSERT INTO master VALUES ( 2, 2) ;
UPDATE master SET m2 = m2 + 1;
2007/9/25, Rhys Stewart [EMAIL PROTECTED]:
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/24/07, Pavel Stehule [EMAIL PROTECTED] wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
On 25/09/2007, Anoo Sivadasan Pillai [EMAIL PROTECTED] wrote:
Hi,
On further testing I found the same behaviour in Unique keys too, The
following batch can reproduce the behaviour.
CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
INSERT INTO master VALUES ( 1, 1 ) ;
On Tue, 2007-09-25 at 10:06 +0530, Anoo Sivadasan Pillai wrote:
But since you can’t control the order, it is likely that the operation
will sometimes and will succeed sometimes, with the same data.. Weird
I feel .
Not so weird (IMO). Many databases will return rows unordered unless you
--- Phoenix Kiula [EMAIL PROTECTED] wrote:
Isn't this expected behavior? When you update the m2 of the first
record, it becomes 2 and violates the unique constraint as the second
row already has an m2 value of 2.
Well, it is a limitation PostgreSQL. This type of update is should work
Hi,
How can I remove characters that form a part of regular expressions? I
would like to remove all instances of the following characters:
[
]
\
+
Given that these all mean something in regexp, I am trying to prefix
them with a backslash, but it doesn't work. I tried the following:
update
72 matches
Mail list logo
