Re: [GENERAL] Array string casts with SELECT but not SELECT DISTINCT

[Ken Tanzer]

I'm not able to run this unless I'm the Postgres super user.  But if I run
it as such, it tells me that cast already exists anyway.

CREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;
ERROR:  cast from type text to type character varying already exists

Of course this will work fine:
INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}'::varchar[];

I was mostly surprised by having DISTINCT added to a SELECT make things
break.  It may be too obscure an issue to be worth adding, but nothing on
the DISTINCT documentation suggests this possibility.

"If DISTINCT is specified, all duplicate rows are removed from the result
set..."
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT

Cheers,
Ken

On Fri, Feb 20, 2015 at 6:30 PM, sridhar bamandlapally <
sridhar@gmail.com> wrote:

> >>>ERROR:  column "my_array" is of type character varying[] but expression
> is of type text
>
> please try this below, may be this should help
>
> CREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;
>
> just for info:
> actually this should be available in default
>
>
> On Fri, Feb 20, 2015 at 9:48 AM, Ken Tanzer  wrote:
>
>> Hi.  Here's a boiled down example of something that caught me by surprise:
>>
>> ag_reach_test=> CREATE TEMP TABLE foo (my_array varchar[]);
>> CREATE TABLE
>> ag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}';
>> INSERT 0 1
>> ag_reach_test=> SELECT my_array[1],array_length(my_array,1) FROM foo;
>>  my_array | array_length
>> --+--
>>  TEST |1
>> (1 row)
>>
>> ag_reach_test=> INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
>> ERROR:  column "my_array" is of type character varying[] but expression
>> is of type text
>> LINE 1: INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
>>^
>> HINT:  You will need to rewrite or cast the expression.
>>
>> It's easy enough to add a cast, but I was curious if this was expected
>> and desired behavior.   Thanks.
>>
>> Ken
>>
>> --
>> AGENCY Software
>> A Free Software data system
>> By and for non-profits
>> *http://agency-software.org/ *
>> *https://agency-software.org/demo/client
>> *
>> ken.tan...@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>>  to
>> learn more about AGENCY or
>> follow the discussion.
>>
>
>


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Array string casts with SELECT but not SELECT DISTINCT

[David G Johnston]

On Fri, Feb 20, 2015 at 7:31 PM, sridhar bamandlapally [via PostgreSQL] <
ml-node+s1045698n5838807...@n5.nabble.com> wrote:

> >>>ERROR:  column "my_array" is of type character varying[] but expression
> is of type text
>
> please try this below, may be this should help
>
> CREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;
>
> just for info:
> actually this should be available in default
>
>
​What is the point of doing this?  Did you do this and then run the OP's
query and see if it solved the problem?  Do you understand the "type[]"
means "array version of type"?

David J.​




--
View this message in context: 
http://postgresql.nabble.com/Array-string-casts-with-SELECT-but-not-SELECT-DISTINCT-tp5838663p5838808.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Array string casts with SELECT but not SELECT DISTINCT

[sridhar bamandlapally]

>>>ERROR:  column "my_array" is of type character varying[] but expression
is of type text

please try this below, may be this should help

CREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;

just for info:
actually this should be available in default


On Fri, Feb 20, 2015 at 9:48 AM, Ken Tanzer  wrote:

> Hi.  Here's a boiled down example of something that caught me by surprise:
>
> ag_reach_test=> CREATE TEMP TABLE foo (my_array varchar[]);
> CREATE TABLE
> ag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}';
> INSERT 0 1
> ag_reach_test=> SELECT my_array[1],array_length(my_array,1) FROM foo;
>  my_array | array_length
> --+--
>  TEST |1
> (1 row)
>
> ag_reach_test=> INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
> ERROR:  column "my_array" is of type character varying[] but expression is
> of type text
> LINE 1: INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
>^
> HINT:  You will need to rewrite or cast the expression.
>
> It's easy enough to add a cast, but I was curious if this was expected and
> desired behavior.   Thanks.
>
> Ken
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://agency-software.org/demo/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

[GENERAL] how to convert "output deleted/inserted into" in My SQL to Postgres

[Michael_LT]

hey guys, I'm kinda new to Postgres and I'm learning it now. I have work to
convert some stored procedures in MySQL to Postgres and I came across an
issue here that I can't solve:

update  db.user
 set Deleted= 1
, UpdateTerminal = @UpdateTerminal
 , UpdateUser = @UpdateUser
 , UpdateDate = @UpdateDate
output deleted.CreditTypeID
 , deleted.CreditID
 , deleted.Amount
  into @ReconDeleted
 where Deleted = 0
   and ClientID = @ClientID;

I think it tried to update a couple of columns in table user and insert
values in other 3 columns into another table ReconDeleted at the same time.
I have issues converting "OUTPUT INTO" and "deleted" items, with my limited
knowledge about Postgres, I don't think we have those in pg. Can someone
tell me how to convert it? Thanks.

PS: it's my first time using this form, nice to meet you guys:)



--
View this message in context: 
http://postgresql.nabble.com/how-to-convert-output-deleted-inserted-into-in-My-SQL-to-Postgres-tp5838750.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] parallel dump fails to dump large tables

[Shanker Singh]

I tried turning off ssl renegotiation by setting "ssl_renegotiation_limit = 0" 
in postgresql.conf but it had no effect. The parallel dump still fails on large 
tables consistently.

Thanks
Shanker


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Saturday, February 14, 2015 9:00 AM
To: r...@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables

"Raymond O'Donnell"  writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres 
>> release 9.4. The size of the table is large(54GB). The dump fails 
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died 
>> unexpectedly". After this error the pg_dump aborts. The error log 
>> file gets the following message:
>> 
>> 2015-02-09 15:22:04 PST [8636]: [2-1] 
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT:  COPY iiirecord.varfield (id, field_type_tag, marc_tag, 
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num, 
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1] 
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL:  connection to client lost

> There's your problem - something went wrong with the network.

I'm wondering about SSL renegotiation failures as a possible cause of the 
disconnect --- that would explain why it only happens on large tables.

regards, tom lane


-- 
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 convert "output deleted/inserted into" in MySQL to Postgres

[Thomas Kellerer]

Tong Michael wrote on 20.02.2015 21:59:


hey guys, I'm kinda new to Postgres and I'm learning it now. I have work to 
convert some stored procedures in MySQL
to Postgres and I came across an issue here that I can't solve:

update  db.user
  set Deleted= 1
 , UpdateTerminal = @UpdateTerminal
  , UpdateUser = @UpdateUser
  , UpdateDate = @UpdateDate
 output deleted.CreditTypeID
  , deleted.CreditID
  , deleted.Amount
   into @ReconDeleted
  where Deleted = 0
and ClientID = @ClientID;

I think it tried to update a couple of columns in table user and insert values 
in other 3 columns into another
table ReconDeleted at the same time. I have issues converting "OUTPUT INTO" and 
"deleted" items,
with my limited knowledge about Postgres, I don't think we have those in pg. 
Can someone tell me how to convert it?


That is not valid for MySQL it does not have an "OUTPUT DELETED" option for any 
DML statement.
Plus: MySQL does not have table variables.

The looks much more like SQL Server/T-SQL.






--
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] Re: How to convert "output deleted/inserted into" in MySQL to Postgres

[Paul Jungwirth]

Hi Michael,


hey, john, i did as you said like:
update db.user
set deleted= 1,
updateterminal = UpdateTerminal,
updateuser = UpdateUser,
updatedate = UpdateDate
  returning
credittypeid,
creditid,
amount
   into ReconDeleted
  where deleted = 0
and clientid = ClientID
);

I have ERROR:  syntax error at or near "into"


I think what you need here is a Postgres CTE, because you need to 
separate the UPDATE from the INSERT. You can do your query like this:


WITH changes AS (
 update db.user
 set deleted= 1,
 updateterminal = UpdateTerminal,
 updateuser = UpdateUser,
 updatedate = UpdateDate
   returning
 credittypeid,
 creditid,
 amount
)
INSERT INTO ReconDeleted
SELECT * FROM changes
;

(not tested, but see CTE docs if you have troubles)

Paul



--
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] Re: How to convert "output deleted/inserted into" in MySQL to Postgres

[Adrian Klaver]

On 02/20/2015 01:41 PM, Michael_LT wrote:

hey, john, i did as you said like:
update db.user
set deleted= 1,
updateterminal = UpdateTerminal,
updateuser = UpdateUser,
updatedate = UpdateDate
  returning
credittypeid,
creditid,
amount
   into ReconDeleted
  where deleted = 0
and clientid = ClientID
);

I have ERROR:  syntax error at or near "into"

ReconDeleted has three columns
 CreditTypeID bigint,
 CreditID bigint,
 Amount   money



To have this make any sense to the rest of us, you will need to show 
your complete function.





--
View this message in context: 
http://postgresql.nabble.com/How-to-convert-output-deleted-inserted-into-in-MySQL-to-Postgres-tp5838762p5838771.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.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] Re: How to convert "output deleted/inserted into" in MySQL to Postgres

[Michael_LT]

hey, john, i did as you said like:
update db.user
   set deleted= 1,
   updateterminal = UpdateTerminal,
   updateuser = UpdateUser,
   updatedate = UpdateDate
 returning 
   credittypeid,
   creditid,
   amount
  into ReconDeleted
 where deleted = 0
   and clientid = ClientID
   );

I have ERROR:  syntax error at or near "into"

ReconDeleted has three columns 
CreditTypeID bigint,
CreditID bigint,
Amount   money



--
View this message in context: 
http://postgresql.nabble.com/How-to-convert-output-deleted-inserted-into-in-MySQL-to-Postgres-tp5838762p5838771.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] How to convert "output deleted/inserted into" in MySQL to Postgres

[John R Pierce]

On 2/20/2015 12:59 PM, Tong Michael wrote:
I think it tried to update a couple of columns in table user and 
insert values in other 3 columns into another table ReconDeleted at 
the same time. I have issues converting "OUTPUT INTO" and "deleted" 
items, with my limited knowledge about Postgres, I don't think we have 
those in pg. Can someone tell me how to convert it? Thanks.


no idea what 'deleted' means, but, assuming this in is plpgsql, use 
UPDATE  RETURNING ...INTO rather than OUTPUT INTO,


http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

now, if this in a trigger function, you can refer to OLD. and NEW.



--
john r pierce  37N 122W
somewhere on the middle of the left coast

[GENERAL] How to convert "output deleted/inserted into" in MySQL to Postgres

[Tong Michael]

hey guys, I'm kinda new to Postgres and I'm learning it now. I have work to
convert some stored procedures in MySQL to Postgres and I came across an
issue here that I can't solve:

update  db.user
 set Deleted= 1
, UpdateTerminal = @UpdateTerminal
 , UpdateUser = @UpdateUser
 , UpdateDate = @UpdateDate
output deleted.CreditTypeID
 , deleted.CreditID
 , deleted.Amount
  into @ReconDeleted
 where Deleted = 0
   and ClientID = @ClientID;

I think it tried to update a couple of columns in table user and insert
values in other 3 columns into another table ReconDeleted at the same time.
I have issues converting "OUTPUT INTO" and "deleted" items, with my limited
knowledge about Postgres, I don't think we have those in pg. Can someone
tell me how to convert it? Thanks.

PS: it's my first time using this form, nice to meet you guys:)

Re: [GENERAL] PL/PgSQL and pg_temp pseudo-schema

[Melvin Davidson]

Take note. Temp tables are only visable, available to the sessions that
created them. You cannot access them from a different session. Is that the
problem you experience> If not, please provide a specific example of what
your problem is.

On Fri, Feb 20, 2015 at 3:34 AM, Pavel Stehule 
wrote:

> Hi
>
> It is little bit strange, it works for me
>
> postgres=# create temp table foo(a int);
> CREATE TABLE
> postgres=# do $$ begin insert into foo values(10); end $$; -- plpgsql
> DO
> postgres=# select * from foo;
>  a
> 
>  10
> (1 row)
>
> Regards
>
> Pavel
>
>
>
> 2015-02-20 9:07 GMT+01:00 Vincenzo Romano :
>
>> PL/PgSQL doesn't know the pg_temp meta schema in case you need to
>> really work on that TEMP TABLE.
>> At the moment I had to move those DDL/DML queries within a "plain" SQL
>> function.
>>
>> Is this a feature or a bug? (Seriously, I mean! :-)
>>
>> TIA.
>>
>> --
>> Vincenzo Romano - NotOrAnd.IT
>> Information Technologies
>> --
>> NON QVIETIS MARIBVS NAVTA PERITVS
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] rollback in C functions

[Juan Pablo L]

Just an update, it was making a mistake, the execution of PQexecute, to
execute the query, was wrapped in a function called "PGresult
*db_execute()" that was returning the PGresult as NULL, i completely
forgot this, inside that function the exception was being caught and
discarded so by the time i got the PGresult pointer back in the caller it
contained nothing so trying to read any error from it at that point was
useless.

Just to clarify and for future reference, the proposal to use ereport
worked perfectly as proposed by the all members.

thank you very much!!

On 19 February 2015 at 16:42, Juan Pablo L 
wrote:

> i tried this but the call to PQresultErrorField(PGresult,
> PG_DIAG_SQLSTATE) is returning NULL, this is what trigger the exception in
> the function code:
>
> ereport(ERROR,(errcode(ERRCODE_SQL_ROUTINE_EXCEPTION),errmsg("Plan with id
> %s does not allow balance with id %s",plan_id,in_balanceid)));
>
> and this is the caller code:
>
> if(PQresultStatus(pg_res) == PGRES_FATAL_ERROR)
> {
> char *t = PQresultErrorField(pg_res,PG_DIAG_SQLSTATE);
> log_debug("[C%03dH%03d] PQres returned NULL:
> %s",handler->my_connection->id,handler->id,t);
>  }
>
> the  above call log_debug show that t is NULL. am i doing something wrong
> ? thanks!
>
> On 19 February 2015 at 16:27, Juan Pablo L 
> wrote:
>
>> Thank you, i will try this, honestly i was checking if PGResult is NULL,
>> when i trigger the exception i always get NULL so i did not any further but
>> i will try this .
>>
>> On 19 February 2015 at 16:22, John R Pierce  wrote:
>>
>>> On 2/19/2015 2:02 PM, Juan Pablo L wrote:
>>>
 Thank you Alvaro, i m afraid ereport seems to be the way, that it is
 complicated to catch this error code in the code of the caller. cause you
 have to use a callback etc etc

>>>
>>> a query that triggers ereport(ERROR,) should return a PGresult* that
>>> you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so
>>> you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back
>>> the SQLSTATE code.
>>>
>>>
>>>
>>> --
>>> john r pierce  37N 122W
>>> somewhere on the middle of the 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
>>>
>>
>>
>

[GENERAL] Oracle to PostgreSQL migration

[sridhar bamandlapally]

Hi

With minimal application code changes we are able to migrate database from
Oracle to PostgreSQL successfully (including performance issues) on
production

This we did on high read intensive database sized 900+GB

Thanks to PostgreSQL

Thanks
Sridhar BN

Re: [GENERAL] stored procedure variable names

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of inspector morse
Sent: Thursday, February 19, 2015 7:58 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] stored procedure variable names

In all other DBMS, the variable names have a distinctive character to 
differentiate between variables and column names:
Example:
SQL Server uses @
MySql uses ?
Oracle uses :
Firebirdsql uses :
It makes it easier to write and manage queries especially in stored procedures.
Just compare the below:
create stored procedure get_user_for_editing(user_id int, out username varchar)
begin
   select username into @username from users where user_id = @user_id;
end;

to this mess:

create stored procedure get_user_for_editing(user_id int, out username varchar)
begin
   select u.username into get_user_for_editing.username from users u where 
get_user_for_editing.user_id = get_user_for_editing.user_id;
end;
Prefixing the variables (ex: p_user_id) makes the application code harder to 
write as we have a lot of dynamic code that is expecting "user_id" instead of 
"p_user_id".
Is there any plan to add a character to differentiate between variables?


That: “Oracle uses :” is simply not true.
There is no such requirement (mandatory prefix) in Oracale’s PlSQL.

In Oracle, only when you use dynamic SQL (EXECUTE ‘…’) with “USING” (to specify 
variable values) – then variable inside EXECUTE ‘…’ should have ‘:’ prefix.
The only difference in Postgres when using dynamic SQL variables inside EXECUTE 
‘…’ are “positional” prefixed with ‘$’, eg.: $1, $2,…

Regards,
Igor Neyman

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

2015-02-09 16:10 GMT-05:00 Jim Nasby :

> On 2/9/15 11:51 AM, Guillaume Lelarge wrote:
>
>> According to this page
>> , exception
>> 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
>> referenced memory at 0x%08lx. The memory could not be %s. This is not of
>> much help to me.
>>
>
> In my experience that means that your data is corrupted.
>
>  I hope these additional bits of information can help someone figuring
>> out a solution to get my cluster up and running again.
>>
>> PS. I was thinking of reinstalling PGSQL over my current install but
>> keeping my PGDATA. I've done it in the past for fixing problems with
>> starting the service and it worked. What do you think?
>>
>
> You could try it, but as Guillaume Drolet mentioned I don't see this
> helping.
>
> Since this is happening on your original database, I suspect that's what's
> been corrupted. In my experience, this means you either have faulty
> hardware, or there's a misconfiguration that means fsync isn't doing what
> it's supposed to do.
>

For those interested, I reinstalled the PGSQL binaries over, keeping my
PGDATA and tablespace. It stopped the crashes mentioned in the previous
posts, at least for now. We'll see if the crashes come back.

> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [GENERAL] PL/PgSQL and pg_temp pseudo-schema

[Pavel Stehule]

Hi

It is little bit strange, it works for me

postgres=# create temp table foo(a int);
CREATE TABLE
postgres=# do $$ begin insert into foo values(10); end $$; -- plpgsql
DO
postgres=# select * from foo;
 a

 10
(1 row)

Regards

Pavel



2015-02-20 9:07 GMT+01:00 Vincenzo Romano :

> PL/PgSQL doesn't know the pg_temp meta schema in case you need to
> really work on that TEMP TABLE.
> At the moment I had to move those DDL/DML queries within a "plain" SQL
> function.
>
> Is this a feature or a bug? (Seriously, I mean! :-)
>
> TIA.
>
> --
> Vincenzo Romano - NotOrAnd.IT
> Information Technologies
> --
> NON QVIETIS MARIBVS NAVTA PERITVS
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] PL/PgSQL and pg_temp pseudo-schema

[Vincenzo Romano]

PL/PgSQL doesn't know the pg_temp meta schema in case you need to
really work on that TEMP TABLE.
At the moment I had to move those DDL/DML queries within a "plain" SQL function.

Is this a feature or a bug? (Seriously, I mean! :-)

TIA.

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS


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