Re: [HACKERS] create or replace view
Problem is when I want change view (or functions) with a lot of dependecies I must drop and recreate all dependent views (or functions) - I want add only one column in view I don't know if solution hard for that. I do not see how adding a column to a view would invalidate dependent objects. (Except an object that uses select *, in which case the writer of the object explicitly states that he can cope with changing column count and order). Thus I think create or replace should work in this case regardless of what definition for create or replace finds a consensus, no ? Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Debian build prob
On Thu, 14 Nov 2002, Patrick Welche wrote: Believe it or not, I'm trying to compile today's cvs pgsql on a Debian 2.2.19 system. Compilation dies while compiling pg_dump with ../../../src/interfaces/libpq/libpq.so: undefined reference to `atexit' In the mail archives there is a mention of upgrading libc to libc6-dev_2.2.5-3_i386.deb. As far as I can tell, that should read libc6_2.2.5-3_i386.deb, and again AFAICT this system already has libc6_2.2.5-6_i386.deb on it. I can see atexit is undefined in libpq, and it is defined in /usr/lib/libc.a. For some reason /lib/libc*.so are stripped, so it is hard to tell, but I assume it must be the same as for /usr/lib/libc.a. Have any of you managed to compile postgresql on an oldstable Debian system? The latest I've built was from somewhere like the beta 3 mark but yes, built it on a Debian 2.2 installation with no library upgrades or anything. Now of course one would need a new bison. -- Nigel J. Andrews ---(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
[HACKERS] 500 tpsQL + WAL log implementation
I have been experimenting with empirical tests of file system and device level writes to determine the actual constraints in order to speed up the WAL logging code. Using a raw file partition and a time-based technique for determining the optimal write position, I am able to get 8K writes physically written to disk synchronously in the range of 500 to 650 writes per second using FreeBSD raw device partitions on IDE disks (with write cache disabled). I will be testing it soon under linux with 10,00RPM SCSI which should be even better. It is my belief that the mechanism used to achieve these speeds could be incorporated into the existing WAL logging code as an abstraction that looks to the WAL code just like the file level access currently used. The current speeds are limited by the speed of a single disk rotation. For a 7,200 RPM disk this is 120/second, for a 10,000 RPM disk this is 166.66/second The mechanism works by adjusting the seek offset of the write by using gettimeofday to determine approximately where the disk head is in its rotation. The mechanism does not use any AIO calls. Assuming the following: 1) Disk rotation time is 8.333ms or 8333us (7200 RPM). 2) A write at offset 1,500K completes at system time 103s 000ms 000us 3) A new write is requested at system time 103s 004ms 166us 4) A 390K per rotation alignment of the data on the disk. 5) A write must be sent at least 20K ahead of the current head position to ensure that it is written in less than one rotation. It can be determined from the above that a write for an offset of something slightly more than 195K past the last write, or offset 1,695K will be ahead of the current location of the head and will therefore complete in less than a single rotation's time. The disk specific metrics (rotation speed, bytes per rotation, base write time, etc.) can be derived empirically through a tester program that would take a few minutes to run and which could be run at log setup time. The obvious problem with the above mechanism is that the WAL log needs to be able to read from the log file in transaction order during recovery. This could be provided for using an abstraction that prepends the logical order for each block written to the disk and makes sure that the log blocks contain either a valid logical order number or some other marker indicating that the block is not being used. A bitmap of blocks that have already been used would be kept in memory for quickly determining the next set of possible unused blocks but this bitmap would not need to be written to disk except during normal shutdown since in the even of a failure the bitmaps would be reconstructed by reading all the blocks from the disk. Checkpointing and something akin to log rotation could be handled using this mechanism as well. So, MY REAL QUESTION is whether or not this is the sort of speed improvement that warrants the work of writing the required abstraction layer and making this very robust. The WAL code should remain essentially unchanged, with perhaps new calls for the five or six routines used to access the log files, and handle the equivalent of log rotation for raw device access. These new calls would either use the current file based implementation or the new logging mechanism depending on the configuration. I anticipate that the extra work required for a PostgreSQL administrator to use the proposed logging mechanism would be to: 1) Create a raw device partition of the appropriate size 2) Run the metrics tester for that device partition 3) Set the appropriate configuration parameters to indicate raw WAL logging I anticipate that the additional space requirements for this system would be on the order of 10% to 15% beyond the current file-based implementation's requirements. So, is this worth doing? Would a robust implementation likely be accepted for 7.4 assuming it can demonstrate speed improvements in the range of 500tps? - Curtis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Ignore prior post -- Where is src/interfaces/perl5 in beta 5?
On Sun, 10 Nov 2002 16:29:03 -0600 GB Clark [EMAIL PROTECTED] wrote: Hello, Well the subject says it all. It was there in the last beta I was testing which was beta 1. Am I being dense here, or is it really missing? Thanks, GB It was me being dense! It has been removed. Sorry for the noise. GB -- GB Clark II | Roaming FreeBSD Admin [EMAIL PROTECTED] | General Geek CTHULU for President - Why choose the lesser of two evils? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Locking Question
Hi guys, I have searched high and low to find a definitive answer and so far have only found lots of discussion about the pros and cons of the subject : Simply, is/will there in the future be a facility to return immediately from select for update with an error message if the row is locked, and not wait until the lock is freed ? (ALA Oracle SELECT FOR UPDATE NOWAIT) I need to know this as this directly affects my long-term use of the product. Thanks very much Jo _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Idea for better handling of cntxDirty
Wouldn't it work for cntxDirty to be set not by LockBuffer, but by XLogInsert for each buffer that is included in its argument list? I thought to add separate call to mark context dirty but above should work if all callers to XLogInsert always pass all modified buffers - please check. Vadim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Proposal of hierachical queries (a la Oracle)
Hi there! I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B I've seen this type of queries often made by adding a new type, which stores position of row in the tree. But sorting such tree are very tricky (i think). Patch allows result tree to be sorted, i.e. subnodes of each node will be sorted by ORDER BY clause. with regards, evgen --- .evgen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE : RE : [HACKERS] Stability problems
Scott you're right, it was a hardware problem. Thanks for your help. Glad to be of help. What was the problem? Bad memory or bad hard drive? Just curious. It was a bad 512Mo memory module and a bad memory slot on the motherboard. Our hosting provider never checks memory before, but now it will make the test systematically. Nicolas VERGER ---(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] Stability problems
You're right, it was a hardware problem. Thanks for your help. Nicolas VERGER -Message d'origine- De : [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED]] De la part de scott.marlowe Envoyé : mercredi 6 novembre 2002 21:38 À : Nicolas VERGER Cc : 'PostgreSQL Hackers Mailing List' Objet : Re: [HACKERS] Stability problems I would recommend checking your memory (look for memtest86 online somewhere. Good tool.) Anytime a machine seems to act flakely there's a better than even chance it has a bad bit of memory in it. On Wed, 6 Nov 2002, Nicolas VERGER wrote: Hi, I have strange stability problems. I can't access a table (the table is different each time I get the problem, it could be a system table (pg_am), or a user defined one): Can't select * the whole table but can select * limit x offset y, so it appears that only a tuple is in bad status. I can't vacuum or pg_dump this table too. The error disappears after waiting some time. I get the following error in log when select the 'bad' line: 2002-11-05 11:26:42 [3062] DEBUG: server process (pid 4551) was terminated by signal 11 2002-11-05 11:26:42 [3062] DEBUG: terminating any other active server processes 2002-11-05 11:26:42 [4555] FATAL 1: The database system is in recovery mode 2002-11-05 11:26:42 [3062] DEBUG: all server processes terminated; reinitializing shared memory and semaphores 2002-11-05 11:26:42 [4557] DEBUG: database system was interrupted at 2002-11-05 11:23:00 CET I get the following error in log when vacuuming the 'bad' table: 2002-11-05 14:46:44 [5768] FATAL 2: failed to add item with len = 191 to page 150 (free space 4294967096, nusd 0, noff 0) 2002-11-05 14:46:44 [5569] DEBUG: server process (pid 5768) exited with exit code 2 2002-11-05 14:46:44 [5569] DEBUG: terminating any other active server processes 2002-11-05 14:46:44 [5771] NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. 2002-11-05 14:46:44 [5772] NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. 2002-11-05 14:46:44 [5569] DEBUG: all server processes terminated; reinitializing shared memory and semaphores 2002-11-05 14:46:44 [5774] DEBUG: database system was interrupted at 2002-11-05 14:46:40 CET template1=# select version(); PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 Is it a lock problem? Is there a way to log it? Thanks for all making such a good job. Nicolas VERGER ---(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 ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Proposal of hierarchical queries, a la Oracle
Hi there! I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B I've seen this type of queries often made by adding a new type, which stores position of row in the tree. But sorting such tree are very tricky (i think). Patch allows result tree to be sorted, i.e. subnodes of each node will be sorted by ORDER BY clause. with regards, evgen --- .evgen ---(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] create or replace view
On Fri, 15 Nov 2002, Zeugswetter Andreas SB SD wrote: Problem is when I want change view (or functions) with a lot of dependecies I must drop and recreate all dependent views (or functions) - I want add only one column in view I don't know if solution hard for that. I do not see how adding a column to a view would invalidate dependent objects. (Except an object that uses select *, in which case the writer of the object explicitly states that he can cope with changing column count and order). I'm not sure, but can all the places that currently save a plan deal with getting a longer rowtype than expected? I'd guess so due to inheritance, but we'd have to be absolutely sure. It'd also change the return type for functions that are defined to return the composite type the view defines. ---(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] c/sql
[EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Hello, I know Ansi C language and SQL. I want to embed a sql statement into a c source code. Would you please recommend me some web sites or tutorials regarding this subject. Thank you for your concern, Emrah ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Hi, You need to read about the possible interfaces for postgreSQL. I've used the libpq++ for my C++ classes with great success. So, #include libpq++.h and embed the sql with sprintf(x,sql) then send x to the server with if(!ExecCommandOk(x)) printf(Error...); Good luck, Jeff ---(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] pg_dump in 7.4
On Wed, 2002-11-13 at 23:37, Philip Warner wrote: At 02:53 PM 13/11/2002 -0500, Rod Taylor wrote: I can make a complete list tonight of whats captured. Sounds good Below is a summary of what pg_depend tracks that might be useful. Skipped a number of dependencies that are internal only (ie. toast table dependencies) as they will be regenerated correctly if their 'owners' are generated correctly. Expression Dependencies include: - Operators - Functions - Relations (and columns) - Aggregates Attributes (Columns) depend on: - Type of attribute Tables depend on: - Namespace - Parent tables (if inheritance) Default expressions depend on: - Table - Expression Dependencies Indexes depend on: - Constraint (where unique / primary key constraint) - Index procedure - Index operator - Attributes of indexed relation Aggregates depend on: - Transformation function - Final function (if required) Foreign Keys depend on: - Foreign key'd relation and its attributes - Constrained relation and its attributes - Unique Index on the foreign key'd relation Check Constraints depend on: - Expression Dependencies - includes parent relation - Domain type (if check constraint on domain -- v7.4) Operators depend on: - Namespace - Left operator type - Right operator type - Result operator type - Code function - Rest function - Join function Functions depend on: - Namespace - Language - Return type - Argument types (all) Types (domains included) depend on: - Namespace - Input type - Output type - Element type (if array) - Base type (if domain) - Default value - Expression Dependencies Casts depend on: - Source type - Target type - Cast function Operator Classes depend on: - Namespaces - Input type - Key data type (if different than input type) - Dependencies on operators in the class - Dependencies on procedures in the class Languages depend on: - Call function - Validation function Triggers depend on: - Trigger function - Relation trigger is on - Constrained relation (if constraint trigger) Rules depend on: - Relation rule is on - Qualifying condition - Expression Dependencies - resulting Query Tree - Expression Dependencies Missing: - Body of all functions -- Rod Taylor [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] regression test failure (CVS HEAD)
Seems like a result of Alverro's cluster patch -- looks like the patch didn't updated the expected results for the regression tests fully. Diffs below. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC *** ./expected/cluster.out Fri Nov 15 12:35:36 2002 --- ./results/cluster.out Fri Nov 15 12:39:33 2002 *** *** 302,307 --- 302,310 INSERT INTO clstr_2 VALUES (1); INSERT INTO clstr_3 VALUES (2); INSERT INTO clstr_3 VALUES (1); + -- CLUSTER tablename on a table that hasn't been clustered + CLUSTER clstr_2; + ERROR: CLUSTER: No previously clustered index found on table clstr_2 CLUSTER clstr_1_pkey ON clstr_1; CLUSTER clstr_2_pkey ON clstr_2; SELECT * FROM clstr_1 UNION ALL *** *** 344,349 --- 347,364 1 (6 rows) + -- cluster a single table using the indisclustered bit previously set + DELETE FROM clstr_1; + INSERT INTO clstr_1 VALUES (2); + INSERT INTO clstr_1 VALUES (1); + CLUSTER clstr_1; + SELECT * FROM clstr_1; + a + --- + 1 + 2 + (2 rows) + -- clean up \c - DROP TABLE clstr_1; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal of hierachical queries (a la Oracle)
On Thu, 2002-11-14 at 06:52, Evgen Potemkin wrote:
Hi there!
I want to propose the patch for adding the hierarchical queries posibility.
It allows to construct queries a la Oracle for ex:
SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B
Great addition. But please use the SQL 99 syntax for recursive queries
(if you need the full segment, I can send them to you):
Section 7.13 of Part 2:
Format
search or cycle clause ::=
search clause
| cycle clause
| search clause cycle clause
search clause ::=
SEARCH recursive search order SET sequence column
recursive search order ::=
DEPTH FIRST BY sort specification list
| BREADTH FIRST BY sort specification list
sequence column ::= column name
cycle clause ::=
CYCLE cycle column list
SET cycle mark column TO cycle mark value
DEFAULT non-cycle mark value
USING path column
cycle column list ::=
cycle column [ { comma cycle column }... ]
cycle column ::= column name
cycle mark column ::= column name
path column ::= column name
cycle mark value ::= value expression
non-cycle mark value ::= value expression
--
Rod Taylor [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [ANNOUNCE] PostgreSQL v7.3b5 Packaged for Testing ...
Hi Marc, On Fri, 8 Nov 2002 at 09:59, Marc G. Fournier wrote: At this point, we are looking for confirmation that all the platforms are building and running the regression tests correctly, v7.3b5 builds and passes the testsuite under SuSE Linux on the following platforms: i386, ia64, ppc, ppc64, s390, s390x, x86_64 For ppc64, and s390x I need patches for tas() which I have subitted to the [Patches] list, but they have been rejected for 7.3. Also the geometry test needs patching on some of the platforms due to variances in the floating point precision. I have not submitted these patches yet, but could do so if desired. cu Reinhard ---(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] [GENERAL] PostgreSQL v7.3b5 Packaged for Testing ...
Marc G. Fournier wrote: At this point, we are looking for confirmation that all the platforms are building and running the regression tests correctly, and, unless any major bug reports are submitted, any further changes to this branch will be either documentation related, or tweaks to ensure as many platforms work as possible before release. All is well with 7.3b5 on Mac OS X 10.2.1 (Jaguar) on a PowerBook G4 [gcc (GCC) 3.1 20020420 (prerelease)]. == All 89 tests passed. == -- Tara Piorkowski System Administrator, vilaj.com, LLC http://www.vilaj.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Transaction safe Truncate
Ok. Transaction safe truncate is simply transaction safe cluster without the data copy and a slightly different set of permission checks. I'd like to split cluster_rel() in cluster.c into 2 functions at line 174. The permission checks, locking, etc will remain in cluster_rel(). The bottom half will be turned into a function called rebuild_rel(tableOid Oid, indexOid Oid, dataCopy bool). If dataCopy is set to false, then indexOid may be null -- this will truncate the table. Cluster will set dataCopy to true which will maintain current expectations for cluster. I'll also move TruncateRelation into cluster.c. PreCommit_on_commit_actions() - ONCOMMIT_DELETE_ROWS is the only location using heap_truncate(). It may be possible to change this and remove heap_truncate() altogether. -- Rod Taylor [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parallel
On Wed, 13 Nov 2002 14:22:51 -0800 (PST), Stephan Szabo
[EMAIL PROTECTED] wrote:
Right now, I know that it has a hole that lets through invalid data
Stephan, your patch has been posted to -general (Subject: Re:
[GENERAL] Help..Help...). Is this version still valid?
void
heap_mark4fk_lock_acquire(Relation relation, HeapTuple tuple) {
[...]
/* try to find the list for the table in question */
This part of the patch works, if the list
(a) is initially empty or
(b) already contains relid or
(c) starts with a table relid.
while (ptr!=NULL) {
if (relidptr-table) {
ptr=ptr-next;
oldptr=ptr;
// AFAICT above two lines should be swapped ...
}
else
break;
}
... otherwise
(d) if the new relid is to be inserted between two existing entries,
we get two items pointing to each other
(e) if the new relid is the last table in the list, we lose the
whole list.
Servus
Manfred
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] char(n) to varchar or text conversion should strip trailing spaces
I've gotten really tired of explaining to newbies why stuff involving char(n) fields doesn't work like they expect. Our current behavior is not valid per SQL92 anyway, I believe. I think there is a pretty simple solution now that we have pg_cast: we could stop treating char(n) as binary-equivalent to varchar/text, and instead define it as requiring a runtime conversion (which would be essentially the rtrim() function). The cast in the other direction would be assignment-only, so that any expression that involves mixed char(n) and varchar/text operations would be evaluated in varchar rules after stripping char's insignificant trailing blanks. If we did this, then operations like WHERE UPPER(charcolumn) = 'FOO' would work as a newbie expects. I believe that we'd come a lot closer to spec compliance on the behavior of char(n), too. Comments? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Transaction safe Truncate
Rod Taylor [EMAIL PROTECTED] writes: I'd like to split cluster_rel() in cluster.c into 2 functions at line 174. The permission checks, locking, etc will remain in cluster_rel(). The bottom half will be turned into a function called rebuild_rel(tableOid Oid, indexOid Oid, dataCopy bool). I just finished fixing the division of labor between TruncateRelation and heap_truncate. Please don't break it merely to avoid rearranging code in cluster.c. Actually, I'd argue that cluster should adopt truncate's code layout, not vice versa. I'll also move TruncateRelation into cluster.c. You could leave it where it is and just move heap_truncate. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parallel
On Fri, 15 Nov 2002, Manfred Koizar wrote:
On Wed, 13 Nov 2002 14:22:51 -0800 (PST), Stephan Szabo
[EMAIL PROTECTED] wrote:
Right now, I know that it has a hole that lets through invalid data
Stephan, your patch has been posted to -general (Subject: Re:
[GENERAL] Help..Help...). Is this version still valid?
I have a newer version of it on my machine, but I was still sending out
that version of the patch. :( Thanks for letting me know before even more
people got a version that was broken. :)
For anyone working with the patch, you need to fix the lines below as
noted by Manfred. This is mostly unrelated to the hole mentioned in the
quoted message above (it's a bug that with the bug you actually partially
fill the hole but instead deadlock). I wonder if there were any other
stupdities in there.
void
heap_mark4fk_lock_acquire(Relation relation, HeapTuple tuple) {
[...]
/* try to find the list for the table in question */
This part of the patch works, if the list
(a) is initially empty or
(b) already contains relid or
(c) starts with a table relid.
while (ptr!=NULL) {
if (relidptr-table) {
ptr=ptr-next;
oldptr=ptr;
// AFAICT above two lines should be swapped ...
}
else
break;
}
... otherwise
(d) if the new relid is to be inserted between two existing entries,
we get two items pointing to each other
(e) if the new relid is the last table in the list, we lose the
whole list.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RI_FKey_check: foreign key constraint blocks parall
On Fri, 15 Nov 2002, Mikheev, Vadim wrote: Just wonder how are you going to implement it - is it by using some kind of read-locks, ie FK transaction locks PK to prevent delete (this is known as pessimistic approach)? About two years ago we discussed with Jan optimistic approach with using dirty reads, when PK/FK transactions do not check existence of FK/PK untill constraint should be checked (after statement processed for immediate mode, at the commit time/ set constraint immediate for deferred constraints). So, at the check time, FK transaction uses dirty reads to know about existence/status of PK: 1. No PK - abort. 2. PK (inserted?/)deleted/updated/selected for update by concurrent transaction P - wait for P commit/abort (just like transactions do for concurrent same-row-update); go to 1. 3. Else (PK exists and no one changing it right now) - proceed. PK transaction does the same: 1. No FK - proceed. 2. FK inserted/updated/selected for update by concurrent transaction F - wait for F commit/abort; go to 1. This would be more in MVCC style -:) Right now, it's similar to the above, but only one direction is doing the dirty reads right now. I don't do the dirty reads on the fk transactions right now. It'll still see delete/update/selected for update on a row that would have otherwise existed for the transaction, but not see the new rows (I'd like to switch it to dirty both directions, but I'm having enough trouble with deadlocks as it is). Or, at least that's the intention behind the code if not the actual effect. It gets rid of the concurrency issues of two fk transactions, but it doesn't get rid of deadlock cases. T1: insert into fk values (1); T2: delete from pk; T1: insert into fk values (1); shouldn't need to deadlock. The lock stuff is actually more like an un-lock to make it not wait on the second T1 statement. It's broken, however, as I just thought of some more things it doesn't handle correctly. Oh well. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Time to move on...
-Original Message- From: Thomas Lockhart [mailto:[EMAIL PROTECTED]] Sent: 15 November 2002 06:38 To: [EMAIL PROTECTED] Subject: [HACKERS] Time to move on... Just a quick note to mention that I've resigned from the PostgreSQL steering committee. It has been a lot of fun and very rewarding to participate in PostgreSQL development over the last six years, but it is time to take a break and to move on to other projects. Good luck in whatever you decide to do next Thomas. Your effort and help over the last few years has certainly been appreciated by me, and I'm sure by many others as well. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Time to move on...
Christopher Kings-Lynne [EMAIL PROTECTED] writes: To the rest of the hackers, is it normal practice to perhaps vote in a new member of the steering committee? Uh ... it's never happened before ... so there is no normal practice. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Time to move on...
Hey Thomas, Although we have never corresponded, I just wanted to say thank you to yourself and all the other hackers who have devoted their time voluntarily to PostgreSQL. It really is appreciated. Cheers Mark Pritchard On Fri, 15 Nov 2002 17:38, Thomas Lockhart wrote: Just a quick note to mention that I've resigned from the PostgreSQL steering committee. It has been a lot of fun and very rewarding to participate in PostgreSQL development over the last six years, but it is time to take a break and to move on to other projects. Thanks to Marc, Bruce, and Vadim for welcoming me many years ago. It has been great working with the group and I'm looking forward to seeing PostgreSQL achieve greater and greater success in the coming years. - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Propose RC1 for Friday ...
I've tested this under 7.3, and it works beautifully for the cases I've built over the last 2 days. I can no longer bugger a plan up mearly by reordering the WHERE clauses. Note that 2 of the five parts won't patch in (involving constantqual). Looks to be code refactoring between here and planmain.c on the 7.4 branch? I tried to hand-patch it in, and gave up. it _seems_ to work without it, but I probably haven't covered that codepath. Ross On Thu, Nov 14, 2002 at 01:33:05PM -0500, Tom Lane wrote: I said: Well, we could define it as a bug ;-) --- that is, a performance regression. I'd be happier about adding a dozen lines of code to sort quals by whether or not they contain a subplan than about flip-flopping on the original patch. That would actually solve the class of problem you exhibited, whereas the other is just a band-aid that happens to work for your particular example. The attached patch does the above. I think it's a very low-risk change, but am tossing it out on the list to see if anyone objects to applying it in the 7.3 branch. (I intend to put it in 7.4devel in any case.) regards, tom lane ---(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] Time to move on...
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: To the rest of the hackers, is it normal practice to perhaps vote in a new member of the steering committee? Uh ... it's never happened before ... so there is no normal practice. The logic usually has been to add people to core who are so involved in the release process that we couldn't imagine scheduling a release without them. I am not saying all current core members are that involved, but at the time they were added to core, they were. -- 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 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] Time to move on...
On Friday 15 November 2002 01:38, Thomas Lockhart wrote: Just a quick note to mention that I've resigned from the PostgreSQL steering committee. It has been a lot of fun and very rewarding to participate in PostgreSQL development over the last six years, but it is time to take a break and to move on to other projects. I'll echo the sad day response of earlier. You have done quite a bit for the project, and you will be missed. Thanks to Marc, Bruce, and Vadim for welcoming me many years ago. It has been great working with the group and I'm looking forward to seeing PostgreSQL achieve greater and greater success in the coming years. Thomas, good luck. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Geometry regression tests (was Re: [HACKERS] Float output
Tom Lane writes: I find that two geometry 'expected' files are now sufficient to cover all the platforms I have available to test. (We'd only need one, if everyone displayed minus zero as '-0', but some platforms print '0'.) Judging from the platforms affected by this, I would suspect that this is a (mis-)feature of the snprintf() implementation rather than compiler or processor. Would it make sense to provide a fixed version of snprintf() and get rid of these differences? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Time to move on...
On Friday 15 November 2002 10:23, Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: To the rest of the hackers, is it normal practice to perhaps vote in a new member of the steering committee? Uh ... it's never happened before ... so there is no normal practice. IMHO, replacement of a core member should be treated the same as bringing in a new core member, which, IIRC, is by invitation and vote of the balance of the core members. If a replacement is immediately necessary, that is. Having five core versus six core isn't a great handicap, as the potential replacement pool consists of people who are already doing development now. Having an odd number of core has its advantages. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Geometry regression tests (was Re: [HACKERS] Float output formatting options)
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: I find that two geometry 'expected' files are now sufficient to cover all the platforms I have available to test. (We'd only need one, if everyone displayed minus zero as '-0', but some platforms print '0'.) Judging from the platforms affected by this, I would suspect that this is a (mis-)feature of the snprintf() implementation rather than compiler or processor. Would it make sense to provide a fixed version of snprintf() and get rid of these differences? Certainly it's a library issue on most of these platforms --- AFAIK, all these machines have IEEE-compliant float hardware, so it must be sprintf's fault and not a matter of not getting the minus zero in the first place. I wouldn't want to write a float converter from scratch, but maybe we could add a few lines in src/port/snprintf.c to patch up a wrong result? regards, tom lane ---(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] [CYGWIN] ipc-daemon
Tom, Peter, On Mon, Nov 04, 2002 at 03:05:25PM -0500, Jason Tishler wrote: On Mon, Nov 04, 2002 at 02:43:01PM -0500, Tom Lane wrote: If you can detect that cygipc is not running, then ENOSYS seems the best choice for reporting that. (ENOSPC would be misleading too.) Thanks for your feedback. I will take this to the Cygwin list and see what happens. I happy to report that the above has been accomplished: http://cygwin.com/ml/cygwin-announce/2002-11/msg00025.html Hopefully, the Cygwin initdb and postmaster hang posts will be a thing of the past. Unfortunately, they probably will be replaced by questions like: Why does initdb fail with IpcMemoryCreate: shmget(...) failed: Function not implemented? Jason -- PGP/GPG Key: http://www.tishler.net/jason/pubkey.asc or key servers Fingerprint: 7A73 1405 7F2B E669 C19D 8784 1AFD E4CC ECF4 8EF6 ---(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] Time to move on...
Lamar Owen wrote: On Friday 15 November 2002 10:23, Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: To the rest of the hackers, is it normal practice to perhaps vote in a new member of the steering committee? Uh ... it's never happened before ... so there is no normal practice. IMHO, replacement of a core member should be treated the same as bringing in a new core member, which, IIRC, is by invitation and vote of the balance of the core members. If a replacement is immediately necessary, that is. Having five core versus six core isn't a great handicap, as the potential replacement pool consists of people who are already doing development now. Having an odd number of core has its advantages. I will reiterate for the new folks that the core group doesn't do much more than decide if the final release will be on a Friday or a Monday, and deal with private issues like discipline. I think we deal with such issues perhaps 2-4 times a year. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [CYGWIN] ipc-daemon
At least that is an FAQ item. --- Jason Tishler wrote: Tom, Peter, On Mon, Nov 04, 2002 at 03:05:25PM -0500, Jason Tishler wrote: On Mon, Nov 04, 2002 at 02:43:01PM -0500, Tom Lane wrote: If you can detect that cygipc is not running, then ENOSYS seems the best choice for reporting that. (ENOSPC would be misleading too.) Thanks for your feedback. I will take this to the Cygwin list and see what happens. I happy to report that the above has been accomplished: http://cygwin.com/ml/cygwin-announce/2002-11/msg00025.html Hopefully, the Cygwin initdb and postmaster hang posts will be a thing of the past. Unfortunately, they probably will be replaced by questions like: Why does initdb fail with IpcMemoryCreate: shmget(...) failed: Function not implemented? Jason -- PGP/GPG Key: http://www.tishler.net/jason/pubkey.asc or key servers Fingerprint: 7A73 1405 7F2B E669 C19D 8784 1AFD E4CC ECF4 8EF6 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] regression test failure (CVS HEAD)
Applied. Sorry I missed this one. I did a clean compile and initdb for testing, but forgot regression. --- Neil Conway wrote: Seems like a result of Alverro's cluster patch -- looks like the patch didn't updated the expected results for the regression tests fully. Diffs below. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC *** ./expected/cluster.outFri Nov 15 12:35:36 2002 --- ./results/cluster.out Fri Nov 15 12:39:33 2002 *** *** 302,307 --- 302,310 INSERT INTO clstr_2 VALUES (1); INSERT INTO clstr_3 VALUES (2); INSERT INTO clstr_3 VALUES (1); + -- CLUSTER tablename on a table that hasn't been clustered + CLUSTER clstr_2; + ERROR: CLUSTER: No previously clustered index found on table clstr_2 CLUSTER clstr_1_pkey ON clstr_1; CLUSTER clstr_2_pkey ON clstr_2; SELECT * FROM clstr_1 UNION ALL *** *** 344,349 --- 347,364 1 (6 rows) + -- cluster a single table using the indisclustered bit previously set + DELETE FROM clstr_1; + INSERT INTO clstr_1 VALUES (2); + INSERT INTO clstr_1 VALUES (1); + CLUSTER clstr_1; + SELECT * FROM clstr_1; + a + --- + 1 + 2 + (2 rows) + -- clean up \c - DROP TABLE clstr_1; ---(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/users-lounge/docs/faq.html
Re: [HACKERS] [SQL] Inconsistent or incomplete behavior obverse in where
Josh, Thanks for the reply. Much of what you say is as we expected. I see that 7.3 has addressed the Unable to identify an operator '=' for types 'numeric' and 'double precision' problem, but I'm not sure how. Context-sensitive approach? Overloaded operator approach? Something else ( is there )? If the release of 7.3 is soon, perhaps we can get by with the band-aid approach of overloading the comparison operators until such time as the new version is available. Production for us is next spring, so maybe we'll be okay on this one. This approach would certainly allow our development team to right their code one way. Paul, Unable to identify an operator '=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast This is due, as you surmised, to decimal values defaulting to floats. While there is little problem with an = operator for numeric and float, you would not want an implicit cast for a / operator with numeric and float. As a result, I believe that all numeric and float operators have been left undefined. I am aware of the use of type casting to force the desired behavior in these situations. I have also started to go down the road of creating functions and operators to force numeric to numeric comparison operations when comparing numeric to float, but realize that this approach is fraught with pitfalls, in fact it is interesting to us to note that with an operator in place to force numeric = float comparisons to parse as numeric = numeric, we started getting the opposite behavior. Queries with 'column reference' = 0.0 worked fine, but queries with 'column reference' = 0 threw a variant of the previous exception: Unable to identify an operator '=' for types 'numeric' and 'integer' Now, that's interesting. Why would defining a numeric = float have broken numeric = integer? There's no reason I can think of. Perhaps I will try this myself and see if I encounter the same problem, or if your team modified the numeric = integer operator by mistake. No, we made no modifications to numeric = integer. In fact, issuing DROP OPERATOR (numeric,float8); cleared that problem right up. And brought us back to square one. Overall, this behavior appears to be inconsistent and is not the same behavior I have experienced with many other DBMS's. Specifically, it seems strange that the parser does not treat values 0.0 or 77.5 as numeric(s[,p]) when comparing the values to a column reference known to be of type numeric (s,[p]). Is an unquoted number in the form of NN.N always treated as a float? Yes. I believe that this is from the SQL 92 spec; hopefully someone on this list with a copy of the Guide to the SQL Standard can quote it for you. If the planner could somehow recognize that the constant/ literal value was being compared to a column reference of the type numeric (s,p) and treat the value accordingly, then would operator identification no longer be a problem? It's an interesting idea, and would be wonderful if it could be made to work. However, the challenge of getting the program to correctly recognize the context for all literal values *without* making any wrong assumptions that would afffect the data could be substantial. Most other RDBMSs deal with this, not by any kind of data type context-sensitivity, but simply by supporting a large number of implicit casts. This approach can have its own perils, as I have experienced with MS SQL Server, where the average of splits for 120,000 transactions is significantly different if you accidentally let the database implicitly cast the values as Float instead of Numeric. As such, there was talk on the Hackers list at one time of *reducing* the number of implicit casts instead of increasing them. This would obviously make your particular problem even worse, but the proponents of reduction point out that implicit casts can get you into real trouble if you're not aware of them, wheras forcing explicit casts just gets you error messages. Hmmm ... in fact, I'd think the perfect solution would be a compile-time option or contrib package which allows you to enable/disable implicit casts for many data types. I think this is a great idea. We're more of the postgres user class than hacker class, so that's out of our scope to undertake, but we'd sure use it. We are looking to maintain a high degree of portability in our application code, and while CAST ( expression as type ) is fairly portable, no one here feels that it is a portable as column reference = literal/constant value. If someone knows of a better approach, or can point us to documentation of build or run-time configuration that affects the query planner where this issue is concerned, it would be much appreciated. Hopefully someone else will respond to your message as well. I'll
Re: [HACKERS] Time to move on...
I will reiterate for the new folks that the core group doesn't do much more than decide if the final release will be on a Friday or a Monday, and deal with private issues like discipline. I think we deal with such issues perhaps 2-4 times a year. OK sorry - I was under the impression that core == commit bit... Chris ---(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] Time to move on...
On Saturday 16 November 2002 01:07, Christopher Kings-Lynne wrote: I will reiterate for the new folks that the core group doesn't do much more than decide if the final release will be on a Friday or a Monday, and deal with private issues like discipline. I think we deal with such issues perhaps 2-4 times a year. OK sorry - I was under the impression that core == commit bit... committers != core -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Time to move on...
Lamar Owen [EMAIL PROTECTED] writes: On Saturday 16 November 2002 01:07, Christopher Kings-Lynne wrote: OK sorry - I was under the impression that core == commit bit... committers != core Is there any reason for this distinction? Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
