[SQL] Rollback & Nextval fails
I have been trying the following SQL code :
BEGIN;
INSERT INTO table VALUES ( NEXTVAL('serial'), 'Data' );
ROLLBACK;
And the insert function is rolled back but the serial sequence isn't. Hav I
misunderstood the functionality of rollback or is this a bug? Is there
someway to get the functionality that rollsback everything?
Cheers,
Fredrik
Re: [SQL] Rollback & Nextval fails
Fredrik,
> I have been trying the following SQL code :
>
> BEGIN;
> INSERT INTO table VALUES ( NEXTVAL('serial'), 'Data' );
> ROLLBACK;
>
> And the insert function is rolled back but the serial sequence isn't. Hav I
> misunderstood the functionality of rollback or is this a bug? Is there
> someway to get the functionality that rollsback everything?
>
It is not a bug. In order to enable concurrent users access to the nextval()
function, every call to that function will increment the counter. Should a
client rollback, we cannot re-use the number(s) allocated to them, as other
clients might already have been allocated higher numbers. This means that a
full table scan would be required to allocate "blank" numbers inside the
sequence. Even this would not solve the issue of contiguous numbering in the
table, as at any point in time, "blanks" might exist.
If you *really* need a sequence with no unused numbers, you might consider
creating a table using a single row as the current sequence number. Use either
SQL or a stored proc. to lock the row and increment the value for the counter
on each next value that you require. This has the effect of serializing every
client update transaction where this scheme is used. Can you afford that?
Regards,
Grant
--
> Poorly planned software requires a genius to write it
> and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED])
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421Johannesburg, South Africa
Antw: [SQL] Rollback & Nextval fails
Fredrik Eriksson wrote:
> I have been trying the following SQL code :
> BEGIN;
> INSERT INTO table VALUES ( NEXTVAL('serial'), 'Data' );
> ROLLBACK;
> And the insert function is rolled back but the serial sequence isn't. Hav I
> misunderstood the functionality of rollback or is this a bug? Is there
> someway to get the functionality that rollsback everything?
It's not a bug but a feature.
Sequence numbers exist to create unique entity-id-number with (usually) no external
meaning, so
1.) there is no need to roll them back
2.) the system would have to keep a list, what sequence number were rolled back.
Gerhard
[SQL] adding fields containing NULL values
Hi all! Let's say I have a table like: employee datesallary extras -- Name1 01-31-1999 5000.00 NULL Name1 02-29-1999 5000.00 500.00 Name1 03-31-1999 5000.00 NULL I would like to get something like: employee datetotal - Name1 01-31-1999 5000.00 Name1 02-29-1999 5500.00 Name1 03-31-1999 5000.00 Umfortunately 'select employee,date,sallary+extras as total' doesn't give the desired result because 'somevalue + NULL' is considered to be NULL. Is there any solution for my (small) problem? - Werner - BTW: I'm running PostgreSQL V 6.5.1 -- --- Werner Modenbach [EMAIL PROTECTED] ALC Computertechnik GmbH http://www.alc.de
Re: [SQL] adding fields containing NULL values
Werner, > Umfortunately 'select employee,date,sallary+extras as total' doesn't give the > desired result because 'somevalue + NULL' is considered to be NULL. > Is there any solution for my (small) problem? Try SELECT employee, date, salary + COALESCE(extras, 0) as total FROM ... Regards Grant -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
[SQL] template or index of some kind
Hi, I have a situation where there are hundreds of inventories that needs to be stored into postgres. And I am thinking of doing a template to ease the track of those inventories. Idea 1: each inventory may be an individual table, and the problem is how can I keep track of those tables? Idea 2: since all inventories have the same attribute, we can have a template for the entire inventory, and every time each item is retrieved, the info is filled into template. (This sounds like having a table for 'item'.) Please give me some feedback. Thank you. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
[SQL] constraint
Hi, I am a newbie to SQL. Could someone please explain why do I need a contraint, and how do I use it in SQL? An example is needed. Thanks - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
Re: [SQL] constraint
Bernie Huang wrote: > Hi, > > I am a newbie to SQL. Could someone please explain why do I need a > contraint, and how do I use it in SQL? An example is needed. Thanks Constraints are used to have the database engine ensure the integrity of the data. It'l like teaching the database itself about "what makes sense". This can be various things, like - UNIQUE constraint In a combination of columns no duplicate entries should ever occur, so that a select with an exact key will give 0 or 1 rows as result, never more. - EXPRESSION constraint Restricts the possible values of a row to a given expression. For example a list of fixed values for one column or something like "used_pct + unused_pct = 100". - NOT NULL constraint The value for a specified attribute is not allowed to be the SQL NULL value. NULL in SQL terms means "unknown", and you cannot tell if "unknown" is equal to "anything" - not even to another "unknown". Thus, the expression NULL = NULL must return FALSE. To avoid the problems, that might arise from this SQL meaning of NULL, you tell that the application must allways provide a value for this attribute. - FOREIGN KEY constraint Restricts the possible values of a combination of attributes to existing values that appear in another table. Any attempt to modify the data in a way, so that the end result would violate a given constraint, results in a transaction abort and finally in a rollback operation, so no application will ever be able to leave the database in a physically OK, but logically inconsistent state. The big benefit is, that if an application programmer forgets about, the database wouldn't let the errorneous action happen. Think of a database containing users and discussion forums. A users login name must be unique, because it's the key you use to identify the user. So you setup a UNIQUE constraint, and the database will ensure it is. Also you have different user types (ADMIN, USER and GUEST). Your application might behave a little weired if a user type "FOO" occurs, so tell the database to never accept that value for this column. Next you only want to allow a user to be removed, if all his discussion forum articles are expired and removed. You setup a FOREIGN KEY constraint, so that noone can store an article with a user that doesn't exist, and noone can delete a user that still has articles referencing him. Oh - if you change the name of a user, you don't want to be able to forget to change all the references in the articles? Tell the FOREIGN KEY constraint by specifying ON UPDATE CASCADE, and the database will automatically change all articles to the new user name. Of course, an application can take care of all these "logical dependancies" of the data. But especially in the case of UNIQUE and FOREIGN KEY, this sounds much simpler than it is in concurrent reality. For example in a WEB application, implemented by tens of CGI scripts, it's hard to never forget about a single check. It's easier to add another ERROR check to a CGI later (because some user complained about a scripting error due to a not catched SQL execution that failed), than to fix logically inconsistent data that is already inside of the database. Good application development has the following, general steps: 1. Describe the problem to be solved in plain text, as detailed as possible. 2. Define what information the application needs to solve the problem. 3. Based on 2. develop the data layout, including all dependancies (constraints) of the information, the application has to deal with. This can be done with some boxes, comments and arrows on a sheet of paper (A0 - one square meter - is a sheet too). 4. Convert the result of step 3. into a DB schema in SQL syntax, including all the comments and arrows converted into CONTRAINT clauses. 5. Hack down a prototype, show it to a possible user (your actual customer) and iterate back from 5. or 2. until he buys the result. Yes, that's really the way of good application development. The key here is, that however buggy your application is, as long as the database schema is good you run a very little risk that the application corrupts the data. It might crash, try things it shouldn't and fail with cryptic error messages, fall over it's own feet and whatnot. But all data that remains after any successful transaction is consistent and makes se
[SQL] storing images!
Hi, Does PostgreSQL support storing image pointers or something alike. Basically, I want to retrieve relative images together with info. Thanks. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
[SQL] create constraint trigger
Hi Jan: In response to your suggestion about possibly being able to use "create constraint trigger," I have tried the following: I have the following trigger function (probably not all that important what it does...): create function prd_parm_tf_iu () returns opaque as ' declare trec record; baseholder varchar; begin select into trec min(pos) as n,max(pos) as m,count(*) as c from prd_parm where new.base = base; if trec.n != 1 or trec.m != trec.c then raise exception \'Parameters for base % not in order\', new.base; end if; return new; end;' language 'plpgsql'; This seems to work pretty good: create trigger prd_part_tr_iu after insert or update on prd_parm for each row execute procedure prd_parm_tf_iu(); But when I create it with "create constraint trigger" as shown next, the trigger doesn't seem to be invoked. It says it is created, but it allows data to pass that would not be allowed with the "create trigger." So I'm assuming I'm doing something wrong. create constraint trigger prd_part_tr_iu after insert or update on prd_parm deferrable initially deferred for each row execute procedure prd_parm_tf_iu(); Any ideas? Also, its not clear to me how to drop a trigger created with "create constrataint trigger." It would be very helpful if I could figure out how to make my triggers deferred. There are things stored in one table that have to be a certain way if other things are a certain way in a related table. It is more than what foreign key relationships can handle. It would really be great if I could figure out how to have the backend monitor the consistency of these details. Without deferral, I can check the consistency in one direction. But if I try to constrain it in both directions, I get into a problem where I can't update either table without breaking the constraints. I'm guessing that if I can get deferral to work, I should be able to update both tables in a transaction and be OK. Kyle begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard
[SQL] Max date in the month
hallo help me please how can i select maximal date in the month ?? as may = 31 june = 30 thanks Nizomi
