Title: Nachricht
Hello
Vijay!
Not
100% sure but Ubuntu should have the database set up similar to Debian on which
it's based. This means, you can start the database via:
/etc/init.d/postgresql start
This
should use the distribution specific startup scripts.
Greetings,
Matthias
Hi.
I can provide some "insight" on the difference between the two interfaces.
AFAIK, the difference is in size of the file you can store, and in the
interface you have when you want to access.
The size is not important (I think), since you are far below the limit.
For the interface, the bytea
Hi all,
inspired by the last posting "Weird disk write load caused by
PostgreSQL?" i increased the work_mem from 1 to 7MB and did some
loadtest with vacuum every 10 minutes. The system load (harddisk) went
down and everything was very stable at 80% idle for nearly 24 hours!
I am currently perform
It appears to me that work_mem is a more significant configuration
option than previously assumed by many PostgreSQL users, myself
included. As with many database optimizations, it's an obscure
problem to diagnose because you generally only observe it through I/O
activity.
One possibility
Hi,
I've been trying to add a unique constraint on a row and a function
result of a row. I.e.:
CREATE TABLE test (
id SERIAL PRIMARY KEY,
t1 TEXT NOT NULL,
t2 TEXT NOT NULL,
UNIQUE (t1, lower(t2)));
That fails with a syntax error (on 8.2beta1). While UNIQUE(t1,
I finished the little benchmarking on our server and the results are
quite curios.
With the numbers from http://sitening.com/tools/postgresql-benchmark/
in mind i did
./pgbench -i pgbench
and then performed some pgbench tests, for example
./pgbench -c 1 -t 1000 -s 1 pgbench
starting vacuum...end.
I forgot to mention that top does not show a noticeable increase of
CPU or system load during the pgbench runs (postmaster has 4-8% CPU).
Shouldn't the machine be busy during such a test?
thx,
Peter
2006/10/5, Peter Bauer <[EMAIL PROTECTED]>:
I finished the little benchmarking on our server and
If you are on Linux, I recommend iostat(1) and vmstat(8) over top.
Iostat will report I/O transfer statistics; it's how I discovered
that work_mem buffers were spilling over to disk files. For Vmstat,
look in particular at the load (ie., how many processes are competing
for the scheduler) i
Hello Markus,
* Markus Schiltknecht <[EMAIL PROTECTED]>, [2006-10-05 11:16 +0200]:
> I've been trying to add a unique constraint on a row and a function
> result of a row. I.e.:
>
> CREATE TABLE test (
> id SERIAL PRIMARY KEY,
> t1 TEXT NOT NULL,
> t2 TEXT NOT NULL,
>
Hi Tom,
Thank you for your reply.
I think the character map is a problem. So I see the choices for the Chinese user is:
1. use the EUC_CN
2. use the UTF-8
The first choice is what i'm using and the problem is clear to me. I can not store some special characters into the PostgreSQL with this enc
Emanuele Rocca wrote:
you'll get a duplicate key error.
Thank you, that solves my problem.
Although it makes me wonder even more why I'm not allowed to define such
a constraint. Looks like all the necessary backend code is there.
Regards
Markus
---(end of broadcast
Markus Schiltknecht <[EMAIL PROTECTED]> writes:
> UNIQUE (t1, lower(t2)));
> I can easily create an index for my needs [1], why can I not add such a
> unique constraint? Thanks for clarification.
Because the SQL spec defines this syntax, and it only allows column
names there.
Extending th
it seems that the machine doesn't really care about the pgbench run. I did a
pgbench -c 10 -t 1 -s 10 pgbench
and here is the output of vmstat 1 100 which has been started some
seconds before pgbench:
vmstat 1 100
procs ---memory-- ---swap-- -io --system-- cpu
r
"Peter Bauer" <[EMAIL PROTECTED]> writes:
> tps = 50.703609 (including connections establishing)
> tps = 50.709265 (excluding connections establishing)
That's about what you ought to expect for a single transaction stream
running on honest disk hardware (ie, disks that don't lie about write
comple
On 10/4/06, Guy Rouillier <[EMAIL PROTECTED]> wrote:
TIJod wrote:
> I need to store a large number of images in a
> PostgreSQL database. In my application, this
> represents a few hundreds of thousands of images. The
> size of each image is about 100-200 Ko. There is a
> large turnover in my data
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> ... postgresql will toast all images over a cerain size which
> is actually pretty efficient although can be a problem if your images
> are really big.
But any reasonable image format is compressed already (or at least, if
you are using an uncompresse
On Oct 5, 2006, at 16:18 , Merlin Moncure wrote:
I see little value to storing the images in the database. For me
that's
a general statement (I'm sure others will disagree); but
especially in
your case, where you have a high volume and only want to store
them for
a couple days. Why incur
On 10/5/06, Alexander Staubo <[EMAIL PROTECTED]> wrote:
On Oct 5, 2006, at 16:18 , Merlin Moncure wrote:
> i'm wondering if anybody has ever attempted to manage large
> collections of binary objects inside the database and has advice here.
We have a production system containing 10,000 images (JP
[EMAIL PROTECTED] ("Marco Bizzarri") writes:
> Hi.
>
> I can provide some "insight" on the difference between the two interfaces.
>
> AFAIK, the difference is in size of the file you can store, and in the
> interface you have when you want to access.
>
> The size is not important (I think), since y
> Right. Instead, specify a column list to the COPY (you are using a PG
> version new enough to have column lists in COPY, no?) and it will
> execute the default expression for the column(s) not coming from the data
> file.
Thanks. (Feeling dumb here.) I am using 8.1, however I haven't read the d
> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
>> ... postgresql will toast all images over a cerain size which
>> is actually pretty efficient although can be a problem if your images
>> are really big.
>
> But any reasonable image format is compressed already (or at least, if
> you are using an u
I appologize for duplicate posting, but I am not sure
if this is getting posted to the news group.
Problem:
PostgreSQL "Service" is not runing on a Linux Box,
but I have a database on the Linux Box, that I want
to relocate to another machine, Windows or Linux.
Question:
What files do I need to tr
Brian J. Erickson <[EMAIL PROTECTED]> schrieb:
> I appologize for duplicate posting, but I am not sure
> if this is getting posted to the news group.
>
> Problem:
> PostgreSQL "Service" is not runing on a Linux Box,
Why not?
> but I have a database on the Linux Box, that I want
> to relocate t
> I’ve been studying the whole evening and don’t seem to find an answer:
> I want to “store” transactions on the server- like view’s, or, (sorry) as in
> M$ SQL Server CREATE OR REPLACE TRANSACTION xyz()
Of course, it's possible.
What you need is
CREATE OR REPLACE FUNCTION xyz() RETURNS
Hi, I have a table with 8M rows. One of the fields is of type text and I
wanted to create an index on it to improve query times. Now this field
is a single string (ie not a piece of normal text) and is really an
identifier (< 100 chars). I envisage queries like
select cid from tableName where fie
Can anybody spot the problem with this function? Or, how i'm calling it?
(it's not clear to me which it is)
CREATE TABLE member (
...
first_name character varying(64),
last_name character varying(64),
organisation character varying(128),
email character varying(128),
...
);
CREATE OR R
Merlin Moncure wrote:
i'm wondering if anybody has ever attempted to manage large
collections of binary objects inside the database and has advice here.
We have designed and built an image library using Postgres and NFS
servers which currently holds 1.4 million images totalling more than 250Gb
I have a good-size DB (some tables approaching 100M rows), with
essentially static data.
Should I always cluster the tables? That is, even if no column jumps
out as being involved in most queries, should I pick a likely one and
cluster on it? (Of course, this assumes that doing so won't c
brian wrote:
Can anybody spot the problem with this function? Or, how i'm calling it?
(it's not clear to me which it is)
CREATE TABLE member (
...
first_name character varying(64),
last_name character varying(64),
organisation character varying(128),
email character varying(128),
...
);
am Thu, dem 05.10.2006, um 13:17:41 -0400 mailte brian folgendes:
> Can anybody spot the problem with this function? Or, how i'm calling it?
> (it's not clear to me which it is)
>
> CREATE TABLE member (
> ...
> first_name character varying(64),
> last_name character varying(64),
> organis
I have seen several posts pertaining to the "overhead" difference in storing
in a db table versus the file system. What is this difference?
I also think the decision as store in a db table or file system is looking
at how the files will be accessed. If all the clients are on the same
network as
A. Kretschmer wrote:
am Thu, dem 05.10.2006, um 13:17:41 -0400 mailte brian folgendes:
Can anybody spot the problem with this function? Or, how i'm
calling it? (it's not clear to me which it is)
CREATE TABLE member ( ... first_name character varying(64),
last_name character varying(64), orga
>> Problem:
>> PostgreSQL "Service" is not runing on a Linux Box,
> Why not?
The file "/etc/mtab" has been corrupted, when start
postgresql the "Service" dies because of an "input/output"
error.
Therefore, "pg_dumpall' won't work.
- Original Message -
From: "Andreas Kretschmer" <[EMAIL P
Hi,is it possible to check for sqlstate inside a function , something like: loop fetch bla. if sqlstate = '02000' then exit; end if; end loop;
if I try to save the above I get a : sqlstate not defined errorThen I tried this with no success: ..
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/05/06 14:50, Brian J. Erickson wrote:
>>> Problem:
>>> PostgreSQL "Service" is not runing on a Linux Box,
>> Why not?
> The file "/etc/mtab" has been corrupted, when start
> postgresql the "Service" dies because of an "input/output"
> error.
>
>
I am getting this in my log file:
2006-10-05 16:06:23 [6469] ERROR: deadlock detected
DETAIL: Process 6469 waits for ShareLock on transaction 668582701;
blocked by process 28763.
Process 28763 waits for ShareLock on transaction 668586325;
blocked by process 6469.
I believe the scenari
In the pg_locks system view there are 'pid', and 'relation' columns
(there's also a 'transaction' column but the pid will work just fine).
Do a look up on that table using the pids from the error messages. The
values for relation can be used to look up the table in pg_class.
Something like t
Terry Fielder <[EMAIL PROTECTED]> writes:
> I am getting this in my log file:
> 2006-10-05 16:06:23 [6469] ERROR: deadlock detected
> DETAIL: Process 6469 waits for ShareLock on transaction 668582701;
> blocked by process 28763.
> Process 28763 waits for ShareLock on transaction 66858632
Leonel Nunez wrote:
>> I think the arguments for keeping stuff inside the database are
>> (a) far easier to maintain transactional semantics for insert/delete,
>> and (b) easier to serve the data out to clients that aren't on the
>> same machine. You aren't going to find a performance win though.
On Oct 5, 2006, at 19:47 , DEV wrote:
I have seen several posts pertaining to the "overhead" difference
in storing
in a db table versus the file system. What is this difference?
Well, there's not much space overhead to speak of. I tested with a
bunch of JPEG files:
$ find files | wc -l
> Leonel Nunez wrote:
>>> I think the arguments for keeping stuff inside the database are
>>> (a) far easier to maintain transactional semantics for insert/delete,
>>> and (b) easier to serve the data out to clients that aren't on the
>>> same machine. You aren't going to find a performance win th
> And since it's a text file, can't someone fix it with $EDITOR?
I tried to edit the file, but I get the Input/Output error.
The recommendatation was to re-install the OS.
However, I DO NOT want to lose my database,
so I am tring to backup the database.
- Original Message -
From: "Ron J
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/05/06 18:10, Brian J. Erickson wrote:
>> And since it's a text file, can't someone fix it with $EDITOR?
>
> I tried to edit the file, but I get the Input/Output error.
>
> The recommendatation was to re-install the OS.
> However, I DO NOT want
On Oct 5, 2006, at 19:10 , Brian J. Erickson wrote:
And since it's a text file, can't someone fix it with $EDITOR?
I tried to edit the file, but I get the Input/Output error.
The recommendatation was to re-install the OS.
However, I DO NOT want to lose my database,
so I am tring to backup th
Hi,If the database had built-in functions to manipulate images (make a thumbnail, add text ont it.., make a montage of two pictures) and I could write something like select thumbnail(image_field, 100, 100) from images_tablethat would be a good reason to go the db route versus the filesystem route.
On Oct 6, 2006, at 01:29 , Jean-Christophe Roux wrote:
By the way, is it practical to set a bytea column (containing
pictures) as primary key? That would severely slow down many
operations I guess.
Why would you? It's possible, but completely impractical, since image
data typically exceed
> Hi,
> If the database had built-in functions to manipulate images (make a
> thumbnail, add text ont it.., make a montage of two pictures) and I could
> write something like
> select thumbnail(image_field, 100, 100) from images_table
> that would be a good reason to go the db route versus the file
"Leonel Nunez" <[EMAIL PROTECTED]> wrote:
>
> > If the database had built-in functions to manipulate images (make a
> > thumbnail, add text ont it.., make a montage of two pictures) and I could
> > write something like
> > select thumbnail(image_field, 100, 100) from images_table
> > that would be
AgentM wrote:
>
> On Oct 5, 2006, at 19:10 , Brian J. Erickson wrote:
>
>>> And since it's a text file, can't someone fix it with $EDITOR?
>>
>> I tried to edit the file, but I get the Input/Output error.
>>
O.k. hold on... are you getting any errors in /var/log/messages?
Joshua D. Drake
>>
> "Leonel Nunez" <[EMAIL PROTECTED]> wrote:
>>
>> > If the database had built-in functions to manipulate images (make a
>> > thumbnail, add text ont it.., make a montage of two pictures) and I
>> could
>> > write something like
>> > select thumbnail(image_field, 100, 100) from images_table
>> > tha
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/05/06 19:41, Bill Moran wrote:
> "Leonel Nunez" <[EMAIL PROTECTED]> wrote:
>>> If the database had built-in functions to manipulate images (make a
>>> thumbnail, add text ont it.., make a montage of two pictures) and I could
>>> write something l
7.4.3
And the records are gone from pg_locks, how much time after the
deadlock do I have before they are purged?
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
Tom Lane wrote:
Terry Fielde
FWIW, the company I work for stores its terrabytes of imagery on disk, using a
database to track them (spatial coordinates, metadata, location, etc.); I have
worked on projects in which we stored images in a database (blobs in Informix)
and it worked fine. Both approaches can have their merits.
Terry Fielder <[EMAIL PROTECTED]> writes:
> 7.4.3
Yoi. You need to think about an update.
> And the records are gone from pg_locks, how much time after the deadlock
> do I have before they are purged?
Approximately none --- pg_locks is a live view.
regards, tom lane
-
am Thu, dem 05.10.2006, um 17:45:36 -0300 mailte Hugo folgendes:
> Hi,
>
> is it possible to check for sqlstate inside a function , something like:
>
>loop
> fetch bla.
> if sqlstate = '02000' then
> exit;
> end if;
>
>end loop;
> if I
55 matches
Mail list logo
