Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Tue, 2005-11-15 at 21:58 -0300, Alvaro Herrera wrote: In fact there's no problem because in D, just like in template1, all tuples are frozen. How should we mark this on the catalogs? I don't see any way. All tuples might be frozen or might not be, the point is you don't know. That's why you can't use FrozenTransactionId. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Doesn't work either because of the argument above. What about assuming that if somebody executes a database-wide FREEZE, he knows what he is doing and thus we can mark datminxid as FrozenTransactionId? If you lock the table before FREEZE then you will guarantee that all rows will be frozen and you really can then set FrozenTransactionId. Making VACUUM FREEZE take full table locks seems like a very useful thing to me, and it would solve your problems also. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Wed, 2005-11-16 at 08:31 +, Simon Riggs wrote: On Tue, 2005-11-15 at 21:58 -0300, Alvaro Herrera wrote: In fact there's no problem because in D, just like in template1, all tuples are frozen. How should we mark this on the catalogs? I don't see any way. All tuples might be frozen or might not be, the point is you don't know. That's why you can't use FrozenTransactionId. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Doesn't work either because of the argument above. What about assuming that if somebody executes a database-wide FREEZE, he knows what he is doing and thus we can mark datminxid as FrozenTransactionId? If you lock the table before FREEZE then you will guarantee that all rows will be frozen and you really can then set FrozenTransactionId. Making VACUUM FREEZE take full table locks seems like a very useful thing to me, and it would solve your problems also. Thinking some more, when initdb issues VACUUM FREEZE we know for certain that nobody else is issuing commands against the database at that point, which is equivalent to a table lock. So we should be able to have a VACUUM FREEZE detect that and if so, set FrozenTransactionId. In normal concurrent running, I would like VACUUM FREEZE to issue a full table SHARE lock to ensure that we can set FrozenTransactionId for that also. Otherwise we will not be able to move frozen tables to read only media. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Wed, 2005-11-16 at 07:52 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Wed, 2005-11-16 at 08:31 +, Simon Riggs wrote: All tuples might be frozen or might not be, the point is you don't know. That's why you can't use FrozenTransactionId. Thinking some more, when initdb issues VACUUM FREEZE we know for certain that nobody else is issuing commands against the database at that point, which is equivalent to a table lock. So we should be able to have a VACUUM FREEZE detect that and if so, set FrozenTransactionId. In normal concurrent running, I would like VACUUM FREEZE to issue a full table SHARE lock to ensure that we can set FrozenTransactionId for that also. Otherwise we will not be able to move frozen tables to read only media. You missed one point however. Even if VACUUM FREEZE freezes all tuples, any transaction following that one is able to insert non-frozen tuples into the table. At that instant, having marked the table with Frozen is bogus, no matter what amount of locks you took on it. (OK I think we are getting there now, after my usual comms errors.) We need something stronger than VACUUM FREEZE then. Perhaps an ALTER TABLE READONLY. That would do a FREEZE and place a permanent table share lock, so we wouldn't need to set/unset the Frozen state. We'd do that as a permissions thing, rather than an actual lock. That way copies of the data could still be taken with ease and the copies would not themselves be READONLY. Not sure what you'd call it to make a whole database readonly all at once...but whatever we call it we know initdb wants to run it on template1. Then your original thought becomes fully viable. This is particularly important because I see the need to be able to freeze older data partitions and migrate them to readonly media as part of very high volume data applications. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] drop if exists
Removed from queue. Andrew is committing it. --- Andrew Dunstan wrote: OK, now it looks like this: andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# drop table if exists blurflx; NOTICE: table blurflx does not exist, skipping DROP TABLE andrew=# create table blurflx ( x text); CREATE TABLE andrew=# drop table if exists blurflx; DROP TABLE andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# revised patch attached. cheers andrew Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# drop table if exists blurflx; DROP TABLE If I read MySQL's documentation correctly, they emit a NOTE (equivalent of a NOTICE message I suppose) when IF EXISTS does nothing because the table doesn't exist. Seems like we should do likewise --- your second example here seems actively misleading. That is, I'd rather see andrew=# drop table if exists blurflx; NOTICE: table blurflx does not exist, skipping DROP TABLE regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] Numeric 508 datatype
Now we're into 8.2devel mode, its time to submit the previously discussed patch that: - reduces Numeric storage format by 2 bytes - limits scale to +/- 508 decimal places This is sufficient to allow Numeric to continue to be used as the default numeric representation for all numbers in the parser. Passes: make check on cvstip, as well as some tests not in there. Code comments explain the new format and consequences. As previously agreed, reviewing this is a 2 stage process: 1. review/possibly agree OK to commit 2. check with everybody on GENERAL that the restriction to 508 is acceptable Figure there's no point doing (2) until we agree the proposal/code is workable. As Atsushi-san point out, there is also come CPU optimization to be done on Numeric comparison, and also on other areas such as aggregation. I've not done this yet. Best Regards, Simon Riggs Index: src/backend/utils/adt/numeric.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/numeric.c,v retrieving revision 1.86 diff -c -r1.86 numeric.c *** src/backend/utils/adt/numeric.c 15 Oct 2005 02:49:29 - 1.86 --- src/backend/utils/adt/numeric.c 16 Nov 2005 17:00:51 - *** *** 84,92 #define MUL_GUARD_DIGITS 2 /* these are measured in NBASE digits */ #define DIV_GUARD_DIGITS 4 ! typedef int16 NumericDigit; #endif /* -- * The value represented by a NumericVar is determined by the sign, weight, --- 84,112 #define MUL_GUARD_DIGITS 2 /* these are measured in NBASE digits */ #define DIV_GUARD_DIGITS 4 ! typedef uint16 NumericDigit; #endif + /* - + * The storage format for NUMERIC is + * + * numeric header + * int32 varlen is the standard variable length header + * weight 8 bits in int8 so +/-127; -128 is reserved for NUMERIC_NAN + * scale 9 bits + * first 8 bits in uint8 + * 9th bit is the high order bit of first digit + * signis the second highest bit of first digit + * + * numeric digits + * an array of NumericDigits, each element storing NBASE + * digits. All trailing and leading zeros are not stored, + * apart from when the value is Zero AND the scale 255 + * in which case we store a single zero digit, with the + * sign set to NUMERIC_POS so the actual stored value + * is equal to NUMERIC_DSCALE9_1 + *-- + */ /* -- * The value represented by a NumericVar is determined by the sign, weight, *** *** 130,137 typedef struct NumericVar { int ndigits; /* # of digits in digits[] - can be 0! */ ! int weight; /* weight of first digit */ ! int sign; /* NUMERIC_POS, NUMERIC_NEG, or NUMERIC_NAN */ int dscale; /* display scale */ NumericDigit *buf; /* start of palloc'd space for digits[] */ NumericDigit *digits; /* base-NBASE digits */ --- 150,157 typedef struct NumericVar { int ndigits; /* # of digits in digits[] - can be 0! */ ! int weight; /* weight of first digit, or NUMERIC_NAN */ ! int sign; /* NUMERIC_POS, NUMERIC_NEG */ int dscale; /* display scale */ NumericDigit *buf; /* start of palloc'd space for digits[] */ NumericDigit *digits; /* base-NBASE digits */ *** *** 199,205 {2, 0, NUMERIC_POS, 1, NULL, const_one_point_one_data}; static NumericVar const_nan = ! {0, 0, NUMERIC_NAN, 0, NULL, NULL}; #if DEC_DIGITS == 4 static const int round_powers[4] = {0, 1000, 100, 10}; --- 219,225 {2, 0, NUMERIC_POS, 1, NULL, const_one_point_one_data}; static NumericVar const_nan = ! {0, NUMERIC_NAN, 0, 0, NULL, NULL}; #if DEC_DIGITS == 4 static const int round_powers[4] = {0, 1000, 100, 10}; *** *** 368,373 --- 388,399 * * External format is a sequence of int16's: * ndigits, weight, sign, dscale, NumericDigits. + * + * Note that the internal format is now different to the external format + * for the representation of NaN. In the external format, a value of + * NUMERIC_NAN_EXTERNAL in the sign field indicates NaN, which is converted + * into a NUMERIC_NAN in the weight field for the internal storage format and + * var formats. Sending data reverses this. */ Datum numeric_recv(PG_FUNCTION_ARGS) *** *** 394,413 alloc_var(value, len); value.weight = (int16) pq_getmsgint(buf, sizeof(int16)); value.sign = (uint16) pq_getmsgint(buf, sizeof(uint16)); if (!(value.sign == NUMERIC_POS || value.sign == NUMERIC_NEG || ! value.sign == NUMERIC_NAN)) ereport(ERROR, (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION), errmsg(invalid sign in external \numeric\ value))); value.dscale = (uint16) pq_getmsgint(buf, sizeof(uint16)); for (i = 0; i len; i++) { NumericDigit d = pq_getmsgint(buf, sizeof(NumericDigit));
Re: [PATCHES] pl/pgSQL doco patch
On Thu, 17 Nov 2005 01:40 am, Andrew Dunstan wrote: I am wondering we should make this warning more prominent - it would be easily missed buried on the Oracle porting section, and I have seen people caught by it lots of times. I added it to the Oracle section because I found this syntax while porting an Oracle stored proc to a pl/pgSQL function, and assumed it was an Oracle-ism. Do other RDBMSs also allow you to qualify function_name.param_name to distinguish a param from a column of the same name? If so, sure, I'll put it somewhere more general (suggestions?), and Tom, I think that would lend weight to allowing PostgreSQL to do it too (not because it's The Right Thing, but for interoperability and ease of porting). Thoughts? Regards, Philip. -- Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. - Brian W. Kernighan - Utiba Pty Ltd This message has been scanned for viruses and dangerous content by Utiba mail server and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] drop if exists
Will we get this functionality for ALL objects? Bruce Momjian wrote: Removed from queue. Andrew is committing it. --- Andrew Dunstan wrote: OK, now it looks like this: andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# drop table if exists blurflx; NOTICE: table blurflx does not exist, skipping DROP TABLE andrew=# create table blurflx ( x text); CREATE TABLE andrew=# drop table if exists blurflx; DROP TABLE andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# revised patch attached. cheers andrew Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# drop table if exists blurflx; DROP TABLE If I read MySQL's documentation correctly, they emit a NOTE (equivalent of a NOTICE message I suppose) when IF EXISTS does nothing because the table doesn't exist. Seems like we should do likewise --- your second example here seems actively misleading. That is, I'd rather see andrew=# drop table if exists blurflx; NOTICE: table blurflx does not exist, skipping DROP TABLE regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] drop if exists
Christopher Kings-Lynne said: Will we get this functionality for ALL objects? The patch does these: table, view, index, sequence, schema, type, domain, and conversion. The reason is that these are all dealt with using the same bit of the grammar, and the first 4 are pretty much completely done by the same code. I think anything else will have to be done individually, although the pattern can be copied. Perhaps we should take bids on what should/should not be covered. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] drop if exists
I think anything else will have to be done individually, although the pattern can be copied. Perhaps we should take bids on what should/should not be covered. Everything should be covered, otherwise it's just annoying for users... Chris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] drop if exists
On Nov 17, 2005, at 11:45 , Christopher Kings-Lynne wrote: I think anything else will have to be done individually, although the pattern can be copied. Perhaps we should take bids on what should/should not be covered. Everything should be covered, otherwise it's just annoying for users... Including objects that already have CREATE OR REPLACE? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org