re are just a lot of
self joins. Those are going to be hard for PostgreSQL to optimize
effectively.
Looks like it's just the query that's hard to understand. You might do
better just writing it yourself from scratch.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (p
stgreSQL
(and humans) can understand well enough to optimize.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
if you say why you are joining so many
tables, and what the numbers are for.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
plan chosen when the query times out
> as well?
>
There's a new module coming in 8.4 that makes this much easier. Right
now, you have to run it yourself with EXPLAIN ANALYZE as above.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
ather than waiting for PostgreSQL to do
another release.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote:
> I think the best solution is to make first-class interval types (for
> time as well as other types). Those intervals can then have operators
> like "contains" and "contained by" which would solve your problem.
>
s). Those intervals can then have operators
like "contains" and "contained by" which would solve your problem.
Additionally, it would allow lots of other interesting operations, like
overlaps and intersects.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing li
play all objects that are visible in the current schema search path —
this is equivalent to using the pattern *. To see all objects in the
database, use the pattern *.*. "
Perhaps it could be a little clearer in the short descriptions, do you
have a suggestion?
Regards,
Jeff Davis
| foo_table | table | tjhart
> (2 rows)
This behavior seems consistent to me. If you type an unqualified name
like:
SELECT * FROM foo_table;
You'll get the one from the first namespace listed in search_path. It
makes sense for "\d" to display only the tables that can be seen
ore, but I couldn't find the thread,
so I don't know what conclusion was reached.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
es. Perhaps more can
be done, but usually questionnaires are either too unimportant to really
dig in, or so important that designing a database around it is the
obvious thing to do.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to
R foo SET statement_timeout='2s';
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s a thread here about possibly supporting 64-bit postgresql on
windows:
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00081.php
Are there major issues holding that back or is it just a lack of demand?
Is there still active work going on?
Regards,
Jeff Davis
--
Sent via pgs
the case with UPDATE) a new index entry must be made in each index to
point to the new row.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
xpensive.
There are some optimizations in 8.3 for when the same tuple gets updated
many times, but that won't help you in this case.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
onality? I
> must admit that it would be handy some times ;-)
> Shouldn't this kind of question be added to the FAQ?
Here's a starting place:
http://archives.postgresql.org/pgsql-hackers/2008-04/msg01475.php
You can find other threads about the status of the work by browsing the
mailing
vent types, so we couldn't throw a syntax error for such a reference.
> A runtime error for a use of the variable might be possible, but a quick
> look at the code doesn't make it look easy.
>
Here's a doc patch that may clear up some of the confusion.
Regards,
his have recorded an
> error or warning somewhere?
This is a feature, not a bug. Sometimes you don't want to delete a
record, and returning NULL is the way to do that.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
ERROR: invalid input syntax for type interval: "expiration_value"
This error is saying that it is trying to convert the string
'expiration_value' to an interval.
What you really want it to convert the string value held inside a
variable named "expiration_value" to an in
On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
> > My question is not why don't we allow subqueries in CHECK, my question
> > is why do we allow stable/volatile functions?
>
> Historically we've allowed it
MIT;
s2=> COMMIT; -- wrong!
The only solution is a big lock, or at least to somehow figure out what
kind of locks might be required.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ECK will be violated.
>From an arbitrary subquery in a CHECK, it's hard to determine what kind
of locking semantics might be necessary for inserting transactions.
I think this is precisely what triggers are for. You define the error
condition and the locking semantics in one procedure.
; 10)
);
We'd need some big locks for that to actually be a true declaration.
All of this can be solved with triggered procedures, where you can
define the locks as needed.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
ed rows) than the HashAgg? It estimated exactly the
same number as it estimated for the output of the HashAgg.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
should be
identical.
Regards,
Jeff Davis
=> explain analyze select
-> a, b, c_max
-> from
-> (select a, max(c) as c_max from t group by a) dummy1
-> natural join
-> (select a, b from t) dummy2;
ort by any column, or arbitrary expression or function.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
iew an accurate way to
interpret pg_locks?
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
#x27;t find a function to determine if two lock modes
conflict. The function is somewhat thrown together so it may have a few
problems; I just included it so people can run the example view.
Regards,
Jeff Davis
CREATE OR REPLACE VIEW query_lock_wait AS
SELECT
l1.pid AS pid,
a1.
ble by providing the period data
type (which is indexable).
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s above) quite quickly.
Do you have an explanation for the slow IN performance? Do you mean
Postgres is slow with a value list or a subquery or both? Is it because
the execution of a particular plan is slow, or does some good plan not
exist in Postgres?
Regards,
Jeff Davi
on) independently.
The general way to implement a relation constraint is by using LOCK
TABLE to prevent other concurrent sessions from interfering (as you
suggest above). This obviously has very bad performance, which is why
UNIQUE indexes provide another synchronization mechanism at the sub-
transacti
on could still
insert something after the UPDATE but before the INSERT, so the unique
constraint violation can still occur.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
considered storing the key in its own two-column
table with a UNIQUE index and having the partitions reference it?
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
n ...
You can replace your table with a view and use rules to transform the
updates and inserts.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
lly equal to a new B?
Similar concerns apply to other changes in ENUMs, and for that matter,
they apply to the FK design, as well.
I would say the *actual* rating is the combination of the rating name,
and the version of the standards under which it was rated.
Regards,
Jeff
normalization that requires the
use of surrogate keys.
The approach suggested by Scott Marlowe is normalized as well as being
quite natural and simple. I think often this is overlooked as being "too
simple", but it's a quite good design in many cases.
Regards,
Jeff Davis
ace to do this because of the ZFS
copy-on-write implementation.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
.
>
> Thank you for your answer. I suggest to add a note in documentation ;-)
I think that it is undocumented on purpose.
"=" in SQL is generally for testing equality, and having one operator
mean two completely different things can be confusing. Therefore, ":="
g on how you want to use it, you may need to make it a set-
returning function (a.k.a. table function).
Regards,
Jeff Davis
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
locale, e.g. en_US,
supposed to have identical behavior on any platform for which it's
available?
If there is a standard of some kind, is apple violating it?
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
tocast.
A few comments:
* Keys should not behave differently from non-keys in comparisons.
* I think the distinction you're trying to make is the casting of
literals versus the casting of variables. All of the examples you gave
involved literals.
* If it's making
ifficulty of conceptualizing data structures
> as EAV is one of the big strikes against it. The quoted citation evidences
Agreed. It has many other strikes as well: for instance, it's difficult
to form any kind of meaningful predicate for a relation in an EAV
design.
Regards,
Jeff
uot; module that you are using. This issue did exist, and
was fixed, but perhaps you have one version behind or something.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ible) to get the 8.2 behavior
back just by adding/modifying casts.
If not, couldn't we just publish those casts so people can be backwards
compatible if they want?
Regards,
Jeff Davis
---(end of broadcast)---
TIP 3: Ha
th it:
http://www.postgresql.org/docs/8.3/static/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING
Although: why do you want to generate your own error? It seems like it
would probably be about the same as the error produced by the casting
failure.
Regards,
Jeff Davis
--
t;
> ERROR: relation "public.account_text_table" does not exist
PostgreSQL folds to lower case unless you put the name in double-quotes.
Try putting the table name in double-quotes.
E.g.: select has_table_privilege('johnf', 'public."Account_T
you have this problem still, send an email to the ruby-pg-general
mailing list (or post in the forum, or email me directly).
Regards,
Jeff Davis
---(end of broadcast)---
TIP 6: explain analyze is your friend
v) unlimited
>
If you are using FreeBSD, is it possible you ran into the kern.maxdsiz
kernel tunable? It can only be adjusted at boot, I think.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
;
> I have attempted the Select count method but it seems that I need something
> more.
>
> If anyone has any thoughts it would be much appreciated.
1. what did you do?
2. what did you see?
3. what did you expect?
Regards,
Jeff Davis
---(end of bro
omevalue + random() AS somevalue
FROM mytable WHERE uid NOT IN
(SELECT MIN(uid)
FROM mytable GROUP by somevalue)
Disclaimer: I haven't actually tested this query, but it looks about
right.
Regards,
Jeff Davis
---(end of broadcast)
UP BY somevalue;
Also consider just doing:
SELECT DISTINCT somevalue FROM mytable;
...if you don't need uid in the result set.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
of the application.
I don't think there's any way around fixing the application. You can
still use 8.2 with tsearch2 (which still work great) until the mapper is
fixed.
What is the name of the third-party product?
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
me other linux configuration options that make
invocation of OOM killer less likely.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Is there an existing tool that can infer the functional dependencies
implied by the keys in an existing database? Or just compute the
canonical cover of a set of functional dependencies?
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5
been adjusted to match."
Regards,
Jeff Davis
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
n't know exactly what you mean by "record-based log shipping", but
perhaps you're looking for something like Slony-I, which can be found
here:
http://www.slony.info
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5: don
franca
between two different applications at the same time.
> Link.
>
Did you intend to include a URL?
> One man's impedance mismatch is another man's layer of abstraction or
> "comms protocol" :).
>
Good point.
Regards,
Jeff Davis
d be renamed.
I think that was the point of the slashdot post: it creates confusion to
call two separate concepts by the same name.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 6: explain analyze is your friend
uot; by C.J.
Date and Hugh Darwen discusses this topic in Appendix E, and a related
topic in Appendix D.
They also propose, in detail with a lot of analysis, how they think that
type inheritance should work in the set of chapters on the Inheritance
Model, or "IM".
Regards
of the code), while data in a database needs to be consistent
across long periods of time. So, a well-designed database will hold
facts that have meaning in the real world and from which inferences can
be made. Mapping application data structures (which contain context-
sensitive information and
those records where a certain field has changed.
EAV will give you many problems down the line, and I don't think it will
solve anything for you.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
st using multiple tables
(like the alternative that I suggested) is the difference between
vertically partitioning and horizontally partitioning. Both seem like
good choices to me.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
lational design that is very flexible and doesn't
require the PostgreSQL-specific "INHERITANCE" feature. You don't need to
use natrual joins of course, it was just easier for this example.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
/4 will have almost no impact on shared buffers.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message
alculations show that you could save up to (47*3 + 8)*29384048
= 4378223152. So you might save up to 4GB with 8.3! It would be fairly
easy for you to check for yourself exactly how much by downloading the
beta.
Regards,
Jeff Davis
---(end of broadcast)---
be that some of your fields contain NULLs.
In SQL, NULL=NULL is _not_ true (more specifically, it is NULL).
To see what I mean, do "SELECT 1 WHERE NULL=NULL", it will return 0
rows.
Regards,
Jeff Davis
---(end of broadcast)---
T
On Mon, 2007-10-29 at 14:20 -0400, Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
> > One minor thing: I think it's still dependent on locale though, because
> > the output of pg_controldata is locale-dependent, right? It would work
> > fine for me, b
ine for me, but it would be nice if there was something that could be
released that anyone could use, including non-english installations.
Also, did you publish your pg_clearxlogtail program anywhere? I think
that would be helpful to many people, but I don't see it on pgfoundry.
Rega
base would not advance that location at all,
and I'm still trying to understand Tom's proposal well enough to know
whether that would be true or not.
If this doesn't get changed, I think we should archive every
archive_timeout seconds, rather than
MAX(archive_timeout,check
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.
>
whole
> system is healthy gets a lot more complicated if we stop sending
> empty WAL files.
>
> Could this at least be a configurable option?
>
A good point.
Keep in mind that even in the current system, your configuration is
variable based on the checkpoint_timeout setting.
Reg
docs/current/static/runtime-config-wal.html
"When this parameter is greater than zero, the server will switch to a
new segment file whenever this many seconds have elapsed since the last
segment file switch."
Regards,
Jeff Davis
--
ty space in WAL segments, you might look at
"pg_clearxlogtail" written by Kevin Grittner (search the archives or
pgfoundry). This allows you to gzip the files to basically nothing.
Regards,
Jeff Davis
---(end of broadcast)---
TIP
l is
that we would lose users...)
(2) it's good advocacy, i.e. not rude, insulting, demanding, or vulgar
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ole that needs access to a group of tables that all happen to
be within a schema.
This isn't critical, but for people who use an ORM that don't want to
think about the database, it's handy.
That being said, I'm not volunteering to write it, especially not in
response to a rude
tead is just rely on the unique index to report an error. If you
don't want an error, you should catch the error in pl/pgsql as described
here:
http://www.postgresql.org/docs/current/static/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING
That will perform better and allow you to disreg
know that all its children also need to be
> reparented as well.
Aren't there still some update anomolies with any schema representing a
DAG?
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
am also interested in graphs and trees in relational databases. Can
you recommend any good books? I particularly like CJ Date as an author,
but I can't find anything by him that specifically addresses this topic.
Also, how exactly is the database denormalized by using ltree?
Regards,
Je
s, do
you actually want access to postgresql's shared structures, or do you
just want your own area?
Regards,
Jeff Davis
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
hance of a power supply
failure, even if you don't have two independent circuits.
Oh, and if you're running linux make sure to use a safe setting for
these settings:
vm.oom-kill
vm.overcommit_ratio
vm.overcommit_memory
The default is not very safe for postgresql*. If a java process
.
> Touching a file doesn't really prove its working either.
>
Right. It's the best I have now, however, and should detect "most" error
conditions.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 1: if posting/rea
On Sat, 2007-09-15 at 12:40 +0500, rihad wrote:
> How can I efficiently return the minimum/maximum of two given
> expressions? Like SELECT MYMIN(a+b-c,d+e*f).
>
SELECT LEAST(a+b-c,d+e*f);
SELECT GREATEST(a+b-c,d+e*f);
Regards,
Jeff Davis
---(end of
ions about logging options, etc.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
intercept the "restored log file ... from archive" messages.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ou want than you think. If you have
a trigger that issues a "NOTIFY foo" for any INSERT/UPDATE/DELETE, it
will only generate one notification per transaction, and only as part of
a successful COMMIT.
Then, your application can check for this notification periodically or
perhap
name in the
> config MUST be unique.
>
You might look at pgpool-II, that can load balance queries.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 6: explain analyze is your friend
bles while the indexes are being created. That might help reduce your
downtime window.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 6: explain analyze is your friend
nd should also record the other
information that you need. This strategy may perform better than using a
set-returning function.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
nd email us when a partition gets above
> > 90% full.
>
>
>
> Wow, Nagois seems like a superb tool. Thanks for the recommendation!
>
You might also consider OpenNMS.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
nction is only called
once. It sounds like I may need to beware of future changes, however.
What is the small extra price for large tables though?
Thanks for the help!
Regards,
Jeff Davis
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
t calls, which would then not be audited.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
view log_yadda as
> select yadda
> union all select null, null, null from log_func();
>
Interesting idea, I hadn't thought of that. Not perfect, but
interesting.
The "returns query" might help reduce the penalty of using a SRF. Maybe
I'l
is for logging, not auditing. There's some
overlap, but logging doesn't seem to do everything that I need directly.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 6: explain analyze is your friend
to
discussions, etc.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
>
> However it does not work. ANy suggestion?
It looks like you want to use:
WHERE d_base.id = ANY(integer_array)
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
s that use simple locking strategies can simply update the
> record in place.
I think in some databases that use locking, an INSERT can actually block
a SELECT, and vice-versa. So wouldn't that mean PostgreSQL MVCC is
better for INSERT performance?
Regards,
Jeff Davis
-
want to read the actual internal working sequentially. Where
> can I find this information?
The source code is the best place. It's very well-commented, and even if
you don't read C you can understand a lot about the internals just by
reading the comments.
Re
st put the newline directly in the SQL:
INSERT INTO mytable(myattr) VALUES('first line
second line
third line');
Regards,
Jeff Davis
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
me relevant pgfoundry projects:
http://pgfoundry.org/projects/nagiosplugins/
http://pgfoundry.org/projects/pgtools/
Regards,
Jeff Davis
---(end of broadcast)---
TIP 6: explain analyze is your friend
ase there is no filter on "score" at all, "score" is
just a sort order.
A compound index should give you what you want.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore yo
201 - 300 of 606 matches
Mail list logo
