Re: [SQL] A transaction in transaction? Possible?
Sorry, but I understand that your example is not really about nested
transactions, but about sequential transactions.
so, the primary question remains:
how to commit/rollback them ?
--- Michael Fuhr <__> wrote:
> On Wed, Nov 10, 2004 at 09:23:02AM +0300, sad wrote:
> > On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote:
> > > I thought nested transactions are available in the new
> > > release (8) coming up.
> >
> > how to commit/rollback them ?
>
> CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>
> BEGIN;
> INSERT INTO person (name) VALUES ('Tom');
> SAVEPOINT foo;
> INSERT INTO person (name) VALUES ('Dick');
> ROLLBACK TO foo;
> INSERT INTO person (name) VALUES ('Harry');
> COMMIT;
>
> SELECT * FROM person;
> id | name
> +---
> 1 | Tom
> 3 | Harry
> (2 rows)
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---(end of
> broadcast)---
> TIP 8: explain analyze is your friend
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Comparing two (largish) tables on different servers
Gregory S. Williamson wrote: >Is there any way to do this from inside postgres that anyone knows of >? I looked through the manual and the contrib stuff and didn't see >much ... Not really "inside postgres"; but could you do something like: mkfifo db1 psql -h "db1" -t -q -c "$query" > db1 mkfifo db2 psql -h "db2" -t -q -c "$query" > db2 diff -u -0 db1 db2 That should work with most shells under Unix. . . Have fun, Sam ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] A transaction in transaction? Possible?
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;
SELECT * FROM person;
id | name
+
1 | Alice
2 | Bob
6 | Frank
7 | George
If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
id | name
+-
1 | Alice
2 | Bob
3 | Charles
5 | Edward
6 | Frank
7 | George
If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
id | name
+
1 | Alice
7 | George
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(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] A transaction in transaction? Possible?
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?
>
> SELECT * FROM person;
> id | name
> +
> 1 | Alice
> 2 | Bob
> 6 | Frank
> 7 | George
>
> If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
>
> id | name
> +-
> 1 | Alice
> 2 | Bob
> 3 | Charles
> 5 | Edward
> 6 | Frank
> 7 | George
>
> If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
>
> id | name
> +
> 1 | Alice
> 7 | George
>
>
--
-Achilleus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] A transaction in transaction? Possible?
Okay, I see you're speaking about pgsql 8.0
What about 7.4?
Andy.
- Original Message -
From: "Achilleus Mantzios" <[EMAIL PROTECTED]>
To: "Michael Fuhr" <[EMAIL PROTECTED]>
Cc: "Riccardo G. Facchini" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; "Theodore Petrosky" <[EMAIL PROTECTED]>; "Andrei
Bintintan" <[EMAIL PROTECTED]>; "sad" <[EMAIL PROTECTED]>
Sent: Wednesday, November 10, 2004 12:58 PM
Subject: Re: [SQL] A transaction in transaction? Possible?
> 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?
>
> >
> > SELECT * FROM person;
> > id | name
> > +
> > 1 | Alice
> > 2 | Bob
> > 6 | Frank
> > 7 | George
> >
> > If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
> >
> > id | name
> > +-
> > 1 | Alice
> > 2 | Bob
> > 3 | Charles
> > 5 | Edward
> > 6 | Frank
> > 7 | George
> >
> > If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
> >
> > id | name
> > +
> > 1 | Alice
> > 7 | George
> >
> >
>
> --
> -Achilleus
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] A transaction in transaction? Possible?
O Peter Eisentraut έγραψε στις Nov 10, 2004 : > Achilleus Mantzios wrote: > > Wouldn't make more sense to allow nested begin/commit/rollback > > blocks? > > Possibly. But that consideration would have been more relevant about 6 > years ago when they wrote the SAVEPOINT syntax into the SQL standard. > :) In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; can be replaced with SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. If only transactions weren't a requirement for SAVEPOINTs, what would we then need BEGIN; COMMIT; ROLLBACK; for? > > -- -Achilleus ---(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] A transaction in transaction? Possible?
Achilleus Mantzios wrote: > Wouldn't make more sense to allow nested begin/commit/rollback > blocks? Possibly. But that consideration would have been more relevant about 6 years ago when they wrote the SAVEPOINT syntax into the SQL standard. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] A transaction in transaction? Possible?
Achilleus Mantzios wrote: > In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; > can be replaced with > SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. > > If only transactions weren't a requirement for SAVEPOINTs, > what would we then need BEGIN; COMMIT; ROLLBACK; for? Note that under the current arrangement, it doesn't make much sense to "commit" a subtransaction. It will be committed anyway when the main transactions commits, and you cannot commit it earlier because the main transaction could still roll back. So savepoint blocks are not really transactions, but more like semi-transactions. In other nested transaction models, things can be different. If you have so-called open nested transactions, which expose their results to other transactions already before the parent transaction commits, then a subtransaction commit is useful. But that behavior violates the isolation criterion of transactions and therefore needs additional facilities to behave tolerably. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Error In connection ??
Hello, I get an error in my log when connecting to postgres server on Windows. Postgres version : 8.0.0-beta4 LOG TEXT 2004-11-10 11:22:47 LOG: invalid entry in file "C:/Program Files/PostgreSQL/8.0.0-beta4/data/pg_hba.conf" at line 64, token "192.168.2.1/254" 2004-11-10 11:22:47 FATAL: missing or erroneous pg_hba.conf file 2004-11-10 11:22:47 HINT: See server log for details. Configuration file pg_hba.conf hostall all 192.168.2.1/254md5 hostall all 127.0.0.1/32 md5 Any ideas how to fix it ?? Thanks ! Goutam Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Unicode problem inserting records - Invalid UNICODE character sequence found (0xfc7269)
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 $ psql -l List of databases Name| Owner | Encoding ---+--+--- devdb | devuser | UNICODE template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII I've tried two different table structures: CREATE TABLE airport_code ( airport_name character varying(70), airport_code character varying(10), ) ; and the same but with "text" instead of character varying. 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 am I missing? Gotta figure its me...cause PG is so damm good otherwise. Tx, D --- 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 8: explain analyze is your friend
Re: [SQL] Error In connection ??
Goutam Paruchuri wrote: Hello, I get an error in my log when connecting to postgres server on Windows. Postgres version : 8.0.0-beta4 LOG TEXT 2004-11-10 11:22:47 LOG: invalid entry in file "C:/Program Files/PostgreSQL/8.0.0-beta4/data/pg_hba.conf" at line 64, token "192.168.2.1/254" 2004-11-10 11:22:47 FATAL: missing or erroneous pg_hba.conf file 2004-11-10 11:22:47 HINT: See server log for details. Configuration file pg_hba.conf hostall all 192.168.2.1/254 md5 You don't have 254 bits in in IP address. Did you mean /32? -- Richard Huxton Archonet Ltd ---(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] A transaction in transaction? Possible?
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. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Error In connection ??
My understanding was it means ip range of 1 to 254. 192.168.2.1 to 192.168.1.254 > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 10, 2004 12:19 PM > To: Goutam Paruchuri > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Error In connection ?? > > > Goutam Paruchuri wrote: > > Hello, > > > > I get an error in my log when connecting to postgres server > on Windows. > > Postgres version : 8.0.0-beta4 > > > > LOG TEXT > > > > 2004-11-10 11:22:47 LOG: invalid entry in file "C:/Program > > Files/PostgreSQL/8.0.0-beta4/data/pg_hba.conf" at line 64, token > > "192.168.2.1/254" > > 2004-11-10 11:22:47 FATAL: missing or erroneous pg_hba.conf file > > 2004-11-10 11:22:47 HINT: See server log for details. > > > > Configuration file pg_hba.conf > > > > hostall all 192.168.2.1/254md5 > You don't have 254 bits in in IP address. Did you mean /32? > > -- >Richard Huxton >Archonet Ltd > Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited. ---(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] Error In connection ??
The IP of the client machine aim connecting from is 192.168.2.123 Which is greater than 32. Thanks ! - Goutam > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 10, 2004 12:19 PM > To: Goutam Paruchuri > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Error In connection ?? > > > Goutam Paruchuri wrote: > > Hello, > > > > I get an error in my log when connecting to postgres server > on Windows. > > Postgres version : 8.0.0-beta4 > > > > LOG TEXT > > > > 2004-11-10 11:22:47 LOG: invalid entry in file "C:/Program > > Files/PostgreSQL/8.0.0-beta4/data/pg_hba.conf" at line 64, token > > "192.168.2.1/254" > > 2004-11-10 11:22:47 FATAL: missing or erroneous pg_hba.conf file > > 2004-11-10 11:22:47 HINT: See server log for details. > > > > Configuration file pg_hba.conf > > > > hostall all 192.168.2.1/254md5 > You don't have 254 bits in in IP address. Did you mean /32? > > -- >Richard Huxton >Archonet Ltd > Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Error In connection ??
Goutam Paruchuri wrote: My understanding was it means ip range of 1 to 254. 192.168.2.1 to 192.168.1.254 No, it refers to the number of bits. You can use either of: 192.168.2.0 255.255.255.0 192.168.2.0/24 Both of these cover the range: 192.168.2.1 - 192.168.1.255 Might be worth searching for a network howto on CIDR subnet formats. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
Re: [SQL] Error In connection ??
Its works if I specify the ipaddress of each client which is being connecting to the server. Its hard to do it on a DHCP network . Any roundabouts ? - Goutam > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 10, 2004 1:30 PM > To: Goutam Paruchuri > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Error In connection ?? > > > Goutam Paruchuri wrote: > > My understanding was it means ip range of 1 to 254. > > > > 192.168.2.1 to 192.168.1.254 > > No, it refers to the number of bits. You can use either of: >192.168.2.0 255.255.255.0 >192.168.2.0/24 > Both of these cover the range: >192.168.2.1 - 192.168.1.255 > > Might be worth searching for a network howto on CIDR subnet formats. > -- >Richard Huxton >Archonet Ltd > Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Comparing two (largish) tables on different servers
On Wed, Nov 10, 2004 at 09:18:21AM +, Sam Mason wrote: > > mkfifo db1 > psql -h "db1" -t -q -c "$query" > db1 > mkfifo db2 > psql -h "db2" -t -q -c "$query" > db2 > diff -u -0 db1 db2 This should work for small data sets, but the OP said the tables were about 5G. Unless you use a cursor, psql will fetch the entire result before writing anything. Also, some implementations of diff might read all of the data from one file before reading much from the other file, especially if the files have differences. Hope you have lots of memory -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] A transaction in transaction? Possible?
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. 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. cheers, stefan ---(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
