Re: [HACKERS] contrib/pg_buffercache
Andrew Dunstan wrote: ... is apparently broken for Windows and Cygwin. See for example http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lorisdt=2005-03-16%2001:55:33 Andrew, The attached patch seems to sort it for me, can you give it try on win32 and cygwin? cheers Mark --- pg_buffercache_pages.c.orig Sun Mar 13 04:36:24 2005 +++ pg_buffercache_pages.c Wed Mar 16 20:57:52 2005 @@ -17,6 +17,11 @@ #define NUM_BUFFERCACHE_PAGES_ELEM 6 +#ifdef WIN32 +extern DLLIMPORT BufferDesc*BufferDescriptors; +extern DLLIMPORT volatile uint32 InterruptHoldoffCount; +#endif + /* * Record structure holding the to be exposed cache data. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Erratic error message ERROR: column id_compte does not exist
Hi everybody, I've just run into a seemingly strange behaviour of postgresql and I'd like to know if it's normal or what. I've got a table operation in which there _isn't_ any column id_compte. So when i do : select id_compte from operation where not compta_g5; I have : ERROR: column id_compte does not exist Everything ok until now. But when i put this query inside another query, i don't have anymore the error message : select distinct id_operation from compte where id_compte in (select id_compte from operation where not compta_g5); No error message. The postgresql version is the debian one : 7.4.7-2 What do you think of that ? David -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests to fail
On Wed, 16 Mar 2005 01:00:21 -0500 (EST), Bruce Momjian pgman@candle.pha.pa.us wrote: I have applied a modified version of your patch, attached. I am so sorry, I sent untested patch again. Thank you very much for patience in fixing it. The patch looks perfectly fine and works under Solaris. Under win32 I am still struggling with build environment. In many directories link fails with undefined reference to `pg_snprintf' in other it fails with undefined reference to `_imp__libintl_sprintf'. In yet another directory it fails with both, like in src/interfaces/ecpg/pgtypeslib: dlltool --export-all --output-def pgtypes.def numeric.o datetime.o common.o dt_common.o timestamp.o interval.o pgstrcasecmp.o dllwrap -o libpgtypes.dll --dllname libpgtypes.dll --def pgtypes.def numeric.o datetime.o common.o dt_common.o timestamp.o interval.o pgstrcasecmp.o -L../../../../src/port -lm numeric.o(.text+0x19ea):numeric.c: undefined reference to `_imp__libintl_sprintf' datetime.o(.text+0x476):datetime.c: undefined reference to `pg_snprintf' common.o(.text+0x1cd):common.c: undefined reference to `pg_snprintf' common.o(.text+0x251):common.c: undefined reference to `pg_snprintf' dt_common.o(.text+0x538):dt_common.c: undefined reference to `_imp__libintl_sprintf' dt_common.o(.text+0x553):dt_common.c: undefined reference to `_imp__libintl_sprintf' dt_common.o(.text+0x597):dt_common.c: undefined reference to `_imp__libintl_sprintf' dt_common.o(.text+0x5d5):dt_common.c: undefined reference to `_imp__libintl_sprintf' dt_common.o(.text+0x628):dt_common.c: undefined reference to `_imp__libintl_sprintf' dt_common.o(.text+0x7e8):dt_common.c: more undefined references to `_imp__libintl_sprintf' follow c:\MinGW\bin\dllwrap.exe: c:\MinGW\bin\gcc exited with status 1 make: *** [libpgtypes.a] Error 1 Could someone with a better grasp of configure and win32 environment check it? Aparently no one regularily compiles source code under win32 during development cycle these days. Best regards, Nicolai ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Erratic error message ERROR: column id_compte does
David Pradier wrote: But when i put this query inside another query, i don't have anymore the error message : select distinct id_operation from compte where id_compte in (select id_compte from operation where not compta_g5); No error message. It looks like it's binding to the outer query - where you seem to have an id_compte. -- Richard Huxton Archonet Ltd ---(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] signed short fd
Ühel kenal päeval (esmaspäev, 14. märts 2005, 22:13-0500), kirjutas Bruce Momjian: Christopher Kings-Lynne wrote: I really don't intend to do that, and it does seem to happen a lot. I am the first to admit I lack tact, but often times I view the decisions made as rather arbitrary and lacking a larger perspective, but that is a rant I don't want to get right now. Perhaps it's your lack of a real name and complete anonyminity (hence invulnerablility) that gets to people... I actually met him _briefly_ at Linuxworld in Boston. He just said hi, then disappeared. :-) Was his real name 'pgsql' ? ;) -- Hannu Krosing [EMAIL PROTECTED] ---(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] Erratic error message ERROR: column id_compte does
Actually, the column id_compte is the primary key of the table compte. The column id_compte doesn't exist in the table operation. But i still don't understand why there is no error message, could you explain a little more ? David On Wed, Mar 16, 2005 at 11:49:28AM +, Richard Huxton wrote: David Pradier wrote: But when i put this query inside another query, i don't have anymore the error message : select distinct id_operation from compte where id_compte in (select id_compte from operation where not compta_g5); No error message. It looks like it's binding to the outer query - where you seem to have an id_compte. -- Richard Huxton Archonet Ltd ---(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 -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 ---(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] read-only database
Hi all, (B (BI've read TODO list in the 8.0.1, (Bthen I'm interested in the following topic. (B (B * Allow a warm standby system to also allow read-only queries (B (BDoes anyone have any plan to work on this? (B (BI think we need to extend the pg_database catalog to (Bhave a database state (read-only or writable), (Band also need to extend ALTER DATABASE command (Bto change the state. (B (BTo make a database read-only, ALTER DATABASE command may change (BMyDatabaseReadOnly flag (like XactReadOnly) (Band update the pg_database catalog. (BIf MyDatabaseReadOnly is true, check_xact_readonly() or (BExecCheckXactReadOnly() will prevent from updating the database. (B (BOtherwise, I think we can also prevent from updating the database (Baround acquiring the XLogInsert lock. (B (BAny ideas? or any suggestions? (B (B-- (BNAGAYASU Satoshi [EMAIL PROTECTED] (BOpenSource Development Center, (BNTT DATA Corp. http://www.nttdata.co.jp (B (B (B---(end of broadcast)--- (BTIP 2: you can get off all lists at once with the unregister command (B(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] caches lifetime with SQL vs PL/PGSQL procs
I've tested with 8.0.1 and get same results. --strk; On Wed, Mar 16, 2005 at 01:04:03PM +0100, [EMAIL PROTECTED] wrote: On postgresql-8.0.0 I've faced a *really* weird behavior. A simple query (single table - simple function call - no index), makes postgres process grow about as much as the memory size required to keep ALL rows in memory. The invoked procedure call doesn't leak. It's IMMUTABLE. Calls other procedures (not leaking). Now. One of the other procedures it calls is an 'SQL' one. Replacing it with a correponding 'PL/PGSQL' implementation drastically reduces memory occupation: SQL: 220Mb PL/PGSQL: 13Mb The function body is *really* simple: -- SQL CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS 'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1' LANGUAGE 'sql' IMMUTABLE STRICT; -- PL/PGSQL CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS ' BEGIN RETURN proj4text::text FROM spatial_ref_sys WHERE srid= $1; END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; Is this expected ? --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] caches lifetime with SQL vs PL/PGSQL procs
I've tried with 7.4.3 - *good* results with both SQL and PL/PGSQL (actually even less that best 8.0.1: 12Mb) I think this makes it a bug... --strk; On Wed, Mar 16, 2005 at 01:58:44PM +0100, [EMAIL PROTECTED] wrote: I've tested with 8.0.1 and get same results. --strk; On Wed, Mar 16, 2005 at 01:04:03PM +0100, [EMAIL PROTECTED] wrote: On postgresql-8.0.0 I've faced a *really* weird behavior. A simple query (single table - simple function call - no index), makes postgres process grow about as much as the memory size required to keep ALL rows in memory. The invoked procedure call doesn't leak. It's IMMUTABLE. Calls other procedures (not leaking). Now. One of the other procedures it calls is an 'SQL' one. Replacing it with a correponding 'PL/PGSQL' implementation drastically reduces memory occupation: SQL: 220Mb PL/PGSQL: 13Mb The function body is *really* simple: -- SQL CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS 'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1' LANGUAGE 'sql' IMMUTABLE STRICT; -- PL/PGSQL CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS ' BEGIN RETURN proj4text::text FROM spatial_ref_sys WHERE srid= $1; END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; Is this expected ? --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Erratic error message ERROR: column id_compte does
David Pradier wrote: Actually, the column id_compte is the primary key of the table compte. The column id_compte doesn't exist in the table operation. But i still don't understand why there is no error message, could you explain a little more ? I think it's intended for use in the WHERE clause: SELECT a.* FROM a WHERE a1 IN (SELECT b3 FROM b WHERE b2=a2) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PHP stuff
Uh, but that's what the BSD license allows --- relicensing as any other license, including commercial. The point remains that Chris, by himself, does not hold the copyright on the PG docs and therefore cannot assign it to anyone. ISTM the PHP guys are essentially saying that they will only take stuff that is clearly authored by the individual contributor. Which is a good safe position for them to take. They are trying to stay out of gray areas, and therefore they are not going to want docs that are just cut-and-pasted from community property. Argh. Well here comes a copy and paste PHP docs patch with a some 'rephrasings'... Since the PHP API to pgsql is identical to libpq's... Technically, that is still a derived work. I would say that The PostgreSQL Global Development Group or its representatives (I'm assuming Tom, Bruce, and/or Marc Fournier) just has to give something written, that says Christopher Kings-Lynne of your address, city, country, etc has the right to re-license or distribute under the PHP license the PostgreSQL documentation to the PHP group for the purpose of documenting the PostgreSQL object relational system, etc. etc. Once that is done, and the Representatives of The PostgreSQL Global Development Group have signed off, you should just be allowed to copy. In fact, I would say that is the best way to do it. Even copying and rephrasing is copyright infringement. The only way around it is the Phoenix technology method, where an unspoiled writer would take instructions from you and your read of the original documentation. Seriously, this should happen anyway. The PHP guys are not doing due diligence on what they accept. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Erratic error message ERROR: column id_compte does
Ok, understood. One can access the columns of the main query from the subquery, therefore in my own query the column id_compte is found, therefore there is no error message. Doesn't this count as a bug ? On Wed, Mar 16, 2005 at 02:24:48PM +, Richard Huxton wrote: David Pradier wrote: Actually, the column id_compte is the primary key of the table compte. The column id_compte doesn't exist in the table operation. But i still don't understand why there is no error message, could you explain a little more ? I think it's intended for use in the WHERE clause: SELECT a.* FROM a WHERE a1 IN (SELECT b3 FROM b WHERE b2=a2) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Erratic error message ERROR: column id_compte does
David Pradier wrote: Ok, understood. One can access the columns of the main query from the subquery, therefore in my own query the column id_compte is found, therefore there is no error message. Doesn't this count as a bug ? Tricky, you could have something like: SELECT a.* FROM a WHERE (a1,a2) IN (SELECT a1,b3 FROM b ...) If that's legal, then so is your query. On Wed, Mar 16, 2005 at 02:24:48PM +, Richard Huxton wrote: David Pradier wrote: Actually, the column id_compte is the primary key of the table compte. The column id_compte doesn't exist in the table operation. But i still don't understand why there is no error message, could you explain a little more ? I think it's intended for use in the WHERE clause: SELECT a.* FROM a WHERE a1 IN (SELECT b3 FROM b WHERE b2=a2) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PQexecParams
Will PQexecParams automatically escape bytea data as it goes in, or must one run it through PQescapeBytea first? Neither. The data does not need escaping (assuming you pass it as a parameter, of course.) Even binary data? ie. You could upload a binary string straight into PQexecParams with no octal conversion? Well, what it means you do not have to escape data at the protocol level you don't have to be concerned about formatting the query string. However the server still may still need to interpret the data for storage...meaning for the bytea type at least you have to do octal encoding (at least that's what I do, I'd like nothing better than to be corrected on this point). Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] snprintf causes regression tests
Nicolai Tufar wrote: On Wed, 16 Mar 2005 01:00:21 -0500 (EST), Bruce Momjian pgman@candle.pha.pa.us wrote: I have applied a modified version of your patch, attached. Here is a patch that fixes the %*$ case. FYI, I am going to pgindent snprintf.c to make it consistent so please us CVS for your next patch. I will work on your Win32 compile problem next. -- 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 Index: src/port/snprintf.c === RCS file: /cvsroot/pgsql/src/port/snprintf.c,v retrieving revision 1.20 diff -c -c -r1.20 snprintf.c *** src/port/snprintf.c 16 Mar 2005 06:00:58 - 1.20 --- src/port/snprintf.c 16 Mar 2005 14:59:00 - *** *** 467,481 fmtparptr[i]-charvalue = va_arg(args, int); break; case FMTLEN: ! if (i + 1 fmtpos fmtpar[i + 1].func != FMTWIDTH) ! fmtpar[i + 1].len = va_arg(args, int); /* For %*.*f, use the second arg */ ! if (i + 2 fmtpos fmtpar[i + 1].func == FMTWIDTH) ! fmtpar[i + 2].len = va_arg(args, int); break; case FMTWIDTH: if (i + 1 fmtpos) ! fmtpar[i + 1].maxwidth = fmtpar[i + 1].precision = va_arg(args, int); break; } --- 467,481 fmtparptr[i]-charvalue = va_arg(args, int); break; case FMTLEN: ! if (i + 1 fmtpos fmtparptr[i + 1]-func != FMTWIDTH) ! fmtparptr[i + 1]-len = va_arg(args, int); /* For %*.*f, use the second arg */ ! if (i + 2 fmtpos fmtparptr[i + 1]-func == FMTWIDTH) ! fmtparptr[i + 2]-len = va_arg(args, int); break; case FMTWIDTH: if (i + 1 fmtpos) ! fmtparptr[i + 1]-maxwidth = fmtparptr[i + 1]-precision = va_arg(args, int); break; } ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Erratic error message ERROR: column id_compte does
David Pradier [EMAIL PROTECTED] writes: One can access the columns of the main query from the subquery, therefore in my own query the column id_compte is found, therefore there is no error message. Doesn't this count as a bug ? No; it's required behavior per the SQL specification. The spec doesn't restrict the sub-query to reference outer columns in only some places; it can use them anywhere. (If you think that's weird, you should try putting the outer reference in an aggregate function ... it then counts as an aggregate of the outer query, not the inner. Also per spec.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] read-only database
Satoshi Nagayasu [EMAIL PROTECTED] writes: * Allow a warm standby system to also allow read-only queries Does anyone have any plan to work on this? I think we need to extend the pg_database catalog to have a database state (read-only or writable), and also need to extend ALTER DATABASE command to change the state. Uh, no, because changing that would by definition not be a read-only operation. Therefore there'd be no way to enter the read-only state, and definitely no way to get out of it again. Furthermore, the envisioned behavior is cluster-wide not per-database: the point is to not execute transactions and not generate WAL entries, and you don't get to be selective about that. (If it doesn't work like that, you couldn't use it for the intended purpose of examining the state of a hot-standby PITR backup that is actively tracking WAL logs shipped from a master. It'd also not be useful for looking at a corrupted cluster.) I'd view this as a postmaster state that propagates to backends. Probably you'd enable it by means of a postmaster option, and the only way to get out of it is to shut down and restart the postmaster without the option. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PQexecParams
Merlin Moncure [EMAIL PROTECTED] writes: Even binary data? ie. You could upload a binary string straight into PQexecParams with no octal conversion? Well, what it means you do not have to escape data at the protocol level you don't have to be concerned about formatting the query string. However the server still may still need to interpret the data for storage...meaning for the bytea type at least you have to do octal encoding (at least that's what I do, I'd like nothing better than to be corrected on this point). Not if you send the bytea parameter in binary format. If you send it in text format that's true. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] contrib/pgcrypto
Hello, How do you encrypt() decrypt() data of types INT4 or DATE? The PGCrypto methods encrypt() and decrypt() each take BYTEA as input: i.e., encrypt( data::bytea, key::bytea, type::text) decrypt( data::bytea, key::bytea, type::text) So how do you convert INT4 and DATE data into BYTEA so that they can be passed as input into the PGCrypto methods encrypt() decrypt()? Thank you, Michael Moran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PQexecParams
Merlin Moncure [EMAIL PROTECTED] writes: Even binary data? ie. You could upload a binary string straight into PQexecParams with no octal conversion? Well, what it means you do not have to escape data at the protocol level you don't have to be concerned about formatting the query string. However the server still may still need to interpret the data for storage...meaning for the bytea type at least you have to do octal encoding (at least that's what I do, I'd like nothing better than to be corrected on this point). Not if you send the bytea parameter in binary format. If you send it in text format that's true. Whoops! In fact, my ISAM driver does exactly this. However, it is still interpreting octal codes returned from the server. This is because liqpq's restriction to ask for the result in all text/all binary. However IIRC this is not a restriction of the protocol. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Erratic error message ERROR: column id_compte does
Ok then, if it's per spec, nothing to say. Thanks to everybody, Richard and Tom, for your time. PS : well yes, i think it is reasonably weird. I sure don't want to try and imagine the case you're proposing. Brain's too precious to burn. I'm confident in you to be assured it's weirder and weirdest :-) On Wed, Mar 16, 2005 at 10:17:17AM -0500, Tom Lane wrote: David Pradier [EMAIL PROTECTED] writes: One can access the columns of the main query from the subquery, therefore in my own query the column id_compte is found, therefore there is no error message. Doesn't this count as a bug ? No; it's required behavior per the SQL specification. The spec doesn't restrict the sub-query to reference outer columns in only some places; it can use them anywhere. (If you think that's weird, you should try putting the outer reference in an aggregate function ... it then counts as an aggregate of the outer query, not the inner. Also per spec.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] contrib/pg_buffercache
It fixes the build error on Windows - haven't tried because i don't have time, but I know it won't work on Cygwin, because WIN32 isn't (usually) defined on Cygwin - see previous almost endless discussions. cheers andrew Mark Kirkwood wrote: Andrew Dunstan wrote: ... is apparently broken for Windows and Cygwin. See for example http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lorisdt=2005-03-16%2001:55:33 Andrew, The attached patch seems to sort it for me, can you give it try on win32 and cygwin? cheers Mark --- pg_buffercache_pages.c.origSun Mar 13 04:36:24 2005 +++ pg_buffercache_pages.c Wed Mar 16 20:57:52 2005 @@ -17,6 +17,11 @@ #define NUM_BUFFERCACHE_PAGES_ELEM6 +#ifdef WIN32 +extern DLLIMPORT BufferDesc *BufferDescriptors; +extern DLLIMPORT volatile uint32 InterruptHoldoffCount; +#endif + /* * Record structure holding the to be exposed cache data. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] Savepoints and SPI
I have some test code that utilize SPI and does the following: 1. SPI_connect 2. set a savepoint (using BeginInternalSubTransaction) 3. execute a statement that contains a syntax error (within PG_TRY/PG_CATCH) 4. rollback to the savepoint (RollbackAndReleaseCurrentSubTransaction) 5. execute some other statement. it errors out in step 5 with SPI_ERROR_UNCONNECTED. It seems that step 3, when it encounters an error, automatically executes a SPI_finish. Is that the desired behavior? Should I assume that _all_ errors will act this way, i.e. should I assume that if I end up in a PG_CATCH() that my SPI has been disconnected always? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Savepoints and SPI
On Wed, Mar 16, 2005 at 07:35:57PM +0100, Thomas Hallgren wrote: I have some test code that utilize SPI and does the following: 1. SPI_connect 2. set a savepoint (using BeginInternalSubTransaction) 3. execute a statement that contains a syntax error (within PG_TRY/PG_CATCH) 4. rollback to the savepoint (RollbackAndReleaseCurrentSubTransaction) 5. execute some other statement. it errors out in step 5 with SPI_ERROR_UNCONNECTED. It seems that step 3, when it encounters an error, automatically executes a SPI_finish. Is that the desired behavior? I'd expect the code to close an SPI connection that is opened inside the savepoint, but if you open it before setting the savepoint then it should remain open. -- Alvaro Herrera ([EMAIL PROTECTED]) Endurecerse, pero jamás perder la ternura (E. Guevara) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [pgsql-hackers-win32] [HACKERS] snprintf causes regression
Nicolai Tufar wrote:
On Wed, 16 Mar 2005 01:00:21 -0500 (EST), Bruce Momjian
pgman@candle.pha.pa.us wrote:
I have applied a modified version of your patch, attached.
I am so sorry, I sent untested patch again. Thank you very
much for patience in fixing it. The patch looks perfectly
fine and works under Solaris.
Here is another patch that adds sprintf() support, and support for '+',
'h', and fixes '%*s' support.
Applied.
--
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
Index: src/bin/psql/command.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.141
diff -c -c -r1.141 command.c
*** src/bin/psql/command.c 11 Mar 2005 17:20:34 - 1.141
--- src/bin/psql/command.c 16 Mar 2005 21:17:50 -
***
*** 1574,1584
shellName = DEFAULT_SHELL;
sys = pg_malloc(strlen(shellName) + 16);
sprintf(sys,
/* See EDITOR handling comment for an explaination */
- #ifndef WIN32
exec %s, shellName);
#else
%s\%s\%s, SYSTEMQUOTE, shellName,
SYSTEMQUOTE);
#endif
result = system(sys);
--- 1574,1586
shellName = DEFAULT_SHELL;
sys = pg_malloc(strlen(shellName) + 16);
+ #ifndef WIN32
sprintf(sys,
/* See EDITOR handling comment for an explaination */
exec %s, shellName);
#else
+ sprintf(sys,
+ /* See EDITOR handling comment for an explaination */
%s\%s\%s, SYSTEMQUOTE, shellName,
SYSTEMQUOTE);
#endif
result = system(sys);
Index: src/include/port.h
===
RCS file: /cvsroot/pgsql/src/include/port.h,v
retrieving revision 1.72
diff -c -c -r1.72 port.h
*** src/include/port.h 11 Mar 2005 19:13:42 - 1.72
--- src/include/port.h 16 Mar 2005 21:17:50 -
***
*** 112,117
--- 112,120
extern int pg_snprintf(char *str, size_t count, const char *fmt,...)
/* This extension allows gcc to check the format string */
__attribute__((format(printf, 3, 4)));
+ extern int pg_sprintf(char *str, const char *fmt,...)
+ /* This extension allows gcc to check the format string */
+ __attribute__((format(printf, 2, 3)));
extern int pg_fprintf(FILE *stream, const char *fmt,...)
/* This extension allows gcc to check the format string */
__attribute__((format(printf, 2, 3)));
***
*** 127,137
--- 130,142
#ifdef __GNUC__
#define vsnprintf(...) pg_vsnprintf(__VA_ARGS__)
#define snprintf(...) pg_snprintf(__VA_ARGS__)
+ #define sprintf(...) pg_sprintf(__VA_ARGS__)
#define fprintf(...) pg_fprintf(__VA_ARGS__)
#define printf(...) pg_printf(__VA_ARGS__)
#else
#define vsnprintf pg_vsnprintf
#define snprintf pg_snprintf
+ #define sprintf pg_sprintf
#define fprintf pg_fprintf
#define printfpg_printf
#endif
Index: src/port/snprintf.c
===
RCS file: /cvsroot/pgsql/src/port/snprintf.c,v
retrieving revision 1.22
diff -c -c -r1.22 snprintf.c
*** src/port/snprintf.c 16 Mar 2005 15:12:18 - 1.22
--- src/port/snprintf.c 16 Mar 2005 21:17:51 -
***
*** 67,80
/*static char _id[] = $PostgreSQL: pgsql/src/port/snprintf.c,v 1.22
2005/03/16 15:12:18 momjian Exp $;*/
- int pg_snprintf(char *str, size_t count, const char
*fmt,...);
- int pg_vsnprintf(char *str, size_t count, const char *fmt,
va_list args);
- int pg_printf(const char *format,...);
static void dopr(char *buffer, const char *format, va_list args, char *end);
/* Prevent recursion */
#undefvsnprintf
#undefsnprintf
#undeffprintf
#undefprintf
--- 67,78
/*static char _id[] = $PostgreSQL: pgsql/src/port/snprintf.c,v 1.22
2005/03/16 15:12:18 momjian Exp $;*/
static void dopr(char *buffer, const char *format, va_list args, char *end);
/* Prevent recursion */
#undefvsnprintf
#undefsnprintf
+ #undefsprintf
#undeffprintf
#undefprintf
***
*** 104,121
}
int
pg_fprintf(FILE *stream, const char *fmt,...)
{
int len;
va_list args;
! char *buffer[4096];
char *p;
va_start(args, fmt);
! len = pg_vsnprintf((char
[HACKERS] WIN1252 patch broke my database
You can't just randomly rearrange the pg_enc enum without forcing an initdb, because the numeric values of the encodings appear in system catalogs (eg pg_conversion). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WIN1252 patch broke my database
Tom Lane wrote: You can't just randomly rearrange the pg_enc enum without forcing an initdb, because the numeric values of the encodings appear in system catalogs (eg pg_conversion). Oh, those numbers appear in the catalogs? I didn't relealize that. I will force an initdb. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WIN1252 patch broke my database
Tom Lane wrote: You can't just randomly rearrange the pg_enc enum without forcing an initdb, because the numeric values of the encodings appear in system catalogs (eg pg_conversion). Oh, those numbers appear in the catalogs? I didn't relealize that. I will force an initdb. Doesn't that also force the end-user to initdb with an upgrade? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WIN1252 patch broke my database
Mark Woodward wrote: Tom Lane wrote: You can't just randomly rearrange the pg_enc enum without forcing an initdb, because the numeric values of the encodings appear in system catalogs (eg pg_conversion). Oh, those numbers appear in the catalogs? I didn't relealize that. I will force an initdb. Doesn't that also force the end-user to initdb with an upgrade? Yes, 8.1 will require a dump/reload for upgrade. I think we decided that was going to be necessary. That has aleady happened for 8.1: date: 2005/02/28 03:45:21; author: neilc; state: Exp; lines: +2 -2 Implement max() and min() aggregates for array types. Patch from Koju Iijima, reviewed by Neil Conway. Catalog version number bumped, regression tests updated. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WIN1252 patch broke my database
Bruce Momjian wrote: Yes, 8.1 will require a dump/reload for upgrade. I think we decided that was going to be necessary. That has aleady happened for 8.1: date: 2005/02/28 03:45:21; author: neilc; state: Exp; lines: +2 -2 Implement max() and min() aggregates for array types. Patch from Koju Iijima, reviewed by Neil Conway. Catalog version number bumped, regression tests updated. Yes, as well as 4 other patches that have bumped the catversion number. I think we are well past the point at which an 8.1 without an initdb would be a plausible option (barring the materialization of a working pg_upgrade tool, of course). -Neil ---(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] read-only database
Tom Lane wrote: (B Uh, no, because changing that would by definition not be a read-only (B operation. Therefore there'd be no way to enter the read-only state, (B and definitely no way to get out of it again. (B (BI saw Oracle's reference manual, and found ALTER DATABASE OPEN READ ONLY command (Bto make a stand-by database. (B (BIn Oracle, when the user execute the command, the database goes read-only mode. (BIs this a bad idea? (B (BI guess some users need per-database read-only state. (BDon't we need to have both read-only and writable databases in single cluster? (B (BOf course, the super-user can change the database state even in read-only. (B (B Furthermore, the (B envisioned behavior is cluster-wide not per-database: the point is (B to not execute transactions and not generate WAL entries, and you (B don't get to be selective about that. (If it doesn't work like that, (B you couldn't use it for the intended purpose of examining the state (B of a hot-standby PITR backup that is actively tracking WAL logs (B shipped from a master. It'd also not be useful for looking at (B a corrupted cluster.) (B (B I'd view this as a postmaster state that propagates to backends. (B Probably you'd enable it by means of a postmaster option, and the (B only way to get out of it is to shut down and restart the postmaster (B without the option. (B (BI agree this is a reasonable way to make cluster-wide read-only state. (B (B-- (BNAGAYASU Satoshi [EMAIL PROTECTED] (BOpenSource Development Center, (BNTT DATA Corp. http://www.nttdata.co.jp/ (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] contrib/pg_buffercache
Andrew Dunstan wrote: It fixes the build error on Windows - haven't tried because i don't have time, but I know it won't work on Cygwin, because WIN32 isn't (usually) defined on Cygwin - see previous almost endless discussions. Yes - I recall that discussion a while ago. This patch should sort the issue. One question, should I be using defined(__MINGW32__) as opposed to defined(WIN32)? I figured I didn't as in this case it is not necessary to distinguish between native and cygwin. regards Mark *** pg_buffercache_pages.h.orig Thu Mar 17 10:12:20 2005 --- pg_buffercache_pages.h Thu Mar 17 13:44:45 2005 *** *** 15,18 --- 15,24 extern Datum pg_buffercache_pages(PG_FUNCTION_ARGS); + /* A little hackery for Windows and Cygwin */ + #if defined (WIN32) || defined (__CYGWIN__) + extern DLLIMPORT BufferDesc *BufferDescriptors; + extern DLLIMPORT volatile uint32 InterruptHoldoffCount; + #endif + #endif /* PG_BUFFERCACHE_PAGES_H */ ---(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] contrib/pg_buffercache
Mark Kirkwood wrote: Andrew Dunstan wrote: It fixes the build error on Windows - haven't tried because i don't have time, but I know it won't work on Cygwin, because WIN32 isn't (usually) defined on Cygwin - see previous almost endless discussions. Yes - I recall that discussion a while ago. This patch should sort the issue. One question, should I be using defined(__MINGW32__) as opposed to defined(WIN32)? I figured I didn't as in this case it is not necessary to distinguish between native and cygwin. You figured correctly. cheers andrew ---(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] contrib/pg_buffercache
Andrew Dunstan wrote: Mark Kirkwood wrote: Andrew Dunstan wrote: It fixes the build error on Windows - haven't tried because i don't have time, but I know it won't work on Cygwin, because WIN32 isn't (usually) defined on Cygwin - see previous almost endless discussions. Yes - I recall that discussion a while ago. This patch should sort the issue. One question, should I be using defined(__MINGW32__) as opposed to defined(WIN32)? I figured I didn't as in this case it is not necessary to distinguish between native and cygwin. You figured correctly. Oh, and thanks to your efforts with the automated build system this gets picked up now instead of lurking till after release - great work! cheers Mark ---(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] read-only database
Satoshi Nagayasu [EMAIL PROTECTED] writes: Tom Lane wrote: Uh, no, because changing that would by definition not be a read-only operation. Therefore there'd be no way to enter the read-only state, and definitely no way to get out of it again. I saw Oracle's reference manual, and found ALTER DATABASE OPEN READ ONLY command to make a stand-by database. Perhaps, but that's *not* what the TODO item is about. 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] read-only database
Tom Lane wrote: (BI saw Oracle's reference manual, and found ALTER DATABASE OPEN READ ONLY (Bcommand (Bto make a stand-by database. (B (B Perhaps, but that's *not* what the TODO item is about. (B (BI see. (B (BThanks for comments. (B-- (BNAGAYASU Satoshi [EMAIL PROTECTED] (BOpenSource Development Center, (BNTT DATA Corp. http://www.nttdata.co.jp/ (B (B---(end of broadcast)--- (BTIP 3: if posting/reading through Usenet, please send an appropriate (B subscribe-nomail command to [EMAIL PROTECTED] so that your (B message can get through to the mailing list cleanly
[HACKERS] Changing the default wal_sync_method to open_sync for Win32?
Magnus Hagander wrote: I'd like to see this one also considered for 8.0.x, though I'd certainly like to see some more testing as well. Perhaps it's suitable for the 8.0.x with extended testing that is planned for the ARC replacement code? It does make a huge difference on win32. While we definitly don't want to risk data, a 60% speedup in write intensive apps is a *lot*. While this patch has been applied to CVS HEAD, there are still two open issues: 1. Should it be the default wal_sync_method for Win32? Right now we do: #if defined(OPEN_DATASYNC_FLAG) #define DEFAULT_SYNC_METHOD_STRopen_datasync #define DEFAULT_SYNC_METHODSYNC_METHOD_OPEN #define DEFAULT_SYNC_FLAGBIT OPEN_DATASYNC_FLAG #else #if defined(HAVE_FDATASYNC) #define DEFAULT_SYNC_METHOD_STR fdatasync #define DEFAULT_SYNC_METHOD SYNC_METHOD_FDATASYNC #define DEFAULT_SYNC_FLAGBIT 0 #else #define DEFAULT_SYNC_METHOD_STR fsync #define DEFAULT_SYNC_METHOD SYNC_METHOD_FSYNC #define DEFAULT_SYNC_FLAGBIT 0 #endif Basically we do open_datasync - fdatasync - fsync. This is empirically what we found to be fastest on most operating systems, and we default to the first one that exists on the operating system. Notice we never default to open_sync. However, on Win32, Magnus got a 60% speedup by using open_sync, implemented using FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I think we should default to open_sync on Win32. The attached patch implements this. 2. Another question is what to do with 8.0.X? Do we backpatch this for Win32 performance? Can we test it enough to know it will work well? 8.0.2 is going to have a more rigorous testing cycle because of the buffer manager changes. --- //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: den 27 februari 2005 01:54 To: Magnus Hagander Cc: Tom Lane; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]; Merlin Moncure Subject: Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question Patch applied. Thanks. I assume this is not approprate for 8.0.X. --- Magnus Hagander wrote: Magnus prepared a trivial patch which added the O_SYNC flag for windows and mapped it to FILE_FLAG_WRITE_THROUGH in win32_open.c. Attached is this trivial patch. As Merlin says, it needs some more reliability testing. But the numbers are at least reasonable - it *seems* like it's doing the right thing (as long as you turn off write cache). And it's certainly a significant performance increase - it brings the speed almost up to the same as linux. //Magnus Content-Description: o_sync.patch [ Attachment, skipping... ] ---(end of broadcast)--- TIP 8: explain analyze is your friend -- 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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go 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 Index: src/backend/access/transam/xlog.c === RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.181 diff -c -c -r1.181 xlog.c *** src/backend/access/transam/xlog.c 12 Feb 2005 23:53:37 - 1.181 --- src/backend/access/transam/xlog.c 17 Mar 2005 04:07:44 - *** *** 69,78 #endif #endif #if defined(OPEN_DATASYNC_FLAG) #define DEFAULT_SYNC_METHOD_STRopen_datasync #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN ! #define DEFAULT_SYNC_FLAGBIT OPEN_DATASYNC_FLAG #else #if defined(HAVE_FDATASYNC) #define DEFAULT_SYNC_METHOD_STR fdatasync --- 69,83 #endif #endif + #if defined(WIN32)/* Fastest on Win32 using FILE_FLAG_WRITE_THROUGH */ + #define DEFAULT_SYNC_METHOD_STRopen_sync + #define
Re: [HACKERS] Changing the default wal_sync_method to open_sync for Win32?
Bruce Momjian pgman@candle.pha.pa.us writes: Notice we never default to open_sync. However, on Win32, Magnus got a 60% speedup by using open_sync, implemented using FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I think we should default to open_sync on Win32. The attached patch implements this. ... and breaks open_datasync for all other platforms ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Michael Paesold wrote: Magnus Hagander wrote: Magnus Hagander wrote: Magnus prepared a trivial patch which added the O_SYNC flag for windows and mapped it to FILE_FLAG_WRITE_THROUGH in win32_open.c. [snip] Michael Paesold wrote: The original patch did not have any documentation. Have you added some? Since this has to be configured in GUC (wal_sync_method), the implications should be documented somewhere, no? The patch just implements behaviour that was already documented (for unix) on a new platform (win32). The documentation in general appears to have very little information on what to pick there, though ;-) Reading your mails about the pull-the-plug tests, I see that at least with write caching enabled, fsync is more secure on win32 than open_sync. I.e. one should disable write caching for use with open_sync. Also open_sync seems to perform much better. All that information would be nice to have in the docs. Michael, I am not sure why you come to the conclusion that open_sync requires turning off the disk write cache. I saw nothing to indicate that in the thread: http://archives.postgresql.org/pgsql-hackers-win32/2005-02/msg00035.php I read the following: * Win32, with fsync, write-cache disabled: no data corruption * Win32, with fsync, write-cache enabled: no data corruption * Win32, with osync, write cache disabled: no data corruption * Win32, with osync, write cache enabled: no data corruption. Once I got: 2005-02-24 12:19:54 LOG: could not open file C:/Program Files/PostgreSQL/8.0/data/pg_xlog/00010010 (log file 0, segment 16): No such file or directory but the data in the database was consistent. It disturbs me that you couldn't produce data corruption in the cases where it theoretically should occur. Seems like this is an indication that your test was insufficiently severe, or that there is something going on we don't understand. The Windows driver knows abotu the write cache, and at least fsync() pushes through the write cache even if it's there. This seems to indicate taht O_SYNC at least partiallyi does this as well. This is why there is no performance difference at all on fsync() with write cache on or off. I don't know if this is true for all IDE disks. COuld be that my disk is particularly well-behaved. This indicated to me that open_sync did not require any additional changes than our current fsync. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Changing the default wal_sync_method to open_sync for
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Notice we never default to open_sync. However, on Win32, Magnus got a 60% speedup by using open_sync, implemented using FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I think we should default to open_sync on Win32. The attached patch implements this. ... and breaks open_datasync for all other platforms ... Oh, fixed. -- 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 Index: src/backend/access/transam/xlog.c === RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.181 diff -c -c -r1.181 xlog.c *** src/backend/access/transam/xlog.c 12 Feb 2005 23:53:37 - 1.181 --- src/backend/access/transam/xlog.c 17 Mar 2005 04:31:32 - *** *** 69,74 --- 69,79 #endif #endif + #if defined(WIN32)/* Fastest on Win32 using FILE_FLAG_WRITE_THROUGH */ + #define DEFAULT_SYNC_METHOD_STRopen_sync + #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN + #define DEFAULT_SYNC_FLAGBIT OPEN_SYNC_FLAG + #else #if defined(OPEN_DATASYNC_FLAG) #define DEFAULT_SYNC_METHOD_STRopen_datasync #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN *** *** 84,89 --- 89,95 #define DEFAULT_SYNC_FLAGBIT0 #endif #endif + #endif /* User-settable parameters */ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] invalidating cached plans
Neil Conway wrote: Do we want to share plans between call sites? After thinking about this a little more, I think the answer is no -- it doesn't really buy us much, and introduces some extra complications (e.g. resource management). BTW, it's quite annoying that the planner scribbles on its input. I've got half a mind to fix this before doing the rest of the cache invalidation work. That might be quite a large project, however... -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] invalidating cached plans
Neil Conway [EMAIL PROTECTED] writes: BTW, it's quite annoying that the planner scribbles on its input. Yeah ... it would be good to fix that ... 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] Changing the default wal_sync_method to open_sync for
On Wed, 16 Mar 2005, Bruce Momjian wrote: Magnus Hagander wrote: I'd like to see this one also considered for 8.0.x, though I'd certainly like to see some more testing as well. Perhaps it's suitable for the 8.0.x with extended testing that is planned for the ARC replacement code? It does make a huge difference on win32. While we definitly don't want to risk data, a 60% speedup in write intensive apps is a *lot*. Notice we never default to open_sync. However, on Win32, Magnus got a 60% speedup by using open_sync, implemented using FILE_FLAG_WRITE_THROUGH. Now, because this the fastest on Win32, I think we should default to open_sync on Win32. The attached patch implements this. Considering how stable an Operating System Windows *isn't*, I think the first thing Magnus states very much goes against making this the default: While we definitely don't want to risk data... ... Setting something like this that increases the risk to data should never be 'the default behaviour', but a conscious decision on the part of the administrator of the individual system ... and even then, with a good skull-n-cross bones warning around it so that they understand the risks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] type unknown - how important is it?
Dave Cramer wrote: Shachar, I think with type oid 705 (unknown) it's safe to treat it as text. Certainly better than punting. Question is what DBTYPE to report it as. Options are DBTYPE_WSTR (UTF-16 string, which means the input string must be a valid UTF-8 string), DBTYPE_STR (just dump it as I get it, and hope that client doesn't barf on the UTF-8 encoding), DBTYPE_BYTES (it's an array of bytes, just let the client figure out what to do with it. No promises on my part). I don't know type 705 well enough to decide which would work best. If it's guaranteed to be a validly encoded text string, then I'll just put it in as DBTYPE_WSTR, and get it done with. On another note are you aware of any issues with transactions? Specifically with using the dated autocommit mode ? I'm not sure what dated autocommit is. What are the issues you are seeing? Dave Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Exception handiling
Hi, i want to add support for oracle exception's like no_data in plpgsql, can anybody guide me on this. Regards, Asif Ali. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] type unknown - how important is it?
Shachar Shemesh wrote: Dave Cramer wrote: Shachar, I think with type oid 705 (unknown) it's safe to treat it as text. Certainly better than punting. Question is what DBTYPE to report it as. Options are DBTYPE_WSTR (UTF-16 string, which means the input string must be a valid UTF-8 string), DBTYPE_STR (just dump it as I get it, and hope that client doesn't barf on the UTF-8 encoding), DBTYPE_BYTES (it's an array of bytes, just let the client figure out what to do with it. No promises on my part). I don't know type 705 well enough to decide which would work best. If it's guaranteed to be a validly encoded text string, then I'll just put it in as DBTYPE_WSTR, and get it done with. Oh, if you want to look it up yourself: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbdata_types.asp Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Bruce Momjian wrote: Michael Paesold wrote: Magnus Hagander wrote: [snip] Michael, I am not sure why you come to the conclusion that open_sync requires turning off the disk write cache. I saw nothing to indicate that in the thread: I was just seeing his error message below... http://archives.postgresql.org/pgsql-hackers-win32/2005-02/msg00035.php I read the following: * Win32, with fsync, write-cache disabled: no data corruption * Win32, with fsync, write-cache enabled: no data corruption * Win32, with osync, write cache disabled: no data corruption * Win32, with osync, write cache enabled: no data corruption. Once I got: 2005-02-24 12:19:54 LOG: could not open file C:/Program Files/PostgreSQL/8.0/data/pg_xlog/00010010 (log file 0, segment 16): No such file or directory but the data in the database was consistent. A missing xlog file does not strike me as very save. Perhaps someone can explain what happened, but I would not feel good about this. Again this note (from Tom Lane) in combination with the above error would tell me, we don't fully understand the risk here. It disturbs me that you couldn't produce data corruption in the cases where it theoretically should occur. Seems like this is an indication that your test was insufficiently severe, or that there is something going on we don't understand. The Windows driver knows abotu the write cache, and at least fsync() pushes through the write cache even if it's there. This seems to indicate taht O_SYNC at least partiallyi does this as well. This is why there is no performance difference at all on fsync() with write cache on or off. I don't know if this is true for all IDE disks. COuld be that my disk is particularly well-behaved. This indicated to me that open_sync did not require any additional changes than our current fsync. We both based our understanding on the same evidence. It seems we just have a different level of paranoia. ;-) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
