Thread: Vacuum with Views
Hello, I have a system converted from DB2 to PostGreSQL, running on eCS. The system has about 36 tables and maybe 10 views. I am writing a PGS backup procedure which includes a vacuumdb call. Question: Does the vacuum procedure mess up my views? It seems as if it does. Thank you. John
On Wed, Aug 03, 2005 at 12:26:32PM -0400, John J. Urbaniak wrote: > Does the vacuum procedure mess up my views? It seems as if it does. Could elaborate on how vacuum "seems to mess up" your views? What are you doing, what are you expecting to happen, and what actually does happen? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote:
The print module worked, then all of a sudden stopped working. It caused a crash in my system which required me to reconnect to PGS, and maybe also to shut down the server and restart it.
The only thing I remember doing to this was use a vacuum procedure in a backup utility I am writing.
When I rebuilt the views (with a program), everything is back to normal and the Work Orders print ok.
I am using Object Rexx to interface eCS with PGS.
I am trying to trace down the culprit.
Can I be sure that if I delete some records from some tables, then vacuum the database, that views involving those tables are preserved?
John
I am not sure. Somehow my views were destroyed. I have a Work Order table, and a Resources table (many Resources to one Work Order). These tables are joined by the Work Order number into a View, which I call ActiveWOs. I have a print module that prints from this view.On Wed, Aug 03, 2005 at 12:26:32PM -0400, John J. Urbaniak wrote:Does the vacuum procedure mess up my views? It seems as if it does.Could elaborate on how vacuum "seems to mess up" your views? What are you doing, what are you expecting to happen, and what actually does happen?
The print module worked, then all of a sudden stopped working. It caused a crash in my system which required me to reconnect to PGS, and maybe also to shut down the server and restart it.
The only thing I remember doing to this was use a vacuum procedure in a backup utility I am writing.
When I rebuilt the views (with a program), everything is back to normal and the Work Orders print ok.
I am using Object Rexx to interface eCS with PGS.
I am trying to trace down the culprit.
Can I be sure that if I delete some records from some tables, then vacuum the database, that views involving those tables are preserved?
John
can i use pgsql to force th einput of leading zeros? i have an application that enters serial numbers within a range, eg, 00001 to 00003. when my user (that's me!) enters 00001 the begin box and then enters 00003 in the end box, the following gets entered in the pgsql... 00001 2 3 i'm using php to increment the serial number until it gets to a value greater than the "end" value. after it increments, it loses the zeros. i guess my options are... 1. have pgsql apply the leading zeros if they aren't already there (if this is possible). 2. have php add the leading zeros prior to insertion. 3. enter the data with leading zeros stripped and then use php to display the data with leading zeros. does anyone have any insight into any of these issues? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Wednesday 03 August 2005 15:23, operationsengineer1@yahoo.com wrote: > can i use pgsql to force th einput of leading zeros? > > i have an application that enters serial numbers > within a range, eg, 00001 to 00003. Use a string type, but only allow certain characters. Gord
--- Gordon Haverland <ghaverla@shaw.ca> wrote: > On Wednesday 03 August 2005 15:23, > operationsengineer1@yahoo.com > wrote: > > can i use pgsql to force th einput of leading > zeros? > > > > i have an application that enters serial numbers > > within a range, eg, 00001 to 00003. > > Use a string type, but only allow certain > characters. > > Gord Gord, if i do that, though, i think i'm unable to increment the number string in php. the user inputs the beginning and ending s/n only. i use php to create and enter the other s/ns. can i still increment a number string using i++? __________________________________ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html
On Wed, Aug 03, 2005 at 08:57:20PM -0700, operationsengineer1@yahoo.com wrote:
> --- Gordon Haverland <ghaverla@shaw.ca> wrote:
>
> > On Wednesday 03 August 2005 15:23, operationsengineer1@yahoo.com wrote:
> > > can i use pgsql to force th einput of leading zeros?
> > > i have an application that enters serial numbers within a range,
> > > eg, 00001 to 00003.
> > Use a string type, but only allow certain characters.
> Gord,
>
> if i do that, though, i think i'm unable to increment
> the number string in php. the user inputs the
> beginning and ending s/n only. i use php to create
> and enter the other s/ns. can i still increment a
> number string using i++?
You probably could do it as a string in php, but I think I'd use it as a
decimal in php, and then use a printf() statement when you do an insert.
Like this example (where mycol is of some string type):
<?php
$num = 12;
$sql = "INSERT INTO table (mycol), VALUES('" .
printf("%06d",$num) . "')";
?>
You can try,
select to_char(generate_series(1,5),'00000');
to_char
---------
00001
00002
00003
00004
00005
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
select to_char(generate_series(1,5),'00000');
to_char
---------
00001
00002
00003
00004
00005
On 8/4/05, operationsengineer1@yahoo.com <operationsengineer1@yahoo.com > wrote:
--- Gordon Haverland <ghaverla@shaw.ca > wrote:
> On Wednesday 03 August 2005 15:23,
> operationsengineer1@yahoo.com
> wrote:
> > can i use pgsql to force th einput of leading
> zeros?
> >
> > i have an application that enters serial numbers
> > within a range, eg, 00001 to 00003.
>
> Use a string type, but only allow certain
> characters.
>
> Gord
Gord,
if i do that, though, i think i'm unable to increment
the number string in php. the user inputs the
beginning and ending s/n only. i use php to create
and enter the other s/ns. can i still increment a
number string using i++?
__________________________________
Yahoo! Mail
Stay connected, organized, and protected. Take the tour:
http://tour.mail.yahoo.com/mailtour.html
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
On Wed, Aug 03, 2005 at 04:09:06PM -0400, John J. Urbaniak wrote: > Michael Fuhr wrote: > >Could elaborate on how vacuum "seems to mess up" your views? What > >are you doing, what are you expecting to happen, and what actually > >does happen? > > > I am not sure. Somehow my views were destroyed. I have a Work Order > table, and a Resources table (many Resources to one Work Order). These > tables are joined by the Work Order number into a View, which I call > ActiveWOs. I have a print module that prints from this view. By "destroyed" do you mean the views disappeared, as though they had been dropped? If so, do you have statement logging enabled so you can see if a DROP VIEW command was executed? > The print module worked, then all of a sudden stopped working. It > caused a crash in my system which required me to reconnect to PGS, and > maybe also to shut down the server and restart it. Did the operating system crash, or just a particular application? When you refer to "server" do you mean the entire box, or just a particular server process? In any case, if the absence of a database view has such adverse effects, then perhaps some part of the system could use better error checking. > The only thing I remember doing to this was use a vacuum procedure in a > backup utility I am writing. Can you reproduce the problem by doing whatever you think caused it the first time? > Can I be sure that if I delete some records from some tables, then > vacuum the database, that views involving those tables are preserved? Vacuuming isn't supposed to have damaging effects like dropping views, and it certainly doesn't in ordinary operation or we'd hear more reports about it. Are you vacuuming the entire cluster regularly? Have you had any operating system or hardware problems that might have damaged some disk pages? Are there any unusual errors in your PostgreSQL logs? What version of PostgreSQL are you using, and on what platform? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote:
No, I did not have logging enabled. On my OS (eCS) redirecting the log messages to go to a file doesn't work and I don't want to see all those messages popping up in stderr, so I turned off most logging. But the only place I have a DROP VIEW command is in the module that builds the views.
By "server" I meant the PGS server, not the OS.
I re-ran my backup procedure, which vacuums the database. It did not reproduce the problem, so it must have been something else.
This is what I wanted to know; I will look elsewhere for what caused the original problem.
John
On Wed, Aug 03, 2005 at 04:09:06PM -0400, John J. Urbaniak wrote:Michael Fuhr wrote:Could elaborate on how vacuum "seems to mess up" your views? What are you doing, what are you expecting to happen, and what actually does happen?I am not sure. Somehow my views were destroyed. I have a Work Order table, and a Resources table (many Resources to one Work Order). These tables are joined by the Work Order number into a View, which I call ActiveWOs. I have a print module that prints from this view.By "destroyed" do you mean the views disappeared, as though they had been dropped? If so, do you have statement logging enabled so you can see if a DROP VIEW command was executed?
No, I did not have logging enabled. On my OS (eCS) redirecting the log messages to go to a file doesn't work and I don't want to see all those messages popping up in stderr, so I turned off most logging. But the only place I have a DROP VIEW command is in the module that builds the views.
The print module worked, then all of a sudden stopped working. It caused a crash in my system which required me to reconnect to PGS, and maybe also to shut down the server and restart it.Did the operating system crash, or just a particular application? When you refer to "server" do you mean the entire box, or just a particular server process?
By "server" I meant the PGS server, not the OS.
The only thing I remember doing to this was use a vacuum procedure in a backup utility I am writing.Can you reproduce the problem by doing whatever you think caused it the first time?
I re-ran my backup procedure, which vacuums the database. It did not reproduce the problem, so it must have been something else.
Can I be sure that if I delete some records from some tables, then vacuum the database, that views involving those tables are preserved?Vacuuming isn't supposed to have damaging effects like dropping views, and it certainly doesn't in ordinary operation or we'd hear more reports about it.
This is what I wanted to know; I will look elsewhere for what caused the original problem.
What version of PostgreSQL are you using, and on what platform?Are you vacuuming the entire cluster regularly? Have you had any operating system or hardware problems that might have damaged some disk pages? Are there any unusual errors in your PostgreSQL logs?
Version 8.0, on eComStation 1.1, with (Object) Rexx as the programming language.
John