Re: [GENERAL] Revisited: Transactions, insert unique.
Joachim Achtzehnter wrote: > > ...It is NOT required that the outcome be > equivalent to the result that would be observed by running the > transactions in a particular order, such as in the order they were > actually started. The outcome is only required to be equivalent to some > (arbitrary) order. That was another fundamental misunderstanding of mine about SERIALIZABLE. I appreciate the explanation, and have a deeper appreciation for Vadim's MVCC work!! Regards, Ed Loehr
[GENERAL] ODBC Stuff
Sorry to ask this as I'm sure it's passed throught here but I'm also sure I just looked at the messages briefly and then deleted them becuase I didn't think I'd ever need the information. I'm working on a program that will run on a MS Windows platform and I want it to use a database (PostreSQL) on a Linux box. From the research I've been doing I get the impression that ODBC is the preferred, if not the only, method to do this. So I'm hoping someone can point me at some simple steps (they don't have to be too detailed) on getting a MS Windows application to talk to a PostgreSQL database across a network. I've made a couple of passes over the unixODBC site and came away with a much better understanding but just gave up on the MS (ODBC) site since it looked like more of an advertisement than an information site. The application is being written in perl (ActiveState) and compiled with perl2exe to make the installation impact (not a full perl installation) on the Windows systems a little lighter. Any and all pointers apprciated, Rod -- Roderick A. Anderson [EMAIL PROTECTED] Altoplanos Information Systems, Inc. Voice: 208.765.6149212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
Re: [GENERAL] Revisited: Transactions, insert unique.
[EMAIL PROTECTED] wrote: > > ...the snapshot is taken from the first DML statement... That explains it. I missed that in the docs, and was mislead by the SERIALIZABLE doc sections. Regards, Ed Loehr
[GENERAL] Re: pgsql odbc
Carsten Huettl schrieb: > > Hello, > > After I altered a table in my database I am not able to connect the > Database with the postgresql-odbc-driver (v.6.40.00.09) > What I get is: > ODBC--call failed. > The Access-Table only shows col-names but nothing else. > > It is a RH6.1Linux with PostgreSQL 6.5.2 ... It seems the postmaster runs out of memory. I checked the postmaster with top while I tried to connect via odbc an it grabs more and more memory. The system has only 32megs and 96megs swap. When postmaster grabs 110megs the odbc-call fails and postmaster releases the memory. How do I correct this behaviour? TIA
Re: [GENERAL] Revisited: Transactions, insert unique.
> Ok so I'm biased to how MySQL does it (it's simple and has a good chance of > working well). Yes it shifts a lot to the application. But if people have > to do things like do their multiple select for updates in the right order > (to prevent deadlocks), they might as well start using something like this > instead (or fix their architecture if possible ;) ). > > And it's likely to be faster! Anyone else here like this arbitrary lock > thingy? > > I'm very interested to know of other ways to achieve good serialisation, > especially database centric methods. > > Cheerio, > > Link. > > p.s. Would anyone actually need timeouts of a day (86400) or greater? Are you asking for sub-second timeout values? If so, we could add that. -- 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
[GENERAL] referencial integrity constraint bug in version 7.0 beta 5
I am running a slackware 7 linux box (kernel 2.2.14 g++2.91.66) and i 've installed the ver 7 beta 5. So i had the following problem I suppose when you make a constraint statement for reference key theere should be a check for the fields names , example: create table tbl1 (p1 int2 primary key,p2 int4); and: create table tbl2(p1 int2 primary key, p2 int2, constraint lala foreign key (p2) references tbl1(po1)); (the o in po1 was misstyped) there was no error message( i didn't noticed it too) when i 've tryed the : insert into tbl1 values (1,1); i got the the error that the there was no field 'po1' for the constraint procedure to check. maybe that's because it's a beta version if this was reported earlier excuse me but i am new to the list Spyros Vasileiadis Salonika Greece
Re: [GENERAL] sequences and Transactions
> Hi! > > how can I setup sequences to have the current-value reset in case > of an Transaction rollback. > > My intension is to get an contignous numbering of the rows. > Currently in case of an Rollback one number is skipped since > the record itself is not inserted but the counter is not reset. People have asked this before. If we did that rollback, we would have to lock the sequence value for the duration of the transaction, which is not good. No real way to do this, sorry. -- 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] sequences and Transactions
Title: RE: [GENERAL] sequences and Transactions You can't. Sequences are not designed for continuity, they are designed for uniqueness. If you want to have a set of contiguous numbers, in ascending order, then you will probably have to write a trigger to insert the next value, which it has to scan the table to work out. And you have to decide what to do in case of deletions: do you reuse the number on the next insert (add complexity and run-time to the code), or just carry on anyway, meaning that you have holes in your sequence, in which case, you could have used a sequence anyway, probably. Depending on the number of expected rows in the table, you may find that the time to insert doesn't justify having contiguous numbers. For each insert, the minimum you are going to get away with is a full table scan. MikeA >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] >> Sent: 27 April 2000 09:39 >> To: [EMAIL PROTECTED] >> Subject: [GENERAL] sequences and Transactions >> >> >> >> >> > -- >> > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] >> > Sent: Thursday, April 27, 2000 10:38:55 AM >> > To: [EMAIL PROTECTED] >> > Subject: [GENERAL] sequences and Transactions >> > Auto forwarded by a Rule >> > >> Hi! >> >> how can I setup sequences to have the current-value reset in case >> of an Transaction rollback. >> >> My intension is to get an contignous numbering of the rows. >> Currently in case of an Rollback one number is skipped since >> the record itself is not inserted but the counter is not reset. >> >> Elmar >>
Re: [GENERAL] I'm just doin' the 7.0 RC1 install and have some inputon the documentation.
[Charset iso-8859-1 unsupported, filtering to ASCII...] > Hey folks, > > Been gettin' situated with 7.0 RC1 and I've come across a couple of places > where small changes to the documentation would have helped. I'd like to > feed this stuff back to the folks doin' the docs as part of my contribution > to the cause, but I don't know who to give it to. Any leads? Someone is working on docs for 7.0. It will be in the final. If yo have any changes you would like to submit, please send them to the patches list. -- 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
[GENERAL] Re: [HACKERS] pgsql/php3/apache authentication
Peter Eisentraut writes: > On Wed, 26 Apr 2000, Jim Mercer wrote: > > > - queries via localhost (unix domain sockets) should assume that the pg_user > > is the same as the unix user running the process. > > There's no way for the server to determine the system user name of the > other end of a domain socket; at least no one has implemented one yet. So > essentially this isn't going to work. The client can pass an SCM_CREDENTIALS (Linux) or SCM_CREDS (BSDish) socket control message down the Unix domain socket and the kernel will fill in the client's credentials (including PID, uid and gid) for the receiver to read. Some Unices don't support this though. If noone else implements this, I'll try to find time to do it myself though I've only touched the server side of pg authentication before and haven't looked at what exactly the client side sends across already. Without SCM_CRED[ENTIAL]S, it gets very messy passing reliable (or even semi-reliable) authentication information. STREAMS has another way to send/receive credentials but not via the socket API. --Malcolm -- Malcolm Beattie <[EMAIL PROTECTED]> Unix Systems Programmer Oxford University Computing Services
[GENERAL] Re: [HACKERS] pgsql/php3/apache authentication
[Charset iso-8859-1 unsupported, filtering to ASCII...] > On Wed, 26 Apr 2000, Jim Mercer wrote: > > > - queries via localhost (unix domain sockets) should assume that the pg_user > > is the same as the unix user running the process. > > There's no way for the server to determine the system user name of the > other end of a domain socket; at least no one has implemented one yet. So > essentially this isn't going to work. The default of "local all trust" is something I allways considered insecure. At least because the unix domain socket isn't changed to mode 0700 after creation, so that only users in the unix dba (or whatever) group are trusted. If we add a permissions field to the local entry, the postmaster can chmod() the socket file after creating it (and maybe drain out waiting connections that slipped in between after a second before accepting the first real one). The default hba would then read: local all trust 0770 host all 127.0.0.1 255.255.255.255 ident sameuser There's IMHO no reason, why the postmaster shouldn't try to create an inet socket bound to 127.0.0.1:pgport by default too. And it must not be considered an error (while some notice would be nice) if the creation of that socket fails. Also we change libpq that if it get's an EPERM at connect(2) to the unix domain socket, it tries again via inet. Some microseconds overhead but transparent for non-dba local users. Now someone can add users, he really trusts to the dba group in /etc/group. Or he can open the entire DB system to all local users by changing the permissions to 0777. 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) #
Re: [GENERAL] pgsql DATE
At 11:03 AM 27-04-2000 +0300, Andras Balogh wrote: >Hi, > >Thank You for your reply. >BUT as i mentioned the date_time field is VARCHAR. >So if i use: >date >= '01/4/2000' and date < '01/5/2000' > >this won't compare the dates it will compare Strings. >so '02/4/2000' will be GREATER than '01/5/2000'. > >That is why i need a DATE TYPE for my field that can be used to create an >index. > >So any other ideas? If you need a date type for your field, use a date type. http://www.postgresql.org/docs/postgres/datatype1143.htm Cheerio, Link.
RE: [GENERAL] Revisited: Transactions, insert unique.
> -Original Message- > From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] > Sent: Thursday, April 27, 2000 4:46 PM > To: Hiroshi Inoue > > On Thu, 27 Apr 2000, Hiroshi Inoue wrote: > > > PostgreSQL's SERIALIZABLE isolation level would allow both inserts. > > READ COMMITED isolation level wouldn't allow A's inserts. > > > > As I mentioned in another posting,PostgreSQL's SERIALIZABLE > > isolation level isn't completely serializable and it's same as Oracle. > > Probably Vadim refers to this incomplete serializability somewhere > > in documentation. > > It seems almost impossible to implement complete serializability > > under MVCC without using table level locking. I love MVCC much > > more than theoretically beautiful complete serializability. > > Given that Postgres correctly recognizes concurrent updates and aborts one > of the transactions, Is what you mean the following ? When a transaction is about to update a row which has been updated by other transactions under SERIALIZABLE isolation level,update is rejected with message ERROR: Can't serialize access due to concurrent update. > how difficult would it be to do the same for inserts? Should INSERT/UPDATE/SELECT .. FOR UPDATE statements wait until being inserted rows to be commit/aborted ? This means INSERT operations block all update operations for the same table. Regards. Hiroshi Inoue [EMAIL PROTECTED]
Re: [GENERAL] pgsql DATE
Hi, Thank You for your reply. BUT as i mentioned the date_time field is VARCHAR. So if i use: date >= '01/4/2000' and date < '01/5/2000' this won't compare the dates it will compare Strings. so '02/4/2000' will be GREATER than '01/5/2000'. That is why i need a DATE TYPE for my field that can be used to create an index. So any other ideas? - Original Message - From: Martijn van Oosterhout <[EMAIL PROTECTED]> To: Andras Balogh <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 26, 2000 5:41 PM Subject: Re: [GENERAL] pgsql DATE > Andras Balogh wrote: > > So i am looking a date type that would allow me to do the following with one > > (or a few) query > > (and to be fast too): > > -select all records for a given month > > -select the count of records where the hour is 14. > > -select all the records from the 7th week. > > -select count of records where the day of the week is Sunday. > > etc. > > How about you write your queries like: > > date >= '01/4/2000' and date < '01/5/2000' > > or equivalent. It allows use of the index but works the same. > Except for the day is Sunday though... > -- > Martijn van Oosterhout <[EMAIL PROTECTED]> > http://cupid.suninternet.com/~kleptog/
RE: [GENERAL] Revisited: Transactions, insert unique.
On Thu, 27 Apr 2000, Hiroshi Inoue wrote: > PostgreSQL's SERIALIZABLE isolation level would allow both inserts. > READ COMMITED isolation level wouldn't allow A's inserts. > > As I mentioned in another posting,PostgreSQL's SERIALIZABLE > isolation level isn't completely serializable and it's same as Oracle. > Probably Vadim refers to this incomplete serializability somewhere > in documentation. > It seems almost impossible to implement complete serializability > under MVCC without using table level locking. I love MVCC much > more than theoretically beautiful complete serializability. Given that Postgres correctly recognizes concurrent updates and aborts one of the transactions, how difficult would it be to do the same for inserts? -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[GENERAL] Re: [HACKERS] pgsql/php3/apache authentication
On Wed, 26 Apr 2000, Jim Mercer wrote: > - queries via localhost (unix domain sockets) should assume that the pg_user > is the same as the unix user running the process. There's no way for the server to determine the system user name of the other end of a domain socket; at least no one has implemented one yet. So essentially this isn't going to work. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] I'm just doin' the 7.0 RC1 install and have someinput on the documentation.
Patches to [EMAIL PROTECTED], discussion at [EMAIL PROTECTED] Thanks. Of course the documentation is more or less frozen but feel free to contribute anyway for the next release. On Wed, 26 Apr 2000, Michael S. Kelly wrote: > Hey folks, > > Been gettin' situated with 7.0 RC1 and I've come across a couple of places > where small changes to the documentation would have helped. I'd like to > feed this stuff back to the folks doin' the docs as part of my contribution > to the cause, but I don't know who to give it to. Any leads? > > -=michael=- > > -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] Revisited: Transactions, insert unique.
In a message to and Hiroshi Inoue pgsql-general, Ed Loehr wrote: > > -- Within transaction A -- > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > > -- Within transaction B -- > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > INSERT INTO foo (id, msg) > SELECT 1, 'From B' > WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1); > COMMIT; > > -- Within transaction A -- > SELECT * FROM foo; > ... In this case, it is actually OK for A to see the committed results of B because the overall outcome is then equivalent to B occuring entirely before A. In general, it is important to remember what SERIALIZABLE means: A particular concurrent execution of several transactions must have an observable outcome that is equivalent to running the same transactions one after the other (serialized). It is NOT required that the outcome be equivalent to the result that would be observed by running the transactions in a particular order, such as in the order they were actually started. The outcome is only required to be equivalent to some (arbitrary) order. A concurrancy mechanism supports the SERIALIZABLE isolation level if it guarantees that every concurrent execution of transactions is serializable. Joachim -- private: [EMAIL PROTECTED](http://www.kraut.bc.ca) work: [EMAIL PROTECTED] (http://www.mercury.bc.ca)
[GENERAL] sequences and Transactions
Hi! how can I setup sequences to have the current-value reset in case of an Transaction rollback. My intension is to get an contignous numbering of the rows. Currently in case of an Rollback one number is skipped since the record itself is not inserted but the counter is not reset. Elmar
