Partitioned Table conversion to Non-Partition table in postgresql open source

2020-12-28 Thread Brajendra Pratap Singh
Hi,

Currently we are running on open-postgresql 9.6 version with some
partitioned Table which we need to convert from Inheritance partitioning to
declarative partitioning during migration to 12.3 open-postgresql version.

Is there any safest and faster way to fulfill this requirement, Please
suggest?

Thanks and regards,
Singh


Re: PostgreSQL HA

2020-12-28 Thread Jehan-Guillaume de Rorthais
On Sat, 26 Dec 2020 05:51:22 +0530
venkata786 k  wrote:

> Hi Team,
> 
> Could you please share postgresql's best active(R/W)-active(R/W) (multi
> master replication) solutions.
> My Team is thinking about implementing active-active replacing master-slave.

Symmetric replication bring a lot of complexity in various layers:
application, architecture, backups.

Why do you need symmetric replication? Do you have any issues with asymmetric
replication?




Re: PostgreSQL HA

2020-12-28 Thread Andreas Kretschmer




Am 26.12.20 um 17:20 schrieb venkata786 k:

Hi Ganesh,

BDR supports postgres 12 & 13 versions ??
I think we have BDR compatible for 9.4 postgres.
Could you plz confirm.


That's true. PG 9.4 is out of support.



Regards, Andreas

--
2ndQuadrant, an EDB company
www.2ndQuadrant.com / www.enterprisedb.com





Re: Is there a good discussion of optimizations?

2020-12-28 Thread Michael Lewis
On Wed, Dec 23, 2020 at 6:56 PM Guyren Howe  wrote:

> I’d like to put together a good video and writeup about what the…
> philosophy behind relational databases is.
>
> Most folks, in my experience, who use relational databases don’t really
> understand the basic theory or even more important the why — the philosophy
> — of what a relational database is and how to get the most out of them. I
> see a lot of folks trying to use SQL in an imperative manner — make this
> temp table, then update it some, then make this other temp table, etc… I
> see this particularly among analysts who for some reason often prefer SQL
> Server. I think this is down to afaict SQL Server having an abominable
> query optimizer.
>

I find temp tables quite helpful to get needed and consistent performance
when doing large data warehouse type queries on source data especially when
it isn't fully & properly normalized. Many row estimates being low because
of correlation with specified client_id and sometimes having 15-25 tables
involved in a report, has meant that temp tables (that are analyzed to
ensure statistics are present) have seemed the best tool for the job.
Perhaps that's all a hack though.

I look forward to when extended statistics may help with join planning and
building out a comprehensive warehouse that facilitates use of simpler
queries, but for now the "imperative straight-jacket" seems to help more
often than it hurts.

>


Re: Partitioned Table conversion to Non-Partition table in postgresql open source

2020-12-28 Thread Hellmuth Vargas
Hi

I had already read about some procedures to do that


https://www.enterprisedb.com/blog/postgres-partition-upgrade-your-partitioning-inheritance-declarative
https://www.2ndquadrant.com/en/blog/how-to-migrate-from-inheritance-based-partitioning-to-declarative-partitioning-in-postgresql/


El lun, 28 de dic. de 2020 a la(s) 04:38, Brajendra Pratap Singh (
singh.bpratap...@gmail.com) escribió:

> Hi,
>
> Currently we are running on open-postgresql 9.6 version with some
> partitioned Table which we need to convert from Inheritance partitioning to
> declarative partitioning during migration to 12.3 open-postgresql version.
>
> Is there any safest and faster way to fulfill this requirement, Please
> suggest?
>
> Thanks and regards,
> Singh
>
>
>

-- 
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate


Re: Dynamic procedure execution

2020-12-28 Thread Muthukumar.GK
Hi Adrian Klaver,



Sorry for typo mistake. Instead of writing lengthy query, I had written it
simple. Actually my main concept is to bring result set with multiple rows
(using select query) with help of dynamic query.

When calling that procedure in Pgadmin4 window, simply getting the message
as ‘ CALL Query returned successfully in 158 msec’.



FYI, I have implemented simple dynamic query for UPDATE and DELETE rows. It
is working fine without any issues.



Please let me know is there any way of getting result set using dynamic
query.



*Issue with dynamic select:-*



CREATE OR REPLACE
Procedure sp_select_dynamic_sql(

keyvalue integer)

LANGUAGE 'plpgsql'

AS $BODY$

Declare v_query text;

BEGIN

v_query:= 'select * from Los_BankInfo '

|| ' where pk_id = '

|| quote_literal(keyvalue);



execute v_query;



END;

$BODY$;



*Execuion** of Proc:-*



CALL sp_select_dynamic_sql (11);



*Output:-*



CALL



Query returned successfully in 158 msec.





*Working fine with Dynamic UPDATE and DELETE Statement :-*



*UPDATE:-*



CREATE OR REPLACE Procedure sp_Update_dynamic_sql(

newvalue varchar(10),

keyvalue integer)

LANGUAGE 'plpgsql'

AS $BODY$

Declare v_query text;

BEGIN

v_query:= 'update Los_BankInfo set approverid'

|| ' = '

|| quote_literal(newvalue)

|| ' where pk_id = '

|| quote_literal(keyvalue);



execute v_query;



END;

$BODY$;

--CALL sp_Update_dynamic_sql (john,10);





*DELETE:-*



CREATE OR REPLACE
Procedure sp_Delete_dynamic_sql(

keyvalue integer)

LANGUAGE 'plpgsql'

AS $BODY$

Declare v_query text;

BEGIN

v_query:= 'delete from Los_BankInfo '

|| ' where pk_id = '

|| quote_literal(keyvalue);



execute v_query;



END;

$BODY$;



--CALL sp_Delete_dynamic_sql(10);



Regards

Muthu

On Mon, Dec 14, 2020, 8:54 PM Adrian Klaver 
wrote:

> On 12/13/20 9:59 PM, Muthukumar.GK wrote:
> > Hi team,
> >
> > When I am trying to implement belwo dynamic concept in postgreSql,
> > getting some error. Kindly find the below attached program and error.
> > Please advise me what is wrong here..
> >
> > CREATE OR REPLACE PROCEDURE DynamicProc()
> >
> > AS $$
> >
> > DECLARE v_query TEXT;
> >
> > C1 refcursor := 'result1';
> >
> > begin
> >
> > v_query := '';
> >
> > v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';
> >
> > EXECUTE (v_query);
> >
> > END;
> >
> > $$
> >
> > Language plpgsql;
> >
> >   Calling procedure :-
> >
> > 
> >
> > CALL DynamicProc();
> >
> > FETCH ALL IN "result1";
> >
> >
> > Error :-
> >
> > --
> >
> > ERROR: syntax error at or near "OPEN"
> >
> > LINE 1: OPEN C1 FOR SELECT * FROM public."Los_BankInfo" ^
> >
> > QUERY: OPEN C1 FOR SELECT * FROM public."Los_BankInfo"
> >
> > CONTEXT: PL/pgSQL function dynamicproc() line 9 at EXECUTE SQL state:
> 42601
>
> Two things:
>
> 1) The error is from a different version of the procedure then the code.
> The table name is different. Can't be sure that this is the only change.
> So can you synchronize your code with the error.
>
> 2) Take a look here:
>
> https://www.postgresql.org/docs/12/plpgsql-cursors.html
>
> 42.7.2. Opening Cursors
>
> For why OPEN is plpgsql specific and how to use it.
>
> >
> >
> > Regards
> >
> > Muthukumar.gk
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>