Thread: Increasing size of BLCKSZ
Hi, I was wondering if somebody could tell me why the BLCKSZ of a page is so small? Because this causes considerable waste of space since there is going to be an empty slot at the end of each page that is too small to be used. What would happen if I increased it to 32K? Daniel Stolk
> Hi, > > I was wondering if somebody could tell me why the BLCKSZ of a page is so > small? Because this causes considerable waste of space since there is > going to be an empty slot at the end of each page that is too small to > be used. What would happen if I increased it to 32K? It is the size of a usual disk file system block. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi, Say I have a transaction which goes like this: begin; insert into stuff; do some nondatabase things based on last inserted id; update a date in stuff; commit; It seems that if the date is out of the database range, everything is thrown out. Is it possible to catch the database error and use a null date instead, without throwing everything away? Right now I only have two ways of proceeding: 1) commit before trying to update the date. And if things still don't work - database has some non date related error, try to delete the previously inserted stuff. 2) Make sure the dates are nice before trying to stick them in. Unless there are I'll have to do 2). Inserting stuff like '27 Oct 19999 18:21:00 +0800' causes errors even though datetime supposedly handles dates up to 1465001 AD. I guess that's expected, and I should insert big years using another less ambiguous format. What is the recommended format? Thanks, Link.
On 1999-12-08, Lincoln Yeoh mentioned: > begin; > insert into stuff; > do some nondatabase things based on last inserted id; > update a date in stuff; > commit; > > It seems that if the date is out of the database range, everything is > thrown out. Is it possible to catch the database error and use a null date > instead, without throwing everything away? Yes, use no transaction at all. :) Seriously, why do you use a transaction, when you don't want any errors caught? Transactions are defined as everything succeeds or nothing goes. If you want update to succeed anyhow, put it in it's own transaction (i.e., commit before it). > I guess that's expected, and I should insert big years using another less > ambiguous format. What is the recommended format? The safest way would be to set a date format with SET DATESTYLE TO and use that, possibly assisted by library formatting routines. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: >Seriously, why do you use a transaction, when you don't want any errors >caught? Transactions are defined as everything succeeds or nothing goes. >If you want update to succeed anyhow, put it in it's own transaction >(i.e., commit before it). I want errors caught, most errors abort everything but some errors I want to try a different update instead, if that doesn't work then only rollback everything. >> I guess that's expected, and I should insert big years using another less >> ambiguous format. What is the recommended format? > >The safest way would be to set a date format with SET DATESTYLE TO and use >that, possibly assisted by library formatting routines. OK. Link.
Peter Eisentraut wrote:
> On 1999-12-08, Lincoln Yeoh mentioned:
>
> > begin;
> > insert into stuff;
> > do some nondatabase things based on last inserted id;
> > update a date in stuff;
> > commit;
> >
> > It seems that if the date is out of the database range, everything is
> > thrown out. Is it possible to catch the database error and use a null date
> > instead, without throwing everything away?
>
> Yes, use no transaction at all. :)
>
> Seriously, why do you use a transaction, when you don't want any errors
> caught? Transactions are defined as everything succeeds or nothing goes.
> If you want update to succeed anyhow, put it in it's own transaction
> (i.e., commit before it).
I have the same problem using transactions. I want to use transactions anyway
and I want to
caught only some errors and thrown out some others depends on which kind of
error, and at end
decide to give an explicit COMMIT or ROLLBACK.
I tried other databases and they have a behavior different from PostgreSQL.
SOLID for example attends for an explicit COMMIT or ROLLBACK and doesn't have a
default rollback,
the same with Oracle8i, take a look at this Oracle example:
$sqlplus scott/tiger
SQL*Plus: Release 8.0.5.0.0 - Production on Thu Dec 9 15:00:47 1999
(c) Copyright 1998 Oracle Corporation. All rights reserved
Connected to:
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
SQL> create table a (a int);
table created.
SQL> insert into a values(1);
1 row created.
SQL> insert into a value(2);
ERROR at line 1: ORA-00928: missing SELECT keyword
SQL> commit work;
Commit complete
SQL> select * from a;
A
----------------
1
SQL>
And now the same example in PostgreSQL:
$ psql prova
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: prova
prova=> begin;
BEGIN
prova=> create table a(a int);
CREATE
prova=> insert into a values(1);
INSERT 1902953 1
prova=> insert into a value(2);
ERROR: parser: parse error at or near "value"
prova=> commit work;
END
prova=> select * from a;
ERROR: a: Table does not exist.
prova=>
Who are right. Oracle or PostgreSQL ?
Jose'
Hmm, sounds like a vote for nested transactions. The JDBC driver developer (Peter Mount) was musing that nested transaction would make large object support easier for him, as well. As to the other example of Oracle not forcing a rollback, I have a feeling that this may be specific to syntax errors in an interactive session. Implementing this sort of behavior has been discussed recently on the hackers list, in the context of making it easier to work interactively inside a transaction. I would be surprised if Oracle allows non-syntax errors inside a transaction to be ignored, or ignores anything in a non-interactive session. How about testing an example like links, where you provide data in a format the backend can't handle, (an out of range int or date or something) and see how Oracle handles that. Who's right? Well, as Peter Eisentraut said, what Postgres implements is the _definition_ of a transaction: all together, or nothing at all. This isn't just an arbitrary rule: the validity of the relational calculus depends on transactional semantics. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote: > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: > >Seriously, why do you use a transaction, when you don't want any errors > >caught? Transactions are defined as everything succeeds or nothing goes. > >If you want update to succeed anyhow, put it in it's own transaction > >(i.e., commit before it). > > I want errors caught, most errors abort everything but some errors I want > to try a different update instead, if that doesn't work then only rollback > everything. > > >> I guess that's expected, and I should insert big years using another less > >> ambiguous format. What is the recommended format? > > > >The safest way would be to set a date format with SET DATESTYLE TO and use > >that, possibly assisted by library formatting routines. > > OK. > > Link. > > > ************ >
"Ross J. Reedstrom" wrote:
>
> Hmm, sounds like a vote for nested transactions. The JDBC driver developer
> (Peter Mount) was musing that nested transaction would make large object
> support easier for him, as well.
>
> As to the other example of Oracle not forcing a rollback, I have a feeling
> that this may be specific to syntax errors in an interactive session.
> Implementing this sort of behavior has been discussed recently on the
> hackers list, in the context of making it easier to work interactively
> inside a transaction.
>
> I would be surprised if Oracle allows non-syntax errors inside a
> transaction to be ignored, or ignores anything in a non-interactive
> session. How about testing an example like links, where you provide data
> in a format the backend can't handle, (an out of range int or date or
> something) and see how Oracle handles that.
here the test:
SQL*Plus: Release 8.0.5.0.0 - Production on Mon Dec 13 21:37:48 1999
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
SQL> create table test(i numeric(12,3), d date);
Table created.
SQL> insert into TEST VALUES(999999999.999,'28-FEB-1999');
1 row created.
SQL> insert into TEST VALUES(111111111.999,'29-FEB-1999')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input
string
SQL> insert into TEST VALUES(999999999999.999,'28-FEB-1999')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
SQL> commit;
Commit complete.
select * from test;
I D
---------- ---------
1000000000 28-FEB-99
SQL> insert into TEST VALUES(1.999,'10-JAN-1999');
1 row created.
SQL> select * from test;
I D
---------- ---------
1000000000 28-FEB-99
1.999 10-JAN-99
SQL> rollback;
Rollback complete.
SQL> select * from test;
I D
---------- ---------
1000000000 28-FEB-99
Jose'
>
> Who's right? Well, as Peter Eisentraut said, what Postgres implements is
> the _definition_ of a transaction: all together, or nothing at all. This
> isn't just an arbitrary rule: the validity of the relational calculus
> depends on transactional semantics.
>
> Ross
>
> --
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St., Houston, TX 77005
>
> On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote:
> > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote:
> > >Seriously, why do you use a transaction, when you don't want any errors
> > >caught? Transactions are defined as everything succeeds or nothing goes.
> > >If you want update to succeed anyhow, put it in it's own transaction
> > >(i.e., commit before it).
> >
> > I want errors caught, most errors abort everything but some errors I want
> > to try a different update instead, if that doesn't work then only rollback
> > everything.
> >
> > >> I guess that's expected, and I should insert big years using another less
> > >> ambiguous format. What is the recommended format?
> > >
> > >The safest way would be to set a date format with SET DATESTYLE TO and use
> > >that, possibly assisted by library formatting routines.
> >
> > OK.
> >
> > Link.
> >
> >
> > ************
> >
>
> ************
"Ross J. Reedstrom" wrote:
>
> Hmm, sounds like a vote for nested transactions. The JDBC driver developer
> (Peter Mount) was musing that nested transaction would make large object
> support easier for him, as well.
>
> As to the other example of Oracle not forcing a rollback, I have a feeling
> that this may be specific to syntax errors in an interactive session.
> Implementing this sort of behavior has been discussed recently on the
> hackers list, in the context of making it easier to work interactively
> inside a transaction.
>
> I would be surprised if Oracle allows non-syntax errors inside a
> transaction to be ignored, or ignores anything in a non-interactive
> session. How about testing an example like links, where you provide data
> in a format the backend can't handle, (an out of range int or date or
> something) and see how Oracle handles that.
>
Here my test on Oracle:
$ sqlplus scott/tiger
SQL*Plus: Release 8.0.5.0.0 - Production on Mon Dec 13 23:22:31 1999
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
SQL> create table test(id int primary key,i numeric(12,3), d date);
Table created.
SQL> insert into TEST VALUES(1,999999999.999,'28-FEB-1999');
1 row created.
SQL> insert into TEST VALUES(1,111111111.999,'29-FEB-1999')
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input
string
SQL> insert into TEST VALUES(1,999999999999.999,'28-FEB-1999')
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
SQL> commit;
Commit complete.
SQL> select * from test;
ID I D
---------- ---------- ---------
1 1000000000 28-FEB-99
SQL> insert into TEST VALUES(1,1.999,'10-JAN-1999')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C001590) violated
SQL> insert into TEST VALUES(2,1.119,'10-MAR-1999');
1 row created.
SQL> select * from test;
ID I D
---------- ---------- ---------
1 1000000000 28-FEB-99
2 1.119 10-MAR-99
SQL> rollback;
Rollback complete.
SQL> select * from test;
ID I D
---------- ---------- ---------
1 1000000000 28-FEB-99
SQL> exit
Disconnected from Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
Jose'
> Who's right? Well, as Peter Eisentraut said, what Postgres implements is
> the _definition_ of a transaction: all together, or nothing at all. This
> isn't just an arbitrary rule: the validity of the relational calculus
> depends on transactional semantics.
>
> Ross
>
> --
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St., Houston, TX 77005
>
> On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote:
> > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote:
> > >Seriously, why do you use a transaction, when you don't want any errors
> > >caught? Transactions are defined as everything succeeds or nothing goes.
> > >If you want update to succeed anyhow, put it in it's own transaction
> > >(i.e., commit before it).
> >
> > I want errors caught, most errors abort everything but some errors I want
> > to try a different update instead, if that doesn't work then only rollback
> > everything.
> >
> > >> I guess that's expected, and I should insert big years using another less
> > >> ambiguous format. What is the recommended format?
> > >
> > >The safest way would be to set a date format with SET DATESTYLE TO and use
> > >that, possibly assisted by library formatting routines.
> >
> > OK.
> >
> > Link.
> >
> >
> > ************
> >
>
> ************