[SQL] Inheritance
I have a table (A) with a few "sibbling" tables (B and C for instance). When I make the following query : select id from A* where [condition] pgsql returns ids taken from A, B or C (which is what I want). Is there a way to know to what table the id actually belong ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF
Re: [SQL] Inheritance
On 30 May 2000, Christophe Labouisse wrote: > I have a table (A) with a few "sibbling" tables (B and C for You probably mean "child" tables. > instance). When I make the following query : select id from A* where > [condition] pgsql returns ids taken from A, B or C (which is what I > want). Is there a way to know to what table the id actually belong ? No. We're kind of pondering a way to solve this. However, according to the SQL99 data model, the row is shared between A and B or C respectively, so when you select from a* then you get rows from "a", period. I know this doesn't help in practice, though. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[SQL] 7.0 weirdness
hi folks, this query works fine in 6.5 but screwie in 7.0 7.0 gm=> SELECT DISTINCT gid FROM members gm-> WHERE active = 't' gm-> AND (gender = 0 gm-> AND (wantrstypemale LIKE '%Short Term%' gm-> OR wantrstypemale like '%Marriage%' gm-> OR wantrstypemale like '%Long Term%' gm-> OR wantrstypemale like '%Penpal%' gm-> OR wantrstypemale like '%Activity Partner%') gm-> ) order by created desc; ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list gm=> any idea's ? jeff
RE: [SQL] 7.0 weirdness
It seems to me that it was lack of control in 6.5 version... For one "gid", you may have several "created" values, so Postgres is not able to decide which value must be taken and ordered Simple example gid created 11 13 22 In which order is Postgres supposed to give the data??? Patrick Fiche -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de Jeff MacDonald Envoyé : mardi 30 mai 2000 14:28 À : [EMAIL PROTECTED]; [EMAIL PROTECTED] Objet : [SQL] 7.0 weirdness hi folks, this query works fine in 6.5 but screwie in 7.0 7.0 gm=> SELECT DISTINCT gid FROM members gm-> WHERE active = 't' gm-> AND (gender = 0 gm-> AND (wantrstypemale LIKE '%Short Term%' gm-> OR wantrstypemale like '%Marriage%' gm-> OR wantrstypemale like '%Long Term%' gm-> OR wantrstypemale like '%Penpal%' gm-> OR wantrstypemale like '%Activity Partner%') gm-> ) order by created desc; ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list gm=> any idea's ? jeff
RE: [SQL] 7.0 weirdness
gid is unique.. jeff On Tue, 30 May 2000, Patrick FICHE wrote: > It seems to me that it was lack of control in 6.5 version... > For one "gid", you may have several "created" values, so Postgres is not > able to decide which value must be taken and ordered > > Simple example > gid created > 11 > 13 > 22 > > In which order is Postgres supposed to give the data??? > > > Patrick Fiche > -Message d'origine- > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part > de Jeff MacDonald > Envoyé : mardi 30 mai 2000 14:28 > À : [EMAIL PROTECTED]; [EMAIL PROTECTED] > Objet : [SQL] 7.0 weirdness > > > hi folks, > > this query works fine in 6.5 but screwie in 7.0 > > 7.0 > > gm=> SELECT DISTINCT gid FROM members > gm-> WHERE active = 't' > gm-> AND (gender = 0 > gm-> AND (wantrstypemale LIKE '%Short Term%' > gm-> OR wantrstypemale like '%Marriage%' > gm-> OR wantrstypemale like '%Long Term%' > gm-> OR wantrstypemale like '%Penpal%' > gm-> OR wantrstypemale like '%Activity Partner%') > gm-> ) order by created desc; > ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list > gm=> > > > any idea's ? > > jeff > > > >
Re: [SQL] 7.0 weirdness
Hi jeff, I'm not sure but may be that's because you are using select distinct and so there would be a few rows with same "gid" but different "created" fields in your table . And PG does not know which one to select and compare for ORDER BY clause. If that ,you would need to change the table structure to a better normal form. Regards , Omid Omoomi >From: Jeff MacDonald <[EMAIL PROTECTED]> >Reply-To: Jeff MacDonald <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED], [EMAIL PROTECTED] >Subject: [SQL] 7.0 weirdness >Date: Tue, 30 May 2000 09:28:11 -0300 (ADT) > >hi folks, > >this query works fine in 6.5 but screwie in 7.0 > >7.0 > >gm=> SELECT DISTINCT gid FROM members >gm-> WHERE active = 't' >gm-> AND (gender = 0 >gm-> AND (wantrstypemale LIKE '%Short Term%' >gm-> OR wantrstypemale like '%Marriage%' >gm-> OR wantrstypemale like '%Long Term%' >gm-> OR wantrstypemale like '%Penpal%' >gm-> OR wantrstypemale like '%Activity Partner%') >gm-> ) order by created desc; >ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target >list >gm=> > > >any idea's ? > >jeff > > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Re: [SQL] 7.0 weirdness
gid is unique.. it's a serial.. funny thing is tho this worked on 6.5 oh well thanks for the info. jeff On Tue, 30 May 2000, omid omoomi wrote: > Hi jeff, > I'm not sure but may be that's because you are using select distinct and so > there would be a few rows with same "gid" but different "created" fields in > your table . And PG does not know which one to select and compare for ORDER > BY clause. If that ,you would need to change the table structure to a better > normal form. > Regards , > Omid Omoomi > > > >From: Jeff MacDonald <[EMAIL PROTECTED]> > >Reply-To: Jeff MacDonald <[EMAIL PROTECTED]> > >To: [EMAIL PROTECTED], [EMAIL PROTECTED] > >Subject: [SQL] 7.0 weirdness > >Date: Tue, 30 May 2000 09:28:11 -0300 (ADT) > > > >hi folks, > > > >this query works fine in 6.5 but screwie in 7.0 > > > >7.0 > > > >gm=> SELECT DISTINCT gid FROM members > >gm-> WHERE active = 't' > >gm-> AND (gender = 0 > >gm-> AND (wantrstypemale LIKE '%Short Term%' > >gm-> OR wantrstypemale like '%Marriage%' > >gm-> OR wantrstypemale like '%Long Term%' > >gm-> OR wantrstypemale like '%Penpal%' > >gm-> OR wantrstypemale like '%Activity Partner%') > >gm-> ) order by created desc; > >ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target > >list > >gm=> > > > > > >any idea's ? > > > >jeff > > > > > > > > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com >
RE: [SQL] 7.0 weirdness
If so then why do you need to use "select distinct " ?! BTW, I just tested on my 6.5.2, This query : "Select distinct f1 from table1 order by f2 " works the same as this one: "select distinct f1,f2 from table1" >From: Jeff MacDonald <[EMAIL PROTECTED]> >Reply-To: Jeff MacDonald <[EMAIL PROTECTED]> >To: Patrick FICHE <[EMAIL PROTECTED]> >CC: Jeff MacDonald <[EMAIL PROTECTED]>, Pgsql-Sql <[EMAIL PROTECTED]> >Subject: RE: [SQL] 7.0 weirdness >Date: Tue, 30 May 2000 10:46:30 -0300 (ADT) > >gid is unique.. > >jeff > >On Tue, 30 May 2000, Patrick FICHE wrote: > > > It seems to me that it was lack of control in 6.5 version... > > For one "gid", you may have several "created" values, so Postgres is not > > able to decide which value must be taken and ordered > > > > Simple example > > gid created > > 11 > > 13 > > 22 > > > > In which order is Postgres supposed to give the data??? > > > > > > Patrick Fiche > > -Message d'origine- > > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part > > de Jeff MacDonald > > Envoyé : mardi 30 mai 2000 14:28 > > À : [EMAIL PROTECTED]; [EMAIL PROTECTED] > > Objet : [SQL] 7.0 weirdness > > > > > > hi folks, > > > > this query works fine in 6.5 but screwie in 7.0 > > > > 7.0 > > > > gm=> SELECT DISTINCT gid FROM members > > gm-> WHERE active = 't' > > gm-> AND (gender = 0 > > gm-> AND (wantrstypemale LIKE '%Short Term%' > > gm-> OR wantrstypemale like '%Marriage%' > > gm-> OR wantrstypemale like '%Long Term%' > > gm-> OR wantrstypemale like '%Penpal%' > > gm-> OR wantrstypemale like '%Activity Partner%') > > gm-> ) order by created desc; > > ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target >list > > gm=> > > > > > > any idea's ? > > > > jeff > > > > > > > > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Re: [HACKERS] Re: [SQL] 7.0 weirdness
Hi, Jeff MacDonald: > gid is unique.. it's a serial.. > Then there is no point in using "DISTINCT" in the first place, is there? > funny thing is tho this worked on 6.5 It happened to work because your gid is unique. But in the general case, it can't work. Consider this table: gid created X 1 Y 2 X 3 Now, should your query's result be gid X Y or should it be gid Y X ? And since the typical implementation throws away non-selected-for columns before UNIQUEing, how should it be able to sort anything? -- Matthias Urlichs | noris network GmbH | [EMAIL PROTECTED] | ICQ: 20193661 The quote was selected randomly. Really. |http://smurf.noris.de/ -- Problem mit cookie: File exists
Re: [SQL] 7.0 weirdness
Jeff MacDonald <[EMAIL PROTECTED]> writes: > gid is unique.. it's a serial.. Mph. If you assume that gid is unique then the query would give well-defined results, but if you know it's unique then why don't you just leave off the DISTINCT? > funny thing is tho this worked on 6.5 No, 6.5 merely failed to notice that it was giving you undefined results. regards, tom lane
Re: [SQL] remove line type?
for your perusal... >From: Bruce Momjian <[EMAIL PROTECTED]> >Subject: Re: [SQL] remove line type? >To: mikeo <[EMAIL PROTECTED]> >Date: Tue, 30 May 2000 10:48:02 -0400 (EDT) > >That is very strange. I would send it to the mailing lists. > >> hi bruce, >> we've run into a problem after having deleted the line type. >> when we attempt to query a table by column which is defined as float8 >> we get this error: >> >> \d test1 >>Table "test1" >> Attribute |Type | Modifier >> ---+-+-- >> tfap_id | float8 | >> tfap_name | varchar(50) | >> groupid | integer | >> groupdesc | varchar(50) | >> switch| varchar(50) | >> >> select * from test1 where tfap_id = 49232; >> ERROR: Unable to locate type oid 628 in catalog >> >> if the column is defined as an integer we get the desired results: >> >> spectl=# \d topflow_application >> Table "topflow_application" >> Attribute |Type | Modifier >> ---+-+-- >> tfap_id | integer | >> tfap_name | varchar(50) | >> groupid | integer | >> groupdesc | varchar(50) | >> switch| varchar(50) | >> >> select * from topflow_application where tfap_id = 049232; >> tfap_id | tfap_name | groupid |groupdesc| >> switch >> -+---+-+-+-- >> - >>49232 | x | 31 | Remote Control Services | >> 111.111.1.111 >> >> now, the programmer who created test1 table made that column a float by >> accident >> but that could mean trouble down the road for us as we do use float for >> some other columns. >> you can still select from test1 as long as you don't reference the float >> column in the where >> clause. >> >> oid 628 is the oid for the line row in the pg_type table. is there >> something else that we >> need to do or is deleting this type not a good idea after all? >> >> thanks, >> mikeo >> >> >> >> >> At 03:04 PM 5/17/00 -0400, you wrote: >> >If you do it in template1 database after initdb, all new databases will >> >not have that type either. >> > >> >> that worked!!! thanks! >> >> >> >> mikeo >> >> >> >> >> >> At 02:51 PM 5/17/00 -0400, Bruce Momjian wrote: >> >> >I guess you could remove the line type from the pg_type table and see if >> >> >that helps. >> >> > >> >> >> hi, >> >> >> we're looking at migrating from ORACLE to postgres in the >> >> >> very near future and we've run into a small problem. there's >> >> >> a data type defined "LINE". we have named one of our tables >> >> >> as "LINE" also and it would require a great deal of code >> >> >> changes to rename that table. is it possible to simply >> >> >> "turn off" the line type? any help is appreciated. >> >> >> >> >> >> thanks, >> >> >>mikeo >> >> > > >-- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 >
[SQL] Pg/PLSQL Errors!!
I am running PostgreSQL 6.5.3 on RedHat Linux 6.1 on a PC. I am trying to use Pg/PLSQL. I even tried being conservative enough to copy the example code from http://www.postgresql.org/docs/user/c40874340.htm as follows: CREATE FUNCTION add_one (int4) RETURNS int4 AS ' BEGIN RETURN $1 + 1; END; ' LANGUAGE 'plpgsql'; though, even that results in "ERROR: Unrecognized language specified in a CREATE FUNCTION: 'pl-pgsql'. Recognized languages are sql, C, internal and the created procedural languages." I have tried variants including PLSQL,PG/PLSQL,PL/SQL,PGSQL and even 'internal procedural language(s)' and 'created procedural language(s)' though with the same error. Does anyone know of the name of the postgreSQL's procedural language of form like CREATE function funcName(arguments) returns returntype AS 'BEGIN statement block END;' LANGUAGE '??'; Many Thanks! Get your free email from AltaVista at http://altavista.iname.com
Re: [SQL] Pg/PLSQL Errors!!
On Tue, 30 May 2000 [EMAIL PROTECTED] wrote: > I am running PostgreSQL 6.5.3 on RedHat Linux 6.1 on a PC. > I am trying to use Pg/PLSQL. > > I even tried being conservative enough to copy the example code from > http://www.postgresql.org/docs/user/c40874340.htm as follows: > CREATE FUNCTION add_one (int4) RETURNS int4 AS ' > BEGIN > RETURN $1 + 1; > END; > ' LANGUAGE 'plpgsql'; > > > though, even that results in "ERROR: Unrecognized language specified in a CREATE >FUNCTION: 'pl-pgsql'. Recognized languages are sql, C, internal and > the created procedural languages." > > I have tried variants including PLSQL,PG/PLSQL,PL/SQL,PGSQL and even 'internal > procedural language(s)' and 'created procedural language(s)' though with the same >error. > > Does anyone know of the name of the postgreSQL's procedural language of form like > CREATE function funcName(arguments) returns returntype AS 'BEGIN > statement block END;' LANGUAGE '??'; Do you install this language in your DB? See the script 'createlang' in PG binary tree. Karel
Re: [SQL] Pg/PLSQL Errors!!
Hi You need to make the call_handler and create the language plpgsql. Assuming postgres is installed in /usr/local/pgsql Example: -- Setup -- define the languages and the associated handlers CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; On Tue, 30 May 2000 [EMAIL PROTECTED] wrote: > I am running PostgreSQL 6.5.3 on RedHat Linux 6.1 on a PC. > I am trying to use Pg/PLSQL. > > I even tried being conservative enough to copy the example code from > http://www.postgresql.org/docs/user/c40874340.htm as follows: > CREATE FUNCTION add_one (int4) RETURNS int4 AS ' > BEGIN > RETURN $1 + 1; > END; > ' LANGUAGE 'plpgsql'; > > > though, even that results in "ERROR: Unrecognized language specified in a CREATE >FUNCTION: 'pl-pgsql'. Recognized languages are sql, C, internal and > the created procedural languages." > > I have tried variants including PLSQL,PG/PLSQL,PL/SQL,PGSQL and even 'internal > procedural language(s)' and 'created procedural language(s)' though with the same >error. > > Does anyone know of the name of the postgreSQL's procedural language of form like > CREATE function funcName(arguments) returns returntype AS 'BEGIN > statement block END;' LANGUAGE '??'; > > Many Thanks! > > > > > Get your free email from AltaVista at http://altavista.iname.com >
Re: [SQL] Pg/PLSQL Errors!!
did you install the procedural language correctly? under RedHat, you have to do the following: (the location of the handler is different because of packaging conventions) CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; On Tue, 30 May 2000 [EMAIL PROTECTED] wrote: > I am running PostgreSQL 6.5.3 on RedHat Linux 6.1 on a PC. > I am trying to use Pg/PLSQL. > > I even tried being conservative enough to copy the example code from > http://www.postgresql.org/docs/user/c40874340.htm as follows: > CREATE FUNCTION add_one (int4) RETURNS int4 AS ' > BEGIN > RETURN $1 + 1; > END; > ' LANGUAGE 'plpgsql'; > > > though, even that results in "ERROR: Unrecognized language specified in a CREATE >FUNCTION: 'pl-pgsql'. Recognized languages are sql, C, internal and > the created procedural languages." > > I have tried variants including PLSQL,PG/PLSQL,PL/SQL,PGSQL and even 'internal > procedural language(s)' and 'created procedural language(s)' though with the same >error. > > Does anyone know of the name of the postgreSQL's procedural language of form like > CREATE function funcName(arguments) returns returntype AS 'BEGIN > statement block END;' LANGUAGE '??'; > > Many Thanks! > > > > > Get your free email from AltaVista at http://altavista.iname.com >
Re: [SQL] Pg/PLSQL Errors!!
[EMAIL PROTECTED] writes: > though, even that results in "ERROR: Unrecognized language specified in a CREATE >FUNCTION: 'pl-pgsql'. Recognized languages are sql, C, internal and > the created procedural languages." Uh, did you run the "createlang" script to install plpgsql into your database? It's not installed by default ... regards, tom lane
[SQL] Insert with replace?
What is the normal way to preform a "insert with replace" on a row in a relational database (specifically postgresql)? I've got a database that (logically) represents a set of items, where each item has a primary key represented by two columns (id and name). If that key isn't in the database, I want to insert the item. If that key is in the database, I want to update the item. I have no need to retain the previous values. The interface is through jdbc. I assume I could try the update first and then, if the update generates an exception, perform an insert, but is there a way to do the same with a single command? (The data is such that the update will fail in the majority of cases.) Thanks! -- Steve Wampler- SOLIS Project, National Solar Observatory [EMAIL PROTECTED]
[SQL] Function-based index not used in a simple query
Hi all,
I have run into a problem trying to optimize a select from a single table.
Due to the nature of the queries our frontends issue most of the time, we
have created an index that reduces the processing time a lot. The problem
(index not being used) arose when I tried to do a select with non-constant
parameters to the WHERE condition.
The table definition looks like this:
CREATE TABLE vals (
timestamp timestamp NOT NULL,
agent varchar(15) NOT NULL,
var varchar(64) NOT NULL,
val textNOT NULL
);
The index definition is based on a user-defined function:
CREATE FUNCTION trunc_to_day(timestamp) RETURNS timestamp AS '
DECLARE tstamp ALIAS FOR $1;
BEGIN
RETURN date_trunc(''day'', tstamp);
END;
' LANGUAGE 'plpgsql';
CREATE INDEX vals_days
ON vals (trunc_to_day(timestamp) timestamp_ops);
A typical query looks like this (additional conditions removed from the
WHERE condition as well as additional GROUP BY and ORDER BY clauses):
SELECT *
FROM vals
WHERE trunc_to_day(timestamp) = '28.5.2000';
Explain on this query produces:
Index Scan using vals_days on vals (cost=0.00..8.16 rows=10 width=44)
Now, when I try to do the same with a slightly changed comparison expression
in the WHERE clause, an optimizer decides to sequentially scan the table:
SELECT *
FROM vals
WHERE trunc_to_day(timestamp) = trunc_to_day('28.5.2000');
Seq Scan on vals (cost=0.00..27.50 rows=10 width=44)
Actually, the problem first appeared in a stored procedure:
CREATE FUNCTION detector(timestamp, varchar) RETURNS float AS '
DECLARE
check_time ALIAS FOR $1;
check_agent ALIAS FOR $2;
from_time timestamp;
to_time timestamp;
from_day timestamp;
to_daytimestamp;
rssi_var vars.var%TYPE;
avg_rssi float;
BEGIN
from_time = check_time;
from_day = trunc_to_day(from_time);
to_time = check_time + ''1 day''::interval;
to_day= trunc_to_day(to_time);
SELECT INTO rssi_var var || ''%''
FROM vars
WHERE name = ''brzAvrgRssi'';
SELECT INTO avg_rssi AVG(val::float)
FROM vals
WHERE trunc_to_day(timestamp) BETWEEN from_day AND to_day AND
timestamp BETWEEN from_time AND to_time AND
agent = check_agent AND
var LIKE rssi_var;
IF avg_rssi IS NULL THEN
RAISE EXCEPTION ''There are no values for % and %.'',
check_time, check_agent;
END IF;
RETURN avg_rssi;
END;
' LANGUAGE 'plpgsql';
Sorry for a lengthy listing, but I didn't want to omit something important
accidentally. It seems the optimizer chooses to seq-scan whenever there is
anything else than a simple string constant in the WHERE condition.
After reading the thread ``Index not used in functions in 7.0'' two weeks ago
in this list, I have experimented with typecasting extensively, trying to add
``::timestamp'' wherever possible to the query, but with no success.
The PostgreSQL version is 7.0.0 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66.
Thanks in advance for any advices!
Orbis
--
Rostislav Opocensky <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111
Re: [GENERAL] Re: [SQL] remove line type?
mikeo <[EMAIL PROTECTED]> writes: >>> we've run into a problem after having deleted the line type. >>> when we attempt to query a table by column which is defined as float8 >>> we get this error: >>> >>> select * from test1 where tfap_id = 49232; >>> ERROR: Unable to locate type oid 628 in catalog Interesting. I get: bust=# create table foo (f1 int, f2 float8); CREATE bust=# insert into foo values(1,2.5); INSERT 148298 1 bust=# select * from foo; f1 | f2 +- 1 | 2.5 (1 row) bust=# drop type line; DROP bust=# select * from foo; f1 | f2 +- 1 | 2.5 (1 row) bust=# select * from foo where f2 = 2.5; f1 | f2 +- 1 | 2.5 (1 row) bust=# select * from foo where f2 < 3; f1 | f2 +- 1 | 2.5 (1 row) bust=# select * from foo where f2 = 3; ERROR: Unable to locate type oid 628 in catalog It looks to me like the problem appears when the parser has to resolve an ambiguous operator. (Since there isn't a "float8 = int" operator, this last case requires some smarts to figure out what to do.) Presumably there is a line = line operator still in the system, and it doesn't surprise me a whole lot that this error would pop up if the parser had occasion to scan through the '=' operators looking for a possible match and came across that one. Let's see: bust=# select * from pg_operator where oprname = '=' and bust-# (oprleft = 628 or oprright = 628); oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin -+--+-+-+---++-+--+---++---+++-+-+--- = | 256 | 0 | b | t | f | 628 | 628 |16 | 1616 | 0 | 0 | 0 | line_eq | eqsel | eqjoinsel (1 row) bust=# delete from pg_operator where oprname = '=' and bust-# (oprleft = 628 or oprright = 628); DELETE 1 bust=# select * from foo where f2 = 3; f1 | f2 + (0 rows) Yup, looks like that's the problem. It's probably not good that DROP TYPE only zaps the pg_type entry and doesn't go hunting for stuff that depends on it. In the meantime you might want to do delete from pg_operator where oprleft = 628 or oprright = 628; and perhaps something similar for pg_proc, although name collisions for functions are probably less of a problem there. regards, tom lane
Re: [GENERAL] Re: [SQL] remove line type?
thanks much. that now works! :) At 01:41 PM 5/30/00 -0400, Tom Lane wrote: >mikeo <[EMAIL PROTECTED]> writes: we've run into a problem after having deleted the line type. when we attempt to query a table by column which is defined as float8 we get this error: select * from test1 where tfap_id = 49232; ERROR: Unable to locate type oid 628 in catalog > >Interesting. I get: > >bust=# create table foo (f1 int, f2 float8); >CREATE >bust=# insert into foo values(1,2.5); >INSERT 148298 1 >bust=# select * from foo; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# drop type line; >DROP >bust=# select * from foo; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# select * from foo where f2 = 2.5; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# select * from foo where f2 < 3; > f1 | f2 >+- > 1 | 2.5 >(1 row) > >bust=# select * from foo where f2 = 3; >ERROR: Unable to locate type oid 628 in catalog > >It looks to me like the problem appears when the parser has to resolve >an ambiguous operator. (Since there isn't a "float8 = int" operator, >this last case requires some smarts to figure out what to do.) >Presumably there is a line = line operator still in the system, and >it doesn't surprise me a whole lot that this error would pop up if the >parser had occasion to scan through the '=' operators looking for a >possible match and came across that one. Let's see: > >bust=# select * from pg_operator where oprname = '=' and >bust-# (oprleft = 628 or oprright = 628); > oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin >-+--+-+-+---++- +--+---++---+++- +-+--- > = | 256 | 0 | b | t | f | 628 | 628 |16 | 1616 | 0 | 0 | 0 | line_eq | eqsel | eqjoinsel >(1 row) > >bust=# delete from pg_operator where oprname = '=' and >bust-# (oprleft = 628 or oprright = 628); >DELETE 1 >bust=# select * from foo where f2 = 3; > f1 | f2 >+ >(0 rows) > >Yup, looks like that's the problem. > >It's probably not good that DROP TYPE only zaps the pg_type entry and >doesn't go hunting for stuff that depends on it. In the meantime you >might want to do > >delete from pg_operator where oprleft = 628 or oprright = 628; > >and perhaps something similar for pg_proc, although name collisions for >functions are probably less of a problem there. > > regards, tom lane >
Re: [SQL] Function-based index not used in a simple query
Rostislav Opocensky <[EMAIL PROTECTED]> writes:
> SELECT *
> FROM vals
> WHERE trunc_to_day(timestamp) = trunc_to_day('28.5.2000');
>
> Seq Scan on vals (cost=0.00..27.50 rows=10 width=44)
The problem here is that the optimizer will only consider an indexscan
for a clause that looks like index_key OP constant. It doesn't think
that trunc_to_day('28.5.2000') is a constant, because you haven't told
it that it can pre-evaluate that function call --- and for all it knows,
the function might generate different values when evaluated at different
rows of the table, rendering an indexscan incorrect. (Consider a
function like random() ...) If you mark trunc_to_day() as a "cachable"
function then the system will know it's safe to collapse the righthand
side to a constant:
CREATE FUNCTION trunc_to_day(timestamp) RETURNS timestamp AS '
DECLARE tstamp ALIAS FOR $1;
BEGIN
RETURN date_trunc(''day'', tstamp);
END;
' LANGUAGE 'plpgsql'
WITH (isCachable);
This is a little bit of a cheat for this function, because what
isCachable actually implies is that the function's value depends
on the presented arguments *and nothing else*. I believe date_trunc
depends on the current TZ setting, so it's not really cachable.
You could possibly get away with it for your application, but you
might run into problems like this:
1. You write trunc_to_day('28.5.2000') in a table's column
default expression, stored rule, or some such. The system
decides it can fold that expression down to a simple constant,
giving you (say) 2000-05-20 00:00-04 --- ie, midnight in
whatever timezone you are using. That value gets stored in
the column or rule definition.
2. You then access the database while running with a different
TZ setting. You wonder why you don't get the answers you expected.
Come to think of it, your index on trunc_to_day() is fundamentally
risky because different backends running with different TZ settings
would generate different index entries for the "same" data entry.
For example, if you make an entry dated '28.5.2000' while running in
one timezone, and then try to query with "= trunc_to_day('28.5.2000')"
while running in another timezone, you won't match that entry if the
index is used, because the timestamp generated on-the-fly from
trunc_to_day('28.5.2000') won't equal the one stored in the index.
(Hmm ... I wonder if that suggests that we shouldn't allow indexes
to be built using functions that are not isCachable?)
You could avoid some of these risks by having trunc_to_day return
a 'date' rather than a 'timestamp', thereby removing at least some
of the timezone dependency from the index definition.
regards, tom lane
Re: [SQL] Use of index in 7.0 vs 6.5
Tom Lane wrote: > Ryan Bradetich <[EMAIL PROTECTED]> writes: > > This worked great! Is their a place I can change the default to 3? > > I do not want to change all the scripts to include this :) > > See src/include/optimizer/cost.h. However, I am currently thinking of > taking Hiroshi's advice instead. Lowering RANDOM_PAGE_COST seems like > a bad idea --- if anything, we might want to raise it ;-) > > Or how about changing current fudge factor ? > For example,from 0.5 to 0.2 which is the fudge factor of attdisbursion > calculation. > >> > >> Yes, that's another way --- and probably more defensible than changing > >> random_page_cost, now that I think about it. Unfortunately it's a > >> hardwired constant and so not as easily experimented with :-(. > > > Can you give me more information about this? I do not have a problem > > re-compiling the database and performing more testing if you would like. > > The fudge factor in question is currently 0.5, and is used in two places > in src/backend/utils/adt/selfuncs.c (looks like lines 193 and 212 in 7.0 > sources). I was thinking of dropping it to 0.25 or 0.1. > > regards, tom lane Tom and Hiroshi, I modified the backend to 0.1 and this has been working great! Thanks again for the suggestion, and I'll let you know if we run into a problem. Thanks again! Ryan
[SQL] does the' text' type cann't store more than 20,000char ?
hi .everyine when i want put a long file into a field with text type . i was warning the turple is too long and the table cann't open again.does the' text' type cann't store more than about 9,050 char ? thank you . xv hai
Re: [SQL] does the' text' type cann't store more than 20,000char ?
It can't be any larger than 8k (minus a bit of overhead). You can increase this now to 32k (again, minus the same overhead) by changing BLKSZ to 32k in the config.h header.. I'm successfully doing this in my database (which is pretty high-traffic and pretty large). Good luck! -Mitch - Original Message - From: xu hai <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 30, 2000 9:11 PM Subject: [SQL] does the' text' type cann't store more than 20,000char ? > hi .everyine > when i want put a long file into a field with text type . i was warning the turple is > too long and the table cann't open again.does the' text' type cann't store more than about 9,050 char ? > thank you . > xv hai >
Re: [HACKERS] Re: [SQL] aliases break my query
> At one time Bruce had made some patches to emit informative notice > messages about implicit FROM entries, but that got turned off again > for reasons that I forget... It was triggered with common cases from the "outer join" syntax. It took a while to track down since it was introduced while I was working on the syntax feature :( If it *really* needs to be put back in, then we should do so with a flag so we can disable the warning at compile time, run time, and/or in the outer join parser area. But imho sprinkling the parser with warnings for allowed syntax is heading the wrong direction. If it is legal, allow it. If it is illegal, disallow it. If it is confusing for some, but works fine for others, it shouldn't become "sort of legal" with a warning. - Thomas
[SQL] Re: Pg/PLSQL Errors!!
Tom Lane <[EMAIL PROTECTED]> writes: > Uh, did you run the "createlang" script to install plpgsql into your > database? It's not installed by default ... Why isn't it installed by default? Just asking.
Re: [SQL] Re: Pg/PLSQL Errors!!
SL Baur <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> Uh, did you run the "createlang" script to install plpgsql into your >> database? It's not installed by default ... > Why isn't it installed by default? Just asking. Paranoia, mostly --- we weren't sure we wanted it to be enabled without an explicit decision by the dbadmin. I'm not sure I can point to any compelling reasons why it'd be dangerous, but on the other side it's not proven to be totally safe either. pltcl and plperl don't get installed by default either, and those I definitely wouldn't agree to enabling by default... they don't have enough track record. plpgsql is more of a borderline call. regards, tom lane
