[SQL] catching errors in function

[padmanabha konkodi]

  
hello sql developers,

i have written function in which while executing it may throw error.
if the error thrown i want rollback the transaction if not i want commit.

how can i achive this task.

how can catch exception thrown in the function

plz help me


regards
konkodi

Re: [SQL] catching errors in function

[Eugene E.]

i have written function in which while executing it may throw error.
if the error thrown i want rollback the transaction if not i want commit.



BEGIN;
SELECT you_function(args);
...
many sql commands;
...
COMMIT;

that's all

if your function raises an error
whole transaction will be aborted when the COMMIT is called.

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

[SQL] Noob question about types and lists

[xchris]

Hi,
i'm really new to postgres and have some doubts.
Hope somebody could explain.

Let say i have a table of "clients" and every client has a small number
of addresses. (let say 3)
My approch is to create a type "address" like this (simplified)

CREATE TYPE address AS
(
street  VARCHAR(160),
cityINTEGER,
cap INTEGER,
country INTEGER  (those are referring to other table)
);

and then composing table client

CREATE TABLE client
(
id  SERIAL PRIMARY KEY,
nameVARCHAR(80),
surname VARCHAR(80),
address address[3]

UNIQUE (name,surname)
);


This doesn't work.
I cannot create a list of types.

I'm using a wrong syntax? or simply i cannot do this?

Please forgive me if this is explained somewhere in the doc or in faqs,I
didn't find it.

Thank You

Christian

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

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

Re: [SQL] Noob question about types and lists

[Richard Huxton]

xchris wrote:

CREATE TYPE address AS
(
street  VARCHAR(160),
cityINTEGER,
cap INTEGER,
country INTEGER  (those are referring to other table)
);

and then composing table client

CREATE TABLE client
(
id  SERIAL PRIMARY KEY,
nameVARCHAR(80),
surname VARCHAR(80),
address address[3]

UNIQUE (name,surname)
);


This doesn't work.
I cannot create a list of types.

I'm using a wrong syntax? or simply i cannot do this?


From the manuals...

8.10. Arrays
PostgreSQL allows columns of a table to be defined as variable-length 
multidimensional arrays. Arrays of any built-in or user-defined base 
type can be created. (Arrays of composite types or domains are not yet 
supported, however.)


So - you can't do this at the moment.

However - I don't think you want to anyway. If you're going to use a 
relational database, try a relational approach! Typically, you would do 
something like:


CREATE TABLE client  (id ..., name ...)
CREATE TABLE address (id ..., street ...)
CREATE TABLE client_address (client_id ..., address_id ...)

Then, you can add some foreign-key constraints so PG makes sure that the 
ID numbers in client_address are all valid.


This makes it simple to get client names, client addresses or both together.

HTH
--
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] Noob question about types and lists

[xchris]

On gio, 2005-10-06 at 12:20 +0100, Richard Huxton wrote:

>  From the manuals...
> 

I didn't search very well.Sorry!

> 
> However - I don't think you want to anyway. If you're going to use a 
> relational database, try a relational approach! Typically, you would do 
> something like [cut]

I wanted to use a different approch because addresses don't make any
sense for other tables and because they are typically few (3,4) and i
wanted to avoid making a join for this task... 
however.. i will use your approch.

Thank you for patience and suggestion!
Regards

Chris


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

   http://archives.postgresql.org

Re: [SQL] Noob question about types and lists

[Richard Huxton]

xchris wrote:

On gio, 2005-10-06 at 12:20 +0100, Richard Huxton wrote:


From the manuals...


I didn't search very well.Sorry!


Easy enough to miss.

However - I don't think you want to anyway. If you're going to use a 
relational database, try a relational approach! Typically, you would do 
something like [cut]



I wanted to use a different approch because addresses don't make any
sense for other tables and because they are typically few (3,4) and i
wanted to avoid making a join for this task... 
however.. i will use your approch.


If you're using a relational DB, better get used to joins :-)
--
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] Scripting GRANT on functions

[Russell Simpkins]

You could continue with this function, with an 
additional cursor to get the parameters for the function. If this is a one off 
thing, that you just need to do once, you could use pg_dump to get the create 
function statements and then simply alter them with an re in your favorite 
editor. 
 
You should consider using groups. Then you could 
just add the new user to the existing group that has execute 
access.
 
hope that helps
 
Russ

  - Original Message - 
  From: 
  Stewart Ben (RBAU/EQS4) * 
  To: pgsql-sql@postgresql.org 
  Sent: Thursday, October 06, 2005 1:27 
  AM
  Subject: [SQL] Scripting GRANT on 
  functions
  Is there any easy way to script granting privileges to a number 
  offunctions? I've got as far as the following code before realising 
  thatI'll need to pass in the arguments, and the arguments are stored as 
  OIDsin pg_proc.Is there any easy way, such as GRANT  FUNCTION 
  OID 12345?---CODE---DECLARE  curs 
  REFCURSOR;  funcname VARCHAR;BEGIN  OPEN foo 
  FOR    SELECT proname FROM 
  pg_proc WHERE proname LIKE 
  'tr\\_%'    OR proname LIKE 
  'tt\\_%'    OR proname LIKE 
  'v\\_%'    OR proname LIKE 
  'vui\\_%';  FETCH curs INTO funcname;  WHILE FOUND 
  LOOP    FETCH curs INTO funcname;    
  EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO myuser';  
  END LOOP;  CLOSE curs;END;---END 
  CODE---Best regards,Ben Stewart--Robert Bosch 
  (Australia) Pty. Ltd.Engineering Quality Services, Software Engineer 
  (RBAU/EQS4)Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIAmailto:[EMAIL PROTECTED]http://www.bosch.com.au/---(end 
  of broadcast)---TIP 4: Have you searched our list 
  archives?   
  http://archives.postgresql.org

Re: [SQL] Scripting GRANT on functions

[Tom Lane]

"Stewart Ben (RBAU/EQS4) *" <[EMAIL PROTECTED]> writes:
> Is there any easy way to script granting privileges to a number of
> functions? I've got as far as the following code before realising that
> I'll need to pass in the arguments, and the arguments are stored as OIDs
> in pg_proc.

> Is there any easy way, such as GRANT  FUNCTION OID 12345?

Coerce the OID to regprocedure to generate a usable name for the
function.

regression=# select oid from pg_proc limit 5;
 oid
--
 1242
 1243
 1244
   31
 1245
(5 rows)

regression=# select oid::regprocedure from pg_proc limit 5;
   oid
--
 boolin(cstring)
 boolout(boolean)
 byteain(cstring)
 byteaout(bytea)
 charin(cstring)
(5 rows)

regression=#

regards, tom lane

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

   http://archives.postgresql.org

Re: [SQL] catching errors in function

[John DeSoi]

On Oct 6, 2005, at 2:55 AM, padmanabha konkodi wrote:


i have written function in which while executing it may throw error.
if the error thrown i want rollback the transaction if not i want  
commit.


how can i achive this task.

how can catch exception thrown in the function



The function itself cannot start or rollback a transaction (functions  
are always executed in the context of a transaction). But I think you  
can use a SAVEPOINT to accomplish your request:


http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html

And see this page on how to catch exceptions:

http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- 
structures.html#PLPGSQL-ERROR-TRAPPING



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

[SQL] UNION index use help

[Dmitri Bichko]

Ok, I'm thoroughly confused.

Simple query:

tb3=> explain analyze select bin, alias as symbol from alias_hs a join
bin_hs using (id,source) where upper(alias) like 'PPARG';
  QUERY PLAN

--
 Nested Loop  (cost=0.00..20.05 rows=1 width=19) (actual
time=0.114..0.118 rows=1 loops=1)
   ->  Index Scan using idx_alias_hs_alias on alias_hs a
(cost=0.00..9.02 rows=2 width=29) (actual time=0.073..0.074 rows=1
loops=1)
 Index Cond: (upper(alias) ~=~ 'PPARG'::text)
 Filter: (upper(alias) ~~ 'PPARG'::text)
   ->  Index Scan using idx_bin_hs_id_source on bin_hs  (cost=0.00..5.50
rows=1 width=28) (actual time=0.035..0.037 rows=1 loops=1)
 Index Cond: ((("outer".id)::text = (bin_hs.id)::text) AND
(("outer".source)::text = (bin_hs.source)::text))
 Total runtime: 0.167 ms
(7 rows)

A very similar query:

tb3=> explain analyze select bin,symbol from gene_hs g join bin_hs b on
(gene_id = id) where upper(symbol) like 'PPARG';
  QUERY PLAN

--
 Nested Loop  (cost=0.00..20.03 rows=2 width=18) (actual
time=0.068..0.073 rows=1 loops=1)
   ->  Index Scan using idx_gene_hs_symbol on gene_hs g
(cost=0.00..9.01 rows=2 width=19) (actual time=0.031..0.032 rows=1
loops=1)
 Index Cond: (upper((symbol)::text) ~=~ 'PPARG'::character
varying)
 Filter: (upper((symbol)::text) ~~ 'PPARG'::text)
   ->  Index Scan using idx_bin_hs_id_source on bin_hs b
(cost=0.00..5.50 rows=1 width=19) (actual time=0.030..0.032 rows=1
loops=1)
 Index Cond: (("outer".gene_id)::text = (b.id)::text)
 Total runtime: 0.119 ms
(7 rows)

Now I create a union over the two of them:

 create view test as 
 select bin, alias as symbol from alias_hs a join bin_hs using
(id,source)
 union all
 select bin,symbol from gene_hs g join bin_hs b on (gene_id = id)

tb3=> explain analyze select * from test where upper(symbol) like
'PPARG';
 
QUERY PLAN




 Subquery Scan test  (cost=0.00..13327.60 rows=253 width=40) (actual
time=479.139..1380.005 rows=2 loops=1)
   Filter: (upper(symbol) ~~ 'PPARG'::text)
   ->  Append  (cost=0.00..12570.37 rows=50482 width=19) (actual
time=0.055..1194.445 rows=80610 loops=1)
 ->  Subquery Scan "*SELECT* 1"  (cost=0.00..7051.67 rows=17627
width=19) (actual time=0.054..742.596 rows=47755 loops=1)
   ->  Merge Join  (cost=0.00..6875.40 rows=17627 width=19)
(actual time=0.052..677.190 rows=47755 loops=1)
 Merge Cond: (("outer".id)::text =
("inner".id)::text)
 Join Filter: (("outer".source)::text =
("inner".source)::text)
 ->  Index Scan using idx_alias_hs_id on alias_hs a
(cost=0.00..2501.30 rows=72214 width=29) (actual time=0.023..98.377
rows=72214 loops=1)
 ->  Index Scan using idx_bin_hs_id_source on bin_hs
(cost=0.00..7819.21 rows=172194 width=28) (actual time=0.015..221.023
rows=61520 loops=1)
 ->  Subquery Scan "*SELECT* 2"  (cost=827.69..5518.70
rows=32855 width=18) (actual time=77.123..382.122 rows=32855 loops=1)
   ->  Hash Join  (cost=827.69..5190.15 rows=32855 width=18)
(actual time=77.119..340.501 rows=32855 loops=1)
 Hash Cond: (("outer".id)::text =
("inner".gene_id)::text)
 ->  Seq Scan on bin_hs b  (cost=0.00..3172.94
rows=172194 width=19) (actual time=3.464..106.064 rows=86097 loops=1)
 ->  Hash  (cost=745.55..745.55 rows=32855 width=19)
(actual time=72.237..72.237 rows=0 loops=1)
   ->  Seq Scan on gene_hs g  (cost=0.00..745.55
rows=32855 width=19) (actual time=0.012..41.666 rows=32855 loops=1)
 Total runtime: 1381.068 ms
(16 rows)

I can't figure out what is going on here.

Just in case here are the table structures:

tb3=> \d bin_hs
   Table "core.bin_hs"
 Column  | Type  |   Modifiers
-+---+
 bin | bigint| not null
 source  | character varying(15) | not null
 id  | character varying(25) | not null
 current | boolean   | not null default false
Indexes:
"idx_bin_hs_bin" btree (bin)
"idx_bin_hs_id_source" btree (id, source)

tb3=> \d gene_hs
Table "core.gene_hs"
 Column  | Type  | Modifiers
-+---+---
 gene_id | character varying(25) | not null
 symbol  | character varying(50) | not null
 name| text  |
Indexes:
"gene_hs_pkey" PRIMARY 

Re: [SQL] UNION index use help

[Tom Lane]

"Dmitri Bichko" <[EMAIL PROTECTED]> writes:
> Ok, I'm thoroughly confused.

You didn't say which PG version you are using, but if it's something
reasonably recent then it should be able to push upper qual conditions
down into a UNION.  I think you are getting bit by this restriction:

 * 3. For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
 * push quals into each component query, but the quals can only reference
 * subquery columns that suffer no type coercions in the set operation.
 * Otherwise there are possible semantic gotchas.

You're getting burnt because you're unioning a text with a varchar.
Make the column types the same and it'll work better.

regards, tom lane

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

Re: [SQL] UNION index use help

[Dmitri Bichko]

That's exactly it - thanks, works perfectly now!

For the record, it's 8.0.3

Dmitri

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, October 06, 2005 12:32 PM
> To: Dmitri Bichko
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] UNION index use help 
> 
> 
> "Dmitri Bichko" <[EMAIL PROTECTED]> writes:
> > Ok, I'm thoroughly confused.
> 
> You didn't say which PG version you are using, but if it's 
> something reasonably recent then it should be able to push 
> upper qual conditions down into a UNION.  I think you are 
> getting bit by this restriction:
> 
>  * 3. For subqueries using UNION/UNION 
> ALL/INTERSECT/INTERSECT ALL, we can
>  * push quals into each component query, but the quals can 
> only reference
>  * subquery columns that suffer no type coercions in the set 
> operation.
>  * Otherwise there are possible semantic gotchas.
> 
> You're getting burnt because you're unioning a text with a 
> varchar. Make the column types the same and it'll work better.
> 
>   regards, tom lane
> 
The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. Any 
review, retransmission, dissemination or other use of, or taking of any action 
in reliance upon, this information by persons or entities other than the 
intended recipient is prohibited. If you received this in error, please contact 
the sender and delete the material from any computer

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

[SQL] DATESTYLE and 0000-00-00

[Joshua Kramer]

Greetings,

I have my DATESTYLE set to ISO MDY.

When I try to create a table with a default date of -00-00, psql says 
that this is an invalid date.  Why, and can (or how can I) get it to 
accept -00-00 as a valid date?


Thanks,
-Josh


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

Re: [SQL] DATESTYLE and 0000-00-00

[D'Arcy J.M. Cain]

On Thu, 6 Oct 2005 09:01:22 -0400 (EDT)
Joshua Kramer <[EMAIL PROTECTED]> wrote:
> I have my DATESTYLE set to ISO MDY.
> 
> When I try to create a table with a default date of -00-00, psql says 
> that this is an invalid date.  Why, and can (or how can I) get it to 
> accept -00-00 as a valid date?

It is invalid.  There was no year 0.  Perhaps what you want is NULL or,
possibly, EPOCH if you are looking for a sentinel value.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP)   |  what's for dinner.

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

Re: [SQL] DATESTYLE and 0000-00-00

[Andrew Sullivan]

On Thu, Oct 06, 2005 at 09:01:22AM -0400, Joshua Kramer wrote:
> I have my DATESTYLE set to ISO MDY.
> 
> When I try to create a table with a default date of -00-00, psql says 
> that this is an invalid date.  Why, and can (or how can I) get it to 
> accept -00-00 as a valid date?

You can't.  There's no year 0.  As the docs say, if you don't like
that, please complain to the Vatican; we can't help you.

If you are trying to say, "Date unknown," you can use NULL (and maybe
add another field to indicate whether the NULL means "no date" or
"date unknown").

Note that your datestyle has nothing to do with this: Postgres won't
let you put an invalid date into a date field.  This also has the
happy consequence that you can't accidentally create leap years where
there aren't any:

andrewtest=# SELECT '2005-02-29'::date;
ERROR:  date/time field value out of range: "2005-02-29"

If you really think you have to have such dates, you are probably
mistaken.  If you have to have them because some application you
can't control relies on them, you could store them in a text column,
and coerce them to dates when you select (but be prepared for the
errors you'll get).  But my suggestion is that if you really think
you have to have such dates, you should redesign (which might mean
"replace") your application.  Anything that uses dates with a year 0
is so fundamentally mistaken about how dates work that I wouldn't
trust it.

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
--Brad Holland

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

Re: [SQL] DATESTYLE and 0000-00-00

[Michael Fuhr]

On Thu, Oct 06, 2005 at 09:01:22AM -0400, Joshua Kramer wrote:
> I have my DATESTYLE set to ISO MDY.
> 
> When I try to create a table with a default date of -00-00, psql says 
> that this is an invalid date.  Why, and can (or how can I) get it to 
> accept -00-00 as a valid date?

Why do you want to, considering that -00-00 is *not* a valid
date?  Why not use NULL to represent an unknown value?

-- 
Michael Fuhr

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

   http://archives.postgresql.org

[SQL] Selecting records not present in related tables

[Hector Rosas]

Hello, I'm trying to select records in a table not present in a related table, in example, I've a table with message information (subject, message, date, etc) and another (usermessages) with where user(s) has that message, its state, etc. Records in this table will be deleted in a certain time (just some extra info).
I want to select messages records that aren't present in the other table (usermessages), I got the next two queries, maybe someone can suggest a better one.SELECT 
m.id FROM messages AS mWHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id
)=0;SELECT m.id FROM messages AS m where id NOT IN (select um.idmessage FROM usermessages um);Both queries work, but doing a 
EXPLAIN ANALYZE I got the next results.bd=# explain analyze SELECT m.id FROM messages AS m
bd-# WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id)=0;    QUERY PLAN    
---
 Seq Scan on messages m  (cost=0.00..3915.75 rows=3 width=4) (actual time=40.531..40.531 rows=0 loops=1)
   Filter: ((subplan) = 0)   SubPlan
 ->  Aggregate  (cost=9.11..9.11 rows=1 width=4) (actual time=0.098..0.104 rows=1 loops=355)
   ->  Index Scan using message_selection on usermessages um  (cost=0.00..9.10 rows=3 width=4) (actual time=0.067..0.078 rows=1 loops=355)
 Index Cond: (idmessage = $0) Total runtime: 40.605 ms
(7 rows) 
bd=# explain analyze select m.id FROM messages AS m where id NOT IN (select um.idmessage FROM usermessages um);
  QUERY PLAN--
 Seq Scan on messages m  (cost=9.68..43.00 rows=213 width=4) (actual time=20.329..20.329 rows=0 loops=1)
   Filter: (NOT (hashed subplan))   SubPlan
 ->  Seq Scan on usermessages um  (cost=0.00..8.54 rows=454 width=4) (actual time=0.008..13.094 rows=454 loops=1)
 Total runtime: 20.386 ms(5 rows)
In first query, cost can be between 0 and almost 4 sec, and also I see that loops value, that I don't know what performance issues could arise.In second query, I see a seq scan, which I don't like, I think that with too many records this query could take ages, or maybe not, but loops value is 1.
I hope someone can give some advice with those queries , or maybe a better query. I've not decided which query I'm going to use, thanks!Jeziel.

Re: [SQL] MOVE in SQL vs PLPGSQL

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> Richard Huxton  writes:
> > Ah, now I see what you're saying. You're quite right in your suspicions, 
> > "MOVE..." isn't supported for plpgsql cursors. You could probably do 
> > something with EXECUTE and returning a refcursor from a previous 
> > function, but that sounds fiddly.
> 
> > I must admit, on the odd occasion I want to skip a row, I just FETCH it 
> > and move on. Anyone else?
> 
> There is something on the TODO list about improving plpgsql's cursor
> functionality --- there's no reason it shouldn't have MOVE, except that
> no one got around to it yet.

Though the original poster should realize, a MOVE command would be only
marginally more efficient than just fetching those records. It would save the
network overhead and context switches involved in communicating those records,
but there's no way it would let the server avoid reading all those records
from disk.

At least as far as I can see.

-- 
greg


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

Re: [SQL] Selecting records not present in related tables

[Frank Bax]

At 03:43 PM 10/6/05, Hector Rosas wrote:

Hello, I'm trying to select records in a table not present in a related 
table, in example, I've a table with message information (subject, 
message, date, etc) and another (usermessages) with where user(s) has that 
message, its state, etc. Records in this table will be deleted in a 
certain time (just some extra info).
I want to select messages records that aren't present in the other table 
(usermessages), I got the next two queries, maybe someone can suggest a 
better one.


SELECT m.id FROM messages AS m
WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id )=0;

SELECT m.id FROM messages AS m where id NOT IN (select 
um.idmessage FROM usermessages um);



select m.id from messages as m left join usermessages as um on 
m.id=um.idmessage where um.idmessage is null;




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

Re: [SQL] Selecting records not present in related tables

[Scott Marlowe]

On Thu, 2005-10-06 at 14:43, Hector Rosas wrote:
> Hello, I'm trying to select records in a table not present in a
> related table, in example, I've a table with message information
> (subject, message, date, etc) and another (usermessages) with where
> user(s) has that message, its state, etc. Records in this table will
> be deleted in a certain time (just some extra info). 
> I want to select messages records that aren't present in the other
> table (usermessages), I got the next two queries, maybe someone can
> suggest a better one.

A fairly common way to do this is to use a left join and a not null:

select a.id  from tablea a left join tableb b on (a.id=b._aid) where
b._aid IS NULL

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

Re: [SQL] Selecting records not present in related tables

[Anthony Molinaro]

Jeziel,

 
there are a couple techniques you can try, two I
like are set difference and anti-joins.

 

here’s the set diff:

 

select id 

  from messages 

except 

select id 

  from usermessages

 

that will returns all id from messages not in usermessages

 

if ID is indexed on both tables, you may wanna try an anti
join:

 

select m.id

  from messages m


   left join 

   usermessages um

   
on ( m.id = um.id )

 where um.id is null

 

 

both techniques can be visciously efficient.

 

good luck,

 
Anthony  

  


 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of Hector Rosas
Sent: Thursday, October 06, 2005
3:44 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Selecting records
not present in related tables

 

Hello, I'm trying to select records in a table not
present in a related table, in example, I've a table with message information
(subject, message, date, etc) and another (usermessages) with where user(s) has
that message, its state, etc. Records in this table will be deleted in a
certain time (just some extra info). 
I want to select messages records that aren't present in the other table
(usermessages), I got the next two queries, maybe someone can suggest a better
one.

SELECT
m.id FROM messages AS m
WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id )=0;

SELECT m.id FROM messages AS m where id NOT IN (select
um.idmessage FROM usermessages um);

Both queries work, but doing a EXPLAIN ANALYZE I got the next results.

bd=# explain
analyze SELECT m.id FROM messages AS m 
bd-# WHERE (SELECT count(um.*) FROM usermessages AS um WHERE
um.idmessage=m.id)=0;
   
QUERY
PLAN   

---

 Seq Scan on messages m  (cost=0.00..3915.75 rows=3 width=4) (actual
time=40.531..40.531 rows=0 loops=1)
   Filter: ((subplan) = 0)
   SubPlan
 ->  Aggregate  (cost=9.11..9.11 rows=1
width=4) (actual time=0.098..0.104 rows=1 loops=355)
   ->  Index
Scan using message_selection on usermessages um  (cost=0.00..9.10 rows=3
width=4) (actual time=0.067..0.078 rows=1 loops=355)

Index Cond: (idmessage = $0)
 Total runtime: 40.605 ms
(7 rows)
 
bd=# explain analyze select m.id FROM messages AS m
where id NOT IN (select um.idmessage FROM usermessages um);
 
QUERY PLAN
--

 Seq Scan on messages m  (cost=9.68..43.00 rows=213 width=4) (actual
time=20.329..20.329 rows=0 loops=1)
   Filter: (NOT (hashed subplan))
   SubPlan
 ->  Seq Scan on usermessages um 
(cost=0.00..8.54 rows=454 width=4) (actual time=0.008..13.094 rows=454 loops=1)
 Total runtime: 20.386 ms
(5 rows)

In first query, cost can be between 0 and almost 4 sec, and also I see that
loops value, that I don't know what performance issues could arise.
In second query, I see a seq scan, which I don't like, I think that with too
many records this query could take ages, or maybe not, but loops value is 1. 

I hope someone can give some advice with those queries , or maybe a better
query. I've not decided which query I'm going to use, thanks!

Jeziel.

[SQL] RULES on SELECT with JDBC/perlDBI from other RDBMS products?

[Bath, David]

Folks,

I'm looking at using pg to be the main platform for integrating info
from other RDBMS products (particularly Oracle) as pg seems to be
the most flexible RDBMS around.

Disregarding writing to foreign products, query-plan efficiencies,
or differences of SQL dialect, I'd like to have a way of setting
up a fairly-transparent SELECT within pg that pulls rows from the
other product.  I wonder if anyone has attempted something like
this, and can recommend (or even deprecate) an approach, and perhaps
point to a code template.

Possible approaches that occur to me include
1. For the low-level integration 
   a) Use of "foreign" JDBC thin client within PL/Java
   b) Use of untrusted perl DBI/DBD
   c) Use of low-level C code (e.g. declaring Oracle OCI calls
  to pg) - very labor intensive
2. For "transparent" use by other routines
   Create pg table/view, then write rules that use functions
   returning rows (including barf exceptions if someone tries
   writing to a table).

If I can embed a java thin client binary/jar for the foreign
database in pg and use it using pg pl/java, then I'd like to
go that path as it would decrease setup/admin effort when
porting to other platforms, as there would be few dependencies
on things like external perl modules.

If any pg developer gurus are reading this, perhaps such templates
might be worthwhile including in the contrib bundle?
   
-- 
David T. Bath
[EMAIL PROTECTED]


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

[SQL] How to delete Large Object from Database?

[Premsun Choltanwanich]

Dear All,
 
I use '$libdir/lo' for manage my PostgreSQL Large Object. It work fine for me to get and put Large Object  from and to database. However I found something that may not correct when I try to backup my data. It seem that I cannot delete Large Object from database. It seem the thing I can do is only delete the reference oid from table but Object still in database.
 
A Detail shown below is  reason I think that I cannot delete Large Object out of database.
 
14MB of file size when BackUp Data (BLOB Included)

900KB of file size when BackUp Data (BLOB Excluded)

13MB of file size when BackUp Data after delete all data from table that has 'lo' column. (BLOB Included) 
 
So,How to delete Large Object from Database? or Is my understanding wrong?