[GENERAL] How identify a long running transaction

is not blocked by a long running read committed transaction but only by the oldest serializable transaction? Any references to relevant documentation would be appreciated. Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] limiting resources to users

your existing system is nearing overload if it can't maintain reasonable performance during concurrent loads. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How use input parameter as path to COPY in function?

I am missing something basic. How can I use an input parameter as the destination path in a COPY statement in a function. The following fails with a syntax error at or near the parameter. CREATE OR REPLACE FUNCTION dvd.export_tables(IN export_path text) RETURNS void AS $$ BEGIN copy dvd.genr

Re: [GENERAL] How use input parameter as path to COPY in function?

Thanks Tom. As usual, I learned a lot more from your reply than just the answer to my question . Bill Tom Lane wrote: Bill Todd writes: I am missing something basic. How can I use an input parameter as the destination path in a COPY statement in a function. plpgsql can only

Re: [GENERAL] quote string exactly as it is

My solution was to set standard_conforming_strings = on in postgresql.conf. Bill dario@libero.it wrote: Hello, How can I quote a string *exactly* as it is? I tried using quote_literal() but it doesn't return what I need in some cases. E.g. If my string is: ss\\\ss And I do: s

Re: [GENERAL] Excessive (and slow) fsync() within single transaction

know the details off the top of my head. What are the two best platforms for PostgreSQL in your opinion? Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

erting you to code that will stop working when that happens. In any event, you can work around this using the string escape syntax (i.e. WHERE colname ~ E'^\s*$') and the official documentation is here: http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-STR

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

havior with configuration settings in your postgresql.conf. standard_conforming_strings is the most dramatic example. And please don't top-post. > On Wed, Dec 23, 2009 at 3:32 PM, Bill Moran wrote: > > In response to "Patrick M. Rutkowski" : > > > >>

Re: [GENERAL] Session based transaction!!

curs. Personally, I would recommend coming up with a different approach, but I might be wrong. -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Session based transaction!!

S Arvind wrote: > > Web application have single DB only.. I'm unsure what you mean by that and how it relates to my answer. > On Fri, Dec 25, 2009 at 7:03 PM, Bill Moran wrote: > > > S Arvind wrote: > > > > > > Hi Everyone, > > > > > &

Re: [GENERAL] DDL commands take forever

holding transactions open for long periods of time, which is a bad idea anyway since it interferes with vacuum and other maintenance operations. You can look at the pg_locks table to see what locks are out, to help you diagnose what apps are holding locks open. -- Bill Moran http://www.potentialtec

Re: [GENERAL] Save MySQL? HA

talking to a MySQL > database in, say, PHP, does not require the program itself being GPL, > only the PHP library that connects to MySQL. It's the library that > "links" to MySQL, not your program, so your program is protected from > GPL's viral nature. Which makes s

Re: [GENERAL] please help

[5432]: 5433 > Username [postgres]: sho > psql: FATAL: no pg_hba.conf entry for host "10.1.1.86", user "sho", database > "v > iewods", SSL off http://www.postgresql.org/docs/8.4/interactive/auth-pg-hba-conf.html -- Bill Moran http://www.potentialtech.com

Re: [GENERAL] Self-referential records

want to set the isolation level to serializable, but I'm not sure if that's necessary. -Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Possible to set postgres in case insensitive mode ?

comparison. All of these conform to what I'm used to in other database systems. If the above doesn't answer your question, perhaps provide a little more detail about what you're trying to do and where you're having problems. -- Bill Moran http://www.potentialtech.com http:/

Re: [GENERAL] Unusual table size and very slow inserts

that the table blows up to 180x the minimum size is a hint that you may be updating very inefficiently.) * Tune PostgreSQL to make more efficient use of RAM for caching (which will speed everything up) * Get faster hardware * Upgrade to a newer version of PostgreSQL that has more efficient

[GENERAL] pg_dump superflous warning message

even when it's not applicable. At least from my perspective, I don't think this warning should be printed if the proper command line switches have been given. Is there a good reason to print it anyway? This is with pg 8.4.2 (both client and server). -- Bill Moran http://www.po

Re: [GENERAL] pg_dump superflous warning message

In response to Scott Marlowe : > On Thu, Feb 11, 2010 at 10:08 AM, Bill Moran wrote: > > > > If I do: > > pg_dump -a --disable-triggers > > > > I get a warning message: > > pg_dump: NOTICE: there are circular foreign-key constraints among these > > t

Re: [GENERAL] db size and VACUUM ANALYZE

erforming slow will usually indicate where indexes can help. >From there, you may simply have too little hardware for the database to run at the speed you expect. Giving it more RAM is cheap and tends to work wonders. Any time the system runs out of RAM, it needs to use disk instead, which s

Re: [GENERAL] Cast char to number

hars as needed. Basically reverse what you're doing. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cast char to number

t; > I think the cast in step (ii) might not be necessary - not sure about this. Agreed. There's a slightly shorter way, you can do: ALTER TABLE tablename ALTER COLUMN columnname TYPE INT; If that doesn't work because the cast isn't automatic, you can add a USING cl

Re: [GENERAL] Cast char to number

uot;id" cannot be cast to type "pg_catalog.numeric" > postgres=# Remember that what comes after the using clause can be arbitrarily complex (I have written ALTER TABLE statements with USING CASE ... that are pages and pages long to fix data consistency problems in the same

Re: [GENERAL] Cast char to number

that the serial number is really just a number, and you actually want to be able to do math on it because you can find out the year the part was designed by dividing by 1000 or something. You make the best decisions you can based on the available information. If you get it wrong, there's al

[GENERAL] Automatic index advisor?

t provide this kind of capability? Regards, Bill Karwin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Automatic index advisor?

to a point that would be useful would be a complex project. Regards, Bill Karwin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Work Mem Allocation Questions

ork_mem. The docs have a little more detail on this. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Licence

to the PostgreSQL developers" rather than "if > you distribute, you must not charge a fee" The "without fee" part means that you don't owe anyone a fee for doing so. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent

Re: [GENERAL] Who is locking me?

tup, the client IP alone might tell you what you need to know. If that's not enough, you should be able to use the client port # on the client's system to track down what process is on the client end (again, using netstat) If you're not on a POSIX system, you can probably still do w

Re: [GENERAL] How long will the query take

o know how long your query will take, but keep in mind it's only an _estimate_. Given that, in my experience EXPLAIN is pretty accurate 90% of the time, as long as you analyze frequently enough. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~

Re: [GENERAL] How long will the query take

In response to Andreas Kretschmer : > Bill Moran wrote: > > > > No, not really. But you can (and should) run EXPLAIN to > > > obtain the execution plan for that query, und you can show us this plan > > > (and the table-definition for all included tables). Ma

Re: [GENERAL] Select in temporary table

between the two runs b) Your application establishes multiple connections at some point and uses a different one on the second run It may be deep in the underlying libraries that this is happening. I have seen examples of code that establishe dozens of database connections for a single applic

Re: [GENERAL] Fuzzy string matching of product names

's built-in regex support may have a role to play too. > > I can live with it not being indexable, because typically there are > only tens of thousands of products in a production system. > > Could someone suggest an approach that is reasonably simple and > reasonably generic ?

Re: [GENERAL] Fuzzy string matching of product names

In response to Peter Geoghegan : > > http://www.postgresql.org/docs/8.4/static/fuzzystrmatch.html > > > > -- > > Bill Moran > > http://www.potentialtech.com > > http://people.collaborativefusion.com/~wmoran/ > > Fuzzystrmatch is generally used to compa

Re: [GENERAL] [offtopic] How do you name a table...

week is a good candidate for an enum ... when's the last time that changed? For lookup tables that aren't static enough to be an enum, it usually ends up appended with _list (i.e. gender_list, county_list, etc) -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion

Re: [GENERAL] [ADMIN] Query is stuck

vacuum analyze on the table repcopy In addition to that, indexes on report_time, report_status, and dm_user might help. And your query is not "hung", it's just taking a LONG time. Based on the explain, it could take several hours to complete. How many rows are in repcopy? What is your vacuum schedule? Do a vacuum verbose, if the number of dead rows is very high on that table, you may benefit from doing a VACUUM FULL + REINDEX or CLUSTER on the table. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query is stuck

is output, you _really_ need to add some indexes to this table. Read back through earlier messages in the thread for suggestions. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] auto vacuum

th of them so you have complete control over when they run. I did a couple of quick tests, and it doesn't seem as if a vacuum running on one connection blocks an analyze running on another connection. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~

Re: [GENERAL] Query is stuck

a rewrite, but I've found that I simply can't understand what it is you're trying to accomplish with that query. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] auto vacuum

In response to Herouth Maoz : > First, I'd like to thank Bill and Alvaro as well as you for your replies. > > Quoting Tom Lane: > > Hmm. Given the churn rate on the table, I'm having a very hard time > > believing that you don't need to vacuum it pretty dang

Re: [GENERAL] auto vacuum

roblem. Of course, the question is "how often is often enough" and again, the answer is dependent on a number of usage factors such that it's difficult to offer any advice other than, "Try some different settings and see how it turns out". However, give

[GENERAL] Need Help With a A Simple Query That's Not So Simple

type farm_idforeign key to farms size crop_cd0041 = corn 0081=soybeans ...year ... Any help would be much appreciated. TIA, - Bill Thoen

Re: [GENERAL] Need Help With a A Simple Query That's Not So Simple

On 10/31/2011 5:05 PM, David Johnston wrote: From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Thoen Sent: Monday, October 31, 2011 6:51 PM To: Postgrresql Subject: [GENERAL] Need Help With a A Simple Query That's Not So Simple [...] Wha

Re: [GENERAL] Re: [pgsql-general] Need Help With a A Simple Query That's Not So Simple

Thanks! Half the problem searching the 'Net for answers is knowing what it's called. Regards, Bill Thoen GISnet http://gisnet.com 303-786-9961 On Nov 1, 2011, at 10:01 AM, "James B. Byrne" wrote: > >> Date: Mon, 31 Oct 2011 16:51:02 -0600 >> From: Bill Th

Re: [GENERAL] Why does autovacuum run in so small blocks?

here were active transactions that prevented vacuum from cleaning up the dead tuples, so it came back later and was able to clean them up at that time. Would need to do a little more in-depth research/monitoring to determine if that guess is correct or not. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] monitoring sql queries

On Thu, 17 Nov 2011 14:32:22 -0700 "J.V." wrote: > How is this accomplished? The best way that I know if is to use pgFouine. The documentation for pgFouine should get you started. HTH, Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make ch

[GENERAL] Using the internal data dictionary

here's not some innocent looking table in there that acts as a doomsday device if you so much as read its first record, etc. I'm just not sure why this isn't more widely used or talked about. Regards, Bill Thoen GISnet http://gisnet.com 303-786-9961 -- Sent via pgsql-general ma

Re: [GENERAL] Using the internal data dictionary

Thanks, guys! I'll take a closer look at the information_schema and pgAdmin and Maestro. Reinventing the wheel isn't a problem as this job is not critical, but the educational experience in looking at the system from another POV may be the bigger prize. - Bill On 11/17/2011 8:34

Re: [GENERAL] Significant Digits in Floating Point Datatype

ess than 15? Given Tom's answer, you may want to consider whether the DECIMAL data type is a better fit for your needs. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Permission Problems

r what am I missing? Is there a better way to tranfer info between my database and the web than by using a generic account? It sure seems like I'm granting too much access to too little a player. Any advice would be welcome. TIA, - Bill Thoen

Re: [GENERAL] General coding question

gs, but it can be difficult to do correctly, depending on the circumstances." -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL server architecture

e instances as seems most logical based on your observation of how they behave. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to escape to quotes on Insert into?

ons is bad news. If you're forced to write the raw SQL statements for some reason, you escape ' with a second ', so: INSERT INTO tablename (colname) VALUES ('http://host.com/cond''nast'); -- Bill Moran http://www.potentialtech.com http://people.collaborativefusi

Re: [GENERAL] Detecting uncommitted changes

f I'm understanding you correctly, you could just make it check the transaction status. If there's an active transaction, then there are "uncommitted changes". -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general

Re: [GENERAL] Update takes longer than expected

um lorem...' where id = 5; Will give you details on what's taking so long. If the output of that doesn't help, you can include it in an email to the list and people will provide details on what it means and advice on how to fix it. -- Bill Moran http://www.potentialtech.com

Re: [GENERAL] Schema version control

ne this is a common problem and > > > there're probably some neat solutions that we don't know about. http://dbsteward.org We've been using this for several years and it's made our schema versioning almost a non-issue. Finally got the go-ahead to release it just this

Re: [GENERAL] Logging access to data in database table

at's not enough. For example with HIPAA in the US, a user may be allowed to access data, but there still _has_ to be a log record for each access. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-genera

Re: [GENERAL] Logging access to data in database table

at's not enough. For example with HIPAA in the US, a user may be allowed to access data, but there still _has_ to be a log record for each access. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-genera

Re: [GENERAL] PG migration policy

first such document to put together, > > I am looking for pointers, samples and so on on which to build this > > document for our scenario. > > > > Allan. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To

Re: [GENERAL] Advisory transaction lock for 128-bit space

a unique 8 byte number that you can use the advisory lock on. You can use a sequence to automatically generate unique values for it, and as long as you don't exceed 2 billion rows, you'll be fine. Of course, if you expect that you might exceed 2 billion rows, that won't work e

Re: [GENERAL] Anonymized database dumps

ron (we have over 100 production databases) then changes the sensitive data, as well changing all the passwords to "password" so developers can easily log in as any account. During the day, the developers have access to all the sanitized dump files and can use them to make as many testing

Re: [GENERAL] Anonymized database dumps

potential data spillage issues as sanitizing the data. I find it intriguing, however, and I'm going to see if there are places where this approach might have advantages over our current one. Since much of our sensitive data is already de-identified, it provides an additional level of

Re: [GENERAL] How can I see if my code is "concurrency safe"?

ad of single click, thus resulting in the exact same queries running close enough together to conflict with each other. Of course, this can be fixed in many ways, but understanding why it happens is the first step to choosing the best approach for your application. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] equal and like

psql server version: 8.2.11 > > Could you help me? Just a guess, but does the problem go away if you do REINDEX table u; -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing lis

Re: [GENERAL] q query about a bug.

ve heard of no such bug. You might want to either get enough details to actually research the alleged problem, or hire a developer who isn't going to lie to you. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4

Re: [GENERAL] disallow updates on column or whole table

EFORE UPDATE trigger which compares old.column_name(s) to > new.column_name(s) and raise an exception if these values are different. > > Are there better ways? You can use the GRANT system to enforce table-wide restrictions. See the docs on GRANT and REVOKE and associated doc sect

[GENERAL] pgdiff equiv

asn't been maintained in 6 years. Does anyone know of anything with a similar feature set, but maintained so it works on modern (8.3) versions of Postgres? -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list

Re: [GENERAL] Why would I want to use connection pooling middleware?

;s true. My understanding of pgpool is that it will reuse an existing connection if it's free, or open a new one if required. -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why would I want to use connection pooling middleware?

. We had servers with 300 - 400 connections that did just fine. We've looked at pgpool, but deemed it not worth the added complexity. Quite honestly, why attempt to solve problems that don't even exist? Are you having a problem? If so, what is the problem? -- Bill Moran http://

Re: [GENERAL] Why would I want to use connection pooling middleware?

In response to Kirk Strauser : > On Jan 15, 2009, at 2:39 PM, Bill Moran wrote: > > > However, it pgpool can't pool connections if each connection has its > > own username. Not sure what exactly is causing it not to work for > > you, > > but that wa

[GENERAL] The difference between RESTRICT and NO ACTION

tailed explanation I would appreciate it. Thanks. Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] The difference between RESTRICT and NO ACTION

Bill Todd wrote: In discussing foreign key constraints the manual makes the following statement about the difference between RESTRICT and NO ACTION. "(The essential difference between these two choices is that |NO ACTION| allows the check to be deferred until later in the transa

Re: [GENERAL] Serial Jumping

you absolutely need consecutive #s, then serial is not for you and you should implement your own method of acquiring sequential numbers. -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: ht

[GENERAL] System table documentation

Where can I find documentation for the system tables? I have not found anything in the 8.3.1 documentation. Thanks. Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

settings: http://www.postgresql.org/docs/8.3/static/monitoring-stats.html -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/

Re: [GENERAL] Pet Peeves?

work, seems like a basic thing. So I am > forced to use "trust". How on earth does failure of md5 to work force you to use trust? How about crypt or password (password is pretty darn simple to set up). -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

". (^; > While you mention it, another "Pet Peeve" was the use of ident. Yikes. _My_ point was that a broken md5 (which is still under debate) doesn't force you to use trust. There are other auth options like crypt and password. -- Bill Moran http://www.potentialtec

[GENERAL] How relate pg_class to pg_constraint

I need to join pg_class and pg_constraint to get information about constraints on a table. It appears that pg_constraint.conrelid is the foreign key but I do not see a relid column in pg_class. What column(s) define the relationship between these tables? Thanks. Bill -- Sent via pgsql

Re: [GENERAL] How relate pg_class to pg_constraint

Bill Todd wrote: I need to join pg_class and pg_constraint to get information about constraints on a table. It appears that pg_constraint.conrelid is the foreign key but I do not see a relid column in pg_class. What column(s) define the relationship between these tables? Thanks. Bill Is the

[GENERAL] COPY questions

If the COPY command fails does it identify the offending row? After reading the manual and the wiki I assume that there is no way to tell copy to start with the Nth record in the input file. Is that correct? It seems like such an obvious feature I was surprised not to find it. Thanks. Bill

Re: [GENERAL] COPY questions

Adrian Klaver wrote: On Wednesday 18 February 2009 10:56:45 am Bill Todd wrote: If the COPY command fails does it identify the offending row? After reading the manual and the wiki I assume that there is no way to tell copy to start with the Nth record in the input file. Is that correct? It

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

same partition as where you've got the 50G free (I've made that mistake more than once) If none of those help, provide more details. Based on the detail level you've provided, you've run out of disk space. -- Bill Moran http://www.potentialtech.com http://people.collabora

Re: [GENERAL] Getting time-dependent load statistics

m is equally costly. Do > you have a suggestion for a better approach? Turn on stats collection and have a look at the various pg_stat* tables. They'll have stats that you can quickly access with considerably lower overhead. Doing it the way you're doing is driv

[GENERAL] Problem setting up PostgreSQL

oad from a different mirror, but always received the same error message. I searched for and found a prior reference to this message (Sept 04 2008) but ws unable to retrieve the full message and response. Can you offer any suggestions for dealing with this problem? Thanks, Bill Herbert --

Re: [GENERAL] max_fsm_relations

to track. Or, just raise the parameter and restart postgres. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] max_fsm_relations

ables, system tables? SELECT relname,reltype from pg_class; Make sure you do that query for every database and add them up. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] max_fsm_relations

peak time. The server's not going to meltdown immediately (unless you've got a _TON_ of update activity) -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

[GENERAL] Include script within a script

Is there any mechanism to include (call) one SQL script file from another? Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] nulls

d inside- joke language. Compare: SELECT * FROM duration WHERE dt_superseded_after IS NULL; to SELECT * FROM duration WHERE NOT superseded; In a larger, more complex query the difference in readability will be even more marked. -- Bill Moran http://www.potentialtech.com http://people.collabo

[GENERAL] Inserting string with a " into an array using {} syntax ... possible?

tempts (various noise has been snipped): bill=# create table testarray (a TEXT[], id BIGSERIAL PRIMARY KEY); bill=# insert into testarray (a) values (E'{"text \"for you\"","moretext"}'); ERROR: malformed array literal: "{"text "for

Re: [GENERAL] Inserting string with a " into an array using {} syntax ... possible?

esn't answer the original question. The code I'm writing is back-end code that other developers will use to submit queries. I don't have the luxury of changing their queries from {} syntax to [] syntax at runtime. However, if this is something that can't be done with

Re: [GENERAL] Inserting string with a " into an array using {} syntax ... possible?

In response to Alvaro Herrera : > Bill Moran wrote: > > > bill=# insert into testarray (a) values (E'{"text \\"for > > you\\"","moretext"}'); > > INSERT 0 1 > > bill=# select * from testarray; > >a

Re: [GENERAL] Running Postgresl in a virual machine

s that the disk IO is usually crap compared to native access to the disks (experience is mostly using VMWare, other hypervisors may not have this problem) -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] Query 4-5 times slower after ANALYZE

ely not the type of query that one can even understand in just a few minutes! Anyway, the real reason I posted -- I doubt if anyone will be able to make sense of a query plan that complex without the actual query, so you'll probably want to post it as well. -- Bill Moran http://www.potential

Re: [GENERAL] Query 4-5 times slower after ANALYZE

t; calculated? Or are they treated completely separately? Is that function STABLE? If it's VOLATILE, can it be rewritten to be STABLE? That might make a lot of difference if it's repeatedly called with the same values. -- Bill Moran http://www.potentialtech.com http://people.coll

Re: [GENERAL] How to configure PostgreSQl for low-profile users

t; thing, in particular) If you really need to prevent users from knowing about each other, you'll probably be better off using a virtual machine infrastructure to give each client a dedicated DB system. FreeBSD jails are particularly useful for this because of how lightweight they are. -- Bill

Re: [GENERAL] Long-running query blocks all other queries

nything else. Start with SELECT * FROM pg_locks; to see if there are actually any locks occurring that could be causing problems. If there are no explicit locks, you may be looking at IO starvation. All the cores in the world won't help you if your disks are too slow to feed the

Re: [GENERAL] Database shut down unexpectedly.

I'd start by questioning them on their activities at that time. If that doesn't answer your question, then implement OS auditing so you can catch the culprit next time. It's quite possible that someone is sending signals to the DB system without knowledge of how those signals are

Re: [GENERAL] Now I am back, next thing. Final PGS tuning.

provement from REINDEXing, but if you've got obvious off-peak times (i.e., if nobody uses the system over weekends or something) it probably doesn't hurt to reindex everything on a regular schedule. Don't obsess over it, though. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Looking for advice on database encryption

he database, they still can't access the data without the passphrases of the individuals who entered the data. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you

Re: [GENERAL] Looking for advice on database encryption

In response to Thomas Kellerer : > Bill Moran wrote on 16.04.2009 21:40: > > The goal here is that if we're going to encrypt the data, it should > > be encrypted in such a way that if an attacker gets ahold of a dump > > of the database, they still can

<    3   4   5   6   7   8   9   10   11   12   >