[SQL] privileges
Hi all can anyone give me a link to a Reference manual which describes all privileges on any DB object and it's meaning :-) thnx. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Problem with Escape charactor
Dear Friends,
I am working with Postgres 7.3.4 on RH Linux 7.2 . I am
executing a dynamic query inside a PL/pgSQL procedure and I am having the
following problem.
While a line in the PL/pgSQL function is like the
following
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = 'Y' WHERE action_plan_id
IN ('|| p_action_plan_ids || ')';
I got the following error.
ERROR: parser: parse error at or
near "Y" at character 68
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| 'Y' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
ERROR: Attribute "y" not
found
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| \'Y\' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
WARNING: plpgsql: ERROR during
compile of sp_del_met_001 near line 47 ERROR:
unterminated string
How can I specify a string charactor, as the PgAdmin3 is not
using double quotes for Strings. Anyone pls shed some light.
Regards
Kumar
[SQL] Interest query plan
Hi all,
I am running pg 7.3.1.
My query is very simple but pg generates not the best possible plan for
me:
analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M
ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060';
The plan is:
---
Nested Loop (cost=1.26..111442.07 rows=6 width=2091) (actual
time=99512.48..101105.48 rows=1 loops=1)
Join Filter: ("outer".ids = "inner".ids_doc)
-> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61 rows=1
width=1344) (actual time=0.13..0.14 rows=1 loops=1)
Index Cond: (ids = 'SOF_700060'::name)
-> Materialize (cost=99981.52..99981.52 rows=916555 width=747)
(actual time=96980.73..99907.73 rows=916555 loops=1)
-> Hash Join (cost=1.26..99981.52 rows=916555 width=747)
(actual time=9.34..86400.88 rows=916555 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Seq Scan on a_sklad s (cost=0.00..83940.55
rows=916555 width=712) (actual time=0.17..45881.02 rows=916555 loops=1)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual
time=8.79..8.79 rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21
width=35) (actual time=8.68..8.75 rows=21 loops=1)
Total runtime: 101563.40 msec
(11 rows)
I think the best olution will be first to left join a_doc and a_sklad
and after it to join a_sklad and a_med.
Can I force pg to execute this query better?
If I do not use left join, the query is very fast:
explain analyze select * from a_doc D,A_SKLAD S,A_MED M where d.IDS=s.
IDS_DOC AND S.IDS_MED=M.IDS AND d.IDS='SOF_700160';
QUERY
PLAN
---
Hash Join (cost=1.26..80.55 rows=6 width=2091) (actual
time=20.41..20.46 rows=1 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Nested Loop (cost=0.00..79.18 rows=6 width=2056) (actual
time=19.23..19.26 rows=1 loops=1)
-> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61
rows=1 width=1344) (actual time=0.59..0.60 rows=1 loops=1)
Index Cond: (ids = 'SOF_700160'::name)
-> Index Scan using i_sklad_ids_doc on a_sklad s
(cost=0.00..75.31 rows=22 width=712) (actual time=18.25..18.26 rows=1
loops=1)
Index Cond: ("outer".ids = s.ids_doc)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.36..0.36
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35)
(actual time=0.22..0.30 rows=21 loops=1)
Total runtime: 21.27 msec
(10 rows)
But I think it is very big penalty for this left join.
regards,
ivan.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Interest query plan
Hi all, I am running pg 7.3.1. My query is very simple but pg generates not the best possible plan for me: analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060'; What about: select * from a_doc D left join A_SKLAD S on(d.IDS=s.IDS_DOC) left join A_MED M ON(S.IDS_MED=M.IDS) where d.IDS='SOF_700060' ? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Interest query plan
explain analyze select * from a_doc D left outer join A_SKLAD S
ON(D.IDS=S.IDS_DOC) left join A_MED M ON(S.IDS_MED=M.IDS) where
d.IDS='SOF_700060';
QUERY PLAN
-
Hash Join (cost=1.26..80.55 rows=6 width=2091) (actual time=1.09..1.11
rows=1 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Nested Loop (cost=0.00..79.18 rows=6 width=2056) (actual
time=0.40..0.41 rows=1 loops=1)
-> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61 rows=1
width=1344) (actual time=0.14..0.14 rows=1 loops=1)
Index Cond: (ids = 'SOF_700060'::name)
-> Index Scan using i_sklad_ids_doc on a_sklad s (cost=0.00..75.31
rows=22 width=712) (actual time=0.12..0.13 rows=1 loops=1)
Index Cond: ("outer".ids = s.ids_doc)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.19..0.19
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=0.07..0.15 rows=21 loops=1)
Total runtime: 1.82 msec
(10 rows)
I thinked that a_sklad join a_med ... will help, but
Tomasz Myrta wrote:
> > Hi all,
> > I am running pg 7.3.1.
> > My query is very simple but pg generates not the best possible plan for
> > me:
> > analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M
> > ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060';
> What about:
>
> select * from a_doc D
> left join A_SKLAD S on(d.IDS=s.IDS_DOC)
> left join A_MED M ON(S.IDS_MED=M.IDS)
> where d.IDS='SOF_700060'
>
> ?
>
> Regards,
> Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Interest query plan
I have also another good example for a slow left join work.
Can I do it better?
explain analyze select * from a_doc D join A_SKLAD S ON(D.IDS=S.IDS_DOC) join
A_MED M ON(S.IDS_MED=M.IDS) where d
.date_op >= 9600 and d.date_op <= 9700;
QUERY PLAN
--
Hash Join (cost=13174.61..112873.53 rows=67002 width=2091) (actual
time=1439.74..86339.93 rows=50797 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Hash Join (cost=13173.35..111699.74 rows=67002 width=2056) (actual
time=1428.01..78454.80 rows=50797 loops=1)
Hash Cond: ("outer".ids_doc = "inner".ids)
-> Seq Scan on a_sklad s (cost=0.00..83940.55 rows=916555
width=712) (actual time=20.25..61817.66 rows=916555 loops=1)
-> Hash (cost=13145.43..13145.43 rows=11167 width=1344) (actual
time=1399.99..1399.99 rows=0 loops=1)
-> Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167
width=1344) (actual time=0.22..1316.10 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=11.18..11.18
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=11.06..11.14 rows=21 loops=1)
Total runtime: 86409.11 msec
(11 rows)
sklad10=# explain analyze select * from a_doc D left outer join A_SKLAD S
ON(D.IDS=S.IDS_DOC) left outer join A_MED M ON(S.IDS_MED=M.IDS) where
d.date_op >= 9600 and d.date_op <= 9700;
QUERY PLAN
Hash Join (cost=772073.87..778722.53 rows=67002 width=2091) (actual
time=129557.36..142125.53 rows=50797 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Merge Join (cost=772072.61..777548.74 rows=67002 width=2056) (actual
time=129556.40..134598.44 rows=50797 loops=1)
Merge Cond: ("outer".ids = "inner".ids_doc)
-> Sort (cost=13896.25..13924.17 rows=11167 width=1344) (actual
time=1403.35..1409.90 rows=9432 loops=1)
Sort Key: d.ids
-> Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167
width=1344) (actual time=0.19..1343.11 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
-> Sort (cost=758176.36..760467.75 rows=916555 width=712) (actual
time=123981.87..127939.17 rows=896110 loops=1)
Sort Key: s.ids_doc
-> Seq Scan on a_sklad s (cost=0.00..83940.55 rows=916555
width=712) (actual time=16.54..66513.61 rows=916555 loops=1)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.32..0.32
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=0.20..0.28 rows=21 loops=1)
Total runtime: 142598.55 msec
(14 rows)
sklad10=# explain analyze select * from a_doc D where d.date_op >= 9600 and
d.date_op <= 9700;
QUERY PLAN
Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167 width=1344) (actual
time=0.19..1300.47 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
Total runtime: 1309.19 msec
(3 rows)
regards,
ivan.
Tomasz Myrta wrote:
> > Hi all,
> > I am running pg 7.3.1.
> > My query is very simple but pg generates not the best possible plan for
> > me:
> > analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M
> > ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060';
> What about:
>
> select * from a_doc D
> left join A_SKLAD S on(d.IDS=s.IDS_DOC)
> left join A_MED M ON(S.IDS_MED=M.IDS)
> where d.IDS='SOF_700060'
>
> ?
>
> Regards,
> Tomasz Myrta
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Interest query plan
I have also another good example for a slow left join work. Can I do it better? explain analyze select * from a_doc D join A_SKLAD S ON(D.IDS=S.IDS_DOC) join A_MED M ON(S.IDS_MED=M.IDS) where d .date_op >= 9600 and d.date_op <= 9700; -> Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167 width=1344) (actual time=0.22..1316.10 rows=9432 loops=1) I wouldn't expect too much from query, which starts joining over 10k rows and returns over 6 rows. Do you really need such a big result? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problem with Escape charactor
Kumar,
Have you tried
EXECUTE 'update "WATS".action_plan_master set
rec_deleted_flag = \'Y\' WHERE action_plan_id IN ('|| p_action_plan_ids ||
')';^
^
HTH,
George
- Original Message -
From:
Kumar
To: psql
Sent: Tuesday, October 07, 2003 7:57
AM
Subject: [SQL] Problem with Escape
charactor
Dear Friends,
I am working with Postgres 7.3.4 on RH Linux 7.2 . I am
executing a dynamic query inside a PL/pgSQL procedure and I am having the
following problem.
While a line in the PL/pgSQL function is like the
following
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = 'Y' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
I got the following error.
ERROR: parser: parse error at
or near "Y" at character 68
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| 'Y' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
ERROR: Attribute "y" not
found
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| \'Y\' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
WARNING: plpgsql: ERROR during
compile of sp_del_met_001 near line 47 ERROR:
unterminated string
How can I specify a string charactor, as the PgAdmin3 is not
using double quotes for Strings. Anyone pls shed some light.
Regards
Kumar
Re: [SQL] privileges
Sad, > can anyone give me a link to a Reference manual > which describes all privileges on any DB object and it's meaning :-) > thnx. http://www.postgresql.org/docs/7.3/static/sql-grant.html http://www.postgresql.org/docs/7.3/static/user-manag.html -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] row and column transposition
Anyone know some sql that display a row as a set of rows with column name vs column value? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] PG equivalent to Sybase varbinary
Anybody know what the Postgresql equivalent to a Sybase varbinary data type is? I have a package that provides ddl to store a 40 byte/char? varbinary column in a table and it is failing against postrgresql. Thanks for any help and if more info is need to answer, please let me know. Bill Pfeiffer ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Bad encoding in URL
Hi I have some URLs in a database but some of the URL contain %3A and so on.. Is there an easy way of converting '%3A' to ':' ??? I cannot find any replace(String str, String str) among the string functions. ThankYou. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] row and column transposition
On Mon, Oct 06, 2003 at 14:28:39 +0100, teknokrat <[EMAIL PROTECTED]> wrote: > Anyone know some sql that display a row as a set of rows with column > name vs column value? contrib/tablefunc provides a crosstab function that should do what you want. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Bad encoding in URL
Maybe you want to look here: http://www.brasileiro.net/postgres/cookbook/ mimic oracle's replace function. versions in pltcl and plpgsql. If the function you want isn't built in to posgresql... Ted --- HR <[EMAIL PROTECTED]> wrote: > Hi > > I have some URLs in a database but some of the URL > contain %3A and so on.. > > Is there an easy way of converting '%3A' to ':' ??? > > I cannot find any replace(String str, String str) > among the string > functions. > > ThankYou. > > > > ---(end of > broadcast)--- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Bad encoding in URL
>From the manual:
replace(string text, from text, to text) text Replace all occurrences
in string of substring from with substring to replace('abcdefabcdef', 'cd',
'XX') abXXefabXXef
HTH.
George
- Original Message -
From: "HR" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 06, 2003 5:45 AM
Subject: [SQL] Bad encoding in URL
> Hi
>
> I have some URLs in a database but some of the URL contain %3A and so on..
>
> Is there an easy way of converting '%3A' to ':' ???
>
> I cannot find any replace(String str, String str) among the string
> functions.
>
> ThankYou.
>
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
