Undeliverable Message - Mailing list pgsql-sql
| From | |
|---|---|
| Subject | Undeliverable Message |
| Date | |
| Msg-id | vines.n4,8++jLVrA@SFRA0046. Whole thread Raw |
| List | pgsql-sql |
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 *******************************