Re: [SQL] interval syntax change from 7.1.2 to 7.2.1?
From: "Jinn Koriech" <[EMAIL PROTECTED]> wrote: > hi - i recently upgraded my server from 7.1.2 to 7.2.1 and now one of my > apps is no longer functioning. this is the query: > > SELECT DISTINCT rate, sum(extract(epoch from interval (end_time - > start_time)))/3600) AS duration > FROM log > WHERE invoice_state='8' > GROUP BY rate HAVING rate > 0 > > where 'end_time' and 'start_time' are columns in table 'log' > > the reported message is: > > PostgreSQL said: ERROR: parser: parse error at or near "end_time" > > any help greatly appreciated. > Redo your query putting intervall beetwen " " : SELECT DISTINCT rate, sum(extract(epoch from "interval"(end_time - start_time)))/3600) AS duration FROM log WHERE invoice_state='8' GROUP BY rate HAVING rate > 0 Ciao Gaetano. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Queries not using Index
"Daryl Herzmann" <[EMAIL PROTECTED]> wrote: > snet=# select count(valid) from t2002_06; > count > - > 1513895 > snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4'; > NOTICE: QUERY PLAN: > > Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47) Can you do the following query for better understand your situation ? select count(*) from t2002_06 where station = 'SGLI4'; select count(*) from t2002_06; Ciao Gaetano. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] unsubscribe
unsubscribe ---(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] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Select the max on a field
Hi all, Suppose that I have a table like this: att_1 |att_2 |att_3 | att_4 1 | a |y |y1 2 | b |y |y2 3 | a |xx |y3 4 | c |zz |y4 5 | a |t|y5 6 | c |x |y6 I want obtain all row that have for each att_2 the max of att_1 some like SELECT * FROM MY_TABLE GROUP BY att_2 HAVING att_1 = max ( id_user_log) for obtain: att_1 |att_2 |att_3 | att_4 2 | b |y |y2 5 | a |t|y5 6 | c |x |y6 Ciao Gaetano ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select the max on a field
"Gaetano Mendola" <[EMAIL PROTECTED]> wrote in message alq3mr$2s7o$[EMAIL PROTECTED]">news:alq3mr$2s7o$[EMAIL PROTECTED]... > Hi all, > > Suppose that I have a table like this: > > > att_1 |att_2 |att_3 | att_4 > > 1 | a |y |y1 > 2 | b |y |y2 > 3 | a |xx |y3 > 4 | c |zz |y4 > 5 | a |t|y5 > 6 | c |x |y6 > > > > I want obtain all row that have for each att_2 > the max of att_1 I forgot to say: "with a single total query without sub select " Ciao Gaetano ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Performance inside and outside view ( WAS Re: Select the max on a field )
"Stephan Szabo" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > select distinct on (att_2) * from test > order by att_2, att_1 desc; Yes that's right it's help me, but here the optimizer have some problems: CREATE VIEW last_user_logs AS SELECT DISTINCT ON (id_user) * FROM user_logs ORDER BY id_user, id_user_log DESC; and now: # explain analyze select * from user_logs where id_user = '5430'; NOTICE: QUERY PLAN: Index Scan using idx_user_user_logs on user_logs (cost=0.00..3038.59 rows=939 width=68) (actual time=0.03..0.03 rows=0 loops=1) Total runtime: 0.12 msec EXPLAIN empdb=# explain analyze select * from last_user_logs where id_user = 5430; NOTICE: QUERY PLAN: Subquery Scan last_user_logs (cost=20256.12..20725.43 rows=18772 width=68) (actual time=3526.10..3526.10 rows=0 loops=1) -> Unique (cost=20256.12..20725.43 rows=18772 width=68) (actual time=3067.14..3522.54 rows=2226 loops=1) -> Sort (cost=20256.12..20256.12 rows=187723 width=68) (actual time=3067.13..3241.94 rows=187723 loops=1) -> Seq Scan on user_logs (cost=0.00..3813.23 rows=187723 width=68) (actual time=0.02..1070.59 rows=187723 loops=1) Total runtime: 3578.07 msec EXPLAIN if I do instead without view and #explain analyze SELECT DISTINCT ON (id_user) * FROM user_logs WHERE id_user = 5430 ORDER BY id_user, id_user_log DESC; NOTICE: QUERY PLAN: Unique (cost=3084.93..3087.27 rows=94 width=68) (actual time=0.21..0.21 rows=0 loops=1) -> Sort (cost=3084.93..3084.93 rows=939 width=68) (actual time=0.21..0.21 rows=0 loops=1) -> Index Scan using idx_user_user_logs on user_logs (cost=0.00..3038.59 rows=939 width=68) (actual time=0.03..0.03 rows=0 loops=1) Total runtime: 0.31 msec EXPLAIN Ciao Gaetano ---(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] Performance inside and outside view ( WAS Re: Select the max on a field )
"Tom Lane" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > > Yes that's right it's help me, > > but here the optimizer have some problems: > > What's the datatype of id_user, and why are you quoting the compared > constant in some cases but not others? I don't think the difference > has anything to do with the view here ... id_user is an INTEGER type. Well what about this two selects: a) SELECT DISTINCT ON (id_user) * FROM user_logs WHERE id_user = 5430 ORDER BY id_user, id_user_log DESC; b) CREATE VIEW last_user_logs AS SELECT DISTINCT ON (id_user) * FROM user_logs ORDER BY id_user, id_user_log DESC; SELECT * FROM last_user_logs WHERE id_user = 5430 I think that in the second case the filter is not pushed inside the view. Ciao Gaetano ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] functions that return a dataset or set of rows
"Brian Ward" <[EMAIL PROTECTED]> wrote in message news:aofqbd$10v5$1@;news.hub.org... > How do I create a function that returns a set of row; > > I can't seem to find the datatype that the return set should be declared as. You should wait for Postgres 7.3 currently in beta. Ciao Gaetano ---(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] 7.3 "group by" issue
- Original Message - From: "Dan Langille" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 21, 2003 7:01 PM Subject: [SQL] 7.3 "group by" issue > Hi folks, > > This query: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id >AND watch_list.user_id = 1 > GROUP BY watch_list_element.element_id Try: SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY watch_list_element.element_id Ciao Gaetano ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Column limits in table/ views
"A.M." <[EMAIL PROTECTED]> wrote: > I have 560 columns of NUMERIC(10,14). To not run up against max column > restraints, I split the information into two tables. Does the column > limit on tables imply the same limit for views or selects or could I > potentially select a row across both tables and make a view that hides > the split? Hi, just for curiosity, can I known why do you need a table or a view with more then 560 Columns ? Usually have a big table like yours is sign of a not good design. Regards Gaetano Mendola ---(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] postgres 7.1.3: why does the query plan ignore indexes?
"Tom Lane" <[EMAIL PROTECTED]> wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Should we consider adding some warning when someone creates an index on > > an int2 column? > > I don't think so. Better to expend our energy on solving the > fundamental problem. In the mean time that the fundamental problem is solved may be a warning is usefull. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Triggers Help...
[EMAIL PROTECTED] wrote: Hi, I have two databases with the same tables, one is my Real Database and the other is my Log Database. My Log database does NOT have any kind of keys (PRIMARY or FOREIGN). I need to insert from my Real database to my Log database every row that is Updated or Deleted in any Table of the Real database. I need to create triggers (BEFORE DELETE or BEFORE UPDATE) to each of my Real database tables, these triggers will INSERT into my Log database table the row that is being deleted or/and updated in the Real Database tables. I dont know how to do this? Anyone can help me? What you are looking for is a replication tools, look here: http://www.postgresql.org/news/147.html Regards Gaetano Mendola ---(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
Re: [SQL] does postgresql execute unions in parallel?
teknokrat wrote: If I have several selects joined with unions does postgresql execute the concurrently or not? nope. Regards Gaetan Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Syntax problem
sad wrote: select... fromauswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f where k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp and a.ask_id = f.ask_id(+) This (+) means JOIN Means OUTER JOIN but I don't remember the side. e.g. (+)-marked equations used as a joining condition Not exactly see above Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Now() in a function
sTesting wrote:
Hi,
I have a big problem, i wrote a very long function that is taking so
much time. inside this function i added few debugging steps to find
which section is taking most of the time, but all the created records
have the same time in them, the time where the fuction begins.
I tried to different methods:
INSERT INTO f_error (error_id, creation_date)
VALUES (nextval(''s_f_error_id''), now());
INSERT INTO f_error (error_id, creation_date)
VALUES (nextval(''s_f_error_id''), ''now'');
now() give you the time when the transaction started,
so what you see is the intended behavior, in your case you must use:
timeofday().
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Dynamic Query for System functions - now()
Kumar wrote: Dear Friends, I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I am trying to generate a dynamic query to fetch the next month interval. select now()+ interval'1 month'; -- This is working fine. I wanna dynamically assign the interval number. i,e --> select now()+ interval'n month'; What about: select now() + n * '1 month'::intervall; Regards Gaetano Mendola ---(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] FATAL 2: PageIndexTupleDelete
cristi wrote: salt=# vacuum full; FATAL 2: PageIndexTupleDelete: corrupted page pointers: lower = 12289, upper = 1, special = 60672 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. Why do I have this error message and how can I fix it? Wich version are you running, and what about give us informations on your platform ? Regards Gaetano Mendola ---(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] How to quote date value?
nobody wrote: I have found it in documentation, it is single quote. But it does not explain why SELECT '1/11/2003' AS "InvoiceDate"; returns "unknown" data type instead of "date". Why not a string ? or a fancy custom type ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Datatype Inet and Searching
Devin Atencio wrote: Dear Users, I am very new to using the inet data type and I have a database with a list of CIDRs in it And I was hoping to be able to do some kind of select statement that would pull the record That the IP given is located in for example. If in my database I have a record like: Id cidr name 1 10.0.0.0/30user1 2 10.0.0.4/30user2 3 10.0.0.8/30user3 I was hoping to be able to do something like: Select * from mytable where cidr <<=’10.0.0.5’ select * from mytable where cider >>= '10.0.0.5' The operation a >>= b mean: a contain or is equal b. Regards Gaetano Mendola ---(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] CONTEXT on PL/pgSQL
Martin Marques wrote: > El Vie 23 Abr 2004 11:18, Tom Lane escribió: > >>Martin Marques <[EMAIL PROTECTED]> writes: >> >>>I have a simple function made with PL/pgSQL and when I call it I get this >>>in the logs: >>> >>>2004-04-23 10:15:32 [30669] LOG: statement: SELECT nodoSuperior(22) AS >>>sup 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 >>>CONTEXT: PL/pgSQL function "nodosuperior" line 7 at assignment >> >>log_statement currently logs everything the parser sees, which includes >>SQL commands generated by plpgsql. >> >>Arguably these facilities should be separated, but until someone >>makes a serious effort to provide plpgsql debugging features, >>it's likely that nothing will be done about it. Right now this is >>almost the only technique available for seeing what's going on inside >>a plpgsql function, and crummy as it is, it's better than nothing... > > > So the CONTEXT line just tells where the statement was made? U'r right, you can get rid of that CONTEXT configuring postgresql with log_error_verbosity = terse Regards Gaetano Mendola ---(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] How to speed up a time dimension query
Hans de Bruin wrote: Hi there, I like to speed up my homepage and need to do something about a query. A half to one second to get 24 records from a time dimension table a bit long. This is the table: news2=> \d dim_time And here is the query which in my opinion take way to long: news2=> explain analyze select id,day,hour from dim_time news2-> where id between (now()-interval '25 hours') news2-> and (now()- interval '1 hour') news2-> order by id ; This is not what I get with a table similiars to your: test=# select count(*) from user_logs; count - 3025880 (1 row) empdb=# explain analyze empdb-# select * empdb-# from user_logs empdb-# where login_time between (now()-interval '25 hours') and empdb-# (now()-interval '1 hour') empdb-# order by login_time; QUERY PLAN - Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..502.67 rows=15130 width=47) (actual time=61.433..61.433 rows=0 loops=1) Index Cond: ((login_time >= (now() - '1 day 01:00:00'::interval)) AND (login_time <= (now() - '01:00:00'::interval))) Total runtime: 61.557 ms (3 rows) what do you obtain if you disable the sequential scan ? do it in this way: set enable_seqscan = off; if you obtain a cost higher then what you get on your explain: cost=590.26..590.44 and of course a lower Total runtime, then you have instruct your engine that is better perform and index scan, you can accoplish this decreasing the following values ( that values are what I use ). cpu_index_tuple_cost = 0.0005 cpu_operator_cost= 0.0025 cpu_tuple_cost = 0.005 decreasing these value you decrease the cost for the index scans if the Total time is higher you have to deal with the parameter effective_cache_size in order to use more ram. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Memory usage on subselect
Dan Field wrote: On 24 May 2004, at 14:37, Tom Lane wrote: Dan Field <[EMAIL PROTECTED]> writes: I have a similar problem with just one of my queries (although it isn't a sub select): You really ought to vacuum and/or analyze occasionally. The EXPLAIN results show that the planner hasn't got any non-default statistics for any of these tables. Wow, thanks for that. I'd been pulling my hair out for a couple of days wondering where I was going wrong. I went from 45 second queries down to sub second query lengths after a simple vacuum full analyze. I've now added nightly and monthly cron jobs to do this for me in future. Out of curiosity, why is this deemed a DBA task rather than an automated postgres task? Once again, many thanks. You have to use the pg_autovacuum demon. Run the vacuum full and the reindex once in a week. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] FOR-IN-EXECUTE, why fail?
Marcos Medina wrote:
I wrote the following:
CREATE OR REPLACE FUNCTION escritorio.seq_valor_actual( text)
RETURNS integer AS '
DECLARE
secuencia ALIAS FOR $1;
valor_actual integer := 0;
v_query text;
actual integer;
BEGIN
RAISE NOTICE ''el parametro tiene el siguiente valor %'' ,secuencia;
v_query := ''SELECT last_value FROM '' || quote_ident(secuencia);
RAISE NOTICE ''la sentencia a ejecutar es %'' ,v_query;
FOR actual IN EXECUTE v_query LOOP
valor_actual := actual;
END LOOP;
RETURN valor_actual;
END;
'LANGUAGE 'plpgsql';
And i call:
select seq_valor_actual('s_id_reserva');
The s_id_reserva is a sequence. The error is the following:
WARNING: plpgsql: ERROR during compile of seq_valor_actual near line 12
ERROR: missing .. at end of SQL expression
I think the problem is in the FOR but i see correct all.
Can i help me?
Any idea?
You shall declare actual as RECORD and perform inside the loop:
valor_actual = actual.last_value;
Regards
Gaetano Mendola
---(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] Please help - performance problems
ctrl wrote: I have news...good news for me:) even though I wasn't able to find the answers I was looking for, I did something that made a big difference: by removing the ORDER BY clause, the same function takes now 5 milliseconds (instead of sometimes 10 minutes). I have tried to vacuum, analyze, etc...nothing worked. I post this hoping it could help somebody. How many memory are you using for the sort operations, is that column indexed ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] constraitnt on case sensetive and case insensetive columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A Bruce wrote: | hello, | | I am attempting to convert a oracle database to postgresql and I am having | some problems creating a constraint across multiple columns which are a | mixture of case insensitive and case sensitive. | | The original oracle database created an index with: | CREATE UNIQUE INDEX hatidx ON hats (upper(name), upper(server), login); | | However postgresql can handle multiple columns in the index, or one function, | but not multiple functions, thus this fails. | | Queries are only done using the actual values, so the presence of the | index is not required for performance reasons, and exists only to | enforce the constraint that (upper(name), upper(server), login) is a | unique tuple. Is there anyway to create a constraint which will check | this? I suspect it would be possible to create a trigger to check this, | however this is a little ugly, and i would like something more similar to | to the original if possible. | | Any suggestions as to how to approach this would be greatly appreciated, | -bruce | I'm using the 7.4.x version and what you ask for is supported: regression=# create table test ( a varchar, b varchar, c varchar ); CREATE TABLE regression=# create unique index test_idx on test ( upper(a), upper(b), c); CREATE INDEX Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBET87UpzwH2SGd4RAvUeAJ4vG0CxIQdUe8KjsYs/kk7yC1/dLQCgsy9t IZrziKueFyht39zm+/XoD8w= =gA20 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Object Create Date
Sameer Deshpande wrote: Hello, Which data dictionary I have to query to determine the object creation date.. F.ex I would like to find out on which date table or Index has been created.. You can't. Regards Gaeatano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problems with UNION ALL and ORDER BY
Kaloyan Iliev Iliev wrote: Dear friends..., I have the following problem: select . from where UNION ALL select ... from where ORDER BY field1 But the the order by doesn't work properly. It returns the rows of the first query ordered and then appends the rows of the second query ordered. But this is not what i expect. I expect the result of both queries to be orderd. So I try. SELECT TEMP.* FROM ( select . from where UNION ALL select ... from where ) TEMP ORDER BY TEMP.field1 But this also doesn't work. Any ideas.p This have to work. Could you please provide a real example ( creation table, insertion data and query execution ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Displaying two tables side by side
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Andreas Haumer wrote: | test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid); "full outer join" is better in this case. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGn5f7UpzwH2SGd4RAjP2AKCZVDTMWX87VXI7SfpAyWsJ57NlygCg6Ki9 5kOVpxAY5KPkHxpwpWFdEcY= =O/Yc -END PGP SIGNATURE- ---(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] SQL Challenge: Arbitrary Cross-tab
Greg Sabino Mullane wrote: Names shortened to spare the line lengths: SELECT bob.cid, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1) AS tk1, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 2) AS tk3, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 3) AS tk4, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 4) AS tk5, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 5) AS tk6, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 6) AS tk7, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 7) AS tk8, FROM (SELECT DISTINCT cid FROM ats) AS bob; Don't you miss for each subselect an order by tid ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Greg Sabino Mullane wrote: Don't you miss for each subselect an order by tid ? No: since all the SELECTs are part of one statement, they will have the same (pseudo-random) implicit order. Is this guaranted ? Regards Gaetano Mendola ---(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] backup of a specific schema
Kenneth Gonsalves wrote: On Friday 27 August 2004 01:17 pm, Michalis Kabrianis wrote: Kenneth Gonsalves wrote: hi, is there anyway to backup/restore a specfic schema in a database? Hi, isn't this what pg_dump --schema=SCHEMA does? thanx - was looking in the wrong place in the manual Which one ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] backup of a specific schema
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kenneth Gonsalves wrote: | On Saturday 28 August 2004 07:46 am, Gaetano Mendola wrote: | |>Kenneth Gonsalves wrote: |> |>>On Friday 27 August 2004 01:17 pm, Michalis Kabrianis wrote: |>> |>>>Kenneth Gonsalves wrote: |>>> |>>>>hi, |>>>>is there anyway to backup/restore a specfic schema in a database? |>>> |>>>Hi, |>>>isn't this what pg_dump --schema=SCHEMA does? |>> |>>thanx - was looking in the wrong place in the manual |> |>Which one ? | | | backup/restore Well, that chapter speak about pg_dump, don't you had the curiosity to look at the complete options for that command ? :-) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBMH8m7UpzwH2SGd4RAquSAKCDzx1l0dwnar1ZLIjSeHnr2TKyZgCgzKW9 2Jk/+LoMiTv0yRXEqbu3A3o= =jUGa -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] backup of a specific schema
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kenneth Gonsalves wrote: | On Saturday 28 August 2004 06:18 pm, Gaetano Mendola wrote: | | |>| |>| backup/restore |> |>Well, that chapter speak about pg_dump, don't you had the curiosity |>to look at the complete options for that command ? :-) | | | yes. so i looked in the book from which i was learning postgres. it gave | about 3 options and didnt say there were more. so i didnt look further. i | thought maybe it has to be done programmatically which is why i posted the | query Is better alway take a look at the on-line doocumentation, in this way you'll be sure to be up-to-date. Regards Gaeatano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBMbvg7UpzwH2SGd4RApdvAKD1MFtYwnsShQIiEvfuQ5y6fZI71wCgpweH WiqNRGoDaNy11W18Dg40/EE= =3a5m -END PGP SIGNATURE- ---(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
Re: [SQL] How to check postgres running or not ?
Sandeep Gaikwad wrote: Hello Sir, I want to know how to check whether postgres database is running or not ? when I give command like ./postmaster -i &, whether all databases in that postgres will run or any one [default] ? If any one, then how to detect that database ? Well, in the $DATADIR you habe postmaster.pid that contains the supposed pid... Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to check postgres running or not ?
Jeff Eckermann wrote:
--- Christopher Browne <[EMAIL PROTECTED]> wrote:
In an attempt to throw the authorities off his
trail, [EMAIL PROTECTED] ("Sandeep Gaikwad")
transmitted:
Hello Sir,
I want to know how to check
whether postgres database
is running or not ? when I give command like
./postmaster -i &,
whether all databases in that postgres will run or
any one [default] ?
If any one, then how to detect that database ?
The "standard" way would be "pg_ctl status". "man
pg_ctl" is recommended reading for anyone
administering a PostgreSQL setup.
Is not enough because it check only for the postmaster.pid
and not if the engine is really up.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to check postgres running or not ?
Tom Lane wrote:
Geoffrey <[EMAIL PROTECTED]> writes:
Worik wrote:
Assuming it is unix The command
ps xau|grep post
You might want to change that to:
ps aux|grep postgres
As your suggestion will pick up extraneous data if one is running
postfix on the same box.
Actually I'd recommend grepping for "postmaster". If your PG user is
named "postgres" then the above command will find any program the PG
user is running --- which might only be a shell, for instance. If your
PG user is not named "postgres" then the above might find nothing at
all, even though the postmaster is alive (since depending on the details
of your local ps command, it might report all the server processes as
"postmaster").
There is even another gotcha, which is that the "grep postmaster"
command could easily find itself in the ps output. So what really
works is
ps aux | grep postmaster | grep -v grep
(or use "ps -ef" if using a SysV-ish ps).
Just to enforce the test is better looking for the entire executable path:
ps aux | grep /usr/bin/postmaster | grep -v grep
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to check postgres running or not ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Worik wrote: | | | [snip] | |> |> |> Just to enforce the test is better looking for the entire executable |> path: |> |> ps aux | grep /usr/bin/postmaster | grep -v grep |> | | Does not work for me! | | [EMAIL PROTECTED]:~$ ps aux | grep /usr/bin/postmaster | grep -v grep | [EMAIL PROTECTED]:~$ ps aux | grep postmaster | grep -v grep | postgres 670 0.1 0.6 8544 1688 pts/1S12:33 0:00 | /usr/lib/postgresql/bin/postmaster | [EMAIL PROTECTED]:~$ | | So... | " ps aux | grep postmaster | grep -v grep " | is more reliable(?) It only depends on your distribution, in your case: ps aux | grep /usr/lib/postgresql/bin/postmaster | grep -v grep consider also the if you run different postmaster version in different location this is the only way I believe Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBTquC7UpzwH2SGd4RAiouAKCpvuLSspsTVXCjSLgDZ2ZKQ3gfywCg7rzl gXkPlq9UanBNjIWpBvPXNwo= =ZNAY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] A transaction in transaction? Possible?
Stefan Weiss wrote: > On Wednesday, 10 November 2004 18:28, Tom Lane wrote: > >>Achilleus Mantzios <[EMAIL PROTECTED]> writes: >> >>>Just a very naive thought >>>Wouldn't make more sense to allow nested begin/commit/rollback blocks? >> >>We actually had it working that way initially, but changed to the >>spec-defined behavior, because (a) it wasn't standard, and (b) it >>was confusing. See the pghackers archives. > > > We used to run into problems with nested transactions in scenarios > like this: > > Imagine a database where you have a table for customers, and > each customer can have (in a seperate table) several contacts; a > contact can have one or more addresses, phone numbers, etc. These > tables are connected by foreign keys, but without "on delete" > triggers. Why "without" ? Are you looking to solve a problem introduced by yourself ? > The frontend application has a function for deleting a contact, > which works something like this: > > * begin transaction > * delete the contact's addresses, phone numbers, etc > * ... > * delete the contact record itself > * commit > > Then there is a function for deleting a customer: > > * begin transaction > * for all contacts, call the "delete contact" function > * ... > * delete the customer record itself > * commit > > At the moment the application is "simulating" support for nested > transactions: We use a wrapper for the BEGIN and COMMIT calls, > and an internal counter, which is incremented for each BEGIN. > Only the first BEGIN gets sent to the backend. When COMMIT has > been called as many times as BEGIN, we send a real commit (errors > and ROLLBACK are handled too, of course). > > It's not perfect, but it does what we need. Savepoints are a nice > feature, but I don't think they could help us here. You can handle this task using the new functionality introduced with savepoint: the exception. For more information look at: http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Your delete customer can do: * BEGIN * for all contacts call delete contact * ... * EXCEPTION * handle your exception * END; * * delete the customer record itself Regards Gaetano Mendola ---(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] A transaction in transaction? Possible?
Achilleus Mantzios wrote:
O Michael Fuhr έγραψε στις Nov 10, 2004 :
On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
Sorry, but I understand that your example is not really about nested
transactions, but about sequential transactions.
Here's a more elaborate example. If this doesn't demonstrate the
capability you're looking for, then please provide an example of
what you'd like to do and describe the desired behavior.
CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
BEGIN;
INSERT INTO person (name) VALUES ('Alice');
SAVEPOINT s1;
INSERT INTO person (name) VALUES ('Bob');
SAVEPOINT s2;
INSERT INTO person (name) VALUES ('Charles');
SAVEPOINT s3;
INSERT INTO person (name) VALUES ('David');
ROLLBACK TO s3;
INSERT INTO person (name) VALUES ('Edward');
ROLLBACK TO s2;
INSERT INTO person (name) VALUES ('Frank');
RELEASE s1;
INSERT INTO person (name) VALUES ('George');
COMMIT;
Just a very naive thought
Wouldn't make more sense to allow nested begin/commit/rollback blocks?
Is not naive because in this way you can do what you could do with
nested begin/commit/rollback blocks, in this way you can do more.
Think for example in the example above to convert the "ROLLBACK TO s3"
in a "ROLLBACK TO s1", that is impossible to do with begin/commit/rollback
blocks.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 8: explain analyze is your friend
