Mike Harding <[EMAIL PROTECTED]> writes:
> The following was run -immediately- after a vacuum.
You realize "vacuum" doesn't update the statistics, right?
You have to do "analyze" or "vacuum analyze" for that.
--
greg
---(end of broadcast)---
josue <[EMAIL PROTECTED]> writes:
> to something like:
>
> select counter(),a,b from foo;
The OLAP SQL Standard way to spell this is "ROW_NUMBER() OVER ()".
Postgres doesn't have any support for any of the OLAP features however. It
would be really nice because they're nigh impossible to emulat
> > On Thu, Feb 24, 2005 at 09:00:46PM +, Charl Gerber wrote:
> > I'm trying to create a function that takes 1
> > paramater (eg an integer) as input, then does 5
> > database updates or deletes in 5 different SQL
> > statements and returns 5 integers (in one resultset)
> > indicating how many
Try creating the trigger on BEFORE DELETE.
--elein
On Fri, Feb 25, 2005 at 05:14:18PM -0700, [EMAIL PROTECTED] wrote:
> Yes, thank you, I corrected my function from statement level to row level.
> This did get rid of the error message. However, I still get no output from
> an OLD variable that sh
Hi,
I am trying to determine if there is a difference in
I/O cost between SPI functions and low level functions
like heap_fetch, heap_getnext, etc...
I want to decide if there is any merit for using the low
level functions instead of the easier SPI.
My guess is that they both use the same underlyin
Alternatives to returning a set of rows is to return
a set of integers, one per row selected. Same example
as Huxton's without the row type.
Also you could return an array of integers.
--elein
On Thu, Feb 24, 2005 at 09:00:46PM +, Charl Gerber wrote:
> I'm trying to create a function that
Rick Schumeyer wrote:
> I tried that, but I get a "...violates foreign-key constraint" error.
Oh, sorry.
---
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > [EMAIL PROTECTED] On Beha
I tried that, but I get a "...violates foreign-key constraint" error.
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Bruce Momjian
> Sent: Friday, February 25, 2005 6:23 PM
> To: Rick Schumeyer
> Cc: 'PgSql General'
> Subject: Re: [GE
I suspected that might be part of the answer.
Would some combination of triggers work instead? I've played
with those too, but without success.
>
> This is an ancient gotcha: as soon as you delete the book row, there is
> no longer any such entry in the bookview view ... and "old.id" is
> effe
On Fri, 25 Feb 2005, Bruce Momjian wrote:
> Tom Lane wrote:
> > Ron Mayer <[EMAIL PROTECTED]> writes:
> > > Should the relation overflow be a WARNING or a LOG? ...
> > I'd go for making them both LOG, I think. More consistent.
>
> Can we also update this wording:
>
> INFO: free space map: 52 r
If you insert the results of your query into a table with a serial
column, the serial column will do what you want..
On Sat, 2005-02-26 at 01:10 +0100, Peter Eisentraut wrote:
> josue wrote:
> > is there a way return a column with the row number automatically
> > generated according the way the ro
Yes, thank you, I corrected my function from statement level to row level.
This did get rid of the error message. However, I still get no output from
an OLD variable that should contain data: see the test variable in the
simple case below.
How else can I test OLD variables? This is the simplest te
josue wrote:
> is there a way return a column with the row number automatically
> generated according the way the rows were processed by the query.
No, but you can easily keep a counter in the client.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end o
"Rick Schumeyer" <[EMAIL PROTECTED]> writes:
> -- delete to item and book instead of bookview
> create rule bookviewdel as on delete to bookview do instead (
> delete from book where id=old.id;
> delete from item where id=old.id;
> );
This is an ancient gotcha: as soon as you delete th
Uh, because of your REFERENCES clause you have to delete from 'item'
first, then 'book':
> -- delete to item and book instead of bookview
> create rule bookviewdel as on delete to bookview do instead (
> delete from item where id=old.id;
> delete from book where id=old.id;
> );
And y
I have two related tables, “item”
and “book”. I have defined
a view, “bookview”
that contains fields from item and book.
My goal was to have all
inserts, updates, and deletes performed
on bookview rather than on
the tables directly. I was able
to do this with ON INSERT
and ON UPDAT
Hello everyone. Thanks for the answers earlier about the new 8.0 version.
We have a client who is thinking about putting postgresql 8.0 on Windows
2003 Server, but he is concerned because this is the first version to
run natively on windows. Are there any issues with 8.0 on Windows?
Also, with
Hello list,
is there a way return a column with the row number automatically
generated according the way the rows were processed by the query.
For instance:
select a,b from foo;
a b
20 yes
40 no
15 yes
to something like:
select counter(),a,b from foo;
counter a b
1 20 yes
2 40 no
3
Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> The alternate input source is created on application startup with:
> ctrl->sys->dbInputId = XtAppAddInput (app,
> ctrl->sys->socketId,
> (XtPointer) (XtInputReadMask),
> DbInputCB, (XtPointer) ctrl);
> And, it remain
Mike Harding <[EMAIL PROTECTED]> writes:
> Any way to adjust n_distinct to be more accurate?
You could try increasing the statistics target for the relevant columns.
What does pg_stats show for the "numdistinct" estimates of the columns
you are grouping over, and does that have anything to do with
Any way to adjust n_distinct to be more accurate?
I don't think a 'disk spill' would be that bad, if you could re-sort the
hash in place. If nothing else, if it could -fail- when it reaches the
lower stratosphere, and re-start, it's faster than getting no result at
all... sort of an auto disable
Mike Harding <[EMAIL PROTECTED]> writes:
> I've been having problems where a HashAggregate is used because of a bad
> estimate of the distinct number of elements involved.
If you're desperate, there's always enable_hashagg. Or reduce sort_mem
enough so that even the misestimate looks like it will
Tom,
The alternate input source is created on application startup with:
ctrl->sys->dbInputId = XtAppAddInput (app,
ctrl->sys->socketId,
(XtPointer) (XtInputReadMask),
DbInputCB, (XtPointer) ctrl);
And, it remains throughout the duration of the application. I bel
I've been having problems where a HashAggregate is used because of a bad
estimate of the distinct number of elements involved. In the following
example the total number of domain IDs is about 2/3 of the number of
rows, and it's estimated at about 1/15 of the actual value. This will
occasionally c
Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> Sorry, I didn't know what you needed. Here is the trace:
> (gdb) bt
> #0 0x2b04eef2 in select () from /lib/i686/libc.so.6
> #1 0x2adeb06c in ?? () from /usr/X11R6/lib/libXt.so.6
> #2 0x0001 in ?? ()
> #3 0x08136b88 in ?? ()
> #4 0x2adc0c47 in
I have a dotgeek.org free account, but they are about to leave
PostgreSQL for MySQL which is too bad. Can you suggest me a
free/cheap PostgreSQL host where I can test an open-source program?
No web hosting is needed, only a single database with TCP/IP access
enabled, plus ability to load a C-l
Sorry, I didn't know what you needed. Here is the trace:
(gdb) bt
#0 0x2b04eef2 in select () from /lib/i686/libc.so.6
#1 0x2adeb06c in ?? () from /usr/X11R6/lib/libXt.so.6
#2 0x0001 in ?? ()
#3 0x08136b88 in ?? ()
#4 0x2adc0c47 in XtChangeManagedSet () from /usr/X11R6/lib/libXt.so.6
#5 0x
Terry Lee Tucker <[EMAIL PROTECTED]> writes:
>> Is this a bug? Did anybody look at the stack trace is sent? I don't have
>> the expertise to analyze it.
I haven't seen any stack trace. strace is not a stack trace --- I'm
talking about attaching to the stuck process with gdb and getting the
resul
I realize that I'm carrying on a conversation with myself here; however,
nothing new 'bout that, I do it all the time ;o)
Just in case anyone else is interested, the only way I can get this to work
correctly is to put a 100 millisecond timer in front of the call to
CheckForNotifies as in:
Alexis Vasquez <[EMAIL PROTECTED]> writes:
> UNSUSCRIBE
Perhaps it's time once again to remind people that the list robot
doesn't read the list, and that posting list commands to the list
accomplishes nothing except to waste the time of all the other
list members.
Subscribe/unsubscribe commands g
Quoting Andrew Hall <[EMAIL PROTECTED]>:
> > Do you happen to have the same type disks in all these systems? That could
>
> > point to a disk cache "problem" (f.e. the disks lying about having written
>
> > data from the cache to disk).
> >
> > Or do you use the same disk parameters on all these
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
- -
PostgreSQL RPM Set Update
2005-02-25
Version(s): 8.0.1, 7.4.7
New set labels: 7.4.7-2PGDG, 8.0.1-2PGDG
- -
-
UNSUSCRIBE
__
Renovamos el Correo Yahoo!: ¡250 MB GRATIS!
Nuevos servicios, más seguridad
http://correo.yahoo.es
---(end of broadcast)---
TIP 6: Have you searched our list archives?
This thing is hanging up in PQnotifies. I have added a simple fprintf
statement to the following function:
void CheckForNotifies (Controls *ctrl)
{
#ifndef CALL_TRACE
fprintf (stderr, "%s: %d: %s (%s)\n", __FILE__, __LINE__,
__FUNCTION__, ctrl->table);
#endif
PQflush (ctrl->sy
Charl Gerber <[EMAIL PROTECTED]> writes:
> There is one 'install.sql' file which does something like:
> DROP DATABASE db_name IF EXISTS (<--- is there something similar in
> Postgre? Like the CREATE OR REPLACE FUNCTION)
> CREATE DATABASE db_name;
There's no IF EXISTS. Usually people just do
Charl Gerber wrote:
> DROP DATABASE db_name IF EXISTS (<--- is there something similar in
> Postgre? Like the CREATE OR REPLACE FUNCTION)
No, you just go ahead and ignore the error.
> CREATE DATABASE db_name;
>
> USE db_name;
\connect db_name
> SOURCE users.sql;
\i users.sql
--
Peter Eisen
On Mon, 21 Feb 2005 14:42:59 +0300
"mef" <[EMAIL PROTECTED]> wrote:
> Hello, pgsql-general :)
> I have a task to make a some actions on 'deleting table' event. It event my
> be raise both stored procederes and any other's ways (pgAdmin GUI for
> example).
> Is any solutions for trace it and exec
Rookie question... I'm converting a MySQL database to Postgre. In MySQL,
all the tables are defined in seprate 'table_name.sql' file.
There is one 'install.sql' file which does something like:
DROP DATABASE db_name IF EXISTS (<--- is there something similar in
Postgre? Like the CREATE OR REPLA
Dave Smith <[EMAIL PROTECTED]> writes:
> I am using 7.4.5 and trying to use the like clause (Local C) but
> postgres does not seem to want to use the index.
You *sure* you're using C locale? Try "show lc_collate".
If you're not, and don't want to re-initdb, you can make an index using
the text_p
On Fri, 25 Feb 2005 09:20:29 -0800, Si Chen <[EMAIL PROTECTED]> wrote:
> Thanks!
>
> Is there any documentation on how to upgrade to 8.0?
Dump your DB, install 8.0, reload your DB. Since you'e using RHEL3,
you should use the RPMs and it will be fairly easy.
> Is it possible to upgrade from 7.4
Bruce Momjian writes:
>> Does anyone know if it will be possible to run the server with ANSI/ISO
>> string escaping instead of C-style escapes? The C style escaping is a
>> shoot-down for our adoption of postgres, since its non-standard.
> Uh, yea, this is going to require quite a bit of discussi
Thanks!
Is there any documentation on how to upgrade to 8.0?
Is it possible to upgrade from 7.4 to 8.0 while keeping a production
database running "in place"? Or should I install 8.0 in another
directory/machine and then restore the database into it? Are there any
incompatibilities/modificatio
Am Freitag, 25. Februar 2005 17:54 schrieb Dave Smith:
> >From my reading of that statement it says it is going to use the index
>
> to match company_id=1000 and then fetch the row and compare
> product_desc. Where as in the the case without the like it is using
> product description in the Index C
Looks to me like it is using an index scan in both example queries.
I'm not an expert plan reader, but are you wondering why the index
condition in the second query includes everything from your WHERE
clause? Are you using a multi-column index that is not applicable in
the first query?
It's
English version of this mail at the end.
Bonjour,
L'Association Loi 1901 PostgreSQLFr a pour but de promouvoir notre SGBDR
Open Source favori. Elle souhaite devenir un interlocuteur privilégié
des entreprises et des particuliers et permettre la mise en relation des
professionnels et des entreprise
>From my reading of that statement it says it is going to use the index
to match company_id=1000 and then fetch the row and compare
product_desc. Where as in the the case without the like it is using
product description in the Index Condition.
The speed of the queries certainly seems to bare it ou
Am Freitag, 25. Februar 2005 17:31 schrieb Dave Smith:
> I am using 7.4.5 and trying to use the like clause (Local C) but
> postgres does not seem to want to use the index.
>
> explain
> declare t scroll cursor for
> select * from product where company_id=1000 and product_desc like 'J%'
> order by
I am using 7.4.5 and trying to use the like clause (Local C) but
postgres does not seem to want to use the index.
explain
declare t scroll cursor for
select * from product where company_id=1000 and product_desc like 'J%'
order by company_id,product_desc;
QUERY
> Does anyone know if it will be possible to run the server with ANSI/ISO
> string escaping instead of C-style escapes? The C style escaping is a
> shoot-down for our adoption of postgres, since its non-standard.
>
>
> Not yet, but we have a TODO item:
>
> * Allow backslash handling in q
Si Chen <[EMAIL PROTECTED]> writes:
> I read the PostgreSQL 8.0 "What's New" page
> (http://www.postgresql.org/docs/whatsnew) and wasn't sure whether
> version 8.0 is significantly faster, more scalability, or more stable
> than versions 7.4? I remember big speed improvements between 7.3 and
>
Is a portal using the V3 protocol the same as a cursor? I am trying to
debug a slow query and I want the JDBC driver to use the cursor syntax.
It says it is using a portal but the performance seems like just a
regular sql statement.
--
Dave Smith
CANdata Systems Ltd
416-493-9020
--
=?iso-8859-1?Q?Martin_Sch=E4fer?= <[EMAIL PROTECTED]> writes:
> I recently found out that my app doesn't work in Spain because it creates
> localized queries like this:
> create table t (c1 float8, c2 float8);
> insert into t (c1, c2) values (3,14159, 1,4142);
> I understand that PostgreSQL obvi
Bruce Momjian wrote:
Ken Johanson wrote:
Greetings,
Does anyone know if it will be possible to run the server with ANSI/ISO
string escaping instead of C-style escapes? The C style escaping is a
shoot-down for our adoption of postgres, since its non-standard.
Not yet,
Tom Lane wrote:
Joe Maldonado <[EMAIL PROTECTED]> writes:
After a create or replace view, the new view definition is not being
used by plpgsql functions that use the view. Is this a known bug ? Is
there a workaround it ?
Start a fresh backend session. The old query plan is presumably being
For scalability the new tablespaces are a major improvement, in that they
allow enhanced allocation of portions of the database to different disks.
This can massively improve speed in RAID environments, or even just
multi-disk environments. PITR isn't a big deal for me, but might be for
you.
Rick
Hi,
Situation:
The client program queries a dataset from the PG server, and it displays
it, say, in a table. The user can navigate and update fields, insert new
records, delete records. And any change should be visible on all clients.
I think I have basically 2 choices:
1 : using SELECT and
On Feb 25, 2005, at 9:41 AM, Si Chen wrote:
Hello.
I read the PostgreSQL 8.0 "What's New" page
(http://www.postgresql.org/docs/whatsnew) and wasn't sure whether
version 8.0 is significantly faster, more scalability, or more stable
than versions 7.4? I remember big speed improvements between 7.3
On Thu, 24 Feb 2005 22:46:02 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Lonni J Friedman <[EMAIL PROTECTED]> writes:
> > Oh, also, do you see a time difference in running this query with &
> > without the genetic optimizer?
>
> Yeah, but that's no surprise.
>
> The reason I couldn't replicate y
Hello.
I read the PostgreSQL 8.0 "What's New" page
(http://www.postgresql.org/docs/whatsnew) and wasn't sure whether
version 8.0 is significantly faster, more scalability, or more stable
than versions 7.4? I remember big speed improvements between 7.3 and
7.4. It seems the biggest advantage o
I think you have created a statement level trigger (If they existed in
7.4.7...) by not including FOR EACH ROW in your create statement. In
statement level triggers, there is no OLD or NEW.
>>> Rick Casey <[EMAIL PROTECTED]> 02/24/05 1:22 PM >>>
Hello all,
I am trying to a simple thing: create
Trying to connect with a pgsql 8 server (locally), psycopg return this error:
Traceback (most recent call last):
File "", line 1, in ?
psycopg.OperationalError: could not connect to server: ?R@
Is the server running locally and accepting
connections on Unix domain socket "/var/run
Thanks very much :-) this saves lot of time for my
update statements
-Venkatesh
--- Alban Hertroys <[EMAIL PROTECTED]> wrote:
> Venkatesh Babu wrote:
> > Hello,
> >
> > I need to update few rows of a table (call it 't')
> and
> > need to set just one column col1 (out of around
> 100
> > co
Hi Tom,
Thanks for the reply -- yes, creating the en_US.utf8 locale and using
that, fixed all of my problems.
Thanks,
--Jatinder
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: 24 February 2005 17:11
To: Jatinder Sangha
Cc: pgsql-general@postgresql.org
Subject: Re: [GE
Venkatesh Babu wrote:
Hello,
I need to update few rows of a table (call it 't') and
need to set just one column col1 (out of around 100
columns... to be exact, our table has 116 columns).
The info about rows to be updated is present in
another table t2. t2 just contains 2 columns (row_key,
new valu
Rick Casey wrote:
I am going to answer my own post here since I found more info in some
threads posted here.
My real question now is: does the OLD variable work at all in plpgsql?
If not, then some major documentation, books and many posted messages
appear to be wrong!
It certainly works in BEFO
Hello,
I need to update few rows of a table (call it 't') and
need to set just one column col1 (out of around 100
columns... to be exact, our table has 116 columns).
The info about rows to be updated is present in
another table t2. t2 just contains 2 columns (row_key,
new value for col1).
What is
See partial output from strace below. On this particular run, the command
locked up during the second edit operation. The notify message, move_update,
was delivered, but the PQsendquery never returns.
Any ideas Tom?
recv(3, "A\0\0\0\25\0\0~3move_update\0\0C\0\0\0\rUPDAT"..., 16384, 0) = 42
writ
Nageshwar Rao wrote:
What is the maximum number we can give for varchar (number) in postgresql
7.4.x
Details on data-types are in the manuals under "Data Types". Go to
http://www.postgresql.org, click "Documentation" then choose "7.4
(without comments)" on the right-hand side. Data Types are in t
Title: Maximum size for varchar
What is the maximum number we can give for varchar (number) in postgresql 7.4.x
Thanks
Rao
Hi.
Moreover, what I find very strange is that when I log in with psql (or
any other SQL client) onto the production database and execute the same
query, it runs fast - even in peak hours!
That is strange. Is this a networking issue?
It is not. psql was executed on the same machine as the runnin
Hi,
I recently found out that my app doesn't work in Spain because it creates
localized queries like this:
create table t (c1 float8, c2 float8);
insert into t (c1, c2) values (3,14159, 1,4142);
I understand that PostgreSQL obviously can't parse this query. But I'm not sure
how to fix this que
Jon Asher wrote:
Thanks for the reply... but which query will be faster and less expensive?
I don't have a table now with 4 million rows, but I'm thinking of building
such a table. Querying it would return 1 row. The alternative is to query
an existing table of 200k rows, and return 800 rows.
Act
Jon Asher wrote:
Thanks for the reply... but which query will be faster and less expensive?
I don't have a table now with 4 million rows, but I'm thinking of building
such a table. Querying it would return 1 row. The alternative is to query
an existing table of 200k rows, and return 800 rows.
Opt
Charl Gerber wrote:
I'm trying to create a function that takes 1
paramater (eg an integer) as input, then does 5
database updates or deletes in 5 different SQL
statements and returns 5 integers (in one resultset)
indicating how many rows were affected by the various
updates/deletes.
How do I do th
74 matches
Mail list logo
