Re: [SQL] Undo an update
Hi, Kis, Kis János Tamás wrote: > So, if you send every insert, update, delete command to a > logger-table, then you can to undo anything. But this is just re-inventing the wheel, we already have Point-in-Time recovery. Or do I miss something? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] problem with sequence.....
Hi good evening every one………
I have a problem with sequence ..
I created one sequence with the name of famaseq..
I am using this sequence in my function..
This is the function
Create or replace function getFamanewcase1(in_cda_no
varchar,in_dak_id varchar,in_name_of_beneficiary varchar,in_relation varchar,
In_address varchar,in_amnt_athrzd int4,in_pay_through
varchar,in_bank_code varchar,in_bank_acc_no varchar,in_amnt_comision int4,
In_from_date date,in_mo_comisionpaid bool,in_remrks varchar)
returns integer as'
Declare
Pay_thrw varchar:=''bank'';
Begin
If pay_thrw =$7 then
Insert into family_allotment_trans (cda_no, dak_id, name_of_the_beneficiary,
relation, address, amount_authorized,
pay_through,bank_code,bank_acc_no,amount_of_comision,from_date,mo_comsn_paidby,remarks,famly_alltmnt_trans_id)
values($1,$2,$3,$4,$5,$6,$7,
$8,$9,$10,$11,$12,$13,nextval(''famaseq''));
else
Insert into family_allotment_trans (cda_no,dak_id,name_of_the_beneficiary,relation,address,amount_authorized,pay_through,bank_code,
amount_of_comision,from_date,mo_comsn_paidby,remarks,famly_alltmnt_trans_id)
values($1,$2,$3,$4,$5,$6,$7,$8,$10,$11,$12,$13,nextval(''famaseq''));
end if;
return 0;
end;
'language'plpgsql';
select
getFamanewcase1('123456a','dak1','penchal','friend','bangalore',2000,'order','bc2','bc1',20,'2006-06-06','false','no
remarks');
If I use this select statement with correct inputs that
sequence is working properly..
But when I was passing wrong values to that function..Automaticaly
sequence is creating but its not showing in view data..
If again I pass correct values to that function that values
can see in view data with increment value of sequence
Any one can tell me how to stop that sequence value when
ever I was passing wrong values to that function….
2 bc11 23456a dak2 vivek brother hyd 333 2006-06-25 bc2 f
4 bc2 123456a dak1 penchal friend bangalore 2006-08-08 2000 2006-06-06 bc1
false f no remarks 20
Actually second record has starts with 3 but here its
showing 4 becs before passing values to second record I given wrong values but sequence
Is incremented automaricaly..
Can u telll me when ever I am going to give wrong values to
that function .. the sequence value must be same………
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] problem with sequence.....
On Wed, Aug 16, 2006 at 05:01:09PM +0530, Penchalaiah P. wrote: > If again I pass correct values to that function that values can see in > view data with increment value of sequence > > > Any one can tell me how to stop that sequence value when ever I was > passing wrong values to that function I'm not entirely sure I understood you, but if what you're asking is for the sequence not to increment on transaction rollback, then you can't have it. The sequence system guarantees that the numbers will be in increasing order (subject to rollover), but it does not guarantee that there will be no gaps. This is to avoid some unpleasant concurrency side-effects from the no-gaps approach. If you really need that, then you have two choices: 1. Roll your own, using some sort of interlock table. This will not play nicely with a lot of concurrent writes, however (which is the disadvantage the current implementation is designed to avoid). 2. In recent PostgreSQL releases, you could use a savepoint. So, get the nextval() of your serial number. Set a savepoint. Try your insert. If that fails, roll back and save the currval() as VOIDed. (This isn't completely safe. You can make it completely safe by doing it in two transactions, but that's best left as an exercise for the reader.) A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Using bitmap index scans-more efficient
Kyle Bateman <[EMAIL PROTECTED]> writes: > I'm wondering if this might expose a weakness in the optimizer having to > do with left joins. Before 8.2 the optimizer has no ability to rearrange the order of outer joins. Do you have time to try your test case against CVS HEAD? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Undo an update
On 8/16/06, Markus Schaber <[EMAIL PROTECTED]> wrote: Hi, Kis, Kis János Tamás wrote: > So, if you send every insert, update, delete command to a > logger-table, then you can to undo anything. But this is just re-inventing the wheel, we already have Point-in-Time recovery. Or do I miss something? That's a type of audit trail. It's useful when you what to see what changed, when, and who did it, and let's you revert if necessary, without doing PITR. So, yeah, it's useful. Regards, Rodrigo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] DB creation script questions
Hey All, I'm new to postgres and am wondering what the recommended way of creating an automated db creation script is. I've reading the postgres 8.1 manual but haven't seen much on this and google doesn't seem to be bringing up much either. I need to create a script that will create a database if it currently doesn't exist on the system. It will then create tables if they don't already exist and populate the tables with some data if this data doesn't currently exist. This script must be able to be run over an over without causing harm to the db. I have previously used MySQL and was able to accomplish this very simply using IF NOT EXISTS for the table and database creation but this doesn't seem to be available for postgres and i'm looking for a more robust solution anyways. Is there a way that from a sql script file/shell script you can find out if certain postgres tables/databases exist? If someone can point me in the right direction it would be greatly appreciated. Thanks, Jon. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] OT: OpenDatabase Model ?
Doing a bit of database work for several friends (golf clubs and so on) I was very keen to start using a sort of "standard" and not least open database model with predefined tables. Up to now I have been writing and defining the tables by hand but I can see an advantage in using a "standard". The ODBM group that was offering this was found at http://www.opendatabasemodel.com - but the past long time it seems like it is dead. Anyone know of any other projects similar to this? Any suggestions that may help is welcome. Best regards Jesper K. Pedersen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] OT: OpenDatabase Model ?
On Wed, Aug 16, 2006 at 06:12:06PM +0200, Jesper K. Pedersen wrote: > > Doing a bit of database work for several friends (golf clubs and so on) > I was very keen to start using a sort of "standard" and not least open > database model with predefined tables. If I understand your question correctly, I think this is what the various Normal Forms are for, no? A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] DB creation script questions
On Wed, Aug 16, 2006 at 02:44:58PM -0400, Jon Horsman wrote: > I have previously used MySQL and was able to accomplish this very > simply using IF NOT EXISTS for the table and database creation but > this doesn't seem to be available for postgres and i'm looking for a > more robust solution anyways. Well, you could query the SQL-standard Information Schema. Alternatively, you can query the system tables like pg_class. But if you want the more standard one, use the tables under schema information_schema. That's the reason there's a standard. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] OT: OpenDatabase Model ?
Andrew Sullivan wrote: On Wed, Aug 16, 2006 at 06:12:06PM +0200, Jesper K. Pedersen wrote: Doing a bit of database work for several friends (golf clubs and so on) I was very keen to start using a sort of "standard" and not least open database model with predefined tables. If I understand your question correctly, I think this is what the various Normal Forms are for, no? A I may be a bit "vague" in saying what the project was about as the last time I visited them was when they were actually active and offering standard table defenitions. The normal forms are as far as I know just how you decide to tie together your tables. The opendatabase model actually offered a standard set of table definitions covering a wide range of data storage. Of course this means that the tables would often have stuff you dont need, and may not have the things you need, but at least there is a common "thread" in how you different databases look. For the big company that can afford having people optimizing databases that isnt the best choice, but for the common small scale users it is rather nice that we dont have to reinvent the tables each time we make a new database. Best regards Jesper K. Pedersen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Using bitmap index scans-more efficient
Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
I'm wondering if this might expose a weakness in the optimizer having to
do with left joins.
Before 8.2 the optimizer has no ability to rearrange the order of outer
joins. Do you have time to try your test case against CVS HEAD?
OK, I figured it out--grabbed the latest snapshot (hope that is what you
need).
My results are similar:
select l.* from ledg_v1 l, proj p where l.proj = p.proj_id and 5 =
p.par; (24 msec)
Nested Loop (cost=0.00..1991.93 rows=480 width=23)
-> Nested Loop (cost=0.00..4.68 rows=6 width=8)
-> Seq Scan on acct a (cost=0.00..1.12 rows=1 width=4)
Filter: ((code)::text = 'ap'::text)
-> Index Scan using i_proj_par on proj p (cost=0.00..3.49
rows=6 width=4)
Index Cond: (5 = par)
-> Index Scan using i_ledg_proj on ledg l (cost=0.00..330.17
rows=83 width=19)
Index Cond: (l.proj = "outer".proj_id)
select l.* from ledg_v2 l, proj p where l.proj = p.proj_id and 5 =
p.par; (1.25 sec)
Hash Join (cost=4.63..16768.43 rows=480 width=23)
Hash Cond: ("outer".proj = "inner".proj_id)
-> Nested Loop Left Join (cost=1.13..14760.13 rows=40 width=23)
-> Seq Scan on ledg l (cost=0.00..6759.00 rows=40 width=19)
-> Materialize (cost=1.13..1.14 rows=1 width=4)
-> Seq Scan on acct a (cost=0.00..1.12 rows=1 width=4)
Filter: ((code)::text = 'ap'::text)
-> Hash (cost=3.49..3.49 rows=6 width=4)
-> Index Scan using i_proj_par on proj p (cost=0.00..3.49
rows=6 width=4)
Index Cond: (5 = par)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Using bitmap index scans-more efficient
Kyle Bateman <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Before 8.2 the optimizer has no ability to rearrange the order of outer >> joins. Do you have time to try your test case against CVS HEAD? > OK, I figured it out--grabbed the latest snapshot (hope that is what you > need). > My results are similar: Are you sure you found a recent version? I get this from CVS HEAD: ledger=# explain analyze select l.* from ledg_v2 l, proj p where l.proj = p.proj_id and 5 = p.par; QUERY PLAN -- Nested Loop Left Join (cost=5.79..1386.74 rows=400 width=23) (actual time=0.125..1.543 rows=329 loops=1) -> Nested Loop (cost=4.66..1377.61 rows=400 width=19) (actual time=0.109..1.072 rows=329 loops=1) -> Index Scan using i_proj_par on proj p (cost=0.00..8.41 rows=5 width=4) (actual time=0.023..0.028 rows=4 loops=1) Index Cond: (5 = par) -> Bitmap Heap Scan on ledg l (cost=4.66..272.83 rows=81 width=19) (actual time=0.073..0.213 rows=82 loops=4) Recheck Cond: (l.proj = p.proj_id) -> Bitmap Index Scan on i_ledg_proj (cost=0.00..4.66 rows=81 width=0) (actual time=0.041..0.041 rows=82 loops=4) Index Cond: (l.proj = p.proj_id) -> Materialize (cost=1.13..1.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=329) -> Seq Scan on acct a (cost=0.00..1.12 rows=1 width=4) (actual time=0.009..0.011 rows=1 loops=1) Filter: ((code)::text = 'ap'::text) Total runtime: 1.696 ms (12 rows) Yours is doing the left join inside the join to proj, which of course is terrible because it has to form the whole 400K-row join result. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Help with optional parameters
I have the need to have optional values for a query in a stored procedure that I am building. (using postgres 8.1.4). This particular query executes against a huge table (several million rows) and has six optional parameters that can be sent to the function. If one of the parameters is null then the parameter doesn't need to be included in the query. Also the values in the columns that are optional can contain NULL values. One way that will work (although extremely cumbersome) is to have a switch on the parameters to execute the correct query: --This is a sample IF (a IS NULL AND b IS NULL) THEN select * from my_table; ELSEIF (a IS NOT NULL and b IS NULL) THEN select * from my_table where a=parama; ELSEIF (a IS NULL and b IS NOT NULL) THEN select * from my_table where b=paramb; ELSE select * from my_table where a=parama AND b=paramb; ENDIF; This is extremely bad when you have 6 parameters giving 64 possible queries. I tried using this (which works) but the planner likes to throw out the index for the columns because of the OR condition: select * from my_table WHERE (parama IS NULL OR a=parama) AND (paramb IS NULL OR b=paramb); My next thought was to get the planner to think that using indexes would be a good thing so I did the following: select * from my_table WHERE a=COALESCE(parama,a) AND b=COALESCE(paramb,b); That works great unless the column value for a or b IS NULL in which case NULL<>NULL because it equals NULL. Then I used the standby: set transform_null_equals to 1 This allows select null=null to return true. However, I ran into the problem that a=a (when a is a NULL value) still equals NULL. But a=NULL is true. So it didn't work out. What is the best way to write a query and get the planner to use indexes when you have optional parameters and columns that can contain NULL values?
Re: [SQL] Help with optional parameters
On Wed, Aug 16, 2006 at 08:39:49PM -0700, Rob Tester wrote: > What is the best way to write a query and get the planner to use indexes > when you have optional parameters and columns that can contain NULL values? Have you considered building a query string and using EXECUTE? That's not as "neat" as a static query but it might be worth testing. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
