In trying to investigate a possible memory issue that affects only one
of our servers, I have been logging the process list for postgres
related items 4 times a day for the past few days.
This server uses postgres 7.4.6 + slon 1.1.0 on Debian i686 (Linux
server2 2.6.8.1-4-686-smp) and is a slo
Hi
Could somebody please tell me if CREATE TYPE is equivalent to CREATE
DOMAIN? If not is there a work around
---
Regards
John Dean,
co-author of Rekall,
the only alternative
to MS Access
---(end of broadcast)---
TIP 6: explain analyze is y
On Wed, Dec 21, 2005 at 11:30:15PM -0800, Benjamin Arai wrote:
> I want to be able to do large updates on an existing backed up database
> with fsync=off but at the end of the updates how do I ensure that the
> data gets synced?
Do you know if that actually makes it much faster? Maybe you're bet
John Dean wrote:
Hi
Could somebody please tell me if CREATE TYPE is equivalent to CREATE
DOMAIN? If not is there a work around
What do you mean by "equivalent"? You wouldn't use them in the same way,
and I'm not sure what a work-around would consist of.
What are you trying to do?
--
Ric
Hi.
Is there a limitation of the length of a char or varchar(x) column
for indexing?
cug
smime.p7s
Description: S/MIME cryptographic signature
On Thu, Dec 22, 2005 at 11:24:12AM +0100, Guido Neitzer wrote:
> Hi.
>
> Is there a limitation of the length of a char or varchar(x) column
> for indexing?
For btrees at least, yes. Around a third of a page or about 2713 bytes
by default. For bigger things you probably want tsearch anyway.
Hav
On 22.12.2005, at 11:27 Uhr, Martijn van Oosterhout wrote:
For btrees at least, yes. Around a third of a page or about 2713 bytes
by default. For bigger things you probably want tsearch anyway.
Thanks.
cug
smime.p7s
Description: S/MIME cryptographic signature
Hi All.
I have a question about the Maximum number of columns in a table ?
In FAQ for PostgreSQL,I can find this description :
Maximum number of columns in a table?
250-1600 depending on column types
But , I want to know what type is 1600 limit , and what type is 250
limit . it is imp
zhaoxin wrote:
> Hi All.
>
> I have a question about the Maximum number of columns in a table ?
>
> In FAQ for PostgreSQL,I can find this description :
> Maximum number of columns in a table?
> 250-1600 depending on column types
> But , I want to know what type is 1600 limit , and wha
I have to face this trouble , it is not I want , but a historical problem .
so , can you tell me ?
Richard Huxton wrote:
> zhaoxin wrote:
>
>>Hi All.
>>
>>I have a question about the Maximum number of columns in a table ?
>>
>>In FAQ for PostgreSQL,I can find this description :
>> Maximum numbe
zhaoxin schrieb:
> I have to face this trouble , it is not I want , but a historical problem .
> so , can you tell me ?
Try it out. I'd change the future of that history though.
You can expect much better performany on virtually any
RDBMS with appropriate schema.
++Tino
PS: try to send text-onl
zhaoxin wrote:
> I have to face this trouble , it is not I want , but a historical problem .
> so , can you tell me ?
Sure, but you'll need to say what column-types you have.
Below is a small script to generate a table with lots of columns.
#!/bin/perl -w
use strict;
my $tbl = 'test_text';
my $
Hello.
Could you, please, help me to optimize my
connection string (MS Access 2003, PostgreSQL 8.1.1. and
psqlodbc-08_01_0102)?
'
PG_ODBC_PARAMETER
ACCESS_PARAMETER'
*'
READONLY
A0'
PROTOCOL
[EMAIL PROTECTED] (S McLurkin) writes:
> Is there some place where I can find information on all the contrib
> extenstions?
Download the sources, and examine each directory for its
documentation. There is commonly a README file...
--
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/inf
On Wed, Dec 21, 2005 at 04:54:08PM -0500, Greg Stark wrote:
> MSSQL presumably has the entire table cached in RAM and postgres doesn't. Even
> if MSSQL can scan just the index (which postgres can't do) I would only expect
> a factor of 2-4x. Hm. Unless perhaps this table is extremely wide? How larg
Am 2005-12-16 21:52:07, schrieb Andrus:
> > Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit
> > immature, but it might do what you want. The dreaded MS Access
> > can do what you describe in about 4 minutes...
>
> Postgres lacks easy GUI frontend with report generation c
On Thu, Dec 22, 2005 at 08:52:08AM -0600, Jim C. Nasby wrote:
> Back when I was using other databases more often, it wasn't uncommon to
> see a 10x speed improvement on count(*) from using an index. This is an
> area where PostgreSQL is seriously behind other databases. Of course
> having vastly su
On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote:
> Actually, ISTM the trend is going the other way. MySQL has instant
> select count(*), as long as you're only using ISAM. Recent versions of
No comment.
> MSSQL use an MVCC type system and it also scans the whole table. Orac
Hi,
Can anyone point me toward an SQL function (whether built-in or an
add-on) that will allow me to sort the contents of an array datatype in
an SQL query?
Something like this:
select sort(my_array_field) from my_table;
Thanks!
Pete
On Wed, Dec 21, 2005 at 09:46:59AM +, Richard Huxton wrote:
> max chin wrote:
> >1.) What I knew is when too many users access a database at the same
> >time, it will slow down database server process. My question is how
> >to make database server process more faster even if a lot of users
> >
On Wed, Dec 21, 2005 at 10:49:29PM +0100, Klein Bal?zs wrote:
> I thought that if I used a view to retrieve data its content might be cached
> so it would make the query faster.
No. A view is essentially exactly the same as inserting the view
definition into the query that's using it. IE:
CREATE
On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote:
> SELECT id
> FROM mq
> WHERE now - start_date > time_to_live;
The problem is you can't use an index on this, because you'd need to
index on (now() - start_date), which obviously wouldn't work. Instead,
re-write the WHERE as:
WHERE start_da
On Thu, Dec 22, 2005 at 09:36:52AM +, Richard Huxton wrote:
> John Dean wrote:
> >Hi
> >
> >Could somebody please tell me if CREATE TYPE is equivalent to CREATE
> >DOMAIN? If not is there a work around
>
> What do you mean by "equivalent"? You wouldn't use them in the same way,
> and I'm not
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I didn't think the method of adding the imperfect known_visible bit to
> the indexes had that much overhead, but it's been a while since those
> discussions took place. I do recall some issue being raised that will be
> very difficult to solve (though
On Thu, 2005-12-22 at 09:33, Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote:
> > Actually, ISTM the trend is going the other way. MySQL has instant
> > select count(*), as long as you're only using ISAM. Recent versions of
>
> No comment.
>
> > MSSQL
On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote:
> Hi All.
>
> I have a question about the Maximum number of columns in a table ?
>
> In FAQ for PostgreSQL,I can find this description :
> Maximum number of columns in a table?
> 250-1600 depending on column types
> But , I want
On Thu, Dec 22, 2005 at 03:52:36PM +0100, Michelle Konzack wrote:
> Am 2005-12-16 21:52:07, schrieb Andrus:
> > > Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit
> > > immature, but it might do what you want. The dreaded MS Access
> > > can do what you describe in about 4
Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 09:36:52AM +, Richard Huxton wrote:
> > John Dean wrote:
> > >Hi
> > >
> > >Could somebody please tell me if CREATE TYPE is equivalent to CREATE
> > >DOMAIN? If not is there a work around
> >
> > What do you mean by "equivalent"? You wouldn't use
On Thu, 2005-12-22 at 10:10, Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote:
> > Hi All.
> >
> > I have a question about the Maximum number of columns in a table ?
> >
> > In FAQ for PostgreSQL,I can find this description :
> > Maximum number of columns in a table
Jim C. Nasby wrote:
> Some (most?) database's idea of 'creating a type' is actually what we
> consider creating a domain,
Which databases do such a thing?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 5:
On Wednesday December 21 2005 8:24 pm, Tom Lane wrote:
> "Ed L." <[EMAIL PROTECTED]> writes:
> > I have a cluster configured for ~800MB of shared memory
> > cache (shared_buffers = 10), but ipcs shows TWO shared
> > memory segments of ~800MB belonging to that postmaster.
> > What kind of a pro
>
> I wouldn't mind a "with visibility" switch for indexes that you could
> throw when creating them for this purpose. But burdening all indexes
> with this overhead when most wouldn't need it is not, IMHO, a good idea.
>
that would add complexity to the index code for... just one case?
what abo
Scott Marlowe wrote:
> On Thu, 2005-12-22 at 10:10, Jim C. Nasby wrote:
> > On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote:
> > > Hi All.
> > >
> > > I have a question about the Maximum number of columns in a table ?
> > >
> > > In FAQ for PostgreSQL,I can find this description :
> > >
Martijn van Oosterhout writes:
> On Wed, Dec 21, 2005 at 11:30:15PM -0800, Benjamin Arai wrote:
>> Somebody said running "sync ; sync; sync" from the console. This seems
> The reason is partly historical. On some OSes running sync only starts
> the process but returns immediatly. However, there
I am learning how to create stored procedures using
pgAdmin and Postgres. I have a couple of questions.
1) For all of my tables (admittedly not many, yet), I
created columns with the type of integer to serve as indices. I now
learned (well, late last might) in your documentation that Post
On 12/22/05, Ted Byers <[EMAIL PROTECTED]> wrote:
> I am learning how to create stored procedures using pgAdmin and Postgres. I
> have a couple of questions.
>
> 1) For all of my tables (admittedly not many, yet), I created columns with
> the type of integer to serve as indices.
columns doesn't s
merge join (cost=0.00..348650.65 rows=901849 width=12)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8)
index scan using {blah index on blah} (cost=0.00..17229.93
rows=902085 width=8)
This query takes about 3 minu
On Thu, Dec 22, 2005 at 08:38:46AM -0700, Pete Deffendol wrote:
> Can anyone point me toward an SQL function (whether built-in or an add-on)
> that will allow me to sort the contents of an array datatype in an SQL
> query?
For integer arrays see contrib/intarray.
SELECT sort('{5,2,3,1,9,7}'::int[
I'm trying to add additional functionality to the contrib/pgcrypto
branch (at least for my own use, although ideally, I'd like to
make whatever additions good enough as to be accepted as part of
the PG distribution)
Anyway, I wanted to add hash functions (SHA-1 is already there,
so I'd like to
David Rysdam <[EMAIL PROTECTED]> writes:
> merge join (cost=0.00..348650.65 rows=901849 width=12)
> merge cond {blah}
> join filter {blah}
> index scan using {blah index on blah} (cost=0.00..289740.65
> rows=11259514 width=8)
> index scan using {blah index on blah} (cost=0.00..17229.
Carlos Moreno <[EMAIL PROTECTED]> writes:
> The problem is, when I execute the SQL statement:
> create or replace function sha1 ;
> for the second time (i.e., after making modifications and
> recompiling), the *backend* crashes
Getting a stack trace from that core dump might be illuminating
On 12/22/05, John Sidney-Woollett <[EMAIL PROTECTED]> wrote:
> In trying to investigate a possible memory issue that affects only one
> of our servers, I have been logging the process list for postgres
> related items 4 times a day for the past few days.
>
> This server uses postgres 7.4.6 + slon 1
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
merge join (cost=0.00..348650.65 rows=901849 width=12)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8)
index scan using {blah index on blah} (cost=0.00.
On Tue, Dec 20, 2005 at 10:21:54PM +0100, Alexander Scholz wrote:
> Hi,
>
> is there a newbie's FAQ / book / link for "howto optimize databases with
> PostgreSQL"?
>
> Background: Customer has the Windows* (sorry ) Postgres 8.1.0
> standard installation "out of the box". A table has 2.5 mio reco
On Thu, Dec 22, 2005 at 08:38:46AM -0700, Pete Deffendol wrote:
> Hi,
>
> Can anyone point me toward an SQL function (whether built-in or an
> add-on) that will allow me to sort the contents of an array datatype
> in an SQL query?
>
> Something like this:
>
> select sort(my_array_field) from my_
On 12/22/05, Carlos Moreno <[EMAIL PROTECTED]> wrote:
> The problem is, when I execute the SQL statement:
>
> create or replace function sha1 ;
>
> for the second time (i.e., after making modifications and
> recompiling), the *backend* crashes -- it then restarts
> automatically, and then I r
Hi Jaime,
Thanks.
I'd suggest the manual be edited by an educator, since it is a little dense
for someone coming to it for the first time. Once I read your reply to me,
and reread the manual, I understood. But on first reading, it is a little
too dense and short on examples.
Regarding ser
>
> Assuming I have set up a sequence called 'seq', and set the default value of
> id in foo to be nextval('seq'), then the following is getting close to what
> I need (there seems to be only one thing left - what do I replace the
> question mark with in order to get the id value from the initial s
Thanks for your response.
None of the data rows are wide (as far as I can remember). We don't have
any blob data, and any text fields only contain several hundred bytes at
most (and even those would be rare).
Just stopping and starting the slon process on the slave node doesn't
seem to help
Marko Kreen wrote:
On 12/22/05, Carlos Moreno <[EMAIL PROTECTED]> wrote:
The problem is, when I execute the SQL statement:
create or replace function sha1 ;
for the second time (i.e., after making modifications and
recompiling), the *backend* crashes -- it then restarts
automatically,
thanks for the help
>What you could do is partition the table so that critical information is
>stored in a smaller table while everything else goes to a larger table.
I was thinking the other way round - maybe I can split the large table by
creating a materialized view. But than I read that it is
=?iso-8859-1?Q?Klein_Bal=E1zs?= <[EMAIL PROTECTED]> writes:
> But later in the same blog it seems to indicate that there is a choice to
> either use or not use the query cache of pg.
Hm? There is no query cache in PG.
regards, tom lane
---(end of
On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote:
> On Wed, Dec 21, 2005 at 01:52:34PM +0100, Martijn van Oosterhout wrote:
> > On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote:
> > > Relational Constraint Inheritance Algebra
> > > With regard to class and attribute uniqueness
> >
Hi all,
So I started to make some changes with my code here and there
(nothing to do with the relevant lines) and suddenly currval and
rollback don't work.
try{
db.setAutoCommit(false);
addLM.setInt(1, lm.getOrigin());
Uh, you really didn't give us enough information to diagnose this. I
recommend you find the queries that are being run by Java and type them
into psql to see if they work there. If not, please show them to us.
---
Assad Ja
Tom has applied a patch to fix this and backpatched it to all relivant
branches. He might be preparing a summary email about this.
---
Mage wrote:
> Martijn van Oosterhout wrote:
>
> >On Sat, Dec 17, 2005 at 05:01:15PM -05
I am not sure what do you mean (In terms of what more do you need).
For the java code:
The insert works. (cause i see it in the db).
the currval does not work anymore. (see error message below).
And for some reason, it still inserts into the db, regardless of the
rollback (and setAutocommit(false))
Assad Jarrahian wrote:
> I am not sure what do you mean (In terms of what more do you need).
> For the java code:
> The insert works. (cause i see it in the db).
> the currval does not work anymore. (see error message below).
> And for some reason, it still inserts into the db, regardless of the
>
On Thu, Dec 22, 2005 at 09:47:11AM -0600, Jim C. Nasby wrote:
> On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote:
> > SELECT id
> > FROM mq
> > WHERE now - start_date > time_to_live;
>
> The problem is you can't use an index on this, because you'd need to
> index on (now() - start_date), wh
Hi,
Refer http://www.postgresql.org/docs/8.1/static/functions-sequence.html
On 12/23/05, Assad Jarrahian <[EMAIL PROTECTED]> wrote:
> Hi all,
>So I started to make some changes with my code here and there
> (nothing to do with the relevant lines) and suddenly currval and
> rollback don't work
Without seeing the SQL statements you executed in the session it is
difficult to see your problem.
Getting a current sequence after a rollback is no problem (in 8.0
anyway). Please note though, the sequence itself is NOT rolled back.
This is correct behaviour. Currval will return the last s
61 matches
Mail list logo