Reg Me Please <[EMAIL PROTECTED]> schrieb:
> How can I "Increasing the statistics target for the larger table"?
> I'ìm sorry for asking, but I'm not that deep into RDBMS.
alter table alter column SET STATISTICS ;
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a complet
Il Friday 26 October 2007 15:18:04 Tom Lane ha scritto:
> Reg Me Please <[EMAIL PROTECTED]> writes:
> >>> (cost=3.95..382140.91 rows=274709 width=91) (actual
> >>> time=1.929..57713.305 rows=92 loops=1)
> >>> Hash Cond: (t_dati.camp_id = t_campi.camp_id)
>
> I think the reason it doesn't want to us
On 10/26/07, brian <[EMAIL PROTECTED]> wrote:
>
>
> Pat Maddox wrote:
> > On 10/26/07, brian <[EMAIL PROTECTED]> wrote:
> >
> >>>
> >>
> >>
> >>SELECT * FROM posts ORDER BY root_id, id;
> >>
> >>brian
> >>
> >>---(end of broadcast)---
> >>TIP 4: Have
On Oct 26, 2007, at 4:46 PM, Jeff Davis wrote:
On Fri, 2007-10-26 at 16:31 -0500, Erik Jones wrote:
In the absence of activity, WAL are archived every checkpoint_timeout
seconds. archive_timeout is how long postgres will wait for the
archive_command to return before declaring it failed.
ht
Jeff Davis <[EMAIL PROTECTED]> writes:
> I would think if the current location does not end in all zeros, you
> should expect a new WAL segment to be archived soon. Although this
> assumes that an idle database would not advance that location at all,
> and I'm still trying to understand Tom's propo
On Fri, 2007-10-26 at 18:06 -0500, Kevin Grittner wrote:
> Hmmm... We would actually prefer to get the WAL file at the
> specified interval. We have software to ensure that the warm
> standby instances are not getting stale, and that's pretty simple
> with the current behavior. We don't have a b
On Fri, 2007-10-26 at 18:47 -0400, Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
> > What's happening is that you have a checkpoint_timeout of 5 minutes, and
> > that checkpoint must write a checkpoint record in the WAL, prompting the
> > archiving.
>
> > If you want it to happen less f
Jeff Davis <[EMAIL PROTECTED]> writes:
> Keep in mind that even in the current system, your configuration is
> variable based on the checkpoint_timeout setting.
Yeah, and he has to keep this less than archive_timeout in order for
it to work the way he wants, which is probably not good for performa
On Fri, 2007-10-26 at 18:06 -0500, Kevin Grittner wrote:
> >>> On Fri, Oct 26, 2007 at 5:47 PM, in message <[EMAIL PROTECTED]>,
> Tom Lane <[EMAIL PROTECTED]> wrote:
>
> > And after
> > each archive_timeout, we test to see if we need to flush the current WAL
> > segment out to the archive; whic
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> On Fri, Oct 26, 2007 at 5:47 PM, in message <[EMAIL PROTECTED]>,
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> And after
>> each archive_timeout, we test to see if we need to flush the current WAL
>> segment out to the archive; which is determined by whet
>>> On Fri, Oct 26, 2007 at 5:47 PM, in message <[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> wrote:
> And after
> each archive_timeout, we test to see if we need to flush the current WAL
> segment out to the archive; which is determined by whether the write
> pointer is currently exactly a
Jeff Davis <[EMAIL PROTECTED]> writes:
> What's happening is that you have a checkpoint_timeout of 5 minutes, and
> that checkpoint must write a checkpoint record in the WAL, prompting the
> archiving.
> If you want it to happen less frequently, it's often safe to have
> checkpoint timeout set to
On Oct 26, 2007, at 16:06 , Tom Hart wrote:
What I'd like to do is add a field is_ok and then use sql or php
(or whatever else, if there's an easier way) to determine the field
datatype (text, numeric, bool, etc.) and then use some regex or
something along those lines to attempt to verify
> ... to determine the field datatype (text, numeric,
> bool, etc.)
I am not sure if this helps, but you can dig around in the system
files (pg_catalog.*), and probably write a query that gets the types
of every column in the data table you want to insert to.
Not a big push, but maybe it will get
Replying to yourself is so depressing...
Anyway, I managed to google myself into a solution, I just wanted to
share it with the list in case anybody else was interested.
Using the INFORMATION SCHEMA and a query like
SELECT column_name, data_type FROM information_schema.columns WHERE
table_nam
On Fri, 2007-10-26 at 16:31 -0500, Erik Jones wrote:
> In the absence of activity, WAL are archived every checkpoint_timeout
> seconds. archive_timeout is how long postgres will wait for the
> archive_command to return before declaring it failed.
>
http://www.postgresql.org/docs/current/stat
On Fri, 2007-10-26 at 15:08 -0600, Brian Wipf wrote:
> I have a test PG 8.2.5 installation that has been left idle with no
> connections to it whatsoever for the last 24 hours plus. WALs are
> being archived exactly 5 minutes apart, even though archive_timeout
> is set to 60. Is this the expe
On Oct 26, 2007, at 4:08 PM, Brian Wipf wrote:
I have a test PG 8.2.5 installation that has been left idle with no
connections to it whatsoever for the last 24 hours plus. WALs are
being archived exactly 5 minutes apart, even though archive_timeout
is set to 60. Is this the expected behavio
Hey guys. This is probably a n00b question, but here goes anyway.
I have a set of csv files that I COPY t o a number of import tables
(same field layout as live tables, but with all datatypes 'text') then
use an INSERT INTO ... SELECT FROM statement to transfer the rows over
to the live table
I have a test PG 8.2.5 installation that has been left idle with no
connections to it whatsoever for the last 24 hours plus. WALs are
being archived exactly 5 minutes apart, even though archive_timeout
is set to 60. Is this the expected behavior for a database with no
changes?
Brian
---
On May 24, 2007 01:02:42 pm John D. Burger wrote:
> Tilmann Singer wrote:
> > We are using this data which seems to be fairly extensive and
> > accurate, and is free:
> >
> > http://earth-info.nga.mil/gns/html/gis_countryfiles.htm
>
> We use that, but it is only non-US, so we combine it with this:
Tom Lane wrote:
> Matthew Hixson <[EMAIL PROTECTED]> writes:
> > Instead of:
> > DEBUG: insert into foo (name) values ($1);
> > DETAIL: parameters: $1 = 'stan'
> > I'd like to see:
> > DEBUG: insert into foo (name) values ('stan');
>
> Don't hold your breath. That would require a great deal more
"Dave Page" wrote:
>> --- Original Message ---
>> From: Rainer Bauer <[EMAIL PROTECTED]>
>> To: pgsql-general@postgresql.org
>> Sent: 26/10/07, 18:09:26
>> Subject: Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
>>
>> Dave could you add that it's the third parameter of
All you're doing is picking random =subsequences= from the same
permutation of the original data.
You have some good points in your reply. I am very much aware of this
non-random behavior you point out for the "static random-value column"
approach but at least it is fast, which is a requiremen
Matthew Hixson <[EMAIL PROTECTED]> writes:
> Instead of:
> DEBUG: insert into foo (name) values ($1);
> DETAIL: parameters: $1 = 'stan'
> I'd like to see:
> DEBUG: insert into foo (name) values ('stan');
Don't hold your breath. That would require a great deal more smarts
in the logging code (and
Pat Maddox wrote:
On 10/26/07, brian <[EMAIL PROTECTED]> wrote:
SELECT * FROM posts ORDER BY root_id, id;
brian
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Okay, b
Ok i gave up just keeping this in rules so i did this
CREATE OR REPLACE RULE version AS ON UPDATE TO
vmain
DO INSTEAD
select version2
(OLD.id,NEW.title,OLD.parent_id,NEW.public,OLD.public);
CREATE OR REPLACE FUNCTION version2(int,varchar,int,bool,bool)
RETURNS VOID
LANGUAGE 'plpgsql' AS '
On 10/26/07, brian <[EMAIL PROTECTED]> wrote:
> Pat Maddox wrote:
> > Right now my table looks like this:
> >
> > posts
> > id
> > body
> > parent_id
> > root_id
> > created_at
> >
> > so if I've got the records
> >
> > (1, 'post 1', NULL, 1, '4pm')
> > (2, 'post 2', NULL, 2, '8pm')
> > (
Pat Maddox wrote:
Right now my table looks like this:
posts
id
body
parent_id
root_id
created_at
so if I've got the records
(1, 'post 1', NULL, 1, '4pm')
(2, 'post 2', NULL, 2, '8pm')
(3, 'post 3', 1, 1, '6pm')
(4, 'post 4', 1, 1, '5pm')
(5, 'post 5', 4, 1, '6pm')
(6, 'post 6', NULL,
With 8.2.4 is it possible to get Postgres to log incoming SQL
statements the same as they look when written? Instead of:
DEBUG: insert into foo (name) values ($1);
DETAIL: parameters: $1 = 'stan'
I'd like to see:
DEBUG: insert into foo (name) values ('stan');
This would be extremely helpful
> --- Original Message ---
> From: Rainer Bauer <[EMAIL PROTECTED]>
> To: pgsql-general@postgresql.org
> Sent: 26/10/07, 18:09:26
> Subject: Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
>
> Dave could you add that it's the third parameter of the "SharedSection" stri
On 10/26/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Oct 26, 2007, at 10:56 , Pat Maddox wrote:
>
> > A bunch of options so far...but there's really no way to do this with
> > standard SQL?
>
> What do you mean by "standard SQL"? Trees aren't inherently relational.
Right now my table
Dave Page wrote:
>Magnus Hagander wrote:
>> Rainer Bauer wrote:
>>> After increasing the session heap size in the registry from 512KB to 1024KB
>>> the no. of connections was roughly doubled. So this might be a solution for
>>> people running out of Desktop heap.
>>>
>>> Alter the value of the fol
On Oct 26, 2007, at 10:56 , Pat Maddox wrote:
A bunch of options so far...but there's really no way to do this with
standard SQL?
What do you mean by "standard SQL"? Trees aren't inherently relational.
I'm starting to feel I'm better off just pulling the data I need and
then building the t
On Oct 26, 2007, at 10:28 AM, Darnell Brawner wrote:
I am trying to make a sql based versioning system.
I am working on a Ruby on Rails project and am using a plugin
called hobo the plugin can do some nice things but over all its
lame but thats what i got to work with.
The problem is hobo d
> "Sam Mason" <[EMAIL PROTECTED]> writes:
>
>> On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote:
>>> Gregory Stark wrote:
>>> >Tom's point is that if you have 55k tables then just *finding* the
>>> newest
>>> >child table is fairly expensive. You're accessing a not
>>> insignificant-s
On 10/26/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Oct 26, 2007, at 4:19 , Gregory Stark wrote:
>
> > "D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
> >
> >> You need to look at the connectby function which is part of contrib.
> >
> > Or ltree. Depending on how static your data is an
On Thu, 2007-10-25 at 08:57 -0600, Josh Tolley wrote:
> On 10/24/07, Bob Pawley <[EMAIL PROTECTED]> wrote:
> > Is there any way of converting text from an AutoCad (.dwg ot .dxf) file into
> > a PostgreSQL Database??
> >
> > Bob Pawley
>
> I know nothing of AutoCad, but your message has been sitti
I am trying to make a sql based versioning system.
I am working on a Ruby on Rails project and am using a plugin called
hobo the plugin can do some nice things but over all its lame but
thats what i got to work with.
The problem is hobo does a lot of work for you but the database most
be in
Hi Tino
Tino Wildenhain wrote:
> Hi Dave,
>> pgAdmin II had change control. No-one ever really used it though so we
>> never bothered to implement it in pgAdmin III.
>
> But it was implemented differently then the proposal above.
I'm not sure the detail of how it was implemented was a huge facto
Hi Dave,
Dave Page schrieb:
--- Original Message ---
From: "Roberts, Jon" <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: 25/10/07, 17:35:32
Subject: Re: [GENERAL] subversion support?
Complaint? Who is complaining?
I am simply asking if this feature that is rather comm
On Fri, Oct 26, 2007 at 02:21:39PM +0100, Gregory Stark wrote:
> "Sam Mason" <[EMAIL PROTECTED]> writes:
> > I think the lookup that is being referred to is the fact that if you've
> > got 55k (plus) files in a directory then the filesystem still has to
> > perform a search in the directory to loca
Hi Tom (and all)
Yes, in the meantime I realised that the other relevant clients (the ones that
seemed to be holding the file handle) were ones that sat idle most of the time
and rarely executed any query. You are right, as each of these executed a query
(thus processing sinval) they released the
"Sam Mason" <[EMAIL PROTECTED]> writes:
> On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote:
>> Gregory Stark wrote:
>> >Tom's point is that if you have 55k tables then just *finding* the newest
>> >child table is fairly expensive. You're accessing a not insignificant-sized
>> >index
Reg Me Please <[EMAIL PROTECTED]> writes:
>>> (cost=3.95..382140.91 rows=274709 width=91) (actual
>>> time=1.929..57713.305 rows=92 loops=1)
>>> Hash Cond: (t_dati.camp_id = t_campi.camp_id)
I think the reason it doesn't want to use an indexed join is the large
estimate of the number of join resul
On Fri, Oct 26, 2007 at 02:39:28PM +0200, Reg Me Please wrote:
> > >- Hash Join
> > > (cost=3.95..382140.91 rows=274709 width=91) (actual
> > > time=1.929..57713.305 rows=92 loops=1)
> > >Hash Cond: (t_dati.camp_id = t_campi.camp_id)
>
> pro
"A. Kretschmer" <[EMAIL PROTECTED]> writes:
> Change the SELECT procpid, ... to
>SELECT into procpid, ...
For something like this, you shouldn't use plpgsql at all: a simple
SQL function gets the job done with a lot less notational overhead
(and likely less runtime overhead too).
post
Il Friday 26 October 2007 13:56:20 Martijn van Oosterhout ha scritto:
> On Fri, Oct 26, 2007 at 01:10:42PM +0200, Reg Me Please wrote:
> > prove=# explain analyze SELECT * from t_dati natural left join t_campi
> > where tabe_id='CONTE';
> >
On Oct 26, 2007, at 5:39 , Reg Me Please wrote:
I'd need to modify the primary key definition in an already
populated table.
How can I do it?
Drop the primary key constraint and create a new one. You can do this
inside a transaction.
test=# \d strings
Table "public.strings"
Column
"Vince Negri" <[EMAIL PROTECTED]> writes:
> What causes the file handles of the truncated table to be released by all
> postmaster processes?
It should happen when the other backends process the sinval message
about the TRUNCATE, which at the latest should be the next time they
begin command exec
Vince Negri wrote:
> So I tried TRUNCATE on the table. It appeared to work - in that the
> row count dropped to zero and the connected client was not disrupted,
> and "du" on the postgres data directory showed a fall.
>
> But the available disk space (reported by "df") did not fall.
I think y
On Fri, Oct 26, 2007 at 01:10:42PM +0200, Reg Me Please wrote:
> prove=# explain analyze SELECT * from t_dati natural left join t_campi where
> tabe_id='CONTE';
> QUERY PLAN
>
On Oct 26, 2007, at 4:19 , Gregory Stark wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
You need to look at the connectby function which is part of contrib.
Or ltree. Depending on how static your data is and what else you
need to do
with it.
Or adjacency list or nested set (or e
Il Friday 26 October 2007 13:05:10 Martijn van Oosterhout ha scritto:
> On Fri, Oct 26, 2007 at 12:34:06PM +0200, Reg Me Please wrote:
> > it's very fast (of course!). But when I run:
> >
> > SELECT * FROM T_DATA NATURAL JOIN T_FIELDS WHERE TABL_ID='TABL';
> >
> > it's very slow.
> > The EXPLAIN sa
On Fri, Oct 26, 2007 at 12:34:06PM +0200, Reg Me Please wrote:
> it's very fast (of course!). But when I run:
>
> SELECT * FROM T_DATA NATURAL JOIN T_FIELDS WHERE TABL_ID='TABL';
>
> it's very slow.
> The EXPLAIN says that in the second case it has to do a sequential
> scan on T_DATA. And this ex
On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote:
> Gregory Stark wrote:
> >Tom's point is that if you have 55k tables then just *finding* the newest
> >child table is fairly expensive. You're accessing a not insignificant-sized
> >index and table of tables. And the situation is worse
On Fri, Oct 26, 2007 at 08:26:13AM +0200, Thomas Finneid wrote:
> Scott Marlowe wrote:
> >It may well be that one big table and partial indexes would do what
> >you want. Did you explore partial indexes against one big table?
> >That can be quite handy.
>
> Hmm, interresting, I suppose it could w
Hi all.
I have a setup like this:
CREATE TABLE T_FIELDS (
TABL_ID TEXT NOT NULL,
COLU_ID TEXT NOT NULL,
FIEL_ID TEXT PRIMARY KEY,
UNIQUE( TABL_ID,COLU_ID ) ); -- < 200 ROWS
CREATE TABLE T_DATA (
ITEM_ID INT8 NOT NULL,
FIEL_ID TEXT NOT NULL REFERENCES T_FIELDS,
DATA_T TEXT NOT NULL,
Hi All,
Running postgres 8.1.9 on SLES10 (kernel 2.6.16) currently, I noticed something
about the behaviour
of TRUNCATE on a large table.
The docs imply that TRUNCATE is the best way to delete everything in a table
(rather than DELETE) since there is no need to VACUUM afterward - the disk space
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/26/07 04:39, Reg Me Please wrote:
> Hi all.
> I'd need to modify the primary key definition in an already populated table.
> How can I do it?
Have you tried dropping the constraint, and creating a new one?
http://www.postgresql.org/docs/8.2/int
Hi all.
I'd need to modify the primary key definition in an already populated table.
How can I do it?
Thanks.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Magnus Hagander wrote:
> Rainer Bauer wrote:
>> After increasing the session heap size in the registry from 512KB to 1024KB
>> the no. of connections was roughly doubled. So this might be a solution for
>> people running out of Desktop heap.
>>
>> Alter the value of the following key
>>
>>
>> The
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
> Pat Maddox wrote:
>> I'd like to store some tree data in my database. I want to be able to
>> sort the data but maintain a tree structure
>> Is it possible to pull all the data like that with one query? How do
>> I need to structure the table,
Roberts, Jon wrote:
> That is awesome. Can it be added to pga3?
Like I said - it wasn't implemented in pga3 because noone used it in
pga2 except for (as far as I know), the team I was working with at the
time. As I recall we polled the mailing lists before dropping it and
noone said they wanted t
am Fri, dem 26.10.2007, um 14:51:52 +0800 mailte Ow Mun Heng folgendes:
>
> On Fri, 2007-10-26 at 08:35 +0200, Reg Me Please wrote:
> > You could try this:
> >
> >
> > CREATE OR REPLACE FUNCTION foo( out procpid integer, out client_addr inet,
> > out
> > query_time interval, out current_query
65 matches
Mail list logo
