[GENERAL] Text search configuration

[Pedro Stavrinides]

Hi All,

This is my first post to this mailing list, so apologies if I am sending my
message to the incorrect place...

My question: I have configured a very basic text search for our application,
but got stuck at the point where I need the search vector to filter out some
unwanted text (HTML tags and special characters etc.). I have been through
the docs and think I have a basic understanding...  it appears that I need
to write a parser? Or is there something simpler? I couldn't find an
examples, so any advise would be much appreciated before I start.

Thanks,
Peter

Re: [GENERAL] Text search configuration

[Oleg Bartunov]

On Tue, 2 Sep 2008, Pedro Stavrinides wrote:


Hi All,

This is my first post to this mailing list, so apologies if I am sending my
message to the incorrect place...

My question: I have configured a very basic text search for our application,
but got stuck at the point where I need the search vector to filter out some
unwanted text (HTML tags and special characters etc.). I have been through
the docs and think I have a basic understanding...  it appears that I need
to write a parser? Or is there something simpler? I couldn't find an
examples, so any advise would be much appreciated before I start.


you don't need special parser, just to *include* what you need into your
configuration. See example 
http://www.postgresql.org/docs/8.3/static/textsearch-configuration.html




Thanks,
Peter



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
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] immutable functions and enumerate type casts in indexes

[Edoardo Panfili]

Tom Lane ha scritto:

Edoardo Panfili [EMAIL PROTECTED] writes:

my enumerated type is (this is a subset)
CREATE TYPE hibridation AS ENUM('none','genus','specie');



function declaration
CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS 
'funzioniGDB.so' LANGUAGE C IMMUTABLE;



index creation (the type of ibrido is hibridation)
CREATE INDEX i_specie_nome_specie ON specie 
(esterna_nome(ibrido::text,proParte,genere,specie));



the result is
ERROR:  functions in index expression must be marked IMMUTABLE


Now, maybe for your purposes here it's okay to consider it immutable.
In that case what I'd suggest doing is redefining ename() to take the
enum directly.  You could invoke enum_out within the function if you
really need a text equivalent.

thank you! this is the right way for me.
Now it works.

But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
---
Datum esterna_nome2(PG_FUNCTION_ARGS){
int label;
label = enum_out(fcinfo);
sprintf(debug,false enum_out: \%s\ ,unrolled);
elog(LOG, debug);
---
but it works only because my enum parameter is the first (and using 
fcinfo is a little obscure).


I must build a FunctionCallInfo structure (I think) but how?

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] plpgsql returning resultset

[tfinneid]

Hi

I know the subject has been discussed before, but I dont find what any
information that helps me make it work, so please bear with me.

In pg 8.2 I want to write a function that gathers data from different
tables and joins it into a single resultset, similar to select * from
tableA, but the problem I keep having is that I cant get the return to
work. I have tried return next and it fails. I have also tried refcursor,
but am not sure if that is the best way, its a littlebit cumbersome in a
program.

Are those the only two options? and what did I do wrong in the return next

create function test2() returns setof record as
$$
declare
   val_list record;
begin

   select * into val_list from tableA;
   return next val_list;
   return:
end
$$ .


with the query:
select test2();

ERROR: set-valued function called in context that cannot accept a set
CONTEXT: line 9 at return next

regards thomas


-- 
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] immutable functions and enumerate type casts in indexes

[Martijn van Oosterhout]

On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
 But i have a little question about parameters of enum_out.
 Datum enum_out(PG_FUNCTION_ARGS);
 this is a part of my function
 ---
 Datum esterna_nome2(PG_FUNCTION_ARGS){
   int label;
   label = enum_out(fcinfo);
   sprintf(debug,false enum_out: \%s\ ,unrolled);
   elog(LOG, debug);
 ---
 but it works only because my enum parameter is the first (and using 
 fcinfo is a little obscure).


Look in the fmgr.h header for functions like DirectFunctionCall1 and
various other ways of calling functions.

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 Please line up in a tree and maintain the heap invariant while 
 boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [GENERAL] plpgsql returning resultset

[Raymond O'Donnell]

On 02/09/2008 11:12, [EMAIL PROTECTED] wrote:

 create function test2() returns setof record as
 $$
 declare
val_list record;
 begin
select * into val_list from tableA;
return next val_list;
return:
 end
 $$ .

Hi there,

You need to do it like this:

with val_list in
  select * from tableA do
loop
  return next val_list;
end loop;
return;

There's an example here:

http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
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] plpgsql returning resultset

[tfinneid]

 Hi there,

 You need to do it like this:

 with val_list in
   select * from tableA do
 loop
   return next val_list;
 end loop;
 return;

 There's an example here:

Does that work in 8.2, cause i get the same error message as I described
above

regards

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] MERGE: performance advices

[Ivan Sergio Borgonovo]

I need to merge 2 tables:

update d set c1=s.c1, c2=s.c2... from s where d.pk=s.pk;
insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
  where s.pk not in (select pk from d);

Any strategy to make it faster? Including modifying postgres.conf
temporary?

Considering I've no concurrency problems. The tables I'm dealing
with are read only for everything else other than the merge
process.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
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] plpgsql returning resultset

[Raymond O'Donnell]

On 02/09/2008 12:18, [EMAIL PROTECTED] wrote:

 Does that work in 8.2, cause i get the same error message as I described
 above

Yep, it does. I should have mentioned that you call your function
like this:

  select * from my_function()

- in other words, a SETOF-returning function takes the place of a table
in a SELECT statement.

Can you show us more of your code?

Ray.


--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
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

[GENERAL] question for upgrade pg 8.2.5 to pg 8.3.3

[ProAce]

I just want to transfer only one db in pg 8.2.5 to pg 8.3.3, others
are still remained on pg 8.2.5.

I call the only one db as db-foo, and the db owner as user-foo following.
The IP for pg 8.2.5 is 10.10.10.1 , IP for pg 8.3.3 is 10.10.10.2.

My operations are listed below:
1. install a new pg 8.3.3 on new machine.
2. create the user-foo on pg 8.3.3
3. create the db-foo and assign the user-foo to owner on pg 8.3.3
4. pg_dump -f db-foo.dump -U pgsql -E UTF-8 -h 10.10.10.1 db-foo
5. psql -f db-foo.dump -U pgsql -h 10.10.10.2 db-foo

Are there any problems about the procedure?

It seems ok and run successfully on my test environment, but still
worried that some problems on the new pg 8.3.3.

-- 
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] MERGE: performance advices

[Gregory Stark]

Ivan Sergio Borgonovo [EMAIL PROTECTED] writes:

 I need to merge 2 tables:

 update d set c1=s.c1, c2=s.c2... from s where d.pk=s.pk;
 insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
   where s.pk not in (select pk from d);

you could try making the not in an exists. In released versions of Postgres
sometimes one is better than the other. Raising work_mem might matter if it
lets you do a hash join for either the IN/EXISTS or the join.

There is another approach though whether it's faster depends on how many
indexes you have and other factors:

CREATE TABLE new_d AS
SELECT DISTINCT ON (pk) pk,c1,c
  FROM (select 1 as t, * from s
union all
select 2 as t, * from d
   )
 ORDER BY pk, t

This will pull in all the rows from both tables and sort them by pk with
records from s appearing before matching records from t and then keep only the
first value for each pk.

Then you'll have to build indexes, swap the tables, and fix any views or rules
which refer to the old table (they'll still refer to the old table, not the
new table even after renaming it to the old name).

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

-- 
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] MERGE: performance advices

[Richard Broersma]

On Tue, Sep 2, 2008 at 4:19 AM, Ivan Sergio Borgonovo
[EMAIL PROTECTED] wrote:


 insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
  where s.pk not in (select pk from d);

This insert statement might be faster:

INSERT INTO d (pk, c1, c2, ... )
SELECT pk, c1, c2, ...
  FROM s
LEFT JOIN d ON s.pk = d.pk
WHERE d.pk IS NULL;


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

Re: [GENERAL] RAISE NOTICE format in pgAdmin

[Tony Caduto]

Bill Todd wrote:
If I have a series of RAISE NOTICE 'xxx' statements in a plpgsql 
function and I call the function from pgAdmin the notice messages are 
concatenated on a single line on the Messages tab. Is there any way to 
get each message to appear on a separate line?


Is there a better way than using RAISE NOTICE to debug functions?

Bill


Bill,
Make sure you are on at least version 8.2 (8.3 is preferred) and use a 
plpgsql debugger.  Later versions of Pgadmin have one built in and a 
stand alone version is available from:
http://www.amsoftwaredesign.com/debugger_client_announce  (built with 
Delphi)


If you are using the win32 version there is a option at the end of the 
installer script to install the debugger part.  On 8.2 or 8.3 you will 
need to install it yourself.

Please see: http://pgfoundry.org/projects/edb-debugger/

Hope that helps.

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of Lightning Admin for PostgreSQL


--
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] MERGE: performance advices

[Steve Clark]

Richard Broersma wrote:

On Tue, Sep 2, 2008 at 4:19 AM, Ivan Sergio Borgonovo
[EMAIL PROTECTED] wrote:




insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
where s.pk not in (select pk from d);



This insert statement might be faster:

INSERT INTO d (pk, c1, c2, ... )
SELECT pk, c1, c2, ...
  FROM s
LEFT JOIN d ON s.pk = d.pk
WHERE d.pk IS NULL;



Hello Richard,

Is there a way to do something similar with the following? I am an SQL noob and 
the
following takes longer to run than is reasonable, on the order of hours.

insert into myevents select * from t_unit_event_log a where exists
   (select b.event_log_no from myevents b
where a.event_status = 1 and a.event_ref_log_no IS NOT NULL
   and a.event_ref_log_no = b.event_log_no and a.event_log_no not in
   (select event_log_no from myevents)
   )

Thanks,
Steve

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

[GENERAL] SQL equivalent to \dT

[Bram Kuijper]

Hi all,

if I want to get a list of types (ie., data types or enums), then I can 
use the '\dT' command from within the postgreSQL client.


However, I cannot seem to figure out what the SQL alternative is to the 
\dT command, so that I might get a list of types scriptable by SQL.


For example, if I create an ENUM myself:
CREATE TYPE bird AS ENUM('duck','goose');

a quick look through the various parts of the information schema did not 
reveal in which place this enum is stored. Is the information schema the 
correct place to look for this? Which SQL statement do I need to get a 
list of user-defined types?


thanks in advance,

Bram Kuijper

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

[GENERAL] pg_catalog forward compatibility

[Robert Gobeille]

Is there a better way to query a database definition than select from  
pg_catalog tables and views?  For example, when I put out a new  
software update, I need to verify that all the table, column,  
constraint, etc definitions are correct for the update.


Thanks,
Bob

--
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] MERGE: performance advices

[Richard Broersma]

On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark [EMAIL PROTECTED] wrote:

 Is there a way to do something similar with the following? I am an SQL noob
 and the
 following takes longer to run than is reasonable, on the order of hours.

 insert into myevents select * from t_unit_event_log a where exists
   (select b.event_log_no from myevents b
where a.event_status = 1 and a.event_ref_log_no IS NOT NULL
   and a.event_ref_log_no = b.event_log_no and a.event_log_no not
 in
   (select event_log_no from myevents)
   )


To start off with, this SQL statement can be refined a bit.  Many of
the sub-query WHERE clause constraints have nothing to do with the
Correlated sub-query.  The refinement would look like so:

INSERT INTO Myevents
 SELECT *
   FROM T_unit_event_log AS A
  WHERE A.event_status = 1
AND A.event_ref_log_no IS NOT NULL
AND A.event_log_no NOT IN ( SELECT event_log_no
  FROM Myevents)
AND EXISTS ( SELECT B.event_log_no
   FROM Myevents AS B
  WHERE A.event_ref_log_no = B.event_log_no );


The next step would be to rework the NOT IN sub-query into a LEFT JOIN
WHERE IS NULL;

INSERT INTO Myevents
 SELECT *
   FROM T_unit_event_log AS A
  LEFT JOIN Myevents AS C
 ON A.event_log_no = C.event_log_no
  WHERE A.event_status = 1
AND A.event_ref_log_no IS NOT NULL
AND C.event_log_no IS NULL
AND EXISTS ( SELECT B.event_log_no
   FROM Myevents AS B
  WHERE A.event_ref_log_no = B.event_log_no );

There is one possible alteration that may or many not improve
performance.  This would be to replace the EXISTS with a LEFT JOIN
WHERE IS NOT NULL;

INSERT INTO Myevents
 SELECT *
   FROM T_unit_event_log AS A
  LEFT JOIN Myevents AS C
 ON A.event_log_no = C.event_log_no
  LEFT JOIN Myevents AS B
 ON A.event_ref_log_no = B.event_log_no
  WHERE C.event_log_no IS NULL
AND B.event_log_no IS NOT NULL
AND A.event_status = 1
AND A.event_ref_log_no IS NOT NULL;


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

Re: [GENERAL] SQL equivalent to \dT

[Pavel Stehule]

Hello

2008/9/2 Bram Kuijper [EMAIL PROTECTED]:
 Hi all,

 if I want to get a list of types (ie., data types or enums), then I can use
 the '\dT' command from within the postgreSQL client.


run psql with -E parameter. You will see all SQL statements used for
metacommands.

[EMAIL PROTECTED] ~]$ psql -E postgres
psql (8.4devel)
Type help for help.

postgres=# \dT
* QUERY **
SELECT n.nspname as Schema,
  pg_catalog.format_type(t.oid, NULL) AS Name,
  pg_catalog.obj_description(t.oid, 'pg_type') as Description
FROM pg_catalog.pg_type t
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM
pg_catalog.pg_class c WHERE c.oid = t.typrelid))
  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid =
t.typelem AND el.typarray = t.oid)
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
**

  List of data types
   Schema   |Name |
Description
+-+---
 pg_catalog | abstime | absolute, limited-range
date and time (Unix system time)
 pg_catalog | aclitem | access control list
 pg_catalog | any   |
 pg_catalog | anyarray|
 pg_catalog | anyelement  |

regards
Pavel Stehule

 However, I cannot seem to figure out what the SQL alternative is to the \dT
 command, so that I might get a list of types scriptable by SQL.

 For example, if I create an ENUM myself:
 CREATE TYPE bird AS ENUM('duck','goose');

 a quick look through the various parts of the information schema did not
 reveal in which place this enum is stored. Is the information schema the
 correct place to look for this? Which SQL statement do I need to get a list
 of user-defined types?

 thanks in advance,

 Bram Kuijper

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

Re: [GENERAL] pg_catalog forward compatibility

[Pavel Stehule]

Hello

2008/9/2 Robert Gobeille [EMAIL PROTECTED]:
 Is there a better way to query a database definition than select from
 pg_catalog tables and views?  For example, when I put out a new software
 update, I need to verify that all the table, column, constraint, etc
 definitions are correct for the update.


information_schema

http://www.postgresql.org/docs/8.3/interactive/information-schema.html

Regards
Pavel Stehule

 Thanks,
 Bob

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

Re: [GENERAL] pg_catalog forward compatibility

[David Fetter]

On Tue, Sep 02, 2008 at 09:25:50AM -0600, Robert Gobeille wrote:
 Is there a better way to query a database definition than select
 from  pg_catalog tables and views?  For example, when I put out a
 new software update, I need to verify that all the table, column,
 constraint, etc definitions are correct for the update.

The catalogs don't have enough information for that.  Instead, keep
better control of your DDL by putting it under source code management
including any upgrade (or possibly downgrade) scripts in it.

If you're deploying an application, be sure you warn people that any
DDL they do that's not one of your scripts will break it.  Explicitly
disclaim any responsibility for such meddling. :)

Cheers,
David.
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

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] MERGE: performance advices

[Steve Clark]

Richard Broersma wrote:

On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark [EMAIL PROTECTED] wrote:



Is there a way to do something similar with the following? I am an SQL noob
and the
following takes longer to run than is reasonable, on the order of hours.

insert into myevents select * from t_unit_event_log a where exists
 (select b.event_log_no from myevents b
  where a.event_status = 1 and a.event_ref_log_no IS NOT NULL
 and a.event_ref_log_no = b.event_log_no and a.event_log_no not
in
 (select event_log_no from myevents)
 )




To start off with, this SQL statement can be refined a bit.  Many of
the sub-query WHERE clause constraints have nothing to do with the
Correlated sub-query.  The refinement would look like so:

INSERT INTO Myevents
 SELECT *
   FROM T_unit_event_log AS A
  WHERE A.event_status = 1
AND A.event_ref_log_no IS NOT NULL
AND A.event_log_no NOT IN ( SELECT event_log_no
  FROM Myevents)
AND EXISTS ( SELECT B.event_log_no
   FROM Myevents AS B
  WHERE A.event_ref_log_no = B.event_log_no );


The next step would be to rework the NOT IN sub-query into a LEFT JOIN
WHERE IS NULL;

INSERT INTO Myevents
 SELECT *
   FROM T_unit_event_log AS A
  LEFT JOIN Myevents AS C
 ON A.event_log_no = C.event_log_no
  WHERE A.event_status = 1
AND A.event_ref_log_no IS NOT NULL
AND C.event_log_no IS NULL
AND EXISTS ( SELECT B.event_log_no
   FROM Myevents AS B
  WHERE A.event_ref_log_no = B.event_log_no );

There is one possible alteration that may or many not improve
performance.  This would be to replace the EXISTS with a LEFT JOIN
WHERE IS NOT NULL;

INSERT INTO Myevents
 SELECT *
   FROM T_unit_event_log AS A
  LEFT JOIN Myevents AS C
 ON A.event_log_no = C.event_log_no
  LEFT JOIN Myevents AS B
 ON A.event_ref_log_no = B.event_log_no
  WHERE C.event_log_no IS NULL
AND B.event_log_no IS NOT NULL
AND A.event_status = 1
AND A.event_ref_log_no IS NOT NULL;




Hi Richard and thanks for the response. When I try the last two queries i get 
and error. I have listed
the results of explain on all three.

srm2=# explain
srm2-# INSERT INTO Myevents
srm2-#  SELECT *
srm2-#FROM T_unit_event_log AS A
srm2-#   WHERE A.event_status = 1
srm2-# AND A.event_ref_log_no IS NOT NULL
srm2-# AND A.event_log_no NOT IN ( SELECT event_log_no
srm2(#   FROM Myevents)
srm2-# AND EXISTS ( SELECT B.event_log_no
srm2(#FROM Myevents AS B
srm2(#   WHERE A.event_ref_log_no = B.event_log_no );
  QUERY PLAN
-

Index Scan using indx_tuel_usn_ec_es on t_unit_event_log a  
(cost=31711.73..3607445990.61 rows=51844 width=146)
  Index Cond: (event_status = 1)
  Filter: ((event_ref_log_no IS NOT NULL) AND (subplan) AND (NOT (subplan)))
  SubPlan
-  Materialize  (cost=31711.73..42857.85 rows=830612 width=4)
  -  Seq Scan on myevents  (cost=0.00..28041.12 rows=830612 width=4)
-  Index Scan using indx1myevents on myevents b  (cost=0.00..8.37 rows=1 
width=4)
  Index Cond: ($1 = event_log_no)
(8 rows)

srm2=#
srm2=#
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-#  SELECT *
srm2-#FROM T_unit_event_log AS A
srm2-#   LEFT JOIN Myevents AS C
srm2-#  ON A.event_log_no = C.event_log_no
srm2-#   WHERE A.event_status = 1
srm2-# AND A.event_ref_log_no IS NOT NULL
srm2-# AND C.event_log_no IS NULL
srm2-# AND EXISTS ( SELECT B.event_log_no
srm2(#FROM Myevents AS B
srm2(#   WHERE A.event_ref_log_no = B.event_log_no );
ERROR:  INSERT has more expressions than target columns
srm2=#
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-#  SELECT *
srm2-#FROM T_unit_event_log AS A
srm2-#   LEFT JOIN Myevents AS C
srm2-#  ON A.event_log_no = C.event_log_no
srm2-#   LEFT JOIN Myevents AS B
srm2-#  ON A.event_ref_log_no = B.event_log_no
srm2-#   WHERE C.event_log_no IS NULL
srm2-# AND B.event_log_no IS NOT NULL
srm2-# AND A.event_status = 1
srm2-# AND A.event_ref_log_no IS NOT NULL;
ERROR:  INSERT has more expressions than target columns

I really appreciate your help.

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] MERGE: performance advices

[Richard Broersma]

On Tue, Sep 2, 2008 at 9:47 AM, Steve Clark [EMAIL PROTECTED] wrote:
 srm2=# explain
 srm2-# INSERT INTO Myevents
 srm2-#  SELECT *

 ERROR:  INSERT has more expressions than target columns

 srm2=# explain
 srm2-# INSERT INTO Myevents
 srm2-#  SELECT *
 ERROR:  INSERT has more expressions than target columns

Oops, replace SELECT * with SELECT A.*.

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

Re: [GENERAL] MERGE: performance advices

[Steve Clark]

Richard Broersma wrote:

On Tue, Sep 2, 2008 at 9:47 AM, Steve Clark [EMAIL PROTECTED] wrote:


srm2=# explain
srm2-# INSERT INTO Myevents
srm2-#  SELECT *




ERROR:  INSERT has more expressions than target columns




srm2=# explain
srm2-# INSERT INTO Myevents
srm2-#  SELECT *
ERROR:  INSERT has more expressions than target columns



Oops, replace SELECT * with SELECT A.*.


Richard,

This is AWESOME! This now only takes seconds where before it was taking longer 
than
I wanted to wait, I had let it run all night before aborting it.

Thanks so much Richard,

Regards,
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] MERGE: performance advices

[Tom Lane]

Richard Broersma [EMAIL PROTECTED] writes:
 There is one possible alteration that may or many not improve
 performance.  This would be to replace the EXISTS with a LEFT JOIN
 WHERE IS NOT NULL;

That changes the behavior, doesn't it?  Or is event_log_no a unique
key for Myevents?  I think what you want is to make the EXISTS an IN
instead.

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] MERGE: performance advices

[Steve Clark]

Tom Lane wrote:

Richard Broersma [EMAIL PROTECTED] writes:


There is one possible alteration that may or many not improve
performance.  This would be to replace the EXISTS with a LEFT JOIN
WHERE IS NOT NULL;



That changes the behavior, doesn't it?  Or is event_log_no a unique
key for Myevents?  I think what you want is to make the EXISTS an IN
instead.

regards, tom lane



Yes event_log_no is a unique key for myevents.

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

[GENERAL] Replication setup

[Jason Long]

I have a custom inventory system built on JBoss AS, Seam, EJB3, JSF, and
Richfaces with a PostgreSQL back end that runs on Centos.

Being a single developer my time is short and I need to set up remote
replication and  fail over without delaying what I am currently working on.

I have  been reading about Slony and  more recently Londiste.

I need help setting this up in a reliable manner quickly.  I need advice
on what to use and scripts that I can run on my servers to set this up
automatically.

Please email me directly at (jason at supernovasoftware dot com) if you
can recommend a company that can get this done for me quickly and
economically.

Thank you for your time,

Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.octgsoftware.com
HJBug Founder and President
http://www.hjbug.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] MERGE: performance advices

[Richard Broersma]

On Tue, Sep 2, 2008 at 10:58 AM, Tom Lane [EMAIL PROTECTED] wrote:
 Richard Broersma [EMAIL PROTECTED] writes:
 There is one possible alteration that may or many not improve
 performance.  This would be to replace the EXISTS with a LEFT JOIN
 WHERE IS NOT NULL;

 That changes the behavior, doesn't it?  Or is event_log_no a unique
 key for Myevents?  I think what you want is to make the EXISTS an IN
 instead.

Thanks for pointing that out Tom.  I hadn't consider how the EXISTS
clause would return potentially fewer rows if event_log_no wasn't
unique.


-- 
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] Foreign Key normalization question

[Matthew Wilson]

I'm building an app that has a customers table, a locations table, a
products table, and a product_locations table.

They make a diamond shape.

The locations table and the products table each have a customer_id
column that links back to the customers table.

Then the product_locations table table has just two columns: a
location_id column and a product_id column, each linking back to the
appropriate table.

I want to write a constraint or a trigger or something else that makes
sure that before a (location_id, product_id) tuple is inserted into the
product_locations table, the system verifies that the product links to
the same customer as the location.

How do I do this?

Thanks in advance.

Matt



-- 
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] Foreign Key normalization question

[Martin Gainty]

you can use setup a foreign key constraint in your create table so that column 
is only populated when
there is a value which syncs to the referenced value
http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html

Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 


 To: pgsql-general@postgresql.org
 From: [EMAIL PROTECTED]
 Subject: [GENERAL] Foreign Key normalization question
 Date: Tue, 2 Sep 2008 19:14:17 +
 
 I'm building an app that has a customers table, a locations table, a
 products table, and a product_locations table.
 
 They make a diamond shape.
 
 The locations table and the products table each have a customer_id
 column that links back to the customers table.
 
 Then the product_locations table table has just two columns: a
 location_id column and a product_id column, each linking back to the
 appropriate table.
 
 I want to write a constraint or a trigger or something else that makes
 sure that before a (location_id, product_id) tuple is inserted into the
 product_locations table, the system verifies that the product links to
 the same customer as the location.
 
 How do I do this?
 
 Thanks in advance.
 
 Matt
 
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

_
Get thousands of games on your PC, your mobile phone, and the web with Windows®.
http://clk.atdmt.com/MRT/go/108588800/direct/01/

Re: [GENERAL] Foreign Key normalization question

[Scott Marlowe]

On Tue, Sep 2, 2008 at 1:14 PM, Matthew Wilson [EMAIL PROTECTED] wrote:
 I'm building an app that has a customers table, a locations table, a
 products table, and a product_locations table.

 They make a diamond shape.

 The locations table and the products table each have a customer_id
 column that links back to the customers table.

 Then the product_locations table table has just two columns: a
 location_id column and a product_id column, each linking back to the
 appropriate table.

 I want to write a constraint or a trigger or something else that makes
 sure that before a (location_id, product_id) tuple is inserted into the
 product_locations table, the system verifies that the product links to
 the same customer as the location.

If the two subordinate tables ALWAYS have to point to the same place,
why two tables?  Can't a customer have  1 location?  I'm pretty sure
IBM has more than one corporate office you could ship things to.

-- 
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] Foreign Key normalization question

[Matthew Wilson]

On Tue 02 Sep 2008 04:06:20 PM EDT, Martin Gainty wrote:
 you can use setup a foreign key constraint in your create table so that col=
 umn is only populated when
 there is a value which syncs to the referenced value
 http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html

I don't think that will work.

When somebody inserts (99, 98) into product_location, I want to make
sure that product ID 99 has the same customer ID as location ID 98.

Matt


-- 
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] Foreign Key normalization question

[Matthew Wilson]

On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
 If the two subordinate tables ALWAYS have to point to the same place,
 why two tables?  Can't a customer have  1 location?  I'm pretty sure
 IBM has more than one corporate office you could ship things to.

Yeah, so the idea is one customer might have many locations and many
products.  And at each location, some subset of all their products is
available.

And I need to track many customers.  So, one customer sells fortified
wine (a product) at one location and fancy champagne at another
location.

Meanwhile, a different customer sells lottery tickets at a different
location (location number three) and sells handguns at a fourth
location.

So, I'd have tuples in product_location that look like this:

(ID of location #1 belonging to customer #1, ID for fortified wine),
(ID of location #2 belonging to customer #1, ID for fancy champagne),
(ID of location #3 belonging to customer #2, ID for lottery tickets),
(ID of location #3 belonging to customer #2, ID for handguns),

I want to guarantee that products and locations don't get mixed up
regarding customers.  In other words, since, customer #1 only sells wine
and champagne, I want to prevent somebody from putting into
product_location a tuple like this:

(ID of location #1, ID for handguns).

Here's all my tables:

create table customer (
id serial primary key,
name text
);

create table product (
id serial primary key,
name text,
customer_id int references customer (id)
);

create table location (
id serial primary key,
name text,
customer_id int references customer (id)
);

create table product_location (
product_id int references product (id),
location_id int references location (id),
);

I want to make sure that when somebody inserts a (product_id,
location_id) tuple into product_location, the product_id refers to a
product that has a customer_id that matches customer_id referred to by
the location_id's location.

Matt



-- 
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] Foreign Key normalization question

[Scott Marlowe]

On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote:
 On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
 If the two subordinate tables ALWAYS have to point to the same place,
 why two tables?  Can't a customer have  1 location?  I'm pretty sure
 IBM has more than one corporate office you could ship things to.

 Yeah, so the idea is one customer might have many locations and many
 products.  And at each location, some subset of all their products is
 available.

You could have the product_locations have a custid1 and custid2 fields
that reference the two parent tables, and then a check constraing on
product_locations that custid1=custid2

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

[GENERAL] How to create a case-insensitive unique constraint?

[Kynn Jones]

Hi!

If I try something like

ALTER TABLE foo
  ADD CONSTRAINT foo_unique_xy
  UNIQUE ( UPPER( x ), UPPER( y ) );

...I get a syntax error

ERROR:  syntax error at or near (
LINE 3:   UNIQUE ( UPPER( x ), UPPER( y ) );

Is there a way to do this?

TIA!

Kynn

Re: [GENERAL] plpgsql returning resultset

[Thomas Finneid]

Raymond O'Donnell wrote:


Can you show us more of your code?


I figured out how to make it work when using for instead of with.

Here is the code and the error message. I couldnt find anything in the 
documentation about with but I did find something about for which I 
managed to make work. In any case here is the code for the with code:


create or replace function get_profile() returns setof tableA as
$$
declare
val_listtableA%rowtype;
begin

with val_list in
  select * from tableA
do
loop
  return next val_list;
end loop;

return;
end;
$$ language 'plpgsql';


the error message is:

psql:functions.sql:116: ERROR:  syntax error at or near with  $1
LINE 1: with  $1  in select * from attribute_values_part_seq_1_ff_5 ...
^
QUERY:  with  $1  in select * from attribute_values_part_seq_1_ff_5 do 
loop return next  $1

CONTEXT:  SQL statement in PL/PgSQL function get_profile near line 10



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

[GENERAL] Conflict between MVCC and manual locking

[jose lawrence]

HI,

I want to get more information whether  MVCC  conflicts with manual locking ?

regards,

Jose Lawrence


  Unlimited freedom, unlimited storage. Get it now, on 
http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/

Re: [GENERAL] Foreign Key normalization question

[Matthew Wilson]

On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote:
 On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote:
 On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
 If the two subordinate tables ALWAYS have to point to the same place,
 why two tables?  Can't a customer have  1 location?  I'm pretty sure
 IBM has more than one corporate office you could ship things to.

 Yeah, so the idea is one customer might have many locations and many
 products.  And at each location, some subset of all their products is
 available.

 You could have the product_locations have a custid1 and custid2 fields
 that reference the two parent tables, and then a check constraing on
 product_locations that custid1=custid2

You inspired me to change my tables to this:

create table location (
id serial unique,
name text,
customer_id int references customer,
primary key (id, customer_id)
);

create table product (
id serial unique,
name text,
customer_id int references customer,
primary key (id, customer_id)
);

create table product_location (
product_id int references product (id),
product_customer_id int references customer (id),
location_id int references location (id),
location_customer_id int references customer (id) check product_customer_id 
= location_customer_id,
foreign key (product_id, product_customer_id) references product (id, 
customer_id),
foreign key (location_id, location_customer_id) references location (id, 
customer_id),
);

This seems to work based on my informal testing, but it seems really
byzantine.  I wish I didn't have to explicitly put the customer IDs in
the table.

Is there a better way?


-- 
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] Oracle and Postgresql

[Lew]

Richard Broersma wrote:

On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter [EMAIL PROTECTED] wrote:


7. Though I don't personally buy it, I have heard others complain
  loudly that there is no print-version of Postgres documentation.



This one should be taken off the list.  The postgresql online
reference manual is in print( volumes 1 - 3)
http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1


I would happily provide a print version to any company needing one, charging 
only a reasonable fee for my extensive knowledge about invocation of the 
PDF-reader Print action on one of their workstations.  (Binding extra.)


--
Lew

--
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] plpgsql returning resultset

[Thomas Finneid]

Hi again, I tried to take the with form of the function further to 
complete the actual method and met with another error message which I 
dont understand.


I have a number for tables (partitioned) from which I need to retrieve 
data. Another table keeps track of which tables I should read from.
The tables are named table_X, where X is 1-N. from that I want to 
retrieve some data from the selected tables and add it all into one 
resultset which I return to the client.


The code is as follows:


create function get_profile(se_arg int4, st_arg int4, tr_arg int4) 
returns setof table_part as

$$
declare
table_name  text;
val_listtable_part%rowtype;
num_listtable_part_num_list%rowtype;
begin

for num_list in select num
from table_part_num_list
where se=se_arg
loop
   table_name := 'table_part_'|| num_list.num;  

   select * into val_list
   from table_name
   where st=st_arg and tr=tr_arg;

   return next val_list;
end loop;

return;
end;
$$ language 'plpgsql';

the error message I get when I try to create the function is:


psql:functions.sql:159: ERROR:  syntax error at or near $1
LINE 1: select * from  $1  where st= $2  and tr= $3
   ^
QUERY:  select * from  $1  where st= $2  and tr= $3
CONTEXT:  SQL statement in PL/PgSQL function get_profile near line 15

Any ideas what I am doing wrong?

regards

thomas


--
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] plpgsql returning resultset

[Raymond O'Donnell]

On 02/09/2008 21:55, Thomas Finneid wrote:
 with val_list in
   select * from tableA
 do
 loop
   return next val_list;
 end loop;

Oops - my mistake - it should indeed be FOR, not WITH, hence your error
message.

One other thing in the above - you don't need the DO, it's just
FOR...LOOP...END LOOP.

Ray.



--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
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] Oracle and Postgresql

[Asko Oja]

On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan [EMAIL PROTECTED] wrote:

 Oracle handles connecting to multiple databases (even on multiple/remote
 computers) fairly seamlessly, PG does not (yet.)

Stuff we do with plProxy on PostgreSQL is in some respects more advanced
than anything Oracle has to offer :) We have hundreds of databases in quite
complex network of remote calls and replication.

regards
Asko


 Oracle's toolsets (for things like forms and reports) are much further
 developed than PG's too, though there are 3rd party tools that work with
 both.

 Back in the old mainframe days, people used to say, Nobody ever got fired
 for selecting IBM.  It appears that mindset is still true.
 --
 Mike Nolan

Re: [GENERAL] Foreign Key normalization question

[Roberts, Jon]

Sounds like you really want this:

create table customer (
id serial primary key,
name text
);

create table location (
id serial primary key,
name text,
customer_id int references customer (id) );

create table product (
id serial primary key,
name text,
location_id int references location (id) );


Jon

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-general-
 [EMAIL PROTECTED] On Behalf Of Matthew Wilson
 Sent: Tuesday, September 02, 2008 3:35 PM
 To: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Foreign Key normalization question
 
 On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
  If the two subordinate tables ALWAYS have to point to the same
place,
  why two tables?  Can't a customer have  1 location?  I'm pretty
sure
  IBM has more than one corporate office you could ship things to.
 
 Yeah, so the idea is one customer might have many locations and many
 products.  And at each location, some subset of all their products is
 available.
 
 And I need to track many customers.  So, one customer sells fortified
 wine (a product) at one location and fancy champagne at another
 location.
 
 Meanwhile, a different customer sells lottery tickets at a different
 location (location number three) and sells handguns at a fourth
 location.
 
 So, I'd have tuples in product_location that look like this:
 
 (ID of location #1 belonging to customer #1, ID for fortified wine),
 (ID of location #2 belonging to customer #1, ID for fancy champagne),
 (ID of location #3 belonging to customer #2, ID for lottery tickets),
 (ID of location #3 belonging to customer #2, ID for handguns),
 
 I want to guarantee that products and locations don't get mixed up
 regarding customers.  In other words, since, customer #1 only sells
wine
 and champagne, I want to prevent somebody from putting into
 product_location a tuple like this:
 
 (ID of location #1, ID for handguns).
 
 Here's all my tables:
 
 create table customer (
 id serial primary key,
 name text
 );
 
 create table product (
 id serial primary key,
 name text,
 customer_id int references customer (id)
 );
 
 create table location (
 id serial primary key,
 name text,
 customer_id int references customer (id)
 );
 
 create table product_location (
 product_id int references product (id),
 location_id int references location (id),
 );
 
 I want to make sure that when somebody inserts a (product_id,
 location_id) tuple into product_location, the product_id refers to a
 product that has a customer_id that matches customer_id referred to by
 the location_id's location.
 
 Matt
 
 
 
 --
 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

Re: [GENERAL] plpgsql returning resultset

[Raymond O'Donnell]

On 02/09/2008 22:19, Thomas Finneid wrote:
 for num_list inselect num
 from table_part_num_list
 where se=se_arg
 loop
table_name := 'table_part_'|| num_list.num;   
 
select * into val_list
from table_name
where st=st_arg and tr=tr_arg;
 
return next val_list;

I think you need to build the query dynamically as a string, then
execute it using EXECUTE:

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

So something like this (I haven't tried it):

  loop
execute 'select * into val_list from '
  || 'table_part_' || num_list.num
  || ' where st = st_arg and tr = tr_arg';
return next val_list;
  end loop;

Hopefully this will work.

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
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] How to create a case-insensitive unique constraint?

[Tom Lane]

Kynn Jones [EMAIL PROTECTED] writes:
 ALTER TABLE foo
   ADD CONSTRAINT foo_unique_xy
   UNIQUE ( UPPER( x ), UPPER( y ) );

 ...I get a syntax error

This is disallowed by the SQL standard: UNIQUE constraints can only be
on plain columns.  (The practical reason for following their rule is
that there'd be no way to represent more-general constraints in the
information_schema.)

Use the CREATE UNIQUE INDEX syntax instead.

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] How to create a case-insensitive unique constraint?

[Dennis Brakhane]

You can use a unique index:

CREATE UNIQUE INDEX idx_foo_unique_upper_x_upper_y ON foo
(UPPER(x), UPPER(y));

-- 
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] plpgsql returning resultset

[Roberts, Jon]

PostgreSQL has table partitioning in it so you don't have to dynamically
figure out which table to get the data from.

http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html


However, you can achieve dynamic SQL in plpgsql too.  
http://www.postgresql.org/docs/8.3/interactive/ecpg-dynamic.html

Jon

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-general-
 [EMAIL PROTECTED] On Behalf Of Thomas Finneid
 Sent: Tuesday, September 02, 2008 4:19 PM
 To: pgsql-general@postgresql.org
 Cc: [EMAIL PROTECTED]
 Subject: Re: [GENERAL] plpgsql returning resultset
 
 
 Hi again, I tried to take the with form of the function further to
 complete the actual method and met with another error message which I
 dont understand.
 
 I have a number for tables (partitioned) from which I need to retrieve
 data. Another table keeps track of which tables I should read from.
 The tables are named table_X, where X is 1-N. from that I want to
 retrieve some data from the selected tables and add it all into one
 resultset which I return to the client.
 
 The code is as follows:
 
 
 create function get_profile(se_arg int4, st_arg int4, tr_arg int4)
 returns setof table_part as
 $$
 declare
   table_name  text;
   val_listtable_part%rowtype;
   num_listtable_part_num_list%rowtype;
 begin
 
   for num_list in select num
   from table_part_num_list
   where se=se_arg
   loop
  table_name := 'table_part_'|| num_list.num;
 
  select * into val_list
  from table_name
  where st=st_arg and tr=tr_arg;
 
  return next val_list;
   end loop;
 
   return;
 end;
 $$ language 'plpgsql';
 
 the error message I get when I try to create the function is:
 
 
 psql:functions.sql:159: ERROR:  syntax error at or near $1
 LINE 1: select * from  $1  where st= $2  and tr= $3
 ^
 QUERY:  select * from  $1  where st= $2  and tr= $3
 CONTEXT:  SQL statement in PL/PgSQL function get_profile near line
15
 
 Any ideas what I am doing wrong?
 
 regards
 
 thomas
 
 
 --
 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

Re: [GENERAL] Foreign Key normalization question

[D. Dante Lorenso]

Matthew Wilson wrote:

On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote:

On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote:

On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:

If the two subordinate tables ALWAYS have to point to the same place,
why two tables?  Can't a customer have  1 location?  I'm pretty sure
IBM has more than one corporate office you could ship things to.

Yeah, so the idea is one customer might have many locations and many
products.  And at each location, some subset of all their products is
available.

You could have the product_locations have a custid1 and custid2 fields
that reference the two parent tables, and then a check constraing on
product_locations that custid1=custid2


You inspired me to change my tables to this:

create table location (
id serial unique,
name text,
customer_id int references customer,
primary key (id, customer_id)
);

create table product (
id serial unique,
name text,
customer_id int references customer,
primary key (id, customer_id)
);

create table product_location (
product_id int references product (id),
product_customer_id int references customer (id),
location_id int references location (id),
location_customer_id int references customer (id) check product_customer_id 
= location_customer_id,
foreign key (product_id, product_customer_id) references product (id, 
customer_id),
foreign key (location_id, location_customer_id) references location (id, 
customer_id),
);

This seems to work based on my informal testing, but it seems really
byzantine.  I wish I didn't have to explicitly put the customer IDs in
the table.

Is there a better way?


You could add a trigger to your product_location table that just 
double-checked the customers matched or prevents the insert/update.  A 
PL/PGSQL function like this might help:


-- 8  8 --

DECLARE
  is_ok BOOLEAN;
BEGIN
  SELECT p.customer_id = l.customer_id
  INTO is_ok
  FROM product p, location l
  WHERE p.product_id = NEW.product_id
  AND l.location_id = NEW.location_id;

  -- didnt find the product and location ... weird
  IF NOT FOUND THEN
  RETURN NULL;
  END;

  -- product customer matches the location customer
  IF is_ok = TRUE THEN
  RETURN NEW;
  END;

  -- product and location customers did NOT match, reject changes
  RETURN NULL;
END;
-- 8  8 --

Disclaimer: I have no idea if that code works.  I just whipped it up now 
without testing it.  That might do your checks without having to add 
columns to tables you don't want to add.


Good luck.

-- Dante

--
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] plpgsql returning resultset

[Alex Vinogradovs]

I believe you need to use for execute '...' loop, since
the table_name is dynamically composed.


Regards,
Alex Vinogradovs


On Tue, 2008-09-02 at 23:19 +0200, Thomas Finneid wrote:
 Hi again, I tried to take the with form of the function further to 
 complete the actual method and met with another error message which I 
 dont understand.
 
 I have a number for tables (partitioned) from which I need to retrieve 
 data. Another table keeps track of which tables I should read from.
 The tables are named table_X, where X is 1-N. from that I want to 
 retrieve some data from the selected tables and add it all into one 
 resultset which I return to the client.
 
 The code is as follows:
 
 
 create function get_profile(se_arg int4, st_arg int4, tr_arg int4) 
 returns setof table_part as
 $$
 declare
   table_name  text;
   val_listtable_part%rowtype;
   num_listtable_part_num_list%rowtype;
 begin
 
   for num_list in select num
   from table_part_num_list
   where se=se_arg
   loop
  table_name := 'table_part_'|| num_list.num;  
 
  select * into val_list
  from table_name
  where st=st_arg and tr=tr_arg;
 
  return next val_list;
   end loop;
 
   return;
 end;
 $$ language 'plpgsql';
 
 the error message I get when I try to create the function is:
 
 
 psql:functions.sql:159: ERROR:  syntax error at or near $1
 LINE 1: select * from  $1  where st= $2  and tr= $3
 ^
 QUERY:  select * from  $1  where st= $2  and tr= $3
 CONTEXT:  SQL statement in PL/PgSQL function get_profile near line 15
 
 Any ideas what I am doing wrong?
 
 regards
 
 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] Subqueries in Check() -- Still Intentionally Omitted?

[Richard Broersma]

I am curious if the motivation is still valid for intentionally
omitting check sub-queries. (what was the motivation to begin with?)

Since we can effectively work around this limitation by doing the same
thing with a function in a CHECK constraint, why would we want to
prevent anyone from using the standard syntax for achieving the same
effect?


As a side point, for consistency I think that the CREATE ASSERTION
feature should have the same comments as a check() sub-query, since it
is very similar in purpose.


http://www.postgresql.org/docs/8.3/interactive/unsupported-features-sql-standard.html
F671 | Enhanced integrity management | Sub queries in CHECK |
intentionally omitted

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

Re: [GENERAL] Subqueries in Check() -- Still Intentionally Omitted?

[Alvaro Herrera]

Richard Broersma escribió:
 I am curious if the motivation is still valid for intentionally
 omitting check sub-queries. (what was the motivation to begin with?)

The problem is that you have to rerun the query to verify that the CHECK
condition still holds, whenever the table that the CHECK clause is
checking changes.  This is rather problematic, because we'd need to make
the system aware of such reverse dependencies.

The usual workaround is only enough protection if you trust that the
table referenced in the CHECK query does not change.  If the query
references something other than a table (say a function), it gets even
more messy.

-- 
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] Subqueries in Check() -- Still Intentionally Omitted?

[Tom Lane]

Richard Broersma [EMAIL PROTECTED] writes:
 I am curious if the motivation is still valid for intentionally
 omitting check sub-queries. (what was the motivation to begin with?)

 Since we can effectively work around this limitation by doing the same
 thing with a function in a CHECK constraint, why would we want to
 prevent anyone from using the standard syntax for achieving the same
 effect?

Because if we supported the standard syntax, we'd also have to support
the standard semantics; which a function-in-CHECK does *not* give you.

The standard says that the constraint is guaranteed not to be violated,
which in the worst case means that any time you update the table(s)
referenced in the subquery, you have to retest the CHECK expression
at every row of the table having the constraint.  Consider for instance
CREATE TABLE t1 (x int CHECK (x  (SELECT sum(y) FROM t2)));
If we change some value of t2.y, do all values of t1.x still satisfy
their constraint?

In some cases, with enough intelligence you could optimize this into
something fast enough to be usable; but it's a research problem.
(The cases that I can see how to optimize are pretty much equivalent to
plain foreign key constraints, anyway.)

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

[GENERAL] Index non-usage problem in 8.2.9

[Joseph S]

I have a table, d2, that has a field sacode that is almost always null. 
 In fact the stanullfrac	in pg_statistic for this column is 1.  I have 
this index on my table:


 d2_sgcode_sacode_idx btree (sgcode, sacode) WHERE sacode IS NOT NULL 
AND sacode  0


The first version of my query wasn't using that index for some reason:

p10:owl=# explain select count(*) from d2 where  d2.sgcode = 156 AND 
d2.sacode IN(2,1);

 QUERY PLAN
-
 Aggregate  (cost=208074.99..208075.00 rows=1 width=0)
   -  Bitmap Heap Scan on d2  (cost=175091.29..208074.99 rows=1 width=0)
 Recheck Cond: (sgcode = 156)
 Filter: (sacode = ANY ('{2,1}'::integer[]))
 -  Bitmap Index Scan on d2_lower_username_sgcode_key 
(cost=0.00..175091.29 rows=9431 width=0)

   Index Cond: (sgcode = 156)
(6 rows)

Time: 0.531 ms

I accidentally stumbled upon the solution:

p10:owl=# explain select count(*) from d2 where  d2.sgcode = 156 AND 
d2.sacode IN(2,1) and d2.sacode  0;

 QUERY PLAN

 Aggregate  (cost=16.33..16.34 rows=1 width=0)
   -  Index Scan using d2_sgcode_sacode_idx on d2  (cost=0.00..16.33 
rows=1 width=0)

 Index Cond: ((sgcode = 156) AND (sacode  0))
 Filter: (sacode = ANY ('{2,1}'::integer[]))
(4 rows)

Time: 0.710 ms

It seems that postgres can't figure out that it can use the index on 
sacode unless I put d2.sacode  0 in my where clause.  It won't use 
the index if I use d2.sacode = 1, for example.


--
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] Subqueries in Check() -- Still Intentionally Omitted?

[Jeff Davis]

On Tue, 2008-09-02 at 15:30 -0700, Richard Broersma wrote:
 I am curious if the motivation is still valid for intentionally
 omitting check sub-queries. (what was the motivation to begin with?)
 
 Since we can effectively work around this limitation by doing the same
 thing with a function in a CHECK constraint, why would we want to

Wow, I assumed you needed an immutable function for that to work. Then I
tried it:

= create table foo(i int check (random()  0.5));

My question is not why don't we allow subqueries in CHECK, my question
is why do we allow stable/volatile functions?

As I understand it, CHECK is meant for simple declarative tuple
constraints. It's not designed for sophisticated inter-relation
constraints -- or even intra-relation constraints, for that matter.

Consider:

CREATE TABLE foo(
  ...
  CHECK ((SELECT COUNT(*) FROM foo)  10)
);

We'd need some big locks for that to actually be a true declaration.

All of this can be solved with triggered procedures, where you can
define the locks as needed.

Regards,
Jeff Davis


-- 
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] Subqueries in Check() -- Still Intentionally Omitted?

[Jeff Davis]

On Tue, 2008-09-02 at 18:47 -0400, Alvaro Herrera wrote:
 The problem is that you have to rerun the query to verify that the CHECK
 condition still holds, whenever the table that the CHECK clause is
 checking changes.  This is rather problematic, because we'd need to make
 the system aware of such reverse dependencies.

Even if you re-ran the query, how do you avoid the race condition?

For example:
CREATE TABLE foo(
 ...
 CHECK ((SELECT COUNT(*) FROM foo)  10)
);

If another transaction commits between the time you re-run the query and
the time you commit, the CHECK will be violated.

From an arbitrary subquery in a CHECK, it's hard to determine what kind
of locking semantics might be necessary for inserting transactions.

I think this is precisely what triggers are for. You define the error
condition and the locking semantics in one procedure.

Regards,
Jeff Davis


-- 
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] Index non-usage problem in 8.2.9

[Tom Lane]

Joseph S [EMAIL PROTECTED] writes:
 It seems that postgres can't figure out that it can use the index on 
 sacode unless I put d2.sacode  0 in my where clause.

Works for me ...

regression=# create table d2(sgcode int, sacode int);
CREATE TABLE
regression=# create index d2i on d2 (sgcode, sacode) WHERE sacode IS NOT NULL 
AND sacode  0;
CREATE INDEX
regression=# explain select count(*) from d2 where  d2.sgcode = 156 AND 
d2.sacode IN(2,1);
 QUERY PLAN 


 Aggregate  (cost=8.54..8.55 rows=1 width=0)
   -  Bitmap Heap Scan on d2  (cost=4.52..8.54 rows=1 width=0)
 Recheck Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
 -  Bitmap Index Scan on d2i  (cost=0.00..4.52 rows=1 width=0)
   Index Cond: ((sgcode = 156) AND (sacode = ANY 
('{2,1}'::integer[])))
(5 rows)

You sure the server is 8.2.9?  Awhile ago there were some bug fixes
around the handling of IS NULL/IS NOT NULL in predicates.

One thought is that the IS NOT NULL is really redundant, since it's
implied by the sacode  0 test anyway.  Does it work better if you
make the index just WHERE sacode  0 ?

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] Subqueries in Check() -- Still Intentionally Omitted?

[Tom Lane]

Jeff Davis [EMAIL PROTECTED] writes:
 My question is not why don't we allow subqueries in CHECK, my question
 is why do we allow stable/volatile functions?

Historically we've allowed it, and it's not clear what we'd buy by
changing that, other than breaking existing applications whose authors
forgot to mark their functions immutable.  If there were something we
could usefully do by checking the mutability status of the condition,
then it would be worth breaking compatibility here...

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] Subqueries in Check() -- Still Intentionally Omitted?

[Jeff Davis]

On Tue, 2008-09-02 at 18:57 -0400, Tom Lane wrote:
 The standard says that the constraint is guaranteed not to be violated,
 which in the worst case means that any time you update the table(s)
 referenced in the subquery, you have to retest the CHECK expression
 at every row of the table having the constraint.  Consider for instance
   CREATE TABLE t1 (x int CHECK (x  (SELECT sum(y) FROM t2)));
 If we change some value of t2.y, do all values of t1.x still satisfy
 their constraint?
 

And as I pointed out to Alvaro, I believe there is a race there as well.

[ say t1 and t2 start empty ]

s1= insert into t2 values(5); -- checks condition, ok
s1= BEGIN;
s2= BEGIN;
s1= insert into t1 values(4);
s2= update t2 set y = 3;
s1= -- checks condition, sees sum(y)=5, ok
s2= -- checks condition, sees no tuples in t1, ok
s1= COMMIT;
s2= COMMIT; -- wrong!

The only solution is a big lock, or at least to somehow figure out what
kind of locks might be required.

Regards,
Jeff Davis


-- 
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] Subqueries in Check() -- Still Intentionally Omitted?

[Jeff Davis]

On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
 Jeff Davis [EMAIL PROTECTED] writes:
  My question is not why don't we allow subqueries in CHECK, my question
  is why do we allow stable/volatile functions?
 
 Historically we've allowed it, and it's not clear what we'd buy by
 changing that, other than breaking existing applications whose authors
 forgot to mark their functions immutable.  If there were something we
 could usefully do by checking the mutability status of the condition,
 then it would be worth breaking compatibility here...
 

I suppose this means that we're already treating any CHECK constraint as
immutable anyway, e.g. for constraint_exclusion?

Regards,
Jeff Davis




-- 
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] Foreign Key normalization question

[Matthew Wilson]

On Tue 02 Sep 2008 05:35:25 PM EDT, D. Dante Lorenso wrote:
 You could add a trigger to your product_location table that just 
 double-checked the customers matched or prevents the insert/update.  A 
 PL/PGSQL function like this might help:

 -- 8  8 --

 DECLARE
is_ok BOOLEAN;
 BEGIN
SELECT p.customer_id = l.customer_id
INTO is_ok
FROM product p, location l
WHERE p.product_id = NEW.product_id
AND l.location_id = NEW.location_id;

-- didnt find the product and location ... weird
IF NOT FOUND THEN
RETURN NULL;
END;

-- product customer matches the location customer
IF is_ok = TRUE THEN
RETURN NEW;
END;

-- product and location customers did NOT match, reject changes
RETURN NULL;
 END;
 -- 8  8 --

 Disclaimer: I have no idea if that code works.  I just whipped it up now 
 without testing it.  That might do your checks without having to add 
 columns to tables you don't want to add.

Thanks!  This is what I was looking for.  Although I got a few syntax
errors in postgreSQL 8.3 until I changed a few END; statements to END
IF;

Also, I had to put:

create or replace function check_customer ()
returns trigger $$

at the top of this, and 

$$ language 'plpgsql';

at the bottom.  I'm a novice at writing triggers, and this is really
useful.

Thanks again.

Matt




-- 
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] Subqueries in Check() -- Still Intentionally Omitted?

[Richard Broersma]

On Tue, Sep 2, 2008 at 3:47 PM, Alvaro Herrera
[EMAIL PROTECTED] wrote:

 The problem is that you have to rerun the query to verify that the CHECK
 condition still holds, whenever the table that the CHECK clause is
 checking changes.  This is rather problematic, because we'd need to make
 the system aware of such reverse dependencies.

Thanks for the clarification.  This makes sense.


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

Re: [GENERAL] Subqueries in Check() -- Still Intentionally Omitted?

[Tom Lane]

Jeff Davis [EMAIL PROTECTED] writes:
 On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
 Jeff Davis [EMAIL PROTECTED] writes:
 My question is not why don't we allow subqueries in CHECK, my question
 is why do we allow stable/volatile functions?
 
 Historically we've allowed it,

 I suppose this means that we're already treating any CHECK constraint as
 immutable anyway, e.g. for constraint_exclusion?

I think the constraint_exclusion code does check for immutability before
assuming it can deduce exclusion.  But the ordinary use of CHECK just
assumes it only needs to evaluate the check at tuple insertion.

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] Oracle and Postgresql

[Robert Treat]

On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
 On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan [EMAIL PROTECTED] wrote:
  Oracle handles connecting to multiple databases (even on multiple/remote
  computers) fairly seamlessly, PG does not (yet.)

 Stuff we do with plProxy on PostgreSQL is in some respects more advanced
 than anything Oracle has to offer :) We have hundreds of databases in quite
 complex network of remote calls and replication.


Yes, but it is also far more complex to install, configure, and use, compared 
to something simple like oracle's dblink, which comes pre-installed, is 
simple to set-up, and has a much more straight-forward syntax for use in day 
to day query work. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Oracle and Postgresql

[Ow Mun Heng]

On Tue, 2008-09-02 at 22:56 -0400, Robert Treat wrote:
 On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
  On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan [EMAIL PROTECTED] wrote:
   Oracle handles connecting to multiple databases (even on multiple/remote
   computers) fairly seamlessly, PG does not (yet.)
 
  Stuff we do with plProxy on PostgreSQL is in some respects more advanced
  than anything Oracle has to offer :) 

 oracle's dblink, which comes pre-installed, is 
 simple to set-up, and has a much more straight-forward syntax for use in day 
 to day query work. 
 

I've not used oracle's dblink before, but based on PG's dblink, one
thing which I didn't especially like is the need to pre-cast the data
types. Then again, how else will it know what data (type) is being
pulled in? 

For simple queries, knowing the data type means an additional lookup to
determine the data-type casting needed for the query.



-- 
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] Index non-usage problem in 8.2.9

[Joseph S]

Creating an index without the IS NOT NULL did not help.  The complete 
version:


 PostgreSQL 8.2.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)


Starting with a fresh database I got the same results you did, but not 
with my production table.


Tom Lane wrote:

Joseph S [EMAIL PROTECTED] writes:
It seems that postgres can't figure out that it can use the index on 
sacode unless I put d2.sacode  0 in my where clause.


Works for me ...

regression=# create table d2(sgcode int, sacode int);
CREATE TABLE
regression=# create index d2i on d2 (sgcode, sacode) WHERE sacode IS NOT NULL AND 
sacode  0;
CREATE INDEX
regression=# explain select count(*) from d2 where  d2.sgcode = 156 AND 
d2.sacode IN(2,1);
 QUERY PLAN 


 Aggregate  (cost=8.54..8.55 rows=1 width=0)
   -  Bitmap Heap Scan on d2  (cost=4.52..8.54 rows=1 width=0)
 Recheck Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
 -  Bitmap Index Scan on d2i  (cost=0.00..4.52 rows=1 width=0)
   Index Cond: ((sgcode = 156) AND (sacode = ANY 
('{2,1}'::integer[])))
(5 rows)

You sure the server is 8.2.9?  Awhile ago there were some bug fixes
around the handling of IS NULL/IS NOT NULL in predicates.

One thought is that the IS NOT NULL is really redundant, since it's
implied by the sacode  0 test anyway.  Does it work better if you
make the index just WHERE sacode  0 ?

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] Index non-usage problem in 8.2.9

[Joseph S]

Tom Lane wrote:


Can you force it to use the partial index by dropping the other index?
(Use begin; drop index ...; explain ...; rollback; to avoid dropping
the index for real.)  It's quite unclear at this point whether it


I tried, and it ends up using a seqscan.

--
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] Index non-usage problem in 8.2.9

[Tom Lane]

Joseph S [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 Can you force it to use the partial index by dropping the other index?
 (Use begin; drop index ...; explain ...; rollback; to avoid dropping
 the index for real.)  It's quite unclear at this point whether it

 I tried, and it ends up using a seqscan.

Just to be sure, what if you set enable_seqscan = off?

If still not, then there must be something about the table or index
declaration that you didn't tell us.  In the past, issues like use of
a domain instead of a bare datatype have been relevant ...

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] Index non-usage problem in 8.2.9

[Joseph S]

Actually sacode is an int2.  I didn't mention it before because I 
already tried explicit casts and that didn't do anything.  Now I just 
realized that in your testcase you use int instead of int2.  I just retried:


[local]:playpen=#  create table d2(sgcode int, sacode int2);
CREATE TABLE
Time: 13.748 ms
[local]:playpen=#  create index d2i on d2 (sgcode, sacode) WHERE sacode 
IS NOT NULL AND sacode  0;

CREATE INDEX
Time: 30.734 ms
[local]:playpen=#  explain select count(*) from d2 where  d2.sgcode = 
156 AND d2.sacode IN(2,1);

QUERY PLAN
--
 Aggregate  (cost=40.61..40.62 rows=1 width=0)
   -  Seq Scan on d2  (cost=0.00..40.60 rows=1 width=0)
 Filter: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
(3 rows)
[local]:playpen=#  explain select count(*) from d2 where  d2.sgcode = 
156 AND d2.sacode IN(2::int2,1::int2);

QUERY PLAN
---
 Aggregate  (cost=40.61..40.62 rows=1 width=0)
   -  Seq Scan on d2  (cost=0.00..40.60 rows=1 width=0)
 Filter: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::smallint[])))
(3 rows)

Time: 0.986 ms



Tom Lane wrote:

Joseph S [EMAIL PROTECTED] writes:

Tom Lane wrote:

Can you force it to use the partial index by dropping the other index?
(Use begin; drop index ...; explain ...; rollback; to avoid dropping
the index for real.)  It's quite unclear at this point whether it



I tried, and it ends up using a seqscan.


Just to be sure, what if you set enable_seqscan = off?

If still not, then there must be something about the table or index
declaration that you didn't tell us.  In the past, issues like use of
a domain instead of a bare datatype have been relevant ...

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] Index non-usage problem in 8.2.9

[Tom Lane]

Joseph S [EMAIL PROTECTED] writes:
 Starting with a fresh database I got the same results you did, but not 
 with my production table.

So, what's different between your table declaration and my toy example?

Can you force it to use the partial index by dropping the other index?
(Use begin; drop index ...; explain ...; rollback; to avoid dropping
the index for real.)  It's quite unclear at this point whether it
believes it *cannot* use the partial index, or whether it just thinks
the other index is cheaper.

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] Oracle and Postgresql

[Artacus]

I can't speak from the dba perspective, but I mainly write applications 
against Postgres and Oracle. I've used a dozen or more RDBMS's and 
Postgres and Oracle are by far the most similar of any two.


When the two differ, its about an even split for when I say I wish 
Oracle did it like Postgres and I wish Postgres did it like Oracle.


Others have mentioned some areas where Postgres is lacking that will 
soon be addressed such as CTE's. I'd really like to see Postgres 
implement something like MERGE INTO, and stored procedures instead of 
just functions.


On the other side of the coin, Oracle doesn't have a boolean type (in 
SQL) which is annoying; you don't have to select from 'dual'; 
generate_series() is quite useful; and custom aggregates are easier in 
postgres.


--
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] Index non-usage problem in 8.2.9

[Tom Lane]

Joseph S [EMAIL PROTECTED] writes:
 Actually sacode is an int2.

Ah.  8.2 is not very good at proving cross-type predicate conditions,
because it lacks the concept of an operator family.  You need to declare
the index this way:

create index d2i on d2 (sgcode, sacode)
  WHERE sacode IS NOT NULL AND sacode  0::int2;

(As previously noted, you don't really need the IS NOT NULL part of the
condition, but that isn't what's causing the problem here.)

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