Re: replace single char for string using regexp_replace

[Daniel Verite]

PegoraroF10 wrote:

> I have a
> Replace(Replace(Replace(Replace($$Text,with;On'It"$$,',','chr(59)'),';','chr(44)'),'"','chr(34)'),,'chr(39)')
> It works but I would like to call just one Replace.

For performance, it might be the fastest method, despite the
lack of elegance and the multiple passes on the string.
In a recent discussion on the list [1] I've asked on how best to do
multiple string replacements. That's a generalized version of your
question and the answer might be heavier but anyway here's some
code on the wiki with plperl and plpgsql versions:

https://wiki.postgresql.org/wiki/Multi_Replace_Perl
https://wiki.postgresql.org/wiki/Multi_Replace_plpgsql

If you're okay with plperl see the first version, as it's
much more efficient, with Perl implementing the multiple
replacement natively in its regexp engine (plus implementing
the search with a trie since 5.10).

The plpgsql version tries do its best with regexp_replace, but it
scales much worse as the number of replacements grows.
But it does work in the situations where a stack of nested replace() calls
wouldn't work, like replace foo with bar and bar with foo.

In your case, as the strings to replace always consist only of one character,
you might also split the string by characters, replace them with a
CASE WHEN... construct, and reassemble the result with string_agg,
as in:

select string_agg(c, '') from 
(select case c
   when ',' then 'chr(59)'
   when ';' then 'chr(44)'
   ... other substitutions...
   else c
   end
 from
   regexp_split_to_table($$The Original String$$, '') as s1(c)
) as s2(c);

If the strings are not too large and there are many occurrences of the
characters
to replace, I would expect  this to be more efficient than the more generic
plpgsql-based solution above. Against the Perl version I don't know.
There is a per-call overhead with plperl that can't be ignored if you're
focused on performance.


[1]
https://www.postgresql.org/message-id/306b726b-f185-4668-bffe-ac8e7f788...@manitou-mail.org

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Need auto fail over cluster solution for PostGres

[Girish Kumar]

Hello,

I need to setup an auto fail over cluster for Postgres similar to Always 
Available Group(AAG) provided by Microsoft for SQL Server. Any suggestions? 
Prefer open source solutions(On Ubuntu)

Regards,
Girish kumar

Sent from Mail for Windows 10

Re: Need auto fail over cluster solution for PostGres

[Fabrízio de Royes Mello]

Em seg., 30 de dez. de 2019 às 13:30, Girish Kumar 
escreveu:
>
> Hello,
>
> I need to setup an auto fail over cluster for Postgres similar to Always
Available Group(AAG) provided by Microsoft for SQL Server. Any suggestions?
Prefer open source solutions(On Ubuntu)
>

Some FOSS options:

https://github.com/zalando/patroni
https://github.com/sorintlab/stolon
https://github.com/ClusterLabs/PAF

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

A JOIN question

[stan]

I am working on a system whee one group of folks inputs portions of data
into a table, and a 2nd completes the data for each row.


The engineers enter in the items they need into a BOM table, and purchasing
agents get quotes and create PO's. There is not a fixed relationship between
the parts (part number and manufacturers) and the vendor (mfg agent or
distributor),  A project manager determines which vendor to buy various
manufacturers items from. This sis controlled by the following table:



CREATE TABLE mfg_vendor_relationship (
mfg_vendor_relationship_key_serial integer DEFAULT 
nextval('ica.mfg_vendor_relationship_key_serial')
PRIMARY KEY ,
mfg_key   integer NOT NULL,
vendor_keyinteger NOT NULL,
project_key   integer NOT NULL,
modtime   timestamptz NOT NULL DEFAULT current_timestamp ,
FOREIGN KEY (mfg_key) references mfg(mfg_key) ON DELETE RESTRICT ,
FOREIGN KEY (vendor_key) references vendor(vendor_key) ON DELETE RESTRICT ,
FOREIGN KEY (project_key) references project(project_key) ON DELETE 
RESTRICT ,
CONSTRAINT mfg_vendor_constraint 
UNIQUE (
mfg_key , 
vendor_key , 
project_key
)
);
So, I have the following view for the purchasing agents.


CREATE view t1 as 
select 
project.proj_no ,
qty ,
costing_unit.unit,
mfg_part.mfg_part_no ,
mfg.name as m_name ,
mfg_part.descrip as description ,
vendor.name as v_name ,
format_phone_no(
vendor.area_code,
vendor.phone_exchange,
vendor.phone_number,
vendor.phone_extension) office_phone ,
cast(cost_per_unit as money) ,
cast(qty * cost_per_unit as   money) line_ttl ,
need_date ,
order_date ,
received_date ,
po_no ,
po_line_item , 
po_revision ,
po_rev_date ,
po_terms_and_conditions.net_interval ,
po_terms_and_conditions.discount_interval ,
po_terms_and_conditions.discount
from 
bom_item 
right join project on 
project.project_key = bom_item.project_key
inner join mfg_part on 
mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join costing_unit on 
costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on 
mfg.mfg_key = mfg_part.mfg_key 
inner join mfg_vendor_relationship on
mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
AND
mfg_vendor_relationship.project_key = bom_item.project_key
inner join vendor on
mfg_vendor_relationship.vendor_key = vendor.vendor_key
FULL OUTER JOIN po_terms_and_conditions ON
po_terms_and_conditions.po_terms_and_conditions_key = 
bom_item.po_terms_and_conditions_key
;

Now, my problem is that this join:

inner join mfg_vendor_relationship on
mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
AND
mfg_vendor_relationship.project_key = bom_item.project_key

Means that the item to be purchased is invisible to the purchasing agent if
the project manager has not populated the cross reference table.

How can I modify this select so that all items for a given project will
appear in the purchasing agents view?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: A JOIN question

[Michael Lewis]

>
> LEFT join mfg_vendor_relationship on
> mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
> AND
> mfg_vendor_relationship.project_key = bom_item.project_key
> LEFT join vendor on
> mfg_vendor_relationship.vendor_key = vendor.vendor_key
>

Perhaps I am missing something, but it seems like just a matter of changing
inner join to left so you keep what you already have and augment vendor
information when it exists. I never use right joins (I re-write to always
declare as left) so I am not sure if the right join near the top screws
that up. I might consider using a UNION ALL to combine sets where values
are NULL with left joins, and use plain (inner) joins for the not nulls.

Re: A JOIN question

[stan]

On Mon, Dec 30, 2019 at 02:47:53PM -0700, Michael Lewis wrote:
> >
> > LEFT join mfg_vendor_relationship on
> > mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
> > AND
> > mfg_vendor_relationship.project_key = bom_item.project_key
> > LEFT join vendor on
> > mfg_vendor_relationship.vendor_key = vendor.vendor_key
> >
> 
> Perhaps I am missing something, but it seems like just a matter of changing
> inner join to left so you keep what you already have and augment vendor
> information when it exists. I never use right joins (I re-write to always
> declare as left) so I am not sure if the right join near the top screws
> that up. I might consider using a UNION ALL to combine sets where values
> are NULL with left joins, and use plain (inner) joins for the not nulls.

Thanks. That looks like it gives me what I need.

RE right joins, I just got started doing that, and it is easier for me to
think that way.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

How to reset a server error '25P02 in_failed_sql_transaction'

[Matthias Apitz]

Hello,

Due to a wrong human input in the GUI of our application our
application server, from the point of view of the PostgreSQL server it
is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
server, here from our own debug logging the command and the error
message of the server:


posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum WHERE 
desk>='*2019' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum  FROM 
:select_anw;
 sqlca 
sqlcode: -400
sqlerrm.sqlerrml: 61
sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
...

All subsequent correct (SELECT ...) statements get rejected with, for example: 


...
posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum WHERE 
desk>='31.12.1900' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum  FROM 
:select_anw;
 sqlca 
sqlcode: -400
sqlerrm.sqlerrml: 105
sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until end of 
transaction block on line 918
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 25P02
posSqlError===
...

Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN 
TRANSACTION;'

What is the correct way to abort the "transaction" as requested by the PG
server to return to normal operations?

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub