am Thu, dem 29.03.2007, um 0:13:09 -0700 mailte Benjamin Arai folgendes:
> Hi,
>
> If I have a PostgreSQL table with records and logical indexes already
> created, if I use COPY to load additional data, does the COPY update
> the indexes during, after, or not at all?
after, i think.
test=#
I have a table with ~5 million rows containing ranges of large (8-digit)
numbers. The table has an int4 field for the range start and the range end,
and a field which is null if that particular range is expired, and has a
value otherwise.
I need to query this table to find a range containing a
2007/3/29, Toke Høiland-Jørgensen <[EMAIL PROTECTED]>:
I have a table with ~5 million rows containing ranges of large (8-digit)
numbers. The table has an int4 field for the range start and the range end,
and a field which is null if that particular range is expired, and has a
value otherwise.
I
Hi!
Can anyone tell me how can I insert an information from a text file to a
PostgreSQL database when the text file is continously being updated with a
new information.
Alternatively if I have a freebsd program which continously echoes to the
console screen some information, how can I insert tha
Suro wrote:
Hi!
Can anyone tell me how can I insert an information from a text file to a
PostgreSQL database when the text file is continously being updated with a
new information.
Alternatively if I have a freebsd program which continously echoes to the
console screen some information, how can
Hi,
I have recently started using the pgAgent and I am having some issues
with the agent service crashing.
The job that I am running has two steps:
1. A vacuum.
2. Backup, which executes the following
"c:\Program Files\PostgreSQL\8.2\bin\pg_dump.exe" -i -h localhost -p
5432 -U postgres -F p
Howard Cole wrote:
Hi,
I have recently started using the pgAgent and I am having some issues
with the agent service crashing.
The job that I am running has two steps:
1. A vacuum.
2. Backup, which executes the following
"c:\Program Files\PostgreSQL\8.2\bin\pg_dump.exe" -i -h localhost -p
5
Humm, ok, it is clear now.
And is there a way to change something in this behaviour, like not ignore
spaces and some type of symbols?
A configuration file or a patch?
Thanks in advance!
> -Mensagem original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] nome de Stephan Szabo
> En
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 03/29/07 06:33, Alejandro D. Burne wrote:
> 2007/3/29, Toke Høiland-Jørgensen <[EMAIL PROTECTED]>:
[snip]
>>
>> Any help will be greatly appreciated.
>>
>> Regards,
>> -Toke
>
> Can you send an explain analyze for that query?
And tell us how often
Dave Page wrote:
Please try v1.6.3 - there was a bug fixed that might well cause this
crash.
Regards, Dave.
Thanks Dave. I think that has fixed it.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
On Thursday 29 March 2007 13:33, Alejandro D. Burne wrote:
> 2007/3/29, Toke Høiland-Jørgensen <[EMAIL PROTECTED]>:
> > I have a table with ~5 million rows containing ranges of large (8-digit)
> > numbers. The table has an int4 field for the range start and the range
> > end, and a field which is n
On Thursday 29 March 2007 15:09, Ron Johnson wrote:
> On 03/29/07 06:33, Alejandro D. Burne wrote:
> > 2007/3/29, Toke Høiland-Jørgensen <[EMAIL PROTECTED]>:
>
> [snip]
>
> >> Any help will be greatly appreciated.
> >>
> >> Regards,
> >> -Toke
> >
> > Can you send an explain analyze for that query?
Carlos H. Reimer wrote:
Humm, ok, it is clear now.
And is there a way to change something in this behaviour, like not ignore
spaces and some type of symbols?
A configuration file or a patch?
Pick a different locale?
--
Richard Huxton
Archonet Ltd
---(end of broad
Hi,
I've written a function using cursors as follows:
can anyone please comment on the text in red.
--
CREATE OR REPLACE FUNCTION sp_insert_tbl_email_address(int4, text, text,
text)
RETURNS void AS
$BODY$
DECLARE
uid int4 ;
src text;
2007/3/29, Jasbinder Singh Bali <[EMAIL PROTECTED]>:
Hi,
I've written a function using cursors as follows:
can anyone please comment on the text in red.
--
CREATE OR REPLACE FUNCTION
sp_insert_tbl_email_address(int4, text, text, text)
RETUR
On Mar 29, 2007, at 10:47 , Jasbinder Singh Bali wrote:
Hi,
I've written a function using cursors as follows:
can anyone please comment on the text in red.
--
CREATE OR REPLACE FUNCTION sp_insert_tbl_email_address(int4, text,
text, text)
Actually I'm doing a duplicate check
My function accepts 4 parameters.
If all four exist in a particular row then i should not be inserting that
record again.
so is
INSERT INTO table(a,b,c) SELECT 'a','b','c' WHERE NOT EXISTS
( SELECT * FROM table WHERE (a,b,c) = ('a','b','c') );
going to solve m
=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <[EMAIL PROTECTED]> writes:
> In fact, I wonder why a date ranging from somme 4000 BC to 3 AC is
> stored as a reference to the 1st january of 2000. Is it because that day
> is some "close to actual time" date ?
The restriction to 4713BC comes from the
"A. Kretschmer" <[EMAIL PROTECTED]> writes:
> am Thu, dem 29.03.2007, um 0:13:09 -0700 mailte Benjamin Arai folgendes:
>> If I have a PostgreSQL table with records and logical indexes already
>> created, if I use COPY to load additional data, does the COPY update
>> the indexes during, after,
Thanks for the quick reply Tom! The pg_locks table helped me to get to
the bottom of this.
For future reference to others, here is a good way to view the pg_locks
table for a particular database, adding table name annotation:
SELECT pg_locks.*, pg_class.relname from pg_locks, pg_class
WHERE pg
Here results of what I've done.
Just a note all this was done on 7.4.16:
First of all problems:
1. cannot complete configure on flass drive
./configure --prefix=/media/sda1/app/psql/postgresql-7.4.16/bin
--without-readline
...
configure: creating ./config.status
config.status: creating GNUmakef
Toke =?utf-8?q?H=C3=B8iland-J=C3=B8rgensen?= <[EMAIL PROTECTED]> writes:
> I need to query this table to find a range containing a particular number,
> e.g. a query might look like this:
> SELECT * FROM table_name WHERE range_start <= 87654321 AND range_end >=
> 87654321 AND expired IS NULL
You
On Thu, 29 Mar 2007, Carlos H. Reimer wrote:
> Humm, ok, it is clear now.
>
> And is there a way to change something in this behaviour, like not ignore
> spaces and some type of symbols?
Well, right now it's generally determined by your OS's definition of the
locale you've chosen. You might be ab
On 3/28/07, Joseph S wrote:
Dmitry Koterov wrote:
> And the general question - why SELECT queries slowdown during the
> CHECKPOINT? I thought that Postgres is a version-based database and read
> queries are never blocked...
Because the disk is busy.
Is your pg_xlog on a separate disk?
I am looking for a mentor for PostgreSQL and Delphi.
I have developed a PostgreSQL database which needs to interface with an
existing Delphi application.
If anyone is interested please contact me off line at [EMAIL PROTECTED] .
Compensation to be discussed.
Bob Pawley
On Mar 29, 2007, at 10:47 , Jasbinder Singh Bali wrote:
Hi,
I've written a function using cursors as follows:
can anyone please comment on the text in red.
--
CREATE OR REPLACE FUNCTION sp_insert_tbl_email_address(int4, text,
text, text)
Hi
I am trying to do an upgrade on an open source app called adempiere The problem
I have is that the
original scripts are for Oracle and I am trying to run it on a PostgreSQLl data
base
I am having a problem converting the 007_ProductAttribute.sql script. See below
I am getting the followin
am Thu, dem 29.03.2007, um 10:02:49 -0700 mailte Benjamin Arai folgendes:
> So, is there a way to defer the index updating until a later period
> of time. More specifically, I would like to do several COPIES to a
> running database, then afterward force a update on the index via a
> vacuum
So, is there a way to defer the index updating until a later period
of time. More specifically, I would like to do several COPIES to a
running database, then afterward force a update on the index via a
vacuum or something similar.
Benjamin
On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote:
On Thu, Mar 29, 2007 at 05:07:42PM +, Pete wrote:
>
> Hi
>
> I am trying to do an upgrade on an open source app called adempiere The
> problem I have is that the
> original scripts are for Oracle and I am trying to run it on a PostgreSQLl
> data base
>
> I am having a problem converting
Note: When timestamp values are stored as double precision
floating-point numbers (currently the default), the effective limit of
precision may be less than 6. timestamp values are stored as seconds
before or after midnight 2000-01-01. Microsecond precision is achieved
for dates within a few year
Pete wrote:
Hi
I am trying to do an upgrade on an open source app called adempiere The problem I have is that the
original scripts are for Oracle and I am trying to run it on a PostgreSQLl data base
I am having a problem converting the 007_ProductAttribute.sql script. See below
I am getting
hello,
I use pgsql to store data for my program (hotel manager). The
filesystem reports that the size on disk of database is not so bing
(~100MB ~20MB actual data). I want to tune the engine to fit the
database as much as possible in RAM, say: postgres, please, use 512MB
(1GB) of my RAM just for
Has anyone experienced any problems with the Postgres 8.2.x database
crashing when running PLPGSQL functions?
When I try to run my plpgsql function it causes Postgres to restart
unexpectedly. The code was working with prior versions of Postgres,
now it seems to be crashing the postmaster. H
Would you suggest posgresql to build a vocabulary?
Any examples?
thank you
Pol
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
Ciao a tutti..
Devo inserire in un campo u testo, prelevato input.
Il problema e che in questo testo possono essere inseriti i seeguenti
caratteri:
è ò à ù ì € $ £ ( )
Al momento sto usando come codifica del Database: UTF8.
non me li fa inserire...
Qualc'uno mi potrebbe consiliare sulla qua
Sorry for the late reply, but thanks for your input, Jeff and Greg.
Greg, the trigger percent_two works for each statement. Would it be
possible to defer any check until the end of the transaction? For
example, I may need to insert into hundred values(1,25), followed by
(1,75), without the 25 imme
Hi!
it would be great to have a coalesce2 function which treats empty strings as
null values.
as far as I have seen, there are a lot of comments and coding solutions about
this, but none is an "easy" one and all make the code a bit more complicated
and more difficult to maintain.
I have creat
Friends,
I did a little research into the archives of this list for my
particular problem, and while I haven't found the solution, I'm
thinking that maybe I'm approaching it wrong. If anyone has any
advice, it'd be much appreciated.
On the one hand I have a database in postgres created WITH
ENCO
Try searching on
postgresql-debuginfo
for the linux distribution and version you want at
http://rpm.pbone.net
Regards,
Ian Johnson
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Tom Lane
Sent: March 27, 2007 8:05 PM
To: Geoffrey
Cc: pgsql-general@postgr
Hi,
We plan to upgrade our database from 7.4.11 to 8.x. It seems that both 8.1
and 8.2 branches are maintained. Which one is more stable? 8.1.18 or 8.2.3?
8.1 or 8.2 branch in general?
We are trying to settle with one so we don't have to upgrade for a while.
Thank you in advance for your answers,
> > Or SQL-on-rails
> > http://www.sqlonrails.org/
>
> LOL!
>
> merlin
Heh heh..."insufficiently AJAX-y"
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PR
On Mar 22, 8:09 pm, [EMAIL PROTECTED] (Jeff Davis) wrote:
> On Tue, 2007-03-20 at 13:21 -0700, Angva wrote:
>
> Your Oracle solution is interesting, and can indeed be implemented in
> PostgreSQL in exactly the same way. Look at materialized views here:
>
> http://www.varlena.com/varlena/GeneralBits
Dear Mr. Tom Lane,
Thank you very much for your answer.
It seems that the legacy application creates tables dynamically and the
number of the created tables depends on the size of the input of the
application. For the specific input which generated that error I've
estimated a number of created tab
How to tune them?
Now I have:
checkpoint_segments = 5 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 900# range 30-3600, in seconds
checkpoint_warning = 30 # in seconds, 0 is off
No checkpoint warnings in pgsql logs.
On 3/27/07, Oleg Bartunov wrote:
hm
So, is there a way to defer the index updating until a later period
of time. More specifically, I would like to do several COPIES to a
running database, then afterward force a update on the index via a
vacuum or something similar.
Benjamin
On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote:
On 27 Mar 2007 23:43:38 -0700, filippo <[EMAIL PROTECTED]> wrote:
I use pgsql to store data for my program (hotel manager). The
filesystem reports that the size on disk of database is not so bing
(~100MB ~20MB actual data). I want to tune the engine to fit the
database as much as possible in RAM
On Mar 27, 10:34 am, [EMAIL PROTECTED] (Aidan Van Dyk) wrote:
>
> Sounds something like mod_libpq:
>http://asmith.id.au/mod_libpq.html
Thanks, I appreciate the link...also looks promising.
Kev
---(end of broadcast)---
TIP 6: explain analyze is
No. Disk read activity is ALWAYS 0, the system has a lot of disk cache.
On 3/28/07, Joseph S wrote:
Dmitry Koterov wrote:
> And the general question - why SELECT queries slowdown during the
> CHECKPOINT? I thought that Postgres is a version-based database and read
> queries are never blocked
PostgreSQL 8.1.3 on Windows 2003 Server.
I am running pg_dumpall on my cluster that is about 500Gb in size. The
dump started ok, but then it gets to a table that's about 160Mb in
size with a 100Mb of indexes and it seems stuck. The dump file is
getting larger, but the dump is not finishing this ta
On Tue, Mar 27, 2007 at 11:43:38PM -0700, filippo wrote:
> I use pgsql to store data for my program (hotel manager). The
> filesystem reports that the size on disk of database is not so bing
> (~100MB ~20MB actual data). I want to tune the engine to fit the
> database as much as possible in RAM, s
I see that Simon has pushed pg_standby into contrib for 8.3. Is there
anything that would make the current version in CVS unsuitable for use
in 8.2.x? I've done a cursory inspection of the code, but I'll admit
that I haven't looked up the interfaces used from postgres_fe.h and
pg_config_manual.h to
On Sat, 2007-03-24 at 11:59 +, Raymond O'Donnell wrote:
>
> Not to mention the danger of losing the confounded thing :)
Or having what happened to me... my emergency crash recovery data (pgp
keys, Lotus Notes ID, stuff like that) on a USB drive got chewed up by
the dog. Thankfully I didn'
"John D. Burger" <[EMAIL PROTECTED]> writes:
> Hmm, except if the timestamp "anchor" is installation-specific, then
> binary exchange of timestamps is complicated.
Yeah, that would be a problem.
> What does libpq do now
> with timetamps, if the client requests data in binary form? How does
Stephen Harris wrote:
I'm vacuuming every night after the inserts are done.
You should vacuum after the deletes and before the inserts, so the
inserts can go into the just reclaimed space.
---(end of broadcast)---
TIP 1: if posting/reading thro
Benjamin Arai wrote:
> So, is there a way to defer the index updating until a later period
> of time. More specifically, I would like to do several COPIES to a
> running database, then afterward force a update on the index via a
> vacuum or something similar.
Sure, drop the index, do the CO
Nik wrote:
The reason I am running pg_dumpall is that I would like to create an
exact copy of my database on another server. I cannot use create
database with template command since there are connections to the
original database. The dumpall method seems somewhat cumbersome since
it generates on
"Gary Winslow" <[EMAIL PROTECTED]> writes:
> Has anyone experienced any problems with the Postgres 8.2.x database
> crashing when running PLPGSQL functions?
Show us the function please --- a self-contained test case would be
best.
> This problem seems to be exactly the same symptoms as a bug I re
On 29/03/2007 20:18, Peter L. Berghold wrote:
Or having what happened to me... my emergency crash recovery data (pgp
keys, Lotus Notes ID, stuff like that) on a USB drive got chewed up by
the dog. Thankfully I didn't actually need the thing before I could
Heh heh heh.dogs are merely dange
Pete <[EMAIL PROTECTED]> writes:
> I am looking for an on line help with loads of examples for PSQL
> specifically in regards to stored procedures or functions.
> The following does not help me much.
> http://www.postgresql.org/docs/8.2/interactive/sql-createfunction.html
Try
http://www.postgresq
andant wrote:
Ciao a tutti..
Devo inserire in un campo u testo, prelevato input.
Il problema e che in questo testo possono essere inseriti i seeguenti
caratteri:
è ò à ù ì € $ £ ( )
Al momento sto usando come codifica del Database: UTF8.
non me li fa inserire...
Qualc'uno mi potrebbe consi
"Billy Gray" <[EMAIL PROTECTED]> writes:
> char *buffer = (char *) xmalloc (STDIN_BLOCK); //xmalloc is really
> malloc
> int offset = 0;
> int read = 1;
> int size = STDIN_BLOCK;
> while ( (read > 0) && (offset <= STDIN_MAX) )
> {
> syslog (LOG_DEB
On 3/27/07, Aidan Van Dyk <[EMAIL PROTECTED]> wrote:
Kev wrote:
> Hi everyone,
>
> I'm still in the design phase of a project. I was just wondering if
> anyone has any thoughts or experience on the idea of cutting the P out
> of the LAMP (or in my case, WAMP for now) stack. What I mean is
> ha
I would prefer not to drop the index because the database is several
hundred gigs. I would prefer to incrementally add to the index.
Benjamin
Bruce Momjian wrote:
Benjamin Arai wrote:
So, is there a way to defer the index updating until a later period
of time. More specifically, I would
No, but disks are about 120 MB/s speed, peak writes during CHECKPOINT are
only 20-30 MB/s, and there is no disk read activity at all, so - no matter
where the xlog resides.
But now seems bgwriter tuning gets some effect, CHECKPOINT is running faster
(about 2-3 seconds instead of 10-15). It is sti
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 03/29/07 14:41, Bruce Momjian wrote:
> Benjamin Arai wrote:
>> So, is there a way to defer the index updating until a later period
>> of time. More specifically, I would like to do several COPIES to a
>> running database, then afterward force a
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 03/29/07 14:51, Benjamin Arai wrote:
> I would prefer not to drop the index because the database is several
> hundred gigs. I would prefer to incrementally add to the index.
Some RDBMSs (well, one that I know of) has the ability to defer
index upd
Benjamin Arai wrote:
> I would prefer not to drop the index because the database is several
> hundred gigs. I would prefer to incrementally add to the index.
I know of now way to do that in a batch, unless you go with partitioned
tables.
-
> You can't usefully use a two-column btree index for this. btree indexes
> are not magic, they're just ordered lists, and if you think about where
> the rows you want might fall in the sort order, you'll see that the two
> given constraints aren't helpful for constraining the indexscan: it'd
> h
Is it possible to declare a unique constraint in combination with a deleted
flag?
For example, if I have a table like this:
CREATE TABLE
(
ID NOT NULL PRIMARY KEY,
Key VARCHAR(32) NOT NULL,
Value VARCHAR(32) NOT NULL,
Deleted INT NOT NULL DEFAULT 0
);
can I declare a unique constraint that
On Thu, 2007-03-29 at 15:13, Dmitry Koterov wrote:
> No, but disks are about 120 MB/s speed, peak writes during CHECKPOINT
> are only 20-30 MB/s, and there is no disk read activity at all, so -
> no matter where the xlog resides.
>
> But now seems bgwriter tuning gets some effect, CHECKPOINT is ru
Hi
Thanks for the help, but
I am kind of stuck on the cursors in PL/pgSQL
I have tried
> adempiere.CUR_Attributes CURSOR ai.Value, a.Name FOR
> SELECT ai.Value, a.Name
and
> DECLARE adempiere.CUR_Attributes CURSOR FOR
> SELECT ai.Value, a.Name
but none are accepted.
>From t
Benjamin Arai <[EMAIL PROTECTED]> writes:
> I would prefer not to drop the index because the database is several
> hundred gigs. I would prefer to incrementally add to the index.
This may well be false economy. I don't have numbers at hand, but a
full rebuild can be substantially faster than ad
"William Gray" <[EMAIL PROTECTED]> writes:
> ... And in the case
> that fread() pulls in less data than requested, that means the next call to
> fread() should return zero, right?
Wouldn't count on that, particularly not when reading from an
interactive device. You are more likely to get a line p
On Mar 29, 2007, at 17:39, Bryan Murphy wrote:
Is it possible to declare a unique constraint in combination with a
deleted flag?
For example, if I have a table like this:
CREATE TABLE
(
ID NOT NULL PRIMARY KEY,
Key VARCHAR(32) NOT NULL,
Value VARCHAR(32) NOT NULL,
Deleted INT NOT NUL
Thanks! That works great!
Bryan
On 3/29/07, Jonathan Hedstrom <[EMAIL PROTECTED]> wrote:
Bryan Murphy wrote:
> Is it possible to declare a unique constraint in combination with a
> deleted flag?
>
> For example, if I have a table like this:
>
> CREATE TABLE
> (
> ID NOT NULL PRIMARY KEY,
>
Bryan Murphy wrote:
Is it possible to declare a unique constraint in combination with a
deleted flag?
For example, if I have a table like this:
CREATE TABLE
(
ID NOT NULL PRIMARY KEY,
Key VARCHAR(32) NOT NULL,
Value VARCHAR(32) NOT NULL,
Deleted INT NOT NULL DEFAULT 0
);
can I declar
I think the other guys suggestion will work better. ;)
Really, the table was just an example off the top of my head. I believe we
do use a boolean as the deleted flag. We primarily use it to track the user
who originally created an item (even if their account is deleted). It's a
bit like a fin
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 03/29/07 18:35, Tom Lane wrote:
> Benjamin Arai <[EMAIL PROTECTED]> writes:
>> I would prefer not to drop the index because the database is several
>> hundred gigs. I would prefer to incrementally add to the index.
>
> This may well be false econ
Bryan Murphy wrote:
I think the other guys suggestion will work better. ;)
Good lord, yes. Dunno what I was thinking - I use partial indexes
all the time, and I know a unique constraint is implemented with an
index. Just got carried away, I guess. :)
- John Burger
MITRE
I agree, this is true if I cannot defer index updates. But if it is
possible to defer index updates until the end then I should be able to
achieve some sort of speedup. Rebuilding an index can't be the
PostgreSQL solution for all cases. I am dealing with databases in the
hundreds of gigs rang
I have one system which I have used partitioning. For this particular
case I have tons of data over about (50 years). What I did is wrote
small loader that breaks data in tables based on date, so I have tables
like abc_2000, abc_2001 etc. The loading script is only a couple
hundred lines of
On Thu, 2007-03-29 at 21:30 -0700, Benjamin Arai wrote:
> Rebuilding an index can't be the PostgreSQL solution for all
> cases. I am dealing with databases in the hundreds of gigs
> range and I am adding about 10gigs of data a week. At
> some point its going to take longer than a week to rebuil
On Thu, 2007-03-29 at 22:15 -0700, Benjamin Arai wrote:
> I have one system which I have used partitioning. For this particular
> case I have tons of data over about (50 years). What I did is wrote
> small loader that breaks data in tables based on date, so I have tables
> like abc_2000, abc_2
Ferdinand Gassauer wrote:
Hi!
it would be great to have a coalesce2 function which treats empty strings as
null values.
Why? What is the use-case for this?
as far as I have seen, there are a lot of comments and coding solutions about
this, but none is an "easy" one and all make the code a b
Qiao Yang wrote:
Hi,
We plan to upgrade our database from 7.4.11 to 8.x. It seems that both 8.1
and 8.2 branches are maintained. Which one is more stable? 8.1.18 or 8.2.3?
8.1 or 8.2 branch in general?
We are trying to settle with one so we don't have to upgrade for a while.
Thank you in advanc
86 matches
Mail list logo
