Re: [SQL] 7.0 weirdness
Hi Jeff! I think you need a solution, and not explains... Tom, and the others told the truth. You missed this query. > gid is unique.. it's a serial.. I give you two ways: 1) gid __realy__ unique -> DISTINCT is unnecessary. SELECT gid FROM members -- ... etc 2) gid not unique -> DISTINCT is not enough. ;( SELECT gid,MAX(created) -- or MIN or AVG ... any aggregate FROM members -- ... etc GROUP BY gid ORDER BY 2; -- second colunm > > >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 best regards -- nek;(
Re: [SQL] Function-based index not used in a simple query
On Tue, 30 May 2000, Tom Lane wrote:
> 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,
[...]
Dear Tom,
thank you for the precise explanation for my problem!
I'll consider having my index function return a `date'. Still one thing
remains unclear to me: why the optimizer doesn't use an indexscan in the
stored procedure I have attached to my previous post. The condition looks
like WHERE trunc_to_day(timestamp) BETWEEN var1 AND var2. var1 and var2
get their values from calling the `volatile' function trunc_to_day, but
from then on, their values can't be changed during the execution of the
query. Is it possible to give the optimizer a hint about it?
Best regards
Orbis
--
Rostislav Opocensky <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> +420 411 825144
Unreal Technology sro., Dobrin 118, 41301 Roudnice n. L. +420 411 825111
Re: [SQL] Function-based index not used in a simple query
Rostislav Opocensky <[EMAIL PROTECTED]> writes:
> On Tue, 30 May 2000, Tom Lane wrote:
>> 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
> I'll consider having my index function return a `date'. Still one thing
> remains unclear to me: why the optimizer doesn't use an indexscan in the
> stored procedure I have attached to my previous post. The condition looks
> like WHERE trunc_to_day(timestamp) BETWEEN var1 AND var2. var1 and var2
> get their values from calling the `volatile' function trunc_to_day, but
> from then on, their values can't be changed during the execution of the
> query. Is it possible to give the optimizer a hint about it?
Hmm, actually the optimizer should/does regard those as constants within
subsequent queries (internally they are Params instead of Consts, but
that's supposed to be OK). What I find here is that the optimizer does
consider an indexscan for this query, but there's a bug in its
selectivity estimation routine that causes it not to recognize the
BETWEEN clause as being a range restriction --- and that means it
produces a fairly high cost estimate for the indexscan. I still got
an indexscan plan for a small test table, but on a larger table you
might not get one.
I've applied the attached patch for 7.0.1 --- if you are in a hurry,
you may care to apply it to your local copy. It just tweaks the range-
query recognizer to accept Param as well as Const nodes.
regards, tom lane
*** src/backend/optimizer/path/clausesel.c.orig Tue May 30 00:26:44 2000
--- src/backend/optimizer/path/clausesel.c Wed May 31 11:38:53 2000
***
*** 120,129
Selectivity s2;
/*
!* See if it looks like a restriction clause with a constant. (If
!* it's not a constant we can't really trust the selectivity!) NB:
!* for consistency of results, this fragment of code had better
!* match what clause_selectivity() would do.
*/
if (varRelid != 0 || NumRelids(clause) == 1)
{
--- 120,131
Selectivity s2;
/*
!* See if it looks like a restriction clause with a Const or Param
!* on one side. (Anything more complicated than that might not
!* behave in the simple way we are expecting.)
!*
!* NB: for consistency of results, this fragment of code had better
!* match what clause_selectivity() would do in the cases it handles.
*/
if (varRelid != 0 || NumRelids(clause) == 1)
{
***
*** 134,174
get_relattval(clause, varRelid,
&relidx, &attno, &constval, &flag);
! if (relidx != 0 && (flag & SEL_CONSTANT))
{
/* if get_relattval succeeded, it must be an opclause
*/
! Oid opno = ((Oper *) ((Expr *)
clause)->oper)->opno;
! RegProcedure oprrest = get_oprrest(opno);
! if (!oprrest)
! s2 = (Selectivity) 0.5;
! else
! s2 = restriction_selectivity(oprrest, opno,
!
getrelid(relidx,
!
root->rtable),
!
attno,
!
constval, flag);
!
! /*
!* If we reach here, we have computed the same result
that
!* clause_selectivity would, so we can just use s2 if
it's
!* the wrong oprrest. But if it's the right oprrest,
add
!* the clause to rqlist for later processing.
!*/
! switch (oprrest)
{
! case F_SCALARLTSEL:
! addRangeClause(&rqlist, clause, flag,
true, s2);
! break;
! case F_SCALARGTSEL:
! addRangeClause(&rqlist, clause, flag,
false, s2);
! break;
!
[SQL] psql problem
Does anyone know why when I am in a particular DB as user postgres and use the following statement, why I get this error?" This is the statement; SELECT * FROM some_file where ID = 1; Error: ERROR: attribute 'id' not found Execution time 0.02 sec. But if I use the following statement, everything is returned fine. SELECT * FROM servlet_file; Thanks ahead of time, Rick Parker
Re: [SQL] psql problem
What is the definition of the table 'some_table'?? Regards, Ed Loehr Rick Parker wrote: > > Does anyone know why when I am in a particular DB as user postgres and use > the following statement, why I get this error?" > > This is the statement; > SELECT * FROM some_file where ID = 1; > > > Error: ERROR: attribute 'id' not found > Execution time 0.02 sec. > > But if I use the following statement, everything is returned fine. > > SELECT * FROM servlet_file; > > Thanks ahead of time, > > Rick Parker
Re: [SQL] psql problem
> > Does anyone know why when I am in a particular DB as user postgres and use > > the following statement, why I get this error?" > > > > This is the statement; > > SELECT * FROM some_file where ID = 1; > > > > -- -- > > Error: ERROR: attribute 'id' not found > > Execution time 0.02 sec. That indicates that you have no column named 'id'.. > > But if I use the following statement, everything is returned fine. > > > > SELECT * FROM servlet_file; That's a totally different query which would yeild totally different results.. -Mitch
Re: [HACKERS] Re: [SQL] 7.0 weirdness
thanks for the hlep guys.. for those that are curious, the distinct is tehr cause it's someone elses code that i'm workig on .. :) have to kick out the bug's// jeff On Tue, 30 May 2000, Matthias Urlichs wrote: > 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 >
[SQL] Inheritance heirarchy
I'm not entirely sure what the correct term for this would be, but I'll just call it an inhertiance heirarchy for lack of a more correct name. Anyway, I'd like some pointers on modeling a heirarchical structure where a given "parent" row can have multiple "children" rows, and each "child" has either zero or one "parent"'s. Each "child" can also be a "parent" to many more "children". I'll need to traverse the hierarchy from a given parent down through its children and from a child throw all of its parents. I'm looking to distribute a quantity of "points" from the children back up to the parent(s) in accordance with a predefined referral scheme. The distribution will be based on predefined percentages (as in, a parent receives 5% of the children's share). I think the closest existing structure that I'm familiar with that follows these rules would be a so-called "pyramid scam", but that has a nasty, dubious connotation, and it's not why I'm trying to do. Anyway, any pointers or examples would be deeply appreciate. I'm not an SQL or data modeling newbie, but I'm sure you'll agree this is a tad on the more advanced side. Oh, and Joe Celko's book "SQL for Smarties" is already on order. =) -- Jon Parise ([EMAIL PROTECTED]) . Rochester Inst. of Technology http://www.csh.rit.edu/~jon/ : Computer Science House Member
[SQL] Re: create constraint trigger
Kyle Bateman wrote: Hi Jan: But when I create it with "create constraint trigger" as shown next, the trigger doesn't seem to be invoked. It says it is created, but it allows data to pass that would not be allowed with the "create trigger." So I'm assuming I'm doing something wrong. create constraint trigger prd_part_tr_iu after insert or update on prd_parm deferrable initially deferred for each row execute procedure prd_parm_tf_iu(); OK, here's a reply to my own post. I've got the constraint trigger working now. The problem was that it was never getting dropped. I still don't know how to drop the constraint. I thought by dropping the trigger function it would get the idea. When I ran the create constraint again, it said it was created (no errors) so I figured everything was OK, but it was still running the previously defined constraint (apparently). I dropped the table and started from scratch and it seems to work fine now. So is there a way to "drop constraint trigger" without having to drop the table? Kyle begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard
[SQL] create view security
Hi All, I am trying to enable my web site to create views in a database owned by a user called ddirpts. Now, the web server runs as nobody, and nobody has a user and database set up in Postgres.. But the problem is, whenever I have a cgi program issue a create view query on the ddirpts database, the backend reports Parse error at or near "". I can however issue create view commands as ddirpts. I was thinking this might be a security restriction, wherein no user can create views/tables in another user's database without some kind of special permission--problem is, how do I create the permission? I am using 6.3 in this case. _ Ted Wallingford Manager of Information Technology Independence Excavating, Inc. Precision Environmental Co. Independence Communications, Inc. www.indexc.com > -Original Message- > From: Thomas Lockhart [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, May 30, 2000 10:04 PM > To: Tom Lane > Cc: Peter Eisentraut; Joseph Shraibman; [EMAIL PROTECTED]; > [EMAIL PROTECTED] > Subject: 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 > Wallingford, Ted.vcf
[SQL] question on diagnostics
Hi all,
does anyone have a clue what this diagnostic from psql (v 7.0) means:
ERROR: aggregate function in qual must be argument of boolean operator
I got it from this query:
SELECT *
FROM last_payment NATURAL INNER JOIN admin_info
WHERE date_part ('month', age(last_payment.date, timestamp 'now')) >= 3 AND
balance > 0;
last_payment is a view of a natural join of 3 tables with an aggregation:
CREATE VIEW last_payment(ssn, cust_name, cust_addr, balance, date) AS
SELECT customer.ssn, cust_name, cust_addr, balance, MAX(date)
FROM customer, journal, payment_info
WHERE customer.ssn = journal.ssn AND
journal.entry_num = payment_info.entry_num
GROUP BY customer.ssn, cust_name, cust_addr, balance;
oh, btw this select refused to use an SQL natural join among the 3 relations -
the server gives up and disconnects without warning.
admin_info is a normal table.
the idea being to get tuples timestamped 3 or more months ago. I get the
impression both 'age' and 'date_part' functions are causing the problem - just a
date comparison without them works fine.
thanks for any ideas
cheers
alex
Re: [SQL] question on diagnostics
"Alexander H. Iliev" <[EMAIL PROTECTED]> writes: > does anyone have a clue what this diagnostic from psql (v 7.0) means: > ERROR: aggregate function in qual must be argument of boolean operator Aggregates and GROUP BY in views have a lot of restrictions at the moment, and this is one of them: the rewriter has a problem with figuring out how to substitute "MAX(date)" for "last_payment.date" in the context of your age() function call. We're planning a thorough rewrite of the view-supporting code for 7.2, and most of these problems should go away at that point. In the short run you would be well advised to avoid grouped views except in the simplest contexts. I think this example could be rewritten with no view, or you could push the "date_part(...)" call into the view as another column. > oh, btw this select refused to use an SQL natural join among the 3 > relations - the server gives up and disconnects without warning. That sounds like a garden-variety bug. I'd be willing to look at it if I had a complete example to follow, but I don't want to try to reverse-engineer your table definitions... regards, tom lane
Re: [SQL] question on diagnostics
> > oh, btw this select refused to use an SQL natural join among the 3 > > relations - the server gives up and disconnects without warning. > > That sounds like a garden-variety bug. I'd be willing to look at it > if I had a complete example to follow, but I don't want to try to > reverse-engineer your table definitions... a join with 3 tables never seems to work: test=# create table a(a int); CREATE test=# create table b(a int); CREATE test=# create table c(a int); CREATE test=# insert into a values (1); INSERT 23734 1 test=# insert into b values (1); INSERT 23744 1 test=# insert into c values (1); INSERT 23736 1 test=# select * test-# from a, b, c test-# where a.a = b.a AND test-# b.a = c.a; a | a | a ---+---+--- 1 | 1 | 1 (1 row) test=# select * from a natural inner join b natural inner join c; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. alex
Re: [SQL] question on diagnostics
"Alexander H. Iliev" <[EMAIL PROTECTED]> writes: > test=# select * from a natural inner join b natural inner join c; > pqReadData() -- backend closed the channel unexpectedly. Yup, I see it too. I had fixed some problems in SQL join syntax since 7.0 release, but evidently not this one :-(. Will look into it. Thanks for the example. regards, tom lane
[SQL] textpos() in postgreSQL 7.0
Hi I notice that string function textpos() doesn't exist in postgreSQL 7.0. I have some plpgsql functions written for 6.5.2 that use textpos() and when I tried to use them in postgreSQL 7.0 I'v got error. Should I compile postgreSQL 7.0 with more options than -enable locale ?!!? Thanks for any help. Adam
[SQL] Re: [ADMIN] create view security
Wallingford, Ted writes: > I am using 6.3 in this case. I'm sorry but that is pre-historic era around here and no one really remembers what the problems might have been back then (other than that they were surely plenty). Upgrading might be your best bet on all fronts. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[SQL] Re: [GENERAL] TOP SESSIONS?
mikeo wrote: > > hi, > in oracle you would use these two cursors to determine who was connected and > what they were doing. > > select distinct s.sid sid, s.serial# serial, s.status status, osuser, spid , > count(o.sid) counter, s.username username, s.program program, sql_address > from v$session s, v$open_cursor o, v$process p > where s.sid = o.sid(+) > and paddr = addr > group by s.sid,s.serial#, s.status , osuser, spid ,s.username, s.program ,sql_address > order by 1,3 > > select distinct name > from sys.audit_actions, v$sqltext > where address = ? > and action = command_type > > does anyone know what tables in postgres would give me the same or similar >information? > > TIA, > mikeo PostgreSQL attempts to communicate what queries are being performed by setting the process information in the connected backend when processing a SQL statement, much like sendmail. You should be able to determine who's connected and what they're doing with something like: ps axf You'll see who's connected to what database from what machine and the type of query being executed. I don't know of any tables in PostgreSQL which would provide similar information. Hope that helps, Mike Mascari
[SQL] TOP SESSIONS?
hi, in oracle you would use these two cursors to determine who was connected and what they were doing. select distinct s.sid sid, s.serial# serial, s.status status, osuser, spid , count(o.sid) counter, s.username username, s.program program, sql_address from v$session s, v$open_cursor o, v$process p where s.sid = o.sid(+) and paddr = addr group by s.sid,s.serial#, s.status , osuser, spid ,s.username, s.program ,sql_address order by 1,3 select distinct name from sys.audit_actions, v$sqltext where address = ? and action = command_type does anyone know what tables in postgres would give me the same or similar information? TIA, mikeo
