Re: [HACKERS] WAL replay failure after file truncation(?)
Tom Lane wrote: Manfred Koizar [EMAIL PROTECTED] writes: [...] Is it sufficient to remember just the relation and the block number or do we need the contents a well? I meant the contents of the WAL record, not the original block contents. Anyway, I think it's not needed. Oh, I see. Yes, it might be worth hanging onto for debugging purposes. If we did get a report of such a failure, I'm sure we'd wish to know what sort of WAL record triggered it. One trusts there won't be so many that storing 'em all is a problem ... regards, tom lane ---(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 I guess I am having the same problem here: I am just dealing with a truncated table after a hard kill. The symptoms are: The storage file of the table is missing while the system tables can still see the table. Looking at TRUNCATE (this is the only command which could potentially have caused this problem in my case) it seems as if the system tables are actually changed propery before the file on disk is truncated. My question is: What happens if the system is killed inside rebuild_relation or inside swap_relfilenodes which is called by rebuild_relation? many thanks and best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] soundex and metaphone
Jonah H. Harris wrote: I'm willing to move soundex and metaphone into the backend. Does anyone see a reason not to do so? As a kinda strange reason, I like them in contrib because they demonstrate a nice simple example of how one can write a contrib extension. This module has simple functions that take a string or two and return a string or number. Most of the other contrib modules do tricky stuff with weird types or indexes that make them rather complex to use as a starting point. If they were to be moved out of contrib, I think it'd be really nice if someone add a hello_world contrib that demonstrates a bunch of simple operations in C to be used as such a model. ---(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] Multiple-statement Rules Incompatible With Constraints
I've got a table I've split into two, a portion that can be modified under normal circumstances, and a portion that can't be. (In a testing mode they both can be, but in production the user IDs doing the work don't have update or delete access on the immutable portion. BTW, I'm open to better ways of doing this.) So it's along the lines of CREATE TABLE offer_immutable (offer_id serial PRIMARY KEY ); CREATE TABLE offer_mutable ( offer_id int PRIMARY KEY REFERENCES offer_immutable); ALTER TABLE offer_immutable ADD CONSTRAINT offer_immutable_offer_id_fkey FOREIGN KEY (offer_id) REFERENCES offer_mutable DEFERRABLE INITIALLY DEFERRED; CREATE VIEW offer AS SELECT * FROM offer_immutable NATURAL JOIN offer_mutable; In a transaction, when I try to commit, this does not work: CREATE OR REPLACE RULE offer_delete AS ON DELETE TO offer DO INSTEAD ( DELETE FROM offer_mutable WHERE offer_id = OLD.offer_id; DELETE FROM offer_immutable WHERE offer_id = OLD.offer_id; ); It gives me: ERROR: insert or update on table offer_immutable violates foreign key constraint offer_immutable_offer_id_fkey DETAIL: Key (offer_id)=(77) is not present in table offer_mutable. On the other hand, if I use this instead: CREATE OR REPLACE FUNCTION offer_delete(int) RETURNS VOID AS $$ DELETE FROM offer_mutable WHERE offer_id = $1; DELETE FROM offer_immutable WHERE offer_id = $1; $$ LANGUAGE 'SQL' VOLATILE SECURITY INVOKER; CREATE OR REPLACE RULE offer_delete AS ON DELETE TO offer DO INSTEAD SELECT offer_delete(OLD.offer_id); It works fine. I can also just do the two separate deletes in a transaction, and it works ok. BEGIN; DELETE FROM offer_mutable WHERE offer_id = 123; DELETE FROM offer_immutable WHERE offer_id = 123; COMMIT; Bug? cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Cost of XLogInsert CRC calculations
-Original Message- From: Manfred Koizar [mailto:[EMAIL PROTECTED] Sent: 25 May 2005 20:25 To: Manfred Koizar Cc: Tom Lane; Greg Stark; Bruce Momjian; Mark Cave-Ayland (External); pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Cost of XLogInsert CRC calculations (cut) The most important figure is, that at MaxSpeed (/O2) 2x32 is almost twice as fast as CRC64 while only being marginally slower than CRC32. Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Cost of XLogInsert CRC calculations
-Original Message- From: Manfred Koizar [mailto:[EMAIL PROTECTED] Sent: 25 May 2005 20:25 To: Manfred Koizar Cc: Tom Lane; Greg Stark; Bruce Momjian; Mark Cave-Ayland (External); pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Cost of XLogInsert CRC calculations (cut) The most important figure is, that at MaxSpeed (/O2) 2x32 is almost twice as fast as CRC64 while only being marginally slower than CRC32. Servus Manfred Hi Manfred, Sorry about taking a while to respond on this one - the hard drive on my laptop crashed :(. I repeated your tests on my P4 laptop with gcc 3.2.3 and reproduced the results below: Opt 32 32a32b2x32 64 64a 64b O1 4.91 4.86 5.43 6.00 11.4 11.3911.39 O2 4.96 4.94 4.69 5.18 15.86 18.7524.73 O3 4.82 4.83 4.64 5.18 15.14 13.7714.73 So in summary I would say: - Calculating a CRC64 using 2 x 32 int can be 3 times as fast as using 1 x 64 int on my 32-bit Intel laptop with gcc. - The time difference between CRC32 and CRC64 is about 0.5s in the worse case shown during testing, so staying with CRC64 would not inflict too great a penalty. Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] foreign keys and RI triggers
Neil Conway [EMAIL PROTECTED] writes: Speaking of which, does anyone see a reason why RI_FKey_keyequal_upd() is implemented as a pseudo-trigger function -- e.g. taking a pointer to a TriggerData? Because (a) it needs all the same arguments and (b) it can share infrastructure with the other RI triggers. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WAL replay failure after file truncation(?)
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: My question is: What happens if the system is killed inside rebuild_relation or inside swap_relfilenodes which is called by rebuild_relation? Nothing at all, because the system catalog updates aren't committed yet, and we haven't done anything to the relation's old physical file. If I were you I'd be looking into whether your disk hardware honors write ordering properly. This sounds like something allowed the directory change to reach disk before the transaction commit WAL record did; which is impossible if fsync is doing what it's supposed to. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Cost of XLogInsert CRC calculations
Mark Cave-Ayland [EMAIL PROTECTED] writes: Opt 32 32a32b2x32 64 64a 64b O14.91 4.86 5.43 6.00 11.4 11.3911.39 O24.96 4.94 4.69 5.18 15.86 18.7524.73 O34.82 4.83 4.64 5.18 15.14 13.7714.73 Not sure I believe these numbers. Shouldn't 2x32 be about twice as slow as just one 32-bit CRC? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WAL replay failure after file truncation(?)
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: My question is: What happens if the system is killed inside rebuild_relation or inside swap_relfilenodes which is called by rebuild_relation? Nothing at all, because the system catalog updates aren't committed yet, and we haven't done anything to the relation's old physical file. This is actually what I expected. I have gone through the code and it looks correct. TRUNCATE is the only command in this application which can potentially cause the problem (it is very unlikely that INSERT removes a file). If I were you I'd be looking into whether your disk hardware honors write ordering properly. This sounds like something allowed the directory change to reach disk before the transaction commit WAL record did; which is impossible if fsync is doing what it's supposed to. regards, tom lane We are on sun Solaris (x86) box here. I am not sure what Sun has corrupted to make this error happen. Obviously it happens only once per 1.000.000 tries ... I am just trying to figure out whether the bug could potentially be inside PostgreSQL. It would have been surprised if somebody had overseen a problem like that. many thanks and best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(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] Multiple-statement Rules Incompatible With Constraints
Curt Sampson [EMAIL PROTECTED] writes: CREATE VIEW offer AS SELECT * FROM offer_immutable NATURAL JOIN offer_mutable; In a transaction, when I try to commit, this does not work: CREATE OR REPLACE RULE offer_delete AS ON DELETE TO offer DO INSTEAD ( DELETE FROM offer_mutable WHERE offer_id = OLD.offer_id; DELETE FROM offer_immutable WHERE offer_id = OLD.offer_id; ); Same old same old: as soon as you've deleted from offer_mutable, there is no row in the view with the given offer_id; and since OLD is a macro for the view, the second delete finds nothing to do. You might be able to fix this by making the view an outer join, and deleting from the nullable side first. The whole data structure seems a tad weird though ... regards, tom lane ---(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] Cost of XLogInsert CRC calculations
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 27 May 2005 15:00 To: Mark Cave-Ayland (External) Cc: 'Manfred Koizar'; 'Greg Stark'; 'Bruce Momjian'; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Cost of XLogInsert CRC calculations Mark Cave-Ayland [EMAIL PROTECTED] writes: Opt 32 32a32b2x32 64 64a 64b O1 4.91 4.86 5.43 6.00 11.4 11.3911.39 O2 4.96 4.94 4.69 5.18 15.86 18.7524.73 O3 4.82 4.83 4.64 5.18 15.14 13.7714.73 Not sure I believe these numbers. Shouldn't 2x32 be about twice as slow as just one 32-bit CRC? Well it surprised me, although Manfred's results with VC6 on /MaxSpeed show a similar margin. The real killer has to be that I wrote a CRC32 routine in x86 inline assembler (which in comparison to the gcc-produced version stores the CRC for each iteration in registers instead of in memory as part of the current frame) which comes in at 6.5s Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] foreign keys and RI triggers
Tom Lane wrote: Because (a) it needs all the same arguments Well, it needs the Trigger that we're in the process of queueing, the old tuple, the new tuple, and the updated relation. It doesn't need the rest of the content of TriggerData. trigger.c has to manually construct a TriggerData to pass to it, so it's not like it's a notational convenience. (b) it can share infrastructure with the other RI triggers. Such as? I don't see anything it allows us to share. -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] Cost of XLogInsert CRC calculations
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 27 May 2005 15:00 To: Mark Cave-Ayland (External) Cc: 'Manfred Koizar'; 'Greg Stark'; 'Bruce Momjian'; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Cost of XLogInsert CRC calculations (cut) Not sure I believe these numbers. Shouldn't 2x32 be about twice as slow as just one 32-bit CRC? Also I've just quickly tested on the Xeon Linux FC1 box I used with my original program using Manfred's program and the margin is even closer: Opt 32 32a32b2x32 64 64a64b -- O12.75 2.81 2.71 3.16 3.53 3.64 7.25 O22.75 2.78 2.87 2.94 7.63 10.61 11.93 O32.84 2.85 3.03 2.99 7.63 7.64 7.71 I don't know whether gcc is just producing an inefficient CRC32 compared to 2x32 but the results seem very odd There must be something else we are missing? Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] A 2 phase commit weirdness
Heikki Linnakangas [EMAIL PROTECTED] writes: Looking at the sequence, at least the relcache init file stuff looks if not broken at least a bit heavy-handed... I was planning to change that ;-) ... using separate 2PC action records for the relcache init file actions would make it much better. Now consider this scenario: backend A: Do updates that cause an init file invalidation backend A: Commit begins backend A: unlink init file backend B starts and recreates init file backend A: send inval message backend C starts and reads the now stale init file No problem, because C will receive A's inval messages after that. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] foreign keys and RI triggers
Neil Conway [EMAIL PROTECTED] writes: (b) it can share infrastructure with the other RI triggers. Such as? I don't see anything it allows us to share. Note the ri_BuildQueryKeyFull call, and the arguments thereto. As to the notational convenience issue, I think it's good that RI_FKey_keyequal_upd looks almost exactly like the other RI trigger functions. Having it look different in order to save a couple lines in trigger.c doesn't seem like a win to me. regards, tom lane ---(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] rendezvous
Joshua D. Drake wrote: Should we not change it to bonjour as that is the actual name for it? Done, in CVS. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 8: explain analyze is your friend
Re: [HACKERS] logging sql from JDBC
On Wed, 2005-05-25 at 12:03 -0400, David Parker wrote: Could somebody point me at the area of the source where this decision gets made, and/or how difficult it would be to enable this logging? This has just been fixed for 8.1, patch recently submitted. In the meantime, use p6spy 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] logging sql from JDBC
Thanks! -Original Message- From: Simon Riggs [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 11:45 AM To: David Parker Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] logging sql from JDBC On Wed, 2005-05-25 at 12:03 -0400, David Parker wrote: Could somebody point me at the area of the source where this decision gets made, and/or how difficult it would be to enable this logging? This has just been fixed for 8.1, patch recently submitted. In the meantime, use p6spy Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL replay failure after file truncation(?)
On Wed, 2005-05-25 at 21:24 +0200, Manfred Koizar wrote: WAL replay does not apply changes to nonexistent blocks, but it keeps a list (hash table, file, whatever) of those blocks. When a truncate WAL record is found, all entries for blocks affected by the truncation are removed from the list. Is it sufficient to remember just the relation and the block number or do we need the contents a well? If the list is non-empty at the end of WAL replay, this is evidence of a serious problem (file system corruption or Postgres bug). Seems like a very neat solution. It has no side effects and seems fairly performant. Judging by the number of PANICs reported, the data structure would be mostly empty anyhow. Best Regards, Simon Riggs ---(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] Multiple-statement Rules Incompatible With Constraints
On Fri, 27 May 2005, Tom Lane wrote: Same old same old: as soon as you've deleted from offer_mutable, there is no row in the view with the given offer_id; and since OLD is a macro for the view, the second delete finds nothing to do. Oh, now I'm starting to see how this behavior does make sense...unwanted as it is in my case. Maybe I find it a bit weird that the sense of OLD.something can change within a rule, though. You might be able to fix this by making the view an outer join, and deleting from the nullable side first. Yuck. Though I'm already deep in yuck anyway. The whole data structure seems a tad weird though ... More than a tad. I'm not happy with this thing I've come up with. I'm really starting to think that I took the wrong path. What's a better way to express my intention? Perhaps the thing to do is go back to one table, and have a trigger that throws an exception if you try to update the non-updatable columns. (And only the test account can do deletes--if I need to do an update during testing, delete and insert is the answer.) What do you think? But as well, this is really also another instance of an area where functions work differently from things not in functions (and I tend to think that the way things work in functions in most of these cases is right). cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA ---(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] overlaps() does not work as expected?
I've quite some trouble with the overlaps function:
SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns true (these are german timestamps dd.mm.)
SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns false
Is this a bug or a (quite strange) feature?
Thanks!
---(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] Cost of XLogInsert CRC calculations
Mark Cave-Ayland [EMAIL PROTECTED] writes:
I don't know whether gcc is just producing an inefficient CRC32 compared to
2x32 but the results seem very odd There must be something else we are
missing?
I went back and looked at the code, and see that I was misled by
terminology: what we've been calling 2x32 in this thread is not two
independent CRC32 calculations, it is use of 32-bit arithmetic to execute
one CRC64 calculation. The inner loop looks like
while (__len-- 0)
{
int__tab_index = ((int) (__crc1 24) ^ *__data++) 0xFF;
__crc1 = crc_table1[__tab_index] ^ ((__crc1 8) | (__crc0 24));
__crc0 = crc_table0[__tab_index] ^ (__crc0 8);
}
whereas a plain CRC32 looks like
while (__len-- 0)
{
int__tab_index = ((int) (crc 24) ^ *__data++) 0xFF;
crc = crc_table[__tab_index] ^ (crc 8);
}
where the crc variables are uint32 in both cases. (The true 64-bit
calculation looks like the latter, except that the crc variable is
uint64, as is the crc_table, and the 24 becomes 56. The 2x32
code is an exact emulation of the true 64-bit code, with __crc1 and
__crc0 holding the high and low halves of the 64-bit crc.)
In my tests the second loop is about 10% faster than the first on an
Intel machine, and maybe 20% faster on HPPA. So evidently the bulk of
the cost is in the __tab_index calculation, and not so much in the table
fetches. This is still a bit surprising, but it's not totally silly.
Based on the numbers we've seen so far, one could argue for staying
with the 64-bit CRC, but changing the rule we use for selecting which
implementation code to use: use the true 64-bit code only when
sizeof(unsigned long) == 64, and otherwise use the 2x32 code, even if
there is a 64-bit unsigned long long type available. This essentially
assumes that the unsigned long long type isn't very efficient, which
isn't too unreasonable. This would buy most of the speedup without
giving up anything at all in the error-detection department.
Alternatively, we might say that 64-bit CRC was overkill from day one,
and we'd rather get the additional 10% or 20% or so speedup. I'm kinda
leaning in that direction, but only weakly.
Comments?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] overlaps() does not work as expected?
On Fri, May 27, 2005 at 06:35:32PM +0200, Mario Weilguni wrote:
I've quite some trouble with the overlaps function:
SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns true (these are german timestamps dd.mm.)
SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns false
I can reproduce it here. I'm not sure if this is a feature, but I don't
think so.
--
Alvaro Herrera (alvherre[a]surnet.cl)
Siempre hay que alimentar a los dioses, aunque la tierra esté seca (Orual)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] overlaps() does not work as expected?
On 5/27/05, Mario Weilguni wrote:
I've quite some trouble with the overlaps function:
SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns true (these are german timestamps dd.mm.)
SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns false
Is this a bug or a (quite strange) feature?
It is consistent with the SQL standard:
quote
overlaps predicate uses the operator OVERLAPS to determine whether
or not two chronological periods overlap in time. A chronological
period is specified aither as a pair of datetimes (starting and
ending) or as a starting datetime and an interval. If the length of
the period is greater than 0 (zero), then the period consists of all
points of time greater than or equal to the lower endpoint, and less
than the upper endpoint. If the length of the period is equal to 0
(zero), then the period consists of a single point in time, the lower
endpoint. Two periods overlap if they have at least one point in
common.
/quote ISO/IEC 9075-2:2003 4.6 Datetimes and intervals.
For those who prefer BNF look at 8.13 overlaps predicate in said standard.
Jochem
---(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] OSCON field trip, Friday afternoon
Folks, With OSCON 2005 (August 1-5) registration now open, I wanted to invite members of the PostgreSQL community along on a field trip immediately following OSCON. We will be visiting the offices of the National Weather Service and of ODSL in Portland. Transportation will be provided sponsored by SRA America. This means that you may not want to fly home from OSCON until late Friday night or Saturday morning. We're limited in the total number of people we can bring, so preference will be given to PostgreSQL contributors if it comes to that. As such, it's important that you RSVP to me and to Brian at SRA ( [EMAIL PROTECTED] ) as soon as you know that you're available. See you at OSCON! -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Cost of XLogInsert CRC calculations
Tom Lane wrote: Alternatively, we might say that 64-bit CRC was overkill from day one, and we'd rather get the additional 10% or 20% or so speedup. I'm kinda leaning in that direction, but only weakly. Yes, I lean in that direction too since the CRC calculation is showing up in our profiling. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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] Oracle Style packages on postgres
Added to TODO: * Add the features of packages o Make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o Add session variables o Allow nested schemas --- Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? --- Dave Held wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 8:43 AM To: Thomas Hallgren Cc: Tom Lane; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? Is that really the best way to go about things? Already RDBMSes are patchwork quilts of functionality. Is merely adding another patch the most elegant way to evolve the database? The problem is that Oracle et al are trying to be ORDBMSes and aren't exactly sure what the best way to go is. Instead of trying to formulate a rational plan for what an ORDBMS should even look like, they simply look at what would work with their existing infrastructure and tack on features. Then Postgres plays the copycat game. Instead of trying to play catch-up with Oracle, why not beat them at their own game? What packages provide is encapsulation. Hiding the data from the user and forcing him/her to use the public interface (methods). That is an important and admirable OO feature. Some people think that using the DB's security model can achieve the same thing. It can't, exactly, but there's an important lesson to be learned from the suggestion. The problem is that OOP is a *programming* paradigm, and a database is not a *programming language*. In a programming language, there really is no such thing as security. There is only visibility and accessibility. Private methods in an OOP language do not provide *security*; they only limit *accessibility*. Like so many other differences between the relational model and the OOP model, there is an impedance mismatch here. However, there is also opportunity. In an OOPL, you can say: Users can call this method from here, but not from there. What you *can't* say is: User X can call this method, but User Y cannot. As you can see, these are orthogonal concepts. You could call the first accessibility by location and the second accessibility by authentication. An ORDBMS should support both. Private does not respect your identity, only your calling location. An ACL does not respect your calling scope, only your identity. A system that has both is clearly more flexible than one that only has one or the other. Now what you need to keep in mind is that each visibility model serves a different purpose. The purpose of a security model is to limit *who* can see/touch certain data because the data has intrinsic value. The purpose of an accessibility model is to limit *where* and *how* data can be seen/touched in order to preserve *program invariants*. So if you have an object (or tuple!) that records the start and stop time of some process, it is probably a logical invariant that the stop time is greater than or equal to the start time. For this reason, in a PL, you would encapsulate these fields (attributes) and only provide controlled access to update them that checks and preserves the invariant, *no matter who you are*. You don't want a superuser violating this invariant any more than Sue User. Now you might object that constraints allow you to preserve invariants as well, and indeed they do. But constraints do not respect calling scope. Suppose there is a process that needs to update the timestamps in a way that temporarily breaks the invariant but restores it afterwards. The only way to effect this in a constraint environment is to drop the constraint, perform the operation, and restore it. However, dropping a constraint is not an ideal solution because there may be other unprivileged processes operating on the relation that still need the constraint to be enforced. There is no way to say: There is a priviledged class of methods that is allowed to violate this constraint because they are trusted to restore it upon completion. Note that this is different from saying There is a priviledged class of users that is allowed to violate
Re: [HACKERS] overlaps() does not work as expected?
Mario Weilguni [EMAIL PROTECTED] writes:
I've quite some trouble with the overlaps function:
SELECT overlaps('9.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns true (these are german timestamps dd.mm.)
SELECT overlaps('8.6.2005'::date, '9.6.2005'::date, '9.6.2005'::date,
'9.6.2005'::date);
returns false
Is this a bug or a (quite strange) feature?
AFAICS it is per spec. SQL99 defines the result of
(S1, T1) OVERLAPS (S2, T2)
(where S1 = T1 and S2 = T2, else swap pairs of values to make this so)
as
( S1 S2 AND NOT ( S1 = T2 AND T1 = T2 ) )
OR
( S2 S1 AND NOT ( S2 = T1 AND T2 = T1 ) )
OR
( S1 = S2 AND ( T1 T2 OR T1 = T2 ) )
Your first case has S1 = S2, so it will be TRUE as long as T1 and T2
are both nonnull, according to the third clause. Your second case
has S1 S2, so the definition reduces to
NOT ( S2 = T1 AND T2 = T1 )
and since in fact those three values are all equal, the NOT is false.
I think they may have intended to treat each time interval
as the half-open interval [S,T), that is S = time T. However
that would leave a zero-length interval as completely empty and
thereby arguably not overlapping anything ... which they didn't
make it do.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] INOUT/OUT problems with IMMUTABLE
Is the following behavior intended? CREATE FUNCTION foo(INOUT x integer, INOUT y integer) AS $$ BEGIN x := x * 10; y := y * 10; END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1, 2); x | y + 10 | 20 (1 row) ALTER FUNCTION foo(integer, integer) IMMUTABLE; SELECT * FROM foo(1, 2); ERROR: function return row and query-specified return row do not match DETAIL: Returned row contains 2 attributes, but query expects 0. A case with a single parameter works: CREATE FUNCTION bar(INOUT x integer) AS $$ BEGIN x := x * 10; END; $$ LANGUAGE plpgsql; SELECT * FROM bar(1); bar - 10 (1 row) ALTER FUNCTION bar(integer) IMMUTABLE; SELECT * FROM bar(1); bar - 10 (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] overlaps() does not work as expected?
Tom Lane [EMAIL PROTECTED] writes: I think they may have intended to treat each time interval as the half-open interval [S,T), that is S = time T. However that would leave a zero-length interval as completely empty and thereby arguably not overlapping anything ... which they didn't make it do. Well an empty interval would be just as inconsistent. I guess they wanted half-open intervals and they had a problem with zero-length intervals one way or the other and just chose the one that seemed most useful. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Can we simplify win32 threading code
Magnus Hagander [EMAIL PROTECTED] writes It may not be necessary at all. But what I meant is I don't know what the default permissinos ar eon a shared mem/event object in the global namespace. On the naemd pipe it's creator/owner full (so the postgres account itself can issue kills), administrators full (so anybody who is admin on the box can) and system full (so the local system can issue them). If this is not the default on the other class of objects, we'll need to set specific permissions. Ok, I will come up with a patch per discussion. One more thing, any tips to test the PG win32 signal part? Regards, Qingqing ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] patches for items from TODO list
I have removed the XML TODO item: * Add XML output to pg_dump and COPY We already allow XML to be stored in the database, and XPath queries can be used on that data using /contrib/xml2. It also supports XSLT transformations. --- Josh Berkus wrote: Folks, - The COPY - XML transformation is trivial -- it would be easy for clients to roll their own. At the same time, there is no standard or canonical XML representation for COPY output, and I can easily imagine different clients needing different representations. So there is limited value in providing a single, inflexible backend implementation. I'm going to second Neil here. This feature becomes useful *only* when there is a certified or de-facto universal standard XML representation for database data. Then I could see a case for it. But there isn't. Feel free to throw it on pgFoundry, though. -- 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]) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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] INOUT/OUT problems with IMMUTABLE
Michael Fuhr [EMAIL PROTECTED] writes: Is the following behavior intended? Nope. Thanks for the report ;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
