Thread: Functions

On 05/08/2015 01:56 AM, Ramesh T wrote:
> Hi All,
>    I want move functions from onedatabase to other database in same host
> on windows 7 and installed  postgres version is 9.4.I'm using pgadmin3 tool.
> any help..?
>

Two options with pgAdmin:

1) See here
http://www.pgadmin.org/docs/1.20/backup.html
http://www.pgadmin.org/docs/1.20/restore.html
    See the Objects pane for selective restore

2) Go through the tree of objects in the object browser and cut and
paste the function scripts.

Best option is to create object(tables, functions, etc) definition
scripts outside your database in text files so you can point them at any
database you want. Ideally they will be in a version control system.

--
Adrian Klaver
adrian.klaver@aklaver.com

Yuri Budilov wrote:
> My employer is evaluating PostgreSQL as a possible replacement for Oracle 11g R2 and
> MS-SQL 2008 R2 for some systems.
> I am completely new to PostgreSQL but experienced in MS-SQL and also in Oracle 11g R2.
> We need to establish what PostgreSQL is good at and not so good at - so we can decide where to use it.
> The OS will be 64-bit Linux (probably Red Hat Linux or Oracle Linux).
>
> Are there any documents comparing these products in terms of features?
> That would save me asking a lot of questions and save me weeks or months of reading of PostgreSQL manuals.

Some general remarks, since Maxim already answered your specific questions:

I am not aware if a document that compares the features, but I would like to
caution in this respect: if you come from another system, you are likely to
start searching for features that are similar to what you know from there, and
end up being disappointed if you cannot find them.
It is often not helpful to look for one-on-one feature comparison, as the same
problems are often solved in quite different ways on different systems.

One example: coming from Oracle, you might be appalled by PostgreSQL's
lack of synonyms. However, if you think the PostgreSQL way, you would view
synonyms as a band-aid for Oracle's lack of a schema search path.

Another familiar eyebrow-raiser is PostgreSQL's lack of stored procedures -
but it seems like that hasn't been a problem for practical application, a
function returning "void" usually does the trick.

Since you come from proprietary databases, I would divide the pros and cons
in two categories:

a) Features of open source software in general:
- You are empowered to analyze and fix your problems yourself, or pay
  people of your choosing to do it.
- Development, design choices and decision processes happen "in the open"
  and can be followed and influenced by everybody.
- You have to do more work to integrate the software with other parts of
  your landscape (backup software, high availability, client software, ...).

b) PostgreSQL features:
- Excellent documentation.
- Excellent extensibility (functions in several programming languages,
  packaged extensions (PGXN), user-defined types, ...).
- High code quality.
- Not owned by a single company. Hence, cannot be bought, and there is
  little danger for half-baked solutions to be shipped because of customer
  or marketing pressure.
- Unusually strict about correct encoding and implicit type casting.
- Excellent support on the mailing lists (you can talk to the developers, and
  bugs are often fixed in a matter of days).

Since you mentioned the documentation, I'd encourage you to spend the time
to read through the manual.  You will find it worth reading, particularly if
you plan to use PostgreSQL at your company.

Yours,
Laurenz Albe

Yuri, Maxim,

A few clarifications-

* Maxim Boguk (maxim.boguk@gmail.com) wrote:
> > In the meantime, I have scanned the manual for PostgreSQL 9.4 and there are
> > a few things I was not able to find in the manual, my apologies if I missed
> > it:
> >
> >  1. does PostgreSQL have parallel query capability like MS-SQL 2008+ and
> > Oracle 11g+ ? Or does a single query only run on 1 CPU?
> >
> The latest stable version will execute a single query on single CPU. Next
> version (9.5) will have some limited parallel query execution facilities.

Right, we're working on it, but what's coming in 9.5 won't be anywhere
close to the parallel query you get with the commercial RDBMS's.

That said, PostgreSQL is *quite* good at parallel *operation*, including
things like having multiple processes which are reading from the same
table all follow each other and avoid adding i/o and without any locking
issues.  I've built very large OLAP systems on PostgreSQL with only a
pretty minimal external utility that managed the multiple connections to
PostgreSQL to get parallel processing across partitioned tables.

One system I built did all of that with nearly arbitrary ad-hoc SQL
written by analysts and just shell scripts and psql. :)

> > 2. does PostgreSQL have ability to apply query optimizer hints to
> > individual queries - such as use a particular index, join type, join order,
> > plan guides, etc ?
> >
> No it's not available. In most (but not all) cases PostgreSQL query
> optimizer will select reasonable good plan.

It's not a goal of PG to provide hints.  If the optimizer isn't doing
its job then come talk to us and we'll do our best to fix it.

> > 3. does PostgreSQL have Column-Store capability?
> >
> In community version - no, but there are some external addons available
> which add column storage (however a bit limited).

Right, there's an extension for PostgreSQL which adds columnar
capability.  It's also possible to get quite close to columnar if you
use arrays in core PG- I was able to squeeze ~1800 4-byte integers into
a PG 8k page using that technique.  Columnar might get you a bit more,
but 1800 out of the 2048 max ain't bad for a relational DB.

> > 4. does PostgreSQL have anything resembling Microsoft SQL Server Profiler
> > Trace or Extended Events ?
> >
> No, but statistical views in 9.2+ provides pretty good overview about
> what's going on the database (especially useful could be
> pg_stat_statements:
> http://www.postgresql.org/docs/9.4/interactive/pgstatstatements.html )

pg_stat_statements is pretty awesome..  Definitely worth looking at for
this requirement.  There is also pgBadger, which is a fantastic tool for
looking at what queries are taking the most time.

> > 5. does PostgreSQL have a database backup capability such that not a
> > single transaction is lost in case of hardware failure? Some of our target
> > databases are several TeraBytes in size with several hundred concurrent
> > connections and transactions are financial in their nature. So we need
> > extremely robust backup/restore capability, 100% on-line.
> >
> Yep PostgreSQL could use built-in synchronous replication with zero
> committed transaction lost after failover.

You don't necessairly need to go to sync rep to get that- if you have a
properly redundany storage subsystem which won't lie about fsync, ever,
then you can simply do direct failover from the write-ahead-log to a new
system in case of failure of the primary.  sync rep adds another level
of latency as PG does both the fsync and waits for the remote server to
acknowledge the write.

> > 6. does PostgreSQL support NUMA on Intel based X64 servers and does it
> > support Hyper-Threading ?
> >
> No NUMA support. Yes PostgreSQL will work on HT enabled servers (will it
> be efficient - depend on workload and CPU type).

PostgreSQL doesn't need to directly support NUMA- the Linux kernel does.
As for if we do anything special when running in a NUMA environment, no,
not at this time.  Ditto with hyper-threading.

> 7. does PostgreSQL support in-memory store (similar to Oracle 12c in-memory
> > and SQL Server 2014 in-memory OLTP) ?
> >
> No.

Temporary tables will be in memory unless they overflow work_mem and we
do support unlogged tables and tablespaces which you could stick out on
a ramdisk if you want.

> 8. does PostgreSQL have temporary tables support?
> >
> Yes full temporary tables support since beginning.

Yup.

    Thanks!

        Stephen

On 05/09/2015 06:33 AM, Stephen Frost wrote:
> Temporary tables will be in memory unless they overflow work_mem and
> we do support unlogged tables and tablespaces which you could stick
> out on a ramdisk if you want.
I would suggest not putting a table space on a ramdisk. According to the
docs this risks corrupting the entire cluster.

http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html

Jack

Stephen Frost <sfrost@snowman.net> wrote:
> Maxim Boguk (maxim.boguk@gmail.com) wrote:

>>> 6. does PostgreSQL support NUMA on Intel based X64 servers and
>>> does it support Hyper-Threading ?
>>
>> No NUMA support.  Yes PostgreSQL will work on HT enabled servers
>> (will it be efficient - depend on workload and CPU type).
>
> PostgreSQL doesn't need to directly support NUMA- the Linux
> kernel does.  As for if we do anything special when running in a
> NUMA environment, no, not at this time.  Ditto with
> hyper-threading.

Since each connection creates its own backend process, each
connection's local data in a NUMA environment tends to be allocated
from a "nearby" memory segment.  So NUMA for a connection's private
memory is very efficiently handled by default.  Where an issue
could possibly arise is in the shared memory, used for the page
buffers and interprocess communication (such as locks).  If
allocations for that become unbalanced, the CPU controlling a
heavily used memory segment could become a bottleneck.

I did some investigation into NUMA issues with PostgreSQL after
seeing some NUMA performance issues on a machine with four memory
segments and a database which fit in about 1/4 of the machine's RAM
*and* the user pre-warmed the cache using a single process.  I
found that by programming PostgreSQL to use interleaved mode for
shared memory *and* using an OS cpuset to interleave OS buffers and
cache I was able to get a consistent 2% to 3% performance increase
and prevent occasional (rare) "spikes" of bad performance.  Almost
all of that was gained just by using the OS cpuset feature (which
does not require any change to PostgreSQL to do), so we didn't make
any change to PostgreSQL -- a sysadmin can manage interleaved
buffer allocation pretty easily if they need to.

If you were able to find a situation where NUMA issues within
PostgreSQL caused even a 1% hit, we could always revisit the issue.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Melvin Davidson (melvin6925@gmail.com) wrote:
> In addition to the other great comments and advice that have been posted,
> you might want to review the "Database Compatibility Technology for Oracle"
> document from EnterpriseDB.
>
> http://www.enterprisedb.com/solutions/oracle-compatibility-technology

That's certainly something to consider, but it's PPAS, not PostgreSQL,
just to be clear.

    Thanks!

        Stephen

* Jack Christensen (jack@jackchristensen.com) wrote:
> On 05/09/2015 06:33 AM, Stephen Frost wrote:
> >Temporary tables will be in memory unless they overflow work_mem
> >and we do support unlogged tables and tablespaces which you could
> >stick out on a ramdisk if you want.
> I would suggest not putting a table space on a ramdisk. According to
> the docs this risks corrupting the entire cluster.
>
> http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html

That warning is about reliability of the cluster, not corruption,
technically speaking.  Still, you're right, that comment of mine was a
bit too nonchalant about it and it's an area that we do need to improve
the support of by being able to deal with the realities of a ramdisk,
perhaps by having a flag associated with the tablespace and then
accepting that files may not exist at startup (or when we go to access
them).

All that said, and I won't say that I've tested it as much as I would if
it was being done in a production environment, but I've used a ramdisk
under a tablespace by copying what's there after creation of the
tablespace and tables (which should be unlogged) that you want there and
then restoring that on reboot.  Might be worth a blog post about, to see
if anyone else is doing that or if people have run into issues with it.
It was a while ago and I don't recall anything else being needed, but
it's possible I've forgotten. :)

    Thanks!

        Stephen

MANY THANKS to everyone who replied !
Keep up great work!

more things (critical for very large and mission critical databases)

- database row/page compression -

it looks to me that there is no page/block compression available on PostgreSQL 9.4 along the lines of MS-SQL/Oracle
row/pagecompression features? 
I realize that there is some compression of individual varchar/text data type columns but there is nothing like a
completerow compression, index page compression and page/dictionary compression? Is that correct? 

database and transaction log backup compression? not available?

- recovery from hardware or software corruption -

suppose I am running a mission critical database (which is also relatively large, say > 1TB) and I encounter a
corruptionof some sort (say, due to hardware or software bug) on individual database pages or a number of pages in a
database

How do I recover quickly and without losing any transactions? MS-SQL and Oracle can restore individual pages (or sets
ofpages) or restore individual database files and then allow me to roll forward transaction log to bring back every
lasttransaction. It can be done on-line or off-line. How do I achieve the same in PostgreSQL 9.4? One solution I see
maybe via complete synchronous replication of the database to another server. I am but sure what happens to the corrupt
page(s)- does it get transmitted corrupt to the mirror server so I end up with same corruption on both databases or is
theresome protection against this?  

many thanks again
Yuri



________________________________________
From: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> on behalf of Stephen Frost
<sfrost@snowman.net>
Sent: Sunday, 10 May 2015 1:05 AM
To: Jack Christensen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

* Jack Christensen (jack@jackchristensen.com) wrote:
> On 05/09/2015 06:33 AM, Stephen Frost wrote:
> >Temporary tables will be in memory unless they overflow work_mem
> >and we do support unlogged tables and tablespaces which you could
> >stick out on a ramdisk if you want.
> I would suggest not putting a table space on a ramdisk. According to
> the docs this risks corrupting the entire cluster.
>
> http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html

That warning is about reliability of the cluster, not corruption,
technically speaking.  Still, you're right, that comment of mine was a
bit too nonchalant about it and it's an area that we do need to improve
the support of by being able to deal with the realities of a ramdisk,
perhaps by having a flag associated with the tablespace and then
accepting that files may not exist at startup (or when we go to access
them).

All that said, and I won't say that I've tested it as much as I would if
it was being done in a production environment, but I've used a ramdisk
under a tablespace by copying what's there after creation of the
tablespace and tables (which should be unlogged) that you want there and
then restoring that on reboot.  Might be worth a blog post about, to see
if anyone else is doing that or if people have run into issues with it.
It was a while ago and I don't recall anything else being needed, but
it's possible I've forgotten. :)

        Thanks!

                Stephen

Maxim Boguk wrote:
>> database and transaction log backup compression? not available?

> Transaction log backup compression not available (however could be easily archived via external utilities like
bzip2).

Well, in PostgreSQL you backup transaction logs by setting "archive_command",
which is a operating system command you write.
You just put a "gzip -1" in there and your WAL archive will be compressed.

>> - recovery from hardware or software corruption -
>>
>> suppose I am running a mission critical database (which is also relatively large, say > 1TB)
>> and I encounter a corruption of some sort (say, due to hardware or software bug)
>> on individual database pages or a number of pages in a database
>>
>> How do I recover quickly and without losing any transactions? MS-SQL and Oracle can restore
>> individual pages (or sets of pages) or restore individual database files and then allow me
>> to roll forward transaction log to bring back every last transaction. It can
>> be done on-line or off-line. How do I achieve the same in PostgreSQL 9.4?
>> One solution I see may be via complete synchronous replication of the database to another server.
>> I am but sure what happens to the corrupt page(s) - does it get transmitted corrupt
>> to the mirror server so I end up with same corruption on both databases or is there some protection against this?

​> It's depend where a corruption happen, if pages become corrupted due to some
> problems with physical storage (filesystem) in that case a replica data should be ok.

I would not count on that.
I have had a case where a table file got corrupted due to hardware problems.
Pages that contained data were suddenly zeroed.
PostgreSQL recognizes such a block as empty, so the user got no error, but
data were suddenly missing. What does a user do in such a case? He/she grumbles
and enters the data again. This insert will be replicated to the standby (which was
fine up to then) and will cause data corruption there (duplicate primary keys).

PostgreSQL replicates the physical block, so data corruption that does not
trigger an error will be replicated.
You should enable checksums to minimize that risk.

If bad comes to worse, you'll just have to recover, although I'd say that in most cases
a standby database will help you survive a hardware failure.

Yours,
Laurenz Albe

* Maxim Boguk (maxim.boguk@gmail.com) wrote:
> On Sun, May 10, 2015 at 12:30 PM, Yuri Budilov <yuri.budilov@hotmail.com>
> wrote:
> > database and transaction log backup compression? not available?
>
> Transaction log backup compression not available (however could be easily
> archived via external utilities like bzip2).
> Both built-in backup utilities (pg_dump and pg_basebackup) support
> compression.

External utilities can provide backup compression (eg: pgBackRest, and I
believe Barman either has it or is also getting it).

In 9.5, we now support compression of full page impages in WAL too.

> > - recovery from hardware or software corruption -
> >
> > suppose I am running a mission critical database (which is also relatively
> > large, say > 1TB) and I encounter a corruption of some sort (say, due to
> > hardware or software bug) on individual database pages or a number of pages
> > in a database
> >
> > How do I recover quickly and without losing any transactions? MS-SQL and
> > Oracle can restore individual pages (or sets of pages) or restore
> > individual database files and then allow me to roll forward transaction log
> > to bring back every last transaction. It can be done on-line or off-line.
> > How do I achieve the same in PostgreSQL 9.4? One solution I see may be via
> > complete synchronous replication of the database to another server. I am
> > but sure what happens to the corrupt page(s) - does it get transmitted
> > corrupt to the mirror server so I end up with same corruption on both
> > databases or is there some protection against this?
> >
>
> It's depend where a corruption happen, if pages become corrupted due to
> some problems with physical storage (filesystem) in that case a replica
> data should be ok.

Correct, it largely depends on the corruption.  PostgreSQL 9.4 does have
page-level checksums to help identify any corruption that happened
outside of PG.

> There are no facility to recover individual database files and/or page
> ranges from base backup and roll forward the transaction log (not even
> offline).

PostgreSQL certainly supports point-in-time-recovery, which you could do
off-line and then grab whatever data was lost, but not individual file
or table at this point.  Combined with ZFS snapshots and other
technologies, you can make it happen quite quickly though.

> >From my practice using a PostgreSQL for the terabyte scale and/or
> mission-critical databases definitely possible but require very careful
> design and planning (and good hardware).

I'd argue that's true for any database of this type. :)

    Thanks!

        Stephen

On Sat, May 9, 2015 at 11:20 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Maxim Boguk wrote:
>> It's depend where a corruption happen, if pages become corrupted due to some
>> problems with physical storage (filesystem) in that case a replica data should be ok.
>
> I would not count on that.
> I have had a case where a table file got corrupted due to hardware problems.
> Pages that contained data were suddenly zeroed.
> PostgreSQL recognizes such a block as empty, so the user got no error, but
> data were suddenly missing. What does a user do in such a case? He/she grumbles
> and enters the data again. This insert will be replicated to the standby (which was
> fine up to then) and will cause data corruption there (duplicate primary keys).

You had zero corrupted pages turned on. PostgreSQL by default does NOT
DO THIS. That setting is for recovering a corrupted database not for
everyday use!

Scott Marlowe wrote:
> On Sat, May 9, 2015 at 11:20 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>> Maxim Boguk wrote:
>>> It's depend where a corruption happen, if pages become corrupted due to some
>>> problems with physical storage (filesystem) in that case a replica data should be ok.

>> I would not count on that.
>> I have had a case where a table file got corrupted due to hardware problems.
>> Pages that contained data were suddenly zeroed.
>> PostgreSQL recognizes such a block as empty, so the user got no error, but
>> data were suddenly missing. What does a user do in such a case? He/she grumbles
>> and enters the data again. This insert will be replicated to the standby (which was
>> fine up to then) and will cause data corruption there (duplicate primary keys).

> You had zero corrupted pages turned on. PostgreSQL by default does NOT
> DO THIS. That setting is for recovering a corrupted database not for
> everyday use!

No, I didn't.

It was not PostgreSQL that zeroed the page, but the hardware or operating system.
The problem was a flaky fibre channel cable that intermittently was connected and disconnected.
That corrupted the file system, and I guess it must have been file system recovery
that zeroed the pages.  I'm not 100% certain, at any rate the symptoms were silently missing data.

Yours,
Laurenz Albe

On Sun, May 10, 2015 at 7:50 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Scott Marlowe wrote:
>> On Sat, May 9, 2015 at 11:20 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>>> Maxim Boguk wrote:
>>>> It's depend where a corruption happen, if pages become corrupted due to some
>>>> problems with physical storage (filesystem) in that case a replica data should be ok.
>
>>> I would not count on that.
>>> I have had a case where a table file got corrupted due to hardware problems.
>>> Pages that contained data were suddenly zeroed.
>>> PostgreSQL recognizes such a block as empty, so the user got no error, but
>>> data were suddenly missing. What does a user do in such a case? He/she grumbles
>>> and enters the data again. This insert will be replicated to the standby (which was
>>> fine up to then) and will cause data corruption there (duplicate primary keys).
>
>> You had zero corrupted pages turned on. PostgreSQL by default does NOT
>> DO THIS. That setting is for recovering a corrupted database not for
>> everyday use!
>
> No, I didn't.
>
> It was not PostgreSQL that zeroed the page, but the hardware or operating system.
> The problem was a flaky fibre channel cable that intermittently was connected and disconnected.
> That corrupted the file system, and I guess it must have been file system recovery
> that zeroed the pages.  I'm not 100% certain, at any rate the symptoms were silently missing data.

Ahh OK. So broken hardware. I've seen some RAID controlelrs do that.
Sorry but your post didn't make it clear where the zeroing came from.