discovered this is
effectively what Perl does, probably for similar motivations wrt to hashes.
I think it's probably the least bad solution, even if it's not really the
right thing.
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
y strcoll and then strcmp will
effectively give this set of semantics with one exception, the case of invalid
UTF encodings that are not canonicalized where it will silently treat them as
distinct strings from the correctly encoded string.
One day when it's possible for the two strings to be in
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Greg Stark wrote:
> > Using iso-8859-1 to encode "é" as a single byte versus using UTF8
> > which would take two bytes to encode it is an issue of using two
> > *different* encodings.
>
> But that's
MSSQL can scan just the index (which postgres can't do) I would only expect
a factor of 2-4x. Hm. Unless perhaps this table is extremely wide? How large
are these records?
--
greg
---(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
second (or even third or fourth)
read in the rollback segment. And you pay these costs on *all* scans.
--
greg
---(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
ostgresql-contrib package) and then:
SELECT id, int_array_enum(val) FROM t7
--
greg
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
If you hard code the list of columns at each layer then you have to
rewrite lots of code when you add a column or change a datatype.
--
greg
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
tor of 3-4 then the merge join should be faster. If it's
really two orders of magnitude (100x?) then the nested loop below would be
faster. I think in 8.1 (and I think in 8.0 too) the planner is capable of
coming up with both plans for the NOT IN query though.
--
greg
-
useful still as a way of ensuring that every block is
read and written to, but then you have to look at the SMART data to see what
happened.
--
greg
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
only
difference would be on queries like "WHERE author_id = ? and book_id BETWEEN ?
AND ?". If you never do range scans then it will hardly matter which way you
go. I tend to do it the way above just so it matches the column order in the
table.
--
greg
-
anyways.
If you access your table with "WHERE a = ?" then it will undoubtedly use the
index effectively. You're accessing less than 5% (presumably much less), so
the index is useful.
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
said for MySQL's which
doesn't introduce an orthogonal syntax to GROUP BY with basically equivalent
meaning, but there's a lot to be said for having a syntax that you can't write
by accident as well.
--
greg
---(end of broadcast)---
olve the whole problem since functions like substr() or LIKE
are locale sensitive too. If you need an encoding like UTF-8 and you're stuck
either pushing all your string manipulations into the client or going ahead
with a non-C locale and UTF-8 even for the strings that are really just ascii
sion seems better than
depending on a huge external library. Especially when the consequences of that
non-standard extension being missing is only that performance will suffer in a
case Postgres currently doesn't handle at all.
--
greg
---(end of broadcast)
r php + some
> Typo3 extension.
>
> Does anyone know of a howto that explains what
> software I'll need for that, and where to get it?
>
> Does anyone here have experience with Typo3+Postgresql that he might
> want to share?
Typo uses migrations, so it should be database a
;);
>
> PLAN
...
> (29 rows)
That plan doesn't correspond to the query you posted. Try sending the result
of EXPLAIN ANALYZE from the actual query or else posting the actual query
you're running.
--
greg
---(end of broadcast)-
ry sequential
scan and on every cache miss it causes whatever you call it.
I'm not saying I like rollback segments better, just yes, TANSTAAFL.
--
greg
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your des
t; considered merge-joinable?
>
> I think you could do something involving a time interval datatype that
> considers "overlap" as equality and does something reasonable for
> sorting non-overlapping intervals.
How could a non-transitive property ever be merge joinable
h and
> italian at the same time.
Which locale would you use to perform comparisons for table joins?
In any case having actually implemented multilingual sites I can't imagine one
locale per table being useful at all. You always end up wanting per column,
even per array element locales
shouldn't want to allow unrestricted use of NEXTVAL either.)
What if you don't mind someone being able to use up unused values but don't
want someone to be able to rewind the sequence and generate duplicate key
violations breaking the application?
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
d-line and file format
interface.
Note that (as I understand it) nobody is saying Postgres is infringing on
anything. Only that combining postgres with OpenSSL and Freeradius results in
a combination of license restrictions that can't all be met at the same time.
So the resulting binary
7;s the basic property any integer should have after
all.
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
rd of a 15kRPM SATA drive.
Well, dollar for dollar you would get the best performance from slower drives
anyways since it would give you more spindles. 15kRPM drives are *expensive*.
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
dge because of IDE errors on
the bad drive. So as far as runtime, instead of added reliability all
you've really added is another point of failure. On the data integrity
front you'll still be better off.
--
Greg
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
.
So sadly I think "at the mercy of randomness" is pretty accurate. You'll have
to raise the statistics target as the table grows and I expect you'll
eventually run into some downsides of large stats targets.
Some better algorithms were posted, but they would require full
+ gcircle_tbl | t
> + gpolygon_tbl| t
>hash_f8_heap| t
>hash_i4_heap| t
>hash_name_heap | t
This seems pretty mystifying. Perhaps it's leftover stuff from the tablespace
that failed to get dropped?
--
greg
--
oing to break a LOT of things. Most Unix mail servers, for example, also
depend on directory operations being synchronous. I would expect "async" to
cause Postgres errors on any filesystem that supports it.
"async" "intr" and "soft" seem like the real foot-guns here.
--
greg
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
cesses.
The description of the option gave me the impression that this would only be
an issue if your processes were on two different clients.
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
. You might be able to find it searching back through the
lists.
There are also people interested in working on it as a built-in feature for
Postgres, but I don't think there's any time-line on though or even any
preliminary results yet, so I wouldn't depend on it any time soon.
Chris <[EMAIL PROTECTED]> writes:
> Craig White wrote:
> > I am getting an error when I import, invalid input syntax for type
> > boolean - which is empty. Is there any way around this of must I
> > necessarily have \N ?
You could try with:
COPY ...
failed to reset locale: %s", localestr);
/* restore normal error handling */
memcpy(&Warn_restart, &save_restart, sizeof(Warn_restart));
PG_RETURN_BYTEA_P(GET_BYTEA(trans));
}
SET search_path = public;
SET autocommit TO 'on';
CREATE OR REPLACE FUNCTION pg_strxfrm(text, text) RETURNS bytea
AS 'pg_strxfrm.so', 'pg_strxfrm'
LANGUAGE 'C' STRICT IMMUTABLE ;
--
greg
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
is and those records will be
dropped. This is true regardless of whether there's an index.
--
greg
---(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
be sure not to be issuing 50 thousand separate transactions, that will be
*much* slower.
--
greg
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
1.) I went to
the OpenSSL ste, and tried to download opnSSL, but I only saw Linux files. Is
OpenSSL supported ni Windows?
2.) I am using
the Npgsql.NET data provider to connect, but it doesn’t support SSL yet,
when will it support SSL?
Thanks
;
Resulting in:
Any thoughts? Thanks.
- Greg Gazanian
Network Systems Analyst
Technology and Information Services
Arcadia Unified School District
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
lass where
relname = 'oom_tab')
You may need to reanalyze and maybe increase the statistics target
(possibly by a lot). It may be interesting to compare the results of the above
query before and after analyzing too.
--
greg
---(end of broadcast)-
kes no sense. Vacuum starts by scanning the table itself, not the
indexes. It only goes to the indexes after it has found tuples that need
cleaning up. There's nothing to look at in the indexes that would tell it
whether there are any tuples to clean up.
--
greg
-
;special values" regardless
of data type rather than allow a single special value and have so many hard
coded magical behaviours.
--
greg
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
is temporary storage place be any faster than scanning the original
table?
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
ld have to go out of
your way to break it but if you're using some sort of connection pooling you
wouldn't want to pull a fresh connection from the pool, for example.
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
to the guest OS. When I buy
> a
> new laptop, I just install VMware on the new thing, copy over the virtual
> machines and fire them up. They don't even notice that they run on entirely
> different hardware.
How does that help? You still need to get VMWare's host OS working with t
I have a table with a field called "id" that is a serial field and
pkey. I would like to set the the sequence to start at 1 and
increase sequentially from there but I can't seem to get that to
work. any suggestions are greatly appreciated.
I am using ver
Hi Michael, from terminal in psql I enter
# ALTER SEQUENCE public.users MINVALUE ;
But it doesn't work. I don't think I am specifying the field
correctly. the schema is public, the table is users, the field is id.
Thanks,
Greg
On 20-Jul-06, at 10:49 PM, Michael Fuhr wrote:
O
Thanks Michael that did the trick!
Greg
On 21-Jul-06, at 12:05 AM, Michael Fuhr wrote:
On Thu, Jul 20, 2006 at 11:35:51PM -0400, Greg Philpott wrote:
Hi Michael, from terminal in psql I enter
# ALTER SEQUENCE public.users MINVALUE ;
But it doesn't work. I don't think I am spec
make those changes take effect. If you roll back your changes their
original state is restored.
--
greg
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
; ---+
> 3 | Offer 3
> 1 | Offer 1
> 2 | Offer 2
You could do something like
select id,offer
from tab
order by case when id = 3 then 1 when id = 1 then 2 when id = 2 then 3 end
But a) if there are thousands of records you're going to have rather a huge
query and b)
ace". The original question was about "tablespaces"
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
ll it to quote even integer arguments.
If it's any consolation 7.5 will use the index even if you put a plain integer
in the query. If production is a long way off you could consider developing
against a CVS build today and plan to use 7.5 for production when it comes
out.
--
greg
--
very least you have to include a secret. Even then I suspect
there are further subtle cryptographic issues. There always are.
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
avoiding round trip latency.
But I don't think there's any such support in postgres currently.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
You might have better success with the form of HAVING and appropriate use of
OR IS NULL as opposed to strict JOIN and WHERE conditions...
Similar to...
SELECT A.a, B.b, C.c FROM A, B, C
HAVING (A.b = B.b OR B.b IS NULL)
GROUP BY A.b;
--
Greg Patnude / The Digital Demention
2916 East Upper
dex code at least through most 7.3
versions.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
TH OIDS;
Using this type of table def will automatically create the sequence for
you -- and always ge thte next value when you do an insert -- ensuring that
you dont have duplicate...
so you would:
INSERT INTO test ('log', 'message');
then
SELECT * FROM test;
would gi
client like MS Access
postgreSQL is NOT just a high-powered version of MS Access or mySQL -- there
are quite a few differences --
Not to be rude -- but the postgreSQL docs (the Preface, Tutorial, and SQL
Language sections) would be good for you to read...
--
Greg Patnude / The Digital Demention
omething like
that.
So for example something like:
SELECT count(results.fixture) AS home_wins, home
FROM fixtures
LEFT OUTER JOIN results USING (fixture)
WHERE winner=home
GROUP BY fixture,home
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
for countable objects like "hours")
>
I'm confused, where was the sarcasm?
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Mike Mascari <[EMAIL PROTECTED]> writes:
> Bas Scheffers wrote:
>
> > Sarcasm: "A form of wit that is marked by the use of sarcastic language
Gee, a helpful dictionary there.
(I suppose that was sarcastic too)
--
greg
-
Sky <[EMAIL PROTECTED]> writes:
> uid CHARACTER(20) NOT NULL,
> pwd CHARACTER(20) NOT NULL,
Incidentally, are you sure you want character(20) ? The input will be padded
out to 20 characters with spaces. Usually people find varchar() more
convenien
use
context switches in a lot of cases where it did previously. You might need a
usleep() there instead or in addition.
--
greg
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail comm
we discussed doing exactly this at my last
job. I expect others have thought of the same idea and wouldn't be surprised
if it existed by now.
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
chemas don't affect performance at all.
If you're debating between storing in the same table versus new identical
tables there could be pros or cons, but if it complicates your SQL it's
probably not worth it in any case.
--
greg
---(end of broadcast)--
web pages, Jobs could be created or deleted from web pages,
failures could automatically create trouble tickets...
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
database resides.
It seems like the obvious solution would be quotas. You would have to set up
each database as a separate postgres postmaster running under a different uid
though.
--
greg
---(end of broadcast)---
TIP 9: the planner will ignore yo
.
I don't think it would be any faster but it might avoid downtime. Reindex
seems to block any use of the index until the reindex completes, while doing
this two-step would avoid blocking queries. I haven't tried it myself though
so I'm not sure what gotchas might arise.
--
greg
-
n the rest of the input file
as arguments and which never stops expanding.
\endAbstract
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
owever unless you lock the table while doing this, you have to be prepared
> to handle errors.
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
ve preferred bytea(16) but for some reason the php
drivers seem to jut drop NULL there when I try to store raw binary md5 hashes.
So for now I just declared it bytea with no length specification and store the
hex encoded hash.
If anyone knows how to get Pear::DB to store binary data in a bytea colum
a higher
expected cost but a lower worst-case cost than the sequential scan.
For some applications the best bet may in fact be to go with the plan expected
to be fastest. But for others it would be more important to go with the plan
that is least likely to perform badly, even if it means paying
on logs wouldn't make it safe to run
without fsync, but it would make the fsyncs basically free.
--
greg
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
worked out very well for reporting needs. If your system is using the
data to handle serving the ads, though, it's a different kettle of fish. For
that I think you'll want something that avoids having to do a database query
for every single impression.
--
greg
--
returns varchar
as 'select $1 || '', '' || $2'
language sql
strict immutable;
create aggregate concat_agg (
basetype = varchar,
stype = varchar,
sfunc = concat_agg_accum
);
select concat_agg(name) as name, ...
--
greg
--
Robert,
I am currently evaluating PolyServe Matrix Server which is a clustering
solution including a clustered file system (mounted read-write
everywhere).
Anything special anyone wants to know? I'm using PostgreSQL 7.4.2 in
a Linux cluster.
Greg
Robert Treat wrote:
On Thu, 2004-05-20 at
David Garamond <[EMAIL PROTECTED]> writes:
> Greg Stark wrote:
> >>Actually, each record will be incremented probably only thousands of times a
> >>day. But there are many banners. Each record has a (bannerid, campaignid,
> >>websiteid, date, countrycode)
This avoids an extra
sort/uniquify step. Again it wouldn't really save much time since the extra
sort was only across 2 rows. But I like seeing simple clean plans even if they
aren't really much faster.
--
greg
---(end of broadcast)---
TIP 8: explain analyze is your friend
of Postgres. I would expect to see it come along sometime, though probably not
in 7.5.
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
ough.
Doing the same tests on the PolyServe file system (psfs) accomplished
the tasks in 117 MB/s and 109 MB/s, just slightly faster.
If you're familiar with Veritas Cluster Server it operates similiar
to it though I didn't have a chance to fully test it.
Given the budget I'd defin
eques_feb, cheques_mar,...
And a union of all the months. Any query that only spanned one month would use
the monthly table so it could do a sequential scan of just that month. I don't
recommend this unless you have a _lot_ of data that you often deal with in
particular c
L sometime when you can deal with 15min of downtime
or so. Actually it would probably be longer. Perhaps the table that's taking
15min has a ton of extra dead tuples left over from the fsm settings being too
low and/or vacuum being too infrequent.
--
greg
---(end
th the
value of the column in OLD and blocked the update/delete if it doesn't match.
--
greg
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that
ood, because you'll eventually need it.
All it will take will be a Linux crash for the database files on disk to
become corrupted. No amount of UPS or RAID protection will protect from that.
--
greg
---(end of broadcast)---
TIP 6: Have
econfigured MTAs without impacting
the database or application at all.
Incidentally, you can arrange things to fail in either direction. In our case
if the cron job failed we would lose a batch of emails, not generate
duplicates. I'm not sure if failing by generating duplicates is
That would explain the unkillable part, though I'm curious how
> > it ended up there.
Is there an NFS server involved? If an NFS server disappears any process
waiting on I/O for it enters disk-wait indefinitely until it reappears.
--
greg
---(end of broadcast)-
s as of 7.4 is capable
of it but even for Postgres not all the drivers have been updated. But that's
mostly irrelevant, at least make it the responsibility of the driver to do the
interpolating, it's more likely to get it right and whatever it does, it will
at least be 100% consistent a
omparisons and integer constants are assumed to be integer not bigint type.
If you leave it in quotes then postgres doesn't pick a type until it looks at
what you're comparing it with. Or if you cast it then you force it to be a
bigint=bigint comparison.
7.5 will avoid this probl
your accesses which would be
bad if you had lots of clients doing small increments, but if you have few
clients doing large increments it shouldn't be a problem.
Do make sure to vacuum this table frequently though.
--
greg
---(end of broadcast)--
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> This is less than a year after the last major release, 7.3.0 by the way.
Uhm. Wasn't 7.4 the last major release?
--
greg
---(end of broadcast)---
TIP 6: Have you se
of them in one shot than one at a time.
--
greg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
more info than normal.
Is there any way to ask the server what plan it's using when it's actually
executing the query in production, rather than trying to feed it the same
query later in another context and hope it gets the same result?
--
greg
---(e
worth the headaches to me personally.
For stability, db/web server usage and such, I'd go with Debian.
For features, desktop systems, etc., I'd go with Suse. 9.1 is
impressive.
For security, firewall, or router usage, I'd go with *BSD.
--
Greg Donald
---
ovide a way for you to add unstable
packages using ACCEPT_KEYWORDS="~x86". I never used the command because
I never _wanted_ unstable packages. Seems I got them anyway.
--
Greg Donald
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
e table.
In 8.0 there will be a command to do what you want, but it will still have to
do all the same work as I described above.
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > I'm not about to run analyze in the middle of the data generation
> > (which wouldn't work anyways since it's in a transaction).
>
> Since 7.3 or 7.4, you *can* r
Joe Conway <[EMAIL PROTECTED]> writes:
> I don't know of a builtin way to do that from SQL, but the attached seems to
> work for me.
Cool. Thank you.
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists
ters and quote "s inside them.
Or better yet (imho) is to use the new array[...] constructor. That you can do
using the existing quoting functions without worrying about someone sneaking a
double quote inside your values. They also play nicer with placeholders.
--
greg
---
Is it legal syntax to use <> instead of != in a Postgres query?
I didn't see it listed on:
http://www.postgresql.org/docs/7.4/static/sql-syntax.html
but wanted to ask to make sure.
--
Greg Donald
---(end of broadcast)---
TIP 3:
Jerome Lyles <[EMAIL PROTECTED]> writes:
> the/@@/ is what the whitespace is replaced with but I don't know what the @@
> stands for,
It doesn't stand for anything, it just means replace with "@@". Now look again
at the second and
t to migrate PHPBB running on a server with mySQL to
> another server running with postgreSQL). Yes PHPBB has postgreSQL
> support.
I found this utility very helpful in a similar situation:
http://freshmeat.net/projects/my2pg/
--
Greg Donald
---(end of broadc
> not far ahead of public research in this field ...
It's presumably ahead. But not like the situation 30 years ago when they were
the only group doing this kind of research.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ed and least dangerous. It's hard to picture someone intentionally doing
?||'%' without thinking it would use an index scan. If they didn't check for
leading %s and _s or empty parameters then it was their oversight or they were
expecting it to be slow.
--
greg
--
ntrib directory.
It would probably be quite simple to implement, though the most complex in
another sense. With twice as many connections it could be hard to determine
where slowdowns are starting for example. And Better make sure your error
logging connections don't trigger
1301 - 1400 of 1774 matches
Mail list logo
