I can run clusterdb -a from the command line to cluster all databases.
After clustering, its recommended to run ANALYZE. But there doesn't
seem to be any way to do this from the command line, and even in SQL,
there doesn't seem to be any way to do this for all databases.
1. What's the recommende
I see how CLUSTER can speed up a range query (eg WHERE val < 30),
because it groups those records in contiguous pages.
What about where I'm only pulling one record back? Eg WHERE user_id =
100. Is there any benefit to a CLUSTER in that case? Is there
anything lost if I CLUSTER on a different ind
Besides the one time spent CLUSTERing, do I loose anything by doing it
for every table? Does a CLUSTER slow anything down?
It would seem to me that a) a CLUSTER should never have worse
performance than a random order b) may have better performance and c)
has the benefits of a VACUUM and REINDEX.
On 5/15/12, Steve Crawford wrote:
> On 05/15/2012 02:02 PM, Robert James wrote:
>> Besides the one time spent CLUSTERing, do I loose anything by doing it
>> for every table? Does a CLUSTER slow anything down?
> Cluster should have better performance but it depends on the
I downloaded the hunspell en_GB from
http://wiki.services.openoffice.org/wiki/Dictionaries#English_.28AU.2CCA.2CGB.2CNZ.2CUS.2CZA.29
and when building the Ispell dictionary I got the following error
ERROR: invalid byte sequence for encoding "UTF8": 0xe9640a
HINT: This error can also happen if
It's postgresql-8.3.5-2 (windows)
On Tue, Feb 3, 2009 at 4:37 PM, Tom Lane wrote:
> James Dooley writes:
> > and when building the Ispell dictionary I got the following error
>
> > ERROR: invalid byte sequence for encoding "UTF8": 0xe9640a
>
> What P
In
http://www.postgresql.org/docs/8.3/interactive/textsearch-dictionaries.html
it mention this feature for support on splitting compound words.
Adding the line
compoundwords controlles z
to the affix file returns an error
ERROR: wrong affix file format for flag
ositions as shown in the example because I rank
according to date/price etc..
Thank you, James
ars')
returns 'car'
Could you please point me in the right direction ?
James
On Fri, Feb 6, 2009 at 4:01 PM, Richard Huxton wrote:
> James - don't repeat the whole of the previous message in your replies,
> trim it to the relevant part. The message is already archived on the list.
>
> James Dooley wrote:
> > Oleg, but I am only interested in wheth
BTW, Oleg I don't need colasque since those values can't be null.
On Fri, Feb 6, 2009 at 4:11 PM, Oleg Bartunov wrote:
> James,
>
> syntax is documented on
>
> http://www.postgresql.org/docs/8.3/static/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH
> an
Richard, I was refereing to the plainto_tsquery
Oleg, I only need colasque if any of those columns can be null, which is not
the case here. Correct?
car')
or should it be as Richard just mentioned
... AND to_tsvector(title || '' || description || '' || name) @@
plainto_tsquery('car')
or some other way ?
On Fri, Feb 6, 2009 at 3:30 PM, Richard Huxton wrote:
> James Dooley wrote:
> > Hi again,
&g
Hi,
Since PostgreSQL is by default case sensitive, I would like to know if it is
possible to do a search or somehow set the column in a relation to be case
insensitive (on search at least).
I would however like to store it case sensitive so I can display it as it
was stored.
Ps. I am not interes
We're running PG 8.1 on CentOS 5.0. When postgres starts the following
processes apparently required by the DB itself get created:
postgres 23784 1 0 13:55 ?00:00:00 /usr/bin/postmaster -p 5432 -D
/var/lib/pgsql/data
postgres 23786 23784 0 13:55 ?00:00:00 postgres: logger
It's a normal pg connection server created by our own code. Nothing native to
pg startup.
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Eshelman, James
Sent: Monday, March 30, 2009 3:29 PM
To: pgsql-general@postgresql.org
Subject: [GE
SET idxfti=to_tsvector('default', field);
it runs for a while then aborts with the following message
ERROR: value is too big
Does anyone know what the problem might be here? Does tsearch2 have
configurable (or compilable) settings that could get around this?
Many Thanks,
James
--
-designer.
Several of the developers hang out in #gnuenterprise on irc.freenode.net if
you'd like more info.
Take Care,
James
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ith a separate oid for object / relational mapping.
James Robinson
Socialserve.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Apr 12, 2005, at 4:48 PM, Tom Lane wrote:
James Robinson <[EMAIL PROTECTED]> writes:
insert into simple_table values (null, '(43)'); -- GRR works!!! It'll
let any smallint in. What happened to the constraint?
The composite-type input routine doesn't check any constra
ving only two lines of SQL to build
to educate the database about each one would be a real winner.
Not to sound ungrateful -- the scalar type tricks can work, but with
more SQL-level effort.
James Robinson
Socialserve.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
-- okay -- eat it now
for testrow in execute query loop
return next testrow;
end loop;
return;
END;
$$ language plpgsql;
social=# select * from dynamic_test('{2,3}');
NOTICE: query: "select * from test where id in (2, 3)"
id
hat they
just aren't true members of the relational model, but, aside from
constraint inheritance, what is known to be lacking, aside from the
plethora of parenthesis they inflict?
James Robinson
Socialserve.com
---(end of broadcast)---
rebuilding any indices on the old type columns, then
dropping the old type. Inconvenient, not insurmountable.
That said -- anyone stepping up to claiming using 'em? Are these things
seen as against the data normalization grain?
James Robinson
Socialserve.com
James Croft wrote:
The problems seems to be with the table_name arg being a variable and
not a literal but can't see how to fix this.
If anyone knows what's going on here or has any pointers it would be
appreciated.
Thanks,
James
Sorry,
I'm running PgSQL 7.4.7 on RedHat Ent
ALTER COLUMN snapshottime SET DEFAULT
CURRENT_TIMESTAMP;
END IF;
The problems seems to be with the table_name arg being a variable and
not a literal but can't see how to fix this.
If anyone knows what's going on here or has any pointers it would be
appreciated.
Thanks,
James
-
Stephan Szabo wrote:
On Thu, 19 May 2005, James Croft wrote:
Hi all,
I'm trying to create a trigger function for a few tables that will store
old versions of rows prior to any update on them. Part of the function
needs to creates other tables (the table to store these snapshots in).
When
g_temp_N schema belongs to my session.
Is this possible?
TIA,
James
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
round
the corner and if it has any features that would benefit the app.
Thanks,
James
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Scott Marlowe wrote:
I'd go for 8.0. It's quite stable now, and 8.1 will take as long as it
needs to take to come out, which could be anything, but will likely be
no earlier than the end of the year.
I believe there was talk about an 8.1 coming out to replace the caching
algorithm, but I believ
Marc G. Fournier wrote:
I'd almost think taht this shuld be much more prominently put in a
section on the main page of the web site, actually ... make it nice and
visible instead of buried on a sub page ...
I agree it would be good to have a link on the main page. Possibly near
"What's ne
or me?
Thanks
Eoghan
---(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
-
James Cradock, [EMAIL PROTECTED]
---
(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
-
James Cradock, [EMAIL PROTECTED]
me3 Technology Consultants, LLC
24
or. I get the error also if I try creating the GiST index on the
table with the 33,000,000 rows.
Are there limitations with the GiST index? Has anyone seen this?
Thanks.
Jim
-
James Cradock, [EMAIL PROTECTED]
-
James Cradock, [EMAIL PROTECTED]
me3 Technology Consultants, LLC
24 P
Thanks.
I'm using PostgreSQL 8.0.2.
On Sep 1, 2005, at 6:35 AM, Richard Huxton wrote:
James Cradock wrote:
Hello.
I'm getting an Out Of Memory error when I try to create a GiST index
on a geometry column (PostGIS) with approximately 33,000,000 rows. I
can truncate the data in
database the table (or index) was
dropped from may free up disk space too.
Jim
-
James Cradock, [EMAIL PROTECTED]
me3 Technology Consultants, LLC
24 Preble Street, 2nd Floor
Portland, ME 04101
207-772-3217 (office)
207-838-8678 (mobile)
www.me3.com
---(end of broadcast
given a 7.4.2 slave and an 8.0.x master?
-JimC
--
James H. Cloos, Jr. <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 6: explain analyze is your friend
s to set the format output, and a sql statement
redirected to our line printer.
I haven't used Oracle since the mid 90s so I don't have a working example but
a description of some of the commands can be found here
http://www.siue.edu/~dbock/cmis564/otext3.htm
Take
> >
> > I haven't used Oracle since the mid 90s so I don't have a working example
> > but a description of some of the commands can be found here
> >
> > http://www.siue.edu/~dbock/cmis564/otext3.htm
>
> Given the choice, I'd *MUCH* rather have a good, easy-to-use CLI than a
> reporting tool. I'm
> (There are many, many others that don't have a GUI designer
> available. There's at least one on pgfoundry that explicitly supports
> postgresql. They usually seem to take XML as an input template and
> render to HTML or PDF.)
Our reports tool does this as well and works with most major datab
tamps are technically ambiguous as to whether the specify
real UTC-based time or POSIX time (Currently there is a
23-second difference between the two.)
--
James Cloos <[EMAIL PROTECTED]> OpenPGP: 0xED7DAEA6
---(end of broadcast)--
using geo-spatial coordinates. Software like MapServer (web) and Qgis (desktop) use PostGIS to provide data in map applications. The Refractions site already noted is a good start. The MapServer site is another: http://mapserver.gis.umn.edu/gallery Jim -James Cradock, [EMAIL PROTECTED]
On Nov 2, 2006, at 10:41 AM, Sandeep Kumar Jakkaraju wrote:After all it is open source ...so how can one trust it fully If you have a question about how something is working, and you find the end-user documentation to be lacking, you can look at the software's source. -James Cr
-
1
(1 row)
The incoming text indicates there are extraneous characters making
there way in somehow.
James
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
else
insert into housing.terse_listing (id,
last_modified) values (NEW.id, now());
end if;
return null;
END;
|
James Robinson
Socialserve.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Many thanks!
\a
before \df+ does the trick.
James
On Dec 21, 2006, at 10:31 AM, Martijn van Oosterhout wrote:
Well, in unaligned mode it's still the old way. There was some
discussion at the time about whether tabs should be converted to
spaces
on output, but I don't thin
up?
Database is 8.2.0 on x86_64-unknown-linux-gnu.
There is nothing else running on this database server (other than
standard linux background programs). PS ax did not show anything else
running. No locks other than the occasional lock by the INSERT query.
I have done a FULL vacuum on thi
Joshua D. Drake wrote:
Also as you are running 8.2 you can use multi valued inserts...
INSERT INTO data_archive values () () ()
Would this speed things up? Or is that just another way to do it?
Thanks,
James
---(end of broadcast)---
TIP 6
Joshua D. Drake wrote:
You need to vacuum during the inserts :)
Joshua D. Drake
I ran the vacuum during the INSERT and it seemed to help a little, but
its still relatively slow compared to the first 2 million records.
Any other ideas?
Thanks,
James
---(end of
hat did it, thank you!
--James
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
a way in a psql
script to try to execute the INSERT statement and if theres a problem to
dump it to a log file and go on with the others?
Thanks in advance,
James
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
Yesh wrote:
Hi,
I need to know how to increment a primary key field automatically in run
time.
If you use the "serial" data type, the database does this for you
automatically and you don't have to worry about it.
Is this the data type your using?
--
James Neff
Techno
f you
need to control security than you have to prevent physical access to the
file (which means no file system storage) and pull the image from the
database through the application.
My two cents,
James
John McCawley wrote:
Don't store your images in the database. Store them on the fi
quot; the pg_dump process? Or is there a
more graceful way to stop the pg_dump process.
Thanks in advance,
James
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joi
Hi,
I'm using Postgresql 8.1 on windows2000 and I have a hard time
understanding how to limit the memory of the sessions to 1 MB.
What I have right now is that each connection (opened with jdbc) takes
about 3MB (some take a little more, some a little less). I think that
this is a waste of memory
Richard Huxton wrote:
> Is there a particular problem you're trying to solve?
yes I'm trying to know how many connections can open to the database
without running out of memory. Ideally I would like to optimize stuff
so that I can open the maximum number of connection/session.
In total I can gi
ard Huxton wrote:
James Im wrote:
Richard Huxton wrote:
> Is there a particular problem you're trying to solve?
yes I'm trying to know how many connections can open to the database
without running out of memory. Ideally I would like to optimize stuff
so that I can open the maxim
louis gonzales wrote:
Is it better to have 1 monolithic table and have to search it, or
small individual tables but many of them?
Ron Johnson wrote:
Yes, 1 large table. This is what RDMS were designed for.
---(end of broadcast)---
TIP 1: if p
I see in all the docs to run VACUUM ANALYZE periodically. My host told me
that in Postgres 8.2 this is not needed as it is done automatically.
Is that true? How can I see the results of the automatic vacuum analyze? Or
configure them?
Mark wrote:
I would like to use postgresql with knopixx, Sounds like a simple
idea :-) and I would like to get full version of postgresql stored on
flash drive.
I remeber I've seen postgresql tar files before, but do not recall
the location - can anybody point?
Also, how big (in MB) postgresql
I'd like to run pg_dump on a database that I have full
read/write/create/drop access to, but am not running as psql.
I'm getting this error:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: permission denied for relation
pg_ts_dict
pg_dump: The command was: LOCK TABLE public
I see in all the docs to run VACUUM ANALYZE periodically. My host told me
that in Postgres 8.2 this is not needed as it is done automatically.
Is that true? How can I see the results of the automatic vacuum analyze? Or
configure them?
The Postgres docs are great. Unfortunatelty, at 1600 pages, my printer is a
little weary. Are bound & printed editions available? If not, has anyone
had any good experience printing them via any of the online services? (The
ones I contacted said it was too big for them to print...)
Also: woul
Excellent!
Two questions:
1. Might it be better to include the index and TOC (in both volumes), and do
something like this:
http://www.postgresql.org/files/documentation/pdf/8.2/postgresql-8.2.1-US.pdf
Vol I: 1-621, 1674-end
Vol II: 1-38, 622-1395, 1672-end
(Dropped some other things, I think...)
Is there anyway to tell if the records in a union came from the 1st query or
the second?
Can I modify the individual queries to let me know this?
Is there anyway to install contrib (fuzzystr) so it's available to all
databases (even ones created previously)?
thanks
an include a list of what exactly is included in each
volume - (Example: Volume II is TOC, Reference, and Index, pages 1-35,
503-912, and 1651-end of http://postgresql.org/...original.pdf)
http://www.lulu.com/iharding
Now in 3 Volumes!
On 5/17/07, Robert James <[EMAIL PROTECTED]> wrote
1. Is there anyway to give a role privs on a certain table, delete that
table, recreate it, and have those privs persist?
2. Is there anyway to give a role privs to all tables in a database, without
having to name each one individually? Can I use this to apply to tables
created later on?
Anyone have an implementation of the Jaro Winkler fuzzy string matching algo
for Postgres?
* http://en.wikipedia.org/wiki/Jaro-Winkler
* http://forums.oracle.com/forums/thread.jspa?threadID=385950&tstart=0
It's rumored to (sometimes?) work better than the Levenshtein algo included
in contrib/fuz
I use a set of DDL scripts to automatically create the database for an app.
I need to create certain functions (from contrib), if they're not there
already. If they are there, I don't want to DROP or REPLACE them, since
they may be used in certain indexes and triggers.
Is there a way to only CR
rence manual page on the COPY command.
James Robinson
Socialserve.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
We're happy to answer questions on our mailing list. Or in IRC at
#gnuenterprise on irc.freenode.net
Take Care,
James
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
SIG. pyscopg though seems to give us the least trouble.
Though if IIRC it does round timestamps which can burn a person at times :)
I don't have a test case to verify if my memory is correct about this though.
Take Care,
James
---(end of broadcast)--
re supported to varying degrees. You can switch out databases by
editing that one file. Providers are not limited to just databases either,
our application server is just another provider in our system. Differences
between providers are handled transparently to the developer.
Take Care,
Jame
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello,
~ I created a schema, database, tables, users and a user group.
create user sma_user password 'x';
create group sma_user_group;
alter group sma_user_group add user sma_user;
create table sma_apps.ordr_dat ( ... );
grant select,delete,insert
Try pg_type. typname should give you what you're looking for.
On Dec 30, 2005, at 1:57 PM, Jonel Rienton wrote:
Hi guys,
Does Postgres store all the possible column datatypes somewhere in its
system tables? Like int8, int4, character varying, etc. I'm trying
to write
another GUI client tha
Have you vacuum'd and/or analyzed D2?
http://www.postgresql.org/docs/8.1/static/maintenance.html
http://www.postgresql.org/docs/8.1/static/sql-analyze.html
James Robinson
Socialserve.com
---(end of broadcast)---
TIP 3:
modification.
If not, has such a thing been considered? What would be the obstacles to
developing such a proxy?
Thanks
James
(Not subscribed to the list at the moment - I'll check the archives but
please cc me on a response)
---(end of broadcast)---
mind that other sessions contain temp tables using the
same names?
Many thanks,
James
--
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify
| 16317551
session_data | 16118447 | 16317563
session_data | 15035529 | 16317579
(14 rows)
How can I determine if one of the above relations is a temporary
table in the current session (one of them, the first in ns 2200, is a
normal permanent table)?
Thanks,
James
--
This email and any
happens? Are all the rows read into memory on the client before
returning the result? Or are rows only fetched from the server as
required?
Thanks
James
---(end of broadcast)---
TIP 6: explain analyze is your friend
answer your questions:
>
> http://archives.postgresql.org/pgsql-interfaces/2005-11/msg00010.php
Found that eventually. I'd seen the thread earlier but assumed that the
word 'incremental' in the subject was to do with auto-incrementing
fields. An interesting read.
Thanks
James
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
> On Tue, Jan 31, 2006 at 10:23:54PM +1100, James Harper wrote:
> > For the libpq interface:
> >
> > I need to be able to know if a column in a result from a query is
> > nullable or not. From reading the documentation it seems that I can
> > obtain the followin
quot; THEN SELECT * FROM fnord
END IF;", "xyzzy");
?
Thanks
James
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
here's a system option
to turn it on???
Thanks
James
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
> On Mon, Feb 06, 2006 at 09:42:22PM +1100, James Harper wrote:
> > I think the answer is no, but can anyone tell me if postgresql
supports
> > the older (pre sql-92?) style outer join syntax, eg:
>
> I think you're referring to some kind of non-standard
> invente
stion it was
suggested that if you want to do this then you should use different
schema's within the same database, instead of different databases. Maybe
it's in the FAQ too?
Given that your trigger is written in C, couldn't you use libpq to
connect to and updat
he form db_owner, eg mydb_james and mydb_fred, to emulate this? There
would also be the schema of the dbo which mydb_dbo, which is also
searched given an unqualified table name. If I'm logged in as james, my
search path would be mydb_james then mydb_dbo.
?
Thanks
James
---
>
> Martijn van Oosterhout writes:
> > IIRC, the idea of a schema came from Oracle where the schema is the
> > username. If you create a schema with the same name as the user what
> > you describe above works. ie mydb.james.myview is in the james
schema
> > in th
E CAST' command, I can't just create
a cast that only kicks in on empty strings whilst leaving casts of other
strings as is...
Thanks
James
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
> am 10.02.2006, um 20:22:57 +1100 mailte James Harper folgendes:
> > Is there anything I can do to make postgres allow an implicit cast
of an
> > empty string to a timestamp, so that a badly behaved application can
do:
>
> > INSERT INTO SomeTable (timestampfield) VA
7;' AS timestamp);
SELECT CAST(char '' AS timestamp);
SELECT CAST(varchar '' AS timestamp);
But this doesn't yet:
SELECT CAST('' AS timestamp);
I can probably get it working, but is it really a good idea to be
fiddling with the pg_cast table like
Not my application :(
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Saturday, 11 February 2006 01:59
> To: James Harper
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] implicit cast of empty string to timestamp
>
> "Ja
It's not normal. What's the installation? OS, applications connecting to the server, etc. On Apr 7, 2006, at 8:20 AM, surabhi.ahuja wrote:hi, is it possible for postmaster to go doen on its own? all what the logs say is FATAL: terminating connection dur to administrator's command. thanks, regar
On 4/30/06, Tony Lausin <[EMAIL PROTECTED]> wrote:
Hello all,
I'm working on a CMS which requires an open source database capable of
handling hundreds of thousands of users simultaneously, with a high
rate of database writes, and without buckling. We're talking somewhere
between nerve.com/catch2
xport the Word Doc From the BLOB colum and dumpit into a PDF format (I guess I am asking if someone has seen or
written a PDF generator script/storedProc for Postgres)?
If someone could help me out, it would be greatly appreciated.cheers,James
at tech which was supposed to make things
easy for you. CMP is a very leaky overcomplicated abstraction.
James Robinson
Socialserve.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscr
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Mon, 15 Dec 2003 09:13:49 +, Tony wrote:
>excel it's previous incantation.
>
The spelling is "its", not "it's".
"Its" is a possessive pronoun. "It's" is a contraction for "it is".
- --
jimoe at sohnen-moe dot com
pgp/gpg public key: ht
John Gibson wrote:
Assuming similar memory and disk sub-systems, I am considering a Quad
Xeon system vs. a Dual Itanium for PostgreSQL. I believe that the
PostgreSQL code is written for 32 bit and not optimized for the 64 bit
Itanium cpu. That makes me think that the Xeon system would be a bet
You can make it interactive if you are working from a unix box.
[EMAIL PROTECTED]:~$ more zip.sql
\echo -n 'Zip to search for: '
\set value `read input; echo $input`
select * from zipcode where zip = :value
Not sure how/if you could do this on a windows box.
Take Care,
James
On T
301 - 400 of 607 matches
Mail list logo
