in the
top-level row estimate; but this one is a simple scalar condition and I'd
expect our stats code to be able to deal with it. Are the stats on zip
up-to-date? Maybe you need to increase the stats target for it.
regards, tom lane
---(end
PostgreSQL Admin [EMAIL PROTECTED] writes:
username | varchar(100)| constraint username =8 and username =100
Perhaps you mean length(username) = 8 and so on?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you
that with a SQL-language wrapper function.
It's pretty grotty on the whole, but should do for a one-time problem.
BTW, check the archives, because I think this type of problem has been
discussed before --- somebody may have already posted usable code.
regards, tom lane
the number of result rows is the least common multiple
of the period lengths.
This is one of the reasons that SRF-in-targetlist is deprecated ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your
want substring() not substr().
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
message.
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
A. Kretschmer [EMAIL PROTECTED] writes:
am Thu, dem 05.04.2007, um 1:27:25 -0400 mailte Tom Lane folgendes:
I'm betting the problem is poor vacuuming practice leading to lots of
dead space. There's no way it takes 22 sec to read 10 rows if the
table is reasonably dense.
This was my first
[EMAIL PROTECTED] writes:
FATAL: sorry, too many clients already
You need a larger max_connections setting.
LOG: unexpected EOF on client connection
I think pgbench just dies ungracefully if it gets a connection failure.
regards, tom lane
operation which happens every few
months, if that.
true, but without an index, it still has to scan the table just to be sure.
If this is only a once-in-awhile thing, maybe you could build the index,
do the deletes, drop the index ...
regards, tom lane
: 22204.476 ms
(3 rows)
which version?
I'm betting the problem is poor vacuuming practice leading to lots of
dead space. There's no way it takes 22 sec to read 10 rows if the
table is reasonably dense.
regards, tom lane
---(end of broadcast
gather that you are reading 8.2
documentation and trying to apply the info to some previous version that
doesn't have SELECT INTO STRICT (which you failed to use anyway...)
You probably want to test the magic FOUND variable instead --- see the
plpgsql docs.
regards, tom
in
check_sql_fn_retval. I think that logic was designed before we had an
idea of VOID-returning functions.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.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
Sabin Coanda [EMAIL PROTECTED] writes:
I used the function array_to_string, and I found it ignores NULL values,
e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
Do you have a better idea?
regards, tom lane
---(end of broadcast
Richard Huxton dev@archonet.com writes:
Tom Lane wrote:
Sabin Coanda [EMAIL PROTECTED] writes:
I used the function array_to_string, and I found it ignores NULL values,
e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
Do you have a better idea?
If you're being strict
the EXCEPT wasn't eliminating any rows.
You need to wrap SELECT count(order_id) FROM ( ... ) around the entire
EXCEPT query to get what you want.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
, only changeable by superusers. This
would still allow the setting to be turned off for use by legacy
applications (probably by means of ALTER USER) while removing the
objection that non-privileged users could break things.
regards, tom lane
---(end
hubert depesz lubaczewski [EMAIL PROTECTED] writes:
On 3/19/07, Tom Lane [EMAIL PROTECTED] wrote:
ERROR: WITH CHECK OPTION is not implemented
It seems perfectly clear to me ...
errors is clear, but maybe the information about check option should
be removed from docs to 8.2
Karthikeyan Sundaram [EMAIL PROTECTED] writes:
I am getting an error message:
ERROR: WITH CHECK OPTION is not implemented
what does this mean?
It seems perfectly clear to me ...
regards, tom lane
---(end of broadcast
the weather reports example in the SELECT reference
page). Unfortunately that's a Postgres-only construct. If you want
something portable then you'll need something messy with subqueries...
regards, tom lane
---(end of broadcast
not --- for full-table scans it's often faster
to sort than to try to use an index.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
superuser, course)?
You'd have to change the code --- the syslogger process inherits umask
077 from the postmaster.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
of ram
and 8 processors.
What postgres version?
8.2 should be considerably faster than prior releases due to Heikki's
fixes to let indexes be scanned in physical order during VACUUM.
regards, tom lane
---(end of broadcast
for production
... if some PHB is trying to force that on you, I suggest resigning from
the project before you get blamed for the inevitable disaster.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet
undertake new
development on a server version older than 8.1.x.
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
Florian Weimer [EMAIL PROTECTED] writes:
For each value in the first column, I need one (and only one) matching
row from the table. A possible solution is:
SELECT DISTINCT ON would do it, if you don't mind a non-portable solution.
regards, tom lane
.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Osvaldo Rosario Kussama [EMAIL PROTECTED] writes:
|Does this mean the filename in COPY command can not be a variable?|
Got it in one.
You can use EXECUTE to put together commands that require a variable
in places where PG doesn't allow one.
regards, tom lane
of the table, you'll still
have problems. In that case I'd advise putting the values into a temp
table, ANALYZEing same, and doing WHERE foo IN (SELECT x FROM tmp_table).
regards, tom lane
---(end of broadcast)---
TIP 7: You can help
actually you don't need all that much extra
notation; this seems to work:
WHERE foo IN (VALUES ($1),($2),($3),...)
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
: you need to identify
and fix (or delete) the offending row(s). In this case you might try
tests like bin_end_date_time '1 Jan ' and so on to see if you
can determine exactly which rows are bad.
regards, tom lane
---(end of broadcast
in the
WHERE clause is just referring to the underlying column (and thus making
the IS NULL test in the CASE rather pointless).
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
happens after that depends on the outer query, but if you don't have any
joining or grouping then it's a reasonably safe bet that the final
output will be in the same order.
regards, tom lane
---(end of broadcast)---
TIP 2
, then:
I) T shall not be a grouped table.
II) QS shall not specify the set quantifier DISTINCT
or directly contain one or more set function
specifications.
regards, tom lane
8.2 can do it too.)
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
UNION SELECT 51 AS i, true AS d ) x ORDER BY i;
PK_ID | Deleted
---+-
49 | t
51 | t
(2 rows)
regression=#
What PG version are you using, exactly?
regards, tom lane
---(end of broadcast)---
TIP 4: Have
multiple commands in the same EXECUTE
string --- if we were going to do anything to fix this, I think it
would be along the lines of enforcing that advice. Trying to make the
world safe for it doesn't sound productive.
regards, tom lane
---(end
Michael Fuhr [EMAIL PROTECTED] writes:
On Thu, Feb 08, 2007 at 10:32:25AM -0500, Tom Lane wrote:
My advice is not to try to execute multiple commands in the same EXECUTE
string --- if we were going to do anything to fix this, I think it
would be along the lines of enforcing that advice
.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
details, nor even an EXPLAIN.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
how many
rows are likely to match.
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
.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
/fuzzystrmatch
(along with a few other alternatives).
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
the situation ...
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
field (or any other auto-generated field...)
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
debugging purposes). I think you may have confused
this with use of the lastval() function --- currval() or sometimes
lastval() are the appropriate way to get the last-assigned value.
regards, tom lane
---(end of broadcast
Mario Splivalo [EMAIL PROTECTED] writes:
On Thu, 2007-01-25 at 11:09 -0500, Tom Lane wrote:
I believe the problem is that for a SQL function we parse the whole
function body before executing any of it. So you'd need to split this
into two separate functions.
Having two function complicates
this
into two separate functions.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
shmall is 65536 page
And how big is a page?
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
available vary across OSes ... try locale -a
for a list.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
problem.
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
.
MHO: if they don't have a way to adjust the verbosity of their error output,
they definitely should.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating
, in pgAdmin I dunno
but you're asking the wrong person...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
into or out of the Master table?
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
chester c young [EMAIL PROTECTED] writes:
cannot get those quotes around the value.
Use backslashes.
regression=# \set var '\'value\''
regression=# \echo :var
'value'
regards, tom lane
---(end of broadcast)---
TIP 7: You
:
select sum(case when i.count = 0 then s.cost else i.count * s.cost end) ...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
interval_in which does a fairly nontrivial parsing process.
The second way is basically just a multiplication, because
'1 day'::interval is already a constant value of type interval.
regards, tom lane
---(end of broadcast
-
cate of R.
B) Otherwise, T contains no duplicate of R.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
for interval constants is so
bizarre and non-orthogonal it's not worth dealing with ...
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
You need to make the second argument type name, too, if you have a lot
of users.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Rajesh Kumar Mallah [EMAIL PROTECTED] writes:
what is the proper way for iterating over column names of a table using
SPI_* functions.
You need to pay attention to the attisdropped field of the TupleDesc
entries.
regards, tom lane
---(end
at it to *not* have that behavior.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
a value
that's relative to the named zone.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
, you can contort the query to
get the IN restriction inside the outer join:
select * from
(select * from documents
where documents.doc_num in (select doc_num from documents limit 10)) ss
left outer join comments on (ss.doc_num = comments.doc_num);
regards, tom lane
that ...
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
of the outer join, but it's not apparent
that that idea is meant to negate a domain constraint. And yet, if it
does not, then an outer join with a NOT NULL domain column on the
nullable side is just invalid.
regards, tom lane
---(end of broadcast
= null where id = r.id;
end;
end loop;
and then do the ALTER TYPE after you've cleaned the data.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating
of identifiers. Now certainly
we are not doing exactly what the spec says, but what you ask is even
less like the spec's requirements.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
Rajesh Kumar Mallah [EMAIL PROTECTED] writes:
data-time_stamp =
DirectFunctionCall1(timestamptz_in, CStringGetDatum(now));
This code is incorrect, as timestamptz_in takes three arguments.
regards, tom lane
---(end of broadcast
CREATE FUNCTION foo ...
RETURNS ...
AS $$DECLARE -- this is line 1
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
of data corruption, in
that transactions made since your last checkpoint may be only partially
applied. I wouldn't recommend a setup in which xlog is less redundant
than your main storage array.
regards, tom lane
---(end of broadcast
.
Drop the alias on the outer join (the t). Per SQL spec, that masks
table names (and aliases) within the join from the rest of the query.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL
Adrian Klaver [EMAIL PROTECTED] writes:
On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
This should work --- in PG 8.1 or later.
The documentation for pl/pgsql in 8.1 and higher says different.
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS
, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
that won't conflict. In general,
don't use plpgsql variables that are named the same as any SQL tables or
columns you need to mention in the function.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support
of estimating this query well
because they don't keep any statistics about the contents of functional
indexes. 8.0 and up do, so they'd probably do a lot better with this.
If I were you I'd be trying to migrate to 8.1.5, not anything older.
regards, tom lane
/8.1/static/runtime-config-custom.html
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
takes two arguments?
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
://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php
(note that none of the first few responses got the point :-() Also
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00209.php
regards, tom lane
---(end of broadcast)---
TIP 5
Jose [EMAIL PROTECTED] writes:
If I try use
select unidade_regiao.(estado_sigla) from unidades
No, you should do
select (unidade_regiao).estado_sigla from unidades
regards, tom lane
---(end of broadcast)---
TIP 7: You
to get. Works perfectly is content-free.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
to make of it. If it happens again, we need to look
more closely.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about
to be frozen no
later than initdb time, for exactly the same reasons we freeze
locale then (hint: index ordering).
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
there, there is no way to impute a rowtype to the
table.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
?
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
Jeff Frost [EMAIL PROTECTED] writes:
On Thu, 2 Nov 2006, Tom Lane wrote:
This seems pretty darn weird. I am wondering about corrupt indexes ---
can you find the indicated key in either table if you set
enable_indexscan and enable_bitmapscan to 0?
test_tracking=# begin;
BEGIN
test_tracking
on tenk1_unique1 (cost=0.00..36.38 rows=489 width=0)
(actual time=0.027..0.027 rows=0 loops=1)
Index Cond: (unique1 = ANY ($1))
Total runtime: 0.478 ms
(5 rows)
This is not SQL-standard syntax IIRC, but then foo IN () would
certainly not be either.
regards, tom lane
.
If you want to be 100% certain, shut down the postmaster while copying,
but unless the index file is pretty large I think that's not necessary.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support
Jesper Krogh [EMAIL PROTECTED] writes:
Tom Lane wrote:
FWIW, as of 8.2 the best option will probably be to use
col = ANY (array), which does support zero-length arrays
if you use either an out-of-line parameter or an array literal.
That looks nice.. is ANY in the SQL-spec?
ANY is, but I
. That can't be acceptable --- it's going to break any
application that does any nontrivial analysis of what it sees there,
not to mention that it violates various primary key constraints in
the information schema specification.
regards, tom lane
---(end
of breaking other people's applications. Perhaps you
should consider fixing your app instead.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
column you mean error. I am
interested to see where you find support for that in the spec...
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index
the moderation software
to auto-approve pending messages from someone who's just subscribed, but
perhaps that's a lot of work. I haven't looked at that code, so I'm not
volunteering ...
regards, tom lane
---(end of broadcast)---
TIP
the result of the cast to timestamp
(implicitly without time zone), then applying the AT TIME ZONE operator.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
familiar with any of the common scripting languages
they're based on. The other PLs don't do implicit plan caching so
they won't have problems with temp tables; but it does mean knowing
still another language and putting up with some notational inconvenience.
regards, tom
that in HEAD, though
I did find out that a zero or negative payment_period makes it recurse
until stack depth exceeded.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
be able to
use this, which does work in 8.1:
select '2006-07-13 09:20:00'::timestamp at time zone 'EST5EDT';
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
501 - 600 of 2222 matches
Mail list logo
