setup you don't need to change many settings and they don't
depend on order.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
, 'id') or even
get_attribute_quoted(NEW, 'id')
It would be nice to have a more dynamic language built-in. I'm not aware
of any BSD-licensed dynamic languages though.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can
obj_list.txt
pg_dump -L obj_list.txt mydb mydb.before.schema
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do
) and allow for stretchy partitions at the
cost of an extra layer of indirection.
For the single-partition case you'd not need to split the file of
course, so it would end up looking much like the current arrangement.
--
Richard Huxton
Archonet Ltd
---(end of broadcast
Csaba Nagy wrote:
On Fri, 2008-01-11 at 11:34 +, Richard Huxton wrote:
1. Make an on-disk chunk much smaller (e.g. 64MB). Each chunk is a
contigous range of blocks.
2. Make a table-partition (implied or explicit constraints) map to
multiple chunks.
That would reduce fragmentation (you'd
Simon Riggs wrote:
On Fri, 2008-01-11 at 11:34 +, Richard Huxton wrote:
Is the following basically the same as option #3 (multiple RelFileNodes)?
1. Make an on-disk chunk much smaller (e.g. 64MB). Each chunk is a
contigous range of blocks.
2. Make a table-partition (implied or explicit
of flagging rather than file-segments. That obviously complicates
the tracking but means you can cope with infrequent updates as well as
mark most of the most recent segment for log-style tables.
--
Richard Huxton
Archonet Ltd
---(end of broadcast
Simon Riggs wrote:
On Fri, 2008-01-04 at 10:22 +, Richard Huxton wrote:
Simon Riggs wrote:
We would keep a dynamic visibility map at *segment* level, showing which
segments have all rows as 100% visible. No freespace map data would be
held at this level.
Small dumb-user question.
I take
this sort of thing. Hopefully that will help you.
--
Richard Huxton
Archonet Ltd
-- History Tracking Trigger-Functions
--
BEGIN;
-- tcl_track_history(TABLE-NAME)
-- Set TABLE-NAME when creating the trigger. Will automatically record change
-- details in tables history/history_detail
--
CREATE
than a dictionary file and
it seems less likely an admin (e.g. me) might fail to prepare the target
accordingly.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating
think the extras stuff for PG packages has been /contrib
but obviously that's not a strict rule.
* PS - it's a useful feature - good work to all concerned*
** PPS - 8.3 is looking good too. This short development cycle is
working wonders ;-)
--
Richard Huxton
Archonet Ltd
a long time, even
to quit after i pressed 'q'.
With oracle SQLPlus, it is quite instantaneous.
Again, you're measuring different things. What is the time to the *last*
row?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1
, but I do
know there are ways to control this. Perhaps try the -jdbc mailing list.
In any case, I think we've established it's nothing for the hackers list.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our
Trevor Talbot wrote:
On 11/12/07, Richard Huxton [EMAIL PROTECTED] wrote:
Gokulakannan Somasundaram wrote:
I also noticed that it doesn't crash with psql, but it takes a
long time to show the first set of records. It takes a long time, even
to quit after i pressed 'q'.
With oracle
install script you can change the
schema it installs to. That should make it easier to identify everything
it installs.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
someone who can, I'm sure people
would like to see it fixed for version 8.4. Don't underestimate the work
involved though.
HTH
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
SELECT f(NEW.test1);
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
the existence of a JRE.
Can help here:
http://pgfoundry.org/projects/pginstaller/
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
://www.postgresql.org/docs/8.2/static/warm-standby.html
Particularly section 23.4.4
That can get you to 1 second or less.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose
Value for WAL
Files is 16 MBytes).
You've either not read 23.4.4 or haven't understood it. If the text is
unclear, documentation additions/changes are always welcome.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you
machines.
You could do this just for WAL.
3. Replication.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
?
Well it's unlikely to be real soon since 8.3 is currently approaching
beta-test.
Surely memcached+pgmemcache does basically this anyway, except:
- it's not restricted to function outputs
- you can cache application objects
- you can spread your cache across multiple machines
--
Richard
the overheads low enough without interfering with
plpgsql itself?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
of
logging things permanently, just for debugging purposes.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
. Perhaps I'm optimising
prematurely though. I'll have to run some timing tests...
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
? That should
automatically return null if a parameter is null without calling the
function.
Simplest way to check is probably to pg_dump --schema-only and search
for the function defn.
--
Richard Huxton
Archonet Ltd
---(end of broadcast
.
- Database independence
In particular, this one makes no sense to me.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
that doesn't seem much better.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
. This question is better suited to the
general / sql / admin lists perhaps.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
versions).
Or are these examples of changes that will only be allowed e.g. every
other major version.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs
like that. Any other takers?
I like synchronous_commit = off, it even has a little girlfriend
getting spin while being accurate :)
Or perhaps sync_on_commit = off?
Less girlfriend-speak perhaps:no_sync_on_commit = on
--
Richard Huxton
Archonet Ltd
---(end
?)*
It's obvious to people on the -hackers list what we're talking about,
but is it so clear to a newbie, perhaps non-English speaker?
* I can see people thinking this means something like commit_delay.
--
Richard Huxton
Archonet Ltd
---(end of broadcast
to get anywhere.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
for binary data types
in libpq.
Um - speaking as a user, not a developer, I don't actually see a
description of what problem(s) you are suggesting be solved. Are you
saying there should be better documentation, or a new format?
--
Richard Huxton
Archonet Ltd
---(end
and
not 64-bit integers?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Marcos FabrÃcio Corso wrote:
I would like to leave the list ...
Not really a question worth posting several lists. If you don't know how
to unsubscribe, try starting with the form linked from here.
http://www.postgresql.org/community/lists/
--
Richard Huxton
Archonet Ltd
( pg_cancel_backend() ).
For the rest, that's what ssh is for imho.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Zdenek Kotala wrote:
Richard Huxton wrote:
For the rest, that's what ssh is for imho.
And what you will do when you don't have ssh access on this machine and
5432 is only one way how to administrate a server? (Windows is another
story.)
If I've not got ssh access to the machine, then I'm
-referential updates
Hiroshi originally noted the problem in one of his views here:
http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0
the inherits flag.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
of role X rather than list all the roles that are
members of X.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
looked at
it in detail it seems to pretty much do what it claims to.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Dave Page wrote:
Richard Huxton wrote:
Magnus Hagander wrote:
It's been on my list to rewrite the whole archive system for a while
for various reasons. There is quite a bit of crossover with the patch
tracker I proposed so I was hoping to look at both together.
Let me know when you start
suspicious if UPDATABLE VIEWS can be implemented
using the rule system.
Remember this affects all self-referential joins on an UPDATE (and
DELETE?) not just views. It's just that a rule is more likely to produce
that type of query.
--
Richard Huxton
Archonet Ltd
.
I'm trying to decide if it's unexpected or just plain wrong, and I think
I'd have to argue wrong.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs
Richard Huxton wrote:
Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the
condition in the update rule, id=OLD.id, but not the condition in the
original update-claus, dt='a'.
Yeah, that's confusing :(.
Bit more than just normal rule confusion I'd
Hiroshi Inoue wrote:
Richard Huxton wrote:
Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the
condition in the update rule, id=OLD.id, but not the condition in the
original update-claus, dt='a'.
Yeah, that's confusing :(.
Bit more than just
that would be useful too.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
friend 4ever wrote:
Hi,
I am getting the parse error while i try to execute a simple sql query in postgres.
This isn't a question for the hackers list.
Try the general, or jdbc lists.
--
Richard Huxton
Archonet Ltd
---(end of broadcast
to find out what the error is would be to
provide the *actual* query, not something very much like it. Perhaps
turn statement logging on in your postgresql.conf if it isn't already.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7
than equal.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
page? Assuming it's being written to as the backup is in progress. (We
are talking about when disk blocks are smaller than PG blocks here, so
can't guarantee an atomic write for a PG block?)
--
Richard Huxton
Archonet Ltd
---(end of broadcast
Simon Riggs wrote:
On Fri, 2007-03-30 at 11:27 +0100, Richard Huxton wrote:
Is that always true? Could the backup not pick up a partially-written
page? Assuming it's being written to as the backup is in progress. (We
are talking about when disk blocks are smaller than PG blocks here, so
to commit.
I don't know how simple it is to measure/estimate the time spent for #
of transactions that finish while an fsync is taking place.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please
everyone can agree on. Being able to say that
values in different columns are related just gives the planner more
information to work with.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
-like function):
To support this I think we'd need to do something like:
create function mygs(int, int)
returns setof int
language plperl
with srfstate
as $$ ... $$;
Is this not what we do with aggregate functions at present?
--
Richard Huxton
Archonet Ltd
is that for high temperature I need a temperature
value, whereas for tooth decay I'll want a tooth number (or whatever
they use). Which brings us back to where we started...
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget
=no then
don't bother to ask type of car=?. Edward - if you want to see a
schema that implements this sort of thing, contact me off list and I'll
see what I can do. The client for that project will probably be fine
with sharing it with one student.
--
Richard Huxton
Archonet Ltd
David Fetter wrote:
On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote:
* Another good example is the questionnaire.
With all due respect, this is a solved problem *without EAV or
run-time DDL*. The URL below has one excellent approach to this.
http://www.varlena.com/GeneralBits
David Fetter wrote:
On Tue, Mar 13, 2007 at 02:21:37PM +, Richard Huxton wrote:
David Fetter wrote:
On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote:
* Another good example is the questionnaire.
With all due respect, this is a solved problem *without EAV or
run-time DDL
, but why not?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Andrew Hammond wrote:
On 3/12/07, Richard Huxton dev@archonet.com wrote:
Josh Berkus wrote:
I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.
What's so horrible about DDL at runtime? Obviously, you're
Gregory Stark wrote:
Richard Huxton dev@archonet.com writes:
Well the cost depends on where/how complex the extra fields are. If you're just
talking about adding columns usercol01..NN with different types and possibly a
lookup to a single client_attributes table, it's not difficult
-admin lists.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
of that?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
you see a
static version. You could rsync that against an older file-level copy as
the base copy in a PITR backup.
Note - even with a snapshot facility you need to use PITR or stop the
database to get a guaranteed working copy.
--
Richard Huxton
Archonet Ltd
was
there before an update, it would be there after too. The only thing
you'd need to prevent would be deletes.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating
but only shows up in a function's context.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
) that would at least let
you check against a pre-determined list of types. The only way I know of
at present is to trap an exception if it fails.
I think you're going to have to store your arguments with their types.
--
Richard Huxton
Archonet Ltd
---(end of broadcast
.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
at the package level and the
bootstrapping for installation.
Bootstrapping could consist of nothing more than a set of SQL scripts
which set up some temporary tables and create and call the _install()
function.
Any of this in the direction that other people were thinking of?
--
Richard
Tom Lane wrote:
Richard Huxton dev@archonet.com writes:
1. Add a new column for all system objects, separate from schema:
package.
Wouldn't it be a whole lot easier just to drive it off schema, rather
than inventing duplicative parallel infrastructure? That is, say that a
package has one
in a
meaningful manner.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
if
view V doesn't mention column C then dropping C has no effect on it.
That's a lot more dependencies to track of course.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
on user-defined types, functions, etc?
Is there not some gain from just a standard partitioning of pg_class
into: (system-objects, user-objects, temp-objects)? I'd expect them to
form a hierarchy of change+vacuum rates (if you see what I mean).
--
Richard Huxton
Archonet Ltd
is to make your on-disk backups and
set it up as though it's PITR recovery you're doing. That way you can
stop the recovery before block 463 causes the failure. Oh, assuming
you've got the space you need on your partition of course.
HTH
--
Richard Huxton
Archonet Ltd
against a basically static table could you get away with
just checking the index and not the table?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs
compound) identifier.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
of the lists). In particular, that
would let you have FK constraints with a constant as part of the key.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
. Perhaps the
general or sql lists instead?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
the threshold is exceeded,
one when the node completes.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Peter Eisentraut wrote:
Jim Nasby wrote:
Any reason not to support renaming columns? Can this be added to
TODO?
Upgrade to Postgres95.
Hey, is Jim running MySQL 5?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9
or are they shared?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
relname = 't';
relhasindex
-
t
(1 row)
richardh=# rollback;
ROLLBACK
richardh=# select relhasindex from pg_class where relname = 't';
relhasindex
-
f
PostgreSQL 8.1.3 on i586-pc-linux-gnu
--
Richard Huxton
Archonet Ltd
---(end of broadcast
Basically, I'm wondering if anyone can see a problem with my standard
workaround to the macro-expansion-vs-nextval problem with view. I can't
see how PG changes might break it, but I might be using it in a
presentation to others so thought I'd best check.
BEGIN;
CREATE TABLE foo (f_id
Tom Lane wrote:
Richard Huxton dev@archonet.com writes:
Basically, I'm wondering if anyone can see a problem with my standard
workaround to the macro-expansion-vs-nextval problem with view.
CREATE FUNCTION foobar_ins_fn(p_f1 int4, p_b1 int4) RETURNS void AS $$
BEGIN
INSERT INTO foo (f_id
switches appeared. Is that OK?
Is there a reason why pg_dump can't do the --list/--use-list flags like
pg_restore, or is it just a matter of round tuits?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our
functions
- start-session/end-session functions
- dependencies (is pg_depend enough)
pg_package_items
- schema-name
- variables, functions, tables, views etc
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our
Martijn van Oosterhout wrote:
On Wed, Aug 09, 2006 at 08:38:22AM +0100, Richard Huxton wrote:
Namespaces
Given that we already have search_path it makes sense to use it. So, we
could have something like:
1. A PRIVATE modifier for objects that mean they are only accessible
if their schema
Richard Huxton wrote:
Packaging
I'd guess we'd need a pg_package and pg_package_items system tables. We
could track:
- package name (different from schema)
- version number
- install/uninstall functions
- start-session/end-session functions
- dependencies (is pg_depend enough
as with indexes:
CREATE STATISTIC ...defn here...
ON invoices (cli_id), clients (id)
WHERE invoices.paid = false
WITH PRIORITY 100;
(I'm thinking the priority so you can delete any rules with a low
priority while keeping ones you think are vital)
--
Richard Huxton
Archonet Ltd
Pavel Stehule wrote:
Package is similar to schema.
Are you saying that the package would effectively *be* a schema from the
outside. That is, if I have package foo then I can't also have a
schema foo?
--
Richard Huxton
Archonet Ltd
---(end of broadcast
be cached. You might
want to look at memcached for this sort of thing.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Jim C. Nasby wrote:
On Mon, Apr 10, 2006 at 10:44:15AM +0100, Richard Huxton wrote:
Bruce Momjian wrote:
* Allow EXPLAIN output to be more easily processed by scripts
Can I request an extension/additional point?
* Design EXPLAIN output to survive cut paste on mailing-lists
Being
, but this would at least remove the hazard
from stuff that thinks whitespace isn't significant.
That's the sort of thing I was thinking of, or even something like:
1 Nested Loop ...
1.1 Join Filter...
1.1.1 HashAggregate...
1.2 etc
--
Richard Huxton
Archonet Ltd
---(end
this is not the case, or refute any that you care to provide.
You can write trigger functions in plpgsql.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
useful on the lists. Sometimes it takes me longer to
reformat the explain than it does to understand the problem.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
] Random hang during commit /
[EMAIL PROTECTED]) that might well be related to this.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
-updatable views in 8.2 - even
if it was only for the simplest of cases. If I can be of any help
testing etc. let me know.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
Subject says it all really. I've got a new client who seems to be
suffering from it, and I'm not sure if any conclusion was reached.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
101 - 200 of 346 matches
Mail list logo
