Обсуждение: Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
Hi:
Is there a way to emulate a SELECT..FOR UPDATE to
series of LOCK/SELECT statement.
I tried the following statements using 2 psql
terminals.
T1 | T2
1) BEGIN; |
2) SELECT x | BEGIN; FROM y | WHERE y=1 | FOR UPDATE; |
3) | SELECT x | FROM y | WHERE y=1
| FOR UPDATE;
4) COMMIT; |
5) | COMMIT;
At point #3 T2 will wait, however changing the WHERE
clause to other clause such as "WHERE y=2" will allow
T2 to proceed.
- I tried changing the SELECT..FOR UPDATE into LOCK
SHARE MODE followed by a SELECT (but w/o FOR UPDATE)
but it T2 is allowed to proceed even for the clause
"where y=1".
I am surprised because according to the docs
(version 7.2), it says: ROW SHARE MODE Note: Automatically acquired by SELECT ... FOR
UPDATE. I'm assuming that the SELECT..FOR UPDATE performs
a lock in ROW SHARE MODE before the SELECT.
I also tried changing the lock mode into SHARE ROW
EXCLUSIVE MODE / EXCLUSIVE MODE but still T2 won't be
allowed to passed even the for the clause "where y=2".
Is there any to do it? I'm asking becuase our db
libaries (using libpq) always a cursor when generating
a SELECT statement thus I'm encourtering the following
error message when I use SELECT..FOR UPDATE: Cursor must be READ ONLY. DECLARE/UPDATE is not supported.
Another alternative would be studying libpq and
removing the cursors in a SELECT statement.
By the way is there any side effect / disadavtages
when I remove the "DELCARE CURSOR" statement and
change it to plain SELECT statememt in C++ codes?
Thank you in advance,
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
Ludwig Lim <lud_nowhere_man@yahoo.com> writes: > Is there a way to emulate a SELECT..FOR UPDATE to > series of LOCK/SELECT statement. No. LOCK can only acquire table-level locks; the point of SELECT FOR UPDATE is to acquire row-level locks. > I am surprised because according to the docs > (version 7.2), it says: > ROW SHARE MODE > Note: Automatically acquired by SELECT ... FOR > UPDATE. SELECT FOR UPDATE gets a table-level lock just to ensure that the table doesn't disappear from under it while it's doing its scan. The ROW SHARE lock is pretty weak though, and doesn't block anything less drastic than DROP/ALTER TABLE or VACUUM FULL. The 7.3 development docs are perhaps clearer about this; try http://developer.postgresql.org/docs/postgres/explicit-locking.html regards, tom lane
Hi i think a hit a major problem on 7.2.1.
I run 3 systems with postgresql 7.2.1.
Its a redhat 7.1 for development, a redhat 7.3 for production
and a FreeBSD 4.6.1RC2 for testing.
After long runs (with periodic (daily) vacuum analyze's)
i noticed that some of the triggers that implement referential integrity
constraints just disapeared.
Some of these triggers were still present on the FreeBSD system
(which has been idle for a month or so), whereas on the linux
systems they were absent.
Has any one have a clue??
Any comment would be really valuable at this moment of darkness.
Thanx.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
Also i must that these lost triggers implement the parent table
side of the constraint, e.g.
CREATE TABLE VslSections(
id serial NOT NULL UNIQUE PRIMARY KEY,
name varchar(20) NOT NULL UNIQUE);
CREATE TABLE MachClasses(
id serial NOT NULL UNIQUE PRIMARY KEY,
name varchar(20) NOT NULL UNIQUE,
vslsecid int4 NOT NULL,
FOREIGN KEY (vslsecid) REFERENCES VslSections (id));
Then the triggers created are :
1)
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON
"machclasses" FROM "vslsections" NOT DEFERRABLE INITIALLY IMMEDIATE FOR
EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>',
'machclasses', 'vslsections', 'UNSPECIFIED', 'vslsecid', 'id');
2)
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "vslsections" FROM
"machclasses" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'machclasses',
'vslsections', 'UNSPECIFIED', 'vslsecid', 'id');
3)
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "vslsections" FROM
"machclasses" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'machclasses',
'vslsections', 'UNSPECIFIED', 'vslsecid', 'id');
The *LOST* triggers are 2 and 3.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
I was wrong about parent side triggers only having
disappeared.
Triggers of both sides are missing.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
On Wed, 6 Nov 2002, Achilleus Mantzios wrote: > > Hi i think a hit a major problem on 7.2.1. > I run 3 systems with postgresql 7.2.1. > Its a redhat 7.1 for development, a redhat 7.3 for production > and a FreeBSD 4.6.1RC2 for testing. > > After long runs (with periodic (daily) vacuum analyze's) > i noticed that some of the triggers that implement referential integrity > constraints just disapeared. > Some of these triggers were still present on the FreeBSD system > (which has been idle for a month or so), whereas on the linux > systems they were absent. > > Has any one have a clue?? Hmm, you haven't done anything like cluster or an incomplete dump and reload have you?
On Wed, 6 Nov 2002, Stephan Szabo wrote:
> On Wed, 6 Nov 2002, Achilleus Mantzios wrote:
>
> >
> > Hi i think a hit a major problem on 7.2.1.
> > I run 3 systems with postgresql 7.2.1.
> > Its a redhat 7.1 for development, a redhat 7.3 for production
> > and a FreeBSD 4.6.1RC2 for testing.
> >
> > After long runs (with periodic (daily) vacuum analyze's)
> > i noticed that some of the triggers that implement referential integrity
> > constraints just disapeared.
> > Some of these triggers were still present on the FreeBSD system
> > (which has been idle for a month or so), whereas on the linux
> > systems they were absent.
> >
> > Has any one have a clue??
>
> Hmm, you haven't done anything like cluster or an incomplete dump and
> reload have you?
No,
Also the FreeBSD system's database was populated with data from
the production on 2002-10-22, so the problem on the FreeBSD
was partially inherited from the production databse.
>
>
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
On Thu, 7 Nov 2002, Achilleus Mantzios wrote:
> On Wed, 6 Nov 2002, Stephan Szabo wrote:
>
> > On Wed, 6 Nov 2002, Achilleus Mantzios wrote:
> >
> > >
> > > Hi i think a hit a major problem on 7.2.1.
> > > I run 3 systems with postgresql 7.2.1.
> > > Its a redhat 7.1 for development, a redhat 7.3 for production
> > > and a FreeBSD 4.6.1RC2 for testing.
> > >
> > > After long runs (with periodic (daily) vacuum analyze's)
> > > i noticed that some of the triggers that implement referential integrity
> > > constraints just disapeared.
> > > Some of these triggers were still present on the FreeBSD system
> > > (which has been idle for a month or so), whereas on the linux
> > > systems they were absent.
> > >
> > > Has any one have a clue??
> >
> > Hmm, you haven't done anything like cluster or an incomplete dump and
> > reload have you?
>
> No,
> Also the FreeBSD system's database was populated with data from
> the production on 2002-10-22, so the problem on the FreeBSD
> was partially inherited from the production databse.
Also i must add, that the database on the production system
was never dumped/reloaded since the creation of the system.
The production 7.2.1 pgsql db was created and loaded on 2002-04-20,
from a 7.1.3 pgsql on our previous Solaris box (which we replaced
with a new linux one).
The production pgsql is started/stopped only during
system shutdowns/boots.
We had some unexpected system failures due to some
Linux/MotherBoard/BIOS problems.
(I was too enthusiastic about pgsql and its stability
that i was overconfident about our database's state
after these failures).
BTW, could that be the cause of the problem??
The problem is that i didnt realize the problem until yesterday.
The only thing i am sure, is that some of the triggers lost
one both linux'es are present on the FreeBSD system,
which was populated on Oct 22, and had NO deletion activity
at all.
I plan to make a huge map of all my tables, and configure
all the pairs of tables with inter-referential integrity constraints,
pg_dump --schema-only, see which triggers are missing
and then create them by hand.
Has anyone got a better idea??
After recreating the missing triggers should i upgrade
to 7.2.3??
Thanx.
>
> >
> >
> >
>
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel: +30-10-8981112
> fax: +30-10-8981877
> email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> After recreating the missing triggers should i upgrade
> to 7.2.3??
Make that "before".
I frankly suspect pilot error here. Triggers do not simply disappear.
If you did have crash-induced corruption leading to loss of some rows
in pg_trigger, it would be exceedingly obvious because *no* operations
on the affected tables would work --- relcache would complain about the
fact that pg_class.reltriggers didn't match the number of rows in
pg_trigger. I think the missing triggers must have been removed or
disabled deliberately. (Which is not to say that it couldn't have been
a software bug, but you're barking up the wrong tree to blame it on a
crash.)
Did all the triggers of the affected tables disappear, or only some
of them?
regards, tom lane
On Thu, 7 Nov 2002, Tom Lane wrote:
> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > After recreating the missing triggers should i upgrade
> > to 7.2.3??
>
> Make that "before".
>
> I frankly suspect pilot error here. Triggers do not simply disappear.
> If you did have crash-induced corruption leading to loss of some rows
> in pg_trigger, it would be exceedingly obvious because *no* operations
> on the affected tables would work --- relcache would complain about the
> fact that pg_class.reltriggers didn't match the number of rows in
> pg_trigger. I think the missing triggers must have been removed or
> disabled deliberately. (Which is not to say that it couldn't have been
> a software bug, but you're barking up the wrong tree to blame it on a
> crash.)
> Did all the triggers of the affected tables disappear, or only some
> of them?
Just some of them.
I really dont know what happened.
Looking back at july backups the problem was already there.
I never played with system tables in production.
I hope to be able somehow to reproduce the problem,
or convince myself its my fault for some reason.
P.S.
I was surprized when i looked up in my english dictionary the
word "deliberately" :)
>
> regards, tom lane
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr