Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Karsten Hilbert]

On Sun, Apr 24, 2005 at 04:34:31PM -0600, Michael Fuhr wrote:

> > gnumed=> select version();
> > version
> > ---
> >  PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
> 
> That *definitely* isn't anything I'd want.  That version of PostgreSQL
Well :-)

> > gnumed=> create teable test (f timestamp with time zone);
> > CREATE
> 
> Does that really work in 7.1.3?  Trying to create a "teable" fails
This results from "script" logging backspaces into the log
file instead of deleting characters in there and my not
spotting that when cleaning up the log file. Nothing to do with
the question at hand.

>   insert into test (f) values (now());
>   INSERT 37830 1
>   SELECT * from test;
>  f   
>   ---
>2005-04-24 22:25:51.669218+00
>   (1 row)
>   
>   select f, f::timestamp at time zone 'MEZ' from test;
>  f   |  timezone  
>   ---+
>2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
>   (1 row)
Huh ? This IS different time zones from the same timestamp
within the same query, isn't it ??

The fact that different versions of PostgreSQL get it right or
wrong in a variety of ways indicates that the logic may need
to be fixed but does show that in principle it is quite
possible. If that's not what you wanted to do then I did
misunderstand your original question.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] Help-Urgent

[ElayaRaja S]

Hi,
   I am using Redhat Linux 9 and PostgreSQL 7.4.5. While shutdown the
linux server i forget to stop the postgresql. After starting the linux
server, now i am unable to stop the postgres. When stopping the
postgres  i am getting the following error as

bash-2.05b$ pg_ctl status
pg_ctl: postmaster is running (PID: 17173)
Command line was:
/usr/local/pgsql/bin/postmaster

bash-2.05b$ pg_ctl stop
/usr/local/pgsql/bin/pg_ctl: line 274: kill: (17173) - No such process
waiting for postmaster to shut
down
failed
pg_ctl: postmaster does not shut down


Note: please help me to shutdown the postgresql.


-- 
Warm Regards,

S.ElayaRaja
Mobile:  (+91) 98450 59540
E-Mail:  [EMAIL PROTECTED] 
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Calculated bigserial column in a view

[Bruno Wolff III]

On Sun, Apr 24, 2005 at 22:16:14 +0200,
  Zlatko Matic <[EMAIL PROTECTED]> wrote:
> I know that it sounds crazy,
> but I need a bigserial coulumn in a view that is consisted of several tables.
> That column should not be based on bigserial column of any table, but should 
> be a calculated column...
> How can I accomplish it ?

This doesn't really make sense. It sounds like you want a key that is unique
accross the union of several tables and that doesn't change when other rows
in the union are added or removed. There are a couple of ways you might
go about doing something like this. Is this what you really want?

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] question about about future 8.1 and IN, INOUT, and OUT parameters

[Tony Caduto]

I saw that the recently released pl/java has the ablity to use IN, INOUT, and 
OUT parameters.

Is the syntax going to be exactly like it is in Oracle?

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

Re: [GENERAL] Immutable attributes?

[Michael Fuhr]

On Sun, Apr 24, 2005 at 09:26:49PM +0200, Stephane Bortzmeyer wrote:
>
> To protect the database from programming errors (there is a team
> working on the project and some beginners may produce bugs), I would
> like to flag some attributes as immutable, meaning non modifiable in
> an UPDATE. (Typical examples are ID or creation time.)
> 
> Currently, I use triggers:
...
> It is quite painful, since I need a function (with the list of
> immutable attributes) and a trigger per table. If I INHERITS from a
> table, triggers on the parent table are not called if I update the
> child table.

This wouldn't solve all your problems, but you could write a generic
trigger function in a language like PL/Tcl or PL/Python (or PL/Perl
in 8.0 and later) and pass the column name(s) as arguments.  Here's
a minimally-tested example -- if it doesn't work exactly the way
you want then at least it should serve as inspiration:

CREATE FUNCTION check_immutable() RETURNS trigger AS '
for col in TD["args"]:
if TD["new"][col] != TD["old"][col]:
plpy.error(''attribute "%s" is immutable'' % col)
return "OK"
' LANGUAGE plpythonu;

CREATE TABLE foo (
id   serial PRIMARY KEY,
created  date NOT NULL DEFAULT current_date,
name text NOT NULL
);

CREATE TRIGGER check_immutable BEFORE UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE check_immutable('id', 'created');

INSERT INTO foo (name) VALUES ('name 1');
SELECT * FROM foo;
 id |  created   |  name  
++
  1 | 2005-04-24 | name 1
(1 row)

UPDATE foo SET created = '2005-05-01';
ERROR:  plpython: function "check_immutable" failed
DETAIL:  plpy.Error: ('attribute "created" is immutable',)

UPDATE foo SET id = 2;
ERROR:  plpython: function "check_immutable" failed
DETAIL:  plpy.Error: ('attribute "id" is immutable',)

UPDATE foo SET name = 'name 2', created = current_date;
SELECT * FROM foo;
 id |  created   |  name  
++
  1 | 2005-04-24 | name 2
(1 row)

I was going to suggest using contrib/noupdate, but it doesn't appear
to work, at least not the way I was expecting:

CREATE TABLE foo (
idserial PRIMARY KEY,
name  text NOT NULL
);

CREATE TRIGGER check_immutable BEFORE UPDATE ON foo
  FOR EACH ROW EXECUTE PROCEDURE noup('id');

INSERT INTO foo (name) VALUES ('name 1');

UPDATE foo SET id = 2;
WARNING:  id: update not allowed
UPDATE 0

UPDATE foo SET name = 'name 2';
WARNING:  id: update not allowed
UPDATE 0

I expected the second update to succeed since we're not changing
the value of id.  I'm wondering if that's a bug in the module.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Optimising Union Query.

[Jim C. Nasby]

On Sat, Apr 23, 2005 at 10:39:14PM +, Patrick TJ McPhee wrote:
> In article <[EMAIL PROTECTED]>,
> Rob Kirkbride <[EMAIL PROTECTED]> wrote:
> 
> % I've done a explain analyze and as I expected the database has to check 
> % every row in each of the three tables below but I'm wondering if I can 
> 
> This is because you're returning a row for every row in the three
> tables.
> 
> % select l.name,l.id from pa i,locations l where i.location=l.id union 
> % select l.name,l.id from andu i,locations l where i.location=l.id union 
> % select l.name,l.id from idu i,locations l where i.location=l.id;
> 
> You might get some improvement from
> 
>  select name,id from locations
>   where id in (select distinct location from pa union
>select distinct location from andu union
>select distinct location from idu);

Note that SELECT DISTINCT is redundant with a plain UNION. By
definition, UNION does a DISTINCT. In fact, this is going to hurt you;
you'll end up doing 4 distinct operations (one for each SELECT DISTINCT
and one for the overall UNION). Unless some of those tables have a lot
of duplicated location values, you should either use UNION ALLs or drop
the DISTINCTs. Note that going with DISTINCTs is different than what
your original query does.

You should also consider this:

SELECT name, id FROM locations l
WHERE EXISTS (SELECT * FROM pa p WHERE p.location=l.id)
OR EXISTS (SELECT * FROM andu a WHERE a.location=l.id)
OR EXISTS (SELECT * FROM idu i WHERE i.location=l.id)

This query would definately be helped by having indexes on
(pa|andu|idu).location.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Michael Fuhr]

On Sun, Apr 24, 2005 at 07:09:44PM +0200, Karsten Hilbert wrote:
> Isn't the following what you want?
...
> gnumed=> select version();
> version
> ---
>  PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3

That *definitely* isn't anything I'd want.  That version of PostgreSQL
is almost four years old and has serious data loss problems.  Aside
from numerous bug fixes, there have been changes in behavior since
then.

> gnumed=> create teable test (f timestamp with time zone);
> CREATE

Does that really work in 7.1.3?  Trying to create a "teable" fails
with a syntax error in all the versions I tried (7.2.7, 7.3.9,
7.4.7, 8.0.2, all from CVS).  I'll create a "table" instead.

> gnumed=> set time zone '+2:00';
> SET VARIABLE
> gnumed=> show time zone;
> NOTICE:  Time zone is +2:00
> SHOW VARIABLE
> gnumed=> insert into test (f) values (now());
> INSERT 6462134 1
> gnumed=> SELECT * from test;
>f
> 
>  2005-04-24 17:06:10+00
> (1 row)
> 
> gnumed=> select f, f::timestamp at time zone 'MEZ' from test;
>f|timezone
> +
>  2005-04-24 17:06:10+00 | 2005-04-24 18:06:10+01
> (1 row)

The above example gives different results depending on the version
of PostgreSQL, and none of the versions I tested show output in
different time zones.  The following were all run around 22:25 UTC
or a few minutes later:

7.2.7

  create table test (f timestamp with time zone);
  CREATE
  set time zone '+2:00';
  SET VARIABLE
  show time zone;
  psql:foo.sql:4: NOTICE:  Time zone is '+2:00'
  SHOW VARIABLE
  insert into test (f) values (now());
  INSERT 37830 1
  SELECT * from test;
 f   
  ---
   2005-04-24 22:25:51.669218+00
  (1 row)
  
  select f, f::timestamp at time zone 'MEZ' from test;
 f   |  timezone  
  ---+
   2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
  (1 row)

7.3.9

  create table test (f timestamp with time zone);
  CREATE TABLE
  set time zone '+2:00';
  SET
  show time zone;
   TimeZone 
  --
   +2:00
  (1 row)
  
  insert into test (f) values (now());
  INSERT 731399 1
  SELECT * from test;
 f   
  ---
   2005-04-24 22:26:41.733617+00
  (1 row)
  
  select f, f::timestamp at time zone 'MEZ' from test;
 f   |   timezone
  ---+---
   2005-04-24 22:26:41.733617+00 | 2005-04-24 21:26:41.733617+00
  (1 row)

7.4.7

  create table test (f timestamp with time zone);
  CREATE TABLE
  set time zone '+2:00';
  SET
  show time zone;
   TimeZone 
  --
   +2:00
  (1 row)
  
  insert into test (f) values (now());
  INSERT 450835 1
  SELECT * from test;
 f   
  ---
   2005-04-24 22:27:25.631466+00
  (1 row)
  
  select f, f::timestamp at time zone 'MEZ' from test;
 f   |   timezone
  ---+---
   2005-04-24 22:27:25.631466+00 | 2005-04-24 21:27:25.631466+00
  (1 row)

8.0.2

  create table test (f timestamp with time zone);
  CREATE TABLE
  set time zone '+2:00';
  psql:foo.sql:3: ERROR:  unrecognized time zone name: "+2:00"
  set time zone '+2';
  SET
  show time zone;
   TimeZone 
  --
   02:00:00
  (1 row)
  
  insert into test (f) values (now());
  INSERT 0 1
  SELECT * from test;
f   
  --
   2005-04-25 00:28:33.34721+02
  (1 row)
  
  select f, f::timestamp at time zone 'MEZ' from test;
f   |   timezone   
  --+--
   2005-04-25 00:28:33.34721+02 | 2005-04-25 01:28:33.34721+02
  (1 row)

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] SQLException "Connection is closed. Operation is not permitted"

[Rajiv Verma]

I'm accessing postgres database through tomcat 4.3.1.
I'm able to execute the select and update query through my application
but Insert query is giving following SQLException :
Connection is closed.  Operation is not permitted.

If I execute the same insert query by simple java program using jdbc
connection, It's working. But the same query is giving above exception
in tamcat.

Please help me. Thanx in advance.

following is the ExceptionStackTrace :

IN insert : insert into account(accNo,traderId,dbcNo,bal) values (1,
'rajiv', 1, 5000.0 )
Insertion Failed : Connection is closed.  Operation is not permitted.
Connection is closed.  Operation is not permitted.
at 
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:503)
at 
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at 
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
at 
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
at src.DataManager.Insert(DataManager.java:64)
at src.Bank.createAccount(Bank.java:149)
at src.Trader.getAccount(Trader.java:314)
at CreateAccount.doPost(CreateAccount.java:144)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:716)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:200)
at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:146)
at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:144)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at 
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2358)
at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:133)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at 
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:127)
at 
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at 
org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at 
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at 
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at 
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at 
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:536)

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

   http://archives.postgresql.org

Re: [GENERAL] Immutable attributes?

[David Fetter]

On Sun, Apr 24, 2005 at 09:26:49PM +0200, Stephane Bortzmeyer wrote:
> To protect the database from programming errors (there is a team
> working on the project and some beginners may produce bugs), I would
> like to flag some attributes as immutable, meaning non modifiable in
> an UPDATE. (Typical examples are ID or creation time.)
> 
> Currently, I use triggers:

You could use RULEs instead, but the effect would be similar.

> PostgreSQL 7.4, switching to 8.0 would be difficult.

Now is easier than later.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

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

[David Roussel]

So I have a scenario in which account creation at the application 
layer generates a set of tables and indexes. The tables created have 
foreign keys to tables in use (and frequently read from and written 
to) by the rest of the application. Occasionally I was getting 
deadlocks, and this definitely explains why, if creating foreign keys 
requires an AccessExclusiveLock on the table to which the key refers.

Ideally, this DDL work should occur in a transaction to avoid partial 
creation of the relevant objects, but it seems like it will always run 
the risk of generating deadlocks in a production environment. Blocking 
is less of an issue because the transaction shouldn't ever take 
terribly long, but deadlocks always strike me as a red flag, 
especially in a production application environment.

Is there a best practice or suitable workaround for this sort of 
scenario?

Deadlock only occurs when resources are acquired out of order by two or 
more concurrent processes.  So you can avoid deadlock by acquiring 
locks in the same order.  This of course is non-trivial, if the 
different processes are doing different tasks.
One way is to acquire an exclusive lock at the very beginning of your 
transaction, say on the users table.  Perhaps do this at the very 
begging, i.e. before the trigger fires.   Of course whether this works 
depends on what you are deadlocking on.  You probably need to analyse 
the lock cycles to determine which locks you need to acquire early.

Another possibility would be to change the trigger to write a message 
to a queue table, and have another process service the queue and add 
the users.  This way you are splitting the transaction in half, and 
thus releasing the locks obtained before the trigger fired.

Of course you don't get this problem in oracle.  I find its optimistic 
locks wonderful.

David
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Optimising Union Query.

[Patrick TJ McPhee]

In article <[EMAIL PROTECTED]>,
Rob Kirkbride <[EMAIL PROTECTED]> wrote:

% I've done a explain analyze and as I expected the database has to check 
% every row in each of the three tables below but I'm wondering if I can 

This is because you're returning a row for every row in the three
tables.

% select l.name,l.id from pa i,locations l where i.location=l.id union 
% select l.name,l.id from andu i,locations l where i.location=l.id union 
% select l.name,l.id from idu i,locations l where i.location=l.id;

You might get some improvement from

 select name,id from locations
  where id in (select distinct location from pa union
   select distinct location from andu union
   select distinct location from idu);

this query might be helped by an index on location in each of those
three tables, but probably not.
-- 

Patrick TJ McPhee
North York  Canada
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] psqlodbc MSAccess and Postgresql

[Zlatko Matic]

Hello,
 
I am currently migrating my MSDE/Access (Access Project) aplication to 
PostgreSQL.
I have experienced a lot of obstacles till now, but anyway it seems quite 
posible to make a good aplication by this combination of Access front-end and 
PostgreSQL base.
I use the following ODBC settings for linked tables successfully:
[ODBC]DRIVER=PostgreSQLUID=zmaticUseServerSidePrepare=0ByteaAsLongVarBinary=0BI=0TrueIsMinus1=1DisallowPremature=0UpdatableCursors=1LFConversion=1ExtraSysTablePrefixes=dd_CancelAsFreeStmt=0Parse=1BoolsAsChar=1UnknownsAsLongVarchar=0TextAsLongVarchar=1UseDeclareFetch=1Ksqo=1Optimizer=1CommLog=0Debug=0MaxLongVarcharSize=8190MaxVarcharSize=254UnknownSizes=0Socket=4096Fetch=100ConnSettings=CLIENT%5fENCODING%3dWIN1250ShowSystemTables=0RowVersioning=1ShowOidColumn=0FakeOidIndex=0Protocol=6.4ReadOnly=0PORT=5432SERVER=localhostDATABASE=MyDatabase
 
 
Tips and tricks:
1. Disable "Recognize Unique Indexes" every time you link tables. That will 
allow you to give proper primary keys to Access (you will be prompted). 
Otherwise, Access do it quite bad.
2- Use Row Versioning
3. Every table must have numeric primary key. Don't use text field as 
primary key. Access will be confused and you will have "#DELETED#" in your 
tables.
4. Instead of having JET queries on linked tables, it is better to have 
good pass-through query. But it doesn't accept parameters, so you will have to 
combine regular JEt queries with pass-through queries. I use pass-through 
queries and server-side functions (for calculated columns) that prepare 
recordset and then filtrate it by regular JET query additionaly (for example: 
start and end date)...
Other possibility is to use server views linked as tables in 
Access...
 
Greetings
 
Zlatko

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  To: pgsql-general@postgresql.org 
  
  Sent: Friday, April 22, 2005 6:28 
AM
  Subject: [GENERAL] psqlodbc MSAccess and 
  Postgresql 
  
  Thanks for the help with /dt... Mike, your post helped me to discover 
  psql command window, I had been trying out the pgAdmin ...   
   
  I found an interesting article regarding MSAccess as a front end to 
  Postgresql, so I downloaded the psqlodbc installer.  I will be getting up 
  the nerve to run the install and go into windows odbc and install that driver 
  so I can use MSAccess with Postgresql ... any tips or caveats 
  appreciated
   
  This is the link which is guiding me
  http://database.sarang.net/database/postgres/postodbc/faq.html
   
   

[GENERAL] Calculated bigserial column in a view

[Zlatko Matic]

I know that it sounds crazy,
but I need a bigserial coulumn in a view that is consisted of several 
tables.
That column should not be based on bigserial column of any table, but 
should be a calculated column...
How can I accomplish it ?
 
Thanks in advance.
 
Zlatko

[GENERAL] Immutable attributes?

[Stephane Bortzmeyer]

To protect the database from programming errors (there is a team
working on the project and some beginners may produce bugs), I would
like to flag some attributes as immutable, meaning non modifiable in
an UPDATE. (Typical examples are ID or creation time.)

Currently, I use triggers:

CREATE OR REPLACE FUNCTION check_immutable() RETURNS TRIGGER
AS 'BEGIN 
  IF NEW.id != OLD.id OR NEW.created != OLD.created THEN
   RAISE EXCEPTION ''Change not allowed in that table'';
  END IF;
  RETURN NEW;   
END;'
LANGUAGE PLPGSQL;

CREATE TRIGGER check_immutable
   BEFORE UPDATE ON MyTable
   FOR EACH ROW
   EXECUTE PROCEDURE check_immutable();

It is quite painful, since I need a function (with the list of
immutable attributes) and a trigger per table. If I INHERITS from a
table, triggers on the parent table are not called if I update the
child table.

Is there a better way? I do not find a constraint IMMUTABLE for
attributes.

PostgreSQL 7.4, switching to 8.0 would be difficult.


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

Re: [GENERAL] Primary Key and Indices

[Rich Shepard]

On Sun, 24 Apr 2005, Ragnar Hafstað wrote:
yes. for example, only the first one can be used for ORDER BY org_id
gnari,
  Thank you. I thought it might be the case but I wasn't sure.
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] New PostgreSQL book coming

[Antoine Solomon]

You should try to get it at www.bookpool.com since opensource books are 43% off

On 4/24/05, Bruce Momjian  wrote:
> I have been recommending the Korry Douglas book to people because it is
> considered one of the best PostgreSQL books available.  I see a new
> edition is coming out in May 2005 and covers 8.0:
> 
> 
> http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1K8b8TLBBo&isbn=0672327562&itm=1
> 
> --
>   Bruce Momjian|  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us   |  (610) 359-1001
>   +  If your life is a hard drive, |  13 Roberts Road
>   +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 


-- 
Antoine W. Solomon Jr.

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

   http://archives.postgresql.org

Re: [GENERAL] Primary Key and Indices

[Ragnar =?ISO-8859-1?Q?Hafsta=F0?=]

On Sun, 2005-04-24 at 09:49 -0700, Rich Shepard wrote:
>I'm converting mysql tables to postgres. One of the tables has this:
> 
>PRIMARY KEY (org_id, contact_id),
>KEY contact (contact_id, org_id)
> 
>Is there really a difference in the two indices if the sequence of fields
> is reversed?

yes.
for example, only the first one can be used for ORDER BY org_id

gnari



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Karsten Hilbert]

On Sun, Apr 24, 2005 at 10:49:13AM -0600, Michael Fuhr wrote:

> > > As far as I know, it's not possible to get output like the following
> > > from the same query if the data type is timestamp with time zone:
> > > 
> > > 2005-04-21 15:00:00-07
> > > 2005-04-21 22:00:00+00
> > 
> > Doesn't "at time zone" do what you need ?
> 
> Not as far as I can tell, because if the result is timestamp with
> time zone then it's rotated to the local time zone for display.  If
> you can post a counterexample then I'd be happy to stand corrected.

Isn't the following what you want?


»Script« wurde gestartet: Sun Apr 24 19:05:06 2005
[EMAIL PROTECTED]:~> psql -d gnumed -U any-doc
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help on internal slash commands
   \g or terminate with semicolon to execute query
   \q to quit

gnumed=> select version();
version
---
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

gnumed=> create teable test (f timestamp with time zone);
CREATE
gnumed=> set time zone '+2:00';
SET VARIABLE
gnumed=> show time zone;
NOTICE:  Time zone is +2:00
SHOW VARIABLE
gnumed=> insert into test (f) values (now());
INSERT 6462134 1
gnumed=> SELECT * from test;
   f

 2005-04-24 17:06:10+00
(1 row)

gnumed=> select f, f::timestamp at time zone 'MEZ' from test;
   f|timezone
+
 2005-04-24 17:06:10+00 | 2005-04-24 18:06:10+01
(1 row)

gnumed=> \q
[EMAIL PROTECTED]:~> exit
exit

»Script« beendet: Sun Apr 24 19:06:59 2005


Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(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

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Michael Fuhr]

On Sun, Apr 24, 2005 at 05:21:41PM +0200, Karsten Hilbert wrote:
> >
> > As far as I know, it's not possible to get output like the following
> > from the same query if the data type is timestamp with time zone:
> > 
> > 2005-04-21 15:00:00-07
> > 2005-04-21 22:00:00+00
> 
> Doesn't "at time zone" do what you need ?

Not as far as I can tell, because if the result is timestamp with
time zone then it's rotated to the local time zone for display.  If
you can post a counterexample then I'd be happy to stand corrected.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

   http://archives.postgresql.org

[GENERAL] Primary Key and Indices

[Rich Shepard]

  I'm converting mysql tables to postgres. One of the tables has this:
  PRIMARY KEY (org_id, contact_id),
  KEY contact (contact_id, org_id)
  Is there really a difference in the two indices if the sequence of fields
is reversed?
Thanks,
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Delphi personal (was Playing with PostgreSQL and Access VBA)

[Tony Caduto]

All you need is the Personal Edition of say Delphi 6 or Delphi 7, and 
even Delphi 2005.
For Delphi 2005 personal edition see this link:
http://andy.jgknet.de/oss/kylix/wiki/index.php/Delphi_2005_Personal_Edition_xmlrtl.dcp_fake
Heck, Delphi 5 is still extremely viable and can do anything 6,7 or 2005 
can do, and I bet you can find version 5 on ebay or elseware for dirt cheap.

The personal editions do not include database access components by 
themselves, but you certainly can use third party tools such as Zeos 
(http://www.zeoslib.net) or those from
http://www.microolap.com.
You could even grab a copy of libpq.pas if you want to do direct access 
to PG.

You get what you pay for and there is nothing available for Python or 
WXwidgets that even comes close to Delphi for RAD/GUI database development.
Not to mention all the other stuff you can do with Delphi, it's also a 
great programming language for console apps, services, TCP/IP servers.  
There are even remote control applications built with it ala PC Anywhere.

Plus if you call borland, I am sure they would give you the upgrade 
price if you have a old copy of VB laying around for a competive upgrade.

 
I was very impressed by what one member said regarding Delphi, but, 
when I looked at pricing,... well I would have to lie through my 
teeth to get the cheap academic version, and the personal version 
sounds like it doesnt have the file access abilities for PostgreSQL. 
And the prices for enterprise versions at programmersparadise.com  
like $4000, sort of puts me off. 


---(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

Re: [GENERAL] Had a problem with pg_clog

[Tom Lane]

Peter Wiersig <[EMAIL PROTECTED]> writes:
> On Sat, Apr 23, 2005 at 08:19:31PM -0400, Tom Lane wrote:
>> Peter Wiersig <[EMAIL PROTECTED]> writes:
>>> vacuumdb -z miwabar
>>> PANIC:  open of /var/lib/pgsql/data/pg_clog/ failed:
>>> Keine Berechtigung
>>> server closed the connection unexpectedly
>> 
>> What PG version is this exactly?

> It's 7.3.9 from SUSE rpms postgresql-7.3.9-3

Hmm.  7.3.9 has all the known patches for hint-bit updates and premature
clog truncation, but maybe you have found a new bug.  Can you repeat
this failure from a standing start --- that is, initdb, load your data
dump, vacuumdb, PANIC?  If so I would very much appreciate a copy of
your data dump, if that's possible.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] psqlODBC and Access

[Tony Caduto]

Let's not forget about PG Lightning Admin :-)
http://www.amsoftwaredesign.com
In case you don't like PgAdminIII you may try commercial products like
EMS PostgreSQL Manager
http://sqlmanager.net/products/postgresql/manager
What they call "Visual Database Designer" might just be what you are 
looking for. It's an MS-Access-like, visual display of tables and 
relations. But remember, it's a frontend and not a modelling tool.



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] timezone() with timeofday() converts the wrong direction?

[Karsten Hilbert]

> As far as I know, it's not possible to get output like the following
> from the same query if the data type is timestamp with time zone:
> 
> 2005-04-21 15:00:00-07
> 2005-04-21 22:00:00+00

Doesn't "at time zone" do what you need ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] [Question]batch execute sql command

[Michael Fuhr]

On Sun, Apr 24, 2005 at 02:08:33PM +0100, neo anderson wrote:
>
> i encounter a question when create database schema.
> because i have a sql script, in which contains lots of
> sql command, including insert, create table, create
> index and so on. however, i do know weather postgresql
> supports batch executing sql script or not, for i try
> to run scrtip like what oracle does; yet it seemly
> doesn't  work. would anyone please to telll me how to
> do that?

>From within psql you can run "\i filename"; from the command line
you can run "psql -f filename" or, if your shell supports redirection,
"psql < filename".  See the psql documentation for details:

http://www.postgresql.org/docs/8.0/interactive/app-psql.html

If that's not what you're looking for, then please provide more
information about what you're trying to do.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

   http://archives.postgresql.org

[GENERAL] Locale question

[Matthew T. O'Connor]

I have a (hopefully simple) question regarding locale.
I am migrating a postgresql database from a server running FC1 & 
PostgreSQL 7.4 to a newer machine running FC2 and PostgreSQL 8.0.  I 
dumped the data from the old server using pg_dumpall and restored it to 
the new server with no problems, and while testing the new server 
everything seems to be OK.

However, I did notice that the client and server encoding settings on 
the old server are set to SQL_ASCII whereas those same settings on the 
new server are set to LATIN1.

I'm wondering if I might wind up with some lurking problems since I 
don't really understand all the implications of locale settings.

Thanks,
Matthew O'Connor
Details below:
On the old server
-bash-2.05b$ cat /etc/redhat-release
Fedora Core release 1 (Yarrow)
-bash-2.05b$ locale
LANG=en_US
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_COLLATE="en_US"
LC_MONETARY="en_US"
LC_MESSAGES="en_US"
LC_PAPER="en_US"
LC_NAME="en_US"
LC_ADDRESS="en_US"
LC_TELEPHONE="en_US"
LC_MEASUREMENT="en_US"
LC_IDENTIFICATION="en_US"
-bash-2.05b$ psql template1
Welcome to psql 7.4.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
  \h for help with SQL commands
  \? for help on internal slash commands
  \g or terminate with semicolon to execute query
  \q to quit
template1=# show all;
 name  |setting
+---
client_encoding| SQL_ASCII
lc_collate | en_US
lc_ctype   | en_US
lc_messages| en_US
lc_monetary| en_US
lc_numeric | en_US
lc_time| en_US
server_encoding| SQL_ASCII
On the new server:
-bash-2.05b$ cat /etc/redhat-release
Fedora Core release 2 (Tettnang)
-bash-2.05b$ locale
LANG=en_US
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_COLLATE="en_US"
LC_MONETARY="en_US"
LC_MESSAGES="en_US"
LC_PAPER="en_US"
LC_NAME="en_US"
LC_ADDRESS="en_US"
LC_TELEPHONE="en_US"
LC_MEASUREMENT="en_US"
LC_IDENTIFICATION="en_US"
-bash-2.05b$ psql template1
Welcome to psql 8.0.2, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
  \h for help with SQL commands
  \? for help with psql commands
  \g or terminate with semicolon to execute query
  \q to quit
template1=# \o foo
template1=# show all;
 name  |setting
+---
client_encoding| LATIN1
lc_collate | en_US
lc_ctype   | en_US
lc_messages| en_US
lc_monetary| en_US
lc_numeric | en_US
lc_time| en_US
server_encoding| LATIN1
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

[GENERAL] [Question]batch execute sql command

[neo anderson]

Hi, 
i encounter a question when create database schema.
because i have a sql script, in which contains lots of
sql command, including insert, create table, create
index and so on. however, i do know weather postgresql
supports batch executing sql script or not, for i try
to run scrtip like what oracle does; yet it seemly
doesn't  work. would anyone please to telll me how to
do that? (i read the document -
http://www.postgresql.org/docs/8.0/static/index.html -
but seemly there ain't no such description in it)
i appreciate any suggestion, sincerely.
jason

Send instant messages to your online friends http://uk.messenger.yahoo.com 

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] optimal hardware for postgres?

[William Yu]

peter pilsl wrote:
I was now wondering which hardware would fit this server best. I think 
about 2GB RAM, a fast Raid1 and now I'm not sure about the CPU.
I was considering 64-bit AMD : A Athlon 64 FX-51 or a Opteron 2.2GHz.
The hosting system will be a 64-Bit linux.
While the A64 and Opteron are two sides of the same coin, I'd avoid 
using A64s because you need ECC reg memory to keep your server stable.

To get fast RAID, you need fast I/O connections. Up to just a month ago, 
that probably meant you had to get a hefty Dual Opteron MB in order to 
get PCI-X slots. The other option now is use PCIe. Ordinarily, most 
people use fast graphics cards in PCIe slots but you could pop in a PCIe 
RAID controller instead. This gives you the option of looking at 
"workstation-class" platforms versus a more expensive server platform.

On other note -- if you are thinking about SMP Opteron, you may actually 
get better performance from 1x275 (Dual Core 2.2ghz) versus 2x248 
(2.2ghz). Full duals have twice the bandwidth but without good NUMA 
support, memory has to be interleaved between CPUs. This means when 
reading chunks of continous memory, CPUs have to continually request 
data from the other CPU. Because the CPU<->CPU requests on DC Opterons 
are so much faster, AMD's own performance optimization guide says to put 
2 threads onto a single DC chip leaving other CPUs in the system idle 
versus trying to fully balance between chips.

Does postgres benefit from 64-bit AMD's or would I be better off with a 
cheaper AthlonXP or even with a Pentium or a more expensive Xeon?
Or is my question faulty at all, cause CPU is only 20% of the whole system.
My experience going from RH9 32-bit to FCx 64-bit was huge increases in 
speed.

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

[GENERAL] New PostgreSQL book coming

[Bruce Momjian]

I have been recommending the Korry Douglas book to people because it is
considered one of the best PostgreSQL books available.  I see a new
edition is coming out in May 2005 and covers 8.0:


http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1K8b8TLBBo&isbn=0672327562&itm=1

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] PHP and Postgresql on Windows

[Hannes Dorbath]

On 23.04.2005 00:03, David Roussel wrote:
Try PEAR DB.
http://pear.php.net/package/DB
It's a database abstraction layer, which means that you can change DB 
later, with less hasle.
http://www.powerpostgresql.com/Downloads/database_depends_public.swf :)
I really dislike db abstraction layers and PEAR::DB being one of the 
worst of all ;p

Just look at the ugly code like the LIMIT emulation for DBMS that don't 
support it native. If you want to use the limited feature set provided 
by PEAR::DB there is little point in using PostgreSQL at all. Just pick 
a stupid storage engine like MySQL or SQLite and go on ;)

---(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

Re: [GENERAL] psqlODBC and Access

[Hannes Dorbath]

On 23.04.2005 03:23, [EMAIL PROTECTED] wrote:
Should I not use MONEY in defining  such fields to 
be used with ACCESS? Is there a different TYPE that I should use  in my 
definition?  Thanks!
From http://www.postgresql.org/docs/8.0/interactive/datatype-money.html
"The money type is deprecated. Use numeric or decimal instead, in 
combination with the to_char function."

By the way, what would you suggest as a tool to administer the PostgreSQL  
databases. I mean, something sort of graphical which would display tables and  
names and relationships.
In case you don't like PgAdminIII you may try commercial products like
EMS PostgreSQL Manager
http://sqlmanager.net/products/postgresql/manager
What they call "Visual Database Designer" might just be what you are 
looking for. It's an MS-Access-like, visual display of tables and 
relations. But remember, it's a frontend and not a modelling tool.

For modelling better look at:
http://www.casestudio.com/
I don't think there's something better out there for the money, but it 
takes some time to learn using it.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Had a problem with pg_clog

[Peter Wiersig]

On Sat, Apr 23, 2005 at 08:19:31PM -0400, Tom Lane wrote:
> Peter Wiersig <[EMAIL PROTECTED]> writes:
> > vacuumdb -z miwabar
> > PANIC:  open of /var/lib/pgsql/data/pg_clog/ failed:
> >Keine Berechtigung
> > server closed the connection unexpectedly
> 
> What PG version is this exactly?

It's 7.3.9 from SUSE rpms postgresql-7.3.9-3

-- 
Peter

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] optimal hardware for postgres?

[peter pilsl]

I'm just going to buy a new server which will mainly run a 
postgreSQL-database and a apache2 with mod_perl and do little more than 
deliver loads of index-based select-queries. But it will have to do a 
lot of them. And it will be master for a replication to slaves.
As always of course we dont know how many selects we'll have to face. 
There will be "many" and the current Athlon1800+ with 1GB Ram is too 
slow. (to its excuse : It has to perform loads of other tasks as well)

I was now wondering which hardware would fit this server best. I think 
about 2GB RAM, a fast Raid1 and now I'm not sure about the CPU.
I was considering 64-bit AMD : A Athlon 64 FX-51 or a Opteron 2.2GHz.
The hosting system will be a 64-Bit linux.

Does postgres benefit from 64-bit AMD's or would I be better off with a 
cheaper AthlonXP or even with a Pentium or a more expensive Xeon?
Or is my question faulty at all, cause CPU is only 20% of the whole system.

Any comments appretiated,
thnx,
peter

--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fae +43 699 4 3574035
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]