's what I'd expect it to do, all right. Please define "doesnt
provide the right results".
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the un
on documentation in String
> Functions and Operators.
Done; I also added its sister function quote_ident. See the devel
docs at
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-string.html
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ression=# select foo();
foo
---
1
(1 row)
What PG version are you using?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
to the top of anyone's to-do list.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
t;ol"? A really grotty way would be to just give
qty_onhand a dummy third parameter and write
qty_onhand( ol.itemcode, ol.uom, o.whatever ) > 0;
but maybe you have a less obscure alternative available.
regards, tom lane
-
e, but most likely that doesn't buy enough
locality of reference to be worth the trouble.
Still it seems like a useful avenue to investigate. If you want to
pursue it further, this is not the list to be discussing it on;
pgsql-hackers is the place for such discussi
"Jörg Holetschek" <[EMAIL PROTECTED]> writes:
> CASE
> WHEN ((focus <> NULL) AND (epilepsy_class = 'f')) THEN
> TRUE
Try "focus IS NOT NULL".
regards, tom l
them in any case.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
lect add.id from (select 'abc' union select 'def' union select 'ghi') add
>(id)where not exists (select from role where add.id = role.id)
Might be worth writing "union all" not "union". This suppresses union's
check for duplicated rows, which y
er write a rule of the form
ON UPDATE TO b DO UPDATE b ...
because it *will* be an infinite loop, condition or no condition.
Consider using a trigger instead.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
quot;GET DIAGNOSTICS varname = ROW_COUNT" (gotta love these Oracle-derived
syntaxes :-()
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
e bit but not figured out where
the cleanest place to do it is.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
for reo_id is too fragile to consider using in any case.
You are making way too many assumptions about when defaults will be
evaluated relative to other actions (such as rule/trigger firings).
I'd suggest that you have no default for column reo_id, and instead
have a BEFORE
Yup. I've applied the attached patch, which seems to solve the problem
in CVS tip. I haven't tested it in the REL7_2 branch, but I believe it
will work if you want to patch locally.
regards, tom lane
*** src/backend/optimizer/path/indxpath.c.orig Fri Jun
mplicit casts and you have no type system at all. But in 7.3 there
should be no reason to object to an explicit-only cast from numeric
to text or vice versa.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/readin
sions about increasing
the default length.
regards, tom lane
---(end of broadcast)---
TIP 3: 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
t 320, if part is 200K rows; that should be enough to
produce at least some change of plan.) You could try patching your
local installation likewise.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at o
t;,
Yeah, you're right. I'm hoping to see a better answer in 7.3, but
right now it's a horrible kluge ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
tain limited applications, if it were equally
well implemented. Or it might not. You'd have to invest a lot
of work to find out, and might well discover that your work
was wasted.
regards, tom lane
---(end of broadcast)---
Markus Bertheau <[EMAIL PROTECTED]> writes:
> is there a difference performance-wise between select count(1) and
> select count(*)?
Nope. In fact, the latter is converted to the former during parsing.
regards, tom lane
---(end
e towards SQL-compliant interpretations of
these type names ...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
at the "weather report" example in the SELECT reference
page.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED]
ill prevent this sort of mistake...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
orrect is
SELECT * FROM
(SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
UNION
SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
) ss
GROUP BY c1
ORDER BY c2;
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched
Milosz Krajewski <[EMAIL PROTECTED]> writes:
> Can I force postgre do it my way ?
Possibly. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
regards, tom lane
---(end of
't help in those cases.)
The MAX/MIN issue will probably be addressed someday, but since there
is a good workaround it's not very high on anyone's TODO queue. We have
many more-pressing problems.
regards, tom lane
---(end of broadc
y.
No, the correct answer is "read the TODO list"...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
or went back further than 7.2, actually.)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
r.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/adt/ruleutils.c
Note this is a server-side patch; the bug is not in pg_dump.
So using a 7.3 pg_dump against your existing server won't help.
You might want to apply the back-patch just so that you can
dump when the time comes to go to
- did you check if you can CREATE CONSTRAINT TRIGGER on a dropped
> column - I think I neglected to look at that in the patch I submitted
> originally.
I'm pretty sure that won't get past the ATTNAME cache patches, but try
it...
regards, tom lane
Bruce Momjian <[EMAIL PROTECTED]> writes:
> What is in the 7.2.X CVS that we would want to release?
CVS logs show the following as post-7.2.1 changes in REL7_2_STABLE branch.
Draw your own conclusions ...
regards, tom lane
2002-06-15 14:38 tgl
* sr
ents from the arrays ?
No, those are support functions for GIST indexes on intarrays. They're
not useful to call directly.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with th
[EMAIL PROTECTED] writes:
> while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | psql
>-d filerian ; done 2>&1 >/dev/null &
Apparently the file table is getting larger. That means the count()
aggregate will take more time to run.
the desired constraint.
In any case you'll probably have to wait for 7.3.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
;
although given the logic used later I wonder whether what you are after
isn't really
set_time := current_date - var_history_age_limit;
BTW I'd declare set_time as timestamp or timestamptz if I were you;
datetime is an obsolete datatype name that's not going to be accepted
d
this. It will dump the table definition first, with the DEFAULT clause,
and so you'll have to do manual surgery on the dump file if you ever
need to reload.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and
en't got
enough RAM for thirty concurrent server processes, and so the system is
wasting a lot of time swapping processes to disk. What are the hardware
parameters, anyway?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
tgres/xfunc-sql.html
The first couple of examples seem to cover the territory ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister Y
hout being able
to try to reproduce the example. Could we see the full declarations of
the tables involved? (pg_dump -s output would be good.)
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
soon as these two view columns
aren't obviously equal. If you really need a view that works just like
this, you can work around the bug by making the null columns trivially
different, perhaps
> select distinct ... ,
> null::timestamp with time zone::timestamp without time zone,
ater we'll
probably get around to implementing that, and that would solve your
problem as long as you declare location.ident properly.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
accept a column list, so you can get the same effect just
with COPY.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
grupo AS grupo" in the
explain output is "epr_ord_grupo g" ... I'm also wondering if any of
the tables used in the queries are really views, and if so what the
view definitions are.
regards, tom lane
---(end of broadcast)-
considered the preferred datatype in the string category.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
OUP BY, WHERE, and the other previous
> clauses, so it makes more sense to me to have it at the end.
In the current implementation, FOR UPDATE acts after LIMIT does, so
putting it last would make sense --- SQL's optional clauses for SELECT
generally act left-to-right.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Wei Weng <[EMAIL PROTECTED]> writes:
> What could have gone wrong? I must have left the trace of user foobar
> somewhere in my system but I couldn't find it.
PGUSER environment variable?
regards, tom lane
---
-- wrong
(1 row)
regression=# SELECT date_part('epoch','2002-08-28'::TIMESTAMPTZ);
date_part
1030507200 -- right
(1 row)
What context are you testing in, and what do you get exactly?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
EXTRACT(EPOCH FROM interval) is designed for this ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
IN"
Yup.
> DB2 simply ignore the FOR UPDATE ...
> I think that is the correct form...
That seems obviously wrong to me. What good is a FOR UPDATE if it fails
to lock down the rows that created your result?
regards, tom lane
---(end of br
the noise once you start
looking at production-sized cases.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
2}$'),
else it will accept values you don't want...
regards, tom lane
---(end of broadcast)---
TIP 3: 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
ifference
has anything to do with the view here ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Thu, 12 Sep 2002, Tom Lane wrote:
>> What's the datatype of id_user, and why are you quoting the compared
>> constant in some cases but not others? I don't think the difference
>> has anything to do with the
CT ... FROM participants
WHERE typenr=2 LIMIT 172)
) ss
ORDER BY zip;
Not sure if the inner set of parens is essential, but it might be.
The outer SELECT superstructure is definitely necessary to give a
place to hang the ORDER BY on.
regards, tom lane
rely on.
If you must use UNION (to eliminate dups) then it won't work.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
sets.
That's the hard way; just do CREATE TEMP SEQUENCE ... works in prior
releases too ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
sing at the outer
level, but the example as given is just fine.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
nking to or from
that table?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
rely irrelevant to the example as given.
There is no reason to reorder the subselect output, and we won't.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
e now discovering, does not work with non-C sorting
rules (so the system doesn't try to apply it).
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
backend(or app.), the query is released...
I think your second backend is holding a lock that the third one needs.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregiste
x27;d only need to pay attention to cmin
if you wanted to notice changes within your own transaction).
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
example of what you are doing, rather than
your interpretation of what's going wrong.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
g the equivalent + operator instead:
regression=# select "timestamp"(date '1998-02-24', time '23:07');
timestamp
-
1998-02-24 23:07:00
(1 row)
regression=# select date '1998-02-24' + time '23:07';
?column?
=?iso-8859-1?Q?Thorbj=F6rn_Eriksson?= <[EMAIL PROTECTED]> writes:
> Thank's Tom Lane & Stephan Szabo for pointing out the problem to me.
> After some testing it turned out that the swedish locale, 'sv_SE', doesn't
> handle sorting spaces as expected, which
the current query?
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
s not conforming to the standard.
As you say, it's been discussed before. We concluded that the spec
defines the behavior as implementation-dependent, and therefore we
can pretty much do what we want.
If you want exact current time, there's always timeofday().
functions, rules,
triggers, and all that other stuff that makes it interesting?
ISTM that if a client or function wants to record intratransaction
times, it can call timeofday() at the appropriate points for itself.
regards, tom lane
---(end of broa
stamp,
allowing the former to be start of transaction and the latter to be
start of client command.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
ange 'current_timestamp' to
conform to a rather debatable reading of the spec, then fine --- but
keep your hands off of now().
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ally
determined the design of this feature change, then go ahead and put that
in. But I'd suggest
o Revise current-time functions to allow access to statement
start time
which doesn't presuppose the vote about how to do it.
s long as we are dorking with the current-time family, does
anyone want to vote for changing timeofday() to return a timestamptz
instead of a text string? There's no good argument except slavish
backward compatibility for having it return text, and we seem to be
quite willing to ignore b
27;d be inclined to just do it; we have not been very good about
following through on multi-version sequences of changes. And the
folks who want a standard-compliant current_timestamp aren't going
to want to migrate to now('statement') instead ...
regards, tom
l have to implement my own transactions table.
That's what I'd recommend. Transaction IDs are internal to the database
and are not designed for users to rely on.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
round to it.)
Consider using a cursor so you can FETCH a reasonable number of rows
at a time.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
racter-type columns. You could define a collation that makes
comparisons case insensitive and then mark selected columns that way.
We don't have anything like that yet, though Tatsuo has been heard
muttering about how to make it happen ...
regards, tom lane
t we've got nothing like that at the moment. (If you don't see the
point of this as compared to
SELECT CAST('{1,2,3}' AS int[3]);
then think about replacing the 1, 2, and 3 by arbitrary integer
expressions.)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Roland Roberts <[EMAIL PROTECTED]> writes:
> What can we do to at least get this on the radar screen as a known
> bug?
Oh, it's on the radar screen all right. Who wants to step up and fix
it?
regards, tom lane
---
time. And the spec is perfectly clear that
CURRENT_TIMESTAMP does not mean true current time...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
on')?
I have no objection to doing that. What seems to be contentious is
whether we should change the current behavior of CURRENT_TIMESTAMP.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
g, pre-execution, etc.
The notion of command arrival time is extremely fuzzy in this model.
It could very well be the time you compiled the ecpg application, or
the time you started the application running.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
case, with only
a small number of possible values for title[0], it seems that an index
wouldn't be helpful anyway.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
only thing that is really clear is that there is a minimum unit
of execution in which current_timestamp is not supposed to change.
It does not clearly define any maximum unit; and it is even less clear
that our interactive commands should be equated to "SQL procedure
statement".
rhaps we need a vote on this.
Perhaps, but let's wait till the facts are in.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
r after changing the file?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
class_teacher_rlt_pkey does not exist
In 7.2 DROP CONSTRAINT only works for CHECK-type constraints; you'll
have to drop the underlying index directly to get rid of a
primary-key-type constraint.
7.3 does allow DROP CONSTRAINT for this.
regards, tom lane
set up a custom-order index.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
construct
instead. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-conditional.html
Other than the DECODE() calls this should run fine in PG.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
tgres to cache a table in RAM?
There is no need to do anything; if you're hitting the table a lot, it
should all migrate into kernel disk buffers.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our
he rows with an active
> attribute maxes out (guess what, a vacancy set).
I think a rule is the wrong way to approach this anyhow. What would
probably make more sense is an AFTER INSERT OR UPDATE trigger that
runs a SUM() computation on the table and throws an error if it doesn't
like t
tial against Oracle is surprising,
given that they don't seem to be using a fundamentally different
implementation. Time to get out the profiler ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscri
reinventing the wheel. Isn't this
query the equivalent of a grouped aggregation --- viz,
select year, month, sum(number_of_items) as NumPots
from monthcustomer
group by year, month
regards, tom lane
"Josh Berkus" <[EMAIL PROTECTED]> writes:
> I'm kind of surprised that it's possible to index a temporary table.
> There's not much point in doing so.
Why not? You seem to be equating "temporary" with "small", but I don't
see w
mit the optimizer's search space. That will bring
the planning time down out of the stratosphere.
See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
for details.
regards, tom lane
---(end of broadcast)--
without that, the planner may flatten the two
levels of SELECT together, eliminating the savings you're trying for.
(I don't recall offhand all the conditions that govern flattening
of a sub-select, but I'm pretty sure a sub-LIMIT will prevent it.)
Richard Huxton <[EMAIL PROTECTED]> writes:
> On Monday 14 Oct 2002 6:17 pm, Tom Lane wrote:
>> Unfortunately that's not true at all, or at least not helpful for this
>> problem. The cachable attribute was poorly named, because it leads
>> people to think that PG *
rk better than a trigger anyway.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
you want to get rid of those too).
Another thing to try is
(a union b) except (a intersect b)
(Again, you might be able to say union all instead of union.)
Not sure which will be faster.
regards, tom lane
---(end of broadcast)-
ifiers.
There's an old saying that meaningful keys are bad database design;
check the mailing list archives for some examples. (F'r instance,
I seem to recall a story about a bank that embedded branch numbers
into account numbers, and then had terrible troubles anytime a
customer moved..
601 - 700 of 2610 matches
Mail list logo
