On Sep 28, 2006, at 8:51 PM, Tom Lane wrote:
[..]
The information we've seen says that the only statistically
reliable way
to arrive at an accurate n_distinct estimate is to examine most of the
table :-(. Which seems infeasible for extremely large tables,
which is
exactly where the problem
I'm re evaluating a few design choices I made a while back, and one
that keeps coming to the forefront is data separation. We store
sensitive information for clients. A database for each client isn't
really workable, or at least I've never though of a way to make it
workable, as we have several
Casey Duncan <[EMAIL PROTECTED]> writes:
> I was also trying to figure out how big the sample really is. Does a
> stats target of 1000 mean 1000 rows sampled?
No. From memory, the sample size is 300 times the stats target (eg,
3000 rows sampled for the default target of 10). This is based on s
"Jack Orenstein" <[EMAIL PROTECTED]> writes:
> I understand that updating a row of t generates a new row version, and
> that different transactions may see different versions of the same
> row.
> How does versioning work for the index?
Each row version has its own index entry pointing to it. So a
- Original Message -
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
Didn't someone just ask this yesterday?
Yes. i did it :-)
I believe you can get that info by increasing client_min_messages.
What you specifically have to set it to I don't know; my guess would be
log or debug1.
I
On Thu, Sep 28, 2006 at 03:19:46PM -0700, Casey Duncan wrote:
> I have some databases that have grown significantly over time (as
> databases do). As the databases have grown, I have noticed that the
> statistics have grown less and less accurate. In particular, the
> n_distinct values have b
On Thu, Sep 28, 2006 at 01:27:24PM +0300, Adnan DURSUN wrote:
>Hi all
>
>I wanna know what is going on while a DML command works. For example
> ;
>Which commands are executed by the core when we send an "UPDATE tab
> SET col = val1..."
>in case there is a foreing k
I have some databases that have grown significantly over time (as
databases do). As the databases have grown, I have noticed that the
statistics have grown less and less accurate. In particular, the
n_distinct values have become many OOM too small for certain foreign
key columns. Predictabl
On Thursday 28 September 2006 14:58, "Jack Orenstein"
<[EMAIL PROTECTED]> wrote:
> How does versioning work for the index?
>
> - The update above does not update the index key. Does the index get
> updated at all?
>
Yes, it does.
> - If not, then how can an index lookup return the correct vers
Suppose I have a table and index:
create table t(x int, y varchar, primary key(x));
and that the only updates are "update t set y = ... where x = ?".
I understand that updating a row of t generates a new row version, and
that different transactions may see different versions of the same
row.
Ah, yes, you are correct.
Hm, it's too bad levenshtein() is ascii-only.
On Thu, 28 Sep 2006, Tom Lane wrote:
Ben <[EMAIL PROTECTED]> writes:
The levenshtein function from contrib/fuzzystrmatch.sql has a max arg
length of 255. OK, that's cool. But check this out:
mbrainz_db=> select max(len
Martijn van Oosterhout wrote:
On Wed, Sep 27, 2006 at 02:57:09PM -0400, Geoffrey wrote:
We continue to have segmentation faults of the /usr/bin/postgres process
as I mentioned in an earlier thread. In all cases, the core file
always indicates a segmentation fault, but the backtraces don't see
Bob wrote:
Is there any reason can't update to a newer version. Like 8.x?
We plan on going to the latest 7.4 the first of October. The latest 8.x
is on the schedule, but there will be coding changes required and
extensive testing, so that's a bit further out.
Geoffrey wrote:
We continue
Ben <[EMAIL PROTECTED]> writes:
> The levenshtein function from contrib/fuzzystrmatch.sql has a max arg
> length of 255. OK, that's cool. But check this out:
> mbrainz_db=> select max(length(name)) from public.track;
> max
> -
> 255
> (1 row)
> mbrainz_db=> select levenshtein(name,'foo')
On Thu, Sep 28, 2006 at 12:02:34PM -0700, Ben wrote:
> The levenshtein function from contrib/fuzzystrmatch.sql has a max arg
> length of 255. OK, that's cool. But check this out:
>
> mbrainz_db=> select levenshtein(name,'foo') from public.track;
> ERROR: argument exceeds max length: 255
The m
The levenshtein function from contrib/fuzzystrmatch.sql has a max arg
length of 255. OK, that's cool. But check this out:
mbrainz_db=> select max(length(name)) from public.track;
max
-
255
(1 row)
mbrainz_db=> select levenshtein(name,'foo') from public.track;
ERROR: argument exceeds max
Joost Kraaijeveld <[EMAIL PROTECTED]> writes:
> On Wed, 2006-09-27 at 14:58 -0400, Tom Lane wrote:
>> LANG=en_US locale charmap
> The working machine says: ISO-8859-1
> The not working machine says: ISO-8859-1
OK, so the problem is that you have a locale that expects ISO-8859-1
encoding, but the
Hi Tom,
Thanks for putting up with the questions.
On Wed, 2006-09-27 at 14:58 -0400, Tom Lane wrote:
> LANG=en_US locale charmap
>
I have done this on both machines:
The working machine says: ISO-8859-1
The not working machine says: ISO-8859-1
I still do not understand what is happening and ma
[EMAIL PROTECTED] wrote:
Is it possible to define a complex return type like a record in a function
without having some table around with the needed structure of the return
values?
Sure, you just have to specify the columns at select time instead (and
this is easy enough to wrap inside a view)
Kai Hessing <[EMAIL PROTECTED]> writes:
> PostgreSQL 8.1.4 (The one which is taking... a very long time...)
> Filter: (NOT (subplan))
> SubPlan
> -> Materialize (cost=7751.81..8497.94 rows=53613 width=4)
> PostgreSQL 8.0.8 (The one which takes only a few seconds... on
One Addition:
> PostgreSQL 8.1.4 (The one which is taking... a very long time...)
>
> Merge Join (cost=7751.81..50026810.45 rows=7364 width=4)
> Merge Cond: ("outer".sid = "inner".sid)
> -> Index Scan using stud_pkey on stud s (cost=7751.81..49994210.01
> rows=56607 width=4)
> Filt
Joe Conway wrote:
> It is possible for a query to run for many days, and still finish. This
> classifies as slow, not hung. The difference is important in
> troubleshooting to determine the cause.
OK, what do you suggest, how long should the process run, until I can
except it not to end?
>>>Als
Andrew Sullivan wrote:
> No, it doesn't. Deadlock means, for the two deadlocked queries, both
> cannot possibly finish because each waits on a lock that the other
> one holds.
Thanks for the clarification. I thought a deadlock is also, when the
system runs into an endless loop.
>> There is no
> EXPLAIN without ANALYZE locking up?!? Maybe some application is holding
> a lock on a record in your result set.
OK, this was a good hint. I got EXPLAIN working. My mistake was, that I
tried explain the last time, while the 'Killer-SQL' was running. Then
EXPLAIN didn't answer until I killed th
Original Message
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Andrus Sent:
Wednesday, September 27, 2006 2:17 PM To: pgsql-general@postgresql.org
Subject: [GENERAL] How to create nightly backups in Linux
> I'm using the the following scheduler script to create nightly
>
Jeff Davis <[EMAIL PROTECTED]> writes:
> I have attached a patch. I wrote it very quickly, but it seems to work
> as I expect.
I don't think this is very workable as a postgresql.conf parser ...
at minimum it needs to handle quoted strings correctly, and really it
ought to deal with file inclusion
On Wed, 27 Sep 2006, Dan Libby wrote:
> Background:
>
> I have a hierarchical table where I have coordinate data for only the leaf
> nodes. I therefore want to find the center of all the leaf nodes under a
> given parent node, and set the parent node coordinate to that center point.
>
> I can cal
am Thu, dem 28.09.2006, um 12:31:37 +0200 mailte Martijn van Oosterhout
folgendes:
> On Thu, Sep 28, 2006 at 10:15:19AM +0200, A. Kretschmer wrote:
> > am Thu, dem 28.09.2006, um 9:46:29 +0200 mailte [EMAIL PROTECTED]
> > folgendes:
> > > Hello all!
> > >
> > > Is it possible to define a comp
On Thu, Sep 28, 2006 at 10:15:19AM +0200, A. Kretschmer wrote:
> am Thu, dem 28.09.2006, um 9:46:29 +0200 mailte [EMAIL PROTECTED] folgendes:
> > Hello all!
> >
> > Is it possible to define a complex return type like a record in a function
> > without having some table around with the needed str
On Wed, Sep 27, 2006 at 08:14:29PM -0600, Dan Libby wrote:
> Hi all,
>
> Using pgsql 8.0.1
>
> I'm just starting with using the geometry data types in postgres, and ran
> into
> what seems like a very basic problem. Namely, I can't seem to convert/cast
> type text into type point when that t
Hi all
I wanna know what is going on while a DML command works. For example
;
Which commands are executed by the core when we send an "UPDATE tab
SET col = val1..."
in case there is a foreing key or an unique constraint on table
"tab".
How can i see that ?
On Wed, Sep 27, 2006 at 05:03:15PM -0400, Jonathan Vanasco wrote:
>
> Someone posted an issue to the mod-perl list a few weeks ago about
> their machine losing a ton of memory under a mod-perl2/apache/
> postgres system - and only being able to reclaim it from reboots
Are you sure you're looki
On Wed, Sep 27, 2006 at 02:57:09PM -0400, Geoffrey wrote:
> We continue to have segmentation faults of the /usr/bin/postgres process
> as I mentioned in an earlier thread. In all cases, the core file
> always indicates a segmentation fault, but the backtraces don't seem to
> consistently point
Joachim Wieland schrieb:
On Thu, Sep 28, 2006 at 07:09:43AM +0100, John Sidney-Woollett wrote:
Why not use an update trigger on the affected tables to record a
lastupdated timestamp value when the record is changed.
Surely this is simpler thanks computing some kind of row hash?
It depends o
Kai Hessing wrote:
Alban Hertroys wrote:
SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid =
34 AND s.sid NOT IN ( SELECT sid FROM stud_vera WHERE veraid = 2 );
I'm pretty sure it's not a deadlock. It probably takes very long for
some reason; maybe an explain of that query
Ah, good point! Missed the subtlety of what was being asked.
John
Joachim Wieland wrote:
On Thu, Sep 28, 2006 at 07:09:43AM +0100, John Sidney-Woollett wrote:
Why not use an update trigger on the affected tables to record a
lastupdated timestamp value when the record is changed.
Surely this
On Thu, Sep 28, 2006 at 07:09:43AM +0100, John Sidney-Woollett wrote:
> Why not use an update trigger on the affected tables to record a
> lastupdated timestamp value when the record is changed.
> Surely this is simpler thanks computing some kind of row hash?
It depends on how you define "change
am Thu, dem 28.09.2006, um 9:46:29 +0200 mailte [EMAIL PROTECTED] folgendes:
> Hello all!
>
> Is it possible to define a complex return type like a record in a function
> without having some table around with the needed structure of the return
> values?
Yes, you can define a new type:
CREATE T
For the others (bostgresql, posdgresql, bosdgresql) i think u should
write a function, that's what we did in our application.
contrib/pg_trgm solves it
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www
for PostgreSQL, Postgresql, postgresql u can use ilike:example: select * from table where name ilike 'postgresql'U can even try:select * from table where name ilike '%postgresql%'the '%' is for any set of caracters (like * when u are doing a shell commande)For the others (bostgresql, posdgresql, bo
Play around contrib/tsearch2 and contrib/pg_trgm
Michael Vodep wrote:
Hi
How can i do a full text fuzzy search with PgSQL?
For example: i got a text domain. I search for Postgresql
He should return a result for 'Postgresql' if i enter following values:
PostgreSQL, Postgresql, postgresql
but al
You sure that's actually what he said? A change in CRC proves the data
changed, but lack of a change does not prove it didn't.
"To quickly determine if rows have changed, we rely on a cyclic
redundancy checksum (CRC) algorithm. If the CRC is identical for the
>
"summary" functions, such as
Hello all!
Is it possible to define a complex return type like a record in a function
without having some table around with the needed structure of the return
values?
For example: if i want a function that returns a date and an integer, i
create the function:
CREATE FUNCTION bla(text) RETURNS SET
Hi
How can i do a full text fuzzy search with PgSQL?
For example: i got a text domain. I search for Postgresql
He should return a result for 'Postgresql' if i enter following values:
PostgreSQL, Postgresql, postgresql
but also
bostgresql, posdgresql, bosdgresql
Is this possible?
Regrads
michae
44 matches
Mail list logo
