A bad trigger was created to update an id with the next value of a sequence.
CREATE OR REPLACE FUNCTION "f_products"() RETURNS "opaque" AS ' BEGIN NEW.ID = NEXTVAL(''products_id_seq''); RETURN NEW; END' LANGUAGE 'plpgsql' CREATE TRIGGER "t_classes" AFTER INSERT ON "products" FOR EACH ROW EXECUTE PROCEDURE f_products();
This does not work because to change a value in the row, you must use a BEFORE trigger rather than an AFTER trigger.
It was also pointed out that it is much easier to use a SERIAL data type or to have a DEFAULT set to the value of nextval() to achieve the same effect. You can find more informatino in the documentation.
Do indexes need maintenance to stay current?
Indexes maintain themselves as you insert, update and delete from your table--mostly.
There is a known issue where some deleted index space could be recovered but isn't. This is the trade-off between being able to run vacuum while the database is live, or having to lock table access in order to vacuum.
The exact problem is that empty index pages are not collapsed. This means that index space doesn't shrink when the table and therefore the number of indexes shrink. When the range of an index constantly grows, the indexes will also constantly grow even if you prune the low end.
Re-indexing will re-construct the index from scratch, freeing any lingering unused index pages. This can be done occasionally, depending on the shrinkage of the table and the narrowing of index values.
There a base table, "dad" and sub tables "kid1", "kid2", etc. who are created with INHERITS( dad ).
create table dad (...); create table kid1 (...) inherits (dad); create table kid2 (...) inherits (dad);Deleting from the "dad" table will delete all of the rows of all of the kids, wiping out all of the data in your table hierarchy.
delete from dad;This is the correct defined behavior. (But don't try this at home!)
To delete from the base table and not the sub tables, use:
delete from only dad;To delete from a sub table, delete explicitly:
delete from kid1 where ....;
The postgres.conf value SQL_INHERITANCE changes the default behavior so that it conforms to the behavior prior to 7.1. Prior to 7.1 commands did not necessarily extend to the sub tables. However, it is recommended that you use ONLY in your queries instead of relying on old semantics.
Sequence function currval('sequence_name') will fetch the value of a sequence as selected by the previous nextval('sequence_name') issued on the the current connection.
This is true whether you are using Perl/DBI or any other interface. And it is true if other connections are also issuing calls to nextval() and currval() for the same sequence. As long as you maintain the current connection, you can get the last value you requested with nextval('sequence_name');
It was suggested that:
select last_value from sequence_name;would get you the last value. And it will, but you would not be guaranteed that it was your last value. Someone else may have changed it. This method is strongly discouraged. Use the functions instead.
There is a one to many relationship between "tableone" and "tablemany".
CREATE TABLE "tableone" ( "sym" varchar(5) NOT NULL, PRIMARY KEY ("sym") ); CREATE TABLE "tablemany" ( "id" int4 NOT NULL DEFAULT nextval('res_id_seq'::text), "sym" varchar(5) REFERENCES tableone ON UPDATE CASCADE, PRIMARY KEY ("id") );
The problem is to find the records in tableone that don't have any
corresponding records in tablemany.
select a.sym from tableone a where a.sym not in (select b.sym from tablemany b where b.sym = a.sym);However, the query would be significantly more effective if there were a secondary index on tablemany(sym).
create index tablemany_sym on tablemany(sym);
This query was meant to find bad data. However, you could prevent the problem by using triggers so that the tables could be updated together or put the initial inserts into one transaction that would be committed or rolled back together.
In 7.3, you can now use the keyword DEFAULT in an insert statement to indicate that you want the value to be its default. This is usually used when you are not using column names in you INSERT statement.
insert into table bubba values ('truck','washer',DEFAULT,'gun rack',DEFAULT,36,24,36);
In 7.2 and earlier, you could only omit columns from the far right of the table. Otherwise you needed to use a column name list.
insert into table bubba values (vehicle, cargo, equip, age, size, depth) ('truck','washer','gun rack',36,24,36);
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009