Re: [GENERAL] function to operate on same fields, different records?
On Mon, Apr 02, 2001 at 02:02:51PM -0500, will trillich wrote: On Mon, Apr 02, 2001 at 10:21:34AM -0400, Tom Lane wrote: Inheritance seems to work for this in current sources: regression=# create table p1 (f1 int); CREATE regression=# create table p2 (f2 int) inherits (p1); CREATE regression=# create function getf1(p1) returns int as ' regression'# select $1.f1' language 'sql'; CREATE regression=# insert into p1 values(1); INSERT 1030276 1 regression=# insert into p2 values(11,22); INSERT 1030277 1 regression=# select getf1(p1) from p1; getf1 --- 1 11 (2 rows) ding! light comes on ... never thought of that. mucho cool, mucho thanks! lots of new exploring to delve into now... clunk, light dims. "current sources" means "not what i'm using". being a staunch potato (debian 2.2) user, i'm already on the leading edge, having upgraded my postgresql to 7.0.3 ... i'll go with the define function gpa(int,int,int,int,int) returns float ... i suppose, then. hmph. -- americans should never read anything so subversive as what's at http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
re : [GENERAL] Address already in use?
Hi, Did you stop the postmaster with an SIGKILL ??? Just go into temp directory and delete file .s.PGSQL.5432 After try to restart postmaster. PostgreSQL check this file in order to know if an another postmaster is running, so if you kill postmaster, it dosen't delete this file. Cheers, PEJAC Pascal ---(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: [GENERAL] Address already in use?
On Thu, Apr 12, 2001 at 11:20:29PM -0400, Justin S. wrote: Hi Everyone, Alright, I've installed PostgreSQL 7.0.3 succesfully, but I still have a problem. When I try starting the PostgreSQL server, using the following command: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data I get the following error: FATAL: StreamServerPort: bind() failed: Address already in use Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. Anyone know how to fix this? Thanks. This happens if postgresql was not able to quit the normal way IMO. Delete the socket and run it again. You need to clean /tmp when the system boots. Most system have this option (on FreeBSD put clear_tmp_enable="YES" in /etc/rc.conf). If you can't find it add rm -f /tmp/.s.PGSQL.* somewhere in the boot scripts as a workaround. You can even put it in the PGSQL starting script, but it's not a quite good idea... -- Players win and winners play Have a lucky day PGP signature ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] setObject bug in postgresql jdbc driver
Hi,
I have some code which legitimately tries to call
setObject(n,o) where o is an Object, and is null. The jdbc driver doesn't check
for this type, and as a result fails.
This is how I changed the code to deal with
this.
public void setObject(int parameterIndex,
Object x) throws SQLException{ if (x ==
null){ // postgres ignores the type so it
doesn't matter what we pass
setNull(parameterIndex,Types.OTHER);
return; }
Is there any reason why it wouldn't check for null
right away ?
Can someone commit this to the source tree if it is
ok?
Regards,
Dave Cramer
[GENERAL] New look for the techdocs website...
Hi all, I've just taken a bit of time to give a more organised look to the whole techdocs.postgresql.org website. If anyone has a few moments spare, could they please take a look and tell me what they think? I like it, but if most people don't, I'll change it back. Regards and best wishes, Justin Clift P.S. - As a bonus, I've even figured out how to access my [EMAIL PROTECTED] email, so I can start using this again! :-) -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Re: ongoing holy grail thread - VICTORY!!!
On 12 Apr 2001 22:40:56 +1000, Justin Clift wrote: When we installed Tomcat and PostgreSQL, I just copied the postgresql.jar file into the /lib directory inside the tomcat installation directory... Justin wins the case of champagne (virtual for the time being) The secret is there - where the jdbc .jar file goes. By default, no-one on your network has permission to touch your PostgreSQL database. If you've opened it up by specifying something like : host all network IP address netmask trust then your users should be able to connect with a username of 'postgres' from what I remember (if that's the PostgreSQL superuser on your server that is). Does not work, must be "host db_name IP user ident" Maybe this has something to do with java security??? - Ultradev is running on NT and connecting via "LAN" thus is using SMB and not TCP/IP is this an issue? Had to connect via ftp (thus tcp/ip) to get it to work. Had to set up ftp just right because Ultradev wants to write over the connection files as it uses them. Please bear with me on this one people: I'll try to write up a sort of HOWTO to document for this. But it has taken me a week full time so far and I have to get back to looking after my clients. For now the secret was a clean install of _everything_ on a developpment server. Everything meaning RedHat 7.0 on down to the Apache module... I think that we should maybe get this info out to Macromedia to supplement the MySQL stuff that is already in Ultradev. Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Debian stable install problems
I am having a bit of trouble geting PostgreSQL working quite corectly on my fresh Debian "stable" istall. I installed it using the Debina dselect tool. Now, If I su - postgres, I can use psql to connect to the db, so I know that that part of the install went OK. However I am having a problem adding users. See below: Script started on Fri Apr 13 09:50:33 2001 postgres@yogi:~$ psql Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=# \q postgres@yogi:~$ /usr/local/pgsql/bin/createuser Enter name of user to add: stan Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) y psql: connectDBStart() -- connect() failed: No such file or directory Is the postmaster running locally and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'? createuser: creation of user "stan" failed postgres@yogi:~$ Script done on Fri Apr 13 09:51:13 2001 Any sugestions as to what I can do to fix this? -- Stan Brown [EMAIL PROTECTED]843-745-3154 Charleston SC. -- Windows 98: n. useless extension to a minor patch release for 32-bit extensions and a graphical shell for a 16-bit patch to an 8-bit operating system originally coded for a 4-bit microprocessor, written by a 2-bit company that can't stand for 1 bit of competition. - (c) 2000 Stan Brown. Redistribution via the Microsoft Network is prohibited. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: Debian stable install problems
Stan Brown [EMAIL PROTECTED] wrote: I am having a bit of trouble geting PostgreSQL working quite corectly on my fresh Debian "stable" istall. I installed it using the Debina dselect tool. Now, If I su - postgres, I can use psql to connect to the db, so I know that that part of the install went OK. However I am having a problem adding users. See below: postgres@yogi:~$ /usr/local/pgsql/bin/createuser ^^ That's probably not the 'creatuser' binary from the Debian package "postgresql-client". The Debian packages of PostgreSQL were changed to look for the PostgreSQL socket in /var/run/postgresql rather than /tmp. Enter name of user to add: stan Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) y psql: connectDBStart() -- connect() failed: No such file or directory Is the postmaster running locally and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'? The psql started by createuser was built to look for the PostgreSQL socket in /tmp. Any sugestions as to what I can do to fix this? Make sure the Debian package versions of "postgresql" and "postgresql-client" match exactly, and make sure you're not mixing a home-built version with the Debian packages. HTH, Ray -- What is this talk of software 'releases'? Klingons do not 'release' software; our software ESCAPES, leaving a bloody trail of designers and quality assurance people in its wake! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Re: ongoing holy grail thread - VICTORY!!!
Hi Tony, That's great to hear. :-) You need a saying like "'twas a long hard battle, but in the end we emerged victorious" about now. Heh Heh Heh Tony Grant wrote: Does not work, must be "host db_name IP user ident" Maybe this has something to do with java security??? Dunno. Errr... could be. Please bear with me on this one people: I'll try to write up a sort of HOWTO to document for this. But it has taken me a week full time so far and I have to get back to looking after my clients. Weekend is afoot too. Relax, have fun. :-) For now the secret was a clean install of _everything_ on a developpment server. Everything meaning RedHat 7.0 on down to the Apache module... I think that we should maybe get this info out to Macromedia to supplement the MySQL stuff that is already in Ultradev. I agree. If you can get the info to the rest of us too, we can make it available for people cruising part the mailing list archives, techdocs website, etc. From memory Macromedia has their Macromedia Exchange area where people can share and contribe extensions for Dreamweaver, Ultradev, etc. They also have a newsletter. Wonder how you would get a mention that PostgreSQL works with it in their monthly newsletter? Probably be quite a few people interested in hearing that. Regards and best wishes, Justin Clift Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(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: [GENERAL] Repairing bad table?
Steve Wampler [EMAIL PROTECTED] writes: I need help - I've had a system crash that has left a table with some invalid OIDs. How do I clean this up? If I try a vacuum analyze, I get: configdb=# vacuum analyze; NOTICE: Rel attributes_table: TID 3304/23: OID IS INVALID. TUPGONE 1. NOTICE: Rel attributes_table: TID 3304/28: OID IS INVALID. TUPGONE 1. NOTICE: Rel attributes_table: TID 3304/31: OID IS INVALID. TUPGONE 1. NOTICE: Rel attributes_table: TID 3304/33: OID IS INVALID. TUPGONE 1. NOTICE: Rel attributes_table: TID 3304/40: OID IS INVALID. TUPGONE 1. NOTICE: Rel attributes_table: TID 3304/41: OID IS INVALID. TUPGONE 1. NOTICE: Rel attributes_table: TID 3304/77: OID IS INVALID. TUPGONE 1. FATAL 1: Memory exhausted in AllocSetAlloc() Try not doing "analyze" --- the most likely cause of the FATAL message is that the system is trying to copy some damaged column value that claims to be ridiculously wide. If nothing else helps, you could manually zero block 3304 of the table, sacrificing those rows to get back the rest of your data. dd(1) should work for this, I think, but be sure to shut down the postmaster while hacking directly on data files. [NB: these are 8K blocks (unless you changed BLCKSZ) and the file starts with block zero.] 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: [GENERAL] updates way slower than selects?
I've got a question: has anybody noticed in your production tables, that updates on existing rows take longer than inserts into those same tables? Updates naturally take longer than inserts. Especially if you haven't provided an index that allows the row(s) to be updated to be found easily. Have you checked the EXPLAIN results for your problem queries? 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: [GENERAL] Question about SELECT FOR UPDATE in transaction, isolation level
Al [EMAIL PROTECTED] writes: begin work; select val from ids where cntr='ct1' for update; update ids set val=val+(some integer value) where cntr='ct1'; commit work; Looks reasonable. However, when I run everything except the commit on one terminal and then run up to and including the select on another terminal, I receive the OLD, non-updated value for val on that second terminal. In other words, the select is not blocked and the id is not unique. Hm, it works for me. You sure you remembered to use select FOR UPDATE in both transactions? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Shared memory failure?
First off, I'd just like to thank everyone for their help with my last problem. It worked, but now it gives me another error: IpcMemoryCreate: shmget failed (Permission denied) key=5432010, size=144, permission=700 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. IpcMemoryAttach: shmat failed (Permission denied) id=2 FATAL 1: AttachSLockMemory: could not attach segment I looked through the troubleshooting guide, but it wasn't very helpful. Anyone know how to get around this? Thanks again. Sincerely, Justin Stayton CometFly Media, LLC. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Deadlock detected
"Brian J. France" [EMAIL PROTECTED] writes: I am getting a few of these errors in my web logs and didn't know what I could do to stop it. NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. Error in query "UPDATE table SET field = value WHERE field2 = value2" : ERROR: WaitOnLock: error on wakeup - Aborting this transaction Is this due to the hash index on field2 or due to the has index in general? Don't use hash indexes for concurrent applications. I don't really know of any reason for preferring a hash index over a btree index in any case. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] pg_dump dying (and VACUUM ANALYZE woes)...
Steve Wampler [EMAIL PROTECTED] writes: I'm getting the following error from pg_dump when trying to dump a particular database: == - pg_dump logdb logdb.dump pqWait() -- connection not open PQendcopy: resetting connection SQL query to dump the contents of Table 'messages' did not execute correctly. After we read all the table contents from the backend, PQendcopy() failed. Explanation from backend: 'The Data Base System is starting up '. The query was: 'COPY "messages" TO stdout; '. == About 25MB has been dumped when this error occurs. (There's 15GB free on the disk partition.) Looks like you've got corrupted data in that table (clobbered length word in some variable-length field, most likely). 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: [GENERAL] Using pl/pgsql or sql for in/out functions for types
John Havard [EMAIL PROTECTED] writes: Is there anyway to do this without having to resort to writing the functions in C or some other language? You really cannot write datatype I/O functions in anything but C, because the I/O functions have to deal in C-style strings, which are not a SQL datatype; so there is no way to describe the necessary behavior in any PL language. There has been some talk of promoting "C string" to be at least a second-class SQL datatype (on the order of being able to declare functions that take or return them, but not use them as a column datatype). Hasn't happened yet though. Why doesn't CREATE FUNCTION complain about plpgsql functions not being able to accept the opaque type as an argument? Because it makes no assumptions about what abilities PL languages might have. The PLs are plug-ins, remember. 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: [GENERAL] Shared memory failure?
"Justin S." [EMAIL PROTECTED] writes: IpcMemoryCreate: shmget failed (Permission denied) key=5432010, size=144, permission=700 Apparently you have an existing shm segment belonging to a different userid. Did you previously start the postmaster under a different user? If so you'll need to get rid of the old segment (using ipcrm under the old userid). 7.1 handles this stuff a little more gracefully, btw ... 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: [GENERAL] Shared memory failure?
First off, I'd just like to thank everyone for their help with my last problem. It worked, but now it gives me another error: IpcMemoryCreate: shmget failed (Permission denied) key=5432010, size=144, permission=700 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. IpcMemoryAttach: shmat failed (Permission denied) id=2 FATAL 1: AttachSLockMemory: could not attach segment I looked through the troubleshooting guide, but it wasn't very helpful. Anyone know how to get around this? Thanks again. It's OS-dependant. What OS are you running, and if it's Linux, which kernel? steve ---(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: [GENERAL] Shared memory failure?
Cobalt RaQ4i with version 6 of their operating system. Sincerely, Justin Stayton CometFly Media, LLC. [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Steve Wolfe Sent: Friday, April 13, 2001 1:13 PM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] Shared memory failure? First off, I'd just like to thank everyone for their help with my last problem. It worked, but now it gives me another error: IpcMemoryCreate: shmget failed (Permission denied) key=5432010, size=144, permission=700 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. IpcMemoryAttach: shmat failed (Permission denied) id=2 FATAL 1: AttachSLockMemory: could not attach segment I looked through the troubleshooting guide, but it wasn't very helpful. Anyone know how to get around this? Thanks again. It's OS-dependant. What OS are you running, and if it's Linux, which kernel? steve ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] consider increasing WAL_FILES
I dump database from 7.0.3 and attempting to restore to 7.1rc4. And get the following messages. I can reproduce them by dropping new db and recreating new ones. Line number varies from one restore to the next and 'MoveOfflineLogs' message was seen only once Should I worry? How do I increase WAL_FILES? The largest table has about 500,000 entries. Thanks in advance DEBUG: copy: line 85254, XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: MoveOfflineLogs: remove 0010 DEBUG: MoveOfflineLogs: remove 0011 DEBUG: copy: line 305196, XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: copy: line 5129, XLogWrite: new log file created - consider increasing WAL_FILES ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [GENERAL] Shared memory failure?
Thanks Tom. Yeah, I heard that an older version PostgreSQL was used. So if I just use a different port number, and not try replacing the version that comes with the OS, everything should work fine? How do I start PostgreSQL on a different port (and which would you recommend)? Thanks. Sincerely, Justin Stayton CometFly Media, LLC. [EMAIL PROTECTED] -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, April 13, 2001 2:59 PM To: [EMAIL PROTECTED] Cc: Steve Wolfe; [EMAIL PROTECTED] Subject: Re: [GENERAL] Shared memory failure? "Justin S." [EMAIL PROTECTED] writes: Cobalt RaQ4i with version 6 of their operating system. Oh. I think Cobalt has a Postgres (6.5.something?) installed as part of the OS. You'll need to pick a different port number than 5432 to avoid conflicting with the system's server. BTW, I've heard that people tried to replace the built-in Postgres with newer releases and had troubles, so I don't recommend trying that unless you're ready to do some debugging. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] updates way slower than selects?
On Friday, April, 2001-04-13 at 18:34:06, Tom Lane wrote:
I've got a question: has anybody noticed in your production
tables, that updates on existing rows take longer than inserts
into those same tables?
Updates naturally take longer than inserts. Especially if you haven't
provided an index that allows the row(s) to be updated to be found
easily. Have you checked the EXPLAIN results for your problem queries?
OK, I take that for granted Tom, but this database has 16 indexes (most
of them on 2-3 columns) and the updated column is just an int4 with no
index defined. No constraints attached. The update is on a single row
selected by serial primary key field ('where field=value'). I change the
field from -1 into 1 to be exact (it is kinda flag field indicating
a state of the record which is being reverted on the update).
'Explain' off course shows index scan that is why I am amazed by the lack
of speed comparing to insert (which is faster, disregarding the need to
update 16 indexes and going through couple of triggers).
(one side-note: I don't argue that the table is well-designed - observe
the number of indexes - I am just puzzled by lack of consistency in the
experience I gained by this - and I would like to learn more about why
it happened or, more likely, to learn that I've messed up badly and
this in not a normal situation).
I have reorganized the app so it doesn't use the 'flag field' anymore
(instead it uses one column table of ints to store the 'marked'
records keys). The process of 'delete from tb1 where id=value' doesn't
compare to 'update tb2 set field1=1 where id=value' in measure of speed
(or slowness). The whole operation (a few inserts/deletes on a single
transaction) takes 20% of the previous time which is much more
satisfactory to me (and my employers ;-)
Any further ideas will be more than appreciated, for the sake of my future
attempts.
thanks and best regards.
--
Marek Ptlicki [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] consider increasing WAL_FILES
[EMAIL PROTECTED] writes: Should I worry? Not unless you see that message frequently during routine operations. Initial bulk data load doesn't count as routine ... How do I increase WAL_FILES? See the documentation. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Shared memory failure?
"Justin S." wrote: Thanks Tom. Yeah, I heard that an older version PostgreSQL was used. So if I just use a different port number, and not try replacing the version that comes with the OS, everything should work fine? How do I start PostgreSQL on a different port (and which would you recommend)? Thanks. As the PostgreSQL that comes with that system is from RPM, that version's executables are in /usr/bin -- if you want the new version's executables to be used, put the path to them _before_ /usr/bin in your PATH -- are you WILL be surprised at the buggy results. If you are not actively using the old PostgreSQL, and have the RPMset files to reinstall, you should be safe in issuing an 'rpm -e' for each postgresql rpm that 'rpm -qa|grep postgresql' returns. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Shared memory failure?
"Justin S." wrote: Thanks Tom. Yeah, I heard that an older version PostgreSQL was used. So if I just use a different port number, and not try replacing the version that comes with the OS, everything should work fine? How do I start PostgreSQL on a different port (and which would you recommend)? Thanks. Oh, and BTW: when you rpm-e postgresql, you have to put all of the rpms on one line, or the circular dependencies that sometimes have been present will bite you. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Shared memory failure?
"Justin S." [EMAIL PROTECTED] writes: Cobalt RaQ4i with version 6 of their operating system. Oh. I think Cobalt has a Postgres (6.5.something?) installed as part of the OS. You'll need to pick a different port number than 5432 to avoid conflicting with the system's server. BTW, I've heard that people tried to replace the built-in Postgres with newer releases and had troubles, so I don't recommend trying that unless you're ready to do some debugging. 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
[GENERAL] Timestamps for BLOB fields
I'm putting together a small database to track communication with our customers as we make some changes to our service. I want the database to store a diary of all the email we have with each customer on this subject, and I'm using BLOBs to store that information. I also want to have a timestamp for the last change date of this email diary, so we can easily see which customers we haven't talked to lately. Normally I'd do this with a trigger, but I'm not sure how to do that in this case. Once the diary is created, the oid for it doesn't change, so I can't trigger on the diary field in the table. The actual contents of the BLOB are in a system table and I can't trigger on them. Anyone have any ideas? Should I just depend on the client to update the timestamp? That certainly wouldn't be hard, but it feels sloppy. Thanks for any help you can provide. ---(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
[GENERAL] to_char(now(), 'YYYY') and time zones
I'm trying to pull the current year with the following query: select to_char(now(), ''); This is fine. Now, I would like to select this date in different time zones. Normally I just do: select now() at time zone 'utc'; I guess I need a combination of the 2 queries above - one that asks for ONLY the year in a certain time zone (fx UTC or CET). I guess I should do some sort of subselect like: select to_char(utc, 'Y') from (select now() as utc at time zone 'utc') But that jsut gives me an error. Does anyone know how to do this? -- Simon Carstensen ---(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: [GENERAL] anti Christian bias?
On Wed, Apr 11, 2001 at 06:32:50PM +, some SMTP stream spewed forth: Hi all: On page 29 of the PostgreSQL User's Guide, distributed with version 7.0.3, in table 3-8 Postgres Date Input, the last item in the Example column is January 8, 99 BC. The corresponding Description item reads "Year 99 before the Common Era". The author or the editor of this manual is obviously expressing his anti Christian bias in attempting to redefine BC to mean Oh yes, obviously. Has society actually been reduced to this level of absurdity? Since when does Christianity cule the world and thus determine what abbreviations should be used to mean what? Furthermore, since when is acknowledgement of differing religious (and other) views considered "anti-Christian"? Please, we all have more important issues. When people stop killing each other we can worry about what BC is supposed to mean. I challenge you to stop them from killing each other. gh "Common Era". Throughout history BC, when associated with a date, has always stood for "Before Christ", and it always will. I challenge the author/editor to tell us exactly what is the significant event in history that marks the boundary of what he chooses to call "Common Era". Nick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Benchmarking PostgreSQL
Justin Clift wrote: As an aside, did anyone ever step up to create a TPC-C benchmark suite for PostgreSQL? Part of the Great Bridge QA process (on every beta, every community major and minor release, and certainly any release we distribute on CD) is the running of the TPC tests and the AS3AP, as implemented by Quest Software's Benchmark Factory (free trial available at http://www.quest.com/benchmark_factory/). These were the tests we originally ran last year as part of our due diligence in evaluating Postgres before forming the company - it's now part of our regular process. Don't know of any other widely-available implementations besides Quest's, but some engineers in the Alpha group at Compaq had done some work on an open source implementation of the AS3AP ... don't have the URL handy, but you ought to be able to find it in the archives. Regards, Ned -- Ned Lilly e: [EMAIL PROTECTED] Vice Presidentw: www.greatbridge.com Evangelism / Hacker Relationsv: 757.233.5523 Great Bridge, LLCf: 757.233. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] anti Christian bias?
On Fri, 13 Apr 2001, Karl DeBisschop wrote: As for postgresql having an anit-Christian bias? I think Lamar and Bruce, among others, could not be accused of an anti-Christian bias. Thanks, Karl. As a matter of fact, I am an ordained Baptist minister. Don't know about Bruce -- other than I like his catchy .sig... :-) Wow, pretty cool. I am just an underling. :-) If anyone asks about my .sig, I witness accordingly. Otherwise, I'm not pushy -- not in this venue, at least. Becoming a Christian was the best thing that ever happened to me, and I want to share that, but I don't want to make people uncomfortable either. -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] anti Christian bias?
On Fri, 13 Apr 2001, Bruce Momjian wrote: Lamar Owen wrote: As a matter of fact, I am an ordained Baptist minister. Don't know about Bruce -- other than I like his catchy .sig... :-) Wow, pretty cool. I am just an underling. :-) Well, we're all underlings. At most I can be an undershepherd (as a 'pastor' is -- 'pastor' comes from the same root as 'pasture' -- one feeds, the other is the place of feeding). Well, other than the marriage thing. You do have to have 'the document' to do that. If anyone asks about my .sig, I witness accordingly. Otherwise, I'm not pushy -- not in this venue, at least. Becoming a Christian was the best thing that ever happened to me, and I want to share that, but I don't want to make people uncomfortable either. Your .sig is ideal for this venue. And your choice of names for your children make it pretty well obvious where your heart lies. :-) And I've been in enough Usenet 'discussions' to know what is and is not appropriate. And I've preached enough to enough congregations to, well, have a feel for when it's over the line. And I do get rather 'energetic' in _that_ venue. And accepting Christ was by far the best thing I've ever done. But, to go back on topic, PostgreSQL isn't a religious vehicle, either way. However, if we're going to call it 'Before Common Era' then our date routines really need to use the BCE abbreviation -- otherwise, call BC 'Before Christ' -- although it becomes more than a little paradoxical when you realize after much study (in particular, the times Cyrenius was governor of Syria that intersect with the time Herod the Great was still alive (he died in 4 BC according to most scholars)) that the historical Jesus was most likely born anywhere from 6 to 4 _BC_, making the abbreviation more than a little eyebrow-raising. (ever heard a computer programmer/engineer preach :-)) God didn't set the calendar date -- a man did, 1600 or so years ago. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 http://www.wgcr.org/about_us/who/lamar.htm ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Very slow query, Help please!
Hi ! Help me please to resolv my problem. I have two tables . One of them is large (say 10 records) with unique index on "ID" and the second table (5000 records ) which i have to insert into the first table , but the second table have much records, which have many duplicate values in "ID" . and this is the query which i used for insertion: insert into LTable select * from STable where ID not in (select ID from LTable ) this query takes much time. and moreover - for big tables i couldn't got result of query for about an hour, it looks like so that it is die... May be there is anoter way for insertion ? Thanks for any suggestion! Igor ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Deadlock detected
Hi Tom, [EMAIL PROTECTED]">"Brian J. France" [EMAIL PROTECTED] writes: I am getting a few of these errors in my web logs and didn't know what I could do to stop it. NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. Error in query "UPDATE table SET field = value WHERE field2 = value2" : ERROR: WaitOnLock: error on wakeup - Aborting this transaction Is this due to the hash index on field2 or due to the has index in general? Don't use hash indexes for concurrent applications. I don't really knowof any reason for preferring a hash index over a btree index in any case. regards, tom lane Could you expand a little on the subject of Hash Vs BTree indexes? And in particular "Don't use hash indexes for concurrent applications". I posted a question about deadlocks a week or two ago and I was advised to upgrade to 7.0.3 (from 7.0.2). I did, but I still get a few deadlocks (i.e. all the backends eventually remain locked in requests). I am using a lot of Hash indexes because they usually provide faster access to a specific record than binary trees (O Vs OlogN). Most of my requests simply lookup a record based on some specific account or transaction ID. If using BTree instead of Hash indexes does not affect performance and solves my deadlock problem, please let me know! Thanks. --Maurice
RE: [GENERAL] Very slow query, Help please!
You should rewrite your query using NOT EXISTS: insert into LTable select * from STable where NOT EXISTS ( SELECT L.ID FROM LTable L WHERE L.ID = ID); This will use an index scan on LTable. This is also a FAQ item BTW. Hope that helps, Mike Mascari [EMAIL PROTECTED] -Original Message- From: Igor [SMTP:[EMAIL PROTECTED]] Sent: Saturday, April 14, 2001 12:33 AM To: [EMAIL PROTECTED] Subject:[GENERAL] Very slow query, Help please! Hi ! Help me please to resolv my problem. I have two tables . One of them is large (say 10 records) with unique index on "ID" and the second table (5000 records ) which i have to insert into the first table , but the second table have much records, which have many duplicate values in "ID" . and this is the query which i used for insertion: insert into LTable select * from STable where ID not in (select ID from LTable ) this query takes much time. and moreover - for big tables i couldn't got result of query for about an hour, it looks like so that it is die... May be there is anoter way for insertion ? Thanks for any suggestion! Igor ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
