|
|
client_encoding
By Dean Gibson (DB Administrator) at Feb 22, 2008, 03:59 am UTC
If I "ALTER DATABASE ... SET client_encoding TO DEFAULT", is the default the "client_encoding" in postgresql.conf when the server was last started, or the value at the time the "ALTER DATABASE ... SET client_encoding TO DEFAULT" statement is executed? In other words, if I "ALTER DATABASE ... SET... More...
If I "ALTER DATABASE ... SET client_encoding TO DEFAULT", is the default
the "client_encoding" in postgresql.conf when the server was last
started, or the value at the time the "ALTER DATABASE ... SET
client_encoding TO DEFAULT" statement is executed?
In other words, if I "ALTER DATABASE ... SET client_encoding TO DEFAULT"
and then restart the server with a different "client_encoding" value in
postgresql.conf, will client_encoding of the database follow the new
"client_encoding" value in postgresql.conf?
My guess is no; that it will keep the "client_encoding" it received
when the "ALTER DATABASE ... SET client_encoding TO DEFAULT" statement
was executed.
What I'm looking for is a way to have the client_encoding of all
databases track some common setting (eg, the "client_encoding" value in
postgresql.conf), but that may not be possible. I know I can use
"PGCLIENTENCODING=${LANG#*.}" (and that's not a bad solution), but I'm
curious if there's a way to avoid that (and similar client methods).
Right now I'm basically ignoring the database's "client_encoding"
setting, and always explicitly setting it in a session where needed.
-- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly
1 Reply
|
|
|
Feedback on PLJava
By Pascal Cohen at Feb 22, 2008, 03:33 am UTC
Hello Are there users of PL/Java ? I would like to get some feedback on using it. As we could use triggers and as we have already some kinds of 'applicative' triggers written in Java, I was wondering if I could just reuse the code and plug it Thanks for any advice ---------------------------(end of... More...
Hello
Are there users of PL/Java ?
I would like to get some feedback on using it.
As we could use triggers and as we have already some kinds of
'applicative' triggers written in Java, I was wondering if I could just
reuse the code and plug it
Thanks for any advice
1 Reply
|
|
|
Postgres 8.3 broke everything
By Alex Turner at Feb 22, 2008, 02:28 am UTC
Upgrading to Postgres 8.3 broke virtually every site we host, and I finally figured out why. In 8.2 you could do this: create table foo ( ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings More...
Upgrading to Postgres 8.3 broke virtually every site we host, and I
finally figured out why. In 8.2 you could do this:
create table foo (
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
4 Replies
|
|
|
need some help on figuring out how to write a query
By Justin at Feb 22, 2008, 02:09 am UTC
need to write a query for sales order table where we get the qty order * price for all line items by week then average then create a moving Average past 10 weeks of entered sales orders line items. So what will happen is some one will enter a date range going back 6 month from the present date then... More...
need to write a query for sales order table where we get the qty order *
price for all line items by week then average then create a moving
Average past 10 weeks of entered sales orders line items.
So what will happen is some one will enter a date range going back 6
month from the present date then query will need to get all the sales
order line items that falls in that date range by week, then average the
first 10 weeks, drop of the first week moving to the 2nd week to the
11th week to create another average, then drops 2nd week off then moves
3rd week to the 12th week in the query date range create another average
and so and so till the end of the date range. This will create moving
average for sales order entered.
Now i could write a function to do this or do it in C++ program that
creates query with all kinds of unions. I'm wondering if there is a way
to create this in a single select statement??
I can't think of a way to do it???
1 Reply
|
|
|
New inst. 8.3.0 has no client
By John D. Tiedeman at Feb 22, 2008, 01:48 am UTC
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C874BE.C1A16FD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable When I upgraded to Kubuntu 7.10 I opted to install 8.3 rather than stick = with 8.2. I knew to dump my 8.2 files first.... More...
This is a multi-part message in MIME format.
------=_NextPart_000_0006_01C874BE.C1A16FD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
When I upgraded to Kubuntu 7.10 I opted to install 8.3 rather than stick with 8.2. I knew to dump my 8.2 files first.
After I finished installing, I got (and still get) a message that I should have an 8.3.0-client. I don't see any separate downloads listed so assume it's in the standard package. Is there a step I might have missed?
------=_NextPart_000_0006_01C874BE.C1A16FD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.3268" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>When I upgraded to Kubuntu 7.10 I opted to install
8.3 rather than stick with 8.2. I knew to dump my 8.2 files first.</FONT></DIV>
<DIV><FONT face=Arial size=2>Af</FONT><FONT face=Arial size=2>ter I finished
installing, I got (and still get) a message that I should have an 8.3.0-client.
I don't see any separate downloads listed so assume it's in the standard
package. Is there a step I might have missed?</FONT></DIV></BODY></HTML>
------=_NextPart_000_0006_01C874BE.C1A16FD0--
2 Replies
|
|
|
ts_headline
By Stephen Davies at Feb 22, 2008, 12:07 am UTC
I am a bit puzzled by the output of ts_headline (V8.3) for different queries. I have one record in a test documentation table and am applying different queries against that table to check out the ts_headline outputs. The "document" in question has 2553 words which generate 519 tokens in the... More...
I am a bit puzzled by the output of ts_headline (V8.3) for different queries.
I have one record in a test documentation table and am applying different
queries against that table to check out the ts_headline outputs.
The "document" in question has 2553 words which generate 519 tokens in the
ts_vector.
For most queries, ts_headline returns a string starting with one of the
criterion words and with all criterion words highlit - as I would expect.
However, some other queries return a string which seems to always start at the
beginning of the "document" and contains no highlit terms.
It seems that the difference is in the number of occurrences of the criterion
words. If the number of hits is less than some number, the ts_headline result
is "correct" but if the number of hits exceeds that limit, the result is just
the first MinWords of the "document".
I have seen cases with up to 20 hits succeed but cases with 35 hits miss.
The spread of hits does not seem to be relevant.
Is this a bug or am I missing some configuration option?
TIA,
Stephen Davies
--
========================================================================
This email is for the person(s) identified above, and is confidential to
the sender and the person(s). No one else is authorised to use or
disseminate this email or its contents.
Stephen Davies Consulting Voice: 08-8177 1595
Adelaide, South Australia. Fax: 08-8177 0133
Computing & Network solutions. Mobile:0403 0405 83
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
9 Replies
|
|
|
Install problem w/8.3 on windows 2000 : application failed to initialize properly (0xc0000022)
By Dee at Feb 21, 2008, 10:30 pm UTC
Hi. This is my first post to this list. So please let me know if my question is in the wrong area. I am trying to install version 8.3.0.1 on an old windows 2000 box I inherited. Firewall s/w is installed but disabled. I am logged in as a user with Administrative rights. When the installing gets to... More...
Hi. This is my first post to this list. So please let me know if my question is in the wrong area.
I am trying to install version 8.3.0.1 on an old windows 2000 box I inherited. Firewall s/w is installed but disabled. I am logged in as a user with Administrative rights. When the installing gets to the step "Starting Services". It halts with the error
ERROR:
"The application failed to initialize properly (0xc0000022). Click on OK to terminate the application."
LOG SNIPPET:
==========================================
StartServices: Service: PostgreSQL Database Server 8.3
MSI (c) (04:00) [22:12:14:687]: Note: 1: 2205 2: 3: Error
MSI (c) (04:00) [22:12:14:687]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2888
DEBUG: Error 2888: Executing the TextStyle view failed
The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2888. The arguments are: TextStyle, ,
Action 22:13:06: Cancel. Dialog created
==========================================
It is an old box with lots of programs installed. So it may well be a configuration problem. Anyone have any suggestions how I might go about diagnosing the problem? Btw, I am not a w2k admin, but if you need more information, please let me know. Any suggestions would be appreciated!
---------------------------------
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
3 Replies
|
|
|
Disable Triggers
By Terry Lee Tucker at Feb 21, 2008, 10:07 pm UTC
Greetings: We have been working diligently toward integrating Slony into our production databases. We've been having trouble with various tables, although being replicated perfectly in the initial replication stage, afterwards, getting out of sync. I have finally figured out what the problem is. We... More...
Greetings:
We have been working diligently toward integrating Slony into our production
databases. We've been having trouble with various tables, although being
replicated perfectly in the initial replication stage, afterwards, getting
out of sync.
I have finally figured out what the problem is. We have a Perl process that
continually updates certain columns across all databases. That Perl process
calls a function we have written called disable_triggers which updates
pg_class, setting reltriggers to 0 for the given table, and then later, after
the work is complete, resetting reltriggers to the original value.
Unfortunately, during this process, the Slony trigger is disabled as well
which is causing our problem.
My questions is this: how would I go about changing my function so that all
the triggers EXCEPT the Slony trigger would be disabled? Any ideas?
Version:
PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-9)
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
[email protected: t...@turbocorp.com]
www.turbocorp.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
14 Replies
|
|
|
No pgxs.mk with win32 binaries
By Mike Leahy at Feb 21, 2008, 8:04 pm UTC
Hello list, I'm trying to build the PL/R module on Win32 with PostgreSQL 8.3. I've done this before with no trouble using several 8.2.x versions of PostgreSQL (from the win32 installer). However, now that I'm trying to update things for 8.3, it seems that the pgxs.mk file needed by the PL/R... More...
Hello list,
I'm trying to build the PL/R module on Win32 with PostgreSQL 8.3. I've
done this before with no trouble using several 8.2.x versions of
PostgreSQL (from the win32 installer). However, now that I'm trying to
update things for 8.3, it seems that the pgxs.mk file needed by the PL/R
Makefile is not included with the PostgreSQL 8.3 installer (PL/R is
looking for this file in this location:
c:/progra~1/postgresql/8.3/lib/pgxs/src/makefiles/pgxs.mk). I checked
in the other zip packages listed on the win32 downloads page, and it's
not in either of those. Are these files no longer part of PostgreSQL
as-of 8.3, or might they be downloadable from some other location?
Thanks for any suggestions,
Mike
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email protected: majo...@postgresql.org] so that your
message can get through to the mailing list cleanly
2 Replies
|
|
|
Mac ordering with locales
By Pascal Cohen at Feb 21, 2008, 7:14 pm UTC
Hello We are using different OS (Mac, win (with a small w!) and Linux). We had some problems when doing some ordering on names. I found that it seemed to be related on locale, While on Linux it used default locales (en_US.UTF-8 or something like that), I had a nice behavior). I learned to check th... More...
Hello
We are using different OS (Mac, win (with a small w!) and Linux).
We had some problems when doing some ordering on names.
I found that it seemed to be related on locale, While on Linux it used
default locales (en_US.UTF-8 or something like that), I had a nice
behavior).
I learned to check th lc_collate value to know on which locale ordering
was based.
I managed to create a DB under windows setting the right locale to
replace the default (en_US.UTF-8 instead of C) and it worked fine.
I tried to do the same under Mac but it still does not work.
A quick list of the kind of tasks I did:
Init the DB Cluster - initdb -D /tmp/data0 --locale='C'
Start the cluster - postgres -D /tmp/data0
Execute a query:
psql -d template1
select * from (VALUES ('a'),('B'),('b'),('A')) as f(name) order by name;
name
------
A
B
a
b
(4 rows)
Init the DB Cluster - initdb -D /tmp/data1 --locale='en_US.UTF-8'
Start the cluster - postgres -D /tmp/data1
Execute a query:
psql -d template1
select * from (VALUES ('a'),('B'),('b'),('A')) as f(name) order by name;
name
------
a
A
b
B
(4 rows)
The fact is that works on Linux and win but under Mac I always get the
ordering with 'default' C locale (I displayed all the lc_* and all are
right set)
Did I something wrong ?
Thanks
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
4 Replies
|
|
|
PostgreSQL 8.3.0 RPMs are available for download
By Devrim GUNDUZ at Feb 21, 2008, 5:33 pm UTC
PostgreSQL New RPM Sets Versions: 8.3.0 Set labels: 8.3.0-1PGDG Release Info: The PostgreSQL RPM Building Project has released RPMs for 8.3.0 and they are available in main FTP site and its mirrors. We currently have RPMs for: - Fedora 7 - x86 - Fedora 7 - x86_64 - Fedora 8 - x86 - Fedora 8 -... More...
--------------------------------------------------------------------
PostgreSQL New RPM Sets
2008-02-04
Versions: 8.3.0
Set labels: 8.3.0-1PGDG
---------------------------------------------------------------------
---------------------------------------------------------------------
Release Info:
The PostgreSQL RPM Building Project has released RPMs for 8.3.0 and they
are available in main FTP site and its mirrors.
We currently have RPMs for:
- Fedora 7 - x86
- Fedora 7 - x86_64
- Fedora 8 - x86
- Fedora 8 - x86_64
- Red Hat Enterprise Linux / CentOS 4 - x86
- Red Hat Enterprise Linux / CentOS 4 - x86_64
- Red Hat Enterprise Linux / CentOS 5 - x86
- Red Hat Enterprise Linux / CentOS 5 - x86_64 (
More may (will) come later.
You can use our yum repository for installing PostgreSQL and more than
20 PostgreSQL related software: http://yum.pgsqlrpms.org
For complete list of changes in RPM sets, please refer to the changelogs
in the RPMs. Use
rpm -q -changelog package_name
for querying the changelog.
This is a major release, and it requires a dump/reload from the previous
major versions. Please see Release Notes to confirm procedures for
upgrading: http://www.postgresql.org/docs/8.3/static/release-8-3.html
The SRPMs are also provided. Please note that we have one SRPM for all
platforms.
We also have a howto document about RPM installation of PostgreSQL, if
you want to install them manually:
http://pgfoundry.org/docman/?group_id=1000048
Please follow the instructions before installing/upgrading.
Most of the RPMs have been signed with a single signature, and each
directory contains CURRENT_MAINTAINER file which includes the name/email
of the package builder and link to the PGP key.
If you experience problems with the RPMs or if you have feature
requests, please join
pgsqlrpms-general ( at ) pgfoundry ( dot ) org
More info about the list is found at:
http://lists.pgfoundry.org/mailman/listinfo/pgsqlrpms-general
The project page is:
http://pgfoundry.org/projects/pgsqlrpms
Please do not use these resources for issues running or using PostgreSQL
once it is installed.
Please download these files from:
http://www.postgresql.org/ftp/binary/v8.3.0/linux/
Regards,
--
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
6 Replies
|
|
|
text and bytea
By hernan gonzalez at Feb 21, 2008, 4:34 pm UTC
post this as comment in the docs, but perhaps someone who knows better can correct or clarify) =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=... More...
(After dealing a while with this, and learning a little, I though of
post this as comment in the docs, but perhaps someone who knows better
can correct or clarify)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
The issues of charset encodings and the distinction between "text" and
"bytea" can led to some confusions. Say I have a database with UTF-8
default encoding.
db=3D# \encoding
UTF8
db=3D# create table chartest ( c text); -- just one text field
CREATE TABLE
db=3D# insert into chartest (c) values ('=E1'); -- just one non ascii
string: acute a
INSERT 0 1
db=3D# select c from chartest; -- to check our terminal works ok
c
---
=E1
(1 row)
db=3D# select ascii(c) from chartest; -- to check the "real" stored value
ascii
-------
225
(1 row)
OK, now let's try to use the to_ascii() function, useful for
accent-insentive queries...
db=3D# select to_ascii(c) from chartest;
ERROR: encoding conversion from UTF8 to ASCII not supported
That's right, the docs say that to_ascii() requires a string in LATIN9
encoding (and a few others)
How do we convert a string from UTF8 to LATIN9 ? Ah, here's is the
convert_to() function. Lets try:
db=3D# select to_ascii(convert_to(c,'LATIN9')) from chartest;
ERROR: function to_ascii(bytea) does not exist
LINE 1: select to_ascii(convert_to(c,'LATIN9')) from chartest;
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
Oops. convert_to() returns a bytea (byte array), to_char() expects a
text, not quite the same thing.
Should we try an explicit cast ?
db=3D# select to_ascii(convert_to(c,'LATIN9')::text) from chartest;
ERROR: encoding conversion from UTF8 to ASCII not supported
Mmmm.... does not work, the casting assumes the default encoding (UTF8).
How do we convince postgresql to interpret a byte array (mind it:
already representing a text codification)
as a text? Looking at the string related functions, it seems that
encode() might do the trick.
We try the 'escape' option (not quite satisfactory, we are not trying
to escape anything but...)
db=3D# select to_ascii(encode(convert_to(c,'LATIN9'),'escape')) from charte=
st;
ERROR: encoding conversion from UTF8 to ASCII not supported
Damn, it seems that the encoding worked but I need to convince
to_ascii() that the text it receives is not UTF8.
Lets see:
db=3D# select to_ascii(encode(convert_to(c,'LATIN9'),'escape'),'LATIN9')
from chartest;
to_ascii
----------
a
(1 row)
Wow! It worked!
I'm not sure if this is the "correct" way of doing it. If so, it does
not seems very satisfaying to me. Besides being a little too
convoluted, the distintion of "text" vs "bytea" seems half baked... I
would have expect one of these alternatives:
1) "bytea" is just a bunch of arbitrary bytes, a "text" is the
codification of a string in a fixed encoding (may be that of the
server installation, or the database one, or even a postgresql
internally choosen). In this case, "texts" only exist in this
encoding, the conversions to-from LATIN1, etc are only to-from bytes
arrays. This is how Java works (and is fairly clean for me).
2) "bytea" is just a bunch of arbitrary bytes, a "text" is the
codification of a string in a arbitrary encoding, and the chosen
encoding is part of the text content. That is, postgresql knows (at
'runtime') the encoding of the string.
Instead, in Postgresql implementation (the gurus might correct me) a
"text" very similar to a bytea, except for some restrictions (null
bytes and invalid sequences). This IMHO results in some confusion and
clumsiness in the conversions and text functions.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Hern=E1n
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
0 Replies
|
|
|
Re: Vacuous errors in pg_dump ... | pg_restore pipeline
By Doug McNaught at Feb 21, 2008, 3:25 pm UTC
How about: CREATE DATABASE newdb TEMPLATE olddb; DATABASE). I think the 'createdb' program also has an option for choosing the template. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster... More...
On 2/20/08, Kynn Jones <kynnjo@gmail.com> wrote:
> Alternatively, is there a better way to streamline the duplication of a
> database?
How about:
CREATE DATABASE newdb TEMPLATE olddb;
(don't remember the exact syntax, but it'll be in the docs for CREATE
DATABASE). I think the 'createdb' program also has an option for
choosing the template.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
3 Replies
|
|
|
Querying the schema for column widths - what syntax do I use?
By Howard Wilkinson at Feb 21, 2008, 2:37 pm UTC
I am working on some upgrades to the MyDNS open source product. I have some expertise in MySQL but am not overly familiar with PostgreSQL and need some guidance on how to query the schema for the maximum size of data a column can hold. In MySQL I can do either: a "DESCRIBE" command or do "SELECT... More...
I am working on some upgrades to the MyDNS open source product. I have
some expertise in MySQL but am not overly familiar with PostgreSQL and
need some guidance on how to query the schema for the maximum size of
data a column can hold.
In MySQL I can do either: a "DESCRIBE" command or do "SELECT COLUMNS"
command. And then parse the result for the length in the type column of
the row returned. How would I do a similar function using PostgreSQL - I
have tried to find this in the manuals and in this mailing list but not
found any pointers to get me started.
Apologies for asking such a simple question but I am being a bit lazy as
I want to get on with releasing the MyDNS code.
Regards, Howard.
4 Replies
|
|
|
selective backup and restore
By luca.cicir...@email.it at Feb 21, 2008, 1:55 pm UTC
Hi All. Is it possible in postgreSQL 8.2.4, using pg_dump.exe, make a backup/restore conditioned to the key of some tables (respectingexisting constraints)? I'm using Windows Xp and Windows Server 2003.Thanks in advance.Luca. Email.it, the professional e-mail, gratis per te: http://www.email.it/f... More...
Hi All.
Is it possible in postgreSQL 8.2.4, using pg_dump.exe, make a backup/restore
conditioned to the key of some tables (respectingexisting constraints)?
I'm using Windows Xp and Windows Server 2003.Thanks in advance.Luca.
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Polizze per vacanze e settimane bianche. Acquista online!
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=7598&d=20080221
0 Replies
|
|
|
How to view temp tables
By pc at Feb 21, 2008, 1:51 pm UTC
My php code is creating temporary table named mytemp,but when I run a selec * from mytemp I cannot see the table.How can I see the table from postgresql command prompt? ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend More...
My php code is creating temporary table named mytemp,but when I run a
selec * from mytemp I cannot see the table.How can I see the table
from postgresql command prompt?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
3 Replies
|
|
|
unnesesary sorting after Merge Full Join
By Alexey Nalbat at Feb 21, 2008, 10:08 am UTC
Hello. I'd like to use ORDER BY in any specified order and LIMIT, OFFSET for paging query results. The query is FULL OUTER JOIN of two tables by field id. I think the results of Merge Full Join to be ordered by some "combined id". And there is no need in extra Sort if I specify ORDER BY that... More...
Hello.
I'd like to use ORDER BY in any specified order and LIMIT, OFFSET for paging query results.
The query is FULL OUTER JOIN of two tables by field id. I think the results of Merge Full Join
to be ordered by some "combined id". And there is no need in extra Sort if I specify ORDER BY
that "combined id". But unfortunately it is not so.
Here is a simple example:
-- BEGIN
create table t1 as select generate_series(1,1000000,2) as id;
create table t2 as select generate_series(1,1000000,3) as id;
create index i1 on t1 ( id );
create index i2 on t2 ( id );
analyze t1;
analyze t2;
explain analyze
select id, t1.*, t2.*
from t1 natural full join t2
order by 1 limit 10 offset 10;
drop table t1;
drop table t2;
-- END
Postgresql chooses such plan:
Limit (cost=44080.12..44080.15 rows=10 width=8) (actual time=6724.850..6724.906 rows=10 loops=1)
-> Sort (cost=44080.10..45330.10 rows=500000 width=8) (actual time=6724.806..6724.845 rows=20 loops=1)
Sort Key: (COALESCE(t1.id, t2.id))
Sort Method: top-N heapsort Memory: 25kB
-> Merge Full Join (cost=0.00..30775.28 rows=500000 width=8) (actual time=0.142..5237.289 rows=666667 loops=1)
Merge Cond: (t1.id = t2.id)
-> Index Scan using i1 on t1 (cost=0.00..15212.30 rows=500000 width=4) (actual time=0.079..1188.601 rows=500000 loops=1)
-> Index Scan using i2 on t2 (cost=0.00..10146.30 rows=333334 width=4) (actual time=0.051..793.635 rows=333334 loops=1)
The desired plan is much faster:
Limit (cost=0.62..1.23 rows=10 width=8) (actual time=0.262..0.366 rows=10 loops=1)
-> Merge Full Join (cost=0.00..30775.28 rows=500000 width=8) (actual time=0.156..0.303 rows=20 loops=1)
Merge Cond: (t1.id = t2.id)
-> Index Scan using i1 on t1 (cost=0.00..15212.30 rows=500000 width=4) (actual time=0.088..0.120 rows=15 loops=1)
-> Index Scan using i2 on t2 (cost=0.00..10146.30 rows=333334 width=4) (actual time=0.056..0.078 rows=11 loops=1)
I found comment in src/backend/optimizer/path/pathkeys.c:
* EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as
* having the outer path's path keys, because null lefthand rows may be
* inserted at random points. It must be treated as unsorted.
How can I get rid of this sorting? Or could this behavior of Merge Full Join be improved?
0 Replies
|
|
|
Trigram performance penalty on varchar?
By Mario Lopez at Feb 21, 2008, 09:56 am UTC
Hi, I am indexing a 100 million record table composed of varchar(255) as the field to be indexed. I have always seen that examples of pg_trgm are based on text type fields. Is this by any special reason?. My computer is creating the index since 5 hours ago so I guess there must be something... More...
Hi,
I am indexing a 100 million record table composed of varchar(255) as the
field to be indexed. I have always seen that examples of pg_trgm are
based on text type fields. Is this by any special reason?.
My computer is creating the index since 5 hours ago so I guess there
must be something wrong...
Thanks :)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
0 Replies
|
|
|
How to make update rapidly?
By hewei at Feb 21, 2008, 09:15 am UTC
Hi,Every body; I have a table contains 100,000 rows, and has a primary key(int). Now ,I need to execute sql command like "update .......... where id=*"(id is primary key). I expect execute 1200-1600 sqlcommands per second(1200-1600/s). In test,when the id increase by degrees in sqlcommands, then I... More...
Hi,Every body;
I have a table contains 100,000 rows, and has a primary key(int).
Now ,I need to execute sql command like "update .......... where id=*"(id
is primary key).
I expect execute 1200-1600 sqlcommands per second(1200-1600/s).
In test,when the id increase by degrees in sqlcommands, then I can reach
the speed(1600/s);
But in fact , the id in sqlcommands is out of rule, then the speed is
very slow, just 100/s.
what can i do? can you help me ?
8 Replies
|
|
|
Error in PlPython procedure
By mars_osrp at Feb 21, 2008, 05:05 am UTC
Hi All, I am using PlPython procedure to insert data in a table: plan = plpy.execute("insert into test(id,name) values(1 , 'test_py')") I am getting error: ERROR: invalid input syntax for integer: "<PLyResult object at 0x374a5a70>" In fact preparing a plan and passing a list of values for columns... More...
Hi All,
I am using PlPython procedure to insert data in a table:
plan = plpy.execute("insert into test(id,name) values(1 , 'test_py')")
I am getting error:
ERROR: invalid input syntax for integer: "<PLyResult object at 0x374a5a70>"
In fact preparing a plan and passing a list of values for columns is also
giving same error.
What is correct valid input syntax for integer in PlPython?
Mars_osrp
2 Replies
|
|
 | |
