This whole MySQL versus PGSQL thing

OK, so I thought I'd do some more checking.

MySQL is known for just swallowing what you give it and going on without
raising an exception. numeric is also one of them

SQL script in postgresql:

create table testa (i1 numeric (4,2), i2 numeric(4,2));
insert into testa values (100.23,99.34);
ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 4
scale 2

in mysql:

create table testa (i1 numeric (4,2), i2 numeric(4,2));
insert into testa values (100.23,99.34);
Query OK, 1 row affected (0.00 sec)
insert into testa values (10000.23,99.34);
Query OK, 1 row affected (0.00 sec)
insert into testa values (100000.23,99.34);
Query OK, 1 row affected (0.00 sec)

select * from testa;
+--------+-------+
| i1 | i2 |
+--------+-------+
| 100.23 | 99.34 |
| 999.99 | 99.34 |
| 999.99 | 99.34 |
| 999.99 | 99.34 |
+--------+-------+
4 rows in set (0.00 sec)

Hope you weren't basing any accounting systems on this database.
:-)

Hi there:

I am just wondering how to remove the files of the database I just drop. I
found there are whole bunch folders and files with number as the name. Once
I drop the database, how do I know which files I should delete manually?
Will the database engine delete that for me?

Thanks!

"Terence Chang" <TChang(at)nqueue(dot)com> writes:
> I am just wondering how to remove the files of the database I just drop. I
> found there are whole bunch folders and files with number as the name. Once
> I drop the database, how do I know which files I should delete manually?

Simple: none.

regards, tom lane

Great! That is easier than Oracle! Now! I have no excuse not to use
PostgreSQL. :-)

I got one more question! Is there a way that I can shut down only one
database for "Cold Backup" while other database are running like Oracle
does?

Thanks!

Terence Chang wrote:
> I got one more question! Is there a way that I can shut down only one
> database for "Cold Backup" while other database are running like Oracle
> does?

No, but then again, there is no need. pg_dump will give you a consistent
snapshot of the database as it was when you started the command. It is
essentially a "cold" backup that is taken while hot!

Joe

On Wed, 16 Jul 2003, Terence Chang wrote:

> Great! That is easier than Oracle! Now! I have no excuse not to use
> PostgreSQL. :-)
>
> I got one more question! Is there a way that I can shut down only one
> database for "Cold Backup" while other database are running like Oracle
> does?

There's really no need to shut down and do a cold backup. pg_backup backs
up databases hot as if they were cold (i.e. it uses a snapshot system).
So, if you connect and start pg_dump, and 20 users initiate transactions
after that, you see none of those later transactions or any part of them
in your backup.

IC.. However, my application is critical. So is it possible to backup those
20 user's transactions from log? Oracle and MS SQL can recover to the
failure point. Does PostgreSQL support that also? I mean recover from a
transaction log and where is the transaction log?

Thanks!

----- Original Message -----
From: "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: "Terence Chang" <TChang(at)nqueue(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, July 17, 2003 7:35 AM
Subject: Re: [GENERAL] Where is the physical files of database that I just

> On Wed, 16 Jul 2003, Terence Chang wrote:
>
> > Great! That is easier than Oracle! Now! I have no excuse not to use
> > PostgreSQL. :-)
> >
> > I got one more question! Is there a way that I can shut down only one
> > database for "Cold Backup" while other database are running like Oracle
> > does?
>
> There's really no need to shut down and do a cold backup. pg_backup backs
> up databases hot as if they were cold (i.e. it uses a snapshot system).
> So, if you connect and start pg_dump, and 20 users initiate transactions
> after that, you see none of those later transactions or any part of them
> in your backup.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

"Terence Chang" <TChang(at)nqueue(dot)com> writes:

> IC.. However, my application is critical. So is it possible to backup those
> 20 user's transactions from log? Oracle and MS SQL can recover to the
> failure point. Does PostgreSQL support that also? I mean recover from a
> transaction log and where is the transaction log?

Not yet. A lot of people have asked for that. The feature is being
actively worked on but will not be in the upcoming 7.4 release.
There's a good chance it will be in 7.5 (or whatever the release after
that is called).

-Doug