[GENERAL] infinity

is there any integer constant for infinity in postgres? i know such beasts exist (with some bugs) in the date/time domain. it would be nice to have an abstraction for numerology as well as chronology... -tfo ---(end of broadcast)--- TIP 1:

[GENERAL] infinity

is there any integer constant for infinity in postgres? i know such beasts exist (with some bugs) in the date/time domain. it would be nice to have an abstraction for numerology as well as chronology... -tfo p.s. i'm not sure this ever _really_ got posted before, since i wasn't subscribed to

[GENERAL] Re: View and function

DaVinci wrote: create function pilpot(calle) returns integer as ' this is your problem. try this: create function pilpot(text) returns integer as ' the syntax for creating postgres functions is to declare the types, not the identifiers of any parameters. -tfo

[GENERAL] creating constants in postgres

is there in postgres a way to create a constant like CURRENT_DATE for general use? -tfo ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[GENERAL] do functions cache views?

are there circumstances under which a pl/pgsql function will cache the contents of a view? i can do this sequence of actions just fine: create table foo ( id int2 primary key ); create view foo_view as select * from foo; create function get_new_foo() returns int2 as ' declare

[GENERAL] trigger sub-functions

is there any way to get access to the new and old records created by a trigger in the function it calls? i.e., if i have create trigger after_insert after insert on foo execute procedure trigger_after_insert_foo(); is there any way to do something like the following... create function

[GENERAL] NAMEDATALEN

is it safe to change NAMEDATALEN, dump an existing database, recompile, and then restore the database? in src/include/postgres_ext.h, it mentions that ... databases with different NAMEDATALEN's cannot interoperate!, and i was wondering if included when altering NAMEDATALEN for a database that

[GENERAL] Re: How to create a trigger

create trigger date_update before update on mytable for each statement execut procedure [procedure that inserts a date for me in the update_date column] So what would I be doing for the portion in brackets? at that point you need a function. check out the CREATE FUNCTION syntax in

[GENERAL] Interrupted pg_dump / pg_restore Upgrade

causes as possible. The new database is 8.2 (as were all the client utilities used in the migration), built from source, running on Solaris: SunOS x41-xl-01.int 5.10 Generic_118855-19 i86pc i386 i86pc -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability

Re: [GENERAL] Interrupted pg_dump / pg_restore Upgrade

On Jan 4, 2007, at 7:03 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: My big question is: Is there anything that happens late in the game in a pg_dumpall that affects system catalogs or other non-data internals in any critical ways that would make an interrupted pg_dumpall

Re: [GENERAL] Database Corruption - last chance recovery options?

preceding your inability to get a table listing via psql? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005 ---(end of broadcast)--- TIP 5: don't forget

Re: [GENERAL] upgrading and pg_restore versions

incompatibilities). You don't have to rely on pg_restore. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005 ---(end of broadcast)--- TIP 5: don't

Re: [GENERAL] Database Corruption - last chance recovery options?

take effect on reload, then how could a related failure manifest at all, regardless of when? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005 ---(end of broadcast

Re: [GENERAL] More activity in pg_stat_activity

think it will error out. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005 ---(end of broadcast)--- TIP 4: Have you searched our list archives

Re: [GENERAL] CPU-intensive autovacuuming

to be of use in a production environment (where I still find its behavior to be preferable to a complicated list of manual vacuums performed in cron). -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue

Re: [GENERAL] CPU-intensive autovacuuming

to hack pg_autovacuum before 8.1 is released, although if it doesn't become integrated by beta feature freeze, I might give it a shot. But I hope if anyone completes the linear improvement, they'll post to the lists. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC

[GENERAL] PostgreSQL Hosting

ings? Am I hurting my prospects for upgrade paths? There were a few minor patches made to phpPgAdmin, for instance, to get it to use the new pb_db view. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Av

Re: [GENERAL] PostgreSQL Hosting

wondering whether anyone else in the community has developed any best practices when it comes to PostgreSQL hosting. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203

Re: [GENERAL] PostgreSQL Hosting

and pg_dump. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jul 11, 2005, at 11:39 AM, Thomas F. O'Connell wrote:Then we ran into the problem

Re: [GENERAL] PostgreSQL Hosting

involving a bit of mucking with system catalogs and the schema search path? -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jul 11, 2005, at 12

Re: [GENERAL] PostgreSQL Hosting

for your insights. I don't think we're really in a position to support postmaster-per-client hosting, though, at the moment. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6

[GENERAL] Quoting $user as Parameter to SET

a variable in the parameter list to SET. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005

Re: [GENERAL] Quoting $user as Parameter to SET

for the noise... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jul 11, 2005, at 6:04 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL

[GENERAL] ERROR: could not open relation

is a "standalone backend"? A single-user version?) Avoid VACUUMing? pg_dump and reload?The database is currently running. Should I stop it to prevent further damage?-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 3

Re: [GENERAL] ERROR: could not open relation

I'm developing a habit of being the most frequent replier to my own posts, but anyway: I discovered the meaning of 1663, which is the default tablespace oid.But I still need help with diagnosis and treatment... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open

Re: [GENERAL] ERROR: could not open relation

still have cause for concern? -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jul 14, 2005, at 7:57 AM, Tom Lane wrote: Thomas F. O'Connell

Re: [GENERAL] ERROR: could not open relation

of the inheritance relationship in the temp tables, is explicitly attempting to access them? -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005

Re: [GENERAL] ERROR: could not open relation

On Jul 14, 2005, at 12:51 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: Unfortunately, this is a system where the interloper is superuser (and, yes, changing this has been a TODO). But even so, I need help understanding how one backend could access the temp table

Re: [GENERAL] ERROR: could not open relation

the new integrated version of the code as far as access to temp tables are concerned? If contrib/pg_autovacuum, temp tables, and bgwriter don't mix well, I'll need to rethink our vacuum strategy. Thanks! -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open

Re: [GENERAL] ERROR: could not open relation

and should they be submitted to that queue? Thanks again to all developers and community folk who lent insight into this error -- diagnosis and recovery (which was, thankfully, virtually non-existent). -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open

Re: [GENERAL] ERROR: could not open relation

pg_autovacuum with this architecture is a bad idea. If so, we can revert to not using temp tables at all. Further, why have we only noticed it once when this version of code (and PostgreSQL) has been running for weeks? -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC

Re: [GENERAL] ERROR: could not open relation

for bgwriter? I'm under the impression that this is mostly an issue with the implementation of temp tables and the planner, but I'd like confirmation from folks who can read the code more easily... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source

[GENERAL] ROW SHARE vs. ACCESS EXCLUSIVE; UPDATE vs. SELECT ... FOR UPDATE

is being acquired when a foreign key shouldn't have to be checked. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005

Re: [GENERAL] Upgrading from 7.1

from the data dump rather than a monolithic dump/restore? Once you get your data import working, you might want to check out contrib/adddepend, though, since you're coming from a pre-7.3 database. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source

Re: [GENERAL] initskript after db start; locks on transactions

, for what? With the LOCK command i can only lock tables, or? You can use any lock mode specified: http://www.postgresql.org/docs/8.0/static/sql-lock.html -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th

Re: [GENERAL] How to implement table caching

Andrus, You might consider something like materialized views: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Whether table caching is a good idea depends completely on the demands of your application. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening

Re: [GENERAL] ORDER BY time consuming

You're also free to set sort_mem (7.4.x) or work_mem (8.0.x) on a per session basis, so you could try experimenting with raising the value of those settings during sessions in which your query is running. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open

Re: [GENERAL] Insert Ignore or something similar...

I don't think any such behavior exists in PostgreSQL, and based on a reading of the behavior in MySQL, I can't imagine it ever existing considering the preference of PostgreSQL developers for correct (and sane) behavior. INSERT IGNORE seems like a foot-cannon... --Thomas F. O'ConnellCo-Founder,

Re: [GENERAL] Insert Ignore or something similar...

On Sep 5, 2005, at 10:51 PM, Alvaro Herrera wrote: On Mon, Sep 05, 2005 at 10:35:49PM -0500, Thomas F. O'Connell wrote: I don't think any such behavior exists in PostgreSQL, and based on a reading of the behavior in MySQL, I can't imagine it ever existing considering the preference

Re: [GENERAL] pg_autovacuum not doing anything

to verify that any activity is occurring. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Sep 11, 2005, at 4:26 AM, Sim

Re: [GENERAL] Replication

On Sep 19, 2005, at 7:10 PM, Bruce Momjian wrote: Added to TODO: * Allow WAL traffic to be steamed to another server for stand-by replication steamed or streamed? :) -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http

Re: [GENERAL] pg_dump output mode

rather than a cluster. I think the only difference between t and c is that c is compressed by default. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615

Re: [GENERAL] How to uninstall Postgres

work in most of the cases. regards suresh There's actually a make uninstall rule, too. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469

Re: [GENERAL] Oracle buys Innobase

of the transaction were not disclosed. I guess it's possible that MySQL didn't have the financial reach to pull off the deal. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville

Re: [GENERAL] Why is this function wrong

as originally written. I recommend a closer reading of the chapter on PL/pgSQL: http://www.postgresql.org/docs/8.0/static/plpgsql.html -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Open Source Solutions. Optimized Web Development. http://www.sitening.com/ 110 30th Avenue

[GENERAL] Lock Modes (Documentation)

the underlying locking requirements of each step of each SQL command to know when locks might implicitly be acquired. Even if UPDATE is the only special case, it seems like it'd be worth mentioning. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http

Re: [GENERAL] Subqueries

to serve as targets for the results of your SELECTs. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast

[GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

important: Has this risked any catastrophic data corruption? If we just switch to a gcc 8.2.2, will we be fine? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005

Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

On Tue, Feb 06, 2007 at 09:43:01AM -0600, Thomas F. O'Connell wrote: DETAIL: Table has type character varying, but query expects character varying. In another thread, someone else is reporting this too. I'm wondering whether something went wrong in the 8.2.2 release. Is this the other

Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade

On Feb 6, 10:33 am, [EMAIL PROTECTED] (Tom Lane) wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: but we just built 8.2.2 from source using cc, and now we're seeing this type of error in the logs: ERROR: attribute 3 has wrong type DETAIL: Table has type character varying

[GENERAL] 8.2.2 Announcement?

Shouldn't there be an announcement about the buggy 8.2.2 announced yesterday preceding the availability of new binaries, or is the bug not considered severe enough to invalidate the 8.2.2 sources that are currently in distribution? -- Thomas F. O'Connell optimizing modern web applications

Re: [GENERAL] New US DST Rules PostgreSQL

USDSTupdates regards, tom lane I'm curious. For 7.4.x, does the database require a restart for the change to take effect? I'm aware of a few production installations where the OS changed, but postgres time functions didn't take hold. -- Thomas F. O'Connell optimizing

[GENERAL] pg_standby

to see whether anything would be unusable or behave unexpectedly in 8.2. I'm assuming the correct way to install it would be to take the pg_standby directory from CVS, add it to an 8.2.x source contrib tree, and build as if it were a part of contrib in 8.2? -- Thomas F. O'Connell optimizing modern

[GENERAL] pg_standby: Unremovable Trigger File

the recovery process, and I'm not helping myself much by reading the code. Doesn't the non-zero exit from CheckForExternalTrigger mean that pg_standby will be signaling to the standby server a file-not- found scenario? -- Thomas F. O'Connell optimizing modern web applications : for search

Re: [GENERAL] pg_standby

On Mar 29, 2:17 pm, [EMAIL PROTECTED] (Thomas F. O'Connell) wrote: I see that Simon has pushed pg_standbyinto contrib for 8.3. Is there anything that would make the current version in CVS unsuitable for use in 8.2.x? I've done a cursory inspection of the code, but I'll admit that I haven't

[GENERAL] pg_standby + test_warm_standby: A Community Report

to test restartable recovery and incrementable backups, but I'd like to say thanks to Simon and the whole PostgreSQL Global Development Group for a fine product and a robust community. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability

Re: [GENERAL] Status of Postgres 8.2.4 and pg_standby

in contrib for 8.3 is designed to be backward compatible with 8.2.x. http://archives.postgresql.org/pgsql-admin/2007-04/msg00069.php I'm currently having good success in testing. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http

[GENERAL] Incrementally Updated Backups: Docs Clarification

. [optional] preserve data directory, remove unnecessary WAL files 3. restart standby server Is that all there is to it? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005

Re: [GENERAL] [DOCS] Incrementally Updated Backups: Docs Clarification

On Apr 25, 2007, at 9:42 AM, Simon Riggs wrote: On Thu, 2007-04-19 at 15:48 -0500, Thomas F. O'Connell wrote: If we take a backup of the standby server's files while it is following logs shipped from the primary, we will be able to reload that data and restart the standby's recovery process

[GENERAL] NFS vs. PostgreSQL on Solaris

message? 3. What would cause postgres to die from a signal 11? I've also got a core file if that's necessary for further forensics. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005

Re: [GENERAL] NFS vs. PostgreSQL on Solaris

On Apr 26, 2007, at 6:51 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: 1. What aspect of postgres' memory usage would create an out of memory condition? I'm guessing you ran the box out of swap space --- look into what other processes got started as a result of adding

[GENERAL] Continuous Archiving for Multiple Warm Standby Servers

introspection of this nature. I ask because it seems like network-to- network failures are a common enough occurrence that some mechanism for archive verification is a must-have. I'm just trying to determine how much of that functionality I'll have to build myself... -- Thomas F. O'Connell

[GENERAL] Large Database \d: ERROR: cache lookup failed for relation ...

of this error in the archives. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005

[GENERAL] pg_dump: ERROR: could not open relation with OID ...

wonder if it was indicative of trouble on the way? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005

[GENERAL] PL/PgSQL Boolean Comparison Operator Binding

of order of evaluation? PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC 2.95.4. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(end of broadcast

Re: [GENERAL] PL/PgSQL Boolean Comparison Operator Binding

Never mind. I think I had some data that hadn't been calibrated lying around prior to testing. I think my test case was flawed. Sorry for the noise. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN

Re: [GENERAL] Question on output of VACUUM VERBOSE

to consider increasing max_fsm_relations and max_fsm_pages in postgresql.conf. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 17, 2005, at 5:14 PM, Cornelia Boenigk

[GENERAL] PL/PgSQL Index Usage with Trigger Variables

to the planner? As in, should I rewrite the above statement as: UPDATE mytable SET mybigintcol = somevalue WHERE mybigintcol = '' || NEW.myotherbigintcol || ''; in order to help the planner understand that it can use an index? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect

Re: [GENERAL] PL/PgSQL Index Usage with Trigger Variables

I think I see what was happening. I was looking at the output of the SELECT that is used for opening a cursor. Got it. Thanks for your help. It's kind of a meta-select in the printed version of a plan if the cursor being opened is a SELECT. -tfo -- Thomas F. O'Connell Co-Founder, Information

Re: [GENERAL] Apparently I don't understand full outer joins....

trying to do it. You could probably create a nested structure, though. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 25, 2005, at 2:29 PM, Ben wrote: I run this: select

[GENERAL] PL/PgSQL, Inheritance, Locks, and Deadlocks

: PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC 2.95.4 -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(end of broadcast

Re: [GENERAL] PL/PgSQL, Inheritance, Locks, and Deadlocks

in the linking table, and the secondary column of the key also has its own index. I'm more concerned with the locking, which is thoroughly unexpected behavior to me. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6

Re: [GENERAL] PL/PgSQL, Inheritance, Locks, and Deadlocks

whatsoever on linking tables on which it has no direct effect (either reading or writing)? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 2, 2005, at 9:41 AM, Tom Lane

Re: [GENERAL] Postgresql and Macintosh

True. Apple even has a page with installation instructions: http://developer.apple.com/internet/opensource/postgres.html -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb

Re: [GENERAL] Help with seq numbers...

The manual is correct. There is no way to roll back a nextval. There are a variety of workarounds suggested in the archives. Take a look. One example is precalculating a large sequence and storing it in a table. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC

Re: [GENERAL] LIke and Indicies

possible that the planner thinks using the index on company_id filtered by product_desc is faster than any multicolumn index that might exist. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203

Re: [GENERAL] Peculiar performance observation....

Well, your expected vs. actual rows are off, so analyzing might help. Otherwise, what is your sort_mem set to? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 14

Re: [GENERAL] no IF - am I missing something ?

and, to me, is more readable than a comma-delimited list where position alone indicates function in the expression. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 17

[GENERAL] checkpoint_timeout

. Instinctively, it seems like it would be nice to have something similar for checkpoint_timeout, but is there any disadvantage to having a value dramatically higher than the default for general use, considering that checkpoint_timeout can only be set at server start? -tfo -- Thomas F. O'Connell

Re: [GENERAL] checkpoint_timeout

was thinking more about the utility of having a knob that could be twisted in the circumstances of large data loads than the reality of what that would mean from the point of view of WAL and checkpoints. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http

Re: [GENERAL] inherited table and rules

TABLE entry also pretty clearly indicates that ONLY is not allowed in specifying inheritance. All you need is the table name, so try eliminating the ONLY from your CREATE example below. GUC is the Grand Unified Configuration scheme, which was introduced in 7.1. -tfo -- Thomas F. O'Connell Co

Re: [GENERAL] How to get the size in bytes of a table data

Check out dbsize in contrib. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 24, 2005, at 9:53 AM, Ricardo Fonseca e Campos wrote: Hi

Re: [GENERAL] Referential integrity using constant in foreign key

-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 25, 2005, at 10:22 AM, Andrus Moor wrote: I need to create referential integrity constraints

Re: [GENERAL] Referential integrity using constant in foreign key

needs. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 25, 2005, at 1:39 PM, Andrus Moor wrote: Thomas, thank you for reply

Re: [GENERAL] Server load planning

in contrib called pgbench that you could use to do some testing. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 27, 2005, at 11:38 AM, Dan

Re: [GENERAL] Linux Filesystem for PG

From what I have gathered on the performance list, JFS seemed to be the best overall choice, but I'd say check the archives of pgsql-performance because so many of your I/O needs depends on what you're going to be doing with your database. -tfo -- Thomas F. O'Connell Co-Founder, Information

Re: [GENERAL] Recovering real disk space

Isn't this also a symptom of inappropriate FSM settings? Try running a VACUUM VERBOSE and check the FSM settings at the end. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6

Re: [GENERAL] error with vacuumdb

You'll need to post the actual error to the list to have any hope of receiving good help. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005

Re: [GENERAL] generating a parent/child relationship in a trigger

... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 12, 2005, at 12:51 PM, Mark Harrison wrote: Suppose I'm adding row to a table, and one

Re: [GENERAL] Table modifications with dependent views - best practices?

statements be more likely to cause lock acquisition at cross purposes? A simple example would help me understand this. Thanks! -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6

Re: [GENERAL] DDL from psql console?

Any reason not to use pg_dump -s? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 25, 2005, at 10:29 AM, John Browne wrote: Hello, I

Re: [GENERAL] Increasing connection limit in postgres

There should be no need to recompile anything. See the entry for max_connections: http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME- CONFIG-CONNECTION -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http

[GENERAL] pg_dump in a production environment

read-only and shouldn't require any exclusive locks.Connections don't really pile up excessively, and load on the machine does not get in the red zone. Is there anything else I should be noticing?-tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open

Re: [GENERAL] pg_dump in a production environment

CPU, it doesn't prevent further access.I'm suspicious more of something involving locks than of CPU.Oh, and one other small(ish) detail: the dumping client is using a 7.4.8 installation, whereas the server itself is 7.4.6.-tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC

Re: [GENERAL] pg_dump in a production environment

exclusive locks? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On May 23, 2005, at 3:18 PM, Scott Marlowe wrote: Basically, it sounds

Re: [GENERAL] pg_dump in a production environment

today in which pg_dump might be able to use statistics in almost the opposite way of pg_autovacuum, such that it steered clear of objects in heavy use, but I'm not familiar enough with the source to know how this might work. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect

Re: [GENERAL] Performance of a view

do in the case of claimnum since it doesn't exist in the view. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax

[GENERAL] Index Administration: pg_index vs. pg_get_indexdef()

of using pg_index for such purposes, the OP in the old thread raised what I think is a good question: why are techniques for accessing int2vector nowhere documented if the type itself makes its way into very user-visible documentation and catalogs/views? -- Thomas F. O'Connell Database

Re: [GENERAL] Index Administration: pg_index vs. pg_get_indexdef()

On Nov 22, 2005, at 10:56 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: In an old thread http://archives.postgresql.org/pgsql-admin/2004-01/ msg00271.php, Tom Lane suggested that it would be unreasonable to use pg_index to reconstruct (expressional) indexes (in 7.4

[GENERAL] SELECT Generating Row Exclusive Locks?

-linux-gnu, compiled by GCC 2.95.4 -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end

  1   2   >