[SQL] Inherited tables: How stable is that feature?

[Florian Weimer]

Is it likely that table inheritance is going to be removed in future
PostgreSQL versions (or that the semantics change radically)?  Or can
I built a database on top of this feature without running the risk of
a major restructuring task in the next few years?

-- 
Florian Weimer[EMAIL PROTECTED]
University of Stuttgart   http://cert.uni-stuttgart.de/
RUS-CERT  +49-711-685-5973/fax +49-711-685-5898

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] has anyone tried running in MAC OS X

[Justin Clift]

Hi,

PostgreSQL 7.1.x does work on MacOS X, there's evan a MacOS X
installation guide at :

http://techdocs.postgresql.org/installguides.php#macosx

Hope that's helpful.  It's mainly focused on Apache + PHP + PostgreSQL
7.1 on MacOS X, although you should be able to make use of it.

If you need further assistance, feel free to ask.

:-)

Regards and best wishes,

Justin Clift


Tom Lane wrote:
> 
> "postgresql" <[EMAIL PROTECTED]> writes:
> > I guess the subject line says ithas anyone tried running
> > PostgreSQL in MAC OS X.
> 
> Some folks at Apple did.
> 
> Postgres 7.1 is alleged to build out-of-the-box on OSX.
> (But I haven't tried it myself.)  Don't bother trying with
> earlier releases.
> 
> regards, tom lane
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
 - Indira Gandhi

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Re: Please dont flame

[Nils Zonneveld]

Olivier PRENANT wrote:
> 
> Sorry to bother,
> 
> Just making sure my newsfeed works
> 
>

Its seems to work :-)


nils

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Help! Record logging

[Edwin S. Ramirez]

Hello,

I would like to setup a trigger to keep track of all changes done
onto the records of paticular tables.  Basically, there would be two
identical tables such as, 'medications' and 'medications_log', when a
user inserts, updates or deletes a record I would like a copy to be
inserted into the log table.  I already have audit information in the
medications table, so I only need to be able to copy the record over to
the log table.

The only other difficulty is how do I let the trigger know the ID of
the user deleting the record?

Thanks,
Edwin S. Ramirez


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Re: fatal ERROR running simple join query...

[Ligia Pimentel]

It didn't work. I checked the tables (both) and found nothing wrong.
Finally I received an e-mail from Tom Lane suggesting that I upgraded to
Postgres 7.1, wich I did. After the upgrade, everything worked just fine.
Thank you.

Ligia
"Martín Marqués" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]...
> Quoting Ligia Pimentel <[EMAIL PROTECTED]>:
>
> >
> > I have a problem with postgres, I'm running a query that joins two
tables,
> > one with 129000+ records, and the other with 1172 records, it's a very
> > simple join.  But postgres can't handle it!!! (Both tables have an index
on
> > the  field "cuentacb").
> >
> > I get the following error after 3 minutes...
> >
> > migracion=# select * from cuentasequivalentes, equiv11mayo m
> > migracion-# where cuentasequivalentes.cuentacb = m.cuentacb
> > migracion-# and cuentasequivalentes.cuentabr <> m.cuentabr;
> > FATAL 1:  Memory exhausted in AllocSetAlloc()
> > pqReadData() -- backend closed the channel unexpectedly.
> > This probably means the backend terminated abnormally
> > before or while processing the request.
> > The connection to the server was lost. Attempting reset: Succeeded.
>
> First you should run VACUUM analyze on that database, or at least on those
2
> tables. Check with EXPLAIN to see how much better the queries get after
the
> analyze.
>
> > What can I do? I'm guessing that I could change a configuration
parameter
> > or
> > something like that, but I don't know.
>
> There are so many parameters to change, that I have to tell you to go and
see
> the "PostgreSQL 7.1 Administrator's Guide".
>
> Saludos... :-)
>
> --
> El mejor sistema operativo es aquel que te da de comer.
> Cuida tu dieta.
> -
> Martin Marques  |[EMAIL PROTECTED]
> Programador, Administrador  |   Centro de Telematica
>Universidad Nacional
> del Litoral
> -
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] HELP: what's wrong with my PL/PSQL function??

[Bruno Boettcher]

hello!

i have the following function:
CREATE FUNCTION plusSum(text,int4) RETURNS int4 AS '
  DECLARE
  jrlname ALIAS FOR $1;
  sumup ALIAS FOR $2;
  actsum int4;
  arow RECORD;
  conversion float8;
  temp float8;
  sum float8;
  BEGIN
  sum := 0;
  RAISE NOTICE ''stats % '', sumup;
  FOR arow IN SELECT * FROM "jrlname" WHERE plus=sumup LOOP
 SELECT conv FROM currencies WHERE tag=arow.currency INTO conversion;
 sum := sum + (arow.amount/conversion);
 END LOOP;
 return  round(sum);
  END;
  ' LANGUAGE 'plpgsql';
 select plusSum('journal','102');

and i get an error:
ERROR:  parser: parse error at or near "$1"
(BTW would be helpful if the thing could spit out also the line
number)

isolated the prob to this line:
FOR arow IN SELECT * FROM "jrlname" WHERE plus=sumup LOOP
in fact it doesn't seem to like the fact that i use a dynamic table
name??

i remind vaguely that pl/psql had indeed this sort of problem... but
this was years ago is there any workaround? even using perl? 

The whole prog is a bookkeeping program... and i want it now really able
to work with several sets of tables without duplicating also the
functions i wrote associated to the tables

BTW is there a possibility to make further select calls out of plperl?
(an example making this would come in handy...)

i am using a postgres7.03 DB on a debian system...

-- 
ciao bboett
==
[EMAIL PROTECTED]
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===
the total amount of intelligence on earth is constant.
human population is growing

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Re: has anyone tried running in MAC OS X

[Giorgio Valoti]

Tom Lane wrote:

> "postgresql" <[EMAIL PROTECTED]> writes:
> > I guess the subject line says ithas anyone tried running
> > PostgreSQL in MAC OS X.
>
> Some folks at Apple did.
>
> Postgres 7.1 is alleged to build out-of-the-box on OSX.
> (But I haven't tried it myself.)  Don't bother trying with
> earlier releases.

It works like a charm... I have succesfully compiled with a somewhat
standard configuration.
--
Giorgio Valoti
Magnetic Media Network 
-





---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Calculating the age of a person

[Hans-Jürgen Schönig]

I have a table containing the birthdays of various persons. The target
is to compute the age of a persons.

persons=# SELECT age(birth), * FROM persons LIMIT 1;
  age  | id |  name  |   birth| gender |
income
---+++++

 31 years 4 mons 16 days 23:00 |  1 | Albert | 1970-01-01 | m  |
35000
(1 row)

When I use age() I don't get full years. Is there an easy way to round
::reltime off or up without writing a function. Is there any possibility
to use plain SQL only?

Hans


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Inherited tables: How stable is that feature?

[Tom Lane]

Florian Weimer <[EMAIL PROTECTED]> writes:
> Is it likely that table inheritance is going to be removed in future
> PostgreSQL versions (or that the semantics change radically)?

I doubt anyone is planning to remove it.  If anything it'll get better
supported.  Notice that 7.1 now allows update/delete over inheritance
hierarchies, which never worked before --- that doesn't look like a
dying feature to me.

I would not really recommend using multiple inheritance, but single
inheritance seems like a safe-enough bet.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Calculating the age of a person

[[EMAIL PROTECTED]]

You are probably looking for date_part().

E.g. 

SELECT date_part('year',age(birth)),* FROM persons LIMIT 1;


Troy



> 
> I have a table containing the birthdays of various persons. The target
> is to compute the age of a persons.
> 
> persons=# SELECT age(birth), * FROM persons LIMIT 1;
>   age  | id |  name  |   birth| gender |
> income
> ---+++++
> 
>  31 years 4 mons 16 days 23:00 |  1 | Albert | 1970-01-01 | m  |
> 35000
> (1 row)
> 
> When I use age() I don't get full years. Is there an easy way to round
> ::reltime off or up without writing a function. Is there any possibility
> to use plain SQL only?
> 
> Hans
> 
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] are NEW and OLD rule attributes broken?

[Forest Wilkinson]

(Postgres 7.0.3, linux kernel 2.4.2, i386, red hat 7.1)

I'm trying to build rules to automatically populate several tables with 
references to any new rows inserted into a primary key table.  The primary 
key is a sequence.  Here's what's going on:


mydb=# create table foo (fooid serial primary key, foonote text);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_fooid_seq' for 
SERIAL column 'foo.fooid'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for 
table 'foo'
CREATE

mydb=# create table bar (fooid integer references foo (fooid) deferrable 
initially deferred, barnote text);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE

mydb=# create rule brule as on insert to foo do insert into bar (fooid, 
barnote) values ( new.fooid, 'blah');
CREATE 30351 1
mydb=# begin;
BEGIN

mydb=# insert into foo (foonote) values ('test row');
INSERT 30353 1

mydb=# select * from foo;
 fooid | foonote
---+--
 2 | test row
(1 row)
 
mydb=# select * from bar;
 fooid | barnote
---+-
 1 | blah
(1 row)
 
mydb=# commit;
ERROR:   referential integrity violation - key referenced from bar 
not found in foo


What's going on here?  My rule inserted a new row into bar, but the fooid 
it used was not the new value inserted into foo.  It looks like my rule is 
inserting "nextval('foo_fooid_seq')" into the bar table, rather than 
inserting the new primary key from the foo table.  Is this the intended 
behavior?  How can I get the result I want?


Cheers,

Forest

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] are NEW and OLD rule attributes broken?

[Tom Lane]

Forest Wilkinson <[EMAIL PROTECTED]> writes:
> What's going on here?  My rule inserted a new row into bar, but the fooid 
> it used was not the new value inserted into foo.  It looks like my rule is 
> inserting "nextval('foo_fooid_seq')" into the bar table, rather than 
> inserting the new primary key from the foo table.  Is this the intended 
> behavior?

NEW and OLD are effectively macros, not variables.  So yes, you get
extra evaluations of nextval() this way.  I think there are cases where
that is actually what you want (not in this case obviously :-().  It
would be fairly difficult to change, anyway.

> How can I get the result I want?

If you want to copy the data actually inserted, a trigger is a much
better bet than a rule.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly