Re: [GENERAL] Performance issues of one vs. two split tables.

Say, if you first SELECT fname, lname FROM user_table; and then you issue SELECT * FROM user_table; -- the second select will be returned from buffer cache -- since all rows are already in the cache. ...Unless your table contains some large TEXT columns that have been stored out of line (T

Re: [GENERAL] Performance issues of one vs. two split tables.

On 5/15/07, Bill Moseley <[EMAIL PROTECTED]> wrote: On Tue, May 15, 2007 at 06:33:26AM +0200, Dawid Kuroczko wrote: > Well, views are not going to help with memory consumption here. > It is the table contents that gets cached in buffer cache, not the > views contents. So if you have a view which

Re: [GENERAL] Performance issues of one vs. two split tables.

On Tue, May 15, 2007 at 06:33:26AM +0200, Dawid Kuroczko wrote: > Well, views are not going to help with memory consumption here. > It is the table contents that gets cached in buffer cache, not the > views contents. So if you have a view which returns only one > column from 15-column table, you w

[GENERAL] Performance issues of one vs. two split tables.

On 5/14/07, Rich Shepard <[EMAIL PROTECTED]> wrote: On Mon, 14 May 2007, PFC wrote: > I did something like that on MySQL some time ago. > In the Users table there was stuff that other users need to see (like > his login name, etc), and stuff that only this user needs to see (like his

Re: [GENERAL] Recovering a messed-up database

"Reuven M. Lerner" <[EMAIL PROTECTED]> writes: > To make a long story short, I thought that the site administrator was > making regular backups, and that I was running pg_dump every night. > Unfortunately, neither assumption was quite right. The administrator > was using rsync to back things u

Re: [GENERAL] Restarting postgres server

On May 14, 2007, at 6:21 PM, George Heller wrote: psql mydatabase postgres it gives me the error, psql: could not connect to server: No such file or directory Depending on your setup, postgres might not be starting automatically on reboot. Try: pg_ctl start -D [datadir] replacing [datadi

[GENERAL] dns less connection

hi all there any was successful with connect to postgres with psqlodbc in dsn less mode? It's wrong list for this subject? any pointer be appreciated best regards mdc __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y l

[GENERAL] Recovering a messed-up database

I'm running a PostgreSQL 8.2.0 system on RHEL WS (update 5). The machine is administered by someone else. To make a long story short, I thought that the site administrator was making regular backups, and that I was running pg_dump every night. Unfortunately, neither assumption was quite righ

Re: [GENERAL] Restarting postgres server

On Mon, 2007-05-14 at 15:21 -0700, George Heller wrote: > psql: could not connect to server: No such file or > directory > Is the server running locally and accepting > connections on Unix domain socket > "/tmp/.s.PGSQL.5432"? > > I have a lot of data in there, so I dont want to ge

[GENERAL] Restarting postgres server

Hi all. I just modified my pg_hba.conf file in order to allow access from another machine. I needed to restart the postgres server in order for the changes to take effect, and so I issued the command, pg_ctl restart -D /usr/local/pgsql/data But the server failed to shutdown. The database was not

Re: [GENERAL] Postgres Printed Manuals

Rich Shepard wrote: > On Mon, 14 May 2007, Bruce Momjian wrote: > > >How much would it be to email the PDF manual to someone like Kinkos and > >get it printed? Effectively, that might be the cheepest solution because > >it is print-on-demand. > > >>What I would suggest is, rather than hoping som

Re: [GENERAL] are foreign keys realized as indexes?

On May 13, 2007, at 17:21 , Lew wrote: Peter Childs wrote: Apart from anything a unique constraint is NOT the same as a unique index, as you need a not null constraint on the column as well. Not true, whichever way 'round you meant it. Technically, the UNIQUE constraint is a logical con

Re: [GENERAL] Performance issues of one vs. two split tables.

On Mon, 14 May 2007, PFC wrote: I did something like that on MySQL some time ago. In the Users table there was stuff that other users need to see (like his login name, etc), and stuff that only this user needs to see (like his preferences). So, when displaying posts in the forum, for

Re: [GENERAL] Performance issues of one vs. two split tables.

On Mon, May 14, 2007 at 10:52:13PM +0200, PFC wrote: > > >Say I have a "user" table that has first, last, email, password, and > >last_accessed columns. This user table will be accessed often. (It's > >not really "user", but that's not important in this discussion) > > > >Say that there's also a

Re: [GENERAL] Performance issues of one vs. two split tables.

Say I have a "user" table that has first, last, email, password, and last_accessed columns. This user table will be accessed often. (It's not really "user", but that's not important in this discussion) Say that there's also about 10 columns of settings or preferences for each user. Are there

Re: [GENERAL] Postgres Printed Manuals

On Mon, 14 May 2007, Bruce Momjian wrote: How much would it be to email the PDF manual to someone like Kinkos and get it printed? Effectively, that might be the cheepest solution because it is print-on-demand. What I would suggest is, rather than hoping somebody has the bankroll for one LARG

[GENERAL] Performance issues of one vs. two split tables.

Is there any benefit of splitting up a table into two tables that will always have a one-to-one relationship? Say I have a "user" table that has first, last, email, password, and last_accessed columns. This user table will be accessed often. (It's not really "user", but that's not important in

Re: [GENERAL] Age function

On Mon, 14 May 2007, Andrus wrote: How to create function which returns persons age in years? Look at the PostgreSQL docs for "Date/Time Functions and Operators." You'll find the syntax for AGE() there. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Appli

Re: [GENERAL] Postgres Printed Manuals

How much would it be to email the PDF manual to someone like Kinkos and get it printed? Effectively, that might be the cheepest solution because it is print-on-demand. --- John Meyer wrote: > Robert James wrote: > > The Pos

Re: [GENERAL] How to implement GOMONTH function

On Sun, 13 May 2007, Andrus wrote: I need to create function GOMONTH which returns date by given number of month before or forward using sql or pgsql in 8.1+ For example, GOMONTH( DATE '20070513', 1 ) should return date '20070613' GOMONTH( DATE '20070513', -2 ) should return date '20070313' An

Re: [GENERAL] Age function

Andrus wrote: How to create function which returns persons age in years? Function parameters: ldDob - Day Of birth ldDate - Day where age is returned I tried CREATE OR REPLACE FUNCTION public.age(date, date, out integer) IMMUTABLE AS $_$ SELECT floor(INT($2::text::integer-$1::text::integer)/1

Re: [GENERAL] Age function

On 5/14/07, Andrus <[EMAIL PROTECTED]> wrote: How to create function which returns persons age in years? [snip] What's wrong with age()? # select age('1879-03-14'::date); age -- 128 years 2 mons # select extract(year from age('1879-03-14'::date)); date_part ---

[GENERAL] Age function

How to create function which returns persons age in years? Function parameters: ldDob - Day Of birth ldDate - Day where age is returned I tried CREATE OR REPLACE FUNCTION public.age(date, date, out integer) IMMUTABLE AS $_$ SELECT floor(INT($2::text::integer-$1::text::integer)/1); $_$ l

[GENERAL] How to implement GOMONTH function

I need to create function GOMONTH which returns date by given number of month before or forward using sql or pgsql in 8.1+ For example, GOMONTH( DATE '20070513', 1 ) should return date '20070613' GOMONTH( DATE '20070513', -2 ) should return date '20070313' I tried CREATE OR REPLACE FUNCTION pub

Re: [GENERAL] are foreign keys realized as indexes?

Peter Childs wrote: Apart from anything a unique constraint is NOT the same as a unique index, as you need a not null constraint on the column as well. Not true, whichever way 'round you meant it. For pg unique constraint

Re: [GENERAL] Database transaction related

Thanks alot john. that was helpful On 5/14/07, John D. Burger <[EMAIL PROTECTED]> wrote: Jasbinder Singh Bali wrote: > could you please elaborate this concept of queue table? The basic idea is to for the insert trigger to not explicitly kick off the work you need to have done, but simply inse

[GENERAL] RE : [GENERAL] Server crash on postgresql 8.2.4 with tsearch2

Hello, Sorry for the delay for response, I was sick and out of office. Today I applied your patch to pg 8.2.4 then recompiled postgresql and stemmer. I Uninstalled old version and installed new patched version. I have still the same problem. I will retry in case I missed something. Thank for you

Re: [GENERAL] migrating a tablespace between postgres instances in shared disk environment

Am Montag, 14. Mai 2007 15:35 schrieb Paula Ta-Shma: > Is there a way to allow migration of a single table/database/tablespace > from one postgres instance to another (in a system with multiple > tables/databases/tablespaces)  ? No. -- Peter Eisentraut http://developer.postgresql.org/~petere/ -

Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)

Thanks very much to all who responded, the replies were very helpful. j On Thu, 10 May 2007 20:43:20 -0500 John Gateley <[EMAIL PROTECTED]> wrote: > I need to make my Postgresql installation fault tolerant. > ... ---(end of broadcast)--- TIP 4: Ha

Re: [GENERAL] a few questions on backup

On 14.05.2007 16:54, Marco Colombo wrote: I have a few questions on backuping a PostgreSQL server (lets say anything 8.x.x). I've read "Continuous Archiving and Point-In-Time Recovery (PITR)" in the manual I'm still missing something...well actually I think I don't but I've been debating on this

Re: [GENERAL] a few questions on backup

Marco Colombo <[EMAIL PROTECTED]> writes: > Am I right in assuming that the following procedure is ok? > 1) issue pg_start_backup(); > 2) copy (or tar or cpio) the data dir, w/o pg_xlog/ > 3) issue pg_stop_backup(); > 4) copy (or tar or cpio) pg_xlog/ contents. No. You have to have an actual arc

[GENERAL] a few questions on backup

Hello, I have a few questions on backuping a PostgreSQL server (lets say anything 8.x.x). I've read "Continuous Archiving and Point-In-Time Recovery (PITR)" in the manual I'm still missing something...well actually I think I don't but I've been debating on this with a friend for a while, and there'

[GENERAL] migrating a tablespace between postgres instances in shared disk environment

Is there a way to allow migration of a single table/database/tablespace from one postgres instance to another (in a system with multiple tables/databases/tablespaces) ? The postgres instances would all share their storage using a clustered file system. Ideally, one postgres instance would "close"

Re: [GENERAL] EXCEPTION clause not identified

"Jasbinder Singh Bali" <[EMAIL PROTECTED]> writes: > In one of my trigger functions, i'm trying to catch invalid ip address > exception > CREATE OR REPLACE FUNCTION func_client_socket() > RETURNS "trigger" AS > $BODY$ > DECLARE > ip_address_present int4; > BEGIN >

Re: [GENERAL] Streaming large data into postgres [WORM like applications]

John D. Burger wrote: > Dhaval Shah wrote: > >> 2. Most of the streamed rows are very similar. Think syslog rows, >> where for most cases only the timestamp changes. Of course, if the >> data can be compressed, it will result in improved savings in terms of >> disk size. > > If it really is usual

Re: [GENERAL] primary key index

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Really? toll_archive=# create table snaggle ( toll_archive(# foo integer, toll_archive(# bar integer, toll_archive(# primary key (foo)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "snaggle_pkey" for table "snaggle" CREATE TABLE Tim

Re: [GENERAL] Streaming large data into postgres [WORM like applications]

Dhaval Shah wrote: 2. Most of the streamed rows are very similar. Think syslog rows, where for most cases only the timestamp changes. Of course, if the data can be compressed, it will result in improved savings in terms of disk size. If it really is usually just the timestamp that changes, one

Re: [GENERAL] Database transaction related

Jasbinder Singh Bali wrote: could you please elaborate this concept of queue table? The basic idea is to for the insert trigger to not explicitly kick off the work you need to have done, but simply insert a row into a "work request table". A separate process notices that there is a work

Re: [GENERAL] tsearch2 problem

On Mon, 14 May 2007, Marcel wrote: hi I'm using tsearch2 first time. The guide suggusts there are 4 weight labels: A, B,C, D. Can it be more flexible? For example, Is it possible to assign each word an different weight (real value). Do you want to have more than 4 labels ? Unfortunately, no.

Re: [GENERAL] primary key index

2007/5/14, Sim Zacks <[EMAIL PROTECTED]>: I read the section on indexes where it says that PostGreSQL creates a unique index, but I didn't see an index on the table so I didn't know if it was implicit or if for some reason my primary keys weren't becoming indexes. You are supposed to see thos

[GENERAL] IGNORE Re: explain problem

I found a slight problem with my test case. I will post again when it is correct. Sim Zacks wrote: I have 2 databases that are supposed to be identical. In one a specific query goes very fast and the other one the same query goes slow. I checked the data and it is identical. I checked the in

[GENERAL] explain problem

I have 2 databases that are supposed to be identical. In one a specific query goes very fast and the other one the same query goes slow. I checked the data and it is identical. I checked the indices and constraints and they are identical. I vacuumed both databases immediately before I ran the E

Re: [GENERAL] tsearch2 problem

On 14.05.2007 11:12, Marcel wrote: I'm using tsearch2 first time. The guide suggusts there are 4 weight labels: A, B,C, D. Can it be more flexible? For example, Is it possible to assign each word an different weight (real value). No, AFAIK. -- Regards, Hannes Dorbath --

Re: [GENERAL] TWO SAME TABLES, ONE UPDATED. HOW TO SYNC THE OTHER?

This query will run quite slow if tables are large, so - you may in addition create a trigger-updated TIMESTAMP columns and search for changed data through the recent created/updated elements only. On 13 May 2007 02:21:30 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: On May 11, 11:06 pm,

[GENERAL] tsearch2 problem

hi I'm using tsearch2 first time. The guide suggusts there are 4 weight labels: A, B,C, D. Can it be more flexible? For example, Is it possible to assign each word an different weight (real value). Thanks, -- Marcel

Re: [GENERAL] primary key index

It is mixture of looking and knowing where to look. I read the section on indexes where it says that PostGreSQL creates a unique index, but I didn't see an index on the table so I didn't know if it was implicit or if for some reason my primary keys weren't becoming indexes. I didn't see the creat

Re: [GENERAL] primary key index

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/14/07 03:27, Sim Zacks wrote: > Is a primary key an implicit index, or if I want to join on that field > is it also worthwhile to make an explicit index? On-line documentation is such a wonderful resource... http://www.postgresql.org/docs/7.3/i

[GENERAL] OID - file object, are damaged by PostgreSQL.

Hi all, I am using Postgresql 8.2. I am using client side api to upload/download files to/from postgresql using calls lo_export()/lo_import(); If I download a file from postgresql - few weeks later, files object's contents got damaged. I don't know why? Do any of you have encountered same prob

[GENERAL] primary key index

Is a primary key an implicit index, or if I want to join on that field is it also worthwhile to make an explicit index? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq