[SQL] transaction in function
Hi, I want to write a function that updates several tables; if there is an exception while updating, it should rollback the transaction and raise an error. How can this be done in pgPLSQL in PostgreSQL 8.1 ? Thank you for helping me out on this one ! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] transaction in function
am Tue, dem 05.12.2006, um 16:25:31 +0100 mailte Marian POPESCU folgendes: > Hi, > > I want to write a function that updates several tables; if there is an > exception while updating, it should rollback the transaction and raise an > error. > > How can this be done in pgPLSQL in PostgreSQL 8.1 ? A function runs within a transaction. You can trapp errors within a function, read this: http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] transaction in function
Marian POPESCU <[EMAIL PROTECTED]> writes: > I want to write a function that updates several tables; if there is an > exception while updating, it should rollback the transaction and raise an > error. > How can this be done in pgPLSQL in PostgreSQL 8.1 ? You just do it. You would have to work at it to *not* have that behavior. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Question about "AT TIME ZONE"
The following is taken from section 9.9.3 of the help docs === Examples (supposing that the local time zone is PST8PDT): SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; Result: 2001-02-16 19:38:40-08 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; Result: 2001-02-16 18:38:40 The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7). === In the first example it says it is converted to PST "for display". In the second example it is not converted to PST for display. Does this mean that if a timestamp *with* a timezone is specified, and it also includes "AT TIME ZONE", that it is not converted to PST "for display" at the end? I just want to make sure that these two examples perform completely different tasks. Essentially the first item: SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; And this: SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-07' AT TIME ZONE 'PST'; are the exact same thing. Kind of confusing. Regards, Collin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Can someone explain the problem with this select
Allow me to demonstrate my pitiful SQL knowledge
I have tables documents and comments
If I run join and list doc_nums the query is quite fast
If I run join and use subselect the query is extremely slow
Can someone offer analysis
Thanks
Richard
dcc=# EXPLAIN ANALYZE select doc_num from documents limit 10;
QUERY PLAN
--
Limit (cost=0.00..1.01 rows=10 width=13) (actual time=0.013..0.061
rows=10 loops=1)
-> Seq Scan on documents (cost=0.00..909333.85 rows=9014885 width=13)
(actual time=0.008..0.027 rows=10 loops=1)
Total runtime: 0.125 ms
(3 rows)
dcc=#
dcc=# EXPLAIN ANALYZE select * from documents left outer join comments
on (documents.doc_num = comments.doc_num) where documents.doc_num in
('105364107','105513059','105513095','105513112','105585627','102933195','014650340','014650361','014650362','105419865');
QUERY PLAN
Hash Left Join (cost=21.23..61.54 rows=10 width=444) (actual
time=0.507..0.574 rows=10 loops=1)
Hash Cond: ("outer".doc_num = "inner".doc_num)
-> Bitmap Heap Scan on documents (cost=20.03..60.28 rows=10
width=361) (actual time=0.397..0.432 rows=10 loops=1)
Recheck Cond: ((doc_num = '105364107'::bpchar) OR (doc_num =
'105513059'::bpchar) OR (doc_num = '105513095'::bpchar) OR (doc_num =
'105513112'::bpchar) OR (doc_num = '105585627'::bpchar) OR (doc_num =
'102933195'::bpchar) OR (doc_num = '014650340'::bpchar) OR (doc_num =
'014650361'::bpchar) OR (doc_num = '014650362'::bpchar) OR (doc_num =
'105419865'::bpchar))
-> BitmapOr (cost=20.03..20.03 rows=10 width=0) (actual
time=0.383..0.383 rows=0 loops=1)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.059..0.059 rows=1 loops=1)
Index Cond: (doc_num = '105364107'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.039..0.039 rows=1 loops=1)
Index Cond: (doc_num = '105513059'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1)
Index Cond: (doc_num = '105513095'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.031..0.031 rows=1 loops=1)
Index Cond: (doc_num = '105513112'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1)
Index Cond: (doc_num = '105585627'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.036..0.036 rows=1 loops=1)
Index Cond: (doc_num = '102933195'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.036..0.036 rows=1 loops=1)
Index Cond: (doc_num = '014650340'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.031..0.031 rows=1 loops=1)
Index Cond: (doc_num = '014650361'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.031..0.031 rows=1 loops=1)
Index Cond: (doc_num = '014650362'::bpchar)
-> Bitmap Index Scan on documents_pkey (cost=0.00..2.00
rows=1 width=0) (actual time=0.035..0.035 rows=1 loops=1)
Index Cond: (doc_num = '105419865'::bpchar)
-> Hash (cost=1.16..1.16 rows=16 width=83) (actual time=0.080..0.080
rows=16 loops=1)
-> Seq Scan on comments (cost=0.00..1.16 rows=16 width=83)
(actual time=0.005..0.037 rows=16 loops=1)
Total runtime: 0.775 ms
(28 rows)
dcc=#
dcc=# EXPLAIN ANALYZE select * from documents left outer join comments on
(documents.doc_num = comments.doc_num) where documents.doc_num in (select
doc_num from documents limit 10);
QUERY PLAN
--
Merge IN Join (cost=10002.19..136154797.93 rows=10 width=654)
(actual time
=23.534..2216180.550 rows=10 loops=1)
Merge Cond: ("outer".doc_num = "inner".doc_num)
-> Merge Left Join (cost=0.00..36129585.92 rows=10083868 width=654)
(actual
tim
Re: [SQL] transaction in function
Any function runs into a single transaction so you don't need to worry about rolling back on exceptions - this is the standard behavior. If there is an exception while running any statement inside the function it's rolls back automatically. On Tue, 05 Dec 2006 17:25:31 +0200, Marian POPESCU <[EMAIL PROTECTED]> wrote: Hi, I want to write a function that updates several tables; if there is an exception while updating, it should rollback the transaction and raise an error. How can this be done in pgPLSQL in PostgreSQL 8.1 ? Thank you for helping me out on this one ! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Question about "AT TIME ZONE"
"Collin Peters" <[EMAIL PROTECTED]> writes: > In the first example it says it is converted to PST "for display". In > the second example it is not converted to PST for display. Does this > mean that if a timestamp *with* a timezone is specified, and it also > includes "AT TIME ZONE", that it is not converted to PST "for display" > at the end? AT TIME ZONE does two different things that are sort of inverses of each other: it can convert timestamp without timezone to timestamp with timezone, or the reverse. In the case where you're converting to ts with tz, there's an additional step involved, which is converting the UTC-base ts-with-tz value to your current TimeZone for display. That happens when SELECT emits the value as text, not as part of AT TIME ZONE. AT TIME ZONE itself basically either adds or subtracts the UTC offset of the named time zone, thus converting a value in UTC to or from a value that's relative to the named zone. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Can someone explain the problem with this select
Richard Ray <[EMAIL PROTECTED]> writes: > dcc=# EXPLAIN ANALYZE select * from documents left outer join comments on > (documents.doc_num = comments.doc_num) where documents.doc_num in (select > doc_num from documents limit 10); > [ is slow ] This isn't your fault, it's an optimizer limitation: PG < 8.2 can't reorder outer joins relative to regular joins, and the IN with a sub-select is a kind of regular join. So it's forming the whole outer-join result and then joining to the sub-select :-( This is fixed in 8.2, released today, so perhaps upgrading is the thing for you to do. Alternatively, you can contort the query to get the IN restriction inside the outer join: select * from (select * from documents where documents.doc_num in (select doc_num from documents limit 10)) ss left outer join comments on (ss.doc_num = comments.doc_num); regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Can someone explain the problem with this select
> dcc=# EXPLAIN ANALYZE select * from documents left outer join comments on > (documents.doc_num = comments.doc_num) where documents.doc_num in (select > doc_num from documents limit 10); This query is preforming the join on all records of your two tables. After all of the that exhaustive work is done, it this filter out the records you want. you should preform a filtered select first and then use those results in you left join. I guess the lesson you can learn from this example is that you should try to filter your data set to get it as small as possible before you do anything else with it. select * from ( select doc_num from documents limit 10 ) as D1 left outer join comments on (D1.doc_num = comments.doc_num) ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: 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] Can someone explain the problem with this select
I've been foolin with this for a couple of days Sometimes you just have to ask Thanks gentlemen On Tue, 5 Dec 2006, Tom Lane wrote: Richard Ray <[EMAIL PROTECTED]> writes: dcc=# EXPLAIN ANALYZE select * from documents left outer join comments on (documents.doc_num = comments.doc_num) where documents.doc_num in (select doc_num from documents limit 10); [ is slow ] This isn't your fault, it's an optimizer limitation: PG < 8.2 can't reorder outer joins relative to regular joins, and the IN with a sub-select is a kind of regular join. So it's forming the whole outer-join result and then joining to the sub-select :-( This is fixed in 8.2, released today, so perhaps upgrading is the thing for you to do. Alternatively, you can contort the query to get the IN restriction inside the outer join: select * from (select * from documents where documents.doc_num in (select doc_num from documents limit 10)) ss left outer join comments on (ss.doc_num = comments.doc_num); regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Can someone explain the problem with this select
Hey Ray,
I'm by no means a guru but here is my simple analysis. In the first
query, the 10 "documents" specified 'IN' the in are first selected from
the "documents" table. Then, those 10 rows are joined with the
"comments" table.
In the second query, every row in the "documents" table is joined with
the "comments" table, which took forever according to the Explain
Analyse. Then, the results of the complete join of those two tables
from are compared against the sub-select.
I'm guessing (emphasis on guessing) that the query planner chose this
approach because it does not know how many rows the sub-select will
return so it does the join and then checks those results against that
sub-select.
Another approach may be to do this.
Create Temp table limit_documents as select doc_num from documents limit 10;
select * from limit_documents left outer join comments on
(limit_documents.doc_num = comments.doc_num);
Hope that helps,
Ted
Richard Ray wrote:
Allow me to demonstrate my pitiful SQL knowledge
I have tables documents and comments
If I run join and list doc_nums the query is quite fast
If I run join and use subselect the query is extremely slow
Can someone offer analysis
Thanks
Richard
dcc=# EXPLAIN ANALYZE select doc_num from documents limit 10;
QUERY PLAN
--
Limit (cost=0.00..1.01 rows=10 width=13) (actual time=0.013..0.061
rows=10 loops=1)
-> Seq Scan on documents (cost=0.00..909333.85 rows=9014885
width=13) (actual time=0.008..0.027 rows=10 loops=1)
Total runtime: 0.125 ms
(3 rows)
dcc=#
dcc=# EXPLAIN ANALYZE select * from documents left outer join
comments on (documents.doc_num = comments.doc_num) where
documents.doc_num in
('105364107','105513059','105513095','105513112','105585627','102933195','014650340','014650361','014650362','105419865');
QUERY PLAN
Hash Left Join (cost=21.23..61.54 rows=10 width=444) (actual
time=0.507..0.574 rows=10 loops=1)
Hash Cond: ("outer".doc_num = "inner".doc_num)
-> Bitmap Heap Scan on documents (cost=20.03..60.28 rows=10
width=361) (actual time=0.397..0.432 rows=10 loops=1)
Recheck Cond: ((doc_num = '105364107'::bpchar) OR (doc_num =
'105513059'::bpchar) OR (doc_num = '105513095'::bpchar) OR (doc_num =
'105513112'::bpchar) OR (doc_num = '105585627'::bpchar) OR (doc_num =
'102933195'::bpchar) OR (doc_num = '014650340'::bpchar) OR (doc_num =
'014650361'::bpchar) OR (doc_num = '014650362'::bpchar) OR (doc_num =
'105419865'::bpchar))
-> BitmapOr (cost=20.03..20.03 rows=10 width=0) (actual
time=0.383..0.383 rows=0 loops=1)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.059..0.059 rows=1
loops=1)
Index Cond: (doc_num = '105364107'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.039..0.039 rows=1
loops=1)
Index Cond: (doc_num = '105513059'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.034..0.034 rows=1
loops=1)
Index Cond: (doc_num = '105513095'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.031..0.031 rows=1
loops=1)
Index Cond: (doc_num = '105513112'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.034..0.034 rows=1
loops=1)
Index Cond: (doc_num = '105585627'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.036..0.036 rows=1
loops=1)
Index Cond: (doc_num = '102933195'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.036..0.036 rows=1
loops=1)
Index Cond: (doc_num = '014650340'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.031..0.031 rows=1
loops=1)
Index Cond: (doc_num = '014650361'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 rows=1 width=0) (actual time=0.031..0.031 rows=1
loops=1)
Index Cond: (doc_num = '014650362'::bpchar)
-> Bitmap Index Scan on documents_pkey
(cost=0.00..2.00 r
Re: [SQL] Using Control Flow Functions in a SELECT Statement
[ removing a bunch of probably uninterested people from CC ] On mán, 2006-12-04 at 22:12 +0530, Ashish Ahlawat wrote: > Hi Team > > Thanks > > FOR your prompt responseBut USING CASE issue still NOT > resolvedOracle prompts same error. this is a postgresql mailing list, but I believe that even with oracle, the case statement should work. a quick google found http://www.oracle-base.com/articles/9i/Case9i.php gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] I don't want receive more emails
hi, could you tell me, what have I do for not reveice more emails thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] I don't want receive more emails
> hi, could you tell me, what have I do for not reveice more emails Send an email to: [EMAIL PROTECTED] Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [SQL] Case Preservation disregarding case
Bruce Momjian wrote: Tom Lane wrote: The real bottom line, though, is that this community has little respect for proposals that involve moving away from the SQL spec rather than closer to it; and that's what you're asking us to do. The spec is not at all vague about the case-sensitivity of identifiers. Now certainly we are not doing exactly what the spec says, but what you ask is even less like the spec's requirements. I think there are two major issues here: o if you quote identifiers that have any upper-case characters, do it both at table creation and use o display of non-quoted identifiers is lower-case I think we are OK making people either always quote, or always not quote. What we don't currently have a good answer for is people wanting the identifiers displayed using the original case. You can use quotes all the time of you want such display, but it is a pain. I think this is the crux of the complaints. Saving the case of the original creation and displaying that does work, but then it isn't clear if the identifier needs quotes (is the upper-case real or just for display). This gets us into even more confusion. Can someone can think of an answer to all this? I believe there is no one answer that will solve all cases.. but one solution that might come close is something like this (I take no credit, others have described this before me): PG would support two modes of operation: 1) Standard compliant, and; 2) Quirks / compatibility mode (case preserving but case-insensitive) I believe (and may be wrong) that the #2 mode-of-operation would only require: a) resultset data to have it's (unquoted) identifiers to be case-folded, and; b) queries with (unquoted) identifiers (joins / aliases etc) would continue in the planner to be case folded, so would work as now (and in effect be case-insensitive). c) the table describe syntax would return the case-preserved id name (which it already does if quoted?), or d: d) in addition to a), optionally send metadata describing the case-preserved name; this might someday allow newer drivers to display (display only, not column lookup) those names in database-designer views (iSQL types apps) If #a is possible, then drivers should not break, even if the DB is config'd to use setting #2. But I don't know the low-level protocol of PG to know if that is possible. .. Hopefully I'm not missing any points here, please correct me if so... I believe what I described above (while not being standard complaint per se) is identical to how mysql and mssql work (operationally anyway) right now.. On the other had Oracle and others work as PG does now, but my point in discussing this, is that the first two DBs have enough market, that offering a compatibility mode to ease the burden of porting apps would have substantial value (I know this from experience) Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
