Re: [GENERAL] Utility of OIDs in postgres

[Martijn van Oosterhout]

On Thu, May 03, 2007 at 10:58:38AM +1200, Brent Wood wrote:
 As oid is unique across all tables (in fact all database objects), but 
 serial is unique within a table, there are odd cases like this where 
 using an oid in each table ensures an automatic unique key in the view. 
 So oids can be useful.

As pointed out already, this isn't true. However, for the case you
provide you can also use tableoid which uniquely identifies the table.
Thus the combination of a serial primary and table oid is unique across
a single DB.

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Update violating constraint

[Alban Hertroys]

Naz Gassiep wrote:
 Hi,
 I'm trying to do an update on a table that has a unique constraint
 on the field, I need to update the table by setting field = field+1 
 however if this does not perform the updates on the table in a proper
 order (from last to first) then the update will cause a violation of the
 index *during* the update even though the table would be consistent
 after the update completes. So the update fails. How do I get around
 this without removing the constraint?

I think you're looking for deferrable constraints; see:

http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

   http://archives.postgresql.org/

[GENERAL] Cross-schema inheritence problem

[Alban Hertroys]

Hello all,

I'm trying to inherit a table from the public schema as a table with the
same name in a user-specific schema, but I can't get it to work.

Say I have:
CREATE TABLE test (test_id serial PRIMARY KEY, name text NOT NULL);

CREATE SCHEMA alban AUTHORIZATION alban;

ALTER USER alban SET search_path TO alban;

CREATE TABLE alban.test () INHERITS (public.test);
ERROR:  must be owner of relation test

Is there some way around this?

I would appreciate this to work, as I need to seperate data of two
applications where the 2nd application extends the data of the 1st.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

Re: [GENERAL] forcing use of a specific (expression) index?

[Richard Huxton]

Dan Weber wrote:

I made an expression index specifically for that where clause:

CREATE INDEX special_testing_idx on my_table (((bool_1 or
int_1 = 0) AND (int_2 IS NULL) AND (int_3 IS
NULL) AND (protocol = 2)));


No, you haven't. What you've done here is create an index *for that 
expression*. And it's not terribly useful because your expression will 
only have two possible values: true, false.


What you were after is something like:

CREATE INDEX special_idx2 ON my_table (id) WHERE (...long expression...)

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Have I b0rked something? Slow comparisons on where x in (...)

[Richard Huxton]

Stephen Harris wrote:

On Wed, May 02, 2007 at 12:45:08PM -0700, Dann Corbit wrote:

Have you done a vacuum on the table recently?


We vacuum daily and cluster weekly after the nightly activities have been
performed.


IN list, then the IN list might benefit from a bit of analysis for


The IN list is just a set of integers (it's an integer index) generated
from some selects on other tables earlier in our processing.  I don't
have any choice as to what is in the IN list :-)


Try creating a temporary table, populating with the list and joining 
against it. That's probably your best bet for a long list of target values.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Cross-schema inheritence problem

[Alban Hertroys]

Alban Hertroys wrote:
 Hello all,
 
 I'm trying to inherit a table from the public schema as a table with the
 same name in a user-specific schema, but I can't get it to work.
 
 Say I have:
 CREATE TABLE test (test_id serial PRIMARY KEY, name text NOT NULL);
 
 CREATE SCHEMA alban AUTHORIZATION alban;
 
 ALTER USER alban SET search_path TO alban;
 
 CREATE TABLE alban.test () INHERITS (public.test);
 ERROR:  must be owner of relation test
 
 Is there some way around this?

Oh never mind, that works the wrong way around anyway. I need VIEWs.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

   http://archives.postgresql.org/

Re: [GENERAL] Update violating constraint

[Richard Huxton]

Alban Hertroys wrote:

Naz Gassiep wrote:

Hi,
I'm trying to do an update on a table that has a unique constraint
on the field, I need to update the table by setting field = field+1 



I think you're looking for deferrable constraints; see:

http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html


Which won't work with unique constraints unfortunately. That's because 
they're implemented through a unique index.


The work-around is to do: field = -field then field = -field + 1 or similar.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 1: 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] Update violating constraint

[Michael Glaesemann]

On May 2, 2007, at 23:36 , Naz Gassiep wrote:


I don't
know why you'd ever use your second option ever, as it virtually
guarantees problems at a random point in your DB's growth.


There may be cases where the values are not all positive so you can't  
use the -1 * technique, but the offset will work. It depends on the  
nature of your data. One size doesn't necessarily fit all.


Michael Glaesemann
grzm seespotcode net



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

Re: [GENERAL] C functions under windows

[Andrei Kovalevski]

Islam Hegazy wrote:

Hi all
 
I have postgresql server installed on a windows machine and I want to 
retrieve data using C functions. I followed the steps in the 
documentation but it didn't work for windows. I created a .dll 
projects for my functions but postgres.h calls .h files that I can't 
find on the windows machine like  strings.h. I tested my functions 
on another server installed on a linix machine and it worked 
correctly. So the problem appears to be in the include files under 
windows.
 
Any idea how to solve this problem...
 
Regards

Islam Hegazy


If you use MSVC - try to setup 'Additional include directories' for your 
project. It should point to the PostgreSQL 'include' folder.


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

Re: [GENERAL] forcing use of a specific (expression) index?

[Dan Weber]

Thanks to you and Tom.  The partial index solution is working splendidly.

On 5/3/07, Richard Huxton [EMAIL PROTECTED] wrote:

Dan Weber wrote:
 I made an expression index specifically for that where clause:

 CREATE INDEX special_testing_idx on my_table (((bool_1 or
 int_1 = 0) AND (int_2 IS NULL) AND (int_3 IS
 NULL) AND (protocol = 2)));

No, you haven't. What you've done here is create an index *for that
expression*. And it's not terribly useful because your expression will
only have two possible values: true, false.

What you were after is something like:

CREATE INDEX special_idx2 ON my_table (id) WHERE (...long expression...)

--
   Richard Huxton
   Archonet Ltd



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

[GENERAL] psql access of user's environmental variables

[Paul Tilles]

I need to use the value of an environment variable as part of an SQL 
query within psql.


I can do the following withing psql:

\set  local_site  `echo  $FXA_LOCAL_SITE

\echo  local site =  :local_site

The result is local_site = xxx  which is correct.

What I really want to do is the following:

\set  local_site  `echo  $FXA_LOCAL_SITE

UPDATE  table_name  SET  office_id =  :local_site;

This results in the message

  column  xxx does not exist

Is there any way that I can use the value of the FXA_LOCAL_SITE env 
variable in my UPDATE statement?


Paul Tilles



---(end of broadcast)---
TIP 1: 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] Have I b0rked something? Slow comparisons on where x in (...)

[Stephen Harris]

On Wed, May 02, 2007 at 05:59:49PM -0400, Tom Lane wrote:
 Stephen Harris [EMAIL PROTECTED] writes:

select stuff from table where index_key in ( .
join(,,keys %hash) . ) AND non_index_row in ('xyz','abc','def')

 In what, a seq scan?

Yeah, if the number of comparisons exceeds 156 then it switched from
index scan to sequential scan.

 time, I can see where the time might get eaten up.  Where is the
 index_key column in the tuples, exactly?

First column.

-- 

rgds
Stephen

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

Re: [GENERAL] Update violating constraint

[Richard Broersma Jr]

  update foo
  set field = -1 * (field + 1);
  update foo
  set field = -1 * field
  where field  0;
 
 Yes, in fact I actually use option one already in the handling of sql
 trees, so I'm annoyed with myself for not figuring that out. I don't
 know why you'd ever use your second option ever, as it virtually
 guarantees problems at a random point in your DB's growth.


If you are updating a large portion of your tree, you will probably want to 
throw in a vacuum in
between the two updates.  This should reduce the bloat caused by dead tuples in 
both your index
and table.

Regards,
Richard Broersma Jr.

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

Re: [GENERAL] Update violating constraint

[Csaba Nagy]

 If you are updating a large portion of your tree, you will probably want to 
 throw in a vacuum in
 between the two updates.  This should reduce the bloat caused by dead tuples 
 in both your index
 and table.

... but that will only work if you can commit the first set of changes
before you get to the end result, possibly having an inconsistent state
for the duration of the vacuum... if you want all in one transaction,
vacuum will not help.

Cheers,
Csaba.


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

Re: [GENERAL] psql access of user's environmental variables

[Tom Lane]

Paul Tilles [EMAIL PROTECTED] writes:
 What I really want to do is the following:

 \set  local_site  `echo  $FXA_LOCAL_SITE
 UPDATE  table_name  SET  office_id =  :local_site;

 This results in the message
column  xxx does not exist

Yes, because you have no quotes in the value of the variable, so that
update looks to the server like
UPDATE  table_name  SET  office_id =  xxx;

After some fooling around, the easiest way to get the needed quotes is
to embed them in the echo result:

\set local_site `echo '$FXA_LOCAL_SITE'`

BTW, does your psql really let you leave off the trailing ` like
that?  Mine doesn't.

regards, tom lane

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

[GENERAL] Stored procedure

[Thorsten Kraus]

Hi NG,

I want to write a stored procedure which creates a table in my 
PostgreSQL database. The procedure has one input parameter: the table name.

Here is my first try, but that does not work:
-- 



CREATE OR REPLACE FUNCTION create_geom_table(text) RETURNS void AS $$
DECLARE
   --table_name TEXT;
BEGIN
---
CREATE TABLE table_name
(
 id integer,
 time timestamp without time zone,
 geom geometry,
 CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
 CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 
'MULTIPOLYGON'::text OR geom IS NULL),

 CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO admin;

CREATE INDEX geo_index ON table_name USING gist(geom);

---
ALTER FUNCTION create_geom_table(table_name) OWNER TO admin;
END;
$$ LANGUAGE plpgsql;

--

Can someone tell me what's wrong with this and what I have to change?

Regards,
Thorsten

Re: [GENERAL] Update violating constraint

[Alban Hertroys]

Richard Huxton wrote:
 Alban Hertroys wrote:
 Naz Gassiep wrote:
 Hi,
 I'm trying to do an update on a table that has a unique constraint
 on the field, I need to update the table by setting field = field+1 
 
 I think you're looking for deferrable constraints; see:

 http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html
 
 Which won't work with unique constraints unfortunately. That's because
 they're implemented through a unique index.

I appreciate the complexities involved, but that really ought to work on
a single statement. I recall seeing something along these lines on the
TODO list some time ago?

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

Re: [GENERAL] psql access of user's environmental variables

[Paul Tilles]

Tom,
Thank you. That works.

My psql does not allow me to leave off the trailing `.

It is my typing that is the problem.

Paul

Tom Lane wrote:

Paul Tilles [EMAIL PROTECTED] writes:
  

What I really want to do is the following:



  

\set  local_site  `echo  $FXA_LOCAL_SITE
UPDATE  table_name  SET  office_id =  :local_site;



  

This results in the message
   column  xxx does not exist



Yes, because you have no quotes in the value of the variable, so that
update looks to the server like
UPDATE  table_name  SET  office_id =  xxx;

After some fooling around, the easiest way to get the needed quotes is
to embed them in the echo result:

\set local_site `echo '$FXA_LOCAL_SITE'`

BTW, does your psql really let you leave off the trailing ` like
that?  Mine doesn't.

regards, tom lane
  


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

Re: [GENERAL] Stored procedure

[Hakan Kocaman]

Hi,
 
Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
 
 
Best Regards

Hakan Kocaman
Software-Development
 
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
 
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
 
digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast 
 




From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thorsten 
Kraus
Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure


Hi NG,

I want to write a stored procedure which creates a table in my 
PostgreSQL database. The procedure has one input parameter: the table name.
Here is my first try, but that does not work:

--
 

CREATE OR REPLACE FUNCTION create_geom_table(text) RETURNS void AS $$ 
DECLARE 
--table_name TEXT; 
BEGIN 
--- 
CREATE TABLE table_name 
( 
  id integer, 
  time timestamp without time zone, 
  geom geometry, 
  CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2), 
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 
'MULTIPOLYGON'::text OR geom IS NULL), 
  CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1) 
) 
WITHOUT OIDS; 
ALTER TABLE table_name OWNER TO admin; 

CREATE INDEX geo_index ON table_name USING gist(geom); 

--- 
ALTER FUNCTION create_geom_table(table_name) OWNER TO admin; 
END; 
$$ LANGUAGE plpgsql; 


--

Can someone tell me what's wrong with this and what I have to change?

Regards,
Thorsten



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

Re: [GENERAL] Feature request - have postgresql log warning when new sub-release comes out.

[Leif B. Kristensen]

On Thursday 26. April 2007 20:12, Jon Sime wrote:
I run 8.2.x on a Gentoo/x86_64 development box (just did the upgrade
 to 8.2.4 yesterday) using the postgresql-experimental overlay (via
 layman) and have run into no problems. Everything has compiled,
installed/upgraded and been run with no hiccups along the way, nor any
hacky workarounds.

Postgresql-8.2.4 went soft-masked today. I've upgraded and my own local 
web application is working just fine, but dependencies for several 
other libs and apps are broken, and I'm in the process of running 
revdep-rebuild and rebuilding 14 packages right now.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

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

Re: [GENERAL] C functions under windows

[Martin Gainty]

Andrei-

*If you're developing under nix* then I would use windows cygwin (bash 
shell) e.g.

\cygwin\cygwin.bat
cd /
find . -name strings.h
you will see /usr/include
but this version of strings.h only includes string.h
to bring into environment make sure you include the /usr/include in .profile 
or .bashrc e.g.

export INCLUDE=/usr/include:$INCLUDE
(then run your compile/link/make utilties)

HTH
Martin--
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed.  If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy.  Thank you.

- Original Message - 


Islam Hegazy wrote:

Hi all
 I have postgresql server installed on a windows machine and I want to 
retrieve data using C functions. I followed the steps in the 
documentation but it didn't work for windows. I created a .dll projects 
for my functions but postgres.h calls .h files that I can't find on the 
windows machine like  strings.h. I tested my functions on another 
server installed on a linix machine and it worked correctly. So the 
problem appears to be in the include files under windows.

 Any idea how to solve this problem...
 Regards
Islam Hegazy


If you use MSVC - try to setup 'Additional include directories' for your 
project. It should point to the PostgreSQL 'include' folder.


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




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

Re: [GENERAL] Update violating constraint

[Alvaro Herrera]

Alban Hertroys wrote:
 Richard Huxton wrote:
  Alban Hertroys wrote:
  Naz Gassiep wrote:
  Hi,
  I'm trying to do an update on a table that has a unique constraint
  on the field, I need to update the table by setting field = field+1 
  
  I think you're looking for deferrable constraints; see:
 
  http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html
  
  Which won't work with unique constraints unfortunately. That's because
  they're implemented through a unique index.
 
 I appreciate the complexities involved, but that really ought to work on
 a single statement. I recall seeing something along these lines on the
 TODO list some time ago?

It is still on the TODO list.  If you want it to disappear from there,
your best bet is implementing a fix, followed by motivating someone to
do it for you.  If you don't, bets are someone will do it eventually
(which may be too late for your taste).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

   http://archives.postgresql.org/

Re: [GENERAL] Stored procedure

[Thorsten Kraus]

Hi,

thanks for your answer, but I don't get the point. Perhaps you can give 
me a small example how to get the EXECUTE into a stored procedure.


Regards

Hakan Kocaman schrieb:

Hi,
 
Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN 
 
Best Regards


Hakan Kocaman
Software-Development
 
digame.de GmbH

Richard-Byrd-Str. 4-8
50829 Köln
 
Tel.: +49 (0) 221 59 68 88 31

Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
 
digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast 
 





From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thorsten 
Kraus
Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure


Hi NG,

I want to write a stored procedure which creates a table in my 
PostgreSQL database. The procedure has one input parameter: the table name.
Here is my first try, but that does not work:
	-- 

	CREATE OR REPLACE FUNCTION create_geom_table(text) RETURNS void AS $$ 
	DECLARE 
	--table_name TEXT; 
	BEGIN 
	--- 
	CREATE TABLE table_name 
	( 
	  id integer, 
	  time timestamp without time zone, 
	  geom geometry, 
	  CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2), 
	  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL), 
	  CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1) 
	) 
	WITHOUT OIDS; 
	ALTER TABLE table_name OWNER TO admin; 

	CREATE INDEX geo_index ON table_name USING gist(geom); 

	--- 
	ALTER FUNCTION create_geom_table(table_name) OWNER TO admin; 
	END; 
	$$ LANGUAGE plpgsql; 



--

Can someone tell me what's wrong with this and what I have to change?

Regards,
Thorsten


  



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

[GENERAL] varchar as primary key

[Matthew Hixson]

I'm investigating the usage of a UUID primary key generator using  
Hibernate and Postgres.  The reason for using a UUID is that we will  
have an application hosted at different sites in different  
databases.  We will need to aggregate the data back into a single  
database from time to time and we want to avoid PK collisions.
  Is there a significant performance difference between using int  
primary keys and string primary keys in Postgres?

  Thanks,
   -M@

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

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

Re: [GENERAL] Stored procedure

[Hakan Kocaman]

Hi,

your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text) RETURNS void AS 
$BODY$
DECLARE
func_text   text;
BEGIN 

func_text:='DROP TABLE ' || table_name ||';
CREATE TABLE ' || table_name ||'
( 
  id integer, 
  mytimestamp timestamp without time zone--, 
  --geom geometry, 
  --CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2), 
  --CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 
'|| quote_literal('MULTIPOLYGON') ||'::text OR geom IS NULL), 
  --CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1) 
) 
WITHOUT OIDS; 
ALTER TABLE ' || quote_literal(table_name) ||'OWNER TO admin; 

--CREATE INDEX geo_index ON '|| quote_literal(table_name) 
||'USING gist(geom); 


--ALTER FUNCTION create_geom_table('|| 
quote_literal(table_name) ||') OWNER TO admin; 
';
EXECUTE func_text;
END; 
$BODY$ LANGUAGE plpgsql; 

select create_geom_table('test_geom_tbl');

It's not exactly the same, hence i don't got some of yout types(geom for 
example) laying around, but you get the picture, no?

Best regards

Hakan Kocaman
Software-Development
 
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
 
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
 
digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast 
 

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of 
 Thorsten Kraus
 Sent: Thursday, May 03, 2007 5:27 PM
 To: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Stored procedure
 
 Hi,
 
 thanks for your answer, but I don't get the point. Perhaps 
 you can give 
 me a small example how to get the EXECUTE into a stored procedure.
 
 Regards
 
 Hakan Kocaman schrieb:
  Hi,
   
  Try EXECUTE
  
 http://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme
 nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYN 
   
  Best Regards
 
  Hakan Kocaman
  Software-Development
   
  digame.de GmbH
  Richard-Byrd-Str. 4-8
  50829 Köln
   
  Tel.: +49 (0) 221 59 68 88 31
  Fax: +49 (0) 221 59 68 88 98
  Email: [EMAIL PROTECTED]
   
  digame.de GmbH, Sitz der Gesellschaft: Köln, 
 Handelsregister Köln, HRB 32349
  Geschäftsführung: Werner Klötsch, Marco de Gast 
   
 
 
  
 
  From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of 
 Thorsten Kraus
  Sent: Thursday, May 03, 2007 5:00 PM
  To: pgsql-general@postgresql.org
  Subject: [GENERAL] Stored procedure
  
  
  Hi NG,
  
  I want to write a stored procedure which creates a 
 table in my PostgreSQL database. The procedure has one input 
 parameter: the table name.
  Here is my first try, but that does not work:
  
 --
  
 
  CREATE OR REPLACE FUNCTION create_geom_table(text) 
 RETURNS void AS $$ 
  DECLARE 
  --table_name TEXT; 
  BEGIN 
  --- 
  CREATE TABLE table_name 
  ( 
id integer, 
time timestamp without time zone, 
geom geometry, 
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2), 
CONSTRAINT enforce_geotype_geom CHECK 
 (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL), 
CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1) 
  ) 
  WITHOUT OIDS; 
  ALTER TABLE table_name OWNER TO admin; 
 
  CREATE INDEX geo_index ON table_name USING gist(geom); 
 
  --- 
  ALTER FUNCTION create_geom_table(table_name) OWNER TO admin; 
  END; 
  $$ LANGUAGE plpgsql; 
 
  
 --
 
  
  Can someone tell me what's wrong with this and what I 
 have to change?
  
  Regards,
  Thorsten
  
 

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

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

   http://archives.postgresql.org/

Re: [GENERAL] C functions under windows

[Islam Hegazy]

I have added the path to the include directory of postgresql but it hasn't 
already some of the files that are in linux include path like strings.h. 
It doesn't object about postgres.h which it can find now but it objects 
about strings.h


I use MSVC6 to make my dll file.

Regards
Islam



- Original Message - 
From: Andrei Kovalevski [EMAIL PROTECTED]

To: pgsql-general@postgresql.org; Islam Hegazy [EMAIL PROTECTED]
Sent: Thursday, May 03, 2007 7:32 AM
Subject: Re: [GENERAL] C functions under windows



Islam Hegazy wrote:

Hi all
 I have postgresql server installed on a windows machine and I want to 
retrieve data using C functions. I followed the steps in the 
documentation but it didn't work for windows. I created a .dll projects 
for my functions but postgres.h calls .h files that I can't find on the 
windows machine like  strings.h. I tested my functions on another 
server installed on a linix machine and it worked correctly. So the 
problem appears to be in the include files under windows.

 Any idea how to solve this problem...
 Regards
Islam Hegazy


If you use MSVC - try to setup 'Additional include directories' for your 
project. It should point to the PostgreSQL 'include' folder. 



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

  http://archives.postgresql.org/

Re: [GENERAL] Have I b0rked something? Slow comparisons on where x in (...)

[Listmail]

Try creating a temporary table, populating with the list and joining  
against it. That's probably your best bet for a long list of target  
values.


Check :

forum_bench= CREATE TABLE test (value INTEGER NOT NULL);
CREATE TABLE
forum_bench= INSERT INTO test SELECT * FROM generate_series( 1, 100 );
INSERT 0 100
forum_bench= ANALYZE test;
forum_bench= EXPLAIN ANALYZE SELECT * FROM test;
  QUERY PLAN
---
 Seq Scan on test  (cost=0.00..14405.24 rows=24 width=4) (actual  
time=0.030..349.699 rows=100 loops=1)

 Total runtime: 542.914 ms
(2 lignes)


OK : 542 ms to grab the data.
IN() :

EXPLAIN ANALYZE SELECT * FROM test WHERE value IN ( 1000 values from 0 to  
999000 in steps of 1000 ):
 Seq Scan on test  (cost=0.00..1264310.24 rows=1000 width=4) (actual  
time=17.649..17977.085 rows=999 loops=1)

   Filter: (value = ANY ('{0,1000..99000}'::integer[]))
 Total runtime: 17978.061 ms

Ouch.

forum_bench= EXPLAIN ANALYZE SELECT * FROM test WHERE value IN (VALUES  
(0),(1000),(2000),(998000),(999000));

   QUERY PLAN

 Hash Join  (cost=19.50..18176.45 rows=200 width=4) (actual  
time=2.823..736.960 rows=999 loops=1)

   Hash Cond: (test.value = *VALUES*.column1)
   -  Seq Scan on test  (cost=0.00..14405.24 rows=24 width=4) (actual  
time=0.032..335.680 rows=100 loops=1)
   -  Hash  (cost=17.00..17.00 rows=200 width=4) (actual  
time=2.108..2.108 rows=1000 loops=1)
 -  HashAggregate  (cost=15.00..17.00 rows=200 width=4) (actual  
time=1.165..1.542 rows=1000 loops=1)
   -  Values Scan on *VALUES*  (cost=0.00..12.50 rows=1000  
width=4) (actual time=0.004..0.478 rows=1000 loops=1)

 Total runtime: 737.362 ms

	Removing the 542 ms to read the table, we see checking if the values are  
in the hash is really rally fast.


So, obvious truth : hash is faster than dumb compare. Much faster.
Now, postgres should do this on its own, I think.

	PS : if the 1000 values are all the same (1000 times 1), IN() doesn't  
detect it, so the runtime does not change. Hash join doesn't care, so the  
runtime doesn't change either.


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

Re: [GENERAL] Have I b0rked something? Slow comparisons on where x in (...)

[Listmail]

Followup to my previous test, with an index this time

EXPLAIN ANALYZE SELECT * FROM test WHERE value IN ( 1000 integers )

 Bitmap Heap Scan on test  (cost=3519.09..7156.83 rows=1000 width=4)  
(actual time=5.843..8.897 rows=999 loops=1)

   Recheck Cond: (value = ANY ('{0,...,999000}'::integer[]))
   -  Bitmap Index Scan on testindex  (cost=0.00..3518.84 rows=1000  
width=0) (actual time=5.594..5.594 rows=999 loops=1)

 Index Cond: (value = ANY ('{0,...,999000}'::integer[]))
 Total runtime: 9.157 ms

EXPLAIN ANALYZE SELECT * FROM test WHERE value IN (VALUES  
(0),(1000),...(999000))


 Nested Loop  (cost=15.00..1461.74 rows=200 width=4) (actual  
time=1.191..26.127 rows=999 loops=1)
   -  HashAggregate  (cost=15.00..17.00 rows=200 width=4) (actual  
time=1.169..1.673 rows=1000 loops=1)
 -  Values Scan on *VALUES*  (cost=0.00..12.50 rows=1000  
width=4) (actual time=0.007..0.517 rows=1000 loops=1)
   -  Index Scan using testindex on test  (cost=0.00..7.21 rows=1  
width=4) (actual time=0.023..0.023 rows=1 loops=1000)

 Index Cond: (test.value = *VALUES*.column1)
 Total runtime: 26.411 ms

Mixing the two would be a win :

- hashing the values
- making a bitmap from them
- grabbing the pages and using the hash in Recheck Cond

ie. something like that :

   -  HashAggregate  (cost=15.00..17.00 rows=200 width=4) (actual  
time=1.169..1.673 rows=1000 loops=1)
 -  Values Scan on *VALUES*  (cost=0.00..12.50 rows=1000  
width=4) (actual time=0.007..0.517 rows=1000 loops=1)
 Bitmap Heap Scan on test  (cost=3519.09..7156.83 rows=1000 width=4)  
(actual time=5.843..8.897 rows=999 loops=1)

   Recheck Cond: (value in hash)
   -  Bitmap Index Scan on testindex  (cost=0.00..3518.84 rows=1000  
width=0) (actual time=5.594..5.594 rows=999 loops=1)

 Index Cond: (value in hash)



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

Re: [GENERAL] C functions under windows

[Magnus Hagander]

Islam Hegazy wrote:
 I have added the path to the include directory of postgresql but it
 hasn't already some of the files that are in linux include path like
 strings.h. It doesn't object about postgres.h which it can find now
 but it objects about strings.h
 
 I use MSVC6 to make my dll file.

The include files that ship with your version are made for MingW, not MSVC.

You could try grabbing pg_config.h.win32 from src/include (I think it's
available in the source distribution of 8.2, if not, just get one from a
snapshot version). Then take that file and overwrite your copy of
pg_config.h with it.

strings.h is one of the differences between msvc and mingw.

//Magnus

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

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

Re: [GENERAL] varchar as primary key

[William Garrison]

I don't recommend it.  There are better ways to store UUIDs:

char(32)-- Easy to work with, fixed length, inefficient
varchar(32) -- 4 bytes larger due to variable size
bytea() -- 20 bytes, variable length
bit(128)-- 16 bytes, optimal

I don't like char() or varchar() because of case-senstivity and 
inefficiency.


We used bytea, and created a small function byte2guid() and guid2byte() 
to handle converting to/from strings when working at a SQL prompt.  But 
the production code doesn't use those.  In retrospect, I would like to 
have tried BIT(128) since I think fixed-length columns perform better 
than variable-length columns.


Matthew Hixson wrote:
I'm investigating the usage of a UUID primary key generator using 
Hibernate and Postgres.  The reason for using a UUID is that we will 
have an application hosted at different sites in different databases.  
We will need to aggregate the data back into a single database from time 
to time and we want to avoid PK collisions.
  Is there a significant performance difference between using int 
primary keys and string primary keys in Postgres?

  Thanks,
   -M@

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

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




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

Re: [GENERAL] varchar as primary key

[Alvaro Herrera]

William Garrison wrote:
 I don't recommend it.  There are better ways to store UUIDs:
 
 char(32)-- Easy to work with, fixed length, inefficient
 varchar(32) -- 4 bytes larger due to variable size
 bytea() -- 20 bytes, variable length
 bit(128)-- 16 bytes, optimal
 
 I don't like char() or varchar() because of case-senstivity and 
 inefficiency.
 
 We used bytea, and created a small function byte2guid() and guid2byte() 
 to handle converting to/from strings when working at a SQL prompt.  But 
 the production code doesn't use those.  In retrospect, I would like to 
 have tried BIT(128) since I think fixed-length columns perform better 
 than variable-length columns.

FWIW, in Postgres, all those types are stored with the word length in
front of each datum.  We don't use the column maximum length as a cue
for the storage of each individual datum.  So the two first items you
list above are stored identically.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 1: 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] varchar as primary key

[Jeff Davis]

On Thu, 2007-05-03 at 08:58 -0700, Matthew Hixson wrote:
 I'm investigating the usage of a UUID primary key generator using  
 Hibernate and Postgres.  The reason for using a UUID is that we will  
 have an application hosted at different sites in different  
 databases.  We will need to aggregate the data back into a single  
 database from time to time and we want to avoid PK collisions.
Is there a significant performance difference between using int  
 primary keys and string primary keys in Postgres?

If the only thing you need to do is avoid primary key collisions, why
not just store an extra int that represents the site ID and have a
primary key on (the_key, site_id)?

That way you're *sure* no collisions happen. A UUID has a lot of bits,
but if the random generator is flawed a collision can still happen.

Regards,
Jeff Davis


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

   http://archives.postgresql.org/

Re: [GENERAL] varchar as primary key

[Alexander Staubo]

On 5/3/07, Matthew Hixson [EMAIL PROTECTED] wrote:

   Is there a significant performance difference between using int
primary keys and string primary keys in Postgres?


PostgreSQL uses B-trees for its indexes, insertion time is logarithmic
regardless of the type of the key, but strings have a larger overhead
since they involve character comparisons; (i - j) is a lot faster than
strcmp(i, j). If you do go for strings, I would suggest that the
beginning of the key be statistically distributed as widely as
possible; ie., avoid common prefixes.

Alexander.

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

[GENERAL] Indexing questions: Index == key? And index vs substring - how successful?

[Andrew Edson]

As the title of this message suggests, I've got a couple of questions about 
indexing that I'm not sure about.  I've tried to take a look at the docs, but I 
can't remember seeing anything on these; it's quite possible, I admit, that I'm 
simply not remembering all of what I saw, but I would appreciate it if someone 
would help me to understand these.
   
  1. Does an indexed column on a table have to be a potential primary key?  
I've been working with a couple of rather large tables where a common select is 
on a foreign key called 'cntrct_id' (Varchar(9) in format).  However, the same 
'cntrct_id' can appear on multiple records in the tables I'm trying to work 
with now; the tables themselves record events associated with the given 
'cntrct_id' record and can store many events for one 'cntrct_id' value.  I'd 
thought that creating an index on the table.cntrct_id field for the event 
tables would allow me to speed up the transations some, but comparisons of time 
before and after the indexing lead me to wonder if I was mistaken in this.  The 
times were almost identical in the following areas: Before Indexing, after 
Indexing but before Analyzing, and after Analyzing.
   
  2. Another common sort on these fields uses part, not all, of the 'cntrct_id' 
value to search for things; the first character marks original location in an 
internal framework we're using, for example, and the third character marks the 
month of the year that the original 'cntrct_id' record was set up.  Sorts on 
either of those are fairly common as well; would indexing on the cntrct_id as a 
whole be able to speed up a sort on a portion of it?
   
  I have in mind something like this:
  select * from [event table] where substring(cntrct_id, 3,1) = 'H';
  which should select any event records associated with 'cntrct_id' values 
initally set up in August.  (Jan = A, Feb = B, etc)
   
  If I established an index on the 'cntrct_id' field in the event tables, would 
it assist in speeding up the substring-based search, or would it not be 
effective at doing so?
   
  Thank you for your assistance.

   
-
Ahhh...imagining that irresistible new car smell?
 Check outnew cars at Yahoo! Autos.

Re: [GENERAL] varchar as primary key

[Dawid Kuroczko]

On 5/3/07, Jeff Davis [EMAIL PROTECTED] wrote:

On Thu, 2007-05-03 at 08:58 -0700, Matthew Hixson wrote:
 I'm investigating the usage of a UUID primary key generator using
 Hibernate and Postgres.  The reason for using a UUID is that we will
 have an application hosted at different sites in different
 databases.  We will need to aggregate the data back into a single
 database from time to time and we want to avoid PK collisions.
Is there a significant performance difference between using int
 primary keys and string primary keys in Postgres?

If the only thing you need to do is avoid primary key collisions, why
not just store an extra int that represents the site ID and have a
primary key on (the_key, site_id)?

That way you're *sure* no collisions happen. A UUID has a lot of bits,
but if the random generator is flawed a collision can still happen.


Also, why not simply use sequences?
You can declare sequence to START WITH 1 and INCREMENT BY 10,
and on the other site START WITH 2 INCREMENT BY 10.
There is no chance these will collide (unless human intervenes ;)),
and you can safely add 8 more similar servers, each with similar
sequence.

 Regards,
  Dawid

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

Re: [GENERAL] Indexing questions: Index == key? And index vs substring - how successful?

[Martijn van Oosterhout]

On Thu, May 03, 2007 at 01:42:44PM -0700, Andrew Edson wrote:
 As the title of this message suggests, I've got a couple of questions about 
 indexing that I'm not sure about.  I've tried to take a look at the docs, but 
 I can't remember seeing anything on these; it's quite possible, I admit, that 
 I'm simply not remembering all of what I saw, but I would appreciate it if 
 someone would help me to understand these.

   1. Does an indexed column on a table have to be a potential primary key?  

No, that's the difference between unique and non-unique indexes.

 The times were almost identical in the following areas: Before Indexing, 
 after Indexing but before Analyzing, and after Analyzing.

If you want reasons, you're going to need to provide EXPLAIN ANALYSE
output.

   I have in mind something like this:
   select * from [event table] where substring(cntrct_id, 3,1) = 'H';
   which should select any event records associated with 'cntrct_id' values 
 initally set up in August.  (Jan = A, Feb = B, etc)

   If I established an index on the 'cntrct_id' field in the event
   tables, would it assist in speeding up the substring-based search,
   or would it not be effective at doing so?

Not directly, no. However, you can have indexes on expressions:
CREATE INDEX foo ON bar((substring(cntrct_id, 3,1)));

Which could speed up the above query (could, since it depends on
exactly how much of the table actually needs to be searched...)

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Indexing questions: Index == key? And index vs substring - how successful?

[Listmail]

 1. Does an indexed column on a table have to be a potential primary  
key?


Nope, create as many index as you need/must/should.

I've been working with a couple of rather large tables where a common  
select is on a foreign key called 'cntrct_id' (Varchar(9) in format).   
However, the same 'cntrct_id' can appear on multiple records in the  
tables I'm trying to work with now; the tables themselves record events  
associated with the given 'cntrct_id' record and can store many events  
for one 'cntrct_id' value.  I'd thought that creating an index on the  
table.cntrct_id field for the event tables would allow me to speed up  
the transations some, but comparisons of time before and after the  
indexing lead me to wonder if I was mistaken in this.  The times were  
almost identical in the following areas: Before Indexing, after Indexing  
but before Analyzing, and after Analyzing.
 2. Another common sort on these fields uses part, not all, of the  
'cntrct_id' value to search for things; the first character marks  
original location in an internal framework we're using, for example, and  
the third character marks the month of the year that the original  
'cntrct_id' record was set up.  Sorts on either of those are fairly  
common as well; would indexing on the cntrct_id as a whole be able to  
speed up a sort on a portion of it?


Nope.
This looks like suboptimal schema design...
	If you had an indexed date column, you would be able to make fast indexed  
queries with BETWEEN, =, =, etc.



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

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

Re: [GENERAL] varchar as primary key

[Jeff Davis]

On Thu, 2007-05-03 at 22:32 +0200, Alexander Staubo wrote:
 On 5/3/07, Matthew Hixson [EMAIL PROTECTED] wrote:
 Is there a significant performance difference between using int
  primary keys and string primary keys in Postgres?
 
 PostgreSQL uses B-trees for its indexes, insertion time is logarithmic
 regardless of the type of the key, but strings have a larger overhead
 since they involve character comparisons; (i - j) is a lot faster than
 strcmp(i, j). If you do go for strings, I would suggest that the

If you're using a non-C locale, it's slower than strcmp() too.
PostgreSQL has to do an extra memcpy() in order to use strcoll(),
because strings in postgresql aren't necessarily NULL-terminated and
there's no such thing as strncoll(), unfortunately (a comment in the
code points this out).

This mostly matters in sorting. If your B-tree is in memory and you do a
simple lookup (what else would you do with UUIDs?), it's unlikely to
take very long at all.

Regards,
Jeff Davis


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

Re: [GENERAL] Temporal Units

[Ted Byers]

Rich,
   
  I would think that as an ecologist, you would have a better sense than most 
here of the kinds of things I'd be doing.  After all, I am a mathematical 
ecologist by training and the majority of applications I have developed have 
been either for agricultural consultants or environmental consultants.  As a 
very simplistic example of where I find the kind of auditing I mentioned being 
priceless, consider a decision support tool for which the input data involves 
concentrations of various contaminants in water leaving some facility (at this 
point we don't care what the facility is).  While I would prefer that the input 
come from a laboratory information system, and fully automated, imagine that 
the data is recorded and input by some lab tech. as each test is completed.  On 
April 1, 2006, there is a typo leading the decision maker to believe that the 
concentration of contaminant X is 1 mg/l, and that this is an error and the 
true value is 1 g/l.  The decision maker may decide to
 do nothing because  1 mg/l is considered safe.  But doing nothing, some bad 
environmental effect occurs, and it is discovered days after the decision was 
made that the data entered is in error.  Who is to blame?  Where does 
responsibility lay and what corrective action is needed to ensure the problem 
does not recurr?  When there is an issue of accountability and responsibility 
involved in environmental issues, every scientific measurement taken becomes a 
candidate for the kind of multitemporal data I routinely use.
   
  Now, for lookup tables vs functions, there is always a tradeoff even within a 
given programming language (I am most familiar with FORTRAN, C++ and Java).  
The tradeoff is between memory use (including how that may affect performance 
if the table is large relative to available memory) and the speed or size of 
the function.  In the case of the date and time functions I typically use in my 
perl scripts, they are so short, I doubt the performance impact is significant 
or easily measurable.
   
  On a side note, how do you like Ruby and Python?  How would you compare them 
to the other options (such as C++, Java, Perl, c.)?
   
   
  Cheers,
   
  Ted

Rich Shepard [EMAIL PROTECTED] wrote:
On Mon, 30 Apr 2007, Ted Byers wrote:

 I am not sure I see why it would be good to do this using SQL, but I do
 know that I have used a number of Perl packages for this sort of thing.

 I am not arguing with you. I just want to know in what circumstances my
 schemas can be improved by a calendar table, and how it provides a
 benefit over my more usual Perl functions.

Ted,

Having never used such a table -- or having written an application that
had such a heavy use of temporal data rather than scientific data -- I have
no idea in what circumstances your schemas might be improved with a calendar
table.

I suspect, however, that a SQL table lookup may well be quicker than
running a script (or compiled function) in another language, and the table
is available for use in multiple apps. Isn't it faster or more efficient to
run SELECT queries with table lookups rather then use stored procedures?

For this web-based application, the UI and communications between client
and server are being written in Ruby (with Rails) while the report
generation is written in Python using ReportLab. If most of the queries can
be done with SQL, I think it will be much easier to maintain, modify, and
expand. Could be wrong, of course.

Rich

-- 
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 1: 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] large table problem

[Jason Nerothin]

Thanks for the redirect... After profiling my client memory usage and using
the built-in cursor functionality I discovered that another part of my
program was causing the memory overflow and that the ResultSet iteration was
doing exactly what it should have all along.

On 4/21/07, Kris Jurka [EMAIL PROTECTED] wrote:




On Fri, 20 Apr 2007, Jason Nerothin wrote:

 I'm trying to work my way around a large query problem.

 Not too unexpectedly, the app server (EJB3/JPA) is choking on the
queries
 which are unnamed native queries in Java parliance. Work-around
attempt 1
 was to call directly to the JDBC driver, but the cursor doesn't dispose
of
 the memory in the ResultSet once I've passed it by (OutOfMemoryError)
and
 the documentation suggests that cursor behavior is a little buggy for
the
 current postgres driver. (The docs suggest implementing a custom stored
 procedure to provide iteration.)

I'm not sure what documentation you're reading:

http://jdbc.postgresql.org/documentation/82/query.html#query-with-cursor

and it works as adverstised.

Kris Jurka





--

Jason Nerothin
Programmer/Analyst IV - Database Administration
UCLA-DOE Institute for Genomics  Proteomics
Howard Hughes Medical Institute

611 C.E. Young Drive East   | Tel: (310) 206-3907
105 Boyer Hall, Box 951570  | Fax: (310) 206-3914
Los Angeles, CA 90095. USA | Mail: [EMAIL PROTECTED]

http://www.mbi.ucla.edu/~jason

[GENERAL] How to get comments for view columns?

[eugene . mindrov]

Hi,
is there any way to get comment meta-information for columns in a
view? I mean, suppose I have several tables and some of their columns
have comments, then I define a view on these tables, and what I want
is to somehow retrieve comments (if any) for those columns which are
selected in a view.
I tried to play with system catalog tables trying to dig out the
necessary info, but to no avail.
The best I could do is to retrieve all dependent columns (which
belong to underlying tables) for a view with their comments, but I
couldn't find any way to determine a relation between dependent
columns and those actually selected in a view.
So, is this an impossible task and should I invent my own meta-
dictionary or I'm missing something important?


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

   http://archives.postgresql.org/

[GENERAL] Dangers of fsync = off

[Joel Dice]

Hello all.

It's clear from the documentation for the fsync configuration option that 
turning it off may lead to unrecoverable data corruption.  I'd like to 
learn more about why this is possible and how likely it really is.


A quick look at xlog.h reveals that each record in the transaction log 
contains a CRC checksum, a transaction ID, a length, etc..  Assuming the 
worst thing that can happen due to a crash is that the end of the log is 
filled with random garbage, there seems to be little danger that the 
recovery process will misinterpret any of that garbage as a valid 
transaction record, complete with matching checksum.


If my assumption is incorrect (i.e. garbage at the end of the log is not 
the worst that can happen), what else might happen, and how would this 
lead to unrecoverable corruption?  Also, are there any filesystems 
available which avoid such cases?


Sorry if this has been discussed before - in which case please point me to 
that discussion.


Thanks.

 - Joel

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

[GENERAL] Password authentication failed

[Suresh Nimbalkar]

Hi!

I am a complete newbee to Postgres. Have installed Postgres on Windows 2003 
server SP1 a week back. When I try to log-in to the server (by writting psql 
mydb at command prompt in postgres/bin directory), I keep getting a message 
psal: FATAL: password authentication failed for userAdministrator. 

I have installed Postgres as an Administrator and log-in to the server as 
administrator. I don't think I am making mistake in entering the password. 

It's quite frustrating. Will someone please help?

Thanks and regards
Vedsur

   
-
Ahhh...imagining that irresistible new car smell?
 Check outnew cars at Yahoo! Autos.

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

[philippe]

Le lundi 30 avril 2007 à 20:13 +0400, Oleg Bartunov a écrit :
 On Mon, 30 Apr 2007, philippe wrote:

  Now if I do a query like this
  select to_tsvector('default', '... something with more than 200
  chars');
 
  - result ok
 
 it doesn't uses french snowball stemmer
 
Yes, it's just to show that tsearch is working by default

 
  but
  select to_tsvector('fr_FR', '... something with more than 200 chars');
 
  - server crash
 
 have you reinstalled tsearch2 ? There was change in  snowball stemmer api.
 Check mailing list archive for the same issue.
 

I have reinstalled tsearch2, 
I have also recompiled it with this patch
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch_snowball_82.gz

I still have the same problem.
I have found similar problem in the archive but not the same one

doing this
select lexize( 'fr', 'voyageur' );
don't crash




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

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

[GENERAL] Separating function privileges from tables

[Barry Brown]

Hi all,

It's nice that privileges on views are separate from the privileges  
on its underlying tables. For example, if view V queries tables A and  
B, I only need to grant SELECT on the view to another user; tables A  
and B can have that privilege revoked and the view works.


Are there plans to extend similar behavior to functions? That is, can  
I simply grant EXECUTE on the function and not have to worry about  
granting the appropriate privileges to the tables used by the function?


Thanks.

-B

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

Re: [GENERAL] [HACKERS] SOS, help me please, one problem towards the postgresql developement on windows

[shieldy]

thankyou very much.
but the method, you said, is adding a alias name, so it can not work.
and as i need to add many functions likes this, so the best way is to
compile the whole postgresql. eventhough, i did, it didnot work, so i am
puzzled, can add a function directly in the source file, and compile it, can
it work??
BTW: I have just seach the source for addingthe built-in function, and found
it need to add declaration in the include geo_decls.h, and add the function
in the geo_ops.c. can it not be enough??



On 5/1/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote:


On Mon, Apr 30, 2007 at 11:05:35AM +0800, shieldy wrote:
 thankyou for your reply.
 I added it to the backend, because the internal ones such as
box_intersect()
 function is keeped at there. so in my opinion, I just need to add a
function
 to the files, and then compile it. then we can use it as the internal
ones.
 bytheway, what's the backend file used for? I just didnot quite
understand
 the postgresql.  and your meaning  place it in a module , how should i
 do??

First, please reply to the list, not to me directly.

Secondly, just because you add it to the backend doesn't mean you can
use it straight away. There are thousands of functions in postgresql you
can't access from SQL, you have to declare them. See here:

http://www.postgresql.org/docs/8.2/interactive/xfunc-internal.html

Finally, by putting it in the backend you have to rebuild postgres
every time you want to change a function. Complete waste of time as
postgres can load external modules. See here:

http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html

Or better, just read the whole Extending SQL section.

http://www.postgresql.org/docs/8.2/interactive/extend.html

Have a nice day,
--
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to
litigate.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGNwoKIB7bNG8LQkwRAjXuAJ9atldhI9Q81lIuRpD8Egguv5ojvgCcCk4v
/Jkr0WGrKP9mxN94iw9X3/U=
=Swve
-END PGP SIGNATURE-

Re: [GENERAL] Temporal Units

[Lew]

John D. Burger wrote:

There was a brief discussion of this just last week, with a few solutions
suggested:

http://archives.postgresql.org/pgsql-general/2007-04/msg01098.php


Rich Shepard wrote:

  That thread asked how to find business days between any two specified
dates. I would like to create a calendar table that includes business days,
holidays, Julianized dates, and other interesting tid-bits that are of 
value
in a business application. Then dates can be looked up in the table to 
learn

their attributes and the calculations don't need to be done each time.


The best solution I've encountered so far to this type of problem is to have a 
table of days with columns like isWeekday, isHoliday, julianDay, 
otherTidbit, ...


Then you select or join the days within the interval of interest and factor 
out weekdays, or holidays, or whatever.


--
Lew

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

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

[GENERAL] Help tracking down error in postgres log

[William Garrison]

I get the following error in the postgres log.  I know what the error 
means and how to fix it, but I don't know how to determine which 
statement is causing it:


2007-03-27 09:29:04 WARNING:  nonstandard use of \\ in a string literal 
at character 72
2007-03-27 09:29:04 HINT:  Use the escape string syntax for backslashes, 
e.g., E'\\'.


Can I get postgres to log the actualy command or stored procthat caused 
the problem?


---(end of broadcast)---
TIP 1: 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

[GENERAL] WAL

[jasme]

hi,
i had made necessary change in the postgresql.conf for enabling WAL. How can
i know that WAL is working?
The configurations done in the postgresql.conf file is as below: 
#---
# WRITE AHEAD LOG
#---

# - Settings -

fsync = true# turns forced synchronization on or off
wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, fsync_writethrough,
# open_sync, open_datasync
wal_buffers = 8 # min 4, 8KB each
commit_delay = 0# range 0-10, in microseconds
commit_siblings = 5 # range 1-1000

# - Checkpoints -

checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 300# range 30-3600, in seconds
checkpoint_warning = 30 # 0 is off, in seconds

# - Archiving -

archive_command = 'cp -i %p /testpg/server/archivedir/%f /dev/null'


your response will be highly appreciated.

thanks





-- 
View this message in context: http://www.nabble.com/WAL-tf3685432.html#a10301494
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] tuple concurrently updated

[Andrus]

My application receives the folllowing error sometimes.

Any idea how to fix ?

Andrus.

7/XX000:Error while executing the query;ERROR:  tuple concurrently updated

CONTEXT:  SQL statement DROP TABLE templsabiPL/pgSQL function drop_table 
line 2 at execute statement


SELECT drop_table('templsabi');CREATE TABLE templsabi AS
   select
 toode,
 osak as sihtyksus,
 partii,
 laosumma,

 1 as lj, 0 as st, 0 as vm,
 0 as kaubasumma,
 kogus,
 0 as kogpak,
 CAST('' AS CHAR(10)) as yksus,
 CAST('' AS CHAR(1)) as doktyyp
   from HETKESEI
   where true
AND osak LIKE 'TARTU%' ESCAPE '!'

AND toode='DIS123103'
;SELECT * FROM templsabi

drop_table is defined as

CREATE OR REPLACE FUNCTION drop_table(TEXT)
 RETURNS VOID STRICT LANGUAGE plpgsql AS $$
BEGIN
   EXECUTE 'DROP TABLE ' || $1;
   EXCEPTION WHEN UNDEFINED_TABLE THEN
RETURN;
END;
$$;


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

[GENERAL] script for taking incremental backup in postgres in LINUX

[pumesh]

Hello sir,
  I have already done the backup with crontab for the interval
of 3 hours. But what i need is to backup the data even in the server crash
between these intervals. Since critical transactions during these intervals
may lost. So what should i do to make the backup continuously or during
these intervals.

Thanks and Regards,
Umesh
-- 
View this message in context: 
http://www.nabble.com/script-for-taking-incremental-backup-in-postgres-in-LINUX-tf3685396.html#a10301381
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Password authentication failed

[Jan Bilek]

Connect to PostgreSql as Postgres user (default database user):

psql yourdb -U Postgres

then you will be asked for password selected during the installation.

Hope this will help.

JB

  - Original Message - 
  From: Suresh Nimbalkar 
  To: pgsql-general@postgresql.org 
  Sent: Tuesday, May 01, 2007 1:29 PM
  Subject: [GENERAL] Password authentication failed


  Hi!

  I am a complete newbee to Postgres. Have installed Postgres on Windows 2003 
server SP1 a week back. When I try to log-in to the server (by writting psql 
mydb at command prompt in postgres/bin directory), I keep getting a message 
psal: FATAL: password authentication failed for userAdministrator. 

  I have installed Postgres as an Administrator and log-in to the server as 
administrator. I don't think I am making mistake in entering the password. 

  It's quite frustrating. Will someone please help?

  Thanks and regards
  Vedsur



--
  Ahhh...imagining that irresistible new car smell?
  Check out new cars at Yahoo! Autos. 

Re: [GENERAL] Temporal Units

[Rich Shepard]

On Wed, 2 May 2007, Lew wrote:


The best solution I've encountered so far to this type of problem is to
have a table of days with columns like isWeekday, isHoliday, julianDay,
otherTidbit, ...

Then you select or join the days within the interval of interest and
factor out weekdays, or holidays, or whatever.


  Thank you, Lew.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
http://www.appl-ecosys.com Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] Help tracking down error in postgres log

[Tom Lane]

William Garrison [EMAIL PROTECTED] writes:
 Can I get postgres to log the actualy command or stored procthat caused 
 the problem?

Set log_min_error_statement = error.

regards, tom lane

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

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

Re: [GENERAL] tuple concurrently updated

[Tom Lane]

Andrus [EMAIL PROTECTED] writes:
 My application receives the folllowing error sometimes.
 7/XX000:Error while executing the query;ERROR:  tuple concurrently updated
 CONTEXT:  SQL statement DROP TABLE templsabiPL/pgSQL function drop_table 
 line 2 at execute statement

That's a bit interesting ... what PG version is this exactly?  What else
might be touching that table at the same time?  Do you have autovacuum
enabled?

regards, tom lane

---(end of broadcast)---
TIP 1: 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] Dangers of fsync = off

[Tom Lane]

Joel Dice [EMAIL PROTECTED] writes:
 It's clear from the documentation for the fsync configuration option that 
 turning it off may lead to unrecoverable data corruption.  I'd like to 
 learn more about why this is possible and how likely it really is.

As you note, WAL is not particularly vulnerable --- the worst likely
consequence is not being able to read the last few WAL entries that
were made.

The real problem with fsync off is that there is essentially no
guarantee about the relative write order of WAL and data files.
In particular, some data-file updates might hit disk before the
corresponding WAL entries.  If other data-file updates part of
the same transaction did *not* reach disk before a crash, then
replay of WAL might not cause those updates to happen (because
the relevant WAL records are unreadable), leaving you with
inconsistent data.

Another scenario is that a checkpoint is shown as completed by WAL but
not all of the before-the-checkpoint data-file updates actually reached
disk.  WAL replay will start from the checkpoint and therefore not fix
the missing updates.

Either way you have inconsistencies in on-disk data, such as missing
tuples, multiple live versions of the same tuple, index contents not
consistent with heap, or outright-corrupt index structure.  The extent
to which these things are visible to applications is hard to predict,
but it's frequently ugly :-(.  Index problems can always be fixed with
REINDEX, but there's no fix for inconsistent heap contents.

regards, tom lane

---(end of broadcast)---
TIP 1: 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] Separating function privileges from tables

[Tom Lane]

Barry Brown [EMAIL PROTECTED] writes:
 It's nice that privileges on views are separate from the privileges  
 on its underlying tables. For example, if view V queries tables A and  
 B, I only need to grant SELECT on the view to another user; tables A  
 and B can have that privilege revoked and the view works.

 Are there plans to extend similar behavior to functions? That is, can  
 I simply grant EXECUTE on the function and not have to worry about  
 granting the appropriate privileges to the tables used by the function?

I think you are looking for SECURITY DEFINER function option.

regards, tom lane

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

Re: [GENERAL] cant get pg_dump/pg_restore to behave

[Mike Frysinger]

On 4/20/07, Tom Lane [EMAIL PROTECTED] wrote:

Hmph.  It should pretty much just work ... and there is *definitely* not
any update command visible in the source code.


i dug deeper (like i should have in the first place) and the UPDATEs
are ok ... they're inside of functions which get triggered on events


If there's not anything confidential about your schema, could you send
me the output of pg_dump -s on the problem database?  Maybe seeing a
fuller picture will yield a clue.


the schema shouldnt be a problem ... just the data :)

thanks for any insight ... ive pretty lost ;(
-mike


gforge.schema.bz2
Description: BZip2 compressed data

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

   http://archives.postgresql.org/

Re: [GENERAL] varchar as primary key

[Tom Lane]

Jeff Davis [EMAIL PROTECTED] writes:
 If you're using a non-C locale, it's slower than strcmp() too.
 PostgreSQL has to do an extra memcpy() in order to use strcoll(),
 because strings in postgresql aren't necessarily NULL-terminated and
 there's no such thing as strncoll(), unfortunately (a comment in the
 code points this out).

The memcpy is the least of the problem --- in many non-C locales,
strcoll() is simply a dog, because the collation rules are ridiculously
complex.

regards, tom lane

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

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

Re: [GENERAL] How to get comments for view columns?

[Tom Lane]

[EMAIL PROTECTED] writes:
 is there any way to get comment meta-information for columns in a
 view? I mean, suppose I have several tables and some of their columns
 have comments, then I define a view on these tables, and what I want
 is to somehow retrieve comments (if any) for those columns which are
 selected in a view.
 I tried to play with system catalog tables trying to dig out the
 necessary info, but to no avail.
 The best I could do is to retrieve all dependent columns (which
 belong to underlying tables) for a view with their comments, but I
 couldn't find any way to determine a relation between dependent
 columns and those actually selected in a view.

What do you consider selected?  Given a view defined as

select a, b, c+d as sum from tab where e  42;

what output are you wishing for?

AFAIR the dependency mechanism will treat a,b,c,d,e alike.  To do more
you'd need to grovel through the pg_rewrite expression for the view's
select rule, which'd be excruciatingly painful from a client program
for lack of any supporting code.

regards, tom lane

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

   http://archives.postgresql.org/

[GENERAL] Regarding autocomplete

[Mageshwaran]

Hi all,

Can anybody tell me how to enable autocomplete and history in psql.

Thanks in advance.



** DISCLAIMER **
Information contained and transmitted by this E-MAIL is proprietary to 
Sify Limited and is intended for use only by the individual or entity to 
which it is addressed, and may contain information that is privileged, 
confidential or exempt from disclosure under applicable law. If this is a 
forwarded message, the content of this E-MAIL may not have been sent with 
the authority of the Company. If you are not the intended recipient, an 
agent of the intended recipient or a  person responsible for delivering the 
information to the named recipient,  you are notified that any use, 
distribution, transmission, printing, copying or dissemination of this 
information in any way or in any manner is strictly prohibited. If you have 
received this communication in error, please delete this mail  notify us 
immediately at [EMAIL PROTECTED]


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

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

Re: [GENERAL] cant get pg_dump/pg_restore to behave

[Tom Lane]

Mike Frysinger [EMAIL PROTECTED] writes:
 On 4/20/07, Tom Lane [EMAIL PROTECTED] wrote:
 Hmph.  It should pretty much just work ... and there is *definitely* not
 any update command visible in the source code.

 i dug deeper (like i should have in the first place) and the UPDATEs
 are ok ... they're inside of functions which get triggered on events

Doh, I should have thought of that.

 If there's not anything confidential about your schema, could you send
 me the output of pg_dump -s on the problem database?  Maybe seeing a
 fuller picture will yield a clue.

 the schema shouldnt be a problem ... just the data :)

Well, I loaded and dumped and reloaded this schema in 8.1 without any
problem, so I'm still baffled.

Looking back at your original message, you say

 $ pg_dump -F c -s -d database-server mydb  mydb.schema
 $ psql -d mydb  mydb.schema
 error about users_idx not existing

There are several obvious things wrong with that (eg, psql cannot read
-Fc format dumps) so I suppose it's an editorialization on what you
really typed.  Perhaps the problem is hidden there.  Can you show us an
*exact* transcript of a failing session?

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Regarding autocomplete

[Stuart Cooper]

Can anybody tell me how to enable autocomplete and history in psql.


Make sure your platform has the readline libraries installed.

Under an RPM based Linux, try
$ rpm -qa | grep readline

You can also under Linux try $ ldd psql
and see if it finds readline.so as one of its dependencies.

I can't help you  on Windows or Mac.

Hope this helps,
Stuart.

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