and later)
it's possible that you're running a version that doesn't support
win1252.
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
:
SELECT pg_postmaster_start_time();
SELECT now() - pg_postmaster_start_time();
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
: start_time
- Seq Scan on stats (cost=0.00..1541.00 rows=10 width=12) (actual
time=0.091..500.853 rows=10 loops=1)
Total runtime: 4226.870 ms
(4 rows)
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
under revision control) then
you can write a deployment script like the following (to be executed
via psql):
BEGIN;
DROP VIEW view_name;
ALTER TABLE table_name ALTER column_name TYPE type_name;
\i views/view_name.sql
COMMIT;
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general
not display correctly.
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jun 26, 2008 at 03:31:01PM +0200, Albe Laurenz wrote:
Michael Fuhr wrote:
Your input data seems to have a mix of encodings: sometimes you're
getting pound signs in a non-UTF-8 encoding, but if characters like
U+2019 RIGHT SINGLE QUOTATION MARK got into the database when
union select 2 as foo)bar;
Perhaps VALUES?
regression=# select max(foo) from (values(1,2),(3,4),(5,6)) as v(foo,bar);
Or perhaps using a set-returning function like generate_series():
test= select max(foo) from generate_series(1, 100) as g(foo);
max
-
100
(1 row)
--
Michael Fuhr
every table in every
database at least once every two billion transactions.
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jun 18, 2008 at 08:25:07AM +0200, Giorgio Valoti wrote:
On 18/giu/08, at 03:04, Michael Fuhr wrote:
Is the data UTF-8? If the error is 'invalid byte sequence for
encoding UTF8: 0xa3' then you probably need to set client_encoding
to latin1, latin9, or win1252.
Why?
UTF-8 has
this problem? Client_encoding =UTF8.
Is the data UTF-8? If the error is 'invalid byte sequence for encoding
UTF8: 0xa3' then you probably need to set client_encoding to latin1,
latin9, or win1252.
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
On Sat, Jun 14, 2008 at 02:35:38PM -0400, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
I created a test case that has close to the same estimated and
actual row counts and has the same plan if I disable enable_nestloop:
There's something weird about this --- why does the second
) (actual time=0.068..0.068 rows=0 loops=1)
Index Cond: (apsilankymai.aps_saskaita = b_saskaita.sas_id)
Total runtime: 1.321 ms
Julius, do you perchance have enable_nestloop = off? If so, do you
get a better plan if you enable it? Also, have you run ANALYZE
lately?
--
Michael Fuhr
trustworthy then you could end up with HTML that's
different from what you intended.
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
:
73 65 6c 65 63 74 20 72 65 67 65 78 70 5f 72 65 |select regexp_re|
0010 70 6c 61 63 65 28 0a 20 20 20 27 5b 70 3d 31 32 |place(. '[p=12|
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
---
a href=./family.php?person=123John Smith] and [p=456|Jane Doe/a
(1 row)
Hey, I told it not to be greedy, didn't I?
Yes, but regexp_replace only replaces that part of the original
string that matches the regular expression -- the rest it leaves
alone.
--
Michael Fuhr
--
Sent via pgsql-general
On Tue, Jun 10, 2008 at 07:41:53AM -0600, Michael Fuhr wrote:
On Tue, Jun 10, 2008 at 02:59:53PM +0200, Leif B. Kristensen wrote:
So far, so good. But look here:
pgslekt= select link_expand('[p=123|John Smith] and [p=456|Jane Doe]');
link_expand
this is, I cannot view it in my
browser, etc.
It translates to Unicode 10BB7, which is not defined.
Actually it's U+20BB7 CJK UNIFIED IDEOGRAPH-20BB7.
http://www.unicode.org/cgi-bin/GetUnihanData.pl?codepoint=20BB7
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general
,PA,VA}
WY|{CO,ID,MT,NE,SD,UT}
--
Michael Fuhr
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
this:
insert into routes_geom values(1, 'J084', GeomFromText('LINESTRING(-121.00
38.20, -118.00 38.20)', 4326));
You might wish to subscribe to the postgis-users mailing list if you
have additional questions.
--
Michael Fuhr
---(end of broadcast
to be succesfull or
no one of them should be carried out.
That's the behavior you'll get if you use a transaction. No changes
will be visible to other transactions until you successfully commit.
--
Michael Fuhr
---(end of broadcast)---
TIP 4
),'') = COALESCE(UPPER(Y.PLZZ),'') AND
COALESCE(UPPER(X.PLZP),'') = COALESCE(UPPER(Y.PLZP),'') AND
X.RecordID Y.RecordID)
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Thu, Feb 14, 2008 at 04:48:33PM +0100, Hermann Muster wrote:
Michael Fuhr schrieb:
COALESCE(UPPER(SUBSTR(X.Firma,1,7)) =
I haven't examined the entire query but the above line appears to
be the problem. Did you mean to write the following?
COALESCE(UPPER(SUBSTR(X.Firma,1,7
,
especially at the end of the query string (syntax error at end of
input).
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
anyone know of an easy way to work around this?
You could convert the empty strings to NULL:
USING cast(nullif(amount, '') AS numeric)
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
such a message.
I'd guess you're using PostGIS and those tables have NULL in all
rows' geometry columns.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
rates are low (a few hundred per minute) and
most logic is in database functions with statements that operate
on hundreds or thousands of rows at a time. Still, this gives an
idea of what a PostgreSQL database on decent hardware can handle.
--
Michael Fuhr
---(end
Athlon 64 X2s with 32G RAM. At
least some of the storage is SAN-attached.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
environment.
http://www.postgresql.org/docs/8.2/interactive/app-initdb.html
The default is derived from the locale, or SQL_ASCII if that does not work.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
'[[\\]+]', '', 'g') from foo;
id | t | regexp_replace
++
1 | foo[]+\bar | foobar
(1 row)
test= select id, t, translate(t, e'[]\\+', '') from foo;
id | t | translate
++---
1 | foo[]+\bar | foobar
(1 row)
--
Michael Fuhr
violated?
In PL/pgSQL you could extract the constraint name from SQLERRM,
which should be a string like 'duplicate key violates unique
constraint foo_id1_key'.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
to time without time zone you're now getting
the number of seconds since 00:00:00 in your local time zone.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
that should be
returned...
Put the lower value first or use BETWEEN SYMMETRIC:
select * from foobar where ts between now() - interval '5 days' and now()
select * from foobar where ts between symmetric now() and now() - interval '5
days'
--
Michael Fuhr
---(end
been fixed in CVS, however). As I mention in the second message
above, vacuuming pg_shdepend resulted in an immediate performance
improvement in an application I was investigating.
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below
. Are you sure your pg_dump is linked against an
SSL-enabled libpq? Have you tried setting the PGSSLMODE environment
variable? What version of PostgreSQL are you running?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive
On Fri, Jul 27, 2007 at 09:33:37AM -0700, [EMAIL PROTECTED] wrote:
Is there a way to configure PostgreSQL 8.0 so that when prompted for a
password, the user enters the MD5 hash of his password, instead of the
normal plaintext password?
What problem are you trying to solve?
--
Michael Fuhr
() or pgp_sym_encrypt_bytea() instead of encrypt().
See the Raw encryption section of README.pgcrypto for some of the
disadvantages of encrypt().
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
list
has the following item:
* Allow accurate statistics to be collected on indexes with more
than one column or expression indexes, perhaps using per-index
statistics
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list
Matching section of the Functions and Operators chapter of the
documentation.
http://www.postgresql.org/docs/8.2/interactive/functions-matching.html
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
and explain how the results differ from what you'd like. If you're
getting errors then please post the exact text of the error messages.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
in the process?
In other words, does encoding influence only data stored in tables, or
it influences database structure as well?
I can't think of how the encoding would influence the structure.
Are you seeing behavior that suggests otherwise?
--
Michael Fuhr
---(end of broadcast
the
performance-related documents at Power PostgreSQL:
http://www.powerpostgresql.com/Docs
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
could add a column to companies that is always set to Company but
that seems like a waste. I tried the above and I got a syntax error.
What purpose is the constraint intended to achieve?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain
this is what you're looking for:
select avg(length)
from (
select distinct on (id) length(consensus)
from cluster
order by id, length(consensus) desc
) s;
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives
and strip unconvertible characters; on many systems
you could do that with iconv -f iso8859-8 -t utf-8 -c. If you
convert to UTF-8 then you'd need to change client_encoding accordingly.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our
PostgreSQL (not Postgre) 8.4 doesn't exist; do you mean 8.2.4?
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
/pg_locale.c in the PostgreSQL source
code has comments about how various LC_* settings are used in the
backend.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
the shared memory key that the
postmaster is complaining about.
--
Michael Fuhr
---(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
But when I do
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
I get a list of the tables and their sizes.
Are the tables in schemas that are in your search_path?
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9
?
Is it possible to catch errors generated pl/perl functions in a BEGIN ...
EXCEPTION WHEN ... END block? Or perhaps in some other way?
You could use WHEN internal_error or WHEN others. If that
doesn't work then please post a simple but complete example that
shows what you're trying to do.
--
Michael Fuhr
be appropriate?
* Allow RAISE and its analogues to set SQLSTATE.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
to a power outage.
Has anybody figured out if this is a Linux kernel bug? I might
have until Monday morning if anybody can suggest something to look
at; after that the admins will probably reboot and/or remove
postmaster.pid to get the database running again.
Thanks.
--
Michael Fuhr
On Sun, Jul 01, 2007 at 10:06:58PM -0400, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
Has anybody figured out if this is a Linux kernel bug? I might
have until Monday morning if anybody can suggest something to look
at; after that the admins will probably reboot and/or remove
--
Michael Fuhr
---(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
On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
On Mon, Jun 25, 2007 at 09:51:30AM +0900, [EMAIL PROTECTED] wrote:
It seems that real takes 8 byte storage sizes.
Real is 4 bytes but other columns' alignment requirements might
result
On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote:
As for that pg_dump measurement, the text form isn't going to get
smaller ... 1.2 is the same length as 1.2.
Non-text formats like -Fc should (or might) shrink, right? They
appear to in the tests I've done.
--
Michael Fuhr
be necessary.
But PostgreSQL's data disk usage did not shrinked.
And pg_dump size remained same.
It seems that real takes 8 byte storage sizes.
Real is 4 bytes but other columns' alignment requirements might
result in no space being saved.
--
Michael Fuhr
---(end of broadcast
-trigger.html
--
Michael Fuhr
---(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
way to maintain statistics
and clean up dead rows automatically.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
also normal_rand() in contrib/tablefunc.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
psql with the
-E (--echo-hidden) option or by executing \set ECHO_HIDDEN. This
is a helpful way to learn about the system catalogs.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
expression; simply casting EXTRACT's result to numeric
won't work. One possibility might involve floor and to_char(value, '.US').
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
for an
example (the example is at the bottom of the page).
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
a certificate
signed by a specific CA and the client could require the same of the
server; see the discussion of root.crt for more information.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
coming
from the database? What do the database logs show? Are you sure
you're using the correct username and password? What do you have
in pg_hba.conf?
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map
/interactive/libpq-pgservice.html
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--++--
public | foo| pg.dropped.2
public | foo| col1
public | foo| col3
(3 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below
to
create it yourself.
--
Michael Fuhr
---(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
,-0.435095,6.9}
\.
--
Michael Fuhr
---(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
://postgis.refractions.net/docs/ch06.html#id2527029
http://geos.refractions.net/
--
Michael Fuhr
---(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
| item_org_id | item_active
-+---+-+-
1 | one | 1 | t
2 | two | 1 | t
3 | three | 1 | t
4 | four | 1 | t
(4 rows)
COMMIT;
--
Michael Fuhr
On Mon, May 28, 2007 at 07:07:41PM +0200, Poul Møller Hansen wrote:
I'm wondering why the sort order on these two servers behaves differently.
What's the output of the following query on each server?
select name, setting from pg_settings where name ~ '^lc_';
--
Michael Fuhr
.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Mon, May 28, 2007 at 04:14:14PM -0600, Michael Fuhr wrote:
On Fri, May 25, 2007 at 04:47:52PM -0400, Justin M Wozniak wrote:
We noticed that some records were mysteriously disappearing from
our DB. I went in with psql and found that the \dt command no longer
works, providing
anyelement
I think this has already been fixed in CVS:
http://archives.postgresql.org/pgsql-hackers/2007-05/msg00014.php
http://archives.postgresql.org/pgsql-committers/2007-05/msg00011.php
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget
) then you have a bit of a problem
because some data needs to be converted from LATIN9 to UTF8 but
other data is already UTF8 and shouldn't be converted.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
debug messages.
--
Michael Fuhr
---(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
.
How and where can I print these values while running the plperlu function.
Use elog().
http://www.postgresql.org/docs/8.2/interactive/plperl-database.html
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html
22P02 INVALID TEXT REPRESENTATION invalid_text_representation
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
suggest?
Which side of the connection is in FIN_WAIT_2? What's the netstat
output for both sides? What can you tell us about how this application
works?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
:5009
#2 0x00020dd8 in main (argc=4, argv=0x6e) at pg_dump.c:691
(gdb) l 8733,+1
8733appendPQExpBuffer(query, .%s;\n,
8734fmtId(owning_tab-attnames[tbinfo-owning_col -
1]));
(gdb) p owning_tab-attnames
$1 = (char **) 0x0
--
Michael Fuhr
: ERROR: could not serialize access due to concurrent update
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message
? If you do a database-wide
VACUUM VERBOSE, what are the last few lines of the output that
mention free space map settings?
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose
On Sat, Apr 07, 2007 at 01:49:38PM +0200, Marcus Engene wrote:
Michael Fuhr skrev:
How often does this table receive updates and deletes and how often
are you vacuuming it?
If I should take a guess, there are 5 deletes per day and 5 updates or
inserts per hour. The table is 1.5 years old
:
for red in select NULL, NULL, NULL from example1 loop
The code should work if you qualify the columns:
for red in select e.id, e.name1, e.value1 from example1 e loop
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Fri, Apr 06, 2007 at 01:10:13PM -0400, Harpreet Dhaliwal wrote:
Can i have more than one trigger on one single table. Actually I want 2
different events to take place simultaneously and independently after
insert.
What happened when you tried it?
--
Michael Fuhr
/functions-info.html
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
doesn't know about the row that T1 inserted but now T2
knows that something happened to the version of the row it was
trying to delete.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
:
\set ECHO_HIDDEN
\d+ mytab
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
increasing the statistics target for
tbl_file.fk_filetype_id and perhaps some of the columns in the join
conditions.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
On Fri, Mar 23, 2007 at 12:41:58PM +0100, Henrik Zagerholm wrote:
23 mar 2007 kl. 12:33 skrev Michael Fuhr:
The row count estimate for fk_filetype_id = 83 is high by an order
of magnitude:
Bitmap Index Scan on tbl_file_idx6 (cost=0.00..25.65 rows=1251
width=0) (actual time=21.958..21.958
://www.powerpostgresql.com/PerfList
If you have additional performance-related questions then consider
posting to pgsql-performance.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
values.
See also the TOAST documentation:
http://www.postgresql.org/docs/8.2/interactive/storage-toast.html
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
you're trying to do.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
*transaction*. Connect to the database you're trying to drop and
use ROLLBACK PREPARED or COMMIT PREPARED, then disconnect from that
database and try dropping it again.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
command is issued
on it, or the session ends. In the current implementation, the rows
represented by a held cursor are copied into a temporary file or
memory area so that they remain available for subsequent transactions.
--
Michael Fuhr
---(end of broadcast
environment variable. Also, make sure group and
world have no permissions on the file.
http://www.postgresql.org/docs/8.2/interactive/libpq-pgpass.html
http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html
--
Michael Fuhr
---(end of broadcast
or WIN1252 for Western European languages).
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
to be checked and possibly converted. Doing that on a large
running system would be problematic; it would probably be just as
easy to dump and restore the entire database.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free
%' queries.
See Operator Classes in the Indexes chapter of the documentation.
http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
1 - 100 of 1290 matches
Mail list logo
