pg_database_size(oid)

Dhimant Patel <drp4kri@gmail.com> writes:

I have created a database, which I plan to load with several tables. I am
trying to find the size of the databases and came
across pg_database_size(oid) function. Since it requires databse oid, I
thought there must be a view where you get it - then came across
"pg_database" table and still all in vain. The "pg_database" table lists all
databases in the server but won't provide me the oid
for pg_database_size().

What you lack is the knowledge that oid is a system column in these
tables, meaning it isn't displayed by "SELECT * FROM ...". It's there
though and you can select it explicitly:
select oid from pg_database where datname = 'foo';

http://www.postgresql.org/docs/8.4/static/ddl-system-columns.html

regards, tom lane

On Wed, 2010-02-17 at 11:59 -0500, Dhimant Patel wrote:

I have created a database, which I plan to load with several tables. I
am trying to find the size of the databases and came
across pg_database_size(oid) function. Since it requires databse oid,
I thought there must be a view where you get it - then came across
"pg_database" table and still all in vain. The "pg_database" table
lists all databases in the server but won't provide me the oid
for pg_database_size().

I noticed this being true for almost all reference views. For example,
pg_tablespace lists the tablespaces but no tablespace oid.
Function pg_tablespace_databases (tablespace_oid) ,
needs tablespace_oid, but where I could get it from?

Use the db name.

test=# select pg_size_pretty(pg_database_size('test'));
pg_size_pretty
----------------
5392 kB
(1 row)

test=#

Dhimant Patel wrote:

I have created a database, which I plan to load with several tables. I
am trying to find the size of the databases and came
across pg_database_size(oid) function.

Here's what you probably want:

select datname,pg_size_pretty(pg_database_size(pg_database.oid)) from
pg_database order by pg_database_size(pg_database.oid) desc;

The OID in these are sort of hidden column, there's a list of them all
at http://www.postgresql.org/docs/current/static/ddl-system-columns.html

Most tables will not have an OID nowadays, but many of the system
catalog ones still do. ctid is another handy one to know about--useful
for removing duplicate records and some other fancy tricks.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us

Hi,
I was reading about oid and default configuration of PostgreSQL. A couple
of doubts
1) Why is use of OIDS considered deprecated? Is there something else that
can be used in place of oids for user tables?
2) Is there a performance impact if we keep the default default_with_oids
to ON?
Googling, I came across this -
http://philmcrew.com/oid.html
But most of the links given at that page were broken and the page itself
did not provide a lot of information.
Thanks,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

On Wednesday 17 February 2010 8:13:51 pm Jayadevan M wrote:

Hi,
I was reading about oid and default configuration of PostgreSQL. A couple
of doubts
1) Why is use of OIDS considered deprecated? Is there something else that
can be used in place of oids for user tables?

Sequences:
http://www.postgresql.org/docs/8.4/interactive/sql-createsequence.html

2) Is there a performance impact if we keep the default default_with_oids
to ON?
Googling, I came across this -
http://philmcrew.com/oid.html
But most of the links given at that page were broken and the page itself
did not provide a lot of information.

The primary question that needs to be asked is what do you want to do with them?
It is not so much a performance issue as an admin issue. OIDs where created for
Postgres internal system use and leaked out to user space. As a result they
have some shortcomings as detailed in the above article. Given that sequences
are available as number generators, it was decided to encourage/force OIDs to
be for internal system use only. That decision is set and using OIDs on user
tables is setting yourself for future problems.

Thanks,
Jayadevan

--
Adrian Klaver
adrian.klaver@gmail.com

Hi,

The primary question that needs to be asked is what do you want to do

with them?

It is not so much a performance issue as an admin issue. OIDs where

created for

Postgres internal system use and leaked out to user space. As a result

they

have some shortcomings as detailed in the above article. Given that

sequences

are available as number generators, it was decided to encourage/force

OIDs to

be for internal system use only. That decision is set and using OIDs on

user

tables is setting yourself for future problems.

I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like
rowid in Oracle. In Oracle, access by rowid is expected to be the fastest
way of accessing a record, faster than even an index access followed by
table access using the primary key. That was why I have this doubt about
usage of oid being deprecated. Even if we use a sequence as PK (which is
there in Oracle too), it is not as fast as access by rowid (I don't know
if this applies to PostgreSQL's oid too). This is important when we use a
cursors in an Oracle procedure (function in PostgreSQL) and loop through
it and update specific records, when some conditions are met. Of course,
that approach has its drawbacks -as in the case when row movement is
enabled some maintenance activity moves the row to another location.
Another scenario is when we want to delete duplicate records in a table.

Thanks for your reply,
Regards,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

On Thu, Feb 18, 2010 at 8:46 PM, Jayadevan M
<Jayadevan.Maymala@ibsplc.com> wrote:

Hi,

The primary question that needs to be asked is what do you want to do with them?
It is not so much a performance issue as an admin issue. OIDs where created for
Postgres internal system use and leaked out to user space. As a result they
have some shortcomings as detailed in the above article. Given that sequences
are available as number generators, it was decided to encourage/force OIDs to
be for internal system use only. That decision is set and using OIDs on user
tables is setting yourself for future problems.

I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like rowid in Oracle. In Oracle, access by rowid is expected to be the fastest way of accessing a record, faster than even an index access followed by table access using the primary key. That was why I have this doubt about usage of oid being deprecated. Even if we use a sequence as PK (which is there in Oracle too), it is not as fast as access by rowid (I don't know if this applies to PostgreSQL's oid too). This is important when we use a cursors in an Oracle procedure (function in PostgreSQL) and loop through it and update specific records, when some conditions are met. Of course, that approach has its drawbacks -as in the case when row movement is enabled some maintenance activity moves the row to another location. Another scenario is when we want to delete duplicate records in a table.

Oracle and postgres are definitely different here. There's really no
equivalent to rowid in pgsql. oid has no special optimizations. An
indexed PK of a serial is about as good as it gets, possibly
clustered.

Jayadevan M wrote:

Hi,

The primary question that needs to be asked is what do you want to

do with them?

It is not so much a performance issue as an admin issue. OIDs where

created for

Postgres internal system use and leaked out to user space. As a

result they

have some shortcomings as detailed in the above article. Given that

sequences

are available as number generators, it was decided to

encourage/force OIDs to

be for internal system use only. That decision is set and using OIDs

on user

tables is setting yourself for future problems.

I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like
rowid in Oracle. In Oracle, access by rowid is expected to be the
fastest way of accessing a record, faster than even an index access
followed by table access using the primary key. That was why I have
this doubt about usage of oid being deprecated. Even if we use a
sequence as PK (which is there in Oracle too), it is not as fast as
access by rowid (I don't know if this applies to PostgreSQL's oid
too). This is important when we use a cursors in an Oracle procedure
(function in PostgreSQL) and loop through it and update specific
records, when some conditions are met. Of course, that approach has
its drawbacks -as in the case when row movement is enabled some
maintenance activity moves the row to another location. Another
scenario is when we want to delete duplicate records in a table.

well, postgres' OID's were never a direct row address of any sort. as
the previous poster said, OID's were an internal identifier, and were
never really meant for general use but their use was tolerated in
earlier versions of postgres when there were things you couldn't do
without them. Even in Oracle, I don't believe rowid bypasses
indexes, its more like an implicit SERIAL PRIMARY KEY field.

Hi,

Even in Oracle, I don't believe rowid bypasses
indexes, its more like an implicit SERIAL PRIMARY KEY field.

Well, I understand the point is not very relevant, since oid is not
similar to rowid. In Oracle, index scans are bypassed if we use rowid.

1)Access by unique index

SQL> select * from myt where id=200;
Execution Plan
----------------------------------------------------------
Plan hash value: 1325982734

--------------------------------------------------------------------------------

----

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time
|

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT | | 1 | 65 | 1 (0)|
00:00:

01 |

| 1 | TABLE ACCESS BY INDEX ROWID| MYT | 1 | 65 | 1 (0)|
00:00:

01 |

|* 2 | INDEX UNIQUE SCAN | MYDX | 1 | | 1 (0)|
00:00:

01 |

--------------------------------------------------------------------------------

2) Access by rowid
SQL> select * from myt where rowid='AAAH9iAAEAAAAafADH';

ID
----------
NAME
--------------------------------------------------------------------------------

200
REFCON$

Execution Plan
----------------------------------------------------------
Plan hash value: 4204525950

--------------------------------------------------------------------------------

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time
|

--------------------------------------------------------------------------------

---

| 0 | SELECT STATEMENT | | 1 | 77 | 1 (0)|
00:00:0

1 |

| 1 | TABLE ACCESS BY USER ROWID| MYT | 1 | 77 | 1 (0)|
00:00:0

Regards,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

Jayadevan M escribi�:

I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like
rowid in Oracle. In Oracle, access by rowid is expected to be the fastest
way of accessing a record, faster than even an index access followed by
table access using the primary key. That was why I have this doubt about
usage of oid being deprecated. Even if we use a sequence as PK (which is
there in Oracle too), it is not as fast as access by rowid (I don't know
if this applies to PostgreSQL's oid too). This is important when we use a
cursors in an Oracle procedure (function in PostgreSQL) and loop through
it and update specific records, when some conditions are met. Of course,
that approach has its drawbacks -as in the case when row movement is
enabled some maintenance activity moves the row to another location.

I suppose you could use a cursor and then

UPDATE ... WHERE CURRENT OF <the cursor>

Another scenario is when we want to delete duplicate records in a table.

You can use the ctid system column for this. This column represents the
physical position of the row in the table, so it changes in many
situations, for example during an UPDATE.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Feb 18, 2010 at 11:08 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Oracle and postgres are definitely different here.  There's really no
equivalent to rowid in pgsql.  oid has no special optimizations.  An
indexed PK of a serial is about as good as it gets, possibly
clustered.

access by CTID is the fastest it gets. I use it to do mass updates
after selecting a large number of rows. I can guarantee nobody else
is modifying those rows so i know it is safe.