Steve Atkins wrote:
The one place where Compression is an immediate benefit is the wire.
It is easy to forget that one of our number one bottlenecks (even at
gigabit) is the amount of data we are pushing over the wire.
Wouldn't "ssl_ciphers=NULL-MD5" or somesuch give zlib compression over
th
On Oct 29, 2008, at 10:43 PM, Joshua D. Drake wrote:
Steve Atkins wrote:
On Oct 29, 2008, at 9:50 PM, Grant Allen wrote:
One other thing I forgot to mention: Compression by the DB trumps
filesystem compression in one very important area -
shared_buffers! (or buffer_cache, bufferpool or
Steve Atkins wrote:
On Oct 29, 2008, at 9:50 PM, Grant Allen wrote:
One other thing I forgot to mention: Compression by the DB trumps
filesystem compression in one very important area - shared_buffers!
(or buffer_cache, bufferpool or whatever your favourite DB calls its
working memory for
On Oct 29, 2008, at 9:50 PM, Grant Allen wrote:
One other thing I forgot to mention: Compression by the DB trumps
filesystem compression in one very important area - shared_buffers!
(or buffer_cache, bufferpool or whatever your favourite DB calls its
working memory for caching data). B
Firstly, thank you very much to all advice that has been passed
through. These groups have thought me many new tricks and I wish all
of you the best.
Now, I'm trying to create a schema for mobile phone custodian and
billing system. Any advice would be greatly appreciated, I've
included a schema
Ron Mayer wrote:
Grant Allen wrote:
...warehouse...DB2...IBM is seeing typical storage savings in the
40-60% range
Sounds about the same as what compressing file systems claim:
http://opensolaris.org/os/community/zfs/whatis/
"ZFS provides built-in compression. In addition to
reducing space
Hi Ho!
--- On Thu, 10/30/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> Because it'd be extremely difficult to do otherwise
> (given the way that
> CREATE DATABASE works)
Understood.
> and it's not at all clear that
> it'd be a good
> idea anyway.
Can it be cleared up by looking at the kind of secu
TOO MANY THANKS RAY!
I have got success in doing replication to an existing database. I did test on
a dummy databases. But I am sure that I can implement to live project.
Regards,
Abdul Rehman.
Eus <[EMAIL PROTECTED]> writes:
> Why does Postgresql by default assign the ownership of the public schema of a
> DB to "postgres" instead of the owner of the DB itself?
Because it'd be extremely difficult to do otherwise (given the way that
CREATE DATABASE works) and it's not at all clear that i
Klint Gore <[EMAIL PROTECTED]> writes:
> Surprisingly, '2008-10-27%' casts to a date in 8.3.3.
Yeah, the datetime input code is pretty willing to overlook unexpected
punctuation. There are enough odd formats out there that I'm not sure
tightening it up would be a good idea.
Scott Marlowe wrote:
On Wed, Oct 29, 2008 at 5:04 PM, Irene Barg <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is the following query a valid use of the 'wildcard' in (='2008-10-27%')?
>
>> [EMAIL PROTECTED] arcsoft]$ psql metadata
>> Password: Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
>
Grant Allen wrote:
...warehouse...DB2...IBM is seeing typical
storage savings in the 40-60% range
Sounds about the same as what compressing file systems claim:
http://opensolaris.org/os/community/zfs/whatis/
"ZFS provides built-in compression. In addition to
reducing space usage by 2-3x, co
Hi Ho!
As a new user of Postgresql 8.3.3, I came across this common error message when
restoring a database previously dumped from another machine:
15: ERROR: must be owner of schema public
when it came to this line in the dump file:
COMMENT ON SCHEMA public IS 'Standard public schema';
And,
On Wed, Oct 29, 2008 at 5:04 PM, Irene Barg <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is the following query a valid use of the 'wildcard' in (='2008-10-27%')?
>
>> [EMAIL PROTECTED] arcsoft]$ psql metadata
>> Password: Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
>>
>> metadata=# SELECT
"Thom Brown" <[EMAIL PROTECTED]> writes:
> Actually I did "ps aux | grep post" just to cover all bases, but still
> nothing.. except of course the grep itself.
The overwhelming impression from here is of a seriously brain-dead
startup script. It's spending all its effort on being chatty and none
I agree with the concept. The issue is the application is an open ended
query tool. So, pretty much whatever they feel like entering is valid. I
totally understand the indexes aren't very selective. I guess I just don't
know what the next step is. There aren't a lot of assumptions I can make
a
Hi,
Is the following query a valid use of the 'wildcard' in (='2008-10-27%')?
[EMAIL PROTECTED] arcsoft]$ psql metadata
Password:
Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
metadata=# SELECT * FROM viewspace.siap AS t WHERE t."startDate"='2008-10-27%'
AND t.prop_id LIKE '%'
Tom Lane wrote:
=?utf-8?Q?=E5=B0=8F=E6=B3=A2_=E9=A1=BE?= <[EMAIL PROTECTED]> writes:
[ snip a lot of marketing for SQL Server ]
I think the part of this you need to pay attention to is
Of course, nothing is entirely free, and this reduction in space and
time come at the expense of
From: Kevin Galligan [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2008 4:34 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Slow query performance
Sorry for the lack of detail. Index on both state and age. Not a
clustered on both as the queries are fa
There was a number of code mistakes in my examples as i was just doing
it off the top of my head, just went through it and got it all working.
I had to change the function around as it was double dipping accounts
just run this and it does work.
Grzegorz Jaśkiewicz wrote:
I use postgresql on MBP, current head, for testing and development. Just
from sources, it won't bite :)
you just have to add user postgres to your system, place $PGDATA
wherever you feel you should, and you're done.
Yes. I actually started using Nix from Slackwa
On Wed, Oct 29, 2008 at 9:41 PM, Ravi Chemudugunta
<[EMAIL PROTECTED]> wrote:
> Thanks for the replies.
>
> I probably should have explained what it is that I am trying to do.
>
> I am trying to create a command line interface to the debugger rather than
> using the GUI (that'll eventually plug int
From: Kevin Galligan [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2008 3:16 PM
To: Dann Corbit
Subject: Re: [GENERAL] Slow query performance
Columns are as follows:
account | integer |
city | character varying(20) |
zip | character(5) |
dincome | ch
An example of a slow query is...
select count(*) from bigdatatable where age between 22 and 40 and state =
'NY';
explain analyze returned the following...
Aggregate (cost=5179639.55..5179639.56 rows=1 width=0) (actual
time=389529.895..389529.897 rows=1 loops=1)
-> Bitmap Heap Scan on bigda
On Wed, Oct 29, 2008 at 2:18 PM, Kevin Galligan <[EMAIL PROTECTED]> wrote:
> I'm approaching the end of my rope here. I have a large database.
> 250 million rows (ish). Each row has potentially about 500 pieces of
> data, although most of the columns are sparsely populated.
A couple of notes her
On Wed, Oct 29, 2008 at 2:59 PM, WaGathoni <[EMAIL PROTECTED]> wrote:
> failing with an error to the effect that that that
> coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY
> clause and what is is the recommended course of action.
Generally the solution in postgresql i
Thanks for the replies.
I probably should have explained what it is that I am trying to do.
I am trying to create a command line interface to the debugger rather than
using the GUI (that'll eventually plug into a sort of testing harness) - so
I am needing to figure out how it actually works. In
Justin was recommending a solution to the Chart of Accounts Problem
posted by jamhitz:
MQUOTE>
One has you chart of Accounts
Create table coa (
coa_id serial not null,
parent_id int not null default 0,
doIhaveChildren boolean default false
account_name text null )
prima
On 29/10/2008 20:11, x asasaxax wrote:
>I need a function to select the first day of the week. For example
> giving today´s date(29/10/2008).
select 'Monday';
;-)
Ray.
--
Raymond O'Donnell, Director of Music, Galway Cathedr
I'm approaching the end of my rope here. I have a large database.
250 million rows (ish). Each row has potentially about 500 pieces of
data, although most of the columns are sparsely populated.
What I'm trying to do is, essentially, search for sub-sets of that
data based on arbitrary queries of
Hello
try
CREATE OR REPLACE FUNCTION public.fdow(date)
RETURNS date
LANGUAGE sql
IMMUTABLE STRICT
AS $function$ select $1 - extract(dow from $1)::int $function$
postgres=# select fdow(current_date);
fdow
2008-10-26
(1 row)
regards
Pavel Stehule
2008/10/29 x asasaxax <[EMAI
Hi,
I need a function to select the first day of the week. For example
giving today´s date(29/10/2008).
Can anyone help´s me?
Thanks!
Noah Freire wrote:
<2008-10-29 11:09:03.453 PDT>DEBUG: 0: accounts: vac: 16697969
(threshold 650), anl: 16697969 (threshold 12048)
<2008-10-29 11:09:05.610 PDT>DEBUG: 0: accounts: vac: 16699578
(threshold 650), anl: 16699578 (threshold 12048)
<2008-10-29 11:10:03.563 PDT>
Hello,
I have a table (accounts) with 600,000,000 rows. A heavy high-concurrent
workload that makes mostly updates on this table generates a lot of dead
tuples in its run, which is expected due to MVCC.
The problem is that even though autovacuum is enabled, the autovacuum worker
does not vacuum th
Actually I did "ps aux | grep post" just to cover all bases, but still
nothing.. except of course the grep itself.
On Wed, Oct 29, 2008 at 6:38 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Wed, Oct 29, 2008 at 9:43 AM, Thom Brown <[EMAIL PROTECTED]> wrote:
>> Hi,
>> * Forcing it to shutdown
On Wed, Oct 29, 2008 at 9:43 AM, Thom Brown <[EMAIL PROTECTED]> wrote:
> Hi,
> * Forcing it to shutdown which leads to a recover-run on next startup.
> pg_ctl: PID file "/var/lib/postgresql/8.3/data/postmaster.pid" does not exist
> Is server running?
> * Forced shutdown failed!!! Something is wro
On Wed, Oct 29, 2008 at 10:09 AM, 小波 顾 <[EMAIL PROTECTED]> wrote:
>
> Data Compression
>
> The new data compression feature in SQL Server 2008 reduces the size of
> tables, indexes or a subset of their partitions by storing fixed-length data
> types in variable length storage format and by reducing
I've been tasked with maintaining a set of postgres databases created by my
predecessor in this position. The original databases several years back were
version 8.1.3, and used the tsearch2 functions to enable some client-program
searches.
We've recently begun preparing to shift to 8.3 (I beli
Permissions are identical to live. I've checked the /tmp folder for a
PID reference, but doesn't exist in live or dev.
What do you mean by "variables"? How can I check?
I only have one postgresql database cluster on each server.
With regards to the config causing memory problems, the specs of
I can imagine my big stats tables , with 300-400M rows, all big ints, that
- mostly - require that sort of length. Gain, none, hassle 100%.
小波 顾 wrote:
Data Compression MSSQL 2008 technots . Your results depend on
your workload, database, and hardware
Sounds cool but i wonder what real world results are??
For IO bound systems lots of pluses
but for CPU bound workloads it would suck
=?utf-8?Q?=E5=B0=8F=E6=B3=A2_=E9=A1=BE?= <[EMAIL PROTECTED]> writes:
> [ snip a lot of marketing for SQL Server ]
I think the part of this you need to pay attention to is
> Of course, nothing is entirely free, and this reduction in space and
> time come at the expense of using CPU cycles.
We alr
Tom Lane escribió:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > I can confirm that when the pager is open, psql does not resize
> > properly. Maybe psql is ignoring signals while the pager is open, or
> > something.
>
> If the pager is running, psql's not going to do anything anyway, no?
> W
Data Compression
The new data compression feature in SQL Server 2008 reduces the size of tables,
indexes or a subset of their partitions by storing fixed-length data types in
variable length storage format and by reducing the redundant data. The space
savings achieved depends on the schema and t
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> How do you know that the pager is in the same process group? Is the
> process group something that's inherited automatically on fork()?
It certainly should be.
> I can confirm that when the pager is open, psql does not resize
> properly. Maybe psql i
Hi,
Did you check permissions?
Do the pid files exist?
What variables are set?
Regards,
Serge Fonville
On Wed, Oct 29, 2008 at 4:43 PM, Thom Brown <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I've got a development virtual server which matches live exactly
> except for the fact that Postgres is running
Thom Brown wrote:
> Hi,
>
> I've got a development virtual server which matches live exactly
> except for the fact that Postgres is running on a different port
What do you mean by "virtual server"? And does it affect definitions of
localhost or shared-memory allocation?
> which
> is not used by
Gregory Stark escribió:
> Hm, this Bash FAQ seems to indicate this shouldn't be a problem -- the whole
> process group is supposed to get the window size. Psql isn't doing the job
> control stuff the FAQ entry talks about so the pager ought to be in the same
> process group. So I'm puzzled.
How d
Hi,
I've got a development virtual server which matches live exactly
except for the fact that Postgres is running on a different port which
is not used by anything else. Postgres was running fine until I
updated postgresql.conf to enhance logging and make better use of
system resources.
Here's t
2008/10/29 小波 顾 <[EMAIL PROTECTED]>
> 1. Little integers of types take 8 bytes in the past now only take 4 or 2
> bytes if there are not so large.
>
So what actually happen if I have a table with few mills of values that fit
in 2 bytes, but all of the sudent I am going to add another column with
s
Philip W. Dalrymple napisal 29.10.2008 15:26:
set_config('session.sessionid','23',false);
I validate strings with regexp. This expression returns true:
select '23' ~'^-{0,1}[0-9]+$';
If it's true - you can cast it safely:
select cast('23' as integer)
--
Regards,
Tomasz Myrta
--
Sent via p
Sorry for following up so late, actually I mean compression features like what
other commercial RDBMS have, such as DB2 9.5 or SQL Server 2008. In those
databases, all data types in all tables can be compressed, following are two
features we think very useful:
1. Little integers of types take 8
On 29/10/2008 12:55, Abdul Rahman wrote:
> 11. Got SUCCESS up to this point. Now I think this is *pgbench *which
> is responsible for replicating tables.
No - it's Slony which is responsible for the replication. pgbench is
just executing lots of SQL commands - SELECTs, UPDATEs, INSERTs,
On Wed, Oct 29, 2008 at 12:11:43PM +0200, Allan Kamau wrote:
> I am however unable to do the same successfully (the Java code simply
> hangs, probably as a result of the second psql not getting the input to
> it) from Java code using objects of ProcessBuilder and Process.
Why don't you use the J
Brent Austin <[EMAIL PROTECTED]> writes:
> I could swear that is what I did..or is it not? That is why I sent a
> copy/paste of that mess from my terminal:
> it showed that I do have PG_CONFIG installed and it showed I did set my path.
> That is why I am asking help because configure is sayin
I have a trigger function that, for every Update or Insert of
a table inserts a row in a "audit" table that contains some trace
data, the key of the first table and the changed columns (unchanged
cols are NULL) I have this working with a plpgsql function execpt
for one of the trace fields (called t
I could swear that is what I did..or is it not? That is why I sent a
copy/paste of that mess from my terminal:
it showed that I do have PG_CONFIG installed and it showed I did set my path.
That is why I am asking help because configure is saying I did neither when it
plainly shows I did (to
On Tue, 28 Oct 2008, Jason Long wrote:
I also have to ship them off site using a T1 so setting the time to
automatically switch files will just waste bandwidth if they are still going
to be 16 MB anyway.
The best way to handle this is to clear the unused portion of the WAL file
and then comp
>
> If there's no update activity on that table, this is to
> be expected.
>
Hmm, there is activity on the table, so I'm guessing I've not got autovacuumm
tuned aggressively enough.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
The
detail of my work is as under:
As I
mentioned earlier that platform is windows xp and using postgres 8.2 in which
Slony- I is included. And all databases are at localhost.
After
reading the document entitled “Replicating Your First Database” which is for
Linux I optimized the instr
Brent Austin <[EMAIL PROTECTED]> writes:
> Configure still failsI've tried everything I can figure
> Last login: Wed Oct 29 02:58:10 on ttys000
> client-6X-1XX-17-XX4:~ brent1a$ cd /psqlodbc-08.03.0300
> client-6X-1XX-17-XX4:psqlodbc-08.03.0300 brent1a$ sudo ./configure
> Password:
> check
On Mon, Oct 27, 2008 at 3:59 AM, wstrzalka <[EMAIL PROTECTED]> wrote:
> I'm using psql mainly in putty window.
>
> I have a problem while resizing the window.
> When changing the window size (and those chars per row) psql output
> becomes mess, the only rescue is to exit and run the psql again. It
Brent Austin wrote:
> Configure still failsI've tried everything I can figure
[...]
> configure: error: pg_config not found (set PG_CONFIG environment variable)
It's quite simple:
- Find out where pg_config is.
- If you don't have it, install the appropriate package.
- Make sure it's i
Glyn Astill <[EMAIL PROTECTED]> writes:
> I've noticed age(relfrozenxid) of some of our tables approaching
> vacuum_freeze_min_age, am I right in thinking this is nothing to worry about,
> autovacuum will just get invoked for those tables?
If there's no update activity on that table, this is to
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
It was easier then I thought.
I built a custom function because I wanted each field value to be on its
own line.
create or replace function textsum(text,text) returns text as
$$
select coalesce($1,'') || case when $1 is null then '' else case
Bruce McAlister <[EMAIL PROTECTED]> writes:
>> Bizarre ... I've never heard of a Unix system that didn't consider that
>> a default place to look. Unless this is a 64-bit machine and uuid
>> should have installed itself in /usr/lib64?
> It is a rather peculiar issue, I also assumed that it would
Hi chaps,
I've noticed age(relfrozenxid) of some of our tables approaching
vacuum_freeze_min_age, am I right in thinking this is nothing to worry about,
autovacuum will just get invoked for those tables?
Even if it isn't, should I be tuning autovacuum so that those tables should
have been vacu
am Wed, dem 29.10.2008, um 13:20:59 +0200 mailte Sim Zacks folgendes:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Is there any way (aside from creating a new aggregate type) to sum the
> text in a text field. I would like to group on a query and concatenate
> all the values of a specifi
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Is there any way (aside from creating a new aggregate type) to sum the
text in a text field. I would like to group on a query and concatenate
all the values of a specific field in the result set.
This is a common practice and currently I find myself w
Gregory Stark <[EMAIL PROTECTED]> writes:
> Could you define "messed up"?
>
> What I see is that the query output is formatted correctly but readline still
> thinks the screen is the old size. (This is in CVS HEAD -- this code was
> definitely different in 8.3 and before so the behaviour may be
Ok Ray!
I will send the detail of my work soon.
Thanks to All participants.
The link of the document, entitled "Replicating Your First Database" is as
under:
http://slony1.projects.postgresql.org/slony1-1.2.6/doc/adminguide/firstdb.html
On 29/10/2008 11:00, Abdul Rahman wrote:
> But, the implementation on existing database is not working for the
> tables already exists in the database. The replication is being only
> among four tables created via pgbench tool (accounts, history, branches,
> tellers).
>
> whats going on!
You'll
I followed the document entitled entitled "Replicating your first database" and
got SUCCESS
In implementing the given example.
But, the implementation on existing database is not working for the tables
already exists in the database. The replication is being only among four tables
created via
wstrzalka <[EMAIL PROTECTED]> writes:
> On 27 Paź, 13:16, [EMAIL PROTECTED] (Sam Mason) wrote:
>> On Mon, Oct 27, 2008 at 01:59:42AM -0700, wstrzalka wrote:
>>
>> > When changing the window size (and those chars per row) psql output
>> > becomes mess, the only rescue is to exit and run the psql a
On Tue, Oct 28, 2008 at 11:41 PM, Tom Allison <[EMAIL PROTECTED]> wrote:
> I was using macports but got into a cluster-F on versions and multiple
> installs. After a spell I had all four versions 8.0 - 8.3 installed in
> order to use postgres, ruby, perl, and rails together.
I use apple's ruby, b
Thanks Reid, Sam and others. Indeed the oversight was once again at my
end. As rightfully pointed out I was using tab as a delimiter where the
copy command "COPY abc FROM STDIN WITH CSV HEADER" expects a comma
character and will ignore the first line.
It does work appropriately when I pipe the
On Wed, Oct 29, 2008 at 6:48 AM, Ravi Chemudugunta
<[EMAIL PROTECTED]> wrote:
> hi all,
>
> has anyone here heard of / used:
>
> http://pgfoundry.org/projects/edb-debugger/
>
> This allows one to debug code, using breakpoints, single stepping etc. which
> seems really great but I haven't figured ou
I use postgresql on MBP, current head, for testing and development. Just
from sources, it won't bite :)
you just have to add user postgres to your system, place $PGDATA wherever
you feel you should, and you're done.
On 28 okt 2008, at 23.41, Tom Allison wrote:
I can get postgresql installed in three flavors:
EnterpriseDB has a dmg package for Mac.
macports has their own package.
fink also has their own package.
You also have the fourth, most delicious flavor: build it yourself;
PostgreSQL compiles nicel
Configure still failsI've tried everything I can figure
Last login: Wed Oct 29 02:58:10 on ttys000
client-6X-1XX-17-XX4:~ brent1a$ cd /psqlodbc-08.03.0300
client-6X-1XX-17-XX4:psqlodbc-08.03.0300 brent1a$ sudo ./configure
Password:
checking for a BSD-compatible install... /usr/bin/instal
>
> Bizarre ... I've never heard of a Unix system that didn't consider that
> a default place to look. Unless this is a 64-bit machine and uuid
> should have installed itself in /usr/lib64?
>
It is a rather peculiar issue, I also assumed that it would check the
standard locations, but I thought
82 matches
Mail list logo
