Re: [PATCHES] [HACKERS] Per-table freeze limit proposal

2005-11-16 Thread Simon Riggs
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

2005-11-16 Thread Simon Riggs
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

2005-11-16 Thread Simon Riggs
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

2005-11-16 Thread Bruce Momjian

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

2005-11-16 Thread Simon Riggs

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

2005-11-16 Thread Philip Yarra
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

2005-11-16 Thread Christopher Kings-Lynne

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

2005-11-16 Thread Andrew Dunstan
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

2005-11-16 Thread Christopher Kings-Lynne

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

2005-11-16 Thread Michael Glaesemann


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