Ok so the solution is to limit the number of connections. But it seems
that there is no good way to choose the ideal number of connections as I
don't know how much RAM will a connection use.
If a connection takes 3MB (on windows I see the process in the Process
Monitor, in Linux the RSS is more l
Bill Moran <[EMAIL PROTECTED]> writes:
> It wasn't the fact that it bloated that surprised me. It was the
> _magnitude_ of bloat that I wasn't expecting, as well as the fact that
> it was _all_ _index_ bloat.
Um, no, you had plenty of table *and* index bloat before. The problem
here is that VACU
Gene schrieb:
you're probably right, actually using LIKE with numeric works fine but
of course i will have to contend with things like truncating zeros to
the left etc... are there any advantages to using ascii encoding as
far as performance of LIKE, REGEXES, INDEXES etc versus using UTF8?
Well
Hello I'm a newbie for PostgreSQL
I want to used a BLOB data
I read Postgre's Manual that make, used at large Object or BLOB used other
library
libpq/libpq-fs.h
I search it, but I cannot find it...
Where can I download header file and dll file
-
8:00?
you're probably right, actually using LIKE with numeric works fine but
of course i will have to contend with things like truncating zeros to
the left etc... are there any advantages to using ascii encoding as
far as performance of LIKE, REGEXES, INDEXES etc versus using UTF8?
thanks, gene
On 1/1
On Jan 18, 2007, at 15:15 , Gene wrote:
My calculations for disk space based off some information i found
online are ( 8 + ( 2 bytes for every four digits) ) for numeric and (
4 + number of chars ) for a utf8 varchar datatype. Are these
calculations still valid and has anyone tried using numeri
On Thu, Jan 18, 2007 at 01:15:02AM -0500, Gene wrote:
> I'm working on a voicemail application which involves storing phone
> numbers and other types that have long sequences of digits [0-9]. I
> need to be able to do pattern matching using LIKE and map the fields
> back to java String objects. I w
On Wed, Jan 17, 2007 at 05:34:27PM -0500, Tom Lane wrote:
> Sterpu Victor <[EMAIL PROTECTED]> writes:
> > I receive this message when I do a specific operation.
> > How can I debug something like this?
>
> It sounds like DBD::Pg is sending an invalidly formed message, or in
> some other way violat
I'm working on a voicemail application which involves storing phone
numbers and other types that have long sequences of digits [0-9]. I
need to be able to do pattern matching using LIKE and map the fields
back to java String objects. I was thinking maybe it would be more
efficient to use the numer
If it's a recent PG:
select lastval()
See this:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of dfx
Sent: Monday, January 01, 2007 11:46 AM
To: pgsql-general@postgresql.org
Subject
dfx wrote:
Dear Sirs,
my question is very simple:
when I insert a row whith a serial field, a value is automatically
generated; how can I know this value, strictly of my row, without the risk
of to read the value of another subsequent insertion?
Thank you.
Domenico
Hiya,
Not sure if it w
On Mon, Jan 01, 2007 at 18:46:26 +0100,
dfx <[EMAIL PROTECTED]> wrote:
> Dear Sirs,
>
> my question is very simple:
> when I insert a row whith a serial field, a value is automatically
> generated; how can I know this value, strictly of my row, without the risk
> of to read the value of another
Hello,
I'm new to postgreSQL (not databases) and trying to find my way arouund.
Have a couple of questions:
(a) In which dir is the pg_global stored?
Is it "C:\Program Files\PostgreSQL\8.2\data\global"?
(b) Why is the pg_tablespace.spclocation column=null for pg_default &
pg_global?
(c) When
Dear Sirs,
my question is very simple:
when I insert a row whith a serial field, a value is automatically
generated; how can I know this value, strictly of my row, without the risk
of to read the value of another subsequent insertion?
Thank you.
Domenico
---(end of br
Hi,
Thanks for the reply.
I still work on it, but i guess this problem may on my application side not the
db server. I mean the appserver connection pool or jdbc configuration. I still
can not nail it. Because I have another web application with the same iBATIS
jdbc configuration to the appserv
On 1/17/07, Richard Huxton wrote:
Toni Casueps wrote:
>
> I have a set of tables with one-to-many relationships between them:
>
> T1 <-->> T2 <-->> T3 <-->> T4
>
> I need to copy some rows of these tables to another set of tables which
> have the same fields.
There's no shortcut.
BEGIN;
INSERT
Brazil has been trying to get its ISPs to block access to a certain
video. I wonder if too wide a net was cast in that effort.
http://www.slate.com/id/2157399/?nav=navoa
On 1/17/07, Jorge Godoy <[EMAIL PROTECTED]> wrote:
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Be sure you aren't blockin
I'm kind of confused how this require thing would actually work
because I tried testing it at my end at its really not working with
postgres.
I'm sure there is some fundamental mistake.
I have to do the following:
I have a perl file and need to call and use full functionaily of
this perl file i
Sterpu Victor <[EMAIL PROTECTED]> writes:
> I receive this message when I do a specific operation.
> How can I debug something like this?
It sounds like DBD::Pg is sending an invalidly formed message, or in
some other way violating the protocol. Or maybe the backend got
confused. Watching the se
Some handy links for you:
http://www.sun.com/software/solaris/howtoguides/postgresqlhowto.jsp
http://docs.sun.com/app/docs/doc/819-5578?q=+postgresql&a=expand
http://www.lethargy.org/~jesus/archives/77-Choosing-Solaris-10-over-Linux.html
On Saturday 13 January 2007 01:26, Gene wrote:
> thanks for
On Jan 17, 2007, at 11:56 AM, Tom Lane wrote:
So the above doesn't sound too unlikely. Perhaps we should recommend
vac full + reindex as standard cleanup procedure. Longer term, maybe
teach vac full to do an automatic reindex if it's moved more than X
% of
a vac full + reindex is a waste o
I receive this message when I do a specific operation.
How can I debug something like this?
I use a Centos 4.0 distribution, postgresql version 8.1.3(I also used
diffrent distibutions and other versions of postgresql).
I connect to postgresql using perl DBD::Pg, using a tcp socket.
The error a
=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <[EMAIL PROTECTED]> writes:
> pg_dump and pg_dumpall have the -o option that should tell them to
> include oids in dump. I didn't chose this option, and the dump doesn't
> include "WITH OIDS", but the tables are created with oids when restoring
> this dump.
Tom Lane wrote:
> Bill Moran <[EMAIL PROTECTED]> writes:
>> The entire database was around 28M prior to the upgrades, etc. Immediately
>> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
>> 165M. Following a database-wide reindex, it dropped to 30M.
>
> As Alvaro said, v
Well, you could always start with something like Drupal:
http://www.drupal.com
I get access denied, seems to be here, rather: drupal.org
- John Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Hi all,
pg_dump and pg_dumpall have the -o option that should tell them to
include oids in dump. I didn't chose this option, and the dump doesn't
include "WITH OIDS", but the tables are created with oids when restoring
this dump.
I'm dumping from 7.4.5 to 8.2.1.
I do have
#default_with_oids = off
On Wed, Jan 17, 2007 at 07:02:13PM +0530, Alexi Gen wrote:
> Hello,
>
> I'm new to postgreSQL (not databases) and trying to find my way arouund.
> Have a couple of questions:
>
> (a) In which dir is the pg_global stored?
> Is it "C:\Program Files\PostgreSQL\8.2\data\global"?
Yes.
> (b) Why is t
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Be sure you aren't blocking the return traffic. I did that once using a /8
As I said, just if I did that on several Brazilian networks. There's no
blocking on any of these networks.
> You might also check to see if the ip address on your end was rec
Jan Mura wrote:
Hello,
I would like to ask if there is a genral concept of creating web pages
using
a database and PHP or another scripting language.
What I mean is to store basic entities and relations between pages.
Only the
certain texts or information regarding every s
On Wed, Jan 17, 2007 at 09:41:03AM -0800, novnov wrote:
>
> There is another blog article comparing postgresql with mysql, where the
> postgres is slow thing is repeated and the benchmark tends to confirm. The
> blogger is new to postgres and does not represent the test as definative.
>
> http://
On Wed, 2007-01-17 at 11:02 +, James Im wrote:
> 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 m
In response to Ben <[EMAIL PROTECTED]>:
> Hey Bill. How do you monitor your shared buffer usage? My understanding
> was that there wasn't a good way to see what was used vs. allocated.
echo "select count(*) from pg_buffercache where reldatabase is not null;" |
$PSQL_BIN -P tuples_only -U pgsql
Jan Mura wrote:
Hello,
I would like to ask if there is a genral concept of creating web pages using
a database and PHP or another scripting language.
The term you'll want to search on is "Content Management System" or CMS.
There are plenty to choose from: Plone, Zope, Mambo
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 maximum number of connection/session.
On 1/17/07, Jan Mura <[EMAIL PROTECTED]> wrote:
I would like to ask if there is a genral concept of creating web pages
using
a database and PHP or another scripting language.
What I mean is to store basic entities and relations between pages. Only
the
certain texts or information regarding ever
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> Bill Moran wrote:
>
> > The entire database was around 28M prior to the upgrades, etc. Immediately
> > after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> > 165M. Following a database-wide reindex, it dropped to 30M.
>
There is another blog article comparing postgresql with mysql, where the
postgres is slow thing is repeated and the benchmark tends to confirm. The
blogger is new to postgres and does not represent the test as definative.
http://wskills.blogspot.com/2007/01/postgresql-vs-mysql-benchmark.html
Fir
Hello,
I would like to ask if there is a genral concept of creating web pages using
a database and PHP or another scripting language.
What I mean is to store basic entities and relations between pages. Only the
certain texts or information regarding every site will differ. But i
On 1/17/07, Patrick Earl <[EMAIL PROTECTED]> wrote:
Certainly support for the GUID field type itself is most important.
As for the generators, though they are non-essential, they are very
useful. Other platforms and libraries have standardized on uuid
generators, so I don't see why PostgreSQL c
On Wed, Jan 17, 2007 at 07:54:55 -0200,
Jorge Godoy <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III <[EMAIL PROTECTED]> writes:
>
> The don't block a host I used to access. And not on several different
> Brazilian networks from different carriers. The traffic stops at speakeasy
> from my house (A
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
am Wed, dem 17.01.2007, um 16:04:10 + mailte David Goodenough folgendes:
> I have a servlet which gets its data through a DataSource (Tomcat 5.5)
> and starts each request as a new SQL transaction and either commits
> the transaction or rolls it back at the end of each request.
>
> In one of
While using third party modules is not really a barrier for people
deep in the PostgreSQL world, here is what I would need to do to use
GUIDs today.
1. Get a set of GUID/UUID patches (there seem to be many) and
recompile the necessary code.
2. Do the same thing for Windows, assuming that the pa
Bill Moran wrote:
> The entire database was around 28M prior to the upgrades, etc. Immediately
> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> 165M. Following a database-wide reindex, it dropped to 30M.
Oh, so it was clearly the upgrade procedure that caused the bl
Bill Moran <[EMAIL PROTECTED]> writes:
> The entire database was around 28M prior to the upgrades, etc. Immediately
> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> 165M. Following a database-wide reindex, it dropped to 30M.
As Alvaro said, vacuum full doesn't shrink
David Goodenough wrote:
I thought I understood that the table would disappear
at the end of the transaction. Other than deleting it, is there something
else I need to do or have I missunderstood that into temporary does?
Dropped when the connection is closed normally, but see the ON COMMIT.
David Goodenough <[EMAIL PROTECTED]> writes:
> In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
> works just fine when I first use it, but from then on it objects saying
> that t1 already exists. When I read the documentation (8.1 as that is
> what I am using) I thought I un
[snip]
> Come to think of it, an auto-reindex option might be nice in core someday.
> TODO item?
Marry it with autovacuum + online index build, and it will be cool ;-)
BTW, having a privileged background thread doing the reindex could be a
solution to most of the objections regarding online reind
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> Today I decided to run reindex during a slow period, and was shocked to
> find the database size drop from 165M to 30M. Keep in mind that the
> 165M is after vacuum full. So, apparently, there was 135M of index bloat?
> That seems a little excess
Hi,
I'm having some problems with creating an odbc connection between postreSQL
8.2 and a multi-value database (Univision) -. I have 2 situations, one that
works, and one that doesn't - unfortunately I need to use the one that
doesn't.
Situation 1.
I have Univision loaded onto my PC runni
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> Bill Moran wrote:
> >
> > We just did a bunch of maintenance on one of our production databases that
> > involved a lot of alter tables and moving records about and the like.
> >
> > Afterwards, I did a vacuum full and analyze to get the datab
I have a servlet which gets its data through a DataSource (Tomcat 5.5)
and starts each request as a new SQL transaction and either commits
the transaction or rolls it back at the end of each request.
In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
works just fine when I firs
Bill Moran wrote:
>
> We just did a bunch of maintenance on one of our production databases that
> involved a lot of alter tables and moving records about and the like.
>
> Afterwards, I did a vacuum full and analyze to get the database back on
> track -- autovac maintains it under normal operati
I have a process that appears to hang every night. I ran the following
query and results, and it looks like an autoanalyze and query are
waiting on a lock that's being exclusively held by a transaction that is
IDLE. Any ideas? Any additional queries I should run to shed light on
the issue?
SEL
Richard Huxton writes:
> James Im wrote:
>> What am I missing to limit the memory taken by session to 1MB?
> You can't. In particular, work_mem is memory *per sort* so can be
> several times that. If you're trying to get PG to run in 64MB or
> something like that, I think you're going to be dis
Richard Huxton writes:
> The time is fixed at the start of the transaction. This lets you do
> several inserts having the same timestamp.
I think there's another problem here, which is that he's declared
"currenttime" as a timestamp without time zone, but the
CURRENT_TIMESTAMP function yields tim
We just did a bunch of maintenance on one of our production databases that
involved a lot of alter tables and moving records about and the like.
Afterwards, I did a vacuum full and analyze to get the database back on
track -- autovac maintains it under normal operations.
Today I decided to run r
On Wed, 2007-01-17 at 14:13 +, Dave Page wrote:
> Raymond O'Donnell wrote:
> > Robert Fitzpatrick wrote:
> >
> >> Thanks, yes, I verified the services has 'postgres' as the account being
> >> used under the Log On tab of the PostgreSQL service. But still, the
> >> service will not start with t
Raymond O'Donnell wrote:
> Robert Fitzpatrick wrote:
>
>> Thanks, yes, I verified the services has 'postgres' as the account being
>> used under the Log On tab of the PostgreSQL service. But still, the
>> service will not start with the administrative permissions error
>> previously posted. Any ot
Robert Fitzpatrick wrote:
Thanks, yes, I verified the services has 'postgres' as the account being
used under the Log On tab of the PostgreSQL service. But still, the
service will not start with the administrative permissions error
previously posted. Any other ideas?
Did you check that the "po
On Tue, 2007-01-16 at 20:00 -0600, Adam Rich wrote:
> Robert,
> Open "Computer Management" and find the postgres service.
> There should be an item saying "Run As". You want that to be
> a non-privledged account. Typically, postgres asks you what account
> to use when you install it. Just make
Hello,
I'm new to postgreSQL (not databases) and trying to find my way arouund.
Have a couple of questions:
(a) In which dir is the pg_global stored?
Is it "C:\Program Files\PostgreSQL\8.2\data\global"?
(b) Why is the pg_tablespace.spclocation column=null for pg_default &
pg_global?
(c) When
Toni Casueps wrote:
I have a set of tables with one-to-many relationships between them:
T1 <-->> T2 <-->> T3 <-->> T4
I need to copy some rows of these tables to another set of tables which
have the same fields.
There's no shortcut.
BEGIN;
INSERT INTO copy_t1 SELECT * FROM t1 WHERE id IN (
James Im wrote:
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.
Well, you don't explicitly, but see below.
What I have right now is that each connection (opened with jdbc) takes
about 3MB (some take a little mo
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
I have a set of tables with one-to-many relationships between them:
T1 <-->> T2 <-->> T3 <-->> T4
I need to copy some rows of these tables to another set of tables which have
the same fields. There are two rows on T1 that I want to copy, and then
those rows of T2 linked to the other ones, and
> That -- and make sure your "Secondary Logon" service is running. Also, to
> confirm, "Log On As" column in front of the pg service should mention the
> postgres
(low privileged) user.
Secondary logon service is only needed during installation. It's only used to
launch initdb.
/Magnus
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> I doubt Speakeasy is blocking anything. They are the ISP for geeks and say
> up front that they don't block ports and the like.
There are ISPs here that say the same think except for the geek part and are
known to block several ports.
> Did you use t
Hi,
Am Dienstag, 16. Jan 2007, 15:51:58 -0500 schrieb Tom Lane:
> Bertram Scharpf <[EMAIL PROTECTED]> writes:
> > Is there a deeper reason why the foreign key allows not
> > referenced non-null values
>
> The SQL spec says so. Use MATCH FULL to get the behavior you want.
Ah, I should have seen
Hello all,
I am trying to get the list of all foreign key tables
for a given primary table after getting this list I
want to filter out the name of table for which a
particular Primary table.primary key exsits in the
foreign table.foreign key column.
For that I have written a SP but the first curs
[EMAIL PROTECTED] wrote:
> For example, the machine time is 11:46:xx, and I execute the SQL insert
> through the jdbc, the record time will be changed to 10:52:xx. I mean the
> time value is always slower than the machine time but not for a fixed time
> period.
>
> My java appserver is Sun Jav
> I want to store count(*) of a table in a variable ,
> for that I have declared a variable and wrote a
> statment but it is giving me error.
> can anybody please help me..
>
> CREATE OR REPLACE FUNCTION foreign_keys_tables(OUT
> par_result charecter varying, IN par_tablename
> character varying
72 matches
Mail list logo
