[GENERAL] Do transactions rollback by default?
I am very much a self taught sql programmer, and I only use it for a few small projects at home. Work requires just a bit of db work on my part, there are others who do the heavy lifting :-) I was surprised when one of my heavy lifting co-workers told me that when a transaction is begun, if the client disconnects (program bug, computer crash, whatever) without doing an explicit commit or rollback, the default in both Oracle and Sybase, and probably in every SQL database, is to commit. This seems completely backwards to me. For instance, the example from Practical SQL Handbok of transferring money from one account to another, you sure don't want any changes at all if the client disconnects after having subtracted money from one account but before having added that same amount to the second account. Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o
RE: [GENERAL] Resend: Using some indexes but not others
Maybe you didn't send the right snippet from your SQL, but your example shows you building an index on "ref_ref_inspec", and the query you are having a problem with has a WHERE clause referencing "ref_ref_article". -Original Message- From: Peter Haworth [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 16, 2000 2:24 AM To: Postgres general list Subject: [GENERAL] Resend: Using some indexes but not others I sent this to the list a month ago, but got no response. Does anyone have an idea what's going on here? --- Begin Forwarded Message --- I have a table with a few indexes, which I thought were going to speed up queries using the indexed columns. however, this doesn't appear to be the case. I've got something like this, but with more columns: create table jnl_refs ( ref_article varchar(26) not null, ref_seqno integer not null, ref_ref_article varchar(26) not null, primary key(ref_article,ref_seqno) ); create index jnl_refs_ix_2 on jnl_refs using btree(ref_ref_inspec); I would expect both of the following queries to use the appropriate index, especially after a vacuum analyze: journals2= explain select * from jnl_refs journals2- where ref_article='1367-2630/1/1/001'; NOTICE: QUERY PLAN: Index Scan using jnl_refs_pkey on jnl_refs (cost=34.70 rows=334 width=284) EXPLAIN journals2= explain select * from jnl_refs journals2- where ref_ref_article='1367-2630/1/1/001'; NOTICE: QUERY PLAN: Seq Scan on jnl_refs (cost=18509.01 rows=219589 width=284) EXPLAIN What may be causing this is that 232000 rows out the 249000 in the table have ref_ref_article=''. Initially, the ''s were nulls, and I'd like them to go back to being null if possible, but I thought that might be what was screwing up the index. This is with Postgres 6.5.3 on Solaris 2.5.1. Is this expected behaviour, and if so, is there something I can do to force the use of the index. Illustra allowed "select ... using(index=jnl_refs_is_2)", but I can't see anything like that in the documentation. --- End Forwarded Message --- -- Peter Haworth [EMAIL PROTECTED] Q: How many developers does it take to change a light bulb? A: The light bulb works fine on the system in my office . . .
Re: [GENERAL] Do transactions rollback by default?
[EMAIL PROTECTED] wrote: I am very much a self taught sql programmer, and I only use it for a few small projects at home. Work requires just a bit of db work on my part, there are others who do the heavy lifting :-) I was surprised when one of my heavy lifting co-workers told me that when a transaction is begun, if the client disconnects (program bug, computer crash, whatever) without doing an explicit commit or rollback, the default in both Oracle and Sybase, and probably in every SQL database, is to commit. This seems completely backwards to me. For instance, the example from Practical SQL Handbok of transferring money from one account to another, you sure don't want any changes at all if the client disconnects after having subtracted money from one account but before having added that same amount to the second account. Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. From the Oracle 7 SQL Language Reference Manual: "Oracle Corporation recommends that you explicitly end every transaction your application programs with a COMMIT or ROLLBACK statement,including the last transaction, before disconnecting from ORACLE. If you do not explicitly commit the transaction and the program terminates abnormally, the last uncommitted transaction is automatically rolled back. A normal exit from most ORACLE utilities and tools causes the current transaction to be committed. A normal exit from an ORACLE Precompiler program does not commit the transaction and relies on ORACLE to rollback the current transaction. See the COMMIT command (Embedded SQL) in the next section." So, apparently, your friend is assuming that since SQL*Plus is performing a COMMIT when the user exits normally, all transactions are being committed on disconnect. This is most definitely NOT true. You're right to feel your friend's statements were completely backwards. Hope that helps, Mike Mascari -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o
Re: [GENERAL] Do transactions rollback by default?
I am very much a self taught sql programmer, and I only use it for a few small projects at home. Work requires just a bit of db work on my part, there are others who do the heavy lifting :-) I was surprised when one of my heavy lifting co-workers told me that when a transaction is begun, if the client disconnects (program bug, computer crash, whatever) without doing an explicit commit or rollback, the default in both Oracle and Sybase, and probably in every SQL database, is to commit. This seems completely backwards to me. For instance, the example from Practical SQL Handbok of transferring money from one account to another, you sure don't want any changes at all if the client disconnects after having subtracted money from one account but before having added that same amount to the second account. Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. Disconnect should abort the transaction. It does in PostgreSQL, and I _hope_ every other database. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] one more sql query question
I believe what you want is called a "left outer join": include all records in one table (projects) and records that match it on the joined tables (all the others). Try something like this (UNTESTED): SELECT p.project_id, p.project_title, SUM(t.timespent) FROM project p, subproject s, timelog t WHERE p.project_id *= s.projectid AND t.jobid = s.subproject_id GROUP BY p.project_id; (assuming a project id and a project title are one to one, there's no need to group by both of them.) - Andrew J. Perrin - [EMAIL PROTECTED] - NT/Unix Admin/Support Department of Demography-University of California at Berkeley 2232 Piedmont Avenue #2120 -Berkeley, California, 94720-2120 USA http://demog.berkeley.edu/~aperrin --SEIU1199 On Thu, 16 Mar 2000, Kevin Heflin wrote: I have the following query which will list projects, which totals up the timespent on jobs select p.project_id, p.project_title, sum(case when t.jobid=s.subproject_id then t.timespent else 0::float4 end) FROM project p, subproject s, timelog t where p.project_id=s.projectid and t.jobid=s.subproject_id GROUP BY p.project_id, p.project_title; This works great assuming that a 'project' has 'jobs' assigned to it. and that one of those jobs has had 'timespent' on it... so if a project does not yet have jobs assigned to it, and therefor no timespent on it.. it doesn't show up in the results. Is there anyway around this? in otherwords, I would like to retrieve a complete list of projects regardless of whether or not it has jobs assigned to it.. Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Production | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] Do transactions rollback by default?
Mike Mascari wrote: So, apparently, your friend is assuming that since SQL*Plus is performing a COMMIT when the user exits normally, all transactions are being committed on disconnect. This is most definitely NOT true. You're right to feel your friend's statements were completely backwards. Would be interesting to see what SQL*Plus does if you explicitly kill it with SIGKILL (-9). That would tell if it runs the connection in autocommit mode by default or not. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #= [EMAIL PROTECTED] (Jan Wieck) #
[GENERAL] large object size
How can I find out size in bytes of a large object?
[GENERAL] OID question
I would like to use the oid as a unique key for a table but in creating
a test table and playing around with inserts, things don't seem to be
working as I thought they would.
create table test (
key oid,
desc varchar(30));
Then I do an insert
insert into test (desc) values ('info');
which returns 100448 1 (i'm assuming 100448 is the oid for that row).
But when I look at the table 100448 is not showing up on the key column
key|desc
---+
|info
What I expected was
key |desc
--+
100448|info
Where am I going wrong?
Chris
RE: [GENERAL] Do transactions rollback by default?
I ran a couple of tests against an Oracle 7.3.4 database. I inserted a row into a table, then immediately typed exit. The insert was committed automatically. I also did an insert and then killed SQL*Plus with a SIGKILL from another window. The transaction was NOT committed. So, by default, SQL*Plus starts up with option AUTOCOMMIT set to OFF, and if one performs some INSERT/UPDATE/DELETE, an EXIT will in fact commit the transaction for you. If the session is abnormally terminated, the transaction is NOT committed. Hope this helps. Phil Culberson DAT Services -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 16, 2000 11:07 AM To: Mike Mascari Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [GENERAL] Do transactions rollback by default? Mike Mascari wrote: So, apparently, your friend is assuming that since SQL*Plus is performing a COMMIT when the user exits normally, all transactions are being committed on disconnect. This is most definitely NOT true. You're right to feel your friend's statements were completely backwards. Would be interesting to see what SQL*Plus does if you explicitly kill it with SIGKILL (-9). That would tell if it runs the connection in autocommit mode by default or not. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #= [EMAIL PROTECTED] (Jan Wieck) #
[GENERAL] Re: Setting One Column Relative to Another
[EMAIL PROTECTED] wrote: This is probably the best way to do this sort of thing, however as an addition, for the benefit of the writer of the question, I'd like to warn him that it's not exactly the same behavior that a default value gives you. insert into t1 (a,b) values (1, NULL) will give you different behavior for a standard default value and a trigger like the above. In the former you'll get a NULL inserted, in the latter, you'll get the default value. If you need to insert NULLs, you can either insert and then update, or find another value to trigger the behavior on (such as setting a default -1 and having the if be IF new.b = -1 THEN ). Also, there's at least one other case (SET DEFAULT action for referential integrity) that grabs the default value and uses it. The system isn't going to know that this trigger is effectively a default value, so anything that relies on pulling the defaults normally is likely to not work the way you expect. If you don't mind losing the ability to update to NULL (or another value you've chosen), you could actually run the trigger before insert or update which should at least work for the SET DEFAULT ri action. Thanks for the clarification. I see the difference between a trigger and a default value. In my case, I want to prevent NULLs from being inserted in this column so that aspect of the difference isn't a problem. However, in addition to letting the system know what I'm doing, being able to do something like the following would be easier to write and understand. CREATE TABLE t1 ( a INTEGER NOT NULL, b INTEGER NOT NULL DEFAULT a + 10 ); Is something like this possible? That exact syntax produces "ERROR: Cannot use attribute(s) in DEFAULT clause." which isn't very promising if I correctly understand this use of the word "attribute". Also, the CREATE TABLE documentation is a bit confusing on the matter. At one point it says "Default niladic-function or user-function means that the default is the value of the specified function at the time of the INSERT", but shortly afterwards it says "In the current release (v6.5), Postgres evaluates all default expressions at the time the table is defined." Rodney
Re: [GENERAL] Postgres JDBC Connection
Andreas, Not to go too far out of the scope of this list, but what does your connection code look like? are you trying to import the driver via an "import" statement (don't do this). Also, did you build the JDBC driver for JDK 1.1, or JDK1.2, there's some documentation in the source directory about how to do this--you should be building for 1.1. (note--jdk1.2.2 is out for Linux from Sun, and it seems to work quite well for me -- using jsp and servlets) -Jason Andreas Jerke [EMAIL PROTECTED] on 03/16/2000 03:41:12 PM Please respond to "[EMAIL PROTECTED]" [EMAIL PROTECTED] To: "PGSQL-General (E-mail) (E-Mail)" [EMAIL PROTECTED] cc: bcc: Subject: [GENERAL] Postgres JDBC Connection Hello Postgres User, I can not get a DataBase Connection over JDBC. I have configured the pg_hba.conf (host all 127.0.0.1 255.255.255.255 trust), so that all local processes like Jserv are able to connect to the DB over TCP/IP. I have installed the latest JDBC driver and JAVA finds the class postgresql.Driver. But when I try to connect to the DB, I allways get: SQLExeption: No suitable driver SQLSTate: 08001 VendorError: 0 mmhhh. Perhaps my postgres is not supported bei JDBC ? My System: SuSE 6.3 JDK 1.1.7 JDBC: jdbc6.5-1.1.jar --- does this mean: postgres 6.5.X and jdk 1.1.X ?? Postgres: postgresql-6.5.3.tar.gz I read serveral FAQ's but could not fix my problem. thanks for reading, Andreas
Re: [GENERAL] Postgres JDBC Connection
Your problem probably has nothing to do with Postgres. It looks like your java program cannot find the driver classes. Make sure that the driver is in your classpath. If you are using Apache+Jserv, add the driver to Jserv's classpath as well. Hello Postgres User, I can not get a DataBase Connection over JDBC. I have configured the pg_hba.conf (host all 127.0.0.1 255.255.255.255 trust), so that all local processes like Jserv are able to connect to the DB over TCP/IP. I have installed the latest JDBC driver and JAVA finds the class postgresql.Driver. But when I try to connect to the DB, I allways get: SQLExeption: No suitable driver SQLSTate: 08001 VendorError: 0 mmhhh. Perhaps my postgres is not supported bei JDBC ? My System: SuSE 6.3 JDK 1.1.7 JDBC: jdbc6.5-1.1.jar --- does this mean: postgres 6.5.X and jdk 1.1.X ?? Postgres: postgresql-6.5.3.tar.gz I read serveral FAQ's but could not fix my problem. thanks for reading, Andreas
[GENERAL] Update Timestamp
What is the correct way to create a table column that timestamps itself whenever an insert OR update occurs. Here is my guess create table ( x int2 modtime timestamp current() ); but from reading the docs on the website, it seems that current() gives you a timestamp when the information is read, rather than updated. Also will this do the same thing as timestamp default now() to timestamp inserted rows? Or, am I going about this in the wrong way and there is something like an oid, hidden, that contains this information already? Thanks Chris
Re: [GENERAL] OID question
Chris Sutton wrote: I would like to use the oid as a unique key for a table but in creating a test table and playing around with inserts, things don't seem to be working as I thought they would. Where am I going wrong? There is a hidden column named 'oid'. Try "select oid, desc from test". Regards, Ed Loehr P.S.: An alternative approach worthy of consideration is to use a SEQUENCE object and possibly the SERIAL type.
Re: [GENERAL] Do transactions rollback by default?
Phil Culberson wrote: I ran a couple of tests against an Oracle 7.3.4 database. Thanks! I inserted a row into a table, then immediately typed exit. The insert was committed automatically. I also did an insert and then killed SQL*Plus with a SIGKILL from another window. The transaction was NOT committed. So, by default, SQL*Plus starts up with option AUTOCOMMIT set to OFF, and if one performs some INSERT/UPDATE/DELETE, an EXIT will in fact commit the transaction for you. If the session is abnormally terminated, the transaction is NOT committed. There must be something I have in common with the Oracle engineers (the ones who design and code, not the management and sales staff). What else could cause that I expected exactly that behaviour? Hope this helps. Did. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #= [EMAIL PROTECTED] (Jan Wieck) #
[GENERAL] Is this possible?
Hi,
I would like to do something like
select attr from table
where
oneof list1 IN list2;
such as
select attr from table where
oneof ('apple', 'orange', 'banana') IN ('mango','coconut','banana');
list1 and list2 are attributes in some table(s).
I have three options:
1) There is actually some SQL that does this?
2) I do it with a procedural language function?
3) SQL cannot do it and I need to use perl to do the iteration over a series
of queries with foreach instead of 'oneof'?
What say you?
Thanks, John
Re: [GENERAL] Do transactions rollback by default?
In article [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] writes: Could someone enlighten me here? He seemed absolutely positive that a disconnect is as good as a commit and always has been. Disconnect should abort the transaction. It does in PostgreSQL, and I _hope_ every other database. Thanks for all the replies, and saving my sanity. The heavy lifter confirms what some supposed, that he is only talking about the console / GUI front end. He was guessing about programming based on that. I tried psql, and it does NOT default to confirm, which is fine with me. I personally would find it confusing to have the front end default to confirm on exit. I'd rather have it behave the same as a program. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o
