Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

[Sridhar N Bamandlapally]

Just for information,

PG current behavior,

"\set AUTOCOMMIT OFF" implicitly does/open "BEGIN;" block

So, "\set AUTOCOMMIT ON" has no effect once "\set AUTOCOMMIT OFF" is issued
until "END;" or "COMMIT;" or "ROLLBACK;"

however, I think if exit session release the transactions then change
session should also release the transactions

Thanks
Sridhar




On Mon, Aug 8, 2016 at 10:34 PM, Vik Fearing  wrote:

> On 08/08/16 17:02, Robert Haas wrote:
> > On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed 
> wrote:
> >> Thank you for inputs everyone.
> >>
> >> The opinions on this thread can be classified into following
> >> 1. Commit
> >> 2. Rollback
> >> 3. Error
> >> 4. Warning
> >>
> >> As per opinion upthread, issuing implicit commit immediately after
> switching
> >> autocommit to ON, can be unsafe if it was not desired.  While I agree
> that
> >> its difficult to judge users intention here, but if we were to base it
> on
> >> some assumption, the closest would be implicit COMMIT in my
> opinion.There is
> >> higher likelihood of a user being happy with issuing a commit when
> setting
> >> autocommit ON than a transaction being rolled back.  Also there are
> quite
> >> some interfaces which provide this.
> >>
> >> As mentioned upthread, issuing a warning on switching back to autocommit
> >> will not be effective inside a script. It won't allow subsequent
> commands to
> >> be committed as set autocommit to ON is not committed. Scripts will
> have to
> >> be rerun with changes which will impact user friendliness.
> >>
> >> While I agree that issuing an ERROR and rolling back the transaction
> ranks
> >> higher in safe behaviour, it is not as common (according to instances
> stated
> >> upthread) as immediately committing any open transaction when switching
> back
> >> to autocommit.
> >
> > I think I like the option of having psql issue an error.  On the
> > server side, the transaction would still be open, but the user would
> > receive a psql error message and the autocommit setting would not be
> > changed.  So the user could type COMMIT or ROLLBACK manually and then
> > retry changing the value of the setting.
>
> This is my preferred action.
>
> > Alternatively, I also think it would be sensible to issue an immediate
> > COMMIT when the autocommit setting is changed from off to on.  That
> > was my first reaction.
>
> I don't care for this very much.
>
> > Aborting the server-side transaction - with or without notice -
> > doesn't seem very reasonable.
>
> Agreed.
> --
> Vik Fearing  +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Online DW

[Sridhar N Bamandlapally]

Ok, let me put this way,

I need every transaction coming from application sync with both production
and archive db,
but the transactions I do to clean old data(before 7 days) on production db
in daily maintenance window should not sync with archive db,

Archive db need read-only, used for maintaining integrity with other
business applications

Issue here is,
1. etl is scheduler, cannot run on every transaction, even if it does, its
expensive

2. Materialize view(refresh on commit) or slony, will also sync clean-up
transactions

3. Replication is not archive, definitely not option

I say, every online archive db is use case for this.

Thanks
Sridhar
Opentext


On 10 Jun 2016 22:36, "David G. Johnston" <david.g.johns...@gmail.com>
wrote:

> On Fri, Jun 10, 2016 at 4:11 AM, Sridhar N Bamandlapally <
> sridhar@gmail.com> wrote:
>
>> Hi
>>
>> Is there any feature in PostgreSQL where online DW (Dataware housing) is
>> possible ?
>>
>> am looking for scenario like
>>
>> 1. Production DB will have CURRENT + LAST 7 DAYS data only
>>
>> 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY
>>
>> expecting something like streaming, but not ETL
>>
>>
> ​The entire DB couldn't operate this way since not every record has a
> concept of time and if you use any kind of physical time you are going to
> have issues as well.
>
> First impression is you want to horizontally partition your
> "time-impacted" tables so that each partition contains only data having the
> same ISO Week number in the same ISO Year.
>
> Remove older tables from the inheritance and stick them on a separate
> tablespace and/or stream them to another database.
>
> As has been mentioned there are various tools out there today that can
> likely be used to fulfill whatever fundamental need you have.  "Not ETL" is
> not a need though, its at best a "nice-to-have" unless you are willing to
> forgo any solution to your larger problem just because the implementation
> is not optimal.
>
> Unless you define your true goals and constraints its going to be hard to
> make recommendations.
>
> David J.
>
>

Re: [HACKERS] Online DW

[Sridhar N Bamandlapally]

One thing looks possible ( feature not available), just an idea

example/syntax:

BEGIN NOARCHIVE;

  --- transaction-1
  --- transaction-2
  .
  .
  --- transaction-N

END;

This/These will be performed in Production to clean-up archive which will
not be sync with Archive/DW DB only

one heads-up is Archive/DW DB may need to build WITHOUT CONSTRAINTS

May need to introduce ARCHIVE system/tag in pg_hba.conf

Thanks
Sridhar
OpenText














On Fri, Jun 10, 2016 at 2:22 PM, Craig Ringer <cr...@2ndquadrant.com> wrote:

> On 10 June 2016 at 16:11, Sridhar N Bamandlapally <sridhar@gmail.com>
> wrote:
>
>> Hi
>>
>> Is there any feature in PostgreSQL where online DW (Dataware housing) is
>> possible ?
>>
>> am looking for scenario like
>>
>> 1. Production DB will have CURRENT + LAST 7 DAYS data only
>>
>> 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY
>>
>> expecting something like streaming, but not ETL
>>
>
> There's nothing built-in, but that's exactly the sort of thing pglogical
> is intended for. You can also build something along those lines with
> Londiste fairly easily.
>
> Hopefully this is the sort of thing we can move toward with built-in
> logical replication in coming releases.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

[HACKERS] Online DW

[Sridhar N Bamandlapally]

Hi

Is there any feature in PostgreSQL where online DW (Dataware housing) is
possible ?

am looking for scenario like

1. Production DB will have CURRENT + LAST 7 DAYS data only

2. Archive/DW DB will have CURRENT + COMPLETE HISTORY

expecting something like streaming, but not ETL

Thanks
Sridhar

Re: [HACKERS] OUT parameter and RETURN table/setof

[Sridhar N Bamandlapally]

Existing application code written to call function in Oracle which return
no.of rows in out parameter and return-values is cursor-result

this need migrate to PostgreSQL, need help here

example: (actual function declaration only)
*Oracle:*
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR2,
v_rowsfound OUT INTEGER,
result_cursor1 OUT SYS_REFCURSOR
) ...


*PostgreSQL:*
*method 1*:
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR,
v_rowsfound OUT INTEGER,
result_cursor1 OUT REFCURSOR
) ...

but this approach issue is, need to do in BEGIN - END block inside
with FETCH ALL IN ""
  - here we need/think common approach for database

*method 2:*
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR,
v_rowsfound OUT INTEGER)
RETURNS TABLE/SETOF
...

this approach is not working


Thanks
Sridhar
OpenText







On Mon, Jun 6, 2016 at 5:57 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally <
> sridhar@gmail.com> wrote:
>
>> Hi
>>
>> Is there any option in PGPLSQL which can RETURNS table or SETOF rows
>> along with an OUT parameter?
>>
>>
> ​No, there would be no point given the internals of how functions work.
>
> ​What is it you are trying to do?
>
> David J.
> ​
>
>

[HACKERS] OUT parameter and RETURN table/setof

[Sridhar N Bamandlapally]

Hi

Is there any option in PGPLSQL which can RETURNS table or SETOF rows along
with an OUT parameter?

please

Thanks
Sridhar
OpenText

Re: [HACKERS] [GENERAL] NULL concatenation

[Sridhar N Bamandlapally]

Hi Adam

we need simple concatenation of all variables(which values may come NULL or
valid-values based on functional process),

coalesce is different functionality

Thanks
Sridhar
OpenText


On Thu, May 12, 2016 at 4:56 PM, Adam Pearson <
adam.pear...@realisticgames.co.uk> wrote:

> Hello Sridhar,
>
>   Have you tried the 'coalesce' function to handle the nulls?
>
>
> Kind Regards,
>
> Adam Pearson
> --
> *From:* pgsql-general-ow...@postgresql.org <
> pgsql-general-ow...@postgresql.org> on behalf of Sridhar N Bamandlapally <
> sridhar@gmail.com>
> *Sent:* 12 May 2016 09:47
> *To:* PG-General Mailing List; PostgreSQL-hackers
> *Subject:* [GENERAL] NULL concatenation
>
> Hi
>
> In migration, am facing issue with NULL concatenation in plpgsql,
> by concatenating NULL between any where/position to Text / Varchar, the
> total string result is setting value to NULL
>
>
> *In Oracle:*
>
> declare
> txt1 VARCHAR2(100) := 'ABCD';
> txt2 VARCHAR2(100) := NULL;
> txt3 VARCHAR2(100) := 'EFGH';
> txt VARCHAR2(100) := NULL;
> begin
>   txt:= txt1 || txt2 || txt3;
>   dbms_output.put_line (txt);
> end;
> /
>
> abcdefgh   *===>return value*
>
>
>
> *In Postgres*
>
> do $$
> declare
> txt1 text := 'ABCD';
> txt2 text := NULL;
> txt3 text := 'EFGH';
> txt text := NULL;
> begin
> txt:= txt1 || txt2 || txt3;
> raise notice '%', txt;
> end$$ language plpgsql;
>
> NOTICE:*===> return value*
>
>
> SQL-Server also does same like Oracle
>
> Is there any way alternate we have for same behavior in PostgreSQL
>
> Please
>
> Thanks
> Sridhar
> OpenText
>
>

Re: [HACKERS] NULL concatenation

[Sridhar N Bamandlapally]

Thanks Pavel

Great !!

I was thinking both || and CANCAT does same

Thanks again

-
Sridhar
OpenText


On Thu, May 12, 2016 at 2:22 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

> Hi
>
> 2016-05-12 10:47 GMT+02:00 Sridhar N Bamandlapally <sridhar@gmail.com>
> :
>
>> Hi
>>
>> In migration, am facing issue with NULL concatenation in plpgsql,
>> by concatenating NULL between any where/position to Text / Varchar, the
>> total string result is setting value to NULL
>>
>>
>> *In Oracle:*
>>
>> declare
>> txt1 VARCHAR2(100) := 'ABCD';
>> txt2 VARCHAR2(100) := NULL;
>> txt3 VARCHAR2(100) := 'EFGH';
>> txt VARCHAR2(100) := NULL;
>> begin
>>   txt:= txt1 || txt2 || txt3;
>>   dbms_output.put_line (txt);
>> end;
>> /
>>
>> abcdefgh   *===>return value*
>>
>>
>>
>> *In Postgres*
>>
>> do $$
>> declare
>> txt1 text := 'ABCD';
>> txt2 text := NULL;
>> txt3 text := 'EFGH';
>> txt text := NULL;
>> begin
>> txt:= txt1 || txt2 || txt3;
>> raise notice '%', txt;
>> end$$ language plpgsql;
>>
>> NOTICE:*===> return value*
>>
>>
>> SQL-Server also does same like Oracle
>>
>> Is there any way alternate we have for same behavior in PostgreSQL
>>
>
> use function concat
> http://www.postgresql.org/docs/9.5/static/functions-string.html
>
>  postgres=# select concat('AHOJ', NULL,'XXX');
>  concat
> -
>  AHOJXXX
> (1 row)
>
> Regards
>
> Pavel
>
>
>> Please
>>
>> Thanks
>> Sridhar
>> OpenText
>>
>>
>

[HACKERS] NULL concatenation

[Sridhar N Bamandlapally]

Hi

In migration, am facing issue with NULL concatenation in plpgsql,
by concatenating NULL between any where/position to Text / Varchar, the
total string result is setting value to NULL


*In Oracle:*

declare
txt1 VARCHAR2(100) := 'ABCD';
txt2 VARCHAR2(100) := NULL;
txt3 VARCHAR2(100) := 'EFGH';
txt VARCHAR2(100) := NULL;
begin
  txt:= txt1 || txt2 || txt3;
  dbms_output.put_line (txt);
end;
/

abcdefgh   *===>return value*



*In Postgres*

do $$
declare
txt1 text := 'ABCD';
txt2 text := NULL;
txt3 text := 'EFGH';
txt text := NULL;
begin
txt:= txt1 || txt2 || txt3;
raise notice '%', txt;
end$$ language plpgsql;

NOTICE:*===> return value*


SQL-Server also does same like Oracle

Is there any way alternate we have for same behavior in PostgreSQL

Please

Thanks
Sridhar
OpenText

[HACKERS] pg_largeobject

[Sridhar N Bamandlapally]

Hi

pg_largeobject is creating performance issues as it grow due to single
point storage(for all tables)

is there any alternate apart from bytea ?

like configuration large-object-table at table-column level and oid
PK(primary key) stored at pg_largeobject


Thanks
Sridhar

[HACKERS] Nested funtion

[Sridhar N Bamandlapally]

Hi

Is there any way to create nested function?

oracle to postgres migration required super function variable reference
into nested function without nested function parameter

Oracle sample:
---
create or replace function f1(n number) return number
is
vs number:=1;
function nf1(m number) return number is
begin
return vs + m + n;
end;
begin
return nf1(2);
end;
/

run:

SQL> select f1(9) from dual;

 F1(9)
--
12



Thanks
Sridhar BN

Re: [HACKERS] [JDBC] JDBC behaviour

[Sridhar N Bamandlapally]

Hi All

I understand your point,

may be I didn't understand everyone or everyone didn't understand me

one feature of PostgreSQL is implemented into another feature of Java ( i
say subject PostgreSQL::autocommit Vs JDBC::setAutoCommit ),
i.e PostgreSQL::"set autocommit to FALSE" is implemented as
JDBC::"BEGIN--END"

currently PostgreSQL::"set autocommit to FALSE ( not supported )

say in future, if PostgreSQL come with proper fix/support for "set
autocommit to FALSE" then will JDBC-team change the to code to JDBC::"set
autocommit to FALSE" ?, then what about existing behaviors dependency
applications ?

this could have handled in different way in blogs saying to add "BEGIN-END"
from JDBC-connection-query with warning

simple, if PostgreSQL DB is not support then same with PostgreSQL JDBC too,
if still JDBC want to support then need to support with expected behavior
way only, how come other feature is added to this ?

basically, decision/review seems to be wrong, may be bug in the decision

and why for this we are continuing/forcing the loop is, because

1. "every/entire application developers expected behavior are matching,
only PostgreSQL::JDBC-team is not in sync"
2. "every organisation want there applications to be multi-database
compatible, only PostgreSQL::JDBC-team <don't know what to say>"

however, looping hackers and ending the loop

sorry, for using hard words(if any), but as open-source we need to complete
transparent


Thanks
Sridhar





On Thu, Feb 18, 2016 at 11:03 PM, Kevin Wooten <kd...@me.com> wrote:

> Using ‘psql’ executing your example would yield the same result, a command
> error would cause a required rollback before proceeding.  This tells you
> that this is how PostgreSQL, the database, is designed to work. It has
> nothing to do with the Java driver implementation.
>
> You are asking the creators of a client driver implementation to change a
> fundamental behavior of the database.  Repeatedly people have suggested you
> take this up with those creating the actual database (that’s the request to
> move this to the ‘-hackers’ list); yet you persist.
>
> I’m only chiming in because it’s getting quite annoying to have you keep
> this thread alive when the situation has been made quite clear to you.
>
> On Feb 18, 2016, at 9:57 AM, Sridhar N Bamandlapally <
> sridhar@gmail.com> wrote:
>
> There are many reasons why this is required,
>
> 1. Postgres migrated client percentage is high,
>
> 2. For application developers this looks like bug in Postgres, as it throw
> exception for next transaction even when current exception
> suppressed/handled,
>
> 3. Most of non-financial application or data-ware-house application have
> batch transaction process where successful transaction goes into
> data-tables and failed transactions goes into error-log-tables,
>
> this is most generic requirement
>
> cannot effort any reason if client think about rollback to old database or
> feel not meeting requirements  -- please ignore
>
>
>
> On Thu, Feb 18, 2016 at 7:06 PM, Mark Rotteveel <m...@lawinegevaar.nl>
> wrote:
>
>> On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
>> <andr...@visena.com> wrote:
>> >  I understand that and indeed this isn't something that should be
>> handled
>> >  by the driver, however some of the response in this thread seem to
>> think
>> >  it
>> >  is an absurd expectation from the OP that failure of one statement
>> should
>> >  still allow a commit. Which it isn't if you look at what other database
>> >  systems do.
>> >
>> >  Mark
>> >
>> > If that one failed statement doesn't raise an exception, how does the
>> > client
>> > (code) know that it failed? If it does raise an exception, then what
>> > standard
>> > specifies that that specific exceptions is to be treated as "don't
>> > rollback for
>> > this type of error"?
>>
>> Of course an exception is raised, but the exact handling could then be
>> left to the client. For example the client could catch the exception,
>> decide based on the specific error to execute another statement to "fix"
>> the error condition and then commit. Think of INSERT, duplicate key, then
>> UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
>> of duplicate key is rare it can be cheaper to do than to first SELECT to
>> check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
>> update count = 0. Another situation could be where the failure is not
>> important (eg it was only a log entry that is considered supportin