[BUGS] BUG #1761: missing files
The following bug has been logged online: Bug reference: 1761 Logged by: Jack Email address: [EMAIL PROTECTED] PostgreSQL version: 8 Operating system: wxp Description:missing files Details: Wellcome On Compiling code with PSQLTable1 component a have error message : PostgreSQL erroc code : (1) syntax error at or on "FROM" at character 314 a thing that a dont have PSQLTables.pas file! Why??? I install PLSQLAC. Help me ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] pg_autovacuum: short, wide tables
mark reid wrote: I've been using pg_autovacuum for a while, and for the most part it's been great. There's one case in my system where it won't run on a particular type of table, even though the table apparently needs it. I have a table called "properties" that has key->value pairs. Usually there are only a handful of rows, some of which are updated relatively frequently compared to the number of rows (hundreds or thousands of times per day). The problem is that some of the rows have long strings for their value (on the order of a few hundred kilobytes), so if I understand correctly, the bulk of the data gets offloaded to a toast table. What I believe is happening is that the main table doesn't meet the minimum activity level for pg_autovacuum based on size / update frequency, but the toast table would, though it isn't specifically checked by pg_autovacuum. The result is that the toast table grows really big before triggering autovacuum (or until I manually vacuum the "properties" table). Not the end of the world, obviously, but might be a "gotcha" for some people with similar situations. I don't think the problem has to do with toast, or pg_autovacuum missing the fact that the toast table has been updated. Rather I think the problem is that autovacuum believes that all updates are created equal. That is 1 update is 1 update even though a single update may effect 1 page or thousands of pages. This is where FSM data needs to be integrated into pg_autovacuum. This isn't going to happen soon (not for 8.1) but it is definatley planned. With the inclusion of autovacuum into the backend for 8.1, you will at least be able to set per table thresholds and set a more aggressive threshold for this table. Matt ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1757: timestamp 'epoch' is not absolute
> > It appears that the timestamp 'epoch' is being interpreted as > > relative to the local timezone, rather than being an absolute time. > > This is correct for timestamp ... you want timestamp with time zone. Sorry, but that's dumb (IMHO). The unix epoch is not relative to the local timezone. I'm now using a function to convert from unix times to timestamps, since the alternative is ugly and verbose even by SQL standards... create function epoch(integer) returns timestamp with time zone as 'select timestamp with time zone \'epoch\' + interval \'1 second\' * $1;' language sql immutable; Is there a better way? Steve. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] postgresSQL data directory
Hi, We have a problems with postgresSQL 7.1.3 that suddenly all databases don't work, and it logged the following message, pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection Smart Shutdown request at Fri Jul 8 09:19:47 2005 DEBUG: shutting down DEBUG: database system is shut down I ran pg_ctl restart/start/stop, nothing works and the error log showed this message, simba[postgres][44]% more postmaster.log.994 /rms2/pgsql/bin/postmaster does not find the database system. Expected to find it in the PGDATA directory "/nsm/pgsql/data", but unable to open file "/nsm/pgsql/data/global/pg_control": No such file or directory I tried setenv PGDATA to /nsm/pgsql/data, but it still doesn't work. Then I moved the current data directory to data.bak and ran initdb --pgdata /nsm/pgsql/data to initialized the data cluster and replaced the data.bak with data, but no luck the postgres server still failed starting up. Since I don't have the dumpall up-to-date file, so I can't run a restore of the database. So, is there any ways to startup the postgres server to use my current data cluster? Thanks in advance, /Thanh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] pg_autovacuum: short, wide tables
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > mark reid wrote: >> What I believe is happening is that the main table doesn't meet the >> minimum activity level for pg_autovacuum based on size / update >> frequency, but the toast table would, though it isn't specifically >> checked by pg_autovacuum. > I don't think the problem has to do with toast, or pg_autovacuum missing > the fact that the toast table has been updated. Rather I think the > problem is that autovacuum believes that all updates are created equal. I think Mark is probably on to something. The activity in the toast table will show as deletes *in the toast table* ... and that activity fails to show at all in the pg_stat_activity view, because it shows only plain relations! So unless autovacuum is ignoring the stats views and going directly to the underlying stats functions, it cannot see at all that there is excessive activity in the toast table. It strikes me that this is a definitional bug in the stats views. Either we should change the filter to be "regular and toast tables", or we should add columns to show activity in a table's toast table, or we should just add the activity in the toast table to the parent table's activity columns. The first of these would be easiest but it seems quite likely to break applications (eg, if unmodified, autovacuum would probably try to issue vacuums against toast tables). And the last seems to be confusing. So I think I favor adding columns. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] postgresSQL data directory
Thanh Q Lam wrote: Hi, We have a problems with postgresSQL 7.1.3 that suddenly all databases don't work, and it logged the following message, I'm sure you know that's an old version, but I'll recommend upgrading anyway. pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection Smart Shutdown request at Fri Jul 8 09:19:47 2005 DEBUG: shutting down DEBUG: database system is shut down I ran pg_ctl restart/start/stop, nothing works and the error log showed this message, The same message every time? simba[postgres][44]% more postmaster.log.994 /rms2/pgsql/bin/postmaster does not find the database system. Expected to find it in the PGDATA directory "/nsm/pgsql/data", but unable to open file "/nsm/pgsql/data/global/pg_control": No such file or directory Is the file there? Are the permissions/ownership right? I tried setenv PGDATA to /nsm/pgsql/data, but it still doesn't work. Then I moved the current data directory to data.bak and ran initdb --pgdata /nsm/pgsql/data to initialized the data cluster and replaced the data.bak with data, but no luck the postgres server still failed starting up. Did the initdb work? Was the error message the same? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1761: missing files
Jack wrote: Bug reference: 1761 Wellcome On Compiling code with PSQLTable1 component a have error message : I'm not sure there is any such component bundled with PostgreSQL - did you get it from somwhere else? PostgreSQL erroc code : (1) syntax error at or on "FROM" at character 314 a thing that a dont have PSQLTables.pas file! That would be a Pascal source file? PostgreSQL isn't written in Pascal. Why??? Because you have a syntax error in your sql perhaps? Check the postgresql logs and see what is there. I install PLSQLAC. Do you mean PostgresqlDAC? http://microolap.com/products/connectivity/postgresdac/ As far as I can see, it looks like you have a problem in your application code rather than with PostgreSQL itself. If that's the case, then the pgsql-general list is probably a good starting point for your questions. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] postgresSQL data directory
Thanks Richard! I finally got it work. It was caused byethe PGDATA environment variable. I fixed it the .cshrc and reran initdd, then everything's back to normal. Yes, our current postgres is very old and I'm setting up 7.4.8 on another box. Thanks again, Thanh Richard Huxton wrote: Thanh Q Lam wrote: Hi, We have a problems with postgresSQL 7.1.3 that suddenly all databases don't work, and it logged the following message, I'm sure you know that's an old version, but I'll recommend upgrading anyway. pq_recvbuf: unexpected EOF on client connection pq_recvbuf: unexpected EOF on client connection Smart Shutdown request at Fri Jul 8 09:19:47 2005 DEBUG: shutting down DEBUG: database system is shut down I ran pg_ctl restart/start/stop, nothing works and the error log showed this message, The same message every time? simba[postgres][44]% more postmaster.log.994 /rms2/pgsql/bin/postmaster does not find the database system. Expected to find it in the PGDATA directory "/nsm/pgsql/data", but unable to open file "/nsm/pgsql/data/global/pg_control": No such file or directory Is the file there? Are the permissions/ownership right? I tried setenv PGDATA to /nsm/pgsql/data, but it still doesn't work. Then I moved the current data directory to data.bak and ran initdb --pgdata /nsm/pgsql/data to initialized the data cluster and replaced the data.bak with data, but no luck the postgres server still failed starting up. Did the initdb work? Was the error message the same? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_autovacuum: short, wide tables
Tom Lane wrote: I think Mark is probably on to something. The activity in the toast table will show as deletes *in the toast table* ... and that activity fails to show at all in the pg_stat_activity view, because it shows only plain relations! So unless autovacuum is ignoring the stats views and going directly to the underlying stats functions, it cannot see at all that there is excessive activity in the toast table. I think I'm missing something here. If I have a table t1 with a long text column, and I do an update on that text column, doesn't that show up as an update on table t1? And when there are enough upd/del autovacuum will issue a VACUUM against t1, which will clean up the associated toast table, right? So I think I must be missing something. Could you please explain the problem in a little more detail. It strikes me that this is a definitional bug in the stats views. Either we should change the filter to be "regular and toast tables", or we should add columns to show activity in a table's toast table, or we should just add the activity in the toast table to the parent table's activity columns. Ok, maybe I get it now, are you saying that if I do: update t1 set "long text column" = "some very long text value" and the update doesn't touch any non-toast columns that the stats system will not show that update against t1? If so, that is a problem. The first of these would be easiest but it seems quite likely to break applications (eg, if unmodified, autovacuum would probably try to issue vacuums against toast tables). And the last seems to be confusing. So I think I favor adding columns. Shouldn't the update to the toast table just be considered an update to table t1? The fact that there is an underlying toast table is an implementation detail that I don't think should show up in the stats system. Matt ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] pg_autovacuum: short, wide tables
> Tom Lane wrote: > >The first of these would be easiest but it seems quite likely to break > >applications (eg, if unmodified, autovacuum would probably try to issue > >vacuums against toast tables). And the last seems to be confusing. > >So I think I favor adding columns. Hmm. With integrated autovacuum, we could set something up to issue vacuums separately to TOAST tables and the main table. It'd probably be a tad easier if the toast stats are separate from the main table; and an autovac of the main table not necessarily would invoke vacuuming the toast table. I'm not proposing it for 8.1 though ... -- Alvaro Herrera () "Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_autovacuum: short, wide tables
Mark Reid <[EMAIL PROTECTED]> writes: > I think the issue is that a single update to the main table causes a > whole bunch of updates to the toast table. So in my case (with the > vacuum output attached previously), a thousand updates to the main table > entails tens of thousands of updates to the toast table. Exactly. If autovac were looking at the properties of the toast table it would think a vacuum pass was warranted sooner than it thinks from just looking at the main table. Admittedly this doesn't come into play unless you have a fairly large number of toast chunks per main-table row, so the rows in question have to be really wide (dozens of KB even after compression) before it gets to be a big deal. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_autovacuum: short, wide tables
"Matthew T. O'Connor" writes: > Shouldn't the update to the toast table just be considered an update to > table t1? The fact that there is an underlying toast table is an > implementation detail that I don't think should show up in the stats system. At the level of the stats system, though, you are interested in "implementation details". The fact that there is such a concept as an index is an implementation detail according to the SQL standard --- but if we hid that we wouldn't be able to show things that people want to know. In particular, I think people would like to be able to use the stats views to see how much toast-related I/O is going on, and not have that smushed together with main-table I/O. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] pg_autovacuum: short, wide tables
Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >>> The first of these would be easiest but it seems quite likely to break >>> applications (eg, if unmodified, autovacuum would probably try to issue >>> vacuums against toast tables). And the last seems to be confusing. >>> So I think I favor adding columns. > Hmm. With integrated autovacuum, we could set something up to issue > vacuums separately to TOAST tables and the main table. It'd probably be > a tad easier if the toast stats are separate from the main table; and an > autovac of the main table not necessarily would invoke vacuuming the > toast table. > I'm not proposing it for 8.1 though ... Well, why not? Arguably Mark's problem is a bug, and it's not too late to address bugs. I checked what actually happens if you try to vacuum a toast table: regression=# vacuum pg_toast.pg_toast_169901; WARNING: skipping "pg_toast_169901" --- cannot vacuum indexes, views, or special system tables VACUUM regression=# So it's not as bad as I thought. Maybe just weakening the filter in the stats tables views isn't a bad idea after all. Furthermore, we could allow VACUUM on a toast table to go through (is there any good reason to disallow it?) and then autovacuum would do more or less the right things with no further changes. I'm not sure about the idea of not vacuuming the toast table when we decide to vacuum the main table. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] pg_autovacuum: short, wide tables
Tom Lane wrote: "Matthew T. O'Connor" writes: Shouldn't the update to the toast table just be considered an update to table t1? The fact that there is an underlying toast table is an implementation detail that I don't think should show up in the stats system. At the level of the stats system, though, you are interested in "implementation details". The fact that there is such a concept as an index is an implementation detail according to the SQL standard --- but if we hid that we wouldn't be able to show things that people want to know. In particular, I think people would like to be able to use the stats views to see how much toast-related I/O is going on, and not have that smushed together with main-table I/O. Fair enough, but how are you planning to display the data, if the stat system just reports that there was an update to a corresponding toast table, that still isn't going to tell us how many pages that updated effected, and then we are back to the all updates are not created equal problem. Currently autovac doesn't look at the block level stats, maybe it should for this reason. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] pg_autovacuum: short, wide tables
"Matthew T. O'Connor" writes: > Tom Lane wrote: >> In particular, I think people would like to be able to use the stats >> views to see how much toast-related I/O is going on, and not have that >> smushed together with main-table I/O. > Fair enough, but how are you planning to display the data, At the moment I'm thinking "just like a regular table" --- see my last message to Alvaro. The existing backend code will count each toast-chunk insert or delete just like a normal row insert or delete, so AFAICS this will produce sane-looking stats that autovac could use the same way as for a plain table. The main bit of additional logic that might be needed is an awareness that firing a VACUUM on a main table will implicitly fire one on its toast table, and so you'd not want to go and issue the toast table VACUUM separately. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_autovacuum: short, wide tables
Alvaro Herrera <[EMAIL PROTECTED]> writes: Hmm. With integrated autovacuum, we could set something up to issue vacuums separately to TOAST tables and the main table. It'd probably be a tad easier if the toast stats are separate from the main table; and an autovac of the main table not necessarily would invoke vacuuming the toast table. I'm not proposing it for 8.1 though ... Well, why not? Arguably Mark's problem is a bug, and it's not too late to address bugs. I checked what actually happens if you try to vacuum a toast table: regression=# vacuum pg_toast.pg_toast_169901; WARNING: skipping "pg_toast_169901" --- cannot vacuum indexes, views, or special system tables VACUUM regression=# So it's not as bad as I thought. Maybe just weakening the filter in the stats tables views isn't a bad idea after all. Furthermore, we could allow VACUUM on a toast table to go through (is there any good reason to disallow it?) and then autovacuum would do more or less the right things with no further changes. That would be a nice improvement. Any increase in the granularity of vacuum can't be a bad thing. I'm not sure about the idea of not vacuuming the toast table when we decide to vacuum the main table. Perhaps by default issuing a vacuum against a table will also vacuum it's associated toast tables (can there be more than one toast table?) but add an option to the vacuum command not to vacuum the toast table. Or, perhaps this shouldn't be made totally public, ie. proving sql commands for it, perhaps it should only be available internally, that is to autovacuum and other tools that might issue a vacuum at the same low level as autovacuum. Matt ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] pg_autovacuum: short, wide tables
On Fri, Jul 08, 2005 at 01:29:03PM -0400, Tom Lane wrote: > "Matthew T. O'Connor" writes: > > Tom Lane wrote: > >> In particular, I think people would like to be able to use the stats > >> views to see how much toast-related I/O is going on, and not have that > >> smushed together with main-table I/O. > > > Fair enough, but how are you planning to display the data, > > At the moment I'm thinking "just like a regular table" --- see my > last message to Alvaro. The existing backend code will count each > toast-chunk insert or delete just like a normal row insert or delete, > so AFAICS this will produce sane-looking stats that autovac could > use the same way as for a plain table. > > The main bit of additional logic that might be needed is an awareness > that firing a VACUUM on a main table will implicitly fire one on its > toast table, and so you'd not want to go and issue the toast table > VACUUM separately. I don't see any reason why this wouldn't work. I even think it'd be very easy to implement (from the autovacuum POV -- not sure about the stat system). Furthermore, the awareness you mention is also very easy to implement: we just need to make sure the pg_autovacuum tuple for the toast table is updated when it is vacuumed, which is just an additional function call. Have you looked at the autovacuum integration patch? There are some ugly points on which I'd like your comments. For example, currently vacuum() takes a RangeVar parameter; it'd be cleaner if it used a Relid instead, and we provided a RangeVar wrapper. Also I'm not sure about the shutdown sequence, mainly the bit about delaying the bgwriter shut down until after autovac has already shut down. -- Alvaro Herrera () "La conclusión que podemos sacar de esos estudios es que no podemos sacar ninguna conclusión de ellos" (Tanenbaum) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] pg_autovacuum: short, wide tables
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> On Fri, Jul 08, 2005 at 01:29:03PM -0400, Tom Lane wrote:
>> The main bit of additional logic that might be needed is an awareness
>> that firing a VACUUM on a main table will implicitly fire one on its
>> toast table, and so you'd not want to go and issue the toast table
>> VACUUM separately.
> I don't see any reason why this wouldn't work. I even think it'd be
> very easy to implement (from the autovacuum POV -- not sure about the
> stat system). Furthermore, the awareness you mention is also very easy
> to implement: we just need to make sure the pg_autovacuum tuple for the
> toast table is updated when it is vacuumed, which is just an additional
> function call.
I'm having some second thoughts about allowing VACUUM on a toast table
independently of its parent table --- it's a bit scary to be messing
with the toast table when we have no lock at all on the parent. It
might work OK, but I'm not sure I want to take the risk. If we simply
expose toast tables in the stats views, what has to be done to
autovacuum to get it to work properly? ("Properly" in this case would
mean "fire a VACUUM on the parent table if either it or its toast table
look like they need vacuumed".) Is this much worse than what you
say above?
> Have you looked at the autovacuum integration patch?
No, but will do.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] pg_autovacuum: short, wide tables
Tom Lane wrote:
I'm having some second thoughts about allowing VACUUM on a toast table
independently of its parent table --- it's a bit scary to be messing
with the toast table when we have no lock at all on the parent. It
might work OK, but I'm not sure I want to take the risk. If we simply
expose toast tables in the stats views, what has to be done to
autovacuum to get it to work properly? ("Properly" in this case would
mean "fire a VACUUM on the parent table if either it or its toast table
look like they need vacuumed".) Is this much worse than what you
say above?
Oh well It sounded like a good idea :-)
No it's not much worse, in fact handling this is much like the way we
handle vacuum analyze and analyze only separately. So the
infrastructure is mostly there, it will require some tweaks to the
patch, but nothing large.
Matt
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] pg_autovacuum: short, wide tables
On Fri, Jul 08, 2005 at 02:35:14PM -0400, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > On Fri, Jul 08, 2005 at 01:29:03PM -0400, Tom Lane wrote:
> >> The main bit of additional logic that might be needed is an awareness
> >> that firing a VACUUM on a main table will implicitly fire one on its
> >> toast table, and so you'd not want to go and issue the toast table
> >> VACUUM separately.
>
> > I don't see any reason why this wouldn't work. I even think it'd be
> > very easy to implement (from the autovacuum POV -- not sure about the
> > stat system). Furthermore, the awareness you mention is also very easy
> > to implement: we just need to make sure the pg_autovacuum tuple for the
> > toast table is updated when it is vacuumed, which is just an additional
> > function call.
>
> I'm having some second thoughts about allowing VACUUM on a toast table
> independently of its parent table --- it's a bit scary to be messing
> with the toast table when we have no lock at all on the parent. It
> might work OK, but I'm not sure I want to take the risk. If we simply
> expose toast tables in the stats views, what has to be done to
> autovacuum to get it to work properly? ("Properly" in this case would
> mean "fire a VACUUM on the parent table if either it or its toast table
> look like they need vacuumed".) Is this much worse than what you
> say above?
No, just a little bit more logic, I think. It requires storing info
about toast tables in pg_autovacuum, but I don't see a problem with
that. Currently there's a heap scan of pg_autovacuum, on which we would
need to skip toast tables and consider them only in conjunction with
their respective main table.
Another issue altogether is the stat system. I don't know if it stores
info about toast tables. Note that it doesn't matter that it doesn't
show up in the pg_stat views, because we don't use those; rather we
access the hash tables directly.
> > Have you looked at the autovacuum integration patch?
>
> No, but will do.
Cool.
--
Alvaro Herrera ()
"Nunca se desea ardientemente lo que solo se desea por razón" (F. Alexandre)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] pg_autovacuum: short, wide tables
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Another issue altogether is the stat system. I don't know if it stores > info about toast tables. Yes, it does --- you can pull out the info, even, if you use the stats access functions directly. It's just that the views filter on relkind = 'r'. > Note that it doesn't matter that it doesn't show up in the pg_stat > views, because we don't use those; rather we access the hash tables > directly. Ah, but this is only true in the integrated version no? The contrib version sure looks like it's depending on the views. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] pg_autovacuum: short, wide tables
Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Note that it doesn't matter that it doesn't show up in the pg_stat views, because we don't use those; rather we access the hash tables directly. Ah, but this is only true in the integrated version no? The contrib version sure looks like it's depending on the views. Correct. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
