Re: [GENERAL] Any Plans for cross database queries on the same server?

I actually disagree, mildly. Our system uses two variants of two types of data. Client data has a presence in the billing database, but has an incarnation in our runtime servers to allow for authentication. Not the same databases, since we can't afford the extra time for the hop, which might be

Re: [GENERAL] Spam from EnterpriseDB?

In 2007 however, the punishment ought to be ... ?? ;-) Greg Williamson DBA GlobeXplorer LLC, a DigitalGlobe Company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information an

Re: [GENERAL] Spam from EnterpriseDB?

I got one as well ... not a big deal in my mind since it's only happened once (weekly would be offensive), and it's not entirely out of the realm of possibility that we'd have use for their services (I try to keep an update list of possible resources for my employers in the event that I get hit

Re: [GENERAL] Performance with very large tables

Limit is somewhat magical ... at least to a degree. Not sure about cursors since I am not currently using them. select count(*) from bill_rpt_work; count - 2317451 (1 row) Time: 1709.829 ms billing=# \d bill_rpt_work Table "reporting.bill_rpt_work" Column |

Re: [GENERAL] Confused by misleading error message on SP creation

Doh ! Thanks for the swift response ... bet you are right. Getting punch drunk on too many electrons ... G -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Tue 1/9/2007 3:55 AM To: Gregory S. Williamson Cc: pgsql-general@postgresql.org

[GENERAL] Confused by misleading error message on SP creation

This is PostgreSQL 8.1.4, and I am utterly lost. This function started in Informix and worked perfectly. Migrating to postgres resulted in about a *10,000* x slow down as it resorts to doing a sequential scan. In the sql below the addition of client id and report id to the initial select are an

Re: [GENERAL] queueing via database table?

Mark -- As others have indicated, there may be some blocking issues with the approach you outlined. A variant I have seen used in the past uses a table with a unique id for the job, the work queue it is in, a status flag, priority and at least one time stamp (and perhaps space for a process id

Re: [GENERAL] a question on SQL

Bruce Momjian wrote: > > Tom Lane wrote: > > "Greg Sabino Mullane" <[EMAIL PROTECTED]> writes: > > >> It's the single most useful non-standard SQL feature postgresql has. It > > >> is thus simultaneously bad (from a portatbility aspect) and brilliant > > >> (because it's a million times easier and

Re: [GENERAL] MySQL drops support for most distributions

FWIW, there is a follow-up note on the original posting from a MySQL person: "we are just starting to roll out [Enterprise] binaries... We don't build binaries for Debian in part because the Debian community does a good job themselves... If you call MySQL and you have support we support you if y

Re: [GENERAL] pg_dump/pg_Restore 8.1.5 db -> 8.2 tsearch2 problem

Henrik -- I have only dabbled in tsearch2 so I can'toffer direct advise, but this looks like the problem when upgrading the postGIS stuff ... tsearch2 might have an upgrade functionality (postGIS does), but you can also do a more lbaorious method that strips out the unwanted tsearch2 definition

Re: [GENERAL] select query not using index

Vivek -- If you could let people know what version of postgres, and which OS, it might help. A guess: the planner sees that there are very few rows and decides that a sequential scan is faster (this is because a sequential scan on a table with only a few rows is probably done in one operation;

Re: [GENERAL] postgresql bug

/27/2006 4:27 PM To: Mark Leet; Gregory S. Williamson; [EMAIL PROTECTED] Cc: Subject:Re: postgresql bug Hi Mark, It seems our problem was our Legato/EMC Networker backup process. Due to an incorrect exclusion directive, it was locking the data files during the nightly backup.

Re: [GENERAL] Eliminating bad characters from a database for upgrading from 7.4 to 8.1

sting bandwidth for what seems to have an RFTM question! G -Original Message- From: mike [mailto:[EMAIL PROTECTED] Sent: Thu 11/16/2006 7:49 PM To: Gregory S. Williamson Cc: Russell Smith; pgsql-general@postgresql.org Subject:Re: [GENERAL] Eliminating bad characters f

Re: [GENERAL] Eliminating bad characters from a database for upgrading from 7.4 to 8.1

. Williamson Cc: pgsql-general@postgresql.org Subject:Re: [GENERAL] Eliminating bad characters from a database for upgrading from 7.4 to 8.1 Gregory S. Williamson wrote: > Dear list, > > I have been banging my head against a problem for a few days now, and > although I am making

[GENERAL] Eliminating bad characters from a database for upgrading from 7.4 to 8.1

Dear list, I have been banging my head against a problem for a few days now, and although I am making progress it is painfully slow, and I am hoping that some one out there can steer me in a better way. I've got a medium size database (7.4), about 64 gigs of data, about 1/3rd of which is in on

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

I am admittedly speaking up somewhat late here, and may be completely off base, but it seems to me that the "LIKE" operation is almost always going to be a loser, performance-wise, when there is an initial wildcard, e.g. "%superman re%" will require a sequential scan, while "superman re%" would

Re: [GENERAL] converting Informix outer to Postgres

Perhaps a more recent version of postgres (8.1 or mayber even look at 8.2 ...); lots of improvements since 7.1, IIRC in the area of joins specifically, but I don't know the answer to your question specifically. HTH, Greg Williamson (a [mostly] former Informix user, but not, alas, with such que

Re: [HACKERS] [GENERAL] Index greater than 8k

I hesitate to mention it, since it's retrograde, uses OIDS, may not handle your locale/encoding correctly, may not scale well for what you need etc., etc. But we've used fti (in the contrib package) to do fast searches for any bit of text in people's names ... we didn't go with tesearch2 because

Re: [GENERAL] old duplicate emails

I've seeing a few ... one just now dated 10/17 on conversion and Oracle ... G -Original Message- From: [EMAIL PROTECTED] on behalf of Richard Broersma Jr Sent: Fri 10/20/2006 8:43 PM To: General PostgreSQL List Cc: Subject:[GENERAL] old duplicate emails It is just me,

Re: [GENERAL] Question with tsearch2 (or it might be a general one too)

Ritesh -- You are correct in thinking that "@@" is a special operator defined for tesearch2; it uses the GIST indexes to do a search, but more than that I can't say, since I am not really familiar with tsearch2. (In the postGIS world there is a vaguely equivalent operator, "&&", again using pos

Re: [GENERAL] Maximum size of database

Roopa, I think that by defintion a "SELECT * FROM ;" will always take longer as the table increases in size (but if anyone who is more versed in theory of searches, sorts, etc. contradicts me I'll happily listen!). Note that the database could increase enormously with no direct effect on speed;

Re: [GENERAL] Postgres Database Slow

Please do not simply repost your obscure and almost meaningless original question. Please respond to the earlier posts asking for more information. People might be willing to help, but they can't unless you respond to them. Greg Williamson DBA GlobeXplorer LLC -Original Message- From:

Re: [SQL] [GENERAL] How to split a table?

Perhaps something like: CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60); ? HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Andreas Kretschmer Sent: Tue 10/17/2006 1:34 AM To: pgsql-general@postgresql.org; pgsql-sq

Re: [GENERAL] postgres' web site malfunctional ?

Thanks muchly! Content is back now, formatting is whack, but I can use it again. G -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Sun 10/15/2006 8:27 PM To: Bill Hawes Cc: Gregory S. Williamson; pgsql-general@postgresql.org; PostgreSQL WWW Subject

Re: [GENERAL] postgres' web site malfunctional ?

Nope, neither Firefox nor IE 6.0 get anything from this site. :-( G -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Sun 10/15/2006 6:53 PM To: Gregory S. Williamson Cc: pgsql-general@postgresql.org Subject:Re: [GENERAL] postgres' web

[GENERAL] postgres' web site malfunctional ?

Then I go to I get a blank page ?!? No bytes ... has lost my marbles, my browser (FireFox 1.5.0.7) lost its electrons, or ?? Thanks, Greg Williamson DBA GlobeXplorer LLC ---(end of broadcast)--- TIP 6: explain analyze

Re: [GENERAL] Storing images in PostgreSQL databases (again)

Hate to suggest corporate software, but there is an Informix/Illustra blade that could do something like what you're after (I remember a demo of sunset/sunrise photos being selected on the basis of color values) ... But I think they used smart blobs and didn't use them as key values. G -Or

Re: [GENERAL] Storing images in PostgreSQL databases (again)

FWIW, the company I work for stores its terrabytes of imagery on disk, using a database to track them (spatial coordinates, metadata, location, etc.); I have worked on projects in which we stored images in a database (blobs in Informix) and it worked fine. Both approaches can have their merits.

Re: [GENERAL] Cause of ERROR: could not open relation 1663/856689/856777: Invalid argument?

Wyatt -- We got a spate of similar errors recently; turned out to be a disk was not mounted properly. Once it was reseated all was well. You might also do a RAM check just to make sure that something isn't wonky there. IIRC, I was told (see the archives of the postgres admin mail list) that thi

Re: [GENERAL] Database design and triggers...

Roman -- You can certainly use a trigger to track changes (as well as creating an audit trail and enforcing rules); performance on inserts,updates and deletes will suffer accordingly since there's extra operations involved, but it definitely be a winner on the other end in generating reports an

Re: [GENERAL] How to access a table from one database to another database

Possible dblink, in the ./contrib directory would help ? I have never had to use it but it seems like it might be what you need. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of VivekanandaSwamy R. Sent: Fri 7/14/2006 9:56 PM To:

Re: [GENERAL] apparent wraparound

Reece -- The "number of slots needed exceeds" messages are telling you that the current FSM (Free Space Map) does not have enough space allocated to track all of the old tuples that are to be reused. I suspect that having such a situation would effect the wraparound issue, since you'd have "dea

Re: [GENERAL] Help with storing spatial (map coordinates) data?

We have had good success with postGIS for storing various spatial data sets (polygons, points and lines). They can be found at . We store our data in lat/long but postGIS has many different spatial reference systems defined and I would suspect that minutes/secon

Re: [GENERAL] Performance

Bert -- You don't include some necessary details: What version of postgres ? (8.x is noticably better at that OR / IN stuff I think, at least to some degree) Please include some info about your setup -- what are the settings in the postgres config file (effective_cache_size, random_page_cost

Re: [GENERAL] Truncate and Foreign Key Constraint question

Doh ! A test schema that was a left over. Thanks for the sanity check ... as usual, pilot error! g -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wed 4/12/2006 10:02 PM To: Gregory S. Williamson Cc: pgsql-general@postgresql.org Subject:Re

[GENERAL] Truncate and Foreign Key Constraint question

This is in postgres 8.1: PostgreSQL 8.1.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) I've got a table in one schema (work.client_collect_rates) which has an FK constraint with a table, content.collections_l (definitions shown below). There's a

Re: [GENERAL] Guidelines for upgrading from pgsql7.4.xxx server to pgsql8.xxx server

As others have pointed out, OID dependant tables may need special attention. We recently upgraded from 7.4 to 8.1.x and found some issues with encoding -- 8.1 defaulted to a different encoding and we had some data that was invalid; we manually fixed the old data before retrying the export. Make

Re: [GENERAL] Is there a way to list running queries

Frank Church caused electrons to display this: > > Is there a way to list running queries in PostgreSQL, especially those that > appear to have gone away or don't seem to be running at all? You should always include relevant info such as which version of postgres you are using. If the stats are

Re: [GENERAL] Detaching database

> > "Gregory S. Williamson" <[EMAIL PROTECTED]> wrote: > > Petr, > > > > As long as the new server is the same operating system, and the versions of > > postgres are the same, > <...> > > As a clarification, 'versions ar

Re: [GENERAL] new beginner to postgresql. Looking at it for a church

It sounds as if reliability of the data is of some importance (usually is in financial applications), and personally, I wouldn't trust mySQL with such data -- the inability to have clean transactions, its willingness to insert data that doesn't match what the original input was with throwing an

Re: [GENERAL] Detaching database

Petr, As long as the new server is the same operating system, and the versions of postgres are the same, you can do a binary copy of the data directory and move it to the new machine, point the new server's postgres to the copied and data and start it up. Indexes, statistics, etc. all are intac

Re: [GENERAL] outdated (bad) information in pg_stat_activity

Jim Nasby wrote: > > I too have had issues with "stuck" entries in the pg_stat_activity view -- > > > the real pid is long gone but postgres still thinks the process is there. > > > > It would be real sweet to have a way of cleaning this table with bringing > > > postgres off-line. > > Rather

Re: [GENERAL] is this a bug or I am blind?

e=# select gex_clientname from gex_clients where gex_clientname like 'HomeHappinesses'; gex_clientname (0 rows) > > Gregory S. Williamson wrote: > > > > I am puzzled by the lack of a "%" in the LIKE query. When I try this on > > p

Re: [GENERAL] is this a bug or I am blind?

> > If you look my 3rd query, you will see that there are no spaces, however: > > select *, length(username), length('potyty') from common_logins where username like 'potyty'; > uid | username | password | lastlogin | status | usertype | loginnum | length | length > ---

Re: [GENERAL] outdated (bad) information in pg_stat_activity

I too have had issues with "stuck" entries in the pg_stat_activity view -- the real pid is long gone but postgres still thinks the process is there. It would be real sweet to have a way of cleaning this table with bringing postgres off-line. My $0.02 worth ... Greg Williamson DBA GlobeXplorer

Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

Jim C. Nasby was quoted as saying: > > Jim C. Nasby wrote: > > > Of course one flip-side to all this is that if Oracle does attack us it > > > actually lends credibility; it means they see PostgreSQL as a threat. At > > > this point that could do more good for us than harm, depending on how > > > e

Re: [GENERAL] PostgreSQL Gotchas --- count()

>> Gregory S. Williamson wrote: >>> [ re COUNT(*) ] >>> On Informix however it is blindingly fast, and can also be instantly >>> conjured with the dbaccess tool (Info/Table/Status). They might be >>> stashing this count somewhere, but it is not available

Re: [GENERAL] PostgreSQL Gotchas --- count()

On Informix however it is blindingly fast, and can also be instantly conjured with the dbaccess tool (Info/Table/Status). They might be stashing this count somewhere, but it is not available when the table is locked, as during a load. However they do it, performance does not seem to suffer, and

Re: [GENERAL] Quotation marks in queries

This is only really necessary if the table or column names are mixed cases or include spaces or some such ... normally (?) this is not required. PostgreSQL relentlessly lower cases such names unless they are double quoted. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- F

Re: [GENERAL] Privileges question

James -- I am not sure if anyone answered this yet, but you might try: GRANT USAGE ON SCHEMA sma_apps TO sma_user; as the same user as createdthe schema ... HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: James Moe [mailto:[EMAIL PROTECTED] Sent: Mon 2/14/2005 10:

Re: [GENERAL] migrating from informix

Jaime -- Sorry that I didn't respond earler -- been quite busy. We have migrated our runtime aspect (which is heavy use of the Spatial Blade/postGIS in a mostly read-only environment); we were using Informix 9.3 dynamic server. I have some notes I wrote up that I'll post tonight -- they are on

[GENERAL] test

The list is either down or very quiet ? G ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] why use SCHEMA? any real-world examples?

As other posters have indicated, there's a convenience factor and an advantage to compartmentalizing data. In our case we don't care so much about user rights (a very useful aspect in and of itself), but more for performance issues. We have some applications that use a lot of detailed data abou

Re: [GENERAL] null value of type java.sql.Time

Try: SELECT * FROM event WHERE game_clock IS NULL; Greg Williamson DBA GlobeXplorer LLC -Original Message- From: phil campaigne [mailto:[EMAIL PROTECTED] Sent: Monday, November 22, 2004 2:33 PM To: [EMAIL PROTECTED] Subject: [GENERAL] null value of type java.sql.Time Occasionally I wa

Re: [GENERAL] Mysterious Death of postmaster (-9)

ailto:[EMAIL PROTECTED] Sent: Sat 11/13/2004 3:06 PM To: Gregory S. Williamson Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Mysterious Death of postmaster (-9) On Sat, Nov 13, 2004 at 02:39:38PM -0800, Gregory S. Williamson wrote: Gregory, > We had an oddness today with o

[GENERAL] Mysterious Death of postmaster (-9)

Dear peoples, We had an oddness today with one of of postgres servers (Dell 2 CPU box running linux) and postgres 7.4. The server was under heavy load (50+ for a 1 minutes spike; about 20 for the 15 minute average) with about 250 connections (we still don't understand the heavy load itself). L

Re: [GENERAL] Conactenating text with null values

Sweet. I learn something every day. thanks for ideas, one and all! G -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Fri 11/5/2004 8:49 AM To: Csaba Nagy Cc: [EMAIL PROTECTED]; Gregory S. Williamson; Postgres general mailing list Subject:Re

Re: [GENERAL] Conactenating text with null values

[mailto:[EMAIL PROTECTED] Sent: Fri 11/5/2004 2:15 AM To: Gregory S. Williamson Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Conactenating text with null values On Fri, 2004-11-05 at 09:25, Gregory S. Williamson wrote: > This is postgres 7.4 on a linux box ... > > I have driven

[GENERAL] Conactenating text with null values

This is postgres 7.4 on a linux box ... I have driven myself to distraction trying to what ought to be easy. I have a table with house number, street direction, street name and street suffix as 4 columns. I want to paste them together as one text string for use by another application. SELECT s

Re: [GENERAL] primary key and existing unique fields

-Original Message- From: Robby Russell [mailto:[EMAIL PROTECTED] Sent: Tue 10/26/2004 9:08 PM To: Kevin Barnard Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] primary key and existing unique fields On Tue, 2004-10-26 at 22:03 -0500, Kevin Barnard wrote: > On Tue, 26 Oct 200

Re: [GENERAL] Setting search paths inside a function (plpgsql)

Doh ! Thanks for the insight. Greg -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wed 9/29/2004 8:40 AM To: Gregory S. Williamson Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Setting search paths inside a function (plpgsql) "Gregory S. Willi

[GENERAL] Setting search paths inside a function (plpgsql)

Dear peoples, I've got a problem which seemed to be neatly solved by the use of schemas, and in fact it mostly works, but I have tried to go one step too far, perhaps. Rather than have the application do SET search_path TO f12057; SELECT * FROM parcel-owners WHERE ... ; SET search_path TO publi

[GENERAL] test message -- wondering if the list is down -- please delete

Sorry to waste bandwidth but I've never seen a day with no messages. G ---(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: [GENERAL] Killing a session

kill -15 pid# will kill a given thread ... you can look in the pg_stat_activity; the query_start shows when it started, the current_query shows at least a portion of the SQL the thread is executing, and procpid if the pid of the process to kill with a -15 command. Not sure about Windows varian

Re: [GENERAL] Trying to minimize the impact of checkpoints (resend)

There is something wonky on this mail list. I did not send this. -Original Message- From: Gregory S. Williamson Sent: Fri 6/11/2004 2:10 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Trying to minimize the impact of checkpoints (resend) In-reply

Re: [GENERAL] "Idle in Transaction" and hung connections

Thu 4/29/2004 7:27 PM To: Gregory S. Williamson Cc: Tom Lane; [EMAIL PROTECTED] Subject:Re: [GENERAL] "Idle in Transaction" and hung connections On Thu, 29 Apr 2004, Gregory S. Williamson wrote: > Tom -- > > Thanks for the suggestion, and the rapid respons

[GENERAL] "Idle in Transaction" and hung connections

Dear peoples, Periodically we are getting runaway postgres processes on our Linux (2.4.21-0.13 on Dell servers), using 7.4 and GIS (0.8 USE_GEOS=1 USE_PROJ=1 USE_STATS=1). All of the queries come in from remote servers using JDBC/proxool; once every 4 hours we have a process on the client side

[GENERAL] FW: Postgres 7.4 runing on BSD

If anyone has any advice on building Postgres 7.4 for a freeBSD box it sure would be welcome. TIA, Greg Williamson DBA GlobeXplorer LLC -- well, it takes gmake to build Postgres.. having trouble installing 'gmake' on the BSD box; it started to do its BSD autoinstall thing

Re: [GENERAL] PostgreSQL versus MySQL

"Oracle = Saturn IV." ?!??? Perhaps they claim to be. More like a shuttle with pretensions. Oracle was utterly unable to support our web site. And then they wanted a truely preposterous sum for their wretched software. Informix, on the other hand, has performed like, well, like a Saturn [which

Re: [GENERAL] setting last_value of sequence

Perhaps: SELECT SETVAL('resrc_serial', MAX(resource_id)) FROM ia_resources; the sequencethe column the table This sets the sequence to the highest number after I have used "copy" to load a table; other values instead of MAX() could be used (e.g. 123456, etc.). HTH

Re: [GENERAL] Fast Table Replication / Mirroring

see contrib/dbmirror ... triggered by updates on the primary. Don't know if it would work for you ... not sure about the second part (see the differences). It uses a table it creates called "Pending" to store transactions. Greg W. -Original Message- From: Alex [mailto:[EMAIL PROTECTED

[GENERAL]

One of our sysads sent this link ... wondering if there is any comment on it from the world of actual users of linux and a database. Greg Williamson DBA GlobeXplorer LLC ---(e

[GENERAL] is linux ready for databases ? (Ziff Davis article on Oracle and Linux)

Perhaps a subject line would help ... -Original Message- From: Gregory S. Williamson Sent: Monday, August 25, 2003 2:28 PM To: '[EMAIL PROTECTED]' Subject: One of our sysads sent this link ... wondering if there is any comment on it from the world of actual users of l

[GENERAL] High volume use of postGres

I've been charged with investigating postGIS as a replacement for our current Informix runtime (9.3), which uses the Spatial blade. Currently we use two Sun Sparc/Ultra 80 boxes with 3 CPUS dedicated to Informix. We use our database to calculate the image files we have within a given area (ST

Re: [GENERAL] One table in two different databases

Perhaps: dblink - Allows remote query execution by Joe Conway <[EMAIL PROTECTED]> This is under the contrib directory in your source distribution. I haven't done much with it, but it seems to allow access to tables not in the current database. HTH, Greg Williamson -Origina

Re: [GENERAL] [ADMIN] Call to undefined function: pg_connect()

I don't have a clue about which is which, but I bet providing some dates (ls -l) would help figuring out which is the most recent vs. oldest) ... might provide helpful information to people who know more about compiling this beast. Greg Williamson -Original Message- From: shreedhar [