[EMAIL PROTECTED] wrote:
>
> Hi all,
>
> I have noted that Postgresql don't make a good memory handle. I have
> made the tables/procedure (in attached file) and run it as "select bench(10,
> 5000)". This will give a 5 records inserts (5 x 1). (well, I run it
> on a P200+64MB of RAM,
Is it possible (feasible) to create an index on a view.
We have a large table and a defined sub-set (view)
from this table, would it be possible to keep an index
of the sub-set.
Keith
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
I've written a HOWTO on how to create new aggregate functions to
create list (comma lists, HTML lists, etc.). It explains the purpose
of these, and gives an example of how to create one in pgplsql.
The HOWTO is written for the Zope site, but it's not really Zope-
or Python- specific.
http://www
[EMAIL PROTECTED] writes:
> I have noted that Postgresql don't make a good memory handle. I have
> made the tables/procedure (in attached file) and run it as "select bench(10,
> 5000)". This will give a 5 records inserts (5 x 1). (well, I run it
> on a P200+64MB of RAM, under Linux,
It seems to just feel like conflicting requirements, so it's a tug-of-war.
I've always done it by doing all the processing I can and then, from inside
a transaction, do
update seed from seed_table set seed=seed+1 where id='abc';
insert into some_table values ((select seed from seed_table where
In article <[EMAIL PROTECTED]>, "Tom Lane" <[EMAIL PROTECTED]>
wrote:
> You're correct, an outer WHERE clause will not be pushed down into the
> member selects of a UNION. (This hasn't really got anything to do with
> whether a VIEW is involved.) I haven't gotten round to convincing
> myself abo
Tom, Gordon,
> You're correct, an outer WHERE clause will not be pushed down into
> the member selects of a UNION. (This hasn't really got anything
> to do with whether a VIEW is involved.) I haven't gotten round to
> convincing myself about whether that transformation is always valid,
> or wha
Tom,
That does bring up a related question: when are we gonna get DROP
COLUMN capability? Currently my tables are littered with unused columns
because I can't remove them without blowing my referential integrity and
views to heck.
-Josh Berkus
_
The Hermit Hacker <[EMAIL PROTECTED]> writes:
> I'm figuring that if I can somehow get the query (using subselects,
> maybe?), to have the LIKE part of the query work only on the 6k records
> returned by the "=" part of it, the overall results should be faster ...
In 7.0.* I think the only way to
Phuong Ma <[EMAIL PROTECTED]> writes:
> WHERE substr("ORDER_NUM", 1, 1) != 'W'; # (Orders NOT prefixed with W.)
> OR...
> WHERE substr("ORDER_NUM", 1, 1) = 'I'; # (Orders prefixed with I.)
> However, the first query runs in about 10-15 seconds, and the second
> query
> ran for over 40 minutes
Em 05 Apr 2001, Cedar Cox escreveu:
>To this I say, remember that you are using a database! I would split this
>into 3 tables (people, exams, answers). Then only the 'answers' table
>would contain 3M records. Should be a bit faster. You don't want to have
>to store the and wi
"Gordon A. Runkle" <[EMAIL PROTECTED]> writes:
> I have a number of views that I'm bringing over from DB2 which
> have UNIONs in them. Some of the UNIONs have joins.
> The views are not working as expected (I'm running 7.1RC2).
> It appears that the where clause being applied to the view
> by t
Cedar Cox <[EMAIL PROTECTED]> writes:
> And I get this notice:
>>
> NOTICE: PortalHeapMemoryFree: 0x0x824a6e8 not in alloc set!
> NOTICE: Buffer Leak: [059] (freeNext=54, freePrev=58, relname=tblstsc1,
> blockNum=0, flags=0x14, refcount=-4 -1)
>>
> Ver 7.0.2 (sorry, I meant to include this...
Good day,
We're running a week-old CVS snapshot of PostgreSQL 7.1, and I'm not
sure
if this performance inconsistency is specific to it, or if this is just
something in PostgreSQL in general, but it seems kind of odd, and I
could
use some help here. ;)
I have run two queries in a table full of i
On Wed, 4 Apr 2001, Tom Lane wrote:
> Cedar Cox <[EMAIL PROTECTED]> writes:
> > And I get this notice:
>
> > NOTICE: PortalHeapMemoryFree: 0x0x824a6e8 not in alloc set!
> > NOTICE: Buffer Leak: [059] (freeNext=54, freePrev=58, relname=tblstsc1,
> > blockNum=0, flags=0x14, refcount=-4 -1)
>
I have a number of views that I'm bringing over from DB2 which
have UNIONs in them. Some of the UNIONs have joins.
The views are not working as expected (I'm running 7.1RC2).
It appears that the where clause being applied to the view
by the user is not being distributed properly to the selects.
Srikanth Rao <[EMAIL PROTECTED]> wrote:
>marketingbyoffice is a view.
How is it defined? [*]
>010404.13:52:28.691 [1352] FATAL 1: Memory exhausted in AllocSetAlloc()
Have you followed the suggestion at
http://www.postgresql.org/docs/faq-english.html#4.19 ?
HTH,
Ray
[*] Not just out of idl
Birgit Jansen wrote:
>I am trying to select from a table all rows that have a date befor
>1/1/2001 or after some date
>I am not sure how to do it.
>I try
>select date_part('year', start_date) from sometable;
>and that works but how do I get it to only show me the years between
>1990
I am trying to select from a table all rows that have a date befor
1/1/2001 or after some date
I am not sure how to do it.
I try
select date_part('year', start_date) from sometable;
and that works but how do I get it to only show me the years between
1990 and 2001 or some
othere set of dates.
I w
[EMAIL PROTECTED] writes:
> I'm glad I did this as I found out I have
> to use 'pg_dump -d' to get a useful backup. =)
Why?
> The tables I need to modify are referenced by other
> tables- will those other tables realise that they should re-establish
> foreign key references? If not automaticall
marketingbyoffice is a view.
I start the postmaster, then issue the following query
in psql console. It goes to sleep forever:-)
Why?
The log is as follows:
--log -
010404.13:49:14.612 [1352] StartTransactionCommand
010404.13:49:14.612 [1352] query: SELECT * INTO TEMP
TABLE marketi
Hi,
I've got a live database running PSQL 7.0.3. I need to do a couple of
changes to some of the table schema's but need to preserve the data
that currently exists in the tables. I've used pg_dump to make
backups and have verified that I can indeed restore into an empty
database from those back
Hi all,
I have noted that Postgresql don't make a good memory handle. I have
made the tables/procedure (in attached file) and run it as "select bench(10,
5000)". This will give a 5 records inserts (5 x 1). (well, I run it
on a P200+64MB of RAM, under Linux, and Postgres 7.0.2. In
"Peter Galbavy" <[EMAIL PROTECTED]> writes:
> We are building a postgresql based backend database for our 'hosting
> provisioning' system. In a vain attempt to add some, what I thought, simple
> performance tweaks, I thought I would try putting some of the larger and
> more straighforward queries
"Albert REINER" <[EMAIL PROTECTED]> writes:
> Is it really reasonable to enforce that the number of years is four
> digits at least?
I believe so. Without that cue it's pretty difficult for the timestamp
parser even to figure out which field is intended to be the year, let
alone whether you'd li
"Graham Vickrage" <[EMAIL PROTECTED]> writes:
> I have just done a rather large transaction via a telnet/psql session which
> executed OK. The problem occured when the telnet session timed out before I
> could commit the rows.
> This must have locked the rows in question because when I tried to v
Saluton,
I have a database with dates, some of which are historical dates.
When I wanted to enter May 28th, 812 I got an error message, had to
use 0812 for the year instead:
albert=> CREATE DATABASE test;
CREATE DATABASE
albert=> \c test
You are now connected to database test.
test=> CREATE TABL
Kyle <[EMAIL PROTECTED]> writes:
> The SQL is a little strange because the subquery tries to reference
> individual records from the outer query and then sum them.
> The more I look at it, I wonder if it is not just bad SQL.
It is bad SQL, but pre-7.1 Postgres failed to catch it. Here's the
CVS
I have just done a rather large transaction via a telnet/psql session which
executed OK. The problem occured when the telnet session timed out before I
could commit the rows.
This must have locked the rows in question because when I tried to vacuum
the table it just hung.
What is the best way of
Algirdas,
This should do the trick:
SELECT a.id,b.name FROM a,b WHERE a.id=b.id UNION SELECT id,null FROM a WHERE id NOT
IN (SELECT id FROM b);
Troy
>
> Hi all,
>
> I'm new to postgre, I've changed my work and consequently now i'm moving
> from MS plaform.
> In MS SQL there are such constr
Cedar Cox <[EMAIL PROTECTED]> writes:
> Now for the problem.. In attempting to get what I want, I wrote this:
> select not exists (select distinct max((select sc1caption from tblstsc1
> where (tblstsc1options.surid like surid || '.%' or
> surid=tblstsc1options.surid) and surid!=tblstsc1options.s
This query worked under 7.0.3 but yields an error under 7.1RC1 or RC2.
The error message is:
psql:outbug.sql:43: ERROR: Sub-SELECT uses un-GROUPed attribute
h.ordnum from outer query
Tom, you patched util/clauses.c (near line 540) a while back to prevent
the same error message on a different que
I've read that the version 7.1 provides outer join feature. Since I did not
try it yet, I've no more information about it.
> -Message d'origine-
> De: Algirdas Sakmanas [SMTP:[EMAIL PROTECTED]]
> Date: mercredi 4 avril 2001 13:03
> À:[EMAIL PROTECTED]
> Objet:[SQL] outer jo
Hello,
I believe these are supported in 7.1
On Wed, 4 Apr 2001, [iso-8859-4] Algirdas ©akmanas wrote:
>Hi all,
>
>I'm new to postgre, I've changed my work and consequently now i'm moving
>from MS plaform.
>In MS SQL there are such constructs left or right outer join, in postgres
>there are no
Can someone suggest how to improve the following query, so as to make the
LIKE section operate on the results found by the = one?
SELECT ndict.url_id,ndict.intag
FROM ndict,url
WHERE ndict.word_id=-720551816
AND url.rec_id=ndict.url_id
AND ((url.url || '') LIKE '%http://www.postgresql.o
Question and a problem:
I have this query
select distinct not exists (
select sc1caption from tblstsc1
where (tblstsc1options.surid like surid || '.%'
or surid=tblstsc1options.surid)
and surid!=tblstsc1options.surid
and sc1caption is not null
)
from tblstsc1o
Hi all,
I'm new to postgre, I've changed my work and consequently now i'm moving
from MS plaform.
In MS SQL there are such constructs left or right outer join, in postgres
there are no such thing
Can You offer me strategy to make query that selects from table (a) and
joins to it another (b)
on e
Hi Postgres people ;-)
This is probably a simple question, still I need to know:
When restoring data using dumps
1. Will your indices be restored using copy dumps?
2. Does vacuumdb restore them?
3. If vacuumdb does not, is there something which does?
Kind regards,
Koen Antonissen
-
BTW The service is 7.0.2 and the client 7.1RC1 and the OSes are
OpenBSD/i386 2.8-stable.
On Wed, Apr 04, 2001 at 11:12:34AM +0100, Peter Galbavy wrote:
> We are building a postgresql based backend database for our 'hosting
> provisioning' system. In a vain attempt to add some, what I thought, sim
We are building a postgresql based backend database for our 'hosting
provisioning' system. In a vain attempt to add some, what I thought, simple
performance tweaks, I thought I would try putting some of the larger and
more straighforward queries into functions. For everything else the same,
the fu
At 19.29 3/4/01 -0400, you wrote:
>Christophe Labouisse <[EMAIL PROTECTED]> writes:
> > I'm trying to run pg_dumpall to backup all my users' bases but since I
> > have configure pg_hba.conf to "passwd" pg_dumpall always fails:
>
>pg_dumpall doesn't work very well with password authentication (and
Tom Lane <[EMAIL PROTECTED]> wrote:
>pg_dumpall doesn't work very well with password authentication (and
>even if it did, storing the password in a cron script doesn't seem
>like a good idea to me).
>
>As long as the dumper will run on the same machine as the database
>server, consider using IDENT
Has somebody insert binary data (BLOBs) in a row in PostgreSQL v7.1?
Is the correct data type to do it "VARCHAR(65535)"?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [E
43 matches
Mail list logo
