Error on Vacuum?

Hi all,

In C, and many other programming languages, statements like the following are popular:

int status = (do_first_thing() && do_second_thing() && do_third_thing() && do_fourth_thing());

With this kind of expression, the program calls the function 'do_first_thing'. If, and only if, that returns non-zero, 'do_second_thing' will be executed. Again, if and only if that returns non-zero, 'do_third_thing' is executed. Etc.

In other words, later statements will only be executed if all before them have 'gone well'. When a statement 'fails', no further expressions are executed.. The variable 'status' is non-zero if, and only if, all four things were successfully executed.

For convenience, I'd really like to be able to achieve similar behaviour within an SQL statement, i.e. present multiple statements (such as INSERT statements) and only execute the later ones if the earlier ones have been executed without error. And I'd like to be able to present all the statements within a single, compound SQL statement to the database.

Is such a thing possible, using any fancy SQL syntactic tricks?

Nathaniel

On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk> wrote:
> Hi all,
>
> In C, and many other programming languages, statements like the following are popular:
>
> int status = (do_first_thing() && do_second_thing() && do_third_thing() && do_fourth_thing());
>
> With this kind of expression, the program calls the function 'do_first_thing'. If, and only if, that returns non-zero, 'do_second_thing' will be executed. Again, if and only if that returns non-zero, 'do_third_thing' is executed. Etc.
>
> In other words, later statements will only be executed if all before them have 'gone well'. When a statement 'fails', no further expressions are executed.. The variable 'status' is non-zero if, and only if, all four things were successfully executed.
>
> For convenience, I'd really like to be able to achieve similar behaviour within an SQL statement, i.e. present multiple statements (such as INSERT statements) and only execute the later ones if the earlier ones have been executed without error. And I'd like to be able to present all the statements within a single, compound SQL statement to the database.
>
> Is such a thing possible, using any fancy SQL syntactic tricks?

No tricks necessary. What you are describing is called a transaction.

CREATE TABLE testing (
id integer,
name text unique
);

BEGIN;
INSERT INTO testing(id,name) values (1,'Bob');
INSERT INTO testing(id,name) values (2,'Joe');
INSERT INTO testing(id,name) values (3,'Sally');
COMMIT;

BEGIN;
INSERT INTO testing(id,name) values (4,'Ann');
-- the next statement will cause an error
-- due to violation of the unique constraint
INSERT INTO testing(id,name) values (5,'Bob');
-- We do a rollback, which will put the database
-- back into the state it was in just before the
-- second BEGIN statement
ROLLBACK;

SELECT * FROM TESTING;

See the documentation and Google about transactions.

Sean

On 18/12/09, Nathaniel Trellice (naptrel(at)yahoo(dot)co(dot)uk) wrote:
> In other words, later statements will only be executed if all before
> them have 'gone well'. When a statement 'fails', no further
> expressions are executed.. The variable 'status' is non-zero if, and
> only if, all four things were successfully executed.

You could do something along the following lines:

CREATE OR REPLACE FUNCTION
fn_test ( integer) RETURNS INTEGER
AS $$
DECLARE
input ALIAS for $1;
status INTEGER := 0;
returner INTEGER := 0;
BEGIN
PERFORM fn_test1 (input);
IF NOT FOUND THEN
RETURN returner;
ELSE
SELECT INTO status * FROM fn_test2 (input);
IF status != 1 THEN
RAISE NOTICE 'status from fn_test2 not expected %' % status
RETURN returner;
ELSE
...etc...
END IF;
returner = 1
RETURN returner;
END IF;
END;$$
LANGUAGE plpgsql;

-
Rory Campbell-Lange
Director
rory(at)campbell-lange(dot)net

Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928

Sean, I am not sure if
what Nathaniel needs is
really a transaction.

The concept of transaction IIRC has just two possible outcomes, Either
everything is executed or nothing is executed.

But it seems that he needs do_first_thing() and do_second_thing() to be
executed if, e.g. do_third_thing() fails. do_forth_thing() should not be
executed, in this scenario, but the first two actions do.

If we bracket these actions in a transaction nothing would be executed if
any of the actions fail, but I guess Nataniel needs the previous actions to
be executed (and not the next).

Nataniel, am I correctly undestanding the background of your question?

I 'm not realizing if this can be done in pure SQL, but it should be easy to
be done in pqplsql or other procedural language

Best,
Oliveiros

----- Original Message -----
From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
To: "Nathaniel Trellice" <naptrel(at)yahoo(dot)co(dot)uk>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Friday, December 18, 2009 1:16 PM
Subject: Re: [NOVICE] Conditionally executing multiple statements in series
as single SQL statement

On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>
wrote:
> Hi all,
>
> In C, and many other programming languages, statements like the following
> are popular:
>
> int status = (do_first_thing() && do_second_thing() && do_third_thing() &&
> do_fourth_thing());
>
> With this kind of expression, the program calls the function
> 'do_first_thing'. If, and only if, that returns non-zero,
> 'do_second_thing' will be executed. Again, if and only if that returns
> non-zero, 'do_third_thing' is executed. Etc.
>
> In other words, later statements will only be executed if all before them
> have 'gone well'. When a statement 'fails', no further expressions are
> executed.. The variable 'status' is non-zero if, and only if, all four
> things were successfully executed.
>
> For convenience, I'd really like to be able to achieve similar behaviour
> within an SQL statement, i.e. present multiple statements (such as INSERT
> statements) and only execute the later ones if the earlier ones have been
> executed without error. And I'd like to be able to present all the
> statements within a single, compound SQL statement to the database.
>
> Is such a thing possible, using any fancy SQL syntactic tricks?

No tricks necessary. What you are describing is called a transaction.

CREATE TABLE testing (
id integer,
name text unique
);

BEGIN;
INSERT INTO testing(id,name) values (1,'Bob');
INSERT INTO testing(id,name) values (2,'Joe');
INSERT INTO testing(id,name) values (3,'Sally');
COMMIT;

BEGIN;
INSERT INTO testing(id,name) values (4,'Ann');
-- the next statement will cause an error
-- due to violation of the unique constraint
INSERT INTO testing(id,name) values (5,'Bob');
-- We do a rollback, which will put the database
-- back into the state it was in just before the
-- second BEGIN statement
ROLLBACK;

SELECT * FROM TESTING;

See the documentation and Google about transactions.

Sean

On Fri, Dec 18, 2009 at 8:45 AM, Oliveiros C,
<oliveiros(dot)cristina(at)marktest(dot)pt> wrote:
> Sean, I am not sure if
> what Nathaniel needs is
> really a transaction.
>
> The concept of transaction IIRC has just two possible outcomes, Either
> everything is executed or nothing is executed.
>
> But it seems that he needs do_first_thing() and do_second_thing() to be
> executed if, e.g. do_third_thing() fails. do_forth_thing() should not be
> executed, in this scenario, but the first two actions do.
>
> If we bracket these actions in a transaction nothing would be executed if
> any of the actions fail, but I guess Nataniel needs the previous actions to
> be executed (and not the next).
>
> Nataniel, am I correctly undestanding the background of your question?
>
> I 'm not realizing if this can be done in pure SQL, but it should be easy to
> be done in pqplsql or other procedural language

Good point--my bad. SAVEPOINTS might still be helpful, though, if
used in conjunction with the transaction.

Sean

> ----- Original Message -----
> From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
> To: "Nathaniel Trellice" <naptrel(at)yahoo(dot)co(dot)uk>
> Cc: <pgsql-novice(at)postgresql(dot)org>
> Sent: Friday, December 18, 2009 1:16 PM
> Subject: Re: [NOVICE] Conditionally executing multiple statements in series
> as single SQL statement
>
>
> On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>
> wrote:
>> Hi all,
>>
>> In C, and many other programming languages, statements like the following
>> are popular:
>>
>> int status = (do_first_thing() && do_second_thing() && do_third_thing() &&
>> do_fourth_thing());
>>
>> With this kind of expression, the program calls the function
>> 'do_first_thing'. If, and only if, that returns non-zero,
>> 'do_second_thing' will be executed. Again, if and only if that returns
>> non-zero, 'do_third_thing' is executed. Etc.
>>
>> In other words, later statements will only be executed if all before them
>> have 'gone well'. When a statement 'fails', no further expressions are
>> executed.. The variable 'status' is non-zero if, and only if, all four
>> things were successfully executed.
>>
>> For convenience, I'd really like to be able to achieve similar behaviour
>> within an SQL statement, i.e. present multiple statements (such as INSERT
>> statements) and only execute the later ones if the earlier ones have been
>> executed without error. And I'd like to be able to present all the
>> statements within a single, compound SQL statement to the database.
>>
>> Is such a thing possible, using any fancy SQL syntactic tricks?
>
> No tricks necessary.  What you are describing is called a transaction.
>
> CREATE TABLE testing (
>  id integer,
>  name text unique
> );
>
> BEGIN;
> INSERT INTO testing(id,name) values (1,'Bob');
> INSERT INTO testing(id,name) values (2,'Joe');
> INSERT INTO testing(id,name) values (3,'Sally');
> COMMIT;
>
> BEGIN;
> INSERT INTO testing(id,name) values (4,'Ann');
> -- the next statement will cause an error
> -- due to violation of the unique constraint
> INSERT INTO testing(id,name) values (5,'Bob');
> -- We do a rollback, which will put the database
> -- back into the state it was in just before the
> -- second BEGIN statement
> ROLLBACK;
>
> SELECT * FROM TESTING;
>
> See the documentation and Google about transactions.
>
> Sean
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>

Nathaniel, PostgreSQL does support Perl which is full of tricks. I
dislike such tricks because they make the code much harder to read. For
that reason even in Perl I sometimes write

if (!defined($a)) then { $a="value"; }

instead just

$a ||= "value";

The latter well known Perl idiom is much shorter and much easier to type
but much harder to read, especially for people not proficient in Perl.
The same applies to the "&&" trick which will execute the second part
only if the first part has executed correctly.

On Fri, 2009-12-18 at 13:01 +0000, Nathaniel Trellice wrote:

> Hi all,
>
> In C, and many other programming languages, statements like the following are popular:
>
> int status = (do_first_thing() && do_second_thing() && do_third_thing() && do_fourth_thing());
>
> With this kind of expression, the program calls the function 'do_first_thing'. If, and only if, that returns non-zero, 'do_second_thing' will be executed. Again, if and only if that returns non-zero, 'do_third_thing' is executed. Etc.
>
> In other words, later statements will only be executed if all before them have 'gone well'. When a statement 'fails', no further expressions are executed.. The variable 'status' is non-zero if, and only if, all four things were successfully executed.
>
> For convenience, I'd really like to be able to achieve similar behaviour within an SQL statement, i.e. present multiple statements (such as INSERT statements) and only execute the later ones if the earlier ones have been executed without error. And I'd like to be able to present all the statements within a single, compound SQL statement to the database.
>
> Is such a thing possible, using any fancy SQL syntactic tricks?
>
>
> Nathaniel
>
>
>
>
>
>

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

The Leader in Integrated Media
Intelligence Solutions

Help!

I am running PostGreSQL 8.0 on eComStation (nee OS/2)

I can't start the database (name = oz).

I try to vacuum and get this message:

Vacuuming of database "oz" failed: ERROR: catalog is missing 3
attributes for relid 45564.

Can anybody tell me what this message means and what, if anything I can
do to fix it?

Thanks,

John

>
>
>
>

"John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
> I try to vacuum and get this message:

> Vacuuming of database "oz" failed: ERROR: catalog is missing 3
> attributes for relid 45564.

> Can anybody tell me what this message means and what, if anything I can
> do to fix it?

It means that some of the pg_attribute rows for the table with oid 45564
seem to be missing. (Try "select relname from pg_class where oid = 45564"
to find out which table that is.)

If you're really lucky, this is just index corruption and "reindex table
pg_attribute" will fix it. I forget whether 8.0 requires you to do
anything special to reindex pg_attribute --- you might need to do it in
a standalone backend. Read the REINDEX man page.

If that doesn't fix it, you're pretty much out of luck as far as
recovering that table goes, but you might be able to just drop it
and perhaps the rest of the database will be okay. (Or perhaps not
... no way to tell from this whether the corruption in pg_attribute
hurt anything else.) I don't think DROP TABLE will work, but you
could delete the pg_class row for it and then pg_dump should work.

I'd recommend dump, initdb, reload to make sure there's not any
hidden corruption lingering. This would also be a great opportunity
to update to something less obsolete than PG 8.0. We don't support
or recommend anything before 8.2 on Windows-ish systems, and even
then you want 8.2.something-pretty-recent.

regards, tom lane

Tom Lane wrote:

Thanks for the advice. But I can't even connect to the database, nor
start the server on it. I guess the data is just lost.

I don't know if there is an eCS (or OS/2) version of PGS beyond 8.0

I'll look into it.

Thanks,

John

> "John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
>
>> I try to vacuum and get this message:
>>
>
>
>> Vacuuming of database "oz" failed: ERROR: catalog is missing 3
>> attributes for relid 45564.
>>
>
>
>> Can anybody tell me what this message means and what, if anything I can
>> do to fix it?
>>
>
> It means that some of the pg_attribute rows for the table with oid 45564
> seem to be missing. (Try "select relname from pg_class where oid = 45564"
> to find out which table that is.)
>
> If you're really lucky, this is just index corruption and "reindex table
> pg_attribute" will fix it. I forget whether 8.0 requires you to do
> anything special to reindex pg_attribute --- you might need to do it in
> a standalone backend. Read the REINDEX man page.
>
> If that doesn't fix it, you're pretty much out of luck as far as
> recovering that table goes, but you might be able to just drop it
> and perhaps the rest of the database will be okay. (Or perhaps not
> ... no way to tell from this whether the corruption in pg_attribute
> hurt anything else.) I don't think DROP TABLE will work, but you
> could delete the pg_class row for it and then pg_dump should work.
>
> I'd recommend dump, initdb, reload to make sure there's not any
> hidden corruption lingering. This would also be a great opportunity
> to update to something less obsolete than PG 8.0. We don't support
> or recommend anything before 8.2 on Windows-ish systems, and even
> then you want 8.2.something-pretty-recent.
>
> regards, tom lane
>
>

"John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
> Thanks for the advice. But I can't even connect to the database, nor
> start the server on it. I guess the data is just lost.

Hm? Then how did you get as far as having vacuumdb produce that error?

regards, tom lane

Tom Lane wrote:
> "John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
>
>> Thanks for the advice. But I can't even connect to the database, nor
>> start the server on it. I guess the data is just lost.
>>
>
> Hm? Then how did you get as far as having vacuumdb produce that error?
>
> regards, tom lane
>
>
Sorry. I can start the server, but I can't connect to the database.

John

"John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
>> Hm? Then how did you get as far as having vacuumdb produce that error?

> Sorry. I can start the server, but I can't connect to the database.

That doesn't make any sense either, unless you have more corruption than
the previous message indicated. The error you showed was something that
would only occur on an attempt to access a user table.

Maybe you have something in a .psqlrc file that is doing something extra
when you try to connect?

regards, tom lane

Tom Lane wrote:
> "John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
>
>>> Hm? Then how did you get as far as having vacuumdb produce that error?
>>>
>
>
>> Sorry. I can start the server, but I can't connect to the database.
>>
>
> That doesn't make any sense either, unless you have more corruption than
> the previous message indicated. The error you showed was something that
> would only occur on an attempt to access a user table.
>
> Maybe you have something in a .psqlrc file that is doing something extra
> when you try to connect?
>
> regards, tom lane
>
>
I don't know. It's strange. I "start" the database with
E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"

I get no error message on the call.

But I notice that there is no postmaster.id file generated.

John

"John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
> I don't know. It's strange. I "start" the database with
> E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"

> I get no error message on the call.

> But I notice that there is no postmaster.id file generated.

What shows up in the logfile?

regards, tom lane

Tom Lane wrote:
> "John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
>
>> I don't know. It's strange. I "start" the database with
>> E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"
>>
>
>
>> I get no error message on the call.
>>
>
>
>> But I notice that there is no postmaster.id file generated.
>>
>
> What shows up in the logfile?
>
> regards, tom lane
>
>
I don't know where the logfile is.

John

On Thu, Jan 7, 2010 at 14:11, John J. Urbaniak <jjurban(at)attglobal(dot)net> wrote:

>> E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"
[...]
> I don't know where the logfile is.

The "-l logfile" tells PG to save the logfile as "logfile" in the
current directory where the command was issued. A good idea is to put
an absolute path to the logfile; "-l E:\postgres_logfile" or similiar.

--
- Rikard

"John J. Urbaniak" <jjurban(at)attglobal(dot)net> writes:
>>> I don't know. It's strange. I "start" the database with
>>> E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"
>>
>> What shows up in the logfile?

> I don't know where the logfile is.

You said "-l logfile". It ought to be named logfile, in the directory
you did this in.

regards, tom lane