I create 1 lot.
every lot is having 1 unit
every unit is having 100 measurement.
hence :
lot - 1 row entry
unit - 1 row entries
measurement - 100 row entries
Currently, I am having JOIN statement as follow (1st case)
SELECT measurement_type.value, measurement.value, measurement_unit
Alex - wrote:
checkpoint_segments = 32# in logfile segments, min 1,
16MB each
checkpoint_timeout = 30min # range 30s-1h
These parameters are not so interesting on their own. The important
thing to check is how often checkpoints are happening, and how much work
e
Hi Craig Ringer,
Really appreciate a lot for your advice! This at least has cleared my doubt,
which had been confused me for quite some time.
Thanks and Regards
Yan Cheng CHEOK
--- On Fri, 1/22/10, Craig Ringer wrote:
> From: Craig Ringer
> Subject: Re: [GENERAL] Extremely Slow Cascade Dele
Oh yeah, what's your swappiness setting (assuming you're running some
flavor of linux:
sysctl -a|grep swapp
should tell you. I set it to something small like 5 or so on db
servers. Default of 60 is fine for an interactive desktop but usually
too high for a server.
--
Sent via pgsql-general ma
On Thu, Jan 21, 2010 at 9:13 PM, Alex - wrote:
> Hi
> i am experience slow queries when i run some functions. I noticed the
> following entries in my server log.
> From this, can anyone tell me if I need to change some config parmeters?
> System has 18GB Memory
> shared_buffers = 4GB
Yan Cheng Cheok wrote:
> I try to create a following simple scenario, to demonstrate cascade delete is
> rather slow in PostgreSQL.
>
> Can anyone help me to confirm? Is this my only machine problem, or every
> PostgreSQL users problem?
>
> I create 1 lot.
> every lot is having 1 unit
> eve
Hii am experience slow queries when i run some functions. I noticed the
following entries in my server log.
>From this, can anyone tell me if I need to change some config parmeters?
System has 18GB Memoryshared_buffers = 4GB# min
128kBtemp_buffers = 32MB #
I try to create a following simple scenario, to demonstrate cascade delete is
rather slow in PostgreSQL.
Can anyone help me to confirm? Is this my only machine problem, or every
PostgreSQL users problem?
I create 1 lot.
every lot is having 1 unit
every unit is having 100 measurement.
hence
On Thursday 21 January 2010 5:57:14 pm Yan Cheng Cheok wrote:
> I have the following stored procedure return void.
>
> CREATE OR REPLACE FUNCTION sandbox()
> RETURNS void AS
> $BODY$DECLARE
> DECLARE me text;
> DECLARE he int;
> BEGIN
> he = 100;
> RAISE NOTICE 'he is %', he;
>
I have the following stored procedure return void.
CREATE OR REPLACE FUNCTION sandbox()
RETURNS void AS
$BODY$DECLARE
DECLARE me text;
DECLARE he int;
BEGIN
he = 100;
RAISE NOTICE 'he is %', he;
-- me = "Hello PostgreSQL";
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Fernando Schapachnik wrote:
I could play soft links tricks, but I'm afraid of paying the
FS-traversal penalty on each file access (is that right?).
Compared to everything else that goes into I/O, symlink traversal
overhead is pretty low.
So, any way of instructing PG (8.1 if that matters)
Florian Weimer wrote:
The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables
into account, presumably because the pg_relation_size does not reflect
that, either. I think this is a bit surprising. From a user
perspective, these are part of the table storage (I understand that
the
On Thu, Jan 21, 2010 at 11:54:32AM -0800, Alan Millington wrote:
> Today for the first time since upgrading to Postgres 8.4.1 I tried
> out part of the code which inserts some binary data into a table. The
> insert failed with the error "invalid byte sequence for encoding
> UTF8". That is odd, beca
2010/1/22 Gauthier, Dave :
> Is there a clever way to replace a single element in an array with another
> value?
>
>
>
> E.g.
>
>
>
> x = array[‘a’,’b’,’c’,’d’];
>
> I want to replace ‘b’ with ‘x’.
>
Not sure you can replace an array value with an array (which kind of is
what you're asking in your
Is there a clever way to replace a single element in an array with another
value?
E.g.
x = array['a','b','c','d'];
I want to replace 'b' with 'x'.
Thanks for any suggestions!
"Bob Pawley" writes:
> I am getting a strange result when using the following -
> Select fluid_id into fluidid
> from p_id.processes
> where new.pump1 = 'True'
> and old.pump1 = 'False'
> or old.pump1 is null;
> The fluid_id return is fine when there is a single row. However with two
> ro
Hi
I am getting a strange result when using the following -
Select fluid_id into fluidid
from p_id.processes
where new.pump1 = 'True'
and old.pump1 = 'False'
or old.pump1 is null;
The fluid_id return is fine when there is a single row. However with two rows,
and updating only one of the r
On Tue, Jan 19, 2010 at 4:49 PM, Andy Colson wrote:
> On 1/19/2010 3:39 PM, Andy Colson wrote:
>
>> On 1/19/2010 3:23 PM, Kynn Jones wrote:
>>
>>> I have a Perl CGI script (using DBD::Pg) that interfaces with a
>>> server-side Pg database. I'm looking for general
>>> guidelines/tools/strategies t
Exactly what I was looking for. Thanks!
On Jan 21, 2010, at 10:50 AM, Thomas Kellerer wrote:
Scott Frankel wrote on 21.01.2010 18:34:
Hi all,
Is there a query I can use to find the location of a db cluster?
SELECT name,
setting
FROM pg_settings
WHERE category = 'File Locations';
I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My
database is UTF8. My program code is written in Python, and to interface to
Postgres I use mxODBC 3.0 and the PostgreSQL Unicode driver PSQLODBCW.DLL
version 8.01.02.00 dated 31/01/2006.
Today for the first time since up
On Thu, 21 Jan 2010 13:49:45 -0500
Kynn Jones wrote:
> I have a table X with some column K consisting of
> whitespace-separated words. Is there some SELECT query that will
> list all these words (for the entire table) so that there's one
> word per row in the returned table? E.g. If the table X
Kynn Jones wrote:
> I have a table X with some column K consisting of whitespace-separated words.
>
> Is there some SELECT query that will list all these words (for the entire
> table) so that there's one word per row in the returned table? E.g. If the
> table X is
>
>K
> --
Kynn Jones wrote on 21.01.2010 19:49:
I have a table X with some column K consisting of whitespace-separated
words. Is there some SELECT query that will list all these words (for
the entire table) so that there's one word per row in the returned
table? E.g. If the table X is
K
---
Scott Frankel wrote on 21.01.2010 18:34:
Hi all,
Is there a query I can use to find the location of a db cluster?
SELECT name,
setting
FROM pg_settings
WHERE category = 'File Locations';
You need to be connected as the superuser (usually postgres)
--
Sent via pgsql-general mailing
I have a table X with some column K consisting of whitespace-separated
words. Is there some SELECT query that will list all these words (for the
entire table) so that there's one word per row in the returned table? E.g.
If the table X is
K
-
foo bar baz
quux fro
Hi all,
Is there a query I can use to find the location of a db cluster?
I've found a term that looks promising (\d+ pg_database;), but can't
seem to tease a directory path from it. The docs list several common
locations, but mine doesn't appear to be one of them.
Searching my local file
Elian Laura wrote:
i understand, but why my teacher wrote in his paper.."Probably the
most obvious case is a database engine where the user defines, at run
time, if a field is integer, char, float, etc. but, it is not
necessary to compile the program again. All this felxibility must be
...
On Jan 21, 2010, at 10:00 AM, Scott Frankel wrote:
>
> Hi all,
>
> Is there a query I can use to find the location of a db cluster?
>
> I've found a term that looks promising (\d+ pg_database;), but can't seem to
> tease a directory path from it. The docs list several common locations, but
Hi all,
Is there a query I can use to find the location of a db cluster?
I've found a term that looks promising (\d+ pg_database;), but can't
seem to tease a directory path from it. The docs list several common
locations, but mine doesn't appear to be one of them.
Searching my local file
Hi,
I have a big database on FS1, now almost full. Have space on FS2,
where I created a tablespace and moved every table and index to it.
Still, lots of space used on FS1. The problem is not pg_xlog, but
base:
# du -hs base/105658651/* | fgrep G
1,0Gbase/105658651/106377323
1,0Gbase/10
Dhimant Patel, 21.01.2010 17:40:
I'm a beginner Postgres user, and need quick hint from someone.
How could I know which users are currently connected to postgres instance?
http://www.postgresql.org/docs/current/static/monitoring.html
More precisely:
http://www.postgresql.org/docs/current/st
Dhimant Patel wrote:
> I'm a beginner Postgres user, and need quick hint from someone.
>
>
> How could I know which users are currently connected to postgres instance?
You can use pg_stat_activity:
select * from pg_stat_activity
Or, if you need only the usernames:
select usename from pg_st
I'm a beginner Postgres user, and need quick hint from someone.
How could I know which users are currently connected to postgres instance?
Thanks,
DP.
The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables
into account, presumably because the pg_relation_size does not reflect
that, either. I think this is a bit surprising. From a user
perspective, these are part of the table storage (I understand that
the indices might be a diffe
On Thu, Jan 21, 2010 at 3:46 AM, Yan Cheng Cheok wrote:
> table measurement will have a *lot* of row (millions). I want to speed up
> write and read access. Hence, I use partition technique.
>
> CREATE TABLE measurement_y2006m02 (
> CHECK ( date >= DATE '2006-02-01' AND date < DATE '2006-03-01
Make sense to me. Thanks for the advice. I will try that out.
Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/21/10, Vick Khera wrote:
> From: Vick Khera
> Subject: Re: [GENERAL] Partitioning on Table with Foreign Key
> To: "Yan Cheng Cheok"
> Cc: pgsql-general@postgresql.org
> Date: Thursd
On 01/21/10 16:09, John Mitchell wrote:
So am I to presume that the current stable version of postgres (before
8.5) does require extra locking?
There is currently (before 8.5) no official replication mechanism in
PostgreSQL. There are some 3rd party implementations, for which
information can
depends on what sort of replication you are going to use really.
Most are based on triggers. So they have a bit more impact on
performance. As far as locking goes, postgresql is very conservative
with locks, ie - it won't abuse them, unlike for instance mysql.
So I don't know whether you are just w
I have seen no difference in performance. Now, if you want large memory for
a DB server, and you should, 64 is the way to go.
I'm currently running CentOS 5 64-Bit vm's for the SaaS app I support.
Works great on ESX 4U1.
--
Larry Rosenman http://www.lerctr.org/~ler
Phon
So am I to presume that the current stable version of postgres (before 8.5)
does require extra locking?
John
2010/1/21 Grzegorz Jaśkiewicz
> On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell
> wrote:
> > Hi,
> >
> > In reading the documentation it states that the SQL dump backup does not
> > bloc
On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell wrote:
> Hi,
>
> In reading the documentation it states that the SQL dump backup does not
> block other operations on the database while it is working.
yes, pg_dump opens serializable transaction thus guarantees data to be
the exact snapshot (as oppos
Hi,
In reading the documentation it states that the SQL dump backup does not
block other operations on the database while it is working.
I presume that while a restore is occurring that no reads or updates are
allowed against the restored database.
What locking mechanism is used for Master-Slave
On Thursday 21 January 2010 13.01:29 Magnus Hagander wrote:
> > Really? With ONLY 2Gb? Why? What is the performance improvement, with
> > 64Bit all pointers and so on needs more memory so i'm expecting lesser
> > memory for the data.
>
I'm not in any way a performance expert, but IIRC 32 bit Linu
2010/1/21 Bjørn T Johansen :
> I also thought that the fact the 64bit system can move more data in parallell
> would also make the system faster
That's true ad the chip level (registers and cache).
Anything else depends on the surrounding hardware (design and implementation).
A 32bit system
On Thu, 21 Jan 2010 13:01:29 +0100
Magnus Hagander wrote:
> On Thu, Jan 21, 2010 at 12:45, A. Kretschmer
> wrote:
> > In response to Magnus Hagander :
> >> 2010/1/21 Bjørn T Johansen :
> >> > We are going to be setting up a PostgreSQL server on a guest under
> >> > VMWare ESX 4... Is there any
On Thu, Jan 21, 2010 at 12:45, A. Kretschmer
wrote:
> In response to Magnus Hagander :
>> 2010/1/21 Bjørn T Johansen :
>> > We are going to be setting up a PostgreSQL server on a guest under VMWare
>> > ESX 4... Is there any performance improvement by choosing 64bits Linux over
>> > 32bits Linux
In response to Magnus Hagander :
> 2010/1/21 Bjørn T Johansen :
> > We are going to be setting up a PostgreSQL server on a guest under VMWare
> > ESX 4... Is there any performance improvement by choosing 64bits Linux over
> > 32bits Linux as the guest OS or is it almost the same?
>
> How much res
On Thu, 21 Jan 2010 10:43:31 +0100
Magnus Hagander wrote:
> 2010/1/21 Bjørn T Johansen :
> > We are going to be setting up a PostgreSQL server on a guest under VMWare
> > ESX 4... Is there any performance improvement by choosing 64bits Linux
> > over 32bits Linux as the guest OS or is it almost
2010/1/21 Vincenzo Romano :
> And, BTW:
> EXECUTE 'INSERT INTO '||partition-table-name||' SELECT $1.*' USING NEW;
won't work on 8.3 where I need it however :)
--
GJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgre
2010/1/21 Bjørn T Johansen :
> We are going to be setting up a PostgreSQL server on a guest under VMWare ESX
> 4... Is there any performance improvement by choosing 64bits Linux over
> 32bits Linux as the guest OS or is it almost the same?
How much resources do you plan to give the machine?
If y
2010/1/21 Grzegorz Jaśkiewicz :
> http://www.pubbs.net/pgsql/201001/16503/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Grzegorz,
Thanks for the reference, which officially i
http://www.pubbs.net/pgsql/201001/16503/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I am referring to
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
I have the follow table :
table lot
=
id | date |
1 2010-01-19 13:53:57.713
2 2010-01-20 11:34:11.856
table measurement
=
id | fk_lot_id |
12
22
32
42
52
62
72
53 matches
Mail list logo
