So it would have to be where coalesce(a, b, 0) <> coalesce(b, a, 0) for your
example with ints, and likewise some default value for other column types...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)
> no cigar.
Well, duh. Showing why IS DISTINCT FROM is useful.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
h
oin
conditions. A common mistake is to leave out a condition in the where clause
which then results in the actual Cartesian product being requested. The more
tables involved in a join, the easier it is to make such a mistake--I think
we've all done this at one time or another.
in--older versions, I haven't used it in quite a while.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 6: explain analyze is your friend
e the helpful message said.
- Start postgres.
Now, if you did that, and the raw files were from the same OS & architecture
& postgres build settings, everything is fine. Otherwise, you're out of
luck.
> I see...I'm using pgAdmin III with 8.1.5, and the dbs don't reappear
where Sat & Sun could
have 24-hour window, and trying to vacuum largest tables during longest
windows. This wouldn't work for every server, but for many...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)-
> Having a mechanism that requires enormous DBA effort and where there
> is considerable risk of simple configuration errors that will be hard
> to notice may not be the best kind of "feature" :-).
Why not? It seems to have worked remarkably well for the market leader ;-)
--
SysV shared memory. Nothing to worry
about, but something to be aware of when trying to match /etc/rc shm values
to postgres settings.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 9: In
nce.
Do you know when this behavior was introduced?
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
thousands,
iow image 1123 might be in images/000/01/01123.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
will fail.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 6: explain analyze is your friend
emp_id from emp_1_id;
$$ language plpgsql stable;
Call set_emp_id once on connection, then use get_emp_id thereafter. Would
that be any faster? (This is what Erik meant by " a temp table is pretty
much a session variable" in his earlier message.)
--
Scott Ribe
[EMAIL PROTE
> ...and you don't need to worry about cleanup...
What cleanup? Temp tables are dropped on connection close.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 2: Don't
of tools mentioned earlier for examining
files does not require giving anyone write access ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ign
> Besides writing a script that looks through the DDL of all tables, and
> CLUSTERs all tables with PK constraints, is there a quicker way?
Is this really a sensible thing to do? As often as not, you want to cluster
on foreign keys...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbyt
> Comments?
Note when it happens, and if it doesn't succeed for some value of "too
long", at least escalate to ERROR message, possibly fail.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
--
Is there a way to force a flush of all cached plans? Particularly, to force
re-evaluation of immutable stored procedures? Don't worry, it's a testing &
development thing, not something I want to do during production ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
schema.) There probably aren't that
many, and I can just keep hitting pg errors one row at a time until I work
through this, but...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)--
> Start a fresh connection.
OK. Better than having to restart the whole server, which is what I was
doing...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 6: explain analyze
> I suppose you could create a boolean function that does a cast, and catches
> the
> execption, returning NULL.
Yes, I was puzzling over query syntax and didn't think about a function.
That would be fine.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303)
, the to_date approximation is
probably no worse than using null.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
the export that way. If not, the query would be a little
more complicated by having to specify the format, but the same basic idea
still would work.
Which is good, since I'm now on to a table that has 108 bogus dates. I'd
hate to find & fix them one at a time...
--
Scott Ribe
[EMAIL
or DD are more than 2 digits, but I'm
going back to look at that again ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
taging table to load it up and study it and fix it.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomai
> SPAM is a term used for unsolicited email of any kind... sent to a large
number...
Thus the use of the word "bulk".
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TI
s work ;-) And apparently you can only do that on
functions that return a value.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
ns boolean as $$ begin return
'f'; end; $$ language plpgsql immutable;
CREATE FUNCTION
pedcard=# select f3();
f3
-
yes
(1 row)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
In addition to the other good suggestions, modify you program to record a
plain old text log of dangerous actions confirmed by users. These kinds of
people usually shut up pretty quickly when you tell them the date, time, IP
address of the machine, and login name of the user who did it.
--
Scott
(0x3603), but the server was compiled with PG_CONTROL_VERSION 822
(0x0336).
This would have saved me a couple of minutes' worth of puzzlement. With Macs
out there that look identical between the PPC and x86 versions, this kind of
error has gotten a lot easier to make ;-)
--
Scott Ribe
[
tical flags. So it would help people like
me, all one or two of us ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
> In that case, the distinction just
> adds work.
In that case you declare the column not null and don't use nulls.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 1:
> The portable (and correct) way to do it is to use the DEFAULT keyword
> like this:
>
> INSERT INTO some_table (id_field) VALUES (DEFAULT);
Alternatively, for columns for which you have no value, don't specify them
at all in the insert.
--
Scott Ribe
[EMAI
yself
> though ... any comments?
Well, I've heard it only really supports single-dispatch style of OO, so
Common Lisp/Dylan type object models are not well supported.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
--
> I'm not sure whether Apple has fixed the
> bug in the startup script, where the external file was referred AFTER
> the values where set.
Yes. You can now edit /etc/sysctl.conf and nothing else is required. I
learned this ~10.4.8, so I don't know when it actually happened.
--
Just discovered (the hard way) that casting a boolean column ::varchar
doesn't work. I assume I can add a function somewhere that will define a
default cast for this? Are there any other standard types that can't be cast
to varchar?
--
Scott Ribe
[EMAIL PROTECTED]
http://www.kille
> Sure, see CREATE CAST.
Too simple ;-) I was expecting to have to dig into data type definitions...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
> You already got an answer to the first part of your question, but I
> thought you might be interested in the second as well. Here's what I
> did:
Thanks. That's very helpful, to see a good example of using the built-in
catalog data.
--
Scott Ribe
[EMAIL PROTECTED]
http://
First off, use sysctl to see what values are actually being set:
sysctl -a | grep kern.sysv.
The if they're not what you think they should be, tell us exactly how you're
trying to set them.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-
When you increase shmmax, you need to increase shmall as well. Max is the
largest single allocation allowed, in bytes. All is the total SysV shared
memory available to all processes, in pages. (I think...)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
sure, but I think the questioner was proposing a policy of "if it
crashes, we go to the standby, no attempt at recovery, ever", and I think
that would be safe.
And, personally, given my experience with pg, I think that's reasonable.
Because the day I see pg crash I'm going to assum
Use psql; it provides all the command-line options you need.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
the database level, is just wrong. Whether it's a direct
deception, iow speaker knows it to be false, or an indirect deception, iow
speaker is implying a level of expertise he does not possess, either way I
would categorize it as a lie.
--
Scott Ribe
[EMAIL PROTECTED]
http://ww
ht to have their head examined ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
etc/rc. For all I know they
may always have done so, but of course prior to 10.3 we didn't have to
modify /etc/rc directly.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 7: do
tty long time.
I was not aware of /etc/mach_init.d, a quick glance at the docs make it
obvious that it runs earlier than SystemStarter, looking at /etc/rc shows
that it runs not that much later than the normal settings of the sysv
parameters. Are you sure changing sysv kernel parameters at that poi
u'd have to fake it using a temp table.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
> I guess what I"m really asking is why can't you run aggregates over an index?
It's got to do with MVCC and transaction consistency. Running count(*) or an
aggregate function on an index could include records that should not be
visible to your current transaction.
--
umn that is required, such as a primary
key, then count(pkey) is equal to count(*). Many databases make use of this
fact to optimize performance of count(*) by using an index scan.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
-
> but If the bytea is encoded only for dump and load
I think that's the case, as with all binary types dump (consider numbers for
instance) to an ASCII encoding.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of b
ble rather than constructing clumsy unions over the common
columns (or having one-to-one relationships, making querying the "base"
attributes easy, but requiring joins for everything dealing with "child"
entities).
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerb
> ACID
> compliance requires that either all or none of the operations in the
> transaction happen. In this case one of them does not.
So maybe it's Durability that's violated in your example or Atomicity ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(
d 4th
Dimension, which fails on all 4 counts ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ve it show me the script and read it carefully before proceeding ;-)
(Hey, I'm not stupid, it was just a development db that I hosed!)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP
le:
select id, updated_date from customers limit 100
Sybase, for instance, would not use table pages at all if there were an
index covering id & updated_date.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
--
scriptive
error messages
- come up with test cases to exercise all branches of code
- print useful debugging info while tracking down problems
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)--
I do something similar, and I have the front end generate updates to the
position column of all rows, executed in a single transaction. Then you can
fiddle around with the options and tradeoffs of how to ensure in the db that
no update is accepted that violates the constraints...
--
Scott Ribe
t month got chewed up by a server hardware failure.)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscrib
except where national security is a concern...
I'm not interested in pushing the issue, just wanted to point out that when
public funds are spent, the paper trail as to who receives them is public
info.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
e,
and when it's done it pushes the pgconn back on, wrapped by a
stack-allocated class whose constructor and destructor take care of
acquiring and releasing the pgconn. The queue is a Mac OS thing, not my
code, so it's not a problem with sharing them, unfortunately. So I'll have
to keep
I wanted to use check constraints and I
wanted a record of the fact that the entry is "incomplete" because of its
source.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 4
ets good results) does a much better job of matching names,
and double metaphone does even better although having each word mapped to
possibly 2 equivalents might complicate your logic depending on your
queries.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303
years I found a few places where Metaphone
needed improvement. Double Metaphone seemed to incorporate all my revisions,
so the best approach would be to start with it, and if your system can't
accommodate the notion of multiple equivalents, then just use the primary.
--
Scott Ribe
[EMAIL PROTE
he app is
distributed. The GPL does not impose any requirement for distribution of the
app. It seems to me that MySQL AB tries to game people's misunderstanding of
this, in order to lay claim to license fees for in-house projects.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303)
> My damn powerbook drive recently failed with very little warning
It seems to me that S.M.A.R.T. reporting is a crock of shit. I've had ATA
drives report everything OK while clearly in the final throes of death, just
minutes before total failure.
--
Scott Ribe
[EMAIL PROTECT
locking
> yourself.
Uhhhmmm, I built without --enable-thread-safety??? I have a process I follow
when building, but pg_config is telling me that I didn't use my standard
options. I'm assuming this could cause all sorts of threading kinkiness...
--
Scott Ribe
[EMAIL PROTECTED]
ht
rse this requires that the backup be on the same subnet as the server,
a restriction which is not shared by the alternate app/DSN nor the DNS
techniques...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---
very first paper by its own authors. To hold it up as any
kind of paradigm is really misinformed.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 4: Have you searched our li
new.id;
insert into tbl (id...) values (nextval(somesequence)...) returning new.id;
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
> You won't have duplicates[1], it's a sequence. It's its purpose.
>
> Now I may have missed something, I didn't follow this thread.
Yes, what you quoted was more the intro. The actual question was how to find
out what ids were generated during a sequence of inser
ry the data...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
gether to try and make a third one ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
you want finer-grained backup, you have to do
something like rsync the current WAL file frequently.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
> Why putting gapless numbers into the database at all? Just calculate them at
> query time.
There is ABSOLUTELY NO WAY that would be acceptable for accounting or legal
purposes. It would be the same as fabricating the numbers during an audit.
--
Scott Ribe
[EMAIL PROTECTED
d you have a compound index on (name, to_date) to support
querying for current state. Perhaps you could use a partial index as well to
speed up those queries, but I haven't yet explored that feature of
PostgreSQL.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes
it. But when you split the script,
you explicitly connect to your db for the second script.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
ol of connections, so
perhaps some prior thread abused a connection and left it in a
non-functioning state. I'll have to take a close look at some of the error
handling, at least make sure that everything is logged by my app, and of
course get the connection's error status when I see PGRE
ithout any license as a free & open source copy.
>
> Awaiting info at the earliest.
<http://www.postgresql.org/licence.html>
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)
> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date) DESC;
You meant:
ORDER BY
date_part('year', uu.add_date) DESC, date_part('month', uu.add_date) DESC,
date_part('d
Note that this is not a perfect way
to test, because the "embedded CR" might have been switched from CR to LF or
vice versa at some point in the logging/downloading/copying/pasting process.
- Is there something I need to do after an error like this, in order to get
the connection back to
l-safe, but I'd still like to
understand more fully what has happened. Can I really be 100% sure this
would keep the connection usable, given that the 1 commit already somehow
failed to end the transaction block? I can certainly understand that a
commit submitted by itself might be recognized
ly repeatable:
I suppose psql sends either a commit or rollback after the query generates
the error, maybe after every query, so my attempt to use it to check this
wasn't a valid test.
Thanks for the help.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com
ss unplanned comments.
I feel good that PostgreSQL has gotten so much attention from IBM--it's a
real sign of accomplishment ;-)
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 7
at the author misquoted Rivot, or misinterpreted
which database he was referring to.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an
> Interesting, IBM is saying that the code is worth 85M dollars!
Well... Actually they're saying: "we paid $85M for it, now we're giving it
away, aren't we generous?"
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
E-mail Administrator at
1-800-222-4700 x1198 to resolve this issue. We apologize for any
inconvenience this may have caused. #D702
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 3: if p
s somewhere isn't filling your
hard disk with some huge log file. I don't remember the UNIX commands
offhand, but you should sudo a search starting in / for all large files, say
> 1GB for instance.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
ype", "Status",
"OurDrId", "TestNum", "TestedWhen", "AssignedWhen")
values
(docid, ptid, docrec."OriginatedWhen", docrec."ReceivedWhen",
docrec."CreatedWhen",
docrec."Cu
d enough.
Would it be a reasonable feature request to ask for the ability to use
myrecord.* on a record type as a value list?
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 6: Ha
> I have asked Apple about using a saner default for shmmax, but a few
> more complaints in their bug system wouldn't hurt.
I suspect it won't help, since their official position is already "don't use
shmget, use mmap instead"...
--
Scott Ribe
[EMAIL PROTECTED]
h
on?
The objection would be attitudinal. I detect a whiff of "that's sooo
obsolete, you should get with the program and do it our way instead" in
their docs...
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of b
he
reasons mentioned above.
- It seemed to me to be much easier to write the actual replication stuff as
a C++ app on top of libpq than to try to stuff that into the backend.
Note that I only need master/slave (take that LA County Commission)
replication, and I don't really need to worry a
would be more like what you expect, a brief instant.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
u have to worry about
performing inserts (and updates) in a particular order. That can be really
painful to code, and in some cases (cyclic relationships) impossible to do
except by leaving some constraints out.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7
l have. I feel confident that dozens or hundreds would
be no problem; thousands I don't have any idea; millions I wouldn't try.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice
---(end of broadcast)---
TIP 9:
301 - 394 of 394 matches
Mail list logo
