AW: AW: AW: AW: AW: [HACKERS] Re: tinterval - operator problems on AI X
I do not have the original thread where Andreas describes
the behavior
of mktime() on his machine. Andreas, can you suggest a
simple configure
test to be used?
#include time.h
int main()
{
struct tm tt, *tm=tt;
int i = -5000;
tm = localtime (i);
i = mktime (tm);
if (i != -5000) /* on AIX this check could also
be (i == -1) */
{
printf("ERROR: mktime(3) does not correctly support
datetimes before 1970\n");
return(1);
}
}
You don't need to put this check into configure, you can just
do the check after mktime() is used.
No, we need that info for the output functions that only use localtime.
The intent is, to not use DST before 1970 on platforms that don't have
mktime for dates before 1970.
Andreas
[HACKERS] getCrossReference
Hi. Does any one know if getCrossReference (jdbc method) works with postgresql 7.0 ? I`m using jdbc V 7.0-1.2 any example ? Thanks. Felipe Diaz Cardona
Re: AW: AW: AW: [HACKERS] Re: tinterval - operator problems on AIX
Zeugswetter Andreas SB writes: The down side is, that I did not do a configure test, and did not incooperate IRIX, since I didn't know what define to check. The correct thing to do instead of the #if defined (_AIX) would be to use something like #ifdef NO_NEGATIVE_MKTIME and set that with a configure. I agree that configure is the way to go. What if someone has installed a third party library to provide a better mktime() and localtime()? But to answer your question, #if defined (__sgi) is a good test for IRIX, at least with the native compiler. I can't speak for gcc. -- Pete Forman -./\.- Disclaimer: This post is originated WesternGeco -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- opinion of Schlumberger, Baker http://www.crosswinds.net/~petef -./\.- Hughes or their divisions.
[HACKERS] compiling 7.1-beta1
I'm compiling beta 1 of 7.1 and I have a par of questions. First I see things like this in the compilation output: gcc -g -Wall -Wmissing-prototypes -Wmissing-declarations -Wno-error -I/usr/local/ssl//include -I../../../src/include -c analyze.c -o analyze.o analyze.c: In function `transformInsertStmt': analyze.c:425: warning: unused variable `resnode' I know it's nothing serious, but. Is it because it's in beta, and some trashed code hasn't been taken off? The other question is if I have to do something special (dump and restore) when upgrading from 7.1-beta1 to 7.1-final (or any of the other betas)? Saludos... :-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martn Marqus email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
[HACKERS] palloc? (re: What's with update?)
I think I know what the problem is:
I have a couple indexes created with a "lower" function to index on
lowercase.
To return a lowercase text object, I use the "lower" function, as copied
from the postgres source (Oddly enough varchar does not work):
text * lower(text *string)
{
text *ret;
char *ptr,
*ptr_ret;
int m;
if ((string == (text *) NULL) || ((m = VARSIZE(string) -
VARHDRSZ) = 0))
return string;
ret = (text *) palloc(VARSIZE(string));
VARSIZE(ret) = VARSIZE(string);
ptr = VARDATA(string);
ptr_ret = VARDATA(ret);
while (m--)
*ptr_ret++ = tolower((unsigned char) *ptr++);
return ret;
}
During a long update, the indexes must also be updated.
I bet, the memory is not freed until after the update is completed, and
that during the update all the previous results of "lower" remain in
RAM. This explains why it is slow, because I begin to hit swap. This
explains why it crashes, can't get memory.
Is this a reasonable conclusion given the source and the circumstances?
If so, if I alter the text* passed to me, would/could it affect the
system? i.e. will it affect the disk image or other processes currently
accessing the record? If I return the text pointer passed to me after
modification, will postgres attempt to free it twice?
[HACKERS] Changes to include files
Could anyone please tell me what changed in some of the include files. I just noticed that ecpg won't compile anymore. Also I think we have yet to agree on the libpq/ecpg problem, or did I miss a mail yet again? Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
[HACKERS] converting from text - inet ... possible?
I scanned the archives, and one person asked it back in July, but there
doesn't appear to be any followup ...
Is it possible to go from text-inet in v7.0.3? if not, is it in v7.1?
the following doesn't work:
template1=# select '216.126.84.1'::text::inet;
ERROR: Cannot cast type 'text' to 'inet'
but I could be missing something in the docs?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[HACKERS] Re: converting from text - inet ... possible?
Is it possible to go from text-inet in v7.0.3? if not, is it in v7.1? Seems to not be in 7.1 either. - Thomas
[HACKERS] Re: Changes to include files
Could anyone please tell me what changed in some of the include files. I just noticed that ecpg won't compile anymore. I haven't been able to update my cvs sources for a few days, but on my machine preproc.y produces 321 shift/reduce errors. Does it still, or is that patched up? - Thomas
[HACKERS] Re: converting from text - inet ... possible?
how hard would it be to rectify before beta4 is put out? i'm doing a
manual dump/restore to get my data from text-inet ... not elegant, but it
works ... ::)
On Thu, 18 Jan 2001, Thomas Lockhart wrote:
Is it possible to go from text-inet in v7.0.3? if not, is it in v7.1?
Seems to not be in 7.1 either.
- Thomas
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [HACKERS] copy from stdin; bug?
Nathan Myers wrote: [ ... ] Not true. There are Debian source packages, Where are they ? I'm *quite* interested ! and taking the source package from Debian 2.x, x2 (woody/sid), you can easily build it on Debian 2.2 (potato). In fact, it seems likely that a 2.2 (potato) packaging of 7.1 should be available from somebody else anyhow. Oliver, do you plan to make the woody 7.1 package depend on any other package versions not in potato? If not, you can just use the 7.1 package directly on your Debian 2.2 system. Oliver Elphick seems awfully busy and once said that 7.1 required a *lot* of packaging ... Better not bug him right now ... -- Emmanuel Charpentier
[HACKERS] Gateway
Hi, I am working on a project that will integrate data from an Oracle database with data from a Postgresql database. Is there a gateway to Oracle from Postgresql? I know that in Oracle there are gateways to other database such as DB2. Thanks for the help. Wenjin Zheng
Re: [HACKERS] Changes to include files
That is strange. It is all compiling here. Could anyone please tell me what changed in some of the include files. I just noticed that ecpg won't compile anymore. Also I think we have yet to agree on the libpq/ecpg problem, or did I miss a mail yet again? Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] copy from stdin; bug?
Emmanuel Charpentier wrote: Oliver Elphick seems awfully busy and once said that 7.1 required a *lot* of packaging ... Better not bug him right now ... I'm working on it at the moment. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For the eyes of the LORD run to and fro throughout the whole earth, to show himself strong in the behalf of them whose heart is perfect toward him..." II Chronicles 16:9
Re: [HACKERS] compiling 7.1-beta1
"Martin A. Marques" [EMAIL PROTECTED] writes: I'm compiling beta 1 of 7.1 and I have a par of questions. analyze.c: In function `transformInsertStmt': analyze.c:425: warning: unused variable `resnode' Fixed in current sources. (I think there is still one unused-var complaint left in the XLOG code; I've been waiting on Vadim to do something about it, because it looks like there is code still to be written there.) The other question is if I have to do something special (dump and restore) when upgrading from 7.1-beta1 to 7.1-final (or any of the other betas)? You will need an initdb to go from beta1 to beta3. Sorry about that; we try to avoid forced initdb after beta cycle starts, but sometimes it's not possible. You might want to skip testing beta1 and just start with beta3, or even a current nightly snapshot. regards, tom lane
Re: [HACKERS] Re: Changes to include files
Could anyone please tell me what changed in some of the include files. I just noticed that ecpg won't compile anymore. I haven't been able to update my cvs sources for a few days, but on my machine preproc.y produces 321 shift/reduce errors. Does it still, or is that patched up? In /pg/pl/plpgsql/src, I get: bison -y -d gram.y sed -e 's/yy/plpgsql_yy/g' -e 's/YY/PLPGSQL_YY/g' y.tab.c ./pl_gram.c sed -e 's/yy/plpgsql_yy/g' -e 's/YY/PLPGSQL_YY/g' y.tab.h ./pl.tab.h rm -f y.tab.c y.tab.h Looks OK to me. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
RE: [HACKERS] compiling 7.1-beta1
Fixed in current sources. (I think there is still one unused-var complaint left in the XLOG code; I've been waiting on Vadim to do something about it, because it looks like there is code still to be written there.) Just commented out for now. Vadim
Re: [HACKERS] palloc? (re: What's with update?)
mlw [EMAIL PROTECTED] writes: I think I know what the problem is: I have a couple indexes created with a "lower" function to index on lowercase. Ah. You're correct, functional indexes leak memory in existing PG releases. The memory is reclaimed at end of statement, which is not soon enough if you insert/update a large number of rows. I think this is fixed in 7.1 though, if you want to try it... regards, tom lane
[HACKERS] A bug with unique indicies
Hello. Hopefully, this is the right mailing list to send
this type of question too.
System:
I am running the newest 7.03 build on a dual 866 Pentium III
with a 128M raid card.
I have found an error that is quite odd.
I have a table that is supposed to keep a map between
urls and ids. Each url in the table should be unique.
Thus I have
Create table "urlmap" (
"url" text not NULL,
"id" int4 not NULL,
PRIMARY KEY ("url"),
UNIQUE ("id","url") );
After inserting a number of urls (via spidering) i did the following.
I vacuumed the db : vacuum verbose analyze.
First:
select * from urlmap where url='blah blah';
Here I got back only one row. Good.
Then i went ahead reindexed the table: I recieved the error:
Cannot create unique index. Table contains non-unique values.
Same problem occurs if I drop the indicies and try to recreate them.
I then :
select * from urlmap u1,urlmap u2 where u1.url=u2.url and u1.oid!=u2.oid
I then got back two rows where the url was indeed the same and the
associated id
different. Why, would this ever occur?
-Aditya
Re: [HACKERS] Changes to include files
Michael Meskes [EMAIL PROTECTED] writes: Could anyone please tell me what changed in some of the include files. I just noticed that ecpg won't compile anymore. It was building for me as of last night (last cvs update Jan 18 00:03 EST). Did someone break something since then? regards, tom lane
[HACKERS] test/locale broken
Just tried it for the first time: % cd src/test/locale % gmake all gmake: Circular test-pgsql-locale - all dependency dropped. cd: can't cd to pgsql-locale gmake: *** [test-pgsql-locale] Error 2 I think the next stage is gmake test-koi8.. Cheers, Patrick
[HACKERS] charset.htm
From http://www.postgresql.org/devel-corner/docs/admin/charset.htm Once you have chosen a set of localization rules this way you must keep them fixed for any particular database cluster. That means that the locales that were active when you ran initdb must be kept the same when you start the postmaster. Is that still true? I seem to remember something about the postmaster using whatever initdb set.. Cheers, Patrick
Re: [HACKERS] OLD.oid issues...
On Thu, 18 Jan 2001, Rod Taylor wrote: create table example ( original_oid oid REFERENCES table(oid) ON UPDATE CASCADE ON DELETE SET NULL ); -- ERROR: UNIQUE constraint matching given keys for reference table "table" not found Postgresql 7.1beta3. I'd consider these to be bugs myself but I've not = tried them in previous versions to know if it's really just a new = feature :) Actually I know the latter never really should have worked in past versions. It may have let you define it before, but I believe it would have errored on the actual fk constraint when used, now it just won't let you define it. I think referencing oids is on the todo list (although you have to give ref actions like the ones you have or the constraint is pretty ugly).
Re: [HACKERS] 7.0.3 reproduceable serious select error
I don't think I'm running postmaster in a non-ASCII locale. At least I did not explicitly do anything to accomplish it. Did you have LANG, LOCALE, or any of the LC_xxx family of environment variables set when you started the postmaster? Some Linux distros tend to set those in system profile scripts ... regards, tom lane
[HACKERS] Re: 7.0.3 reproduceable serious select error
Hi Mark, I just checked: the "demo.dump" file does not contain any characters above 0x7F; it's just plain ASCII. So that can't be the reason. greetings, Rob van Nieuwkerk Rob van Nieuwkerk wrote: Ehm .., *you* wrote this ! :-) I tried to reproduce this bug on 7.0.2 and 7.0.3 with both 8K and 32K block sizes, and could not reproduce the error. I am running RedHat 6.2 kernel 2.2.16. I don't know enough to even be close, but I wonder if there are any subtle differences between the way characters are treated for indexes vs the way they are treated for table scans? If there are even slight differences in the way this happens, a misinterpretation of ascii conversions for instance, (I am assuming you may be using ascii characters above 0x7F), it could behave something like this, and explain why I wouldn't see it. .Like I said, however, I don't know much so don't read too much into what I say. Hello, I've selected postgresql 7.0.3 for our (critical) application and while doing my first experiments I've found a bug which makes me worry very much. The problem is that a SELECT with a certain LIKE condition in combination with a GROUP BY does not find the proper records when there is an index on the particular column present. When the index is removed the SELECT *does* return the right answer. Fortunately I managed to strip down our database and create a simple single table with which the bug can be easily reproduced. I've been searching in the Postgres bug-database and this problem might be related to this report: http://www.postgresql.org/bugs/bugs.php?4~111 Below you find a psql-session that demonstrates the bug. I've made a dump of the test-database available as: http://dutepp0.et.tudelft.nl/~robn/demo.dump.bz2 (it is 46100 bytes long in compressed form but 45 MB when uncompressed, I tried to trim it down but then the bug isn't reproducable anymore !) The table is filled with all Spaces execpt for the "town" column. Sysinfo: - well-maintained Linux Red Hat 6.2 - kernel 2.2.18 - Intel Pentium III - postgresql-7.0.3-2 RPMs from the Postgresql site (the problem also occurs with locally rebuilt Source RPM) Any help is much appreciated ! Friendly greetings, Rob van Nieuwkerk psql session: *** demo= \d List of relations Name| Type | Owner +---+--- demo_table | table | robn (1 row) demo= \d demo_table Table "demo_table" Attribute | Type | Modifier ---+--+-- postcode | char(7) | odd_even | char(1) | low | char(5) | high | char(5) | street| char(24) | town | char(24) | area | char(1) | demo= \di No relations found. demo= SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town; town -- ZWOLLE (1 row) demo= SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town; here 86 towns are correctly found (output removed) demo= CREATE INDEX demo_table_town_idx ON demo_table(town); CREATE demo= SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town; town -- (0 rows) This is wrong !! demo= SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town; town -- ZWOLLE (1 row) demo= DROP INDEX demo_table_town_idx; DROP demo= SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town; here 86 towns are correctly found again ***
Re: [HACKERS] copy from stdin; bug?
On Wed, Jan 17, 2001 at 10:34:45PM +0100, Emmanuel Charpentier wrote: Nathan Myers wrote: Not true. There are Debian source packages, Where are they ? I'm *quite* interested ! and taking the source package from Debian 2.x, x2 (woody/sid), you can easily build it on Debian 2.2 (potato). They're as near as "apt-get source postgresql", once you have edited /etc/apt/sources.list to point to the package repositories you want, and have run "apt-get update" to synchronize with those repositories. Under the best circumstances, "apt-get source -b postgresql" will download the sources and build a ".deb" tailored for your system, much as in the BSD "ports" system. (I say "best circumstances" because the package or the build process may depend on tools and "-dev" packages you have not "apt-get install"'ed yet, and because you might prefer to tinker with configuration options before building.) I'm sure once Oliver has prepared a 7.1 Debian package he will announce it here. (This is getting dangerously Debian-specific.) Nathan Myers [EMAIL PROTECTED]
Re: [HACKERS] 7.0.3 reproduceable serious select error
Tom Lane wrote: I don't think I'm running postmaster in a non-ASCII locale. At least I did not explicitly do anything to accomplish it. Did you have LANG, LOCALE, or any of the LC_xxx family of environment variables set when you started the postmaster? Some Linux distros tend to set those in system profile scripts ... Checking whith ps and looking in /proc reveiled that postmaster indeed had LANG set to "en_US" in its environment. I disabled the system script that makes this setting, restarted postgres/postmaster and reran my tests. The problem query returns the *right* answer now ! Turning LANG=en_US back on gives the old buggy behaviour. I know very little about this LANG, LOCALE etc. stuff. But for our application it is very important to support "weird" characters like "éõåÊ ..." etc. for names. Basically we need all letter symbols in ISO-8859-1 (Latin 1). A quick experiment shows that without the LANG setting I can still insert select strings containing these symbols. Do I lose any postgresql functionality by just getting rid of the LANG environment variable ? Will I be able to use full ISO-8859-1 in table fields without problems ? Please tell if you want me to do any other tests ! greetings, Rob van Nieuwkerk
Re: [HACKERS] 7.0.3 reproduceable serious select error
Rob van Nieuwkerk [EMAIL PROTECTED] writes: I know very little about this LANG, LOCALE etc. stuff. But for our application it is very important to support "weird" characters like " ..." etc. for names. Basically we need all letter symbols in ISO-8859-1 (Latin 1). en_US is latin1 - this is what distinguishes it from POSIX/C. -- Trond Eivind Glomsrd Red Hat, Inc.
[HACKERS] Re: Getting configure to notice link-time vs run-time failures
Tom Lane writes: Gene and I looked into this, and the cause of the misbehavior is this: gcc on this installation is set to search /usr/local/lib (along with the usual system library directories). libz.so and libreadline.so are indeed in /usr/local/lib, so configure's tests to see if they can be linked against will succeed. But he had LD_LIBRARY_PATH set to a list that did *not* include /usr/local/lib, so actually firing up the executable would fail. You get what you pay for. If you're running executables from configure you're asking for it. This setup is a poor man's cross-compilation situation because the system you're compiling on is not identically configured to the system you're going to run on. (Strictly speaking, the behaviour of a test program might even vary with different LD_LIBRARY_PATH settings.) So a) PostgreSQL does not support cross-compilation (yet). Too bad. b) We could get rid of all executition time checks in configure (to remedy (a)). This is one of my plans for the future. c) You could move the execution time checks up before the suspicious library checks, but I'm afraid that this will only cure a particular symptom and might introduce other problems. I'd say, you're stuck. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] Re: [NOVICE] Re: Interval output format
"Stefan Waidele jun." wrote:
At 13:37 18.01.2001 -0500, Tom Lane wrote:
"Stefan Waidele jun." [EMAIL PROTECTED] writes:
How can I tell Postgres to return an interval value in an format like
hhh:mm?
See to_char(),
http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.h
tm
to_char() can't take an interval, even in 7.1:
bray=# select proname,pronargs,proargtypes from pg_proc where proname = 'to_char';
proname | pronargs | proargtypes
-+--+-
to_char |2 | 20 25
to_char |2 | 23 25
to_char |2 | 700 25
to_char |2 | 701 25
to_char |2 | 1184 25
to_char |2 | 1700 25
(6 rows)
and date_part() merely extracts the requested part, thus losing data:
bray=# select date_part('hour','3 days 10:23'::INTERVAL);
date_part
---
10
(1 row)
Can to_char be extended?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"For the eyes of the LORD run to and fro throughout the
whole earth, to show himself strong in the behalf of
them whose heart is perfect toward him..."
II Chronicles 16:9
AW: [HACKERS] compilation error
I'm trying to compile postgres on a Solaris 7 SPARC machine and I get this error: gcc -g -Wall -Wmissing-prototypes -Wmissing-declarations -fPIC -I../../../src/include -I../../../src/interfaces/libpq -c pgtcl.c -o pgtcl.o In file included from pgtcl.c:19: libpgtcl.h:19: tcl.h: No such file or directory Now, tcl.h is in /usr/local/include Run configure with: ./configure --with-includes=/usr/local/include --with-libraries=/usr/local/lib Andreas
Re: [HACKERS] GET DIAGNOSTICS SELECT PROCESSED INTO int4_variable
It is in 7.1beta, and is not documented yet. it Does anyone know if this feature exists? If so, what version or where can a patch be obtained? Thanks --- Forwarded message follows --- Date sent:Mon, 15 Jan 2001 08:44:46 +0100 From: "J.H.M. Dassen (Ray)" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: getting number of rows updated within a procedure On Sun, Jan 14, 2001 at 23:27:06 +1300, Dan Langille wrote: I'm writing some stuff in PL/pgsql (actually, a lot of stuff). I have a question: At various times, it does UPDATEs. Is there a way to tell if the UPDATE actually affected any rows or not? I couldn't see how to get UPDATE to return anything. Quoting a recent message by Jan Wieck [EMAIL PROTECTED]: :Do a : :GET DIAGNOSTICS SELECT PROCESSED INTO int4_variable; : :directly after an INSERT, UPDATE or DELETE statement and you'll know :how many rows have been hit. : :Also you can get the OID of an inserted row with : :GET DIAGNOSTICS SELECT RESULT INTO int4_variable; HTH, Ray -- "The software `wizard' is the single greatest obstacle to computer literacy since the Mac." http://www.osopinion.com/Opinions/MichaelKellen/MichaelKellen1.html --- End of forwarded message --- -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ FreshPorts - http://freshports.org/ NZ Broadband - http://unixathome.org/broadband/ -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[HACKERS] Deadlock reimplementation notes (kinda long)
I've completed a paper design for the reimplementation of DeadLockCheck. Notes attached, in case anyone wants to kibitz. (This will appear in storage/lmgr/README when I commit the code.) regards, tom lane --- The deadlock detection algorithm: Since we allow user transactions to request locks in any order, deadlock is possible. We use a deadlock detection/breaking algorithm that is fairly standard in essence, but there are many special considerations needed to deal with Postgres' generalized locking model. A key design consideration is that we want to make routine operations (lock grant and release) run quickly when there is no deadlock, and avoid the overhead of deadlock handling as much as possible. We do this using an "optimistic waiting" approach: if a process cannot acquire the lock it wants immediately, it goes to sleep without any deadlock check. But it also sets a delay timer, with a delay of DeadlockTimeout milliseconds (typically set to one second). If the delay expires before the process is granted the lock it wants, it runs the deadlock detection/breaking code. Normally this code will determine that there is no deadlock condition, and then the process will go back to sleep and wait quietly until it is granted the lock. But if a deadlock condition does exist, it will be resolved, usually by aborting the detecting process' transaction. In this way, we avoid deadlock handling overhead whenever the wait time for a lock is less than DeadlockTimeout, while not imposing an unreasonable delay of detection when there is an error. Lock acquisition (routines LockAcquire and ProcSleep) follows these rules: 1. A lock request is granted immediately if it does not conflict with any existing or waiting lock request, or if the process already holds an instance of the same lock type (eg, there's no penalty to acquire a read lock twice). Note that a process never conflicts with itself, eg one can obtain read lock when one already holds exclusive lock. 2. Otherwise the process joins the lock's wait queue. Normally it will be added to the end of the queue, but there is an exception: if the process already holds locks on this same lockable object that conflict with the request of any pending waiter, then the process will be inserted in the wait queue just ahead of the first such waiter. (If we did not make this check, the deadlock detection code would adjust the queue order to resolve the conflict, but it's relatively cheap to make the check in ProcSleep and avoid a deadlock timeout delay in this case.) Note special case: if the process holds locks that conflict with the first waiter, so that it would go at the front of the queue, and its request does not conflict with the already-granted locks, then the process will be granted the lock without going to sleep at all. When a lock is released, the lock release routine (ProcLockWakeup) scans the lock object's wait queue. Each waiter is awoken if (a) its request does not conflict with already-granted locks, and (b) its request does not conflict with the requests of prior un-wakable waiters. Rule (b) ensures that conflicting requests are granted in order of arrival. There are cases where a later waiter must be allowed to go in front of conflicting earlier waiters to avoid deadlock, but it is not ProcLockWakeup's responsibility to recognize these cases; instead, the deadlock detection code re-orders the wait queue when necessary. To perform deadlock checking, we use the standard method of viewing the various processes as nodes in a directed graph (the waits-for graph or WFG). There is a graph edge leading from process A to process B if A waits for B, ie, A is waiting for some lock and B holds a conflicting lock. There is a deadlock condition if and only if the WFG contains a cycle. We detect cycles by searching outward along waits-for edges to see if we return to our starting point. There are three possible outcomes: 1. All outgoing paths terminate at a running process (which has no outgoing edge). 2. A deadlock is detected by looping back to the start point. We resolve such a deadlock by canceling the start point's lock request and reporting an error in that transaction, which normally leads to transaction abort and release of that transaction's held locks. Note that it's sufficient to cancel one request to remove the cycle; we don't need to kill all the transactions involved. 3. Some path(s) loop back to a node other than the start point. This indicates a deadlock, but one that does not involve our starting process. We ignore this condition on the grounds that resolving such a deadlock is the responsibility of the processes involved --- killing our start- point process would not resolve the deadlock. So, cases 1 and 3 both report "no deadlock". Postgres' situation is a little more complex than the standard discussion of deadlock detection,
Re: [HACKERS] 7.0.3 reproduceable serious select error
Rob van Nieuwkerk [EMAIL PROTECTED] writes: Checking whith ps and looking in /proc reveiled that postmaster indeed had LANG set to "en_US" in its environment. I disabled the system script that makes this setting, restarted postgres/postmaster and reran my tests. The problem query returns the *right* answer now ! Turning LANG=en_US back on gives the old buggy behaviour. Caution: you can't just change the locale willy-nilly, because doing so invalidates the sort ordering of btree indexes. An index built under one sort order is effectively corrupt under another. I recommend that you dumpall, then initdb under the desired LANG setting, then reload, and be careful always to start the postmaster under that same setting henceforth. (BTW, 7.1 prevents this type of index screwup by locking down the database's locale at initdb time --- the ONLY way to change sort order in 7.1 is to initdb with the right locale environment variables. But in 7.0 you gotta be careful about keeping the locale consistent.) I know very little about this LANG, LOCALE etc. stuff. But for our application it is very important to support "weird" characters like "éõåÊ ..." etc. for names. Basically we need all letter symbols in ISO-8859-1 (Latin 1). As long as you are not expecting things to sort in any particular order, it really doesn't matter what locale you run Postgres in. If you do care about sort order of characters that aren't bog-standard USASCII, then you may have a problem. But you can store 'em in any case. regards, tom lane
Re: [HACKERS] 7.0.3 reproduceable serious select error
I meant to ask this the last time this came up on the list, but now is a good time. Given what Tom describes below as the behavior in 7.1 (initdb stores the locale info), how do you determine what locale a database is running in in 7.1 after initdb? Is there some file to look at? Is there some sql statement that can be used to select the setting from the DB? thanks, --Barry Tom Lane wrote: Rob van Nieuwkerk [EMAIL PROTECTED] writes: Checking whith ps and looking in /proc reveiled that postmaster indeed had LANG set to "en_US" in its environment. I disabled the system script that makes this setting, restarted postgres/postmaster and reran my tests. The problem query returns the *right* answer now ! Turning LANG=en_US back on gives the old buggy behaviour. Caution: you can't just change the locale willy-nilly, because doing so invalidates the sort ordering of btree indexes. An index built under one sort order is effectively corrupt under another. I recommend that you dumpall, then initdb under the desired LANG setting, then reload, and be careful always to start the postmaster under that same setting henceforth. (BTW, 7.1 prevents this type of index screwup by locking down the database's locale at initdb time --- the ONLY way to change sort order in 7.1 is to initdb with the right locale environment variables. But in 7.0 you gotta be careful about keeping the locale consistent.) I know very little about this LANG, LOCALE etc. stuff. But for our application it is very important to support "weird" characters like "éõåÊ ..." etc. for names. Basically we need all letter symbols in ISO-8859-1 (Latin 1). As long as you are not expecting things to sort in any particular order, it really doesn't matter what locale you run Postgres in. If you do care about sort order of characters that aren't bog-standard USASCII, then you may have a problem. But you can store 'em in any case. regards, tom lane
[HACKERS] Re: Program test strcoll().
It seems for me your test program do same thing as PostgreSQL backend
does. So I would say if the program worked well, why not PostgreSQL
works?
What do you think Oleg?
From: Maneerat Sappaso [EMAIL PROTECTED]
Subject: Program test strcoll().
Date: Thu, 18 Jan 2001 19:52:39 -0700 (GMT)
Message-ID: [EMAIL PROTECTED]
Deer sir,
Program collsort.c is a test program in THAI locale version
th_TH-2.1.1-5.src.tar.gz for testing strcoll(). It sort by
thai dictionary not by ascii. I use to test this program with
thai or english (or thai+english) words and it sorted correctly.
I found that before run this program we must
setlocale(LC_COLLATE,"th_TH");
When I install PostgreSQL with locale I try to test data by
use sql command like this " select * from table order by name"
The result are sorted by ascii.
regards,
maneerat sappaso
[the test program from Maneerat]
/*
* collsort.c - a word list sorting tool using strcoll()
* Created: 26 Nov 1998
* Author: Theppitak Karoonboonyanan
*/
/*
Copyright (C) 1999 Theppiak Karoonboonyanan
collsort.c is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; either version 2 of
the License, or (at your option) any later version.
*/
#include locale.h
#include stdio.h
#include string.h
#include stdlib.h
typedef unsigned char tchar;
/* for qsort() */
typedef int (*CMPFUNC)(const void *, const void *);
static size_t readData(FILE *dataFile, tchar *data[], int maxData)
{
size_t nData = 0;
static char wordBuf[128];
while (nData maxData fgets(wordBuf, sizeof wordBuf, dataFile) != NULL)
{
int len = strlen(wordBuf);
if (len == 0) { return nData; }
/* eliminate terminating '\n' */
wordBuf[--len] = 0;
/* allocate copy the line */
data[nData] = (tchar*)malloc(len+1);
if (data[nData] == NULL) {
printf("Warning: Only %d items were read\n", nData);
return nData;
}
strcpy((char*)data[nData], wordBuf);
nData++;
}
return nData;
}
static void freeData(tchar *data[], size_t nItems)
{
size_t i;
for (i=0; inItems; i++) {
free(data[i]);
}
}
static int dataCmp(const char **pStr1, const char **pStr2)
{
return strcoll(*pStr1, *pStr2);
}
static void sortData(tchar *data[], size_t nItems)
{
qsort(data, nItems, sizeof data[0], (CMPFUNC)dataCmp);
}
static void writeData(FILE *outFile, tchar *data[], size_t nItems)
{
size_t i;
for (i = nItems; i 0; i--) {
fprintf(outFile, "%s\n", *data);
data++;
}
}
#define MAX_DATA 4
static tchar *data[MAX_DATA];
int main(int argc, char *argv[])
{
FILE *dataFile;
FILE *outFile;
size_t dataRead;
charDataFileName[64];
charOutFileName[64];
const char* pPrevLocale;
pPrevLocale = setlocale(LC_COLLATE, "");
if (pPrevLocale == 0) {
fprintf(stderr, "Cannot set locale\n");
exit(1);
}
if (argc == 3) {
strcpy(DataFileName, argv[1]);
strcpy(OutFileName, argv[2]);
} else {
fprintf(stderr, "Usage: collsort input file output file\n");
return 1;
}
dataFile = fopen(DataFileName, "rt");
if (dataFile == NULL) {
fprintf(stderr, "Can't open file %s\n", DataFileName);
perror("fopen");
return 1;
}
outFile = fopen(OutFileName, "wt");
if (outFile == NULL) {
fprintf(stderr, "Can't open file %s for write\n", OutFileName);
perror("fopen");
return 1;
}
dataRead = readData(dataFile, data, MAX_DATA);
sortData(data, dataRead);
writeData(outFile, data, dataRead);
freeData(data, dataRead);
fclose(outFile);
fclose(dataFile);
setlocale(LC_COLLATE, pPrevLocale);
return 0;
}
Re: [HACKERS] 7.0.3 reproduceable serious select error
Barry Lind [EMAIL PROTECTED] writes: I meant to ask this the last time this came up on the list, but now is a good time. Given what Tom describes below as the behavior in 7.1 (initdb stores the locale info), how do you determine what locale a database is running in in 7.1 after initdb? Hm. There probably ought to be an inquiry function or SHOW variable for that, but at the moment there's not. Offhand I can't think of any direct way except to paw through the pg_control file looking for the locale name (at least it's stored there in ASCII ;-)). regards, tom lane
Re: [HACKERS] 7.0.3 reproduceable serious select error
Added to TODO: * Add SHOW command to see locale Barry Lind [EMAIL PROTECTED] writes: I meant to ask this the last time this came up on the list, but now is a good time. Given what Tom describes below as the behavior in 7.1 (initdb stores the locale info), how do you determine what locale a database is running in in 7.1 after initdb? Hm. There probably ought to be an inquiry function or SHOW variable for that, but at the moment there's not. Offhand I can't think of any direct way except to paw through the pg_control file looking for the locale name (at least it's stored there in ASCII ;-)). regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[HACKERS] s_lock.h cleanup
In looking at the VAX ASM problem, I realized that the ASM in s_lock.h
is all formatted differently, making it even more confusing. I have
applied the following patch to s_lock.h to try and clean it up.
The new standard format is:
/*
* Standard __asm__ format:
*
* __asm__(
* "command;"
* "command;"
* "command;"
* : "=r"(_res) return value, in register
* : "r"(lock) argument, 'lock pointer', in register
* : "r0"); inline code uses this register
*/
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
? config.log
? config.cache
? config.status
? GNUmakefile
? src/Makefile.custom
? src/GNUmakefile
? src/Makefile.global
? src/log
? src/crtags
? src/backend/postgres
? src/backend/catalog/global.bki
? src/backend/catalog/global.description
? src/backend/catalog/template1.bki
? src/backend/catalog/template1.description
? src/backend/port/Makefile
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_restore
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/pgaccess/pgaccess
? src/bin/pgtclsh/Makefile.tkdefs
? src/bin/pgtclsh/Makefile.tcldefs
? src/bin/pgtclsh/pgtclsh
? src/bin/pgtclsh/pgtksh
? src/bin/psql/psql
? src/bin/scripts/createlang
? src/include/config.h
? src/include/stamp-h
? src/interfaces/ecpg/lib/libecpg.so.3.2.0
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgeasy/libpgeasy.so.2.1
? src/interfaces/libpgtcl/libpgtcl.so.2.1
? src/interfaces/libpq/libpq.so.2.1
? src/interfaces/perl5/blib
? src/interfaces/perl5/Makefile
? src/interfaces/perl5/pm_to_blib
? src/interfaces/perl5/Pg.c
? src/interfaces/perl5/Pg.bs
? src/pl/plperl/blib
? src/pl/plperl/Makefile
? src/pl/plperl/pm_to_blib
? src/pl/plperl/SPI.c
? src/pl/plperl/plperl.bs
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/pl/tcl/Makefile.tcldefs
Index: src/include/storage/s_lock.h
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/storage/s_lock.h,v
retrieving revision 1.78
diff -c -r1.78 s_lock.h
*** src/include/storage/s_lock.h2001/01/18 23:40:26 1.78
--- src/include/storage/s_lock.h2001/01/19 02:52:49
***
*** 35,41
*
*int TAS(slock_t *lock)
*Atomic test-and-set instruction. Attempt to acquire the lock,
! *but do *not* wait. Returns 0 if successful, nonzero if unable
*to acquire the lock.
*
*TAS() is a lower-level part of the API, but is used directly in a
--- 35,41
*
*int TAS(slock_t *lock)
*Atomic test-and-set instruction. Attempt to acquire the lock,
! *but do *not* wait. Returns 0 if successful, nonzero if unable
*to acquire the lock.
*
*TAS() is a lower-level part of the API, but is used directly in a
***
*** 48,56
*unsignedspins = 0;
*
*while (TAS(lock))
- *{
*S_LOCK_SLEEP(lock, spins++);
- *}
*}
*
*where S_LOCK_SLEEP() checks for timeout and sleeps for a short
--- 48,54
***
*** 87,96
/* Platform-independent out-of-line support routines */
extern void s_lock(volatile slock_t *lock,
! const char *file, const int line);
extern void s_lock_sleep(unsigned spins, int microsec,
!volatile slock_t *lock,
!const char *file, const int line);
#if defined(HAS_TEST_AND_SET)
--- 85,94
/* Platform-independent out-of-line support routines */
extern void s_lock(volatile slock_t *lock,
! const char *file, const int line);
extern void s_lock_sleep(unsigned spins, int microsec,
!volatile slock_t *lock,
!const char *file, const int line);
#if defined(HAS_TEST_AND_SET)
***
*** 101,106
--- 99,116
* All the gcc inlines
*/
+ /*
+ * Standard __asm__ format:
+ *
+ *__asm__(
+ *"command;"
+ *"command;"
+ *"command;"
+ *: "=r"(_res) return value, in register
+ *: "r"(lock) argument, 'lock pointer', in
+register
+ *: "r0"); inline code uses this register
+ */
+
#if
Re: [HACKERS] charset.htm
Patrick Welche [EMAIL PROTECTED] writes: From http://www.postgresql.org/devel-corner/docs/admin/charset.htm Once you have chosen a set of localization rules this way you must keep them fixed for any particular database cluster. That means that the locales that were active when you ran initdb must be kept the same when you start the postmaster. Is that still true? Yup, it's out of date. Working on a fix now. regards, tom lane
Re: [HACKERS] s_lock.h cleanup
As long as we're cleaning things up, I would suggest that all the ports that use gcc assembler be made to declare it uniformly, as __asm__ __volatile__ ( ... ); As I read the GCC manual, there's some risk of the asm sections getting moved around in the program flow if they are not marked volatile. Also we oughta be consistent about using the double-underscore keywords IMHO. regards, tom lane
Re: [HACKERS] s_lock.h cleanup
Done and applied. As long as we're cleaning things up, I would suggest that all the ports that use gcc assembler be made to declare it uniformly, as __asm__ __volatile__ ( ... ); As I read the GCC manual, there's some risk of the asm sections getting moved around in the program flow if they are not marked volatile. Also we oughta be consistent about using the double-underscore keywords IMHO. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[HACKERS] FW: User management
-Original Message- From: Patrick Dunford [mailto:[EMAIL PROTECTED]] Sent: Friday, 19 January 2001 15:19 To: [EMAIL PROTECTED] Subject: User management What commands in SQL enable administrators to view user / group information? Are there special SQL commands? Or are there special system tables? === Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/ Not only so, but we also rejoice in our sufferings, because we know that suffering produces perseverance; perseverance, character; and character, hope. -- Romans 5:3-4 http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010118 === Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/
Re: [HACKERS] Re: postgres TODO
Restated in TODO: * Allow [INSERT/UPDATE] ... RETURNING new.col or old.col (Philip) At 09:14 8/07/00 -0400, Bruce Momjian wrote: hi, threre are a postgresql/mysql comparative. You can get something for the TODO: http://www.phpbuilder.com/columns/tim2705.php3?page=1 regards, Thanks. Yes, I have added to the TODO list: * Add function to return primary key value on INSERT I had a look at the page and could not see the reference, so this suggestion may be inappropriate, but... How about something more general - an incredibly useful feature of Dec/Rdb is: insert into t1(...) values(...) returning attr-list which is like performing a select directly after the insert. The same kind of syntax applies to updates as well, eg. update t1 set f1 = 2 where stuff returning f1, f2, f3; Perhaps your original suggestion is a lot easier, but this is a convenient feature... Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[HACKERS] PlPerl.c patch
I have attached a simple change to src/pl/plperl/plperl.c to
enable the :bash_math opcodes. Currently plperl.c only
enables the :default opcodes. This leave out about five of six
math functions including sqrt().
It might be worth considering allowing the user's to enable
other packages on the command line. However, most of the other
packages allow you to do things like access the underlying file
system (as the owner of the backend process), make system calls,
and perform network operations.
The patch is off of the 7.0.3 released code.
--
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
d227 1
a227 1
"sub ::mksafefunc { my $x = new Safe;
$x-permit_only(':default');$x-permit(':base_math');"
[HACKERS] Standards URL's
Here are the URL's for the SQL standards. Where should these go? Are they legal? http://www.ansi.org http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ftp://gatekeeper.dec.com/pub/standards/sql ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/ ANSI PDF $20 ISO PDF $310 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Standards URL's
Bruce Momjian [EMAIL PROTECTED] writes: Here are the URL's for the SQL standards. Where should these go? Are they legal? http://www.ansi.org http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ftp://gatekeeper.dec.com/pub/standards/sql ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/ ANSI PDF $20 ISO PDF $310 The document at CMU is a draft version of SQL92, which is legal to distribute free AFAIK (at least that's been ISO's practice with other draft standards). The documents at DEC are some intermediate version that is probably best ignored. The documents at umassd are SQL99, and not marked as drafts; if they are final text then they might well be considered pirate copies by ISO. But more likely they are late drafts. Of course, ANSI will be happy to sell you a certifiably legal copy. What I want to know is what's the difference between the "ANSI" and "ISO" PDF versions that ANSI sells, other than a factor of 15 in price? I sent an email inquiry about that to ANSI a month ago, and have not gotten an answer. Anyone know? regards, tom lane
Re: [HACKERS] Standards URL's
In one of the emails I deleted from my mailbox, the person stated they are identical, except in price. Bruce Momjian [EMAIL PROTECTED] writes: Here are the URL's for the SQL standards. Where should these go? Are they legal? http://www.ansi.org http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ftp://gatekeeper.dec.com/pub/standards/sql ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/ ANSI PDF $20 ISO PDF $310 The document at CMU is a draft version of SQL92, which is legal to distribute free AFAIK (at least that's been ISO's practice with other draft standards). The documents at DEC are some intermediate version that is probably best ignored. The documents at umassd are SQL99, and not marked as drafts; if they are final text then they might well be considered pirate copies by ISO. But more likely they are late drafts. Of course, ANSI will be happy to sell you a certifiably legal copy. What I want to know is what's the difference between the "ANSI" and "ISO" PDF versions that ANSI sells, other than a factor of 15 in price? I sent an email inquiry about that to ANSI a month ago, and have not gotten an answer. Anyone know? regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Standards URL's
Bruce Momjian [EMAIL PROTECTED] writes: In one of the emails I deleted from my mailbox, the person stated they are identical, except in price. That's what I kinda suspected, but I'd like to see an authoritative statement. Who was this person? regards, tom lane
Re: [HACKERS] 7.1beta3-2 RPMset uploading.
Lamar Owen [EMAIL PROTECTED] writes: Tom, try out a PPC build on this one. I know of one problem that I have to fix -- postgresql-perl fails dependencies for libpq.so (I backed out the patch to Makefile.shlib). The backend seems to build OK, but the build fails in interfaces/perl5 because libpq-fe.h isn't found. The compiler is getting passed -I/usr/include/postgresql, which might work if I'd already installed the RPM, but that's tough when I haven't built it yet :-( I dunno how to get the RPM build process to bypass perl support, so I can't get any further than that ... regards, tom lane
