Re: [SQL] Query TIME ZONE
Thanks Tom it worked for me... yes I got it wrong.. thanks for the correction. thanks a lot.. -- Raman - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Raman" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, January 28, 2004 11:28 AM Subject: Re: [SQL] Query TIME ZONE > "Raman" <[EMAIL PROTECTED]> writes: > > [ okay: ] > > "select current_time AT TIME ZONE INTERVAL '+5:30'" > > [ not okay: ] > > "select current_time AT TIME ZONE INTERVAL time_difference from customer_events" > > "TIME ZONE INTERVAL" is not a SQL construct. You have misunderstood the > interaction of two different SQL constructs: > timestamp AT TIME ZONE timezonespec > INTERVAL 'interval-literal' > One of the possible forms of "timezonespec" in the AT TIME ZONE operator > is an interval value, so your first example works fine. Your second > example does not work because the INTERVAL 'foo' construct is only for > simple literal constants. > > > here : time_difference - is my varchar column in the table customer_events. > > Why are you using varchar rather than an interval column? An interval > value would work directly in this construct and would provide some > checking that entered values are sane. > > If you are absolutely intent on using varchar as the column datatype, > you can do a run-time cast like this: > select current_time AT TIME ZONE "interval"(time_difference) from customer_events > but don't complain when the query fails because some rows contain > time_difference strings that don't look like legal interval values... > > regards, tom lane > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Aggregate function error in 7.4
j knight <[EMAIL PROTECTED]> writes:
> [ query with GROUP BY on a FULL JOIN USING column ]
> psql:pgsql.dump:301: ERROR: column "r.day" must appear in the GROUP BY
> clause or be used in an aggregate function
Argh. This is the result of a thinko in an optimization added in 7.4.
The patch is attached...
regards, tom lane
*** src/backend/parser/parse_agg.c.orig Sat Nov 29 14:51:51 2003
--- src/backend/parser/parse_agg.c Wed Jan 28 02:25:53 2004
***
*** 98,104
parseCheckAggregates(ParseState *pstate, Query *qry)
{
List *groupClauses = NIL;
! boolhave_non_var_grouping = false;
List *lst;
boolhasJoinRTEs;
Node *clause;
--- 98,104
parseCheckAggregates(ParseState *pstate, Query *qry)
{
List *groupClauses = NIL;
! boolhave_non_var_grouping;
List *lst;
boolhasJoinRTEs;
Node *clause;
***
*** 127,135
* No aggregates allowed in GROUP BY clauses, either.
*
* While we are at it, build a list of the acceptable GROUP BY
!* expressions for use by check_ungrouped_columns() (this avoids
!* repeated scans of the targetlist within the recursive routine...).
!* And detect whether any of the expressions aren't simple Vars.
*/
foreach(lst, qry->groupClause)
{
--- 127,133
* No aggregates allowed in GROUP BY clauses, either.
*
* While we are at it, build a list of the acceptable GROUP BY
!* expressions for use by check_ungrouped_columns().
*/
foreach(lst, qry->groupClause)
{
***
*** 144,151
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("aggregates not allowed in GROUP BY
clause")));
groupClauses = lcons(expr, groupClauses);
- if (!IsA(expr, Var))
- have_non_var_grouping = true;
}
/*
--- 142,147
***
*** 169,174
--- 165,185
if (hasJoinRTEs)
groupClauses = (List *) flatten_join_alias_vars(qry,
(Node *) groupClauses);
+
+ /*
+* Detect whether any of the grouping expressions aren't simple Vars;
+* if they're all Vars then we don't have to work so hard in the
+* recursive scans. (Note we have to flatten aliases before this.)
+*/
+ have_non_var_grouping = false;
+ foreach(lst, groupClauses)
+ {
+ if (!IsA((Node *) lfirst(lst), Var))
+ {
+ have_non_var_grouping = true;
+ break;
+ }
+ }
/*
* Check the targetlist and HAVING clause for ungrouped variables.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] limit 1 and functional indexes
Hi,
Postgres choses the wrong index when I add limit 1 to the query.
This should not affect the index chosen.
I read that functional indexes are sometimes not chosen correctly by
optimizer.
Is there anything I can do to always use the functional index in the
following queries?
Query with limit 1 choses wrong index:
---
explain
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
order by order_date DESC LIMIT 1
Index Scan Backward using transactions_date_aff on transactions (cost=0.00..930780.96
rows=2879 width=33)
---
Without limit 1 choses correct index:
---
explain
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
order by order_date DESC
Index Scan using transactions_pop_i on transactions (cost=0.00..11351.72 rows=2879
width=33)
---
We have postgresql-7.3.2-3.
Thank you,
Alexandra
---(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] managing users in postgresql 7.4.1
hi Can you tell me how to disconnect some users from command line. I mean I want to disconnect some user from database without restarting postmaster when i do something like this select * from pg_stat_activity I had information but how can I use this to disconnect users from databases THX
[SQL] Question about isolation
If in a transaction I call an embedded function in Pl/PgSQL, in which I have: delete from t where condition; for e in select distinct on (f) * from t where ... loop ... end loop; Do I have the guarantee that, in any event, rows deleted from table t by the delete won't reappear in the select result? Sam -- Samuel Tardieu -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Aggregate function error in 7.4
Tom Lane wrote: j knight <[EMAIL PROTECTED]> writes: [ query with GROUP BY on a FULL JOIN USING column ] psql:pgsql.dump:301: ERROR: column "r.day" must appear in the GROUP BY clause or be used in an aggregate function Argh. This is the result of a thinko in an optimization added in 7.4. The patch is attached... Thank you Tom. This does indeed solve the problem. .joel ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Question about isolation
On Wed, 28 Jan 2004, Samuel Tardieu wrote: > If in a transaction I call an embedded function in Pl/PgSQL, in which > I have: > > delete from t where condition; > for e in select distinct on (f) * from t where ... loop > ... > end loop; > > Do I have the guarantee that, in any event, rows deleted from table t > by the delete won't reappear in the select result? i do not think you have that guarantee in READ COMMITTED mode because there is a slight possibility another backend sneaked a committed insert in between the delete and select statement. perhaps you want to change to SERIALIZABLE transaction isolation. or perhaps you would like to repeat the WHERE condition from the DELETE in the following SELECT so as to not gather any of the offending rows. http://www.postgresql.org/docs/7.4/static/sql-set-transaction.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about isolation
On Wed, 28 Jan 2004, Chester Kustarz wrote: > On Wed, 28 Jan 2004, Samuel Tardieu wrote: > > If in a transaction I call an embedded function in Pl/PgSQL, in which > > I have: > > > > delete from t where condition; > > for e in select distinct on (f) * from t where ... loop > > ... > > end loop; > > > > Do I have the guarantee that, in any event, rows deleted from table t > > by the delete won't reappear in the select result? > > i do not think you have that guarantee in READ COMMITTED mode because > there is a slight possibility another backend sneaked a committed insert in > between the delete and select statement. perhaps you want to > change to SERIALIZABLE transaction isolation. or perhaps you would > like to repeat the WHERE condition from the DELETE in the following > SELECT so as to not gather any of the offending rows. > > http://www.postgresql.org/docs/7.4/static/sql-set-transaction.html perhaps the isolation level applies to the statement that called the function, in which case you would be ok. that would make more sense, no? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Question about isolation
> "Chester" == Chester Kustarz <[EMAIL PROTECTED]> writes: > On Wed, 28 Jan 2004, Chester Kustarz wrote: >> On Wed, 28 Jan 2004, Samuel Tardieu wrote: > If in a transaction I >> call an embedded function in Pl/PgSQL, in which > I have: >> > >> > delete from t where condition; > for e in select distinct on (f) >> * from t where ... loop > ... > end loop; >> > >> > Do I have the guarantee that, in any event, rows deleted from >> table t > by the delete won't reappear in the select result? >> >> i do not think you have that guarantee in READ COMMITTED mode >> because there is a slight possibility another backend sneaked a >> committed insert in between the delete and select >> statement. perhaps you want to change to SERIALIZABLE transaction >> isolation. or perhaps you would like to repeat the WHERE condition >> from the DELETE in the following SELECT so as to not gather any of >> the offending rows. >> >> http://www.postgresql.org/docs/7.4/static/sql-set-transaction.html > perhaps the isolation level applies to the statement that called the > function, in which case you would be ok. that would make more sense, > no? Yes. But the possible effect your describe (insertion of new rows after the DELETE statement and before the SELECT) matches accurately the symptoms we are observing. However, as we do have a lot of transactions, this is not easy to reproduce. Sam -- Samuel Tardieu -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about isolation
Samuel Tardieu <[EMAIL PROTECTED]> writes: >>> Do I have the guarantee that, in any event, rows deleted from >>> table t by the delete won't reappear in the select result? >> >> i do not think you have that guarantee in READ COMMITTED mode >> because there is a slight possibility another backend sneaked a >> committed insert in between the delete and select >> statement. > Yes. But the possible effect your describe (insertion of new rows > after the DELETE statement and before the SELECT) matches accurately > the symptoms we are observing. Hmm. I think you need to look closer. AFAIR the READ COMMITTED behavior is only an issue if you give the commands interactively from the client. Inside a plpgsql function we do not do SetQuerySnapshot() and therefore the snapshot of other transactions' effects does not advance. So I think the coding should be safe ... at the moment. (A number of people think the lack of SetQuerySnapshot inside functions is a bug; so the behavior might change in future.) Using SERIALIZABLE mode would probably make your code more future-proof, but if you are presently seeing failures, there's some other effect involved here. regards, tom lane ---(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] postgres timeout.
Hi all , I'm trying to find out if there is a specific setting to make transactions time out faster in a scenario where there's an update on a table in a transaction block, and another update process tries to update the same column. It looks like the second process will wait until you end the transaction block in the first transaction. I've looked at the deadlock timeout parameter and other parameters, but I don't think I found what I'm looking for. I basically need to be able to let the second process exit with an error after waiting 5 - 10 seconds. Please can someone help? Kind Regards Stefan pgp0.pgp Description: PGP signature
Re: [SQL] postgres timeout.
Stef <[EMAIL PROTECTED]> writes: > I'm trying to find out if there is a specific setting > to make transactions time out faster in a scenario > where there's an update on a table in a transaction > block, and another update process tries to update > the same column. > It looks like the second process will wait until you > end the transaction block in the first transaction. You can use statement_timeout to limit the wait. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] postgres timeout. [SOLVED]
Hi all, It seems I always find a solution just after panicking a little bit. Anyway, I found that statement_timeout solved my problem. When I tested it earlier, I actually made an error, and skipped it as a possible solution. Cheers Stef Stef mentioned : => Forgot to mention that I use postgres 7.3.4 => => Stef mentioned : => => Hi all , => => => => I'm trying to find out if there is a specific setting => => to make transactions time out faster in a scenario => => where there's an update on a table in a transaction => => block, and another update process tries to update => => the same column. => => => => It looks like the second process will wait until you => => end the transaction block in the first transaction. => => => => I've looked at the deadlock timeout parameter and => => other parameters, but I don't think I found what => => I'm looking for. => => => => I basically need to be able to let the second process => => exit with an error after waiting 5 - 10 seconds. => => => => Please can someone help? => => => => Kind Regards => => Stefan => => => pgp0.pgp Description: PGP signature
Re: [SQL] postgres timeout.
Forgot to mention that I use postgres 7.3.4 Stef mentioned : => Hi all , => => I'm trying to find out if there is a specific setting => to make transactions time out faster in a scenario => where there's an update on a table in a transaction => block, and another update process tries to update => the same column. => => It looks like the second process will wait until you => end the transaction block in the first transaction. => => I've looked at the deadlock timeout parameter and => other parameters, but I don't think I found what => I'm looking for. => => I basically need to be able to let the second process => exit with an error after waiting 5 - 10 seconds. => => Please can someone help? => => Kind Regards => Stefan => pgp0.pgp Description: PGP signature
[SQL] LEFT JOIN on one and/or another column
Hi. I have a table with two foreign keys (1 field each), like in id| serial ext_key_original | integer ext_key_exception | integer They mean different things, as one refers to a typical value, and the other one refers to an exception that applies for that tuple. Each key references a different table, one with typical data, and one with exception data. I'd like to join on the original, except when exception <> NULL, but I can't quite figure out how to do so. Can anyone help? Thanks you. :-) Octavio. -- Octavio Alvarez. E-mail: [EMAIL PROTECTED] Agradezco que sus correos sean enviados siempre a esta dirección. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] SQL Query for Top Down fetching of childs
Dear Friends, Postgres 7.3.4 on RH Linux 7.2. I need a query to get the Childs of a parent (Top down analysis). Need to list all the departments(Childs) of a parent organization. The table structure is CREATE TABLE organization( entity_id int4, entity_name varchar(100), entity_type varchar(25), parent_entity_id int4,) WITH OIDS; A parent can have n number of Childs. So I need to list all the childs for a parent. For example I query the Division , then it lists it Childs # select * from organization where parent_entity_id = 3; entity_id | entity_name | entity_type | parent_entity_id---+-+-+-- 5 | HR | EngineeringTeam | 3 12 | PM | EngineeringTeam | 3 8 | Finance | Dept | 3 6 | Quality | Dept | 3 I need to drill down to the last level Engineering Team in this example. So I query entity_id 8 further, it gives me its Childs =# select * from organization where parent_entity_id = 8; entity_id | entity_name | entity_type | parent_entity_id---+-+-+-- 15 | Audit | Group | 8 16 | Mkt | Group | 8(2 rows) Again, I need to query the entity_id 15 to get its child =# select * from organization where parent_entity_id = 15; entity_id | entity_name | entity_type | parent_entity_id---+-+-+-- 17 | CA | EngineeringTeam | 15 18 | Comm | EngineeringTeam | 15 19 | EComm | EngineeringTeam | 15(3 rows) I have used the following query, but not useful. CREATE OR REPLACE FUNCTION.fn_get_all_organization(int4) RETURNS SETOF organization AS'DECLARE pi_entity_id ALIAS FOR $1; rec_result organization%ROWTYPE; rec_proc organization%ROWTYPE; v_patent_entity_id INT; BEGIN FOR rec_result IN SELECT entity_id, entity_name, entity_type, parent_entity_id FROM organization ben WHERE ben.parent_entity_id = pi_entity_id LOOP IF rec_result.entity_type = \'EngineeingTeam\' THEN RETURN NEXT rec_result; ELSE v_patent_entity_id := rec_result.entity_id; LOOP FOR rec_proc IN SELECT bse.entity_id, bse.entity_name, bse.entity_type, bse.parent_entity_id FROM organization bse WHERE bse.parent_entity_id= v_patent_entity_id LOOPIF rec_proc.entity_type = \'EngineeringTeam\' THEN RETURN NEXT rec_proc;ELSE v_patent_entity_id := rec_proc.entity_id; END IF; END LOOP; EXIT WHEN rec_proc.entity_type = \'EngineeringTeam\'; END LOOP; END IF; END LOOP;RETURN;END;' LANGUAGE 'plpgsql' VOLATILE; Anybody pls help me with this. I am first time writing these kind of function for TOP DOWN analysis. Please shed light. Regards Senthil Kumar S
