Re: [HACKERS] [GENERAL] to_timestamp() and quarters
A. Kretschmer *EXTERN* > > Well, I can easily make it do what you expect, and I don't see many > > error returns in that area of the code, so I just wrote a patch that > > does what you would expect rather than throw an error. > > Well, that's great and better than an error, thx. > > > test=> select to_date('2010-7', '-Q'); > > to_date > > > > 2011-07-04 > > (1 row) > > Is this per SQL-Spec? I would expect an error for a quarter not in > (1,2,3,4). > > But stop, now i see: > > test=*# select to_date('2010-02-29', '-MM-DD'); > to_date > > 2010-03-01 > (1 row) > > So it is maybe a congruously behavior ;-) Ugh. I thought that to_date was an Oracle compatibility function. SQL> select to_date('2010-02-29', '-MM-DD') from dual; select to_date('2010-02-29', '-MM-DD') from dual * ERROR at line 1: ORA-01839: date not valid for month specified And for that matter: SQL> select to_date('2010-7', '-Q') from dual; select to_date('2010-7', '-Q') from dual * ERROR at line 1: ORA-01820: format code cannot appear in date input format Oracle allows Q only when converting date to string. So this can be seen as an extension. But allowing 2010-02-29 is incompatible and smacks of MySQL... Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
2010/3/3 Andrew Dunstan : > > > Tom Lane wrote: >> >> Andrew Dunstan writes: >> >>> >>> Baiji is now failing, however. Perhaps it is not finding the XSLT lib or >>> dll? >>> >> >> Curious indeed, because it passed yesterday, *after* I had made all >> those changes in contrib/xml2 itself. The only deltas since then are >> your MSVC script additions. Now presumably, the XSLT support was >> commented out in yesterday's run for lack of USE_LIBXSLT, and the "pass" >> was against the variant output file that allowed for that to fail. >> So I think you're right that there's something wrong with the link >> to libxslt, but how come no sign of trouble in the build log? Does >> Windows have an equivalent of "rpath" that maybe we're forgetting to >> add libxslt to? >> >> >> > > There is something funny about the iconv setup (libxml requires iconv). My > paths on red_bat are a bit different from baiji's so it might not expose the > problem. I'm going to make the iconv setup work like the other libraries, and > hope it fixes baiji's problem. Yeah, there are some weird cross dependencies along that. The MSVC build system really expects you to have libxml+libxslt+iconv all installed to really get any of them to work. I originally had them all work the same, but that didn't work, so I just put that in place - blame being lazy for that. It may well be that it works now due to other changes around the build system, but originally that hack was required to make it build at all with XML. But it's good news if it's not needed anymore and they can all work the same :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
Hi, KaiGai Kohei írta: > (2010/03/03 14:26), Robert Haas wrote: > >> 2010/3/2 KaiGai Kohei: >> >>> Is it an expected behavior? >>> >>> postgres=> CREATE SEQUENCE s; >>> CREATE SEQUENCE >>> postgres=> ALTER TABLE s RENAME sequence_name TO abcd; >>> ALTER TABLE >>> >>> postgres=> CREATE TABLE t (a int primary key, b text); >>> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" >>> for table "t" >>> CREATE TABLE >>> postgres=> ALTER TABLE t_pkey RENAME a TO xyz; >>> ALTER TABLE >>> >>> The documentation says: >>> http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html >>> >>> : >>> RENAME >>> The RENAME forms change the name of a table (or an index, sequence, or >>> view) or >>> the name of an individual column in a table. There is no effect on the >>> stored data. >>> >>> It seems to me the renameatt() should check relkind of the specified >>> relation, and >>> raise an error if relkind != RELKIND_RELATION. >>> >> Are we talking about renameatt() or RenameRelation()? Letting >> RenameRelation() rename whatever seems fairly harmless; renameatt(), >> on the other hand, should probably refuse to allow this: >> >> CREATE SEQUENCE foo; >> ALTER TABLE foo RENAME COLUMN is_cycled TO bob; >> >> ...because that's just weird. Tables, indexes, and views make sense, >> but the attributes of a sequence should be nailed down I think; >> they're basically system properties. >> > > I'm talking about renameatt(), not RenameRelation(). > > If our perspective is these are a type of system properties, we should > be able to reference these attributes with same name, so it is not harmless > to allow renaming these attributes. > I just tried it on 8.3.7: zozo=# create sequence seq2; CREATE SEQUENCE "is_called" is modified from false to true on the first call of nextval() so I renamed it: zozo=# alter table seq2 rename column is_called to bob; ALTER TABLE zozo=# create table seq2_tab (id integer primary key default nextval('seq2'), t text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "seq2_tab_pkey" for table "seq2_tab" CREATE TABLE zozo=# alter sequence seq2 owned by seq2_tab.id; ALTER SEQUENCE No error it seems: zozo=# insert into seq2_tab (t) values ('a'); INSERT 0 1 zozo=# select * from seq2; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | bob ---++--+-+---+-+-+---+- seq2 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t (1 sor) Let's try other fields: zozo=# alter table seq2 rename column min_value to first; ALTER TABLE zozo=# alter table seq2 rename column max_value to last; ALTER TABLE zozo=# alter table seq2 rename column last_value to always; ALTER TABLE Still no error: zozo=# insert into seq2_tab (t) values ('b'); INSERT 0 1 Let's try more fields: zozo=# alter table seq2 rename column cache_value to keep; ALTER TABLE zozo=# alter table seq2 rename column increment_by to advance; ALTER TABLE zozo=# alter table seq2 rename column is_cycled to bobek; ALTER TABLE Still no error: zozo=# insert into seq2_tab (t) values ('c'); INSERT 0 1 zozo=# select * from seq2; sequence_name | always | advance | last | first | keep | log_cnt | bobek | bob ---++-+-+---+--+-+---+- seq2 | 3 | 1 | 9223372036854775807 | 1 | 1 | 31 | f | t (1 sor) Still no error: zozo=# alter table seq2 rename column log_cnt to pampalini; ALTER TABLE zozo=# insert into seq2_tab (t) values ('d'); INSERT 0 1 zozo=# select * from seq2; sequence_name | always | advance | last | first | keep | pampalini | bobek | bob ---++-+-+---+--+---+---+- seq2 | 4 | 1 | 9223372036854775807 | 1 | 1 | 32 | f | t (1 sor) Change the last remaining field and still no error: zozo=# alter table seq2 rename column sequence_name to pimpa; ALTER TABLE zozo=# insert into seq2_tab (t) values ('d'); INSERT 0 1 zozo=# select * from seq2; pimpa | always | advance | last | first | keep | pampalini | bobek | bob ---++-+-+---+--+---+---+- seq2 | 5 | 1 | 9223372036854775807 | 1 | 1 | 31 | f | t (1 sor) zozo=# select * from seq2_tab; id | t +--- 1 | a 2 | b 3 | c 4 | d 5 | d (5 rows) Internally, the system refers these column by position instead of names. But from the user perspective, the sequence fields are more like system columns, renaming them leads to confusion. > I also agree that it makes sense to allow renaming attributes of tables > and views. But I don't know whether it makes sense to allow it on indexs, > like sequence and toast relations. > Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is m
Re: [HACKERS] double and numeric conversion
Theo Schlossnagle wrote: I didn't look deeply at the postgres internals to see if there was a way to do double -> numeric and integer-types -> numeric without intermediary string format. If that sort of thing is easy to leverage, I'd be happy to share the code. I think your code could be valuable for postgres on the fact alone that it is almost twice as fast, and probably easy to integrate and unit test. We make heavy use of the numeric data type, so I'm very interested! regards Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] double and numeric conversion
2010/3/3 Yeb Havinga : > Theo Schlossnagle wrote: >> >> I didn't look deeply at the postgres internals to see if there was a way >> to do double -> numeric and integer-types -> numeric without intermediary >> string format. If that sort of thing is easy to leverage, I'd be happy to >> share the code. >> > > I think your code could be valuable for postgres on the fact alone that it > is almost twice as fast, and probably easy to integrate and unit test. We > make heavy use of the numeric data type, so I'm very interested! I did some test and numeric->double is about 5% faster than numeric->string->double (on my PC) Regards Pavel Stehule > > regards > Yeb Havinga > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] double and numeric conversion
Pavel Stehule wrote: 2010/3/3 Yeb Havinga : Theo Schlossnagle wrote: I didn't look deeply at the postgres internals to see if there was a way to do double -> numeric and integer-types -> numeric without intermediary string format. If that sort of thing is easy to leverage, I'd be happy to share the code. I think your code could be valuable for postgres on the fact alone that it is almost twice as fast, and probably easy to integrate and unit test. We make heavy use of the numeric data type, so I'm very interested! I did some test and numeric->double is about 5% faster than numeric->string->double (on my PC) numeric_to_double_no_overflow() also uses string as intermediate format. Theo's conversions are the converse, from double to numeric, and do not use string as intermediate format (if I understand it correct). (where float8_numeric http://doxygen.postgresql.org/backend_2utils_2adt_2numeric_8c.html#2de7f65c8de4b65dad441e77ea1bf402 does) regards Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] double and numeric conversion
2010/3/3 Yeb Havinga : > Pavel Stehule wrote: >> >> 2010/3/3 Yeb Havinga : >> >>> >>> Theo Schlossnagle wrote: >>> I didn't look deeply at the postgres internals to see if there was a way to do double -> numeric and integer-types -> numeric without intermediary string format. If that sort of thing is easy to leverage, I'd be happy to share the code. >>> >>> I think your code could be valuable for postgres on the fact alone that >>> it >>> is almost twice as fast, and probably easy to integrate and unit test. We >>> make heavy use of the numeric data type, so I'm very interested! >>> >> >> I did some test and numeric->double is about 5% faster than >> numeric->string->double (on my PC) >> > > numeric_to_double_no_overflow() also uses string as intermediate format. > > Theo's conversions are the converse, from double to numeric, and do not use > string as intermediate format (if I understand it correct). (where > float8_numeric > http://doxygen.postgresql.org/backend_2utils_2adt_2numeric_8c.html#2de7f65c8de4b65dad441e77ea1bf402 > does) > aha - it is reason why time similar Pavel > regards > Yeb Havinga > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Magnus Hagander wrote: 2010/3/3 Andrew Dunstan : Tom Lane wrote: Andrew Dunstan writes: Baiji is now failing, however. Perhaps it is not finding the XSLT lib or dll? Curious indeed, because it passed yesterday, *after* I had made all those changes in contrib/xml2 itself. The only deltas since then are your MSVC script additions. Now presumably, the XSLT support was commented out in yesterday's run for lack of USE_LIBXSLT, and the "pass" was against the variant output file that allowed for that to fail. So I think you're right that there's something wrong with the link to libxslt, but how come no sign of trouble in the build log? Does Windows have an equivalent of "rpath" that maybe we're forgetting to add libxslt to? There is something funny about the iconv setup (libxml requires iconv). My paths on red_bat are a bit different from baiji's so it might not expose the problem. I'm going to make the iconv setup work like the other libraries, and hope it fixes baiji's problem. Yeah, there are some weird cross dependencies along that. The MSVC build system really expects you to have libxml+libxslt+iconv all installed to really get any of them to work. I originally had them all work the same, but that didn't work, so I just put that in place - blame being lazy for that. It may well be that it works now due to other changes around the build system, but originally that hack was required to make it build at all with XML. But it's good news if it's not needed anymore and they can all work the same :-) It's not working on mastodon, and presumably will fail again on baiji. I notice that mastodon does not appear to have the presumed location of the iconv and xslt DLLs in the PATH it sets. That would be a plausible explanation of the problem ;-) On red_bat these are all in the same bin directory, but I suspect on baiji and mastodon they are not. I think these might need to have c:\pgBuild\{libxslt,iconv}\bin added to the PATH in the buildfarm.conf file, right after where c:\pgBuild\libxml2\bin is added. (That could also explain why it is failing at run time rather than at build time). cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parameter name standby_mode
On Wed, Feb 24, 2010 at 2:18 PM, Fujii Masao wrote: > If standby_mode is enabled, and neither primary_conninfo nor restore_command > are set, the standby would get stuck. How about forbidding (i.e., causing a > FATAL message) this wrong setting? Here is the patch which forbids that wrong setting of recovery.conf. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 5043,5052 readRecoveryCommandFile(void) cmdline), errhint("Lines should have the format parameter = 'value'."))); ! /* If not in standby mode, restore_command must be supplied */ ! if (!StandbyMode && recoveryRestoreCommand == NULL) ereport(FATAL, ! (errmsg("recovery command file \"%s\" did not specify restore_command nor standby_mode", RECOVERY_COMMAND_FILE))); /* Enable fetching from archive recovery area */ --- 5043,5058 cmdline), errhint("Lines should have the format parameter = 'value'."))); ! /* ! * Either restore_command or primary_conninfo must be supplied. Otherwise, ! * we cannot determine how to retrieve log files. Note that also ! * standby_mode should be checked since primary_conninfo has no effect ! * if standby_mode is off. ! */ ! if ((!StandbyMode || PrimaryConnInfo == NULL) && ! recoveryRestoreCommand == NULL) ereport(FATAL, ! (errmsg("recovery command file \"%s\" did not specify restore_command nor primary_conninfo", RECOVERY_COMMAND_FILE))); /* Enable fetching from archive recovery area */ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] recovery.conf.sample
On Tue, Feb 23, 2010 at 1:44 PM, Fujii Masao wrote: > recovery.conf.sample has the following section for Hot Standby. > Is this still required? > >> #--- >> # HOT STANDBY PARAMETERS >> #--- >> # >> # If you want to enable read-only connections during recovery, enable >> # recovery_connections in postgresql.conf >> # >> #--- > > > Heikki classified the recovery options into the following three > groups, in the document. > > 26.1. Archive recovery settings > 26.2. Recovery target settings > 26.3. Standby server settings > > OTOH, recovery.conf.sample has classified them into the following > two groups. This is inconsistent with the document, and looks > confusing. How about modifying recovery.conf.sample to make the > grouping the same? > > ARCHIVE RECOVERY PARAMETERS > LOG-STREAMING REPLICATION PARAMETERS The attached patch removes the unnecessary section about HS from recovery.conf.sample. Also it changes the grouping of parameters in recovery.conf.sample as mentioned above. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center *** a/src/backend/access/transam/recovery.conf.sample --- b/src/backend/access/transam/recovery.conf.sample *** *** 55,60 --- 55,64 #recovery_end_command = '' # # + #--- + # RECOVERY TARGET PARAMETERS + #--- + # # By default, recovery will rollforward to the end of the WAL log. # If you want to stop rollforward before that point, you # must set a recovery target. *** *** 79,85 # # #--- ! # LOG-STREAMING REPLICATION PARAMETERS #--- # # When standby_mode is enabled, the PostgreSQL server will work as --- 83,89 # # #--- ! # STANDBY SERVER PARAMETERS #--- # # When standby_mode is enabled, the PostgreSQL server will work as *** *** 99,110 # when it's found. # #trigger_file = '' - # - #--- - # HOT STANDBY PARAMETERS - #--- - # - # If you want to enable read-only connections during recovery, enable - # recovery_connections in postgresql.conf - # - #--- --- 103,105 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming rep - why log shipping is necessary?
On Fri, Feb 26, 2010 at 2:34 AM, Josh Berkus wrote: > >>> If you're adventurous enough, it's actually possible to set an >>> archive_command that checks the status of the standby and returns >>> failure as long as the standby still needs the given WAL segment. That >>> way the primary doesn't recycle segments that are still needed by the >>> standby, and you can get away without restore_command in the standby. > > I'd prefer something a little different ... is there any way to tell > which log segments a standby still needs, *from* the standby? pg_controldata can tell that. The log segment containing the "Latest checkpoint's REDO location" that pg_controldata reports is the oldest one still required for the standby. So we can remove the older log segments than it from the archive. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
2010/3/3 KaiGai Kohei : > (2010/03/03 14:26), Robert Haas wrote: >> 2010/3/2 KaiGai Kohei: >>> Is it an expected behavior? >>> >>> postgres=> CREATE SEQUENCE s; >>> CREATE SEQUENCE >>> postgres=> ALTER TABLE s RENAME sequence_name TO abcd; >>> ALTER TABLE >>> >>> postgres=> CREATE TABLE t (a int primary key, b text); >>> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" >>> for table "t" >>> CREATE TABLE >>> postgres=> ALTER TABLE t_pkey RENAME a TO xyz; >>> ALTER TABLE >>> >>> The documentation says: >>> http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html >>> >>> : >>> RENAME >>> The RENAME forms change the name of a table (or an index, sequence, or >>> view) or >>> the name of an individual column in a table. There is no effect on the >>> stored data. >>> >>> It seems to me the renameatt() should check relkind of the specified >>> relation, and >>> raise an error if relkind != RELKIND_RELATION. >> >> Are we talking about renameatt() or RenameRelation()? Letting >> RenameRelation() rename whatever seems fairly harmless; renameatt(), >> on the other hand, should probably refuse to allow this: >> >> CREATE SEQUENCE foo; >> ALTER TABLE foo RENAME COLUMN is_cycled TO bob; >> >> ...because that's just weird. Tables, indexes, and views make sense, >> but the attributes of a sequence should be nailed down I think; >> they're basically system properties. > > I'm talking about renameatt(), not RenameRelation(). OK. Your original example was misleading because you had renameatt() in the subject line but the actual SQL commands were renaming a whole relation (which is a reasonable thing to do). > If our perspective is these are a type of system properties, we should > be able to reference these attributes with same name, so it is not harmless > to allow renaming these attributes. > > I also agree that it makes sense to allow renaming attributes of tables > and views. But I don't know whether it makes sense to allow it on indexs, > like sequence and toast relations. I would think not. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication and pg_xlogfile_name()
On Tue, Mar 2, 2010 at 10:52 PM, Fujii Masao wrote: >> It's not clear what it should return, a TLI corresponding the filename >> of the WAL segment the record was replayed from, so that you can use >> pg_xlogfile_name() to find out the filename of the WAL segment being >> replayed, or the accurate TLI of the record being replayed. I'm leaning >> towards the latter, it feels more correct and accurate, but you could >> argue for the former too. In any case, it needs to be well-defined. > > I agree with you that the latter is more correct and accurate. The simple > fix is updating the lastPageTLI with the CheckPoint->ThisTimeLineID when > replaying the shutdown checkpoint record. Though we might need to use new > variable to keep the last applied timeline instead of the lastPageTLI. Here is the revised patch. I used new local variable instead of lastPageTLI to track the tli of last applied record. It is updated with the tli of the log page header when reading the page, and with the tli of the checkpoint record when replaying the checkpoint shutdown record that changes the tli. So pg_last_xlog_replay_location() can return the accurate tli of the last applied record. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** *** 13199,13204 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); --- 13199,13208 This is usually the desired behavior for managing transaction log archiving behavior, since the preceding file is the last one that currently needs to be archived. + These functions also accept as a parameter the string that consists of timeline and + location, separated by a slash. In this case a transaction log file name is computed + by using the given timeline. On the other hand, if timeline is not supplied, the + current timeline is used for the computation. *** *** 13245,13257 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); pg_last_xlog_receive_location() text !Get last transaction log location received and synced to disk during ! streaming recovery. If streaming recovery is still in progress this will increase monotonically. If streaming recovery has completed then this value will remain static at the value of the last WAL record received and synced to disk during that recovery. When the server has been started without a streaming recovery then the return value will be ! InvalidXLogRecPtr (0/0). --- 13249,13263 pg_last_xlog_receive_location() text !Get timeline and location of last transaction log received and synced ! to disk during streaming recovery. The return string is separated by a slash, ! the first value indicates the timeline and the other the location. ! If streaming recovery is still in progress this will increase monotonically. If streaming recovery has completed then this value will remain static at the value of the last WAL record received and synced to disk during that recovery. When the server has been started without a streaming recovery then the return value will be ! 0/0/0. *** *** 13259,13270 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); pg_last_xlog_replay_location() text !Get last transaction log location replayed during recovery. If recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last WAL record applied during that recovery. When the server has been started normally without a recovery ! then the return value will be InvalidXLogRecPtr (0/0). --- 13265,13278 pg_last_xlog_replay_location() text !Get timeline and location of last transaction log replayed during ! recovery. The return string is separated by a slash, the first value ! indicates the timeline and the other the location. If recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last WAL record applied during that recovery. When the server has been started normally without a recovery ! then the return value will be 0/0/0. *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 392,397 typedef struct XLogCtlData --- 392,399 TimestampTz recoveryLastXTime; /* end+1 of the last record replayed */ XLogRecPtr recov
Re: [HACKERS] [GENERAL] to_timestamp() and quarters
Albe Laurenz wrote: > > But stop, now i see: > > > > test=*# select to_date('2010-02-29', '-MM-DD'); > > to_date > > > > 2010-03-01 > > (1 row) > > > > So it is maybe a congruously behavior ;-) > > Ugh. I thought that to_date was an Oracle compatibility function. > > SQL> select to_date('2010-02-29', '-MM-DD') from dual; > select to_date('2010-02-29', '-MM-DD') from dual >* > ERROR at line 1: > ORA-01839: date not valid for month specified > > And for that matter: > > SQL> select to_date('2010-7', '-Q') from dual; > select to_date('2010-7', '-Q') from dual > * > ERROR at line 1: > ORA-01820: format code cannot appear in date input format > > Oracle allows Q only when converting date to string. > So this can be seen as an extension. > > But allowing 2010-02-29 is incompatible and smacks of MySQL... Yea, we had a similar issue with to_timestamp(): test=> SELECT to_timestamp('20096040','MMDD'); to_timestamp 2014-01-17 00:00:00-05 (1 row) If we are going to tighten these up, we should do them all. Right now we allow it and for consistency should allow the Q=7 value too. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] double and numeric conversion
I can't release all of it, but the functions to convert uint64_t, int64_t and double to numeric Datum are the meat and I can expose those... https://labs.omniti.com/pgsoltools/trunk/contrib/scratch/pg_type_to_numeric.c As I mentioned, the dscale on the double_to_numeric is imperfect resulting in things like: 1.23 turning into 1.2300 in the numeric returned. This are significantly faster (as expected) than the type -> string -> numeric conversions. On Mar 3, 2010, at 5:01 AM, Yeb Havinga wrote: > Theo Schlossnagle wrote: >> I didn't look deeply at the postgres internals to see if there was a way to >> do double -> numeric and integer-types -> numeric without intermediary >> string format. If that sort of thing is easy to leverage, I'd be happy to >> share the code. >> > I think your code could be valuable for postgres on the fact alone that it is > almost twice as fast, and probably easy to integrate and unit test. We make > heavy use of the numeric data type, so I'm very interested! > > regards > Yeb Havinga > > -- Theo Schlossnagle http://omniti.com/is/theo-schlossnagle -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Andrew Dunstan writes: > It's not working on mastodon, and presumably will fail again on baiji. > I notice that mastodon does not appear to have the presumed location of > the iconv and xslt DLLs in the PATH it sets. That would be a plausible > explanation of the problem ;-) Ah. If it's just misconfiguration of the buildfarm animals, that would also explain the lack of complaints from the field about contrib/xml2 not working on Windows. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Tom Lane wrote: Andrew Dunstan writes: It's not working on mastodon, and presumably will fail again on baiji. I notice that mastodon does not appear to have the presumed location of the iconv and xslt DLLs in the PATH it sets. That would be a plausible explanation of the problem ;-) Ah. If it's just misconfiguration of the buildfarm animals, that would also explain the lack of complaints from the field about contrib/xml2 not working on Windows. Something completely different but a bit alike; today I chased a strange initdb bug on a rarely used machine. It said something with fgets: some file error (sorry, history is gone from shell) and then the message that postgres could not be found in the same path as initdb. But all was there, permissions etc ok. In the end it turned out that the fgets error was near the real problem: postgres was started with -V by initdb and the output read via a pipe. (function was find_other_exec) But the postgres command failed with an error that a shared library could not be loaded, however this error message was not picked up by the pipe read command / error message by initdb, which maked it hard to diagnose without getting to the debugger. I don't know if the buildfarm machines fail on initdb, however I think that maybe a better errormessage at that point in initdb could help future users. regards, Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Safe security (was: plperl _init settings)
On Tue, Mar 02, 2010 at 07:33:47PM -0500, Andrew Dunstan wrote: > > There appears to be some significant misunderstanding of what can be > done effectively using the various *_init settings for plperl. > > In particular, some people have got an expectation that modules > loaded in plperl.on_init will thereby be available for use in > trusted plperl. > > I propose to add the following note to the docs: > >Preloading modules using plperl.on_init does not make them available >for use by plperl. External perl modules can only be used in plperlu. > > Comments? Sounds good. FYI the maintainers of Safe are aware of (at least) two exploits which are being considered at the moment. You might want to soften the wording in http://developer.postgresql.org/pgdocs/postgres/plperl-trusted.html "There is no way to ..." is a stronger statement than can be justified. The docs for Safe http://search.cpan.org/~rgarcia/Safe-2.23/Safe.pm#WARNING say "The authors make no warranty, implied or otherwise, about the suitability of this software for safety or security purposes". Tim. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
Tim Bunce wrote: FYI the maintainers of Safe are aware of (at least) two exploits which are being considered at the moment. You might want to soften the wording in http://developer.postgresql.org/pgdocs/postgres/plperl-trusted.html "There is no way to ..." is a stronger statement than can be justified. Perhaps "There is no way provided to ...". The docs for Safe http://search.cpan.org/~rgarcia/Safe-2.23/Safe.pm#WARNING say "The authors make no warranty, implied or otherwise, about the suitability of this software for safety or security purposes". Well, we could put in similar weasel words I guess. But after all, Safe's very purpose is to provide a restricted execution environment, no? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
On Wed, 2010-03-03 at 11:33 -0500, Andrew Dunstan wrote: > > > > Well, we could put in similar weasel words I guess. But after all, > Safe's very purpose is to provide a restricted execution environment, no? We already do, in our license. Joshua D. Drake > > cheers > > andrew > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quarters
On 3 March 2010 14:34, Bruce Momjian wrote: > Scott Bailey wrote: >> Tom Lane wrote: >> > Asher Hoskins writes: >> >> I can't seem to get to_timestamp() or to_date() to work with quarters, >> > >> > The source code says >> > >> > * We ignore Q when converting to date because it is not >> > * normative. >> > * >> > * We still parse the source string for an integer, but it >> > * isn't stored anywhere in 'out'. >> > >> > That might be a reasonable position, but it seems like it'd be better to >> > throw an error than silently do nothing. Anybody know what Oracle does >> > with this? >> >> +1 for throwing error. >> Oracle 10g throws ORA-01820: format code cannot appear in date input format. > > Well, I can easily make it do what you expect, and I don't see many > error returns in that area of the code, so I just wrote a patch that > does what you would expect rather than throw an error. > > test=> select to_date('2010-1', '-Q'); > to_date > > 2010-01-01 > (1 row) I don't think this is the way to go. Why should the "date" for quarter 1, 2010 be the first date of that quarter? Why not the last date? Why not some date in between? A quarter on its own doesn't assist us in producing a *date* result, which is after all the purpose of the to_date() function. I first proposed ignoring the Q field back in 2007 [1]. My motivation for not throwing an error was that I think the main use-case for to_date() would be importing data from another system where dates are in a predictable but non-standard format. If such a date included the quarter, the user might expect to be able to include the quarter in his format string. For example, you're trying to import a date that is written as "Wed 3rd March, Q1 2010". You might give to_date a format string like 'Dy FMDDTH Month, "Q"Q ' and expect to get the correct answer. If we start throwing an error on the Q field, then users would have to resort to some strange circumlocution to get around it. Having said all of that, it's been pointed out to me in the past that Oracle compatibility is the main goal of these functions, so if we're going to change the behaviour of Q in to_date(), I think it should be in order to move closer to Oracle's treatment. I certainly don't think we should get back into the business of delivering an exact answer to an inexact question. So a +1 for throwing the error per Tom Lane and Scott Bailey. Cheers, BJ [1] http://archives.postgresql.org/message-id/37ed240d0707170747p4f5c26ffx63fff2b5750c6...@mail.gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quarters
Brendan Jurd wrote: > > Well, I can easily make it do what you expect, and I don't see many > > error returns in that area of the code, so I just wrote a patch that > > does what you would expect rather than throw an error. > > > > ? ? ? ?test=> select to_date('2010-1', '-Q'); > > ? ? ? ? ?to_date > > ? ? ? ? > > ? ? ? ? 2010-01-01 > > ? ? ? ?(1 row) > > I don't think this is the way to go. Why should the "date" for > quarter 1, 2010 be the first date of that quarter? Why not the last > date? Why not some date in between? > > A quarter on its own doesn't assist us in producing a *date* result, > which is after all the purpose of the to_date() function. > > I first proposed ignoring the Q field back in 2007 [1]. My motivation > for not throwing an error was that I think the main use-case for > to_date() would be importing data from another system where dates are > in a predictable but non-standard format. > > If such a date included the quarter, the user might expect to be able > to include the quarter in his format string. > > For example, you're trying to import a date that is written as "Wed > 3rd March, Q1 2010". You might give to_date a format string like 'Dy > FMDDTH Month, "Q"Q ' and expect to get the correct answer. If we > start throwing an error on the Q field, then users would have to > resort to some strange circumlocution to get around it. > > Having said all of that, it's been pointed out to me in the past that > Oracle compatibility is the main goal of these functions, so if we're > going to change the behaviour of Q in to_date(), I think it should be > in order to move closer to Oracle's treatment. I certainly don't > think we should get back into the business of delivering an exact > answer to an inexact question. So a +1 for throwing the error per Tom > Lane and Scott Bailey. OK, patch attached that throws an error: test=> SELECT to_date('2010-7', '-Q'); ERROR: "Q" format is not supported in to_date -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do Index: src/backend/utils/adt/formatting.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.168 diff -c -c -r1.168 formatting.c *** src/backend/utils/adt/formatting.c 26 Feb 2010 02:01:08 - 1.168 --- src/backend/utils/adt/formatting.c 3 Mar 2010 17:06:59 - *** *** 2671,2686 s += SKIP_THth(n->suffix); break; case DCH_Q: ! ! /* ! * We ignore Q when converting to date because it is not ! * normative. ! * ! * We still parse the source string for an integer, but it ! * isn't stored anywhere in 'out'. ! */ ! from_char_parse_int((int *) NULL, &s, n); ! s += SKIP_THth(n->suffix); break; case DCH_CC: from_char_parse_int(&out->cc, &s, n); --- 2671,2680 s += SKIP_THth(n->suffix); break; case DCH_Q: ! /* It is unclear which date in the quarter to return. */ ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("\"Q\" format is not supported in to_date"))); break; case DCH_CC: from_char_parse_int(&out->cc, &s, n); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quarters
Brendan Jurd writes: > For example, you're trying to import a date that is written as "Wed > 3rd March, Q1 2010". You might give to_date a format string like 'Dy > FMDDTH Month, "Q"Q ' and expect to get the correct answer. If we > start throwing an error on the Q field, then users would have to > resort to some strange circumlocution to get around it. Hmm. That's an interesting test case: if Q throws an error, there doesn't seem to be any way to do it at all, because there is no format spec for ignoring non-constant text. Conversely, Bruce's proposed patch would actually break it, because the Q code would overwrite the (correct) month information with the first-month-of-the-quarter. So at the moment my vote is "leave it alone". If we want to throw error for Q then we should provide a substitute method of ignoring a field. But we could just document Q as ignoring an integer for input. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quartersf
Tom Lane wrote: > Brendan Jurd writes: > > For example, you're trying to import a date that is written as "Wed > > 3rd March, Q1 2010". You might give to_date a format string like 'Dy > > FMDDTH Month, "Q"Q ' and expect to get the correct answer. If we > > start throwing an error on the Q field, then users would have to > > resort to some strange circumlocution to get around it. > > Hmm. That's an interesting test case: if Q throws an error, there > doesn't seem to be any way to do it at all, because there is no format > spec for ignoring non-constant text. Conversely, Bruce's proposed > patch would actually break it, because the Q code would overwrite the > (correct) month information with the first-month-of-the-quarter. > > So at the moment my vote is "leave it alone". If we want to throw > error for Q then we should provide a substitute method of ignoring > a field. But we could just document Q as ignoring an integer for > input. Here is an updated patch that honors 'Q' only if the month has not been previously supplied: test=> SELECT to_date('2010-3', '-Q'); to_date 2010-07-01 (1 row) test=> SELECT to_date('2010-04-3', '-MM-Q'); to_date 2010-04-01 (1 row) but it fails if a later month is specified: test=> select to_date('2010-3-05', '-Q-MM'); ERROR: conflicting values for "MM" field in formatting string DETAIL: This value contradicts a previous setting for the same field type. even if the month is in that quarter but not the first month of the quarter. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do Index: src/backend/utils/adt/formatting.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.168 diff -c -c -r1.168 formatting.c *** src/backend/utils/adt/formatting.c 26 Feb 2010 02:01:08 - 1.168 --- src/backend/utils/adt/formatting.c 3 Mar 2010 17:18:43 - *** *** 2671,2685 s += SKIP_THth(n->suffix); break; case DCH_Q: ! ! /* !* We ignore Q when converting to date because it is not !* normative. !* !* We still parse the source string for an integer, but it !* isn't stored anywhere in 'out'. !*/ ! from_char_parse_int((int *) NULL, &s, n); s += SKIP_THth(n->suffix); break; case DCH_CC: --- 2671,2684 s += SKIP_THth(n->suffix); break; case DCH_Q: ! /* Honor "Q" only if a month has not previously be set */ ! if (out->mm == 0) ! { ! from_char_parse_int(&out->mm, &s, n); ! out->mm = (out->mm - 1) * 3 + 1; ! } ! else/* ignore */ ! from_char_parse_int((int *) NULL, &s, n); s += SKIP_THth(n->suffix); break; case DCH_CC: -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quarters
On 4 March 2010 04:08, Tom Lane wrote: > Brendan Jurd writes: >> For example, you're trying to import a date that is written as "Wed >> 3rd March, Q1 2010". You might give to_date a format string like 'Dy >> FMDDTH Month, "Q"Q ' and expect to get the correct answer. If we >> start throwing an error on the Q field, then users would have to >> resort to some strange circumlocution to get around it. > > Hmm. That's an interesting test case: if Q throws an error, there > doesn't seem to be any way to do it at all, because there is no format > spec for ignoring non-constant text. Not entirely true. It's possible, it's just not at all obvious: =# select to_date('Wed 3rd March, Q1 2010', 'Dy FMDDTH Month, "QQ" '); to_date 2010-03-03 (1 row) Anything in a format string which is quoted is ignored. Or to put it another way, putting stuff in quotes is telling to_date() that the characters in those positions are not important to you and should not be used to help construct the date result. It doesn't actually check that the characters in the source string match what you have put inside the quotes, it just skips over the quoted number of characters. I doubt anyone unfamiliar with the source code of the function would ever devise the above solution, and it's an ugly hack reliant on a quirk anyway. So a user in-the-field would probably just resort to running a regexp_replace() over the text in order to strip out the quarter before passing it to to_date(). > So at the moment my vote is "leave it alone". If we want to throw > error for Q then we should provide a substitute method of ignoring > a field. But we could just document Q as ignoring an integer for > input. Sounds good to me. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quartersf
Bruce Momjian writes: > Here is an updated patch that honors 'Q' only if the month has not been > previously supplied: That's just weird. It's not even self-consistent much less unsurprising --- having the behavior be dependent on field order is really horrid. I think what people would actually want for this type of situation is a way to specify "there is an integer here but I want to ignore it". Q as it's presently constituted accomplishes that, though it is not documented as doing so. Brendan's comment about quoted text is interesting, but it doesn't really solve the problem because of the possibility of the integer field being variable width. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] double and numeric conversion
if (p1 > buf) ++ * --p1; else { ++ * --p1; ??? does it even compile ? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] double and numeric conversion
On 3 Mar 2010, at 17:41, Grzegorz Jaskiewicz wrote: > if (p1 > buf) >++ * --p1; >else { > > > > > ++ * --p1; ??? > > does it even compile ? Oh, I can see, that it is *(--p1)++ ,mea culpa. Which doesn't change the fact, that the code is rather messy imo. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quartersf
Tom Lane wrote: > Bruce Momjian writes: > > Here is an updated patch that honors 'Q' only if the month has not been > > previously supplied: > > That's just weird. It's not even self-consistent much less > unsurprising --- having the behavior be dependent on field order is > really horrid. > > I think what people would actually want for this type of situation is > a way to specify "there is an integer here but I want to ignore it". > Q as it's presently constituted accomplishes that, though it is not > documented as doing so. Brendan's comment about quoted text is > interesting, but it doesn't really solve the problem because of the > possibility of the integer field being variable width. I have updated the comments that "Q" is ignored by to_date and to_timestamp, and added a C comment. I also documented the double-quote input-skip behavior of to_timestamp, to_number, and to_date. Applied patch attached. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.506 diff -c -c -r1.506 func.sgml *** doc/src/sgml/func.sgml 23 Feb 2010 16:14:25 - 1.506 --- doc/src/sgml/func.sgml 3 Mar 2010 22:27:36 - *** *** 5089,5095 Q ! quarter RM --- 5089,5095 Q ! quarter (ignored by to_date and to_timestamp) RM *** *** 5209,5215 even if it contains pattern key words. For example, in '"Hello Year "', the will be replaced by the year data, but the single Y in Year !will not be. --- 5209,5218 even if it contains pattern key words. For example, in '"Hello Year "', the will be replaced by the year data, but the single Y in Year !will not be. In to_date, to_number, !and to_timestamp, double-quoted strings skip the number of !input characters contained in the string, e.g. "XX" !skips two input characters. Index: src/backend/utils/adt/formatting.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.168 diff -c -c -r1.168 formatting.c *** src/backend/utils/adt/formatting.c 26 Feb 2010 02:01:08 - 1.168 --- src/backend/utils/adt/formatting.c 3 Mar 2010 22:27:38 - *** *** 2671,2680 s += SKIP_THth(n->suffix); break; case DCH_Q: - /* ! * We ignore Q when converting to date because it is not ! * normative. * * We still parse the source string for an integer, but it * isn't stored anywhere in 'out'. --- 2671,2682 s += SKIP_THth(n->suffix); break; case DCH_Q: /* ! * We ignore 'Q' when converting to date because it is ! * unclear which date in the quarter to use, and some ! * people specify both quarter and month, so if it was ! * honored it might conflict with the supplied month. ! * That is also why we don't throw an error. * * We still parse the source string for an integer, but it * isn't stored anywhere in 'out'. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Linux start script updates
Tom Lane wrote: > "Kevin Grittner" writes: > > Exactly. With Fedora respecting the standard in this regard, I'm > > convinced we should, too. In reviewing things based on Peter's > > question, I did start to have doubts about *not* special-casing > > "status" -- it has its own set of values and 5 is not assigned, so > > using it seems wrong. It seems like it should be 3 ("program is not > > running"). Agreed? > > Probably. I think that in practice most scripts are not very tense > about this --- as long as the exit code is 0 or not-0 per spec, which > not-0 value is reported is not so exciting to most people. So, do the startup scripts as they exist in CVS need any adjustment? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom GUCs still a bit broken
Where are we on this? --- Andrew Dunstan wrote: > > It seems like Custom GUCs are still in need of some work, as shown in my > recent email. In particular, they are not transaction safe - if a > transaction attempts to do DefineCustomFooVariable() and that > transaction aborts, the placeholder setting that it used is already gone > by the time it tries to roll back GUC settings. I think this code at the > end of define_custom_variable() > > /* > * Free up as much as we conveniently can of the placeholder > structure > * (this neglects any stack items...) > */ > set_string_field(pHolder, pHolder->variable, NULL); > set_string_field(pHolder, &pHolder->reset_val, NULL); > > free(pHolder); > > > needs to be removed and instead we need to save pHolder in a list along > with the GUC level, to be processed later by AtEOXact_GUC(), which would > do the right thing according to whether or not it had a commit or an abort. > > I want to get this fixed before we consider custom settings for plperl > that have possible security implications. > > Thoughts? > > cheers > > andrew > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Getting to 9.0 beta
I whittled my 1.5k open emails down to a handful and have updated the 9.0 open items list with the items I feel should be addressed before beta: http://wiki.postgresql.org/wiki/PostgreSQL_9.0_Open_Items#Bugs I added only five items. Of course, I doubled the list size too. :-( I will probabably start on the release notes in a week. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom GUCs still a bit broken
Nowhere, really. I tried to fix it, but could not come up with anything remotely clean. cheers andrew Bruce Momjian wrote: Where are we on this? --- Andrew Dunstan wrote: It seems like Custom GUCs are still in need of some work, as shown in my recent email. In particular, they are not transaction safe - if a transaction attempts to do DefineCustomFooVariable() and that transaction aborts, the placeholder setting that it used is already gone by the time it tries to roll back GUC settings. I think this code at the end of define_custom_variable() /* * Free up as much as we conveniently can of the placeholder structure * (this neglects any stack items...) */ set_string_field(pHolder, pHolder->variable, NULL); set_string_field(pHolder, &pHolder->reset_val, NULL); free(pHolder); needs to be removed and instead we need to save pHolder in a list along with the GUC level, to be processed later by AtEOXact_GUC(), which would do the right thing according to whether or not it had a commit or an abort. I want to get this fixed before we consider custom settings for plperl that have possible security implications. Thoughts? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom GUCs still a bit broken
Andrew Dunstan wrote: > > Nowhere, really. I tried to fix it, but could not come up with anything > remotely clean. So it is something for the TODO list or a 9.0 open item? --- > > cheers > > andrew > > > Bruce Momjian wrote: > > Where are we on this? > > > > --- > > > > Andrew Dunstan wrote: > > > >> It seems like Custom GUCs are still in need of some work, as shown in my > >> recent email. In particular, they are not transaction safe - if a > >> transaction attempts to do DefineCustomFooVariable() and that > >> transaction aborts, the placeholder setting that it used is already gone > >> by the time it tries to roll back GUC settings. I think this code at the > >> end of define_custom_variable() > >> > >> /* > >> * Free up as much as we conveniently can of the placeholder > >> structure > >> * (this neglects any stack items...) > >> */ > >> set_string_field(pHolder, pHolder->variable, NULL); > >> set_string_field(pHolder, &pHolder->reset_val, NULL); > >> > >> free(pHolder); > >> > >> > >> needs to be removed and instead we need to save pHolder in a list along > >> with the GUC level, to be processed later by AtEOXact_GUC(), which would > >> do the right thing according to whether or not it had a commit or an abort. > >> > >> I want to get this fixed before we consider custom settings for plperl > >> that have possible security implications. > >> > >> Thoughts? > >> > >> cheers > >> > >> andrew > >> > >> > >> > >> -- > >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-hackers > >> > > > > -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom GUCs still a bit broken
Bruce Momjian wrote: Andrew Dunstan wrote: Nowhere, really. I tried to fix it, but could not come up with anything remotely clean. So it is something for the TODO list or a 9.0 open item? It's not new, AFAIK. So arguably fixing it could just be a TODO. I don't have time right now to go down that rathole. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Custom GUCs still a bit broken
Andrew Dunstan wrote: > > > Bruce Momjian wrote: > > Andrew Dunstan wrote: > > > >> Nowhere, really. I tried to fix it, but could not come up with anything > >> remotely clean. > >> > > > > So it is something for the TODO list or a 9.0 open item? > > > > > > It's not new, AFAIK. So arguably fixing it could just be a TODO. I don't > have time right now to go down that rathole. OK, added to TODO: Have custom GUCs be transaction safe http://archives.postgresql.org/message-by-id.php?4b577e9f.8000...@dunslane.net -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Safe security
Joshua D. Drake wrote: On Wed, 2010-03-03 at 11:33 -0500, Andrew Dunstan wrote: Well, we could put in similar weasel words I guess. But after all, Safe's very purpose is to provide a restricted execution environment, no? We already do, in our license. True. I think the weasel formula I prefer here is a bit different. It might be reasonable to say something along the lines of: To the extent it is prevented by the Perl Safe module, there is no way provided to access internals of the database server process or to gain OS-level access with the permissions of the server process, as a C function can do. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
(2010/03/03 22:42), Robert Haas wrote: > 2010/3/3 KaiGai Kohei: >> (2010/03/03 14:26), Robert Haas wrote: >>> 2010/3/2 KaiGai Kohei: Is it an expected behavior? postgres=>CREATE SEQUENCE s; CREATE SEQUENCE postgres=>ALTER TABLE s RENAME sequence_name TO abcd; ALTER TABLE postgres=>CREATE TABLE t (a int primary key, b text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE postgres=>ALTER TABLE t_pkey RENAME a TO xyz; ALTER TABLE The documentation says: http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html : RENAME The RENAME forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table. There is no effect on the stored data. It seems to me the renameatt() should check relkind of the specified relation, and raise an error if relkind != RELKIND_RELATION. >>> >>> Are we talking about renameatt() or RenameRelation()? Letting >>> RenameRelation() rename whatever seems fairly harmless; renameatt(), >>> on the other hand, should probably refuse to allow this: >>> >>> CREATE SEQUENCE foo; >>> ALTER TABLE foo RENAME COLUMN is_cycled TO bob; >>> >>> ...because that's just weird. Tables, indexes, and views make sense, >>> but the attributes of a sequence should be nailed down I think; >>> they're basically system properties. >> >> I'm talking about renameatt(), not RenameRelation(). > > OK. Your original example was misleading because you had renameatt() > in the subject line but the actual SQL commands were renaming a whole > relation (which is a reasonable thing to do). > >> If our perspective is these are a type of system properties, we should >> be able to reference these attributes with same name, so it is not harmless >> to allow renaming these attributes. >> >> I also agree that it makes sense to allow renaming attributes of tables >> and views. But I don't know whether it makes sense to allow it on indexs, >> like sequence and toast relations. > > I would think not. OK, the attached patch forbid renameatt() on relations expect for tables and views. postgres=# CREATE TABLE t (a serial primary key, b text); NOTICE: CREATE TABLE will create implicit sequence "t_a_seq" for serial column "t.a" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE postgres=# ALTER TABLE t_a_seq RENAME sequence_name TO foo; ERROR: "t_a_seq" is not a table or view postgres=# ALTER TABLE t_pkey RENAME a TO var; ERROR: "t_pkey" is not a table or view postgres=# ALTER TABLE t RENAME b TO baz; ALTER TABLE postgres=# SELECT * FROM t; a | baz ---+- (0 rows) Ideally, I think it is not necessary to call CheckRelationOwnership() at ExecRenameStmt() with OBJECT_COLUMN, because ATSimplePermissions() also apply same checks later. However, it might be done in the context of access control reworks for the ALTER TABLE statement. Thanks, -- KaiGai Kohei pgsql-renameatt-check-relkind.patch Description: application/octect-stream -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting to 9.0 beta
On Wed, Mar 3, 2010 at 6:28 PM, Bruce Momjian wrote: > I whittled my 1.5k open emails down to [five items] That's a pretty good compression ratio. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
2010/3/3 KaiGai Kohei : > (2010/03/03 22:42), Robert Haas wrote: >> 2010/3/3 KaiGai Kohei: >>> (2010/03/03 14:26), Robert Haas wrote: 2010/3/2 KaiGai Kohei: > Is it an expected behavior? > > postgres=> CREATE SEQUENCE s; > CREATE SEQUENCE > postgres=> ALTER TABLE s RENAME sequence_name TO abcd; > ALTER TABLE > > postgres=> CREATE TABLE t (a int primary key, b text); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "t_pkey" for table "t" > CREATE TABLE > postgres=> ALTER TABLE t_pkey RENAME a TO xyz; > ALTER TABLE > > The documentation says: > http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html > > : > RENAME > The RENAME forms change the name of a table (or an index, sequence, > or view) or > the name of an individual column in a table. There is no effect on > the stored data. > > It seems to me the renameatt() should check relkind of the specified > relation, and > raise an error if relkind != RELKIND_RELATION. Are we talking about renameatt() or RenameRelation()? Letting RenameRelation() rename whatever seems fairly harmless; renameatt(), on the other hand, should probably refuse to allow this: CREATE SEQUENCE foo; ALTER TABLE foo RENAME COLUMN is_cycled TO bob; ...because that's just weird. Tables, indexes, and views make sense, but the attributes of a sequence should be nailed down I think; they're basically system properties. >>> >>> I'm talking about renameatt(), not RenameRelation(). >> >> OK. Your original example was misleading because you had renameatt() >> in the subject line but the actual SQL commands were renaming a whole >> relation (which is a reasonable thing to do). >> >>> If our perspective is these are a type of system properties, we should >>> be able to reference these attributes with same name, so it is not harmless >>> to allow renaming these attributes. >>> >>> I also agree that it makes sense to allow renaming attributes of tables >>> and views. But I don't know whether it makes sense to allow it on indexs, >>> like sequence and toast relations. >> >> I would think not. > > OK, the attached patch forbid renameatt() on relations expect for tables > and views. OK, I will review it. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting to 9.0 beta
Robert Haas wrote: > On Wed, Mar 3, 2010 at 6:28 PM, Bruce Momjian wrote: > > I whittled my 1.5k open emails down to [five items] > > That's a pretty good compression ratio. It helped that everyone promptly answered my email questions asking for status, so it wasn't as hard as it sounds. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] elimination of code duplication in DefineOpFamily()
It looks like for me the bottom half of the DefineOpFamily() is a block copied and pasted from CreateOpFamily(). It has identical logic, variable names and source code comments. Perhaps, we can replace this code block by CreateOpFamily() call. Thanks, -- KaiGai Kohei pgsql-cleanup-defineopfamily.patch Description: application/octect-stream -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Upcoming back-branch releases
It's been almost three months since our last minor releases, and we've accumulated enough fixes to justify a new set of updates. The core team is planning to wrap new tarballs next Thursday for release Monday 3/15. If you've got any pending back-branch fixes, get 'em in soon. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers