[BUGS] WAL partition ran out of space, pg_subtrans pg_clog partially written...
This is an FYI re: a bug I ran across. Background: RHEL 6 ext4. PGDATA, a table space, and WAL logs are all on their own partitions. The WAL partition filled up (wal_keep_segments was changed, but Pg hadn't been restarted), and a write happened, and it appears to have resulted in an index page being corrupt. REINDEX'ing the table didn't work with the following error: WARNING: concurrent delete in progress within table tblA WARNING: concurrent delete in progress within table tblA ERROR: could not access status of transaction 86081816 DETAIL: Could not read from file pg_subtrans/0521 at offset 131072: Success. pg_subtrans/0521 was a 90112 byte file and pg_clog/0052 was 24576 bytes in size. I haven't dug in to the code, but I did see the commit message for pgsql/src/backend/access/transam/slru.c 1.23.4.2, which I'm guessing is related. My WAG is that there's an assumption someplace that pg_subtrans and pg_clog are on the same partition as pg_xlog, or that creation of files in pg_subtrans and pg_clog will either absolutely succeed or absolutely fail. It could also be that Linux reported back a successful write(2), but it didn't actually have the space available (ext4). Anyway, after extending pg_subtrans/0521 w/ zeros to its proper 256KB size, I was able to REINDEX the table, but there was a stream of WARNINGs about concurrent inserts and deletes that I didn't dig in to. Upon learning the WAL files were removed as a temporary solution to the space problem, I opted to dump, re-initdb, and load the data, which worked without any errors or warnings being reported. I've saved the data if there are pointed questions about their contents. -sc -- Sean Chittenden s...@chittenden.org -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Feature request: ALTER TABLE tbl SET STATISTICS...
Subject nearly says it all. Could we add it to the TODO list to have per table statistics? I have a table here that requires a stats target of 1000 in order for it to project values correctly (believe me, tried everything in increments of 50 from 10 on up, it needs 1000). The problem being that the rest of the database works just fine with a statistics target of 20. Using per-column stats works, but was kind of tedious to setup (there are ~20 active columns that need that high of a stats value). Anyway, something along the lines of: ALTER TABLE tbl SET STATISTICS 1000; would be fantastic. Thanks. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] Stack not being popped correctly (was: Re: [HACKERS] plpgsql lacks generic identifier for record in triggers...)
[snip] Err... wait, this is a classic case of send first then
finishing to pondering the gripe.
And sending a reply to ones self without actually testing my suggestion.
db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
EXECUTE public.mc_init();
EXECUTE public.mc_delete(''mc_key'');
RETURN NULL;
END;' LANGUAGE 'plpgsql';
db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON
schma.tbl FOR EACH STATEMENT EXECUTE PROCEDURE schma.tbl_inval();
Which, doesn't work as expected as it seems as though there's something
left behind on the stack that shouldn't be. Here's the case to
reproduce (doesn't involve pgmemcache):
test=# CREATE TABLE t5 (i int);
Time: 35.294 ms
test=# CREATE FUNCTION t5_func() RETURNS TRIGGER AS 'BEGIN EXECUTE
TRUE; RETURN NULL; END;' LANGUAGE 'plpgsql';
Time: 101.701 ms
test=# CREATE TRIGGER t5_func_trg AFTER INSERT ON t5 FOR EACH STATEMENT
EXECUTE PROCEDURE t5_func();
Time: 62.345 ms
test=# INSERT INTO t5 VALUES (1);
ERROR: syntax error at or near t at character 1
QUERY: t
CONTEXT: PL/pgSQL function t5_func line 1 at execute statement
LINE 1: t
^
Doh! Can someone with plpgsql foo look into this? -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[BUGS] ALTER USER SET log_* not allowed...
I've got a particular database account that connects/disconnects hundreds of times a second, literally (email delivery agent). Being the ever ready logger that I am, I have a number of logging bits turned on to help me identify problems when they come up. In this case, I'm not worried about a problem and want to turn off most logging for this particular user. I can issue an ALTER USER [usr] SET log_* = false/none in all cases, except for log_duration. db=# ALTER USER dbmail SET log_disconnections = false; ERROR: parameter log_disconnections cannot be set after connection start :-/ I use disconnections as the way of noting the number of connections. Regardless, I think I'm off to the races... BUT! not quite: INFO: permission denied to set parameter log_statement HINT: Must be superuser to increase this value. INFO: permission denied to set parameter log_duration HINT: Must be superuser to change this value to false. doh! So much for that idea. There's no real way to have some useconfig variables run by the super user and some run by the session user. My workaround is to have the program call a SECURITY DEFINER function, but I'd be nice to be able to remove that hack. Anyway, something to consider next time the system catalogs are being tweaked. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] ALTER USER SET log_* not allowed...
doh! So much for that idea. There's no real way to have some
useconfig variables run by the super user and some run by the session
user. My workaround is to have the program call a SECURITY DEFINER
function, but I'd be nice to be able to remove that hack.
Yeah, the whole concept of USERLIMIT GUC variables is fairly dubious,
because of the fact that we have to be able to set these values at
times
when we don't necessarily know whether the user is a superuser or not.
[snip]
It strikes me that a more useful definition would be to say that you
must be superuser, period, to install an ALTER USER/DATABASE value for
any USERLIMIT variable; and then we could treat these values as
privileged for USERLIMIT purposes. This would lose the ability for
non-superusers to set allowable values for themselves this way, but
I think the case we'd gain is more useful.
Comments?
Oh! Please! I thought about suggesting that but didn't think it'd
pass your litmus test and figured a pg_shadow.useconfig and an
pg_shadow.admconfig would be received better, but, absolutely! The
reason I hesitated to suggest such a change was SET search_path = foo;,
which a user should be able to set on their own. Sure it'd be easy to
have it a one-off exception, but then it'd be a one-off exception and
having an 'ALTER USER usr ADMIN SET guc = val' would skirt that issue
completely. That's the only concern that I have.
How about this, leave the existing system in place, but since useconfig
is just a TEXT[], if an admin sets the value (ALTER USER usr ADMIN
SET), prefix the guc name with an 'A:'. As things currently stand,
useconfig looks like, '{search_path=dbmail,log_statements=none}', but
after, it'd look like: '{search_path=dbmail,A:log_statements=none}'.
Then log_statements gets set with admin privs, where as search_path is
set with user privs. Pros:
*) Preserves backwards compatibility with existing databases and the
GUC security infrastructure (no need to bump catalog version)
*) Allows GUC variables to be set with differing permission levels and
still be set by the user
*) At ALTER USER time, a permission message can be returned that tells
the user a GUC has already been set by the admin, go bug them to change
this value
Cons:
*) Places a special value on the prefix of GUC variable names.
*) Requires adding a new keyword in the ALTER USER syntax.
*) Feels a tad like a miscellaneous column that is on the verge of
being abused.
Then again, isn't it on the horizon to have GUC reworked? Maybe this
would be an acceptable addition. *shrug* Just an idea. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] ALTER USER SET log_* not allowed...
Without USERLIMIT, we could easily change the rules to make ALTER USER work the way you want: we'd say you have to be superuser to issue ALTER USER or ALTER DATABASE with a SUSET variable (already true), and then the value can be adopted at session start in all cases since we can then treat pg_shadow and pg_database as secure sources. Nevermind, you win. That's far more elegant/easier... is 8.0 looking like a good time to make this break from tradition? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[BUGS]
set nomail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] bgwriter interfering with consistent view of system tables?
When making lots of DDL changes to a database (I believe this includes temp tables too), delayed flushing of dirty buffers from the system catalogs is causing a severe problem with maintaining a consistent view of the structure of the database. For these examples, I'd create a quick Makefile to aid in testing. printf testing_delay: Makefile.bug printf \tpsql -c 'DROP DATABASE mydb' template1 Makefile.bug printf \tpsql -c 'CREATE DATABASE mydb' template1 Makefile.bug To reproduce and test this bug, issue `make -f Makefile.bug`. With the following config settings: # - Background writer - bgwriter_delay = 5000 # 10-5000 milliseconds bgwriter_percent = 1# 0-100% of dirty buffers bgwriter_maxpages = 1 # 1-1000 buffers max at once it is *very* easy to reproduce this problem (note, there is a bug in the default config, the min percent is 1, no 0 as the comment suggests). With the default settings, it has been harder to spot on my laptop. I believe that higher end systems with higher values will trip over this problem less frequently. With the settings set: % make -f Makefile.bug psql -c DROP DATABASE mydb template1 DROP DATABASE psql -c CREATE DATABASE mydb template1 ERROR: source database template1 is being accessed by other users *** Error code 1 The problem being, I've disconnected from template1 already, but the database hasn't flushed this to disk so the parent postmaster process isn't aware of the disconnection, so when I connect to the backend again, the newly created child has an inconsistent view of the current connections which prevents me from creating a new database (maybe the old backend is still around cleaning up and really hasn't exited, I'm not sure). I think the same phenomena used to exist with temp tables across connections that reconnected to a backend with the same backend # (ie, connect to backend 123, create a temp table, disconnect, reconnect and get backend 123, recreate the same temp table and you'll get an error... though I can't reproduce the temp table error right now, yay!). Anyway, Tom/Jan, this code seems to be your areas of expertise, could either of you take a look? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] Denial of service via VACUUM, all backends exit and restart...
: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. somedb= SELECT * from t1; id | i +--- (0 rows) Happy happy joy joy. :-( I ran this test a dozen times, and periodically I'd get the following error message: psql:test.sql:36: ERROR: relcache reference pg_toast_81859 is not owned by resource owner instead of the crash, which leads me to believe that this could be related to bgwriter as it seems to be execution time dependent and bgwriter is the only component that I know of that could alter the ordering of events via its calls to msleep(). I'm also of the belief that pg_autovacuum seems to help mitigate this if I'm running this script right as pg_autovacuum. If I split the above VACUUM commands into two different VACUUM's: VACUUM; VACUUM FULL; I get varying results: COMMIT psql:test.sql:36: ERROR: relcache reference tmptbl is not owned by resource owner TopTransaction ANALYZE psql:test.sql:37: ERROR: relcache reference pg_toast_122795 is not owned by resource owner TopTransaction or sometimes: psql:test.sql:36: ERROR: pg_toast_122805 is not an index psql:test.sql:37: ERROR: pg_toast_122805 is not an index or: psql:test.sql:36: ERROR: could not open relation with OID 245679 psql:test.sql:37: ERROR: could not open relation with OID 245679 or: psql:test.sql:36: ERROR: relcache reference pg_toast_204715 is not owned by resource owner TopTransaction psql:test.sql:37: ERROR: relcache reference tmptbl is not owned by resource owner and sometimes: psql:test.sql:36: ERROR: relcache reference pg_class is not owned by resource owner TopTransaction server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:test.sql:37: connection to server was lost If I change things around so it's ANALYZE; VACUUM;, I can prod out a different error message: ANALYZE psql:test.sql:36: WARNING: you don't own a lock of type RowExclusiveLock VACUUM psql:test.sql:37: ERROR: relcache reference tmptbl is not owned by resource owner TopTransaction and sometimes I just get: psql:test.sql:36: WARNING: you don't own a lock of type AccessShareLock VACUUM VACUUM Both of them I can't get when doing VACUUMs alone. :-( That last error message is spooky because I don't know if the backend is in a stable state or not given the other error messages, I'm spooked. :-/ So, with the wide range of error messages that come from the same script, I'm wondering if some memory isn't being trampled on, or the new subtransaction code and VACUUM/ANALYZE don't get along, or it's bgwriter somehow. *big shrug* Regardless, I thought this would be of keen interest to many: hopefully a fix can be found before 8.0 is released. -sc PS I haven't tested to see if this bug exists in pre-8.X releases. PPS Sorry for the barrage of bugs, I've been working offline for a few days now... now driving and found a hot spot along 101. /me gives 3 cheers for unprotected access points!!! -- Sean Chittenden ---(end of broadcast)--- TIP 3: 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] bgwriter interfering with consistent view of system tables?
When making lots of DDL changes to a database (I believe this includes temp tables too), delayed flushing of dirty buffers from the system catalogs is causing a severe problem with maintaining a consistent view of the structure of the database. This analysis is completely bogus. That doesn't surprise me at all: I couldn't think of what else it would've been. % make -f Makefile.bug psql -c DROP DATABASE mydb template1 DROP DATABASE psql -c CREATE DATABASE mydb template1 ERROR: source database template1 is being accessed by other users It's always been possible for this to happen, primarily because libpq doesn't wait around for the connected backend to exit. If the kernel prefers to schedule other processes then the old backend may still be alive when the new one tries to do CREATE DATABASE. There is nothing stopping the old one from exiting, it's just that the kernel hasn't given the old backend any cycles at all. There's been some discussion of making PQfinish() wait to observe connection closure, which would guarantee that the backend has exited in the non-SSL-connection case. It's not clear how well it would work in the SSL case, though. In any case it's a bit of a band-aid solution. I think the real solution is to find a way to not need the accessed by other users interlock for CREATE DATABASE. *shrug* It'd be good from a security stand point to wait if there is any chance the connection could be resurrected via a man-in-the-middle attack. As it stands, this isn't a real important bug given that the SQL is programatically created and it's trivial to throw in some kind of a sleep... still, it did bother me. I figured locks on tables were stored in stuffed into some kind of a refcount in shared memory segment and that the time needed to decrease the refcount would be insignificant or done as soon as the client signaled their intention to disconnect, not controlled by wait*(2) and the listening postmaster. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Denial of service via VACUUM, all backends exit and restart...
There exists a crash that could easily be used as a denial of service against PostgreSQL by any user who can call a trusted stored procedure that makes use of temp tables. What this is actually exposing is a case where CurrentResourceOwner is left pointing at garbage. PortalRun saves and restores the caller's value of CurrentResourceOwner, which is normally fine and dandy. When doing a top-level command such as the VACUUM, CurrentResourceOwner is TopTransactionResourceOwner. However, VACUUM does internal CommitTransaction and StartTransaction commands, which destroy and recreate the whole transaction including TopTransactionResourceOwner. In many situations TopTransactionResourceOwner ends up getting recreated at the same address it was at before, but this is obviously not guaranteeable in the general case; Sean's test case simply exposes one path in which it isn't at the same address. FYI, I can confirm that your commit fixes this issue. Thank you very much! -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] BUG #1276: Backend panics on SETVAL('..', 0)...
ERROR: setval: value 0 is out of bounds for sequence foo_id_seq (1..9223372036854775807) FATAL: block 0 of 1663/97972/98006 is still referenced (private 1, global 1) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost This bug can be closed. I was able to confirm that this bug has been fixed by Tom's latest VACUUM fix. I was triggering this via a pg_autovacuum that was running with a running with a 15sec sleep on a schema load that was taking roughly 3 minutes (just the DDL). After Tom's latest VACUUM commit, this bug does not appear to exist any more. Thank you Tom! -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] CREATE DATABASE OWNER not propagating to child objects?
Howdy. I think this problem is best demonstrated with a test case: template1=# CREATE DATABASE foo OWNER someuser; CREATE DATABASE template1=# \c foo You are now connected to database foo. foo=# \dn List of schemas Name| Owner +--- information_schema | dba pg_catalog | dba pg_toast | dba public | dba (4 rows) ?? I set the owner to someuser. A listing from \l reveals that the database is indeed owned by the user someuser, but, since some user is not a super user, this causes problems when someuser tries to perform operations in the public schema. My use case being, when I create a new database for a user who isn't a super user, I execute the following as someuser: \c foo someuser REVOKE ALL PRIVILEGES ON DATABASE foo FROM PUBLIC CASCADE; GRANT CREATE,TEMPORARY ON DATABASE foo TO someuser; WARNING: no privileges could be revoked REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC CASCADE; WARNING: no privileges were granted GRANT USAGE ON SCHEMA public TO PUBLIC; Which makes sense since someuser doesn't own the schema... but I can't help but think someuser should. I'm guessing dime to dollar most database owners are also superusers so this hasn't been a problem to date. When not a superuser and I try and plop some functions into the public schema as someuser, I get the following: \c foo someuser foo= SHOW search_path ; search_path -- $user,public (1 row) foo= CREATE FUNCTION bar() RETURNS VOID AS 'BEGIN RETURN; END;' LANGUAGE 'plpgsql'; ERROR: permission denied for schema public Which seems like the most egregious problem to me. When looking into the createdb() code in src/backend/commands/dbcommands.c, I noticed that the owner is only used to set the database owner and does nothing to set the owner of the objects which are copied from the template database. This seems really scary to me from a security perspective... man I'm sure glad I trust template1... having template1 open for business to anyone by default is creapy, however. CREATE EMPTY DATABASE foo, anybody? :) The dependency on 'cp -r' would go away if an empty database was created natively in the backend. :) Empty being defined as only pg_catalog, pg_toast, and public with no permissions granted on it (not even the information_schema schema). My $0.02. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] [GENERAL] cache lookup of relation 165058647 failed
I'v find out that this error occurs in: dependency.c file 2004-04-26 11:09:34 ERROR: dependency.c 1621: cache lookup of relation 149064743 failed 2004-04-26 11:09:34 ERROR: Relation tmp_table1 does not exist 2004-04-26 11:09:34 ERROR: Relation tmp_table1 does not exist in getRelationDescription(StringInfo buffer, Oid relid) function. Any ideas what can cause this errors. aolMe too./aol But, I am suspecting that it's a race condition with the new background writer code. I've started testing a new database design and was able to reproduce this on my laptop nearly 90% of the time, but could only reproduce it about 10% of the time on my production databases until I figured out what the difference was, fsync. temp tables don't use the shared buffer cache, how can this be related to the BG writer? Don't the system catalogs use the shared buffer cache? BEGIN; SELECT create_temp_table_func(); -- Inserts a row into pg_class via CREATE TEMP TABLE -- Do other stuff COMMIT; -- After the commit, the row is now visible to other backends -- disconnect -- If the delay between the disconnect and reconnect is small enough -- reconnect -- It's as though there is a race condition that allows the function -- pg_table_is_visible() to assert the cache lookup of relation -- error. BEGIN; SELECT create_temp_table_func(); -- Before the CREATE TEMP TABLE, I call /* SELECT TRUE FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = ''footmp''::TEXT AND c.relkind = ''r''::TEXT AND pg_catalog.pg_table_is_visible(c.oid); */ -- But the query fails My guess was that the series of events went something like: proc 0) COMMIT's and the row in pg_class is committed proc 1) bgwriter writer code removes a page for the cache proc 2) queries for the page [*] proc 1) writes it to disk proc 2) queries for the page [*] proc 1) sync's the fd [*] proc 2 queries for the page at either of these points In 7.4, there is no bgwriter or background process mucking with cache, which is why this works 100% of the time. In 7.5, however, there's a 200ms gap where a race condition appears and pg_table_is_visible() fails its PointerIsValid() check. If I put a sleep in, the sleep gives the bgwriter enough time to commit the pages to disk so that the queries for the page happen after the fd's been sync()'ed. I have no other clue as to why this would be happening though, so believe me when I say, I could very well be quite wrong but this is my best, quasi-educated/grep(1)'ed guess. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] [GENERAL] cache lookup of relation 165058647 failed
[Renames thread from The Pepsi Challenge to The PostgreSQL Challenge] But, I am suspecting that it's a race condition with the new background writer code. Why? Have you demonstrated that the failure does not occur in 7.4? What other operations have been added to HEAD that would allow for successful operation of sequential use or testing of temp tables? More importantly, yes, I can confirm that this behavior doesn't exist in REL7_4 as of 40min ago. I've been running the exact same tests repeatedly with nearly identical configs (as close as they can be given the config changes) on REL7_4 as I have HEAD and only HEAD is giving me problems. psql:test-end2.sql:3: ERROR: cache lookup failed for relation 398033 CONTEXT: SQL query SELECT TRUE FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 'tmptbl'::TEXT AND c.relkind = 'r'::TEXT AND pg_catalog.pg_table_is_visible(c.oid) I think that pg_table_is_visible() will inspect the catalogs using SnapshotNow semantics, while the above query will feed it with OIDs that were valid under a start-of-query snapshot. So I'd expect failures in any recent PG version, if tables are being dynamically created/deleted by concurrent transactions. There is no concurrency in the test I gave: it's all sequential. 0 | -- connect 1 | BEGIN; 2 | SELECT setuid_wrapper(); 3 | -- Do other things; 4 | COMMIT; 5 | -- disconnect 6 | -- connect again 7 | BEGIN; 8 | SELECT setuid_wrapper(); -- This fails some % of the time If I add step 5.5 that is a sleep, step 8 will work, without fail. In 7.4, I have no problems, however. That said, the bgwriter code is the only think that I can think of that would muck with caching. -sc PS Other comments temp schema permission patch? -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] [GENERAL] cache lookup of relation 165058647 failed
I'v find out that this error occurs in: dependency.c file 2004-04-26 11:09:34 ERROR: dependency.c 1621: cache lookup of relation 149064743 failed 2004-04-26 11:09:34 ERROR: Relation tmp_table1 does not exist 2004-04-26 11:09:34 ERROR: Relation tmp_table1 does not exist in getRelationDescription(StringInfo buffer, Oid relid) function. Any ideas what can cause this errors. aolMe too./aol But, I am suspecting that it's a race condition with the new background writer code. I've started testing a new database design and was able to reproduce this on my laptop nearly 90% of the time, but could only reproduce it about 10% of the time on my production databases until I figured out what the difference was, fsync. fsync was causing enough of a slow down that SearchSysCache() was finding the tuple, whereas with fsync = false, it wasn't able to find it. But, in search of proving that it wasn't fsync (I use fsync = false on my laptop to save my pour drive), I threw in a sleep in between my tests, and I'm able to get things to work 100% of the time by adding a sleep. The following fails to work with fsync = false, 90% of the time and with fsync = true, only 10% of the time. % psql -f test-begin.sql template1 psql -f test_enterprise_class.sql psql -f test-end1.sql template1 psql -f test-end2.sql template1 But, if I change the command to: % psql -f test-begin.sql template1 psql -f test_enterprise_class.sql psql -f test-end1.sql template1 sleep 1 psql -f test-end2.sql template1 I have no problems with cache relation misses. As for what happens in those commands, I'm: -- 1) Dropping the test database and re-creating it -- 2) In a different connection, load a rather large schema as the dba -- 3) Connect again and create a temp table -- 4) Connect a second time, and check to see if the temp table exists The sleep comes at step 3.5 in the above sequence of operations. *boom* Here's a snippet of my terminal (the first thing I do after BEGINning a transaction is create a temp table if it doesn't exist): ## BEGIN ## [snip] [...] COMMIT You are now connected to database test as user usr. BEGIN psql:test-end2.sql:3: ERROR: cache lookup failed for relation 398033 CONTEXT: SQL query SELECT TRUE FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 'tmptbl'::TEXT AND c.relkind = 'r'::TEXT AND pg_catalog.pg_table_is_visible(c.oid) PL/pgSQL function create_tmptbl line 2 at perform PL/pgSQL function check_or_populate_func line 8 at assignment PL/pgSQL function setuid_wrapper_func line 5 at return ## END ## What's really bothering me is I can push the up arrow on the console, run the exact same thing (including dropping the database), and it'll work sometimes. Very disturbing. As I said, I'm *very* suspicious of the background writer goo that Jan added simply because I can't think of anything else that'd have this problem. I've run each of those commands 100 times now, with and without the sleep 1. With the sleep 1, it's worked 100% of the time. Jan, any bit of code that comes to mind? All of my bgwriter_* settings are set to their default. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[BUGS] \dn [PATTERN] handling not quite right...
I haven't looked in great detail into why this is happpening, but it
seems as though processNamePattern() doesn't handle ?'s correctly in
the negative lookahead context correctly.
1) \dn [pattern] strips ?'s and replaces them with periods. This may
be intentional (as the comment in describe.c suggests, converting input
from shell-style wildcards gets converted into regexp notation), but is
quite annoying. Ex:
test=# \dn foo(?!_log|_shadow)
* QUERY **
SELECT n.nspname AS Name,
u.usename AS Owner
FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u
ON n.nspowner=u.usesysid
WHERE (n.nspname NOT LIKE 'pg\\_temp\\_%' OR
n.nspname = (pg_catalog.current_schemas(true))[1])
AND n.nspname ~ '^foo(.!_log|_shadow)$'
ORDER BY 1;
**
Which is incorrect, IMHO. Instead the last bit of the query should be:
AND n.nspname ~ '^foo(?!_log|_shadow)$'
2) This brings up a large deficiency with the way that \d? [pattern]
handling is done in psql(1). It'd be slick if there was a way to have
psql's pattern routine look at the first non-whitespace character or
two to change change the structure of the query. Something like \dn
!.*_shadow% would change the RE operator from ~ to !~ and \dn %bar%
would translate to LIKE('bar%'). Doing the regexp equiv of
!LIKE('%_shadow') isn't trivial because '^.*(?!_shadow)$' doesn't
return the expected result for various reasons. Oh! This'd be a gun
pointed at foot feature, but having the first character being an =
would, without escaping, drop the remainder of the input directly into
the query (ex: \dn =nspname != (LIKE('%_log') OR LIKE('%_shadow'))).
Maybe a psql(1) variable that changes the behavior of the pattern
queries from using an RE to a LIKE statement could also be a
possibility. The more I think about this, a leading pipe could be used
to pipe the output to a utility, so that \dn | egrep -v '(log|shadow)
would work and would be the easiest solution.
Maybe a better bug report would be, what's the suggested way of doing:
n.nspname !~ '_(log|shadow)$'?
from a list pattern?
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] \dn [PATTERN] handling not quite right...
I haven't looked in great detail into why this is happpening, but it
seems as though processNamePattern() doesn't handle ?'s correctly in
the negative lookahead context correctly.
Negative lookahead context!? You are several sigmas beyond the subset
of regex functionality that \d and friends are intended to support.
Given that we're defining * and ? as shell-style wildcards, it's not
going to be feasible to handle very much of ordinary regex usage let
alone advanced.
I was worried you'd say as much. I'm in a situation where I've got a
few hundred schemas floating around now and about half of them end with
_log or _shadow and I was surprised at how non-trivial it was to filter
out the _log or _shadow schemas with \dn. I tried thinking up the psql
equiv of tcsh's fignore but had no luck (ex: set fignore = (\~ .o
.bak)).
The more I think about this, a leading pipe could be used
to pipe the output to a utility, so that \dn | egrep -v '(log|shadow)
would work and would be the easiest solution.
This on the other hand seems more like a potentially useful feature,
although I'm unclear on what you expect to get sent through the pipe
exactly --- you want column headers for instance? What if you're using
a nondefault display layout?
Instead of using printf(), fprintf(), fwrite(), or whatever it is that
psql(1) uses internally for displaying result sets, have it use the
following chunk of pseudo code:
if (pipe_symbol_found) {
char buf[8192];
size_t len = 0;
memset(buf, 0, sizeof(buf));
fh = popen(..., r+); /* or setsocketpair() + fork() */
fwrite(formatted_output_buffer, strlen(formatted_output_buffer), 1,
fh);
while((len = read(fileno(fh), buf, sizeof(buf {
fwrite(buf, len, 1, stdout);
}
} else {
/* whatever the current code does */
}
That doesn't take into account the set option that lets you write the
output to a file, but that is trivial to handle. To answer your
question, I'd send _everything_ through the pipe and use the pipe as a
blanket IO filter. I haven't thought about this, but would it be
possible to hand the data off to sh(1) and have it handle the
pipe/redirection foo that way psql doesn't have to have any
pipe/redirection brains? If so, I think that'd be slick since you
could do things like '\dn | tail -n +3 | grep -v blah' to handle your
concern about having the header sent through and a utility not wanting
it.
Too bad tee(1) doesn't support a -p option to have tee(1)'s argument
sent to sh(1) or a pipe instead of a file, then there'd be some real
interesting things that one could script. Ex:
\dn | tee -p 'head -n 3 /dev/stdout' | tail -n +3 | egrep -v
'_(log|shadow)$'
Which'd show you the header, but everything after the header would be
sent to egrep(1). I can't understand why win32 users think *NIX's CLI
can be confusing *grin*.
Just some thoughts. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 8: explain analyze is your friend
[BUGS] Expected regression test difference on Mac OSX...
FYI, the regression tests on OS-X 10.3 aren't quite correct (formatting of -0). Not sure how to address this, but thought I'd point it out to the interested parties. -sc Darwin suxunil.local 7.2.0 Darwin Kernel Version 7.2.0: Thu Dec 11 16:20:23 PST 2003; root:xnu/xnu-517.3.7.obj~1/RELEASE_PPC Power Macintosh powerpc regression.diffs Description: Binary data -- Sean Chittenden ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] Table alias fun == incorrectly adding tables to join...
Here's a fun one. test=# CREATE TABLE table_s1 (i int); test=# CREATE TABLE table_s2 (j int); test=# EXPLAIN ANALYZE SELECT table_s1.i, table_s2.j FROM table_s1 AS s1, table_s2 AS s2 WHERE s1.i = s2.j; NOTICE: adding missing FROM-clause entry for table table_s1 NOTICE: adding missing FROM-clause entry for table table_s2 QUERY PLAN - Nested Loop (cost=0.00..0.03 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=1) - Nested Loop (cost=0.00..0.02 rows=1 width=4) (actual time=0.02..0.02 rows=0 loops=1) - Nested Loop (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=1) Join Filter: (outer.i = inner.j) - Seq Scan on table_s1 s1 (cost=0.00..0.00 rows=1 width=4) (actual time=0.01..0.01 rows=0 loops=1) - Seq Scan on table_s2 s2 (cost=0.00..0.00 rows=1 width=4) (never executed) - Seq Scan on table_s1 (cost=0.00..0.00 rows=1 width=4) (never executed) - Seq Scan on table_s2 (cost=0.00..0.00 rows=1 width=4) (never executed) Total runtime: 0.20 msec (9 rows) If there's real data in the tables, this joins the tables on itself and execution times explode, naturally. I don't know if the spec says this is the correct behavior or not, but I have a feeling its not and this is a bug. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Table alias fun == incorrectly adding tables to join...
Here's a fun one. test=# CREATE TABLE table_s1 (i int); test=# CREATE TABLE table_s2 (j int); test=# EXPLAIN ANALYZE SELECT table_s1.i, table_s2.j FROM table_s1 AS s1, table_s2 AS s2 WHERE s1.i = s2.j; NOTICE: adding missing FROM-clause entry for table table_s1 NOTICE: adding missing FROM-clause entry for table table_s2 QUERY PLAN - Nested Loop (cost=0.00..0.03 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=1) - Nested Loop (cost=0.00..0.02 rows=1 width=4) (actual time=0.02..0.02 rows=0 loops=1) - Nested Loop (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=1) Join Filter: (outer.i = inner.j) - Seq Scan on table_s1 s1 (cost=0.00..0.00 rows=1 width=4) (actual time=0.01..0.01 rows=0 loops=1) - Seq Scan on table_s2 s2 (cost=0.00..0.00 rows=1 width=4) (never executed) - Seq Scan on table_s1 (cost=0.00..0.00 rows=1 width=4) (never executed) - Seq Scan on table_s2 (cost=0.00..0.00 rows=1 width=4) (never executed) Total runtime: 0.20 msec (9 rows) If there's real data in the tables, this joins the tables on itself and execution times explode, naturally. I don't know if the spec says this is the correct behavior or not, but I have a feeling its not and this is a bug. -sc Well, the query is technically invalid I think. I'm not 100% sure what the definition of invalid is... If I turn off add_missing_from, it still fails. ex: test=# EXPLAIN ANALYZE SELECT table_s1.i, table_s2.j FROM table_s1 AS s1, table_s2 AS s2 WHERE s1.i = s2.j; ERROR: missing FROM-clause entry for table table_s1 I know the docs say, When an alias is provided, it completely hides the actual name of the table or function; for example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item as f not foo. It just struck me as a strange behavior. The above is the expected behavior for 7.3 and earlier I believe (note the notices). IIRC, 7.4 has a guc option to turn the adding of missing from clauses off. Correct. This is another IRC special, so I don't really care one way or another, but it was certainly aggravating to track it down so I figured I'd report it as the behavior seems a tad bogus in some cases, though I do appreciate the value of being able to join a table on itself it just seems as though users stumble across this more often than they join a table with itself. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] PostgreSQL 7.3.3 with pgcrypto on FreeBSD 5.1
Perhaps the problem is that Marko didn't fix the crypt() code in
the same way?
Ah, I think that's _very_ likely the case here... -sc
I updated to openssl 0.9.7b on my HPUX machine, and still do not see any
failure in
regression=# SELECT crypt('lalalal',gen_salt('md5'));
crypt
$1$.yzzlyzz$W8vpUQ3Nonx20vchlBQye/
(1 row)
So it seems that OpenSSL version is not the issue --- or at least not
the whole story. Maybe there is some platform-dependency here too.
Possible, but I'm a skeptical of that. FreeBSD's openssl code
_should_ be stock (unless someone bungled the import) with the
exception of not including Win32 or other non-FreeBSD related bits.
crypt() works when salted with only 2 chars, however it shouldn't core
with more than that... I ran this test case through gdb a few times
and couldn't come up with anything worth while, though I'm pretty sure
that's because the object files were optimized -O and garbled.
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] PostgreSQL 7.3.3 with pgcrypto on FreeBSD 5.1
Possible, but I'm a skeptical of that. FreeBSD's openssl code _should_ be stock (unless someone bungled the import) with the exception of not including Win32 or other non-FreeBSD related bits. crypt() works when salted with only 2 chars, however it shouldn't core with more than that... But maybe the problem is in crypt(3) and not in OpenSSL at all? I'm quite willing to believe that neither Linux nor HPUX share crypt() source with FreeBSD... Using the builtin crypt() solved this problem for the user though. I wonder if the configure script isn't correctly detecting using the builtin crypt() as opposed to the openssl version... or visa versa. Try applying the attached patch and seeing if that lets you reproduce the crash. random may still need to be silly on HPUX, I don't know if it has a /dev/random. -sc -- Sean Chittenden --- contrib/pgcrypto/Makefile.orig Thu May 8 16:09:28 2003 +++ contrib/pgcrypto/Makefile Thu May 8 16:12:47 2003 @@ -7,7 +7,7 @@ include $(top_builddir)/src/Makefile.global # either 'builtin', 'mhash', 'openssl' -cryptolib = builtin +cryptolib = openssl # either 'builtin', 'system' cryptsrc = builtin @@ -21,7 +21,7 @@ # This works ofcouse only with cryptolib = openssl # # 'silly'- use libc random() - very weak -random = silly +random = dev random_dev = \/dev/urandom\ ## ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] PostgreSQL 7.3.3 with pgcrypto on FreeBSD 5.1
Try applying the attached patch and seeing if that lets you reproduce the crash. Hmm. I get *a* crash, maybe not the same one. But there's no autoconfiguration of this setting in pgcrypto/Makefile, so how would anyone be using anything but builtin crypt()? I patch contrib/pgcrypto/Makefile in FreeBSD's postgresql-devel port to turn on the use of the OpenSSL where applicable. FreeBSD and OpenBSD have hardware offloading support for crypto routines, but offloading (the speedup of crypto handling is measured in orders of magnitude) only takes place when OpenSSL's lib handle the crypto, hence the patch. I'm torn as to what to do, I'll probably conditionalize that part of the patch behind an #ifdef unless a patch comes through shortly. My bet is Pg is smashing OpenSSL's stack when passing more than 2 chars as a salt. When passed the right arguments, pgcrypto works correctly, but it falls on its face when OpenSSL is handed out of bounds data. More than anything, I think I should submit a quick doc patch to the README so that people use a DES salt when using a DES based crypt() routine and an md5 salt when calling an md5 crypt routine, which is where the bug submitter triggered this boundary condition. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] PostgreSQL 7.3.3 with pgcrypto on FreeBSD 5.1
testdb=# SELECT crypt('lalalal',gen_salt('md5'));
server closed the connection unexpectedly
FWIW, I can confirm this, but I don't think it's a FreeBSD specific
problem given that the backend dies inside of an OpenSSL routine.
Works fine here:
regression=# SELECT crypt('lalalal',gen_salt('md5'));
crypt
$1$2tnDkstz$e2vTadSh2zGH4yh51Ocbu0
(1 row)
here being Red Hat 8.0 with these OpenSSL packages:
openssl095a-0.9.5a-16
openssl-devel-0.9.6b-29
openssl-perl-0.9.6b-29
openssl-0.9.6b-29
openssl096-0.9.6-11
What OpenSSL release are you using?
0.9.7a, which lends weight to the theory of shifting internals...
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] PostgreSQL 7.3.3 with pgcrypto on FreeBSD 5.1
FWIW, I can confirm this, but I don't think it's a FreeBSD specific problem given that the backend dies inside of an OpenSSL routine. #0 0x2864ae9c in EVP_DigestUpdate () from /usr/lib/libcrypto.so.3 #1 0x28576a90 in px_find_cipher () from /usr/local/lib/postgresql/pgcrypto.so #2 0x2857c584 in px_crypt_md5 () from /usr/local/lib/postgresql/pgcrypto.so By chance I noticed this entry in the CVS logs: 2002-11-14 21:54 momjian * contrib/pgcrypto/openssl.c: OpenSSL 0.9.6g in Debian/unstable stopped working with pgcrypto. This is pgcrypto bug as it assumed too much about inner workings of OpenSSL. Following patch stops pgcrypto using EVP* functions for ciphers and lets it manage ciphers itself. This patch supports Blowfish, DES and CAST5 algorithms. Marko Kreen Perhaps the problem is that Marko didn't fix the crypt() code in the same way? Ah, I think that's _very_ likely the case here... -sc -- Sean Chittenden ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] PostgreSQL 7.3.3 with pgcrypto on FreeBSD 5.1
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
testdb=# SELECT crypt('lalalal',gen_salt('md5'));
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
pglog
LOG: ReadRecord: record with zero length at 0/18BDEA8
LOG: redo is not required
LOG: database system is ready
LOG: server process (pid 21460) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing shared memory and semaphores
LOG: database system was interrupted at 2003-07-21 11:44:47 EEST
LOG: checkpoint record is at 0/18BDEA8
LOG: redo record is at 0/18BDEA8; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 996; next oid: 98896
LOG: database system was not properly shut down; automatic recovery in progress
FATAL: The database system is starting up
/pglog
FWIW, I can confirm this, but I don't think it's a FreeBSD specific
problem given that the backend dies inside of an OpenSSL routine.
#0 0x2864ae9c in EVP_DigestUpdate () from /usr/lib/libcrypto.so.3
#1 0x28576a90 in px_find_cipher () from /usr/local/lib/postgresql/pgcrypto.so
#2 0x2857c584 in px_crypt_md5 () from /usr/local/lib/postgresql/pgcrypto.so
#3 0x2857efa4 in px_gen_salt () from /usr/local/lib/postgresql/pgcrypto.so
#4 0x2857ee1f in px_crypt () from /usr/local/lib/postgresql/pgcrypto.so
#5 0x2857d2be in pg_crypt () from /usr/local/lib/postgresql/pgcrypto.so
#6 0x0811acb6 in ExecMakeFunctionResult ()
#7 0x0811b7db in ExecEvalExpr ()
#8 0x0811e794 in ExecProject ()
#9 0x0811cb7a in ExecProject ()
#10 0x081282a5 in ExecResult ()
#11 0x0811a0e5 in ExecProcNode ()
#12 0x0811952c in ExecEndPlan ()
#13 0x081188b8 in ExecutorRun ()
#14 0x0819b703 in PortalRun ()
#15 0x0819b53e in PortalRun ()
#16 0x08199b21 in authdie ()
#17 0x081982fd in PostgresMain ()
#18 0x0816de64 in PostmasterMain ()
#19 0x0816d628 in PostmasterMain ()
#20 0x0816bcc6 in PostmasterMain ()
#21 0x0816b5c7 in PostmasterMain ()
#22 0x0813588b in main ()
#23 0x0806c802 in _start ()
Both crypt() and gen_salt() work independently of each other:
test=# SELECT gen_salt('md5');
gen_salt
-
$1$nouzuI/B
(1 row)
test=# SELECT crypt('foo','ba');
crypt
---
ba4TuD1iozTxw
(1 row)
:-/ crypt() does die with the salt, '$1$' Not sure why either...
That said, the right work around is to _not_ use gen_salt('md5') when
passing data in to crypt(), instead, use gen_salt('des'). The readme
says its not recommended, but in the same breath, it should recommend
that gen_salt('des') is as weak as crypt() and gen_salt('des')
produces the correct salt for use with crypt().
What's interesting, however, is that I can no longer ctrl+c to get out
of psql after the backend dies. To kill psql, I have to suspend it,
then kill.
test=# SELECT crypt('lalal',gen_salt('md5'));
The connection to the server was lost. Attempting reset: Failed.
connection pointer is NULL
!
!
!
!
!
!
!
[1] + 15252 Suspended psql test pgsql
1:08pm [EMAIL PROTECTED]:databases/postgresql-devel kill %1
1:08pm [EMAIL PROTECTED]:databases/postgresql-devel
[1]Terminatedpsql test pgsql
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] pg_class_aclcheck: relation [oid] not found...
CREATE FUNCTION s.f()
RETURNS BIGINT
EXTERNAL SECURITY DEFINER
AS '
BEGIN
EXECUTE ''CREATE LOCAL TEMP TABLE t (
a TEXT NOT NULL,
b TEXT
) WITHOUT OIDS ON COMMIT DROP;'';
EXECUTE ''CREATE UNIQUE INDEX t_key_udx ON t(a);'';
INSERT INTO t (a, b) VALUES (''foo''::TEXT, ''bar''::TEXT);
This is not going to work more than once, because the INSERT caches
a plan that refers to the first-time-through temp table.
You could put the INSERT into an EXECUTE as well. Or use a different
PL language that doesn't cache plans.
Hrm... this limitation makes temporary tables that drop on commit +
pl/pgsql unusable beyond the 1st transaction. Is there a mechanism to
test to see if a relation in a plan is a temporary table? It seems as
though in pl_exec.c that around 1926 it'd be possible to add a test to
see if the plan uses temporary tables and add a new member to struct
expr telling exec_stmt_execsql to free the plan around line 2016 when
its cleaning up after itself.
For the archives, there are two workarounds for this:
1) Don't use ON COMMIT DROP, instead use ON COMMIT DELETE ROWS. This
preserves the relation thus all cached plans are still valid.
Before creating the temporary table, however, you have to test for
its existence. This came out at about 0.4ms.
2) Use a FOR-IN-EXECUTE statement. It's slower, but works (~1.2ms
instead of 0.2ms. On thousands of inserts a second and it makes a
big difference).
-sc
PS For the sake of completeness, returning the value from CURRVAL()
takes ~0.3ms from pl/pgsql and only ~0.14ms outside of pl/pgsql.
The difference is the runtime cost of using pl/pgsql which is
pretty reasonable given pl/pgsql walks an AST.
--
Sean Chittenden
---(end of broadcast)---
TIP 3: 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] segv's on CREATE INDEX with recent HEAD...
Don't know whether or not this is preferred here or on hackers, but, I just updated my development database to a snapshot from today and have been getting the following backtrace when importing a dump from before earlier today. It looks as though something's tromping on variable. pid 68526 (postgres), uid 70: exited on signal 11 (core dumped) #0 0x0810c29d in ExecEvalVar (variable=0x2, econtext=0x83cc0a0, isNull=0xbfbfed70 ) at execQual.c:403 #1 0x0810aa67 in ExecEvalExpr (expression=0x83cc208, econtext=0x83cc0a0, isNull=0xbfbfed70 , isDone=0xbfbfecbc) at execQual.c:1985 #2 0x08109e60 in ExecMakeFunctionResult (fcache=0x83cc100, econtext=0x83cc0a0, isNull=0xbfbfee2b , isDone=0x0) at execQual.c:641 #3 0x0810ab46 in ExecEvalExpr (expression=0x83cc100, econtext=0x83cc0a0, isNull=0xbfbfee2b , isDone=0x83cc0a0) at execQual.c:2012 #4 0x0810bcd9 in ExecEvalExprSwitchContext (expression=0x8316b70, econtext=0x83cc0a0, isNull=0x8316b70 \b, isDone=0x8316b70) at execQual.c:2129 #5 0x080a5a0c in FormIndexDatum (indexInfo=0x83f9c18, heapTuple=0x8351938, heapDescriptor=0x8316b70, estate=0x83cc018, datum=0xbfbfeec0, nullv=0xbfbfeea0 \b)/\b) at index.c:955 #6 0x080a6529 in IndexBuildHeapScan (heapRelation=0xbfbfeec0, indexRelation=0x8416468, indexInfo=0x83f9c18, callback=0x8087eb0 btbuildCallback, callback_state=0xbfbfef90) at index.c:1625 #7 0x08086e1b in btbuild (fcinfo=0x8316b70) at nbtree.c:129 #8 0x081ffd60 in OidFunctionCall3 (functionId=137456496, arg1=137456496, arg2=137456496, arg3=137456496) at fmgr.c:1277 #9 0x080a62a9 in index_build (heapRelation=0x8316b70, indexRelation=0x2, indexInfo=0x8316b70) at index.c:1398 #10 0x080a530e in index_create (heapRelationId=33576, indexRelationName=0x834c350 t_udx, indexInfo=0x83f9c18, accessMethodObjectId=137456496, classObjectId=0x83f9ca0, primary=0 '\0', isconstraint=0 '\0', allow_system_table_mods=0 '\0') at index.c:750 #11 0x080e332d in DefineIndex (heapRelation=0x83f9c18, indexRelationName=0x834c350 t_udx, accessMethodName=0x82165c6 btree, attributeList=0x834c510, unique=1 '\001', primary=0 '\0', isconstraint=0 '\0', predicate=0x0, rangetable=0x8328) at indexcmds.c:238 #12 0x0818709e in ProcessUtility (parsetree=0x834c528, dest=0x0, completionTag=0x834c528 \002) at utility.c:736 #13 0x08185299 in PortalRunUtility (portal=0x83a0018, query=0x0, dest=0x834c568, completionTag=0x0) at pquery.c:737 #14 0x08185513 in PortalRunMulti (portal=0x83a0018, dest=0x834c568, altdest=0x834c568, completionTag=0xbfbff3a0 ) at pquery.c:801 #15 0x08184df4 in PortalRun (portal=0x83a0018, count=2147483647, dest=0x834c568, altdest=0x8316b70, completionTag=0xbfbff3a0 ) at pquery.c:465 #16 0x081834b3 in exec_simple_query (query_string=0x834c030 CREATE UNIQUE INDEX t_udx ON s.t (LOWER(a));) at postgres.c:853 #17 0x08181eea in PostgresMain (argc=4, argv=0x82db9d8, username=0x82db9a8 nxadba) at postgres.c:2730 #18 0x0815948d in BackendFork (port=0x82e6000) at postmaster.c:2473 #19 0x08158e54 in BackendStartup (port=0x82e6000) at postmaster.c:2095 #20 0x081575ec in ServerLoop () at postmaster.c:1049 #21 0x08156e68 in PostmasterMain (argc=1, argv=0x82d9030) at postmaster.c:829 #22 0x08123b63 in main (argc=1, argv=0xbfbffcb8) at main.c:211 #23 0x0806c0a5 in _start () It looks like the change that went in on 1.128 is the culprit simply because it's the only change in this function since the last stable snapshot. I can consitently reproduce this with one of my schemas if someone needs more debugging information, point me in the right direction for where you'd like me to attach gdb. I haven't been able to get a use case bottled up, but with a backtrace, I'm not that worried. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] segv's on CREATE INDEX with recent HEAD...
Don't know whether or not this is preferred here or on hackers, but, I just updated my development database to a snapshot from today and have been getting the following backtrace when importing a dump from before earlier today. It looks as though something's tromping on variable. Argh! Can't believe that snuck through my testing. Fix committed (and regression test case added...) Thanks, this fixed loading my dump! Looks like the protocol stuff is much more solid now too, SELECT * FROM pg_class doesn't return garbage any more. :) ... looks like everything is nominal in HEAD again. *cheers* Thanks Tom! -sc PS What's the dilly with the server(s)? I haven't gotten a commit message all day, but CVSup and anoncvs picked up the change. -- Sean Chittenden ---(end of broadcast)--- TIP 3: 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] segv's on CREATE INDEX with recent HEAD...
Sean Chittenden [EMAIL PROTECTED] writes:
PS What's the dilly with the server(s)? I haven't gotten a commit
message all day, but CVSup and anoncvs picked up the change.
Well, the CVS master server has been pretty much 100% through this
whole rigmarole (kudos to Marc for keeping up what *had* to be up ;-))
The anoncvs mirror was busted for a couple days but has been okay since
then, AFAIK. The pgsql-committers list is still, uh, AWOL. I get the
impression that the listserv doesn't think the CVS server is authorized
to send mail to the list, and that Marc has been manually approving
batches of mail every couple days but hasn't got round to locating the
real problem yet. I trust he'll get it fixed before much longer though
...
Hardware problems are the worst. Best of Marc. Well, normally you're
good about committing things in batches, but I'm not sure if I'm
building against a complete/up to date version, but here is some
regression.diff fun to digest. Looks like expected/misc.out and
./expected/sanity_check.out need to be updated and there's something
wrong with triggers/constraints:
*** ./expected/constraints.out Wed May 28 23:26:42 2003
--- ./results/constraints.out Wed May 28 23:28:15 2003
***
*** 191,210
CREATE TABLE INSERT_CHILD (cx INT default 42,
cy INT CHECK (cy x))
INHERITS (INSERT_TBL);
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
! ERROR: ExecInsert: rejected due to CHECK constraint insert_child_cy on
insert_child
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
! ERROR: ExecInsert: rejected due to CHECK constraint $1 on insert_child
INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
! ERROR: ExecInsert: rejected due to CHECK constraint insert_con on insert_child
SELECT * FROM INSERT_CHILD;
! x | y| z | cx | cy
! ---++++
! 7 | -NULL- | -7 | 42 | 11
! (1 row)
!
DROP TABLE INSERT_CHILD;
--
-- Check constraints on INSERT INTO
--
--- 191,209
CREATE TABLE INSERT_CHILD (cx INT default 42,
cy INT CHECK (cy x))
INHERITS (INSERT_TBL);
+ ERROR: RelationClearRelation: relation 135137 deleted while still in use
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
+ ERROR: Relation insert_child does not exist
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
! ERROR: Relation insert_child does not exist
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
! ERROR: Relation insert_child does not exist
INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
! ERROR: Relation insert_child does not exist
SELECT * FROM INSERT_CHILD;
! ERROR: Relation insert_child does not exist
DROP TABLE INSERT_CHILD;
+ ERROR: table insert_child does not exist
--
-- Check constraints on INSERT INTO
--
==
*** ./expected/triggers.out Sat Nov 23 10:13:22 2002
--- ./results/triggers.out Wed May 28 23:28:15 2003
***
*** 87,92
--- 87,93
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
DROP TABLE pkeys;
+ ERROR: Relation 120339 does not exist
DROP TABLE fkeys;
DROP TABLE fkeys2;
-- -- I've disabled the funny_dup17 test because the new semantics
==
*** ./expected/sanity_check.out Wed May 28 09:04:02 2003
--- ./results/sanity_check.out Wed May 28 23:28:29 2003
***
*** 58,68
pg_statistic| t
pg_trigger | t
pg_type | t
road| t
shighway| t
tenk1 | t
tenk2 | t
! (52 rows)
--
-- another sanity check: every system catalog that has OIDs should have
--- 58,69
pg_statistic| t
pg_trigger | t
pg_type | t
+ pkeys | t
road| t
shighway| t
tenk1 | t
tenk2 | t
! (53 rows)
--
-- another sanity check: every system catalog that has OIDs should have
==
*** ./expected/misc.out Wed May 28 23:26:42 2003
--- ./results/misc.out Wed May 28 23:28:39 2003
***
*** 633,638
--- 633,639
onek2
path_tbl
person
+ pkeys
point_tbl
polygon_tbl
ramp
***
*** 657,663
toyemp
varchar_tbl
xacttest
! (93 rows)
--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS
equip_name;
SELECT hobbies_by_name('basketball');
--- 658,664
toyemp
varchar_tbl
xacttest
! (94 rows)
--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS
equip_name;
SELECT hobbies_by_name('basketball
[BUGS] Regression testing failure: float8...
Howdy. Just a quick FYI, but I was running some regression tests on
HEAD and noticed that float8 is failing on FreeBSD 5. FreeBSD 4 seems
to be fine. Any suggestions on what I can poke at or where I should
look regarding this? -sc
*** ./expected/float8-small-is-zero.out Tue Oct 22 13:01:15 2002
--- ./results/float8.outSun Feb 16 15:25:48 2003
***
*** 274,280
--- 274,282
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
ERROR: Input '-10e400' is out of range for float8
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+ ERROR: Input '10e-400' is out of range for float8
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+ ERROR: Input '-10e-400' is out of range for float8
-- maintain external table consistency across platforms
-- delete all values and reinsert well-behaved ones
DELETE FROM FLOAT8_TBL;
==
--
Sean Chittenden
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] readline usage in psql...
A dump from using psql this is one of those helpful, I have no clue what's causing this, but here it is for the record or someone who knows more than I. I don't know if it's a readline() problem or something in PQclientEncoding(), and neither of which I have any experience with. If I figure out what's causing it beyond pushing Ctrl+something with a halfway completed connect command (\c datab[ctrl+???]), I'll post a reply w/ more info. -sc #0 0x281cbaeb in sigprocmask () from /usr/lib/libc.so.5 #1 0x280c5a2a in _rl_savestring () from /usr/lib/libreadline.so.4 #2 signal handler called #3 0x28168803 in read () from /usr/lib/libc.so.5 #4 0x280c1c79 in rl_read_key () from /usr/lib/libreadline.so.4 #5 0x280d407f in readline_internal_char () from /usr/lib/libreadline.so.4 #6 0x280d4235 in readline_internal_char () from /usr/lib/libreadline.so.4 #7 0x280d426e in readline_internal_char () from /usr/lib/libreadline.so.4 #8 0x280d3dad in readline () from /usr/lib/libreadline.so.4 #9 0x0804ede4 in PQclientEncoding () at fe-connect.c:2725 #10 0x0804f697 in PQclientEncoding () at fe-connect.c:2725 #11 0x08051653 in PQclientEncoding () at fe-connect.c:2725 #12 0x0804a485 in PQclientEncoding () at fe-connect.c:2725 -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] dropping user doesn't erase his rights.
Difficult to do, when those privileges might be recorded in databases you're not even connected to at the time of the drop. I belive it would be pretty difficult, but leaving it just like that creates ssecurity breach (imagine someone droping user, beliving that everytinh is o.k.), than someone else creates different user but with keeping unused sysid (this might be the case with system users and keeping system user-id with database user-id the same) - which happens to be not unused. i'm not sure if i'm clear about it. Wouldn't an ON DELETE trigger on the system catalogs work? I'd think it would be possible to select the tables and groups that a user had privs to and iterate through each calling REVOKE. -sc -- Sean Chittenden msg05642/pgp0.pgp Description: PGP signature
Re: [NOVICE] [BUGS] Postgres storing time in strange manner
Out of curiosity: why does -ffast-math break the datetime rounding code? What code bits is this for? Is there a place where -fno-fast-math could be used as a CC option if the CC is gcc? After looking through gcc, using -O and -ffast-math will create broken code, but -O2 -ffast-math _should_ be okay. If it's not, then -O2 -fno-fast-math is likely the correct work around for GCC. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] SET autocommit begins transaction?
Here's the simplest way of reproducing this:
ways# psql -q template1 pgsql
template1=# SET AUTOCOMMIT TO OFF;
template1=# DROP DATABASE my_db_name;
ERROR: DROP DATABASE: may not be called in a transaction block
2002-09-18 11:05:19 LOG: query: select getdatabaseencoding()
2002-09-18 11:05:19 LOG: query: SELECT usesuper FROM pg_catalog.pg_user WHERE usename
= 'pgsql'
2002-09-18 11:05:30 LOG: query: SET AUTOCOMMIT TO OFF;
2002-09-18 11:05:38 LOG: query: DROP DATABASE my_db_name;
2002-09-18 11:05:38 ERROR: DROP DATABASE: may not be called in a transaction block
2002-09-18 11:05:38 LOG: statement: DROP DATABASE my_db_name;
Does turnning autocommit off enter you into a transaction? Am I
smoking something or does that seems broken? It looks like this was a
conscious and deliberate decission based off of the comments in
src/backend/access/transam/xact.c around lines 1248-1293. In my
reading of the code, I might be confusing the GUC autocommit with the
SET autocommit, but ... this just doesn't seem right because it
forces my application code to do the following:
db = MyOrg::Db.connect('init')
db.rollback
db.do('DROP DATABASE my_db_name')
which reads really awkwardly and warrents a comment explaining why I'm
rolling back immediately after I connect. Thoughts/comments? -sc
--
Sean Chittenden
msg04859/pgp0.pgp
Description: PGP signature
Re: [NOVICE] [BUGS] Postgres storing time in strange manner
After looking through gcc, using -O and -ffast-math will create broken
code, but -O2 -ffast-math _should_ be okay.
At least in the gcc shipped with Red Hat 7.2, it doesn't seem to matter:
you get the wrong answer regardless of -O level. Here's the test case
I used:
[tgl@rh1 tgl]$ cat bug.c
#include stdio.h
double d18000 = 18000.0;
main() {
int d = d18000 / 3600;
printf(18000.0 / 3600 = %d\n, d);
return 0;
}
[tgl@rh1 tgl]$ gcc bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 5-- right
[tgl@rh1 tgl]$ gcc -O2 -ffast-math bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 4-- wrong
-- I get 4 if -ffast-math, -O doesn't affect it
[tgl@rh1 tgl]$ gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)
Heh, chalk this one up as another Linux-ism then 'cause it's not
present in FreeBSD -stable or -current. This actually makes me feel
better about setting an option in the -devel port for turning on
compilation with -O3. -sc
stable$ gcc -v
Using builtin specs.
gcc version 2.95.4 20020320 [FreeBSD]
current$ gcc -v
Using built-in specs.
Configured with: FreeBSD/i386 system compiler
Thread model: posix
gcc version 3.2.1 [FreeBSD] 20020901 (prerelease)
--
Sean Chittenden
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] SET autocommit begins transaction?
Well there is discussion on whether a SET with autocommit off should start a transaction if it is the first command. Right now it does, and clearly you have a case where it acts strangely. Problem is that through various DB APIs such as DBI, you can't garuntee to the user doing development that that it's the 1st command that they're performing. OK, but why does my suggestion not work: SET autocommit = ON; COMMIT; Hrm... if I changed the DBI layer for Ruby to have: db['AutoCommit'] = true use 'SET autocommit = ON; COMMIT;' I think I'd be breaking tons of applications where they wouldn't be expecting the commit. Yep, we don't like special cases and that is why we avoided it. Just explaining the special case causes all sorts of confusion, as you have seen from my emails. Yup, exceptions aren't elegant, but since there's only one way of SET'ting variables and this one is very key to transactions, I don't know of another way than possibly creating a parallel command to SET that'd avoid this rollback/commit silliness... but that seems like a step backwards and is why I'd think an exception would be good. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SET autocommit begins transaction?
Problem is that through various DB APIs such as DBI, you can't
garuntee to the user doing development that that it's the 1st command
that they're performing.
OK, but why does my suggestion not work:
SET autocommit = ON;
COMMIT;
Hrm... if I changed the DBI layer for Ruby to have:
db['AutoCommit'] = true
use 'SET autocommit = ON; COMMIT;' I think I'd be breaking tons of
applications where they wouldn't be expecting the commit.
Actually, the current approved way is:
BEGIN; SET autocommit = ON; COMMIT;
db.transaction do |dbh|
db.do('DELETE FROM tbl WHERE id = 5')
db['AutoCommit'] = true
end
Because there wasn't a commit given, that shouldn't actually delete
the rows found, but by tossing that AutoCommit in there, it should and
will generate a nifty warning if AutoCommit sends the above
BEGIN/SET/COMMIT. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] SET autocommit begins transaction?
db.transaction do |dbh|
db.do('DELETE FROM tbl WHERE id = 5')
db['AutoCommit'] = true
end
Because there wasn't a commit given, that shouldn't actually
delete the rows found, but by tossing that AutoCommit in there, it
should and will generate a nifty warning if AutoCommit sends the
above BEGIN/SET/COMMIT. -sc
You can't be setting autocommit willy-nilly. What I was going to
suggest is that we allow 'SET autocommit' only at the start of a
transaction, and then have it take effect immediately. If you try
autocommit when a transaction is already in progress from a previous
statement, we throw an error.
But that'd result in at least two transactions per connection because
in my database class wrapper I turn autocommit off. Under any kind of
load or performance situations, that's pretty unacceptable. Granted
there's nothing that would need to be flushed to disk (hopefully), it
still strikes me that there would have to be some locking involved and
that would degrade the performance of the entire system.
If you're throwing an error in the middle of a transaction just
because of 'SET autocommit', aren't you already making an exception
and one that degrades the performance of the entire system as a
result?
I just saw Tom's post and it seems like something has to give
someplace... I'm not a fan of the idea of creating the special case,
don't get me wrong, but is there a reasonable alternative? -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] SET autocommit begins transaction?
... I think if we special case autocommit we have to force it to start a transaction. Be aware that SET AUTOCOMMIT does *not* start a transaction in other systems (at least in Ingres, where I first ran into the feature). This case is illustrating a general issue with trying to bracket variables within transactions; the special case is that if a transaction is not open then the change should be global across transactions. Any counterexamples would argue for two separate behaviors, not for shoehorning everything into one, uh, shoe. I am fine with special casing autocommit. Is that what you are suggesting? I think he means: Ex: SET autocommit TO off; SHOW autocommit; ROLLBACK; # warning about being outside of a transaction BEGIN; SET autocommit TO on; SHOW autocommit;# shows on ROLLBACK; SHOW autocommit;# shows off Only have the SET's in a transaction/rollback-able if they're made inside of a transaction, otherwise leave them as atomic changes. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] SET autocommit begins transaction?
I am fine with special casing autocommit. Is that what you are suggesting? I think he means: Ex: SET autocommit TO off; SHOW autocommit; ROLLBACK; # warning about being outside of a transaction BEGIN; SET autocommit TO on; SHOW autocommit;# shows on ROLLBACK; SHOW autocommit;# shows off Only have the SET's in a transaction/rollback-able if they're made inside of a transaction, otherwise leave them as atomic changes. -sc But it seems so illogical that SET doesn't start a transaction, but if it is in a transaction, it is rolled back, and this doesn't help our statement_timeout example except to require that they do BEGIN to start the transaction even when autocommit is off. Really? To me that makes perfect sense. Logic: *) Only BEGIN starts a transaction *) Database or session tunables are adjusted with SET *) Only things that happen inside of a transaction are rollback-able *) SET operations that happen outside of a transaction are atomic changes that aren't subject to being rolled back What about that doesn't make sense? Having SET begin a transaction seems like a gross violation of POLS and likely to contradict the spec and cause problems with many applications. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] SET autocommit begins transaction?
Of course, the reason they're rollbackable is: begin; create schema newschema; set search_path = newschema; rollback; create table junk; -- DOH! And: set statement_timeout = 20; query_with_error; set statement_timeout = 0; COMMIT; That will have to change in autocommit off to: BEGIN; SET statement_timeout = 20; query_with_error; SET statement_timeout = 0; COMMIT; I assume that BEGIN does start a transaction. With no BEGIN above, the big problem is that it will work most of the time, but when/if the query fails, they will find out they forgot the BEGIN. Wouldn't it roll back to 0 though because the SET statement_timeout TO 20 was inside of a transaction (assuming the value was 0 before the transaction began)? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] SET autocommit begins transaction?
Yes, with the BEGIN, it will roll back. With autocommit off, this: set statement_timeout = 20; query_with_error; set statement_timeout = 0; COMMIT; will not roll back to 0. It will be 20. But that's the correct/expected behavior, is it not? That's what I'd expect at least. I'd think it's a gotcha for those that aren't good about explicitly calling BEGIN, but most libraries should do that for you, ruby-dbi does and used to be overly zealous about that actually (I just fixed that last night as a matter of fact). -sc -- Sean Chittenden ---(end of broadcast)--- TIP 3: 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] ecpg hackery to get ecpg to compile from FreeBSD ports...
Different bogon that I ran across: 2:35pm sean@mat:ecpg/lib gmake cc -O -pipe -g -O -I/usr/local/include -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../../src/interfaces/ecpg/include -I../../../../src/interfaces/libpq -I../../../../src/include -I/usr/local/include -I/usr/local/include -c -o connect.o connect.c Apparently you somehow put -I/usr/local/include into CFLAGS. Don't do that. Easier said than done... /usr/local/include is propagated throughout the build to catch local package installations (getopt/readline)... I'll see if I can't figure out the correct fix for this though. Thanks for the info though. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] ecpg hackery to get ecpg to compile from FreeBSD ports...
Different bogon that I ran across: 2:35pm sean@mat:ecpg/lib gmake cc -O -pipe -g -O -I/usr/local/include -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../../src/interfaces/ecpg/include -I../../../../src/interfaces/libpq -I../../../../src/include -I/usr/local/include -I/usr/local/include -c -o connect.o connect.c Apparently you somehow put -I/usr/local/include into CFLAGS. Don't do that. Easier said than done... /usr/local/include is propagated throughout the build to catch local package installations (getopt/readline)... I'll see if I can't figure out the correct fix for this though. Thanks for the info though. -sc This is anecdotal for the archives, but the problem was that when building with krb5, I had (wrongly?) appended krb5-config's --cflags output to the CFLAGS for the build... which, nine times out of ten, was exactly the same as what was used with the --with-includes. If they're different, the person's horked, but that should be a minority of the time. Anyway, just an FYI. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 3: 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] pg_config Makefile bogon...
Attached is a quick patch that fixes making pg_config. When making the port on FreeBSD, it uses commas in the LDFLAGS section of the configure args and sed doesn't grok that so well. Patch just changes the delimiters. -sc Now: sed -e 's|bindir|/usr/local/bin|g' \ -e 's|includedir|/usr/local/include|g' \ -e 's|includedir_server|/usr/local/include/postgresql/server|g' \ -e 's|libdir|/usr/local/lib|g' \ -e 's|pkglibdir|/usr/local/lib/postgresql|g' \ -e s|configure|'--enable-locale' '--enable-syslog' '--with-CXX' '--docdir=/usr/local/share/doc' '--with-libdir=/usr/local/lib' '--with-includes=/usr/local/include' '--with-openssl=/usr' '--with-krb5=/usr/local' '--prefix=/usr/local' 'i386-portbld-freebsd4.7' 'CC=cc' 'CFLAGS=-O -pipe -g -O -I/usr/local/include' 'LDFLAGS= -L/usr/local/lib -lgnugetopt -L/usr/local/lib -Wl,-rpath -Wl,/usr/local/lib -lkrb5 -lk5crypto -lcom_err' 'build_alias=i386-portbld-freebsd4.7' 'host_alias=i386-portbld-freebsd4.7' 'target_alias=i386-portbld-freebsd4.7'|g \ -e 's|version|7.3b1|g' \ pg_config.sh pg_config chmod a+x pg_config Was: sed -e 's,bindir,/usr/local/bin,g' \ -e 's,includedir,/usr/local/include,g' \ -e 's,includedir_server,/usr/local/include/postgresql/server,g' \ -e 's,libdir,/usr/local/lib,g' \ -e 's,pkglibdir,/usr/local/lib/postgresql,g' \ -e s,configure,'--enable-locale' '--enable-syslog' '--with-CXX' '--docdir=/usr/local/share/doc' '--with-libdir=/usr/local/lib' '--with-includes=/usr/local/include' '--with-openssl=/usr' '--with-krb5=/usr/local' '--prefix=/usr/local' 'i386-portbld-freebsd4.7' 'CC=cc' 'CFLAGS=-O -pipe -g -O -I/usr/local/include' 'LDFLAGS= -L/usr/local/lib -lgnugetopt -L/usr/local/lib -Wl,-rpath -Wl,/usr/local/lib -lkrb5 -lk5crypto -lcom_err' 'build_alias=i386-portbld-freebsd4.7' 'host_alias=i386-portbld-freebsd4.7' 'target_alias=i386-portbld-freebsd4.7',g \ -e 's,version,7.3b1,g' \ pg_config.sh pg_config sed: 1: s,configure,'--enable ...: bad flag in substitute command: '-' gmake[4]: *** [pg_config] Error 1 gmake[4]: *** Deleting file `pg_config' -- Sean Chittenden Index: Makefile === RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_config/Makefile,v retrieving revision 1.4 diff -u -r1.4 Makefile --- Makefile2002/03/29 17:32:55 1.4 +++ Makefile2002/09/08 21:22:39 -7,13 +7,13 all: pg_config pg_config: pg_config.sh $(top_builddir)/src/Makefile.global Makefile - sed -e 's,bindir,$(bindir),g' \ - -e 's,includedir,$(includedir),g' \ - -e 's,includedir_server,$(includedir_server),g' \ - -e 's,libdir,$(libdir),g' \ - -e 's,pkglibdir,$(pkglibdir),g' \ - -e s,configure,$(configure_args),g \ - -e 's,version,$(VERSION),g' \ + sed -e 's|bindir|$(bindir)|g' \ + -e 's|includedir|$(includedir)|g' \ + -e 's|includedir_server|$(includedir_server)|g' \ + -e 's|libdir|$(libdir)|g' \ + -e 's|pkglibdir|$(pkglibdir)|g' \ + -e s|configure|$(configure_args)|g \ + -e 's|version|$(VERSION)|g' \ $ $ chmod a+x $ msg04795/pgp0.pgp Description: PGP signature
[BUGS] ecpg hackery to get ecpg to compile from FreeBSD ports...
Different bogon that I ran across: 2:35pm sean@mat:ecpg/lib gmake cc -O -pipe -g -O -I/usr/local/include -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../../src/interfaces/ecpg/include -I../../../../src/interfaces/libpq -I../../../../src/include -I/usr/local/include -I/usr/local/include -c -o connect.o connect.c connect.c: In function `ECPGnoticeProcessor': connect.c:148: `ECPG_WARNING_UNRECOGNIZED' undeclared (first use in this function) connect.c:148: (Each undeclared identifier is reported only once connect.c:148: for each function it appears in.) connect.c:166: `ECPG_WARNING_QUERY_IGNORED' undeclared (first use in this function) connect.c:175: `ECPG_WARNING_UNKNOWN_PORTAL' undeclared (first use in this function) connect.c:182: `ECPG_WARNING_IN_TRANSACTION' undeclared (first use in this function) connect.c:193: `ECPG_WARNING_NO_TRANSACTION' undeclared (first use in this function) connect.c:201: `ECPG_WARNING_PORTAL_EXISTS' undeclared (first use in this function) gmake: *** [connect.o] Error 1 2:35pm sean@mat:ecpg/lib pwd /usr/ports/databases/postgresql7-beta/work/postgresql-7.3b1/src/interfaces/ecpg/lib This works: cc -O -pipe -g -O -I../../../../src/interfaces/ecpg/include -I/usr/local/include -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../../src/interfaces/libpq -I../../../../src/include -I/usr/local/include -I/usr/local/include -c -o connect.o connect.c This doesn't: cc -O -pipe -g -O -I/usr/local/include -I../../../../src/interfaces/ecpg/include -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../../src/interfaces/libpq -I../../../../src/include -I/usr/local/include -I/usr/local/include -c -o connect.o connect.c I've got an older ecpgerrno.h in /usr/local/include that doesn't seem to have these defined. Two quick suggestions: *) Prepend the CPPFLAGS to the CFLAGS (works, but is hackish) *) Alter the #ifndef This strikes me as an upgrade problem for folks that others are going to run across. -sc -- Sean Chittenden msg04796/pgp0.pgp Description: PGP signature
Re: [BUGS] Schemas not available for pl/pgsql %TYPE....
::sigh:: Is it me or does it look like all of pl/pgsql is schema un-aware (ie, all of the declarations). -sc Yeah. The group of routines parse_word, parse_dblword, etc that are called by the lexer certainly all need work. There are some definitional issues to think about, too --- plpgsql presently relies on the number of names to give it some idea of what to look for, and those rules are probably all toast now. Please come up with a sketch of what you think the behavior should be before you start hacking code. Not a problem there. I walked around the code for a bit, made a few hacks to see how things are working, and I can tell you strait up that if you'd like this by 7.3, it won't be happening from me. :~) I'm stretched kinda thin as is and don't think I'll be able to get this working correctly with time to test by release. I can send you the patch I've got for the lexer, but that was chump. What I was going to do could be totally wrong, but... * Change the lexer to recognize schema.table.column%TYPE as a token and was going to create parse_tripwordtype() that'd look up the table and column in the appropriate schema and would return the appropriate type. If I were lazy, I'd just unshift the schema off of the token and return what comes back from parse_dblwordtype(), but that doesn't strike me as correct for something that's performance sensitive. Beyond doing that, I'm at a loss. :-/ Thoughts? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Schemas not available for pl/pgsql %TYPE....
Call me crazy, but shouldn't the following work? :~| CREATE FUNCTION t() RETURNS TEXT AS ' DECLARE col_name pg_catalog.pg_attribute.attname%TYPE; BEGIN col_name := ''uga''; RETURN col_name; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION SELECT t(); WARNING: plpgsql: ERROR during compile of t near line 2 ERROR: Invalid type name 'pg_catalog.pg_attribute.attname % TYPE' -sc -- Sean Chittenden msg04760/pgp0.pgp Description: PGP signature
Re: [BUGS] Schemas not available for pl/pgsql %TYPE....
Sean Chittenden [EMAIL PROTECTED] writes: Call me crazy, but shouldn't the following work? :~| Sure should. Want to fix plpgsql's parser? Why not: I've never been one to avoid strapping on 4tons in rocks and jumping into the deep end. ::sigh:: Is it me or does it look like all of pl/pgsql is schema un-aware (ie, all of the declarations). -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [BUGS] Default values, inserts, and rules...
Rules do not operate on individual tuples --- a rule is a textual transformation on the whole query before it ever gets executed at all. What gets substituted into the INSERT command in your rule is not any specific tuple's value, but the NEXTVAL()-containing expression. That makes loads of sense. I thought it did its transformations further down in the process and on individual tuples as opposed to a wholistic rewrite of the query. Using rules would've worked had I not tried to be clever with tracing transaction histories through tags. :-/ Guess it's no biggie though... rules just are so elegant compared to triggers. :~) -sc PS I converted everything to use schemas tonight and I can't applaud the efforts enough: they really clean up the database and make things much usable. The output of psql is also much easier on the eyes for large and complex schemas. -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Default values, inserts, and rules...
There are two bits going on in this example: 1) To get the current value from a sequence, I have to use CURRVAL(seq_name) in the actual rule. Not that big of a deal, but kind of a PITA that I can't just use NEW.s. 2) When the rule does the insert into the t_log table, NEW.c1 doesn't contain the default value of 42. How can I do this? I don't want to force the application to specify the default values for everything. This is a known bug in 7.2: default values that should be inserted into unspecified columns of an INSERT aren't inserted till after the rule rewriter, thus the rule text doesn't see 'em. This is fixed in CVS for 7.3, but I don't believe we have made a back-patch for 7.2 branch. I believe this bug accounts for both your gripes. Hrm, I think I'm just going to start developing against CVS then because it also has the added perk of schemas which are perfect for hiding log tables. ;~) That said, if things are working correctly in CVS, would you still recommend a trigger over a rule? I'd think that a macro/rule would be faster than a trigger, but I don't have any real basis for my statement. In my examples I'm using CURRVAL() and not NEXTVAL() so I wouldn't worry about that being a problem. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Default values, inserts, and rules...
... That said, if things are working correctly in CVS, would you still
recommend a trigger over a rule?
Yes I would. I don't think you've thought carefully enough about
the implications of the statement that rules are macros... the
perennial problem with macros is multiple evaluations of an
argument, and if the argument has side-effects (like nextval()) you
*will* get bit.
::nods:: I understand the HUGE pitfall of using NEXTVAL() or the like
in a rule: it makes complete sense. But given that the NEW tuple is
being correctly populated with both the sequence number default
values, I'd think a rule is an ideal way of copying the contents of
the insert + some logging/transaction goo into a logging table.
Let me phrase my question better: if the rule contains nothing more
than an insert statement into a duplicate logging table, is it faster
and more efficient to use a rule than a trigger? For pretty much
everything else I'm using triggers, but for logging purposes, rules
seem ideal. Triggers struck me as being heavier weight than rules in
terms of parsing and the context switch to execute some pl code in the
triger... err... hold the phone... wait a sec, I see what you were
getting at. This behavior seems broken. :~) Example:
CREATE TABLE t (pk SERIAL NOT NULL, c1 CHAR(1) NOT NULL, PRIMARY KEY(pk));
CREATE TABLE t_log (pk INT NOT NULL, c1 CHAR(1) NOT NULL);
CREATE RULE t_ins AS ON INSERT TO t DO INSERT INTO t_log (pk,c1) VALUES
(NEW.pk,NEW.c1);
INSERT INTO t (c1) VALUES ('a');
SELECT * FROM t;
pk | c1
+
1 | a
(1 row)
SELECT * FROM t_log;
pk | c1
+
2 | a
(1 row)
What I get from this is that NEW.pk is doing a NEXTVAL() instead of
reading the value that the tuple was populated with from the sequence.
I can't think of an instance where this'd be the desired behavior...
kinda breaks the data consistency that I had expected.
The good news is though that the default values work like an absolute
CHARM and I can continue to use CURRVAL() in my rules... still, this
behavior seems a tad broken. There a good reason for this or could
you give me a filename to look into so I can toss together a patch.
Seems like something is going out of its way to get a new value from
the pk sequence when it shouldn't... thoughts? -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
The FreeBSD folk are absolutely adamant about having mktime() no compensate for deadzones between DST shifts and they insist that the application handle this. Someone's off looking at how other OS'es handle this, but this could be an arduous battle on that front. :~) Personally I'd like to see FreeBSD do away with this strange behaviour. It cause my grief because certaint hings *MUST* be done at 0200 every day in our system, I was forced to do them manually recently, shifing several hours of work into daytime which had to be paused and bulked into the next days work. I realise that this is getting off track but it just points out that the FreeBSD behaviour is IMHO WRONG. It causes applications to fail in an unexpected and odd way. I'm not objecting to pg patching for it (no choice at the moment) but I hope the pg team 'officially' puts a little pressure on the BSD folk to make this behave as expected. Feel free to read over their arguments (archive may not be 100% up to date): http://docs.freebsd.org/mail/archive/2002/freebsd-standards/20020414.freebsd-standards.html I don't have any compliance docs at the moment, but this strikes me as somewhat out of spec personally. ::shrug:: I've gotten enough push back to have an indifferent opinion: I just want to see PG work w/ some of the bogus data I get every now and then. :~) -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed incorrectly...
date/time storage problem: timestamp parsed incorrectly... It looks like a bad parser or defaults for time values. The example code below explains the problem best. I'm not sure why, or where... but it took me about a day to track down (PostgreSQL is never wrong!). If I include a timezone, things seem to work. For some reason, only dates from yesterday and today break things... I think it's because -7 is the same as my timezone, PST (now -7). Well, as long as you realize that PostgreSQL is always right you are on track ;) I'm guessing that you have a damaged timezone database on your system. What time zone does your system think it is in? What system are you running on? I'm not seeing a problem on my Linux box running 7.2 (well, except for the jump at the time zone boundary): lockhart=# select timestamp '2002-4-7 2:0:0.0'; timestamptz 2002-04-07 01:00:00-08 (1 row) But that is not the 2036 result you are seeing, so I can only speculate on your specific problem... ACK! Hmm... fresh build of FreeBSD: $ uname -a FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr 5 18:08:12 PST 2002 [EMAIL PROTECTED]:/opt/obj/opt/src/sys/NINJA i386 $ psql # SELECT timestamp '2002-4-7 2:0:0.0'; timestamptz 2036-06-02 22:57:08-07 (1 row) # SELECT version(); version PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 (1 row) This isn't happy making. What OS are you running? Seems like a lower level problem. Do you know if it's a system call making the formatting call? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Ugh, I'm too tired to file a gdb report:
1490t = mktime(tmp);
(gdb)
1491fprintf(stderr, %p\n, t);
(gdb) print t
$7 = -1
Good call Tom. ... I'm going to file a PR w/ FreeBSD.
The FreeBSD folk are absolutely adamant about having mktime() no
compensate for deadzones between DST shifts and they insist that the
application handle this. Someone's off looking at how other OS'es
handle this, but this could be an arduous battle on that front. :~)
I know the attached patch is something of a hack, but it works. I'm
not totally wild about altering the original time object, but I
don't know that I have a choice in this case. Does anyone switch
timezones and only adjust their clocks by anything other than 60min?
I seem to recall that happening in a few places, but the patch isn't
any worse than where we are now. ::shrug:: This look like an okay
patch?
Are there any objections to the following? Instead of returning 0 or
utc, I could have it raise an error. Would that be acceptable? -sc
Index: src/backend/utils/adt/datetime.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.88
diff -u -r1.88 datetime.c
--- src/backend/utils/adt/datetime.c 2002/02/25 16:17:04 1.88
+++ src/backend/utils/adt/datetime.c 2002/04/10 06:12:45
-1439,6 +1439,7
DetermineLocalTimeZone(struct tm * tm)
{
int tz;
+ time_t t;
if (HasCTZSet)
tz = CTimeZone;
-1463,7 +1464,23
/* indicate timezone unknown */
tmp-tm_isdst = -1;
- mktime(tmp);
+ t = mktime(tmp);
+ if (t == -1)
+ {
+ /* Bump time up by an hour to see if time was an
+ * invalid time during a daylight savings switch */
+ tmp-tm_hour += 1;
+ t = mktime(tmp);
+
+ /* Assume UTC if mktime() still fails.
+ *
+ * If mktime() was successful with the adjusted time,
+ * adjust the real time object. */
+ if (t == -1)
+ return 0;
+ else
+ tm-tm_hour += 1;
+ }
tm-tm_isdst = tmp-tm_isdst;
--
Sean Chittenden
msg03995/pgp0.pgp
Description: PGP signature
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Good call Tom. ... I'm going to file a PR w/ FreeBSD. I know the attached patch is something of a hack, but it works. I'm not totally wild about altering the original time object, but I don't know that I have a choice in this case. Does anyone switch timezones and only adjust their clocks by anything other than 60min? I seem to recall that happening in a few places, but the patch isn't any worse than where we are now. ::shrug:: This look like an okay patch? Yuck. You are trying the right things, but I'm not sure that we should allow mktime() to fail for special cases like this. Falling back to GMT (a potential offset of up to 12 hours from what was intended by the user) is (perhaps) unacceptably ugly, particularly for recent/current epochs which would be reasonably expected to behave correctly. I'm inclined to test for an error return from mktime(), or to test it as an ASSERT(), and then throw an error, rather than passing along garbaged-up values. Comments? I thought about throwing an exception but noticed a few lines down that the code was returning GMT on failure. When in Rome, do as the Romans do. :~) I personally think that ASSERT should be called after the 2nd mktime() fails but think that calling ASSERT would break existing applications though maybe not: I've just had a 6hr crash course in this code and don't quite have the incite to say one way or another. FWIW, I've lobbed something off to the FreeBSD crowd asking if mktime() should be updated in the system libraries but don't think that'll fix things soon enough. -sc -- Sean Chittenden msg03973/pgp0.pgp Description: PGP signature
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
pfree(val);
(gdb) print *pstate
$9 = {parentParseState = 0x0, p_rtable = 0x8495708, p_joinlist = 0x0,
p_namespace = 0x0, p_last_resno = 2, p_forUpdate = 0x0,
p_hasAggs = 0 '\000', p_hasSubLinks = 0 '\000', p_is_insert = 1 '\001',
p_is_update = 0 '\000', p_target_relation = 0x847fba0,
p_target_rangetblentry = 0x84953a0}
[snip]
backend select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
1: tt = 2036-06-02 22:19:48-07 (typeid = 1184, len = 8, typmod = -1,
byval = f)
What seems to be particularly interesting is the following:
backend insert into tt values ('2002-4-8 2:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend insert into tt values ('2002-4-9 2:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
1: tt = 2036-06-02 22:19:48-07 (typeid = 1184, len = 8, typmod = -1,
byval = f)
1: tt = 2002-04-08 02:00:00-07 (typeid = 1184, len = 8, typmod = -1,
byval = f)
1: tt = 2002-04-09 02:00:00-07 (typeid = 1184, len = 8, typmod = -1,
byval = f)
It seems as if this problem only happens with dates that happen
_during_ the date switch.
backend insert into tt values ('2002-4-7 2:30:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend insert into tt values ('2002-4-7 3:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
1: tt = 2036-06-02 22:19:48-07 (typeid = 1184, len = 8, typmod = -1,
byval = f)
1: tt = 2036-06-02 22:49:48-07 (typeid = 1184, len = 8, typmod = -1,
byval = f)
1: tt = 2002-04-07 03:00:00-07 (typeid = 1184, len = 8, typmod = -1,
byval = f)
Ideas where to look? -sc
--
Sean Chittenden
msg03964/pgp0.pgp
Description: PGP signature
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Err... brain-o on my part (didn't know what I was looking for until I
put in a date that does exist and followed it through):
(gdb) b DecodeDateTime
Breakpoint 1 at 0x811568d: file datetime.c, line 892.
(gdb) b DetermineLocalTimeZone
Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
(gdb) run foo
backend create table tt ( tt timestamp );
backend insert into tt values ('2002-4-7 2:0:0.0');
If I use 3am on the 7th, I get the following:
(gdb) print *tm
$2 = {tm_sec = 0, tm_min = 0, tm_hour = 3, tm_mday = 7, tm_mon = 3,
tm_year = 102, tm_wday = 0, tm_yday = 96, tm_isdst = 1,
tm_gmtoff = -25200, tm_zone = 0x28420c78 PDT}
Looks like it's a bug in mktime() on FreeBSD: it doesn't seem to do
so well with invalid times that happen between daylight savings
time... or is that a postgres thing for not kicking up an error (out
of bounds time)? Or should 2am PST be converted to 3am? -sc
--
Sean Chittenden
msg03965/pgp0.pgp
Description: PGP signature
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Looks like it's a bug in mktime() on FreeBSD: it doesn't seem to do so well with invalid times that happen between daylight savings time... or is that a postgres thing for not kicking up an error (out of bounds time)? Or should 2am PST be converted to 3am? -sc Here is the man page on Linux: The mktime() function converts a broken-down time structure, expressed as local time, to calendar time representation. The function ignores the specified contents of the structure members tm_wday and tm_yday and recomputes them from the other information in the broken-down time structure. If structure members are outside their legal interval, they will be normalized (so that, e.g., 40 October is changed into 9 November). Calling mktime() also sets the external variable tzname with information about the current time zone. If the specified broken-down time cannot be represented as calendar time (seconds since the epoch), mktime() returns a value of (time_t)(-1) and does not alter the tm_wday and tm_yday members of the broken-down time structure. Does that look similar to FreeBSD? Very familiar, from mktime(2): The functions mktime() and timegm() convert the broken-down time in the structure pointed to by tm into a time value with the same encoding as that of the values returned by the time(3) function (that is, seconds from the Epoch, UTC). mktime() interprets the input structure according to the current timezone setting (see tzset(3)). timegm() interprets the input structure as representing Universal Coordinated Time (UTC). The original values of the tm_wday and tm_yday components of the struc- ture are ignored, and the original values of the other components are not restricted to their normal ranges, and will be normalized if needed. For example, October 40 is changed into November 9, a tm_hour of -1 means 1 hour before midnight, tm_mday of 0 means the day preceding the current month, and tm_mon of -2 means 2 months before January of tm_year. (A positive or zero value for tm_isdst causes mktime() to presume initially that summer time (for example, Daylight Saving Time) is or is not in effect for the specified time, respectively. A negative value for tm_isdst causes the mktime() function to attempt to divine whether summer time is in effect for the specified time. The tm_isdst and tm_gmtoff members are forced to zero by timegm().) I don't think that our code checks explicitly for a -1 return, since the range is checked just before the call, but it would probably be a good idea if it did (assuming that other mktime() implementations had the same convention for an error return of course). Just poked through how Ruby handles this and it looks like they go to reasonable lengths to make sure that it does the right thing. http://www.ruby-lang.org/~knu/cgi-bin/cvsweb.cgi/ruby/time.c?rev=1.55content-type=text/x-cvsweb-markup irb(main):005:0 Time.local(2002,4,7,1) Sun Apr 07 01:00:00 PST 2002 irb(main):006:0 Time.local(2002,4,7,3) Sun Apr 07 03:00:00 PDT 2002 irb(main):007:0 Time.local(2002,4,7,2) Sun Apr 07 03:00:00 PDT 2002 irb(main):008:0 Time.local(2002,4,7,2,20) Sun Apr 07 03:20:00 PDT 2002 It's artistically licensed... ::shrug:: Time.local is a thin wrapper around mktime(). Check out make_time_t() in the link above. This is the first report I can remember in 6 years of this particular symptom, and I have the strong feeling that no matter what we end up doing there *is* a problem with the FreeBSD database of time zones or (possibly) in its implementation of mktime(). I hope so... this bug hit me nasty like. I was doing a time series regression and thought it'd be a cute exercise to sum up the components... when I hit only ~99.7% and found out that part of my data was in 2036 I... flipped, freaked out, debugged, cursed, scratched head, cursed more... then I went for a Guinness and my world was calm again. :~) What do you see as the return value from mktime()? Ehh... let me hack/check. Looks like 11. ?? In lib/libc/stdtime/localtime.c, WRONG is defined as -1, not 11. 1490t = mktime(tmp); (gdb) 1491fprintf(stderr, %p\n, t); /* GCC optimizes this away if I don't do something */ (gdb) 0x3c5e5ba0 (gdb) print t $1 = 11 Doesn't make much sense to me where that'd come from... ? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed incorrectly...
date/time storage problem: timestamp parsed incorrectly... It looks like a bad parser or defaults for time values. The example code below explains the problem best. I'm not sure why, or where... but it took me about a day to track down (PostgreSQL is never wrong!). If I include a timezone, things seem to work. For some reason, only dates from yesterday and today break things... I think it's because -7 is the same as my timezone, PST (now -7). Well, as long as you realize that PostgreSQL is always right you are on track ;) I'm guessing that you have a damaged timezone database on your system. What time zone does your system think it is in? What system are you running on? I'm not seeing a problem on my Linux box running 7.2 (well, except for the jump at the time zone boundary): lockhart=# select timestamp '2002-4-7 2:0:0.0'; timestamptz 2002-04-07 01:00:00-08 (1 row) But that is not the 2036 result you are seeing, so I can only speculate on your specific problem... ACK! Hmm... fresh build of FreeBSD: $ uname -a FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr 5 18:08:12 PST 2002 [EMAIL PROTECTED]:/opt/obj/opt/src/sys/NINJA i386 $ psql # SELECT timestamp '2002-4-7 2:0:0.0'; timestamptz 2036-06-02 22:57:08-07 (1 row) # SELECT version(); version PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 (1 row) This isn't happy making. What OS are you running? Seems like a lower level problem. Do you know if it's a system call making the formatting call? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
