Re: [HACKERS] How to submit Tsearch V2 ?
On Fri, 4 Jul 2003, Christopher Kings-Lynne wrote: we still have no r/w access to CVS, so I'm asking authoritative developers to grab archive http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch-v2-stable.tar.gz and submit it to CVS for 7.4 beta. Out of interest - is it completely backwards compatible? unfortunately, no Then surely fully replacing tsearch will cause badness? I see, so are you suggesting separate dir for tsearch v2 ? Chris Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 feature freeze is here
Folks, You might like to mention that (as far as I can tell) ECPG is now safe for pthreads on Linux and FreeBSD. The recursive mutex locks are removed, so even platforms that implement the earlier version of pthreads ought to work as well, once configure supports them (anyone care to actually test this assertion?) I don't quite understand this. This doesn't mean that *postgresql* is threaded, does it? -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Mirro updates
Guys, On Fri, Jun 13, 2003 at 10:18:06PM +0100, Dave Page wrote: Yes, this is expected. Currently the website is not being mirrored due to it's dependency on a backend database. We are considering different ways of overcoming this problem in a useful way. Bricolage? i.e. generation of static HTML from a central site that pushes out to the mirrors. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] How to submit Tsearch V2 ?
Out of interest - is it completely backwards compatible? unfortunately, no Then surely fully replacing tsearch will cause badness? I see, so are you suggesting separate dir for tsearch v2 ? Well, we use tserach 1 extensively. I want to be able to upgrade to 7.4 and have nothing break! If things will break, then you need to have a tsearch2 dir instead. Chris ---(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] 7.4 feature freeze is here
On Fri, 4 Jul 2003 04:03 pm, Josh Berkus wrote: I don't quite understand this. This doesn't mean that *postgresql* is threaded, does it? I was just referring to the client interfaces ECPG and libpq. AFAIK the back-end is not threaded (and I'm beginning to understand why not). So my app starts multiple threads of execution through the ECPG libs... the ECPG libs (and libpq) start multiple sockets to the back-end - one for each thread. No changes to the back-end. That's my understanding - Lee did most of the work, so maybe he can confirm that. Regards, Philip Yarra. ---(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] Error messages --- now that we've got it, do you like
Oops, just remembered I was going to look at the new style error codes to answer one of your emails before. Just not had the time. On Thu, 3 Jul 2003, Tom Lane wrote: First fruits of all that work on error message rejiggering ... regression=# \set VERBOSE terse regression=# select 1!! ; ERROR: operator does not exist: integer !! I can see why 'terse' contains the least amount of information and that generally it should not therefore contain information not in the next higher level but I would have thought 'terse' would include the error number. Even _just_ the error number. I presume this setting is completely different from the one to determine the verbosity in the server log. In the server logs I would think it better to be able to include the error code in the error line without having any other detail lines. In fact in the server log would it not be the case that the LOCATION detail came before the HINT detail in the verbosity stakes, or is it viewed as being closer to a debug setting and so requires more verbosity? regression=# \set VERBOSE default regression=# select 1!! ; ERROR: operator does not exist: integer !! HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. regression=# \set VERBOSE verbose regression=# select 1!! ; ERROR: 42883: operator does not exist: integer !! HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. LOCATION: op_error, parse_oper.c:691 regression=# select 'z' 'q'; ERROR: 42725: operator is not unique: unknown unknown HINT: Unable to choose a best candidate operator. You may need to add explicit typecasts. LOCATION: op_error, parse_oper.c:684 Before we go too much further, does this look sane to people? Any adjustments you want to make around the edges? (BTW, if you're wondering where the 42xxx error codes came from, I borrowed them from DB2. The SQL99 spec seems happy to lump all sorts of conditions under 42000 syntax error or access violation ...) Looks good. Error codes are always handy to have and the extra details are just the ticket, I especially like the hint. -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] cvs version compile error
Hi, I'm trying to compile pgsql-7.4devl on Solaris8, but got the error below: 8- numeric.c: In function `PGTYPESnumeric_cmp': numeric.c:1308: `INT_MAX' undeclared (first use in this function) numeric.c:1308: (Each undeclared identifier is reported only once numeric.c:1308: for each function it appears in.) numeric.c:1310: warning: control reaches end of non-void function numeric.c: In function `PGTYPESnumeric_to_int': numeric.c:1452: `INT_MAX' undeclared (first use in this function) numeric.c: In function `PGTYPESnumeric_to_long': numeric.c:1474: `LONG_MAX' undeclared (first use in this function) make[4]: *** [numeric.o] Error 1 make[4]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src/interfaces/ecpg/pgtypeslib' make[3]: *** [all] Error 2 make[3]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src/interfaces/ecpg' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src' make: *** [all] Error 2 $ gcc --version 2.95.3 --8- the source file are just updated from CVS. ISTM a little bug on Solaris8 platform. Thanks and Regards Laser ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Mirro updates
It's rumoured that Josh Berkus once said: Guys, On Fri, Jun 13, 2003 at 10:18:06PM +0100, Dave Page wrote: Yes, this is expected. Currently the website is not being mirrored due to it's dependency on a backend database. We are considering different ways of overcoming this problem in a useful way. Bricolage? i.e. generation of static HTML from a central site that pushes out to the mirrors. Well I'm looking at Bricolage for other reasons, but basically that's what we're doing on the main site, just with our own code. Regards, Dave ---(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] How to submit Tsearch V2 ?
I think with upgrade X.Y to X.Y+1 version of pgsql you need dump/restore all
databases, isn't it?
So, for upgrading tsearch to V2 you need to do following:
1 rename columns or typecast for type txtidx to tsvector, query_txt to tsquery
2 rename function txt2txtidx to to_tsvector, ( may be to strip(to_tsvector()),
now tsvector has information for ranking ), trigger 'tsearch' isn't changed
3 operator class (in create index command) gist_txtidx_ops to gist_tsvector_ops
4 In queries: operator ## to @@ to_tsquery() and remove mquery_txt typecast
5 If your locale of pgsql differ from 'C', execute before first query
select set_curcfg('default');
That's all. So upgrade from v1 to v2 isn't difficult.
Christopher Kings-Lynne wrote:
Out of interest - is it completely backwards compatible?
unfortunately, no
Then surely fully replacing tsearch will cause badness?
I see, so are you suggesting separate dir for tsearch v2 ?
Well, we use tserach 1 extensively. I want to be able to upgrade to 7.4 and
have nothing break!
If things will break, then you need to have a tsearch2 dir instead.
Chris
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(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] How to submit Tsearch V2 ?
Chris,
what if we write, that old tsearch (v1) is deprecated in 7.4 and will be
obsoleted in 7.5 and put tsearch-v1.tar.gz on our site.
Oleg
On Fri, 4 Jul 2003, Teodor Sigaev wrote:
I think with upgrade X.Y to X.Y+1 version of pgsql you need dump/restore all
databases, isn't it?
So, for upgrading tsearch to V2 you need to do following:
1 rename columns or typecast for type txtidx to tsvector, query_txt to tsquery
2 rename function txt2txtidx to to_tsvector, ( may be to strip(to_tsvector()),
now tsvector has information for ranking ), trigger 'tsearch' isn't changed
3 operator class (in create index command) gist_txtidx_ops to gist_tsvector_ops
4 In queries: operator ## to @@ to_tsquery() and remove mquery_txt typecast
5 If your locale of pgsql differ from 'C', execute before first query
select set_curcfg('default');
That's all. So upgrade from v1 to v2 isn't difficult.
Christopher Kings-Lynne wrote:
Out of interest - is it completely backwards compatible?
unfortunately, no
Then surely fully replacing tsearch will cause badness?
I see, so are you suggesting separate dir for tsearch v2 ?
Well, we use tserach 1 extensively. I want to be able to upgrade to 7.4 and
have nothing break!
If things will break, then you need to have a tsearch2 dir instead.
Chris
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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] Error messages --- now that we've got it, do you like it?
Nigel J. Andrews [EMAIL PROTECTED] writes: ... I would have thought 'terse' would include the error number. Even _just_ the error number. Well, we can talk about that, but remember this is psql's take on what to display, not anyone else's. IMHO it should be designed for human readability --- programs aren't going to be looking at its output, only people. Error codes are being added for the use of programs, and programs will have other APIs that they use to get at 'em. In my mind, making people look at error codes in place of readable messages went out with the punch card. I presume this setting is completely different from the one to determine the verbosity in the server log. Right, this is psql. The current code offers exactly the same three verboseness levels for server log entries, but there's no hard and fast reason for them to be the same. In the server logs I would think it better to be able to include the error code in the error line without having any other detail lines. I think you vastly overestimate the usefulness of the bare error code. We are *not* planning to make one error code per distinct error message; for example, there'll be one code for undefined function or operator regardless of the context the problem occurs in. I'm not even really convinced that I should have bothered with separate error codes for the two examples I gave (unknown versus non-unique operator). Really the error codes are designed to let programs have some idea of whether they can recover from a failure --- for example, that's why SQL99 doesn't have a problem with lumping every variety of syntax error under one code, because it's unlikely a program will be able to repair a syntax error in a query it's issued. AFAICS people will always want to look at the primary error message. However, I wouldn't object to redesigning the log verbosity mechanism so that my ideas about this aren't imposed on other people. Maybe allow settings along the line of log_error_fields = 'code,message,details' where you pick out the fields you want? In fact in the server log would it not be the case that the LOCATION detail came before the HINT detail in the verbosity stakes, or is it viewed as being closer to a debug setting and so requires more verbosity? In most cases LOCATION should be effectively a debug detail. We'd ask for it in bug reports but I can't imagine non-developers having much use for it otherwise. CONTEXT, which is the user-land aspect of location, that is the user function call stack, *is* included in the default set of fields to display. I didn't give an example of it, but here's one: regression=# create function fooey(real) returns real as ' regression'# begin regression'# return 1.0 / $1; regression'# end' language plpgsql; CREATE FUNCTION regression=# select fooey(0); ERROR: division by zero CONTEXT: PL/pgSQL function fooey line 2 at return The verbose version of this adds LOCATION: float84div, float.c:1840 but I can't see that being wanted in the default field set. 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] How to submit Tsearch V2 ?
So, for upgrading tsearch to V2 you need to do following:
1 rename columns or typecast for type txtidx to tsvector, query_txt to tsquery
2 rename function txt2txtidx to to_tsvector, ( may be to strip(to_tsvector()),
now tsvector has information for ranking ), trigger 'tsearch' isn't changed
3 operator class (in create index command) gist_txtidx_ops to gist_tsvector_ops
4 In queries: operator ## to @@ to_tsquery() and remove mquery_txt typecast
5 If your locale of pgsql differ from 'C', execute before first query
select set_curcfg('default');
That's all. So upgrade from v1 to v2 isn't difficult.
And you expect thousands of users to do this? I don't know about that...
I can barely handle it myself, and I was looking forward to a Postgres
upgrade for a change where I didn't have to edit the dump :(
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] cvs version compile error
Weiping He [EMAIL PROTECTED] writes: I'm trying to compile pgsql-7.4devl on Solaris8, but got the error below: I think Bruce already fixed this. How old is your CVS pull? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] How to submit Tsearch V2 ?
Well, I doubt what is right way.
tsearch V2 use new names, new function new feature and hasn't compatibility with
old one.
1 tsearch2 and tsearch directory in contrib. tsearch marked as deprecated and
will be removed for 7.5
2 V2 places in contrib as tsearch, old tsearch will be at our site.
It seems to me that second way is correcter way. Two version of tsearch is
surplus...
What do people think about it?
Christopher Kings-Lynne wrote:
So, for upgrading tsearch to V2 you need to do following:
1 rename columns or typecast for type txtidx to tsvector, query_txt to tsquery
2 rename function txt2txtidx to to_tsvector, ( may be to strip(to_tsvector()),
now tsvector has information for ranking ), trigger 'tsearch' isn't changed
3 operator class (in create index command) gist_txtidx_ops to gist_tsvector_ops
4 In queries: operator ## to @@ to_tsquery() and remove mquery_txt typecast
5 If your locale of pgsql differ from 'C', execute before first query
select set_curcfg('default');
That's all. So upgrade from v1 to v2 isn't difficult.
And you expect thousands of users to do this? I don't know about that...
I can barely handle it myself, and I was looking forward to a Postgres
upgrade for a change where I didn't have to edit the dump :(
Chris
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] cvs version compile error
Tom Lane wrote: I think Bruce already fixed this. How old is your CVS pull? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings upgraded this morning, around 2003-07-04 09:29:00 CST or 2003-07-03 17:29:00 PST. and later I add a #include limits.h to src/interfaces/ecpg/pgtypeslib/numeric.c fix it temporary, don't know if it's correct, but make check all passed. Will try newer cvs tip later. Thank you laser ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] cvs version compile error
Weiping He [EMAIL PROTECTED] writes: Tom Lane wrote: I think Bruce already fixed this. How old is your CVS pull? upgraded this morning, around 2003-07-04 09:29:00 CST or 2003-07-03 17:29:00 PST. and later I add a #include limits.h Yeah, that is the correct fix, and Bruce did fix it on Wednesday. I just found out from Marc that he had to restore cvs.postgresql.org from a backup, and all CVS commits from Wednesday were lost. I have chastised him for not making that crystal-clear to all committers :-( I believe I can recover the missing updates from my own backup tapes, working on it now. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Compile error in current cvs (~1230 CDT July 4)
I get the following compilation error when making postgres from current CVS: timestamp.c: In function `tm2timestamp': timestamp.c:69: warning: implicit declaration of function `elog' timestamp.c:69: `ERROR' undeclared (first use in this function) timestamp.c:69: (Each undeclared identifier is reported only once timestamp.c:69: for each function it appears in.) make[4]: *** [timestamp.o] Error 1 This is on a machine with RH 6.1. The following configure command was used: ./configure --prefix=/usr/local/pgsql --enable-integer-datetimes --with-pgport=5433 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Hitting the nfile limit
We ran into problem while load-testing 7.3.2 server. From the database log: FATAL: cannot open /home/some_path/postgresql/PG_VERSION: File table overflow The QA engineer who ran the test claims that after server was restarted one record on the database was missing. We are not sure what exactly happened. He was running about 10 servers on HP-11, hitting them with AstraLoad. Most requests would try to update some record on the database, most run with Serializable Isolation Level. Apparently we managed to run out of the open file descriptors on the host machine. I wonder how Postgres handles this situation. (Or power outage, or any hard system fault, at this point) Is it possible that we really lost a record because of that? Should we consider changing default WAL_SYNC_METHOD? Thanks in advance, Michael. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] pg_autovacuum bug and feature request
Hi, I've been using pg_autovacuum for a couple of weeks now, and have noticed one weird little bug: sometimes the daemon calculates it used a negative amount of time for the last vacuum it did, and waits no time at all before checking if it needs to run anything again. Sample output: 2411 All DBs checked in: -717533400 usec, will sleep for 30 secs. The 30 secs is only because I ran it like this: pg_autovacuum -d 2 -s 30 -S 0 -t 250 -T 0.01 -U postgres I'm using PostgreSQL 7.3.2 on Debian Linux, kernel 2.4.21-rc3. Also, I'd like to see a way to tell pg_autovacuum which tables it should monitor. I understand most setups would like to have all tables monitored, but on our setup pg_autovacuum is wasting most of it's time (and a fair amount of serverload) vacuuming some large tables (several GB's of data, the vacuums regularly take half an hour per table or something in the very rough vicinity) which doesn't give a large win in performance anyway, while it should be focusing it's efforts on a few intensively used small tables, where frequent vacuums are a much larger win for performance. I vacuum everything nightly anyway, so those large tables can be totally ignored by pg_autovacuum in my setup. As you can see from the weird -t and -T parameters I already tried to make it favor those smaller tables (which get about the same amount of updates as the large tables), but I'm not quite sure I'm doing it the right way. Regards, Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(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] Hitting the nfile limit
Michael Brusser [EMAIL PROTECTED] writes: Apparently we managed to run out of the open file descriptors on the host machine. This is pretty common if you set a large max_connections value while not doing anything to raise the kernel nfile limit. Postgres will follow what the kernel tells it is a safe number of open files per process, but far too many kernels lie through their teeth about what they can support :-( You can reduce max_files_per_process in postgresql.conf to keep Postgres from believing what the kernel says. I'd recommend making sure that max_connections * max_files_per_process is comfortably less than the kernel nfiles setting (don't forget the rest of the system wants to have some files open too ;-)) I wonder how Postgres handles this situation. (Or power outage, or any hard system fault, at this point) Theoretically we should be able to recover from this without loss of committed data (assuming you were running with fsync on). Is your QA person certain that the record in question had been written by a successfully-committed transaction? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Compile error in current cvs (~1230 CDT July 4)
Bruno Wolff III wrote: I get the following compilation error when making postgres from current CVS: timestamp.c: In function `tm2timestamp': timestamp.c:69: warning: implicit declaration of function `elog' timestamp.c:69: `ERROR' undeclared (first use in this function) timestamp.c:69: (Each undeclared identifier is reported only once timestamp.c:69: for each function it appears in.) make[4]: *** [timestamp.o] Error 1 This is on a machine with RH 6.1. The following configure command was used: ./configure --prefix=/usr/local/pgsql --enable-integer-datetimes --with-pgport=5433 Yeah, I've been getting that since Wednesday morning (west coast USA time), and reported it Wednesday evening, but no one else has replied to that post, so I thought maybe it was somehow related to the othee ecpg issues being discussed. I sync'd up after Tom committed the lost commits from Wednesday, and I'm still seeing the issue. Adding + #include utils/elog.h to timestamp.c lets me compile, but I'm left with two warnings: timestamp.c: In function `PGTYPEStimestamp_from_asc': timestamp.c:315: warning: overflow in implicit constant conversion timestamp.c:319: warning: overflow in implicit constant conversion Joe ---(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] Compile error in current cvs (~1230 CDT July 4)
Joe Conway [EMAIL PROTECTED] writes: Yeah, I've been getting that since Wednesday morning (west coast USA time), and reported it Wednesday evening, but no one else has replied to that post, so I thought maybe it was somehow related to the othee ecpg issues being discussed. I've committed fixes for the problems noted by gcc. I wouldn't care to bet that the code actually works though. The HAVE_INT64_TIMESTAMP paths in ecpg seem to be totally untested :-( 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] Compile error in current cvs (~1230 CDT July 4)
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Yeah, I've been getting that since Wednesday morning (west coast USA time), and reported it Wednesday evening, but no one else has replied to that post, so I thought maybe it was somehow related to the othee ecpg issues being discussed. I've committed fixes for the problems noted by gcc. I wouldn't care to bet that the code actually works though. The HAVE_INT64_TIMESTAMP paths in ecpg seem to be totally untested :-( Thanks, Tom. It does at least compile cleanly now. I don't use ecpg, so I can't say whether the changes actually work. Joe ---(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] Compile error in current cvs (~1230 CDT July 4)
On Fri, Jul 04, 2003 at 11:23:57 -0700, Joe Conway [EMAIL PROTECTED] wrote: Thanks, Tom. It does at least compile cleanly now. I don't use ecpg, so I can't say whether the changes actually work. I am still seeing the problem in anoncvs, but I seem to remember there being a lag between the real cvs and the anoncvs servers, so I might just need to wait a bit. I don't use ecpg either, so I won't be testing out the code there. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Hitting the nfile limit
I wonder how Postgres handles this situation. (Or power outage, or any hard system fault, at this point) Theoretically we should be able to recover from this without loss of committed data (assuming you were running with fsync on). Is your QA person certain that the record in question had been written by a successfully-committed transaction? He's saying that his test script did not write any new records, only updated existing ones. My uneducated guess on how update may work: - create a clone record from the one to be updated and update some field(s) with given values. - write new record to the database and delete the original. If this is the case, could it be that somewhere along these lines postgres ran into problem and lost the record completely? But all this should be done in a transaction, so... I don't know... As for fsync, we currently go with whatever default value is, same for wal_sync_method. Does anyone has an estimate on performance penalty related to turning fsync on? Michael. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Proof-of-concept for initdb-time shared_buffers selection
The attached patch shows how initdb can dynamically determine reasonable
shared_buffers and max_connections settings that will work on the
current machine. It consists of two trivial adjustments: one rips out
the PrivateMemory code, so that a standalone backend will allocate a
shared memory segment the same way as a postmaster would do, and the
second adds a simple test loop in initdb that sees how large a setting
will still allow the backend to start.
The patch isn't quite complete since I didn't bother adding the few
lines of sed hacking needed to actually insert the selected values into
the installed postgresql.conf file, but that's just another few minutes'
work. Adjusting the documentation to match would take a bit longer.
We might also want to tweak initdb to print a warning message if it's
forced to select very small values, but I didn't do that yet.
Questions for the list:
1. Does this approach seem like a reasonable solution to our problem
of some machines having unrealistically small kernel limits on shared
memory?
2. If so, can I get away with applying this post-feature-freeze? I can
argue that it's a bug fix, but perhaps some will disagree.
3. What should be the set of tested values? I have it as
buffers: first to work of 1000 900 800 700 600 500 400 300 200 100 50
connections: first to work of 100 50 40 30 20 10
but we could certainly argue for different rules.
regards, tom lane
*** src/backend/port/sysv_shmem.c.orig Thu May 8 15:17:07 2003
--- src/backend/port/sysv_shmem.c Fri Jul 4 14:47:51 2003
***
*** 45,52
static void *InternalIpcMemoryCreate(IpcMemoryKey memKey, uint32 size);
static void IpcMemoryDetach(int status, Datum shmaddr);
static void IpcMemoryDelete(int status, Datum shmId);
- static void *PrivateMemoryCreate(uint32 size);
- static void PrivateMemoryDelete(int status, Datum memaddr);
static PGShmemHeader *PGSharedMemoryAttach(IpcMemoryKey key,
IpcMemoryId *shmid, void *addr);
--- 45,50
***
*** 243,283
}
- /*
- *private memory support
- *
- * Rather than allocating shmem segments with IPC_PRIVATE key, we
- * just malloc() the requested amount of space. This code emulates
- * the needed shmem functions.
- *
- */
-
- static void *
- PrivateMemoryCreate(uint32 size)
- {
- void *memAddress;
-
- memAddress = malloc(size);
- if (!memAddress)
- {
- fprintf(stderr, PrivateMemoryCreate: malloc(%u) failed\n, size);
- proc_exit(1);
- }
- MemSet(memAddress, 0, size);/* keep Purify quiet */
-
- /* Register on-exit routine to release storage */
- on_shmem_exit(PrivateMemoryDelete, PointerGetDatum(memAddress));
-
- return memAddress;
- }
-
- static void
- PrivateMemoryDelete(int status, Datum memaddr)
- {
- free(DatumGetPointer(memaddr));
- }
-
-
/*
* PGSharedMemoryCreate
*
--- 241,246
***
*** 289,294
--- 252,260
* collision with non-Postgres shmem segments.The idea here is to detect and
* re-use keys that may have been assigned by a crashed postmaster or backend.
*
+ * makePrivate means to always create a new segment, rather than attach to
+ * or recycle any existing segment.
+ *
* The port number is passed for possible use as a key (for SysV, we use
* it to generate the starting shmem key).In a standalone backend,
* zero will be passed.
***
*** 323,342
for (;;NextShmemSegID++)
{
- /* Special case if creating a private segment --- just malloc() it */
- if (makePrivate)
- {
- memAddress = PrivateMemoryCreate(size);
- break;
- }
-
/* Try to create new segment */
memAddress = InternalIpcMemoryCreate(NextShmemSegID, size);
if (memAddress)
break; /* successful create and
attach */
/* Check shared memory and possibly remove and recreate */
!
if ((hdr = (PGShmemHeader *) memAddress = PGSharedMemoryAttach(
NextShmemSegID, shmid,
UsedShmemSegAddr)) == NULL)
continue; /* can't attach, not one of
mine */
--- 289,304
for (;;NextShmemSegID++)
{
/* Try to create new segment */
memAddress = InternalIpcMemoryCreate(NextShmemSegID, size);
if (memAddress)
break; /*
Re: [HACKERS] [pgadmin-hackers] [GENERAL] pgAdmin III - Call for Translators
On Thursday 03 July 2003 12:32, A. van Roggen wrote: If you ever have read the instruction books for e.g. early VCR recorders, translated from the original Japanese to English, you will know exactly what I mean. Proper translation is not an easy job, and volunteers from another branch of technology should be informed that some technical suggestion to the text may be made before acceptance. Dear friend, We agree with you and have therefore written translation guidelines. Your feedback would be greatly appreciate. In the past, I translated part of Oracle8i into French several years ago and noticed it was not an easy game. We share the same ideas. Please refer to (CVS is down at the moment) : http://cvs.pgadmin.org/cgi-bin/viewcvs.cgi/*checkout*/pgadmin3/docs/en_US/translation_guidelines.html Any suggestion is welcome. Cheers, Jean-Michel POURE ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL vs. MySQL
On Fri, Jul 04, 2003 at 10:49:01AM -0400, Rod Taylor wrote: In my opinion the defaults should be set up for a typical database server machine. Ok.. thats fair. The first problem would be to define typical for current PostgreSQL installations, and typical for non-postgresql installations (the folks we want to convert). It's been a while since the last one of these discussions, so stop me if this has been suggested before, but... Do we actually want to have a default configuration file? Seriously, if we provide, say, 4 or 5 files based on various system assumptions (conf.MINIMAL, conf.AVERAGE, conf.MULTIDISK, or whatever), then we might be able to get away with not providing an actual default. Change the installation instructions to say PostgreSQL requires a configuration file, which it expects to be located in $DIR. Provided are several example configurations (in $DIR/eg/). If you're just starting with PostrgreSQL, we recommend reading through those and selecting one which most closely matches your machine. If you're in doubt as to which file to use, try $AVERAGE. If you're still having difficulty getting PostgreSQL to run, try $MINIMAL. $MINIMAL should work on every supported platform, but is not optimized for modern hardware -- PostgreSQL will not run well in this configuration. This makes the installation process slightly less simple, but only in the way that we want it to be. That is, it forces the end user to the realization that there actually is configuration to be done, and forces them into a minimally interactive way to deal with it. It also doesn't require any kernel-test coding, or really any development at all, so we should theoretically be able to get it finished and ready to go more quickly. Thoughts? -johnnn ---(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] '_' '5' -- different answer on 7.2 and 7.3
I noticed a change between our 7.2 and 7.3 postgresql database. On 7.2: template1= select '_' '5'; ?column? -- f (1 row) On 7.3: template1=# select '_' '5'; ?column? -- t (1 row) Any reason for this change? dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] '_' '5' -- different answer on 7.2 and 7.3
On Fri, 4 Jul 2003, David Blasby wrote:
I noticed a change between our 7.2 and 7.3 postgresql database.
On 7.2:
template1= select '_' '5';
?column?
--
f
(1 row)
On 7.3:
template1=# select '_' '5';
?column?
--
t
(1 row)
Any reason for this change?
My first guess would be that you're not running in C locale
on the 7.3 system. I get false on my 7.3.1 system in C locale,
but if I compare the two strings in C using en_US for example I
seem to get results like the above ('_''5' is true).
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proof-of-concept for initdb-time shared_buffers selection
On Fri, Jul 04, 2003 at 03:29:37PM -0400, Tom Lane wrote: 2. If so, can I get away with applying this post-feature-freeze? I can argue that it's a bug fix, but perhaps some will disagree. I'd say it is a bug fix. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proof-of-concept for initdb-time shared_buffers selection
On Friday 04 July 2003 13:31, Michael Meskes wrote: On Fri, Jul 04, 2003 at 03:29:37PM -0400, Tom Lane wrote: 2. If so, can I get away with applying this post-feature-freeze? I can argue that it's a bug fix, but perhaps some will disagree. I'd say it is a bug fix. Michael I'm with you Michael/Tom on this one as well, Lets at least get this framework inplace, we can always experment with what values we settle on. -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] '_' '5' -- different answer on 7.2 and 7.3
My first guess would be that you're not running in C locale
on the 7.3 system. I get false on my 7.3.1 system in C locale,
but if I compare the two strings in C using en_US for example I
seem to get results like the above ('_''5' is true).
It turns out our 7.3 database was somehow initd with local en_US.
I'm trying to get postgresql and a MS vc++ to communicate.
In postgresql 7.3 (en_US):
toponymy=# select '_' '5';;
?column?
--
t
(1 row)
toponymy=# select '_5' '5';
?column?
--
f
(1 row)
But in MS vc++:
TRACE(locale set to 'en_US'\n);
setlocale( LC_ALL, English_United States );
if (strcoll(_5,5) 0 )
TRACE(strcoll('_5','5') -- 0 \n);
else
TRACE( strcoll('_5','5') -- =0\n);
returns:
locale set to 'en_US'
strcoll('_5','5') -- 0
Which is to say postgresql thinks _5 5, but
(a bit strangely) _ 5 (the '' and '' are reversed).
vc++ thinks _5 5 and _ 5.
So, which one is correct and why does the other disagree?
dave
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] Are we backwards on the sign of timezones?
On 3 Jul 2003 at 13:18, Tom Lane wrote: Comments? Now that my NZ server is up and running: template1=# select now(); now --- 2003-07-05 12:47:15.444535+12 That doesn't look backwards to me. Perhaps I don't understand the problem. After rereading your original post: Local time is equal to UTC (Coordinated Universal Time) plus the time zone displacement, In the above, the local time is 2003-07-05 12:47:15.444535. UTC would be 2003-07-05 00:47:15.444535. To which we add +12 hours to get local time. That appears to be consistent with the SQL99 spec. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Need help for our thesis.
Gud day! We are currently having our thesis for our undergarduate course and this involoves the code of PostgreSQL. Basically, our thesis needs a modification of the existing code of the said database. As of now, our University has an existing online registration system which uses Postgre as their back end. But the problem is the existing one is not a distributed system. Our thesis adviser would like us to deal with the load balancing of the said online registration. He wanted a multidatabase querying. For example, we knew that the following statements are posible, select * from t1, t2., t3; provided that t1 and t2, t3 are tables from a single database. Ourthesis is to make the following satatement legal select * from d1, d2, d3; provided that d1 and d2, d3 are separate databases. We are currently having our research about this problem and as beginners, we surely need help from experts like you. Any form of comments and suggestions will surely be appreciated by the group. Thank you very much and hope to here from you. Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: [HACKERS] [PATCHES] Proof-of-concept for initdb-time shared_buffers selection
Tom Lane wrote: 1. Does this approach seem like a reasonable solution to our problem of some machines having unrealistically small kernel limits on shared memory? Yes, it does to me. 2. If so, can I get away with applying this post-feature-freeze? I can argue that it's a bug fix, but perhaps some will disagree. I'd go with calling it a bug fix, or rather pluging a known deficiency. 3. What should be the set of tested values? I have it as buffers: first to work of 1000 900 800 700 600 500 400 300 200 100 50 connections: first to work of 100 50 40 30 20 10 but we could certainly argue for different rules. These seem reasonable. We might want to output a message, even if the highest values fly, that tuning is recommended for best performance. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Are we backwards on the sign of timezones?
Dan Langille [EMAIL PROTECTED] writes: Now that my NZ server is up and running: template1=# select now(); 2003-07-05 12:47:15.444535+12 That doesn't look backwards to me. Try EXTRACT(timezone_hour from now()); The timestamp I/O routines are using what I think is the correct sign. EXTRACT() is at variance. So is SET TIMEZONE with a numeric offset. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
