[SQL] A transaction in transaction? Possible?
Is it possible to have another transatction in a transaction??? In the following example the last ROLLBACK is totally ignored(transaction1). //connect to database$database = dbConnect($dbhost, $dbuser, $dbpass, $dbname);dbExec($database, "BEGIN"); //transaction1//* dbExec($database, "BEGIN");//transaction2 $sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143"; dbExec($database, $sql); dbExec($database, "COMMIT");//transaction2/**/ $sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143"; dbExec($database, $sql);dbExec($database, "ROLLBACK");//transaction1dbClose($database);
Re: [SQL] Simple SQL Question
Franco Bruno Borghesi wrote: Hi! Sorry for being late with the answer, I was busy at one of our customer;) wouldn't it be easier using offset & limit?: you always select from the table with an itemkey,location order by clause. You save the current offset between requests, and for every request you re-execute the query with a different offset. Sure, but sometimes I have to query by name, and dont want to create another query component. If you still want to go with what you already have, you should keep the lastItemKey and lastLocaltion values between requests, and your where clause should be something like: WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR (itemKey>:lastItemKey) It works perfectly, but I have to consider the performance issue, because if i use 'or' statement, postgres doesn't use index scan, and I also have tables with 3 or more keys and 500.000 records , where the performance of this method is poor. Maybe I will end up using limit and offset in case of incremental fetching,but if the table is modified between requests, it can behave weird. Is there a perfect solution to this? Sorry for the english Thank you again, Andras Kutrovics ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] INSERT INTO VIEW - Replacement
Hi,
I've this data model:
CREATE SEQUENCE a_seq START 1;
CREATE SEQUENCE b_seq START 1;
CREATE TABLE a (
aid integer NOT NULL PRIMARY KEY,
aval character varying (255) NOT NULL
);
INSERT INTO a (select nextval('a_seq'),'a1');
INSERT INTO a (select nextval('a_seq'),'a2');
CREATE TABLE b (
bid integer NOT NULL PRIMARY KEY,
bval character varying (255) NOT NULL
);
INSERT INTO b (select nextval('b_seq'),'b1');
INSERT INTO b (select nextval('b_seq'),'b2');
CREATE TABLE c (
cid integer NOT NULL,
aid integer REFERENCES a (aid),
bid integer REFERENCES b (bid),
cval character varying (255) NOT NULL,
PRIMARY KEY (cid)
);
CREATE VIEW myview AS SELECT cid,aval,bval,cval FROM c INNER JOIN a ON
(a.aid=c.aid) INNER JOIN b ON (b.bid=c.bid);
Now I'd like to insert a row into "c" and if necessary
simultaneously create the referenced rows in
the referenced tables:
INSERT INTO myview VALUES (10,'a3','b1','c1');
(Here a row in "a" with aval 'a3' should be
created)
INSERT INTO myview VALUES (20,'a1','b2','c2');
(here a row in "a" with aval 'a1' should not
be created but referenced.)
(Same for "b").
"Insert into view" would't work, how could I do
my INSERTS efficiently in postgres without
having eache time an extra query which asks
whether there already is a row in "a" which
could be referenced to.
ie mache ich das
Thanks!
Marc
_
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.com/
---(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?
On Tue, Nov 09, 2004 at 10:47:06AM +0200, Andrei Bintintan wrote: > Is it possible to have another transatction in a transaction??? PostgreSQL 8.0 (currently in beta) has savepoints, so you'll be able to do this: BEGIN; UPDATE orders SET technikernotiz='51' WHERE id=16143; SAVEPOINT foo; UPDATE orders SET reklamationsdetail='51' WHERE id=16143; ROLLBACK TO foo; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] tricky GROUP BY / JOIN question
Hello Tom, Tom Lane wrote: T E Schmitz <[EMAIL PROTECTED]> writes: This is *almost* what I need: SELECT BRAND.BRAND_NAME, MODEL.MODEL_NAME, min (ITEM.PRICE),max (ITEM.PRICE) *min (CONDITION.POSITION),max (CONDITION.POSITION)* FROM ITEM left outer join MODEL on MODEL_PK =ITEM.MODEL_FK left outer join BRAND on BRAND.BRAND_PK =MODEL.BRAND_FK left outer join CONDITION on CONDITION.CONDITION_PK = ITEM.CONDITION_FK group by BRAND.BRAND_NAME,MODEL.MODEL_NAME In the result I don't want min/max(POSITION) but CONDITION.NAME of min and max(POSITION) for each MODEL. (select name from condition c1 where position = min(condition.position)), (select name from condition c2 where position = max(condition.position)), Thank you, Tom, this worked a treat! -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(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
[SQL] Drop all indexes of a table w/o knowing the index names
7.4.6 on Linux. I need a way to drop all indexes of a table without knowing the names of the indexes. Say I have a table table1 index1 index2 index3 I don't want to do drop index1; drop index2; drop index3; but I want drop is this possible? I looked in the manual at pg_index, but couldn't build an sql string to do it. Thanks -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] upper/lower for german characters
Hi to all, I have the problem that:
select lower('MöBÜEL') or select upper('MöBÜEL')
are not working well.
I read on some forums that there is some locale
setting that needs to be done here, but could not fix this.
I am using the ASCII encoding.
Please advice.
Thakx.Andy.
Re: [SQL] Drop all indexes of a table w/o knowing the index names
O Giulio Orsero έγραψε στις Nov 9, 2004 : > 7.4.6 on Linux. > > I need a way to drop all indexes of a table without knowing the names of the > indexes. > > Say I have a table > > table1 > index1 > index2 > index3 > > I don't want to do > > drop index1; > drop index2; > drop index3; > > but I want > > drop if your are using default namespace (schema) % tcsh % foreach i ( `psql -t -q -c "SELECT ci.relname from pg_index i,pg_class ci,pg_class ct where i.indexrelid=ci.oid and i.indrelid=ct.oid and ct.relname='YOUR_TABLE_HERE'"` ) foreach? psql -c "drop index $i" foreach? end > > is this possible? I looked in the manual at pg_index, but couldn't build an > sql string to do it. > > Thanks > > -- -Achilleus ---(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?
I thought nested transactions are available in the new release (8) coming up. Ted --- Andrei Bintintan <[EMAIL PROTECTED]> wrote: > Is it possible to have another transatction in a > transaction??? In the following example the last > ROLLBACK is totally ignored(transaction1). > > //connect to database > $database = dbConnect($dbhost, $dbuser, $dbpass, > $dbname); > dbExec($database, "BEGIN"); //transaction1 > > //* > dbExec($database, "BEGIN");//transaction2 > $sql = "UPDATE orders SET > technikernotiz='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "COMMIT");//transaction2 > /**/ > > $sql = "UPDATE orders SET > reklamationsdetail='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "ROLLBACK");//transaction1 > > dbClose($database); > > > __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Loading text data/binary data !!
Hello all, 2 questions ! Question 1 Iam trying to load binary data from sql server to postges. Do i have to write a script .. ?? Question 2 How i do load text data with newlines into postgres database .. (as newline is the default row delimiter and cannot be changed in the column table). 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.
Re: [SQL] Simple SQL Question
Didn't know about the seqscan problem when using ORs. But you still can split the query in two, and then use Union to join the results: SELECT WHERE itemKey=:lastItemKey AND location>:lastLocation UNION SELECT ... WHERE itemKey>:lastItemKey You could solve the OFFSET/LIMIT modification problem if you could keep the transaction open, but I don't know if it's applicable in your case. Andras Kutrovics wrote: Franco Bruno Borghesi wrote: Hi! Sorry for being late with the answer, I was busy at one of our customer;) wouldn't it be easier using offset & limit?: you always select from the table with an itemkey,location order by clause. You save the current offset between requests, and for every request you re-execute the query with a different offset. Sure, but sometimes I have to query by name, and dont want to create another query component. If you still want to go with what you already have, you should keep the lastItemKey and lastLocaltion values between requests, and your where clause should be something like: WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR (itemKey>:lastItemKey) It works perfectly, but I have to consider the performance issue, because if i use 'or' statement, postgres doesn't use index scan, and I also have tables with 3 or more keys and 500.000 records , where the performance of this method is poor. Maybe I will end up using limit and offset in case of incremental fetching,but if the table is modified between requests, it can behave weird. Is there a perfect solution to this? Sorry for the english Thank you again, Andras Kutrovics ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] upper/lower for german characters
Hi, Andrei,
On Tue, 9 Nov 2004 16:58:27 +0200
"Andrei Bintintan" <[EMAIL PROTECTED]> wrote:
> Hi to all, I have the problem that:
> select lower('MöBÜEL') or select upper('MöBÜEL') are not working well.
>
> I read on some forums that there is some locale setting that needs to
> be done here, but could not fix this.
>
> I am using the ASCII encoding.
By definition, ASCII does not contain any umlauts.
So I would advise that, first, you switch to an umlaut capable encoding
(e. G. Latin1, Latin9 or UTF-8).
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(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] Drop all indexes of a table w/o knowing the index names
On Tue, 9 Nov 2004 17:30:25 +0200 (EET), Achilleus Mantzios <[EMAIL PROTECTED]> wrote: >O Giulio Orsero Ýãñáøå óôéò Nov 9, 2004 : >> I need a way to drop all indexes of a table without knowing the names of the >> indexes. >% foreach i ( `psql -t -q -c "SELECT ci.relname from pg_index i,pg_class >ci,pg_class ct where i.indexrelid=ci.oid and i.indrelid=ct.oid and >ct.relname='YOUR_TABLE_HERE'"` ) >foreach? psql -c "drop index $i" >foreach? end Ok, worked as expected. thanks -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Aggregate like AVG() with Money Data Type
Hello Friends How can I use the aggregate AVG() with a column MONEY? Thanks in Advance André (Brazilian User) __ Acabe com aquelas janelinhas que pulam na sua tela. AntiPop-up UOL - É grátis! http://antipopup.uol.com.br/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Comparing two (largish) tables on different servers
This is probably a silly question. Our runtime deployment of database servers (7.4) involves some redundant/duplicate databases. In order to compare tables (about 5 gigs each) on different servers I unload the things (takes a while etc.), sort them with a UNIX sort and then do a cksum on them. 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 ... Thanks, Greg Williamson DBA GlobeXplorer LLC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Comparing two (largish) tables on different servers
Idea : Write a program which connects on the two databases, creates a cursor on each to return the rows in order, then compare them as they come (row 1 from cursor 1 == row 1 from cursor 2, etc). Fetch in batchs. If there's a difference you can then know which row. I hope you have an index to sort on, to save you a huge disk sort. On Tue, 9 Nov 2004 14:41:00 -0800, Gregory S. Williamson <[EMAIL PROTECTED]> wrote: This is probably a silly question. Our runtime deployment of database servers (7.4) involves some redundant/duplicate databases. In order to compare tables (about 5 gigs each) on different servers I unload the things (takes a while etc.), sort them with a UNIX sort and then do a cksum on them. 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 ... Thanks, Greg Williamson DBA GlobeXplorer LLC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Aggregate like AVG() with Money Data Type
On Tue, Nov 09, 2004 at 06:40:53PM -0200, andre.toscano wrote: > How can I use the aggregate AVG() with a column MONEY? The MONEY type has been deprecated since at least PostgreSQL 7.0. Is there a reason you're not using NUMERIC? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] UPDATE/INSERT on multiple co-dependent tables
Is it possible for an UPDATE/INSERT query string to function in such a way that it requires two like fields in different tables to be equal to/'in sync with' one another: Example: I have two tables: registration & schedules they both record a class_id, start_date, end_date... I want to make sure that if the schedule_id field is updated in the registration table; that class_id, start_date & end_date fields automatically change to match the schedules.id record in the schedules table I've devised a function to handle this but pgsql recognizes the query to be 'infinitely recursive: CREATE RULE registration_update AS ON UPDATE TO registration DO UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id = (SELECT schedules.id FROM schedules WHERE id = new.schedule_id); What I'm doing is kind of redundant but necessary for backwards-compatibility ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Is NULLIF nullable?
Recently I asked about why a field from the nullable side of an OUTER JOIN was causing the JOIN to be inefficient, and was told that it was because that field had a CASE statement as part of its definition, and that CASE (and by extension COALESCE) were non-nullable constructs. Is NULLIF nullable, in that sense? Reason: I'd like to define a VIEW with a field definition thusly: 'P.O. Box' || NULLIF( po_box, ' '::char ) AS ... And I would like the field thusly defined to be nullable. -- Dean ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] UPDATE/INSERT on multiple co-dependent tables
On Tue, 9 Nov 2004, Ferindo Middleton, Jr wrote: > Is it possible for an UPDATE/INSERT query string to function in such a way > that it requires two like fields in different tables to be equal to/'in sync > with' one another: > > Example: I have two tables: registration & schedules > they both record a class_id, start_date, end_date... I want to make sure > that if the schedule_id field is updated in the registration table; that > class_id, start_date & end_date fields automatically change to match the > schedules.id record in the schedules table I've devised a function to > handle this but pgsql recognizes the query to be 'infinitely recursive: > > CREATE RULE registration_update AS > ON UPDATE TO registration > DO > UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id > = (SELECT schedules.id FROM schedules WHERE id = new.schedule_id); > > What I'm doing is kind of redundant but necessary for > backwards-compatibility You would probably have better luck doing something like the above in a before trigger rather than a rule by having the before trigger change NEW.class_id to the desired value. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is NULLIF nullable?
You can ignore my question below, since I just put the field definition below in an SQL FUNCTION and marked it STRICT. -- Dean Dean Gibson (DB Administrator) wrote on 2004-11-09 19:29: Recently I asked about why a field from the nullable side of an OUTER JOIN was causing the JOIN to be inefficient, and was told that it was because that field had a CASE statement as part of its definition, and that CASE (and by extension COALESCE) were non-nullable constructs. Is NULLIF nullable, in that sense? Reason: I'd like to define a VIEW with a field definition thusly: 'P.O. Box' || NULLIF( po_box, ' '::char ) AS ... And I would like the field thusly defined to be nullable. -- Dean ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] A transaction in transaction? Possible?
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 ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] A transaction in transaction? Possible?
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
