[SQL] unsubscribe

[Patrice Oliver]

unsubscribe

--
Patrice Oliver
Utilisateur GNU / Linux Enregistré 430054 http://counter.li.org

Jabber ID : [EMAIL PROTECTED]


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

[SQL] dynamic sql

[Gabriel Adolfo Sirni]

Error using the command EXECUTE IMMEDIATE with an OUT
parameter

Example: I have this procedure pr_prueba, who call
other procedure "pr_out_area_code" using the command
execute immediate, who has an IN paramter and an OUT
paramter.

Procedure pr_prueba(param in varchar2) is
v_name varchar2(50);
BEGIN
EXECUTE IMMEDIATE ' Begin pr_out_area_code(:1,:2);
End; ' USING param, OUT v_name;
END;

When I execute the procedure pr_prueba 

begin
pr_prueba('3491');
end;

The result is the following

ERROR: EDB-42601: syntax error at or near "$1" at
character 14
QUERY: SELECT out $1
LINE 1: SELECT out $1

I had make another test without an OUT parameter and
it works fine, but when I include an OUT parameter it
dosen't work.

 



Gabriel Adolfo Sirni

__
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis! 
Regístrate ya - http://correo.espanol.yahoo.com/ 

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

Re: [SQL] huge disparities in =/IN/BETWEEN performance

[Tom Lane]

Joe <[EMAIL PROTECTED]> writes:
> George didn't provide the inside of his view, but it's
> possible that my earlier example could be rephrased as follows:
> create view v_foo as select * from tab where x < 5;
> select * from v_foo where x = 10;

So try it:

regression=# create table tab (x int);
CREATE TABLE
regression=# create view v_foo as select * from tab where x < 5;
CREATE VIEW
regression=# explain select * from v_foo where x = 10;
 QUERY PLAN

 Seq Scan on tab  (cost=0.00..46.00 rows=4 width=4)
   Filter: ((x < 5) AND (x = 10))
(2 rows)

regression=# set constraint_exclusion to 1;
SET
regression=# explain select * from v_foo where x = 10;
QUERY PLAN
--
 Result  (cost=0.00..0.01 rows=1 width=0)
   One-Time Filter: false
(2 rows)

(This is with HEAD, but I think 8.2 can do it too.)

regards, tom lane

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

Re: [SQL] dynamic sql

[Tom Lane]

Gabriel Adolfo Sirni <[EMAIL PROTECTED]> writes:
> Procedure pr_prueba(param in varchar2) is
> v_name varchar2(50);
> BEGIN
> EXECUTE IMMEDIATE ' Begin pr_out_area_code(:1,:2);
> End; ' USING param, OUT v_name;
> END;

You're asking the wrong people; this is not an Oracle mailing list.

regards, tom lane

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

   http://archives.postgresql.org

[SQL] alias not applied

[Sabin Coanda]

Hi all,

I have two queries:

1. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS d  ) x 
ORDER BY i
2. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS d 
UNION SELECT 51 AS i, true AS d  ) x ORDER BY i

The first returns the columns "PK_ID", "Deleted"
The second returns the columns i, d.

Why ?

TIA,
Sabin 



---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] alias not applied

[Frank Bax]

At 11:04 AM 2/9/07, Sabin Coanda wrote:

I have two queries:

1. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS d  ) x
ORDER BY i
2. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS d
UNION SELECT 51 AS i, true AS d  ) x ORDER BY i

The first returns the columns "PK_ID", "Deleted"
The second returns the columns i, d.

Why ?



perhaps you are running an old version - both queries produce the same 
column names on my 8.2.1 system.



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

Re: [SQL] alias not applied

[Tom Lane]

"Sabin Coanda" <[EMAIL PROTECTED]> writes:
> I have two queries:

> 1. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS d  ) x 
> ORDER BY i
> 2. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS d 
> UNION SELECT 51 AS i, true AS d  ) x ORDER BY i

> The first returns the columns "PK_ID", "Deleted"
> The second returns the columns i, d.

Works for me:

regression=# SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS 
d  ) x ORDER BY i;
 PK_ID | Deleted
---+-
52 | t
(1 row)

regression=# SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS 
d UNION SELECT 51 AS i, true AS d  ) x ORDER BY i;
 PK_ID | Deleted
---+-
49 | t
51 | t
(2 rows)

regression=#

What PG version are you using, exactly?

regards, tom lane

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

   http://archives.postgresql.org

Re: [SQL] alias not applied

[Sabin Coanda]

You are right. I use "PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC 
gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows.

But I just tried it on a newer version and it works well ( "PostgreSQL 8.1.4 
on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20051125 (Red Hat 
4.0.2-8)" ).

Thanks a lot,
Sabin


"Tom Lane" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> "Sabin Coanda" <[EMAIL PROTECTED]> writes:
>> I have two queries:
>
>> 1. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS 
>>   ) x
>> ORDER BY i
>> 2. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS d
>> UNION SELECT 51 AS i, true AS d  ) x ORDER BY i
>
>> The first returns the columns "PK_ID", "Deleted"
>> The second returns the columns i, d.
>
> Works for me:
>
> regression=# SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, 
> true AS d  ) x ORDER BY i;
> PK_ID | Deleted
> ---+-
>52 | t
> (1 row)
>
> regression=# SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, 
> true AS d UNION SELECT 51 AS i, true AS d  ) x ORDER BY i;
> PK_ID | Deleted
> ---+-
>49 | t
>51 | t
> (2 rows)
>
> regression=#
>
> What PG version are you using, exactly?
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>   http://archives.postgresql.org
> 



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

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

Re: [SQL] huge disparities in =/IN/BETWEEN performance

[George Pavlov]

Thanks all for the various useful thoughts. Let me backtrack a bit and
state my real underlying issue a bit with actual examples. Hope not to
bore you with the length of this. Looks to me like an optimizer issue
unless I am missing something. 

So, suppose I have a query:

select *
from stuff
inner join 
  ( -- just getting the distinct
-- user-stuff associations
-- since there may be multiple;
-- ultimately I need to query by user
   select stuff_id, user_id 
   from stuff_user
   group by 1,2 -- GROUP BY outperforms DISTINCT
  ) su
  using (stuff_id)
left join
  ( -- this obtains summary statistics
-- about each stuff item
   select stuff_id, count(*)
   from stuff_events
   group by 1
  ) se
using (stuff_id)
where user_id = 41

This is a very pared down version of what I have. And yes this specific
query can be rewritten as a single GROUP BY, but in the real world I am
gathering the aggregate statistics from several tables, so I actually
have several sub-recordsets similar to the one called "se" above.
Rewriting ALL those as a single GROUP BY is not feasible. I know, all
this cries for a single summarized rollup table, but let's not go there
(for now).

So running the above is inefficient. This particular user_id has only
one associated stuff_id and does not even have much data for that in
stuff_events. The query runs in ~4600ms. Were I to query by stuff_id
instead, things look great (if I change the where clause to the stuff_id
it runs in 25ms). When I query based on stuff_id the optimizer uses an
index on stuff_events.stuff_id. However, when I query by user_id it does
a Seq Scan on stuff_events. I somehow wish I could tell the optimizer to
first figure out which stuff_ids are related to the user_id that is
being asked for and then look ONLY those up in the stuff_events table
using the index on stuff_id. 

It would seem (and this is where we get back to my original question)
that one should be able to just say:

select *
from stuff
left join
  (select stuff_id, count(*)
   from stuff_events
   group by 1
  ) se
using (stuff_id)
where stuff_id in 
  (select distinct stuff_id
   from stuff_user
   where user_id = 41
  )

You'd think that the subquery in the IN would be (very quickly) resolved
to a list of stuff_ids and then stuff_events would be accessed via its
stuff_id index. Instead, the Seq Scan on stuff_events still happens and
the query actually is even slower than the original, running in ~5500ms.

So one (very ugly) way to optimize the first query is to add an extra
join to stuff_user INSIDE the "se" subquery:

select *
from stuff
inner join 
  (select stuff_id, user_id 
   from stuff_user
   group by 1,2
  ) su
  using (stuff_id)
left join
  (select stuff_id, user_id, count(*)
   from stuff_events
   inner join 
 ( -- same subquery as above
  select stuff_id, user_id 
  from stuff_user
  group by 1,2  
 ) su2
 using (stuff_id)
   group by 1,2
  ) se
using (stuff_id)
where user_id = 41;

This does improve things a lot, bringing the execution time for this
particular user to 3ms (!), but it is quite ugly and not fast enough for
me for a user_id with lots of associated stuff_ids.

George

---(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] Have anyone this man e-mail ?

[Ezequias Rodrigues da Rocha]

Hi list,

I am looking for this guy for some help with Slony-I.

http://people.planetpostgresql.org/xzilla/index.php?/archives/200-Alpha-testing-Slony-on-win32-Crib-Notes.html

Robert 
Treat

Any information would be glad.


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 Atenciosamente (Sincerely)
   Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Re: [SQL] huge disparities in =/IN/BETWEEN performance

[Tom Lane]

"George Pavlov" <[EMAIL PROTECTED]> writes:
> I somehow wish I could tell the optimizer to
> first figure out which stuff_ids are related to the user_id that is
> being asked for and then look ONLY those up in the stuff_events table
> using the index on stuff_id. 

This is not really an optimizer problem, or at least not just an
optimizer problem.  The type of plan I think you are wishing for is what
the source code calls a "nestloop with inner index scan", and that
terminology should tip you off that it's only considered when the inner
relation is just a simple indexscannable table.  GROUP BY subqueries
need not apply :-(.

I've been speculating recently about how this situation might be
improved, but I fear it will require nontrivial executor changes along
with planner changes.  The executor's present mechanism for passing
variable values from the outer plan to the inner is a hack that only
really works for indexscans.  I got it to work for inheritance cases
too, recently, but that's about as far as it can be pushed.  I think
it might be possible to get rid of it and use the more-recently-invented
subplan parameter mechanism, but I haven't worked out the details.
(And I know that the Greenplum crowd would like to get rid of subplan
parameters, so I'm not sure this idea will go over well anyway.)  The
planner changes needed will be pretty wide-ranging too, likely.

This might happen for 8.4 but I wouldn't promise it for 8.3.

regards, tom lane

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