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
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 re
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 P
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
I
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 i
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
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,
measur
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 numb
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 o
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 t
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.
http://developer.postgresql.org/pgdocs/postgres
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
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 th
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
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 report
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:
>
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 s
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 m
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:
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 d
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 tim
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 Herrerahttp://www.advogato.
"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 w
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 st
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?
>
> > allo
Probably you are using a sequence, and if so you need to update the value of
sequence prior to update:
SELECT setval('master_m1_seq',((SELECT id_m1 FROM master ORDER BY 1 DESC LIMIT
1)+1));
- Original Message -
From: Anoo Sivadasan Pillai
To: pgsql-general@postgresql.org
Cc: A
Rodrigo De Le? wrote:
Pg is pretty smart for almost any case.
Which still doesn't say anything about best practice. In the end I found the
relevant part of the SQL spec, correct forms are HOUR and MINUTE where the
associated numbers are integers.
--
Mark Morgan Lloyd
markMLl .AT. telemetry
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 stan.somepla
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 able
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
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) re
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], '~^~')
R
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 co
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: dupli
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 documentat
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
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 <[EM
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_Dum
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 o
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
n
> 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 AN
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 al
"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 sp
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 se
> 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
SR
> 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
SR
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, C
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: Ardi
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
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 (
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
spe
--- 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
wi
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 T
On Tue, Sep 25, 2007 at 01:36:26PM +0800, Phoenix Kiula wrote:
> How can I remove characters that form a part of regular expressions?
Why do you want to do that?
> I would like to remove all instances of the following characters:
>
> [
> ]
> \
> +
test=> select id, t, regexp_replace(t, e'[[
Greetings list,
I have created a function which inserts a row in a table which has 2 unique
indexes on two different columns.
I am wondering, if there is a way in case of UNIQUE_VIOLATION exception to
find out which index would have been violated?
Petri Simolin
---(end
74 matches
Mail list logo
