Re: [ADMIN] postgres types

[Peter Eisentraut]

Bruce Momjian writes:

> OID should be an unsigned int.  Let us know if there are any problems.

The oid type is for storing oids, not arbitrary numbers. It might happen
to do that too, but it isn't designed for it. To get unsigned numbers use
a check contraint. Then everyone knows what's going on; don't rely on the
particulars of the implementation.

Anyway ...

peter=# create table foo (a oid, b text);
CREATE
peter=# insert into foo values (-1, 'zzz');
INSERT 18730 1
peter=# select * from foo;
 a  |  b
+-
 -1 | zzz
(1 row)


> > Can I have UNSIGNED integer in PostgreSQL ?
> > E.g. how should I describe type of field to have
> > full range of 32 bits? If I describe filed as INT4,

int8/bigint

> > I'll get SIGNED integer, insn't it?

Per SQL, all numbers are signed.


-- 
Peter Eisentraut  Sernanders väg 10:115
[EMAIL PROTECTED]   75262 Uppsala
http://yi.org/peter-e/Sweden

RE: [ADMIN] rules problem

[Nicolas Huillard]

Here is my $0.02 :
* when you create "id SERIAL", Postgres remembers to call function nextval on each 
insertion,
* the rule's NEW.id item uses the function nextval itself instead of it's result
This explains why the ID's are what you see :
* first of all, you insert the log, calling nextval for the SERIAL (id=1 in the log)
* then you actually insert the data into the colors table (first row has id=2)
* then you insert a second time : first into the log (id=3) then into the actual table 
(id=4)
This make me think about date constants : 'now' is a constant that have a different 
value each time you call it. In your case, the rule must use then constant 'nextval', 
which increments the actual sequence on each call.
Either this is a bug... or a feature...
I don't see any genral workaround here. Maybe there is another way of retreiving the 
actual inserted data (other than NEW.id)

Yours,

Nicolas Huillard
G.H.S
Directeur Technique
Tél : +33 1 43 21 16 66
Fax : +33 1 56 54 02 18
mailto:[EMAIL PROTECTED]
http://www.ghs.fr


-Message d'origine-
De: Vladimir V. Zolotych [SMTP:[EMAIL PROTECTED]]
Date:   lundi 8 mai 2000 18:00
À:  [EMAIL PROTECTED]
Objet:  [ADMIN] rules problem

Hello all,

Encountered the problem with using RULEs. Cannot log
(e.g. write some info about insertions into sepearate table)
insertions properly. Detailed description (not long or sophisticated)
follows:

I do:

1) CREATE TABLE colors (id SERIAL, color TEXT);

2) Create table for log info:

   CREATE TABLE colors_log (color_id INT4, color TEXT);

3) Create RULE that actually makes log:

   CREATE RULE log_color
   AS ON INSERT
   TO colors
   DO INSERT INTO colors_log VALUES (NEW.id, NEW.color);

4) Make some insertions:

   INSERT INTO colors (color) VALUES ('red');

   The same for 'green', 'blue'.

5) SELECT * FROM colors;

   id|color
   --+-
2|red
4|green
6|blue

   Here appears the first question:
   why 'id' is 2, 4, 6, not 1,  2, 3?

7) SELECT * FROM colors_log;

   color_id|color
   +-
  1|red
  3|green
  5|blue

   The problem is: the 'id's differ. E.g.,
   In colors_log table the saved 'id' are wrong.

Thanks!



--
Vladimir Zolotych [EMAIL PROTECTED]

 

[ADMIN] rules problem

[Vladimir V. Zolotych]

Hello all,
Encountered the problem with using RULEs. Cannot log
(e.g. write some info about insertions into sepearate table)
insertions properly. Detailed description (not long or sophisticated)
follows:
I do:
1) CREATE TABLE colors (id SERIAL, color TEXT);
2) Create table for log info:
   CREATE TABLE colors_log (color_id INT4, color TEXT);
3) Create RULE that actually makes log:
   CREATE RULE log_color
   AS ON INSERT
   TO colors
   DO INSERT INTO colors_log VALUES (NEW.id, NEW.color);
4) Make some insertions:
   INSERT INTO colors (color) VALUES ('red');
 
   The same for 'green', 'blue'.
 
5) SELECT * FROM colors;
   id|color
   --+-
    2|red
    4|green
    6|blue
 
   Here appears the first question:
   why 'id' is 2, 4, 6, not 1,  2, 3?
 
7) SELECT * FROM colors_log;
   color_id|color
   +-
  1|red
  3|green
  5|blue
 
   The problem is: the 'id's differ. E.g.,
   In colors_log table the saved 'id' are wrong.
Thanks!
 
 
-- 
Vladimir Zolotych [EMAIL PROTECTED]
 

[ADMIN]

[Andrei N.Sobchuck]

Hi!
When perform an 'UPDATE ...' query, it was executed for 1 minute,
when I perform the query second time, it was executed for 2 minutes,
third time - for 3 minutes.
After doing vacuum the update query was executed for 1 min again.
The number of records in table updated not changed between executing update
queries.

Is it normal.

Andrei.