Question about AWS Calculator

[Ravi Krishna]

I am using http://calculator.s3.amazonaws.com/index.html to calculate the
cost of RDS vs EC2.  Assuming that I am going for only a 2 node setup
(master and slave), few questions:

1.  In EC2 how do I indicate that I need two nodes so that I can set up
streaming replication between them for HA.  Do I mention 2 as number of DB
Instance or just add two rows.

2. I understand that in EC2 I can also pick the two nodes in different
availability zones and pay for replication data transfer between those
zones.  Am I right ?

3. In RDS it seems HA is built in without the need to mention nodes, be is
same zone or MZ.  So how does user pick number of read replicas.

thanks.

FDW with DB2

[Ravi Krishna]

Has anyone used PG with DB2(Linux) ?

Re: FDW with DB2

[Ravi Krishna]

On Fri, Apr 6, 2018 at 4:09 PM, Joshua D. Drake 
wrote:

> On 04/06/2018 01:01 PM, Ravi Krishna wrote:
>
> Has anyone used PG with DB2(Linux) ?
>
>
> Looks like the way you go about it is with the ODBC FDW.
>

​https://wiki.postgresql.org/wiki/Foreign_data_wrappers

There are two ODBC drivers. One does not compile and the other one piggy
backs
on a community effort for Cartodb. Looks like Db2 is used by very few and
even less want a divorce from DB2 -> no motivation to write native FDW like
that for Oracle :-)
​

Re: FDW with DB2

[Ravi Krishna]

>
>
>
> the CartoDB ODBC driver works quite well.  I've used it to move a large
> amount of data from DB2 and Netezza databases.
>

​Hello Steven

Will it be OK if I or my team reach out to you for any guidance/help.
​

Re: FDW with DB2

[Ravi Krishna]

Yes of course I respect your time. regards.

Please do not contact me personally, as I'm too busy to provide technical
> support on a private basis.  Thanks for your understanding!
>

Re: Rationale for aversion to the central database?

[Ravi Krishna]

1. With a micro service based architecture these days, it is difficult to
justify putting all logic in a central database as you can only scale up in
a database.  Business logic in things like Spark can make a claim for scale
out solution.
2. All RDBMS have a non portable stored proc language, making migration a
pain.

Re: Rationale for aversion to the central database?

[Ravi Krishna]

> I am however very comfortable with using psql and PL/pgSQL and I am 
very opinionated.


Nothing wrong with this approach and it may very well work 90% of the 
time.   Until ... a day comes when

you need to migrate out of PG to another RDBMS.  Good luck at that time.

Two things bit baffling in RDS PG

[Ravi Krishna]

I am playing around with RDS PG and I am not able to understand the following:

1. The database name I created via RDS console is in upper case with no quotes. 
From the remote machine via psql, 
 if I try to use lower case db name with the -d option it errors out 
"database not found".  Works only with upper case.  
This behavior does not exist in non RDS env.

2. Two RDS parameter group

rds.force_ssl 
ssl
have been set to 1 (true)

yet from a remote machine I can connect to the database via psql without 
specifying any option for ssl.  How is it doing ?

psql (10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)


thanks

Re: Semantics around INSERT INTO with SELECT and ORDER BY.

[Ravi Krishna]

Why is it even important?  Once you use ORDER BY clause, you are guaranteed
to get the rows in the order.  Why do you need how it was inserted in the
first place.

Re: Semantics around INSERT INTO with SELECT and ORDER BY.

[Ravi Krishna]

>Or to put it another way, I want to select values from one table ordered
by
>complex criteria and insert them into another table. I want to be able to
>retrieve the rows from the target table in the same order they were
inserted,
>but I don't care about the specific ordering criteria. I only care about
the order they were inserted.

As I understand, your business requirement is to retrieve the rows from the
target
table the same way they were inserted.  The one and only way to achieve it
is
to use the same ORDER by clause to SELECT from target, what it was used
to insert into target. In your case, the insert is
 INSERT INTO bar (val) SELECT val FROM foo ORDER BY id DESC;
So the SELECT should also be ORDER BY ID desc

Just don't care on how it inserts and stores row internally.

PG on AWS RDS and IAM authentication

[Ravi Krishna]

As per https://forums.aws.amazon.com/thread.jspa?threadID=258822&tstart=0 there 
was no IAM authentication for PG on AWS RDS. (It is there for MySQL).
However the link is a year old. Has it changed since then?  Can we use IAM 
authentication for PG.

Thanks

Re: PostgreSQL Volume Question

[Ravi Krishna]

> 
> Hi, I'm new to the community. 
> 
> Recently, I've been involved in a project that develops a social network data 
> analysis service (and my client's DBMS is based on PostgreSQL).
> I need to gather huge volume of unstructured raw data for this project, and 
> the problem is that with PostgreSQL, it would be so dfficult to handle this 
> kind of data. Are there any PG extension modules or methods that are 
> recommended for my project? 

Can you give a number to "huge volume" and how did you conclude that PG can not 
handle it.

Load data from a csv file without using COPY

[Ravi Krishna]

In order to test a real life scenario (and use it for benchmarking) I want to 
load large number of data from csv files.  
The requirement is that the load should happen like an application writing to 
the database ( that is, no COPY command). 
Is there a tool which can do the job.  Basically parse the csv file and insert 
it to the database row by row.

thanks

Re: Load data from a csv file without using COPY

[Ravi Krishna]

> 
> If performance is relevant then your app should probably be using COPY 
> protocol, not line by line inserts. It's
> supported by most postgresql access libraries. If your app does that then 
> using "\copy" from psql would be
> an appropriate benchmark.

Actually the reluctance to not use COPY is to make the benchmark same across 
two different RDBMS in
two diff env. 

Re: Load data from a csv file without using COPY

[Ravi Krishna]

> 
> I think an easy approach would be to COPY the CSV files into a separate 
> database using psql's \copy command and then pg_dump that as separate insert 
> statements with pg_dump —inserts.
> 

This was my first thought too.  However, as I understand, pg_dump --insert 
basically runs INSERT INTO ... sql for every row.  
In other words, each row is un-prepared and executed individually.  That is 
also not real life scenario.

Re: Load data from a csv file without using COPY

[Ravi Krishna]

Thanks all for replying.  I see that I did not explain my requirement in 
detail.  So let me

explain it in detail.

1. Currently we have a legacy app running in DB2/LUW. Application writes 
to it either via Java program

or uses a custom ETL scripts using a vendor product.
2. We want to migrate it to DB2 and eliminate vendor ETL tool.
3. We now have a catch-22 situation.  Should we spend time porting the 
app to PG without first verifying
that PG can perform as well as DB2. In other words, if some sort of 
testing rules out PG as a good
replacement for DB2, why even bother to port.  Of course that does 
not prove conclusively that if PG
passes the test, then it would mean that the app will work just as 
fine.  But at least basic test will tell

   that we are not on a wrong path.
4. What I am planning is:
4.a Get a set of large tables exported as a pipe delimited text 
file.

4.b Load them in both DB2 and PG on a similar h/w
4.c  Run OLAP queries.

4.b is to test i/o. Our app is sensitive to the load times and some of 
the tables are really wide.
4.c is to test maturity of PG in handling complex OLAP SQLs. From what I 
have read, while PG
 optimizer is very good in handling OLTP, it is not, as yet, as good 
in OLAP queries.


I just want to keep the testing tool same in 4.b for both db2 and pg. If 
COPY is the only way,

we will use it with something comparable on the DB2 side.

Re: using pg_basebackup for point in time recovery

[Ravi Krishna]

> 
> 
> >You should avoid top-posting on the Postgres lists, this is not the
> >usual style used by people around :)
> 
> Will do, but Yahoo Mail! does not seem to like that, so I am typing the > 
> myself
> 

Same here even though I use Mac mail. But it is not yahoo alone. 
Most of the web email clients have resorted to top posting.  I miss the old 
days of Outlook Express which was so '>' friendly.  I think Gmail allows
'>' when you click on the dots to expand the mail you are replying to, but it 
messes
up in justifying and formatting it.

The best for '>':  Unix elm :-)

Re: Convert Existing Table to a Partition Table in PG10

[Ravi Krishna]

Does this help:  
http://ashutoshpg.blogspot.com/2018/06/upgrade-your-partitioning-from.html

Disabling/Enabling index before bulk loading

[Ravi Krishna]

We recently did a test on COPY and found that on large tables (47 million rows 
, 20GB of raw data) the 
difference in COPY with 16 indexes and COPY without any index is 1:14. That is, 
COPY is 14 times slower 
when data is ingested with all indexes as opposed to COPY first without index 
and then create all index.

I googled for earlier posting on this and it looks like this has been asked 
before too.  

This is what I am thinking to do:

1 - Extract index definition and save it as a SQL somewhere, either a file or a 
table.
2 - Drop all indexes.
3 - Ingest data via COPY
4 - Recreate all indexes saved in (1).

Is there a generic sql or script or tool to accomplish (1).

thanks

Re: Disabling/Enabling index before bulk loading

[Ravi Krishna]

> 
> Did you include the time to CREATE INDEX after the COPY or is the 1:14 only 
> for the COPY stage?

Yes.

Time taken to load 47 mil rows with all 16 indexes intact:  14+ hrs
Time taken to load the same after dropping index and then loading and finally 
creating 16 indexes: 1 hr 40 min

Frankly I am surprised by this staggering difference.

Re: Disabling/Enabling index before bulk loading

[Ravi Krishna]

> 
> 
> https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html
>  
> 
>  


This does not work in RDS.  In order to update system catalog tables 
(pg_index), one needs privileges which is
denied in RDS. In RDS terminology, the user must belong to role rdsadmin and 
that role is reserved only for AWS 
internal users.

Re: Disabling/Enabling index before bulk loading

[Ravi Krishna]

> 
> I am very suspicious of why you need 16 indexes. Are you sure all those 
> indexes are actually being utilized?
> Try executing the attached query, You may find find some are really not 
> needed.

This is a DATAMART application and the indexes are to satisfy a large number of 
queries possible from tableau. Keep in mind
this table has 200+ cols. 
That said, it is possible that a few of them may be unnecessary.  I will run 
your script and check that out.

thanks for the script.

Re: Extremely slow autovacuum:vacuum to prevent wraparound

[Ravi Krishna]

>  Setting it that high and disabling autovacuum isn’t just silly - it borders 
> on sabotage!


LOL.  My thoughts too.  Perhaps some disgruntled employee's parting shot before 
quitting :-)

Re: Using CTE vs temporary tables

[Ravi Krishna]

​Does temp tables also suffer from optimization fence we see in CTE.​

>

Re: Open Source tool to deploy/promote PostgreSQL DDL

[Ravi Krishna]

> 
> Where I work, the requirement to have rollback scripts is part of the ITIL 
> requirement for Changes to have a backout procedure.
> 

Liquibase provides that ability, but IMO rollback for RDBMS is always bit 
tricky.  Certain DDL operations can take long time if it involves
a table rewrite.  PG is actually better than others.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Ravi Krishna]

What would this new IDE offer which a product like dbeaver does not have.


--Sent from phone.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Ravi Krishna]

1. dbeaver covers many DBMS and even nosql.  Many shops are not one product
specific.  That is definitely a plus.
2. Lightweight tool which can run even from command line will be a plus for
the product you are thinking to develop.
3. Who cares in what language it is developed?

We are a dbeaver shop and we love it.


On Sun, Jul 15, 2018 at 4:22 PM, Dmitry Igrishin  wrote:

>
>
> вс, 15 июл. 2018 г. в 23:05, Ravi Krishna :
>
>> What would this new IDE offer which a product like dbeaver does not have.
>>
> AFAIK, DBeaver:
>   - covers many DBMS (I want to focus on PostgreSQL);
>   - full fledged IDE with feature rich editor (I want a lightweight tool
> that can be used from
> command line or from GUI to help SQL code refactoring in your favorite
> editor);
>   - written in Java (I develop in C++).
>
> This is what I can currently say about my plans on this project.
>

PG backup check

[Ravi Krishna]

Not sure I am following this.  Did Google release this because PG backups
are not 100% reliable or the data corruption can occur due to hardware
failure.

http://www.eweek.com/cloud/google-releases-open-source-tool-that-checks-postgres-backup-integrity?utm_medium=email&utm_campaign=EWK_NL_EP_20180713_STR5L2&dni=450493554&rni=24844166

Re: PANIC: could not open critical system index 2662

[Ravi Krishna]

Just curious, why can't you restore the db from the backup ?

What is the use case for UNLOGGED tables

[Ravi Krishna]

I am trying to understand the use case for UNLOGGED tables in PG. I am 
specifically talking about normal tables which need to be turned into UNLOGGED 
for a specific purpose like bulk loading because generating WAL logs during the 
load makes no sense, even when we take into consideration that this generally 
breaks replication. 
Typically work flow is:

1. Turn off logging on the table.
2. Load a large set of data using some bulk load tool.
3. Turn back logging.

Depending on RDBMS, some make (3) less painful and some make it painful by 
rebuilding all indexes.  DB2 is very good in that as the only
penalty you have is in step (2) when the table is locked exclusively.

With PG step (3) basically rebuilds entire table.  Imagine a 500 million row 
table, which got additional 30 million rows via a batch load. Does PG
rebuild entire 530 million rows ?  Then what exactly is the benefit of UNLOGGED 
tables, unless we have permanent unlogged tables for disposable
data.

thanks.

Re: What is the use case for UNLOGGED tables

[Ravi Krishna]

> 
> I use them for "ELT" oriented processing where the final results get stored 
> on permanently logged tables but I want to manipulate tables while 
> transforming from the original input.

Yeah I see the use case.  Basically raw data -> rolled up data -> to final 
results in normal tables.
However flipping a normal table between LOGGED and UNLOGGED does not seem to be 
a use case.

Re: CTE with JOIN of two tables is much faster than a regular query

[Ravi Krishna]

> What can I do to improve the performance of the regular query without using a 
> CTE? 

Why do you care ?  When I find that I can write a SQL 3 different ways, I will 
go for the most
efficient one.  So why not accept the CTE version of this SQL.  Just curious.

[no subject]

[Ravi Krishna]

Can I split a large file into multiple files and then run copy using each file. 
 The table does not contain any serial or sequence column which may need 
serialization. Let us say I split a large file to 4 files.  Will theperformance 
boost by close to 4x??

Multiple COPY on the same table

[Ravi Krishna]

Can I split a large file into multiple files and then run copy using each file. 
 The table does not contain any serial or sequence column which may need 
serialization. Let us say I split a large file to 4 files.  Will theperformance 
boost by close to 4x??
ps: Pls ignore my previous post which was without a subject (due to mistake)

Re: [External] Multiple COPY on the same table

[Ravi Krishna]

> What is the goal you are trying to achieve here.
> To make pgdump/restore faster?
> To make replication faster?
> To make backup faster ?

None of the above.
 We got csv files from external vendor which are 880GB in total size, in 44 
files.  Some of the large tables had COPY running for several hours. I was just 
thinking of a faster way to load.

Re: [External] Multiple COPY on the same table

[Ravi Krishna]

1. The tables has no indexes at the time of load.2.  The create table and copy 
are in the same transaction.
So I guess that's pretty much it.  I understand the long time it takes as some 
of the tables have 400+ million rows.Also the env is a container and since this 
is currently a POC system , not much time has been invested in fine tuning it.
thanks all.

COPY FROM - to avoid WAL generation

[Ravi Krishna]

In a recent thread of mine I learned something very interesting.  If a table is 
created and data is loaded via COPY FROM within the same transaction, then PG 
will be smart enough to not generate WAL logs because all it needs to do is to 
track the status of the transaction and let the data load go to the new data 
file created for the table.  If committed, the table is released for other 
sessions, if rolledback, vaccum will delete the data file later on.
I tested it as follows for a table with 50 milllion rows.  No indexes.
Case 1  - create the table first.  - in a separate transaction load the 50 
million rows.
Took 3 min 22 seconds
Case 2  - start transaction  - create table  - load 50 million rows  - commit 
transaction
Took: 3 min 16 seconds.
Am I missing anything?

Re: COPY FROM - to avoid WAL generation

[Ravi Krishna]

>Please note this is only the case if wal_level = minimal. If replication
>(or PITR) is supported, that mode can't be used, because the data has to
>go into the WAL.
>Were you using wal_level = minimal?

Aha. No it was not minimal. For a second I thought PG is super smart. Oh well. 
Thanks.
 

Re: Can Pg somehow recognize/honor linux groups to control user access ?

[Ravi Krishna]

AFAIK PG does not support it , as yet. IMO this should be implemented as a 
priority.

Re: Can Pg somehow recognize/honor linux groups to control user access ?

[Ravi Krishna]

> On Aug 22, 2018, at 12:08 , David G. Johnston  
> wrote:
> 
> On Wed, Aug 22, 2018 at 8:58 AM, Ravi Krishna  <mailto:sravikris...@aol.com>> wrote:
> AFAIK PG does not support it , as yet. IMO this should be implemented as a 
> priority.
> 
> It does not support it natively, no.  What it does support is PAM which I'm 
> led to believe (haven't used it myself) can be configured to accommodate this 
> use case as well as many other configurations people may think up.
> 
> https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-PAM 
> <https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-PAM>
> 

I am not sure this is the same what I was thinking about.  For example, in db2, 
connect privilege can be granted to a group and so is grant privilege as in
GRANT SELECT ON TABLE SCHEMA.TABLE TO GROUP ABC;
GRANT CONNECT ON DATABASE TO GROUP ABC 

And the group may not necessarily be local group on the node.  it can be AD too.

In fact DBAs don't even need to get involved when a new user needs DB access.  
Sysadmin had to just add that user in a group and we are done.

Re: pg_sample

[Ravi Krishna]

> 
> sir have taken pg_sample 
> Now i want to run pg_sample with credential but i'm getting this error
> 
> Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 
> /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl 
> /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at 
> ./pg_sample line 192.
> BEGIN failed--compilation aborted at ./pg_sample line 192.

As is clear from the message, you need to install Perl DBI/DBD first.

WARNING: could not flush dirty data: Function not implemented

[Ravi Krishna]

Ubuntu 18.04 as Windows bash

Distributor ID: Ubuntu
Description:Ubuntu 18.04.1 LTS
Release:18.04
Codename:   bionic


PG 10.5.1

postgres@ravi-lenovo:~$ psql -d postgres
psql (10.5 (Ubuntu 10.5-1.pgdg16.04+1))

A CREATE DATABASE statement spewed out 

WARNING:  could not flush dirty data: Function not implemented

many times, but the db was created.

What exactly is this?

A simple search tells that this is an old problem and my Windows has necessary 
WSL and other subsystem for this error to not show up.

Re: WARNING: could not flush dirty data: Function not implemented

[Ravi Krishna]

> 
> If this is on Ubuntu, I don't understand why you're talking
> about Windows.

Because I am using Ubuntu Bash on Windows, which requires WLS (Windows Linux 
Subsystem).  I also have necessary build version of Windows which supports 
Ubuntu Bash.

Re: WARNING: could not flush dirty data: Function not implemented

[Ravi Krishna]

>That means that the linux emulation by microsoft isn't good enough.  You
>can work around it by setting checkpoint_flush_after=0 and
>wal_writer_flush_after=0.


bgwriter_flush_after = 0# measured in pages, 0 disables
backend_flush_after = 0# measured in pages, 0 disables
wal_writer_flush_after = 0  # measured in pages, 0 disables
checkpoint_flush_after = 0  # measured in pages, 0 disables

I set it as shown above and yet while create the database I get the same 
warning.

Re: WARNING: could not flush dirty data: Function not implemented

[Ravi Krishna]

> 
> Whee ... so you get to cope with all the bugs/idiosyncrasies of three
> operating system layers, not just one.  I concur that running Postgres
> in the underlying Windows O/S is probably a much better idea.

Me too, but this is purely for learning and I am much more use to Linux stack 
then ... gasp Windows :-)

Anyhow so far I am seeing this warning only for create database command.  So I 
can ignore.

Re: *Regarding brin_index on required column of the table

[Ravi Krishna]

> i can see a lot of max(string-field) (for instance, LastName, 
> MiddleName, FirstName).
> wild guess: completely broken design, but i don't know your application 
> and use-case for that.
> again, as i said already, i think this is a case for an in-deep 
> consultation.

My thoughts exactly. There is a UNION also.
A mere look at the SQL indicates that it will be a miracle if this runs fast. 
Tuning such queries
in a mailing list is difficult, the best we can do is to give suggestion.

New behavior with JDBC 42.2.5

[Ravi Krishna]

We recently upgraded our JDBC driver to 42.2.5 after seeing this
https://www.postgresql.org/about/news/1883/

All of our PG databases mandates SSL connections.  So the first line in 
pg_hba.conf is

hostnossl all all all reject

We use dbeaver and while setting up connection we check box SSL (require).  
Attempt to connect without that checked would result in the following error
"pg_hba.conf rejected the connection: SSL off"

With the JDBC version listed in the subject, we are finding that we can connect
to the database even without checking SSL.  Is that expected with this version 
of 
JDBC. Does it automatically make the connection an SSL one?  Otherwise it is 
hard
to see PG allowing that connection to pass.

thanks

Re: New behavior with JDBC 42.2.5

[Ravi Krishna]

Just to clarify:

With JDBC 42.1.4 attempt to connect from dbeaver without SSL results in the 
following error:

"pg_hba.conf rejected the connection: SSL off"

This is what we expect.

With 42.2.5, the connection succeeds without SSL.

Re: New behavior with JDBC 42.2.5

[Ravi Krishna]

> First are doing a socket connection or a host connection? Socket 
> connections ignore sslmode.

The URL template of JDBC used by dbeaver is 
jdbc:postgresql://{host}[:{port}]/[{database}]

>From the manual

"The host component is interpreted as described for the parameter host. In 
particular, a Unix-domain socket connection is chosen if the host part is 
either empty or starts with a slash, otherwise a TCP/IP connection is 
initiated."

In our case the host name does not start with a slash.  So I conclude it is 
using TCP/IP.


> psql postgresql://localhost:5432/test?sslmode=require
> 
> 
> psql postgresql://localhost:5432/test?sslmode=disable

We are using dbeaver, not psql. dbeaver also provides disable option for 
sslmode.  I set sslmode=disable and it immediately rejected connection with 
error "SSL Mode off".  

So the puzzle is -> in version 42.1.4 when we do not check "Use SSL " button, 
connection
fails with "SSL Mode off". In that version we check the box "Use SSL" and then 
in the dropdown
for sslmode, we select sslmode=require. Only then the connection passes.

In version 42.2.5 even if we do not check "Use SSL" it still behaves the same 
as 
"Use SSL" checked and sslmode=require. 

I was not able to import the security bulletin fully, but am I right in 
concluding
that JDBC 42.2.5 always turns on sslmode=require unless we explicitly set 
sslmode=disable.

Thanks for your help.

PG security alerts

[Ravi Krishna]

Hi

 

Is there a place to get all PG related security alerts?  I saw this in IBM site:

 

https://www-01.ibm.com/support/docview.wss?uid=ibm10730491

 

which points to this:

 

http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-10915

 

>From the looks of it, it seems to be a generic libpq security issue affecting 
>all platform, not necessarily IBM PowerPC alone.

 

thanks

Re: Why my query not using index to sort?

[Ravi Krishna]

> Is there anyway, I can improve the sorting so that it can use the index ?

Are you telling that why PG is not simply reading the data from the index 
(which is already in sorted
order)?

COPY from a remote machine in Datastage

[Ravi Krishna]

We are doing a POC of using Datastage with PG using ODBC.  

Problem to solve:  How to load a large CSV file using COPY command.  The file 
is on the client machine.

A typical SQL syntax of a copy coming from a remote machine  COPY TABLE FROM 
STDIN WITH CSV HEADER

Question is, how to make the contents of the file available as STDIN in a SQL.  
It is easy in a shell.

Re: COPY from a remote machine in Datastage

[Ravi Krishna]

> 
> Hello, if you need to use COPY command from remote machine and you use some 
> libpq bindings (aka ruby pg gem for example), you can use functions 
> associated with COPY command 
> (https://www.postgresql.org/docs/10/static/libpq-copy.html 
> ). They should be 
> provided by bindings of postgres library you're using.
> 
> If you can share more info, at least how do you access postgres (via Ruby gem 
> for example or from client's console), I can try to be more descriptive.

We not writing any Ruby/Python code.  We are using Datastage. Datastage has in 
built features for most of the stuff. For example to bulk load data from csv 
files
into Oracle/DB2 etc, it has a BULK loader feature. However DS has no support 
for PG directly and we are piggy backing on ODBC where there is no BULK loader.

The only recourse for us is to type in SQL as DS allows user code.

Re: COPY from a remote machine in Datastage

[Ravi Krishna]

> 
> Can you install the postgres client software (psql) on the client machine and 
> then have Datastage spawn "psql -c 'COPY ...'"?

That is already an option for us :-)

Re: COPY threads

[Ravi Krishna]

> 
> You obviously can just copy the data into postgres over multiple
> connections if you need to speed COPY up. But that requires splitting up
> the data on the clientside.
> 

You obviously are referring to multiple connections running COPY on different 
tables, right?  Like what pg_restore does with -j option.
Doesn't copy take an exclusive lock on the table which makes it incompatible 
with parallelization.

Re: COPY threads

[Ravi Krishna]

> 
> No, why would that seem to be the case?  If it did so, then you could
> not run pg_dump to dump data while regular activity was going on.


Not sure. In fact I am now confused.
I am talking about pg_restore which is COPY FROM, which takes exclusive lock on 
the table
while the data is loaded into the table.
pg_dump does COPY TO which reads data out of the table to a file or something 
else.

Re: COPY threads

[Ravi Krishna]

> 
> pg_restore doesn't take locks on the table for the COPY, it does so
> because creating the table takes an exclusive lock.


Interesting.  I seem to recollect reading here that I can't have concurrent 
COPY on the same table because of the lock.
To give an example:

If I have a large file with say 400 million rows, can I first split it into 10 
files of 40 million rows each and then fire up 10 different
COPY sessions , each reading from a split file, but copying into the same 
table.  I thought not.  It will be great if we can do this.

Re: COPY threads

[Ravi Krishna]

Thank you.  Let me test it and see the benefit. We have a use case for this.


> On Oct 10, 2018, at 17:18 , Andres Freund  wrote:
> 
> 
> 
> On October 10, 2018 2:15:19 PM PDT, Ravi Krishna  wrote:
>>> 
>>> pg_restore doesn't take locks on the table for the COPY, it does so
>>> because creating the table takes an exclusive lock.
>> 
>> 
>> Interesting.  I seem to recollect reading here that I can't have
>> concurrent COPY on the same table because of the lock.
>> To give an example:
>> 
>> If I have a large file with say 400 million rows, can I first split it
>> into 10 files of 40 million rows each and then fire up 10 different
>> COPY sessions , each reading from a split file, but copying into the
>> same table.  I thought not.  It will be great if we can do this.
> 
> Yes, you can.
> 
> Andres
> -- 
> Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: COPY threads

[Ravi Krishna]

>>> 
>> Thank you.  Let me test it and see the benefit. We have a use case for this.
> 

Well the result is not what I expected.

this is the sql I used

copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xaa' with 
delimiter '|' NULL as '' CSV HEADER;

From another session

copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xab' with 
delimiter '|' NULL as '' CSV HEADER;

Each had 16M rows.

I see that one copy is blocking other.

The table has no indexes while loading.

Aren't they suppose to run  concurrently without locking ?

Re: COPY threads

[Ravi Krishna]

> 
> Well the result is not what I expected.
> 
> this is the sql I used
> 
> copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xaa' 
> with delimiter '|' NULL as '' CSV HEADER;
> 
> From another session
> 
> copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xab' 
> with delimiter '|' NULL as '' CSV HEADER;
> 
> Each had 16M rows.
> 
> I see that one copy is blocking other.
> 
> The table has no indexes while loading.
> 
> Aren't they suppose to run  concurrently without locking ?
> 
> 

Strike that.  The lock wait is bogus.  The data did get loaded concurrently.

Re: Slot issues

[Ravi Krishna]

When I read all such posts related to replication I realize how backward is 
PG's replication architecture
specially when compared to DB2.  

This is how it is done in Db2 to set up replication.

1. take a full backup on the primary.
2. restore the backup on the other machine (aka standby)
3. start the instance on the standby machine as a standby and point to primary 
as the master
4. that's it.  Db2 will fetch the relevant WAL (active) logs and start applying 
the logs to catch up.
5. Once it has caught up with the primary, it is in PEER mode.

To failover from master to slave

On the standby issue db2 takeover database dbname
that's it. it will flip master and standby and reverse their roles.
[ I am aware that why it is impossible in PG to reverse roles like this ]

Long time back I use to work in SQL Server and the setup of mirroring was as 
simple as DB2.

Negative of db2 replication:  In DB2 replication, lot of restriction on standby 
to be used as a read-only.
One DDL statement or stats collection in the primary will put the standby in 
replay only mode where
it will kick out all sessions on standby until DDL/stats is applied on standby 
also.
Note: My knowledge of db2 replication is bit dated as I have not worked on it 
since 2014.

I love PG, but definitely replication management can be better.

Re: Slot issues

[Ravi Krishna]

The best part in Db2 HADR is that when the standby is catching up with the 
master after a long time, it will
start from the last LSN and fetch it from the primary WAL directory (active 
logs ). If not found, it will look for it
in the archived logs and start applying from there until the current point.  No 
rep slot business.

Re: Slot issues

[Ravi Krishna]

> 
> You're not forced to use slots.  Their purpose is to allow to force the
> primary to keep necessary resources around. Which also allows to get rid
> of the archive in some setups.

Thanks.

Disclaimer:  We don't use replication as we piggy back on AWS HA.

The reason why I posted this is because majority of replication related 
messages in this forum
is about slots :-)

postgres server process crashes when using odbc_fdw

[Ravi Krishna]

Version 10.5
AWS Linux
Foreign server: Db2 on Linux 10.5.9
carto_odbc
unix_odbc

I am able to build odbc_fdw extension, register the extension and also create 
foreign server.
I also created foreign table.

When I run a sql 'select * from odbctest' postgres crashes, generate a core 
file. 

2018-10-16 20:03:42.494 EDT [4639] LOG:  listening on IPv4 address "127.0.0.1", 
port 5432
2018-10-16 20:03:42.498 EDT [4639] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2018-10-16 20:03:42.513 EDT [4640] LOG:  database system was shut down at 
2018-10-16 20:00:05 EDT
2018-10-16 20:03:42.517 EDT [4639] LOG:  database system is ready to accept 
connections
2018-10-16 20:05:11.777 EDT [4639] LOG:  server process (PID 4787) was 
terminated by signal 11: Segmentation fault
2018-10-16 20:05:11.777 EDT [4639] DETAIL:  Failed process was running: select 
* from odbctest ;
2018-10-16 20:05:11.777 EDT [4639] LOG:  terminating any other active server 
processes
2018-10-16 20:05:11.777 EDT [4644] WARNING:  terminating connection because of 
crash of another server process
2018-10-16 20:05:11.777 EDT [4644] DETAIL:  The postmaster has commanded this 
server process to roll back the current transaction and exit, because another 
server process exited abnormally and possibly corrupted shared memory.
2018-10-16 20:05:11.777 EDT [4644] HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
2018-10-16 20:05:11.777 EDT [4816] WARNING:  terminating connection because of 
crash of another server process
2018-10-16 20:05:11.777 EDT [4816] DETAIL:  The postmaster has commanded this 
server process to roll back the current transaction and exit, because another 
server process exited abnormally and possibly corrupted shared memory.
2018-10-16 20:05:11.777 EDT [4816] HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
2018-10-16 20:05:11.778 EDT [4639] LOG:  all server processes terminated; 
reinitializing
2018-10-16 20:05:11.847 EDT [4818] LOG:  database system was interrupted; last 
known up at 2018-10-16 20:03:42 EDT
2018-10-16 20:05:11.860 EDT [4818] LOG:  database system was not properly shut 
down; automatic recovery in progress
2018-10-16 20:05:11.863 EDT [4818] LOG:  redo starts at 5/7F9971A0
2018-10-16 20:05:11.863 EDT [4818] LOG:  invalid record length at 5/7F9971D8: 
wanted 24, got 0
2018-10-16 20:05:11.863 EDT [4818] LOG:  redo done at 5/7F9971A0
2018-10-16 20:05:11.877 EDT [4639] LOG:  database system is ready to accept 
connections

Re: postgres server process crashes when using odbc_fdw

[Ravi Krishna]

I enabled ODBC trace .  Attached trace log.  Had to edit connection details and 
snipped few repetitive lines as I ran the same sql multiple times

Connection Out [DSN=BLUE;UID=*;PWD=***;DATABASE=***;HOST...]
[ODBC][4248][1539734369.611042][SQLGetInfo.c][554]
Entry:
Connection = 0x2150e90
Info Type = SQL_IDENTIFIER_QUOTE_CHAR (29)
Info Value = 0x7ffdaf8700ee
Buffer Length = 2
StrLen = (nil)
[ODBC][4248][1539734369.611055][SQLGetInfo.c][617]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.611067][SQLGetInfo.c][554]
Entry:
Connection = 0x2150e90
Info Type = SQL_CATALOG_NAME_SEPARATOR (41)
Info Value = 0x7ffdaf8700ee
Buffer Length = 2
StrLen = (nil)
[ODBC][4248][1539734369.611075][SQLGetInfo.c][617]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.611092][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x2150e90
[ODBC][4248][1539734369.611122][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x2167610
[ODBC][4248][1539734369.611138][SQLExecDirect.c][240]
Entry:
Statement = 0x2167610
SQL = [select fld1,fld2 from rkrishna.fdwtest][length = 
38 (SQL_NTS)]
[ODBC][4248][1539734369.612221][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.612242][SQLNumResultCols.c][156]
Entry:
Statement = 0x2167610
Column Count = 0x7ffdaf87015e
[ODBC][4248][1539734369.612257][SQLNumResultCols.c][248]
Exit:[SQL_SUCCESS]
Count = 0x7ffdaf87015e -> 2
[ODBC][4248][1539734369.612282][SQLFetch.c][162]
Entry:
Statement = 0x2167610
[ODBC][4248][1539734369.612303][SQLFetch.c][348]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.612314][SQLNumResultCols.c][156]
Entry:
Statement = 0x2167610
Column Count = 0x7ffdaf8701ca
[ODBC][4248][1539734369.612321][SQLNumResultCols.c][248]
Exit:[SQL_SUCCESS]
Count = 0x7ffdaf8701ca -> 2
[ODBC][4248][1539734369.612334][SQLDescribeCol.c][247]
Entry:
Statement = 0x2167610
Column Number = 1
Column Name = 0x2164b78
Buffer Length = 255
Name Length = 0x7ffdaf8701cc
Data Type = 0x7ffdaf8701ce
Column Size = 0x7ffdaf8701d8
Decimal Digits = 0x7ffdaf8701d0
Nullable = 0x7ffdaf8701d2
[ODBC][4248][1539734369.612394][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [FLD1]
Data Type = 0x7ffdaf8701ce -> 4
Column Size = 0x7ffdaf8701d8 -> 10
Decimal Digits = 0x7ffdaf8701d0 -> 0
Nullable = 0x7ffdaf8701d2 -> 1
[ODBC][4248][1539734369.612409][SQLDescribeCol.c][247]
Entry:
Statement = 0x2167610
Column Number = 2
Column Name = 0x2164b78
Buffer Length = 255
Name Length = 0x7ffdaf8701cc
Data Type = 0x7ffdaf8701ce
Column Size = 0x7ffdaf8701d8
Decimal Digits = 0x7ffdaf8701d0
Nullable = 0x7ffdaf8701d2
[ODBC][4248][1539734369.612419][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [FLD2]
Data Type = 0x7ffdaf8701ce -> 1
Column Size = 0x7ffdaf8701d8 -> 1
Decimal Digits = 0x7ffdaf8701d0 -> 0
Nullable = 0x7ffdaf8701d2 -> 1
[ODBC][4787][1539734703.466709][__handles.c][460]
Exit:[SQL_SUCCESS]
Environment = 0x2432cd0
[ODBC][4787][1539734703.466760][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x2432cd0
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][4787][1539734703.466769][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734703.466776][SQLAllocHandle.c][375]
Entry:
   

GIN Index for low cardinality

[Ravi Krishna]

In  
https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/

it is mentioned:

"GIN, the most know non-default index type perhaps, has been actually around 
for ages (full-text search) and in short is perfect for indexing columns where 
there are lot of repeating values – think all kinds of statuses or good old 
Mr/Mrs/Miss. GIN only stores every unique column value only once as for the 
default B-tree you’ll have e.g. 1 millon leaf nodes with the integer “1” in it."


Does it mean that GIN is a very good choice for low cardinality columns.  

Re: postgres server process crashes when using odbc_fdw

[Ravi Krishna]

It turned out that enabling ODBC trace was causing PG to crash.  Once disabled 
it started working, but found another issue.
All object names in DB2 is assumed to be upper case.  odbc_fdw sends queries 
like this


select "fld1","fld2" from "schema_name"."table_name".

So the foreign table in PG has to created in upper case within quotes.  It is 
bit of an annoyance.

Re: postgres server process crashes when using odbc_fdw

[Ravi Krishna]

> 
> Please note that odbc_fdw is not maintained by the postgresql developers, but 
> a separate project.


Translation: You are on your own.  We are hoping this will make our migration 
out of DB2 quicker. Oh well.

Re: postgres server process crashes when using odbc_fdw

[Ravi Krishna]

> 
> Come on. We can't realistically support & debug random postgres extending 
> projects, nor do we have control over them. And you're not necessarily on 
> your own, you could report the issue to odbcfdw's authors/github tracker.  Or 
> pay a company for support.
> 

On a related note is fdw for Oracle and SQLServer supported by the community ?

Re: Urgent :: Postgresql streaming replication issue - sync mode

[Ravi Krishna]

> 
> As the failed primary is having more data, How is it possible that primary is 
> committing transaction before they were applied on standby with 
> synchronous_commit=remote_apply?

If I am not mistaken remote_apply is only from ver 11.

PG 12 not yet for mac

[Ravi Krishna]

https://postgresapp.com/downloads.html

The link which says PG 12 is actually PG 11.

Re: date function bug

[Ravi Krishna]

> ctrlmdb=> select to_date('2018100X','MMDD');
> to_date

>2018-10-01
>(1 row)

I am able to reproduce this in 11.5 It seems PG can take a single digit for Day 
too.
select to_date('2018109','MMDD') produces 2018-10-09.

Re: date function bug

[Ravi Krishna]

> postgres=# select to_date('2018150X','MMDD');
  > to_date

> 2019-03-03


> postgres=# select to_date('20181501','MMDD');
>  to_date
> 
  > 2019-03-03
is this a cut-n-paste mistake?

Re: date function bug

[Ravi Krishna]

> 
> 
> 
> Surprisingly (to me), no….
> 
> db=# select to_date('20181501','MMDD');
>  to_date
> 
> 2019-03-03

The above fails in 11.5 SQL Error [22008]: ERROR: date/time field value out 
of range: "20181501"

Is this a bug ?

[Ravi Krishna]

We noticed this bug in Redshift. It then occurred to me that this may 
very well be a PG bug since Redshift is based on PG.

Reproduced it in Version 11.5

create table bugtest (fld1 char(1)) ;

insert into bugtest values('a');
insert into bugtest values('b');
insert into bugtest values('c');
insert into bugtest values('d');
insert into bugtest values('e');

select count(*) from bugtest where fld1 in ('a','b','c',
'd','e');  -> produces 5 as output.
So far so good.

Now check this:

select count(*) from bugtest where fld1 in ('a','b','c'
'd','e');
  
Note the missing comma after 'c'. 

PG takes it a syntactically right SQL and gives 3 as output. 

In SQLServer it errors out SQL Error [102] [S0001]: Incorrect syntax near 'd'.

Can't believe this bug was never found before.  We ended up wasting lot of time 
to figure this out.

Re: Is this a bug ?

[Ravi Krishna]

> 
> Simplify:
> 
> select 'a'
> db-# 'b';
> ?column?
> --
> ab
> (1 row)
> 
> This is not a bug.
> 
> https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html
> 
> Two string constants that are only separated by whitespace with at
> least one newline are concatenated and effectively treated as if the
> string had been written as one constant.
> 
> Geoff

Shouldn't the output be 4 in that case since it is looking 
for('a','b','cd','e') and it should find all but cd.
  

Re: Is this a bug ?

[Ravi Krishna]

So reading responses from all, here is a SQL test I did on few RDBMS:


select 'John'
   'Doe' as rrr
from information_schema.tables
limit 1;

PG output

rrr
---
JohnDoe

Oracle and mysql gave same output as PG with no error

SQLServer: Syntax error near 'as'.
DB2 gave same error as SQLServer.

Re: Index

[Ravi Krishna]

> 
> I have created indexes with certain set of columns. Now I want to remove one 
> of the column and add new column. Can this done without dropping the index?
> 

If you intent is to avoid taking that table offline while the index is being 
built, you can do this:

1. create index concurrently on the new set of columns
2. once (1) done drop old index

Re: explain plan difference

[Ravi Krishna]

> On Nov 3, 2019, at 11:03 PM, Steve Baldwin  wrote:
> 
> Thanks very much for the explanation Tom !!  You are correct - there are 
> dropped columns in the original.


Just to confirm, we are talking about tables from where some cols were deleted 
in the past, but 
VACUUM FULL not run on that table, right ?

Re: explain plan difference

[Ravi Krishna]

>> Just to confirm, we are talking about tables from where some cols were 
>> deleted in the past, but 
>> VACUUM FULL not run on that table, right ?
> 
> VACUUM would not change the state of the dropped columns.
> 

When does it change?

Re: explain plan difference

[Ravi Krishna]

> 
> Never, unless you drop and recreate the table.  Removing a dropped
> column would change the attnums of following columns, which we
> can't support because the tableoid+attnum is the only persistent
> identifier of a column.
> 
> (From memory, operations like VACUUM FULL and CLUSTER will rewrite
> dropped columns with NULLs to reduce their storage impact.  But they
> don't go away.)
> 
> 

Thank you.  I remember reading it here that VACUUM FULL does what you describe 
above.
So even TRUNCATE does not help here?

Re: Upgrade PGSQL main version without backup/restore all databases?

[Ravi Krishna]

-k option is kept precisely for this.  The upgrades are pretty fast, but still 
with some downtime. may be 30-45 min tops.  

Re: Partitioning large table (140GB)

[Ravi Krishna]

>In our production, we use postgres 9.5 with streaming replication 
>using repmgr, there is a large table of 140GB size which receives
>lots of inserts,
>Is it possible to partition this table in this version of postgres? 
>and if so, please, can someone let me know the best way to accomplish 
>this and the best practices around it?

In this version only inheritance is possible.  From my experience avoid using
triggers for insert and let the application (if possible) directly write to the 
right table.
 
 

Re: Conditional return of aggregated data

[Ravi Krishna]

> My initial idea was something along the lines of :
>  select (select sum(statcount) from stats_residence) as 
> aggstat,statcount,short_name_en from stats_residence where 
> aggstat>some_number;

Am I missing something basic.  The above can be done using
GROUP BY and HAVING clause.

Re: Conditional return of aggregated data

[Ravi Krishna]

>
> > My initial idea was something along the lines of :
> >  select (select sum(statcount) from stats_residence) as 
> > aggstat,statcount,short_name_en from stats_residence where 
> > aggstat>some_number;
>
> Am I missing something basic.  The above can be done using
> GROUP BY and HAVING clause.
or this
with t as
(select (select sum(statcount) from stats_residence) as aggstat,
 statcount,short_name_en from stats_residence
)
select * from t where aggstat > some_number

Apology if I did not understand the question correctly.

Re: Partitions child tables and analyze

[Ravi Krishna]

> 
> If rows are added, deleted etc from specific child tables of a partition is 
> it necessary when performing an analyze to analyze the entire partition or 
> just the children that experienced the row changes?

Under the hood each partition is a separate table, so why is analyzing entire 
parent table necessary.

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Ravi Krishna]

> 
> Depending on who wrote the code, they may have extracted the rows
> as hashrefs rather than arrays; that can be a 10x slowdown right
> there. [I have no idea why so many people are so addicted to storing
> rows in hashes, but it is always a significant slowdown; and 
> array slices are no more complicated than hash slices!]


I have not done perl code for a while now, but most perl coders, already
suffering from a complex of coding in an unreadable language do not
prefer to make it worse by using array which is position dependent
and hence reading it can be a nightmare when large number of cols are
selected.

Also isn't array_ref even better than array, since it avoids copying the data
to your local array in the code.

Re: Restrict connection from pgadmin.

[Ravi Krishna]

> 
> Not that I know of. pgAdmin is just another client so you just have the 
> methods available here:
> 
> https://www.postgresql.org/docs/12/auth-pg-hba-conf.html
> 

will the col application_name from pg_stat_activity list pgadmin as pgadmin. if 
yes, then the closest one can achieve
is to have a daemon process wake up every 1 min or so and terminate any pgadmin 
app.

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

[Ravi Krishna]

> 
> "already suffering from a complex of coding in an unreadable language"
> 
> hearsay and conjecture
> 

I was somewhat facetious. But what finished perl was the reputation it earned 
that it is a write only
language.  I don't think that reputation it earned is without merit.

IMO the early adopters of perl  were sysadmins and other non programmers who 
didn't do a stellar
job in writing a highly readable code.  To make it worse, perl folks take it as 
a badge of honor to
write their code in most obfuscated manner.  They even get some recognition for 
that.

Re: Need to find the no. of connections for a database

[Ravi Krishna]

> 
> Before taking a few reports, we need to ensure that only one connection is 
> made to the database and all other computers need to close the connection to 
> that database.   This is to prevent any additional entry(ies) that could be 
> made during the course of the report taking.  This single-user mode is to be 
> there till the report is taken and few entries are passed.  
> 
> How to do it?Is it possible to switch to single-user mode from 
> application and back to multi-user mode once the work is completed?  Or any 
> other solution available?
> 
> Happiness Always
> BKR Sivaprakash
> 

This concept is outdated.  You should set your application to correct isolation 
level to get a consistent state.

Re: Need to find the no. of connections for a database

[Ravi Krishna]

"If they enter any data, those data also need to be taken into account in this 
report. "

Pls read on PG's MVCC architecture.  In SQLServer, unless you enabled its bad 
implementation of Snapshot isolation,
you can't achieve the same.  So it makes sense there.  In PG it is easy to 
ensure that your report gets a point in time
consistent view of the data.

Trying to mimic one database in another is not a smart way.

Re: Determining the type of an obkect in plperl

[Ravi Krishna]

how can I determine what the data type of the value element is?


perl has a ref function which can tell what type of object.

https://perldoc.perl.org/functions/ref.html





--
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Re: Real application clustering in postgres.

[Ravi Krishna]

> 
> If you have mirrored disks, and you write junk (e.g, because of
> a flaw in a fibre channel cable, something I have witnessed),
> then you have two perfectly fine copies of the junk.
> 

Few years ago didn't this happen to Salesforce where a firmware bug corrupted 
the Disk, resulting in corruption of Oracle tablespace blocks
and all RAC nodes were equally useless since all of them read from the same 
disk.  Salesforce lost 4 hours worth of data.

How to get RAISE INFO in JDBC

[Ravi Krishna]

How do we get the notification mentioned in RAISE INFO of PL/PGSQL in JDBC. 
I can get it in psql, but not in JDBC.

I am talking about redshift which has the same procedural language like 
postgres.

thanks

Re: Postgres cluster setup

[Ravi Krishna]

> 
> Do you have similar setup like Oracle RAC in postgres core . I found in edb 
> but didn't find anything in postgres core. We are looking for setting up 
> replication with no outage and other node will be up , if the primary is 
> down. Any help would be great 😊

Oracle RAC is based on shared storage and not replication.  Are you talking 
about Dataguard ?

Re: dbeaver

[Ravi Krishna]

> - does dbeaver is a good frontend for pg administration?

It is an excellent sql client tool and I use it heavily for Redshift, 
SQLServer, Snowflake and PG.
However it is a not dba tool in the sense that it can DBA specific details and 
graphs as shown in pgadmin.

Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

[Ravi Krishna]

> On May 6, 2020, at 5:48 AM, Ram Pratap Maurya 
>  wrote:
> 
> Hi Team,
>  
> We are facing a problem in our PostgreSQL production database related to 
> abnormal growth of index size. Some of the indexes are having abnormal growth 
> and index size is larger than table data size.
> One table is having 75 G.B of index though table size is only 25 G.B. On 
> monthly basis we are performing vacuum to release the used space.
>  
> I am attaching the screen shot for your reference. Could you please help us 
> in resolving the same as this is degrading performance drastically.
>  
> 
> 

How many indexes are there in the table tstock_movement?  Could it be that 65GB 
is the sum total of index size of all indexes.

Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

[Ravi Krishna]

> 
> Hello Ravi,
>  
> Total number of index is 10 and 65GB is the sum total of index size of all 
> indexes for table “tstock_movement”
> I am attaching the screen shot for your reference.

>  
In that case 65GB is not surprising.

Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

[Ravi Krishna]

> On May 6, 2020, at 10:52 AM, Ashish Chugh  
> wrote:
> 
> Hello Ravi, 
> 
> 
> Total number of indexes are 10 and size is 65 GB. Shall we consider this as a 
> normal scenario or we need to look into the growth of the indexes as this is 
> increasing day by day and table data is not increasing so drastically. Due to 
> this performance degradation is there and we have to run full vacuum on 
> monthly basis.
> 
> Table size is only 25 gb.
> 
> Any help in this regard is appreciable.

Indexes are stored just like tables. From storage perspective there is no 
difference between a table and an index.
So the sum of 10 different tables to 65GB, compared to 25GB of one table sounds 
possible.

Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction

[Ravi Krishna]

the pgm does an INSERT, after this the row is there and can be seen
with SELECT; than I CLOSE a non existing cursor, which rolls back
the INSERTed data:



I have not done coding in ESQL/C in a long time, but shouldn't that be 
expected as any error should trigger a rollback.