Postgres Pro
Downloads
Home   >   mailing lists

Thread: why doesn't this work?

why doesn't this work?

From
Ashley Clark
Date:
Conceptually I don't understand why this doesn't work.

from the psql prompt:

plumbing=# begin;
BEGIN
plumbing=# insert into plan_prices
plumbing-# (plan_id, eff_date, ground, topout, final)
plumbing-# values
plumbing-# (1, '2001-01-01',1000,1000,1000);
INSERT 215620 1
plumbing=# select currval('plan_prices_id_seq');
 currval
---------
      11
(1 row)

plumbing=# SELECT * from plan_prices ;
 id | plan_id |  eff_date  | ground  | topout  |  final
----+---------+------------+---------+---------+---------
  1 |       1 | 2000-01-01 | 1000.00 | 2400.00 | 1700.00
  4 |       1 | 2000-06-01 |    0.00 |    0.00 |    0.00
  7 |       1 | 2001-01-01 | 1000.00 | 1000.00 | 1000.00
 11 |       1 | 2001-01-01 | 1000.00 | 1000.00 | 1000.00
(4 rows)

plumbing=# delete from plan_prices where id=11;
ERROR:  triggered data change violation on relation "plan_prices"
plumbing=# ABORT ;
ROLLBACK

--
shaky cellar
Attachment

Re: why doesn't this work?

From
Ashley Clark
Date:
* Ashley Clark in "[GENERAL] why doesn't this work?" dated 2000/12/09
* 18:37 wrote:

> Conceptually I don't understand why this doesn't work.
>
> from the psql prompt:

I have an even simpler example now:
plumbing=# begin;
BEGIN
plumbing=# insert into subdivs (code, name) values
plumbing-# ('VG', 'Village Grove');
INSERT 215717 1
plumbing=# delete from subdivs where code='VG';
ERROR:  triggered data change violation on relation "subdivs"
plumbing=# abort;
ROLLBACK
plumbing=# \d subdivs
         Table "subdivs"
 Attribute |   Type   | Modifier
-----------+----------+----------
 code      | char(10) | not null
 name      | char(35) | not null
Indices: subdivs_name_key,
         subdivs_pkey

But I've found another quirk... I have a set of functions that insert
and delete rows in a table (while maintaining a tree structure), these
work fine in a transaction but doing the same combination of steps in a
transaction by hand doesn't work. What's going on here?

I can provide any information needed to figure this out.

--
shaky cellar
Attachment

Re: why doesn't this work?

From
Ashley Clark
Date:
Sorry for replying to my own mail so many times but I've finally
tracked this down... somewhat.

* Ashley Clark in "Re: [GENERAL] why doesn't this work?" dated
* 2000/12/09 22:17 wrote:

> But I've found another quirk... I have a set of functions that insert
> and delete rows in a table (while maintaining a tree structure), these
> work fine in a transaction but doing the same combination of steps in a
> transaction by hand doesn't work. What's going on here?

This isn't quite true. It's not *exactly* the same commands, I was
executing the functions from a windows frontend I'm working on and
after calling the insert_node function it proceeded to do an update on
the resulting row. If I do an update on a record before I try to delete
it it works, but if I don't it bombs the transaction. Is this expected
behaviour? Why?

I was getting ready to show this for a simple case, but apparently
there's still something I'm missing because it doesn't work. I'd really
love to know what's going on. The more I look at this the more I get
confused as to what is actually happening.

If anyone wants to try I've attached my testing code, it will create a
database to do all this in.

Well shit, I just finished copying the relevant parts over and it works
in a separate database where there are no foreign keys defined but
what's even worse is that it also now works on the table where nothing
has changed either. This is *really* aggravating.

AHA! It seems that when a table has a foreign key in another table then
this problem surfaces (I think). So now I can actually ask, Is this
supposed to happen?

Again, sorry for the long message.

--
creaky halls
Attachment

deletion of records before commit doesn't work

From
Ashley Clark
Date:
I've come up with this example and I want to know why it does what it
does.

-- snip --
You are now connected to database template1.
CREATE DATABASE
You are now connected to database testing.
psql:test2.sql:11: NOTICE:  CREATE TABLE/UNIQUE will create implicit
index 'subdivs_name_key' for table 'subdivs'
psql:test2.sql:11: NOTICE:  CREATE TABLE/PRIMARY KEY will create
implicit index 'subdivs_pkey' for table 'subdivs'
CREATE
psql:test2.sql:20: NOTICE:  CREATE TABLE will create implicit sequence
'blah_id_seq' for SERIAL column 'blah.id'
psql:test2.sql:20: NOTICE:  CREATE TABLE/PRIMARY KEY will create
implicit index 'blah_pkey' for table 'blah'
psql:test2.sql:20: NOTICE:  CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT 218198 1
BEGIN
INSERT 218199 1
psql:test2.sql:29: ERROR:  triggered data change violation on relation
"blah"
ROLLBACK
BEGIN
INSERT 218200 1
UPDATE 1
DELETE 1
ROLLBACK
-- snip --

and the test file is attached.

--
hackers ally
Attachment

Re: deletion of records before commit doesn't work

From
Jens Hartwig
Date:
Hello all,

I just reproduced the same phenomenon on my installation (PostgreSQL
7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66) and it seems
to me that maybe the index is not correctly actualized while inserting
the record? It seems that any (!) update on blah (before executing the
delete) will solve the problem:

...
insert into blah (subdiv_code) values ('VG');
delete from blah where subdiv_code='VG';
...

=> ERROR

...
insert into blah (subdiv_code) values ('VG');
update blah set subdiv_code='VG' where subdiv_code='VG';
delete from blah where subdiv_code='VG';
...

=> OK

...
insert into blah (subdiv_code) values ('VG');
update blah set subdiv_code=subdiv_code;
delete from blah where subdiv_code='VG';
...

=> OK

...
insert into blah (subdiv_code) values ('VG');
update blah set id=id;
delete from blah where subdiv_code='VG';
...

=> OK

Best regards, Jens

Ashley Clark schrieb:
>
> I've come up with this example and I want to know why it does what it
> does.
>
> -- snip --
> You are now connected to database template1.
> CREATE DATABASE
> You are now connected to database testing.
> psql:test2.sql:11: NOTICE:  CREATE TABLE/UNIQUE will create implicit
> index 'subdivs_name_key' for table 'subdivs'
> psql:test2.sql:11: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'subdivs_pkey' for table 'subdivs'
> CREATE
> psql:test2.sql:20: NOTICE:  CREATE TABLE will create implicit sequence
> 'blah_id_seq' for SERIAL column 'blah.id'
> psql:test2.sql:20: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'blah_pkey' for table 'blah'
> psql:test2.sql:20: NOTICE:  CREATE TABLE will create implicit
> trigger(s) for FOREIGN KEY check(s)
> CREATE
> INSERT 218198 1
> BEGIN
> INSERT 218199 1
> psql:test2.sql:29: ERROR:  triggered data change violation on relation
> "blah"
> ROLLBACK
> BEGIN
> INSERT 218200 1
> UPDATE 1
> DELETE 1
> ROLLBACK
> -- snip --
>
> and the test file is attached.
>
> --
> hackers ally
>
>   ------------------------------------------------------------------------
>
>    test2.sqlName: test2.sql
>             Type: Plain Text (text/plain)
>
>    Part 1.2Type: application/pgp-signature

=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel.     : +49 (0)30 2554-3282
Fax      : +49 (0)30 2554-3187
Mobil    : +49 (0)170 167-2648
E-Mail   : jhartwig@debis.com
=============================================

PostgreSQL v.7.0.2 for windows98,NT,2000

From
Chris Ian Capon Fiel
Date:

I made a easy to install PostgreSQL v.7.0.2 for Windows98, NT, 2000 with
some add features like (PgGuardian and More). Just go to this url
http://208.160.255.143 if you have any question just email me
ian@xavier.cc.xu.edu.ph or ian@cdo.philcom.com.ph


ian