Re: [GENERAL] Transaction safety and large objects. Possible?
Eric Davies <[EMAIL PROTECTED]> writes: > Now I'm trying to make the functions transaction safe. In theory, all > I need to do is convert my libpq calls to equivalent SPI calls. > However, there don't appear to be any SPI equivalents for the large > object functions like lo_write and lo_read. What do you need SPI for? Call the functions directly. See src/include/libpq/be-fsstubs.h src/backend/libpq/be-fsstubs.c regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] transaction safety
DaVinci wrote: > On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote: > > The problem is, that a referential integrity constraint needs > > to ensure that from the moment the constraint got checked > > until your INSERT got made persistent (COMMIT), nobody else > > has a chance to kick out the referenced key. > > > > Due to the lack of the ability to put a shared row lock with > > a SELECT, we currently use FOR UPDATE, placing an exclusive > > lock onto the referenced key. > > I understand this but, why then other changes on "aviso" get locked?. My > first impression is that only referenced keys should be, but not a table > that references them. You're right, at the time two FK rows referencing the same PK get inserted, there is no need to block one of them. Thus, PostgreSQL *shouldn't* block. But at the time beeing, the foreign key triggers issue a SELECT oid FROM WHERE = FOR UPDATE; Since both INSERT operations on the FK table do it, the second one is blocked until COMMIT of the first, releasing the lock. What we need is something like SELECT ... WITH LOCK; applying a shared (read) lock of the PK row. In this state, UPDATE/DELETE to the PK row in question or SELECT ... FOR UPDATE of that will block, but more SELECT ... WITH LOCK would be allowed and pass through without blocking. I don't really want that language construct, since it's not SQL standard. Maybe it's possible to achieve the same behaviour with dirty reads or the like. However it's done finally, it should behave like the above. > > If this works so for now, is any plan to change in future?. > > > In your case it might help to make the constraints INITIALLY > > DEFERRED. That causes that the checks are delayed until > > COMMIT, so it shortens the time the lock is present. > > Thanks!. That works fine. You're welcome. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [GENERAL] transaction safety
On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote: > DaVinci wrote: > > Problem is with referential integrity like you say. But I don't understand > > well reason. Interlocks should appear only when values of referenced tables > > are changed, added or removed, shouldn't they?. But that is not the case. I > > only insert in table "aviso". > > > > Is this normal? > > Maybe. > > The problem is, that a referential integrity constraint needs > to ensure that from the moment the constraint got checked > until your INSERT got made persistent (COMMIT), nobody else > has a chance to kick out the referenced key. > > Due to the lack of the ability to put a shared row lock with > a SELECT, we currently use FOR UPDATE, placing an exclusive > lock onto the referenced key. I understand this but, why then other changes on "aviso" get locked?. My first impression is that only referenced keys should be, but not a table that references them. If this works so for now, is any plan to change in future?. > In your case it might help to make the constraints INITIALLY > DEFERRED. That causes that the checks are delayed until > COMMIT, so it shortens the time the lock is present. Thanks!. That works fine. David
Re: [GENERAL] transaction safety
DaVinci wrote: > On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote: > > DaVinci <[EMAIL PROTECTED]> writes: > > > procedenciaint2 references procedencia, > > > línea int2 references línea, > > > empresaint2 references empresa, > > > > Depending on the data values you are working with, it could be that the > > foreign key references cause interlocks --- ie, one transaction has to > > wait to see if the other commits to know if it has a valid foreign key. > > Problem is with referential integrity like you say. But I don't understand > well reason. Interlocks should appear only when values of referenced tables > are changed, added or removed, shouldn't they?. But that is not the case. I > only insert in table "aviso". > > Is this normal? Maybe. The problem is, that a referential integrity constraint needs to ensure that from the moment the constraint got checked until your INSERT got made persistent (COMMIT), nobody else has a chance to kick out the referenced key. Due to the lack of the ability to put a shared row lock with a SELECT, we currently use FOR UPDATE, placing an exclusive lock onto the referenced key. In your case it might help to make the constraints INITIALLY DEFERRED. That causes that the checks are delayed until COMMIT, so it shortens the time the lock is present. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [GENERAL] transaction safety
On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote: > DaVinci <[EMAIL PROTECTED]> writes: > > procedencia int2 references procedencia, > > línea int2 references línea, > > empresa int2 references empresa, > > Depending on the data values you are working with, it could be that the > foreign key references cause interlocks --- ie, one transaction has to > wait to see if the other commits to know if it has a valid foreign key. Problem is with referential integrity like you say. But I don't understand well reason. Interlocks should appear only when values of referenced tables are changed, added or removed, shouldn't they?. But that is not the case. I only insert in table "aviso". Is this normal? Greets. David
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety OK, someone want to answer this? I have always been under the impression that Postgres would not block under these circumstances, however, this is clearly blocking, for no apparently good reason. I have just run a test on my own server, and this blocking does not happen. Both sessions run independently until each has committed, then displaying information from the other insert, but definitely not blocking. It works exactly as I would have expected. Anybody??? MikeA -Original Message- From: DaVinci [mailto:[EMAIL PROTECTED]] Sent: 13 February 2001 10:42 To: Lista PostgreSql Subject: Re: [GENERAL] transaction safety On Tue, Feb 13, 2001 at 09:56:18AM -, Michael Ansley wrote: > Hi, > > Well, the number is 'locked', because once it's given to you, that's it, > it's gone from the 'list of available numbers' (i.e.: the sequence). > However, between the insert, and the read of the ID, if another transaction > performs an insert, it does NOT affect the ID that the first transaction > reads (i.e.: your ID read in the first transaction IS definitely still safe, > it will still read the correct one). I understand this. > AND, the first insert does NOT block > the second insert. The second insert could complete and commit before the > first one. But I don't know how to reproduce this part. If I have two different sessions of psql connected to same database: psql-1# begin; psql-2# begin; psql-1# insert into foo ...; psql-2# insert into foo ...; <- ¡¡¡Frozen!!! psql-1# commit; <- psql-2 unfrozen Why I get this?. > Does this explain better? Very well, thanks ;) David ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: [GENERAL] transaction safety
On Tue, Feb 13, 2001 at 09:56:18AM -, Michael Ansley wrote: > Hi, > > Well, the number is 'locked', because once it's given to you, that's it, > it's gone from the 'list of available numbers' (i.e.: the sequence). > However, between the insert, and the read of the ID, if another transaction > performs an insert, it does NOT affect the ID that the first transaction > reads (i.e.: your ID read in the first transaction IS definitely still safe, > it will still read the correct one). I understand this. > AND, the first insert does NOT block > the second insert. The second insert could complete and commit before the > first one. But I don't know how to reproduce this part. If I have two different sessions of psql connected to same database: psql-1# begin; psql-2# begin; psql-1# insert into foo ...; psql-2# insert into foo ...;<- ¡¡¡Frozen!!! psql-1# commit; <- psql-2 unfrozen Why I get this?. > Does this explain better? Very well, thanks ;) David
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety Hi, Well, the number is 'locked', because once it's given to you, that's it, it's gone from the 'list of available numbers' (i.e.: the sequence). However, between the insert, and the read of the ID, if another transaction performs an insert, it does NOT affect the ID that the first transaction reads (i.e.: your ID read in the first transaction IS definitely still safe, it will still read the correct one). AND, the first insert does NOT block the second insert. The second insert could complete and commit before the first one. Does this explain better? MikeA -Original Message- From: DaVinci [mailto:[EMAIL PROTECTED]] Sent: 13 February 2001 08:08 To: Lista PostgreSql Subject: Re: [GENERAL] transaction safety On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote: > DaVinci <[EMAIL PROTECTED]> writes: > > On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: > >> Typically, the insert for a person, and for all the associated addresses > >> would be done in the same transaction so that if the insert for one of the > >> addresses failed, then the whole lot would role back (perhaps a bit extreme, > >> but I think that's what you asked for ;-) > > > I thought it is possible to have different transactions opened and insert > > data in same table from them. It seems my idea was fault, doesn't it?. > > In sumary: locks with inserts are for table and not for tuple. If this is > > not true, tell me details, please :) > > It's not true. How did you arrive at that conclusion from what Mike > said? I'll try to explain. Mike said: "in a transaction make an insert and then a read in serial current value". If in gap between those operations occurs another insert from different transaction, then reading serial is not safe. In order to understand this well I have made some basic experiments, freezing a transaction with an insert and making other transaction with an insert to the same table. Second gets frozen until first commit or cancel. That is reason of my last message: "locks with inserts are for table and not for tuple". Perhaps I didn't explain myself very well or there is some detail about locks that I don't understand at all. Thanks all for your time. David ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote: > DaVinci <[EMAIL PROTECTED]> writes: > > On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: > >> Typically, the insert for a person, and for all the associated addresses > >> would be done in the same transaction so that if the insert for one of the > >> addresses failed, then the whole lot would role back (perhaps a bit extreme, > >> but I think that's what you asked for ;-) > > > I thought it is possible to have different transactions opened and insert > > data in same table from them. It seems my idea was fault, doesn't it?. > > In sumary: locks with inserts are for table and not for tuple. If this is > > not true, tell me details, please :) > > It's not true. How did you arrive at that conclusion from what Mike > said? I'll try to explain. Mike said: "in a transaction make an insert and then a read in serial current value". If in gap between those operations occurs another insert from different transaction, then reading serial is not safe. In order to understand this well I have made some basic experiments, freezing a transaction with an insert and making other transaction with an insert to the same table. Second gets frozen until first commit or cancel. That is reason of my last message: "locks with inserts are for table and not for tuple". Perhaps I didn't explain myself very well or there is some detail about locks that I don't understand at all. Thanks all for your time. David
Re: [GENERAL] transaction safety
DaVinci <[EMAIL PROTECTED]> writes: > On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: >> Typically, the insert for a person, and for all the associated addresses >> would be done in the same transaction so that if the insert for one of the >> addresses failed, then the whole lot would role back (perhaps a bit extreme, >> but I think that's what you asked for ;-) > I thought it is possible to have different transactions opened and insert > data in same table from them. It seems my idea was fault, doesn't it?. > In sumary: locks with inserts are for table and not for tuple. If this is > not true, tell me details, please :) It's not true. How did you arrive at that conclusion from what Mike said? regards, tom lane
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety
There should be no locks on the table for insert. The sequence number is retained by the session, not locked on the database. What this means is that somebody else may start their insert after you, and then you roll back, leaving a gap in the sequences. However, gaps in the sequences are not (or should not be) important. You can test this out using a few sessions of psql.
You need to view this with about 110 chars across, in fixed-width font (tab-width of 4 chars):
a$ psql test | b$ psql test
a=> begin; b=> begin; |
a=> insert into person (name, dob) |
a-> values ('Peter', '03/09/1945'); |
INSERT |
a=> select currval('person_id_seq'); |
1 |
| b=> insert into person (name, dob)
| b-> values ('Marge', '05/03/1967');
| INSERT
| b=> select currval('person_id_seq');
| 2
a=> select currval('person_id_seq'); |
1 |
a=> insert into address (id_person, address) |
a-> values (currval('person_id_seq'), ''); |
INSERT XXXY |
| b=> insert into address (id_person, address)
| b-> values (currval('person_id_seq'), '');
| INSERT XXXY
| b=> select currval('person_id_seq');
| 2
a=> rollback; |
ROLLBACK |
| b=> select currval('person_id_seq');
| 2
| b=> end;
| COMMIT
v
time
Now, although the left side has rolled back, the sequence number 1 is lost. However, this is not a problem, as the primary key requires uniqueness, but not continuity, and it means that your inserts do not block the table. Inserts can happen as fast as you can push data into the table.
Read up on sequences, because serial fields are based on sequences, thus avoiding the locking issue.
Hope this helps...
MikeA
-Original Message-
From: DaVinci [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2001 13:43
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote:
> The number returned by the sequence for the serial ID is retained within the
> session, and so it can be returned by calling currval, e.g.:
...[a detailed example]...
> Typically, the insert for a person, and for all the associated addresses
> would be done in the same transaction so that if the insert for one of the
> addresses failed, then the whole lot would role back (perhaps a bit extreme,
> but I think that's what you asked for ;-)
That sounds good ;)
I thought it is possible to have different transactions opened and insert
data in same table from them. It seems my idea was fault, doesn't it?.
In sumary: locks with inserts are for table and not for tuple. If this is
not true, tell me details, please :)
Thanks for all.
David
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: > The number returned by the sequence for the serial ID is retained within the > session, and so it can be returned by calling currval, e.g.: ...[a detailed example]... > Typically, the insert for a person, and for all the associated addresses > would be done in the same transaction so that if the insert for one of the > addresses failed, then the whole lot would role back (perhaps a bit extreme, > but I think that's what you asked for ;-) That sounds good ;) I thought it is possible to have different transactions opened and insert data in same table from them. It seems my idea was fault, doesn't it?. In sumary: locks with inserts are for table and not for tuple. If this is not true, tell me details, please :) Thanks for all. David
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety
Hi,
The number returned by the sequence for the serial ID is retained within the session, and so it can be returned by calling currval, e.g.:
CREATE TABLE person (
id serial primary key,
name varchar not null,
dob timestamp not null
);
CREATE TABLE address (
id serial primary key,
id_person int not null references person(id),
address varchar not null
);
INSERT INTO person (name, dob) VALUES ('Peter', '03/09/1945');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '44 Willowdown Road');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '23 Second Ave. (Second Home)');
INSERT INTO person (name, dob) VALUES ('Jane', '06/12/1958');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '16 Parsons Crescent');
Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-)
Cheers...
MikeA
-Original Message-----
From: DaVinci [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2001 11:34
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 11:08:55AM +, Oliver Elphick wrote:
> DaVinci wrote:
> > Hi all.
> >
> > I want to create a new tuple of main info and detail tuples (in
> > different tables) that are joined by a key field. Key field is created by
> > generator automatically and I need that number in order to assign to detail
> > tuples. How can I to get that number in a safe way?.
>
> A successful INSERT returns the oid of the row just created; so get the
> new value with a query like this:
>
> SELECT key_field FROM table WHERE oid = new_oid_value;
I have a new question for this idea (thanks). When Database is big, do i
need index for oid field to speed select?.
Greets.
David
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 11:08:55AM +, Oliver Elphick wrote: > DaVinci wrote: > > Hi all. > > > > I want to create a new tuple of main info and detail tuples (in > > different tables) that are joined by a key field. Key field is created by > > generator automatically and I need that number in order to assign to detail > > tuples. How can I to get that number in a safe way?. > > A successful INSERT returns the oid of the row just created; so get the > new value with a query like this: > > SELECT key_field FROM table WHERE oid = new_oid_value; I have a new question for this idea (thanks). When Database is big, do i need index for oid field to speed select?. Greets. David
Re: [GENERAL] transaction safety
DaVinci wrote: > Hi all. > > I want to create a new tuple of main info and detail tuples (in > different tables) that are joined by a key field. Key field is created by > generator automatically and I need that number in order to assign to detail > tuples. How can I to get that number in a safe way?. A successful INSERT returns the oid of the row just created; so get the new value with a query like this: SELECT key_field FROM table WHERE oid = new_oid_value; In libpq programming, use PQoidValue() to get the oid. Other interfcaes should have equivalent methods. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "When I consider thy heavens, the work of thy fingers, the moon and the stars, which thou hast ordained; What is man, that thou art mindful of him? and the son of man, that thou visitest him? For thou hast made him a little lower than the angels, and hast crowned him with glory and honour."Psalms 8:3-5
