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
Re: [SQL] A transaction in transaction? Possible?
On Thursday, 11 November 2004 09:23, Gaetano Mendola wrote: > Stefan Weiss wrote: > > These tables are connected by foreign keys, but without "on delete" > > triggers. > > Why "without" ? Are you looking to solve a problem introduced by > yourself ? There are numerous checks involved before a customer (or a contact) can be deleted, and not all of these checks can be done by querying the database. Sometimes triggers aren't enough. > 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 The "delete contact" and "delete customer" functions are not stored procedures, so I'm not sure how this can help. thanks, stefan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Unicode problem inserting records - Invalid UNICODE character
show client_encoding gives: UNICODE databases reads: DATABASE OWNER ENCODING mydb david UNICODE testdb david SQL_ASCII table columns are character varying(255) Tx, David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Richard Huxton Sent: Wednesday, November 10, 2004 10:34 AM To: David B Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Unicode problem inserting records - Invalid UNICODE character David B wrote: > My first time using unicode. Based on reading other messages I think I've > got it all setup correctly but still have prob. > Running: psql 7.3.6-RH [snip] > For both I get the same results when I try to: > > INSERT INTO airport_code ( airport_name, airport_code ) values ( 'Zurich > (Zürich) - Kloten', 'ZRH' ) ; > > > I get: > > psql:airport_codes.sql:1728: ERROR: Invalid UNICODE character sequence > found (0xfc7269) > > Obviously I'd like to keep the proper German spelling. What is your client encoding? (look into "show client_encoding", "\encoding" in the "Localization" chapter of the manuals) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings --- --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.771 / Virus Database: 518 - Release Date: 9/28/2004 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
