From what I have gathered on the performance list, JFS seemed to be the
best overall choice, but I'd say check the archives of
pgsql-performance because so many of your I/O needs depends on what
you're going to be doing with your database.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source Open Your i
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 25, 2005, at 10:22 AM, Andrus Moor wrote:
I need to create referential integrity constraints
Check out dbsize in contrib.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source Open Your i
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 24, 2005, at 9:53 AM, Ricardo Fonseca e Campos wrote:
Hi
.
Instinctively, it seems like it would be nice to have something similar
for checkpoint_timeout, but is there any disadvantage to having a value
dramatically higher than the default for general use, considering
that checkpoint_timeout can only be set at server start?
-tfo
--
Thomas F. O'Connell
was thinking more about the utility
of having a knob that could be twisted in the circumstances of large
data loads than the reality of what that would mean from the point of
view of WAL and checkpoints.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http
TABLE entry also pretty clearly indicates that ONLY is not
allowed in specifying inheritance. All you need is the table name, so
try eliminating the ONLY from your CREATE example below.
GUC is the Grand Unified Configuration scheme, which was introduced in
7.1.
-tfo
--
Thomas F. O'Connell
Co
and, to me, is more readable than a comma-delimited
list where position alone indicates function in the expression.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 17
Well, your expected vs. actual rows are off, so analyzing might help.
Otherwise, what is your sort_mem set to?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 14
possible that the planner thinks using the index on company_id
filtered by product_desc is faster than any multicolumn index that
might exist.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203
The manual is correct. There is no way to roll back a nextval.
There are a variety of workarounds suggested in the archives. Take a
look. One example is precalculating a large sequence and storing it in
a table.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
True. Apple even has a page with installation instructions:
http://developer.apple.com/internet/opensource/postgres.html
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Feb
whatsoever on linking tables on which it has no direct
effect (either reading or writing)?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Feb 2, 2005, at 9:41 AM, Tom Lane
: PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by
GCC 2.95.4
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
---(end of broadcast
in the linking table, and the secondary column of the
key also has its own index.
I'm more concerned with the locking, which is thoroughly unexpected
behavior to me.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
trying to do it.
You could probably create a nested structure, though.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jan 25, 2005, at 2:29 PM, Ben wrote:
I run this:
select
I think I see what was happening. I was looking at the output of the
SELECT that is used for opening a cursor. Got it. Thanks for your help.
It's kind of a meta-select in the printed version of a plan if the
cursor being opened is a SELECT.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information
to consider increasing max_fsm_relations and
max_fsm_pages in postgresql.conf.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jan 17, 2005, at 5:14 PM, Cornelia Boenigk
to the planner?
As in, should I rewrite the above statement as:
UPDATE mytable SET mybigintcol = somevalue WHERE mybigintcol = ''
|| NEW.myotherbigintcol || '';
in order to help the planner understand that it can use an index?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
of order of
evaluation?
PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC 2.95.4.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
---(end of broadcast
Never mind. I think I had some data that hadn't been calibrated lying
around prior to testing. I think my test case was flawed.
Sorry for the noise.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN
Pierre,
Your re-write makes a lot of sense. Thanks! It's not using indexes for
some reason, and discovering why will be my next challenge.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Dec 2, 2004, at 6:42 AM, Pierre-Frédéric Caillaud wrote:
Let's re-take your query from the start. At each step you should
explain
up in the EXCEPT clause?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Dec 2, 2004, at 10:26 AM, Tom Lane wrote:
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=
[EMAIL
Nice. Thanks, Bruce. If I felt as if I could speak a little more
authoritatively about why it had been missing, I would've offered to
document it.
Now it is a psql/postgres internals issue that causes \df trim not to
reveal anything?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information
I'm trying to do some research and reporting for an email application
by domain name. This has led to a confounding attempt to do any of the
legwork in SQL via postgres.
Here is my foundational query:
SELECT DISTINCT split_part( u.email, '@', 2 )
FROM user AS u, message AS m
WHERE u.id =
What version of postgres are you using? In postgresql-7.4.6, I get an
empty query buffer when I try what you describe here.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
explain why it doesn't show up in a \df listing:
pg=# \df trim
List of functions
Result data type | Schema | Name | Argument data types
--++--+-
(0 rows)
This one's more a curiosity thing.
-tfo
--
Thomas F. O'Connell
Co-Founder
PM, Josh Close wrote:
On Tue, 19 Oct 2004 00:48:34 -0500, Thomas F. O'Connell
[EMAIL PROTECTED] wrote:
Ah, yes. You're right.
Presumably you could use the hidden query from \dn in psql to get the
tables necessary for a script. That's the next best thing I can think
of.
I wrote a script to do
Are you looking for something different than vacuumdb?
http://www.postgresql.org/docs/7.4/static/app-vacuumdb.html
-tfo
On Oct 18, 2004, at 3:23 PM, Josh Close wrote:
Is there a way to vacuum all tables in a schema? Or will I need to
write a script to do it?
-Josh
---(end
Ah, yes. You're right.
Presumably you could use the hidden query from \dn in psql to get the
tables necessary for a script. That's the next best thing I can think
of.
-tfo
On Oct 18, 2004, at 6:02 PM, Josh Close wrote:
On Mon, 18 Oct 2004 15:35:53 -0500, Thomas F. O'Connell
[EMAIL PROTECTED
What error do you get when the server fails to restart? It sounds like
it could be a kernel resource issue. See:
http://www.postgresql.org/docs/7.4/static/kernel-resources.html
-tfo
On Oct 17, 2004, at 8:15 PM, Edwin New wrote:
I need to increase the number of connections to PostgreSQL 7.2.1.
http://www.postgresql.org/docs/7.4/static/datatype-boolean.html
CASE WHEN its THEN 'true' ELSE 'false' END
-tfo
On Oct 14, 2004, at 3:43 PM, Bambero wrote:
Postgres returns me 't' or 'f' from boolean field
How to change that it returns me 'true' or 'false'
replace(its, 'f', 'false') AS its
doesn't
Roberto Mello used to maintain a PL/PgSQL Cookbook, but this link is
dead, and I don't know if it's still around:
http://techdocs.postgresql.org/redir.php?link=http://
www.brasileiro.net/postgres/cookbook
-tfo
On Oct 11, 2004, at 4:05 AM, Armen Rizal wrote:
Hello all,
Is there anybody know
There's also pg_config.
http://www.postgresql.org/docs/7.4/static/app-pgconfig.html
-tfo
On Oct 11, 2004, at 3:04 AM, m.b. wrote:
Hello everybody,
is there a possibility to get some useful information about
my postgresql installation, its configuration, the locales
and information about the
Well, considering that the EXPLAIN tables are different from
tablea/tableb, I can only assume that your jobdata tables follow the
same pattern.
Are you familiar with the ONLY syntax for SELECT when using
inheritance? It looks like you might need to SELECT ONLY ... FROM
tableb.
-tfo
On Sep
I'm interested to know a little bit more about the postgres
implementation of indexes. I'm specifically wondering what it means in
the output of EXPLAIN when a filter is applied.
I'm trying to decide whether it makes sense to use indexes on
expressions rather than relying on a left-anchored
Yeah, I suppose this would be faster than the EXTRACT technique, too,
eh? Because it requires only a single index and is not an index on an
expression, which are generally more expensive?
-tfo
On Sep 29, 2004, at 6:59 PM, Tom Lane wrote:
Thomas F.O'Connell [EMAIL PROTECTED] writes:
Here's what
>From 11.5 in the docs:
The syntax of the x-tad-biggerCREATE INDEX/x-tad-bigger command normally requires writing parentheses around index expressions, as shown in the second example. The parentheses may be omitted when the expression is just a function call, as in the first example.
But when I
There's also pg_restore -P. See:
http://www.postgresql.org/docs/7.4/static/app-pgrestore.html
-tfo
On Sep 23, 2004, at 7:34 PM, Alvaro Herrera wrote:
On Thu, Sep 23, 2004 at 02:57:30PM -0600, Josué Maldonado wrote:
Hola,
Is there a way to restore a specific function from backup file created
with
You can use an index on an expression like lower( col ) LIKE ... as
long as the LIKE expression is left-anchored. See
http://www.postgresql.org/docs/7.4/static/indexes-expressional.html
-tfo
On Sep 21, 2004, at 1:16 PM, Kevin Murphy wrote:
I am pretty sure the answer is no, but ... is there any
So the answer is that ILIKE will not use indexes.
But using lower()/LIKE will give you exactly the same results. lower()
forces all column data to lower case for the purposes of comparison.
-tfo
On Sep 21, 2004, at 4:07 PM, Kevin Murphy wrote:
On Sep 21, 2004, at 4:52 PM, Thomas F.O'Connell
You'll probably need a sequence per thread. A sequence is not
necessarily tied to a column.
-tfo
On Sep 12, 2004, at 11:16 AM, Nick wrote:
This is actually a table that holds message threads for message
boards. Column A is really 'message_board_id' and column B is
'thread_id'. I would like every
The spacing in Konsole is directly related to the wrapping that it's
doing based on the size of the prod_name field (I.e., length in terms
of characters).
You can alter the format settings of psql. See:
http://www.postgresql.org/docs/7.4/static/app-psql.html
-tfo
On Sep 14, 2004, at 4:05 PM,
One thing I used to do (and I won't necessarily claim it as a best
practice) was to maintain my entire data model (tables, functions,
indexes, sequences) as SQL (plus postgres extensions) CREATE statements
in text files that were version controlled (via CVS). I had an entire
set of utilities
How does this imply one sequence? Is it guaranteed that for each value
of a, the values of b will be equivalent to all (and only) values of a?
There's plenty of flexibility within postgres for ways to use sequences.
Regardless, I think you need to have (and present) a better idea of
what you're
Hmm. I do a nightly dump of our production database, archive a copy
offsite, and verify the quality of the dump by running a little
verification script that is little more than a restore.
But if it would take you more than a day to do that, I'm not sure.
-tfo
On Sep 2, 2004, at 3:30 PM, Wes
On Sep 1, 2004, at 5:09 PM, Matthew T. O'Connor wrote:
This is the first report I have heard of pg_autovacuum causing cpu
usage spikes. When pg_autovacuum wakes up, it loops through all the
databases checking for recent activity and decides if it is time to do
something. I would think that
On Sep 1, 2004, at 10:27 PM, Matthew T. O'Connor wrote:
Number of rows is irrelevant, but the number of tables might not be.
It could be that the process of checking it's list of tables against
the server might be slow when used with lots of tables. Does this cpu
spike happen every other
We've got a table that has a definition as follows:
CREATE TABLE linking_table (
fk int8 REFERENCES source_table( pk1 ),
value int8,
PRIMARY KEY( fk1, value )
);
I would've thought that the multicolumn primary key would behave as a multicolumn index is supposed to behave per
I'm about to try to implement a simple pg_autovacuum script that can be
used in conjunction with or integrated entirely with the contrib
start-scripts for postgres. I just want to check that what I'm doing
has the appropriate sanity checks.
The behavior I'm considering is:
if pg_ctl status
Since the current stable version of postgres (7.4.x) doesn't allow
cross-datatype comparisons of indexes, is it always necessary to cast
my application data explicitly in order for an index to be used, even
among the integer types?
E.g., If I have a table with a bigint primary key and
On Jul 18, 2004, at 6:46 PM, Matthew T. O'Connor wrote:
Regular vacuum will (almost) never return your table to it's minimum
size. I don't think it's unreasonable for a table that is 4MB after a
vacuum full, to grow to 11MB, especially if it's a very active table.
That's good to know.
The
[EMAIL PROTECTED] (Thomas F. O'Connell) wrote in message news:
postgresql.conf just has the default of 1000 shared_buffers. The
database itself has thousands of tables, some of which have rows
numbering in the millions. Am I correct in thinking that, despite the
hint, it's more likely that I
create trigger date_update before update on mytable
for each statement execut procedure [procedure that inserts a date
for me in the update_date column]
So what would I be doing for the portion in brackets?
at that point you need a function.
check out the CREATE FUNCTION syntax in
is there any way to get access to the new and old records created by a
trigger in the function it calls?
i.e., if i have
create trigger after_insert after insert on foo
execute procedure trigger_after_insert_foo();
is there any way to do something like the following...
create function
is it safe to change NAMEDATALEN, dump an existing database, recompile,
and then restore the database?
in src/include/postgres_ext.h, it mentions that ... databases with
different NAMEDATALEN's cannot interoperate!, and i was wondering if
included when altering NAMEDATALEN for a database that
are there circumstances under which a pl/pgsql function will cache the
contents of a view?
i can do this sequence of actions just fine:
create table foo (
id int2 primary key
);
create view foo_view as
select *
from foo;
create function get_new_foo() returns int2 as '
declare
is there in postgres a way to create a constant like CURRENT_DATE for
general use?
-tfo
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
DaVinci wrote:
create function pilpot(calle) returns integer as '
this is your problem. try this:
create function pilpot(text) returns integer as '
the syntax for creating postgres functions is to declare the types, not
the identifiers of any parameters.
-tfo
is there any integer constant for infinity in postgres? i know such
beasts exist (with some bugs) in the date/time domain. it would be nice
to have an abstraction for numerology as well as chronology...
-tfo
p.s. i'm not sure this ever _really_ got posted before, since i wasn't
subscribed to
is there any integer constant for infinity in postgres? i know such
beasts exist (with some bugs) in the date/time domain. it would be nice
to have an abstraction for numerology as well as chronology...
-tfo
---(end of broadcast)---
TIP 1:
101 - 161 of 161 matches
Mail list logo
