Re: [GENERAL] Incremental refresh - Materialized view

[Rakesh Kumar]

You have already been informed. PG, as yet, does not allow incremental refresh 
of a MV.  It allows online refresh of a MV, but that it does by doing a full 
table scan of the base table and rebuilding the MV.



From: Krithika Venkatesh 
To: John R Pierce 
Cc: pgsql-general@postgresql.org
Sent: Tuesday, November 7, 2017 2:35 AM
Subject: Re: [GENERAL] Incremental refresh - Materialized view

Materialized view log is one of the feature in oracle. It creates a log in 
which the changes made to the table are recorded. This log is required for an 
asynchronous materialized view that is refreshed incrementally.

I read in the below link about incrementally refreshing the materialized view 
in postgresql:

https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log

Thanks,
Krithika





On 07-Nov-2017 12:37 PM, "John R Pierce" 
mailto:pie...@hogranch.com>> wrote:
On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:
I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized view in 
postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.


you refresh a materialized view with REFRESH MATERIALIZED VIEW name;There's 
no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a feature of 
some other database server ?



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Naming conventions for column names

[Rakesh Kumar]

>Those naming conventions are twenty-five years old, and there is an
>astonishing amount of client code that would break if we ran around
>changing existing system catalog column names.  It's very unlikely that
>any proposal to do that would even receive serious consideration.

>The bar to using new naming conventions in new catalogs would be
>lower, of course, but then you have to think about the confusion
>factor of having different naming styles in different places.

Isn't there a word for the above : Technology debt :-)


-- 
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] Incremental / Level -1 backup in PG

[Rakesh Kumar]

>> Yes John I do know about using WAL archive.  IMO that will not be as fast as
>> restoring using the incremental backup.

>That's an opinion, have you tried measuring? Because normally I've found that
>1.- Incremental backups are slow and impose a greater runtime penalty
>on the system than log-change-archiving methods.

I assume you are talking about other RDBMS where we can do PITR restore either:

 Restore base backup
 Apply  incremental backup
 Rollfoward WAL logs to the point you want to recover
vs
 Restore base backup
Rollfoward WAL logs to the point you want to recover

When the PITR is far apart from the time of base backup (far apart as in, let us
say 4 to 5 days), the first approach beats the second approach hands down. This
coming from experience. Reason is simple. In the second approach every 
transaction
(from the time of base backup) has to applied to roll-foward to PIT. In 
incremental backup,
a block is only applied once, regardless of how many times it changed after the 
base backup.

The diff may not be much if PITR is close to the time of base backup.

Note: I have never tried option (1) for PG. 

>You are assuming your backup product does direct-diff to base. Those
>are gonna be costly when friday arrives.

You mean costly as in finding more and more blocks changed since weekend. that 
is
correct. However Oracle keeps track of location of all changed blocks since 
last base
backup and it helps in quick backup. It does not scan entire tablespace to 
figure which
blocks changed.


>Is it really testable / a lot faster ? ( bear in mind if a product
>just supports one strategy there is a huge interest in telling it is
>the faster one )

Nope. Incremental backup is not the only way to reach PITR until Thu afternoon
from a base backup on Sunday. You can always apply redo logs after restoring
from the base backup.
And yes, it is much faster.  We do it to prove to our clients why incremental 
backup
will benefit them more.



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

[GENERAL] Incremental / Level -1 backup in PG

[Rakesh Kumar]

PG does not have a concept of incremental  backup. The way it works in Oracle 
and other RDBMS is that incremental backup only backups up changed blocks since 
the last full backup.  So if only 10% of blocks changed since the last full 
backup, incremental backup will be only for 10%.
I am wondering whether it is technically feasible to implement it like this:

1 - At the time of full backup, note the last modified time of each data file 
in a repository.
2 - Next time when incremental backup runs, for every data file it will check 
the last modified time of it with the one in the repository to determine 
whether it has changed since last full backup. If yes, back it up.

Now on to restore:

1 - First restore full backup.
2 - Restore incremental backup.  

My question: Will it work in PG?


-- 
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] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

[Rakesh Kumar]

>More to the point, whatever "MorphOS" is, it isn't AmigaOS.  It was pretty
>clearly stated in the original thread that AmigaOS had no support for
>fork(), without which there was no chance of running Postgres. 

Just curious, if PG is completely dependent on fork(), how was it ported to 
Windows
which also does not support fork directly.

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

Re: R: [GENERAL] Slow queries on very big (and partitioned) table

[Rakesh Kumar]

> We thought to implement one partition for day.

That would be 365 partitions in a year.

In our experience INSERTS suffers the most in a partitioned table because 
triggers are the only way to route the row to the proper child (inherited) 
table.

Question: How is your insert pattern? Do you insert always current date. In 
that case you can write the trigger code to have current date at the top so 
that the insert trigger finds the matching date as early as possible.

Selects, updates and deletes are reasonably better.

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

[GENERAL] Using xmin and xmax for optimistic locking

[Rakesh Kumar]

In the chapter "Using optimistic locking" of the book "PG Cookbook Second 
Edition"
it is mentioned how the app can first fetch row from the table in the form
select a.*::text from table a where ...
Then do the work and then when it comes to committing do it as
   update table
  set 
   where table.*::text = (saved from select).

If the row was changed between the time it was first read and updated, the
update will do touch any rows as the ::text will be different.

Why can't we use xmin and xmax columns to achieve the same.

select a.*,xmin,xmax into ... from table A
do your work
update table
set ...
where pky = blahblah
and xmin = 2907587
and xmax = 0 ;

I tested it and it works.  what I did was to select xmin and xmax and then 
sleep for a min.
In the meantime, I update the same row in another session.
After 1 min the update session failed to update any row because the combination 
of xmin
and xmax was no longer true.

I was under the impression that xmin/xmax can not be used in the where clause 
for business logic as described above.

Am I missing anything ?  If this works, it can make optimistic locking lot 
easier due to generic coding using xmin/xmax.

[GENERAL] Indexes and MVCC

[Rakesh Kumar]

https://www.youtube.com/watch?v=8mKpfutwD0U&t=1741s

Somewhere around 13th minute, Chris Tavers mentions this:

1 - In the queuing table, the application deletes lot of rows (typical for a 
queuing table).
2 - Query trying to find out rows to be picked by the queue, accesses them via 
indexes.
3 - Vacuum took lot of time to clean up dead rows.
4 - Indexes do not reach out to the latest visible tuple and has to traverse 
lot of dead
 rows before hitting the row required by the sql. This is because of (3).

My Question:
Is the (4) true for all updates.  AFAIK, if an update occurs on even one index 
col, the
index itself creates a new version in MVCC. Only HOT updates will end up in 
situation
described in (3).

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

[Rakesh Kumar]

LOCK TABLE yourtable ;
CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ;
TRUNCATE yourtable;
INSERT INTO yourtable SELECT * from keep;
COMMIT;
===
the above snippet assumes truncate in PG can be in a transaction. In other 
words, while truncate by itself
is atomic, it can't be rolled back. So in the above case, if "INSERT INTO 
yourtable SELECT * from keep;" and
we rollback, will it rollback yourtable.

-- 
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] clarification about standby promotion

[Rakesh Kumar]

>> Kill ? You mean "pg_ctl stop -m fast" right ?

Yes.


>Use timeline to resync ? Timeline is an internal mechanism in PostgreSQL, not
>a tool, so I don't get this step...You mean using pg_rewind ?

pg_rewind which uses timeline.



-- 
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] clarification about standby promotion

[Rakesh Kumar]

>Sure, but when you are doing a switchover, the standby is supposed to be
>connected to the master when you shutdown the master. So based on the doc,
>the standby should receive **everything** from the master before the master
>actually shutdown.

We use 9.5 and even in that version there is no handshake during role reversal.
In fact PG does not have concept of handshake and role reversal unlike in Db2, 
oracle
and sqlserver you can switchover from one to other by a single command.

Our DBAs use home grown script for switchover which does the following:

1 - first kill postmaster in the outgoing primary.
2 - promote the standby as the new primary
3 - use timeline to resync former primary (of step 1) with the new primary 
(step 2).
4- open up connection to the new standby (former primary)

I hope a more elegant way exists as in other RDBMS.

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

[GENERAL] Transaction apply speed on the standby

[Rakesh Kumar]

Ver 9.6.1

In a streaming replication can it be assumed that if both primary and standby 
are of the same hardware, then the rate at which transactions are applied on 
the standby will be same as that on primary. Or standbys are always slower than 
primary in applying transactions because of the way replication works.

The reason why I am asking is that I am trying to find what are the 
circumstances when there can be a large gap between primary and standby in 
async replication mode.

Thanks


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

[GENERAL] What happens when a large table is alterted from UNLOGGED to LOGGED

[Rakesh Kumar]

In order to speed up loading a set of 12 tables, I have declared them as 
UNLOGGED.  As per documentation, an unlogged table will also have unlogged 
indices.  

Question: When the table is converted back to logged, does PG rebuild all 
indices before marking them as logged.  Also what about FKY constraints. Does 
it revalidate them ?

thanks.


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

[GENERAL] Querying dead rows

[Rakesh Kumar]

Is there a way to query dead rows (that is, rows which are dead and still not 
cleaned up by Vacuum) using SQL.  I am asking this just as an academical 
question.


-- 
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] pgbench and scaling

[Rakesh Kumar]

I forgot to mention that the db is replicated synchronously.  I think that is 
the culprit.


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

[GENERAL] pgbench and scaling

[Rakesh Kumar]

PG 9.5.3
RHEL Linux

To cut the story short and giving as much details as I can remember:

I created 40 database db1 .. db40 in a cluster.
On each database I initialized pgbench tables with a scale of 15.

Then I concurrently ran 5 pgbenches for 15 min. Each pgbench connected to one 
db. In other words, no two pgbench sessions were working on the same db.

I noticed that as I scale from 5 to 10 to 20 to 40, the TPS starts falling 
almost linearly :

with 5, TPS was doing 639
with 10 TPS was down to 490
with 20 TPS was down to 280
and so on.

Are we missing something?  Where do I start looking for the root cause.

thanks






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

[GENERAL] Abscence of synonym

[Rakesh Kumar]

I need some ideas here.

Let us say we have a bunch of tables, called a,b,c,d. We will get a batch of 
data in files which need to be processed. At the end of processing, the tables 
will get a fresh set of data.  The problem we would like to solve is to allow 
access to the tables while they are getting processed by the new batch. 
Obviously, during the processing the data will be in an inconsistent state, 
which the app should not be exposed to. At the end of processing, the app can 
see the new data.

In RDBMS where synonyms are allowed we would do this:

keep two set of tables a_1 and a_2
A synonym a will either point to a_1 or a_2. 
applications will refer synonym a only, never a_1 or a_2.
While synonym is referring to a_1, we can process a_2. 
At the end of processing change the synonym to point to a_2.
next time, repeat it, just flip the tables from a_2 to a_1.

How this can be done in PG 9.5.3?

Thanks.

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

[GENERAL] How is Greenplum's partitioning different from PG's inheritance

[Rakesh Kumar]

Reading the following two:

http://www.greenplumdba.com/partitioning-in-greenplum

http://gpdb.docs.pivotal.io/4350/admin_guide/ddl/ddl-partition.html

It is clear that GP's partitioning is just a skin on top of inheritance.  Does 
anyone know how GP implements the insert re-routing.  Is it done via triggers, 
just like we do in PG.

thanks. 

-- 
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] [Bucardo-general] doubts about target db?

[Rakesh Kumar]

>Other thing, with bucardo what is the difference between in a
>master-master replica vs master-slave, at the end in both ways the
>user has a 2nd DB with R/W permission?

I think in a M-M mode, updates from both dbs will be replicated to the other db.
In other words they will be eventually consistent.
In a M-S mode, even though Slaves can be updated by the app, its updates will
not be replicated back to the master. While updates from M->S will happen all
the time, updates from S will not go to M.



-- 
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] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Rakesh Kumar]

>Cores do not help, postgres is single-threaded. RAM MAY help, but I

I hope this is no longer true from 9.6 for those queries where PG can use 
parallelism.

>suspect your operations are IO bound. Of course, with the sparseness
>of the details, one can not say too much.



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

[GENERAL] pgpool question

[Rakesh Kumar]

I posted this in the pgpool mailing list and so far has not recvd any response. 
Looks like that mailing list is hardly active.
=

I am researching pgpool technology.  Let us say we want to use it as an 
alternative to streaming replication of PG.  Apparently in replication mode, 
pgpool can replicate to all participating nodes, which presumably will be in 
raw mode (non replicating mode).  

My questions:

1 - Do we have a choice of limiting number of nodes for sync writes (and 
remaining one in async), so that overall performance impact is limited.
2 - If a node is down, does pgpool has the ability to resync from the last 
known transaction that was committed. How does it do it? Does it maintain 
 a state information or save all transactions that need to be replayed 
later on.
3 - Is app-stickiness possible. By app-stickiness I mean, a given app or 
session always goes to the same node.  This helps in avoiding stale reads.
4-  Is the failover transparent to the client. Meaning, if a node goes down, do 
clients have to use a new connection string.
5 -  How about high availability  of pgpool itself.  Can we have multiple 
pgpools?

If there is a documentation to answer these questions, please let me know.

thanks.


-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

>do you run a separate instance of the app for each tenant, or is there one app 
>that identifies the 
>tenant and handles them accordingly ?

Each tenant will have different app server. there will be persistent connection 
for each tenant.

-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

>Are you restoring because your whole system failed, or because one client did 
>something 
>wrong and needs just their data rolled back?

Chances of restoring just for one client will probably be 99% of use cases.  

> Do your clients authenticate directly to the database, or to the app server?

thru app server.

thanks.

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

[GENERAL] pgadmin vs psql output

[Rakesh Kumar]

I have a bytea col which stores Java UUID data. When I run

Select cast(uuid_col as varchar) from table limit 1 

Output in pgadmin
-===
"A001"

Output in psql

 \x4130303030303030303030303030303030303030303030303030303030303031

is there some setting in psql output I need to take care of.


-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

I don't know if that's helpful to you or not, but hopefully it was at least a 
little.
===
yes it was.  thanks

-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

>Then you need different clusters per tenant.  Otherwise, the WAL records
> of different tenants are inextricably mingled together.

Yes we are aware of it .This part is OK as it is not deemed as user table data. 

-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

> I've been reading this discussion with great interest, to see what other
> Postgres experts think. :-)

I am bit disappointed that most of the replies are questioning why we are
doing what we are doing. Once again, we (db designers) have no choice
in that.  What I would like to know that which one is better :- multiple db
vs multiple schema.  Read few interesting arguments and noted that
connection pooling works better with multiple schemas than dbs. Anything else?

thanks

-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

> ok, thats ridiculous, isn't it.   so now its time to find a compromise.

You don't understand how sales people pitch our products. We deal with 
financial data
and our customers are extremely sensitive to even imagining that their data 
will co-reside
with that of their competitors who also are our customers. A typical fear 
mongering Q from
them "what if due to a bug in your s/w, our competitors end up looking at our 
data" or 
something like that. That's why schema level vs db level discussion.

Just a reminder, I started this thread to learn more on the technical drawbacks 
of choosing
either option. For example, in SQL Server, having multiple databases in an 
instance does not
mean more significantly pressure on resources (as compared to multiple 
schemas). In DB2
it does since many resources like cache (buffers) are db specific. 

-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

From: Venkata B Nagothi 
Sent: Friday, September 30, 2016 02:48
To: Rakesh Kumar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 10:16 AM, Rakesh Kumar 
mailto:rakeshkumar...@outlook.com>> wrote:



From: Venkata B Nagothi mailto:nag1...@gmail.com>>
Sent: Thursday, September 29, 2016 17:25
To: Rakesh Kumar
Cc: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar 
mailto:rakeshkumar...@outlook.com><mailto:rakeshkumar...@outlook.com<mailto:rakeshkumar...@outlook.com>>>
 wrote:

Hi

I would like to know which technique is better for supporting multi-tenancy=
 applications, going upto hundreds or even thousands of tenants.

1 - One database with difference schemas (one schema per tenant)
or
2 - One database per tenant.

Did you mean one database with-in a postgresql cluster ?

Yes.  Say something like this within a PG cluster

db4978
db6234
...
100s of such databases.

That would make things worst if you are going for one database per tenant. As 
said by John just now, it would end up in an very complex and bad design 
contributing to very poor performance and high maintenance overhead.
A schema per tenant would be a good idea and its hard to say without knowing 
the data isolation levels you require for each tenant.


We require complete data isolation. Absolutely nothing should be shared between 
two tenants.

WHy would multiple dbs be any worse than multiple schemas in performance?




-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

From: Venkata B Nagothi 
Sent: Thursday, September 29, 2016 17:25
To: Rakesh Kumar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar 
mailto:rakeshkumar...@outlook.com>> wrote:

Hi

I would like to know which technique is better for supporting multi-tenancy=
 applications, going upto hundreds or even thousands of tenants.

1 - One database with difference schemas (one schema per tenant)
or
2 - One database per tenant.

Did you mean one database with-in a postgresql cluster ?

Yes.  Say something like this within a PG cluster

db4978
db6234
...
100s of such databases.


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

[GENERAL] Multi tenancy : schema vs databases

[Rakesh Kumar]

Hi

I would like to know which technique is better for supporting multi-tenancy=
 applications, going upto hundreds or even thousands of tenants.

1 - One database with difference schemas (one schema per tenant)
or
2 - One database per tenant.

The points to be considered are:

1 - which is more light weight from resources point of view.
2 - which is easier for backup/restore
3 - Which is better from security p.o.v

It is possible that this question has been asked before. Is there a  link t=
o earlier posts on the subject.

Thanks.

-- 
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] Incrementally refreshed materialized view

[Rakesh Kumar]

> Of course 9.5 is the current release so the answer is Yes, since 9.5

https://www.postgresql.org/docs/current/static/sql-creatematerializedview.html

What am I missing. I don't see any support for incremental refresh.

Just in case we are not speaking the same thing:  When a MV is created for the 
first time on a base table, all further updates on the base table, will be 
handled by only applying the delta changes to the base table. That is called 
incremental refresh. Check how DB2 does it:

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r977.html

Or does " REFRESH MATERIALIZED VIEW." does it only incremental refresh.

Does PG have a concept of MV log, from where it can detect the delta changes 
and apply  incremental changes quickly.

Re: [GENERAL] Incrementally refreshed materialized view

[Rakesh Kumar]

Does PG support INCREMENTAL MV ? Looks like not (until 9.5)

Re: [GENERAL] Chante domain type - Postgres 9.2

[Rakesh Kumar]

>You sound like you think that varchar(50) is somehow cheaper than text.

The biggest impediment to text cols in other RDBMS  is no index allowed.
If PG has an elegant solution to that, then yes I see the point made by the
original poster.

Re: [GENERAL] Chante domain type - Postgres 9.2

[Rakesh Kumar]

>I have done some research after converting my database from MySQL 5.6 to 
>PostgreSQL 9.6 (the best move I have ever made),
>and the consensus I found can be summed up as:

>1.  Never, neve, never use VARCHAR or even CHAR
>2. Always always, always use TEXT

>Unless, that is, you have some kind of edge case. This may require a little 
>work upfront, but it will save you from a
>TON of grief down the road.

Can you elaborate?  Why would anyone create a text column to store customer 
name or product name which can very well be in varchar(50) type of cols.

[GENERAL] Multiple inserts

[Rakesh Kumar]

Hi

I am noticing that if I do this

insert into table values(1,a)
insert into table values(2,b)

insert into table values(3,c)

commit after 500 rows
it is lot slower (almost 10x) than
insert into table values((1,a),(2,b),(3,c)) .. upto 500

It is obvious that the gain is due to network trip avoided after every insert.

My question is: Are they any known side-effects or drawbacks of using multiple 
inserts.  I am aware the error checking is much more difficult in the second 
approach. Any other drawbacks?

thanks

Re: [GENERAL] question on error during COPY FROM

[Rakesh Kumar]

Is it true that one datafile in PG can only belong to one object (table/index)

On Tue, Aug 23, 2016 at 9:55 AM, Francisco Olarte
 wrote:
> On Tue, Aug 23, 2016 at 2:32 PM, Ilya Kazakevich
>  wrote:
>>>does that mean that I should always execute a VACUUM to recover the
>>>wasted space when an error is triggered or will the auto-vacuum mechanism
>>>do the job by itself ?
>> If you have autovacuum enabled it will clean up tablespace. However, space 
>> will not be returned to filesystem but will be reused by database.
>> You may run VACUUM FULL manually to return it to filesystem.
>
> A normal vacuum may also return some space, specially after a big bulk
> load, see second paragraph of 23.1.2 the URL you posted:
>> https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html
>
> Where it says "However, it will not return the space to the operating
> system, except in the special case where one or more pages at the end
> of a table become entirely free and an exclusive table lock can be
> easily obtained.". A big aborted bulk load may just fit the case, as
> it may put a lot of tuples at new pages at the end and be executed in
> a low-load period where the lock is easier to acquire.
>
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


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

[GENERAL] Serializable read and blocking

[Rakesh Kumar]

As per this blog post

http://bonesmoses.org/2016/07/29/pg-phriday-constipated-connections/

I have a question which I asked there too, but unfortunately did not
receive any answer.

The question is re the following claim:

- Readers do not block readers.
- Readers do not block writers.
- Writers do not block readers.
- Writers may block writers.


Are the above statements true even with SET TRANSACTION SERIALIZEABLE mode.
I am specifically interested in the 3rd condition (- Writers do not
block readers.)

thanks


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

[GENERAL] Can stored procedures be deployed online

[Rakesh Kumar]

Can an existing stored procedure be modified online while other users
are executing it. In Oracle, the session doing CREATE OR REPLACE
PACKAGE would wait for other session to complete. Once the package is
changed, first time other sessions will get an error "package
invalidated". How is it in PG.

thanks.


-- 
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] restore a specific schema from physical backup

[Rakesh Kumar]

> Are you saying that?:
>
> 1) You ran pg_basebackup against a live cluster and sent the output to
> another location.
>
> 2) At the other location the cluster is not in use.
>
> 3) You want to grab the contents of the inactive cluster directly off the
> disk.
>
> If that is the case, then no it is not possible without making the cluster
> live.
>
> If you mean something else then more details are needed.

Sure.

1 - You ran pg_basebackup on node-1 against a live cluster and store
it on NFS or tape.
2 - Do a  restore on node-2 from the backup taken on (1), but only for
a subset of the database
 (schema/database)
3- Put the cluster live on node-2 after (2) completes. Essentially the
cluster will now be a small
subset of cluster on node-1.

Benefit: If I have to restore only 5% of entire db, it should be lot faster.


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

[GENERAL] restore a specific schema from physical backup

[Rakesh Kumar]

If a cluster is backed up physically using pg_basebackup, how can we
restore only a particular schema from it. Is it even possible?

Thanks


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

> And? Oracle and MySql doesnt have it but can downgrade right out the box.
> Quick and easy.

No it is not for mysql.

http://dev.mysql.com/doc/refman/5.7/en/downgrading.html

"In-place Downgrade: Involves shutting down the new MySQL version,
replacing the new MySQL binaries or packages with the old ones, and
restarting the old MySQL version on the existing data directory.
In-place downgrades are supported for downgrades between GA versions
within the same release series. For example, in-place downgrades are
supported for downgrades from 5.7.10 to 5.7.9.
The above is easy and same as PG if you are going from 9.5.2 to 9.5.1.
"
Now let us take about major downgrade.

Downgrading one release level is supported using the logical downgrade
method. For example, downgrading from 5.7 to 5.6 is supported. Logical
Downgrade: Involves using mysqldump to dump all tables from the new
MySQL version, and then loading the dump file into the old MySQL
version. Logical downgrades are supported for downgrades between GA
versions within the same release series and for downgrades between
release levels. For example, logical downgrades are supported for
downgrades from 5.7.10 to 5.7.9 and for downgrades from 5.7 to 5.6.

Only a fool will consider this as simple. And BTW all products support
methods similar to mysqldump. What we are looking at, is an in-place
downgrade after a major version upgrade and that looks to be almost
non-existent.


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov  wrote:

> Sorry, what? You can rollback to previous version of software what you had.
>
> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007

Not so fast. This requires the db to be set in compatibility mode of
the lower version. Once that is removed, no downgrade is possible.

Also starting from Oracle 12 this downgrade option has been removed.
That is, you can't upgrade in-place from Or 11 to 12 and then
downgrade back. The only supported way is the restore from an old
backup (which is true  for all products). I don't work in Oracle, but
got this clarified by someone who is an Oracle dba.


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

On Wed, Jul 27, 2016 at 2:07 PM, Andrew Sullivan  wrote:

> It almost never happens that a minor version (N.M.x, x is minor)
> requires an upgrade at all.  Change your binaries and you're done.
> Catalogue incompatibility historically was the basis for something
> becoming a major version upgrade.  (I can recall a couple bugs where
> you had to tickle the catalogues, so it's not exactly true that
> they're never incompatible, but it's incredibly rare.)

Yeah good enough.


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier
 wrote:

> Stupid question here, but do we provide any less then what MySQL does?   I’m
> reading:

mysql provides same functionality for rollback like oracle/db2
provides. That is,
rollback on a minor version upgrade possible, but not on major version upgrade.

I am surprised PG does not even allow minor version rollback.


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian  wrote:

> Yes.  I was saying I don't know how to improve pg_upgrade to address it.

This problem is there even in oracle/db2/sqlserver. None of them allow
rollback to the lower version
unless it is a minor version upgrade. Major version upgrade almost
definitely involves change in transaction log
(WAL) structure and hence no rollback.


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian  wrote:

> I agree, but I am not sure how to improve it.  The big complaint I have
> heard is that once you upgrade and open up writes on the upgraded
> server, you can't re-apply those writes to the old server if you need to
> fall back to the old server.  I also don't see how to improve that either.

doesn't and pg_logical solve this by logically replicating and allowing for
different architecture/version between the replication nodes ?


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

--
Sent from mobile. 

On Jul 26, 2016, at 5:56 PM, Joshua D. Drake  wrote:

> On 07/26/2016 02:49 PM, Rakesh Kumar wrote:
> This is an old news. They are using mysql as a nosql to store schemaless. 
> Basically one giant blob col. And thats where the role of mysql ends. The 
> bulk of the processing will be in nosql.

That doesn't mean they didn't bring up some very good points.

JD

Yes of course. But this is not one of those "product A sucked and we moved to 
product B and same workload and work is now 10 times better. "



-- 
Command Prompt, Inc.  http://the.postgres.company/
   +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


-- 
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] Uber migrated from Postgres to MySQL

[Rakesh Kumar]

This is an old news. They are using mysql as a nosql to store schemaless. 
Basically one giant blob col. And thats where the role of mysql ends. The bulk 
of the processing will be in nosql. 



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

Re: R: [GENERAL] Vacuum full: alternatives?

[Rakesh Kumar]

But then autovaccum avoids this. Granted it won't give back free space to OS, 
but it won't let it grow too (or am I missing something).

  From: Job 
 To: Rakesh Kumar ; "pgsql-general@postgresql.org" 
 
 Sent: Monday, June 20, 2016 5:39 AM
 Subject: R: [GENERAL] Vacuum full: alternatives?
   
#yiv6783361115 #yiv6783361115 --P 
{MARGIN-BOTTOM:0px;MARGIN-TOP:0px;}#yiv6783361115 Hi Rakesh, if i do not free 
disk space, after some days disk can become full.Everyday we have a lot of 
pg_bulkload and delete. Thank you!
Francesco Da: pgsql-general-ow...@postgresql.org 
[pgsql-general-ow...@postgresql.org] per conto di Rakesh Kumar 
[rakeshkumar46...@gmail.com]
Inviato: lunedì 20 giugno 2016 11.34
A: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Vacuum full: alternatives?

Any reason why you need the space back? What is wrong with space remaining 
constant at 4GB.

From: Job 
To: "pgsql-general@postgresql.org" 
Sent: Monday, June 20, 2016 5:18 AM
Subject: [GENERAL] Vacuum full: alternatives?

Hello, 

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is 
issueing a vacuum full .
But the operation is very slow, sometimes 2/4 hours, and table is not available 
for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the 
problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.

Are there some suggestions or another way to manage this?

Thank you!
Francesco

-- 
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] Vacuum full: alternatives?

[Rakesh Kumar]

Any reason why you need the space back? What is wrong with space remaining 
constant at 4GB.

  From: Job 
 To: "pgsql-general@postgresql.org"  
 Sent: Monday, June 20, 2016 5:18 AM
 Subject: [GENERAL] Vacuum full: alternatives?
   
Hello, 

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is 
issueing a vacuum full .
But the operation is very slow, sometimes 2/4 hours, and table is not available 
for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the 
problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.

Are there some suggestions or another way to manage this?

Thank you!
Francesco

-- 
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] Changelog version from 8.1.2 to 9.3.6

[Rakesh Kumar]

> 8.2, 8.3, and 8.4 are all Major releases of the PostgreSQL product.  For
> most other products it would as if the numbering went from "15, to 16, to
> 17".  The last 8.x release was 8.4 (so, there were 5 major releases that all
> shared the same prefix value of 8) and the 9.x series goes from 9.0 to 9.6
> (presently in beta).  Instead of 9.7 we will be going to 10.0 AND at the
> same time modernizing our numbering scheme to lose the prefix-suffix
> components.  IOW, after 10.0 the next major release will be 11.0.  The .0
> will increment for minor releases in which we only apply bug-fixes.  There
> will no longer be a third number.

Currently are minor upgrades reversible, that is, can we rollback from
9.4.3 to 9.4.2.


-- 
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] BDR

[Rakesh Kumar]

http://bdr-project.org/docs/next/logical-vs-physical.html

"It (BDR) has significant advantages - and some disadvantages - when
compared to PostgreSQL's older physical (block-based) streaming or
archive-based replication with warm or hot standby"

What exactly is block based? Changes are recorded in the redo log,
right? Does that mean that in streaming replication, from redo log the
server applies changes at the block level of the data-file. That would
also mean that at any time, both primary and standby would be exactly
same, block by block.


-- 
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] BDR

[Rakesh Kumar]

> This seems relevant...
>
> http://bdr-project.org/docs/stable/logical-vs-physical.html

thanks. very useful.


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

[GENERAL] BDR

[Rakesh Kumar]

Sorry if this question was asked before.  As I understand currently
BDR does not support the replicating nodes to run different major
versions, like
9.4 <-> 9.5.

Is this in the works?

thanks


-- 
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] What is the general opinion on use of tablespaces

[Rakesh Kumar]

> Their main problem to overcome when using them is that they tie PostgreSQL
> much more tightly to the underlying configuration of the operating system
> and thus you need to ensure that your processes and procedures accommodate
> that reality since the tools that PostgreSQL provides can only do so much.

Are you specifically referring to the restore process.

Tablespaces help distributing large tables in different file systems.


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

[GENERAL] What is the general opinion on use of tablespaces

[Rakesh Kumar]

I saw a slide recently where the use of tablespaces was discouraged.
What does the community think of tablespaces.

thanks


-- 
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] Alternate or Optimization for with hold cursor

[Rakesh Kumar]

On May 12, 2016, at 4:57 AM, sangeetha  wrote:

Currently , I am using "With hold" cursor. In our case , the With hold cursor
is used to fetch the next record of the given primary key . 

Can you explain your use case. If i understand with hold correctly, it is 
typically used to preserve locks even after commit , so as to get a consistent 
view of data. 


The performance
is very slow for large data set. Can you provide me some alternative ways
like having own copy of table , or optimization for With hold cursor?

Thanks and Regards,
S.Sangeetha



--
View this message in context: 
http://postgresql.nabble.com/Alternate-or-Optimization-for-with-hold-cursor-tp5903211.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


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

[GENERAL] Does PG support in place upgrade

[Rakesh Kumar]

Compose (@composeio)
4/26/16, 1:24 PM
You can now upgrade your #PostgreSQL 9.4 to 9.5 easily at Compose. 
buff.ly/1WRsFFu #RDBMS

Based on the above tweet it seems that PG has no native way of doing an inplace 
upgrade of a db. How do users upgrade db of tera byte size. 

--
Sent from mobile. 

[GENERAL] Does frequent update of a row affects performance

[Rakesh Kumar]

Pardon me if this has been discussed before. 

I believe that PG back-end does not version index rows the way it does the data 
rows. Assume that the app updates a row frequently (several times in a second). 
For each update, PG will create a new version.  However I believe the primary 
key index pointing to that row will point to only the first row and the back 
end has to work its way traversing the list until it finds the appropriate row 
matching the xmin/xmax.  Does frequent vaccum take care of this by removing the 
dead rows and hence reducing the commute.

Re: [GENERAL] Freezing localtimestamp and other time function on some value

[Rakesh Kumar]

I think PG does fixed time within a tran. check the output of the following sql

begin;
select now() ;
select pg_sleep(10);
select now() ;
commit;
select now() ;
select pg_sleep(10);
select now() ;
~

On Tue, Apr 12, 2016 at 6:50 AM, Alex Ignatov  wrote:
> Hello!
> Is there any method to freeze localtimestamp and other time function value.
> Say after freezing on some value sequential calls to these functions give
> you the same value over and over again.
> This  is useful primarily for testing.
>
> In oracle there is alter system set fixed_date command. Have Postgres this
> functionality?
>
> --
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Trying to understand page structures in PG

[Rakesh Kumar]

On Wed, Apr 6, 2016 at 6:33 AM, Albe Laurenz  wrote:
> Rakesh Kumar wrote:

> Every row has two system columns associated with it: xmin and xmax
>
> xmin is the transaction ID that created the row, while xmax is
> the transaction ID that removed the row.
>
> So when an update takes place, xmax of the original row and xmin
> of the new row are set to the transaction ID of the current transaction.
>
> Furthermore, the commit log (CLOG) logs for each transaction whether
> it was committed or rolled back.
>
> Now when a backend examines a row, it first checks if the row is
> visible, i.e. xmin must be less or equal to the current transaction ID
> and xmax must be 0 or greater than the current transaction ID
> or belong to a transaction that was rolled back.
>
> To save CLOG lookups, the first reader who consults the CLOG will
> save the result in so-called "hint bits" on the row itself.

I am assuming the same must be true for the indexes also. Does PG look
up primary key
by examining the rows like you described above.


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

[GENERAL] Trying to understand page structures in PG

[Rakesh Kumar]

Hello

I understand that when an update of say 100,000 rows are made, PG
writes the updated rows as a new row. These new rows are not visible
to any sessions except the one creating it. At commit time PG flips
something internally to make these rows visible to all.

My Q: what happens to those rows which use to contain the values
before the update. Shouldn't something change in those rows to
indicate that those rows are no longer valid. Who does it chain those
rows to the new rows.

thanks.


-- 
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] What does Rows Counted mean

[Rakesh Kumar]

On Tue, Apr 5, 2016 at 1:10 PM, Melvin Davidson 
wrote:


> FYI, in PgAdmin, there is an Option which determines if a _physical row
> count_ should be performed if the _estimated_ row count is less than a
> specified amount. It defaults to 2000.
> To change it, Click on File, Options and under Browser click Properties.
> There you can change the default.
>

Ok that was it. Thanks.

Re: [GENERAL] What does Rows Counted mean

[Rakesh Kumar]

This is on the object browser. When you navigate to the table and
click on the table name, you will see the output on the right side and
check the Properties tab.

On Tue, Apr 5, 2016 at 10:37 AM, Adrian Klaver
 wrote:
> On 04/05/2016 07:11 AM, Rakesh Kumar wrote:
>>
>> pgAdmin shows this:
>>
>> Name:   tableA
>> OID
>> Owner
>> Tablespace
>> Rows (estimated) : 10
>> Rows (Counted)   : not counted
>>
>> What is Rows (Counted) and why it is showing not counted even though
>> the table has been analyzed.
>
>
> Where is the above coming from in pgAdmin?
>
> http://www.postgresql.org/docs/9.5/interactive/sql-analyze.html
>
> "ANALYZE collects statistics about the contents of tables in the database,
> and stores the results in the pg_statistic system catalog. "
>
> so:
>
> production=# analyze verbose projection;
> INFO:  analyzing "public.projection"
> INFO:  "projection": scanned 403 of 403 pages, containing 25309 live rows
> and 0 dead rows; 25309 rows in sample, 25309 estimated total rows
> ANALYZE
>
>
> Therefore the results of ANALYZE are snapshots in time and are considered to
> be estimates.
>
> EXPLAIN ANALYZE gives you an actual count:
>
> http://www.postgresql.org/docs/9.5/interactive/sql-explain.html
>
> production=# explain analyze select count(*) from projection where p_item_no
> < 100;
>   QUERY PLAN
> --
>  Aggregate  (cost=438.72..438.73 rows=1 width=0) (actual time=1.655..1.655
> rows=1 loops=1)
>->  Bitmap Heap Scan on projection  (cost=13.74..436.96 rows=703 width=0)
> (actual time=0.328..1.226 rows=679 loops=1)
>  Recheck Cond: (p_item_no < 100)
>  Heap Blocks: exact=120
>
>  ->  Bitmap Index Scan on pj_pno_idx  (cost=0.00..13.56 rows=703
> width=0) (actual time=0.271..0.271 rows=679 loops=1)
>Index Cond: (p_item_no < 100)
>
>  Planning time: 0.181 ms
>
>  Execution time: 1.749 ms
>
> (8 rows)
>
>
>
>
> production=# select count(*) from projection where p_item_no < 100;
>  count
>
> ---
>
>679
>
> (1 row)
>
>
> But, again that is a snapshot of a point in time:
>
>
> production=# begin;
> BEGIN
> production=# delete from projection where p_item_no < 25;
> DELETE 117
> production=# explain analyze select count(*) from projection where p_item_no
> < 100;
>   QUERY PLAN
> --
>  Aggregate  (cost=438.72..438.73 rows=1 width=0) (actual time=1.517..1.518
> rows=1 loops=1)
>->  Bitmap Heap Scan on projection  (cost=13.74..436.96 rows=703 width=0)
> (actual time=0.336..1.159 rows=562 loops=1)
>  Recheck Cond: (p_item_no < 100)
>  Heap Blocks: exact=120
>  ->  Bitmap Index Scan on pj_pno_idx  (cost=0.00..13.56 rows=703
> width=0) (actual time=0.271..0.271 rows=679 loops=1)
>Index Cond: (p_item_no < 100)
>  Planning time: 0.214 ms
>  Execution time: 1.610 ms
> (8 rows)
>
> production=# select count(*) from projection where p_item_no < 100;
>  count
> ---
>562
> (1 row)
>
> Note the difference in actual row count between the Bitmap Index Scan and
> the Bitmap Heap Scan, due to the above taking place in an open transaction
> where the 117 'deleted' rows are still in play until I either commit or
> rollback.
>
>
> Unless the table is getting absolutely no activity a row count is going to
> be tied to a point in time.
>
>>
>> thanks
>>
>>
>
>
> --
> 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] What does Rows Counted mean

[Rakesh Kumar]

pgAdmin shows this:

Name:   tableA
OID
Owner
Tablespace
Rows (estimated) : 10
Rows (Counted)   : not counted

What is Rows (Counted) and why it is showing not counted even though
the table has been analyzed.

thanks


-- 
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] PostgreSQL advocacy

[Rakesh Kumar]

On 03/21/2016 10:57 AM, Thomas Kellerer wrote:


So - at least as far as I can tell - it's usually only used where 
high-availability is really important, e.g. where zero-downtime is required.
If you can live with a short downtime, a hot standby is much cheaper and 
probably not that much slower.


Even the above statement can be challenged , given the rising popularity 
of nosql databases which are all based on

eventual consistency (aka async replication).

A PG with BDR and an application designed to read/write only
one node via connection mapping can match the high availability
requirement of RAC.

BTW disk is a single point of failure in RAC.


--
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] Confusing deadlock report

[Rakesh Kumar]

is there a possibility that there is no index on the FKY column
bravo.alpha_id.

On Wed, Mar 16, 2016 at 11:09 AM, Albe Laurenz 
wrote:

> Thomas Kellerer wrote:
> >> Can you determine what statements were executed in these transactions
> before the deadlock?
> >> It was probably one of these that took the conflicting lock.
> >
> > Unfortunately not. Statement logging is not enabled on that server
> (space-constrained).
> >
> > And while we know the statements that can possibly be executed by these
> parts of the application,
> > several on them depend on the actual data, so it's hard to tell which
> path the two transactions
> > actually used.
>
> But that's where the solution to your problem must be...
>
> Look at all statements that modify "alpha" and could be in the same
> transaction
> with the INSERT to "bravo".
>
> Yours,
> Laurenz Albe
>
> --
> 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] Question about shared_buffer cache behavior

[Rakesh Kumar]

PG loads data at the block level to shared_buffers. Most likely it is
because the second sql selects different set of rows (from different
blocks) than the first sql.

On Fri, Mar 18, 2016 at 4:24 PM, Paul Jones  wrote:
> In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from
> a single table that uses an index appears to read the table into the
> shared_buffer cache.  Then, as many times as the exact same SELECT is
> repeated in the same session, it runs blazingly fast and doesn't even
> touch the disk.  All good.
>
> Now, in the *same* session, if a different SELECT from the *same* table,
> using the *same* index is run, it appears to read the entire table from
> disk again.
>
> Why is this?  Is there something about the query that qualifies the
> contents of the share_buffer cache?  Would this act differently for
> different kinds of indexes?
>
> PJ
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Multiple databases and shared_buffers

[Rakesh Kumar]

aha ok it is clear now.  

The splitting of buffers for each db is not for the reasons you stated below, 
but for better management of RAM. 
In our current RDBMS we allocate BPs for each database based on its usage/size. 
 With that being said, in case
of PG. having no control on BP is not a big deal.

Also, just curious , is it possible to shut down only one database in an 
instance,
in case a rogue session connected to it is causing havoc. I know there are 
other ways
of achieving it (like killing all sessions and revoking grant priv), but if 
there is an easier
way to knock out a db temporarily, it will be great.


-Original Message-
From: Melvin Davidson 
To: Rakesh Kumar ; pgsql-general 

Sent: Thu, Feb 18, 2016 4:12 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffers



>What do you mean that the data is actually not stored in the shared buffer.  
>From the link you provided :
>"The “shared_buffers” configuration parameter determines how much memory 
>is dedicated to PostgreSQL to use for caching data."


Again, you misunderstand. Cached data (and queries) is for sharing only to the 
same database.

So if user A in Database abc does a "SELECT some_column FROM table1 WHERE col2 
= 4" Then the results/data from that query are available to any other user (who 
has permission) in database abc and does the same 

exact query. However, users from database xyz CANNOT see data and/or results 
from database abc unless they specifically connect to it. Further to the point, 
Beginning with 9.4, PostgreSQL also makes better use of O/S memory for 
shared_buffers. But the bottom line is, you do not need to split shared_buffers 
up among different databases. PostgreSQL just uses it to make queries more 
efficient. There is no security problem because users in one database cannot 
request buffer information about another.


-- 

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

Re: [GENERAL] Multiple databases and shared_buffers

[Rakesh Kumar]

NOTE: It is very important you provide the version of PostgreSQL and O/S when 
addressing this mail list. 

Since it is a new project, we are going with:

PG: 9.5.1


OS: RHEL 6.4

Re: [GENERAL] Multiple databases and shared_buffers

[Rakesh Kumar]

What do you mean that the data is actually not stored in the shared buffer.  
>From the link you provided :
"The “shared_buffers” configuration parameter determines how much memory 
is dedicated to PostgreSQL to use for caching data."

This seems to be the same like other RDBMS. Typically the terminology used is 
Buffer Pool. 
BP is used to cache the changes done to a page and the page cleaning
process periodically flushes the dirty pages to disk.
Checkpoints typically gurantees that at a given time a page in cache is same as 
on the disk and is usually
used as the start time for roll forward recovery.


Does PG follow a different architecture ?




-Original Message-
From: Melvin Davidson 





NOTE: It is very important you provide the version of PostgreSQL and O/S when 
addressing this mail list. That is so others searching the archives in the 
future

can determine it's pertenance.


That being said, It looks to me like you have a misunderstanding about how 
PostgreSQL uses shared_buffers. Data is not actually stored in the 
shared_buffers.
Please refer to the following url which explains it's usage.

http://leopard.in.ua/2013/09/05/postgresql-sessting-shared-memory




-- 

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

Re: [GENERAL] Multiple databases and shared_buffers

[Rakesh Kumar]

It is a business requirement that we store our clients data in separate 
databases. Our sales folks insist it is non negotiable. Our current technology 
does
support that and also maintain buffer pools for each db independently. That's 
why I brought this up. Personally I don't think this is a serious limitation at 
all.







-Original Message-
From: Melvin Davidson 
To: pgsql-general 
Sent: Wed, Feb 17, 2016 9:53 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffers





I think this begs the question "Why do you think you need to separate the 
shared_buffers"? 

What version of PostgreSQL are you using?

What is your O/S?

How many CPU's on your server?

How much memory?



On Wed, Feb 17, 2016 at 5:21 PM, John R Pierce  wrote:

  

On 2/17/2016 6:54 AM, Data Cruncher  wrote:


We will becreating multiple databases in a cluster (instance). Is there 
   any way to separate shared_buffers for each database? Looks like
not since PG does not allow user created shared buffers.


  


you would need to run multiple instances if you feel you need thatlevel 
of control over shared_buffers.



-- 
john r pierce, recycling bits in santa cruz
  




-- 

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