Thread: array surprising behavior

Hi,

I think this is most surprising behavior -- shouldn't the UPDATE raise
an error?

alvherre=# create table foo (a int[]);
CREATE TABLE
alvherre=# insert into foo values (null);
INSERT 33649 1
alvherre=# update foo set a[3] = '42';
UPDATE 1
alvherre=# select a, a is null from foo;a | ?column?
---+----------  | t
(1 fila)
alvherre=# select version();                                                 version
-----------------------------------------------------------------------------------------------------------PostgreSQL
7.5develon i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (Mandrake Linux 9.2 3.3.1-2mdk)
 
(1 fila)


(This is CVS tip as of a couple weeks ago)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

Alvaro Herrera wrote:
> I think this is most surprising behavior -- shouldn't the UPDATE raise
> an error?

Surprising, but not new (probably has been there back to the Berkley 
code), and has come up before on one of the lists (I think it might even 
have been pgsql-bugs).

regression=# select version();    version
------------------------------------------------------------------------ PostgreSQL 7.3.5 on i686-pc-linux-gnu,
compiledby GCC gcc (GCC) 3.2.2 
 
20030222 (Red Hat Linux 3.2.2-5)
(1 row)

regression=# create table foo (a int[]);
CREATE TABLE
regression=# insert into foo values (null);
INSERT 1104092 1
regression=# update foo set a[3] = '42';
UPDATE 1
regression=# select a, a is null from foo; a | ?column?
---+----------   | t
(1 row)

I'm still hoping to scrounge up the time to continue working on arrays 
for 7.5, including figuring out how to deal with this.

Joe

We have customers who prefer to use their backup facilities
instead of what we provide in the app (we use pg_dump)
I hear speed is at least one consideration.

The questions I need to answer are these:

1) Is this absolutely safe to do file copy (cpio, or 
smth. else, whatever the robust backup app. would use)
on the Postgres db, when it's completely shut down.

2) Same question, but the database is up and running in
read-only mode. We're making sure that no updates are 
taking place.

If it matters - this is on Solaris, HP, Linux.
We're using v.7.3.2 running on UDS and v.7.3.4 on TCP/IP
We provide no explicit settings for wal, fsync and the like.
And (yes, I know) they often install it on NFS.

Thank you much.
Mike.

On Wed, 4 Feb 2004, Michael Brusser wrote:

> We have customers who prefer to use their backup facilities
> instead of what we provide in the app (we use pg_dump)
> I hear speed is at least one consideration.
> 
> The questions I need to answer are these:
> 
> 1) Is this absolutely safe to do file copy (cpio, or 
> smth. else, whatever the robust backup app. would use)
> on the Postgres db, when it's completely shut down.

Yes, it is.

> 2) Same question, but the database is up and running in
> read-only mode. We're making sure that no updates are 
> taking place.

Most likely, it is.  No guarantees if the database is up and running, even 
if you're certain there are no updates happening.

Also, you can use a snapshotting file system to make a backup image and 
then back up the image, while the database is up and being accessed, both 
by readers and writers, assuming you get REAL snapshots.

> If it matters - this is on Solaris, HP, Linux.
> We're using v.7.3.2 running on UDS and v.7.3.4 on TCP/IP
> We provide no explicit settings for wal, fsync and the like.
> And (yes, I know) they often install it on NFS.

OK, here's a couple more issues to chew on as well.  Suppose you have a 
backup, and the database server was compiled with UNKNOWN switches.  The 
machine burns to the ground.  now you get to try and figure out how to 
compile the database on the new server so it can read the old dataset.  
This may or may not be a complete friggin' nightmare for you.  Dumps can 
move between versions / hardware configs / differently compiled versions 
of postgresql with some amount of reliability.  binary copies, may or may 
not move so easily.

Scenario II, the HP burns to the ground, and your boss just buys a big old 
intel box.  how do you get your data up and running with a binary backup?  
you don't.

Scenario III.  Subtle corruption gets into your dataset due to a bad block 
or what not.  No one notices for a while.  Suddenly, someone notices.  
With only file system backups, with no error messages in them, how do you 
determine when the corruption occurred and get the uncorrupt data out 
leaving the corrupted behind?

Plain and simple.  Postgresql is designed to be backed up by pg_dump.  
Using anything else "isn't supported" so to speak, and may cause you 
untold grief in the future.  That said, sometimes file backups are the 
perfect solution, just go into with your eyes open to the possible 
problems, and I'd make a pg_dump every so often just in case.

On Wed, Feb 04, 2004 at 11:06:29AM -0800, Joe Conway wrote:
> Alvaro Herrera wrote:
> >I think this is most surprising behavior -- shouldn't the UPDATE raise
> >an error?
> 
> Surprising, but not new (probably has been there back to the Berkley 
> code), and has come up before on one of the lists (I think it might even 
> have been pgsql-bugs).

Too bad :-(

> I'm still hoping to scrounge up the time to continue working on arrays 
> for 7.5, including figuring out how to deal with this.

I have the same hope, though I know for sure that I won't have any time
to work on anything until March, and then I will probably devote most of
my time to Pg.  When is beta freeze supposed to happen?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> When is beta freeze supposed to happen?

AFAIK, no date has been set at all.  I doubt we'll even think about it
until we see how the Windows port effort goes.
        regards, tom lane