Обсуждение: How do we find serial types
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? -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> 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. - Tom
Thus spake Thomas G. Lockhart > > 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. That would be good. Mostly I am interested in knowing that it was declared serial in order to write generic forms handlers and such. Basically I want to not insert values into serial types so that the system will generate the sequential number. Actually, there is another way to do this. If the system were to always generate a sequential number on insert and ignore any value specified, that would work to. Unfortunately that wouldn't work on a dump and restore. > 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. At least with the primary key there is a field so we can kluge it. I wrote some documentation that you should now have explaining how to do that. Argh! I fscked up! I sent the docs to Tom Ivar Helbekkmo by mistake because he sent me the inet skeleton docs. He explained the error but I misread his message. I thought he was sending it on to you but he said for me to send it on. I'll get that to you right away. Sorry about that. I'll bounce you those messages right away. Did you get the inet/cidr regression test queries? I'll bounce that message to you too just in case. And I was so happy that I got everything in before the Friday date. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> Argh! I fscked up! I sent the docs to Tom Ivar Helbekkmo by mistake > because he sent me the inet skeleton docs. He explained the error but > I misread his message. I thought he was sending it on to you but he > said for me to send it on. I'll get that to you right away. Sorry > about that. I'll bounce you those messages right away. Did you get > the inet/cidr regression test queries? I'll bounce that message to > you too just in case. Uh, sorry but I haven't seen anything. Still waiting for any/all inet docs and regression tests, and will freeze with or without in one more day. But it sounds like you have something, so be sure to send it soon. > And I was so happy that I got everything in before the Friday date. Happiness sure is fleeting sometimes, eh? :) - Tom^H^H^HThomas
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@debis.com (Jan Wieck) #
D'Arcy J.M. Cain wrote: > Actually, there is another way to do this. If the system were to always > generate a sequential number on insert and ignore any value specified, that > would work to. Unfortunately that wouldn't work on a dump and restore. The way I described it, using a kind of pre-rewriting, would work for dump/restore. Data moved in by COPY doesn't invoke any rule since there is no query to rewrite (except you dump it as INSERT statements). COPY is a utility statement and they aren't rewritten at all. On the other hand we could change pg_dump to omit SERIAL and CONSTRAINT information at CREATE TABLE and later turn all this on like triggers/rules (using ALTER TABLE?) when the data is put back by COPY/INSERT. I think for 6.5 it would also be good to restrict the use of COPY to tables the user has RULE permissions for. I think such a check doesn't exist already and if I'm right, COPY is a way to get around rules ON INSERT. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #