Re: [SQL] subselect query time and loops problem
Thanks Tom, I am using version 7.3.4, having problems updating to postgres 8 because of cpanel problems. regarding, >Evidently one has been analyzed much more recently than the other,because the estimated row counts are wildly different. Both the explain/analyse queries has been run at the same time. Best regards Pankaj Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone.
Re: [SQL] subselect query time and loops problem
On Sun, 2005-04-10 at 07:54 -0700, pankaj naug wrote: > [quoting Tom] > >Evidently one has been analyzed much more recently than the other, > because the estimated row counts are wildly different. > > Both the explain/analyse queries has been run at the same time. in that case, is the data the same? if so, what about STATISTICS settings for relevant columns? just to make things clear, have both databases have been ANALYZEd or VACUUM ANALYZEd recently ? (in case your 'explain/analyse' only refers to a EXPLAIN ANALYZE) gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] DROP TYPE without error?
I was confronted with a similar problem. I have several scripts which create or modify schemas, and each run in a single transaction. So, dropping a non-existent TYPE will produce a show-stopping error. I wrote this function, and others for various database objects: CREATE OR REPLACE FUNCTION dba_droptype(varchar) RETURNS boolean AS ' DECLARE p_type ALIAS FOR $1; v_exists boolean; BEGIN SELECT INTO v_exists TRUE WHERE EXISTS( SELECT 1 FROM pg_type WHERE typname = p_type::name); IF v_exists THEN RAISE NOTICE ''Dropping TYPE %'', p_type; EXECUTE ''DROP TYPE '' || p_type || '' CASCADE''; END IF; RETURN FOUND; END; ' LANGUAGE 'plpgsql'; COMMENT ON FUNCTION dba_droptype(varchar) IS ' Usage: SELECT dba_drop_type(type_name) Checks for existence of a type and drops it if found. Implements DROP TYPE CASCADE; if a function or other object depends on the type, that object will also be dropped. Returns TRUE if successful, returns FALSE if type is not found.'; -- Craig Addleman DBA ShareChive LLC * Philippe Lang <[EMAIL PROTECTED]> [2005-04-06 05:59]: > Hi, > > Since it is not possible to use CREATE OR REPLACE TYPE, is there a way > of using DROP TYPE on a non-existing type, without causing the entire > script to abort? It may sound crazy to ask for this, but it could be > really useful in my case, where Pl/Pgsql and Pl/Perl code is being > generated automatically, based on data found in a database. > > Thanks > > - > Philippe Lang > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] Problems with Set Returning Functions (SRFs)
Ofcourse this works perfectly ! Thanks a lot Tom ! Just curious, is this (x)-"trick" in the postgres manual somewhere ? Just just common SQL guru knowledge ? ;) /Otto Blomqvist "Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Otto Blomqvist" <[EMAIL PROTECTED]> writes: > > secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test)); > > ERROR: more than one row returned by a subquery used as an expression > > In 8.0 I think it'd work to do > > select (x).f1, (x).f2, (x).f3 from > (select testpassbyval(1, number1) as x from test) ss; > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(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] Date/Time Conversion
beta_jgw=# update scenario1.time_test set local_hour = extract(hour from to_timestamp(to_char(gmt_date,'-MM-DD')||' '||to_char(gmt_hour,'99')||':00:00-00','-MM-DD HH24:MI:SS') at time zone 'EST'); Wild guess, but shouldn't that be :00:00:00? Regards, Yasir ---(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] Query history file
> From the > server side, if you enable 'log_statement' all > queries will go into the > server logs. Thank you, I enabled log_statement = all log_duration = true It's beautiful! :) ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Getting the output of a function used in a where clause
HI, I’m a newbie so please bear with me. I have a function defined (got it from one of your threads… thanks Joe Conway) which calculates the distance between 2 zip code centeroids (in lat,long). This thing works great. However, I want to sort my results by distance without incurring the additional burden of executing the function twice. A simplified version of my current SQL (written in a perl cgi) that returns a set of zip codes within a given radius is: $sql = “SELECT zipcode from zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;”; What I want to write is something like: $sql = “SELECT zipcode, distance from zipcodes where distance <= $dist order by distance;”; But I don’t the magic SQL phrase to populate the distance variable using my nifty function. Do I need to create an output type for distance? Thanks in advance! Bill
Re: [SQL] Merging item codes using referential integrity
> Andrus Moor wrote:
>> I have item table and a lot of child tables where the items are used.
>> I want to merge two item codes into single item in all tables.
>> It is not nice to write a lot of separate UPDATE statements for each
>> table.
>> So I want to utilize REFERENCES clause for merging.
>>
>> I tried the following code but got duplicate key error in UPDATE
>> statement.
>>
>> Any idea how to impement this?
>>
>> CREATE TABLE parent ( code CHAR(10) PRIMARY KEY );
>
>> BEGIN;
>> -- Direct Postgres to update all child tables. This causes error.
>> UPDATE parent SET code='1' WHERE code='2';
>> -- Remove duplicate row
>
> That's the problem - you can't have a duplicate row at *any* time with a
> primary key. The UNIQUE constraint is instant and can't be deferred (at
> least, not yet).
>
> However, in this case I would simply write a function:
>
> CREATE FUNCTION merge_all(char(10), char(10) AS '
> UPDATE table_1 SET col_1=$2 WHERE col1=$1;
> UPDATE table_2 SET col_2=$2 WHERE col2=$2;
> ...etc...
> ' LANGUAGE SQL;
>
> Then: SELECT merge_all('OLD_VAL','NEW_VAL') for each value (you could even
> join to your "parent" table if all the values are in there). All the
> updates in the function take place in the same transaction, so if there
> are any problems then all changes will be rolled back.
Richard,
thank you.
Is is possible to determine table_1 , col_1 etc values automatically.
I have some hundreds of referential intgrety constraints which are changing.
So I must write and maintains hundres of additional lines of code which
duplicates existing referential integrity information.
I'm researching the following method:
Input:
Master table name $master and two its primary key values $value1 and
$value2
Output:
1. All $value2 field values in child tables are update to $value1
2. $value2 record is deleted from $master table
Algorithm:
SELECT
childtablename,
childfieldname
FROM pg_referentialinfo
WHERE pg_referentialinfo.mastertable=$master
INTO CURSOR childs;
BEGIN TRANSACTION;
SCAN FOR ALL childs RECORDS;
UPDATE (childs.childtablename) set (childs.childfieldname)=$value2
WHERE EVAL(childs.childfieldname)=$value1;
ENDSCAN;
SELECT
primarykeyfieldname
FROM pg_tables
WHERE pg_tables.tablename=$master
INTO CURSOR mfield;
DELETE FROM $master WHERE EVAL(mfield.primarykeyfieldname)=$value2;
COMMIT;
How to implement SCAN FOR ALL childs RECORDS in PostgreSQL ?
Andrus.
---(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] [ocpfree] Problem finding the time difference while tuning a query
Dear all, Of late, I have been involved in tuning the Performance for a particular program with my project. 1) When you execute a particular query, say if it takes, 1 mins, the second run will always take less time, like 10 msecs etc, the reason is that the second run, benefits from the parsing done on the first run of the query. One way to overcome, this is to use "alter system flush shared_pool" which is normally "supposed" to clear the parsing in shared_pool, but that's where my problem is, it sometimes does and most times doesn't, so I can't really say, how mush I have tuned or is it tuned at all. Any solutions ??? 2) The entire program must have only one commit, 'cos of that it takes a longtime, 'cos of the huge amts of information's that need to be maintained in the rollback segments. Pragma autonomous_transaction commits Or backup tables are not an option. Any methods, to enhance the speed to the program with rollback segment Or methods to ease the load on the rollback segments Cheers Kart Lot of failures in life are those who didn't realise how close they were to success when they gave up !! Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ocpfree/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Problems with Set Returning Functions (SRFs)
Helloo ! We have a database that contains data that we need to Parse. Ideally I would like write a C-function, ParseData, and run select ParseData([data_column]) from datatable where date='2005-05-05'; and have it return 5 columns with the parsed data. Each row in Data_column will potentially create multiple output-rows. I did some research and SRF seems to be the solution (?). After playing around with the TestPassByVal example on the postgres website (http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html) I'v ran into troubles. Here is the type definion CREATE TYPE __testpassbyval AS (f1 integer, f2 integer, f3 integer); CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testpassbyval AS 'filename', 'testpassbyval' LANGUAGE C IMMUTABLE STRICT; First paramter is the number of rows the function returns. Second Parameter is the multiplier. First we Try secom=# select testpassbyval(2, 5); testpassbyval --- (5,10,15) (5,10,15) (2 rows) Then we can extract the columns using secom=# select f1, f2, f3 from testpassbyval(2, 5); f1 | f2 | f3 ++ 5 | 10 | 15 5 | 10 | 15 (2 rows) So far so good. But What I want is to feed the testpassbyval function with data from a column (data_column) Creating a test table with column data_column having integers from 1 trew 9 we get secom=# select testpassbyval(2, data_column) from datatable; testpassbyval --- (1,2,3) (1,2,3) (2,4,6) (2,4,6) (3,6,9) (3,6,9) (4,8,12) (4,8,12) (5,10,15) (5,10,15) (6,12,18) (6,12,18) (7,14,21) (7,14,21) (8,16,24) (8,16,24) (9,18,27) (9,18,27) (18 rows) Looking good. Now I try to extract the columns secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test)); ERROR: more than one row returned by a subquery used as an expression This is where I fail. Am I even on the right path here ? Writing the actual parsing function will be easy once I have a working concept. Any ideas ? Thanks a lot /Otto Blomqvist I'm Running PSQL 8.0.0 on Linux 8.0 ---(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] [GENERAL] Problems with Set Returning Functions (SRFs)
On Wed, Apr 06, 2005 at 03:31:45PM -0700, Otto Blomqvist wrote: > > Just curious, is this (x)-"trick" in the postgres manual somewhere ? Just > just common SQL guru knowledge ? ;) I think the relevant documentation is "Field Selection" in the "Value Expressions" section of the "SQL Syntax" chapter. http://www.postgresql.org/docs/8.0/interactive/sql-expressions.html#AEN1642 -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
