that
a timezone name shouldn't contain digits ... which is bogus, but we'll
have to think carefully about how to improve it ...
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
Michael Fuhr [EMAIL PROTECTED] writes:
On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote:
No, I don't think so, because the DELETE will already be holding
exclusive lock on the doomed PK row, which any would-be inserters of
matching FK rows will be blocked on. AFAICS the DELETE should
integrity trigger, maybe it would work...
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
tables. Zero-column tables are not in
themselves very useful, but disallowing them creates odd special cases
for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
restriction.
regards, tom lane
---(end of broadcast
.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
with a
very slow clock-reading capability. Each node output row counted by
explain analyze takes two gettimeofday() calls, and apparently it's not
unusual for those to take several microseconds on cheap motherboards,
even when the CPU is nominally very fast.
regards, tom lane
will
be covered by the 8.2 improvements. There isn't any understanding of
how to commute joins and unions though ... (offhand I'm not even sure
of the conditions under which such a thing would be safe).
regards, tom lane
---(end of broadcast
select FOR UPDATE OF a in this situation, it's just
the B side that is problematic.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
to have an index on the
referencing side, but you pay for it when you update or delete in
the master table...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
have changed. (To name only the most obvious
problem...)
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
the old results to apply justify_hours() to the
subtraction result for themselves. Not sure what the fallout would be,
though.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives
some differences in the calculation
compared to a plain timestamp subtraction.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
of
cosmetic code rearrangements and added a regression test for the
problem.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
there are
appropriate operators at least as far back as 7.3.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
? A restorecon on the postgres executables and everything
under /var/lib/pgsql might help.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Kyle Bateman [EMAIL PROTECTED] writes:
Is there an easy way to get postgres to
spit out the SQL statement it was parsing when it generated the error?
log_min_error_statement = error is what you're looking for.
regards, tom lane
---(end
that you see UPDATE 0 right after that is
further evidence for this theory --- after the failure, there's nothing
in fragment_table, so the UPDATE just falls through.)
regards, tom lane
---(end of broadcast)---
TIP 4: Have you
= dps.packet_uuid)
Total runtime: 18205.880 ms
If packet_status is large, that seems like a perfectly reasonable plan
to me. If not ... what data type is packet_uuid? Is its equality op
marked mergeable or hashable?
regards, tom lane
---(end of broadcast
the equality function is
marked volatile, and so the planner is afraid to try to use it for
merging or hashing. (It's also not marked strict, which means you can
trivially crash the backend by passing it a null ...)
regards, tom lane
---(end of broadcast
to copy it.
The same goes for other pseudotypes.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so
to have a length word at the front.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
will probably destroy the ordering
by b.code.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
). It has to be one that
expects LATIN1 encoding.
The current regex code is generally not able to deal with locale-specific
behaviors in UTF8 encoding, but it should work for single-byte encodings
as long as you've got the locale setting right.
regards, tom lane
-report example in the SELECT reference page if you have
no idea what I'm talking about.)
this lookup does not have to be especially fast.
Good ;-) ... otherwise changing your schema would definitely be indicated.
regards, tom lane
---(end of broadcast
/SHARE clause not found in
FROM clause
Use the alias, ie, DD. Remember that an alias hides the real name of
that table for all purposes in the current query.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched
down to the core SQL engine. So it fails on OLD.bar not being
defined, long before the expression evaluator gets to think about
whether TG_OP = 'INSERT' or not.
So, yeah, you want to rewrite it as two separate IF-tests.
regards, tom lane
---(end
Mario Splivalo [EMAIL PROTECTED] writes:
Now, here is what happens if I try this in postgres:
pulitzer2=# select '+mario' ~ '^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*$';
I'm thinking you've forgotten to double your backslashes.
regards, tom lane
(or turn it off
temporarily) and redo initdb. You can find more info if you search
our archives for selinux.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
, tom lane
---(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
in the future, but that's where it stands today.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
boolean
Well, is it insertPoints or inserirPontos? Is schema base in your
search path?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
to run it on.
The 1Gb upper limit for MySQL is a pretty academic number too, for
exactly the same reasons. Have you tried putting a 1Gb query string
into MySQL?
regards, tom lane
---(end of broadcast)---
TIP 1: if posting
is in the
SQL standard, it's certainly not going anywhere.
regards, tom lane
---(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
the database often enough? Is this table suffering
from bloat?
That's my bet. With numbers like those, that table has to get vacuumed
every few minutes to keep performance from going into the tank.
regards, tom lane
---(end of broadcast
.
Even if we were willing to do that, I think we'd also have to give
up using bison to generate the parser :-( because some constructs
would require more than one-token lookahead.
regards, tom lane
---(end of broadcast)---
TIP 5
schedule where sessionnumber = 165
order by (select min(meetingday.date) from meetingday
where schedule.id = meetingday.scheduleid);
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
with regprocedure:
select 'drop function ' || p.oid::regprocedure || ';' from
pg_proc p join pg_namespace b on (p.pronamespace=b.oid) where nspname='public';
[ tries it ... ] ... except that there's no regprocedure-to-text cast.
How annoying :-(
regards, tom lane
?
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Kyle Bateman [EMAIL PROTECTED] writes:
Tom Lane wrote:
Before 8.2 the optimizer has no ability to rearrange the order of outer
joins. Do you have time to try your test case against CVS HEAD?
OK, I figured it out--grabbed the latest snapshot (hope that is what you
need).
My results
and p.left 2345 and p.right 2345;
so that you can constrain the range of left values scanned.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
work. One possibility is that you created the
function in a schema that isn't part of the application's search path.
Other than that, look for *really* silly errors, like not creating the
function in the same database the application is connected to ...
regards, tom lane
--- do you
have a real use-case for suppressing mtime updates?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
.
regards, tom lane
---(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
Andrew Hammond [EMAIL PROTECTED] writes:
Alternatively, you already have the USING clause to tell you how to
alter the data. How about using it to alter the default as well?
The reasons not to do that are already set forth in the ALTER TABLE
man page.
regards, tom lane
, but with the change
the DROP part would be re-ordered to occur first. So maybe the
cure is worse than the disease. OTOH that's a pretty silly example,
whereas wanting to ALTER TYPE and fix the default in a single command
is quite reasonable. Thoughts?
regards, tom lane
Rod Taylor [EMAIL PROTECTED] writes:
On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
Hmm ... the way I would have expected to work is
alter table posts
alter column deleted drop default,
alter column deleted type char(1)
using (case when deleted then 't' else 'f' end),
alter column
value being bogus. I'm pretty well
convinced now that we're looking at a problem with corrupted data. Can
you do a SELECT * FROM (or COPY FROM) the table without error?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't
region is then just after the last ctid you see.
You can look at those blocks with pg_filedump -i -f and see if
anything pops out. Check the PG archives for previous discussions
of dealing with corrupted data.
regards, tom lane
---(end of broadcast
, provoke the
crash, and do bt --- search for gdb in the archives if you need
details).
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
not gonna
be able to afford when your back is to the wall.)
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
of arguments shown for slot_attisnull suggests that all we're
going to get is a list of function names, without line numbers or
argument values. If that's not enough to figure out the problem, can
you rebuild with --enable-debug to get a more useful stack trace?
regards, tom lane
remains to be seen ... we need that stack trace!
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Marc G. Fournier [EMAIL PROTECTED] writes:
if I do:
NOW() + '? day'::interval
it, of course, takes the ? as a literal ... so is there some way I can do
this such that I can do the placeholder?
NOW() + n * '1 day'::interval
n can be any numeric value ...
regards, tom
plpgsql;
You've got a small error in the layout: the DECLARE part goes before
BEGIN not after. Swap the first two lines of the function.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives
don't want the users messing with, and not get too worried about whether
they know the tables exist or not.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
of (at least) select privilege to PUBLIC.
You'll need to revoke that if you don't want every user to have that
privilege implicitly.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
to the
tmp_created checkpoint, but I don't think this will pick up the new
rows in the real table, and the INSERT will fail again.
Why do you think that? If you're running in READ COMMITTED mode then
each statement takes a new snapshot.
regards, tom lane
sathiya moorthy [EMAIL PROTECTED] writes:
what is the use of the cmin system column
http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze
(arrayvalue) syntax is what you want. Note
however that this can't be turned into an indexscan on field1 in existing
releases (8.2 will be able to do it).
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9
into non-capturing parentheses.
Until this is fixed, your best bet is to use the POSIX-regexp form of
substring(). You can't sneak non-capturing parens through
similar_escape, because it'll try to escape the ? ...
regards, tom lane
---(end
break anything of interest.
This would certainly not be so if we were to randomly replace integer
constants in general WHERE conditions with non-constant values.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you
, so probably the best answer is
don't do that. I think it's entirely too error-prone anyway ...
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe
help you figure out which one.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
.
The unique-index hack that Michael suggested amounts to hand-optimizing
the sub-SELECT constraint into something that's efficiently checkable.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet
Keith Worthington [EMAIL PROTECTED] writes:
The following is a section of code inside an SQL function.
SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause
(there can be only one).
regards, tom lane
---(end of broadcast
do you arrive at that conclusion? I haven't done the math,
but by eyeball an average of four-something days doesn't look out of
line for those values.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase
://www.postgresql.org/docs/8.1/static/functions-info.html
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
.
regards, tom lane
---(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
.
regards, tom lane
---(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
. PG doesn't (currently) have direct support for materialized
views, but it has some tools you can get the effect with.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
present in 7.3), and even then it didn't have anything to do with
support for multibyte encodings like UTF8.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
FATAL: could not create any TCP/IP sockets
There should be more info than that --- AFAICS all the failure paths in
that code emit LOG messages. Perhaps you have log_min_messages set too
high to allow the info to come out?
regards, tom lane
pg_hba.conf.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
your system so it's all on the same page about whether IPv6 is supported.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
time trying to outsmart the language.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
to the backend ...
regards, tom lane
---(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
where the right people to ask hang out. Don't have
the address at hand.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
, this will perform a lot better than an array-based
translation.
And no, in neither case will you be able to import that file without
massaging it first.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
.
regards, tom lane
---(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
values in
http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
:
SELECT DISTINCT Key FROM MRTPContactValue
Try
SELECT Key FROM MRTPContactValue GROUP BY Key
The select distinct code is a bit old and crufty, GROUP BY is usually
smarter.
regards, tom lane
---(end of broadcast)---
TIP 1
ON (email_broadcast_id) *
FROM email_broadcast_history
ORDER BY email_broadcast_id, date_sent DESC
You order by the DISTINCT ON fields, then one or more additional fields
to select the representative row you want within each DISTINCT ON group.
regards, tom lane
Aaron Bono [EMAIL PROTECTED] writes:
Is this SQL-99 compliant or a PostgreSQL specific query? I really like it
and have never seen this before.
DISTINCT ON is a Postgres-ism, I'm afraid. It's pretty handy though.
regards, tom lane
---(end
.
regards, tom lane
---(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
.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
the lines of
case when xy then x else y end
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
(do you know
the true figure?). A larger statistics target would probably produce
a better number and hence a better join estimate.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives
?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
for user_url_tag.user_url_id contain?
Have you analyzed that table recently?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Catalin Pitis [EMAIL PROTECTED] writes:
ERROR: plan should not reference subplan's variable
Do you have any clue why does this happen?
It's a bug :-(. Thanks for the test case --- I'll look into it tonight
or tomorrow, if no one beats me to it.
regards, tom lane
Catalin Pitis [EMAIL PROTECTED] writes:
ERROR: plan should not reference subplan's variable
I've applied a patch for this; will be in 8.1.4.
http://archives.postgresql.org/pgsql-committers/2006-05/msg00016.php
regards, tom lane
---(end
should be in form:
obj_id1 o1att1_value o1att2_value o1att3_value
obj_id2 o2att1_value o2att2_value o2att3_value
...
This isn't an outer-join problem, it's a crosstab problem. Try the
crosstab functions in contrib/tablefunc.
regards, tom lane
query, but coverage_area is being used outside
an aggregate without having been grouped by.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
to the specific variable occurrence
that it's complaining about. That would help at least somewhat in
cases like this.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
-aR would give more
information than guessing.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Markus Schaber [EMAIL PROTECTED] writes:
Tom Lane wrote:
Instead of assuming anything, why don't you look in the tablespace
directory and see what's there? A quick ls -aR would give more
information than guessing.
There's plenty of stuff there, 8.8 Gigabytes in total. The question is
how
601 - 700 of 2222 matches
Mail list logo