Re: [HACKERS] [PATCHES] How can I use 2GB of shared buffers on Windows?
Takayuki Tsunakawa wrote: From: Magnus Hagander [EMAIL PROTECTED] Right. Which is why you're likely to see better performance if you keep shared buffers smaller. There is something in dealing with it that's slow on win32, per reports from the field. It needs to be investigated further... We've had reports that it's slow with large shared_buffers, yes. That's a shocking news. I'm sad. It shouldn't make you sad until it has been proven that it's actually a problem. And if we can do something about it :-) I wonder whether the field you are talking about set Windows to use more memory for programs than for filesystem cache, which is selectable from [System] applet of Control Panel (Oh, I wonder how my machine is set in this respect... have to check.) If filesystem cache is preferred, the following senario may be possible: Could be, I haven't run the tests myself, and it was tests on production systems, and not actual benchmark runs. Are you sure you're not running this on for example IDE disks with write-cache that lies? Windows will write through that write-cache even if the disk lies, whereas most linux versions won't. At least that used to be the case not too long ago, but there has also been talking about fixign that in linux, so maybe that's done... I'm using a PC server whose disks are all SCSI. It has no IDE disk. There goes that idea :-) Then you need to dig further into why it's such a big difference. Also note that when you run pg_bench on the local machine, you take a much higher hit from the fact that context switching between processes is a lot more expensive on Windows than it is on Linux. But it shouldn't be big enough to explain the huge difference you had in your test. Yes, I suspect it, too. So, Oracle uses one multi-threaded server process on Windows, while it employs multi-process architecture. SQL Server is of course multi-threaded. SRA's original PostgreSQL for Windows (based on 7.x) was also multi-threaded. Right. The windows MM and scheduler system is definitely geared for multithreaded. But that would make pg on win32 too different from pg on unix to be maintained without a significantly larger effort than today, so unless you can find some non-intrusive way to sneak it in (which I doubt), that's just not going to happen. That said, the context switching overhead shouldn't be *that* large. but it'd be interesting to see what the performance difference would be for the same machine with pg_bench running on a different machine across a fast network connection. (BTW, another difference is that pg_bench on unix would be using unix domain sockets and on windows it would be using tcp/ip. But that really shouldn't make such a huge difference either, but I guess it would add a bit to the factor) //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Ooops ... seems we need a re-release pronto
Tom Lane wrote: Jim Nasby [EMAIL PROTECTED] writes: On Feb 6, 2007, at 12:40 PM, Tom Lane wrote: ... massive expansion of the tests doesn't seem justified What about the idea that's been floated in the past about a -- extensive mode for regression testing that would (generally) only be used by the build farm. That would mean others wouldn't have to suffer through extremely long make check's. Or is there another reason not to expand the tests? I'm not concerned so much about the runtime as the development and maintenance effort... Shouldn't we at least add the one or two exemplary statements that failed so we have some sort of coverage of the problem? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Ooops ... seems we need a re-release pronto
Peter Eisentraut [EMAIL PROTECTED] writes: Shouldn't we at least add the one or two exemplary statements that failed so we have some sort of coverage of the problem? We could, but I'm unexcited about it. The known failures are an extremely narrow case: we're trying to evaluate expressions (either CHECK constraints or index expressions) over a tuple proposed to be inserted into a relation. But the TupleDesc that's been supplied to the evaluator is not the tuple descriptor of the target relation, it's a descriptor generated on-the-fly from the target list of the plan tree (by ExecTypeFromTL). And the target list includes some constants, and our nodetree representation for constants fails to preserve typmod knowledge, and so ExecTypeFromTL produces atttypmod -1 for this column, and the security check didn't like that because the Var it was checking had a nondefault typmod. When the first reports came in, I thought seriously about fixing it by forcing the target relation's real tupdesc (from its relcache entry) to be used in this context instead of a generated tupdesc. I concluded that it was too likely that there were other cases where we were evaluating expressions against generated tuples, and we had to back off the strength of the security check instead. I do not actually have any specific examples, but I think it's fairly pointless to add a regression test that covers this one narrow scenario when there are probably lots of others. I'm not especially a fan of the testing philosophy that says you memorialize each individual past mistake as a permanent regression test --- I think that just bloats the tests, and test bloat is a bad thing because it discourages people from running the tests. (MySQL's regression tests currently require about an hour on a fast machine. Somehow this has not helped them to achieve a low bug rate...) I do agree with adding a test when you think it is likely to be able to catch a whole class of errors, or even a specific error if it seems especially likely to recur, but right now I'm not seeing how we do that here. BTW, I think a good case could be made that the core of the problem is exactly that struct Const doesn't carry typmod, and thus that we lose information about constructs like 'foo'::char(7). We should fix that, and also anywhere else in the expression tree structure where we are discarding knowledge about the typmod of a result. This has got some urgency because of Teodor's recent work on allowing user defined types to have typmods --- we can expect massive growth in the number of scenarios where it matters. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Foreign keys for non-default datatypes, redux
On Fri, 9 Feb 2007, Tom Lane wrote: Almost a year ago, we talked about the problem that referential integrity should be selecting comparison operators on the basis of b-tree index opclasses, instead of assuming that the appropriate operator is always named =: http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php http://archives.postgresql.org/pgsql-hackers/2006-03/msg00161.php I'm about to go off and implement that at last. To refresh folks' memory, what I think we agreed to was that at the time of definition of a foreign-key constraint, we should identify the specific equality operator to be used for (each column of) the constraint. The method for doing this is to be: * First, identify the unique index that is relied on to enforce uniqueness of the PK entries (we do this already of course). * Look to see if there is an equality operator in this index's opfamily accepting exactly the PK and FK data types (ie, PK = FK). If so, use that. * Else, check to see if there is an implicit promotion from the FK datatype to the PK datatype. If so, use the equality operator PK = PK, which must exist since the opfamily supports an index on the PK datatype. * Else fail (this means that the present warning about inefficient foreign keys will become a hard error). I assume you're speaking of the version where we just change the constraints to use statements with the OPERATOR() syntax and potential casts rather than the discussion at the end about changing the pk checks to avoid planning entirely? My intention is that we'd record pg_depend entries making the RI constraint dependent on not only the index, but the specific operators to use. This would not have been too critical a year ago given that opclasses were effectively immutable; but in the current opfamily design it's entirely likely that we'd select cross-type equality operators that are considered loose and potentially droppable from the opfamily. So we need dependencies to prevent the operators from disappearing out from under us. (Come to think of it, we might want to record dependencies on the casts too, if we're using implicit casts?) I think we probably should, so the above seems reasonable to me. * Add an oid[] column to pg_constraint that stores the equality operator OIDs for a foreign-key constraint, in the same column order as conkey[] and confkey[]. * Add an OID column to pg_trigger giving the OID of the constraint owning the trigger (or 0 if none). Add this information to struct Trigger as well, so that it gets passed to trigger functions. Given the pg_constraint OID, the RI triggers could fetch the constraint row and look at conkey[], confkey[], and the new operator oid[] array to determine what they need to know. This would actually mean that they don't need pg_trigger.tgargs at all. I am pretty strongly tempted to stop storing anything in tgargs for RI triggers --- it's ugly, and updating the info during RENAME commands is a pain in the rear. On the other hand removing it might break client-side code that expects to look at tgargs to learn about FK constraints. I'd personally think that pg_constraint is a lot easier to work with, but there might be some code out there left over from way back before pg_constraint existed --- anyone know of any such issue? I'd say we probably want to keep the tgargs info for at least a version or two after changing the implementation. Getting rid of using the args info sounds like a good idea. One side question is what should we do about the places in the current system where it checks for the key sets being empty? AFAIK, we still don't actually support letting you define a constraint that way, and I haven't heard any complaints about that, and I'm not even sure if that actually made it into the spec proper. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] XML export
The issue of XML export has been discussed a few times throughout
history. Right now you've got the HTML output in psql. A few
people have proposed real XML output formats in psql or elsewhere.
I dug out some old code today that implements what SQL/XML has to say
on the matter and fitted the code to work with the current XML support
in the backend.
Below are examples of what it can do. I'm thinking about hosting this
on PgFoundry, but if the crowd thinks this should be somewhere else,
short of the moon, let me know.
regression=# select table_to_xml('select * from emp');
table_to_xml
---
table xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
row
namesharon/name
age25/age
location(15,12)/location
salary1000/salary
managersam/manager
/row
...
row
namelinda/name
age19/age
location(0.9,6.1)/location
salary100/salary
manager xsi:nil='true'/
/row
/table
(1 row)
As a use case of sorts, I've got an XSLT stylesheet that can convert
this to HTML tables.
regression=# select table_to_xmlschema('select * from emp');
table_to_xmlschema
-
xsd:schema
xmlns:xsd='http://www.w3.org/2001/XMLSchema'
xmlns:sqlxml='http://standards.iso.org/iso/9075/2003/sqlxml'
xsd:import
namespace='http://standards.iso.org/iso/9075/2003/sqlxml'
schemaLocation='http://standards.iso.org/iso/9075/2003/sqlxml.xsd'/
xsd:simpleType name=X-PostgreSQL.regression.pg_catalog.text
xsd:restriction base=xsd:string
xsd:maxLength value=MLIT/
/xsd:restriction
/xsd:simpleType
xsd:simpleType name=INTEGER
xsd:restriction base='xsd:int'
xsd:maxInclusive value=2147483647/
xsd:minInclusive value=-2147483648/
/xsd:restriction
/xsd:simpleType
xsd:simpleType
name='X-PostgreSQL.regression.pg_catalog.point'/xsd:simpleType
xsd:simpleType
name='X-PostgreSQL.regression.pg_catalog.name'/xsd:simpleType
xsd:complexType name='RowType'
xsd:sequence
xsd:element name='name' type='X-PostgreSQL.regression.pg_catalog.text'
nillable='true'/xsd:element
xsd:element name='age' type='INTEGER' nillable='true'/xsd:element
xsd:element name='location'
type='X-PostgreSQL.regression.pg_catalog.point' nillable='true'/xsd:element
xsd:element name='salary' type='INTEGER' nillable='true'/xsd:element
xsd:element name='manager' type='X-PostgreSQL.regression.pg_catalog.name'
nillable='true'/xsd:element
/xsd:sequence
/xsd:complexType
xsd:complexType name='TableType'
xsd:sequence
xsd:element name='row' type='RowType' minOccurs='0'
maxOccurs='unbounded'/
/xsd:sequence
/xsd:complexType
xsd:element name='table' type='TableType'/
/xsd:schema
(1 row)
I also have a table function which can convert both of these back into
an table, so that would be XML import. But that doesn't work quite yet.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Foreign keys for non-default datatypes, redux
Stephan Szabo [EMAIL PROTECTED] writes: I assume you're speaking of the version where we just change the constraints to use statements with the OPERATOR() syntax and potential casts rather than the discussion at the end about changing the pk checks to avoid planning entirely? Yeah, we might get around to doing that someday but I'm not excited about it right now. (I'm mainly doing this because it fits in with the operator-family work I've been doing --- that also got rid of some unsupportable assumptions about operators being named = ...) I'd say we probably want to keep the tgargs info for at least a version or two after changing the implementation. Getting rid of using the args info sounds like a good idea. We whack the catalogs around in incompatible ways in every release. I'm willing to keep filling tgargs if someone can point to a real use-case, but not just because there might be code out there somewhere using it. One side question is what should we do about the places in the current system where it checks for the key sets being empty? I don't see that this affects that either way. I can't quite imagine what the semantics would be, though --- there's no such thing as a unique constraint with no columns, so how can there be an RI constraint with none? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Foreign keys for non-default datatypes, redux
On Sat, 10 Feb 2007, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: One side question is what should we do about the places in the current system where it checks for the key sets being empty? I don't see that this affects that either way. I can't quite imagine what the semantics would be, though --- there's no such thing as a unique constraint with no columns, so how can there be an RI constraint with none? Well, the code currently has checks with comments based on SQL3 text AFAICT. /* -- * SQL3 11.9 referential constraint definition * General rules 2) a): * If Rf and Rt are empty (no columns to compare given) * constraint is true if 0 (SELECT COUNT(*) FROM T) * * Note: The special case that no columns are given cannot * occur up to now in Postgres, it's just there for * future enhancements. * -- */ The reason I was wondering is that it uses tgnargs == 4 as the check, and if we change the meanings of tgnargs, we'd need to change the check. Personally, I think we should probably just pull out the special case for now, but thought it should be brought up. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Foreign keys for non-default datatypes, redux
Stephan Szabo [EMAIL PROTECTED] writes: The reason I was wondering is that it uses tgnargs == 4 as the check, and if we change the meanings of tgnargs, we'd need to change the check. Sure, it'd be looking for a zero-length conkeys array instead. regards, tom lane ---(end of broadcast)--- TIP 1: 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] XML export
Peter Eisentraut wrote: The issue of XML export has been discussed a few times throughout history. Right now you've got the HTML output in psql. A few people have proposed real XML output formats in psql or elsewhere. I dug out some old code today that implements what SQL/XML has to say on the matter and fitted the code to work with the current XML support in the backend. Below are examples of what it can do. I'm thinking about hosting this on PgFoundry, but if the crowd thinks this should be somewhere else, short of the moon, let me know. Integrated, native XML support can only help PostgreSQL. IMO, I want this in core. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] XML export
On Feb 10, 2007, at 2:35 PM, Joshua D. Drake wrote: Peter Eisentraut wrote: The issue of XML export has been discussed a few times throughout history. Right now you've got the HTML output in psql. A few people have proposed real XML output formats in psql or elsewhere. I dug out some old code today that implements what SQL/XML has to say on the matter and fitted the code to work with the current XML support in the backend. Below are examples of what it can do. I'm thinking about hosting this on PgFoundry, but if the crowd thinks this should be somewhere else, short of the moon, let me know. Integrated, native XML support can only help PostgreSQL. IMO, I want this in core. Agreed. In the server would be more useful to more people I think. It would be really convenient to be able to have no effort XML results sets to queries. // Theo Schlossnagle // [EMAIL PROTECTED]: http://omniti.com // Esoteric Curio: http://www.lethargy.org/~jesus/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] XML export
Joshua D. Drake wrote: Peter Eisentraut wrote: The issue of XML export has been discussed a few times throughout history. Right now you've got the HTML output in psql. A few people have proposed real XML output formats in psql or elsewhere. I dug out some old code today that implements what SQL/XML has to say on the matter and fitted the code to work with the current XML support in the backend. Below are examples of what it can do. I'm thinking about hosting this on PgFoundry, but if the crowd thinks this should be somewhere else, short of the moon, let me know. Integrated, native XML support can only help PostgreSQL. IMO, I want this in core. +1 Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] XML export
Peter Eisentraut wrote: The issue of XML export has been discussed a few times throughout history. Right now you've got the HTML output in psql. A few people have proposed real XML output formats in psql or elsewhere. I dug out some old code today that implements what SQL/XML has to say on the matter and fitted the code to work with the current XML support in the backend. Below are examples of what it can do. I'm thinking about hosting this on PgFoundry, but if the crowd thinks this should be somewhere else, short of the moon, let me know. I'm not really a XML fan - but nevertheless having something like this in core sounds useful. Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XML export
Peter Eisentraut wrote: Below are examples of what it can do. I'm thinking about hosting this on PgFoundry, but if the crowd thinks this should be somewhere else, short of the moon, let me know. How do you treat columns whose names are not legal XML names? I'm glad to see you treat NULL as an attribute - that's definitely the right way I think. Have you thought about possibly using a standard encoding (e.g. base64) for bytea? Not sure what the standard says on encoding. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Foreign keys for non-default datatypes, redux
Stephan Szabo [EMAIL PROTECTED] writes: On Fri, 9 Feb 2007, Tom Lane wrote: I am pretty strongly tempted to stop storing anything in tgargs for RI triggers --- it's ugly, and updating the info during RENAME commands is a pain in the rear. I'd say we probably want to keep the tgargs info for at least a version or two after changing the implementation. Getting rid of using the args info sounds like a good idea. After digging around in the code for awhile I realized that there's a potentially bigger backwards-compatibility issue here: if we make the RI triggers dependent on finding a pg_constraint entry, then foreign key constraints loaded from dumps from pre-7.3 databases will no longer work. Those dumps just contain CREATE CONSTRAINT TRIGGER commands which will not provide enough information. We can make the triggers throw errors suggesting that the user drop the triggers and perform ALTER TABLE ADD CONSTRAINT. Is that enough, or do we need to try harder? It would probably be possible to teach pg_dump to cons up ADD CONSTRAINT commands when dumping from an old server, but I think it would be a lot of work (certainly we punted on that idea back in the 7.3 devel cycle) and I'm not sure there are enough people running pre-7.3 PG for it to be worth the effort to provide an automated solution. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Foreign keys for non-default datatypes, redux
After digging around in the code for awhile I realized that there's a potentially bigger backwards-compatibility issue here: if we make the RI triggers dependent on finding a pg_constraint entry, then foreign key constraints loaded from dumps from pre-7.3 databases will no longer work. Those dumps just contain CREATE CONSTRAINT TRIGGER commands which will not provide enough information. We can make the triggers throw errors suggesting that the user drop the triggers and perform ALTER TABLE ADD CONSTRAINT. Is that enough, or do we need to try harder? I think it is reasonable to expect that we can not support 7.3 dumps in that manner considering we are talking about 8.3 ;). We can't be backward compatible forever. Further in their right mind is trying to do a 24x7 shop on 7.3. They could always dump to 8.1 and then to 8.3. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Foreign keys for non-default datatypes, redux
Joshua D. Drake [EMAIL PROTECTED] writes: key constraints loaded from dumps from pre-7.3 databases will no longer work. Those dumps just contain CREATE CONSTRAINT TRIGGER commands which will not provide enough information. We can make the triggers throw errors suggesting that the user drop the triggers and perform ALTER TABLE ADD CONSTRAINT. Is that enough, or do we need to try harder? Further in their right mind is trying to do a 24x7 shop on 7.3. They could always dump to 8.1 and then to 8.3. Actually that wouldn't help: you'd still have a CREATE CONSTRAINT TRIGGER -based foreign key. The only thing that'd really fix it is having used the old contrib adddepend utility at some point along the line. Since we never forced people to do that, it's fairly likely that some never did. [Thinks for a bit...] It would still work to run adddepend over the schema even after having loaded it into 8.3, assuming that adddepend hasn't suffered bit-rot. We dropped it from contrib because no one was maintaining it anymore, but AFAIR there was no evidence that it's actively broken. So maybe we can just point to that for anyone who comes along with an upgrade problem. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] XML export
Andrew Dunstan wrote: How do you treat columns whose names are not legal XML names? There are escape mechanisms in place. You can verify yourself how they work using select xmlelement(name something unusual); I'm glad to see you treat NULL as an attribute - that's definitely the right way I think. The standard provides for the option of representing them the way I showed or omitting them. Have you thought about possibly using a standard encoding (e.g. base64) for bytea? Not sure what the standard says on encoding. It says to use base64 or hex. You can also verify that yourself using select xmlelement(name foo, bytea 'something'); -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 10 weeks to feature freeze (Pending Work)
Jim Nasby wrote: On Feb 5, 2007, at 12:53 PM, Andrew Hammond wrote: On Jan 26, 2:38 pm, [EMAIL PROTECTED] (Tom Lane) wrote: Rick Gigger [EMAIL PROTECTED] writes: I thought that the following todo item just barely missed 8.2: Allow a warm standby system to also allow read-only statements [pitr] No, it's a someday-wishlist item; the work involved is not small. Slony1 has supported log-shipping replication for about a year now. It provides similar functionality. Not really 1) It's not possible for a PITR 'slave' to fall behind to a state where it will never catch up, unless it's just on inadequate hardware. Same isn't true with slony. 2) PITR handles DDL seamlessly 3) PITR is *much* simpler to configure and maintain Which is why I was hoping for a PITR based solution. Oh well, I will have to figure out what is my best option now that I know it will not be available any time in the near future. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Acclerating INSERT/UPDATE using UPS
Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(end of broadcast)--- TIP 1: 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: StrNCpy - strlcpy (not complete)
Woh. Peter, you realize one of the reasons we use StrNCpy as a macro is for performance. I don't see strlcpy as a macro. Are you going to change all call locations to strlcpy? If so, have you measured the performance impact? --- Peter Eisentraut wrote: Log Message: --- StrNCpy - strlcpy (not complete) Modified Files: -- pgsql/src/backend/bootstrap: bootstrap.c (r1.229 - r1.230) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/bootstrap/bootstrap.c.diff?r1=1.229r2=1.230) pgsql/src/backend/libpq: crypt.c (r1.72 - r1.73) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/crypt.c.diff?r1=1.72r2=1.73) hba.c (r1.159 - r1.160) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/hba.c.diff?r1=1.159r2=1.160) ip.c (r1.39 - r1.40) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/ip.c.diff?r1=1.39r2=1.40) pgsql/src/backend/nodes: print.c (r1.83 - r1.84) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/print.c.diff?r1=1.83r2=1.84) pgsql/src/backend/postmaster: pgarch.c (r1.28 - r1.29) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/pgarch.c.diff?r1=1.28r2=1.29) postmaster.c (r1.518 - r1.519) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c.diff?r1=1.518r2=1.519) pgsql/src/backend/tcop: postgres.c (r1.521 - r1.522) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/postgres.c.diff?r1=1.521r2=1.522) pgsql/src/backend/utils/misc: guc-file.l (r1.46 - r1.47) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc-file.l.diff?r1=1.46r2=1.47) pgsql/src/bin/initdb: initdb.c (r1.131 - r1.132) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/initdb/initdb.c.diff?r1=1.131r2=1.132) pgsql/src/bin/pg_ctl: pg_ctl.c (r1.77 - r1.78) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_ctl/pg_ctl.c.diff?r1=1.77r2=1.78) pgsql/src/bin/pg_dump: pg_dumpall.c (r1.89 - r1.90) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dumpall.c.diff?r1=1.89r2=1.90) pgsql/src/bin/pg_resetxlog: pg_resetxlog.c (r1.56 - r1.57) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_resetxlog/pg_resetxlog.c.diff?r1=1.56r2=1.57) pgsql/src/interfaces/libpq: fe-auth.c (r1.122 - r1.123) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-auth.c.diff?r1=1.122r2=1.123) fe-connect.c (r1.342 - r1.343) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-connect.c.diff?r1=1.342r2=1.343) pgsql/src/timezone: pgtz.c (r1.49 - r1.50) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c.diff?r1=1.49r2=1.50) ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(end of broadcast)--- TIP 1: 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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, Thanks for your comments ! Right. As you pointed out, Sigres cannot recover if UPS fails. Therefore, I think currently Sigres cannot be used for mission critical applications. Sigres keeps data integrity *only when* UPS works. On the other hand, some users such as researchers of sensor networks would like store and analyze 10^6 Hz sensor data insertions in real-time, and obviously research usages are not mission critical. Thus Sigres may be accepted for researchers, but I have no prospect now since I have just started to distributing Sigres for research institutes in Japan. BTW, Joshua, could you please let me know or give me any pointers for the reason why fsync=off option exists on PostgreSQL although PostgreSQL developers does not allow sacrificing data integrity ? If the reason is famous and clear in the community, I am sorry for bothering you. -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(end of broadcast)--- TIP 1: 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: explain analyze is your friend
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
BTW, Joshua, could you please let me know or give me any pointers for the reason why fsync=off option exists on PostgreSQL although PostgreSQL A couple of reasons that I can think of. One would be data loads or restoring from backup. Another would be on data that you can afford to throw away. developers does not allow sacrificing data integrity ? If the reason is famous and clear in the community, I am sorry for bothering you. No bother at all! We invite all good ideas and I am glad to see more from our eastern community participate. Another option you might want to look at to further give yourself a boost in performance is full_page_writes. Joshua D. Drake -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(end of broadcast)--- TIP 1: 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: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Major topic. The full_page_writes option is already ignored. In Sigres, bgWriter does not call CreatCheckPoint periodically. Sigres calls CreateCheckPoint only when bgWriter calls ShutdownXLOG. Thus also from this point, durable UPS is required for Sigres. The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. Regards, -- Hideyuki Joshua D. Drake wrote: BTW, Joshua, could you please let me know or give me any pointers for the reason why fsync=off option exists on PostgreSQL although PostgreSQL A couple of reasons that I can think of. One would be data loads or restoring from backup. Another would be on data that you can afford to throw away. developers does not allow sacrificing data integrity ? If the reason is famous and clear in the community, I am sorry for bothering you. No bother at all! We invite all good ideas and I am glad to see more from our eastern community participate. Another option you might want to look at to further give yourself a boost in performance is full_page_writes. Joshua D. Drake -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(end of broadcast)--- TIP 1: 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: explain analyze is your friend -- 筑波大学大学院システム情報工学研究科 講師 川島英之 〒305-8573 つくば市天王台1-1-1 TEL: 029-853-5322 ＃2月より所属が変わりました ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
I think it would be great to have this kind of functionality built into postgres (optional and disabled by default of course) I use postgres mainly for its querying and concurrency features (a 10x increase in insert/update speed would be phenomenal) I know most people need 100% data integrity but as Hideyuki pointed out we all don't need 100%. On our project, when the power is out, we aren't receiving data anyways... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? On 2/10/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Gene Hart cell: 443-604-2679
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. However, Sigres modifies WAL which is the most important point of DBMS on stability. Although I myself could not find any bugs in Sigres, I am really afraid of it. It a bug exists on Sigres, it puts everyone to huge inconvenience... Therefore, before incorporating Sigres into PostgreSQL, the code must be checked, and the behaviors of Sigres must be checked carefully. Since PostgreSQL is a famous and wide spread software, I strongly want to avoid losing its great reputation. Unfortunately in Japan, I do not know any WAL hackers except for a friend of mine, and he is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am really happy. Best Regards, -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- 筑波大学大学院システム情報工学研究科 講師 川島英之 〒305-8573 つくば市天王台1-1-1 TEL: 029-853-5322 ＃2月より所属が変わりました ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Hideyuki Kawashima wrote: Joshua, I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. The best way is to create a patch against -head and submit that patch with a complete description of why, and what. If you have test cases that show the improvement all the better. I would suggest though if you are going to submit the patch that you take a look at how you could disable/enable the feature within the postgresql.conf via a guc. Sincerely, Joshua D. Drake However, Sigres modifies WAL which is the most important point of DBMS on stability. Although I myself could not find any bugs in Sigres, I am really afraid of it. It a bug exists on Sigres, it puts everyone to huge inconvenience... Therefore, before incorporating Sigres into PostgreSQL, the code must be checked, and the behaviors of Sigres must be checked carefully. Since PostgreSQL is a famous and wide spread software, I strongly want to avoid losing its great reputation. Unfortunately in Japan, I do not know any WAL hackers except for a friend of mine, and he is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am really happy. Best Regards, -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
[EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. But there is a not-inconsiderable risk that people will fail to read warnings, deploy databases in a way that leaves them exposed to total loss, and then lay blame at this community's feet. I'm sure you can understand why the community might resist that... -- output = reverse(moc.liamg @ enworbbc) http://linuxdatabases.info/info/internet.html Babbage's Rule: No man's cipher is worth looking at unless the inventor has himself solved a very difficult cipher (The Codebreakers by Kahn, 2nd ed, pg 765) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. You should consider submitting a patch for this against CVS HEAD. And actually, I'd think it a better idea to define a GUC variable and use that to control whether Sigres is active or not. At the more sophisticated end of the spectrum, you might set things up so that it could be activated/deactivated at runtime by a superuser. At the less sophisticated end, it might need to be configured in postgresql.conf... -- output = (cbbrowne @ acm.org) http://linuxfinances.info/info/ If you've done six impossible things this morning, why not round it off with breakfast at Milliways, the Restaurant at the End of the Universe? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: StrNCpy - strlcpy (not complete)
On Sat, Feb 10, 2007 at 09:21:04PM -0500, Bruce Momjian wrote: Woh. Peter, you realize one of the reasons we use StrNCpy as a macro is for performance. I don't see strlcpy as a macro. Are you going to change all call locations to strlcpy? If so, have you measured the performance impact? I think we had this discussion already. strncpy() copies N bytes, whereas strlcpy() copies only as many bytes as necessary. For short strings with larger buffers, strlcpy() wins. It's understood that in many cases in PostgreSQL, the expectation is for short strings, and it is not required for the later bytes to be '\0'. I assume Peter is only changing the provably good uses? :-) Cheers, mark Peter Eisentraut wrote: Log Message: --- StrNCpy - strlcpy (not complete) Modified Files: -- pgsql/src/backend/bootstrap: bootstrap.c (r1.229 - r1.230) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/bootstrap/bootstrap.c.diff?r1=1.229r2=1.230) pgsql/src/backend/libpq: crypt.c (r1.72 - r1.73) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/crypt.c.diff?r1=1.72r2=1.73) hba.c (r1.159 - r1.160) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/hba.c.diff?r1=1.159r2=1.160) ip.c (r1.39 - r1.40) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/ip.c.diff?r1=1.39r2=1.40) pgsql/src/backend/nodes: print.c (r1.83 - r1.84) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/print.c.diff?r1=1.83r2=1.84) pgsql/src/backend/postmaster: pgarch.c (r1.28 - r1.29) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/pgarch.c.diff?r1=1.28r2=1.29) postmaster.c (r1.518 - r1.519) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c.diff?r1=1.518r2=1.519) pgsql/src/backend/tcop: postgres.c (r1.521 - r1.522) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/postgres.c.diff?r1=1.521r2=1.522) pgsql/src/backend/utils/misc: guc-file.l (r1.46 - r1.47) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc-file.l.diff?r1=1.46r2=1.47) pgsql/src/bin/initdb: initdb.c (r1.131 - r1.132) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/initdb/initdb.c.diff?r1=1.131r2=1.132) pgsql/src/bin/pg_ctl: pg_ctl.c (r1.77 - r1.78) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_ctl/pg_ctl.c.diff?r1=1.77r2=1.78) pgsql/src/bin/pg_dump: pg_dumpall.c (r1.89 - r1.90) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dumpall.c.diff?r1=1.89r2=1.90) pgsql/src/bin/pg_resetxlog: pg_resetxlog.c (r1.56 - r1.57) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_resetxlog/pg_resetxlog.c.diff?r1=1.56r2=1.57) pgsql/src/interfaces/libpq: fe-auth.c (r1.122 - r1.123) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-auth.c.diff?r1=1.122r2=1.123) fe-connect.c (r1.342 - r1.343) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-connect.c.diff?r1=1.342r2=1.343) pgsql/src/timezone: pgtz.c (r1.49 - r1.50) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c.diff?r1=1.49r2=1.50) ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 1: 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] Acclerating INSERT/UPDATE using UPS
Gene, Thanks for your comments ! On our project, when the power is out, we aren't receiving data anyways... just my two cents. I am sorry, but I do not know how I can appropriately answer since I do not understand what you would like to ask me in this sentence. I understand that you have a project with UPS, and when the power failure occurs on UPS, suddenly your system cannot get data. Is my understanding right ? If so, then I agree with you and the reliability and durability of UPS is mandatory for your system. on a side note, would putting the wal on a tmpfs partition give you something similar? There are two differences between Sigres method and tmpfs with UPS method. 1: XLogWrite Even if you use tmpfs, your system executes XLogWrite which includes write(). Since write() is heavy system call, Sigres would be slightly faster than tmpfs method. 2: XLogWriteLock Even if you use tmpfs, your system lock/release XLogWriteLock while Sigres ignores. Although the frequency of XLogWriteLock accesses is lower than XLogInsertLock, ignoring XLogWriteLock improves the performance especially in many core environment. Best Regards, -- Hideyuki Gene wrote: I think it would be great to have this kind of functionality built into postgres (optional and disabled by default of course) I use postgres mainly for its querying and concurrency features (a 10x increase in insert/update speed would be phenomenal) I know most people need 100% data integrity but as Hideyuki pointed out we all don't need 100%. On our project, when the power is out, we aren't receiving data anyways... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? On 2/10/07, *Joshua D. Drake* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We ( www.commandprompt.com http://www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Gene Hart cell: 443-604-2679 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
On Feb 10, 2007, at 9:33 PM, Christopher Browne wrote: The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. Ask me about the time a year ago that I had a 24x7 database, with two power supplies connected to two UPSes on independent mains circuits, dropped dead because one UPS was overloaded (more than one server connected to it, apparently too much) and the other UPS was simply dead (undetected zombie UPS), when a catastrophic power failure killed both of the generator backed mains circuits. I wasn't pleased, but it happened nonetheless. A UPS is not a 100% guarantee of anything. They fail more often than they should. No amount of paranoia guarantees uptime. That said, I see plenty of use for loosening restrictions on databases where the contents do not matter and a little loss is acceptable. Cheers, J. Andrew Rogers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [COMMITTERS] pgsql: StrNCpy - strlcpy (not complete)
Bruce Momjian [EMAIL PROTECTED] writes: Woh. Peter, you realize one of the reasons we use StrNCpy as a macro is for performance. I don't see strlcpy as a macro. Huh? StrNCpy is a wrapper around strncpy(). Do you have reason to think that strncpy() is especially tightly implemented on most platforms? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher Browne wrote: [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. Correct, that is certainly a problem. However, I think the interesting opportunity here is in devices that don't typically loose power. A PDA for example. The PostgreSQL footprint is actually quite small, and PDAs are getting larger and larger in capacity. Heck, they even have 32GB SD now. In the near future I believe we can expect to see always on, mini laptops as well. From an deployable application perspective, this could be a big deal. We are already starting to see very large traction in the Win32 desktop app arena. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. But there is a not-inconsiderable risk that people will fail to read warnings, deploy databases in a way that leaves them exposed to total loss, and then lay blame at this community's feet. I'm sure you can understand why the community might resist that... I certainly can, but a feature left off by default and documented thoroughly can mitigate a lot of that. Heck if we really wanted to we could even make it a custom build; --with-lazy-wal ;) Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Under a Linux or FreeBSD environment sure... but what about where a good portion of the memory *is* flash? We have a customer right now where they have a device that has 64 megs of ram and 512 megs of flash. The system itself considers it total ram. They run over the 64 megs almost on boot. Allowing postgresql to be less aggressive on writes would help them quite a bit. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. Absolutely agreed there. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] XML export
On Sat, Feb 10, 2007 at 11:35:08AM -0800, Joshua D. Drake wrote: Peter Eisentraut wrote: The issue of XML export has been discussed a few times throughout history. Right now you've got the HTML output in psql. A few people have proposed real XML output formats in psql or elsewhere. I dug out some old code today that implements what SQL/XML has to say on the matter and fitted the code to work with the current XML support in the backend. Below are examples of what it can do. I'm thinking about hosting this on PgFoundry, but if the crowd thinks this should be somewhere else, short of the moon, let me know. Integrated, native XML support can only help PostgreSQL. IMO, I want this in core. +1 :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. Absolutely agreed there. Just to be clear: I believe our current assumptions can be stated as Postgres will not lose data if the kernel and disk drive do not lose data that they have acknowledged as being successfully fsync'd. This is independent of any questions about Postgres bugs or measures that we take to limit the impact of our bugs --- it's about what our extent of responsibility is. I think that Hideyuki-san is proposing a different contract for data integrity, and I want to understand what that contract is and why someone would want it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
