Re: [HACKERS] [COMMITTERS] pgsql-server: Rearrange pg_subtrans
On Wed, 25 Aug 2004, Mark Kirkwood wrote: Greg Stark wrote: It's only allowed when the transaction is in READ UNCOMMITTED isolation level. Something Postgres doesn't currently support. In fact I'm not aware of any SQL database that supports it, though I'm sure there's one somewhere. FYI - DB2 supports this isolation level, I don't know of any others (tho Informix is a possibility). I wasn't aware pf DB2 supporting it, but SQL Server and Informix do. Oracle only supports COMMITTED and SERIALIZABLE according to my 10g manual. Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql-server: Rearrange pg_subtrans handling
On 8/25/2004 1:32 AM, Greg Stark wrote: A dirty read is a read that includes data that hasn't been committed yet. Or as the SQL 92 standard puts it: [...] It could also be useful for referential integrity checks since, for example, it would let you see if someone has deleted the referenced record but not committed the delete yet. But that alone wouldn't let you avoid locking the record, TODO items are mostly just pointers to old threads on the mailing lists. They don't contain the complete story. You could maybe find more information searching the pgsql-hackers archive on the web site. Plus ... wouldn't doing the on delete lookup as dirty reads let referencing rows that have been deleted but still could come back through a rollback disappear? What you want to see are new tuples of uncommitted insert/update as well as old tuples of uncommitted delete/update. I don't think there is any term in the standard for that read mode, so we should call it dusty-reads because they see everything vacuum is interested in. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] missing data/global
Tom Lane said: Daniel Kalchev [EMAIL PROTECTED] writes: (found out 7.2.3 does not have pg_database) You think not? Not as a file similar to pg_control. pg_database is indeed table in the system catalog. By the way, I had to copy over the 'new' files from pg_clog and pg_xlog (t his is the second possible error) to get the postmaster running. That was *not* part of the recipe, and is guaranteed *not* to work. I know that, but wondered if it would help in any way.. By the way, what would be the solution to sync WAL with the pg_control contents? It seems likely though that you are wasting your time --- the index failure suggests strongly that you have more corruption than just the loss of the /global subdirectory :-( After spending some time to find possible ways to adjust pointers (could eventually save part of the data), I decided to move to plan B, which is to have few people manually re-enter the data - would have been more effective to waste my time anyway - but not if it will take days and the result be not guaranteed to be consistent. Does such toll exist, that could dump data (records?) from the heap files given the table structure? Regards, Daniel ---(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] missing data/global
On Wed, Aug 25, 2004 at 07:07:23PM +0300, Daniel Kalchev wrote: Does such toll exist, that could dump data (records?) from the heap files given the table structure? You may want to check pg_filedump (from http://sources.redhat.com/rhdb IIRC). (What happened to pg_fsck BTW?) -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] futex
Josh Berkus wrote: Gaetano, I knew there was an evaluation on the futex vs spinlock, and Josh Berkus on IRC told me that there was only a 20% performance increase, is this increase to throw away ? Before we get totally off track here I evaluated futexes strictly as an attempt to solve the context switch storm bug. I did NOT test whether they improved performance overall. What did you test exactly and could you explain a bit about the context switch storm? Did you use the futex interface directly or pthread_rwlock_rdlock? -- Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Typo in a comment in initdb.c
Hello, This comment in initdb.c::main(): char *pgdenv; /* PGDATA value got from sent to * environment */ doesn't look very correct, not at least from the English language perspective. -- Serguei A. Mokhov| /~\The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | XAgainst HTML Montreal, Quebec, Canada | / \ Email! ---(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] futex
Manfred, What did you test exactly and could you explain a bit about the context switch storm? PostgreSQL currently causes a CS storm (context switches 100,000) on multi-processor machines where you have a number of concurrent processes ( than the number of processors) accessing the same small quantity of data repeatedly, particularly if doing seq scans and/or bad queries. This issue causes a significant drop in server performance, as much as 60%. Did you use the futex interface directly or pthread_rwlock_rdlock? I'll have to admit to having only a beginner's grasp of this stuff, but I do believe that that was what the OSDL patch did. Jan also tried a simpler patch, which didn't work either.Both Jan and Tom have expressed the opinion that the only solution is to make the BufrMgrLock more granular (as in, not monolithic) but this is a huge task. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] rpm for RH AS 2.1 ?
Hi all, anyone succesfull create all the rpm for RH AS 2.1 ? I was able to do it using the srpms for RH9 but I did it with --define 'python 0'. Any idea on how to obtain also the postgresql-python.rpm ? Without 'pyhton 0' this is the error: checking for python... /usr/bin/python checking Python installation directories... Traceback (innermost last): File string, line 1, in ? ImportError: No module named distutils Traceback (innermost last): File string, line 1, in ? ImportError: No module named distutils /usr/ checking how to link an embedded Python application... no configure: error: Python Makefile not found error: Bad exit status from /var/tmp/rpm-tmp.56347 (%build) Regards Gaetano Mendola ---(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] rpm for RH AS 2.1 ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 25 Aug 2004, Gaetano Mendola wrote: anyone succesfull create all the rpm for RH AS 2.1 ? I was able to do it using the srpms for RH9 but I did it with --define 'python 0'. Any idea on how to obtain also the postgresql-python.rpm ? Without 'pyhton 0' this is the error: snip AFAIK, it's an error related to Python version since I could build Python rpm for RHEL3. It has python v1.5 or so. I don't have any server running RH 9, so I don't know how to fix it. If someone can assign me a shell account then I could give it a try. Regards, - -- Devrim GUNDUZ devrim~gunduz.org devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBLNH8tl86P3SPfQ4RAg1sAKC4iMwR4c6I+0IAP9qaUVkKK/6I1wCeM791 M/zNWj0MOYk9RSYCybfCn4A= =2ehi -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Typo in a comment in initdb.c
s/from/from and/ cheers andrew Serguei A. Mokhov wrote: Hello, This comment in initdb.c::main(): char *pgdenv; /* PGDATA value got from sent to * environment */ doesn't look very correct, not at least from the English language perspective. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [BUGS] vm/swap used until exhausted
Zane [EMAIL PROTECTED] writes: Different memory usage 7.4.3 vs 8.0.0beta1 client does: begin bulk inserts into single table via PQexecParams (1.2 million records) commit under 7.4.3 memory usage is static under 8.0.0beta1 server used increasing memory untill depletion of vm/swap I've looked into this, and the source of the problem is the new ResourceOwner mechanism we added to manage locks etc. held by subtransactions. Each of the INSERT commands takes out another lock on the target table. In prior releases this had no effect except to increment a lock count in shared memory. In CVS tip, each lock request is also recorded in a ResourceOwner object, and it's the accumulation of those that is responsible for the memory leak. To deal with this, I am thinking about creating a new hash table (local in each backend) that records locks already held, the ResourceOwner(s) they are held on behalf of, and a lock count for each one. Increasing the lock count for a lock already held would thus not need any additional memory. Another nice property is that we could have the shared-memory lock table register only one lock count per backend; increasing the local lock count for an already-obtained lock wouldn't require touching shared memory and thus not require obtaining the LockMgrLock. (This would be comparable to the existing mechanism for private vs. shared reference counts for buffers.) That might be enough of a win to buy back the extra time spent maintaining the additional hash table. This is a bigger change than I'd really like to be making in beta, but I don't see any other good solution to the memory-leak problem. Anyone have a better idea? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [COMMITTERS] pgsql-server: Rearrange pg_subtrans
On 25 Aug 2004, Greg Stark wrote: It's only allowed when the transaction is in READ UNCOMMITTED isolation level. Something Postgres doesn't currently support. In fact I'm not aware of any SQL database that supports it, though I'm sure there's one somewhere. Looks like mysql also supports it: http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_isolation.html Together with the other replies we now have a whole bunch of databases that implements it. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Typo in a comment in initdb.c
Andrew Dunstan [EMAIL PROTECTED] writes: s/from/from and/ Done. 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
[HACKERS] printing HeapTuple
Is there an existing function that I can use to print the HeapTuple return value of ExecScanHashBucket in nodeHash.c? Thanks, --h ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Contrib -- PostgreSQL shared variables
This is a first pass on a simple shared memory variable system for
PostgreSQL. I would appriciate anyone interested in this functionality to
rip it apart.
It basically adds this functionality:
SetShared('name', value);
GetSharedInt('name');
SetSharedText('name);
RemovedShared('name');
I also added two extra functions that are sort of a kludge, but could be
very helpful.
AddSharedInt('name', value);
SubSharedInt('name', value);
These add or subtect the 'value' from the variable and return the result.
sharedvar.tgz
Description: application/unix-tar
---(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] [COMMITTERS] pgsql-server: Rearrange pg_subtrans handling
Christopher Kings-Lynne wrote: OK, TODO updated: * Implement dirty reads or shared row locks and use them in RI triggers Can someone explain to me what a dirty read is and how it relates to RI triggers? Dirty read allows you to see uncommited rows. I think RI triggers need it so they can know if someone has a current transaction that is going to conflict with the RI trigger action, or something like that. Right now I think we hang waiting for the transaction to complete. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Contrib -- PostgreSQL shared variables
LockShared('name');
[EMAIL PROTECTED] wrote:
This is a first pass on a simple shared memory variable system for
PostgreSQL. I would appriciate anyone interested in this functionality to
rip it apart.
It basically adds this functionality:
SetShared('name', value);
GetSharedInt('name');
SetSharedText('name);
RemovedShared('name');
I also added two extra functions that are sort of a kludge, but could be
very helpful.
AddSharedInt('name', value);
SubSharedInt('name', value);
These add or subtect the 'value' from the variable and return the result.
---(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
---(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] Contrib -- PostgreSQL shared variables
LockShared('name');
Hmmm, I thought about that, but it is *WAY* more complicated than it
looks. What if after a Lock the process crashes before it can call
Unlock? It is this problem that inspired the add and sub calls.
[EMAIL PROTECTED] wrote:
This is a first pass on a simple shared memory variable system for
PostgreSQL. I would appriciate anyone interested in this functionality
to
rip it apart.
It basically adds this functionality:
SetShared('name', value);
GetSharedInt('name');
SetSharedText('name);
RemovedShared('name');
I also added two extra functions that are sort of a kludge, but could be
very helpful.
AddSharedInt('name', value);
SubSharedInt('name', value);
These add or subtect the 'value' from the variable and return the
result.
---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] printing HeapTuple
Hicham G. Elmongui [EMAIL PROTECTED] writes: Is there an existing function that I can use to print the HeapTuple return value of ExecScanHashBucket in nodeHash.c? printtup.c would be a good place to look ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Contrib -- PostgreSQL shared variables
[EMAIL PROTECTED] wrote:
This is a first pass on a simple shared memory variable system for
PostgreSQL. I would appriciate anyone interested in this functionality to
rip it apart.
It basically adds this functionality:
SetShared('name', value);
GetSharedInt('name');
SetSharedText('name);
RemovedShared('name');
I also added two extra functions that are sort of a kludge, but could be
very helpful.
AddSharedInt('name', value);
SubSharedInt('name', value);
These add or subtect the 'value' from the variable and return the result.
Something that I've found very useful when dealing with shared memory is
the ability to do atomic exchange of values. With that in place, its
possible to perform atomic operations involving several variables.
Perhaps it could be as simple as splitting your SetShared into
SetSharedInt and SetSharedString and then have them return the old value?
Here's an example of what I mean (I use C syntax for clarity, I know the
intended use is from SQL).
/* Loop until something other than LOCKED is returned. When
* that happens, we have the lock.
*/
while(SetSharedInt('lock', LOCKED) == LOCKED)
usleep(WAIT_PERIOD);
Set a group of variables here.
/* Unlock
*/
SetSharedInt('lock', UNLOCKED);
Regards,
Thomas Hallgren
---(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] [COMMITTERS] pgsql-server: Rearrange pg_subtrans handling
On Wed, Aug 25, 2004 at 05:10:30PM -0400, Bruce Momjian wrote: Christopher Kings-Lynne wrote: OK, TODO updated: * Implement dirty reads or shared row locks and use them in RI triggers Can someone explain to me what a dirty read is and how it relates to RI triggers? Dirty read allows you to see uncommited rows. I think RI triggers need it so they can know if someone has a current transaction that is going to conflict with the RI trigger action, or something like that. Right now I think we hang waiting for the transaction to complete. Yes, we hang, but we behave differently depending on the commit status of the transaction we are waiting for. We don't know that in advance, which means we would have to do something at our own transaction end to check that status. Personally I think this is a bad mechanism to hide our lack of shared row locks. We should be solving that problem instead, which I would like to attack next. FYI, I leave tomorrow morning to give a talk on Postgres at the 'Encuentro de Linux Norte' here in Chile, and will be back on monday. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La fuerza no está en los medios físicos sino que reside en una voluntad indomable (Gandhi) ---(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] [COMMITTERS] pgsql-server: Rearrange pg_subtrans
Dennis Bjorklund [EMAIL PROTECTED] writes: On 25 Aug 2004, Greg Stark wrote: It's only allowed when the transaction is in READ UNCOMMITTED isolation level. Something Postgres doesn't currently support. In fact I'm not aware of any SQL database that supports it, though I'm sure there's one somewhere. Looks like mysql also supports it: http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_isolation.html Together with the other replies we now have a whole bunch of databases that implements it. Well it would be a pretty handy feature. Several times I've seen people on the list trying to calculate how far some big batch update or load had proceeded by looking at the sizes of files in the data directory, estimating row sizes, and dividing. That's a pretty kludgy method for doing what could be done cleanly and with more flexibility by just running switching to read uncommitted mode and selecting to see how many records had been inserted. I don't know the details, but with postgres's model wouldn't it be a simply matter of treating every tuple found as having been inserting or deleted without checking to see if the transaction id in the tuple is committed? It should be even easier than the normal read committed mode. One danger would be for such dirty data leaking out into the rest of the database. But I would be pretty happy with such a feature even if it meant no inserts/deletes/updates could be performed while in that mode. -- 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] futex
Josh Berkus wrote: I'll have to admit to having only a beginner's grasp of this stuff, but I do believe that that was what the OSDL patch did. Jan also tried a simpler patch, which didn't work either.Both Jan and Tom have expressed the opinion that the only solution is to make the BufrMgrLock more granular (as in, not monolithic) but this is a huge task. It's worth noting that I did a lot of work toward that goal (making the BufMgrLock more granular) earlier in 2004, but unfortunately wasn't able to get it finished before leaving for the summer. I can't guarantee that the approach I took was the right one, but if anyone's interested in working on this area then it might save some time to start with previous work. I want to take another shot at doing this for 8.1, although I don't know right now if I'll be able to. -Neil ---(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] futex
Neil, It's worth noting that I did a lot of work toward that goal (making the BufMgrLock more granular) earlier in 2004, but unfortunately wasn't able to get it finished before leaving for the summer. I can't guarantee that the approach I took was the right one, but if anyone's interested in working on this area then it might save some time to start with previous work. I want to take another shot at doing this for 8.1, although I don't know right now if I'll be able to. Jan was interested in working on this as well; we hashed it out this summer. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql-server: Rearrange pg_subtrans
Greg Stark [EMAIL PROTECTED] writes: I don't know the details, but with postgres's model wouldn't it be a simply matter of treating every tuple found as having been inserting or deleted without checking to see if the transaction id in the tuple is committed? No. At least not if you want a view of the data that's even marginally sane (not multiple versions of the same row, for instance, or versions that are definitely dead by anyone's viewpoint). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Contrib -- PostgreSQL shared variables -with swap
This new version contains, in addition to the previous version, SwapShared(..) which allows you to swap a value in a variable. sharedvar-040825.tgz Description: application/unix-tar ---(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] rpm for RH AS 2.1 ?
Devrim GUNDUZ wrote: Hi, On Wed, 25 Aug 2004, Gaetano Mendola wrote: anyone succesfull create all the rpm for RH AS 2.1 ? I was able to do it using the srpms for RH9 but I did it with --define 'python 0'. Any idea on how to obtain also the postgresql-python.rpm ? Without 'pyhton 0' this is the error: snip AFAIK, it's an error related to Python version since I could build Python rpm for RHEL3. It has python v1.5 or so. Problem solved, apparently the RH AS 2.1 doesn't have the Distutil package installed by default so the distutils module was not available in order to retrieve the python_path. If you want put the rpm that I obtained in the ftp server you can find them here: http://mendola.no-ip.com/rpm745.htm let me know if you take it so I'll remove it. Regards Gaetano Mendola ---(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] AT TIME ZONE
Added to TODO. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: TODO entry? * Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken * allow customization of the known set of TZ names (generalize the present australian_timezones hack) I'm not sure whether we already have an entry for the latter. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] regression test failure with HEAD on OSX
make check produces the following regression.diffs: *** ./expected/geometry.out Fri Oct 31 22:07:07 2003 --- ./results/geometry.out Thu Aug 26 00:51:46 2004 *** *** 117,123 | (5.1,34.5) | [(1,2),(3,4)] | (3,4) | (-5,-12) | [(1,2),(3,4)] | (1,2) | (10,10)| [(1,2),(3,4)] | (3,4) ! | (0,0) | [(0,0),(6,6)] | (-0,0) | (-10,0)| [(0,0),(6,6)] | (0,0) | (-3,4) | [(0,0),(6,6)] | (0.5,0.5) | (5.1,34.5) | [(0,0),(6,6)] | (6,6) --- 117,123 | (5.1,34.5) | [(1,2),(3,4)] | (3,4) | (-5,-12) | [(1,2),(3,4)] | (1,2) | (10,10)| [(1,2),(3,4)] | (3,4) ! | (0,0) | [(0,0),(6,6)] | (0,0) | (-10,0)| [(0,0),(6,6)] | (0,0) | (-3,4) | [(0,0),(6,6)] | (0.5,0.5) | (5.1,34.5) | [(0,0),(6,6)] | (6,6) == The platform is OSX 10.3.5. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
