Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Chris Travers]

It seems to me there is one very simple reason not to change current
behavior which those in favor are glossing over.

Most interactions with a database are not occurring over an interface
like psql with one person typing on one side and the db executing on
the other.If that were the case I would understand the concern
that a typo should give the user an opportunity to pick up the
statement where he/she left off.

However most interactions with the database are purely through
intermediary software.  Adding a lot of "do what I mean" or "give me a
chance to retry that" adds a great deal of complexity to the job of
the software in trapping and handling errors.  It is far, far more
simple to say "syntax errors abort transactions" and leave it at that.
 I know as a developer I don't want that behavior to change.

I guess it seems to me that I would not object to a new option for
transaction behavior where one could do something like SET TRANSACTION
INTERACTIVE; and have no errors abort the transaction at all (explicit
commit or rollback required) but I would complain loudly if this were
to be the default, and I don't see a real need for it.

Best Wishes,
Chris Travers

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Philip Couling]

On 20/06/2012 08:24, Chris Travers wrote:
> It seems to me there is one very simple reason not to change current
> behavior which those in favor are glossing over.
> 
> Most interactions with a database are not occurring over an interface
> like psql with one person typing on one side and the db executing on
> the other.If that were the case I would understand the concern
> that a typo should give the user an opportunity to pick up the
> statement where he/she left off.
> 
> However most interactions with the database are purely through
> intermediary software.  Adding a lot of "do what I mean" or "give me a
> chance to retry that" adds a great deal of complexity to the job of
> the software in trapping and handling errors.  It is far, far more
> simple to say "syntax errors abort transactions" and leave it at that.
>  I know as a developer I don't want that behavior to change.
> 
> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.
> 
> Best Wishes,
> Chris Travers
> 

It would be very nice to turn this feature off completely as a property
of your session.

I generally see it as necessary to do everything inside a transaction
when working in the DB manually. It adds greater protection against
forgotten WHERE clauses etc.  I've seen too many DBs mashed because of a
careless typo. The current behavior encourages admins not to use
transactions because any error (typo or not) forces them to re-do all
their work so far or put in a lot of extra typing to wrap everything.


On the idea of different error behavior between bad syntax and pragmatics...

Splitting hairs between a syntax error and other errors is dangerous.
There are too many cases where the division can not be clear.  And any
implementation would find it difficult not to fall foul of the principle
of least astonishment.
http://en.wikipedia.org/wiki/Principle_of_least_astonishment

For example pg/plsql executing dynamic SQL.  An error may have been
caused by faulty arguments. However one of the arguments may have been a
SQL statement in part or full.  How should PostgreSQL behave? See the
argument as bad (data error) or the SQL it contains as a syntax error.
You can always find an answer to this that works, but will that answer
be obvious to every developer?

Regards

Phil

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

Re: [GENERAL] db server processes hanging around

[Sumit Raja]

Or ask your Java devs to investigate why the shut down does not close
the physical connection properly. Does IDEA claim to shut down Tomcat
but actually it is still running because of a threads not being
cleaned up?

Are you sure this isn't happening during normal operation of the
application? If its bad connection/thread management, something like
this might show up in production.

- Sumit


On 19 June 2012 18:28, Steve Crawford  wrote:
> On 06/19/2012 09:29 AM, Mark Rostron wrote:
>>
>> hi
>>
>> we are running out of database connections.
>>
>> we are using pg 9.0.6 on linux centos 5.7 64bit.
>> we are not using any go-between connection pools such as pgbouncer or
>> pgpool - connections occur directly from client to database.
>> the connection setup on the client (java) is default, only providing
>> (user,password,dbhost,dbname).
>>
>> we have about 10 developers developing java thru IDEA who start/stop the
>> local tomcat server frequently.
>> i have observed that tomcat doesn't disconnect from pg cleanly when they
>> cycle, and the server processes persist for a long time.
>> I have had them reduce their local connection factory pool size to 1 (this
>> helped) and increased our max_connection value to 1000.
>> yet the problem persists.
>>
>> I have noticed that the server processes do die "after some time" - due to
>> inactivity?
>> we are looking for a way to control server processes better than we are
>> doing now.
>>
>> thnx for your time.
>> mr
>>
>>
> I am unaware of any system setting like max_connection_idle_time (though it
> might be a useful addition). I have not had to mess with tcp_keepalive
> settings but you might be able to alter those (perhaps at the OS instead of
> PostgreSQL) to reduce the delay before the backend terminates. But this
> won't work for socket connections.
>
> You could hack together a tailored solution by having cron run a script that
> would query pg_stat_activity for queries equal to "" and with a
> backend_start age greater than whatever you find reasonable and then execute
> pg_terminate_backend() on those PIDs. You could even have a table of
> developer IP addresses and only terminate those processes. Alternately, if
> Tomcat connected to a different port you could only kill those.
>
> Cheers,
> Steve
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
Raja Consulting Ltd.
Incorporated in England and Wales No. 06454814,  Registered Office: 4
Calder Court, Shorebury Point, Amy Johnson Way, Blackpool FY4 2RH

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Rafal Pietrak]

On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote:
[--]
> 
> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.

Awesome!

Or rather: "BEGIN [INTERACTIVE];" (mind the ) for a one shot
interaction.

-R


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

[GENERAL] View parsing

[Dr. F. Lee]

Hi all,

I would like to be able to determine which tables each field of a view 
comes from.


I have a view definition like
 SELECT tbl_a.fld_a, tbl_b.fld_b AS fld_e, function(c,d,f) as fld_c, 
(SELECT fld_d FROM tbl_d WHERE tbl_d.fld_e=tbl_a.fld_a) as fld_d FROM 
tbl_a inner join tbl_b on tbl_a.fld_e=tbl_b.fld_f WHERE cond_a;


What I'd like to get to is a list of fields in the view indicating how 
each is calculated - without putting the definition into a horrible bunch 
of regexps. (Which would no doubt work but seems the wrong thing to do.)


Something like this would be ideal:

fld_a -> tbl_a.fld_a
fld_e -> tbl_b.fld_b
fld_c -> function(c,d,f)
fld_d -> (SELECT fld_d FROM tbl_d WHERE tbl_d.fld_e=tbl_a.fld_a)

My goal is to answer the question "in what other views might I find the 
data which I see as, say, fld_e?". Once I know where the data comes from I 
can dig through the pg_depends data and figure out other views which 
depend on that (table,column) tuple and go from there.


Yours,

Frank

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

Re: [GENERAL] How to get no. of commits/rollbacks by application on the database?

[Raghavendra]

> This sounds like one of those "what are you trying to achieve" questions,
> where you need to step back and ask why you're trying to collect that info
> and what you want it for.
>
> First, how do you define "application"? Any client that connects to a
> given database? Any client that sets the application_name GUC to a
> particular value? Any client from a given host? etc. "Application" can mean
> a lot of different things. It sounds like you might simply mean work done
> on a connection that isn't internal to the database system's bookkeeping,
> which is a bit easier, but I'm not sure.
>
> Second, why? What does the transaction count tell you? How will you
> account for work done by PgAgent (if used), via dblink, etc?
>
> I guess I'm unsure what you're trying to accomplish.
>
>
Thanks Craig for your reply.
4 What's, 2 Why's, 2 How's ...  :)

Let me put my question simple.

What counts include in xact_commit & xact_rollback of pg_stat_database ?

--Raghav

Re: [GENERAL] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

CREATE OR REPLACE FUNCTION "morse_new_sit"."proc_get_freq_svrty_array1"(IN
ip_dim_type bigint, IN ip_type bigint, IN ip_frqsubype bigint, IN
ip_svrsubtype bigint ,
--,
 op_dimlist OUT  morse_new_sit.user_fs_obj[],op_freqlist OUT 
morse_new_sit.user_fs_obj[],op_svrlist OUT 
morse_new_sit.user_fs_obj[],op_clrlist OUT morse_new_sit.user_clr_obj[]
) RETURNS SETOF record AS $BODY$

DECLARE

op_dimlist morse_new_sit.user_fs_obj%rowtype;
op_dimlist_array morse_new_sit.user_fs_obj[];
op_freqlist morse_new_sit.user_fs_obj%rowtype;
op_freqlist_array morse_new_sit.user_fs_obj[];
op_svrlist morse_new_sit.user_fs_obj%rowtype;
op_svrlist_array morse_new_sit.user_fs_obj[];
op_clrlist morse_new_sit.user_clr_obj%rowtype;
op_clrlist_array morse_new_sit.user_clr_obj[];
m int;

BEGIN
RAISE NOTICE 'GET DIM DETAILS';
-- Get the DIM details
FOR op_dimlist IN
SELECT DD_DIMID,dd_dimname,dd_dimcolorcd FROM morse_new_sit.DIM_DEF
WHERE DD_DIMTYPE = IP_DIM_TYPE
AND DD_STATUS = 0
AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND 
DD_VALIDTO
LOOP
op_dimlist_array := array_append(op_dimlist_array,op_dimlist);
*proc_get_freq_svrty_array1.op_dimlist = op_dimlist_array;*
RAISE NOTICE 'OP_DIM_LIST %',op_dimlist;
END LOOP;
m := array_length(op_dimlist_array, 1);
RAISE NOTICE ' array count ::: %',m;

--Return  op_dimlist_array;

-- GET the FREQ details
FOR op_freqlist IN
SELECT DD_DIMID,dd_dimname, dd_dimcolorcd FROM morse_new_sit.DIM_DEF
WHERE DD_DIMTYPE = ip_type

AND DD_DIMSUBTYPE = ip_frqsubype

AND DD_STATUS = 0

AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND
DD_VALIDTO
LOOP
op_freqlist_array := array_append(op_freqlist_array,op_freqlist);
RAISE NOTICE 'op_freqlist LIST %',op_freqlist;
*   proc_get_freq_svrty_array1.op_freqlist = op_freqlist_array;*
END LOOP;
m := array_length(op_freqlist_array, 1);
RAISE NOTICE ' array count ::: %',m;
--RETURNop_freqlist_array;
--Get the Severity
FOR op_svrlist IN
SELECT DD_DIMID,dd_dimname, dd_dimcolorcd FROM morse_new_sit.DIM_DEF
WHERE DD_DIMTYPE = ip_type

AND DD_DIMSUBTYPE = ip_svrsubType

AND DD_STATUS = 0

AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND
DD_VALIDTO
LOOP
op_svrlist_array := array_append(op_svrlist_array,op_svrlist);
RAISE NOTICE 'op_svrlist LIST %',op_svrlist;
*   proc_get_freq_svrty_array1.op_svrlist = op_svrlist_array;*
END LOOP;
m := array_length(op_svrlist_array, 1);
RAISE NOTICE ' array count ::: %',m;
--RETURNop_svrlist_array ;

FOR OP_CLRLIST IN
SELECT cs_second_scale, cs_first_scale,CS_COLOR_CODE FROM
morse_new_sit.COMPOSITE_SCORE
WHERE CS_SECOND_SCALE IN (SELECT DD_DIMID

  FROM morse_new_sit.DIM_DEF

  WHERE DD_DIMTYPE = ip_type

  AND DD_DIMSUBTYPE = ip_frqsubype

   AND date_trunc('day', LOCALTIMESTAMP)
BETWEEN DD_VALIDFROM AND DD_VALIDTO

  AND DD_STATUS = 0)

AND CS_FIRST_SCALE IN (SELECT 
DD_DIMID

  FROM morse_new_sit.DIM_DEF

  WHERE DD_DIMTYPE = ip_type

  AND DD_DIMSUBTYPE = ip_svrsubType

   AND date_trunc('day', LOCALTIMESTAMP)
BETWEEN DD_VALIDFROM AND DD_VALIDTO

  AND DD_STATUS = 0)

AND CS_STATUS = 0
LOOP
OP_CLRLIST_array := array_append(OP_CLRLIST_array,OP_CLRLIST);
RAISE NOTICE 'OP_CLRLIST %',OP_CLRLIST;
*   proc_get_freq_svrty_array1.OP_CLRLIST = OP_CLRLIST_array ;*
 --RETURN OP_CLRLIST_array;
END LOOP;
m := array_length(OP_CLRLIST_array, 1);
RAISE NOTICE ' array count ::: %',m;
--RETURN  anyarray;

END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100;

*I am getting null in the output *
*/
Appreciate your help merlin /*

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5713491.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[GENERAL] Finding all 8.4->9.x incompatible PL/pgSQL functions with conflicting parameter/column names causing "column reference ... is ambiguous" errors

[Joel Jacobson]

I'm upgrading from 8.4 to 9.1, and have a lot of PL/pgSQL functions which
works in 8.4, but when called, throws an error in 9.1.

Example:

CREATE TABLE mytable (id serial not null primary key, value text);

INSERT INTO mytable (id, value) VALUES (1, 'foo');
INSERT INTO mytable (id, value) VALUES (2, 'bar');

CREATE OR REPLACE FUNCTION myfunc(id int) RETURNS TEXT AS $$
DECLARE
value text;
BEGIN
SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id;
RETURN value;
END;
$$ LANGUAGE plpgsql;

SELECT myfunc(1);
SELECT myfunc(2);

This returns "foo" and "bar" like expected in 8.4, but in 9.1 I get "column
reference "id" is ambiguous", "It could refer to either a PL/pgSQL variable
or a table column.".

This is of course easy to fix by qualifying id with the name of the
function:

-SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id;
+SELECT mytable.value INTO value FROM mytable WHERE mytable.id = myfunc.id;

The problem is, how can I find all functions which have this problem?

You don't get this error when creating the functions, only when running
them and hitting a statement where there is a conflict.

Would it be possible to somehow automatically scan through all functions
and getting a list of the functions which have this problem?

Thanks!

Best regards,

Joel Jacobson

Re: [GENERAL] Finding all 8.4->9.x incompatible PL/pgSQL functions with conflicting parameter/column names causing "column reference ... is ambiguous" errors

[Pavel Stehule]

2012/6/20 Joel Jacobson :
> I'm upgrading from 8.4 to 9.1, and have a lot of PL/pgSQL functions which
> works in 8.4, but when called, throws an error in 9.1.
>
> Example:
>
> CREATE TABLE mytable (id serial not null primary key, value text);
>
> INSERT INTO mytable (id, value) VALUES (1, 'foo');
> INSERT INTO mytable (id, value) VALUES (2, 'bar');
>
> CREATE OR REPLACE FUNCTION myfunc(id int) RETURNS TEXT AS $$
> DECLARE
> value text;
> BEGIN
> SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id;
> RETURN value;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT myfunc(1);
> SELECT myfunc(2);
>
> This returns "foo" and "bar" like expected in 8.4, but in 9.1 I get "column
> reference "id" is ambiguous", "It could refer to either a PL/pgSQL variable
> or a table column.".
>
> This is of course easy to fix by qualifying id with the name of the
> function:
>
> -SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id;
> +SELECT mytable.value INTO value FROM mytable WHERE mytable.id = myfunc.id;
>
> The problem is, how can I find all functions which have this problem?
>
> You don't get this error when creating the functions, only when running them
> and hitting a statement where there is a conflict.
>
> Would it be possible to somehow automatically scan through all functions and
> getting a list of the functions which have this problem?
>

you can try to apply patch - plpgsql_check_function
http://archives.postgresql.org/message-id/CAFj8pRBRWXA98T9k=Cqw==brpsl1omwjiwzdi4gsivraeeu...@mail.gmail.com

you need 9.2 and with this functionality you can find functions with
some issues.

Regards

Pavel Stehule

> Thanks!
>
> Best regards,
>
> Joel Jacobson

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

Re: [GENERAL] pgstat wait timeout : permission denied

[pandorino]

Hi all
just to close the post and let you know: it was the antivirus (McAfee).
Now I have to see how to create an exception on this folder, because of
course I would keep the antivirus running.

Thanks Alban for the hint.

Regards


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-permission-denied-tp5713236p5713509.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Please make it easy to drop a database that is in use

[Sergey Konoplev]

On Tue, Jun 19, 2012 at 1:40 PM, Evan Martin
 wrote:
> When I'm developing against a PostgreSQL database I often drop and re-create
> it and I often find that the drop fails, because it's "in use by other
> users". This is really annoying, especially when I know full well there are
> no other users - it's just me.

Just connect another (say postgres) database and disconnect the
database you are trying to delete. And keep it in mind.

> hassle, for something that should be a very simple operation. (I'm not even
> writing SQL for it normally, just pressing Delete in pgAdmin.) Secondly,

So I think this proposal/issue should be sent not to PG development
team but to pgAdmin's one. Clients software should make all this
re-connections accordingly to its own rules.

> pg_terminate_backend requires superuser rights. If I'm not a superuser, but
> I am the owner of the database, it doesn't seem right that another user
> should be able to prevent me from dropping my database.
>
> I'd really like to see PostgreSQL directly support dropping a database,
> regardless of who is using it - something like "DROP DATABASE ... CASCADE".
> (Although "CASCADE" wouldn't be the appropriate word here. Maybe "DROP
> DATABASE ... TO_HELL_WITH_USERS"?)
>
> Evan



-- 
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[felix]

On Tue, Jun 19, 2012 at 11:25:24AM -0600, Scott Marlowe wrote:
> On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter  
> wrote:
> > There is also the case of dynamically generated sql statements based on 
> > user selection... being syntax or not, I would never want half job done. 
> > Thia is the purpose of transactions: or all or nothing...
> 
> This this this, and again, this.  Imagine:
> 
> begin;
> insert into tableb selcet * from tableb;
> truncate tableb;
> commit;
> 
> What should happen when we get to the error on the second line?  Keep
> going?  Boom, data gone because of a syntax error.

I've been lurking, and maybe I should go back to that :-) but I think you 
misunderstand.  The idea is not to ignore or second-guess typoes, but to report 
them without affecting the transaction, and only do this in interactive 
sessions.

Personally, I like the idea of BEGIN INTERACTIVE, but note I do not offer to do 
the work.

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / fe...@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Simon Riggs wrote:
> So it would be useful to have a non-default option of 
> statement-level abort for those cases, as an ease of use feature.
   
I think you misspelled "foot gun"

Chris Travers wrote:

> Most interactions with a database are not occurring over an interface
> like psql with one person typing on one side and the db executing on
> the other. If that were the case I would understand the concern
> that a typo should give the user an opportunity to pick up the
> statement where he/she left off.

Well, that's really up to the users/authors of other tools, if they 
feel the need to scratch that itch.

> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.

I would object. That's a recipe for disaster, and goes against our 
philosophy of being safe, careful, and correct.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201206200945
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk/h1BQACgkQvJuQZxSWSshZ6QCfaGG1y0d76aTMKiXTU8Gy8i2G
MjUAnAiAbf53qL3MOXUEiqKARhm2mezx
=wbJw
-END PGP SIGNATURE-



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

[GENERAL] error handling

[Little, Douglas]

Hello,

Greenplum 4.1.2.4 (PG 8.2.3)
We are revising how we implement functions in order to better capture and 
handle fatal errors.

What we want to have happen,

1.   is to have the fatal error captured,

2.   logged to our processing table,

3.   then have the function & psql exit with a non-zero return code, 
informing Informatica of the process failure.

I'm having several problems.

1.   Under GP,  any function called from psql is committed as a single 
transaction.   I can handle the sql exception, and log the error to the table,  
and return a non-zero return code to psql.

2.   I don't know how to raise exception in the psql script.  All I can 
imagine is to nest the function calls,  having the outer function issue raise 
exception - but then the entire transaction is rolled back since the 
transactions are nested.  so I loose the logging messages.

3.   I'd like to have psql capture the return value and provide to a 2nd 
function which would then raise exeception.   But don't know and can't tell 
from the doc if I can assign function output to psql metavariables.

4.   Don't know how to do anything with the psql metavariable   ieif 
:last_return_code >= 16 then raise exception.


Suggestions would be appreciated.
Thanks



Doug Little

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[felix]

On Wed, Jun 20, 2012 at 06:36:09AM -0700, fe...@crowfix.com wrote:
> On Tue, Jun 19, 2012 at 11:25:24AM -0600, Scott Marlowe wrote:
> > On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter  
> > wrote:
> > > There is also the case of dynamically generated sql statements based on 
> > > user selection... being syntax or not, I would never want half job done. 
> > > Thia is the purpose of transactions: or all or nothing...
> > 
> > This this this, and again, this.  Imagine:
> > 
> > begin;
> > insert into tableb selcet * from tableb;
> > truncate tableb;
> > commit;
> > 
> > What should happen when we get to the error on the second line?  Keep
> > going?  Boom, data gone because of a syntax error.
> 
> I've been lurking, and maybe I should go back to that :-) but I think you 
> misunderstand.  The idea is not to ignore or second-guess typoes, but to 
> report them without affecting the transaction, and only do this in 
> interactive sessions.
> 
> Personally, I like the idea of BEGIN INTERACTIVE, but note I do not offer to 
> do the work.

Looks like I should go back to lurking, and do better at it :-(

The discussion began about differentiatng typoes and other errors, which is 
clearly not easy or obvious, and something that has always frustrated me when I 
find programs which try to do so.  Then I saw the idea of BEGIN INTERACTIVE and 
lost sight of the discussion.

My apoligies for stepping in so badly.

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / fe...@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

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

Re: [GENERAL] pgstat wait timeout : permission denied

[Craig Ringer]

On 06/20/2012 08:31 PM, pandorino wrote:

Hi all
just to close the post and let you know: it was the antivirus (McAfee).
Now I have to see how to create an exception on this folder, because of
course I would keep the antivirus running.



You'll probably also want to set an exception on the folder with the 
PostgreSQL executables etc in it (by default it contains the datadir, 
but not always, so may need to be set separately), *AND* set an 
exception for the postmaster and postgres.exe executables.


--
Craig Ringer

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

[GENERAL] Simple method to format a string?

[Emi Lu]

Good morning,

Is there a simply method in psql to format a string?

For example, adding a space to every three consecutive letters:

abcdefgh -> *** *** ***

Thanks a lot!
Emi

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Edson Richter]

Em 20/06/2012 11:00, fe...@crowfix.com escreveu:

On Wed, Jun 20, 2012 at 06:36:09AM -0700, fe...@crowfix.com wrote:

On Tue, Jun 19, 2012 at 11:25:24AM -0600, Scott Marlowe wrote:

On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter  wrote:

There is also the case of dynamically generated sql statements based on user 
selection... being syntax or not, I would never want half job done. Thia is the 
purpose of transactions: or all or nothing...

This this this, and again, this.  Imagine:

begin;
insert into tableb selcet * from tableb;
truncate tableb;
commit;

What should happen when we get to the error on the second line?  Keep
going?  Boom, data gone because of a syntax error.

I've been lurking, and maybe I should go back to that :-) but I think you 
misunderstand.  The idea is not to ignore or second-guess typoes, but to report 
them without affecting the transaction, and only do this in interactive 
sessions.

Personally, I like the idea of BEGIN INTERACTIVE, but note I do not offer to do 
the work.

Looks like I should go back to lurking, and do better at it :-(

The discussion began about differentiatng typoes and other errors, which is 
clearly not easy or obvious, and something that has always frustrated me when I 
find programs which try to do so.  Then I saw the idea of BEGIN INTERACTIVE and 
lost sight of the discussion.

My apoligies for stepping in so badly.


Don't be sorry, your question arrived a great discussion.

Nobody is mad about that, is just a matter to have great minds having 
great discussions!


Thansk for bringing that. Does not mean I agree with your point, but the 
debate is bigger than my personal opinion.


I'm certain that something good will arrive from this thread.

In time: I believe that "Begin Interactive" is not a good idea. Such 
behavior shall be built in the interface application, not in database 
backend... As I stated before, being tolerant or not, or trying to 
"guess" what user means is a task for the interface. The database must 
keep the "always safe, always good" policy with data.


Regards,

Edson.

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

Re: [GENERAL] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[Merlin Moncure]

On Wed, Jun 20, 2012 at 5:31 AM, utsav  wrote:
> CREATE OR REPLACE FUNCTION "morse_new_sit"."proc_get_freq_svrty_array1"(IN
> ip_dim_type bigint, IN ip_type bigint, IN ip_frqsubype bigint, IN
> ip_svrsubtype bigint ,
> --,
>  op_dimlist OUT  morse_new_sit.user_fs_obj[],op_freqlist OUT
> morse_new_sit.user_fs_obj[],op_svrlist OUT
> morse_new_sit.user_fs_obj[],op_clrlist OUT morse_new_sit.user_clr_obj[]
> ) RETURNS SETOF record AS $BODY$
>
> DECLARE
>
> op_dimlist morse_new_sit.user_fs_obj%rowtype;
> op_dimlist_array morse_new_sit.user_fs_obj[];
> op_freqlist morse_new_sit.user_fs_obj%rowtype;
> op_freqlist_array morse_new_sit.user_fs_obj[];
> op_svrlist morse_new_sit.user_fs_obj%rowtype;
> op_svrlist_array morse_new_sit.user_fs_obj[];
> op_clrlist morse_new_sit.user_clr_obj%rowtype;
> op_clrlist_array morse_new_sit.user_clr_obj[];
> m int;
>
> BEGIN
> RAISE NOTICE 'GET DIM DETAILS';
>        -- Get the DIM details
>        FOR op_dimlist IN
>        SELECT DD_DIMID,dd_dimname,dd_dimcolorcd FROM morse_new_sit.DIM_DEF
>        WHERE DD_DIMTYPE = IP_DIM_TYPE
>        AND DD_STATUS = 0
>        AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND 
> DD_VALIDTO
>        LOOP
>        op_dimlist_array := array_append(op_dimlist_array,op_dimlist);
> *proc_get_freq_svrty_array1.op_dimlist = op_dimlist_array;*
>        RAISE NOTICE 'OP_DIM_LIST %',op_dimlist;
>        END LOOP;
>        m := array_length(op_dimlist_array, 1);
> RAISE NOTICE ' array count ::: %',m;
>
> --Return  op_dimlist_array;
>
>        -- GET the FREQ details
>        FOR op_freqlist IN
>        SELECT DD_DIMID,dd_dimname, dd_dimcolorcd FROM morse_new_sit.DIM_DEF
>        WHERE DD_DIMTYPE = ip_type
>
>        AND DD_DIMSUBTYPE = ip_frqsubype
>
>        AND DD_STATUS = 0
>
>        AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND
> DD_VALIDTO
>        LOOP
>        op_freqlist_array := array_append(op_freqlist_array,op_freqlist);
>        RAISE NOTICE 'op_freqlist LIST %',op_freqlist;
> *       proc_get_freq_svrty_array1.op_freqlist = op_freqlist_array;*
>        END LOOP;
>        m := array_length(op_freqlist_array, 1);
> RAISE NOTICE ' array count ::: %',m;
> --RETURN        op_freqlist_array;
>        --Get the Severity
>        FOR op_svrlist IN
>        SELECT DD_DIMID,dd_dimname, dd_dimcolorcd FROM morse_new_sit.DIM_DEF
>        WHERE DD_DIMTYPE = ip_type
>
>        AND DD_DIMSUBTYPE = ip_svrsubType
>
>        AND DD_STATUS = 0
>
>        AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND
> DD_VALIDTO
>        LOOP
>        op_svrlist_array := array_append(op_svrlist_array,op_svrlist);
>        RAISE NOTICE 'op_svrlist LIST %',op_svrlist;
> *       proc_get_freq_svrty_array1.op_svrlist = op_svrlist_array;*
>        END LOOP;
>        m := array_length(op_svrlist_array, 1);
> RAISE NOTICE ' array count ::: %',m;
> --RETURN        op_svrlist_array ;
>
>                FOR OP_CLRLIST IN
>                SELECT cs_second_scale, cs_first_scale,CS_COLOR_CODE FROM
> morse_new_sit.COMPOSITE_SCORE
>                WHERE CS_SECOND_SCALE IN (SELECT DD_DIMID
>
>                                  FROM morse_new_sit.DIM_DEF
>
>                                  WHERE DD_DIMTYPE = ip_type
>
>                                  AND DD_DIMSUBTYPE = ip_frqsubype
>
>                                   AND date_trunc('day', LOCALTIMESTAMP)
> BETWEEN DD_VALIDFROM AND DD_VALIDTO
>
>                                  AND DD_STATUS = 0)
>
>                                                AND CS_FIRST_SCALE IN (SELECT 
> DD_DIMID
>
>                                  FROM morse_new_sit.DIM_DEF
>
>                                  WHERE DD_DIMTYPE = ip_type
>
>                                  AND DD_DIMSUBTYPE = ip_svrsubType
>
>                                   AND date_trunc('day', LOCALTIMESTAMP)
> BETWEEN DD_VALIDFROM AND DD_VALIDTO
>
>                                  AND DD_STATUS = 0)
>
>        AND CS_STATUS = 0
>                LOOP
>                OP_CLRLIST_array := array_append(OP_CLRLIST_array,OP_CLRLIST);
>                RAISE NOTICE 'OP_CLRLIST %',OP_CLRLIST;
> *               proc_get_freq_svrty_array1.OP_CLRLIST = OP_CLRLIST_array ;*
>  --RETURN OP_CLRLIST_array;
>        END LOOP;
>        m := array_length(OP_CLRLIST_array, 1);
> RAISE NOTICE ' array count ::: %',m;
> --RETURN  anyarray;
>
> END;
>
> $BODY$
>  LANGUAGE 'plpgsql' VOLATILE COST 100;
>
> *I am getting null in the output *
> */
> Appreciate your help merlin /*

uh, you have no return statements. of course the output is null.  if
you are using loops, you *must* use return next.  Also each return
next will return *all* the OUT variables.

merlin

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

Re: [GENERAL] db server processes hanging around

[Edson Richter]

Em 20/06/2012 04:53, Sumit Raja escreveu:

Or ask your Java devs to investigate why the shut down does not close
the physical connection properly. Does IDEA claim to shut down Tomcat
but actually it is still running because of a threads not being
cleaned up?

Are you sure this isn't happening during normal operation of the
application? If its bad connection/thread management, something like
this might show up in production.

- Sumit


On 19 June 2012 18:28, Steve Crawford  wrote:

On 06/19/2012 09:29 AM, Mark Rostron wrote:

hi

we are running out of database connections.

we are using pg 9.0.6 on linux centos 5.7 64bit.
we are not using any go-between connection pools such as pgbouncer or
pgpool - connections occur directly from client to database.
the connection setup on the client (java) is default, only providing
(user,password,dbhost,dbname).

we have about 10 developers developing java thru IDEA who start/stop the
local tomcat server frequently.
i have observed that tomcat doesn't disconnect from pg cleanly when they
cycle, and the server processes persist for a long time.
I have had them reduce their local connection factory pool size to 1 (this
helped) and increased our max_connection value to 1000.
yet the problem persists.

I have noticed that the server processes do die "after some time" - due to
inactivity?
we are looking for a way to control server processes better than we are
doing now.

thnx for your time.
mr



I am unaware of any system setting like max_connection_idle_time (though it
might be a useful addition). I have not had to mess with tcp_keepalive
settings but you might be able to alter those (perhaps at the OS instead of
PostgreSQL) to reduce the delay before the backend terminates. But this
won't work for socket connections.

You could hack together a tailored solution by having cron run a script that
would query pg_stat_activity for queries equal to "" and with a
backend_start age greater than whatever you find reasonable and then execute
pg_terminate_backend() on those PIDs. You could even have a table of
developer IP addresses and only terminate those processes. Alternately, if
Tomcat connected to a different port you could only kill those.

Cheers,
Steve

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



I've been working with Tomcat and PostgreSQL (and NetBeans + Eclipse 
IDEs) for about 4 years now, without such problem.
When Tomcat is stopped, all database connections are closed (if you are 
using the great Database Pooling that comes with Tomcat).
But then, you must check your application. There are times when you 
create a thread (and forget to set "Deamon" as true), and then when you 
Shutdown Tomcat, it remains running (you check confirm that looking for 
the "java" processess in memory.
If you are running IDEA + Tomcat, you will see (at minimum) 2 Java 
process running. When you stop Tomcat, then you should see only one Java 
process running (otherwise, you have a "hang" thread in your application).
One idea is to create a Application Lifecycle listener, and make it 
shutdown all your Threads (and possible database connections) before 
leaving.


Regards,

Edson




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

[GENERAL] 32-bit libpq with 64-bit server

[Marc Watson]

Hello all,

On Windows, I have a 32-bit client application that uses the 32-bit
libpq.dll. In testing the client application with a Postgres 9.2 64-bit
server I've noticed no problems, and was wondering if anyone knows of
any caveats in this - AFAICS the tcp communication is between the 32-bit
libpq and the 64-bit  backend is compatible.

- Mark Watson

Re: [GENERAL] 32-bit libpq with 64-bit server

[Merlin Moncure]

On Wed, Jun 20, 2012 at 10:07 AM, Marc Watson
 wrote:
> Hello all,
>
> On Windows, I have a 32-bit client application that uses the 32-bit
> libpq.dll. In testing the client application with a Postgres 9.2 64-bit
> server I’ve noticed no problems, and was wondering if anyone knows of any
> caveats in this – AFAICS the tcp communication is between the 32-bit libpq
> and the 64-bit  backend is compatible.

It should pretty much work.  The main thing to watch out for is that
very large results sets will hit the upper limit on memory
allocations.

merlin

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

[GENERAL] efficiency of wildcards at both ends

[Sam Z J]

Hi all

I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
How efficient is it if that's the only search criteria against a large
table? how much does indexing the column help and roughly how much more
space is needed for the index?

if the answers are too long, please point me to the relavant text =D

thanks

-- 
Zhongshi (Sam) Jiang
sammyjiang...@gmail.com

Re: [GENERAL] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

Ya but when i use the return next it gives me all OUT parameters but i will
get last record of out parameter1 repetitive untill the last record of last
out parameter . Sorry i didn't have output with me . Is there any other way
to achive this ? 

Many Thanks for your help merlin ...  

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5713602.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] efficiency of wildcards at both ends

[Lonni J Friedman]

On Wed, Jun 20, 2012 at 10:10 AM, Sam Z J  wrote:
> Hi all
>
> I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
> How efficient is it if that's the only search criteria against a large
> table? how much does indexing the column help and roughly how much more
> space is needed for the index?
>
> if the answers are too long, please point me to the relavant text =D

My limited understanding is that any time you need to resort to using
wildcards, indices are never used, and you're falling back to using
the inefficient table scan.

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

Re: [GENERAL] efficiency of wildcards at both ends

[Andy Colson]

On 6/20/2012 12:10 PM, Sam Z J wrote:

Hi all

I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
How efficient is it if that's the only search criteria against a large
table? how much does indexing the column help and roughly how much more
space is needed for the index?

if the answers are too long, please point me to the relavant text =D

thanks

--
Zhongshi (Sam) Jiang
sammyjiang...@gmail.com 


An index will not be used for that kind of search.  PG will scan the 
entire table to find matches.  PG can only use an index if you have a 
search LIKE 'str%'


There are options like full text search, and pg_trgm that you might be 
able to use.


-Andy


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

Re: [GENERAL] efficiency of wildcards at both ends

[Edson Richter]

AFAIK, wildcards at both ends are not optimized at all, unless you use 
some sort of specialized index (may be Gist or FullText).
Until 9.1 there is no such "Index Scan" feature, that would help (very 
little).
Other databases (like MS SQL Server) solve this kind of query by 
executing an Index Scan, then merge join with rest of the query.


This is all I know about LIKE optimization in PostgreSQL:

LIKE 'str%' -> optimized by normal indexes
LIKE '%str%' -> not optimized. You can use FullText, but then your 
wildcards will have to change to something not SQL-standard compatible 
solution...
LIKE '%str' -> can be optimized if you create index with column content 
reversed then query reversed as well. See code below for details.


How did I optimized "%str" queries (code implemented with help from the 
PgSql community):


CREATE OR REPLACE FUNCTION reverse(input character varying)
  RETURNS character varying AS
$BODY$
DECLARE
  result character varying = '';
  i int;
BEGIN
  FOR i IN 1..length(input) BY 2 LOOP
result = substr(input,i+1,1) || substr(input,i,1) || result;
  END LOOP;
  RETURN result;
END$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100;
create index idx on tb1 (reverse(nome));
select * from tb1 where reverse(nome) like reverse('%RICHTER');


Regards,

Edson.

Em 20/06/2012 14:10, Sam Z J escreveu:

Hi all

I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
How efficient is it if that's the only search criteria against a large 
table? how much does indexing the column help and roughly how much 
more space is needed for the index?


if the answers are too long, please point me to the relavant text =D

thanks

--
Zhongshi (Sam) Jiang
sammyjiang...@gmail.com 

Re: [GENERAL] efficiency of wildcards at both ends

[Edson Richter]

Just ocurred to me that would be possible to create some sort of 
"hybrid" solution...


create index idx1 on tb1 (nome);
create index idx2 on tb1 (reverse(nome));

select * from tb1
  where nome like 'CARLOS%' or reverse(nome) like reverse('%CARLOS')

Should return same results as
select * from tb1
  where nome like '%CARLOS%'

I supposed that this hybrid solution will be optmized by the indexes 
(but at what cost?).


Can some PostgreSQL expert tell if this assumption is right? Would be 
possible to have PostgreSQL doing that automatically? Something like


create index idx1 on tb1 (nome) with options (optimize wildcards);
select * from tb1 where nome like '%CARLOS%';

and then this get expanded as the example above? Then, what happens with 
the following query:


select * from tb1 where nome like '%CARLOS%ERICKSSON%';

?

Edson

Em 20/06/2012 14:28, Edson Richter escreveu:
AFAIK, wildcards at both ends are not optimized at all, unless you use 
some sort of specialized index (may be Gist or FullText).
Until 9.1 there is no such "Index Scan" feature, that would help (very 
little).
Other databases (like MS SQL Server) solve this kind of query by 
executing an Index Scan, then merge join with rest of the query.


This is all I know about LIKE optimization in PostgreSQL:

LIKE 'str%' -> optimized by normal indexes
LIKE '%str%' -> not optimized. You can use FullText, but then your 
wildcards will have to change to something not SQL-standard compatible 
solution...
LIKE '%str' -> can be optimized if you create index with column 
content reversed then query reversed as well. See code below for details.


How did I optimized "%str" queries (code implemented with help from 
the PgSql community):


CREATE OR REPLACE FUNCTION reverse(input character varying)
  RETURNS character varying AS
$BODY$
DECLARE
  result character varying = '';
  i int;
BEGIN
  FOR i IN 1..length(input) BY 2 LOOP
result = substr(input,i+1,1) || substr(input,i,1) || result;
  END LOOP;
  RETURN result;
END$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100;
create index idx on tb1 (reverse(nome));
select * from tb1 where reverse(nome) like reverse('%RICHTER');


Regards,

Edson.

Em 20/06/2012 14:10, Sam Z J escreveu:

Hi all

I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
How efficient is it if that's the only search criteria against a 
large table? how much does indexing the column help and roughly how 
much more space is needed for the index?


if the answers are too long, please point me to the relavant text =D

thanks

--
Zhongshi (Sam) Jiang
sammyjiang...@gmail.com 

Re: [GENERAL] efficiency of wildcards at both ends

[Alan Hodgson]

On Wednesday, June 20, 2012 01:10:03 PM Sam Z J wrote:
> Hi all
> 
> I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
> How efficient is it if that's the only search criteria against a large
> table? how much does indexing the column help and roughly how much more
> space is needed for the index?
> 

Indexing helps not at all. If the search string starts with a wildcard you 
will always get a sequential scan of the whole table.

Look at the full text search documentation for a better approach.

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

Re: [GENERAL] efficiency of wildcards at both ends

[Sam Z J]

thank you all for the useful information =D

On Wed, Jun 20, 2012 at 1:39 PM, Alan Hodgson  wrote:

> On Wednesday, June 20, 2012 01:10:03 PM Sam Z J wrote:
> > Hi all
> >
> > I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
> > How efficient is it if that's the only search criteria against a large
> > table? how much does indexing the column help and roughly how much more
> > space is needed for the index?
> >
>
> Indexing helps not at all. If the search string starts with a wildcard you
> will always get a sequential scan of the whole table.
>
> Look at the full text search documentation for a better approach.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Zhongshi (Sam) Jiang
sammyjiang...@gmail.com

Re: [GENERAL] efficiency of wildcards at both ends

[Thomas Kellerer]

Sam Z J wrote on 20.06.2012 19:10:

Hi all

I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
How efficient is it if that's the only search criteria against a large table? 
how much does indexing the column help and roughly how much more space is 
needed for the index?

if the answers are too long, please point me to the relavant text =D



Since 9.1 you can speed up such a query using a trigram index.

http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/
http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html

Another option might be to use the wildspeed extension

http://www.sai.msu.su/~megera/wiki/wildspeed

(never used that myself though)






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

Re: [GENERAL] Simple method to format a string?

[Ben Chobot]

On Jun 20, 2012, at 7:43 AM, Emi Lu wrote:

> Good morning,
> 
> Is there a simply method in psql to format a string?
> 
> For example, adding a space to every three consecutive letters:
> 
> abcdefgh -> *** *** ***
> 
> Thanks a lot!
> Emi

I'm unaware of such a function (it seems like a generic format() function's 
arguments would be so complex as to be almost unusable) but you can make use of 
locales to do some formatting for you. See 
http://www.postgresql.org/docs/9.1/static/locale.html.
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to determine to which version of PostgreSQL a patch was applied

[Norberto Dellê]

Hi all

I would like to determine to which version of PostgreSQL a certain patch
was applied.
I'm having trouble with a message flooding my log files and possibly
affecting the server
performance, and I found a patch that may shed some light over the problem:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=79b2ee20c8a041a85dd230c4e787bef22edae57b#patch1

How do I find the version to which this patch was applied?

Thanks

Norberto

Re: [GENERAL] How to create c language in postgresql database. Thanks.

[yxj]

Hi Albe,
 
Thanks. I will study it.
 
Regards.
 
Grace





At 2012-06-14 15:33:07,"Albe Laurenz"  wrote:
>leaf_yxj wrote:
>> Thanks for your answers. I really appreciate it. Although I don't
>understand the whole things you guys
>> mentioned to me. I think maybe I should do it by myself. I need to do
>a test. If there is any good
>> guide/white paper, please give me a link for me to study.
>
>I think the documentation is quite good and comprehensive:
>
>http://www.postgresql.org/docs/current/static/xfunc-c.html
>
>If you look for samples, the contrib modules will serve.
>
>Yours,
>Laurenz Albe

Re: [GENERAL] Simple method to format a string?

[Scott Marlowe]

This pretty much calls for a user defined function.  Plpgsql should
work, but if you're more comfy in perl or tcl there's pl/tcl and
pl/perl you can try as well.

On Wed, Jun 20, 2012 at 8:43 AM, Emi Lu  wrote:
> Good morning,
>
> Is there a simply method in psql to format a string?
>
> For example, adding a space to every three consecutive letters:
>
> abcdefgh -> *** *** ***
>
> Thanks a lot!
> Emi
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
To understand recursion, one must first understand recursion.

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

Re: [GENERAL] Simple method to format a string?

[Andy Colson]

On 6/20/2012 12:59 PM, Scott Marlowe wrote:

This pretty much calls for a user defined function.  Plpgsql should
work, but if you're more comfy in perl or tcl there's pl/tcl and
pl/perl you can try as well.

On Wed, Jun 20, 2012 at 8:43 AM, Emi Lu  wrote:

Good morning,

Is there a simply method in psql to format a string?

For example, adding a space to every three consecutive letters:

abcdefgh -> *** *** ***

Thanks a lot!
Emi

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





This is the perl func I use:  sorry about the formatting


-- mask char is 0 (zero).  anything else gets copied across
create or replace function applyMask(text, text) returns text as $$
my($mask, $src) = @_;

my $srcAt = 0;
my $srcLen = length($src);
my $result = '';

for my $i (0..length($mask)-1)
{
my $mchar = substr($mask, $i, 1);
if ($mchar eq '0')
{
if ($srcAt >= $srcLen)
{
$result .= ' ';
} else {
$result .= substr($src, $srcAt, 1);
$srcAt++;
}
} else {
$result .= $mchar;
}
}
return $result;
$$ language plperl;



For example:

select applyMask('(000) 000-', '1235551313');

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

Re: [GENERAL] efficiency of wildcards at both ends

[John R Pierce]

On 06/20/12 10:37 AM, Edson Richter wrote:


select * from tb1
  where nome like 'CARLOS%' or reverse(nome) like reverse('%CARLOS')

Should return same results as
select * from tb1
  where nome like '%CARLOS%'


no, that won't match 'abcCARLOSxyx'



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] How to determine to which version of PostgreSQL a patch was applied

[hubert depesz lubaczewski]

On Wed, Jun 20, 2012 at 02:45:16PM -0300, Norberto Dellê wrote:
> Hi all
> 
> I would like to determine to which version of PostgreSQL a certain patch
> was applied.
> I'm having trouble with a message flooding my log files and possibly
> affecting the server
> performance, and I found a patch that may shed some light over the problem:
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=79b2ee20c8a041a85dd230c4e787bef22edae57b#patch1
> 
> How do I find the version to which this patch was applied?

Simple. On the top of the page you can see "tree" link to this address:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;h=8f7336a210832e1d33c613ae24121d63e0a3c54a;hb=79b2ee20c8a041a85dd230c4e787bef22edae57b

in this tree, open "configure" file (blob, link:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=configure;h=ddc25d53ac1a52f3238ba96a7b58bb40a99f2264;hb=79b2ee20c8a041a85dd230c4e787bef22edae57b)
and check line # 601:
PACKAGE_VERSION='9.2devel'

this means that this particular patch was committed to 9.2version - i.e.
to upcomin 9.2 release.

Also - we can check in committers archive, in here:
http://archives.postgresql.org/pgsql-committers/2011-08/mail2.php
that this patch was applied *only* to 9.2devel.
To show difference - please note three mails from 13th of august, with
title "pgsql: Fix incorrect timeout handling during initial
authentication tra"
in their bodies we can see:
Branch: REL9_1_STABLE
Branch: REL9_0_STABLE
Branch: master (master being 9.2devel at the time).

Hope it helps.

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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] efficiency of wildcards at both ends

[Edson Richter]

Em 20/06/2012 15:03, John R Pierce escreveu:

On 06/20/12 10:37 AM, Edson Richter wrote:


select * from tb1
  where nome like 'CARLOS%' or reverse(nome) like reverse('%CARLOS')

Should return same results as
select * from tb1
  where nome like '%CARLOS%'


no, that won't match 'abcCARLOSxyx'




Perfect. I did not realized this case.

Thanks,

Edson.


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

Re: [GENERAL] Simple method to format a string?

[Bosco Rama]

Emi Lu wrote:
> Good morning,
> 
> Is there a simply method in psql to format a string?
> 
> For example, adding a space to every three consecutive letters:
> 
> abcdefgh -> *** *** ***

Depending on how you want 'extras' handled, you could work from
something like this:

   select trim(regexp_replace('123456', '...', '\& ', 'g'));

If you don't care about trailing space remove the 'trim()'.

The replacement string may need to be escaped differently depending
on your PG version and setting for standard_conforming_strings.  For
example:  E'\\& '

HTH

Bosco.

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

Re: [GENERAL] efficiency of wildcards at both ends

[Thomas Kellerer]

Alan Hodgson wrote on 20.06.2012 19:39:

I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%'
How efficient is it if that's the only search criteria against a large
table? how much does indexing the column help and roughly how much more
space is needed for the index?



Indexing helps not at all. If the search string starts with a wildcard you
will always get a sequential scan of the whole table.


Not necessarily: 
http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/


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

Re: [GENERAL] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[Merlin Moncure]

On Wed, Jun 20, 2012 at 12:15 PM, utsav  wrote:
> Ya but when i use the return next it gives me all OUT parameters but i will
> get last record of out parameter1 repetitive untill the last record of last
> out parameter . Sorry i didn't have output with me . Is there any other way
> to achive this ?
>
> Many Thanks for your help merlin ...

right -- exactly.  this is how set returning functions work.  Each
returned row contains both OUT variables.  You can't return a set of
parameter1 then swing around and return a set of parmameter2.  If you
want to return two independent sets, you are using the wrong
mechanism.

merlin

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

Re: [GENERAL] Simple method to format a string?

[Emi Lu]

select trim(regexp_replace('123456', '...', '\& ', 'g'));

If you don't care about trailing space remove the 'trim()'.

The replacement string may need to be escaped differently depending
on your PG version and setting for standard_conforming_strings.  For
example:  E'\\& '


After combined with several more replace(s), regexp_replace will provide 
me the expecting result.


Thanks!
Emi

--
select
regexp_replace(
   replace(
   replace(col-val, ' ', ''), '-', ''),
 replace...
'(...)', E'\\1 ', 'g')
from tn;

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

Re: [GENERAL] 32-bit libpq with 64-bit server

[Craig Ringer]

On 06/20/2012 11:07 PM, Marc Watson wrote:


Hello all,

On Windows, I have a 32-bit client application that uses the 32-bit 
libpq.dll. In testing the client application with a Postgres 9.2 
64-bit server I've noticed no problems, and was wondering if anyone 
knows of any caveats in this -- AFAICS the tcp communication is 
between the 32-bit libpq and the 64-bit backend is compatible.





Unless you go to considerable efforts to ensure otherwise, the protocol 
is text-based and isn't affected by endianness or word size. I'm not 
sure about the binary protocol, but it's rarely used.


--
Craig Ringer

Re: [GENERAL] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

merlin can u please suggest any solution for achiving this . I have tried
array but still i am not getting how to return different array and for
returning array i must have to pass one array in input parameter and that
only i can return that is where i got stuck .

Thanks

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5713714.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[GENERAL] Is it possible to send data in client end?

[AI Rumman]

I am using Postgresql 9.1. I know that COPY is used to export data as csv.
But COPY creates file in server end. I need to create a file in client end.
My application uses php.
Is there any tool for it?

Re: [GENERAL] Is it possible to send data in client end?

[John R Pierce]

On 06/20/12 9:13 PM, AI Rumman wrote:
I am using Postgresql 9.1. I know that COPY is used to export data as 
csv. But COPY creates file in server end. I need to create a file in 
client end.

My application uses php.
Is there any tool for it?



use COPY blahblah TO STDOUT;

in php, you can use pg_copy_to() which will put the results in a php 
array, one array row per line of output.







--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] Error message "psql: could not connect to server: No such file or directory"

[Stefan Schwarzer]

>> I need unfortunately to come back to this issue. I (again) re-installed Lion 
>> from scratch, and finally got everything working. The Postgres was running, 
>> I uploaded a couple of dumped SQL files. And then re-started the machine for 
>> another reason. And suddenly it says again the it can't connect to the 
>> server.
>> 
>> I tried
>> /usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data 
>> --encoding=UTF8 --locale=en_US
>> 
>> and
>> /usr/local/pgsql-9.1/bin/pg_ctl -D /usr/local/pgsql-9.1/data/ -l logfile 
>> start
>> 
>> and
>> sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist
>> 
>> … but nothing happens. I don't have the feeling that it's a problem with the 
>> "postgres" and "_postgres" users (as it has worked before already).
>> 
>> Can anyone give me a hint how I can figure out where the problem lies and 
>> what I can do about it? Why did it work before I restarted the computer and 
>> why does it not work anymore?
>> 
>> Thanks a lot for any hints!
>> 
> 
> When troubleshooting I am firm believer in working from the known to the 
> unknown. In that vein:
> 
> 1) Is there /usr/local/pgsql-9.1/data/?

Yes: 

drwx--  18 _postgres  _postgres   612 Jun 19 09:41 data


>   a) If so are there subdirectories in it indicating cluster was created? 
> i.e. base/ global/ pg_clog/, etc

Yes:

drwx--   7 _postgres  _postgres238 Jun 19 08:59 base
drwx--  43 _postgres  _postgres   1462 Jun 19 09:41 global
….


> 2) When you say nothing happens does that mean nothing is written to log file 
> and/or terminal? No process shows up in process list?

When I do:  sudo launchctl load 
/Library/LaunchDaemons/org.postgresql.postgres.plist
it says:org.postgresql.postgres: Already loaded

When I do:  /usr/local/pgsql-9.1/bin/initdb -U postgres -D 
/usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US
it says:The files belonging to this database system will be owned 
by user "xxx".
   This user must also own the server process.
   The database cluster will be initialized with locale 
en_US.
   The default text search configuration will be set to 
"english".
   initdb: could not access directory 
"/usr/local/pgsql-9.1/data": Permission denied

Same when using "_postgres" instead of "postgres".

When I do: sudo su - _postgres /usr/local/pgsql-9.1/bin/initdb -U postgres 
-D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US
it seems to run, but still, same error message "psql: could not connect…."

And still nothing in the process list (using "ps auxw | grep post").


> 3) If something does happen in, terms of error messages, what are they?
> 
> 4) Have you looked at system (not Postgres specific) logs to see if this a 
> system issue?

I looked at Apple's Console, where all (?) logs/messages are being assembled, 
but nothing worrisome there. is there any other specific place where I can look 
for error messages?


> 5) What user are you running the above commands as?

I am user with "Administrator" rights.


>  a) What user is associated with the Postgres data directory(if it exists)?

See above, "_postgres". There is or could be confusion between Apple's 
"_postgres" and the normal "postgres" user. But it looks as it isn't in this 
case..

Thanks for your help!


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

[GENERAL] Output of query_to_xml

[P. Broennimann]

Hi there

In my stored function I use:

select query_to_xml('select * from table12', true, true, '') into ...


The result is OK but there is always an empty line:

http://www.w3.org/2001/XMLSchema-instance";>
 *<-- Empty line here*
 FG8976SDFRETG
  NL
 2011-02-28
  MFT
 GAB
  TAFP



Is this supposed to be or is this a cosmetic bug?

Thanks,
P.

Re: [GENERAL] How to include Tablefunc as an extension

[Stefan Schwarzer]

 I read through the Postgres doc and many Google results, but it seems
 still unclear to me how to include additional packages into my postgres
 database. I see that there are a few installed under
 "/usr/local/pgsql-9.1/share/extension/" (I am on Lion and installed the
 Kyngchaos libs). But as I want to install crosstab now, I downloaded the
 source code for postgres, run a make/install - and now wonder what to do
 with it. I could eventually load the .sql file as usual; but it seems this
 should be now avoided, and loaded as an extension. I tried to copy three
 files (--1.0.sql, --unpackaged.sql and the normal .sql) to the folder and
 then load it from PgAdmin, but this results in an error message ("could
 not access file "$libdir/tablefunc"").
>>> 
>>> This should do the trick:
>>> 
>>> CREATE EXTENSION crosstab;
>> 
>> Thanks a lot. Yes, that's the command I used when it launches the above 
>> error message. The question is more about: "where do I need to place the 
>> tablefunc files (and which ones) in order to execute successfully that 
>> command"?
> 
> make install should copy files including $libdir/tablefunc.  If not,
> you should take a look at the output of the make command.  Make sure
> your PATH points to pg_config that your database is running with.

I do as indicated in Kyngchaos ReadMe file:

export PATH="/usr/local/pgsql-9.1/bin:$PATH"
export USE_PGXS=1
make
sudo make install


Problem is that when I run the install, it shows this:

Makefile:19: ../../src/Makefile.global: No such file or directory
Makefile:20: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.

And this, after looking around, seem to indicate that I need to configure the 
Postgres source code. But there, with:

./configure --with-openssl --with-pam --with-krb5 --with-gssapi --with-ldap 
--enable-thread-safety --with-bonjour --with-python --without-perl --enable-nls 
--with-libxml CFLAGS=-Os x86_64 LD=gcc LDLFAGS=-arch x86_64

I run into another problem:

configure: WARNING: you should use --build, --host, --target
configure: WARNING: you should use --build, --host, --target
checking build system type... x86_64-pc-none
checking host system type... x86_64-pc-none
checking which template to use... configure: error:
***
PostgreSQL has apparently not been ported to your platform yet.

Thanks for any hints how to solve that riddle!
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general