[SQL] unwritable /tmp causes initdb to crash
this is a goofy situtation. we had a new linux 2.4.4 kernel with a /tmp not writable by postgres. granted, this is categorically hosed, but initdb still dumped core under these circumstances. changing the /tmp to writable immediately fixed the problem. -j = John Scott Senior Partner August Associates web: http://www.august.com/~jmscott email: [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] can't get rid of unnesesary SORT step in explain plan for hash join
Alexey Nalbat <[EMAIL PROTECTED]> writes: > While executing this query postgres at first creates hash on table > "resellers", then get from index "products_mcr" for rows with > "m_id=123" already ordered (!!!) pairs "c_id,r_id", for each that > pair it checks join condition using hash. If postgers behaves like > this then the result of this hash join is already sorted, and extra > "Sort" is not needed. No, because the hash step might choose to divide its processing into multiple "batches", thereby destroying the sort order. The outer path's ordering is preserved only in relatively small test cases... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Counting rows in a table
MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii I have a query as follows: SELECT date(c.timestart) as ondate, c.userid, u.username, count(c.callinfoid) as numCalls, sum(b.broquant) as numBroc, sum(b.broquant)/count(c.callinfoid) as BrochuresaCall FROM callinfo c, user_ u, brochurerequest b WHERE c.userid = u.userid AND b.callinfoid = c.callinfoid GROUP BY c.userid,u.username,ondate; But, I want numCalls to count rows where b.callinfoid is not in c.callinfoid. How do i do it ? I am on version 7.0.2. Below are the table create statements that I have. - CREATE TABLE Patient ( patientId INT8, personId INT8 , constraint Patient_key primary key (patientId) ); CREATE TABLE User_ ( userId INT8, userName TEXT constraint uname_uniq unique, userPassword TEXT, userRole INTEGER, userMode TEXT, personId INT8, constraint User_key primary key (userId) ); CREATE TABLE CallInfo ( callInfoId INT8, timeStart TIMESTAMP, timeStop TIMESTAMP, marketing TEXT, userId INT8 constraint CI_U_FK references User_, patientId INT8, constraint CallInfo_key primary key (callinfoId) ); CREATE TABLE BrochureRequest ( broQuant INTEGER, method TEXT, comment TEXT, officeId INT8, callInfoId INT8 constraint BR_CI_FK references CallInfo ); __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Informix->PostgreSQL database convertion
is there an informix utility called dbschema which does this? dbschema -t all -d databasename > schema.sql Sylte writes: > How is the SQL command UNLOAD (informix) used to extract both data and > database architecture? > > Is it able to create a file of SQL statements like when using postgresql's > command "pg_dump -f outputfile mydb" > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl -- Rex McMaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] execute client application from PL/pgSql
hi, phpPGAdmin is a web based php driven postgresql admin tool. not sure of the exact url, try google :) it has a pg_dump option in it. jeff On Sat, 12 May 2001, datactrl wrote: > Date: Sat, 12 May 2001 10:23:39 +1000 > From: datactrl <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [SQL] execute client application from PL/pgSql > > Thank You Jeff, > What is phpPgAdmin and where can get it? > > Jack > > - Original Message - > From: "Jeff MacDonald" <[EMAIL PROTECTED]> > To: "Jack" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Saturday, May 12, 2001 2:28 AM > Subject: Re: [SQL] execute client application from PL/pgSql > > > > you could hack the pg_dump bit out of phpPgAdmin > > i think the license permits it. > > > > just my 2 cents. > > > > jeff > > > > On Wed, 9 May 2001, Jack wrote: > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Case
Hello, what's wrong when you receive this message from an select statement : CASE types "date" and "text" not matched thank you for any help -- Best regards, Cwhisperer mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] working with stored procedures
Hi , I am trying to create a stored procedure that returns a user defined type. The following piece of code shows the way I am trying to do it. regy1=> create function regyuser_rec(int) returns varchar as ' declareregy1'> v_userid alias for $1;regy1'> output_rec varchar(400);regy1'> user_rec record;regy1'> regy1'> beginregy1'> regy1'> select fname ,lname into user_rec from regyuserregy1'> where userid = v_userid;regy1'> output_rec := user_rec;regy1'> return output_rec;regy1'> end;regy1'> ' language 'plpgsql';CREATEregy1=> select regyuser_rec(2);ERROR: Attribute 'user_rec' not found Does a postgresql function returns a user defined object? If so, could I have an example. I am new to postgresql, and was trying to figure out what's wrong with my code.Could some one help me. Thanks, Radhika.
[SQL] error message...
Hi I have Postgres 7.0.3/6 on a Suse Professional 7.1 (kernel 2.4.0) machine. The database is used very often and I see in the logfile the error message: Sorry, too many clients already Can I set the number of 'active' clients? And where can I set this? And How? Thanx in advance! Sven Franke ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement
Tom, Thanks for the reply, and for correcting my broken SQL. :^) A couple days after I sent my own follow-up message, I realized how silly my question was. I'll let future questions sit a bit longer next time. To make up for my silly question, here is a function + aggregate I created while working on a different aspect of my original problem. Perhaps someone will find this useful. The function, 'booland', returns the logical 'AND' of two input values. DROP FUNCTION "booland" (bool, bool); CREATE FUNCTION "booland" (bool, bool) RETURNS bool AS 'BEGIN RETURN $1 AND $2; END;' LANGUAGE 'PLPGSQL'; The aggregate, 'logical_and', returns the logical 'AND' of all values in a column in an aggregated query. DROP AGGREGATE logical_and bool; CREATE AGGREGATE logical_and ( SFUNC1 = booland, BASETYPE = bool, STYPE1 = bool, INITCOND1 = 't' ); Obviously, this concept could be extended for a logical 'OR' function and aggregate. Dave On Fri, May 11, 2001 at 12:33:52PM -0400, Tom Lane wrote: > "David D. Kilzer" <[EMAIL PROTECTED]> writes: > > [ wants to write an aggregate function that returns its last input ] > > The SQL model of query processing has a very definite view of the stages > of processing: first group by, then aggregate, and last order by. Tuple > ordering is irrelevant according to the basic semantics of the language. > Probably the SQL authors would have left out ORDER BY entirely if they > could have got away with it, but instead they made it a vestigial > appendage that is only allowed at the very last instant before query > outputs are forwarded to a client application. > > Thus, it is very bad form to write an aggregate that depends on the > order it sees its inputs in. This won't be changed, because it's part > of the nature of the language. > > In PG 7.1 it's possible to hack around this by ordering the result of > a subselect-in-FROM: > > SELECT orderedagg(ss.x) FROM (select x from tab order by y) ss; > > which is a gross violation of the letter and spirit of the spec, and > should not be expected to be portable to other DBMSes; but it gets the > job done if you are intent on writing an ordering-dependent aggregate. > > However, I don't see any good way to combine this with grouping, since > if you apply GROUP BY to the output of the subselect you'll lose the > ordering again. > > > SELECT r.personid AS personid > > ,SUM(r.laps) AS laps > > ,COUNT(DISTINCT r.id) AS nightsraced > > ,(SELECT r.carid > > FROM race r > >WHERE r.personid = 14 > > ORDER BY r.date DESC > >LIMIT 1) AS carid > > FROM race r > >WHERE r.personid = 14 > > GROUP BY r.personid > > ORDER BY r.date; > > This is likely to be reasonably efficient, actually, since the subselect > will be evaluated only once per output group --- in fact, as you've > written it it'll only be evaluated once, period, since it has no > dependencies on the outer query. More usually you'd probably do > > ,(SELECT r2.carid > FROM race r2 >WHERE r2.personid = r.personid > ORDER BY r2.date DESC >LIMIT 1) AS carid > > so that the result tracks the outer query, and in this form it'd be > redone once per output row. > > regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] can't get rid of unnesesary SORT step in explain plan for hash join
On Sun, 13 May 2001, Tom Lane wrote: > Alexey Nalbat <[EMAIL PROTECTED]> writes: > > So, my question is: how can I get rid of this unnesesary "Sort" step > > in the execution plan for hash join? > > You can't, because it's not unnecessary. Hash join doesn't promise > to produce its outputs in any particular order. But the Unique > filter needs to see its inputs in order by the fields being made > unique. Hello. Tom, thanks for your answer. But I am not agree with it. Here is the query of my interest: + select distinct c_id, m_id from products, ( select r_id from resellers where r_name +like 'a%' ) as temp where m_id = 123 and products.r_id = temp.r_id order by m_id, +c_id; + + Unique (cost=16.83..16.83 rows=1 width=12) + -> Sort (cost=16.83..16.83 rows=1 width=12) + -> Hash Join (cost=8.16..16.82 rows=1 width=12) + -> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10 +width=8) + -> Hash (cost=8.14..8.14 rows=10 width=4) + -> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10 +width=4) While executing this query postgres at first creates hash on table "resellers", then get from index "products_mcr" for rows with "m_id=123" already ordered (!!!) pairs "c_id,r_id", for each that pair it checks join condition using hash. If postgers behaves like this then the result of this hash join is already sorted, and extra "Sort" is not needed. In fact query without "distinct" and "order by" clauses returns ordered values of "c_id". I mean this query: + select c_id from products, ( select r_id from resellers where r_name like 'a%' ) as +temp where m_id = 123 and products.r_id = temp.r_id" returns ordered values of "c_id; + + Hash Join (cost=8.16..16.82 rows=1 width=12) + -> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10 width=8) + -> Hash (cost=8.14..8.14 rows=10 width=4) + -> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10 width=4) And one more argument. We are now transferring our DB from Oracle to Postgres, and in oracle this query does not have "Sort" in explain_plan, it has only "Unique": + select distinct c_id from products where m_id = 123 and r_id in ( select r_id from +resellers where r_name like 'a%' ) order by c_id + + SORT, UNIQUE +HASH JOIN, + INDEX, RANGE SCAN PRODUCTS_MCR + TABLE ACCESS, FULL RESELLERS So I suppose, that this "Sort" step can be removed from the execution plan. What do you think about this? Thanks a lot. -- WBR, Alexey Nalbat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Auto incrementing an integer
How do I construct a datatype that autoincrement in postgreSQL? Thanks Sylte ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] fatal ERROR running simple join query...
I have a problem with postgres, I'm running a query that joins two tables, one with 129000+ records, and the other with 1172 records, it's a very simple join. But postgres can't handle it!!! (Both tables have an index on the field "cuentacb"). I get the following error after 3 minutes... migracion=# select * from cuentasequivalentes, equiv11mayo m migracion-# where cuentasequivalentes.cuentacb = m.cuentacb migracion-# and cuentasequivalentes.cuentabr <> m.cuentabr; FATAL 1: Memory exhausted in AllocSetAlloc() 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: Succeeded. What can I do? I'm guessing that I could change a configuration parameter or something like that, but I don't know. Please help me, it's kind of urgent. Ligia ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Auto incrementing an integer
Take a look at the SERIAL datatype as well as sequences (CREATE SEQUENCE, NEXTVAL, CURRVAL). good luck! On Mon, 14 May 2001, Sylte wrote: > How do I construct a datatype that autoincrement in postgreSQL? > > Thanks > Sylte > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
