[BUGS] BUG #2389: function within function return value
The following bug has been logged online: Bug reference: 2389 Logged by: James M Doherty Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: RH9 Description:function within function return value Details: reate or replace function get_glaccttotals(text,integer,text,text) returns float as ' declare RECORD_DATE alias for $1; BANKID alias for $2; BEG_GL_RANGEalias for $3; END_GL_RANGEalias for $4; arecrecord; grecrecord; brecrecord; total_due numeric := 0; total numeric := 0; BEGIN total_due := 0; total := 0; --= -- now for Total Cash & Due --= for arec in select * from amggenledger ag where (ag.amg_gl_nbr between BEG_GL_RANGE and END_GL_RANGE) loop for grec in select * from genledger g where g.amg_gl_nbr = arec.id and g.bank_id = BANKID loop select into total sum(bbs.bbs_current_balance) from bank_balance_sheet as bbs where bbs.bank_id = BANKID and grec.id = bbs.bbs_bank_acct_nbr and date_eq(bbs.record_date,date(RECORD_DATE)); -- -- the select got us the total for this invidual -- account we not need to keep track of the total -- so we know what to return from all accounts -- raise NOTICE ''[0]get_accttotals() -TOTAL DUE(%) total(%)'',total_due,total; total_due := total_due + total; end loop; --END OF for grec in select * end loop; --END OF for arec in select * from amggenledger ag raise NOTICE ''[1]get_accttotals() -TOTAL DUE(%)'',total_due; RETURN total_due; END; ' language 'plpgsql'; The above function is called as follows: trec.tot_value := get_glaccttotals(RECORD_DATE,BANKID,''A50'',''A500299''); The result is always null. When called on its own via: select * from get_glaccttotals(RECORD_DATE,BANKID,''A50'',''A500299''); it returns the correct value: get_glaccttotals -- 5234938.4 (1 row) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] [EMAIL PROTECTED]: BUG in logs]
OK, you're right about the log_min_error_statement value, but this behaviour has changed from 8.0. In earlier versions ERROR statements did get logged if log_statment was set to all or in 7.4, set to "on" DMaybe I missed something in the changelog of 8.1? On Tue, 11 Apr 2006 23:51:51 +0200, "Guillaume Smet" <[EMAIL PROTECTED]> wrote: >> From: Martin Marques >> I encountered a rare BUG in the way PG is logging. Let me first enlight > with some configuration I have and PG version: > > Perhaps I'm missing something but I think it's not a bug but a > configuration problem. > >> log_min_error_statement| panic > > If you set this one to error instead of panic, you will have your > failed statements logged. > >> log_statement | all > > This one only logs successful queries so it's normal you don't have > the statement in the log file if it fails. > > Regards, > > -- - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(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
[BUGS] BUG #2390: check constraint
The following bug has been logged online:
Bug reference: 2390
Logged by: Andreas Kretschmer
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system: Debian Linux
Description:check constraint
Details:
i want to add a check constraint like:
create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$'));
i doesn't work, but if works, if i change the type for i to varchar(7).
Bug or feature?
---(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
[BUGS] BUG #2391: "Similar to" pattern matching does not operate as documented
The following bug has been logged online: Bug reference: 2391 Logged by: Eric Noriega Email address: [EMAIL PROTECTED] PostgreSQL version: 7.0.5 Operating system: Linux Fedora core 4 Description:"Similar to" pattern matching does not operate as documented Details: As far as I can tell, this may be a bug in how the pattern matches. db=# select 'tab' similar to '(a|b)'; ?column? -- f db=# select 'tab' similar to 'a|b'; ?column? -- t The doc says: Like LIKE, the SIMILAR TO operator succeeds only if its pattern matches the entire string; this is unlike common regular expression practice, wherein the pattern may match any part of the string. If the second case is invalid as an expression (not clear in the docs:Parentheses may be used to group items into a single logical item), then the statement should fail, or return false, not return true. ---(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] BUG in logs
I encountered a rare BUG in the way PG is logging. Let me first enlight with some configuration I have and PG version: prueba2=> SELECT version(); version PostgreSQL 8.1.0 on sparc-unknown-linux-gnu, compiled by GCC cc (GCC) 4.0.3 2005 (prerelease) (Debian 4.0.2-4) (1 row) prueba2=> select name, setting from pg_settings where name like 'log%'; name| setting +- log_connections| on log_destination| stderr log_disconnections | off log_duration | off log_error_verbosity| default log_executor_stats | off log_hostname | off log_line_prefix| <%t> log_min_duration_statement | -1 log_min_error_statement| panic log_min_messages | notice log_parser_stats | off log_planner_stats | off log_rotation_age | 1440 log_rotation_size | 10240 log_statement | all log_statement_stats| off log_truncate_on_rotation | off (18 rows) Now, when I do something like the sentence below, I get an error, which is OK: prueba2=> SELECT * FROM perfiles WHERE codigo = AND perfil = 'something here'; ERROR: error de sintaxis en o cerca de <> at character 39 LINE 1: SELECT * FROM perfiles WHERE codigo = AND perfil = 'somethin... But I should see in the logs the query and then the error, which is not what I'm getting at the momento (I only get the error, ad is shown below). <2006-04-11 16:31:03 ART>ERROR: error de sintaxis en o cerca de <> en car?cter 39 If anymore information is needed, let me know. -- - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects
I'm a bit hurt by your statement that what I sent was just about useless :( The problem here is that I am new to PostgreSQL and PGAdmin III and so, in my confusion about what's normal and what's not, I am unable to provide you with all the details that would help you resolve the problem. However, I tried to be clear about what actions didn't work and those that did. Just as a point of reference, I was essentially thrown into the world of PostgreSQL where the installations were incomplete and the databases were poorly designed so the learning curve has been short and steep. So, let me try to explain this again. I recently added an LO object to a database using Peter Mount's LO type. So far, that's working. Yesterday, I made a backup of the database in order to restore it onto my test server. I used PGAdmin III to do the backup and it worked OK. Due to the problems I'm having with the restore, I tried the backup from two Mac OS X G4 servers and one Mac OS X Intel Dou server. All the backups were run from PGAdmin III and they all seem to work. I didn't attempt to restore every backup from every machine but they all ran the same and no error messages appeared. When I try to restore the backup using PGAdmin III, the log window begins to fill up. Near the end, when it should say it's restoring the BLOBS an error message appears stating the BLOBS couldn't be restored. I don't have the exact text of the message but I could get it for you if needed. I even created a test database with one table and two fields. The fields were recordid and logo (the LO type field). I couldn't even get this database to restore using PGAdmin III. The point here is that it doesn't matter which server I tried to restore too or which database I used (as long as it had at least one large object stored in it), if I used PGAdmin III, the same error message appeared at the same place in the process. However, if I restored the backup by opening a command or terminal window and ran the command from the command line, it worked. You should have no problem reproducing the same error message that I received. If you don't see the same problem, let me know and the next time I go to do a restore I'll get the details for you. By the way, when I put the backup file on one of the Macs and then ran the restore using the command line from the Mac Terminal window I was only prompted for a password once. However, when restoring the backup onto the Windows 2003 server I was prompted for the password at the beginning of the process and then just before restoring the BLOBs. Don't know how this might be related by I thought I would let you know. If you are unable to reproduce the problem by simply attempting to restore a backup of a database that has some LO data stored in it, let me know and I'll start from scratch and send you all the details that I can come up with. Patrick Headley Linx Consulting, Inc. (303) 916-5522 [EMAIL PROTECTED] www.linxco-inc.com -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 2:14 PM To: Patrick Headley Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects "Patrick Headley" <[EMAIL PROTECTED]> writes: > Description:pg_restore doesn't restore large objects At no point did you show us exactly what you did or exactly what went wrong, so even though this report has a lot of version-number details, it's just about useless :-(. Please see the reporting suggestions at http://www.postgresql.org/docs/8.1/static/bug-reporting.html regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Permission denied on fsync / Win32 (was [BUGS] right sibling is not next child)
It turns out we've been getting rather huge numbers of "Permission denied" errors relating to fsync so perhaps it wasn't really a precursor to the crash as I'd previously thought. I've pasted in a complete list following this email covering the time span from 3/20 to 4/6. The number in the first column is the number of times the given log message appeared. The interesting thing is that _none_ of the referenced relfilenode numbers actually appear in the file system. In a possibly related note, I've confirmed there is a race condition on Windows when the temporary stats file is renamed to the working one. There is apparently a window where a backend opening the stats file will find it missing. I'll send more info later. I haven't had a chance to come back to it yet. Regarding your other questions: - The file system is NTFS - Regarding the initial crash, looking more closely, I don't think it was a crash at all (or at most it was a human-induced "crash"). In the log everything looks normal (assuming the Permission denied errors are "normal"), and then [2006-03-31 14:26:30.705 ] 2328 LOG: received fast shutdown request [2006-03-31 14:35:33.173 ] 4016 FATAL: the database system is shutting down [2006-03-31 14:35:33.189 ] 6504 FATAL: the database system is shutting down [2006-03-31 14:39:53.595 ] 7576 FATAL: the database system is shutting down and in the next log file [2006-03-31 14:31:05.298 ] 608 LOG: database system was interrupted at 2006-03-31 13:20:06 Central Standard Time [2006-03-31 14:31:05.314 ] 608 LOG: checkpoint record is at EF/B41D7580 [2006-03-31 14:31:05.314 ] 608 LOG: redo record is at EF/B41A0C08; undo record is at 0/0; shutdown FALSE [2006-03-31 14:31:05.314 ] 608 LOG: next transaction ID: 295492806; next OID: 1395901 [2006-03-31 14:31:05.314 ] 608 LOG: next MultiXactId: 1; next MultiXactOffset: 0 [2006-03-31 14:31:05.314 ] 608 LOG: database system was not properly shut down; automatic recovery in progress Pete 747 LOG: could not fsync segment 0 of relation 1663/16385/1361661: Permission denied 414 LOG: could not fsync segment 0 of relation 1663/16385/1363194: Permission denied 2 LOG: could not fsync segment 0 of relation 1663/16385/1363196: Permission denied 441 LOG: could not fsync segment 0 of relation 1663/16385/1369401: Permission denied 4520 LOG: could not fsync segment 0 of relation 1663/16385/1373027: Permission denied 1024 LOG: could not fsync segment 0 of relation 1663/16385/1374375: Permission denied 2683 LOG: could not fsync segment 0 of relation 1663/16385/1375726: Permission denied 775 LOG: could not fsync segment 0 of relation 1663/16385/1375733: Permission denied 83 LOG: could not fsync segment 0 of relation 1663/16385/1377367: Permission denied 64 LOG: could not fsync segment 0 of relation 1663/16385/1377685: Permission denied 3334 LOG: could not fsync segment 0 of relation 1663/16385/1379641: Permission denied 16 LOG: could not fsync segment 0 of relation 1663/16385/1381290: Permission denied 819 LOG: could not fsync segment 0 of relation 1663/16385/1383833: Permission denied 347 LOG: could not fsync segment 0 of relation 1663/16385/1386037: Permission denied 1 LOG: could not fsync segment 0 of relation 1663/16385/1388257: Permission denied 135 LOG: could not fsync segment 0 of relation 1663/16385/1388264: Permission denied 6397 LOG: could not fsync segment 0 of relation 1663/16385/1389813: Permission denied 3719 LOG: could not fsync segment 0 of relation 1663/16385/1391589: Permission denied 763 LOG: could not fsync segment 0 of relation 1663/16385/1391610: Permission denied 10784 LOG: could not fsync segment 0 of relation 1663/16385/1392439: Permission denied 5846 LOG: could not fsync segment 0 of relation 1663/16385/1392444: Permission denied 1 LOG: could not fsync segment 0 of relation 1663/16385/2282587: Permission denied 243 LOG: could not fsync segment 0 of relation 1663/16385/2282602: Permission denied 526 LOG: could not fsync segment 0 of relation 1663/16385/2293690: Permission denied 1754 LOG: could not fsync segment 0 of relation 1663/16385/2293695: Permission denied 894 LOG: could not fsync segment 0 of relation 1663/16385/2304886: Permission denied 692 LOG: could not fsync segment 0 of relation 1663/16385/2304891: Permission denied 1556 LOG: could not fsync segment 0 of relation 1663/16385/2315303: Permission denied >>> Tom Lane <[EMAIL PROTECTED]> 04/13/06 2:30 am >>> crash at 14:31? What was the immediate cause of the crash (I'm expecting a PANIC or possibly an Assert triggered it)? BTW, what sort of filesystem is the database sitting on, on this machine? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2390: check constraint
On Wed, 12 Apr 2006, Andreas Kretschmer wrote:
> The following bug has been logged online:
>
> Bug reference: 2390
> Logged by: Andreas Kretschmer
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.1.3
> Operating system: Debian Linux
> Description:check constraint
> Details:
>
> i want to add a check constraint like:
>
> create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$'));
>
> i doesn't work, but if works, if i change the type for i to varchar(7).
Well, the regex doesn't entirely make sense for char(n) data. It's not
possible to have 6 characters between beginning and end because it's a
fixed length 7 character string. If you try to insert '00' into i,
you're actually inserting '00 ' which is invalid by the constraint.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] right sibling is not next child
Sounds good. There is nothing sensitive in DbTranImageStatus_pkey so if you decide you want it after all, it's there for the asking. Pete >>> Tom Lane <[EMAIL PROTECTED]> 04/13/06 3:30 am >>> Oh, never mind ... I've sussed it. ---(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] BUG #2390: check constraint
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Wed, 12 Apr 2006, Andreas Kretschmer wrote:
>> i want to add a check constraint like:
>> create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$'));
>>
>> i doesn't work, but if works, if i change the type for i to varchar(7).
> Well, the regex doesn't entirely make sense for char(n) data. It's not
> possible to have 6 characters between beginning and end because it's a
> fixed length 7 character string. If you try to insert '00' into i,
> you're actually inserting '00 ' which is invalid by the constraint.
You could argue that since we consider trailing spaces not to be
semantically significant in char(n), it would be more consistent to
strip those spaces before performing the regex match. Currently the
system goes out of its way to cause the trailing spaces in the char(n)
value to be seen by the regex: there's actually a separate ~ operator
for bpchar. If we simply removed that, and let the normal char-to-text
promotion be invoked first, the match would work as Andreas expects.
I seem to recall that we've discussed this before, but don't remember if
the idea was actively rejected or just faded out of mind without being
implemented.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #2389: function within function return value
"James M Doherty" <[EMAIL PROTECTED]> writes: > The above function is called as follows: > trec.tot_value := > get_glaccttotals(RECORD_DATE,BANKID,''A50'',''A500299''); > The result is always null. It's impossible to do much with this when you have not shown us a complete test case, but I'm wondering if your calling function is passing parameter values that don't match anything in the bank_balance_sheet table. That would cause the sum() to return null and then total_due would go to null as well. It's pretty bogus that SQL defines sum() over no rows to return null rather than zero, but the spec is perfectly clear about it. You might want to change sum(bbs.bbs_current_balance) to coalesce(sum(bbs.bbs_current_balance), 0) if you need to deal with such situations. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2391: "Similar to" pattern matching does not operate as documented
"Eric Noriega" <[EMAIL PROTECTED]> writes:
> db=# select 'tab' similar to 'a|b';
> ?column?
> --
> t
Yeah, this is a bug ... the cause can be seen by looking at the
underlying similar_escape() function, which converts a SIMILAR TO
pattern into a POSIX regex pattern:
regression=# select similar_escape('(a|b)', null);
similar_escape
^(a|b)$
(1 row)
regression=# select similar_escape('a|b', null);
similar_escape
^a|b$
(1 row)
regression=#
I believe that in the second case, ^ and $ bind more tightly than |
per POSIX rules. So we need to put parens around the pattern to
prevent that.
Thanks for the report!
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2390: check constraint
On Thu, 13 Apr 2006, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Wed, 12 Apr 2006, Andreas Kretschmer wrote:
> >> i want to add a check constraint like:
> >> create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$'));
> >>
> >> i doesn't work, but if works, if i change the type for i to varchar(7).
>
> > Well, the regex doesn't entirely make sense for char(n) data. It's not
> > possible to have 6 characters between beginning and end because it's a
> > fixed length 7 character string. If you try to insert '00' into i,
> > you're actually inserting '00 ' which is invalid by the constraint.
>
> You could argue that since we consider trailing spaces not to be
> semantically significant in char(n), it would be more consistent to
> strip those spaces before performing the regex match.
Possibly, although I'm not sure that the particulars of how we treat
spaces in char(n) are precisely right either. :)
AFAIR, the spec doesn't talk about stripping spaces, it talks about
padding shorter values. That's usually the same, but for cases like this
one, I think it's different.
---(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: Permission denied on fsync / Win32 (was [BUGS] right sibling is not next child)
"Peter Brant" <[EMAIL PROTECTED]> writes: > It turns out we've been getting rather huge numbers of "Permission > denied" errors relating to fsync so perhaps it wasn't really a precursor > to the crash as I'd previously thought. > I've pasted in a complete list following this email covering the time > span from 3/20 to 4/6. The number in the first column is the number of > times the given log message appeared. Wow. What was happening to your pg_xlog directory while this was going on? I would expect that the system would plow ahead after this error, but having failed to complete the checkpoint, it would never be able to free any back WAL segments. Were you accumulating lots of gigabytes of WAL files? Or maybe the errors came and went, so that sometimes you could get through a checkpoint? > The interesting thing is that _none_ of the referenced relfilenode > numbers actually appear in the file system. Could they have been temporary tables? Alternatively, if you routinely use TRUNCATE, CLUSTER, or REINDEX (all of which assign new relfilenode numbers), then maybe they were older versions of tables that still exist. > - The file system is NTFS OK, anyone know anything about permissions on NTFS? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Permission denied on fsync / Win32 (was [BUGS] right sibling is not next child)
> > - The file system is NTFS > > OK, anyone know anything about permissions on NTFS? Yes. What do you need to know ;-) BTW, win32 sometimes has a bad habit of returning access denied for other things as well - in some caes you can get access denied instead of sharing violation, and you can often get it from AV and firewalls and such. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Permission denied on fsync / Win32 (was [BUGS] right sibling is not next child)
"Magnus Hagander" <[EMAIL PROTECTED]> writes: > BTW, win32 sometimes has a bad habit of returning access denied for > other things as well - in some caes you can get access denied instead of > sharing violation, and you can often get it from AV and firewalls and > such. Looking at the fsync code in md.c, I note that it's designed to disregard file-not-found (ENOENT). Are there any cases in which win32 might return EPERM for scenarios that a sane person would call file-not-found? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects
I think our problem is that we understand the backend very well, but not how pgadmin does this operation. --- Patrick Headley wrote: > I'm a bit hurt by your statement that what I sent was just about useless :( > The problem here is that I am new to PostgreSQL and PGAdmin III and so, in > my confusion about what's normal and what's not, I am unable to provide you > with all the details that would help you resolve the problem. However, I > tried to be clear about what actions didn't work and those that did. Just as > a point of reference, I was essentially thrown into the world of PostgreSQL > where the installations were incomplete and the databases were poorly > designed so the learning curve has been short and steep. > So, let me try to explain this again. > > I recently added an LO object to a database using Peter Mount's LO type. So > far, that's working. Yesterday, I made a backup of the database in order to > restore it onto my test server. I used PGAdmin III to do the backup and it > worked OK. Due to the problems I'm having with the restore, I tried the > backup from two Mac OS X G4 servers and one Mac OS X Intel Dou server. All > the backups were run from PGAdmin III and they all seem to work. I didn't > attempt to restore every backup from every machine but they all ran the same > and no error messages appeared. > > When I try to restore the backup using PGAdmin III, the log window begins to > fill up. Near the end, when it should say it's restoring the BLOBS an error > message appears stating the BLOBS couldn't be restored. I don't have the > exact text of the message but I could get it for you if needed. I even > created a test database with one table and two fields. The fields were > recordid and logo (the LO type field). I couldn't even get this database to > restore using PGAdmin III. The point here is that it doesn't matter which > server I tried to restore too or which database I used (as long as it had at > least one large object stored in it), if I used PGAdmin III, the same error > message appeared at the same place in the process. However, if I restored > the backup by opening a command or terminal window and ran the command from > the command line, it worked. You should have no problem reproducing the same > error message that I received. If you don't see the same problem, let me > know and the next time I go to do a restore I'll get the details for you. > > By the way, when I put the backup file on one of the Macs and then ran the > restore using the command line from the Mac Terminal window I was only > prompted for a password once. However, when restoring the backup onto the > Windows 2003 server I was prompted for the password at the beginning of the > process and then just before restoring the BLOBs. Don't know how this might > be related by I thought I would let you know. > > If you are unable to reproduce the problem by simply attempting to restore a > backup of a database that has some LO data stored in it, let me know and > I'll start from scratch and send you all the details that I can come up > with. > > Patrick Headley > Linx Consulting, Inc. > (303) 916-5522 > [EMAIL PROTECTED] > www.linxco-inc.com > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 11, 2006 2:14 PM > To: Patrick Headley > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects > > "Patrick Headley" <[EMAIL PROTECTED]> writes: > > Description:pg_restore doesn't restore large objects > > At no point did you show us exactly what you did or exactly what went > wrong, so even though this report has a lot of version-number details, > it's just about useless :-(. Please see the reporting suggestions at > http://www.postgresql.org/docs/8.1/static/bug-reporting.html > > regards, tom lane > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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: Permission denied on fsync / Win32 (was [BUGS] right
The culprit is CLUSTER. There is a batch file which runs CLUSTER against six, relatively small (60k rows between them) tables at 7am, 1pm, and 9pm. Follows is the list of dates and hours when the "Permission denied" errors showed up. They match up to a tee (although the error apparently sometimes persists for a while). The machine is clean (basically just Windows + Postgres [no AV, firewall, etc. software]). Pete 2006-03-20 21 2006-03-21 07 2006-03-22 21 2006-03-23 21 2006-03-23 22 2006-03-24 13 2006-03-24 21 2006-03-24 22 2006-03-26 13 2006-03-27 13 2006-03-27 21 2006-03-27 22 2006-03-28 13 2006-03-28 21 2006-03-29 13 2006-03-29 21 2006-03-30 13 2006-03-30 14 2006-03-30 15 2006-03-30 21 2006-03-30 22 2006-03-31 07 2006-03-31 08 2006-03-31 09 2006-03-31 10 2006-03-31 11 2006-03-31 12 2006-03-31 13 2006-04-03 21 2006-04-04 07 2006-04-05 07 2006-04-05 21 >>> Tom Lane <[EMAIL PROTECTED]> 04/13/06 8:30 pm >>> > The interesting thing is that _none_ of the referenced relfilenode > numbers actually appear in the file system. Could they have been temporary tables? Alternatively, if you routinely use TRUNCATE, CLUSTER, or REINDEX (all of which assign new relfilenode numbers), then maybe they were older versions of tables that still exist. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects
pgAdmin just uses pg_dump/pg_restore to handle the heavy lifting. Regards, Dave. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian > Sent: 13 April 2006 20:40 > To: Patrick Headley > Cc: 'Tom Lane'; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore > large objects > > > I think our problem is that we understand the backend very > well, but not how pgadmin does this operation. > > -- > - > > Patrick Headley wrote: > > I'm a bit hurt by your statement that what I sent was just about > > useless :( The problem here is that I am new to PostgreSQL > and PGAdmin > > III and so, in my confusion about what's normal and what's > not, I am > > unable to provide you with all the details that would help > you resolve > > the problem. However, I tried to be clear about what actions didn't > > work and those that did. Just as a point of reference, I was > > essentially thrown into the world of PostgreSQL where the > > installations were incomplete and the databases were poorly > designed so the learning curve has been short and steep. > > So, let me try to explain this again. > > > > I recently added an LO object to a database using Peter Mount's LO > > type. So far, that's working. Yesterday, I made a backup of the > > database in order to restore it onto my test server. I used PGAdmin > > III to do the backup and it worked OK. Due to the problems > I'm having > > with the restore, I tried the backup from two Mac OS X G4 > servers and > > one Mac OS X Intel Dou server. All the backups were run > from PGAdmin > > III and they all seem to work. I didn't attempt to restore every > > backup from every machine but they all ran the same and no > error messages appeared. > > > > When I try to restore the backup using PGAdmin III, the log window > > begins to fill up. Near the end, when it should say it's > restoring the > > BLOBS an error message appears stating the BLOBS couldn't > be restored. > > I don't have the exact text of the message but I could get > it for you > > if needed. I even created a test database with one table and two > > fields. The fields were recordid and logo (the LO type field). I > > couldn't even get this database to restore using PGAdmin III. The > > point here is that it doesn't matter which server I tried > to restore > > too or which database I used (as long as it had at least one large > > object stored in it), if I used PGAdmin III, the same error message > > appeared at the same place in the process. However, if I > restored the > > backup by opening a command or terminal window and ran the command > > from the command line, it worked. You should have no problem > > reproducing the same error message that I received. If you > don't see the same problem, let me know and the next time I > go to do a restore I'll get the details for you. > > > > By the way, when I put the backup file on one of the Macs > and then ran > > the restore using the command line from the Mac Terminal > window I was > > only prompted for a password once. However, when restoring > the backup > > onto the Windows 2003 server I was prompted for the password at the > > beginning of the process and then just before restoring the BLOBs. > > Don't know how this might be related by I thought I would > let you know. > > > > If you are unable to reproduce the problem by simply attempting to > > restore a backup of a database that has some LO data stored > in it, let > > me know and I'll start from scratch and send you all the > details that > > I can come up with. > > > > Patrick Headley > > Linx Consulting, Inc. > > (303) 916-5522 > > [EMAIL PROTECTED] > > www.linxco-inc.com > > -Original Message- > > From: Tom Lane [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, April 11, 2006 2:14 PM > > To: Patrick Headley > > Cc: pgsql-bugs@postgresql.org > > Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large > > objects > > > > "Patrick Headley" <[EMAIL PROTECTED]> writes: > > > Description:pg_restore doesn't restore large objects > > > > At no point did you show us exactly what you did or exactly > what went > > wrong, so even though this report has a lot of > version-number details, > > it's just about useless :-(. Please see the reporting > suggestions at > > http://www.postgresql.org/docs/8.1/static/bug-reporting.html > > > > regards, tom lane > > > > > > ---(end of > > broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > Bruce Momjian http://candle.pha.pa.us > EnterpriseDBhttp://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---(end of > broadcast)--- > TIP 1: if posting/r
Re: Permission denied on fsync / Win32 (was [BUGS] right
"Peter Brant" <[EMAIL PROTECTED]> writes: > The culprit is CLUSTER. There is a batch file which runs CLUSTER > against six, relatively small (60k rows between them) tables at 7am, > 1pm, and 9pm. Follows is the list of dates and hours when the > "Permission denied" errors showed up. They match up to a tee (although > the error apparently sometimes persists for a while). OK ... but what's still unclear is whether the failures are occurring against the old relfilenode (the one just removed by the CLUSTER) or the new one just added by CLUSTER. If you note the relfilenodes assigned to these tables just before and just after the next cycle of CLUSTERs, it should be easy to tell what the complaints refer to. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
