, and
it does that by converting the record value to text ... which produces
the parenthesized data format specified at
http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604
regards, tom lane
---(end of broadcast)---
"Bath, David" <[EMAIL PROTECTED]> writes:
> ... Note that Sybase/MS-SQL's
> check constraint model asserts the constraint BEFORE the trigger, which
> discourages you from attempting to check and handle meaning of data!
Er, doesn't PG do it that way too?
some idea about doing more work during low-load
periods.
Unless MySQL invents some concept equivalent to VACUUM, they won't have
any prayer at all of being able to shift maintenance overhead to
low-load times.
regards, tom lane
---(end of broadcast
e for
such a case, the way it does for constant-false top level WHERE clauses,
but I really doubt it's worth any extra cycles at all to make this
happen. The proposed example is quite unconvincing ... why would anyone
want to depend on the existence of a "dual" table rather tha
Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Because the rule converts those inserts into, effectively,
>>
>> INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row);
>>
>> and there are no longer any matching OLD rows in the
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> I know that the server knows that ADT == -0400, and AST == -0300 ...
Other way around isn't it? Unless Canada observes a pretty strange
variety of daylight saving time ;-)
regards, tom lane
---
"Shaun Watts" <[EMAIL PROTECTED]> writes:
> Is there any way to eliminate the blank padding at the end of character
> fields in a table.
Use varchar, or text.
regards, tom lane
---(end of broadcast)-
;t significant. This gripe seems to me exactly comparable to
complaining if a numeric datatype doesn't remember how many trailing
zeroes you typed after the decimal point. Those zeroes aren't
semantically significant, so you have no case.
regards, tom lane
---
Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should
>> bump the CommandCounter between plan trees, but fails to ...
> Is this something I have to report?
Nah, I fixed it already
Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>>> The rule that actually deletes the rows from the underlying has to fire
>>> last, since the rows are gone from the view (and hence from OLD) the
>>> moment you delete them.
> A quote from the p
(and hence from OLD) the
moment you delete them.
In practice, you'd be way better off using an ON DELETE trigger for
these tasks.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
that these
might be two different machines; certainly two very different compilers
were used. One thing I'd wonder about is whether both databases were
initialized in the same locale.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
han that about which
version you are working with ;-)
You may find that username::text::integer will work, depending on which
7.x this actually is.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
s per spec.
The computation is effectively
NOT (0 = NULL OR 0 = 1)
NOT (NULL OR FALSE)
NOT NULL
NULL
ie, the result is UNKNOWN, which WHERE treats the same as FALSE.
regards, tom lane
---(end of broadcast)
ere's the rows with
trans_item.parent=20116?)
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
has ...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
into the spec that neither Oracle nor IBM intended to
implement. Those two pretty much control the committee after all ...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
*was*
true, in SQL92, but SQL99 lets you omit unnecessary GROUP BY columns.
The gripe against mysql, I think, is that they don't enforce the
conditions that guarantee the query will give a unique result.
The gripe against postgres is that we haven't implemented the SQL99
semant
No. You can use the same function for multiple triggers, but you have
to CREATE TRIGGER for each table separately.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://
languages first, but obviously it's not getting the job done.
Anybody have a better idea?
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
d I don't think we want to tackle all of
it any time soon, but the primary-key case probably wouldn't be very
hard to implement. We really ought to have this in TODO ... I'm sure
it's been discussed before.
regards, tom lane
---(e
way to solve
it. See the documentation about triggers. The first example on this
page does it along with a few other things:
http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html
regards, tom lane
---(end of broadcast)--
queries involving views (since a view is nothing but a macro for a
sub-select).
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
pposing that WHERE clauses are guaranteed to be
evaluated in a particular order. Such guarantees are made only for a
very few specific constructs such as CASE. See
http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
regards, tom lane
-
Judith Altamirano Figueroa <[EMAIL PROTECTED]> writes:
> ERROR: not exist the function ichar(integer)
[ digs in archives... ] Looks like we renamed ichar() to chr() quite
some time ago.
regards, tom lane
---(end of
gregates.
This isn't necessarily true for other forms of common subexpressions,
but it works for aggregate functions.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ata_pic lo,
> CONSTRAINT t_data_pic_pkey PRIMARY KEY ("sysid")
> )
> WITH OIDS;
> ALTER TABLE t_data_pic OWNER TO admin;
Why am I not seeing any trigger attached to this table? That lo_manage
trigger is the useful part of contrib/lo --- the separate data
ou're getting burnt because you're unioning a text with a varchar.
Make the column types the same and it'll work better.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
rocedure from pg_proc limit 5;
oid
--
boolin(cstring)
boolout(boolean)
byteain(cstring)
byteaout(bytea)
charin(cstring)
(5 rows)
regression=#
regards, tom lane
---(end of broadcast)---
T
ON events (event_id)
WHERE tag_type_fk = 2;
then it would be competitive for this query, since the index could
effectively handle both constraints not just one. (THe way you did
define it, the actual content of the index keys is just dead weight,
since they obviously must all be "2".
at sounds fiddly.
> I must admit, on the odd occasion I want to skip a row, I just FETCH it
> and move on. Anyone else?
There is something on the TODO list about improving plpgsql's cursor
functionality --- there's no reason it shouldn't have MOVE, except that
no one got ar
on in which you should rethink your
data design. Those tables should all get merged into one big table,
adding one extra column that reflects what you had been using to
segregate the data into different tables.
regards, tom lane
---(end of broadcast)---
expressing the query without that.
My guess is that trying to use a function for this is counterproductive
in itself; the table access that's going on inside the function needs
to be exposed for optimization in order to get reasonable overall
performance.
_sync" line 2 at if
> What am I failing to understand with this?
We don't guarantee short-circuit evaluation of boolean expressions.
You'll have to break that into two IFs, ie,
IF TG_OP = 'DELETE' THEN
IF ... test on OLD.something ...
ent that no one has a use
for it ...
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
;s still true in the latest spec. Postgres treats column
constraints and table constraints alike, but other SQL databases are
likely to be pickier.
BTW, is there any actual need for the "id" column here, seeing that
you have a natural primary key?
might want to exclude stuff in information_schema
as well.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Hilary Forbes <[EMAIL PROTECTED]> writes:
> How can I easily get to see the definition of a user defined function
> please?
Look in pg_proc.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked o
issue?
We'd certainly have heard about it if so. But you haven't provided
enough info to let anyone reproduce the problem for investigation.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Matthew Peter <[EMAIL PROTECTED]> writes:
> How is it possible to delete an item from a single
> dimension varchar[] array?
AFAIR there is no built-in function for this, but it seems like you
could write a generic polymorphic function for it easily enough.
is still a possible problem.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
onal assignment doesn't seem to be catered for.
The equivalent construct in SQL is CASE.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Luis Sousa <[EMAIL PROTECTED]> writes:
> But how can I create a table using a query and putting ON COMMIT DROP.
You can't. Use INSERT ... SELECT to fill the table, instead.
regards, tom lane
---(end of broadcast)---
uot;fbt IS NULL" should yield true
in your example, but I think there are/were some implementation reasons
why it doesn't.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignor
der server.
Try 7.4 or later --- plpgsql was pretty weak on handling rowtype
variables that far back.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
will hose the DB completely, so don't do
it till you've exhausted the possibilities for pg_dump without it.
Consider updating to a more recent PG release while you are
recovering...
regards, tom lane
---(end of broadcast)---
required ordering or the nature of the condition
that defines "this row" to really say much about the best solution.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
than 10.0 the
> column should return the relevant values. However, the column needs to be
> able
> to hold values like "<0.05".
contrib/seg might do more or less what you're looking for, but none of
the standard datatypes will.
n int2vector *is* an int2 array, so = ANY just works.
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
Don't try to use
arrays to replace tables.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
ix
timestamptz_age() to do calculation in local timezone not GMT, per
bug 1332.
and here is a link to the discussion that prompted the change:
http://archives.postgresql.org/pgsql-bugs/2004-12/msg00013.php
regards, tom lane
---(end of br
QL-STATEMENTS-DIAGNOSTICS
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
ependent --- or at least should be. The locale support in our
regexp code is definitely pretty weak at the moment.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ly no API to read and
write bytea values in a streaming fashion. If your objects are small
enough that you can load and store them as units, bytea is fine.
BLOBs, on the other hand, have a number of drawbacks --- hard to dump,
impossible to secure, etc.
, you might have to make the cast
IMPLICIT rather than ASSIGNMENT. I'd try ASSIGNMENT first, though,
since it's less likely to bite you when you weren't expecting it.
regards, tom lane
---(end of broadcast)---
(ofsomething)>0 =
> )
> Is this possible at all with just plain SQL?
Instead of INSERT ... VALUES, use INSERT ... SELECT.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
different encodings then
you are in for some pain. At the minimum you'll have to separate out
the rows that are in each encoding so you can pass them through
different conversion processes.
regards, tom lane
---(end of broadcast)---
data. It's possible to
develop appropriate code for dollar-quoting random text, but it's a lot
harder than it is to escape the data in the old style.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you che
"Lane Van Ingen" <[EMAIL PROTECTED]> writes:
> I want to select 2nd oldest transaction from foo (transaction 3).
Can't you just do
select * from foo order by update_time desc offset 1 limit 1
regards, tom lane
ate, end_date) OVERLAP (new_start_date,
new_end_date) AND property_id = X;
As far as the reason for the difference between function execution and
manual execution: check for unintended variable substitutions. Which
words in the query match variable names in the plpgsql function? Are
those only th
select fc_editlanguage(42, 'foo', 'bar', 1::smallint);
This is enough of a notational pain in the neck that it's easier just to
declare the argument as integer.
regards, tom lane
---(end of broadcast)
vector in
pre-8.1 releases, so I think you're kinda stuck with the above
for now.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Mischa Sandberg <[EMAIL PROTECTED]> writes:
> Anyone care to comment on the third row of output?
I think you mistyped the last INSERT:
> insert into c values(2, 'C2');
> insert into b values(3, 'C3');
I suppose you meant insert into c ...
the same effect on SQL-data and schemas as that sequence.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Mon, Aug 15, 2005 at 08:21:23PM -0400, Tom Lane wrote:
>> Given that we consider trailing spaces in char(n) to be semantically
>> insignificant, would it make sense to strip them before doing the
>> regex pattern match?
>
tra ~ operator definition that
specifically prevents that (bpcharregexeq).
I have a feeling that we added that operator definition at some point
for backwards compatibility, but it seems a bit odd now.
regards, tom lane
---(end of broadcast)--
ust let it default, which you'd do with, say,
INSERT INTO newtable (fielda, fieldb)
SELECT field1, field2 FROM anothertable
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore y
and B01.tglavd <= A38.tglavd)
> )A
> Where Temp_hasil2.NIK = A.NIK;
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ded to supply a simpler API if there's enough demand for it.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Chris Mungall <[EMAIL PROTECTED]> writes:
> On Mon, 1 Aug 2005, Tom Lane wrote:
>> Chris Mungall <[EMAIL PROTECTED]> writes:
>>> What are the reasons for deprecating the use of the function in the
>>> SELECT clause?
>>
>> The semantics of
e because (as you note) there are things you can't do any
other way.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
might be, if there can be
multiple pg_depend entries linking the same entities.
Peter, any thoughts?
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
which might not be
infinite bloat but it's surely not too efficient.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ow
anything about case-folding for non-ASCII characters. But it will at
least give consistent results.
When you use a non-C locale, it's best to stick to the encoding that
the locale expects.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ecause
> of the dollar quotes.
However, plpgsql wasn't fixed to follow that behavior till 8.0.2 or so.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
nk that most errors in the "data exception",
"cardinality violation", "insufficient resources",
and "operator intervention" categories are possible. See the
error codes appendix for some ideas.
And of course, if the SELECT invokes a user-defined function,
definitions. I can do
> awk and sed commands to do this if I need to, but first wanted to check
> if Pg already had tools to export the table structure (without the
> data). Does it?
pg_dump with the -s switch is a much better way ...
regards, tom lan
Luca Pireddu <[EMAIL PROTECTED]> writes:
> On July 15, 2005 08:58, Tom Lane wrote:
>> Ah-hah: this one is the fault of create_unique_path, which quoth
> In any case, it looks like Tom has already found the problem :-) Thanks guys!
On closer analysis, the test in create_unique_
ath, which quoth
/*
* If the input is a subquery whose output must be unique already, we
* don't need to do anything.
*/
Of course, that needs to read "... unique already, *and we are using all
of its output columns in our DISTINCT list*,
ally have time to reverse-engineer a test
case from your description ...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
on
pg_shadow would get the job done just as well.
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
Harald Fuchs <[EMAIL PROTECTED]> writes:
> FOR row IN EXECUTE 'EXPLAIN SELECT * FROM ' || tbl LOOP
> fails with the following message:
> ERROR: cannot open non-SELECT query as cursor
[ checks CVS history... ] Use 8.0.2 or later.
Steve Wampler <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> If you want something cheap, you could use the same technique the
>> planner uses nowadays: take RelationGetNumberOfBlocks() (which is
>> guaranteed accurate) and multiply by reltuples/relpages.
> Ye
Ying Lu <[EMAIL PROTECTED]> writes:
> A question about creating index for the following expression.
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
You need more parentheses:
CREATE INDEX idx_t1 ON test ((col1 || '-' || col2));
functions to
provide this functionality.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
via triggers for specific tables that you think
it's worth doing for.
Also, if an approximate answer is good enough, there are a whole other
set of possible solutions.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Jocelyn Turcotte <[EMAIL PROTECTED]> writes:
> i'm wondering if there is a way to prepare and execute a plan in a
> plpgsql function.
You do not need that because plpgsql automatically caches plans for
SQL statements appearing in a plpgsql function.
s of PG actively recognize this case
and reduce the LEFT JOIN to plain JOIN, but even if we did not do that
you'd get the same result.
I've heard it claimed that Oracle produces different results; if true,
it must have something to do with their rather standards-challenged
interpre
Erik Wasser <[EMAIL PROTECTED]> writes:
> But 'current_query' is still always empty... B-(
The pg_stats views lag reality by a certain amount, so checking for your
own query is generally not gonna work. Try starting a long-running
query in another session.
t fails:
> ERROR: type "serial" does not exist
Serial isn't quite a true type, and so it doesn't work in every context
that you might think. It'd probably make sense for "alter column type"
to accept it, but for now what you gotta do is create a sequence
tgreSQL hasn't
> implemented it.
It's not that hard to make your own type using the builtin textin and
textout functions, and then add just the functions you wish to provide.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Bruce Momjian writes:
> Tom Lane wrote:
>> but it doesn't seem to me to follow from what the spec says that we need
>> to explicitly cast the result of now() to six places. As long as it's
>> coming from gettimeofday it can't have more than 6 places anyway,
l when presented with this parse
tree.)
In short, I'm inclined to remove the above-quoted lines, and similarly
for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
some type-conversion issues.
(You don't really want them in the output of a view anyway; "unknown"
type columns are bad news.)
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
r was O(N^2) in the number of
rows affected, and would surely be intolerably slow for multiple deletes
in a reasonably sized table. Given an index on the grouping columns
plus sort_order, it could even be reasonably fast (don't forget to make
the ORDER BY match the index).
leted in decreasing order of c, and there's no
very easy way to guarantee that.
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])
hing convert_IN_to_join() to attach the IN subselect
further down in the join tree, using logic similar to what we use to
decide where ordinary WHERE quals can bubble down to.
regards, tom lane
---(end of broadcast)---
TIP 9: th
workaround I know of is to make the foreign key
checks all deferred, so that they're not checked until the transaction
is about to commit. This is not bulletproof, but because it
considerably reduces the time window for a conflict, it may do as a
workaround until 8.1 is ready.
REATE INDEX
(I hadn't bothered to make a tablespace to test with, but the point
is the syntax is fine.)
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> Does that make sense? Would it ever get used?
It could get used if one of the two values is far less frequent than the
other. Personally I'd think about a partial index instead ...
901 - 1000 of 2610 matches
Mail list logo
