Re: [BUGS] BUG #8496: psqlODBC driver does not work well via Excel

[Pavel Stehule]

2013/10/4 Heikki Linnakangas 

> On 02.10.2013 14:57, 
> manindra.sarkar@brightnorth.**co.ukwrote:
>
>> Excel does not seem to respond with any data - but does give an idea that
>> it
>> has made some sort of a connection with the database. SQL commands fail
>> from
>> being executed.
>>
>
It is strange - I used pg ODBC driver together with MS Excel without any
issue.

Regards

Pavel



>
> I'm afraid you'll have to provide a lot more details for anyone to be able
> to help you.
>
> - Heikki
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-bugs
>

Re: [BUGS] BUG #8495: PLSQL PG Terrible performance by using two-dimensional arrays.

[Pavel Stehule]

Hello


2013/10/1 

> The following bug has been logged on the website:
>
> Bug reference:  8495
> Logged by:  Miguel A. Manso Callejo
> Email address:  m.ma...@upm.es
> PostgreSQL version: 9.1.9
> Operating system:   Ubuntu 12.04LTS
> Description:
>
> I'm trying to random access to a 2-dimensional array of double precision
> numbers. When range of the array increase, the performance decrease
> quickly.
> A simple function as:
> CREATE OR REPLACE FUNCTION fill_2d_array( rows integer, cols integer)
> RETURNS integer AS
> $BODY$
> DECLARE
> img double precision[][];
> i integer; j integer;
> cont integer;
> BEGIN
> img  := ARRAY( SELECT 0 FROM generate_series(1, rows * cols) ) ;
> cont:= 0;
> For i IN 1..rows LOOP
> For j IN 1..cols LOOP
> img[i * cols + j] := (i * cols + j)::double precision;
> cont := cont + 1;
> END LOOP;
> END LOOP;
> return cont;
> END;
> $BODY$
>   LANGUAGE plpgsql;
> ALTER FUNCTION fill_2d_array( integer, integer)
>   OWNER TO postgres;
>
>
> when call the function with 700 rows & 1200 cols (explain (analyze,buffers)
> select fill_2d_array(700,1200); ) the time consumed is about 50minutes.
>
>
> What is bad? what i'm doing bad?
>

update a large array is terrible slow due fact, so PostgreSQL arrays are
immutable - so any update is same as copy of array.

sometimes is better to generate table and build a array from table, but it
is not possible for two dimensional array without custom C extension :(

if you can, try to use PLPerl instead PL/pgSQL

You cannot do much more - for 1D arrays exists some tricks, but it doesn't
work for 2D arrays. You can write relative simply C extension, and solve
problem there.

Regards

Pavel Stehule





>
>
> Thank you very much.
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Re: [BUGS] BUG #8441: Recursive function in plpgsql does not seem to handle null values correctly

[Pavel Stehule]

Hello

it works on 9.1.9

postgres=# SELECT longlevenshtein(null, 'foobar');
 longlevenshtein
-
   6


Regards

Pavel

P.S. unlimitted varchar is "text" type in Postgres


2013/9/9 

> The following bug has been logged on the website:
>
> Bug reference:  8441
> Logged by:  Tom van Ees
> Email address:  tv...@davincigroep.nl
> PostgreSQL version: 9.0.4
> Operating system:   Windows Server 2008 R2
> Description:
>
> The Levenshtein function can only handle strings with length 255 or less.
> I needed a Levenshtein function that could handle longer strings.
> Therefore I wrote the following udf:
>
>
> CREATE OR REPLACE FUNCTION longlevenshtein (string1 character varying
> (100), string2 character varying (100)) RETURNS integer AS $$
> BEGIN
> IF  (length(coalesce($1, '')) = 0 AND length(coalesce($2, '')) =
> 0) THEN
> RETURN 0;
> ELSEIF ($1 IS NULL and $2 IS NOT NULL and length($2) > 0) THEN
> RETURN length($2);
> ELSEIF ($2 IS NULL and $1 IS NOT NULL and length($1)> 0) THEN
> RETURN length($1);
> ELSEIF length($1) = 0  AND length(coalesce($2, '')) > 0 THEN
> RETURN length(coalesce($2, ''));
> ELSEIF length($1) > 0 AND (length($2) = 0 or $2 is null) THEN
> RETURN length(coalesce($1, ''));
> ELSE
> RETURN (Levenshtein(SUBSTRING($1 FROM 1 FOR 254), SUBSTRING($2
> FROM 1
> for 254)) + longlevenshtein(coalesce(SUBSTRING($1 FROM 255), ''),
> coalesce(SUBSTRING($2 FROM 255), '')));
> END IF;
> END;
> $$ LANGUAGE plpgsql;
>
>
> When I invoke this function with
> SELECT longlevenshtein(null, 'foobar')
> I get a ERROR:  stack depth limit exceeded
> while I expected the return value 6
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Re: [BUGS] BUG #8329: UPDATE x SET x.y = x.y + z does not work in PL/pgSQL

[Pavel Stehule]

Hello

2013/8/11 Petr Chmelar :
> Hi,
>
> according to the previous thread, can you update the documentation, please:
> http://www.postgresql.org/docs/current/static/functions-math.html
> so there is clean what results do you get eg. on "NULL + 1" (and "1 +
> NULL"), which gives you null and it is different from sum(x), where it gives
> 1 where are NULL and 1 in column x. Probably you should introduce coalesce()
> here.

These information is general - and it is related to ANSI SQL NULL definition.

But I didn't find it in PostgreSQL documentation in one place.

Please, can you send a patch

Regards

Pavel


>
> Thank you,
>
> Petr
>
>
>
>
> On 29.7.2013 22:55, Petr Chmelar wrote:
>>
>> Dear Pavel and Andrew,
>>
>> avoiding the nulls solves the thing - thank you! I was considering NULL as
>> 0... lame.
>> I just wonder why it was working in the console, but it is not important -
>> at the moment it works just fine.
>>
>> Cheers,
>> Petr
>>
>> On 27.7.2013 22:59, Pavel Stehule wrote:
>>>
>>> Hello
>>>
>>> 2013/7/27 Andrew Gierth :
>>>>
>>>> Seems clearly your mistake to me... you do realize that (null + z) is
>>>> always going to be null, right? Maybe your totals columns should have
>>>> been declared NOT NULL (and presumably DEFAULT 0) to avoid this
>>>> problem?
>>>>
>>>> Adding some diagnostics to your function (and fixing all the syntax
>>>> errors) and running it shows that you're frequently trying to add to
>>>> nulls, e.g.:
>>>>
>>>> NOTICE:  sum_pkt_in_int = 
>>>> NOTICE:  sum_orig_raw_pktcount = 4
>>>> NOTICE:  sum_pkt_in_int = 
>>>> NOTICE:  sum_orig_raw_pktcount = 599
>>>>
>>>> these diagnostics were obtained as follows:
>>>>
>>>> CREATE OR REPLACE FUNCTION statistics.notice(text, anyelement)
>>>>   RETURNS anyelement
>>>>   LANGUAGE plpgsql
>>>> AS $function$ begin raise notice '% = %', $1, $2; return $2; end;
>>>> $function$
>>>>
>>>> and changing your update to:
>>>>
>>>>sum_pkt_in_int  = notice(''sum_pkt_in_int'',sum_pkt_in_int)
>>>>  +
>>>> notice(''sum_orig_raw_pktcount'',sum_orig_raw_pktcount),
>>>> -- XXX THIS IS IT, does not work even when ... + 100 XXX
>>>>
>>>> (doing \set VERBOSITY terse in psql is a good idea for this case to
>>>> avoid excessive CONTEXT output)
>>>>
>>>> --
>>>> Andrew (irc:RhodiumToad)
>>>>
>>> it is strange. I didn't find any problem on tested data, although a
>>> bugs was displeasing.
>>>
>>> If query works from console, then planner is clearly ok, and possible
>>> issue can be somewhere in plpgsql. But it should be located more
>>> preciously.
>>>
>>> You can use a debug function or debug trigger
>>>
>>> CREATE OR REPLACE FUNCTION statistics.foo()
>>>   RETURNS trigger
>>>   LANGUAGE plpgsql
>>> AS $function$
>>> begin
>>>if new.sum_pkt_in_int is null then
>>>  raise notice 'attention, new is null';
>>>end if;
>>>return new;
>>> end;
>>> $function$
>>>
>>> create trigger xx before update on hosts1 for each row execute procedure
>>> foo();
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>> p.s. check if COALESCE helps, and then problem is somewhere in data
>>> probably
>>>
>>> sum can return null if all values are null
>>>
>>> postgres=# select sum(a) is null from (values(null::integer)) x(a);
>>>   ?column?
>>> --
>>>   t
>>> (1 row)
>>>
>>>
>>>
>>>
>>>> --
>>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #8345: PostGre SQL database is crashing

[Pavel Stehule]

Hello

2013/7/30  :
> The following bug has been logged on the website:
>
> Bug reference:  8345
> Logged by:  Arun Kumar
> Email address:  arun...@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system:   Linux kernel 2.6.29
> Description:
>
> Currently we have ported PostGRE SQL database on to the Linux kernel 2.6.29
> with Motorolla coldfire cross platform.
> Version of PostgreSQL = 7.4.3.

This version is very old, and there are exists lot of upgrade of
7.4.3, so please upgrade to last 7.4 version

http://www.postgresql.org/ftp/source/v7.4.30/ is probably last.

Regards

Pavel Stehule

> Problem is when multiple threads are accessing the postgresql database some
> times it is seen that, the database is getting crashed. Also we have seen
> the following error displayed:
>
>
> WARNING: terminating connection because of crash of another server process.
>
>
> DETAIL: The postmaster has commanded this server process to roll back
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted the shared memory.
> HINT: In a moment you should be able to reconnect to the database and repeat
> your command.
>
>
>
>
> Some times it wont recover the database.
> Let me know what could be the issue and what fix is required.
>
>
> Regards
> Arun R.H
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #8329: UPDATE x SET x.y = x.y + z does not work in PL/pgSQL

[Pavel Stehule]

Hello

2013/7/27 Andrew Gierth :
> Seems clearly your mistake to me... you do realize that (null + z) is
> always going to be null, right? Maybe your totals columns should have
> been declared NOT NULL (and presumably DEFAULT 0) to avoid this
> problem?
>
> Adding some diagnostics to your function (and fixing all the syntax
> errors) and running it shows that you're frequently trying to add to
> nulls, e.g.:
>
> NOTICE:  sum_pkt_in_int = 
> NOTICE:  sum_orig_raw_pktcount = 4
> NOTICE:  sum_pkt_in_int = 
> NOTICE:  sum_orig_raw_pktcount = 599
>
> these diagnostics were obtained as follows:
>
> CREATE OR REPLACE FUNCTION statistics.notice(text, anyelement)
>  RETURNS anyelement
>  LANGUAGE plpgsql
> AS $function$ begin raise notice '% = %', $1, $2; return $2; end; $function$
>
> and changing your update to:
>
>   sum_pkt_in_int  = notice(''sum_pkt_in_int'',sum_pkt_in_int)
> + notice(''sum_orig_raw_pktcount'',sum_orig_raw_pktcount),
>-- XXX THIS IS IT, does not work even when ... + 100 XXX
>
> (doing \set VERBOSITY terse in psql is a good idea for this case to
> avoid excessive CONTEXT output)
>
> --
> Andrew (irc:RhodiumToad)
>

it is strange. I didn't find any problem on tested data, although a
bugs was displeasing.

If query works from console, then planner is clearly ok, and possible
issue can be somewhere in plpgsql. But it should be located more
preciously.

You can use a debug function or debug trigger

CREATE OR REPLACE FUNCTION statistics.foo()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
  if new.sum_pkt_in_int is null then
raise notice 'attention, new is null';
  end if;
  return new;
end;
$function$

create trigger xx before update on hosts1 for each row execute procedure foo();

Regards

Pavel

p.s. check if COALESCE helps, and then problem is somewhere in data probably

sum can return null if all values are null

postgres=# select sum(a) is null from (values(null::integer)) x(a);
 ?column?
--
 t
(1 row)




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


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

Re: [BUGS] BUG #8329: UPDATE x SET x.y = x.y + z does not work in PL/pgSQL

[Pavel Stehule]

Hello

2013/7/24  :
> The following bug has been logged on the website:
>
> Bug reference:  8329
> Logged by:  Petr
> Email address:  chmel...@fit.vutbr.cz
> PostgreSQL version: 9.2.4
> Operating system:   Fedora x64
> Description:
>
> Hi there,
> we have an issue - if I run a query from a console, it runs OK. When it is
> run using EXECUTE in an PL/pgSQL function, it sets NULL. The simplest
> version of the query is like this:
> UPDATE x
> SET x.y = x.y + z.
> FROM (SELECT z) as Z
> WHERE ... ;
>
>
> If I don't use the "+" in the SET command, it works OK. The whle query is
> quite complex, but OK in a console. If you need the query an tables..., mail
> me, please. I can't make it public.
>

It looks like some problem with naming collision - have you some
plpgsql variable named "z" ??

Please, you have to send a complete example in this case. Usually this
query is ook - and probably you have a problem with some interaction
PL/pgSQL | SQL

Regards

Pavel Stehule

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


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

Re: [BUGS] BUG #8317: ROUND(double*1/2) != ROUND(1/2*double)

[Pavel Stehule]

2013/7/22  :
> The following bug has been logged on the website:
>
> Bug reference:  8317
> Logged by:  Rémi
> Email address:  remi.c...@gmail.com
> PostgreSQL version: 9.2.4
> Operating system:   Ubuntu 12.0.4 32 bits hosted by a VirtualBox
> Description:
>
> Hello;
> I ran into a strange behaviour.
> Initially it showed in a group by, but is also happens in regular select :
>
>
> example :
> SELECT 2*ROUND(1/2*3.2)
> --> gives 0
> SELECT 2*ROUND(3.2*1/2)
> --> gives 3
>
>

Hello

It is not a bug - a reason for this behave is using a integer div in first case

( 1 / 2 ) * 3.2 ... 1/2 .. integer div, because left operand is int
and right operand is int
(3.2 * 1) / 2 ... 3.2 / 2 .. using numeric div, left operand is
numeric and right operand is int

Regards

Pavel Stehule



> Obviously both should return the same thing, preferably 3.
> It looks like a bug to me, or at least a non desirable behaviour.
>
>
> Cheers,
> Rémi
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #8278: psql describe table

[Pavel Stehule]

Hello

2013/7/3  :
> The following bug has been logged on the website:
>
> Bug reference:  8278
> Logged by:  rostap
> Email address:  ros...@wdcarea.com
> PostgreSQL version: 9.2.4
> Operating system:   mac os x
> Description:
>
> describe table does not list tables with the same names in different
> schemas. only one table is being listed while the search_path is set
> properly.
>

It is not a bug, but requested feature - it respect SEARCH_PATH.

you can use wildcard

postgres=# \dt *.xx
   List of relations
 Schema | Name | Type  | Owner
+--+---+---
 public | xx   | table | pavel
 xx | xx   | table | pavel
(2 rows)

Regards

Pavel Stehule


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


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

Re: [BUGS] BUG #8237: CASE Expression - Order of expression processing

[Pavel Stehule]

2013/6/18 Andres Freund :
> On 2013-06-18 13:17:14 +, and...@lombardoni.ch wrote:
>> template1=# SELECT CASE WHEN 1=0 THEN 0 ELSE 1/0 END;
>> ERROR:  division by zero
>>
>>
>> In this case the CASE behaves as expected.
>>
>>
>> But in the following expression:
>>
>>
>> template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END;
>> ERROR:  division by zero
>
> Hrmpf. This is rather annoying. Const simplification processes all
> clauses and evaluates them if it can. Which is - as demonstrated above -
> broken. The only reason
> #= SELECT CASE WHEN 1=1 THEN 0 ELSE 1/0 END;
> works is that we abort even looking at further WHEN clauses if we know
> that one WHEN succeeds.
> So it seems we need to stop processing after finding a single WHEN
> that's not const? Does anybody have a better idea?

probably we should to evaluate constants under subtransaction, and
after exception, we should to stop precalculation of related
subexpression.

But it is slow :(

>
> Greetings,
>
> Andres Freund
>
> --
>  Andres Freund http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #8226: Inconsistent unnesting of arrays

[Pavel Stehule]

Hello

it is known old strange feature

http://postgresql.1045698.n5.nabble.com/Set-returning-functions-in-select-column-list-td5491544.html

Regards

Pavel Stehule

p.s. don't use this feature, it is strange - and we cannot change
behave due compatibility reasons.



2013/6/12 Denis de Bernardy :
> The actual query was something like:
>
> select id, person, unnest(groups) as grp from people
>
> … where groups is a crazy column containing an array that needed to be joined 
> with another table. In this case, you cannot do your suggested solution, 
> which would look like this:
>
> select id, person, grp from people, unnest(groups) as grp
>
> Admittedly, there are other ways to rewrite the above, but — if I may — 
> that's entirely besides the point of the bug report. The Stack Overflow 
> question got me curious about what occurred when two separate arrays are 
> unnested.
>
> Testing revealed the inconsistency, which I tend to view as a bug.
>
> This statement works as expected, unnesting the first array, then cross 
> joining the second accordingly:
>
>>> select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5,6}'::int[])
>
>
> This seems to only unnest one of the arrays, and match the element with the 
> same subscript in the other array:
>
>>> select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::int[])
>
>
> Methinks the behavior should be consistent. It should always do one 
> (presumably like in the first statement) or the other (which leads to 
> undefined behavior in the first statement).
>
> Or it should raise some kind of warning, e.g. "you're using 
> undocumented/unsupported/deprecated/broken syntactic sugar".
>
> Denis
>
>
> On Jun 12, 2013, at 12:05 PM, Greg Stark wrote:
>
>> On Wed, Jun 12, 2013 at 9:58 AM,   wrote:
>>> denis=# select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::int[])
>>
>> set returning functions in the target list of the select don't behave
>> the way you're thinking. What you probably want to do is move the
>> unnest() to the FROM clause:
>>
>> select 1 as a, b, c from unnest('{2,3}'::int[]) as b(b),
>> unnest('{4,5}'::int[]) as c(c)
>>
>>
>> --
>> greg
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

[Pavel Stehule]

Hello

2013/2/21 Josh Kupershmidt :
> On Tue, Feb 19, 2013 at 6:00 AM, Pavel Stehule  
> wrote:
>> 2013/2/16 Pavel Stehule :
>>> 2013/2/16 Tom Lane :
>>>> I think it has come up before.  I wouldn't object to a pg_dump option to
>>>> add IF EXISTS to all the drop commands (though changing the default
>>>> behavior would be more controversial).  Don't intend to spend my own
>>>> time on it though ...
>
> FYI, it was proposed here:
> http://www.postgresql.org/message-id/507ad08c.5020...@dalibo.com
>
>> here is patch, that we use about one year - originally for 9.1 - I did
>> port to 9.3
>
> dropdb and dropuser both support a similar option named --if-exists. I
> suggest --if-exists instead of --conditional-drops for consistency.
> I've only glanced at the patch, but if it makes no sense to use
> --conditional-drops (or --if-exists, whatever it ends up being called)
> without --clean, then attempting to do so should raise an error.

so

* --conditional-drops replaced by --if-exists
* -- additional check, available only with -c option
* fix bug with dump custom functions

Regards

Pavel

>
> Josh


conditional-drops.patch
Description: Binary data

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

Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3

[Pavel Stehule]

2013/2/19 Peter Kroon :
>>try to use pgAdminIII
>
> Could you be more specific?

you can test your queries from pgAdmin SQL executor

but it is strange error - try to look to postgresql and system logs

Pavel

>
>
> 2013/2/19 Pavel Stehule 
>>
>> 2013/2/19 Peter Kroon :
>> > Where can I check and/or alter this?
>>
>> try to use pgAdminIII
>>
>> Regards
>>
>> Pavel
>>
>> >
>> >
>> > 2013/2/19 Lou Picciano 
>> >>
>> >> I wonder if there's a difference in the implementation(s) of readline
>> >> buffering?
>> >>
>> >>
>> >> - Original Message -
>> >> From: Peter Kroon 
>> >> To: Lou Picciano 
>> >> Cc: Michael Paquier ,
>> >> pgsql-bugs@postgresql.org
>> >> Sent: Tue, 19 Feb 2013 15:28:47 - (UTC)
>> >> Subject: Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3
>> >>
>> >> Exceeding length 4679 is a problem. Query results(length) equal or
>> >> below
>> >> this number succeed.
>> >>
>> >>
>> >> 2013/2/19 Peter Kroon 
>> >>>
>> >>> When there are more then 88 rows in the table like 595 I can run the
>> >>> query with success when using: WHERE id BETWEEN 1 AND 88;
>> >>>
>> >>> Using LIMIT 88 fails -> returns nothing
>> >>> Selecting all fails as well.
>> >>>
>> >>>
>> >>> 2013/2/19 Peter Kroon 
>> >>>>
>> >>>> When there are in __table_to_table more than 88 rows nothing gets
>> >>>> returned, otherwise the query rolls out fine.
>> >>>>
>> >>>>
>> >>>>
>> >>>> 2013/2/19 Peter Kroon 
>> >>>>>
>> >>>>> It appears to be a Windows issue only.
>> >>>>> I'll try to post some code.
>> >>>>>
>> >>>>>
>> >>>>> 2013/2/19 Lou Picciano 
>> >>>>>>
>> >>>>>> Seems your testing from different environments like that could
>> >>>>>> easily
>> >>>>>> add any mix of libpq client libraries into the equation (??)
>> >>>>>> (Are both test machines running the same version of pgAdmin? and
>> >>>>>> are
>> >>>>>> both connecting using the libpq installed with them?)
>> >>>>>>
>> >>>>>> We have plenty of experience with clients reporting varying
>> >>>>>> behavior
>> >>>>>> from our 'applications', when it turns out they've 'hooked into' an
>> >>>>>> unexpected version of the libpq client without, for example, SSL
>> >>>>>> support
>> >>>>>> built in, or Kerberos, or... This often happens after the client
>> >>>>>> has
>> >>>>>> unwittingly modified his environment in some way, sometimes after
>> >>>>>> installing
>> >>>>>> software.
>> >>>>>>
>> >>>>>> While the 'support libraries' issues above have no bearing on your
>> >>>>>> case, of course, I certainly don't know enough to know that the
>> >>>>>> different
>> >>>>>> versions of libpq don't present xmlagg output differently!
>> >>>>>>
>> >>>>>> The experts here will weigh in.
>> >>>>>>
>> >>>>>> Lou Picciano
>> >>>>>>
>> >>>>>>
>> >>>>>> - Original Message -
>> >>>>>> From: Peter Kroon 
>> >>>>>>
>> >>>>>>
>> >>>>>>
>> >>>>>>
>> >>>>>> Sent: Tue, 19 Feb 2013 11:52:37 - (UTC)
>> >>>>>> Subject: Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3
>> >>>>>>
>> >>>>>> > When I'm on the sql machine via localhost or 192.168.1.100 I'm
>> >>>>>> > getting results.
>> >>>>>>
>> >>>>>> I mean when I'm physically behind the machine and login 

Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3

[Pavel Stehule]

2013/2/19 Peter Kroon :
> Where can I check and/or alter this?

try to use pgAdminIII

Regards

Pavel

>
>
> 2013/2/19 Lou Picciano 
>>
>> I wonder if there's a difference in the implementation(s) of readline
>> buffering?
>>
>>
>> - Original Message -
>> From: Peter Kroon 
>> To: Lou Picciano 
>> Cc: Michael Paquier , pgsql-bugs@postgresql.org
>> Sent: Tue, 19 Feb 2013 15:28:47 - (UTC)
>> Subject: Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3
>>
>> Exceeding length 4679 is a problem. Query results(length) equal or below
>> this number succeed.
>>
>>
>> 2013/2/19 Peter Kroon 
>>>
>>> When there are more then 88 rows in the table like 595 I can run the
>>> query with success when using: WHERE id BETWEEN 1 AND 88;
>>>
>>> Using LIMIT 88 fails -> returns nothing
>>> Selecting all fails as well.
>>>
>>>
>>> 2013/2/19 Peter Kroon 

 When there are in __table_to_table more than 88 rows nothing gets
 returned, otherwise the query rolls out fine.



 2013/2/19 Peter Kroon 
>
> It appears to be a Windows issue only.
> I'll try to post some code.
>
>
> 2013/2/19 Lou Picciano 
>>
>> Seems your testing from different environments like that could easily
>> add any mix of libpq client libraries into the equation (??)
>> (Are both test machines running the same version of pgAdmin? and are
>> both connecting using the libpq installed with them?)
>>
>> We have plenty of experience with clients reporting varying behavior
>> from our 'applications', when it turns out they've 'hooked into' an
>> unexpected version of the libpq client without, for example, SSL support
>> built in, or Kerberos, or... This often happens after the client has
>> unwittingly modified his environment in some way, sometimes after 
>> installing
>> software.
>>
>> While the 'support libraries' issues above have no bearing on your
>> case, of course, I certainly don't know enough to know that the different
>> versions of libpq don't present xmlagg output differently!
>>
>> The experts here will weigh in.
>>
>> Lou Picciano
>>
>>
>> - Original Message -
>> From: Peter Kroon 
>>
>>
>>
>>
>> Sent: Tue, 19 Feb 2013 11:52:37 - (UTC)
>> Subject: Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3
>>
>> > When I'm on the sql machine via localhost or 192.168.1.100 I'm
>> > getting results.
>>
>> I mean when I'm physically behind the machine and login via pgadmin
>> using localhost or 192.168.1.100 then I get results.
>> When I'm on another machine and login via pgadmin(192.168.1.100) then
>> I get no results.
>> Not sure what to think of this...
>>
>>
>> 2013/2/19 Peter Kroon 
>>>
>>> >Don't you have for example problems with the client application you
>>> > use?
>>>
>>> Yes, with 1 table only. I'm not getting any results.
>>> When I'm on the sql machine via localhost or 192.168.1.100 I'm
>>> getting results.
>>>
>>>
>>> 2013/2/19 Michael Paquier 



 On Tue, Feb 19, 2013 at 5:50 PM, Peter Kroon 
 wrote:
>
> Also no result with FROM __my_table LIMIT 1;

 I'm having correct results with PG 9.2 by using either xmlagg or
 xmlelement.



 For example:
 postgres=# SELECT xmlelement(name el_name, id) FROM __table LIMIT 1;
   xmlelement
 --
  1
 Or:
 postgres=# SELECT xmlagg(xmlelement(name el_name, id)) FROM __table;



   xmlagg
 --

  12
 (1 row)

 Btw, such simple tests would have failed on the buildfarm for
 regression xml.sql, so this looks to be an error in your environment.



 Don't you have for example problems with the client application you
 use?
 --
 Michael
>>>
>>>
>>
>>
>

>>>
>>
>>
>


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

Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

[Pavel Stehule]

Hello

2013/2/16 Pavel Stehule :
> 2013/2/16 Tom Lane :
>> Bruce Momjian  writes:
>>> On Fri, Feb 15, 2013 at 04:06:12PM -0600, Dave Rolsky wrote:
>>>> Fair enough. It should probably an option to add "if exists", at
>>>> least. I can't imagine I'm the only using this tool to ship database
>>>> updates around to different machines, some of which may not have new
>>>> tables. I'd really like to be able to know when the restore fails
>>>> versus when it succeeds but is noisy.
>>
>>> All I can say is I don't remember anyone asking for this in the past.
>>
>> I think it has come up before.  I wouldn't object to a pg_dump option to
>> add IF EXISTS to all the drop commands (though changing the default
>> behavior would be more controversial).  Don't intend to spend my own
>> time on it though ...
>
> we use this feature more than one year.
>
> I'll send patch at Monday

here is patch, that we use about one year - originally for 9.1 - I did
port to 9.3

Regards

Pavel

>
> Regards
>
> Pavel Stehule
>
>
>
>>
>> regards, tom lane
>>
>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs


conditional-drops.patch
Description: Binary data

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

Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

[Pavel Stehule]

2013/2/16 Tom Lane :
> Bruce Momjian  writes:
>> On Fri, Feb 15, 2013 at 04:06:12PM -0600, Dave Rolsky wrote:
>>> Fair enough. It should probably an option to add "if exists", at
>>> least. I can't imagine I'm the only using this tool to ship database
>>> updates around to different machines, some of which may not have new
>>> tables. I'd really like to be able to know when the restore fails
>>> versus when it succeeds but is noisy.
>
>> All I can say is I don't remember anyone asking for this in the past.
>
> I think it has come up before.  I wouldn't object to a pg_dump option to
> add IF EXISTS to all the drop commands (though changing the default
> behavior would be more controversial).  Don't intend to spend my own
> time on it though ...

we use this feature more than one year.

I'll send patch at Monday

Regards

Pavel Stehule



>
> regards, tom lane
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #7850: left outer join is not working or I didn't contruct the query properly

[Pavel Stehule]

Hello

this is not a bug report.

please, send your query to pg_general mailing list

probably better - ask on gridsql mailing list

Regards

Pavel Stehule

2013/2/5  :
> The following bug has been logged on the website:
>
> Bug reference:  7850
> Logged by:  Nadir Vardar
> Email address:  nvar...@carrieriq.com
> PostgreSQL version: 9.0.10
> Operating system:   linux-gridsql
> Description:
>
> I am trying to run below query;
>
> SELECT
> tbl.id,
> tbl.day,
> tbl.week,
> tbl.appfailure,
> tbl.appname,
> tbl.deviceid,
> ext6.DIM_NAME,
> ext7.DIM_NAME,
> ext8.DIM_NAME,
> ext9.DIM_NAME,
> tbl.starttime
> FROM
> APPSTARTEVT tbl
> left outer join DIM_deviceMakeModel ext6 on
> (ext6.DIM_ID=tbl.devicemakemodel)
> left outer join DIM_devicePlatformType ext7 on
> (ext7.DIM_ID=tbl.deviceplatformtype)
> left outer join DIM_deviceVersion ext8 on
> (ext8.DIM_ID=tbl.deviceversion)
> left outer join DIM_operator ext9 on (ext9.DIM_ID=tbl.operator)
> ORDER BY
> tbl.id ASC
>
> throws;
>
>>[Error] Script lines: 1-20 -
>  ERROR: Node 2 has aborted execution, cause is:
> com.edb.gridsql.exception.XDBServerException :
> java.lang.NullPointerException
> QUERY: SELECT "TMPTT6624_1"."deviceplatformtype" AS
> "deviceplatformtype","TMPTT6624_1"."XSERIALID0" AS
> "XSERIALID0","TMPTT6624_1"."XONODEID0" AS "XONODEID0" FROM "TMPTT6624_1"
> LEFT OUTER JOIN "dim_deviceplatformtype" "ext7" ON ("ext7"."dim_id" =
> "TMPTT6624_1"."deviceplatformtype")  WHERE "ext7"."dim_id" IS NULL
>  Line: 1
>
>  [Executed: 2/4/13 11:07:08 PM PST ] [Execution: 0/ms]
>
>
> exception.
>
> Is there anything i am missing.
>
> Query;
>
> SELECT
> tbl.id,
> tbl.day,
> tbl.week,
> tbl.appfailure,
> tbl.appname,
> tbl.deviceid,
> ext6.DIM_NAME,
> ext7.DIM_NAME,
> ext8.DIM_NAME,
> ext9.DIM_NAME,
> tbl.starttime
> FROM
> APPSTARTEVT tbl,
> DIM_deviceMakeModel ext6,
> DIM_devicePlatformType ext7,
> DIM_deviceVersion ext8,
> DIM_operator ext9
> WHERE
> tbl.devicemakemodel=ext6.DIM_ID AND tbl.deviceplatformtype=ext7.DIM_ID
> AND tbl.deviceversion=
> ext8.DIM_ID AND tbl.operator=ext9.DIM_ID
> ORDER BY
> tbl.id ASC
>
> works btw...
> Thanks.
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] wrong search_path being used

[Pavel Stehule]

2013/1/13 Andres Freund :
> On 2013-01-12 15:13:51 -0500, Tom Lane wrote:
>> Andres Freund  writes:
>> > On 2013-01-12 14:29:38 -0500, Tom Lane wrote:
>> >> I think that the alternative most likely to succeed is to consider any
>> >> change in the active value of search_path as forcing replanning of
>> >> cached plans.
>>
>> > I guess it wouldn't really be feasible to keep the search path used to
>> > plan a query in its cached form and check that it fits the one currently
>> > used on every use of the cached plan?
>>
>> Actually that's exactly what I meant: every time we arrive at a query
>> with a cached plan, check to see if the active search_path value is the
>> same as what it was when we made the cached plan, and replan if not.
>
> Okay. I was afraid it would add noticeable overhead to stuff like
> plpgsql... Maybe would lower that by having a "search_path" generation
> counter that gets increased everytime it gets changed but that seems a
> bit too complicated.
>

we can use same mechanism, that is used for plpgsql polymorphic
functions - different parameters => different instances of plpgsql
function.

we can store n instances per function - probably there can be memory
issues when too much different search paths will be used - it is
optimal probably for 10 - 100 different search paths

different solution - can we specify search_path early in connection
string? Then this information can be used by connection pooler - and
we can do replan when different search_path is identified.

Regards

Pavel


> Greetings,
>
> Andres Freund
>
> --
>  Andres Freund http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #7783: lower & upper function incorrect work

[Pavel Stehule]

Hello

maybe this is same problem

http://postgresql.1045698.n5.nabble.com/PostgreSQL-UTF-8-and-Mac-OS-X-td1852185.html

please, try to use different collate - maybe this advice should to help to you

http://yzisin.wordpress.com/2012/01/09/how-to-fix-locale-issues-in-mac-os-x-lion-terminal/

regards

Pavel Stehule

2013/1/3  :
> The following bug has been logged on the website:
>
> Bug reference:  7783
> Logged by:  Alexander Shniperson
> Email address:  alex.shniper...@gmail.com
> PostgreSQL version: 9.1.7
> Operating system:   osx lion 10.7.5
> Description:
>
> Hi.
>
> I have problem at my project, try to do simple steps:
> 1) create DataBase with
> ENCODING = 'UTF8'
> LC_COLLATE = 'C'
> LC_CTYPE = 'C'
>
> 2) create new table at just created database
>
> CREATE TABLE testtable
> (
>   id serial NOT NULL,
>   name character varying(50) NOT NULL,
>   CONSTRAINT testtable_pkey PRIMARY KEY (id )
> )
>
> 3) insert one simple record with Russian word
>
> INSERT INTO testtable (id, name) VALUES (1, 'Залог');
>
> 4) try to select data with upper & lower functions with 'like' operator
>
> select *, lower(name), upper(name) from testtable where lower(name) like
> '%залог%'
>
> 5) as result you must see nothing, just fields without data
>
> fix it please, thanks.
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] Re: BUG #7555: fail to install ora2pg through rhel5 postgresql repo (depsolv issues)

[Pavel Stehule]

Hello

2012/12/12 Samrat :
> have a look at it if you find it usefull
> http://ora2pg-samrat.blogspot.in/

it is not a bug

please, use a different mailing list

Regards

Pavel Stehule

>
>
>
>
>
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/BUG-7555-fail-to-install-ora2pg-through-rhel5-postgresql-repo-depsolv-issues-tp5724474p5736272.html
> Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #7711: Accessing nested composite types in PL/PgSQL doesn't work

[Pavel Stehule]

Hello

it is not a bug - it is known limit :(

nested structures are not supported as target

anybody can implement it if like it

Regards

Pavel Stehule

2012/11/29  :
> The following bug has been logged on the website:
>
> Bug reference:  7711
> Logged by:  Michael Kolomeitsev
> Email address:  mkolomeit...@gmail.com
> PostgreSQL version: 9.2.1
> Operating system:   Arch Linux
> Description:
>
> It seems multiple dots (".") are not allowed. For example: a.b.c := value;
>
> How to reproduce:
> CREATE TABLE nodes (
>   id SERIAL NOT NULL PRIMARY KEY,
>   name TEXT NOT NULL
> );
>
> CREATE TYPE pathId AS (
>   id INTEGER,
>   node nodes
> );
>
> CREATE OR REPLACE FUNCTION test_AEN7304() RETURNS void AS $$
> DECLARE
>   r pathId;
>   n nodes;
> BEGIN
>   n.name := 'abc';
>   r.id := 1;
>   r.node := n; -- All is ok
>   r.node.name := 'def'; -- << Error here
> --  (r).node.name := 'def'; -- << and here
> --  ((r).node).name := 'def'; -- << and here
> END;
> $$ LANGUAGE 'plpgsql';
>
> ERROR:  "r.node.name" is not a known variable
>
> (From http://archives.postgresql.org/pgsql-novice/2012-11/msg00057.php)
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] Prepared Statement Name Truncation

[Pavel Stehule]

2012/11/23 Euler Taveira :
> On 22-11-2012 04:27, Pavel Stehule wrote:
>> 2012/11/21 Greg Sabino Mullane : Separately, what are
>> the objections to raising the size limit to 128?
>>
>>> significantly larger catalog
>>
> Less than 5% of catalog columns? I don't buy your argument.

default 6201kB (64) updated 6967kB (128) on empty database - so it is
12% more per one database

in our warehouses we have more than 10 relations per database and
about 1000 databases per warehouse - so these number can be
significantly higher

second issue can be increasing memory allocations for system caches
and some other place

Regards

Pavel

>
>
> --
>Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
>PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento


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

Re: [BUGS] Prepared Statement Name Truncation

[Pavel Stehule]

2012/11/21 Greg Sabino Mullane :
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
> Gavin Flower asks:
>
>> Would it be appropriate to make it a WARNING in 9.2.2, then
>> increase the length in 9.3?
>
> No: revisions are reserved for bug fixes. This would be more of
> a behavior fix and as such would go into a major version.
>
> Gavan Schneider wrote:
>> (Wild speculation) There may be a "sweet spot" using even shorter
>> identifiers than is the case now, with full disambiguation, which
>> might improve overall performance.
>
> I really don't think the length is really a bottleneck, but others
> can correct me if it is.
>
> Tom Lane wrote:
>> There's some possible value in having a non-default option to throw
>> error for overlength names, but TBH I fear that it won't buy all that
>> much, because people won't think to turn it on when testing.
>>
>> Given the historical volume of complaints (to wit, none up to now),
>> I can't get very excited about changing the behavior here.  I think
>> we're more likely to annoy users than accomplish anything useful.
>
> Well, as with many other things, a lack of complaints does not indicate
> there is no problem. I've certainly seen this problem in the wild before,
> but have not bothered to file an official bug report or anything. Perhaps
> my bad, but the problem is out there. How would you feel about switching
> from NOTICE to WARNING, Tom? That seems to make a lot more sense as we
> are changing the user's input, which warrants more than a notice IMO.
>
> Separately, what are the objections to raising the size limit to 128?

significantly larger catalog

Pavel

>
> - --
> Greg Sabino Mullane g...@turnstep.com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201211211525
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -BEGIN PGP SIGNATURE-
>
> iEYEAREDAAYFAlCtOYMACgkQvJuQZxSWSsjmEQCfb6GOEs7jwst1ao70L+j8IW5q
> gNYAn110QAhwjuhUSW3/uexvU+StsfZz
> =iw6q
> -END PGP SIGNATURE-
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #7571: Query high memory usage

[Pavel Stehule]

2012/9/27 Radovan Jablonovsky :
> Hi Pavel,
>
> Here are the test data with set enable_hashagg to off. It does not looks
> like improvement. Query was running for 30min without returning result set.

so maybe it is PostgreSQL bug - probably window function doesn't reset
some memory context and then execution is memory expensive

Regards

Pavel

>
> db=> set enable_hashagg=off;
> SET
> db=> explain
> db-> SELECT
> db->   schema_name,
> db->   sum(table_size)
> db-> FROM
> db->   (SELECT
> db(> pg_catalog.pg_namespace.nspname as schema_name,
> db(> pg_relation_size(pg_catalog.pg_class.oid) as table_size,
> db(> sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as
> database_size
> db(>FROM pg_catalog.pg_class
> db(>JOIN pg_catalog.pg_namespace
> db(> ON relnamespace = pg_catalog.pg_namespace.oid
> db(>   ) t
> db-> GROUP BY schema_name, database_size;
> QUERY PLAN
> --
>  GroupAggregate  (cost=725540.59..756658.18 rows=4 width=104)
>->  Sort  (cost=725540.59..733219.99 rows=3071759 width=104)
>  Sort Key: pg_namespace.nspname,
> (sum(pg_relation_size((pg_class.oid)::regclass, 'main'::text)) OVER (?))
>  ->  WindowAgg  (cost=120.98..243838.73 rows=3071759 width=68)
>->  Hash Join  (cost=120.98..190082.95 rows=3071759 width=68)
>  Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
>  ->  Seq Scan on pg_class  (cost=0.00..143885.59
> rows=3071759 width=8)
>  ->  Hash  (cost=90.99..90.99 rows=2399 width=68)
>->  Seq Scan on pg_namespace  (cost=0.00..90.99
> rows=2399 width=68)
> (9 rows)
>
>
> Data from top after 30 min of query run with hashagg set off:
>   PID  USER PR  NI  VIRT   RES  SHR S %CPU %MEM    TIME+COMMAND
>  2235 postgres  25   0   27.5g  23g4.6g R  95.175.231:39.81
> postgres: aspuser aspdata 10.0.2.67(52716) SELECT
>
>
> Radovan
>
> On Wed, Sep 26, 2012 at 10:15 PM, Pavel Stehule 
> wrote:
>>
>> Hello
>>
>> you should to run this query on real data - and if it works now, then
>> send EXPLAIN ANALYZE result, please
>>
>> Pavel
>>
>> 2012/9/27 Melese Tesfaye :
>> > Thanks Pavel,
>> > Setting enable_hashagg to off didn't resolve the issue.
>> > Please find the explain as well as query results after "set
>> > enable_hashagg=off;"
>> >
>> > mtesfaye@[local](test_db)=# EXPLAIN SELECT DISTINCT(A.*)
>> > test_db-# FROM table1_t A LEFT JOIN table2_v B
>> > test_db-# ON A.pnr_id=B.pnr_id
>> > test_db-# WHERE  A.pnr_id IN(1801,2056) AND
>> > B.departure_date_time>=DATE('2012-09-26')
>> > test_db-# ORDER BY pnr_id ASC,nam_id ASC;
>> >
>> > +---+
>> > |QUERY PLAN
>> > |
>> >
>> > +---+
>> > | Unique  (cost=1354.62..1354.66 rows=4 width=13)
>> > |
>> > |   ->  Sort  (cost=1354.62..1354.63 rows=4 width=13)
>> > |
>> > | Sort Key: a.pnr_id, a.nam_id, a.pty_num
>> > |
>> > | ->  Merge Join  (cost=1084.06..1354.58 rows=4 width=13)
>> > |
>> > |   Merge Cond: (table2_t.pnr_id = a.pnr_id)
>> > |
>> > |   ->  Unique  (cost=1084.06..1198.67 rows=11461 width=16)
>> > |
>> > | ->  Sort  (cost=1084.06..1112.72 rows=11461
>> > width=16)
>> > |
>> > |   Sort Key: table2_t.pnr_id, table2_t.itn_id,
>> > table2_t.departure_date_time|
>> > |   ->  Seq Scan on table2_t  (cost=0.00..311.34
>> > rows=11461 width=16)   |
>> > | Filter: (departure_date_time >=
>> > '2012-09-26'::date)   |
>> > |   ->  Index Scan using table1_t_pnr_id_idx1 on table1_t a
>> > (cost=0.00..12.60 rows=4 width=13) |
>> > | Index Cond: (pnr_id = ANY
>> > ('{1801,2056}'::integer[]))
>> > |
>> >
>> > +---

Re: [BUGS] BUG #7571: Query high memory usage

[Pavel Stehule]

Hello

you should to run this query on real data - and if it works now, then
send EXPLAIN ANALYZE result, please

Pavel

2012/9/27 Melese Tesfaye :
> Thanks Pavel,
> Setting enable_hashagg to off didn't resolve the issue.
> Please find the explain as well as query results after "set
> enable_hashagg=off;"
>
> mtesfaye@[local](test_db)=# EXPLAIN SELECT DISTINCT(A.*)
> test_db-# FROM table1_t A LEFT JOIN table2_v B
> test_db-# ON A.pnr_id=B.pnr_id
> test_db-# WHERE  A.pnr_id IN(1801,2056) AND
> B.departure_date_time>=DATE('2012-09-26')
> test_db-# ORDER BY pnr_id ASC,nam_id ASC;
> +---+
> |QUERY PLAN
> |
> +---+
> | Unique  (cost=1354.62..1354.66 rows=4 width=13)
> |
> |   ->  Sort  (cost=1354.62..1354.63 rows=4 width=13)
> |
> | Sort Key: a.pnr_id, a.nam_id, a.pty_num
> |
> | ->  Merge Join  (cost=1084.06..1354.58 rows=4 width=13)
> |
> |   Merge Cond: (table2_t.pnr_id = a.pnr_id)
> |
> |   ->  Unique  (cost=1084.06..1198.67 rows=11461 width=16)
> |
> | ->  Sort  (cost=1084.06..1112.72 rows=11461 width=16)
> |
> |   Sort Key: table2_t.pnr_id, table2_t.itn_id,
> table2_t.departure_date_time|
> |   ->  Seq Scan on table2_t  (cost=0.00..311.34
> rows=11461 width=16)   |
> | Filter: (departure_date_time >=
> '2012-09-26'::date)   |
> |   ->  Index Scan using table1_t_pnr_id_idx1 on table1_t a
> (cost=0.00..12.60 rows=4 width=13) |
> | Index Cond: (pnr_id = ANY ('{1801,2056}'::integer[]))
> |
> +---+
> (12 rows)
>
> Time: 5.889 ms
>
> mtesfaye@[local](test_db)=# show enable_hashagg;
> ++
> | enable_hashagg |
> ++
> | on |
> ++
> (1 row)
>
> Time: 0.136 ms
>
> mtesfaye@[local](test_db)=# set enable_hashagg=off;
> SET
> Time: 0.203 ms
> mtesfaye@[local](test_db)=# show enable_hashagg;
> ++
> | enable_hashagg |
> ++
> | off|
> ++
> (1 row)
>
> Time: 0.131 ms
>
>
> mtesfaye@[local](test_db)=# SELECT DISTINCT(A.*)
> test_db-# FROM table1_t A LEFT JOIN table2_v B
> test_db-# ON A.pnr_id=B.pnr_id
> test_db-# WHERE  A.pnr_id IN(1801,2056) AND
> B.departure_date_time>=DATE('2012-09-26')
> test_db-# ORDER BY pnr_id ASC,nam_id ASC;
> ++----+-----+
> | pnr_id | nam_id | pty_num |
> +++-+
> |   1801 |   3359 |   1 |
> |   1801 |   3360 |   1 |
> |   1801 |   3361 |   1 |
> |   1801 |   3362 |   1 |
> ++----+-----+
> (4 rows)
>
> Time: 8.452 ms
>
>
> On Thu, Sep 27, 2012 at 3:54 AM, Pavel Stehule 
> wrote:
>>
>> Hello
>>
>> this situation is possible, when optimizer use HashAgg where should not
>> use it.
>>
>> Please, try to disable HashAgg - set enable_hashagg to off;
>>
>> please, send EXPLAIN result
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2012/9/26  :
>> > The following bug has been logged on the website:
>> >
>> > Bug reference:  7571
>> > Logged by:  Radovan Jablonovsky
>> > Email address:  radovan.jablonov...@replicon.com
>> > PostgreSQL version: 9.1.5
>> > Operating system:   CentOs 5.8 Linux 2.6.18-308.el5 x86_64
>> > Description:
>> >
>> > During checking our company database size we used query, which was not
>> > the
>> > best to find out the tables/db size but should do the job. The query was
>> > tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running
>> > alone without other activity. It consumed almost all RAM forced server
>> > to
>> > use swap and after 1hour it was still running. The simplified version of
>> > query used 20% of memory and finished after 1hour 8min.
>> >
>> > The size of pg_class is 3mil rows/objects and pg_namespace has 3000
>> > rows/schemata.
>> >
>> > query:
>> > SELECT
>> >   schema_name,
>> >   sum(table_size)
>> > FROM
>> >   (SELECT

Re: [BUGS] BUG #7571: Query high memory usage

[Pavel Stehule]

Hello

this situation is possible, when optimizer use HashAgg where should not use it.

Please, try to disable HashAgg - set enable_hashagg to off;

please, send EXPLAIN result

Regards

Pavel Stehule

2012/9/26  :
> The following bug has been logged on the website:
>
> Bug reference:  7571
> Logged by:  Radovan Jablonovsky
> Email address:  radovan.jablonov...@replicon.com
> PostgreSQL version: 9.1.5
> Operating system:   CentOs 5.8 Linux 2.6.18-308.el5 x86_64
> Description:
>
> During checking our company database size we used query, which was not the
> best to find out the tables/db size but should do the job. The query was
> tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running
> alone without other activity. It consumed almost all RAM forced server to
> use swap and after 1hour it was still running. The simplified version of
> query used 20% of memory and finished after 1hour 8min.
>
> The size of pg_class is 3mil rows/objects and pg_namespace has 3000
> rows/schemata.
>
> query:
> SELECT
>   schema_name,
>   sum(table_size)
> FROM
>   (SELECT
> pg_catalog.pg_namespace.nspname as schema_name,
> pg_relation_size(pg_catalog.pg_class.oid) as table_size,
> sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
>FROM pg_catalog.pg_class
>JOIN pg_catalog.pg_namespace
> ON relnamespace = pg_catalog.pg_namespace.oid
>   ) t
> GROUP BY schema_name, database_size;
>
>
> top - 10:50:44 up 20 days, 19:00,  1 user,  load average: 1.15, 1.10, 0.84
> Tasks: 239 total,   3 running, 236 sleeping,   0 stopped,   0 zombie
> Cpu(s): 15.1%us,  1.5%sy,  0.0%ni, 83.0%id,  0.5%wa,  0.0%hi,  0.0%si,
> 0.0%st
> Mem:  32946260k total, 32599908k used,   346352k free,   141924k buffers
> Swap: 55043952k total,85216k used, 54958736k free, 14036516k cached
>
> Info from top:
>   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
>  2016 postgres  25   0 22.8g  17g 3.2g R 96.1 56.0  19:17.01 postgres:
> postgres db 10.0.1.10(49928) SELECT
>
> Simplified version of query uses pg_tables. It has 0.5mil rows/tables.
> Simplified version of query:
> SELECT
>   schemaname,
>   sum(pg_relation_size(schemaname || '.' || tablename))::bigint
> FROM pg_tables
> GROUP BY schemaname;
>
>
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

Re: [BUGS] BUG #7529: Support different error handling behavior than auto rollback

[Pavel Stehule]

Hello

this is not bug - please, can you send your proposal to pg_hackers mailing list?

Regards

Pavel Stehule

2012/9/10  :
> The following bug has been logged on the website:
>
> Bug reference:  7529
> Logged by:  Lego Haryanto
> Email address:  legoharya...@gmail.com
> PostgreSQL version: 9.0.5
> Operating system:   Any
> Description:
>
> I understand that current transaction behavior in PostgreSQL is to throw
> away the whole transaction away (rollback) if there's at least one error
> within the transaction.
>
> I believe on certain application of data replication, say, migration from
> other database source, ... this will be pretty cumbersome to support in
> PostgreSQL even though users have some conflict resolution strategy in
> mind.
>
> On the following scenario, imagine this transaction is coming from a source
> of different DB, migrating into a PostgreSQL target.
>
> BEGIN
>   INSERT #1... (success)
>   INSERT #2... (success)
>   INSERT #3... (error or conflict/collision)
>   INSERT #4... (success)
> COMMIT;
>
> Current behavior of PostgreSQL is that the INSERT #4 command is ignored
> because of the error on INSERT #3 (subsequent commands are ignored).
>
> And the COMMIT command is accepted as ROLLBACK, which we can argue it's
> misleading because user does an explicit COMMIT, but the actual action is a
> rollback.
>
> Can we actually support honoring the successful DMLs above, and do the
> actual COMMIT that is inserting #1, #2, and #4 in above example?
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs


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

[BUGS] function format doesn't support values with variadic flag

[Pavel Stehule]

Hello

this code should to work, but it doesn't

postgres=# select format('%s %s', variadic array['Hello','World']);
ERROR:  too few arguments for format

postgres=# create or replace function un(variadic integer[])
postgres-# returns setof int as $$ select unnest($1)$$ language sql;
CREATE FUNCTION
postgres=# select un(1,2,3,4);
 un

  1
  2
  3
  4
(4 rows)

postgres=# select un(variadic array[1,2,3]);
 un

  1
  2
  3
(3 rows)

A problem is in too simple evaluation of "any" parameter - without
check to "VARIADIC" flag

Regards

Pavel Stehule


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

Re: [BUGS] BUG #6761: unexpected behaviour of 'now'::timestamp

[Pavel Stehule]

Hello

this is not bug - it is consequence of plan cache

http://postgres.cz/wiki/Automatic_execution_plan_caching_in_PL/pgSQL

please, use CURRENT_TIMESTAMP instead - using 'now'::timestamp is
deprecated due this issue

Regards

Pavel

2012/7/25  :
> The following bug has been logged on the website:
>
> Bug reference:  6761
> Logged by:  Bert Thomas
> Email address:  b...@brothom.nl
> PostgreSQL version: 9.1.3
> Operating system:   Linux
> Description:
>
> Hi,
>
> To reproduce what I mean, consider this function:
>
> CREATE FUNCTION testbug() RETURNS character varying
> LANGUAGE plpgsql
> AS $$declare
>   l_ts timestamp(0);
>
> begin
>   l_ts := 'now'::timestamp(0);
>   return l_ts::varchar;
> end
> $$;
>
> If a program invokes this function multiple times on a single connection,
> only the first time the correct date and time is produced. All other
> invocations return the exact same value as the first invocation.
>
> Changing the function to this fixes the problem:
>
> CREATE FUNCTION testbug() RETURNS character varying
> LANGUAGE plpgsql
> AS $$declare
>   l_ts timestamp(0);
>   l_nu varchar;
>
> begin
>   l_nu := 'now';
>   l_ts := l_nu::timestamp(0);
>   return l_ts::varchar;
> end
> $$;
>
> Appearently the expression is re-evaluated every time in this case, whilst
> in the first case it is only evaluated once as the constant 'now' could not
> change obviously. I'm not sure if this is a bug or not, but at least it is
> suprising behaviour. To me it looks like a bad form of optimization.
>
> Kind regards,
> Bert Thomas
> BroThom
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] Duplicate rows primary key bug

[Pavel Stehule]

Hello

2012/7/23 leo xu :
>   version
> -
>  PostgreSQL 9.1.2 on red hat 5.4 x64, compiled by GCC 4.1
>
>
> There are several tables have two same rows with the same primary key.  This
> shouldn't happen. Has this been reported as a bug in this version?
>

you index is broken - the source of this issue should be in hw or in sw.

Can you repeat a operations that breaks your indexes?

Regards

Pavel Stehule


>
>
>
>
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/Duplicate-rows-primary-key-bug-tp5717581.html
> Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] BUG #6745: feature request

[Pavel Stehule]

Hello

2012/7/20  :
> The following bug has been logged on the website:
>
> Bug reference:  6745
> Logged by:  ram
> Email address:  blastofftopl...@gmail.com
> PostgreSQL version: 9.1.4
> Operating system:   linux mint maya
> Description:
>
> Please add built in feature in postgresql for setting size of individual
> table in terms of bytes ,megabyes etc. so that user wont be concerned about
> the data flooding out of disk.
>
> Thankyou, I hope you take this seriously.
>
>

this is not bug, but request feature

Regards

Pavel Stehule




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

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

Re: [BUGS] BUG #6724: EXCEPTION syntax not recgonised

[Pavel Stehule]

Hello

This is not bug

keyword EXCEPTION is supported only in plpgsql.

Regards

Pavel Stehule

2012/7/9  :
> The following bug has been logged on the website:
>
> Bug reference:  6724
> Logged by:  Chine Bleu
> Email address:  chine.b...@yahoo.com
> PostgreSQL version: 9.0.4
> Operating system:   macosx
> Description:
>
> query: BEGIN
> INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
> EXCEPTION WHEN unique_violation THEN
> UPDATE config SET value=E'10' WHERE name=E'dbexp';
> END error: fatal query: ERROR:  syntax error at or near "INSERT"
> LINE 2: INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
> ^
>
>
> query: BEGIN;
> INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
> EXCEPTION WHEN unique_violation THEN
> UPDATE config SET value=E'10' WHERE name=E'dbexp';
> END error: fatal query: ERROR:  syntax error at or near "EXCEPTION"
> LINE 3: EXCEPTION WHEN unique_violation THEN
> ^
>
> This is so much easier than implementing that horror of REPLACE everyone
> else has somehow figured out how to implement.
>
> And, no, I don't really expect an answer. I will continue to use the
> DELETE...;;INSERT... improvement on REPLACE.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] BUG #6701: IS NOT NULL doesn't work on complex composites

[Pavel Stehule]

2012/6/21 Rikard Pavelic :
> On 20.6.2012. 21:10, Tom Lane wrote:
>> rikard.pave...@zg.htnet.hr writes:
>>> create type t AS (i int); create type complex as (t t, i int); create table 
>>> bad(i int, c complex); --This doesn't work as expected select * from bad 
>>> where c is not null;
>> What do you consider to be "expected"? Have you read the documentation where 
>> it points out that IS NULL and IS NOT NULL are not inverses for composite 
>> values?
>> http://www.postgresql.org/docs/9.1/static/functions-comparison.html (I'm not 
>> that thrilled with this behavior either, but it is per SQL standard AFAICT.) 
>> regards, tom lane
>
> I understand the concept behind if one composite property is null then then 
> IS NULL check returns NULL (instead true or false).
> I can even understand IS NULL check returning false.
>
> I can use ::text to get what I expected,
> but Postgres still seems inconsistent in handling NULL checks.
>
> create type complex as (i int, j int);
> create table t (i int, c complex not null);
>
> --error as expected
> insert into t values(1, null);
>
> --unexpected - passed!?
> insert into t values(1, (null,4));
>
> -- this is false - I think it would be better if it's null, but lets move on
> select (null, 4) is not null
>
> --lets try again with check constraint
> alter table t add check(c is not null);
>
> --error as expected from is not null check above
> insert into t values(1, (null,4));
>
> It seems that check constraint behaves differently.
> Docs say: (http://www.postgresql.org/docs/9.1/static/ddl-constraints.html)
> "A not-null constraint is functionally equivalent to creating a check 
> constraint CHECK (column_name IS NOT NULL)"
> And at least there is more notes required ;(
>
> Let's try some more.
>
> create table x (i int, c complex);
>
> insert into x values(1,null);
> insert into x values(2,(1,null));
> insert into x values(3,(1,2));
>
> --first row - ok
> select * from x where c is null;
>
> --last row - ok
> select * from x where c is not null;
>
> --unexpected result again
> select c is null from x;
>
> I must admit I was expecting
> true
> null
> false

but C is not one value - it is composite - and composite in SQL is not
pointer like C or C++, but it is list of values - and composite is
null (list is null) when all fields are null.

Regards

Pavel

>
> Regards,
> Rikard
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] BUG #6701: IS NOT NULL doesn't work on complex composites

[Pavel Stehule]

2012/6/20 Rikard Pavelic :
> On 20.6.2012. 20:55, Pavel Stehule wrote:
>> Hello
>>
>> it is not a bug - see
>> http://archives.postgresql.org/pgsql-hackers/2009-07/msg01525.php
>>
>> Regards
>>
>> Pavel Stehule
>>
>
> I found that in documentation after reporting bug.
> I'm sorry for not searching some more, but didn't know where to look exactly.
>
> Well, at least is counter intuitive if not a bug ;(

I had a problem with this issue too, but it is well designed - I believe now.

Regards

Pavel


>
> Thanks.

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

Re: [BUGS] BUG #6701: IS NOT NULL doesn't work on complex composites

[Pavel Stehule]

Hello

it is not a bug - see
http://archives.postgresql.org/pgsql-hackers/2009-07/msg01525.php

Regards

Pavel Stehule

2012/6/20  :
> The following bug has been logged on the website:
>
> Bug reference:      6701
> Logged by:          Rikard Pavelic
> Email address:      rikard.pave...@zg.htnet.hr
> PostgreSQL version: 9.1.3
> Operating system:   Windows 7
> Description:
>
> create type t AS (i int);
> create type complex as (t t, i int);
> create table bad(i int, c complex);
>
> insert into bad values(1, null);
> insert into bad values(1, ROW(null, 2));
> insert into bad values(1, ROW(ROW(1), 2));
>
> select * from bad;
> select * from bad where c is null;
>
> --This doesn't work as expected
> select * from bad where c is not null;
>
> --Fortunately a workaround
> select * from bad where NOT c is null;
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] BUG #6626: union all with values of type "unknown"

[Pavel Stehule]

2012/5/22 Robert Haas :
> On Tue, May 22, 2012 at 3:55 PM, Tom Lane  wrote:
>> Robert Haas  writes:
 deik3qfhu265n6=> with hello as (select 'hello' as name)
 deik3qfhu265n6-> , bye as (select 'bye' as name)
 deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
 ERROR:  failed to find conversion function from unknown to text
>>
>>> I think it should return a column of type text, just as if you'd done this:
>>> select v from (select 'hello' union all select 'bye') x(v);
>>
>> I don't think it's a great idea to make CTEs handle this differently
>> from other places where the same issue arises (from memory, views and
>> INSERT/SELECT have problems with unknown literals, and there are
>> probably other places I'm forgetting).
>>
>> Should we institute a uniform policy of forcing unknown sub-select
>> outputs to text type?  This would almost certainly break a few peoples'
>> queries, but the reduction of surprise might be worth it for most.
>
> I think if we can't do real type inference, forcing unknown to text is
> probably the least of evils.

can we implement late cast? Cast unknown to text only when exception
is raised, resp. before? This issue is relative unfriendly for
beginners

Regards

Pavel
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] BUG #6572: The example of SPI_execute is bogus

[Pavel Stehule]

2012/4/15 Boszormenyi Zoltan :
> 2012-04-14 18:15 keltezéssel, Peter Eisentraut írta:
>
>> On lör, 2012-04-14 at 08:23 -0400, Robert Haas wrote:
>>>
>>> On Sat, Apr 14, 2012 at 3:27 AM, Pavel Stehule
>>>  wrote:
>>>>>
>>>>> It has a lot of sense.  Without it, it's very difficult to do logical
>>>>> replication on a table with no primary key.
>>>>>
>>>>> (Whether or not people should create such tables in the first place
>>>>> is, of course, beside the point.)
>>>>
>>>> I am not against to functionality - I am against just to syntax DELETE
>>>> FROM tab LIMIT x
>>>>
>>>> because is it ambiguous what means: DELETE FROM tab RETURNING * LIMIT x
>>>
>>> What's ambiguous about that?
>>
>> I suppose one could wonder whether the LIMIT applies to the deleting or
>> just the returning.
>
>
> Ambigous only in this order. LIMIT x RETURNING * wouldn't be.

but theoretically you can has two LIMIT clauses in one SQL statements

DELETE FROM tab LIMIT n RETURNING * LIMIT m

without updatable CTE it is probably only one solution, but because we
have UCTE, then we don't need this construct.

Regards

Pavel

>
> --
> --
> Zoltán Böszörményi
> Cybertec Schönig&  Schönig GmbH
> Gröhrmühlgasse 26
> A-2700 Wiener Neustadt, Austria
> Web: http://www.postgresql-support.de
>     http://www.postgresql.at/
>

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

Re: [BUGS] BUG #6572: The example of SPI_execute is bogus

[Pavel Stehule]

2012/4/14 Peter Eisentraut :
> On lör, 2012-04-14 at 08:23 -0400, Robert Haas wrote:
>> On Sat, Apr 14, 2012 at 3:27 AM, Pavel Stehule  
>> wrote:
>> >> It has a lot of sense.  Without it, it's very difficult to do logical
>> >> replication on a table with no primary key.
>> >>
>> >> (Whether or not people should create such tables in the first place
>> >> is, of course, beside the point.)
>> >
>> > I am not against to functionality - I am against just to syntax DELETE
>> > FROM tab LIMIT x
>> >
>> > because is it ambiguous what means: DELETE FROM tab RETURNING * LIMIT x
>>
>> What's ambiguous about that?
>
> I suppose one could wonder whether the LIMIT applies to the deleting or
> just the returning.
>

yes, exactly

Regards

Pavel

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

Re: [BUGS] BUG #6572: The example of SPI_execute is bogus

[Pavel Stehule]

2012/4/14 Robert Haas :
> On Fri, Apr 13, 2012 at 10:43 PM, Pavel Stehule  
> wrote:
>>> Yeah.  I think it would be a good idea for UPDATE and DELETE to expose
>>> a LIMIT option, but I can't really see the virtue in making that
>>> functionality available only through SPI.
>>
>> I don't agree - LIMIT after UPDATE or DELETE has no sense. Clean
>> solution should be based on using updateable CTE.
>
> It has a lot of sense.  Without it, it's very difficult to do logical
> replication on a table with no primary key.
>
> (Whether or not people should create such tables in the first place
> is, of course, beside the point.)

I am not against to functionality - I am against just to syntax DELETE
FROM tab LIMIT x

because is it ambiguous what means: DELETE FROM tab RETURNING * LIMIT x

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

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

Re: [BUGS] BUG #6572: The example of SPI_execute is bogus

[Pavel Stehule]

>
> Yeah.  I think it would be a good idea for UPDATE and DELETE to expose
> a LIMIT option, but I can't really see the virtue in making that
> functionality available only through SPI.
>

I don't agree - LIMIT after UPDATE or DELETE has no sense. Clean
solution should be based on using updateable CTE.

Regards

Pavel

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

Re: [BUGS] plpgsql doesn't check a number of expressions and number of target variables correctly

[Pavel Stehule]

2012/3/5 Tom Lane :
> Pavel Stehule  writes:
>> Hello
>> I found a plpgsql bug:
>
>> create or replace function fx()
>> returns void as $$
>> declare a int; b int;
>> begin
>>   select 10,20 into a;
>> end;
>> $$ language plpgsql;
>
>> this is bug, or minimally potential source of strange behave.
>
> AFAICS this is intentional --- see the comment in exec_move_row.
>
> In any case, I think tightening it up is more likely to break working
> applications than do anything helpful.

I agree with you about break working application :( But this is good
example what should be checked in CHECK statement.

but it should be bug - it has no sense - no in this form - it is
assign row to scalar.

I found a some mysterious and very difficult identified bugs in one
application based on this behave.

Regards

Pavel

>
>                        regards, tom lane

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

[BUGS] plpgsql doesn't check a number of expressions and number of target variables correctly

[Pavel Stehule]

Hello

I found a plpgsql bug:

create or replace function fx()
returns void as $$
declare a int; b int;
begin
  select 10,20 into a;
end;
$$ language plpgsql;

this is bug, or minimally potential source of strange behave.

Regards

Pavel Stehule

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

Re: [BUGS] BUG #6495: mis-sorting on string field consisting of space-padded numbers

[Pavel Stehule]

Hello

2012/2/28  :
> The following bug has been logged on the website:
>
> Bug reference:      6495
> Logged by:          Lou Duchez
> Email address:      l...@paprikash.com
> PostgreSQL version: 9.1.2
> Operating system:   Fedora 16, CentOS 5
> Description:
>
> Download the following file and feed it into psql:
>
> http://www.paprikash.com/lou/missorttest.sql
>
> Then run the command:
>
> select * from missorttest order by hiercode;
>
> Each "hiercode" entry contains concatenations of numbers, where each number
> is left-padded to take exactly four characters.  PostgreSQL is sorting as if
> the padding were on the right.  As a result, "   2  10" is coming before "
> 2   2", even though a literal sorting of the string should put "   2   2"
> first (because chr 32 comes before chr 49).
>
> More info: this error does not occur on the Windows build of PostgreSQL.
> Also, I compile my own versions of PostgreSQL on Fedora and CentOS, taking
> all the defaults.
>

This should not be bug - this behave depends on system libraries and
locales setting.

Please, check value of lc_collate variable

postgres=> show lc_collate;
 lc_collate
-
 en_US.UTF-8
(1 row)

different collations should have different order.

Regards

Pavel Stehule

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

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

Re: [BUGS] BUG #6483: Rows being evaluated, although being outside the LIMIT / OFFSET boundaries

[Pavel Stehule]

Hello

this is not bug

"OFFSET" doesn't mean go to line n - it means - first n lines don't
send to client.

Regards

Pavel Stehule

2012/2/22  :
> The following bug has been logged on the website:
>
> Bug reference:      6483
> Logged by:          Kouber Saparev
> Email address:      kou...@saparev.com
> PostgreSQL version: 9.1.2
> Operating system:   Debian
> Description:
>
> The rows of a SELECT statement are being evaluated, even when not shown in
> the final result, when using an OFFSET > 0. Although I know that LIMIT is
> imposed just before flushing the result set to the client, this behaviour
> seems quite confusing, especially when using DML statements in the field
> list of the SELECT itself.
>
> CREATE TABLE xxx (id INT);
>
> CREATE FUNCTION f(xxx) RETURNS VOID AS $$
> BEGIN
>  -- imagine some DML statements here --
>
>  RAISE NOTICE '%', $1.id;
> END;
> $$ LANGUAGE PLPGSQL;
>
> INSERT INTO xxx VALUES (1), (2), (3), (4), (5);
>
>
> -- shows a notice for 1 and 2
> SELECT x.id, f(x) FROM xxx as x LIMIT 2;
>
>
> -- shows a notice for 1, 2, 3 and 4
> SELECT x.id, f(x) FROM xxx as x LIMIT 2 OFFSET 2;
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] BUG #6379: SQL Function Causes Back-end Crash

[Pavel Stehule]

2012/1/4 Paul Ramsey :
> One extra detail, my PostgreSQL is compiled with --enable-cassert.
> This seems to be what sets off the killer function.

me too

Pavel


>
> On Wed, Jan 4, 2012 at 11:25 AM, hubert depesz lubaczewski
>  wrote:
>> On Wed, Jan 04, 2012 at 07:17:17PM +, pram...@cleverelephant.ca wrote:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference:      6379
>>> Logged by:          Paul Ramsey
>>> Email address:      pram...@cleverelephant.ca
>>> PostgreSQL version: 9.1.2
>>> Operating system:   OSX 10.6.8
>>> Description:
>>>
>>> CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>>   DROP TABLE if EXISTS foo;
>>>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
>>
>> Cannot replicate:
>>
>> (depesz@localhost:5910) 20:23:43 [depesz]
>> $ CREATE OR REPLACE FUNCTION kill_backend()
 RETURNS VOID
 AS $$
   DROP TABLE if EXISTS foo;
   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
 $$ LANGUAGE 'SQL';
>> CREATE FUNCTION
>> (depesz@localhost:5910) 20:23:49 [depesz]
>> $ select kill_backend();
>> NOTICE:  table "foo" does not exist, skipping
>> CONTEXT:  SQL function "kill_backend" statement 1
>>  kill_backend
>> --
>>  [null]
>> (1 row)
>>
>> (depesz@localhost:5910) 20:23:55 [depesz]
>> $ select kill_backend();
>>  kill_backend
>> --
>>  [null]
>> (1 row)
>>
>> (depesz@localhost:5910) 20:23:56 [depesz]
>> $ select kill_backend();
>>  kill_backend
>> --
>>  [null]
>> (1 row)
>>
>> (depesz@localhost:5910) 20:23:58 [depesz]
>> $ select version();
>>                                                version
>> ---
>>  PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.real 
>> (Debian 4.6.2-5) 4.6.2, 64-bit
>> (1 row)
>>
>> Side note - definition as is, doesn't work on 9.2:
>> $ CREATE OR REPLACE FUNCTION kill_backend()
 RETURNS VOID
 AS $$
   DROP TABLE if EXISTS foo;
   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
 $$ LANGUAGE 'SQL';
>> ERROR:  language "SQL" does not exist
>>
>> changing it to proper sql (not uppercase) fixed this problem.
>>
>> Best regards,
>>
>> depesz
>>
>> --
>> The best thing about modern society is how easy it is to avoid contact with 
>> it.
>>                                                             
>> http://depesz.com/
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] BUG #6379: SQL Function Causes Back-end Crash

[Pavel Stehule]

Hello

I can replicate it

postgres=# select kill_backend();
NOTICE:  table "foo" does not exist, skipping
CONTEXT:  SQL function "kill_backend" statement 1
The connection to the server was lost. Attempting reset: Failed.
!>

bash-4.2$ uname -a
Linux nemesis 2.6.41.4-1.fc15.x86_64 #1 SMP Tue Nov 29 11:53:48 UTC
2011 x86_64 x86_64 x86_64 GNU/Linux

Program terminated with signal 11, Segmentation fault.
#0  0x005aaacd in postquel_end (es=0x2c77298) at functions.c:637
637 (*es->qd->dest->rDestroy) (es->qd->dest);

Missing separate debuginfos, use: debuginfo-install glibc-2.14-5.x86_64
(gdb) bt
#0  0x005aaacd in postquel_end (es=0x2c77298) at functions.c:637
#1  0x005abb3e in fmgr_sql (fcinfo=) at functions.c:902
#2  0x005a44aa in ExecMakeFunctionResult (fcache=0x2c6acd0,
econtext=0x2c6aaa8, isNull=0x2c6b680 "", isDone=0x2c6b7c0)
at execQual.c:1832
#3  0x005a79e2 in ExecTargetList (isDone=0x7fff58a0fedc,
itemIsDone=0x2c6b7c0, isnull=0x2c6b680 "", values=0x2c6b660,
econtext=0x2c6aaa8, targetlist=0x2c6b788) at execQual.c:5112
#4  ExecProject (projInfo=, isDone=0x7fff58a0fedc) at
execQual.c:5327
#5  0x005ba73a in ExecResult (node=0x2c6a990) at nodeResult.c:155
#6  0x005a0658 in ExecProcNode (node=0x2c6a990) at execProcnode.c:372
#7  0x0059d1f2 in ExecutePlan (dest=0x2c2d3c0,
direction=, numberTuples=0, sendTuples=1 '\001',
operation=CMD_SELECT, planstate=0x2c6a990, estate=0x2c6a878) at
execMain.c:1449
#8  standard_ExecutorRun (queryDesc=0x2c6a468, direction=, count=0) at execMain.c:323
#9  0x00681487 in PortalRunSelect (portal=0x2c68458,
forward=, count=0, dest=0x2c2d3c0) at pquery.c:942
#10 0x00682a90 in PortalRun (portal=0x2c68458,
count=9223372036854775807, isTopLevel=1 '\001', dest=0x2c2d3c0,
altdest=0x2c2d3c0, completionTag=0x7fff58a102e0 "") at pquery.c:786
#11 0x0067eb99 in exec_simple_query (query_string=0x2c2ba38
"select kill_backend();") at postgres.c:1021
#12 PostgresMain (argc=, argv=,
username=) at postgres.c:3881
#13 0x00636da9 in BackendRun (port=0x2baa940) at postmaster.c:3587
#14 BackendStartup (port=0x2baa940) at postmaster.c:3272
#15 ServerLoop () at postmaster.c:1350
#16 0x00637798 in PostmasterMain (argc=,
argv=0x2b8a440) at postmaster.c:1110
#17 0x00455f9a in main (argc=3, argv=0x2b8a440) at main.c:199

Regards

Pavel


2012/1/4 hubert depesz lubaczewski :
> On Wed, Jan 04, 2012 at 07:17:17PM +, pram...@cleverelephant.ca wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      6379
>> Logged by:          Paul Ramsey
>> Email address:      pram...@cleverelephant.ca
>> PostgreSQL version: 9.1.2
>> Operating system:   OSX 10.6.8
>> Description:
>>
>> CREATE OR REPLACE FUNCTION kill_backend()
>> RETURNS VOID
>> AS $$
>>   DROP TABLE if EXISTS foo;
>>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>> $$ LANGUAGE 'SQL';
>
> Cannot replicate:
>
> (depesz@localhost:5910) 20:23:43 [depesz]
> $ CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>>   DROP TABLE if EXISTS foo;
>>>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
> CREATE FUNCTION
> (depesz@localhost:5910) 20:23:49 [depesz]
> $ select kill_backend();
> NOTICE:  table "foo" does not exist, skipping
> CONTEXT:  SQL function "kill_backend" statement 1
>  kill_backend
> --
>  [null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:55 [depesz]
> $ select kill_backend();
>  kill_backend
> --
>  [null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:56 [depesz]
> $ select kill_backend();
>  kill_backend
> --
>  [null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:58 [depesz]
> $ select version();
>                                                version
> ---
>  PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.real 
> (Debian 4.6.2-5) 4.6.2, 64-bit
> (1 row)
>
> Side note - definition as is, doesn't work on 9.2:
> $ CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>>   DROP TABLE if EXISTS foo;
>>>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
> ERROR:  language "SQL" does not exist
>
> changing it to proper sql (not uppercase) fixed this problem.
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact with 
> it.
>                                                             http://depesz.com/
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

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

Re: [BUGS] user names & non-ASCII

[Pavel Stehule]

Hello

this is not bug

you can try use a system variable PGCLIENTENCODING

bash-4.2$ PGCLIENTENCODING='latin2' psql postgres
psql (9.1.1)
Type "help" for help.

postgres=# show client_encoding ;
 client_encoding
-
 LATIN2
(1 row)

other way is connect with "only ascii" user name, changing encoding
and changin current role

SET role TO specialcharsrole;

Regards

Pavel Stehule


2011/12/16 Pavel Holec :
> Hi All, please help.
>
> I Have PostgreSQL server 8.4.9 on Linux, database utf-8 and Client app on
> Windows (VC++ and libpq.dll).
> I need to use user account with non-ASCII and PQconnectdb() with
> options="client_encoding=WIN1250" doesn't work.
> SET CLIENT_ENCODING TO 'WIN1250' after PQconnectdb is too late because
> connection fails.
>
> Best regards,
>
> Pavel Holec

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

Re: [BUGS] "like" and "ilike" not working properly on postgresql 9.0.3 where clause, bug fix not mentioned on 9.0.5 either

[Pavel Stehule]

Hello

2011/11/18 Mlungisi Sincuba :
> To whom it may concern,
> like and ilke not working on postgresql 9.0.3. Bug fix is not mentioned on
> 9.0.5 either. The same statement works very well on postgresql 8.4.
> Kind Regards,
> Mlungisi Sincuba

can you send more informations, please? Can you send a related SQL
statement, result from 9.0 and 8.4?

Regards

Pavel Stehule

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

[BUGS] Re: tsearch prefix searching doesn't work when last char of prefix is accent char

[Pavel Stehule]

Hello

sorry, this is not bug, it is based on ispell dictionary behave

Regards

Regards

Pavel Stehule

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

[BUGS] tsearch prefix searching doesn't work when last char of prefix is accent char

[Pavel Stehule]

Hello

I found a following bug

create table n(a varchar);
postgres=# insert into n values('Stěhule'),('Chromečka');


postgres=# select * from n;
 a
───
 Stěhule
 Chromečka
(2 rows)

-- work
postgres=# select * from n where to_tsvector('cs', a) @@
to_tsquery('cs','St:*') ;
a
─
 Stěhule
(1 row)

-- doesn't work
postgres=# select * from n where to_tsvector('cs', a) @@
to_tsquery('cs','Stě:*') ;
 a
───
(0 rows)

-- work again
postgres=# select * from n where to_tsvector('cs', a) @@
to_tsquery('cs','Stěh:*') ;
a
─
 Stěhule
(1 row)

postgres=# select version();
 version
──
 PostgreSQL 9.2devel on i686-pc-linux-gnu, compiled by gcc (GCC) 4.5.1
20100924 (Red Hat 4.5.1-4), 32-bit
(1 row)

postgres=# show server_encoding;
 server_encoding
─────
 UTF8
(1 row)

postgres=# show lc_collate ;
 lc_collate
─
 cs_CZ.utf-8
(1 row)

Regards

Pavel Stehule

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

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Pavel Stehule]

2011/10/20 Tom Lane :
> Pavel Stehule  writes:
>> I didn't design a PERFORM statement. There is two views - somebody
>> from sybase's family know so SELECT without into is forwarded to
>> client. This functionality is missing on Oracle's family. Is true so
>> PERFORM statement is strange,  but maybe it's open door for sybase's
>> functionality that was not implemented ever.
>
> I cannot imagine that we'd ever make SELECT inside a plpgsql function
> act like that.  Functions have no business directly transmitting
> information to the client; if they tried, they'd most likely just break
> the FE/BE protocol.
>
> There might be use for such a thing in a hypothetical "real stored
> procedure language" where the code is executing in a context entirely
> different from what Postgres functions run in ... but that language
> would be something different from plpgsql.
>
> I grant the argument that people coming from Sybase-ish DBs might be
> confused by this; but the current arrangement is also confusing lots
> of people, so I don't think that argument has all that much weight.

I agree with you you in almost all - Sybase-ish SELECT has sense for
procedures only (in PL/pgSQL) - In SQL/PSM is natural for table
functions. I disagree with Merlin or Valentine from one reason - What
is sense of SELECT, that has not processed result? Is it correct from
language design perspective?

I can do a write a query with sense - like SELECT fx(i) FROM
generate_series(1,1000)  or query without sense - like SELECT 1 FROM
generate_series(1,1000) - when we enable a SELECT without INTO. And
there is next question - is first select a good idea - from
readability perspective - in PL/pgSQL - is not better to use a SQL
language??

Regards

Pavel Stehule

p.s. other question is implementation of PERFORM - that is it a just
SELECT synonym,


>
>                        regards, tom lane
>

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

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Pavel Stehule]

2011/10/20 Tom Lane :
> Valentine Gogichashvili  writes:
>> And, ernestly, if it were useless to have SELECT without INTO in plpgsql,
>> there also would be no PERFORM command in plpgsql...
>
> Precisely.  Pavel's claim is nonsense.  The only real question is how
> useful is it to call it PERFORM instead of SELECT.

I didn't design a PERFORM statement. There is two views - somebody
from sybase's family know so SELECT without into is forwarded to
client. This functionality is missing on Oracle's family. Is true so
PERFORM statement is strange,  but maybe it's open door for sybase's
functionality that was not implemented ever.

Regards

Pavel Stehule


>
>                        regards, tom lane
>

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

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Pavel Stehule]

2011/10/20 Merlin Moncure :
> On Thu, Oct 20, 2011 at 2:28 AM, Pavel Stehule  
> wrote:
>>>
>>> it would be really a good idea to allow SELECT without INTO in plpgsql.
>>
>> SELECT without INTO is useless in plpgsql - because you have to drop result.
>
> not if you're calling a function:
> select func();

it is correct just for void function.

Pavel

>
> merlin
>

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

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Pavel Stehule]

2011/10/20 Valentine Gogichashvili :
>>
>> I suppose you could argue that selecting a value and implicitly throwing
>> it away is confusing to novices, but on the other hand I've seen a whole
>> lot of novices confused by the need to write PERFORM instead of SELECT.
>> I think it wouldn't be an unreasonable thing to just interpret a SELECT
>> with no INTO clause as being a PERFORM (ie execute and discard results).
>> Then we'd not have to do anything magic for commands starting with WITH.
>>
>>                        regards, tom lane
>>
>
> it would be really a good idea to allow SELECT without INTO in plpgsql.

SELECT without INTO is useless in plpgsql - because you have to drop result.

regards

Pavel Stehule


> PERFORM just makes things much more complicated, without actually adding any
> really working "protection" from misusing SELECT without INTO.
> With best regards,
> -- Valentin Gogichashvili

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

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Pavel Stehule]

2011/10/19 Tom Lane :
> I wrote:
>> Merlin Moncure  writes:
>>> The point being, how do I convert any query to a non WITH variant so
>>> it can be PERFORM'd?  Anyways, I always thought having to do perform
>>> at all was pretty weak sauce -- not sure why it's required.
>
>> Possibly it was an Oracle compatibility thing ... anyone know PL/SQL
>> well enough to say how this works there?
>
> After writing that, I remembered I had an old PL/SQL manual sitting
> about, so I took a look.  So far as I can see, there is no PERFORM
> statement in PL/SQL, and no SELECT-without-INTO either; that is, the
> functionality of executing a SELECT and discarding the result simply
> isn't there.
>
> So at this point it looks like we made up PERFORM out of whole cloth,
> and we could just as easily choose to do it another way.  Jan, do you
> remember anything about the reasoning for PERFORM?
>

It has a CALL statement, or procedures can be called directly.

Regards

Pavel Stehule

>                        regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] memory leak in postgresql

[Pavel Stehule]

2011/10/11 Tom Lane :
> Pavel Stehule  writes:
>> I found a following issue (tested on PostgreSQL 9.2)
>
>> CREATE OR REPLACE FUNCTION public.setfield(a anyelement, text, text)
>> RETURNS anyelement
>> LANGUAGE plpgsql
>> AS $function$
>> begin
>>   create temp table aux as select $1.*;
>>   execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3);
>>   select into $1 * from aux;
>>   drop table aux;
>>   return $1;
>> end;
>> $function$
>
>> create type mypoint as (a int, b int);
>
>> create table omega(p mypoint);
>
>> insert into omega select mypoint '(10,20)' from generate_series(1,10);
>
>> update omega set p = setfield(p, 'a', '20');
>
>> WARNING:  out of shared memory
>> CONTEXT:  SQL statement "create temp table aux as select $1.*"
>> PL/pgSQL function "setfield" line 3 at SQL statement
>> ERROR:  out of shared memory
>> HINT:  You might need to increase max_locks_per_transaction.
>> CONTEXT:  SQL statement "create temp table aux as select $1.*"
>> PL/pgSQL function "setfield" line 3 at SQL statement
>
> This is not a memory leak, this is a "your transaction is holding too
> many locks" problem (namely, one lock for each transient table).  Please
> follow the advice given in the error message.

ok

On other hand - is necessary to hold a locks for dropped temporary tables?

Regards

Pavel

>
>                        regards, tom lane
>

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

[BUGS] memory leak in postgresql

[Pavel Stehule]

Hello

I found a following issue (tested on PostgreSQL 9.2)

CREATE OR REPLACE FUNCTION public.setfield(a anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
  create temp table aux as select $1.*;
  execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3);
  select into $1 * from aux;
  drop table aux;
  return $1;
end;
$function$

create type mypoint as (a int, b int);

create table omega(p mypoint);

insert into omega select mypoint '(10,20)' from generate_series(1,10);

update omega set p = setfield(p, 'a', '20');

WARNING:  out of shared memory
CONTEXT:  SQL statement "create temp table aux as select $1.*"
PL/pgSQL function "setfield" line 3 at SQL statement
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "create temp table aux as select $1.*"
PL/pgSQL function "setfield" line 3 at SQL statement

Regards

Pavel Stehule

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

[BUGS] bug in dependency - there is possibility to break a system catalog

[Pavel Stehule]

Hello

our customer reported a broken system dictionary. The following
statements breaks a catalog:


postgres=# CREATE TYPE test_type AS (a integer, b integer);
CREATE TYPE
Time: 61,016 ms
postgres=#
postgres=# CREATE FUNCTION test_func (variadic x text[] =
'{NULL}'::"test_type"[])
postgres-# RETURNS pg_catalog.void AS $body$ BEGIN END; $body$
LANGUAGE 'plpgsql';
CREATE FUNCTION
Time: 24,637 ms
postgres=#
postgres=# DROP TYPE test_type;
DROP TYPE
Time: 6,824 ms

postgres=# \df
ERROR:  cache lookup failed for type 16579
postgres=# select version();
 version
──
 PostgreSQL 9.2devel on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.6.0 20110603 (Red Hat 4.6.0-10), 64-bit
(1 row)

Time: 2,021 ms
postgres=#

Regards

Pavel Stehule

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

Re: [BUGS] PG8.4.7: updating rows leaves duplicate rows violating PK

[Pavel Stehule]

2011/8/17 Rainer Pruy :
> Hallo,
> this is strange for sure. The database is in heavy use regularly.
> So it is - if at all - a rare occurrence.
>
> The update statement reported 346305 updated rows.
> And I could verify that this is the number of rows that hat there value
> change
> (where afterwards there was a "new" version in the table.
> Thus, the update statement actually performed its operation.
>
> After eliminating the "offending" rows the index is operational again
> and does not complaining about violations.
> Thus, it is not likely a plain bad PK index.
>
> I already tried to come up with something that could create a false
> positive here,
> but am out of ideas now.

it should be a race condition too.

you can try to use a triggers for identification of place where value
is modified back.

Pavel

>
> Up to now this only happened with said table.
> May be something is bad with the table?
> However, the current instance is nearly a fresh installation of PG
> with data loaded from a pg_dumpall from another instance
> (for some special testing and analysis).
> Thus, I have events with two different instances of PG.
> Something being imported by plain DML operations?
> A strange idea by itself anyway.
>
> Still clueless...
>
> Rainer
>
>
> Am 17.08.2011 13:33, schrieb Pavel Stehule:
>> Hello
>>
>> 2011/8/17 Rainer Pruy :
>>> This is strange and as of now I do not have a reliable way of reproducing.
>>> Nevertheless,
>>> either there is a major blunder on my side that urgently needs being
>>> pointed at and eliminated
>>> or there is something really strange with PG.
>>>
>>> Short version:
>>>
>>> I update some rows of a table changing non-primary key column values.
>>> Afterwards some of the updated rows are returned from a query with
>>> the version from before and after the update.
>>>
>>> Consequently the PK is detected inconsistent later on and errors are
>>> reported accordingly.
>>>
>>>
>> It is strange - are you sure, so UPDATE statement doesn't fail? Are
>> you sure, so UPDATE statement really modified rows?
>> Are you sure, so you are has not a broken index on PK?
>>
>> Regards
>>
>> Pavel Stehule
>>
>>> Longer Version: please see text attachment
>>>
>>>
>>>  server_version                  | 8.4.7
>>>  server_version_num         | 80407
>>>
>>> OS: NetBSD 5.99.38
>>>
>>> Sizes:
>>> account_item    12 GB    6,8079,402 rows
>>>
>>> While the update was executing another process was active that was
>>> issuing a sequence of select.
>>>
>>> Running that very sequence on a copy clone of the database (before the
>>> update)
>>> worked without such effect.
>>>
>>> I had 3 similar occurrences before.
>>> But those were on a DB instance used for development and I could not
>>> verify the primary key was active during update.
>>> Here it is verified it was in place. So the "bad" entries probably could
>>> have been rejected due to PK violation?
>>>
>>> Not much input I can give for decent analysis,
>>> but either someone can point me to the obvious
>>> or it is something thats worth being watched for somehow
>>>
>>> Rainer
>>>
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>>
>

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

Re: [BUGS] PG8.4.7: updating rows leaves duplicate rows violating PK

[Pavel Stehule]

Hello

2011/8/17 Rainer Pruy :
> This is strange and as of now I do not have a reliable way of reproducing.
> Nevertheless,
> either there is a major blunder on my side that urgently needs being
> pointed at and eliminated
> or there is something really strange with PG.
>
> Short version:
>
> I update some rows of a table changing non-primary key column values.
> Afterwards some of the updated rows are returned from a query with
> the version from before and after the update.
>
> Consequently the PK is detected inconsistent later on and errors are
> reported accordingly.
>
>

It is strange - are you sure, so UPDATE statement doesn't fail? Are
you sure, so UPDATE statement really modified rows?
Are you sure, so you are has not a broken index on PK?

Regards

Pavel Stehule

>
> Longer Version: please see text attachment
>
>
>  server_version                  | 8.4.7
>  server_version_num         | 80407
>
> OS: NetBSD 5.99.38
>
> Sizes:
> account_item    12 GB    6,8079,402 rows
>
> While the update was executing another process was active that was
> issuing a sequence of select.
>
> Running that very sequence on a copy clone of the database (before the
> update)
> worked without such effect.
>
> I had 3 similar occurrences before.
> But those were on a DB instance used for development and I could not
> verify the primary key was active during update.
> Here it is verified it was in place. So the "bad" entries probably could
> have been rejected due to PK violation?
>
> Not much input I can give for decent analysis,
> but either someone can point me to the obvious
> or it is something thats worth being watched for somehow
>
> Rainer
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>

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

Re: [BUGS] BUG #6079: Wrong select result

[Pavel Stehule]

Hello

It working as expected on 9.0.4 Linux. Please, can you recheck your
application? Try to use a console - psql,

postgres=# select * from foo where date_to is null and date_to > '2011-01-01';
 id | date_to
+-
(0 rows)

Regards

Pavel Stehule

2011/6/28 Renat :
>
> The following bug has been logged online:
>
> Bug reference:      6079
> Logged by:          Renat
> Email address:      renat.nasy...@itv.ru
> PostgreSQL version: 9.0.4
> Operating system:   Windows
> Description:        Wrong select result
> Details:
>
> INPUT:
>
> create table foo (
> id bigint not null,
> date_to timestamp without time zone,
> CONSTRAINT foo_pkey PRIMARY KEY (id)
> );
>
> CREATE INDEX foo_date_to_index
>  ON foo
>  USING btree
>  (date_to)
>
> insert into foo (id, date_to) values (1, now());
> insert into foo (id, date_to) values (2, NULL);
>
> select * from foo where date_to is null and date_to > '2011-01-01'
>
> Expected: 0 rows
>
> But: it return 1 row with id=2
>
> If we will replace foo_date_to_index to:
>
> CREATE INDEX foo_date_to_index
>  ON foo
>  USING btree
>  (date_to)
>  WHERE date_to is NOT NULL
>
> Then:
>
> SELECT * FROM foo where date_to is null and date_to > '2011-01-01'
>
> Return: 0 rows
>
> Please explain for me what happens?
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] BUG #6067: In PL/pgsql, EXISTS(SELECT ... INTO...) fails

[Pavel Stehule]

Hello

2011/6/18 David Fetter :
>
> The following bug has been logged online:
>
> Bug reference:      6067
> Logged by:          David Fetter
> Email address:      dfet...@vmware.com
> PostgreSQL version: 9.0.4
> Operating system:   Linux
> Description:        In PL/pgsql, EXISTS(SELECT ... INTO...) fails
> Details:
>
> Here's some example code that reproduces the problem:
>
> CREATE OR REPLACE FUNCTION foo()
> RETURNS void
> LANGUAGE plpgsql
> AS $$
> DECLARE
>    i int;
> BEGIN
>    IF EXISTS (SELECT 1 INTO STRICT i) THEN
>        RAISE NOTICE '%', a;
>    END IF;
>    RETURN;
> END;
> $$;
> ERROR:  syntax error at or near "i"
> LINE 8:     IF EXISTS (SELECT 1 INTO STRICT i) THEN

You cannot to use PLpgSQL's addition "INTO" inside SQL statement. This
is not bug. INTO and STRICT are not SQL keywords, so they cannot be
used inside SQL expression.

Regards

Pavel Stehule

>                                            ^
>
> This came up in a case where there was an IF block that checked some
> conditions before checking whether there was a row.  If it found a row, it
> was supposed to use it in an EXCEPTION.  Instead, I had to do the query
> unconditionally, check the FOUND block in a separate nested IF statement,
> and generally uglify the code.
>
> Not everybody in IRC agreed that this is a bug, though.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] BUG #6043: Compilation PLpgsql Succesful but execution bad

[Pavel Stehule]

2011/5/28 Emanuel Calvo :
>>>
>>> Thanks Heikki for your fast response! ^^
>>>
>>>
>>>> The compiler would have to determine that the loop never ends, or it
>>>> would complain that there's no RETURN at the end.
>>>>
>>>> Many compilers for other languages do that kind of analysis, but it
>>>> usually only results in a warning, and compilers sometimes get that
>>>> wrong. I don't think it's worthwhile to do that, but of course, patches
>>>> are welcome.
>>>>
>>>
>>> Yeah, it's not a very big concern, althougth cold be taken for future
>>> improvements
>>> in plpgsql. I very far for submit a patch :P
>>>
>>
>> The deep check of embedded SQL is not possible in PL/pgSQL -  this
>> remove dependency between PL/pgSQL and database objects. Deeper checks
>> mean a broken compatibility :(.
>>
>
> Good point.
>
>> PL/PSM has different philosophy where full check is implemented now.
>>
>
> Do you think that make some test in 9.1  worthwhile for this
> language? I see that the last contrib was submitted years ago.
>

I worked on new implementation called PL/PSM - but it is not mature.
It should to work with 9.1.

https://github.com/okbob/plpsm0

all features https://github.com/okbob/plpsm0/blob/master/test.sql are
supported, but there are no real project that is based on this
language now.

Regards

Pavel Stehule

> Regards,
>
>
> --
> --
>               Emanuel Calvo
>               Helpame.com
>

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

Re: [BUGS] BUG #6043: Compilation PLpgsql Succesful but execution bad

[Pavel Stehule]

2011/5/28 Emanuel Calvo :
> El 27/05/2011 16:18, "Heikki Linnakangas"
>  escribió:
>> On 27.05.2011 17:05, Emanuel wrote:
>>> postgres=# CREATE OR REPLACE FUNCTION p_() RETURNS TABLE (i int) AS $$
>>> DECLARE
>>> BEGIN
>>> SELECT * FROM p; --<<<-- here must ne RETURN QUERY ..
>>> END;
>>> $$ LANGUAGE plpgsql;
>>> CREATE FUNCTION
>>> postgres=# select p_();
>>> ERROR: query has no destination for result data
>>> HINT: If you want to discard the results of a SELECT, use PERFORM
>>> instead.
>>> CONTEXT: PL/pgSQL function "p_" line 4 at SQL statement
>>>
>>> I don't know if it's really a bug or a feature request. But seems that
>>> the
>>> function compiles well without checking the existence of a RETURN QUERY.
>>> I
>>> think the best in this cases is raise an error during compilation.
>
> Thanks Heikki for your fast response! ^^
>
>
>> The compiler would have to determine that the loop never ends, or it
>> would complain that there's no RETURN at the end.
>>
>> Many compilers for other languages do that kind of analysis, but it
>> usually only results in a warning, and compilers sometimes get that
>> wrong. I don't think it's worthwhile to do that, but of course, patches
>> are welcome.
>>
>
> Yeah, it's not a very big concern, althougth cold be taken for future
> improvements
> in plpgsql. I very far for submit a patch :P
>

The deep check of embedded SQL is not possible in PL/pgSQL -  this
remove dependency between PL/pgSQL and database objects. Deeper checks
mean a broken compatibility :(.

PL/PSM has different philosophy where full check is implemented now.

Regards

Pavel Stehule

> Regards,
> E
>

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

[BUGS] documentation bug - behave of NEW a OLD in plpgsql's triggers

[Pavel Stehule]

Hello

one czech user reported a bug in documentation -
http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html

NEW

Data type RECORD; variable holding the new database row for
INSERT/UPDATE operations in row-level triggers. This variable is NULL
in statement-level triggers and for DELETE operations.
OLD

Data type RECORD; variable holding the old database row for
UPDATE/DELETE operations in row-level triggers. This variable is NULL
in statement-level triggers and for INSERT operations.

It isn't correct. NEW is not declared in DELETE trigger, OLD isn't
declared in INSERT

Regards

Pavel Stehule

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

Re: [BUGS] Postgres not using indexes

[Pavel Stehule]

Hello

2011/3/30 Lawrence Cohan :
> We have a huge performance issues in Postgres that surfaced due to existing
> indexes not being used like in the example below in both 8.35 and 9.0
> versions.
>
>
>
> Client_Orders table with and int ID as PK which is the order_id and indexed
> – about 155,000 rows
>
> Order_Items table with and int ID primary key and INDEX on Order_id (int)
> matching the ID in the above client_orders table. – about 33 million rows
>
>
>
> A query like below takes almost ten minutes to complete however the result
> set is 33768 rows and Total query runtime: 427539 ms.!!! This is due to the
> fact that the index on Order_Items it is NOT used and a sequence scan is
> done instead but this is obviously not acceptable from performance point of
> view. If I add a LIMIT 1000 for instance then the index is used and query
> returns results in no time as expected but as soon as I go higher in the
> limit to a few thousands then the index on Order_Items.Order_id is no longer
> used – why??? Is there any way to force Postgres to use the existing indexes
> instead of table seq scan which is deadly?
>
>
>
> select oi.id from order_items oi INNER JOIN client_orders co ON oi.order_id
> = co.id
>
>

Do you do a ANALYZE and VACUUM. Can you send a result of EXPLAIN
ANALYZE SELECT ...

Please, do ANALYZE and VACUUM first.

regards

Pavel Stehule

>
> Regards,
>
> Nenea Nelu.
>
>
>
> 
> Attention:
> The information contained in this message and or attachments 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 system and destroy any copies.
>

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

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Pavel Stehule]

Hello

why you can do it?

please, try to RETURN QUERY ...

Regards

Pavel Stehule


>
> $$begin
>
> perform(
>
> with A as (select generate_series(1,3) as foo)
>
> select foo from A
>
> );
>
> end$$;
>
>

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

Re: [BUGS] BUG #5867: wish: plpgsql print table for debug

[Pavel Stehule]

2011/3/3 Dimitri Fontaine :
> Tom Lane  writes:
>> Note that doing anything more than RAISE NOTICE or equivalent would
>> imply a significant protocol change.
>
> My understanding is that the standard allows multiple resultsets per
> query, is that the protocol change you're talking about?
>

There is nothing similar in standard. Multirecordset is nice, but not
standard feature.

Regards

Pavel Stehule


> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] BUG #5867: wish: plpgsql print table for debug

[Pavel Stehule]

2011/3/3 Richard Neill :
>
>> Sure it does.  You can pass the tuple to RAISE NOTICE easily enough.
>> It won't have all the same bells and whistles psql would supply, but
>> it prints out well enough for debugging.  Or at least it's never
>> bothered me.
>
> Sorry if I'm being dense, but I can't see how you can pass a tuple; I think
> raise-notice only lets you pass individual strings/integers. But I don't
> think we can pass all of them without specifying in advance how many there
> are....

yes, it's possible for ROW or RECORD datatype

Regards

Pavel Stehule

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

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

Re: [BUGS] BUG #5867: wish: plpgsql print table for debug

[Pavel Stehule]

Hello

>
> Do you not think it would be really amazingly useful? After all, in C, the
> single most useful debugging tool is "fprintf(stderr,...)", and yet
> postgresql doesn't have an equivalent that can operate on the most common
> data format. [I'm stretching the analogy a bit here, but it seems to me that
> a multi-row table is to postgresql as int is to C.]

it's nonsense - PL/pgSQL is procedural language - so there are same -
similar types like C

>
> There are a lot of people who would benefit from it, most of whom (including
> me) don't really have the expertise to do it well.
>

I don't think so we need a special enhancing of RAISE statement. What
is a problem on lines

FOR r IN SELECT ... LOOP
  RAISE NOTICE r;
END LOOP;

???

> Also, there is a lot of value in being able to debug as needed with a 1-line
> debugging statement, then get back to the problem at hand, rather than
> having to break out of the current programming task to write a debug
> function :-)
>

CREATE OR REPLACE FUNCTION debug_query(text)
RETURNS void AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE $1 LOOP
RAISE NOTICE r;
  END;
END;
$$ LANGUAGE plpgsql;

Regards

Pavel Stehule


> Thanks very much,
>
> Richard
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] BUG #5884: i cant select entire array values

[Pavel Stehule]

2011/2/14 Kevin Grittner :
> Pavel Stehule  wrote:
>
>> p.s. Using a case sensitive identifiers isn't good idea
>
> Is there any reason for that assertion besides the need to
> consistently quote them?
>

readability - and when you are started quoting, then you have to use
quotes everywhere.

Readability is subjective argument, I know.

Pavel




> -Kevin
>

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

Re: [BUGS] BUG #5884: i cant select entire array values

[Pavel Stehule]

Hello

2011/2/14 yashar ebrahimpoor :
>
> The following bug has been logged online:
>
> Bug reference:      5884
> Logged by:          yashar ebrahimpoor
> Email address:      ebrahimpoor.yas...@gmail.com
> PostgreSQL version: 8.4.7
> Operating system:   centos 5.5
> Description:        i cant select entire array values
> Details:
>
> CREATE TABLE "flight"."FlightList" (
>  "FlighID" INTEGER DEFAULT nextval('flight.flist_flightid_seq'::regclass)
> NOT NULL,
>  "FlightDate" DATE[] NOT NULL,
>  "FlightNum" VARCHAR[] NOT NULL,
>  "FlightTime" TIME WITHOUT TIME ZONE[] NOT NULL,
>  "FlightDuration" VARCHAR[] NOT NULL,
>  CONSTRAINT "FlightList_pkey" PRIMARY KEY("FlighID")
> ) WITH OIDS;
>
> after create this table, insert some data
> INSERT INTO
> "flight"."FlightList"("FlightDate","FlightNum","FlightTime","FlightDuration"
> )
> VALUES(ARRAY["date"('2011-10-10'),"date"('2011-10-15')],
> ARRAY['230','231'],
> ARRAY["time"('21:00:00'),"time"('16:25:00')],
> ARRAY['2:30','2:35']);
>
> now i want to execute this query:
> select "FlightNum[0]" FROM flight."FlightList";
>
> return this error:
>
> ERROR:  column "FlightNum[0]" does not exist
> LINE 1: select "FlightNum[0]" FROM flight."FlightList"
>

please, try to SELECT "FlightNum"[0] FROM FlightList

Pavel Stehule

p.s. Using a case sensitive identifiers isn't good idea






> please help to resolve this problem.
> thank you so much.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] Feature request: include script file into function body

[Pavel Stehule]

2011/2/2 Tom Lane :
> Pavel Stehule  writes:
>> probably you need a third form of expansion - not implemented yet
>> ":$$var$$
>
> Seems quite useless.  A string literal is a string literal.
>

I don't propose this form now. I saying so this form can be usefull for Steve.

It appends a started and ending tags, but it does not a double or
single quotes escaping.

I still don't think so Steve's idea is good. It does very unstable mix

Pavel


>                        regards, tom lane
>

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

Re: [BUGS] Feature request: include script file into function body

[Pavel Stehule]

Hello

probably you need a third form of expansion - not implemented yet

":$$var$$

escaping :'xxx' is designed for SQL language, not for Python :(

Regards

Pavel

2011/2/2 Steve White :
> Hi, Robert,
>
> On  1.02.11, Robert Haas wrote:
>>
>> Can't you already do it this way:
>>
>> \set yadda `cat yadda_yadda.py`
>> CREATE FUNCTION yadda_yadda() returns text language plpythonu AS
>> :'yadda';
>>
>> I guess it probably won't work on Windows...
>>
> This would also satisfy my immediate needs...
> if only I could get it to work.
>
> I made a file 'yadda_yadda.py' containing only the line:
> print 'hello world'
>
> 
> d=# \set yadda `cat yadda_yadda.py`
> d=# \echo :yadda
> print 'hello world'
> 
>
> So far, so good.
>
> But the :'yadda'; produces an error--it seems the variable yadda isn't
> expanded in the presence of the quotes.
>
> 
> d=# CREATE FUNCTION yadda_yadda() returns text language plpythonu AS
> :'yadda';
> ERROR:  syntax error at or near ":"
> LINE 2: :'yadda';
> 
>
> Without the quotes, the colon expands the variable, but not into a
> string function body:
>
> 
> d=# CREATE FUNCTION yadda_yadda() returns text language plpythonu AS
> :yadda;
> ERROR:  syntax error at or near "print"
> LINE 2: print 'hello world';
> 
>
> Just for completeness and blind optimism let's try putting string
> delimiters on the outside.  The command succeeds but with the wrong
> effect.
>
> 
> d=# CREATE OR REPLACE FUNCTION yadda_yadda() returns text language plpythonu 
> AS
> $$:yadda$$;
> CREATE FUNCTION
> cepheids=# \df+ yadda_yadda
>                                                             List of functions
>  Schema   |    Name     | Result data type | Argument data types |  Type  | 
> Volatility |  Owner   | Language  | Source code | Description
> ---+-+--+-+++--+---+-+-
>  astronomy | yadda_yadda | text             |                     | normal | 
> volatile   | cepheids | plpythonu | :yadda      |
> 
>
>
>
> Please explain.
>
> --
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
> | Steve White                                             +49(331)7499-202
> | E-Science                                        Zi. 27  Villa Turbulenz
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
> | Astrophysikalisches Institut Potsdam (AIP)
> | An der Sternwarte 16, D-14482 Potsdam
> |
> | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
> |
> | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] Feature request: include script file into function body

[Pavel Stehule]

Hello

2011/2/1 Steve White :
> Hi Tom,
>
> This seems like a detail that is beside the point I'm making.
> But security is important, so let's think about it.
>
> PostgreSQL has an \i command, which loads the text from any readable file
> interpretes and executes it as further PostgreSQL commands.  I'm proposing
> a similar mechanism that would load a file containing script language, and
> process it as though it were in the current funcition body.
>
> Isn't the \i command a similar security hole?

if you ran  psql under "postgres" account, then it is.

I don't think, so your idea is good too. What about caching? Code of
stored procedures stays in session cache. Who will ensure, so your
cache is fresh?

Why you need a direct link to source files?

Regards

Pavel Stehule

>
> If somehow loading script text for a function is substantially different
> from loading it by \i, and if there is some problem, it seems to me that
> some simple restriction could solve it, such as restricting the directories
> from which such files can be read.  But I'm just guessing here.
>
> I'll leave it to the security experts explicitly by amending my original
> proposal with this:
>
>        " -- without doing anything stupid that would open a security hole."
>
> Cheers again!
>
>
> On  1.02.11, Tom Lane wrote:
>> Steve White  writes:
>> > It would be really nice to have a way to load script (especially Python
>> > and Perl) from a separate file into a function body.
>>
>> This seems like a security hole, ie, you could use it to read any file
>> the backend has access to.
>>
>>                       regards, tom lane
>>
>
> --
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
> | Steve White                                             +49(331)7499-202
> | E-Science                                        Zi. 27  Villa Turbulenz
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
> | Astrophysikalisches Institut Potsdam (AIP)
> | An der Sternwarte 16, D-14482 Potsdam
> |
> | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
> |
> | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

[BUGS] statement EXECUTE isn't possible call from SPI_execute_with_args function

[Pavel Stehule]

Hello

I can't to execure a prepared statement with one parameter via
SPI_execute_with_args function.

pavel=# select name, statement from pg_prepared_statements ;
 name │   statement
──┼
 xx   │ PREPARE "xx" AS select 10 + $1
(1 row)

 EXECUTE "xx"($1)

ERROR:  there is no parameter $1 - but function is called with one parameter

Regards

Pavel Stehule

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

Re: [BUGS] BUG #5831: Splitting string into char array with string_to_array does not work

[Pavel Stehule]

2011/1/11 Splitting string into char array with string_to_array
:
>
> The following bug has been logged online:
>
> Bug reference:      5831
> Logged by:          Splitting string into char array with string_to_array
> Email address:      val...@gmail.com
> PostgreSQL version: 9.0.1
> Operating system:   Ubuntu/Linaro 4.4.4-14ubuntu5
> Description:        Splitting string into char array with string_to_array
> does not work
> Details:
>
> Hi,
>
> I cannot really remember now for sure, but the splitting string into array
> using an empty string ('') worked for me on pre-9.0 versions to get array of
> string chars. Now I have the following behavior:

Hello

it's newer worked like you remember

postgres=# select regexp_split_to_array('abc','');
 regexp_split_to_array
---
 {a,b,c}
(1 row)

Regards

Pavel Stehule


>
>    postgres=# select string_to_array('ab', '' ),
> regexp_split_to_array('ab', '' );
>     string_to_array │ regexp_split_to_array
>
> ─┼───â
> ��───
>     {ab}            │ {a,b}
>    (1 row)
>
> Why an empty string given as a separator to string_to_array is not splitting
> the string at all?
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] BUG #5801: characters not encoded properly for column names

[Pavel Stehule]

Hello Marc,

It was entered from a windows cmd console? It doesn't use win
encodings as default. For example, it must be executed with parameter
/c chcp 1250 for win1250 encoding.

Regards

Pavel Stehule

2010/12/23 Marc Cousin :
>
> The following bug has been logged online:
>
> Bug reference:      5801
> Logged by:          Marc Cousin
> Email address:      cousinm...@gmail.com
> PostgreSQL version: 9.0.2
> Operating system:   Windows XP
> Description:        characters not encoded properly for column names
> Details:
>
> I get a different behaviour between a Linux and a Windows server, when a
> user creates an accentuated column name.
>
> All tests below were done with a linux psql client, the console being set on
> win1252 charset (so the input character is truly 'é' in win1252)
>
> With the Linux server :
> marc=# SET client_encoding TO 'win1252';
> SET
> marc=# CREATE TABLE test (nom varchar, prénom varchar);
> CREATE TABLE
>
>
> marc=# \d test
>
>
>          Table "public.test"
>
>
>  Column |       Type        | Modifiers
>
>
> +---+---
>
>
>  nom    | character varying |
>
>
>  prénom | character varying |
>
> 'prénom' is also displayed correctly if client_encoding and console are
> UTF8, so the conversion is good.
>
> With the Windows server :
> test=# SET client_encoding TO 'win1252';
> SET
> test=# CREATE TABLE test (nom varchar, prénom varchar);
> CREATE TABLE
> test=# \d test
> ERROR:  invalid byte sequence for encoding "UTF8": 0xe3a96e
> test=# SELECT attname from pg_attribute where attrelid = (select oid from
> pg_class where relname = 'test');
> ERROR:  invalid byte sequence for encoding "UTF8": 0xe3a96e
> test=# select version();
>                           version
> -
>  PostgreSQL 9.0.2, compiled by Visual C++ build 1500, 32-bit
> (1 row)
>
>
> The main reason that this is a problem is that the table cannot be pg_dumped
> anymore because of this.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] BUG #5776: Unable to create view with parameter in PL/pgsql

[Pavel Stehule]

Hello

2010/12/14 Andrey G. :
> Pavel,
>
> As far as I'm aware, SETOF return creates whole result in memory, what
> has some implications.
>

it's not true for immutable SQL function - look on EXPLAIN

> My intention was to create a temporary view in session, which can be
> used in other processing. Of course, I've found another solution, but
> creating view with parameter dynamically could be a good feature.
>

It's done - SQL immutable function works exactly like you need.

Regards

Pavel Stehule


> Andrey
>
>
>
> 2010/12/14 Pavel Stehule :
>> Hello
>>
>> View must not has a parameter in PostgreSQL. You can use a SRF function:
>>
>> postgres=# create or replace function parametrized_view(a int)
>>                  returns setof foo as $$
>>                     select * from foo where a = $1;
>>                  $$ language sql immutable;
>> CREATE FUNCTION
>> postgres=# select * from parametrized_view(10);
>>  a
>> 
>>  10
>> (1 row)
>>
>> postgres=# explain select * from parametrized_view(10);
>>                          QUERY PLAN
>> ------
>>  Index Scan using aa on foo  (cost=0.00..8.27 rows=1 width=4)
>>   Index Cond: (a = 10)
>> (2 rows)
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>> 2010/12/13 Andrey G. :
>>> It seems my original test, which also includes the EXECUTE approach,
>>> has not come to you in full. EXECUTE statement also fails with
>>> parameter: The test is attached in file.
>>>
>>> psql -q < db/db/pgbug_5776.sql
>>> ERROR:  there is no parameter $1
>>> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
>>>   ^
>>> QUERY:  CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
>>> CONTEXT:  PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at
>>> EXECUTE statement
>>>
>>> Andrey
>>>
>>>
>>> 2010/12/13 Robert Haas 
>>>>
>>>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin  wrote:
>>>> >
>>>> > The following bug has been logged online:
>>>> >
>>>> > Bug reference:      5776
>>>> > Logged by:          Andrey Galkin
>>>> > Email address:      andv...@gmail.com
>>>> > PostgreSQL version: 9.0.1
>>>> > Operating system:   Debian unstable
>>>> > Description:        Unable to create view with parameter in PL/pgsql
>>>> > Details:
>>>> >
>>>> > Below is simple test case. Perhaps, I'm doing something wrong.
>>>>
>>>> You can accomplish what you're trying to do using EXECUTE.
>>>>
>>>> --
>>>> Robert Haas
>>>> EnterpriseDB: http://www.enterprisedb.com
>>>> The Enterprise PostgreSQL Company
>>>
>>>
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>>
>>
>

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

Re: [BUGS] BUG #5776: Unable to create view with parameter in PL/pgsql

[Pavel Stehule]

Hello

View must not has a parameter in PostgreSQL. You can use a SRF function:

postgres=# create or replace function parametrized_view(a int)
  returns setof foo as $$
 select * from foo where a = $1;
  $$ language sql immutable;
CREATE FUNCTION
postgres=# select * from parametrized_view(10);
 a

 10
(1 row)

postgres=# explain select * from parametrized_view(10);
  QUERY PLAN
--
 Index Scan using aa on foo  (cost=0.00..8.27 rows=1 width=4)
   Index Cond: (a = 10)
(2 rows)

Regards

Pavel Stehule


2010/12/13 Andrey G. :
> It seems my original test, which also includes the EXECUTE approach,
> has not come to you in full. EXECUTE statement also fails with
> parameter: The test is attached in file.
>
> psql -q < db/db/pgbug_5776.sql
> ERROR:  there is no parameter $1
> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
>   ^
> QUERY:  CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
> CONTEXT:  PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at
> EXECUTE statement
>
> Andrey
>
>
> 2010/12/13 Robert Haas 
>>
>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin  wrote:
>> >
>> > The following bug has been logged online:
>> >
>> > Bug reference:      5776
>> > Logged by:          Andrey Galkin
>> > Email address:      andv...@gmail.com
>> > PostgreSQL version: 9.0.1
>> > Operating system:   Debian unstable
>> > Description:        Unable to create view with parameter in PL/pgsql
>> > Details:
>> >
>> > Below is simple test case. Perhaps, I'm doing something wrong.
>>
>> You can accomplish what you're trying to do using EXECUTE.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>

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

Re: [BUGS] variadic flag doesn't work with "any" type

[Pavel Stehule]

2010/12/10 Tom Lane :
> Pavel Stehule  writes:
>> 2010/12/9 Tom Lane :
>>> What exactly is the use-case for that?
>
>> I am working on function that can help with record updating. It's
>> based on polymorphic types. I would to allow a multiple modification
>> per one call - like UPDATE statement does.
>> some like:
>> record_set_fields(anyelement, key text, value "any" [, key text, value
>> "any" [..]]) returns anyelement
>
> OK, makes sense, since you don't want to constrain the values to be all
> the same datatype.

ok, can I send a patch?

Pavel

>
>> sometimes can be interesting to use a VARIADIC value - so list of
>> pairs (key,  value) can be created dynamically - (now I don't talk if
>> this is good way or not).
>
> That can't work unless you constrain all the values to be text (to match
> the column-name parameters), which more or less defeats the entire point
> of the function.  So I see no interesting use-case for VARIADIC here.
>
>                        regards, tom lane
>

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

Re: [BUGS] variadic flag doesn't work with "any" type

[Pavel Stehule]

2010/12/9 Tom Lane :
> Pavel Stehule  writes:
>> I didn't explain it well, sorry
>
>> so I have a function foo(variadic "any")
>
>> usual calling like foo(10,20) or foo('a',10) working perfectly. But I
>> have a problem with call with VARIADIC keyword
>
>> like foo(VARIADIC ARRAY[10,20]) or foo(VARIADIC ARRAY['a','10']).
>
>> Keyword VARIADIC is allowed, and this a calling doesn't raise any
>> error.
>
> What exactly is the use-case for that?  You can't expect that an array
> will hold the parameter list, since the parameters might not be all the
> same type.
>

I am working on function that can help with record updating. It's
based on polymorphic types. I would to allow a multiple modification
per one call - like UPDATE statement does.

some like:

record_set_fields(anyelement, key text, value "any" [, key text, value
"any" [..]]) returns anyelement

because we cannot work with pairs of params I designed interface

CREATE FUNCTION record_set_fields(anyelement, VARIADIC "any") RETURNS anyelement

most often case working well:

SELECT record_set_fields(row(0,0,0,0,0,0), 'f1', 10, 'f2', 20, 'f3', 30)

I prefer "any" type because a user must not use a quotes for values -
and I am able to do late conversion based on record tuple desc. But
sometimes can be interesting to use a VARIADIC value - so list of
pairs (key,  value) can be created dynamically - (now I don't talk if
this is good way or not). And because we have a VARIADIC value for
VARIADIC parameter, it should be no problem - It works for non "any"
types now.

I would to do some like:

DECLARE change_set text[];
BEGIN
   change_set = ARRAY(SELECT CASE WHEN i % 2 = 1 THEN 'f' || (i / 2)
ELSE (-100)::text END FROM generate_series(1,10) g(i));
   -- change_set = {f0,-100,f1,-100,f2,-100,f3,-100,f4,-100}
  NEW := record_set_fields(NEW, VARIADIC change_set);
 ...

 RETURN NEW;

but  NEW := record_set_fields(NEW, VARIADIC change_set) doesn't work
now - because keyword VARIADIC hasn't necessary effect for "any" type.
It's just quietly ignored. Isn't a problem to expand array inside
custom function - but I don't have a info if VARIADIC keyword was used
or not.

From some custom function's perspective there isn't difference between
(when "any" type is used):

SELECT foo(VARIADIC arrayval) and SELECT foo(arrayval).

I don't know when I have to raise exception and when I have to expand array.

Regards

Pavel

p.s. I found a workaroud - but it's a workaround and needs twice code.
I have to define second record_set_fields(anyelement, VARIADIC
anyarray), and this variant is chosen when VARIADIC value is used. But
I afraid about stability of this solution.




>>               CALL foo(VARIADIC ARRAY[10,20,20]) ---> real
>> call foo(10,20,20) -- but it doesn't work now.
>
> I'm not convinced it should work that way.  Even if you had convinced me
> that this was sensible and had a real use-case, making it work like that
> would take a whole bunch of mechanism that doesn't exist.
>
>                        regards, tom lane
>


pstcoll.tgz
Description: GNU Zip compressed data

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

Re: [BUGS] variadic flag doesn't work with "any" type

[Pavel Stehule]

2010/12/9 Tom Lane :
> Pavel Stehule  writes:
>> There is missing expansion for variadic parameter from any array to
>> real parameters when variadic type is "any".
>
> That's not a bug, it's intentional.  Variadic any is intended to let the
> C function accept any old parameter list.  We should not assume that we
> should do something special with a parameter that happens to be an array.
>
> Possibly variadic anyarray will do what you are after.

I didn't explain it well, sorry

so I have a function foo(variadic "any")

usual calling like foo(10,20) or foo('a',10) working perfectly. But I
have a problem with call with VARIADIC keyword

like foo(VARIADIC ARRAY[10,20]) or foo(VARIADIC ARRAY['a','10']).

Keyword VARIADIC is allowed, and this a calling doesn't raise any
error. But there isn't transformation to standard parameters. It's in
negation to other than "any" types. And because parser doesn't expand
array to parameters and just ignore VARIADIC keyword I am must not fix
it inside custom function.

so:

   FUNCTION foo(VARIADIC text[])
 CALL foo(10,20,20) ---> real call
foo(ARRAY['10','20','20']);
 CALL foo (VARIADIC ARRAY['10','20','20']) --->
real call foo(ARRAY['10','20','20'])

-- same mechanism should be for "any" type - in reverse order

FUNCTION foo(VARIADIC "any")
  CALL foo(10,20,20)  ---> real call foo(10,20,20);
  CALL foo(VARIADIC ARRAY[10,20,20]) ---> real
call foo(10,20,20) -- but it doesn't work now.

Pavel



>
>                        regards, tom lane
>

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

[BUGS] variadic flag doesn't work with "any" type

[Pavel Stehule]

Hello

There is missing expansion for variadic parameter from any array to
real parameters when variadic type is "any".

It can be solved on C level, but in this moment we have not a access
to parser, so we don't know, if variadic flag was used or not.

Regards

Pavel Stehule

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

Re: [BUGS] BUG #5753: Existing Functions No Longer Work

[Pavel Stehule]

2010/11/16 vince maxey :
> Here is my website:  www.emenusonline.net  It appears that the pg version my 
> web
> host company uses is 8.1.3
>
> Right now this site is in beta mode, so you can register and order food
> and there will be no repercussions, other than you will receive an email
> indicating you did so.  (No money will be collected, cc information or 
> anything
> - use fake everything other than email address)
>
> If you were to register on this site and then log in, the following screen 
> shot
> is of the function that would be called to determine whether you are an 
> existing
> valid user or not.  This site exists for real, gentlemen and it works; or 
> after
> you register you will never be able to log back in to the site.
>
> I reviewed the documenation referenced by Pavel and if I'm understanding it,
> this is only to invoke a cursor from the pgAdmin GUI, correct?  So instead of
> entering ' select get_user_login_info('vamax27') '  I should type:   FETCH ALL
> IN "";
>
> If that is the case then why is it that each time I type select
> get_user_login_info('vamax27') the result increments by one:   1>
> ,  ,    etc?
>
> portal  NOT  cursor
>
> I have lots of these functions written using the identical syntax in regard to
> refcursors and they all work on this site.  I know my data is set up properly
> and I know my code is good.
>
> Screenshot2 is the results of my running select refcursor_function  and
> screenshot3 is the results of my running fetch all in  and
> finally screenshot4 is my trying to run fetch all in
> get_user_login_info('vamax27').  These are all being run from phppgAdmin on my
> web host provider's site.
>
> With all due respect, guys, what you are telling me doesn't add up.

you cannot to fetch data from function that returns a refcursor.

refcursor is varchar - name of some object (cursor). when you open
cursor, then you can specify a name (it's refcursor), but when you
fetch data, you must to use a direct sql identifier of cursor - not a
reference.

simply, you can't to write

fetch all from function(parameter);

Pavel


>
> It would take you minutes to set up the test data I provided and run the
> test function I submitted and either prove or disprove what I am saying; or
> re-write the syntax so that it does return results and show me how I'm a fool
> and wasting your time; at which point I will apologize profusely :)
>
> I'm not trying to give you a hard time or make you do work I should be able to
> do for myself.  I'm out of ideas, Tom.
>
> Sincerely,
>
> Vince Maxey
>
>
>
> - Original Message 
> From: Tom Lane 
> To: vince maxey 
> Cc: pgsql-bugs@postgresql.org
> Sent: Tue, November 16, 2010 11:31:28 AM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> vince maxey  writes:
>> My functions all worked prior to switching to 9.0 and I can excute functions
>> from the pgAdminIII UI which return an individual result, such as an integer,
>> but cannot test/troubleshoot those which use refcursors in the same manner; 
>> and
>>
>> they are not working within my application.  They used to work.
>
> That's basically impossible to believe, because the behavior on this
> point didn't change.  There are certainly some incompatibilities between
> 9.0 and previous releases, but nothing about refcursor-returning
> functions specifically; and AFAICT the example functions you provided
> work just the same in 9.0 as before.  So I think you're barking up the
> wrong tree and the issue is something else than you think.
>
> Perhaps you could put together a complete test case (not just the
> function, but including its call) that works in 8.4 and not in 9.0?
>
> BTW, if the gripe is specifically about what happens in the pgAdmin UI,
> another possible explanation is that pgAdmin changed.  Can you reproduce
> a change of behavior using just psql?
>
>             regards, tom lane
>

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

Re: [BUGS] BUG #5753: Existing Functions No Longer Work

[Pavel Stehule]

hello

2010/11/16 vince maxey :
> Tom, or anyone else working with this dB, can you respond to my question:
>
> How should the syntax for a function be formulated to return a refcursor
> containing one or more records?
>

http://www.network-theory.co.uk/docs/postgresql/vol2/ReturningCursors.html

I check this sample for more values

postgres=# select * from test;
 col
-
 123
 333
(2 rows)

postgres=# begin;
BEGIN
postgres=# select reffunc('cursorname');
  reffunc

 cursorname
(1 row)

postgres=# fetch all in cursorname;
 col
-
 123
 333
(2 rows)

postgres=# commit;
COMMIT

Regards

Pavel Stehule


>
> I have many years SQL development experience and work with Oracle in my 
> current
> position.  I'm not a novice programmer.
>
> My functions all worked prior to switching to 9.0 and I can excute functions
> from the pgAdminIII UI which return an individual result, such as an integer,
> but cannot test/troubleshoot those which use refcursors in the same manner; 
> and
> they are not working within my application.  They used to work.
>
> I've provided test data and functions for your inspection and validation.  
> Even
> pointing me to some substantial documentation (white paper or actual book) 
> that
> contains bonafide examples of how to write postgresql functions would probably
> help.  But simply providing syntax segments is not working,  I've not come
> across any examples that I can translate or compare with my existing efforts.
>
> According to your documentation, new releases should be backward compatible;
> other than for specific elements.  I would think this particular functionality
> should be backward compatible but as I'm finding it not to be, please take 
> some
> time to investigate and validate for yourselves what I have communicated.
>
> I really do think postgreSQL is a great database from a development
> perspective.  If I can get over this issue, perhaps I can provide some
> documentation which others can use to create their own functions.
>
> thank you for your assistance.
>
>
> - Original Message 
> From: vince maxey vama...@yahoo.com
> To: Tom Lane 
> Cc: Me Yahoo ; pgsql-bugs@postgresql.org
> Sent: Sat, November 13, 2010 3:44:03 PM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> Thanks for your response, Tom.
>
> I guess my question would be, what needs to change in my syntax to expect to 
> get
>
> one row returned?
>
> Here are a couple of examples that do work in my existing application prior to
> my recent computer switch and re-build (and I have well over 100 of these 
> types
> of functions defined, some more complex than others, but I figured a simple
> example would help someone else to most easily be able to help me).
>
>
> -- Function: dimension.get_location_holiday(bigint)
> -- DROP FUNCTION dimension.get_location_holiday(bigint);
> CREATE OR REPLACE FUNCTION dimension.get_location_holiday(bigint)
>   RETURNS refcursor AS
> $BODY$
> DECLARE
>
>  loc refcursor;
> BEGIN
>  open loc for
>   select * from dimension.location_holiday where holidayid = $1;
>  return loc;
> END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
> ALTER FUNCTION dimension.get_location_holiday(bigint) OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO public;
> GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO postgres;
>
>
> -- Function: dimension.get_location_list(character varying, character varying,
> integer)
> -- DROP FUNCTION dimension.get_location_list(character varying, character
> varying, integer);
> CREATE OR REPLACE FUNCTION dimension.get_location_list(character varying,
> character varying, integer)
>   RETURNS refcursor AS
> $BODY$
> DECLARE
>  loc refcursor;
> BEGIN
>  IF $3 = 1 THEN
>   open loc for
>   select a.locationid, a.locationname, a.partnerid, b.partnername,
> a.phone1,a.phone2,
>
>   a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.city||',
> '||e.statecode||'  '||e.zipcode,
>   a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.timezone,
> a.taxrate, e.statecode,a.faxflag,
>   a.ticklerflag,case when a.ticklerflag = 't' then 'YES' else 'NO' end, e.city
>   from dimension.location_base a, dimension.partner b, postal.us_zip e
>   where a.partnerid = b.partnerid and a.physcityid = e.zipid and e.statecode =
> $2 order by a.locationname;
>  ELSE
>   IF $3 = 0 THEN
>    open loc for
>    select a.locationid, a.locationname, a.partnerid, b.partnername,
> a.phone1,a.phone2,
>
>    a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityi

Re: [BUGS] string_agg delimiter having no effect with order by

[Pavel Stehule]

2010/8/5 Tom Lane :
> Pavel Stehule  writes:
>> but still when we remove one parametric string_agg, then this issue
>> will not be documented.
>
> How so?  This paragraph will still be there:
>
>   
>    When dealing with multiple-argument aggregate functions, note that the
>    ORDER BY clause goes after all the aggregate arguments.
>    For example, this:
> 
> SELECT string_agg(a, ',' ORDER BY a) FROM table;
> 
>    not this:
> 
> SELECT string_agg(a ORDER BY a, ',') FROM table;  -- incorrect
> 
>    The latter is syntactically valid, but it represents a call of a
>    single-argument aggregate function with two ORDER BY keys
>    (the second one being rather useless since it's a constant).
>   
>
>
>                        regards, tom lane
>

ok

Regards

Pavel Stehule

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

Re: [BUGS] string_agg delimiter having no effect with order by

[Pavel Stehule]

2010/8/5 Tom Lane :
> Pavel Stehule  writes:
>>>>> The same problem can be with custom aggregates :( so this syntax isn't
>>>>> too robust.
>
> BTW, I'm really not worried about that case.  By the time someone is
> advanced enough to have written their own multi-argument aggregate
> definitions, they'll have absorbed the idea that the ORDER BY goes at
> the end.  What we need to accomplish here is just to not set traps at
> the feet of novices using the feature for the first time.  Which is
> why I think it's sufficient to have a policy of not having built-in
> aggregates that conflict in this way; I'm not proposing that we restrict
> or discourage custom aggregates with optional arguments.
>

+1

but still when we remove one parametric string_agg, then this issue
will not be documented.

Pavel

>                        regards, tom lane
>

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

Re: [BUGS] string_agg delimiter having no effect with order by

[Pavel Stehule]

2010/8/5 Tom Lane :
> Thom Brown  writes:
>> On 5 August 2010 10:29, Greg Stark  wrote:
>>> On Thu, Aug 5, 2010 at 5:18 AM, Pavel Stehule  
>>> wrote:
>>>> The same problem can be with custom aggregates :( so this syntax isn't
>>>> too robust. We can support Oracle's syntax in future releases, where
>>>> syntax divide aggregate call and ORDER BY clause.
>>>
>>> What syntax is that?
>
>> An example I've found is:
>> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
>> FROM   emp
>> GROUP BY deptno;
>
> That wouldn't help this problem in the least.  The problem is that
> novices unfamiliar with the SQL-standard aggregrate ORDER BY syntax
> may try to put the ORDER BY in the wrong place.  Offering a different
> syntax won't stop them from doing that.  The only way it might stop
> would be if we documented *only* the Oracle syntax and not the
> spec-compliant syntax.  Which ain't gonna happen.
>
> [ does a bit more research ... ]  Actually, the syntax Thom mentions
> is not Oracle-specific; it's in SQL:2008, and AFAICT it means something
> different from an aggregate ORDER BY anyway.  Maybe Pavel had something
> else in mind.  But my point is still that offering a different syntax
> doesn't fix the problem unless we eliminate the mistake-prone syntax;
> which we can't because it's in the spec.
>

I though this syntax - and what I know Oracle use it for explicit
order and I found lot of sources on net, where is syntax of aggregates
like

name(parameters) [within group ( order by ... ) ]

but my knowledge of this subject is minimal, based on Oracle doc, when
I worked on string_agg function.

I agree, so different syntax doesn't remove a risks, but can decrease
some risks. SQL has lot of a possible dangerous syntaxes and everybody
can selects the most robust syntax.

But this issue can be solved a better documentation.

Regards

Pavel

>                        regards, tom lane
>

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

Re: [BUGS] string_agg delimiter having no effect with order by

[Pavel Stehule]

2010/8/4 Kevin Grittner :
> Alex Hunsaker  wrote:
>> On Wed, Aug 4, 2010 at 11:04, Tom Lane  wrote:
>>> If we were a bit earlier in the 9.0 cycle I would suggest that
>>> this confusion is a sufficient reason to drop the one-argument
>>> form of string_agg.  It's too late now though.
>>

The same problem can be with custom aggregates :( so this syntax isn't
too robust. We can support Oracle's syntax in future releases, where
syntax divide aggregate call and ORDER BY clause.

>> FWIW I think we can still change it.   Isn't this type of issue
>> part of what beta is for?  If we were in RC that would be a
>> different story
>
> I like to think I'm pretty serious about controlling scope creep to
> prevent a release dragging out, but this one seems like beta testing
> uncovered a flaw in new code for the release.  In my book, that
> makes it fair game to balance the risk of breaking things by
> changing it now against the problems we'll have long term if we
> leave it alone.  I'm not sure if that was the basis of saying it was
> too late, or some other consideration.

It is just removing some from one perspective problematic code. This
doesn't add any feature - so it cannot be a precedents.

we can look on this situation from two views:

a) it is good, because we can document this feature/behave - without
one param aggregates people will repeat same situation with custom
aggregates- and this will not be documented.

b) it is bad, because lot of users will be confused.

I prefer @a

Regards

Pavel

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

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

Re: [BUGS] string_agg delimiter having no effect with order by

[Pavel Stehule]

2010/8/4 Thom Brown :
> On 4 August 2010 14:24, Pavel Stehule  wrote:
>> 2010/8/4 Thom Brown :
>>> On 4 August 2010 14:04, Robert Haas  wrote:
>>>> On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown  wrote:
>>>>> Actually, this rings a bell.  I think this may have been raised
>>>>> before, something to do with the delimiter being accepted as one of
>>>>> the order by values.  If this isn't really a bug, could someone
>>>>> mention it in the docs somewhere?
>>>>
>>>> Oh, yeah.  I guess you need this:
>>>>
>>>> select thing, string_agg(stuff, ',' order by stuff) from agg_test
>>>> group by thing;
>>>>
>>>> Rather than this:
>>>>
>>>> select thing, string_agg(stuff order by stuff, ',') from agg_test
>>>> group by thing;
>>>>
>>>> It's all kinds of not obvious to me what the second one is supposed to
>>>> mean, but I remember this was discussed before.  Perhaps we need a
>>>>  somewhere about multi-argument aggregates.
>>>>
>>>
>>> Yes, that works with the order clause.  That's really weird!  It looks
>>> like part of the delimiter parameter, and that's undocumented, or at
>>> least impossible to gleen from the documentation.
>>>
>>> This should be clarified as it looks like having ORDER BY *or* a
>>> delimiter is supported, but not both.  It's horribly unintuitive!
>>> This is one of the very few cases where MySQL's version actually makes
>>> more sense.
>>
>> this goes from ANSI SQL standard :( - I agree, this isn't intuitive
>> and pg can do better diagnostic now. But it has a sense. ORDER BY
>> hasn't sense for one parameter - only for complete function, so is
>> wrong to write ORDER BY over a some interesting parameter
>>
>> Regards
>>
>> Pavel Stehule
>>
>
> So really, should the documentation be changed from:
>
> string_agg(expression [, delimiter ] )
>
> to
>
> string_agg(expression [, delimiter ] [ GROUP BY expression [, ...] ] )

This syntax is available for all aggregate functions - this feature
isn't specific for string_agg

but there can be more descriptive example.

Regards

Pavel
>
> ?
>
> --
> Thom Brown
> Registered Linux user: #516935
>

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

Re: [BUGS] string_agg delimiter having no effect with order by

[Pavel Stehule]

2010/8/4 Thom Brown :
> On 4 August 2010 14:04, Robert Haas  wrote:
>> On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown  wrote:
>>> Actually, this rings a bell.  I think this may have been raised
>>> before, something to do with the delimiter being accepted as one of
>>> the order by values.  If this isn't really a bug, could someone
>>> mention it in the docs somewhere?
>>
>> Oh, yeah.  I guess you need this:
>>
>> select thing, string_agg(stuff, ',' order by stuff) from agg_test
>> group by thing;
>>
>> Rather than this:
>>
>> select thing, string_agg(stuff order by stuff, ',') from agg_test
>> group by thing;
>>
>> It's all kinds of not obvious to me what the second one is supposed to
>> mean, but I remember this was discussed before.  Perhaps we need a
>>  somewhere about multi-argument aggregates.
>>
>
> Yes, that works with the order clause.  That's really weird!  It looks
> like part of the delimiter parameter, and that's undocumented, or at
> least impossible to gleen from the documentation.
>
> This should be clarified as it looks like having ORDER BY *or* a
> delimiter is supported, but not both.  It's horribly unintuitive!
> This is one of the very few cases where MySQL's version actually makes
> more sense.

this goes from ANSI SQL standard :( - I agree, this isn't intuitive
and pg can do better diagnostic now. But it has a sense. ORDER BY
hasn't sense for one parameter - only for complete function, so is
wrong to write ORDER BY over a some interesting parameter

Regards

Pavel Stehule

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

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

Re: [BUGS] BUG #5569: Select in trigger don't retrive true record

[Pavel Stehule]

Hello

your trigger is AFTER or BEFORE trigger? Because BEFORE trigger is
executed before new value is propagated to table.

Regards

Pavel Stehule

2010/7/23 Alessio :
>
> The following bug has been logged online:
>
> Bug reference:      5569
> Logged by:          Alessio
> Email address:      a.burga...@gmail.com
> PostgreSQL version: 8.4.3 build1400
> Operating system:   Windows 2008 Web
> Description:        Select in trigger don't retrive true record
> Details:
>
> Hi,
>
> My trigger is executed on insert in a table.
> After some check ther is a schema change.
> Afeter schema change saltuary and random time the select on set FOUND =
> false when the record exist!
> if i execute a full analizy and vacum the problem is solved.
>
> var_azienda_schema := 'azienda_'|| var_azienda_codice;
> rec:=NULL;
> SELECT nspname INTO rec FROM pg_catalog.pg_namespace WHERE nspname =
> var_azienda_schema LIMIT 1;
> IF NOT FOUND THEN
>  -- SE NON TROVO LO SCHEMA ESCO
>  var_err:= var_trigger_name || ' ' || 'ERR4 schema '||
> COALESCE(CAST(var_azienda_schema as TEXT),'NULL') ||' inesistente.
> CODICE='|| NEW.codice;
>  INSERT INTO public.log(data, errore, pagina, priorita) VALUES ( NOW(),
> var_err, '', -1);
>  UPDATE public.chiamata_evento_temp SET data_elaborazione = NOW(),note =
> var_err WHERE codice = NEW.codice;
>  EXECUTE 'SET search_path = public';
>  RETURN NEW;
> END IF;
> --CAMBIO SCHEMA
> EXECUTE 'SET search_path = '||var_azienda_schema;
>
> --
> -- FIND INFO CODICEID
> --
> -- SE codiceid E' SETTATO CERCO LE INFORMAZIONI
>  rec:=NULL;
>  SELECT
> account_codiceid.codice_protocollo,account_codiceid.descrizione,account_codi
> ceid.area,account_codiceid.codice, account_codiceid.codice_account AS
> codice_account  FROM public.account_codiceid INTO rec WHERE
> account_codiceid.codice = NEW.codiceid AND account_codiceid.codice_azienda =
> var_azienda_codice LIMIT 1
> IF FOUND THEN
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] Odd behavior of SELECT INTO in PL/pgSQL

[Pavel Stehule]

Hello,

this is solved in new PostgreSQL 9.0

postgres=# create table test_table(id int);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION select_test()
postgres-#
postgres-#   RETURNS void AS
postgres-#
postgres-# $BODY$
postgres$#
postgres$# DECLARE
postgres$#
postgres$# id integer = -1;
postgres$#
postgres$# BEGIN
postgres$#
postgres$# select max(id) into id from test_table;
postgres$#
postgres$# END
postgres$#
postgres$# $BODY$
postgres-#
postgres-#   LANGUAGE 'plpgsql' VOLATILE
postgres-#
postgres-#   COST 100;
CREATE FUNCTION
postgres=# select select_test();
ERROR:  column reference "id" is ambiguous
LINE 1: select max(id) from test_table
   ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  select max(id) from test_table
CONTEXT:  PL/pgSQL function "select_test" line 8 at SQL statement
postgres=#

Regards
Pavel Stehule



2010/7/20  :
> PostgreSQL 8.4
>
>
>
> Here is a PL/pgSQL procedure:
>
>
>
> CREATE OR REPLACE FUNCTION select_test()
>
>   RETURNS void AS
>
> $BODY$
>
> DECLARE
>
> id integer = -1;
>
> BEGIN
>
> select max(id) into id from test_table;
>
> END
>
> $BODY$
>
>   LANGUAGE 'plpgsql' VOLATILE
>
>   COST 100;
>
>
>
> test_table is some table with an integer column ‘id’.
>
>
>
> The above procedure has an obvious mistake: the variable ‘id’ has the same
> name as a column in test_table. The select statement should have generated
> an error, preferably at function creation time.
>
>
>
> What actually happens is that no error is thrown, the select result is not
> assigned to the variable ‘id’, and the function always returns -1.

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

Re: [BUGS] Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

[Pavel Stehule]

2010/5/31 Bruce Momjian :
> Pavel Stehule wrote:
>> 2010/4/15 Tom Lane :
>> > Pavel Stehule  writes:
>> >> I think, so RETURNS TABLE can be modified for returning typmode
>> >> without significant problems - this function is called in table
>> >> context and I don't see any problematic use case.
>> >
>> > RETURNS TABLE is just a shorthand for some OUT parameters. ?I don't
>> > believe it's either easy or a good idea to make it work differently
>> > from every other function-argument-or-result case.
>> >
>>
>> I don't know now. It minimally have to be documented
>
> Can you suggest some documentation?

some like "typmod in declared parameters are ignored - so returned
table can not be  same as table declared by CREATE STATEMENT."

Pavel

>
> --
>  Bruce Momjian          http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + None of us is going to be here forever. +
>
>

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

Re: [BUGS] BUG #3563: DATESTYLE feature suggestion

[Pavel Stehule]

Hello

2010/5/16 Ben Hockey :
> i apologize for bringing this up from over 2 years ago but i haven't been
> able to find how this issue was resolved.

it isn't bug, but request for new feature.

look on http://wiki.postgresql.org/wiki/Developer_FAQ

I have nothing against some new datestyles - xml, ecma5 and I am able
to add to pg when hackers will agree

Parametrised datestyle is little bit different. I know so it can be
used for SQL injection on Oracle. So I am not sure if it is a good
idea. But isn't problem create external project (maybe on pgFoundry)
for customized datatype.

Regards

Pavel Stehule

>
> the following is from the ecmascript 5 specification at
> http://www.ecmascript.org/docs/tc39-2009-043.pdf page 168:
>
>> 15.9.1.15 Date Time String Format
>> ECMAScript defines a string interchange format for date-times based upon a
>> simplification of the ISO 8601
>> Extended Format.  The format is as follows: -MM-DDTHH:mm:ss.sssZ
>
>
> ecmascript 5 is the most recent specification for JavaScript and i would
> think that having a DATESTYLE format to simplify interoperability with
> JavaScript applications would be highly desirable.  simplifying
> interoperability could be achieved by either providing a new format that
> matched this specific format or by allowing a way to specify a custom
> DATESTYLE format.  being able to specify a custom DATESTYLE format would be
> preferred since it is the more flexible option.
>
> perhaps this is already possible but i just haven't managed to find it.  any
> help appreciated.
>
> thanks,
>
> ben...
>
> On Aug 21, 2007, at 7:53 PM, Randolf Richardson wrote:
>
>>
>> The following bug has been logged online:
>>
>> Bug reference:      3563
>> Logged by:          Randolf Richardson
>> Email address:      randolf+postgresql@inter-corporate.com
>> PostgreSQL version: 8.2.4
>> Operating system:   NetBSD 4 (beta), NetBSD 3.1, NetWare 6.5
>> Description:        DATESTYLE feature suggestion
>> Details:
>>
>> After convincing clients and colleagues to switch from Oracle (and others)
>> to PostgreSQL, an issue that comes up is the need to customize DATESTYLE.
>> Because this isn't possible, the developers who were against the move to
>> PostgreSQL make it political and recommended work-around solutions such as
>> using to_char() or implementing a view for each table that contain
>> TIMESTAMP[TZ]s is very difficult to argue with management because a lot of
>> time is required to implement these items.
>>
>> In a future version, to solve this problem, an additional DATESTYLE option
>> that uses the same rules as the to_char() function for date formatting
>> would
>> solve this problem.  Here's an example:
>>
>> SET DATESTYLE = 'Custom -Mon-DD';
>>
>> This feature would not only resolve this particular political strife, but
>> would also solve many other problems, including simplifying coding for raw
>> SQL output serving as reports (e.g., users still get confused about dates
>> like "2007-06-03," wondering if they refer to June 3rd, or March 6th).
>>
>> I'm hoping that this suggestion will be an easy one to implement.
>>
>> Thanks in advance.
>>
>> P.S.:  I searched around for a "feature suggestions" page but couldn't
>> find
>> it (if one exists, it should be linked to from the "Report a Bug" page).
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

[Pavel Stehule]

2010/4/15 Tom Lane :
> Pavel Stehule  writes:
>> I think, so RETURNS TABLE can be modified for returning typmode
>> without significant problems - this function is called in table
>> context and I don't see any problematic use case.
>
> RETURNS TABLE is just a shorthand for some OUT parameters.  I don't
> believe it's either easy or a good idea to make it work differently
> from every other function-argument-or-result case.
>

I don't know now. It minimally have to be documented

foodmart=# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 121,506 ms
foodmart=# select * from f();
   a   |b
---+-
 Pavel | Stehule
(1 row)

Time: 0,718 ms
foodmart=# create table x as select * from f();
SELECT
Time: 105,357 ms
foodmart=# \d x
Table "public.x"
 Column |   Type| Modifiers
+---+---
 a  | character varying |
 b  | character varying |

workaround is relative simple

foodmart=# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 1,009 ms
foodmart=# create table x as select a::varchar(20), b::varchar(20)
from (select * from f()) x ;
SELECT
Time: 48,592 ms
foodmart=# \d x
  Table "public.x"
 Column | Type  | Modifiers
----+-------+---
 a  | character varying(20) |
 b  | character varying(20) |

Regards
Pavel Stehule


>                        regards, tom lane
>

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

Re: [BUGS] Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

[Pavel Stehule]

2010/4/15 Tom Lane :
> "Kevin J Bluck"  writes:
>> But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be
>> legal to specify them in that clause?
>
> Yeah, possibly.  CREATE FUNCTION has historically accepted (and then
> discarded) typmod information for all function parameter and result
> types; RETURNS TABLE doesn't seem particularly different from other
> cases here.  We could tighten that up, but again it's not clear whether
> the probable ensuing application breakage would be worth the reduction
> in astonishment quotient.

I think, so RETURNS TABLE can be modified for returning typmode
without significant problems - this function is called in table
context and I don't see any problematic use case.

Pavel

>
>> I do think Pavel G. has a real bug with the char thing, though.
>
> No, it's exactly the same thing: we're accepting and then throwing away
> the typmod.  The fact that it's implicit rather than written out doesn't
> change that.
>
> char would be a particularly nasty case if we did reject typmod
> specifications for function arguments/results, because there is no
> standard syntax for specifying char without a defined max length.
> You'd have to fall back to writing "bpchar", which isn't going to
> make people happy either...
>
>                        regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Re: [BUGS] Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

[Pavel Stehule]

2010/4/14 Kevin J Bluck :
>> I'd certainly call this a bug, if not a couple of different bugs.
>
> There seems to be more. I see a problem not just with unqualified char not 
> being treated equivalent to char(1). I also see a general problem with 
> RETURNS TABLE disregarding any explicit lengths declared for any character 
> type. For example, if you declare a RETURNS TABLE column as VARCHAR(15), it 
> seems to actually consider it as if you had declared it unqualified VARCHAR, 
> equivalent to TEXT. Similarly, a column defined as CHAR(2) will also be 
> treated as unqualified CHAR without an explicit length, with the further 
> problem reported by Pavel of being treated as unlimited length instead of 
> equivalent to CHAR(1) as expected. In other words, it seems to be effectively 
> impossible to declare explicit lengths for RETURNS TABLE character type 
> columns.
>
> This doesn't happen with the RETURNS SETOF variation, only RETURNS TABLE.
>
> Regards,

RETURNS TABLE (x int, y int) is equal to CREATE FUNCTION foo(.. OUT x
int, OUT y int) RETURNS SETOF RECORD. But PostgreSQL functions ignore
typmod for parameters - so it is not bug, it is feature :(

Pavel Stehule

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

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

Re: [BUGS] Can you please let me know?

[Pavel Stehule]

2010/4/14 Gaurav K Srivastav :
> Hi Sir,
>
> Suppose I created a function getage(id character varying).
>
> Now Is there any query in postgreSQL that I can retreive the name of all
> user difned/system function in the database schema.
> like "select VIEW_TYPE_OWNER, VIEW_TYPE, OID_TEXT from user_views" will list
> all views in oracle.
> or
> select object_name,CREATED,OBJECT_TYPE from user_objects;
>
> so that I can get the name of all objects created in a database schema.


>
> If yes Please help me out or is there any tutorial online please give me URL
> for the same.


SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname ~ '^(public)$' -- <<  put here your schema
ORDER BY 1, 2, 4;

http://www.postgresql.org/docs/8.4/static/catalog-pg-proc.html

Regards
Pavel Stehule
>
>
> --
> Thanks & Regards
> Gaurav K Srivastav

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