sing
on one delete).
How can I tell what the trigger is doing? I'm using 8.2.5 and I've
ANALYZED everything.
Thanks.
- John D. Burger
MITRE
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ing around such issues, anyway.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
. It turned out that the natural
keys were always positive, so I set up the sequence to range
=downward= from 0.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
D. Dante Lorenso wrote:
I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE
commands. Is this possible?
UPDATE invoice i
SET reserve_ts = NOW() + '1 hour'::timestamp
FROM account a
WHERE a.acct_id = i.acct_id
AND i.reserve_ts < NOW()
AND a.status = 'A'
AND i.is_pai
ot;Do you prefer the good x above, or the bad x below?".
It's a fair cop (but society's to blame :). Sorry, I thought better
of it right after I hit Send.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 4: Have you search
Quoting the text to which you are responding is often the only
way to provide the necessary specific context for your comments.
As an illustration, which helps you understand the preceding
paragraph better, the extract above, or the mess below?
- John D. Burger
MITRE
On Dec 11, 2007, at 11:54,
So two design patterns for a makeshift UPSERT have been presented -
one is to check beforehand, and only insert if the item isn't present
already, the other is to do the insert blindly and let PG check for
you, and catch any exceptions.
I'm also wondering what people's ideas are for a sort
Tom Lane wrote:
It seemed reasonable to me that a select on the first element of an
array column could use an index on the column, but, as seen in this
example, I can't get it to do so:
Nope. The operators that go along with a btree index are equality,
less than, etc on the whole indexed colu
It seemed reasonable to me that a select on the first element of an
array column could use an index on the column, but, as seen in this
example, I can't get it to do so:
=> create temp table tempPaths (path int[] primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"
I'm developing some triggers for the first time, and I'm having
trouble analyzing their performance. Does anyone have any advice for
doing EXPLAIN and the like on statements involving NEW? For
instance, I'd like to know what plan PG is coming up with for this
fragment of a trigger functio
lumn, despite
its renaming. Contrast this with
... order by random; // plain column reference
This substantially breaks the principle of least surprise for me.
Caveat - this is on 7.4 (sigh), perhaps more modern versions have
different behavior.
- John D. Burger
MITRE
As far as I can tell, all of the proposed solutions lack sample
independence. Take the OP's suggested approach of doing something
like this:
SELECT * FROM mydata
WHERE mydata.random_number >= (SELECT RANDOM() OFFSET 0)
ORDER BY mydata.random_number ASC LIMIT 100
All you're doing is pi
DB-related humor:
http://xkcd.com/327/
- John D. Burger
MITRE
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Richard Broersma Jr wrote:
Here is the example that doesn't do what I expect:
--find all parents that have a mixture of boys and girls.
--but this doesn't return anything
SELECT *
FROM Parents AS P
WHERE 'girl' <> ALL ( SELECT gender
FROM Children AS C1
the actual
pulling
would take some code that doesn't exist now, too,
Okay, good to know. The situation is obviously easy to avoid, I just
found the contrast surprising.
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 1: if
select results were the
same in both cases, but I'm willing to believe that's an accident of
our data.
(Sorry if no one can answer my question without the table
definitions, etc. - it seemed worthwhile trying to get away without
that for now.)
Thanks.
- John D. Burger
rivileges to tables which do not yet exist, which I discovered in
this thread:
http://archives.postgresql.org/pgsql-general/2007-02/msg00911.php
If you follow the thread, you'll find that one reply pointed to some
existing functions for managing this stuff. I found these usef
tter plan. I don't
know the details of your setup, but you can do things like this with
any ordered type:
where test between '11' and '113'
or test >= '114'
I know this does not match the exact semantics of your query, but
hopefully you get the
e
issues (but I have not looked closely at it). You can also see
Wikipedia for one of the most well known, due to Knuth/Wellford:
http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance
- John D. Burger
MITRE
---(end of broadcast)---
Tom Lane wrote:
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to
leave
their behavior alone, at least until such time as they're actually
standardized.
I don't think I buy this - MIN and MAX are aggregate
nute - does this have to do with the varying
number of days in different months?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Richard Huxton wrote:
Ah, but this just includes the time of the last message, not its data.
Oops, I read the OP's question as "date and time", rather than "data
and time". Nevermind. :)
- John D. Burger
MITRE
---
er join message using (userid)
group by userid, user.name, user.address;
As to whether this is faster or prettier than a subquery, I dunno.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
Lonni J Friedman wrote:
I have a need to
determine which rows in a specific table are less than 24 hours old.
I've tried (and failed) to do this with the age() function.
And on the suggestion of a timestamp column with DEFAULT NOW():
Unfortunately, its too late now. The database (and its ta
Tyler Durden wrote:
I'm having some problems in performance in a simple select count(id)
from I have 700 000 records in one table, and when I do:
# explain select (id) from table_name;
-[ RECORD
1 ]
QUERY PLAN | Seq Scan on t
Even ISO country codes are not guaranteed to be stable
I'm not sure where the idea that primary keys must be stable comes
from. There's nothing necessarily wrong with updating a primary
key. All a primary key does is uniquely identify a row in a table.
If that id changes over time, that's
standards.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
he number of US "terms of
sovereignty" that exist.
Yah, that's my point - some data sources might lump all these
together as state/province level entities, and some might not.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 1: i
same kind of goals we did. Anyway, I will send
our schema under separate cover, and I will investigate sending you
the data as well.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
tp://archives.postgresql.org/pgsql-general/2006-10/msg00526.php
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
, etc. As far as
FIPS and ISO codes are concerned, we have a separate table mapping
(locationID, standards body) to codes.
We are interested in sharing this stuff, so I'd be happy to pass
along the schema and/or the data, although all of it is kind of beta.
- John D.
defaults you want. I gather that's the point of template0. See the
Fine Manual:
http://www.postgresql.org/docs/8.1/interactive/manage-ag-
templatedbs.html
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget t
Shoot The Other Node In The Head:
http://www.linux-ha.org/STONITH
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ABHANG RANE wrote:
I have a array column which has 12 real values in it. Basically
these values represent co-ordinates in 12 dimensions for a
substance. My main need is to find substances similar to a
particular compound. Now I can do by calculating differences with
each array in the whol
s and pieces to read would be prohibitively slow.
Moreover, caching only those bits and pieces would require
complicated code to decide whether the cached data is relevant to the
next query. Validating cached data at the page level is much
simpler, and thus faster.
Or so I assume ...
-
ow
often it's only the timestamp that changes, this could result in
decent "compression".
Of course, now you need referential integrity.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
there is a
work request, and performs the work. This can be done by a periodic
cron job, or using NOTIFY/LISTEN.
There was a fairly detailed discussion of this last month - the
thread starts here:
http://archives.postgresql.org/pgsql-general/2007-04/msg01152.php
- John D. Burg
g
Regular expressions would work, but a between statement should work
also.
SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';
Ron, in case it's not clear, if an index on text_field exists, the
planner can use it to make such queries run re
Andrew - Supernews wrote:
Anyone have any ideas on how to handle a work queue?
Advisory locks (userlocks in pre-8.2).
Can someone explain why these are a better fit than whatever locks
SELECT FOR UPDATE acquires?
Thanks.
- John D. Burger
MITRE
---(end of
was a brief discussion of this just last week, with a few
solutions suggested:
http://archives.postgresql.org/pgsql-general/2007-04/msg01098.php
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free
... ] That shouldn't happen. Look into pg_locks to see if
you can determine who's waiting for what.
I don't want to recreate the problem right now, but I will
investigate later. For what it's worth, while the workers were
locked up, I couldn't query the table i
I wrote:
I use a variant of The Tom Lane Solution previously pointed to,
your Plan 1 is very similar.
Hmm, per that pointed-to post:
http://archives.postgresql.org/pgsql-general/2003-05/msg00351.php
I decided to run a periodic vacuum on my work queue. Lo and behold,
I get this:
ERR
Steve Crawford wrote:
Anyone have any ideas on how to handle a work queue? I've been
thinking
about optimizing this process for quite a while.
I use a variant of The Tom Lane Solution previously pointed to, your
Plan 1 is very similar.
This does not produce desirable results. In the case
Tom Lane replied:
I have two queries for looking up related words which I think should
be equivalent, but 7.4.8 comes up with very different plans.
They're not at all equivalent:
If there are duplicate word1id,word2id entries in allwordrelations,
the
first query will produce duplicate out
quot;:
http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html
The big ugly union might need to be munged a bit, but most of the non-
weekend US holidays seem to be there.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Hi -
I have a table of words and a table linking words in various ways:
create table allWords (
wordIDserial PRIMARY KEY,
word text
);
create unique index ix_allwords_word ON allwords (word);
create table allWordRelations (
word1ID integer references allWords,
wo
Andrew Edson wrote:
I am aware of this, yes, but the data in question is all (both
sets) contained on a single table. That's why I was looking for a
way to do a 'dump where (select foo where bar = 'criteria')'
structure.
What if you do that select into a new table, then pg_dump just that
T min(datecol1) FROM table1
UNION ALL
...
)
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
uation?
Thanks for any advice.
- John D. Burger
MITRE
---(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
nextval() and sequences are not what I'm looking for. I want to
assign the same id to all the rows imported from the same file.
Let's say user A is working on portfolio_id 3, and decides to
upload a spreadsheet with new values. I want to be able to import
the spreadsheet into the staging ta
Bryan Murphy wrote:
I think the other guys suggestion will work better. ;)
Good lord, yes. Dunno what I was thinking - I use partial indexes
all the time, and I know a unique constraint is implemented with an
index. Just got carried away, I guess. :)
- John Burger
MITRE
On Mar 29, 2007, at 17:39, Bryan Murphy wrote:
Is it possible to declare a unique constraint in combination with a
deleted flag?
For example, if I have a table like this:
CREATE TABLE
(
ID NOT NULL PRIMARY KEY,
Key VARCHAR(32) NOT NULL,
Value VARCHAR(32) NOT NULL,
Deleted INT NOT NUL
binary exchange of timestamps is complicated. What does libpq do now
with timetamps, if the client requests data in binary form? How does
the client know whether it's getting floats or integers?
- John D. Burger
MITRE
---(end of broadcast)-
for
the pointers toward this solution.
- John D. Burger
MITRE
---(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
under the covers, so my client doesn't spin up the cpu?
(Yes, I know I could poll-sleep-poll-sleep - that's what I'll do if
there's nothing cleaner.)
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
create a function lower index and instead of calling ilike call ~
lower('123')
To clarify a little:
CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
SELECT
*
FROM
table_a
WHERE
id
rithms pretty easily. I haven't
looked, but I would be surprised if there was much OOP in the
rational code, it might be fairly trivial to "dumb it down" to C.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have y
brary that provides rationals, or
model my code closely after one.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Tom Lane wrote:
How dangerous is it to UPDATE pg_class
directly, perhaps copying the relacl column for a table that I've
done by hand with GRANT.
You can do it, and it will seem to work. However, unless you also
make
entries in pg_shdepend, bad things will happen if you later drop
any of
Alvaro Herrera wrote:
If I am reading the (7.4) docs correctly, privileges can be granted
only with respect to tables that exist at the time the GRANT command
is given
Yes.
In fact, I have to individually grant access to each table, and any
associated sequences, yes? How dangerous is it
?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
I have a backup as of last night, but I'd like to recover something
more recent if I can. If I'm stuck with the backup, I can just
stop the postmaster, drop the whole PG directory into place from
the backup, and restart, yes?
I presume from the near-deafening silence there's nothing else I
John D. Burger wrote:
With a slip of the keyboard, I just dropped a database I'd like to
have
back. I don't have PITR or anything turned on - if nothing else has
And no backups?
been done to the cluster since then, is there any way to recover
anything at all?
I have a backup
With a slip of the keyboard, I just dropped a database I'd like to
have back. I don't have PITR or anything turned on - if nothing else
has been done to the cluster since then, is there any way to recover
anything at all?
Thanks.
- John Burger
MITRE
---(end of b
Sorry if I'm the only one to find this amusing, but I see that the
original message was sent twenty minutes =after= I received it. :)
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our extensiv
s others have observed, a
determined user can sniff the compound password out if they really
wish. I suspect the only really secure approach is some sort of
challenge-response algorithm, or a one-time pad in the application -
in either case, whatever the black-hat user sniffs off the wire or
quite a lot about the secret. If this is an issue, there are
more sophisticated combining schemes that give the user no advantage
over someone who knows neither half of the secret.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: e
Tom Lane wrote:
Since the tables you need to touch are all shared, it's conceivable
that
this could be hacked around, but it seems awfully messy. Another
consideration is that this'd significantly increase the amount of work
done before validating that the connection request is authorized,
Joris Dobbelsteen wrote:
Personally I've found nothing that will beat Excel for doing data
analysis. Learn to use the pivot table and pivot charts. They are
extremely powerful.
Funny, there is an on-going discussion about this on one of our
internal mailing lists. Excel is perhaps okay for
I had some ideas about this slow flat file issue, but it's apparently
not yet much of an issue, in fact ...
Someone talked about the postmaster having to be "at arms' length"
from the actual tables. But I was looking at the postmaster code,
and it seems to fork a new backend as soon as sel
ed would be to keep the out-of-memory copies of this kind of
data in something faster than a flat file - say Berkeley DB. Do
either of these things make sense?
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our exte
Ken Johanson wrote:
Just to be sure, will the RETURNING clause work with custom
sequences (say, non numeric or increment by two) or other types of
key generators?... And how will triggers interfere with it (if at
all)?
RETURNING has nothing to do with sequences per se - it's just a way
y more formats than I'd be likely to dream up
on my own.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Well, you could always start with something like Drupal:
http://www.drupal.com
I get access denied, seems to be here, rather: drupal.org
- John Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Mike Poe wrote:
SELECT foo, baz, bar FROM public.table WHERE lastname ~*
'$lastname' OR ssn='$ssn'"
I need to leave the last name a wildcard in case someone enters a
partial name, lower case / upper case, etc.
I want the SSN to match exactly if they search by that.
The way it's written, if
Tom Lane wrote:
The other point I'd make against John's argument is that there are a
whole lot of Fortune 500 companies buying Red Hat support, and RH is
effectively a third party for large chunks of Linux. (Of course,
there are also large chunks for which Red Hat employees write as much
code a
Joshua D. Drake wrote:
Surely there are also third-party companies that provide "support"
for MySqueal in some similar sense?
Of course :) but... Fortune 2500+ for the most part will *not* use a
third party for support for something like MySQL.
Sure, but they won't use PG either, for essenti
The good thing is that there are several companies supporting
Postgres,
so whatever one of them does it does not affect the market as a whole.
Surely there are also third-party companies that provide "support"
for MySqueal in some similar sense?
- John Burger
MITRE
Steve Crawford wrote:
Of course this breaks apart when dealing with that very rare syndrome
(name escapes me) where the child appears female at birth but is
actually a male whose male sex-organs descend and appear at puberty
so I
guess we need to add apparent_sex_at_birth.
It turns out ther
stallation - note that the above is only
guaranteed to work if architecture/compiler/etc. are all the same.
If the files were created by exactly the same PG instance, then you
should be okay.
- John D. Burger
MITRE
---(end of broadcast)---
mething like:
coalesce(a, 'SOME MAGIC VALUE') <> coalesce(b, 'SOME MAGIC VALUE')
and wanted to make it work for any types. Sigh.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Scott Ribe wrote:
where a <> b or (a is null and b is not null) or (a is not null and
b is null)
In the absence of IS DISTINCT FROM, I think this has the same semantics:
where coalesce(a, b) <> coalesce(b, a)
although it's not as concise as one might wish.
- John
n the OP's code sample seems problematic
in the other direction:
sprintf(buf, "%u", (unsigned int)PQoidValue(results));
since unsigned int could be as small as 16 bits, thus truncating the
OID value.
Ok, I'll stop now, I promise.
- John D. Burger
MITRE
own
problems.
Sorry for the pedantry ...
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
suppose) and can take a
while. Is there any way to convince the planner that the sorts are
unnecessary, and it can just zip the two tables together as is?
This is under PG 7.4, by the way. Any comments welcome.
- John D. Burger
MITRE
---(end of broadcast
Maurice Yarrow wrote:
So it turned out to be possible to do it like this:
CREATE SEQUENCE id_seq;
SELECT setval('id_seq',100111);
FYI, you could have done this:
CREATE SEQUENCE id_seq START 100111;
- John D. Burger
MITRE
---(end of
I'm having trouble figuring out when (if) the planner inlines sql
functions (I'm running 7.4). I was assuming that pure sql functions
are kind of like views with parameters, but I can't seem to see any
cases where functions that select from a table get inlined. For
instance:
create func
key a
composite:
PRIMARY KEY (user_id_from, user_id_to, message_time)
This should cut way down on the possibility of key collision.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Jochem van Dieten wrote:
I think you might want to check US Patent 6,763,359 before you
start writing any code.
http://tinyurl.com/yzjdve
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
to optionally use parametric
modeling (this column is a Gaussian, let's estimate the mean and
variance, this one is a Beta distribution ...). Then the smarter
planner could spend some cycles applying more sophisticated
statistical modeling to problematic tables/columns.
- John D. B
ownside= to doing it?
Here's a simpler question - for static data, should I always cluster
on the index I think will be used the most?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
es in [1, 10]. If I'm only comparing
within such ratings, and possibly computing floating point averages,
etc., what are the good and bad points of using, say, SMALLINT? What
about NUMERIC(1) or (2)?
Thanks in advance for the usual brilliant replies!
- John D. Burg
here other languages where sequences behave similarly?
> perl -e '@A = (1, 2, 3); print "@A\n"; $A[10] = 10; print "@A\n";'
1 2 3
1 2 310
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
have been surprised to find
such a nice comment pointing me at the literature.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Tom Lane wrote:
The information we've seen says that the only statistically
reliable way
to arrive at an accurate n_distinct estimate is to examine most of the
table :-(.
IIRC I picked an equation out of the literature partially on the basis
of it being simple and fairly cheap to compute...
)
This has nothing to do with SUM():
> select 4.88::float + 117.1::float + -121.98::float;
?column?
--
-1.4210854715202e-14
(1 row)
It's just the inherent inexactness of floating point, and probably not
even particular to Postgres.
- John D. Burger
g in the
view definition as is. Presumably something minor (to me) but a
show-stopper for the planner.
Sorry for the distraction. :(
- John D. Burger
MITRE
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
he view by hand. I was =very= surprised at this.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
never, never, never try quoting on your own! You can only fail. The
only choice is to fail now or later.
Nonetheless, in case it's useful, here's a barebones wrapper I call on
everything:
def sqlValue (value):
if value is None:
return "NULL"
elif type(value) == types.StringType:
One option is to write a Python translator to create CSV files, or even
an uploader to go directly from the Excel files to the database. There
is at least one module to read Excel files, in all their complexity:
http://cheeseshop.python.org/pypi/xlrd/0.5.2
and a number of Postgres modules:
1 - 100 of 160 matches
Mail list logo
