Re: [HACKERS] How do we find serial types

From: jwieck(at)debis(dot)com (Jan Wieck)
To: lockhart(at)alumni(dot)caltech(dot)edu (Thomas G(dot) Lockhart)
Cc: darcy(at)druid(dot)net, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] How do we find serial types
Date: 1998-10-26 17:35:58
Message-ID: m0zXqYg-000EBPC@orion.SAPserv.Hamburg.dsh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Thomas G. Lockhart wrote:

>
> > I guess it's too late for 6.4 so hopefully there is already a way to
> > find out whether a field was declared as a serial type. Am I stuck
> > using some sort of index name hack?
>
> Probably. I did the serial type implementation as a hash of hooks I
> already had in the backend (and at the behest of others, so don't blame
> me too much :). It isn't integrated as well as it could/should be, and
> we should clean this up for v6.5.
>
> It actually might be related to what we will need to do for primary and
> foreign keys, so might get fixed with that stuff. Jan has indicated an
> interest in pursuing some of this through the rules system.

I'm not sure if the rule system can address serial types as I
don't know what is required for them. Should a serial type be
forced to use nextval('serial') on INSERT and then be
forbidden to be touched until DELETE?

That's not possible with the current rule system. But the
force/check could be implemented similar to rewrite rules.
After parsing we could replace or add the TLE expression in
INSERT to be a nextval('serial') call if it isn't already
that. And on UPDATE we could simply throw an ERROR if the
serial type column appears in the targetlist and isn't a Var
reference to it's own OLD value. Other behaviour is also
possible. Since I'm very familiar with screwing around in
the targetlist from the work in the rule system I would help
doing so :-)

What I'm planning on the rule system is to bring back
attribute checks. I think it would be good to have an array
of up to 8 attribute numbers instead of the one attno
currently present. Then the actual rule will only be fired,
if one of the named attributes appears in the targetlist and
isn't a reference to OLD.same_attr. Having a rule ON UPDATE
will then only add the rule actions, if one of the attributes
could change at all (attributes not in the targetlist will
later be added by the optimizer as Var refs to the OLD tuple
and thus could never change on this query).

The next thing required then would be the implementation of a
RAISE statement. This is mostly the same thing as a SELECT,
but it will use the final result rows to produce elog()
messages. Trivial to add.

After these enhancements, constraints could be implemented by
adding special rules built from the CONSTRAINT clauses at
CREATE TABLE time. The following event/action pairs could
easily be implemented with the rule system as it already is.
The attribute checks would only suppress stupid-in-fact-noop
queries to get generated.

Event: INSERT to a table
Action: RAISE an ERROR if column values are outside of a
specified range (fixed list of possible values or a
subselect from a key table).

Event: UPDATE to a table
Action: RAISE an ERROR if new colum values are outside of a
specified range (fixed list of possible values or a
subselect from a key table).

Event: DELETE from a referenced key table
Action: Constraint delete rows in other tables that reference
the deleted key.

OR

RAISE an ERROR telling that the row cannot be deleted
while it is referenced.

Event: UPDATE on a referenced key table where the values of
the key fields change
Action: Constraint delete rows in other tables that reference
the old key.

OR

RAISE an ERROR telling that the key value cannot be
changed while it is referenced.

Isn't that all (or already more) we need for FOREIGN KEY and
ON DELETE CASCADE?

Even if it is possible with the rule system, to let
references to a key table follow on updates, such a scenario
isn't easy to setup during CREATE TABLE from the CONSTRAINT
clauses. This must be setup manually if it is really
required.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas G. Lockhart 1998-10-26 17:42:35 Re: [HACKERS] datetime regression test fails at daylight savings transitions
Previous Message Thomas G. Lockhart 1998-10-26 17:28:25 Re: [HACKERS] Re: [INTERFACES] Odbc parser error