[GENERAL] Domain types versus anyelement

[Brendan Jurd]

I'm getting some surprising behaviour from domain types in 8.3.7.

I have a domain aud on numeric(20,2).  When I try to use a value of
this type with the built-in greater() function, I get this:

# select greater(1::aud, 0);
ERROR:  function greater(aud, integer) does not exist
LINE 1: select greater(1::aud, 0);
   ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

I see that the argument types for greater() are (anyelement, anyelement).

It seems peculiar at best, that a domain on a perfectly valid
anyelement type is not considered a candidate match for anyelement.

Cheers,
BJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Domain types versus anyelement

[Brendan Jurd]

2009/9/3 Brendan Jurd dire...@gmail.com:
 # select greater(1::aud, 0);
 ERROR:  function greater(aud, integer) does not exist
 LINE 1: select greater(1::aud, 0);
               ^
 HINT:  No function matches the given name and argument types. You
 might need to add explicit type casts.


Apologies for the spam, I just realised that it's because all
anyelement args in the call to greater() must be the same type.

Cheers,
BJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] maximum count of contiguous years

[gorsa]

is there a way to get the maximum count of contiguous years? for example:

SELECT version();
PostgreSQL 8.3.1, compiled by Visual C++ 1400

CREATE TABLE sch_applform
(
  scholar_id integer NOT NULL,
  award_year numeric(4) NOT NULL,
  CONSTRAINT sch_applform_pkey PRIMARY KEY (scholar_id, award_year)
)
WITH (OIDS=FALSE);

INSERT INTO sch_applform VALUES (1, 1994);
INSERT INTO sch_applform VALUES (1, 1995);
INSERT INTO sch_applform VALUES (1, 1996);
INSERT INTO sch_applform VALUES (1, 1997);
INSERT INTO sch_applform VALUES (1, 1999);
INSERT INTO sch_applform VALUES (1, 2000);
INSERT INTO sch_applform VALUES (1, 2001);

INSERT INTO sch_applform VALUES (2, 1994);
INSERT INTO sch_applform VALUES (2, 1996);
INSERT INTO sch_applform VALUES (2, 1997);
INSERT INTO sch_applform VALUES (2, 1998);
INSERT INTO sch_applform VALUES (2, 1999);
INSERT INTO sch_applform VALUES (2, 2000);
INSERT INTO sch_applform VALUES (2, 2002);

INSERT INTO sch_applform VALUES (3, 1994);
INSERT INTO sch_applform VALUES (3, 1995);
INSERT INTO sch_applform VALUES (3, 1997);
INSERT INTO sch_applform VALUES (3, 1998);
INSERT INTO sch_applform VALUES (3, 2000);
INSERT INTO sch_applform VALUES (3, 2001);

is there a select statement containing 'AND award_year BETWEEN 1994
AND 2002' that could generate the following?
scholar_idconsistent_yrs
 1 4
 2 5
 3 2

thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Does PG cache results of an aggregate function, (and results of non-volatile functions)?

[Allan Kamau]

Hi,
I do have a query which make use of the results of an aggregate
function (for example bit_or) several times in the output column list
of the SELECT clause, does PostgreSQL simply execute the aggregate
function only once and provide the output to the other calls to the
same aggregate function.
How about the case of non volatile functions? Do they get executed as
many times as they occur in the select clause?

Allan.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] easy task: concurrent select-updates

[Nickolay]

Hi All,

I have a trivial task. There is a table with messages queue, let's say 
msg_queue.
There are a few processes and each of them is taking one message from 
this table at a time to transmit into communication channel.
I've done it my way, but I have postgresql's messages about deadlocks 
and a lot of warnings.


I my program, every process is doing approx the following procedure:
SELECT ... FROM msg_queue WHERE busy = false ORDER BY ... LIMIT 1;
if a message was found:
BEGIN;
SELECT id FROM msg_queue WHERE id = ... AND busy = false FOR SHARE;
UPDATE msg_queue SET busy = true, channel_id = ... WHERE id = ... AND 
busy = false;

COMMIT;


I do understand that this way is stupid, but I have not came with 
anything else yet.
Could somebody share ideas how to do this so the same message 100% WOULD 
NOT be transmitted over two or more channels.

Sorry for the newbie question!

Best regards, Nick.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] easy task: concurrent select-updates

[Nickolay]

one important addition: the message cannot be removed from queue table 
until it is transmitted, so DELETE is not an option :)

Hi All,

I have a trivial task. There is a table with messages queue, let's say 
msg_queue.
There are a few processes and each of them is taking one message from 
this table at a time to transmit into communication channel.
I've done it my way, but I have postgresql's messages about deadlocks 
and a lot of warnings.


I my program, every process is doing approx the following procedure:
SELECT ... FROM msg_queue WHERE busy = false ORDER BY ... LIMIT 1;
if a message was found:
BEGIN;
SELECT id FROM msg_queue WHERE id = ... AND busy = false FOR SHARE;
UPDATE msg_queue SET busy = true, channel_id = ... WHERE id = ... AND 
busy = false;

COMMIT;


I do understand that this way is stupid, but I have not came with 
anything else yet.
Could somebody share ideas how to do this so the same message 100% 
WOULD NOT be transmitted over two or more channels.

Sorry for the newbie question!

Best regards, Nick.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] install postgis in linux server without desktop

[Robert Dörfler]

Am Mittwoch, den 02.09.2009, 16:54 -0700 schrieb shane_china:
 I do follow your instruction. I successfully install postgis, but I can't
 find postgis.sql under any folder.
 My postgresql installed by apt-get.
 
 After apt-get postgis, What should I to do?  Only execute sql in
 postgis.sql?
 

Already checked:
http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/ ?

-- 
Greetings, 
Robert



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Subselect problem

[Wellmann, Harald]

Ok, here's some more details.

This is my query:

SELECT ah.FEATURE_ID, FG.TILE_NUM FROM
nndb.admin_hierarchy ah
JOIN NNDB.LINK_ADMIN LA
ON ah.FEATURE_ID = LA.ADMIN_ID
JOIN psi.FEATURE_GEOMETRY FG
ON LA.LINK_ID = FG.NNDB_FEATURE_ID
WHERE  fg.tile_num = 8613949  and ah.feature_id in (SELECT AH2.FEATURE_ID
FROM NNDB.ADMIN_HIERARCHY AH2 
LEFT JOIN psi.FEATURE_GEOMETRY FG2
ON AH2.FEATURE_ID = FG2.NNDB_FEATURE_ID
WHERE FG2.nndb_feature_id IS NULL)


This is the output of EXPLAIN:


Hash Join  (cost=87.20..105.37 rows=13 width=8)
  Hash Cond: (fg.nndb_feature_id = la.link_id)
  -  Bitmap Heap Scan on feature_geometry fg  (cost=4.45..22.51 rows=26 
width=8)
Recheck Cond: (tile_num = 8613949)
-  Bitmap Index Scan on nx_featuregeometry_tilenum  (cost=0.00..4.45 
rows=26 width=0)
  Index Cond: (tile_num = 8613949)
  -  Hash  (cost=82.59..82.59 rows=13 width=8)
-  Nested Loop Semi Join  (cost=73.89..82.59 rows=13 width=8)
  Join Filter: (ah.feature_id = la.admin_id)
  -  Seq Scan on admin_hierarchy ah  (cost=0.00..1.13 rows=13 
width=4)
  -  Materialize  (cost=73.89..99.95 rows=2606 width=12)
-  Nested Loop  (cost=50.20..71.29 rows=2606 width=12)
  -  HashAggregate  (cost=50.20..50.21 rows=1 
width=4)
-  Hash Anti Join  (cost=48.95..50.19 rows=1 
width=4)
  Hash Cond: (ah2.feature_id = 
fg2.nndb_feature_id)
  -  Seq Scan on admin_hierarchy ah2  
(cost=0.00..1.13 rows=13 width=4)
  -  Hash  (cost=31.20..31.20 rows=1420 
width=4)
-  Seq Scan on feature_geometry 
fg2  (cost=0.00..31.20 rows=1420 width=4)
  -  Index Scan using linkadmin_adminid on link_admin 
la  (cost=0.00..17.82 rows=261 width=8)
Index Cond: (la.admin_id = ah2.feature_id)


The query result is empty.

However, using the query

SELECT ah.FEATURE_ID, FG.TILE_NUM FROM
nndb.admin_hierarchy ah
JOIN NNDB.LINK_ADMIN LA
ON ah.FEATURE_ID = LA.ADMIN_ID
JOIN psi.FEATURE_GEOMETRY FG
ON LA.LINK_ID = FG.NNDB_FEATURE_ID
WHERE  fg.tile_num = 8613949  and ah.feature_id in (170303063)


where 170303063 is one of the values returned by the subselect in the first 
query when run in isolation, you get a non-empty result set, so there seems to 
be a problem with the subselect.

If you need any other information to decide whether this is one of the known 
bugs or a new one, just let me know what exactly you need. I can provide a 
backup of the three tables in question, which should be enough to isolate the 
problem.

Best regards,
Harald



 -Ursprüngliche Nachricht-
 Von: Tom Lane [mailto:t...@sss.pgh.pa.us] 
 Gesendet: Mittwoch, 2. September 2009 20:09
 An: Wellmann, Harald
 Cc: pgsql-general@postgresql.org
 Betreff: Re: [GENERAL] Subselect problem 
 
 Wellmann, Harald harald.wellm...@harman.com writes:
  The problem occurs with PostgreSQL 8.4.0. I cannot 
 reproduce it with 
  PostgreSQL 8.3.7.
 
 There are known bugs in 8.4.0 having to do with improperly 
 exchanging the ordering of semijoins (IN joins) and other 
 joins.  You haven't provided enough information to test 
 whether your case is one of them.
 If you can try CVS branch tip or a recent nightly snapshot, 
 there might still be enough time to do something about it for 
 8.4.1, if it isn't fixed already.
 
   regards, tom lane
 
 
***
innovative systems GmbH Navigation-Multimedia
Geschaeftsfuehrung: Edwin Summers - Michael Juergen Mauser
Sitz der Gesellschaft: Hamburg - Registergericht: Hamburg HRB 59980 
 
***
Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte 
Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail 
irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und 
loeschen Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe 
dieser Mail ist nicht gestattet.
This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received this e-mail in error) please 
notify the sender immediately and delete this e-mail. Any unauthorized copying, 
disclosure or distribution of the contents in this e-mail is strictly forbidden.
***

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] To pass schemaname as a function parameter

[Kalai R]

Hi,

I wrote a function in plpgsql, to dispaly the student list.

In a Database all schemas contains studentlist table. so I wrote the
function with schemaname as a parameter(text data type). My code is
like

CREATE FUNCTION disp_fn(schemaname text) AS $$
BEGIN
SELECT * FROM schemaname.studentlist;
END;
$$ LANGUAGE plpgsql;


In the above function schemaname varaible does not taken.

Is there any way to pass schemaname as argument? Any idea would be
much  appreciated.

 Thanks in Advance.

Regards

Softlinne

Re: [GENERAL] To pass schemaname as a function parameter

[A. Kretschmer]

In response to Kalai R :
 Hi,
 
 I wrote a function in plpgsql, to dispaly the student list.
 
 In a Database all schemas contains studentlist table. so I wrote the function 
 with schemaname as a parameter(text data type). My code is like
 
 CREATE FUNCTION disp_fn(schemaname text) AS $$
 BEGIN
 SELECT * FROM schemaname.studentlist;
 END;
 $$ LANGUAGE plpgsql;

Not possible in this way, use EXECUTE:

   execute 'select * from ' || schemaname || '.studentlist';


(it is a dynamic SQL, you haven't a fix tablename)

Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] To pass schemaname as a function parameter

[Merlin Moncure]

On Thu, Sep 3, 2009 at 7:37 AM, A.
Kretschmerandreas.kretsch...@schollglas.com wrote:
 In response to Kalai R :
 Hi,

 I wrote a function in plpgsql, to dispaly the student list.

 In a Database all schemas contains studentlist table. so I wrote the 
 function with schemaname as a parameter(text data type). My code is like

 CREATE FUNCTION disp_fn(schemaname text) AS $$
 BEGIN
     SELECT * FROM schemaname.studentlist;
 END;
 $$ LANGUAGE plpgsql;

 Not possible in this way, use EXECUTE:

   execute 'select * from ' || schemaname || '.studentlist';


 (it is a dynamic SQL, you haven't a fix tablename)

also (IMO preferred),

execute 'set search_path = public, ' || schemaname;
SELECT * FROM studentlist;

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] maximum count of contiguous years

[Tim Landscheidt]

gorsa gorsa.1...@gmail.com wrote:

 [...]
 is there a select statement containing 'AND award_year BETWEEN 1994
 AND 2002' that could generate the following?
 scholar_idconsistent_yrs
  1 4
  2 5
  3 2

You could either do some wild fancy query where you parti-
tion the data by scholar_id, then by award_year, then filter
on the condition that the sum of award_year and RANK() (?)
less one equals the current award_year, find the maximum of
those, ...

  ... or you could just write a short function in your ap-
plication (or a set-returning PL/pgSQL function if your ap-
plication is dumb).

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PosgreSQL Service does not Start In Vista

[Inigo Barandiaran]

Hi.

I've tried to install postgree in Vista in program files and the service
does not start. I gave manually full privileges to the posgre user to data
folder, and still the service does not start.

Finally, I installed posgre database out of program Files as Thomas
suggested and I gave full privileges to posgre user to data folder and
everything works correctly :)

Do you know how can I install posgre in program Files in Vista?. I might
some users of my application would like to install it  in porgram Files
folder :(. How can I Solve this problem?

Thanks in advance!!!


Thomas Kellerer wrote:
 
 Inigo Barandiaran, 02.09.2009 14:53:
 Thanks Thomas!.
 That sounds very interesting. How can I set privileges for writing in
 data
 directory for the postgres  user account?. 
 Right click on the directory and choose Security. Anything after that is
 off-topic in this list ;)
 
 
 Or is it very to directly install
 posgreSQL out of Program Files Directory?
 
 Putting application data into Program Files is a very bad idea in
 general, not only for Postgres. 
 I'm not sure if the current installer still suggests this, but this was a
 major flaw in the installers I have used before. The installer does offer
 the possibility to change this, but how many people really take the time
 to read the wizard pages?
 
 Thomas
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
 
 

-- 
View this message in context: 
http://www.nabble.com/PosgreSQL-Service-does-not-Start-In-Vista-tp25255182p25275277.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] install postgis in linux server without desktop

[Robert Dörfler]

 Thank you very much, I successfully install postgis.

no problem ;)

-- 
Greetings,
Robert


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Data folder in differnet filesystem

[S Arvind]

I have 2 doubts related to Filsesytem and Postgres data folder

1.Currently in CentOS,  our postgres data folder is in single filesystem. Is
there any possibility to have a single data folder of single postgres in
more then one file system.
2.I am having three big DB, is it possible to have a data of each DB in
different file system. so if i want to add a DB to postgre i will mount a
drive (filesystem ) and point that location for the postgres to use that
space.
  for eg
Filesystem Mounted on
/dev/hda3  /data/db1for DB1
/dev/hda4  /data/db2for DB2

thanks in advance


Arvind S



Many of lifes failure are people who did not realize how close they were to
success when they gave up.
-Thomas Edison

[GENERAL] How do I use tsvector_update_trigger to index non-character columns?

[dennis jenkins]

Hello.
 I have several tables in a database that I want to use with full
text searching.  Some of the fields in some of these tables are not
character types, but for the purposes of searching, I'd like to
include their character representation in the tsvector.
Unfortunately, I cannot make this work without duplicating the column
(original as integer and dup as 'text' and using a trigger to
regenerate the dup on insert/update).  I would prefer a cleaner
approach.

I've reduced the problem to a small amount of SQL (included below)
that illustrates my problem.  Thank you kindly for any assistance.

capybara=# select version();
   version
--
 PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.1)
(1 row)


-- Attempt #1:


capybara=# drop table if exists abc;
DROP TABLE

capybara=# create table abc (
abc_id serial not null,
client_num integer not null,
abc_name text not null,
tsv tsvector,
constraint abc_pkey primary key (abc_id)
) with (oids=false);
NOTICE:  CREATE TABLE will create implicit sequence abc_abc_id_seq
for serial column abc.abc_id
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
abc_pkey for table abc
CREATE TABLE

capybara=# CREATE TRIGGER abc_tsv_update BEFORE INSERT OR UPDATE ON
abc FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('tsv',
'pg_catalog.english', 'client_num', 'abc_name');
CREATE TRIGGER

capybara=# insert into abc (client_num, abc_name) values (2751, 'bob');
ERROR:  column client_num is not of character type


--- Attempt #2
-- same table, different trigger function:


capybara=# CREATE TRIGGER abc_tsv_update BEFORE INSERT OR UPDATE ON
abc FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('tsv',
'pg_catalog.english', 'cast (client_num as text)', 'abc_name');
CREATE TRIGGER

capybara=# insert into abc (client_num, abc_name) values (2751, 'bob');
ERROR:  column cast (client_num as text) does not exist

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] array weirdity

[Sim Zacks]

How can these both be true? Is there a way to see if a value is not in
an array?


select 1229  any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result

result

---

t


select 1229 = any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result

result

---

t




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] array weirdity

[Richard Huxton]

Sim Zacks wrote:
 How can these both be true? Is there a way to see if a value is not in
 an array?
 
 
 select 1229  any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result
 
 result
 ---
 t

Not any(), all() - it *is* different from some of the numbers there.

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] array weirdity

[Michael Glaesemann]

On Sep 3, 2009, at 9:19 , Sim Zacks wrote:


How can these both be true? Is there a way to see if a value is not in
an array?


select 1229  any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result

result

---

t



Here you're comparing 1229 to each element in the array in turn, and  
returning TRUE if ANY of those comparisons are TRUE. As 1229  1220  
(for example), it's TRUE.



select 1229 = any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result

result

---

t


Again, you're comparing 1229 to each element in the array in turn,  
returning TRUE if ANY of the comparisons are TRUE. AS 1229 is an  
element in the  array, the result is TRUE.


You're probably looking for ALL

SELECT 1229  ALL('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result;
 result

 f
(1 row)

or use NOT

SELECT NOT (1229 = ANY('{1220,0,0,1228,1229,1231,0,0,0}'::int[])) as  
result;

 result

 f
(1 row)

Michael Glaesemann
grzm seespotcode net




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

[Richard Broersma]

On Wed, Sep 2, 2009 at 4:35 PM, David Fetterda...@fetter.org wrote:

 Hibernate has the very nice feature of being able to get out of your
 way.  Properly used, it can keep completely out of the business of
 making (wrong) guesses based on DDL, which is what ORMs often do.
 DBIx::Class http://search.cpan.org/dist/DBIx-Class/ has gone a long
 way in the right direction.

 Ones which (attempt to) dictate decisions about DDL are just off the
 map. :P

David, do you know how well these kinds of ORMs work when it come to
mapping  non-trivial schema designs?  For example, how would these
work when creating a mapping for the multiple inheritance design that
you've blogged about earlier?


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Snow Leopard and plpython problem

[Jerry LeVan]

Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard.

I cannot get plpython to build...

I have installed MacPython 2.6.2 in /Library/Frameworks/Python.framework

The binary:

mbp:postgresql-8.4.0 postgres$ which python
/Library/Frameworks/Python.framework/Versions/Current/bin/python

mbp:postgresql-8.4.0 postgres$ file `which python`
/Library/Frameworks/Python.framework/Versions/Current/bin/python: Mach- 
O universal binary with 2 architectures
/Library/Frameworks/Python.framework/Versions/Current/bin/python (for  
architecture ppc):	Mach-O executable ppc
/Library/Frameworks/Python.framework/Versions/Current/bin/python (for  
architecture i386):	Mach-O executable i386


mbp:postgresql-8.4.0 postgres$ echo $PATH
/Library/Frameworks/Python.framework/Versions/Current/bin:/usr/bin:/ 
bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin

mbp:postgresql-8.4.0 postgres$ python
Python 2.6.2 (r262:71600, Apr 16 2009, 09:17:39)
[GCC 4.0.1 (Apple Computer, Inc. build 5250)] on darwin
Type help, copyright, credits or license for more information

I use the following config params:

mbp:postgresql-8.4.0 postgres$ cat configJHL
./configure --bindir=/usr/local/bin --mandir=/usr/local/share/man  \
 --enable-thread-safety \
 --with-python \
 --with-perl --with-tcl \
 --with-libedit-preferred \
  --with-openssl --with-bonjour

Configure completes ok.

When I do a make 2mylog.log

The build fails on
gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - 
Wdeclaration-after-statement
-Wendif-labels -fno-strict-aliasing -fwrapv  -bundle -multiply_defined  
suppress  plpython.o
-bundle_loader ../../../src/backend/postgres  -L../../../src/port - 
framework Python  -o plpython.so


The contents of mylog.log are:
postmaster.c: In function ‘PostmasterMain’:
postmaster.c:859: warning: ‘DNSServiceRegistrationCreate’ is  
deprecated (declared at /usr/include/DNSServiceDiscovery/ 
DNSServiceDiscovery.h:139)

pgc.c:4363: warning: ‘yy_flex_realloc’ defined but not used
ld: warning: in ../../../src/backend/postgres, file is not of required  
architecture

ld: warning: in plperl.o, file is not of required architecture
ld: warning: in spi_internal.o, file is not of required architecture
ld: warning: in SPI.o, file is not of required architecture
ld: warning: in ../../../src/backend/postgres, file is not of required  
architecture

ld: warning: in plperl.o, file is not of required architecture
ld: warning: in spi_internal.o, file is not of required architecture
ld: warning: in SPI.o, file is not of required architecture
ld: warning: in /Library/Frameworks//Python.framework/Python, missing  
required architecture x86_64 in file

Undefined symbols:
  _PyDict_DelItemString, referenced from:
  _PLy_function_delete_args in plpython.o
  _PyIter_Next, referenced from:
  _PLy_function_handler in plpython.o
  _PyFloat_FromDouble, referenced from:
  _PLyFloat_FromString in plpython.o
  _PyErr_Fetch, referenced from:
  _PLy_elog in plpython.o
  _PyErr_NewException, referenced from:
  __PG_init in plpython.o
  __PG_init in plpython.o
  __PG_init in plpython.o
  _PyErr_GivenExceptionMatches, referenced from:
  _PLy_elog in plpython.o
  _PLy_elog in plpython.o
  _PyDict_GetItemString, referenced from:
  _PLy_modify_tuple in plpython.o
  _PLy_procedure_get in plpython.o
  _Py_Initialize, referenced from:
  __PG_init in plpython.o
  _PyArg_ParseTuple, referenced from:
  _PLy_plan_status in plpython.o
  _PLy_spi_prepare in plpython.o
  _PLy_spi_execute in plpython.o
  _PLy_spi_execute in plpython.o
  _PyList_New, referenced from:
  _PLy_trigger_build_args in plpython.o
  _PLy_function_build_args in plpython.o
  _PLy_spi_execute_fetch_result in plpython.o
  _PLy_spi_execute_fetch_result in plpython.o
  _PyCObject_FromVoidPtr, referenced from:
  _PLy_procedure_create in plpython.o
  _PyRun_StringFlags, referenced from:
  _PLy_procedure_create in plpython.o
  _PyDict_GetItem, referenced from:
  _PLy_modify_tuple in plpython.o
  _PyObject_GetIter, referenced from:
  _PLy_function_handler in plpython.o
  _PySequence_Check, referenced from:
  _PLy_function_handler in plpython.o
  _PLy_spi_prepare in plpython.o
  _PLy_spi_execute_plan in plpython.o
  _PyMapping_GetItemString, referenced from:
  _PLyMapping_ToTuple in plpython.o
  _PySequence_Size, referenced from:
  _PLySequence_ToTuple in plpython.o
  _PLy_spi_prepare in plpython.o
  _PLy_spi_execute_plan in plpython.o
  _PyErr_NormalizeException, referenced from:
  _PLy_elog in plpython.o
  _PySequence_GetItem, referenced from:
  _PLySequence_ToTuple in plpython.o
  _PLy_spi_prepare in plpython.o
  _PLy_spi_execute_plan in plpython.o
  _PyObject_GetAttrString, referenced from:
  _PLyObject_ToTuple in plpython.o
  _PyBool_FromLong, referenced from:
  _PLyBool_FromString in plpython.o
  _PLyBool_FromString in plpython.o
  _PyObject_Str, 

Re: [GENERAL] Snow Leopard and plpython problem

[Emanuel Calvo Franco]

2009/9/3 Jerry LeVan jerry.le...@eku.edu:
 Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard.

 I cannot get plpython to build...

 I have installed MacPython 2.6.2 in /Library/Frameworks/Python.framework

 The binary:


In Debian I had to install these libs to get work 8.4:libsdl-perl,
libperl-dev, python-all-dev (for perl and python). For 8.3 i didn't
need it (seems).
But in my case i use 2.5. Hope it helps :S




-- 
  Emanuel Calvo Franco
 DBA at:  www.siu.edu.ar
www.emanuelcalvofranco.com.ar

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Snow Leopard and plpython problem

[Jan Otto]

hi jerry,


Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard.

I cannot get plpython to build...

I have installed MacPython 2.6.2 in /Library/Frameworks/ 
Python.framework


do you really need python 2.6.2? snow leopard has python 2.6.1 on  
board and
there are no problems compiling/using postgresql with the python that  
comes

with snow leopard.


The binary:

mbp:postgresql-8.4.0 postgres$ which python
/Library/Frameworks/Python.framework/Versions/Current/bin/python

mbp:postgresql-8.4.0 postgres$ file `which python`
/Library/Frameworks/Python.framework/Versions/Current/bin/python:  
Mach-O universal binary with 2 architectures
/Library/Frameworks/Python.framework/Versions/Current/bin/python  
(for architecture ppc):	Mach-O executable ppc
/Library/Frameworks/Python.framework/Versions/Current/bin/python  
(for architecture i386):	Mach-O executable i386


you need the 64-bit version of python (x86_64) or try compile  
postgresql 32-bit (i386)


asche:~ asche$ file `which python`
/usr/bin/python: Mach-O universal binary with 3 architectures
/usr/bin/python (for architecture x86_64):	Mach-O 64-bit executable  
x86_64

/usr/bin/python (for architecture i386):Mach-O executable i386
/usr/bin/python (for architecture ppc7400): Mach-O executable ppc


./configure --bindir=/usr/local/bin --mandir=/usr/local/share/man  \
--enable-thread-safety \
--with-python \
--with-perl --with-tcl \
--with-libedit-preferred \
 --with-openssl --with-bonjour

Configure completes ok.

When I do a make 2mylog.log


i have tried configuring an building with the same options as you and  
it compiles without an error or warning

about python.

regards, jan otto



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Audit Trigger puzzler

[David Kerr]

On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote:
- In Oracle, the way we handle audit triggers is by using Package 
- Variables.  We emulate some of that functionality in postgresql by 
- adding a custom variable to the configuration file:
- 
- custom_variable_classes = 'mysess'
- 
- Then, whenever a user logs into the application, my login procedure 
- calls this function:
- 
- CREATE OR REPLACE FUNCTION begin_sess(staffid character varying)
-   RETURNS void AS $BODY$ BEGIN
- PERFORM set_config('mysess.curr_user', coalesce(staffid,''), false);
- END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
- 
- This makes the current application user automatically available to every 
-   function, including triggers.  Then, in your triggers, you can do 
- this:
- 
- DECLARE
- curr_user staff.staff_id%TYPE;
- BEGIN
- SELECT current_setting('mysess.curr_user') INTO curr_user;
- 
- 
- In your trigger, you could check that this variable was unset, and fall 
- back to the database user.
- 

Thanks! that does seem slick, but will it work with connection pooling?

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] install postgis in linux server without desktop

[shane_china]

Thank you very much, I successfully install postgis.



Robert Dörfler wrote:
 
 Am Mittwoch, den 02.09.2009, 16:54 -0700 schrieb shane_china:
 I do follow your instruction. I successfully install postgis, but I can't
 find postgis.sql under any folder.
 My postgresql installed by apt-get.
 
 After apt-get postgis, What should I to do?  Only execute sql in
 postgis.sql?
 
 
 Already checked:
 http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/ ?
 
 -- 
 Greetings, 
 Robert
 
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
 
 

-- 
View this message in context: 
http://www.nabble.com/install-postgis-in-linux-server-without-desktop-tp25258662p25274835.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL Live CD based on CentOS 5.3 and PG 8.4 released

[Devrim GÜNDÜZ]

On Thu, 2009-09-03 at 11:38 -0300, Emanuel Calvo Franco wrote:
 Other thing that you can implement is a test or example database.

Done. It will now create pagila database and load pagila data to it. :)
This will also appear in next week's release.

 For the lazy admins, you can add postgres to sudoers file or create
 a user with that option.

Live CD is used/opened with postgres user, so I don't think sudo will be
useful. ;)

Thanks again.

Regards,
-- 
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
   http://www.gunduz.org


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Audit Trigger puzzler

[Adam Rich]

David Kerr wrote:

On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote:
- In Oracle, the way we handle audit triggers is by using Package 
- Variables.  We emulate some of that functionality in postgresql by 
- adding a custom variable to the configuration file:
- 
- custom_variable_classes = 'mysess'
- 
- 
- In your trigger, you could check that this variable was unset, and fall 
- back to the database user.
- 


Thanks! that does seem slick, but will it work with connection pooling?

Dave



I don't see why it wouldn't work, as long as you set reset_query_list 
properly, and set the session variable the the logged in user whenever 
you grab a connection from the pool.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] easy task: concurrent select-updates

[Andy Colson]

Nickolay wrote:
one important addition: the message cannot be removed from queue table 
until it is transmitted, so DELETE is not an option :)

Hi All,

I have a trivial task. There is a table with messages queue, let's say 
msg_queue.
There are a few processes and each of them is taking one message from 
this table at a time to transmit into communication channel.
I've done it my way, but I have postgresql's messages about deadlocks 
and a lot of warnings.


I my program, every process is doing approx the following procedure:
SELECT ... FROM msg_queue WHERE busy = false ORDER BY ... LIMIT 1;
if a message was found:
BEGIN;
SELECT id FROM msg_queue WHERE id = ... AND busy = false FOR SHARE;
UPDATE msg_queue SET busy = true, channel_id = ... WHERE id = ... AND 
busy = false;

COMMIT;


I do understand that this way is stupid, but I have not came with 
anything else yet.
Could somebody share ideas how to do this so the same message 100% 
WOULD NOT be transmitted over two or more channels.

Sorry for the newbie question!

Best regards, Nick.







how about this:

andy=# create table msg (id integer, busy boolean, message text);
CREATE TABLE
andy=# insert into msg values (1, false, 'message one');
INSERT 0 1
andy=# insert into msg values (2, false, 'message two');
INSERT 0 1


CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql
AS $function$
declare
   rec record;
begin
   for rec in select id from msg where busy = false order by id loop
   update msg set busy = true where id = rec.id and busy = false;
   if found then
   return rec.id;
   end if;
   end loop;
   return -1;
end;
$function$



It returns -1 if no message found.  Not 100% sure, but a quick two session test 
seemed to work.

-Andy


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problem with leaking connections

[Mark Lange]

Hi,

 

we have an webapplication running on tomcat6  with hibernate.

We are using the apache DBCP connection pool (tomcat built in) and an
postgres 8.19 database server on an different host.

Every few days  the pool is  running out of connections.

The pool is configured to aggressively close connections when it is
exhausted, but the application gets very slow when this happens, mostly we
have to restart

the tomcat server.

#ps -aux | grep postgres on the database host shows me many open connections
in the select or in transaction state (that never get closed until tomcat or
postgres restart).

Is there a way to figure out which statement didn't closed the connections?

 

In the application we found a few places where the connections not closed
and fixed them.

But they are hard to find.

 

Is it possible to configure postgres to close connections after a timeout or
something like this (maybe statement_timeout)?

Are there any other possibilities?

 

Thanks

Mark

 

 

Re: [GENERAL] Problem with leaking connections

[Joshua D. Drake]

On Thu, 2009-09-03 at 17:55 +0200, Mark Lange wrote:
 Hi,

 The pool is configured to aggressively close connections when it is
 exhausted, but the application gets very slow when this happens,
 mostly we have to restart
 
 the tomcat server.
 
 #ps –aux | grep postgres on the database host shows me many open
 connections in the select or in transaction state (that never get
 closed until tomcat or postgres restart).
 
 Is there a way to figure out which statement didn’t closed the
 connections?
 
It isn't a statement it is your app code. If you have a ps aux that is
showing select it means you have selects running that aren't finished.
If it shows in transaction state (I assume idle in transaction) it also
means your app code is not properly committing or rollingback
transactions.

 Is it possible to configure postgres to close connections after a
 timeout or something like this (maybe statement_timeout)?
 

statement_timeout will terminate a query not a connection.


 Are there any other possibilities?
 

Fix your code. Not to sound harsh but this is blatant code issues.

Joshua D. Drake

 
 
-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problem with leaking connections

[Andy Colson]

Mark Lange wrote:

Hi,

 


we have an webapplication running on tomcat6  with hibernate.

We are using the apache DBCP connection pool (tomcat built in) and an 
postgres 8.19 database server on an different host.


Every few days  the pool is  running out of connections.

The pool is configured to aggressively close connections when it is 
exhausted, but the application gets very slow when this happens, mostly 
we have to restart


the tomcat server.

#ps –aux | grep postgres on the database host shows me many open 
connections in the select or in transaction state (that never get closed 
until tomcat or postgres restart).


Is there a way to figure out which statement didn’t closed the connections?

 

In the application we found a few places where the connections not 
closed and fixed them.


But they are hard to find.

 

Is it possible to configure postgres to close connections after a 
timeout or something like this (maybe statement_timeout)?


Are there any other possibilities?

 


Thanks

Mark




I'm gonna guess you are not leaking connections.  That, in itself, would not 
make pg slow.  It would cause errors when you hit the max_connections setting 
though.


#ps –aux | grep postgres on the database host shows me many open 
connections in the select or in transaction state 


If you mean you see idle in transaction, then that's what's causing the slow 
down.  And you really don't want to time out or force close them, because the transaction 
would be rolled back.

I think you're only option is to fix the code.  You really need to commit 
transactions.

If, on the other hand, the ps -aux shows you many that are idle, then that's what you 
want.  The connection pooler is supposed to keep open connections.  (and having a pooler 
aggressively close seems counter productive... why even bother with it then?)

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PosgreSQL Service does not Start In Vista

[Thomas Kellerer]

Inigo Barandiaran, 03.09.2009 14:47:

Finally, I installed posgre database out of program Files as Thomas
suggested and I gave full privileges to posgre user to data folder and
everything works correctly :)

Do you know how can I install posgre in program Files in Vista?. I might
some users of my application would like to install it  in porgram Files
folder :(. How can I Solve this problem?



You _can_ install it into Program Files, just don't put the *data directory* there. 


I don't remember the individual steps of the installer wizard, but I'm sure 
there was a point where you could choose to run initdb and specify a location 
for the datadir.

Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] auto-increment in a view

[Willy-Bas Loos]

Hi,

I'm trying to figure out to generate a auto-increment column in a view.
There is no physical column to base it on, the view contains a group
by clause, which renders that impossible.
In a normal query i can create a sequence for that purpouse and drop
it afterwards, but apart form it being ugly, it's impossible in a
view.

Another possibility is to crate a function and call that function from the view.
It works, but the function is not transparent, like the view is.
Meaning: the function will execute the whole query, gather the
results, and when i only need a subset, it will just forget about the
surplus.

Isnt't there a decent way to add an incrementing value to a view?

Cheers,

WBL

see code below, this is postgresql 8.3.7

--drop table test;create table test(id integer primary key, value integer);
insert into test (id, value) values (generate_series(1,100),
generate_series(1,100)/4);
vacuum analyze test;
--drop view testview;
create or replace view testview as (select value from test group by value);
select * from testview limit 5;
--2734 ms (warm)

create or replace view testview2 as (select null::serial, value from
test group by value);
--ERROR:  type serial does not exist
create or replace view testview2 as (create sequence tempseq;select
nextval('tempseq'), value from test group by value;create sequence
tempseq;);
--ERROR:  syntax error at or near create

create type testview2_type as (recnr integer, value integer);
create or replace function testview2() returns setof testview2_type as $$
declare
  t_recnr integer:=0;
  t_rec record;
  t_rec2 testview2_type;
begin
for t_rec in select value from test group by value
loop
  t_recnr:=t_recnr+1;
  t_rec2.recnr:=t_recnr;
  t_rec2.value:=t_rec.value;
  return next t_rec2;
end loop;
return;
end
$$ language plpgsql;

create or replace view testview2 as select * from testview2();
select * from testview2 limit 5;
--3946 ms (warm)

-- 
Patriotism is the conviction that your country is superior to all
others because you were born in it. -- George Bernard Shaw

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] auto-increment in a view

[Adam Rich]

Willy-Bas Loos wrote:

Hi,

I'm trying to figure out to generate a auto-increment column in a view.
There is no physical column to base it on, the view contains a group
by clause, which renders that impossible.
In a normal query i can create a sequence for that purpouse and drop
it afterwards, but apart form it being ugly, it's impossible in a
view.

Another possibility is to crate a function and call that function from the view.
It works, but the function is not transparent, like the view is.
Meaning: the function will execute the whole query, gather the
results, and when i only need a subset, it will just forget about the
surplus.

Isnt't there a decent way to add an incrementing value to a view?

Cheers,

WBL



Sounds like you need ROWNUM which is easy to do with windowing 
functions in 8.4, but on 8.3 you'll need a hack like this:


http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/

Then you could define your view as:

create or replace view testview as
select rownum(), value from test group by value;







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] auto-increment in a view

[Raymond O'Donnell]

On 03/09/2009 17:22, Willy-Bas Loos wrote:
 Hi,
 
 I'm trying to figure out to generate a auto-increment column in a view.
 There is no physical column to base it on, the view contains a group
 by clause, which renders that impossible.
 In a normal query i can create a sequence for that purpouse and drop
 it afterwards, but apart form it being ugly, it's impossible in a
 view.

Just a wild notion, but would the generate_series function be any use to
you?

  http://www.postgresql.org/docs/8.4/static/functions-srf.html

HTH,

Ray.


--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] handle audiofiles in postgres

[Christopher Browne]

urs.edi...@gmail.com (edisan) writes:
 Can anyone used or tell me how to handle audio files in postgres

 Audio files may be in  wav / vox / dss format and each have average 30 min
 running time.

In principle, you could store these as BLOB data; I'd quite prefer
storing this as bytea data.

The TOAST capability
http://www.postgresql.org/docs/8.4/interactive/storage-toast.html
means that these BLOBs are actually stored in side tables, which
should be good for efficiency in that they won't make data tuples
enormous even though there's enormous data tied to them.

As a result, queries on metadata (e.g. - the other attributes of the
data) can be nicely efficient despite the bulky data.

It's not obvious, however, that storing the files in the database is
preferable to:

 - Putting the files in a filesystem, perhaps with cryptic names
   (hashes?)  

 - Storing the metadata about the files in the database, referencing the
   files' names

If there's good reason to store the files in the DBMS, then do so; just
make sure there's good reason for it!
-- 
let name=cbbrowne and tld=ca.afilias.info in name ^ @ ^ tld;;
Christopher Browne
Bother,  said Pooh,  Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

[David Fetter]

On Thu, Sep 03, 2009 at 07:24:50AM -0700, Richard Broersma wrote:
 On Wed, Sep 2, 2009 at 4:35 PM, David Fetterda...@fetter.org wrote:
 
  Hibernate has the very nice feature of being able to get out of
  your way.  Properly used, it can keep completely out of the
  business of making (wrong) guesses based on DDL, which is what
  ORMs often do.  DBIx::Class
  http://search.cpan.org/dist/DBIx-Class/ has gone a long way in
  the right direction.
 
  Ones which (attempt to) dictate decisions about DDL are just off
  the map. :P
 
 David, do you know how well these kinds of ORMs work when it come to
 mapping  non-trivial schema designs?  For example, how would these
 work when creating a mapping for the multiple inheritance design
 that you've blogged about earlier?

If your mapper only does the job of mapping, you can choose
classes/objects and then map them to the appropriate, possibly
parameterized, SQL queries, which the DB people can then freely
rearrange.

One nice feature of such a system is that the DBA and/or DB developer
has a way to know what the client code expects.  In OO terms, there
are public interfaces--everything mentioned in the ORM layer--and
private interfaces--DDL, DML, and DCL--to the database.

Programmers who like to use object-oriented languages and methods
should be happy about this object-oriented approach to database
management, but for some reason, a lot of them don't understand that
the idea of public and private interfaces applies to what they (too
simplistically, much of the time) think of as the persistence layer.

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] auto-increment in a view

[Willy-Bas Loos]

 Just a wild notion, but would the generate_series function be any use to
 you?

Good idea, but i can't get it to work.

create or replace view testview3 as (
select generate_series(1,(select count(*) from test group by value)), value
from test group by value);
select * from testview3 limit 5;
--ERROR:  more than one row returned by a subquery used as an expression

When i put the generate_series in the FROM clause, the results will be
matched everything to everything.

I guess the windowing function is the way to go.

Thx,

WBL


-- 
Patriotism is the conviction that your country is superior to all
others because you were born in it. -- George Bernard Shaw

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] add a value to an ENUM type

[Edoardo Panfili]

I have an enum type
CREATE TYPE shapeName AS ENUM('rectangle','circle');

now I need another value: 'square'

the pg_type.oid of 'shapename is 16458

It works, but Is it safe to use this?
insert into pg_enum (enumtypid,enumlabel) VALUES('16458','square');

thank you
Edoardo

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] column level, uid based authorization to update columns

[Gauthier, Dave]

In linux, given the linux based uid of the user, how might someone implement 
column level update restrictions on a uid basis?  For example...

create table foo (strcol varchar(256), intcol integer);

Now, I want linux processes runing under uid joesmith to be able to update 
strcol but not intcol.  Some other user could update intcol but not strcol.  
Others could update both, others neither.  I'm also willing to give you a table 
that maps all uids to the columns they can update, something you could ref in a 
constraint or update trigger or something.   So that might be something like...

create table foo_auth (uid varchar(256), cols text[]);
insert into foo_auth (uid,cols) values ('joesmith','{'strcol'}');
insert into foo_auth (uid,cols) values ('jillbrown','{'intcol'}');
insert into foo_auth (uid,cols) values 
('thedba','{'strcol','intcol'}');

Thanks in Advance !

Re: [GENERAL] column level, uid based authorization to update columns

[Ries van Twisk]

On Sep 3, 2009, at 12:17 PM, Gauthier, Dave wrote:

In linux, given the linux based uid of the user, how might someone  
implement column level update restrictions on a uid basis?  For  
example...


create table foo (strcol varchar(256), intcol integer);

Now, I want linux processes runing under uid “joesmith” to be able  
to update strcol but not intcol.  Some other user could update  
intcol but not strcol.  Others could update both, others neither.   
I’m also willing to give you a table that maps all uids to the  
columns they can update, something you could ref in a constraint or  
update trigger or something.   So that might be something like...


create table foo_auth (uid varchar(256), cols text[]);
insert into foo_auth (uid,cols) values  
(‘joesmith’,’{‘strcol’}’);

insert into foo_auth (uid,cols) values (‘jillbrown’,’{‘intcol’}’);
insert into foo_auth (uid,cols) values  
(‘thedba’,’{‘strcol’,’intcol’}’);


Thanks in Advance !



http://wiki.postgresql.org/wiki/SEPostgreSQL

Ries

Re: [GENERAL] column level, uid based authorization to update columns

[David Fetter]

On Thu, Sep 03, 2009 at 10:17:15AM -0700, Gauthier, Dave wrote:
 In linux, given the linux based uid of the user, how might someone
 implement column level update restrictions on a uid basis?  For
 example...

You can use ident authentication in pg_hba.conf and per-column
GRANT/REVOKE in 8.4.

http://www.postgresql.org/docs/current/static/sql-grant.html

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Create language PLPERL error

[Shakil Shaikh]

Hi all,

Running Postgres 8.4 on Ubuntu 9.04, installed via the clickonce installer. 
I'm getting a curious error when trying to create/add support for plperl to 
any database:


ERROR:  could not load library 
/opt/PostgreSQL/8.4/lib/postgresql/plperl.so: 
/opt/PostgreSQL/8.4/lib/postgresql/plperl.so: undefined symbol: 
Perl_Tcurpad_ptr


I previously got a libperl.so not found error which I thought I alleviated 
by installing dev-perl via Synaptic.


Any ideas on how to figure out what's going on?

Shak 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PL/Perl 64-bit and sending emails

[Mark Lubratt]

Hello!

I've been running the 64-bit version of 8.3.4 on OpenSolaris 2009.06  
for over a year.  Now, I need to put a perl function call into it to  
allow emails to be sent by the database backend.  I tried installing  
plperl, but it looks like only a 32-bit version is available.  Does  
the 64-bit version of plperl exist?  Or, does someone know of another  
way to get the backend to send an email?


Thanks!
Mark


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Create language PLPERL error

[Tom Lane]

Shakil Shaikh ssha...@hotmail.com writes:
 Running Postgres 8.4 on Ubuntu 9.04, installed via the clickonce installer. 
 I'm getting a curious error when trying to create/add support for plperl to 
 any database:

 ERROR:  could not load library 
 /opt/PostgreSQL/8.4/lib/postgresql/plperl.so: 
 /opt/PostgreSQL/8.4/lib/postgresql/plperl.so: undefined symbol: 
 Perl_Tcurpad_ptr

You've apparently got a version of libperl.so that is not compatible
with the one that your Postgres was built against.  There are lots of
compile-time options for Perl that affect this, so it's not exactly
a surprising situation.  The easiest fix is to be sure you get your
postgres and perl packages from the same place.  Alternatively, if
you really want to use a specific version of perl, recompile Postgres
from source against that perl.

 I previously got a libperl.so not found error which I thought I alleviated 
 by installing dev-perl via Synaptic.

Adding yet other versions of perl into the mix is definitely not the
way to get out of this kind of trouble ;-)

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Create language PLPERL error

[Shakil Shaikh]

From: Tom Lane t...@sss.pgh.pa.us


You've apparently got a version of libperl.so that is not compatible
with the one that your Postgres was built against.  There are lots of
compile-time options for Perl that affect this, so it's not exactly
a surprising situation.  The easiest fix is to be sure you get your
postgres and perl packages from the same place.  Alternatively, if
you really want to use a specific version of perl, recompile Postgres
from source against that perl.


Thanks, I see. Some further questions then:

Since 8.4 isn't on Synaptic, would that mean that I'm limited to running 
8.3.7?


Is there any way of finding which version of libperl.so Postgres is 
expecting and so obtain that instead?


I previously got a libperl.so not found error which I thought I 
alleviated

by installing dev-perl via Synaptic.


Adding yet other versions of perl into the mix is definitely not the
way to get out of this kind of trouble ;-)


But no version of libperl.so was present so I had to get *something* (even 
though it was the wrong one!). Unless you mean I can do CREATE LANGUAGE 
without libperl-dev (sorry, I got the name wrong in my OP!)?


Shak 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] easy task: concurrent select-updates

[Kevin McConnell]

 CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql
 AS $function$
 declare
       rec record;
 begin
       for rec in select id from msg where busy = false order by id loop
               update msg set busy = true where id = rec.id and busy = false;
               if found then
                       return rec.id;
               end if;
       end loop;
       return -1;
 end;
 $function$

I think you could also do something roughly similar in a statement by
using a RETURNING clause on the update, such as:

  update msg set busy = true where id = (select min(id) from msg where
busy = false) returning *;

Cheers,
Kevin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Create language PLPERL error

[Alvaro Herrera]

Shakil Shaikh wrote:
 Hi all,
 
 Running Postgres 8.4 on Ubuntu 9.04, installed via the clickonce
 installer.

Remove that, and install them from Martin Pitt's repository:
https://launchpad.net/~pitti/+archive/postgresql

The one-click installer does not integrate well with the platform.
Avoid using them.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PL/Perl 64-bit and sending emails

[Steve Atkins]

On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote:


Hello!

I've been running the 64-bit version of 8.3.4 on OpenSolaris 2009.06  
for over a year.  Now, I need to put a perl function call into it to  
allow emails to be sent by the database backend.  I tried installing  
plperl, but it looks like only a 32-bit version is available.  Does  
the 64-bit version of plperl exist?  Or, does someone know of  
another way to get the backend to send an email?


Have a queue table in the database you put your emails into and an  
external process that polls the table, sends the email and deletes the  
entry from the queue. Apart from avoiding the ickiness of doing high  
latency work from a database function this also makes sending email  
transaction safe - if the transaction rolls back after sending the  
email, the email doesn't get sent.


Using listen/notify based on a trigger on the table makes it a little  
more responsive.


This comes up fairly often. It's probably worth doing a tidy perl  
daemon to handle it and stashing it up on pgfoundry.


Cheers,
  Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] easy task: concurrent select-updates

[Andy Colson]

Kevin McConnell wrote:

CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql
AS $function$
declare
  rec record;
begin
  for rec in select id from msg where busy = false order by id loop
  update msg set busy = true where id = rec.id and busy = false;
  if found then
  return rec.id;
  end if;
  end loop;
  return -1;
end;
$function$


I think you could also do something roughly similar in a statement by
using a RETURNING clause on the update, such as:

  update msg set busy = true where id = (select min(id) from msg where
busy = false) returning *;

Cheers,
Kevin



I had thought of that, but you'd need to add one thing, in the update ' and 
busy = false ', cuz two people may get the same id from the select min(id).

update msg set busy = true where busy = false and id = (select min(id) from msg 
where busy = false) returning *;

but then you'd have to fire it over-and-over until you actually got a row 
updated.

Seemed easer to put the loop in function, then you can:

select id from getmsg();

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_ctl with unix domain socket?

[Josef Wolf]

On Tue, Sep 01, 2009 at 04:47:25PM +0200, Josef Wolf wrote:
 On Tue, Sep 01, 2009 at 06:34:21AM -0700, Adrian Klaver wrote:
  On Tuesday 01 September 2009 4:28:22 am Josef Wolf wrote:
 [ ... ]
   Any hints how to use pg_ctl to start/stop postgresql on a unix domain
   socket?
  1) In postgresql.conf make listen_addresses empty per instructions:
 I could get it running with putting listen_addresses='' in postgresql.conf
 and then running

BTW: it works fine without the listen_address='' setting in postgresql.conf

 pg_ctl -Ddb -o -h '' -k `pwd`/db -l postgreslog start

This works, but when I add the -w option, it waits all the 60 seconds. So
for some reason, pg_ctl does not notice that postgres is ready to accept
connections. This problem seems to happen only when starting. Stopping works
immediately.

Any ideas?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Getting insufficient data left in message on copy with binary

[Gordon Shannon]

Hello,

I'm running 8.4 on Linux/Centos.  I am doing a copy (select ) to
'/absolute/path/to/file.dat' with binary. That works fine.  But when I load
that file into a table...

  copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id,
content_type_id, provider_id,
is_duplicate, is_spam, language_code, profanity, tonality, sentiment,
created_time, updated_at)
  from '/absolute/path/to/file.dat' with binary;

  ERROR:  insufficient data left in message
  CONTEXT:  COPY mytable, line 1, column provider_id

The same data works fine without the with binary.  Also, the column it's
complaining about, provider_id, is a NOT NULL column, and the data is
definitely there -- i.e. not a NULL in data file.

I have searched for this message and mostly I see issues related to JDBC
drivers, so that doesn't appear relevant.  And they all talk about nul bytes
(0x00), but again, how can that be relevant when I'm in binary mode?  Seems
like it should understand null bytes here, if that's what this is about.

Anybody seen this?

Thanks,
Gordon

-- 
View this message in context: 
http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25282935.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] New server disk setup?

[Adam Rich]

Hello,
I'm building a new server on RHEL 5.3 and was wondering if there was an 
optimized build guide published somewhere with guidelines on disk 
partitioning, filesystems, etc?  For example, do you recommend putting 
the data on an ext2 partition mounted noatime, and the logs on ext3? Or 
should I just use XFS for the whole thing?


Are there any other brand-new server choices to consider now?

I did find some discussions in the mailing archives around filesystems, 
but there was mostly conflicting information.  And I realize that a lot 
of the drawbacks that were considerations in the past may have been 
resolved by now.


It would be great if one could view the developer-recommended 
configuration for a particular operating system on the postgresql.org 
website.  I found a few guides on the wiki, but they are either 3+ years 
old, or they don't focus on server-setup as a whole.


Thanks,
Adam






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] add a value to an ENUM type

[APseudoUtopia]

On Thu, Sep 3, 2009 at 12:52 PM, Edoardo Panfiliedoa...@aspix.it wrote:
 I have an enum type
 CREATE TYPE shapeName AS ENUM('rectangle','circle');

 now I need another value: 'square'

 the pg_type.oid of 'shapename is 16458

 It works, but Is it safe to use this?
 insert into pg_enum (enumtypid,enumlabel) VALUES('16458','square');

 thank you
 Edoardo


I have done this previously on several occasions to modify ENUM
values, so it will work. However, when I inquired about doing the
same, I was told be careful and be sure of what you're doing, so
I'll forward it on to you as well.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting insufficient data left in message on copy with binary

[Tom Lane]

Gordon Shannon gordo...@gmail.com writes:
 I'm running 8.4 on Linux/Centos.  I am doing a copy (select ) to
 '/absolute/path/to/file.dat' with binary. That works fine.  But when I load
 that file into a table...

   copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id,
 content_type_id, provider_id,
   is_duplicate, is_spam, language_code, profanity, tonality, sentiment,
 created_time, updated_at)
   from '/absolute/path/to/file.dat' with binary;

   ERROR:  insufficient data left in message
   CONTEXT:  COPY mytable, line 1, column provider_id

 Anybody seen this?

No.  Can you extract a self-contained test case?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] add a value to an ENUM type

[Edoardo Panfili]

On 03/09/09 22.13, APseudoUtopia wrote:

On Thu, Sep 3, 2009 at 12:52 PM, Edoardo Panfiliedoa...@aspix.it  wrote:

I have an enum type
CREATE TYPE shapeName AS ENUM('rectangle','circle');

now I need another value: 'square'

the pg_type.oid of 'shapename is 16458

It works, but Is it safe to use this?
insert into pg_enum (enumtypid,enumlabel) VALUES('16458','square');

thank you
Edoardo



I have done this previously on several occasions to modify ENUM
values, so it will work. However, when I inquired about doing the
same, I was told be careful and be sure of what you're doing, so
I'll forward it on to you as well.


thank you,
I forgot to say that for me sorting order is not important on this type.

Edoardo

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Add Large Object support to database programmatically

[acordner]

I have been working to create a VB6 program to automatically create a
PostgreSQL database, tables and triggers for an application I am updating. I
have everything working great, except one of my tables needs to store a
bitmap image. I am using the Large Object (lo) contrib module to do this.
Using pgAdmin III, I can run the Query Tool and load the lo.sql file from
the \share\contrib folder and execute it on my database and it succeeds.

What I need to be able to do is load this contrib module on the fly from VB6
after creating my database. Using the content of the lo.sql file, I created
the following code:


Private Function AddLargeObjectDataType(connConnection As ADODB.Connection)
As Boolean
Dim cmdCommand As New ADODB.Command

With cmdCommand
.ActiveConnection = conConnection
.CommandType = adCmdText
.CommandText = SET search_path = public; CREATE DOMAIN lo AS
pg_catalog.oid; _
 CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
AS _
 'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT
IMMUTABLE; _
 CREATE FUNCTION lo_manage() RETURNS
pg_catalog.trigger _
 AS '$libdir/lo' LANGUAGE C;
Call .Execute
End With
Set cmdCommand = Nothing
End Function 

However, when I execute this code, I get a SQL syntax error at or near
'$libdir/lo' and the contrib module is not loaded. So I tried using a hard
coded path 'C:\Program Files\PostgreSQL\8.1\lib\lo' instead of '$libdir/lo'
and it also fails. I tried using double backslashes, same result. Forward
slashes, same result.

Any ideas?
-- 
View this message in context: 
http://www.nabble.com/Add-Large-Object-support-to-database-programmatically-tp25283311p25283311.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] query speed question

[Christopher Condit]

I have two tables that are georeferenced (although in this case I'm not using 
PostGIS) that I need to join.
A ( lat | lon | depth | value)
|A| = 1,100,000

B ( lat | lon | attributes)
|B| = 14,000,000

A is a special case because the lat / lon values are all at half degree 
intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary.
I've written a function in B called getSpecialLat(latitude) and 
getSpecialLon(longitude)  to calculate the correct A latitude and built an 
index on both functions.

Here's the query that I'm trying, but it's rather slow:
SELECT B.* FROM B,
(SELECT lat, lon FROM A WHERE value  0 AND value  2 AND depth = 0) AS foo
WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) = foo.lon

Nested Loop  (cost=3569.88..32055.02 rows=1414 width=422)
  -  Index Scan using A_valueidx on A  (cost=0.00..555.26 rows=6 width=16)
Index Cond: ((value  0) AND (value  2))
Filter: (depth = 0)
  -  Bitmap Heap Scan on B  (cost=3569.88..5029.48 rows=424 width=422)
Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) AND 
(getSpecialLat((B.lat)::numeric) = A.lat))
-  BitmapAnd  (cost=3569.88..3569.88 rows=424 width=0)
  -  Bitmap Index Scan on Blonidx  (cost=0.00..1760.38 rows=84859 
width=0)
Index Cond: (getSpecialLon((B.lon)::numeric) = A.lon)
  -  Bitmap Index Scan on Blatidx  (cost=0.00..1766.81 rows=84859 
width=0)
Index Cond: (getSpeicalLat((B.latitude)::numeric) = A.lat)

Am I missing something in terms of speeding up this query?

Thanks,
-Chris

Re: [GENERAL] Add Large Object support to database programmatically

[Richard Huxton]

acordner wrote:
 .CommandText = SET search_path = public; CREATE DOMAIN lo AS
 pg_catalog.oid; _
  CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
 AS _
  'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT
 IMMUTABLE; _
  CREATE FUNCTION lo_manage() RETURNS
 pg_catalog.trigger _
  AS '$libdir/lo' LANGUAGE C;
 Call .Execute
 End With
 Set cmdCommand = Nothing
 End Function 
 
 However, when I execute this code, I get a SQL syntax error at or near
 '$libdir/lo' and the contrib module is not loaded. 

Turn statement logging on server-side and you'll see what's happening.

My VB isn't the greatest, but doesn't the _ join together multi-line
strings? In which case you don't need the .

However, it looks to me like the actual problem is that you're missing
some spaces/newlines and getting SQL like:
  RETURNS pg_catalog.triggerAS '$libdir/lo'

Turn up your server logging and you'll know for sure.
-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] query speed question

[Bill Moran]

Christopher Condit con...@sdsc.edu wrote:

 I have two tables that are georeferenced (although in this case I'm not using 
 PostGIS) that I need to join.
 A ( lat | lon | depth | value)
 |A| = 1,100,000
 
 B ( lat | lon | attributes)
 |B| = 14,000,000
 
 A is a special case because the lat / lon values are all at half degree 
 intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary.
 I've written a function in B called getSpecialLat(latitude) and 
 getSpecialLon(longitude)  to calculate the correct A latitude and built an 
 index on both functions.
 
 Here's the query that I'm trying, but it's rather slow:
 SELECT B.* FROM B,
 (SELECT lat, lon FROM A WHERE value  0 AND value  2 AND depth = 0) AS foo
 WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) = foo.lon
 
 Nested Loop  (cost=3569.88..32055.02 rows=1414 width=422)
   -  Index Scan using A_valueidx on A  (cost=0.00..555.26 rows=6 width=16)
 Index Cond: ((value  0) AND (value  2))
 Filter: (depth = 0)
   -  Bitmap Heap Scan on B  (cost=3569.88..5029.48 rows=424 width=422)
 Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) AND 
 (getSpecialLat((B.lat)::numeric) = A.lat))
 -  BitmapAnd  (cost=3569.88..3569.88 rows=424 width=0)
   -  Bitmap Index Scan on Blonidx  (cost=0.00..1760.38 
 rows=84859 width=0)
 Index Cond: (getSpecialLon((B.lon)::numeric) = A.lon)
   -  Bitmap Index Scan on Blatidx  (cost=0.00..1766.81 
 rows=84859 width=0)
 Index Cond: (getSpeicalLat((B.latitude)::numeric) = 
 A.lat)
 
 Am I missing something in terms of speeding up this query?

I'd be interested to see if the query rewritten as a JOIN would be faster.

-- 
Bill Moran
http://www.potentialtech.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What happens when syslog gets blocked?

[Alvaro Herrera]

decibel wrote:
 On Aug 6, 2009, at 2:00 PM, Bill Moran wrote:

 Well ... life better really depends on which failure scenario you're
 more comfortable with ... personally, I'd rather lose log messages
 than
 have the DB system go down.  Of course, if auditing is critical to
 your
 scenario, then your priorities are different ...
 
 Bingo. I'm thinking we should make mention of this in the docs...

I propose the following patch.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: doc/src/sgml/config.sgml
===
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.224
diff -c -p -r1.224 config.sgml
*** doc/src/sgml/config.sgml	24 Aug 2009 20:08:31 -	1.224
--- doc/src/sgml/config.sgml	3 Sep 2009 22:03:00 -
*** local0.*/var/log/postgresql
*** 2408,2413 
--- 2408,2426 
   is dynamic-linker failure messages).
   This parameter can only be set at server start.
 /para
+ 
+note
+ para
+   The logging collector is designed to never lose messages.  This means
+   that in case of extremely high load, server processes could be
+   blocked due to trying to send additional log messages when the
+   collector has fallen behind.  In contrast, applicationsyslog/
+   prefers to drop messages if it cannot write them, which means it's
+   less reliable in those cases but it will not block the rest of the
+   system.
+ /para
+/note
+ 
/listitem
   /varlistentry
  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Snow Leopard and plpython problem

[Jerry LeVan]

On Sep 3, 2009, at 11:01 AM, Jan Otto wrote:


hi jerry,


Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard.

I cannot get plpython to build...

I have installed MacPython 2.6.2 in /Library/Frameworks/
Python.framework


do you really need python 2.6.2? snow leopard has python 2.6.1 on
board and
there are no problems compiling/using postgresql with the python that
comes
with snow leopard.


The binary:

mbp:postgresql-8.4.0 postgres$ which python
/Library/Frameworks/Python.framework/Versions/Current/bin/python

mbp:postgresql-8.4.0 postgres$ file `which python`
/Library/Frameworks/Python.framework/Versions/Current/bin/python:
Mach-O universal binary with 2 architectures
/Library/Frameworks/Python.framework/Versions/Current/bin/python
(for architecture ppc): Mach-O executable ppc
/Library/Frameworks/Python.framework/Versions/Current/bin/python
(for architecture i386):Mach-O executable i386


you need the 64-bit version of python (x86_64) or try compile
postgresql 32-bit (i386)

asche:~ asche$ file `which python`
/usr/bin/python: Mach-O universal binary with 3 architectures
/usr/bin/python (for architecture x86_64):  Mach-O 64-bit executable
x86_64
/usr/bin/python (for architecture i386):Mach-O executable i386
/usr/bin/python (for architecture ppc7400): Mach-O executable ppc


./configure --bindir=/usr/local/bin --mandir=/usr/local/share/man  \
--enable-thread-safety \
--with-python \
--with-perl --with-tcl \
--with-libedit-preferred \
--with-openssl --with-bonjour

Configure completes ok.

When I do a make 2mylog.log


i have tried configuring an building with the same options as you and
it compiles without an error or warning
about python.

regards, jan otto




Jan, Thanks for convincing me that it could be done...

It appears to me that even though I simplified my PATH to a bare  
minimum that
the link process was picking up the framework in /Library/Frameworks/ 
Python

instead of /System/Library/Frameworks/Python.framework.

I renamed /Library/Frameworks/Python so it was no longer visible and  
the build succeeded.


Jerry



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Regex substring help

[Nick]

Im trying to get all the text before the 'br' tag.

SELECT SUBSTRING('onebrtwobrthree','(^.*)br.*$');

returns onebrtwo

How do I get it to return one?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Regex substring help

[David Fetter]

On Thu, Sep 03, 2009 at 03:22:12PM -0700, Nick wrote:
 Im trying to get all the text before the 'br' tag.
 
 SELECT SUBSTRING('onebrtwobrthree','(^.*)br.*$');
 
 returns onebrtwo
 
 How do I get it to return one?

You can either use a non-greedy regex like this:

SELECT substring('onebrtwobrthree','(^.*?)br.*$');

Note the '?' after the '*'.  That makes it non-greedy.

Another way to do this would be with string_to_array:

SELECT (string_to_array('onebrtwobrthree','br'))[1];

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] query speed question

[Christopher Condit]

  I have two tables that are georeferenced (although in this case I'm
 not using PostGIS) that I need to join.
  A ( lat | lon | depth | value)
  |A| = 1,100,000
 
  B ( lat | lon | attributes)
  |B| = 14,000,000
 
  A is a special case because the lat / lon values are all at half
 degree intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary.
  I've written a function in B called getSpecialLat(latitude) and
 getSpecialLon(longitude)  to calculate the correct A latitude and built
 an index on both functions.
 
  Here's the query that I'm trying, but it's rather slow:
  SELECT B.* FROM B,
  (SELECT lat, lon FROM A WHERE value  0 AND value  2 AND depth = 0)
 AS foo
  WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) =
 foo.lon
 
  Nested Loop  (cost=3569.88..32055.02 rows=1414 width=422)
-  Index Scan using A_valueidx on A  (cost=0.00..555.26 rows=6
 width=16)
  Index Cond: ((value  0) AND (value  2))
  Filter: (depth = 0)
-  Bitmap Heap Scan on B  (cost=3569.88..5029.48 rows=424
 width=422)
  Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) AND
 (getSpecialLat((B.lat)::numeric) = A.lat))
  -  BitmapAnd  (cost=3569.88..3569.88 rows=424 width=0)
-  Bitmap Index Scan on Blonidx  (cost=0.00..1760.38
 rows=84859 width=0)
  Index Cond: (getSpecialLon((B.lon)::numeric) =
 A.lon)
-  Bitmap Index Scan on Blatidx  (cost=0.00..1766.81
 rows=84859 width=0)
  Index Cond:
 (getSpeicalLat((B.latitude)::numeric) = A.lat)
 
  Am I missing something in terms of speeding up this query?
 
 I'd be interested to see if the query rewritten as a JOIN would be
 faster.

I can write it like this:
select b.*
from b join a on (getwoalatitude(b.latitude::numeric) = a.lat
and getwoalongitude(b.longitude::numeric) = a.lon)
where 
a.value  0 and a.value  2 and a.depth = 0

which results in this plan:
Nested Loop  (cost=1387.20..13152982.35 rows=1625767 width=422)
  -  Index Scan using a_depthidx on a_(cost=0.00..1464.07 rows=6897 width=16)
Index Cond: (depth = 0)
Filter: ((value  0::numeric) AND (value  2::numeric))
  -  Bitmap Heap Scan on b  (cost=1387.20..1686.37 rows=424 width=422)
Recheck Cond: ((getSpecialLon((b.lon)::numeric) = a.lon) AND 
(getSpecialLat((b.lat)::numeric) = a.lat))
-  BitmapAnd  (cost=1387.20..1387.20 rows=424 width=0)
  -  Bitmap Index Scan on Blonidx  (cost=0.00..672.15 rows=84859 
width=0)
Index Cond: (getSpecialLon((b.lon)::numeric) = a.lon)
  -  Bitmap Index Scan on Blatidx  (cost=0.00..672.36 rows=84859 
width=0)
Index Cond: (getSpecialLat((b.lat)::numeric) = a.lat)

However it's still taking ages to execute (over five minutes - I stopped it 
before it finished)

-Chris


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] column level, uid based authorization to update columns

[Stephen Frost]

* Gauthier, Dave (dave.gauth...@intel.com) wrote:
 In linux, given the linux based uid of the user, how might someone implement 
 column level update restrictions on a uid basis?  For example...

The first issue is getting the linux uid to equate to a PG role.  That
can be done using 'ident' authentication, though I would recommend only
allowing that on socket-based connections (not with tcp/ip connections).
You could also give 'joeuser' a username and password to connect to PG
with.

In PG, with 8.4, you can use column-level privileges to limit what a
given PG role can do.  See the GRANT syntax for 8.4.

Thanks,

Stephen


signature.asc
Description: Digital signature

[GENERAL] How do I store tables on a remote host?

[Sergey Samokhin]

Hello.

Is this possible to move a table from one machine to another and then
redirect queries involving this table to the remote host in a
transparent way?

As I have just read, tablespaces let us define where given tables
should be stored on the file system. What I'm looking for is like
tablespaces, but with the ability to use a remote host as location.

Although it of course will make any queries that need remote tables to
look at slower, I think it's a good idea to move, say, old fragments
of table containing log entries to remote machines.

An exotic idea to use both remote file systems and tablepaces has just
come to my mind, but I have a feel that there may be an analouge built
into PG.

Thanks.

-- 
Sergey Samokhin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_ctl with unix domain socket?

[Adrian Klaver]

On Thursday 03 September 2009 12:20:02 pm Josef Wolf wrote:
 On Tue, Sep 01, 2009 at 04:47:25PM +0200, Josef Wolf wrote:
  On Tue, Sep 01, 2009 at 06:34:21AM -0700, Adrian Klaver wrote:
   On Tuesday 01 September 2009 4:28:22 am Josef Wolf wrote:
 
  [ ... ]
 
Any hints how to use pg_ctl to start/stop postgresql on a unix domain
socket?
  
   1) In postgresql.conf make listen_addresses empty per instructions:
 
  I could get it running with putting listen_addresses='' in
  postgresql.conf and then running

 BTW: it works fine without the listen_address='' setting in postgresql.conf

  pg_ctl -Ddb -o -h '' -k `pwd`/db -l postgreslog start

 This works, but when I add the -w option, it waits all the 60 seconds. So
 for some reason, pg_ctl does not notice that postgres is ready to accept
 connections. This problem seems to happen only when starting. Stopping
 works immediately.

 Any ideas?

My playing around with this seems to indicate that the -k switch is causing the 
problem. Changing the default location of the socket seems to throw things off. 

-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] trick problem

[纪晓曦]

Consider the following relational schema about daily stock prices.
StockPrice (stockid; timeid; price)
We make the simplifying assumption that timeid is an integer that count the
number
of days from a particular date in the past (that is, the stock prices on the
x-th day can
be found by a simple selection condition of WHERE timeid = x).
Write the following queries in SQL.
*Print out the 15-day moving averages of each stock sorted by stockid and
timeid*
(ascending order).
If the stock prices for a particular stock are
timeid price
5 10
6 12
7 14
then its 2-day moving averages will be
timeid 2-day moving average
6 11.
7 13.
Note that we want strict 15-day moving average, meaning that
1. There should be no 15-day moving average for the first 14 days (as shown
in
the above example).
2.If there are missing data (e.g., the price for a stock at some date is
missing)
within the 15-day (sliding) window, the computation on this window should
be abandoned.

I don't know how to do it without plsql

Re: [GENERAL] How do I store tables on a remote host?

[Scott Marlowe]

On Thu, Sep 3, 2009 at 6:10 PM, Sergey Samokhinprikru...@gmail.com wrote:
 Hello.

 Is this possible to move a table from one machine to another and then
 redirect queries involving this table to the remote host in a
 transparent way?

 As I have just read, tablespaces let us define where given tables
 should be stored on the file system. What I'm looking for is like
 tablespaces, but with the ability to use a remote host as location.

The way to do it that makes me cringe, would be to remote mount (NFS,
CIFS) another file system from a remote machine and put the table
there.

The better way is probably to look up pl/proxy and use that.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting insufficient data left in message on copy with binary

[Gordon Shannon]

Tom Lane-2 wrote:
 
 Gordon Shannon gordo...@gmail.com writes:
 
   ERROR:  insufficient data left in message
   CONTEXT:  COPY mytable, line 1, column provider_id
 
 Anybody seen this?
 
 No.  Can you extract a self-contained test case?
 


Got it.  The problem was a combination of 2 mis-matched data types. 
Consider this test case:

begin---

drop table if exists bar;
drop table if exists foo;

create table foo (
system_id smallint,
credibility real not null
);

insert into foo ( system_id, credibility) values (1,1);

copy foo to '/tmp/repeat.dat' with binary;

create table bar (
system_id int,
credibility numeric(10,9) not null
);

copy bar from '/tmp/repeat.dat' with binary;

copy bar from '/var/lib/pgsql/backups/repeat.dat' with binary;
psql:repeat:19: ERROR:  insufficient data left in message
CONTEXT:  COPY bar, line 1, column system_id

--end-

It's interesting to note that I get this error only when there are 2 bad
fields.
If I fix only the numeric field, I get incorrect binary data format on the
int field.
If I fix only the smallint field, I get invalid length in external
numeric value on the real field.

So, my fault, and the fix is obvious.  But it does seem like a less than
ideal error message.

Also, maybe a sentence like this would be helpful on the COPY page: Be
careful that the data types match from 'copy to' to 'copy from'.  There is
no implicit conversion done in binary mode... or some such?

Cheers

-- 
View this message in context: 
http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25287583.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Got could not truncate directory pg_multixact/offsets: apparent wraparound

[Gordon Shannon]

Hello,  running 8.4 on Centos.  Been running production for 6 months.  Never
saw this message until tonight:

  LOG: could not truncate directory pg_multixact/offsets: apparent
wraparound

In case it helps...

Output of pg_controldata:
Latest checkpoint's NextMultiXactId:  145725622
Latest checkpoint's NextMultiOffset:  394849408

Contents of pg_multixact/offsets:  08B0
Contents of pg_multixact/members: 178B

In conf file:
vacuum_freeze_min_age = 10  # (1 billion)
autovacuum_freeze_max_age = 15  # (1.5 billion)

Oldest txn in cluster (pg_database.datfrozenxid) : 648

Should I be concerned? 

Thanks
Gordon


-- 
View this message in context: 
http://www.nabble.com/Got-could-not-truncate-directory-%22pg_multixact-offsets%22%3A-apparent-wraparound-tp25287801p25287801.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] New server disk setup?

[Scott Marlowe]

On Thu, Sep 3, 2009 at 2:08 PM, Adam Richada...@sbcglobal.net wrote:

 Hello,
 I'm building a new server on RHEL 5.3 and was wondering if there was an
 optimized build guide published somewhere with guidelines on disk
 partitioning, filesystems, etc?  For example, do you recommend putting the
 data on an ext2 partition mounted noatime, and the logs on ext3? Or should I
 just use XFS for the whole thing?

I generally create 2 or 3 RAID sets.  If two sets, the first set has 2
drive RAID-1 for OS and pg_xlog and one RAID-10 for as many drives as
I can stuff into a machine, minus one or two for spares.  If three
sets, then a RAID-1 for the OS, a RAID-1 for pg_xlog and the rest
(i.e. data/everythingbutpg_xlog in a RAID-10 with spare(s).  After
that I'd add drives to pg_xlog or or the data partition as needed
based on testing.

Suppose you have a near infinite number of drives to toss at a
problem, with a perfect computer with fantastic bandwidth.  Most
likely you'll keep adding drives to the RAID-10 data partition, until
it got so big it started outrunning pg_xlog ON RAID-1.  The next step
is to make pg_xlog faster, usually going to RAID-10. Assuming you can
write to as many drives as you want as fast as you want, this need
will arise somewhere fairly up into the numbers in the /data
partition.

Getting pg_xlog, which is mostly sequential, onto its own set of
drives is a great way to speed up most pg installs, even with only a
few drives.  As few as 8 drives, preferably with a spare, can be setup
in a three RAID set with 2, 2, and 4 drives in RAID-1, RAID-1 and
RAID-10 respectively can run pretty fast.  I usually setup OS on ext3,
pg_xlog on ext2, and xfs or RAID-3 on the bigger RAID-10 partition.

You can choose whether or not to use software RAID alone, SW RAID on a
battery backed RAID controller in JBOD mode, mixed software and
hardware for RAID-10 (OS does the striping, HW builds the mirrors) or
full hardware RAID controller.  You really need to test on your load
to know which is faster for you, but there's no obvious answer.  But
battery backed cache on your drives buys you a lot, whether or not
it's the hardware doing it all or the OS using it as a JBOD.
Definitely test both of those.

As you increase the size, note that Centos / RHEL 5 have an 8TB file
system limit.  And you can't boot from anything  2TB as a drive.

As for setting up the physical drives in the partition, it's important
to create physically, not just logically, separate RAID sets.

 Are there any other brand-new server choices to consider now?

I think BSD and RHEL/Centos and debian are the only real choices for servers.

 It would be great if one could view the developer-recommended configuration
 for a particular operating system on the postgresql.org website.  I found a
 few guides on the wiki, but they are either 3+ years old, or they don't
 focus on server-setup as a whole.

Problem it's always a moving target.  :)  So these discussions show up
again every few months, and they server a good purpose.  You might get
better / different answers from -perform or -admin on a second try.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] N + 1 replication

[Juan Backson]

Hi,

Does anyone know if there is any N + 1 replication for Postgres?

Could someone please point me to the right direction?

Thanks,
JB

Re: [GENERAL] easy task: concurrent select-updates

[Nickolay]

Kevin McConnell wrote:

CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql
AS $function$
declare
  rec record;
begin
  for rec in select id from msg where busy =alse order by id loop
  update msg set busy =rue where id = rec.id and busy = false;
  if found then
  return rec.id;
  end if;
  end loop;
  return -1;
end;
$function$



I think you could also do something roughly similar in a statement by
using a RETURNING clause on the update, such as:

  update msg set busy =rue where id = (select min(id) from msg where
busy =alse) returning *;

Cheers,
Kevin
  


Thank you guys! But what's min(id) for? Is it neccessary? Is there any 
chance I can replace min(id) to LIMIT 1?


Best regards, Nick.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Moving avg using SQL

[纪晓曦]

How can I do a moving avg by only using SQL?

[GENERAL] How to stop a query

[A B]

Hi.
How can I abort a query that I see is listed in

select * from pg_stat_activity;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] libpq performance

[Juan Backson]

Hi,

For this problem, what if I use prepared statement?  if I use prepared
statement directly from libpq, would it help?

Thanks,
JB

On Mon, Aug 24, 2009 at 9:59 AM, Craig Ringer
cr...@postnewspapers.com.auwrote:

 On Mon, 2009-08-24 at 00:08 +0800, Juan Backson wrote:

  I have a sql that only takes 0.3 ms to run when using psql with
  explain analyze.

 explain analyze reports server-side execution time.

  However, when I execute it using PQexec, it takes 12ms for PGexec.
  Does anyone know why it is that slow?

 ... and if you're timing PQexec you're probably taking the client-side
 time, ie the time from start of PQexec call to the time the PQexec call
 returns.

 That means that network latency *is* a factor, albeit a small one. I
 don't think EXPLAIN ANALYZE will report any delays due to lock
 acquisition or anything like that either. However, most of the
 difference probably comes from the time taken to parse and plan the
 statement. It'd help if you actually provided the EXPLAIN ANALYZE output
 and the statement in question so there was less guesswork involved.

  My db server is in the internal network, so there should not be any
  latency issue.

 Rather than assuming that, I'd recommend measuring it:

 - Run the test program on the DB server with a connection over the
 loopback interface (127.0.0.1); and
 - if the DB server is UNIX based, run the test program on the DB server
 with a connection over a UNIX socket; and
 - Use Wireshark to examine the actual network traffic to see how big a
 gap there is between request and response

 However, as I said above I personally expect the difference is mostly in
 parsing and planning time.

 There are ways to reduce planning time (at the cost of potentially
 inferior query plans) - but if you're really that worried about query
 execution time, might you perhaps be executing a huge number of tiny
 queries in a situation where one or two bigger queries can get the job
 done more quickly?

 --
 Craig Ringer

Re: [GENERAL] libpq performance

[Chris]

Juan Backson wrote:

Hi,

For this problem, what if I use prepared statement?  if I use prepared 
statement directly from libpq, would it help?


It will possibly change the way postgres plans the query, so you may get 
a different execution time.


http://www.postgresql.org/docs/current/static/sql-prepare.html

See the Notes section.

--
Postgresql  php tutorials
http://www.designmagick.com/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] custom datatype - rgb color

[gabriel munteanu]

Hi,
I have a products table, with id, name, price columns.
the products are shoes.
now, they have a color , and in the UI we have a color palette so the
user can search the shoes for colors.
example: the user searches for red - [255,0,0], so i must give him all
shoes close to red in color.
i add the color column , with a custom datatype named color - should i
use array? or does anybody has a better approach to it?
anyway, by computing the RGB color distance in this way [pseudocode]:
dist=SQRT(POW(R1-R2,2)+POW(G1-G2,2)+POW(B1-B2,2)) , i am can provide
him OK results.
so, the select sql would be:

select * from products where color_distance (color,[255,0,0]]  10;
i don't know how to create an efficient index and operator class for
such column so that the select would run as fast as possible.

the problem would be much simpler if i would have an integer
representation of colors, but i could find such thing.
has anybody encountered such a situation and can help me with
guidance, or pointing me to some online docs?

Thanks for help,

-- 
jgabios
http://bash.editia.info

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to stop a query

[Guillaume Lelarge]

Le vendredi 4 septembre 2009 à 07:37:20, A B a écrit :
 Hi.
 How can I abort a query that I see is listed in

 select * from pg_stat_activity;

You have to do:

  SELECT pg_cancel_backend(pid of the postgres process);


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to stop a query

[Pavel Stehule]

hello

2009/9/4 A B gentosa...@gmail.com:
 Hi.
 How can I abort a query that I see is listed in

 select * from pg_stat_activity;


look on pg_cancel_backend function

http://www.postgresql.org/docs/8.2/static/functions-admin.html

regards
Pavel Stehule

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general