To: ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:
Subject: pgsql-sql-digest V1 #284
Message not delivered to recipients below. Press F1 for help with VNM
error codes.
VNM3043: DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST
VNM3043 -- MAILBOX IS FULL
The message cannot be delivered because the recipient's mailbox contains the maximum number of messages, as set by
thesystem administrator. The recipient must delete some messages before any other messages can be delivered. The
maximummessage limit for a user's mailbox is 10,000. The default message limit is 1000 messages. Administrators
canset message limits using the Mailbox Settings function available in the Manage User menu (MUSER).
When a user's mailbox reaches the limit, the user must delete some of the messages before the mailbox can accept
anymore incoming messages.
---------------------- Original Message Follows ----------------------
pgsql-sql-digest Thursday, July 8 1999 Volume 01 : Number 284
Index:
Re: [SQL] Index on Type Numeric
Re: [SQL] Good Optimization
concatenation varchar and float
Re: [SQL] Good Optimization
Re: [SQL] Good Optimization
Re: [SQL] Good Optimization
unsubscribe
Re: [SQL] Good Optimization
Re: [SQL] Good Optimization
Re: [SQL] Good Optimization
RewriteDefine.c : Rule size
Re: [SQL] RewriteDefine.c : Rule size
uncorrelated subqueries
Re: [SQL] uncorrelated subqueries
Referential Integrity
offtopic: odbc C frontend for linux?
Re: [SQL] Referential Integrity
Re: [SQL] offtopic: odbc C frontend for linux?
Re: [SQL] offtopic: odbc C frontend for linux?
subscribe
----------------------------------------------------------------------
Date: Wed, 7 Jul 1999 23:04:00 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Index on Type Numeric
This will have to wait for 6.6.
> Using version 6.5, when I try to create an index on a table using columns that have data types of "numeric", I get an
errormessage;
> can't find default operator class for type 1700
>
> What do I do now?
>
> Steven Pennie
> Application Programmer/Analyst III
> KDOT Materials & Research Center
> (785) 291-3863 - Fax (785) 296-2526
>
>
>
- -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
------------------------------
Date: Thu, 08 Jul 1999 10:09:39 +0200
From: Patrik Kudo <kudo@partitur.se>
Subject: Re: [SQL] Good Optimization
Bruce Momjian wrote:
>
> Added to TODO:
>
> * In WHERE x:ND x
I don't know if I'm way off, but wouldn't removing "xperformance further?
- --
ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
KEnns det oklart? Froga po!
------------------------------
Date: Thu, 08 Jul 1999 10:27:43 +0200
From: Eric BASIER <basier@ipgp.jussieu.fr>
Subject: concatenation varchar and float
Hi all,
Does anyone known fi it's possible to make a concatenation of varchar
and float.
I try the request bellow and I don't known how to correct.
seedending)) || ('" "' || latitude) from station;
ERROR: There is more than one possible operator '||' for types
'unknown' and 'float8' You will have to retype this query using an explicit cast
seedending)) || ('" "'::text || latitude) from station;
ERROR: There is more than one possible operator '||' for types 'text'
and 'float8' You will have to retype this query using an explicit cast
seedending)) || ('" "'::text || latitude::float8) from station;
ERROR: There is more than one possible operator '||' for types 'text'
and 'float8' You will have to retype this query using an explicit cast
seed
Table +----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| network | varchar() not null
| 2 |
| station | varchar() not null
| 5 |
| begining | datetime not null
| 8 |
| ending | datetime
| 8 |
| modified | datetime
| 8 |
| latitude | float8
| 8 |
| lat_prec | int4
| 4 |
| longitude | float8
| 8 |
| long_prec | int4
| 4 |
| coord_type | int4
| 4 |
| coord_map | varchar()
| 100 |
| elevation | float8
| 8 |
I work with postgres 6.4.2
- --
Basier Eric e-mail : basier@ipgp.jussieu.fr
http://geoscope.ipgp.jussieu.fr
IPGP Observatoires Sismologie-Volcanologie
4,Place Jussieu 75005 Paris Tour 24-14 4eme Etage Tel 01 44 27 38 96
------------------------------
Date: Thu, 8 Jul 1999 11:04:26 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] Good Optimization
>
> Bruce Momjian wrote:
> >
> > Added to TODO:
> >
> > * In WHERE x=3 AND x=y, add y=3
>
> I don't know if I'm way off, but wouldn't removing "x=y" improve
> performance further?
Maybe in the simple case above. But it will probably change the result set if someone issues
SELECT a.x, b.y FROM a, b WHERE a.x > AND a.x <
AND a.x which should then get rewritten into
SELECT a.x, b.y FROM a, b WHERE a.x > AND a.x <
AND a.x AND b.x > AND b.x < This time the
"a.x IMHO we're improving optimization more and more on the cost of query parse/rewrite/optimize/plan time.
Thusperformance of statements that EXECUTE fast slows down more and more. Isn't it time to think
about some (maybe shared) "parsetree->plan" cache that provides ready to use plans if only Const values have
changed?
Jan
- --
## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#
------------------------------
Date: Thu, 08 Jul 1999 11:23:14 +0200
From: Patrik Kudo <kudo@partitur.se>
Subject: Re: [SQL] Good Optimization
Jan Wieck wrote:
>
> >
> > Bruce Momjian wrote:
> > >
> > > Added to TODO:
> > >
> > > * In WHERE x=3 AND x=y, add y=3
> >
> > I don't know if I'm way off, but wouldn't removing "x=y" improve
> > performance further?
>
> Maybe in the simple case above. But it will probably change
> the result set if someone issues
>
> SELECT a.x, b.y FROM a, b WHERE a.x >> AND a.x <>
AND a.x >
> which should then get rewritten into
>
> SELECT a.x, b.y FROM a, b WHERE a.x >> AND a.x <>
AND a.x > AND b.x >> AND b.x <>
> This time the "a.x
Ouch... didn't think that far. However, if an cache is implemented
it might be worth the extra overhead to optimize special cases like
the one I commented on. Since most systems use a fairly limited set
of queries (I suppose...) most of the queries will be cached quite
fast.
BTW, I just love the work all of you are doing! Postgres is awsome!
/Kudo
------------------------------
Date: Thu, 8 Jul 1999 12:24:20 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] Good Optimization
Patrik Kudo wrote:
> Jan Wieck wrote:
> >
> > This time the "a.x >
> Ouch... didn't think that far. However, if an cache is implemented
> it might be worth the extra overhead to optimize special cases like
> the one I commented on. Since most systems use a fairly limited set
> of queries (I suppose...) most of the queries will be cached quite
> fast.
Exactly the fact that most system use a limited set of queries made me think that a shared query->plan
cache, that in the best of all worlds isn't lost on backend termination, is worth the efford. And it would lower
the need of client side prepared execution plans as discussed sometimes.
Jan
- --
## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#
------------------------------
Date: Thu, 08 Jul 1999 13:07:19 +0200
From: Seb <unixseb@club-internet.fr>
Subject: unsubscribe
unsubscribe
------------------------------
Date: Thu, 08 Jul 1999 11:01:17 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [SQL] Good Optimization
wieck@debis.com (Jan Wieck) writes:
> IMHO we're improving optimization more and more on the cost
> of query parse/rewrite/optimize/plan time. Thus performance
> of statements that EXECUTE fast slows down more and more.
> Isn't it time to think about some (maybe shared)
> "parsetree->plan" cache that provides ready to use plans if
> only Const values have changed?
Easier said than done, because the plan chosen by the optimizer may
well depend on the values of the constants. For example, in
SELECT * FROM t1, t2 WHERE t1.a
the optimizer will attempt to estimate the fraction of t1's rows that
will pass the restriction "t1.b < 12", and then it will choose the
type of join depending on how many rows it thinks there will be.
If we require plans to be chosen without dependence on the values of
constants, we will have to give up a great deal of optimization.
I do not object to letting the user specifically say PREPARE xyz...
and then using that prepared plan; there are plenty of times when
trading off planning time against getting a narrowly-tailored plan
is a useful thing to do. But we mustn't pre-empt the user's choice.
Note also that in a PREPARE context, it is known which items are
substitutable parameters and which are plain constants, so some
amount of optimization can still go on.
regards, tom lane
------------------------------
Date: Thu, 8 Jul 1999 11:40:18 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Good Optimization
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Bruce Momjian wrote:
> >
> > Added to TODO:
> >
> > * In WHERE x:ND x>
> I don't know if I'm way off, but wouldn't removing "x> performance further?
Let me rewrite it. TODO list updated:
* In WHERE tab1.x:ND tab1.x
- -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
------------------------------
Date: Thu, 8 Jul 1999 11:48:04 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Good Optimization
> IMHO we're improving optimization more and more on the cost
> of query parse/rewrite/optimize/plan time. Thus performance
> of statements that EXECUTE fast slows down more and more.
> Isn't it time to think about some (maybe shared)
> "parsetree->plan" cache that provides ready to use plans if
> only Const values have changed?
TODO list has:
* Cache most recent query plan(s?)
- -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
------------------------------
Date: Thu, 08 Jul 1999 18:15:07 +0200
From: Olivier Ertz <ertz@illite.u-strasbg.fr>
Subject: RewriteDefine.c : Rule size
Hello,
I'm using Postgres 6.4.2.
I want to create a view, but a message appear :ERROR: DefineQueryRewrite: rule plan string too big.
What could be the solution to avoid the problem? In fact, I have 31
columns in a table, and i want to make a view on this table (with
a line restrict I watched the RewriteDefine.c code and saw : #define RULE_PLAN_SIZE BLCKSZ
I thought changing the value of this size, but I don't find the
place to modify the BLCKSZ value.
But, before doing the modification and recompile, I would like to
have your opinion on the problem...perhaps is there an easier way to
resolve the problem.
Thank you for answering
- --
ERTZ Olivier - ertz@illite.u-strasbg.fr - Tel 03 88 35 85 87
- ------------------------------------------------------------ Etudiant DESS Informatique | Stagiaire EOST/ULP-CGS
UniversiteLouis Pasteur | Ecole et Observatoire des STRASBOURG - France | Sciences de la Terre
- ------------------------------------------------------------ World Wide Web : http://dess-info.u-strasbg.fr/~ertz
- ------------------------------------------------------------
------------------------------
Date: Thu, 8 Jul 1999 18:30:16 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] RewriteDefine.c : Rule size
>
> Hello,
>
> I'm using Postgres 6.4.2.
> I want to create a view, but a message appear :
> ERROR: DefineQueryRewrite: rule plan string too big.
> What could be the solution to avoid the problem? In fact, I have 31
> columns in a table, and i want to make a view on this table (with
> a line restrict That's a well known, long standing problem which is still not fixed in v6.5. I'm planning to
fixit for v6.6 - though that doesn't help you.
> I watched the RewriteDefine.c code and saw :
> #define RULE_PLAN_SIZE BLCKSZ
> I thought changing the value of this size, but I don't find the
> place to modify the BLCKSZ value.
> But, before doing the modification and recompile, I would like to
> have your opinion on the problem...perhaps is there an easier way to
> resolve the problem.
Never did it, but the BLCKSZ is defined in include/config.h. That is a file generated from config.h.in, so be
sure to change it there and reconfigure before recompiling.
And if you change the blocksize, be sure to run initdb after because your databases will not be binary
compatible any more!
Jan
- --
## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#
------------------------------
Date: Fri, 09 Jul 1999 02:08:18 -0500
From: Bruce Lowery <bruce.lowery@edventions.com>
Subject: uncorrelated subqueries
Hello,
In v6.4.2 do uncorrelated subqueries get run on each iteration of the
outer loop? For instance,
SELECT a
FROM table1
WHERE table1.b AND table1.c IN ( SELECT d FROM table2 WHERE table2.e;
Does the subquery get run for each row from table 1 where table1.b is true?
Bruce Lowery
edventions.com
------------------------------
Date: Thu, 08 Jul 1999 15:30:06 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [SQL] uncorrelated subqueries
Bruce Lowery <bruce.lowery@edventions.com> writes:
> In v6.4.2 do uncorrelated subqueries get run on each iteration of the
> outer loop?
It looks like the particular example you give is done that way, but the
system does know about uncorrelated subqueries. For example, using 6.5:
explain SELECT a FROM table1 WHERE table1.b3 AND
table1.c NOTICE: QUERY PLAN:
Seq Scan on table1 (costC.00 rows th InitPlan -> Seq Scan on table2 (costC.00 rows th
explain SELECT a FROM table1 WHERE table1.b3 AND
table1.c NOTICE: QUERY PLAN:
Seq Scan on table1 (costC.00 rows th SubPlan -> Seq Scan on table2 (costC.00 rows th
You can see that we get an "InitPlan" (ie, run once) for an uncorrelated
subquery but a "SubPlan" (repeat each time) for a correlated one.
Unfortunately, the case you care about is:
explain SELECT a FROM table1 WHERE table1.b3 AND
table1.c IN ( SELECT d FROM table2 WHERE table2.eD);
NOTICE: QUERY PLAN:
Seq Scan on table1 (costC.00 rows th SubPlan -> Seq Scan on table2 (costC.00 rows th
The main problem that would have to be solved to convert this to
an InitPlan is what to do if the subselect returns a huge number
of tuples ... with the current implementation, since we scan the
tuples one at a time, there's no problem, but if we try to store
all the tuples we could run out of memory.
regards, tom lane
------------------------------
Date: Thu, 8 Jul 1999 15:41:56 -0300
From: "Gilson Costa" <gpcosta@ruralsp.com.br>
Subject: Referential Integrity
I am a novice in PostgreSQL.
How can I impose referential integrity to my tables?
------------------------------
Date: Thu, 8 Jul 1999 22:36:31 +0200 (CEST)
From: Engard Ferenc <fery@pons.sote.hu>
Subject: offtopic: odbc C frontend for linux?
Hi all,
I need to connect to odbc data sources with a linux C prg. Can I do
it? (And how? ;-)
Thx:
Circum
PS: There is a tclodbc package, did anybody use that? Experiences?
__ @
/ \ _ _ Engard Ferenc
l | ( \ / | | (\/) mailto:s-fery@kkt.sote.hu
\__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery
------------------------------
Date: Thu, 08 Jul 1999 13:38:26 -0700
From: Steven Bradley <sbradley@llnl.gov>
Subject: Re: [SQL] Referential Integrity
PostgreSQL does not implement declarative referential integrity. It lets
you specify foreign key constraints in table definitions, but does nothing
with them. As such, you have to resort to writing triggers to implement the
traditional RI concerns (e.g.: mandatory parent, cascade delete, etc...).
At 03:41 PM 7/8/99 -0300, you wrote:
>I am a novice in PostgreSQL.
>How can I impose referential integrity to my tables?
>
>
>
>
Steven Bradley
Lawrence Livermore National Laboratory
PO Box 808
Livermore, California 94550
(925) 423-2101 sbradley@llnl.gov
------------------------------
Date: Thu, 8 Jul 1999 22:53:06 -0700
From: "Stephen Boyle" <yd80@dial.pipex.com>
Subject: Re: [SQL] offtopic: odbc C frontend for linux?
I haven't done it but have read how to do this in 'The Linux Database p323 -
p366' - Fred Butzen and Dorothy Forbes ISBN 1-55828-491-5.
'a fine reference manual for ODBC....Lauri Savioja' apparently is available
@ www.cs.hut.fi/~las
also www.openlinksw.com for odbc driver/client.
HIH
------------------------------
Date: Thu, 8 Jul 1999 23:52:39 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] offtopic: odbc C frontend for linux?
>
> Hi all,
>
> I need to connect to odbc data sources with a linux C prg. Can I do
> it? (And how? ;-)
Not that offtopic IMHO.
Sybase offers an ODBC client library (ctlib). This is AFAIK freely available for Linux (binary only and other OS
versions are told to cost money).
I never played around with it, but I've read that it could also contact other ODBC datasources like MS
SQL-Server.Might be worth looking at it.
Jan
- --
## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#
------------------------------
Date: Fri, 9 Jul 1999 11:51:46 +0900
From: Soon-Suk Kim <master@golden21.net>
Subject: subscribe
------------------------------
End of pgsql-sql-digest V1 #284
*******************************
