Andreas Haumer [EMAIL PROTECTED] writes:
How can I get the functionality of an deferred AFTER trigger
again with PostgreSQL 8?
Use CREATE CONSTRAINT TRIGGER. The manual is fairly negative about this
but I don't actually foresee it going away any time soon.
regards, tom
(n) *is* a variable-length type, not merely handled as such,
because the limit N is measured in characters not bytes. To support
variable-length encodings such as UTF8 we have to treat it as variable
length.
regards, tom lane
---(end of broadcast
ZONE has the effect of specifying what the stored
timestamp really means, and the second does the rotation to London time.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
,
respectively.
a) If either S1 or S2 is the null value, then the result of the
concatenation is the null value.
...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your
'BE' in your table ... the
planner does examine statistics while deciding what sort of scan to use.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
prefer to code it using
sub-selects.
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 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
(SELECT
get_xy(SetSRID(sightings.location, 26910), 4326) AS foo,
sightings.title
FROM sightings
WHERE sighting_id = 25
OFFSET 0) bar;
There are some other features such as DISTINCT that also prevent
flattening, but there seems no call for that here.
regards, tom lane
expectations.
We'd forbid these combinations if there were any fully portable way to
detect which encoding the locale expects...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
want
SELECT SUM(x0 + y0 + z0)::float / SUM(x2 + y2) AS A1,
SUM(x1 + y1 + z1)::float / SUM(x3 + y3) AS A2
FROM test
HAVING SUM(x2 + y2) 0
(maybe also having SUM(x3 + y3) 0)
regards, tom lane
---(end of broadcast
list, rather than extend it.
It's weird and not very semantically sound --- in particular, there's no
very sensible definition if there's more than one of them in the target
list. See past discussions in the PG archives.
regards, tom lane
---(end
a bit like a bug to me...
No, because that Description column is for comments on the individual
columns.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
), min(columnB)) from tab;
greatest/least are relatively new but you can roll your own in
older PG releases.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
from
tab;
Just for the record, we've gotten that right since 7.4. greatest()
would be a notationally cleaner solution than CASE, but multiple
occurrences of identical aggregates don't cost much of anything.
regards, tom lane
---(end of broadcast
.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
, unless the file is set up so that the second TAB is
missing when column3 is empty. If so, you'll need to fix that.
COPY is going to complain if there aren't exactly two TABs on every
line.
regards, tom lane
---(end of broadcast
using EXECUTE.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
parameters and are unlikely to start doing so. There isn't any way
that you can affect locals of a calling procedure before you return.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
symbol (notice they are not in the regurgitated query).
It would be more efficient anyway to not generate multiple parameters
for the same value, so we oughta fix this.
Patch applied to HEAD and 8.1 branches.
regards, tom lane
---(end
processing of \000 happens somewhere further
downstream, and wouldn't be affected.)
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
involving the dropped table,
but your description doesn't mention either of those risk factors.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
before 8.2 at the earliest. In the
meantime I'm wondering why you are insistent on dropping and recreating
credit_card_audit, as opposed to something less invasive like TRUNCATE.
regards, tom lane
---(end of broadcast)---
TIP 5
Jeff Frost [EMAIL PROTECTED] writes:
On Sat, 18 Mar 2006, Tom Lane wrote:
No, I think it's that you've got a plpgsql trigger function that
contains queries referring to credit_card_audit. Dropping and
recreating that table invalidates plpgsql's cached plans for those
queries
Emi Lu [EMAIL PROTECTED] writes:
Should I install any patches or do anything elese to have SQLSTATE and
SQLERRM work for me?
Update to 8.1 ...
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our
version of the spec but we allow it anyway. I'm not sure how common
that notation is.
This does not work in any context except repeating a SELECT result
expression in GROUP BY or ORDER BY.
regards, tom lane
---(end of broadcast
Markus Schaber [EMAIL PROTECTED] writes:
Tom Lane wrote:
The fact that the other form is even allowed is more of a holdover from
PostQUEL than something we have consciously decided is a good idea.
(IMHO it's actually a fairly *bad* idea, because it does not work nicely
when there's more than
privilege to be
needed.
If we had per-column privileges then we could be finer-grained about it,
but we don't (yet).
Please examine the following patch and make your judgment:
This patch is so wrong it's not even worth discussing :-(
regards, tom lane
himself off 7.2.2 ASAP. Some of them can be
found here:
http://developer.postgresql.org/docs/postgres/release.html
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
the
functionality. I seem to recall speculating that SQL2003's LATERAL
tables might do the job.
Btw, having several set-returning functions with equal or different set
lengths produce interesting results:
No kidding.
regards, tom lane
---(end
with a given
name; there needs to be some thought about schemas here.
In general though I agree with Alvaro's comment that touching system
catalogs directly is bad practice. You should update to a PG version
that has ALTER TABLE DISABLE TRIGGER, and use that.
regards, tom lane
Scott Marlowe [EMAIL PROTECTED] writes:
For same reasons, i.e. a need for precision, I find it hard to accept
the idea of mixing positive and negative units in the same interval.
The semantics are perfectly well defined, so I don't buy this.
regards, tom lane
, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Richard Huxton dev@archonet.com writes:
Hmm - looking at the source (and \df in psql) it seems the basic problem
is that COALESCE() isn't a function.
If it were an ordinary function, it couldn't satisfy the property of not
evaluating unused arguments ...
regards, tom
.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
,
the delete is still run, with the added where-condition
NOT(user_departed now()). Since the UPDATE has caused that to be
true, the delete happens. You should reconsider whether this is to be
conditional or not.
regards, tom lane
---(end of broadcast
to it.
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 2: Don't 'kill -9' the postmaster
.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
with different signs for month
and day (except for the case with month = 0, per my last message).
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
at tsearch2?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Achilleus Mantzios [EMAIL PROTECTED] writes:
Is there a reason that the NEW values should remain unchanged in AFTER
row triggers?
By definition, an AFTER trigger is too late to change what was stored.
Use a BEFORE trigger.
regards, tom lane
Alvaro Herrera [EMAIL PROTECTED] writes:
Tom Lane wrote:
Achilleus Mantzios [EMAIL PROTECTED] writes:
Is there a reason that the NEW values should remain unchanged in AFTER
row triggers?
By definition, an AFTER trigger is too late to change what was stored.
Use a BEFORE trigger
, it's a bug; please provide a reproducible
test case.
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
that are = 30 days.
However, I think a good case could be made that it should normalize
negative days too; that is, the invariant on its result should be
0 = days 30, not merely days 30. Similarly for justify_hours.
Comments anyone? Patch anyone?
regards, tom lane
(*) from log where from_ip = logouter.from_ip)
... from log logouter ...
Note that anything like this is going to be pretty expensive if your log
table is large. You might want to think about something involving
another layer of GROUP BY instead.
regards, tom lane
the
permanent view in the same way as the temp table hides the permanent table).
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
of the
index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
maybe not such a good idea in a live environment ... but
then again, dropping useful indexes in a live environment isn't a good
idea either, and this at least reduces the duration of the experiment by
a good deal.
regards, tom lane
---(end
(and the system knows it, note the huge
cost estimate). Try increasing work_mem enough so you get a hashed
subplan instead.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
--- there are
syntactic conflicts with allowing it in general, but I'm not sure that
objection applies to table declarations. But it's not there today.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
in your first example are a no-op.
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
Bryce Nesbitt [EMAIL PROTECTED] writes:
Tom Lane wrote:
What does EXPLAIN show for this and for the base query?
- Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
Filter: (reconciled = false)
select count(*) from event;
---
116226
It seems pretty clear
of the involved tables.
It's also less than polite to complain about the behavior of
two-year-old releases, without making any effort to ascertain
whether more-current versions are smarter.
regards, tom lane
---(end of broadcast
be of use, particularly on the
input side.
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
://developer.postgresql.org/docs/postgres/release.html
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
doesn't; you can make a FOREIGN KEY reference to a
column that's only UNIQUE, but you'll always have to specify which column.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map
records
where
other_thing is null, they all go in without complaint. I can insert as
many duplicates as I want.
This is per SQL spec. You're imagining that two nulls are considered
equal, which they are not.
regards, tom lane
---(end of broadcast
, or at least a warning, not just an
empty rowset.
Access to upper-level variables from subqueries is (a) useful and (b)
required by the SQL spec, so we are not going to start throwing warnings
about it.
regards, tom lane
---(end of broadcast
where it would fit in the manual. Perhaps some sort
of SQL gotchas webpage on techdocs would be appropriate.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
Philly Mandiza [EMAIL PROTECTED] writes:
Driver=/usr/lib/psqlodc.so
Perhaps you meant psqlodbc.so ?
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
Daniel Caune [EMAIL PROTECTED] writes:
I would like to create an index on a table, specifying an order clause
for one of the columns.
Search the archives for discussions of reverse-sort operator classes
(you might also get hits on the shorthand opclass).
regards, tom
) ...
CREATE OR REPLACE FUNCTION foo(i IN int) ... return foo(i, 1) ...
Remember that PG lets you overload a function name by using the same
name with different parameter lists.
regards, tom lane
---(end of broadcast)---
TIP 9
. (If you ever got it to work, which
I doubt you will, you'd probably then start to notice how badly it
leaks memory for whole-row operations...)
You should either forget this idea or invest the effort to move up
to PG 8.1.
regards, tom lane
---(end
Daniel Caune [EMAIL PROTECTED] writes:
Is there a way to define a function as a procedure, I mean a function
that returns nothing.
In recent versions you can say RETURNS VOID, which is a bit of a hack
but it gets the point across...
regards, tom lane
version I have alive to check)
been able to write '0' or '1' as the input textual representation of
bool. The latter has nothing to do with any integer coercion though.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't
reasonable to be using today.
Anyway, no this is not the right list for asking for build help for
pgadmin. Try pgadmin-support, or maybe pgadmin-hackers.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading
easily in prior
releases ...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
is 't' and 'f', though.
Otherwise you'll need to make the domain be over a type with
suitable I/O format (perhaps integer or text will work).
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
!= 0;
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
to make it seem like a high-priority
problem, however. I can only recall one or two people complaining about
it in all the time we've had outer-join support.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below
) are removed, the
SELECT functions (does not give an error message),
Really? The FULL JOIN condition using ~ is the source of the failure,
and I'd be quite surprised if changing WHERE makes it work.
regards, tom lane
---(end of broadcast
and right joins seems the only very reasonable
solution. As long as you're sure there are no duplicate rows you
need to keep, it'll work well enough.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list
=?ISO-8859-1?Q?Alexandre_Gon=E7alves_Jacarand=E1?= [EMAIL PROTECTED] writes:
It's possible to make an SELECT inside an AS clause ?
No. You could try building the query as a string.
regards, tom lane
---(end of broadcast
.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Dirk Jagdmann [EMAIL PROTECTED] writes:
Now I'd like to know if the current order of deletions in PostgreSQL
is intended in the top-down way or if that could be changed?
Sorry, I don't see much chance of changing it.
regards, tom lane
---(end
as a keyword outside the context where the keyword
is meaningful.
This could probably be fixed, or at least greatly reduced, with some
flex/bison hacking. Anyone up for it?
regards, tom lane
---(end of broadcast)---
TIP 4: Have
to 8.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 joining column's datatypes do not
match
discussed before ... see the archives.)
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
that you created after changing ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
the end of the string.) If you delete
the field (text :fieldname ) from the non-working ev_action strings
you should be able to get to a state where all the views will dump in a
non-hier build.
regards, tom lane
---(end of broadcast
for those counters.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
.)
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
your schema correctly).
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
precision than that.
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
, assigning
to an element of a NULL array left the array still NULL --- this is an
artifact of the fact that the system sees the assignment as a binary
operation with the array and the new element as inputs.
regards, tom lane
---(end of broadcast
a particular session.
IIRC there is a syntax for opening a cursor without specifying a name,
in which case plpgsql will pick one that's not in use. This is probably
what you want to use.
regards, tom lane
---(end of broadcast
without complaint.
You might as well not have a type system at all, if you're going to
destroy its ability to detect mistakes that way.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
a type choice, so that the
INSERT sees the raw untyped values, but I think that this query is
probably illegal per spec --- I believe the spec requires a SELECT to
deliver well-defined data types. Too lazy to go look up chapter and
verse at the moment.
regards, tom lane
reports example in the SELECT
reference page.
If you want to stick to portable SQL, you can still do it, but it's
pretty ugly and slow. Look in the list archives for previous
discussions.
regards, tom lane
---(end of broadcast
).
But it seems people expect to be able to do things like
number || ' string'
without explicitly casting the number to text.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
of t1 has a NULL value of name.
Many people find the behavior of NOT IN with nulls unintuitive,
but it's per SQL spec ...
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
);
?column?
--
f
(1 row)
regression=# select 3 in (NULL, 1, 2);
?column?
--
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
mytable;
The date_trunc() function can also be useful for this sort of thing,
particularly if you need to round off to something finer or coarser
than days.
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
regards, tom lane
));
This is generally true everywhere in expressions, not just in
function arguments. Without the parens, it's often ambiguous
what's subselect and what's outer query.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze
Andrew Sullivan [EMAIL PROTECTED] writes:
On Sun, Dec 18, 2005 at 10:08:22PM -0500, Tom Lane wrote:
Just for the record, that behavior is seriously broken: it violates
MVCC if any of the deleted tuples are still visible to anyone else.
Does it remove tuples that VACUUM FULL wouldn't?
Yes
for quite a few view-rewriting
scenarios, mainly because you'd avoid all the gotchas with double
evaluation and so on. So it seems like it might be worth doing.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0
.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
be
spelled without parentheses); the traditional Postgres way is
now()
Either way only sets an insertion default, though. If you want to
enforce a correct value on insertion, or change the value when the
row is UPDATEd, you need to use a trigger.
regards, tom lane
?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
701 - 800 of 2222 matches
Mail list logo
