On second thought another way to optimize a query like that would be
to remove the * and only put in the columns that are actually being
used, as opposed to taking the * literally.
Such that if the fields in the select list use 2 columns and the join
uses 1 column, only those 3 columns should be ex
Tom Lane wrote:
That argument has zilch to do with the question at hand. If you use a
coding style in which these things should be considered recoverable
errors, then setting up a signal handler to recover from them works
about the same whether the process is multi-threaded or not. The point
I wa
On Thu, 28 Oct 2004 01:01:20 -0400
Tom Lane <[EMAIL PROTECTED]> wrote
> Joel <[EMAIL PROTECTED]> writes:
> > Any thoughts on the urgency of the move?
>
> How large is your pg_log file? 7.1 was the last release that had the
> transaction ID wraparound limitation (after 4G transactions your
> data
On Thu, Oct 28, 2004 at 12:13:41AM +0200, Thomas Hallgren wrote:
> Martijn van Oosterhout wrote:
> >A lot of these advantages are due to sharing an address space, right?
> >Well, the processes in PostgreSQL share address space, just not *all*
> >of it. They communicate via this shared memory.
> >
>
[EMAIL PROTECTED] wrote:
So Thomas, you say you like the PostgreSQL process based modell better
than the threaded one used by MySQL. But you sound like the opposite. I'd
like to know why you like processes more.
Ok, let me try and explain why I can be perceived as a scatterbrain :-).
PostgreSQL
Martijn van Oosterhout wrote:
Now you've piqued my curiosity. You have two threads of control (either
two processes or two threads) which shared a peice of memory. How can
the threads syncronise easier than processes, what other feature is
there? AFAIK the futexes used by Linux threads is just as a
[processes vs threads stuff deleted]
In any modern and reasonable Unix-like OS, there's very little difference
between the multi-process or the multi-thread model. _Default_ behaviour
is different, e.g. memory is shared by default for threads, but processes
can share memory as well. There are ver
Guy Fraser wrote:
Trivia: In approximately 620 million years a day will be twice as long
as it is today.
Do you think then that Postgres628M.0 will fix it ? :-)
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 8: explain analyze is your frien
Hi guys,
Is there a way I can separate things in PostgreSQL?
Putting tables in a disk partition and indexes in
another one?
Regards,
MaRcELo PeReiRa
PHP/SQL/PostgreSQL
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection
Thanks for the response on this, especially the tip regarding xmin. I've been
spending much of the night and morning comptemplating this issue. I am glad
to have gotten this information, before going any further. Due to the front
end design, I believe I can implement all this within a short peri
Hi,
I think that TABLESPACE is what you need...
It's now available in Postgresql, just look at the syntax in documentation.
> --
-
> Patrick Fiche
> email : [EMAIL PROTECTED]
> tél : 01 69 29 36 18
>
Tom,
thanks ! You are even helping lurkers like me that haven't
asked anything :-)
...
> A better design is to fetch the data without locking it, allow the
> user to edit as he sees fit, and then when he clicks "save" you do
> something like
>
> begin;
> select row for update;
>
Patrick Fiche wrote:
Hi,
I think that TABLESPACE is what you need...
It's now available in Postgresql, just look at the syntax in documentation.
Actually it is only available in Beta. You will have to wait a little
while longer for stable release.
Sincerely,
Joshua D. Drake
Hi,
Even in Beta, it is just fine to me!
When I create a index, the tablespace used is the
table's tablespace, unless I use the tablespace clause
to put it in another one. It is ok, I have read
documentation and learned that.
I was looking for a default configuration, so ALL the
indexes created
Dawid,
I am interested in the first point you made that:
having varchar(12) in every referencing table, takes more storage
space.
The thing is though, if I have a serial primary key then it would be an
additional column. Or you are saying the space taken by a VARCHAR(12) field
is more than two IN
On Thu, Oct 28, 2004 at 10:35:27AM +0900, Joel wrote:
>
> I'm looking at the release notes for 7.2 and thinking that, when we make
> the jump, jumping to 7.4 will probably be the best bet.
Given that 7.2 is pretty much end of life now, I certainly wouldn't
adopt it. If you're going through the p
I would like to be able to truncate all of the tables in a schema without worrying
about FK constraints. I tried issuing a "SET CONSTRAINTS ALL DEFERRED" before
truncating, but I still get constraint errors. Is there a way to do something like:
1) disable all constraints
2) truncate all tables
3
Marco Colombo wrote:
[processes vs threads stuff deleted]
In any modern and reasonable Unix-like OS, there's very little difference
between the multi-process or the multi-thread model. _Default_ behaviour
is different, e.g. memory is shared by default for threads, but processes
can share memory as
Hello:
I'm trying to use kylix3 and postgresql 7.4.1.
My distro was Debian Woody, kernel 2.20.
I make the link /usr/local/pgsql/lib/libpq.so pointing to libpq.so.2.2
and connection with my database get fine.
But now, using Debian Sarge Testing, kernel 2.4.27 (I tried also with
kernel 2.6.8-1) th
Gaetano Mendola wrote:
Guy Fraser wrote:
Trivia: In approximately 620 million years a day will be twice as
long as it is today.
Do you think then that Postgres628M.0 will fix it ? :-)
Regards
Gaetano Mendola
I just hope, I don't have to work an equivalent fraction of the day for the
same pay, but
Tom,
You hit the nail on the head with what we did. We did two things and it
made a world of difference.
We moved from RAID 5 SCSII drives to our EMC SAN RAID 10 and adjusted
the checkpoint segments from 15 to 30.
The bottleneck disappeared totally and actually have never seen better
perfor
"Brian Maguire" <[EMAIL PROTECTED]> writes:
> What are the implications to further increasing the checkpoint so say
> 40?
AFAIK the downsides are (a) more disk space eaten for pg_xlog,
(b) if you suffer a crash, it will take longer to recover (because
there'll be more uncheckpointed work to replay
Hi,
My boss wants to add a special type of logging to some of our tables on
update/delete/insert. I need to log who, when, table_name, field name,
original value and new value for each record, but only logging modified
fields, and he wants me to do this using postgres pgSQL triggers. The
changes
Just so that I am not getting this wrong:
> BTW, a handy proxy for "row has not changed" is to see if its XMIN
> system column is still the same as before.
Considering that my business objects remember XMIN from when
they first got the row would the following sequence make sure
I am in good shape
On Wed, Oct 27, 2004 at 16:26:13 -0600,
Guy Fraser <[EMAIL PROTECTED]> wrote:
>
> When calculating any usage based on time, it is a good idea to
> store usage in days:hours:minutes:seconds because they are static
> and stable, if you discount the deceleration of the earth and
> corrections in
Karsten Hilbert <[EMAIL PROTECTED]> writes:
> Just so that I am not getting this wrong:
>> BTW, a handy proxy for "row has not changed" is to see if its XMIN
>> system column is still the same as before.
> Considering that my business objects remember XMIN from when
> they first got the row would t
"Tim Vadnais" <[EMAIL PROTECTED]> writes:
> My questions are: Is there a way I can dynamically determine the number of
> fields in the rows that is being maintained.
I'm starting to think there should be a FAQ entry for this ;-)
plpgsql is essentially incapable of doing anything that involves dyn
Dénouement :
J'ai enfin trouvé toutes les réponses à mes questions via la comande
REINDEX.
Merci à "Jean-Christophe Arnu" (s'il passe par ici) qui a confirmé via
son article sur http://www.postgresqlfr.org/?q=node/view/49 la solution
que j'avais cherché depuis quelques temps.
"Froggy / Froggy Co
On Thu, Oct 28, 2004 at 14:31:32 +,
Sally Sally <[EMAIL PROTECTED]> wrote:
> Dawid,
> I am interested in the first point you made that:
> having varchar(12) in every referencing table, takes more storage
> space.
> The thing is though, if I have a serial primary key then it would be an
> add
On Thu, Oct 28, 2004 at 09:14:17AM -0700, Tim Vadnais wrote:
>
> My questions are: Is there a way I can dynamically determine the number of
> fields in the rows that is being maintained. (a function much like:
> PQnfields(const PGresult *); )
> Then I need a way to get the name of the field (using
On Wed, 2004-10-27 at 22:45 -0700, Jonathan Daugherty wrote:
> # CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS '
> # SELECT adsrc
> # FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
> # WHERE
> # adrelid = pg_class.oid AND
> # pg_class.relnamesp
I think the same too but sometimes it seems in the real world performance is
given more value than a properly designed db. Or the long term flexiblity is
not taken into account given the short term requirements.
regards
Sally
From: Bruno Wolff III <[EMAIL PROTECTED]>
To: Sally Sally <[EMAIL PROT
Thanks to some help here on the list, I've been able to get addresses
sorting pretty well, but now I have a issue with same addresses on
different streets not grouping the streets. This is what I'm using a
substring search in the ORDER BY statement now like in this view:
SELECT tblhudsimilargroups
I have 2 existing tables in my db:
iss=> \d pollgrpinfo
Table "public.pollgrpinfo"
Column | Type | Modifiers
---++---
pollgrpinfoid | integer| not null
pollgrpid | integer| not
Just wondering - does PG support derived tables? I'm
not really sure what the difference is between them
and subqueries.
http://www.mysql.com/news-and-events/press-release/release_2004_32.html
http://www.sqlservercentral.com/columnists/rmarda/derivedtablebasics_printversion.asp
On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote:
> I would like all those on the same street grouped together. Is there any
> tricks to getting the street names sorted first, possibly where numbers
> and strings separate?
You could do something like
CREATE FUNCTION streetname(
Richard Poole wrote:
On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote:
I would like all those on the same street grouped together. Is there any
tricks to getting the street names sorted first, possibly where numbers
and strings separate?
You could do something like
CREATE FUNCTI
CSN <[EMAIL PROTECTED]> writes:
> Just wondering - does PG support derived tables? I'm
> not really sure what the difference is between them
> and subqueries.
None whatever, at least using the definition offered by your second
reference:
A derived table is a select statement inside paren
I'm sure this has been answered before, but the search seems to be down
again.
How can I convert the results of a subselect into an array? IE:
CREATE TABLE a(a int, b int, c int[]);
INSERT INTO table_a
SELECT a, b, (SELECT c FROM table_c WHERE table_c.parent = table_b.id)
FROM table_b
On Thu, Oct 28, 2004 at 02:44:55PM +0200, Marco Colombo wrote:
> I think that it would be interesting to discuss multi(processes/threades)
> model vs mono (process/thread). Mono as in _one_ single process/thread
> per CPU, not one per session. That is, moving all the "scheduling"
> between sessio
On Thursday October 28 2004 11:42, Robby Russell wrote:
>
> Thanks, this seems to work well. My goal is to actually create a php
> function that takes a result and returns the insert_id like
> mysql_insert_id() does, but without needing to know the sequence names
> and such. I would make a psql fun
On Thu, 2004-10-28 at 16:51 -0600, Ed L. wrote:
> On Thursday October 28 2004 11:42, Robby Russell wrote:
> >
> > Thanks, this seems to work well. My goal is to actually create a php
> > function that takes a result and returns the insert_id like
> > mysql_insert_id() does, but without needing to k
On Thu, Oct 28, 2004 at 05:37:29PM -0500, Jim C. Nasby wrote:
> I'm sure this has been answered before, but the search seems to be down
> again.
>
> How can I convert the results of a subselect into an array? IE:
>
> CREATE TABLE a(a int, b int, c int[]);
> INSERT INTO table_a
> SELECT a, b,
On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote:
>
> But I didn't understand why you care to get rid of the explicit reference to
> the sequence object in your code in the first place. In PostgreSQL, at
> least for the past 5 years if not longer, if you create a SERIAL column for
> (sche
# But I didn't understand why you care to get rid of the explicit reference to
# the sequence object in your code in the first place. In PostgreSQL, at
# least for the past 5 years if not longer, if you create a SERIAL column for
# (schemaname, tablename, columnname), then your sequence will *a
On Thursday October 28 2004 5:31, Michael Fuhr wrote:
> On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote:
> > But I didn't understand why you care to get rid of the explicit
> > reference to the sequence object in your code in the first place. In
> > PostgreSQL, at least for the past 5 years
"Ed L." <[EMAIL PROTECTED]> writes:
> In PostgreSQL, at least for the past 5 years if not longer, if you create a
> SERIAL column for (schemaname, tablename, columnname), then your sequence
> will *always* be "schemaname.tablename_columnname_seq". If that naming
> convention changes, there will b
I did not notice in the Install instructions that a
dump restore needed to be done...
This is what I got when I upgrade the v8b3 to the v8b4
FATAL: database files are incompatible with server
DETAIL: The database cluster was initialized with CATALOG_VERSION_NO
200408031, but the server was comp
How will that work when people reside at
123A Some St.
Address that need to be sorted and/or grouped in any way should be
stored as multiple fields.
door number
door number suffix Most often a letter
street name prefix Section
street name
street name suffix Direcetion
On Thu, Oct 28, 2004 at 07:55:51PM -0400, Greg Stark wrote:
>
> "Ed L." <[EMAIL PROTECTED]> writes:
>
> > In PostgreSQL, at least for the past 5 years if not longer, if you create a
> > SERIAL column for (schemaname, tablename, columnname), then your sequence
> > will *always* be "schemaname.tabl
I've sent an e-mail to Guiseppe Tanzilli about this, but maybe someone
here has seen this. I'm pretty sure it's not PostGreSQL, but it is
tangential.
We are updating to mod_auth_pgsql2 v2.0.latest and apache 2.0.latest, in
the process of updating to PostGreSQL 7.4.latest.
We get the following er
51 matches
Mail list logo
