When locking is involved, does a transaction wait for access to a row or table, or does it just fail back to the calling code? Would it be up to my PHP code to keep hammeing for access to a row/table, or could a user defined function do that?
I'd like to have a certain object in my PHP application have essentially individual SERIAL rows per object created site wide. So only one script instance at a time in apache can have access to a row to read and increment a value in a row.
Example, (totally random idea, example only), any user on site can create a group. Each group assigns group_user_ids per site member in his group, starting at zero for each new user joining a group, no matter their site_id.
My choices so far seem to be:
IN PHP, Use a system file for locking only one instance of the class gets access to the table.
IN PHP, Use the transaction failure to hammer the database for one instance of the class.
IN PHP, Use the transaction failure to hammer the database for each ROW's instance of a class.
IN POSTGRESQL, use the transaction failure to hammer the database for each ROW's instance of a class.
But maybe there's more to the locking than failed transactions for UPDATE, some kind of sequential queueing of access to tables or rows for transactions?
I'm trying to minimize the interfaces, cpu time, etc involved in getting access to the table.
extremely basic SQL for this idea.
CREATE TABLE group (
group_id SERIAL NOT NULL,
CONSTRAINT PK_group PRIMARY KEY (group_id)
);
CREATE TABLE singletons_for_last_grp_mbr_id_issued (
group_id INTEGER NOT NULL,
last_grp_mbr_id_issued INTEGER DEFAULT 0 NOT NULL,
CONSTRAINT PK_singletons PRIMARY KEY (counts_per_main, main_id)
);
CREATE UNIQUE INDEX IDX_One_Group_Row_Only ON singletons_for_last_grp_mbr_id_issued (group_id);
ALTER TABLE singletons_for_last_grp_mbr_id_issued
ADD CONSTRAINT group_singletons_for_last_grp_mbr_id_issued
FOREIGN KEY (group_id) REFERENCES group (group_id)
Dennis Gearon
Signature Warning
----------------
EARTH has a Right To Life
I agree with Bolivian President Evo Morales
# The right to life: "The right for no ecosystem to be eliminated by the irresponsible acts of human beings."
# The right of biosystems to regenerate themselves: "Development cannot be infinite. There's a limit on everything."
# The right to a clean life: "The right for Mother Earth to live without contamination, pollution. Fish and animals and trees have rights."
# The right to harmony and balance between everyone and everything: "We are all interdependent."
See the movie - 'Inconvenient Truth'
See the movie - 'Syriana'
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
