Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Tom Lane wrote:
each candidate. func_select_candidate depends on having some notion of
the same argument position, but what does that mean in such a case?
While it is true that I don't know everything about the current code I
still claim that it can not be such a big problem as you try to paint
a picture of here. And even more importantly, even if it is it should not
affect the current behaviour, more about that later.
If you have function:
f(x int, y text)
f(y float, x text);
and you make a call
f(2,'foo');
then for each candidate above there is some matching going on based on the
order of the arguments. I just assume that this works as it should today,
even if I don't know the details. Not everyone have worked with PG before
and knows everything directly.
Now, lets assume there is a call
f (y = 2, x = 'foo')
then for each candidate the order is fixed again. I hoped to treat it
differently for each candidate, so the possible calls are
f('foo',2)
f(2, 'foo')
and these orders are the same every time we look at a candidate.
Now, the above is just my plan before coding and before understanding
everything. It might work and it might not. So far I've got no reason to
thing that it wont work,
Let's assume that I can't make something like the above to work as fast
as today. For functions calls without named arguments then the current
fast function can still be used. Only for the new kind of calls do you
need to do something more fancy. That would make named calls be a lot
slower (relatively speaking) then calls without named and a fixed order of
the arguments. The problem here is not speed but code duplication.
There are also some difficult questions raised by schemas and search
paths. s1.f1(text, text) masks s2.f1(text, text) if s1 appears before
s2 in your search path.
But does s1.f1(foo text, bar text) mask s2.f1(baz text, xyzzy text)?
Does your answer change depending on whether the actual call has
parameter names or not?
That is an open question, one can go either way. I think both will work
and both will be understandable/predictable from the programmers point of
view.
For that matter, should f1(foo text, bar text) and f1(baz text, xyzzy
text) be considered to be different function signatures that ought to be
permitted to coexist in a single schema? If actual parameter names are
going to affect resolution of search-path ambiguity, it's hard to argue
that the parameter names aren't part of the signature.
At first I plan to not have the argument names as part of the signature.
Mainly because if one start without one can add it later if needed. To
have it part of the signature only lets you define more functions then
today. The other database that implements this does have the argument
names as part of the signature.
I think that the value of having it is no that big. Just don't name your
functions and arguments like that. Rejecting cases like that above will
not make life harder for the programmer. It would rather help him/her
designing better functions. If it hurts, don't do it.
What might be the best compromise is to treat parameter names as
documentation *only*, that is, we insist that the parameters have to
appear in the declared order in any case.
That would suck big time.
About the speed, how many functions do you have with the same name.
Try select proname, count(*) from pg_proc group by 1 order by 2 desc;
Note that the ones at the top are pretty popular in usage, not only in
having lots of variants. I don't think it's acceptable to take major
speed hits in parsing them
There will be no hit at all since the functions calls for these don't use
named arguments, the exact same method of function resolution as today
should work fine. You just need to detect at the start if this function
call is with or without named arguments. I have never had any plans of
slowing down the current method, including the fast case where all
arguments have the correct types. I'm sure I will run into problems, like
the above and/or others. I'll have to deal with it when I run into it.
There is of course another way all this can turn out also, that I don't
manage to make it work in a good way. In that case there will be no named
parameter function calls (unless somebody else makes them).
In any case, let me try to make it work before we throw it away. I work on
pg on some free hours here and there. It might take some time until I have
something working, but when I do I would love for you to review the patch
pointing out all errors!
The worst that can happen is that it doesn't work. So what, I can live
with that :-)
--
/Dennis Björklund
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] 7.5 change documentation
On Wednesday 28 January 2004 00:38, Simon Riggs wrote: POSTGRESQL: Summary of Changes since last release (7.4.1) All corrections and changes welcome...if this is well received, then I will monitor pgsql-commiters to keep track of things. Speaking as JustAUser (TM) I find this very useful. I always have trouble keeping track on what may/probably/will appear in upcoming versions when people ask on the lists. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Call a function when a User (dis)connects from the Server/Database
Hello, I search a way to realize following: When a user connects to a databse, a function of my should be executed. The best would be a system table where the active Databaser Users are in, and creating a Trigger on it. But there is not such a table. Sometold me to modifying the source, but i feel a bit lost in such a big source. Please help me :) The best would be a modification which stores active Users in table. I think this would be a nice feature for following PostgreSQL Versions, or? I dropped this question in the general list, but i thought its a better question for this list. Sorry, for my bad english :/ -- Johannes 'robotron' Barop ---(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: [HACKERS] Question about indexes
Tom Lane [EMAIL PROTECTED] writes: In any case, this discussion is predicated on the assumption that the operations involving the bitmap are a significant fraction of the total time, which I think is quite uncertain. Until we build it and profile it, we won't know that. The other thought I had was that it would be difficult to tell when to follow this path. Since the main case where it wins is when the individual indexes aren't very selective but the combination is very selective, and we don't have inter-column correlation statistics ... -- greg ---(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: [HACKERS] Function call
Dennis Bjorklund [EMAIL PROTECTED] writes: Now, the above is just my plan before coding and before understanding everything. It might work and it might not. So far I've got no reason to thing that it wont work, Before you start writing anything, I suggest you read http://www.postgresql.org/docs/7.4/static/typeconv-func.html I can see at least three assumptions in there that will be broken by allowing different candidate functions to have arguments matched in different orders. That's not even counting the questions about whether we should allow the names of parameters to affect which functions are considered to be potential candidates. What might be the best compromise is to treat parameter names as documentation *only*, that is, we insist that the parameters have to appear in the declared order in any case. That would suck big time. I don't think you should reject it out of hand. It's simple and understandable, and it is guaranteed not to break any existing code when the programmer simply adds names to the parameter declarations of a function without changing any call sites. If the presence of parameter names changes the ambiguity resolution rules at all, I'm doubtful that we could guarantee not to break things. regards, tom lane ---(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: [HACKERS] Function call
On Wed, 28 Jan 2004, Tom Lane wrote: when the programmer simply adds names to the parameter declarations of a function without changing any call sites. If the presence of parameter names changes the ambiguity resolution rules at all, I'm doubtful that we could guarantee not to break things. Agreed. Calls without argument names shall work exactly as today. If you are saying that a call like foo(x = 14, y = 'text') shall always call the same function even if you add parameter names to a function who did not have it before. Then that is wrong. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Write cache
Hi Simon, Sorry I couldn't answer sooner. Hope your daughter is OK by now. On Wed, 28 Jan 2004, Simon Riggs wrote: Date: Wed, 28 Jan 2004 14:56:40 - From: Simon Riggs [EMAIL PROTECTED] To: [EMAIL PROTECTED], 'pgsql-hackers list' [EMAIL PROTECTED] Subject: RE: [HACKERS] Write cache Olivier PRENANT writes... Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... The bottom line here is that Olivier has lost some data and I'm sure we all want to know if there is a bug in PostgreSQL, or he has a hardware problem. However, PostgreSQL is partially implicated only because it discovered the error, but hasn't in any other way been associated yet with the fatal crash itself. I agree I MAY have an hardware problem. What happens is more a system freeze than a system crash (there's no panic, no nothing, just freezes, no disk activity, not network) What bothers me is that the fs itself was badly hurt, although fsck did repair errors, postgresql complained that it could'nt read a file (relation) that obviously had a wrong block number somewhere. Now, what puzzle me is that my fs are all vxfs, with an intent log. Fairly like postgres. In that case, how can I loose data with it? Also I have mysql on the same filesystem (although VERY quiet) and it did'nt suffer. Postgresql is doing a LOT of job here, and since I host this very busy database I experience data loose in case of crash. This is NOT intended to start a war, I love postgres and I'm very confident in it, but I may have a configuration where ch.. happens. (like the 32 WAL buffers I have) Likewise, I'd like to understand that statistic buffer full condition My intuition tells me that this is hardware related. We've discussed some probable causes, but nobody has come up with a diagnostic test to evaluate the disks accuracy. This might be because this forum isn't the most appropriate place to discuss disk storage or linux device drivers? Olivier: if your disks are supported or under warranty, then my advice would be to contact these people and ask for details of a suitable diagnostic test, or go via their support forums to research this. Expensive disks are usually fairly well supported, especially if they smell an upgrade. :) According to my vendor, there is NO write cache, and the system freeze is the heart of the problem My experience with other RDBMS vendor's support teams is that they give out this advice regularly when faced with RDBMS-reported data corruption errors: check your disks are working; I think it is reasonable to do the same here. Data corruption by the dbms does occur, but my experience is that this is frequent than hardware-related causes. In the past, I have used the dd command to squirt data at the disk, then read it back again - but there may be reasons I don't know why a success on that test might not be conclusive, so I personally would be happy to defer to someone that does. I've seen errors like this come from soon-to-fail disks, poor device drivers, failing non-volatile RAM, cabinet backplane noise, poorly wired cabling and intermittently used shared SCSI... The problem is that while the system is up and running, I have no log of any error, it goes very fast does it's job correctly. Best of luck, Simon Riggs Many thanks to all for your help Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] lock related issues...
Hi All, I'm looking for some details on how the locking system works in relation to transactions dealing with INSERTs and UPDATEs. The version of PostgreSQL is 7.3.2 and the connections to the database are going through a JDBC driver. The details of what we are running into are as follows: A plpgsql function call is being made through the JDBC driver, auto-commits are off, and any commits or rollbacks are dependent on the results of the function. When more then one client evokes the function (again, through the JDBC driver), the first caller is able to gain a lock with out issue, via a SELECT ... FOR UPDATE.. clause. Any connections that are made during the select are obviously set in a wait queue. Once the first transaction has completed, then the next call in the wait queue is process, and so on. The issue that we are seeing is that if there is a update that takes place on a record, the results are available on any transactions that follow the initial update, regardless of whether they have been in a wait queue or not. However, if there are inserts that are mode during a transcation, those inserts are not becomming available if a transaction is already in motion (unlike the updates, which do show up). If the transaction is closed and a new one is reopened, after all of the inserts have been completed, then we can see them. Is this the standard behaviour associate to transactions? -- Chris Bowlby [EMAIL PROTECTED] PostgreSQL Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Question about indexes
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: I would see that as the next step, But it seems to me it would be only a small set of queries where it would really help enough to outweigh the extra work of the sort. What sort? To build the in-memory bitmap you effectively have to do a sort. If the tuples come out of the index in heap order then you can combine them without having to go through that step. I'm a little dubious that true bitmap indexes would be worth building for Postgres. Seems like partial indexes cover the same sorts of applications and are more flexible. I'm clear on the distinction. I think bitmap indexes still have a place, but if regular btree indexes could be combined efficiently then that would be an even narrower niche. Partial indexes are very handy, and they're useful in corner cases where bitmap indexes are useful, such as flags for special types of records. But I think bitmap indexes are specifically wanted by certain types of data warehousing applications where you have an index on virtually every column and then want to do arbitrary boolean combinations of all of them. btree indexes would generate more i/o scanning all the indexes than just doing a sequential scan would. Whereas bitmap indexes are much denser on disk. However my experience leans more towards the OLTP side and I very rarely saw applications like this. -- greg ---(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: [HACKERS] Write cache
On Wed, 28 Jan 2004 [EMAIL PROTECTED] wrote: I agree I MAY have an hardware problem. What happens is more a system freeze than a system crash (there's no panic, no nothing, just freezes, no disk activity, not network) I would suspect either bad hardware,a flakey SCSI driver, or a possible kernel bug. If your system is freezing hard, it is NOT postgresql's fault. It simply doesn't have the access to the kind of system resources needed to freeze a machine. Is there a different SCSI driver / card you can try in there? We've (and many others have too) had good luck with the LSI/MegaRAID cards and both the older 1.18 seris and new 2.x series drivers. No freezes, no crashes, no hangs on the boxes with those in them. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Write cache
Olivier PRENANT writes... Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... The bottom line here is that Olivier has lost some data and I'm sure we all want to know if there is a bug in PostgreSQL, or he has a hardware problem. However, PostgreSQL is partially implicated only because it discovered the error, but hasn't in any other way been associated yet with the fatal crash itself. My intuition tells me that this is hardware related. We've discussed some probable causes, but nobody has come up with a diagnostic test to evaluate the disks accuracy. This might be because this forum isn't the most appropriate place to discuss disk storage or linux device drivers? Olivier: if your disks are supported or under warranty, then my advice would be to contact these people and ask for details of a suitable diagnostic test, or go via their support forums to research this. Expensive disks are usually fairly well supported, especially if they smell an upgrade. :) My experience with other RDBMS vendor's support teams is that they give out this advice regularly when faced with RDBMS-reported data corruption errors: check your disks are working; I think it is reasonable to do the same here. Data corruption by the dbms does occur, but my experience is that this is frequent than hardware-related causes. In the past, I have used the dd command to squirt data at the disk, then read it back again - but there may be reasons I don't know why a success on that test might not be conclusive, so I personally would be happy to defer to someone that does. I've seen errors like this come from soon-to-fail disks, poor device drivers, failing non-volatile RAM, cabinet backplane noise, poorly wired cabling and intermittently used shared SCSI... Best of luck, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Question about indexes
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: What sort? To build the in-memory bitmap you effectively have to do a sort. Hm, you're thinking that the operation of inserting a bit into a bitmap has to be at least O(log N). Seems to me that that depends on the data structure you use. In principle it could be O(1), if you use a true bitmap (linear array) -- just index and set the bit. You might be right that practical data structures would be O(log N), but I'm not totally convinced. If the tuples come out of the index in heap order then you can combine them without having to go through that step. But considering the restrictions implied by that assumption --- no range scans, no non-btree indexes --- I doubt we will take the trouble to implement that variant. We'll want to do the generalized bitmap code anyway. In any case, this discussion is predicated on the assumption that the operations involving the bitmap are a significant fraction of the total time, which I think is quite uncertain. Until we build it and profile it, we won't know that. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Write cache
Simon Riggs [EMAIL PROTECTED] writes: In the past, I have used the dd command to squirt data at the disk, then read it back again - but there may be reasons I don't know why a success on that test might not be conclusive, so I personally would be happy to defer to someone that does. Well that's an interesting tangent. 1) I've seen bad memory on a scsi controller once. Caused one-bit errors in data read back after being written. a dd might or might not find that depending on the buffer usage pattern, and depending on the pattern being written and read. Memory errors are notoriously fickle and can sometimes be triggered only by particular bit patterns in adjacent memory addresses in rapid succession. badblocks does try to address this by writing four different complementary patterns. but I'm not convinced it's really conclusive either. It's certainly not as sophisticated as memtest86 and can't really since it can't directly control the placement of data in the disk's buffers. 2) The disk could be finding lots of bad blocks during the dd run and remapping them. It gives no information to the OS through the regular interfaces. A low level diagnostic program can inquire about how many blocks have been remapped and how many spare blocks are available. I know Maxtor is hot to have you run their PowerMax(tm) program whenever you call tech support. I think it just runs something similar to badblocks and asks the disk firmware if it's detected any low level problems. In theory it can check things like the drive having trouble syncing to tracks due to environmental factors like noise, vibrations, and heat. I don't know if it does or not though. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Question about indexes
Some potentially helpful background comments on the discussion so far... Tom Lane writes Greg Stark writes Note that the space saving of bitmap indexes is still a substantial factor. I think you are still confusing what I'm talking about with a bitmap index, ie, a persistent structure on-disk. It's not that at all, but a transient structure built in-memory during an index scan. Oracle allows the creation of bitmap indices as persistent data structures. The space saving of bitmap indices is only a saving when compared with btree indices. If you don't have them at all because they are built dynamically when required, as Tom is suggesting, then you save even more space. Maintaining the bitmap index is a costly operation. You tend to want to build them on characteristic columns, of which there tends to be more of in a database than partial/full identity columns on which you build btrees (forgive the vagueness of that comment), so you end up with loads of the damn things, so the space soon adds up. It can be hard to judge which ones are the important ones, especially when each is used by a different user/group. Building them dynamically is a good way of solving the question which ones are needed?. Ever seen 58 indices on a table? Don't go there. My vote would be implement the dynamic building capability, then return to implement a persisted structure later if that seems like it would be a further improvement. [The option would be nice] If we do it dynamically, as Tom suggests, then we don't have to code the index maintenance logic at all and the functionality will be with us all the sooner. Go Tom! Tom Lane writes In any case, this discussion is predicated on the assumption that the operations involving the bitmap are a significant fraction of the total time, which I think is quite uncertain. Until we build it and profile it, we won't know that. Dynamically building the bitmaps has been the strategy in use by Teradata for nearly a decade on many large datawarehouses. I can personally vouch for the effectiveness of this approach - I was surprised when Oracle went for the persistent option. Certainly in that case building the bitmaps adds much less time than is saved overall by the better total query strategy. Greg Stark writes To build the in-memory bitmap you effectively have to do a sort. Not sure on this latter point: I think I agree with Greg on that point, but want to believe Tom because requiring a sort will definitely add time. To shed some light in this area, some other major implementations are: In Teradata, tables are stored based upon a primary index, which is effectively an index-organised table. The index pointers are stored in sorted order lock step with the blocks of the associated table - No sort required. (The ordering is based upon a hashed index, but that doesn't change the technique). Oracle's tables/indexes use heaps/btrees also, though they do provide an index-organised table feature similar to Teradata. Maybe the lack of heap/btree consistent ordering in Oracle and their subsequent design choice of persistent bitmap indices is an indication for PostgreSQL too? In Oracle, bitmap indices are an important precursor to the star join technique. AFAICS it is still possible to have a star join plan without having persistent bitmap indices. IMHO, the longer term goal of a good star join plan is an important one - that may influence the design selection for this discussion. Hope some of that helps, Best regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] lock related issues...
Chris Bowlby writes I'm looking for some details on how the locking system works in relation to transactions dealing with INSERTs and UPDATEs. The version of PostgreSQL is 7.3.2 p.152 of the 7.3.2 Users Guide, section 9.2.1 Read Committed Isolation Level applies to your situation as described A plpgsql function call is being made through the JDBC driver, auto-commits are off, and any commits or rollbacks are dependent on the results of the function. When more then one client evokes the function (again, through the JDBC driver), the first caller is able to gain a lock with out issue, via a SELECT ... FOR UPDATE.. clause. Any connections that are made during the select are obviously set in a wait queue. Once the first transaction has completed, then the next call in the wait queue is process, and so on. The issue that we are seeing is that if there is a update that takes place on a record, the results are available on any transactions that follow the initial update, regardless of whether they have been in a wait queue or not. However, if there are inserts that are mode during a transcation, those inserts are not becomming available if a transaction is already in motion (unlike the updates, which do show up). If the transaction is closed and a new one is reopened, after all of the inserts have been completed, then we can see them. Is this the standard behaviour associate to transactions? Does what it says on the tin. The manual doesn't explicitly draw attention to the situation you have recognized, but the described behaviour fits exactly what it says in the manual. The SELECT .. FOR UPDATE sees rows that were there when the transaction started, not when it eventually gets to read them, some time later. The lock prevents them from accessing those rows for some time, during which time other inserts are applied, which they cannot see. When they get the lock, they are able to access the rows they wanted to access, but because of this particular lock mode (read committed isolation level), you see the updated version of those rows (if they still match the WHERE clause). You can of course use the serializable isolation level, though this would cause your second and subsequent transactions to abort, allowing a retry. Use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE as the first statement of the transaction, wherever that is. If you don't like these behaviours, you can make other design choices that prevent these situations from occurring. The locking mechanisms are designed to give various options of data protection/concurrency trade-offs. They aren't designed to provide general (or even that efficient) queuing mechanisms - it would be more appropriate to select a different form of queuing mechanism, probably within your Java - or just have a single connection do everybody's work for them. If you really must do this, lock the rows you wish to see earlier in the transaction using a stricter form of locking. An example of this might be to issue an explicit UPDATE using the same WHERE clause as you did for the SELECT..FOR UPDATE, though whether this was possible and desirable would require a wider view of the application before that advice is safe to take as-is. So, doesn't look like a bug to me, nor an awful hidden secret feature either. Best regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] lock related issues...
Hi Simon, Thanks for the confirmation, I just wanted to make sure I was not going ape over it and getting confused. At 08:04 PM 1/28/04, Simon Riggs wrote: Chris Bowlby writes I'm looking for some details on how the locking system works in relation to transactions dealing with INSERTs and UPDATEs. The version of PostgreSQL is 7.3.2 p.152 of the 7.3.2 Users Guide, section 9.2.1 Read Committed Isolation Level applies to your situation as described A plpgsql function call is being made through the JDBC driver, auto-commits are off, and any commits or rollbacks are dependent on the results of the function. When more then one client evokes the function (again, through the JDBC driver), the first caller is able to gain a lock with out issue, via a SELECT ... FOR UPDATE.. clause. Any connections that are made during the select are obviously set in a wait queue. Once the first transaction has completed, then the next call in the wait queue is process, and so on. The issue that we are seeing is that if there is a update that takes place on a record, the results are available on any transactions that follow the initial update, regardless of whether they have been in a wait queue or not. However, if there are inserts that are mode during a transcation, those inserts are not becomming available if a transaction is already in motion (unlike the updates, which do show up). If the transaction is closed and a new one is reopened, after all of the inserts have been completed, then we can see them. Is this the standard behaviour associate to transactions? Does what it says on the tin. The manual doesn't explicitly draw attention to the situation you have recognized, but the described behaviour fits exactly what it says in the manual. The SELECT .. FOR UPDATE sees rows that were there when the transaction started, not when it eventually gets to read them, some time later. The lock prevents them from accessing those rows for some time, during which time other inserts are applied, which they cannot see. When they get the lock, they are able to access the rows they wanted to access, but because of this particular lock mode (read committed isolation level), you see the updated version of those rows (if they still match the WHERE clause). You can of course use the serializable isolation level, though this would cause your second and subsequent transactions to abort, allowing a retry. Use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE as the first statement of the transaction, wherever that is. If you don't like these behaviours, you can make other design choices that prevent these situations from occurring. The locking mechanisms are designed to give various options of data protection/concurrency trade-offs. They aren't designed to provide general (or even that efficient) queuing mechanisms - it would be more appropriate to select a different form of queuing mechanism, probably within your Java - or just have a single connection do everybody's work for them. If you really must do this, lock the rows you wish to see earlier in the transaction using a stricter form of locking. An example of this might be to issue an explicit UPDATE using the same WHERE clause as you did for the SELECT..FOR UPDATE, though whether this was possible and desirable would require a wider view of the application before that advice is safe to take as-is. So, doesn't look like a bug to me, nor an awful hidden secret feature either. Best regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] lock related issues...
Chris Bowlby wrote: Hi Simon, Thanks for the confirmation, I just wanted to make sure I was not going ape over it and getting confused. At 08:04 PM 1/28/04, Simon Riggs wrote: Chris Bowlby writes I'm looking for some details on how the locking system works in relation to transactions dealing with INSERTs and UPDATEs. The version of PostgreSQL is 7.3.2 p.152 of the 7.3.2 Users Guide, section 9.2.1 Read Committed Isolation Level applies to your situation as described A great description of concurrency issues is Tom Lane's O'Reilly presentation. After installing PostgreSQL, a message should be output to read it: http://conferences.oreillynet.com/presentations/os2002/lane_tom.tar.gz Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] rule and JDBC
Hi,
It seems JDBC driver does not handle if a INSERT SQL statement
performed by executeUpdate() is actually a SELECT, which is rewritten
by the rule system.
Exception in thread main postgresql.stat.result
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:199)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
at test.main(test.java:10)
I'm not sure whether the JDBC driver or PostgreSQL backend should be
blamed though.
--
Tatsuo Ishii
Here is a test case:
create table daily_log (
log_date timestamp default current_timestamp,
log_memo varchar
);
create table daily_log01 (
log_date timestamp default current_timestamp,
log_memo varchar
);
create table daily_log02 (
log_date timestamp default current_timestamp,
log_memo varchar
);
create function insert_daily_log(timestamp,varchar) returns void as '
declare
in_log_date alias for $1;
in_log_memo alias for $2;
begin
if 1=0 then
insert into daily_log01 (log_date,log_memo)
values (in_log_date,in_log_memo);
elsif 1=1 then
insert into daily_log02 (log_date,log_memo)
values (in_log_date,in_log_memo);
end if;
return;
end;
' language plpgsql;
create rule insert_daily_log_rule as on insert to daily_log
do instead select insert_daily_log(NEW.log_date,NEW.log_memo);
import java.sql.*;
public class test {
public static void main(String[] args) throws Exception {
Class.forName(org.postgresql.Driver);
Connection conn = DriverManager.getConnection(
jdbc:postgresql://localhost/testdb, dummy, );
conn.setAutoCommit(false);
Statement stat = conn.createStatement();
stat.executeUpdate(
insert into daily_log (log_memo) values ('hoge'));
/* this will not raise an error BTW
stat.executeQuery(
insert into daily_log (log_memo) values ('hoge'));
*/
conn.commit();
stat.close();
conn.close();
}
}
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] rule and JDBC
It seems JDBC driver does not handle if a INSERT SQL statement performed by executeUpdate() is actually a SELECT, which is rewritten by the rule system. The JDBC spec says an exception should be thrown if the given SQL statement produces a ResultSet object which it does. As you note using executeQuery works, but won't if there isn't a rule. Perhaps using plain execute() would be the most appropriate thing to do. Kris Jurka Got it. With regard to this PostgreSQL JDBC driver confirms the JDBC spec. -- Tatsuo Ishii ---(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: [HACKERS] rule and JDBC
On Thu, 29 Jan 2004, Tatsuo Ishii wrote: Hi, It seems JDBC driver does not handle if a INSERT SQL statement performed by executeUpdate() is actually a SELECT, which is rewritten by the rule system. The JDBC spec says an exception should be thrown if the given SQL statement produces a ResultSet object which it does. As you note using executeQuery works, but won't if there isn't a rule. Perhaps using plain execute() would be the most appropriate thing to do. Kris Jurka ---(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
[HACKERS] msg translation into sk_SK, Docs: SGML - XML
Dear Hackers, First: I think I have sent out a msg with no body. I'm sorry, I have to communicate thru a weird Win98 machine, it gets things screwed up sometimes. I would like to take up the translation of pgsql msg strings into slovak (sk_SK). It is fairly similar to czech (cz_CZ), so it should go quite fast. Please stand up, if you have any objections. The other thing I wanted to ask: I made the translation of parts of the 7.2 manual in slovak (tutorial, users manual, admin's manual almost ready) that time I thought I would publish it, but Bruce Momjian's book appeared in the bookstores a few weeks ago. I decided I would go on with the translation and use the sgml files (my translation was formatted as a LyX document) and a message translating program. I wanted to use KBabel, but that understands .po(t) files only. To have a .pot file I could use xml2po, but I need xml. I can convert sgml to xml myself, but it would be easier to have it done centrally - perhaps other languages would want their docs in their native language too. Once it is ready, the changes could be easily incorporated before the respective releases. I remember having seen an email by Karel Zak on this topic, but I do not really remember the consequences, so someone could kindly enlighten me. Cheers Zoltan
