Re: [BUGS] creating a table with a serial column sets currval
On Thu, 18 Oct 2007, Tom Lane wrote:
Looks like any alter sequence command will do this. The serial case uses
alter sequence owned by under the hood which exposes this. The problem is
that altering the sequence puts it into the SeqTable cache list when it
really shouldn't be.
It's not that it gets put in the cache, it's that read_info gets called
(setting elm->increment). I think we probably should clean this up by
creating a separate flag in that struct that explicitly says "currval is
valid", which would be set by nextval(), setval() (because historically
it's acted that way), and I guess ALTER SEQUENCE RESTART WITH (for
consistency with setval()).
Personally I think setval should only set validCurrval and the last_value
if iscalled = true. If is_called = false I think it should retain the
previous last_value if any until the next nextval call.
jurka=# create sequence s;
CREATE SEQUENCE
jurka=# select nextval('s');
nextval
-
1
(1 row)
jurka=# select setval('s',5, false);
setval
5
(1 row)
jurka=# select currval('s');
currval
-
5
(1 row)
Should return 1 instead of 5.
Kris Jurka
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [JDBC] Re: [BUGS] 'on insert do instead' rule with a where clause responds 'INSERT 0 0'
Julius Stroffek wrote: There is only one option that comes to my mind - always return Statment.SUCCESS_NO_INFO in executeBatch (or possibly only depending on some java property). I can not see any simple solution for Statement.executeUpdate since the number of rows affected may differ depending on the rules and might be also difficult to calculate. The server is reporting to the driver that zero rows were affected (not "unknown", *zero*) so I don't see any reason why the driver should not report that as the number of rows affected. Returning SUCCESS_NO_INFO reduces the usefulness of the driver in the other 98% of cases where there are no INSTEAD rules. The protocol docs say: CommandComplete (B) [...] For an INSERT command, the tag is INSERT oid rows, where rows is the number of rows inserted. oid is the object ID of the inserted row if rows is 1 and the target table has OIDs; otherwise oid is 0. So if the server is not returning "the number of rows inserted" then either the server has a bug or the protocol docs are wrong. -O ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] creating a table with a serial column sets currval
Kris Jurka <[EMAIL PROTECTED]> writes:
>> jurka=# create table t (c serial);
>> NOTICE: CREATE TABLE will create implicit sequence "t_c_seq" for serial
>> column "t.c"
>> CREATE TABLE
>> jurka=# select currval('t_c_seq');
>> currval
>> -
>> 1
>> (1 row)
>>
>> I would expect it to say that currval wasn't set like so:
... as indeed it did say, up till 8.2, so I concur this is a bug.
> Looks like any alter sequence command will do this. The serial case uses
> alter sequence owned by under the hood which exposes this. The problem is
> that altering the sequence puts it into the SeqTable cache list when it
> really shouldn't be.
It's not that it gets put in the cache, it's that read_info gets called
(setting elm->increment). I think we probably should clean this up by
creating a separate flag in that struct that explicitly says "currval is
valid", which would be set by nextval(), setval() (because historically
it's acted that way), and I guess ALTER SEQUENCE RESTART WITH (for
consistency with setval()).
Should any of the ALTER SEQUENCE options *reset* such a flag?
Offhand I don't see any...
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] creating a table with a serial column sets currval
On Thu, 18 Oct 2007, Kris Jurka wrote:
jurka=# create table t (c serial);
NOTICE: CREATE TABLE will create implicit sequence "t_c_seq" for serial
column "t.c"
CREATE TABLE
jurka=# select currval('t_c_seq');
currval
-
1
(1 row)
I would expect it to say that currval wasn't set like so:
Looks like any alter sequence command will do this. The serial case uses
alter sequence owned by under the hood which exposes this. The problem is
that altering the sequence puts it into the SeqTable cache list when it
really shouldn't be.
Kris Jurka
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[BUGS] creating a table with a serial column sets currval
jurka=# create table t (c serial);
NOTICE: CREATE TABLE will create implicit sequence "t_c_seq" for serial
column "t.c"
CREATE TABLE
jurka=# select currval('t_c_seq');
currval
-
1
(1 row)
I would expect it to say that currval wasn't set like so:
jurka=# create sequence myseq;
CREATE SEQUENCE
jurka=# select currval('myseq');
ERROR: currval of sequence "myseq" is not yet defined in this session
Kris Jurka
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [BUGS] 'on insert do instead' rule with a where clause responds 'INSERT 0 0'
Hi, please see my comments inline. Tom Lane wrote: Julius Stroffek <[EMAIL PROTECTED]> writes: Attached is the example script 'repro.sql' which creates two relations tab1 and tab2. It also creates a rule on tab1 which simply does insert into tab2. The insert statement into tab1 is executed afterwards. It responds with 'INSERT 0 1'. However if I would create the same rule with the where clause the response to the same insert statement is 'INSERT 0 0'. The output of the script executed through psql is in 'repro.out'. Is this a bug? No. See http://www.postgresql.org/docs/8.2/static/rules-status.html and note that you don't have an unconditional INSTEAD rule. I explored this and agree that the current PostgreSQL behavior as described in the above link is correct. However, this behavior is a serious issue when using Java Persistence through Hibernate (and probably using other providers as well). I have created a simple application running on glassfish just inserting records to the tables using Hibernate and PostgreSQL as a persistence provider. If I would use partitioning of the tables all the insert transactions would be marked for rollback and would be rolled back. After playing a bit with a very ugly code of glassfish trying to change it to report the root cause why the transactions are marked for roll back I discovered that Hibernate uses Statement.executeBatch method to execute the sql statements which then is supposed to return the number of rows affected by the passed statements. Hibernate then compares the value returned by this function with the number of records it passes to the batch. The number of affected rows is determined in PostgreSQL JDBC driver in QueryExecutorImpl.interpretCommandStatus method by parsing the command status string returned. The JDBC javadoc at http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeBatch() describes the behavior and allows to return Statement.SUCCESS_NO_INFO in a case where the number of rows is unknown. More severe issue is that JDBC spec requires a set of methods Statement.executeUpdate which are forced to return the number of rows affected and can not return Statement.SUCCESS_NO_INFO. Any thoughts how to deal with this issue? Was there a discussion on this already in JDBC driver team? There is only one option that comes to my mind - always return Statment.SUCCESS_NO_INFO in executeBatch (or possibly only depending on some java property). I can not see any simple solution for Statement.executeUpdate since the number of rows affected may differ depending on the rules and might be also difficult to calculate. Thanks Cheers Julo create table tab1 (id int); create table tab2 (id int); create rule tab1_insert as on insert to tab1 do instead insert into tab2 values (new.id); insert into tab1 values (100); select * from tab2; drop rule tab1_insert on tab1; create rule tab1_insert as on insert to tab1 where 1<2 do instead insert into tab2 values (new.id); delete from tab2; select * from tab2; insert into tab1 values (100); select * from tab2; drop rule tab1_insert on tab1; drop table tab2; drop table tab1; CREATE TABLE CREATE TABLE CREATE RULE INSERT 0 1 id - 100 (1 row) DROP RULE CREATE RULE DELETE 1 id (0 rows) INSERT 0 0 id - 100 (1 row) DROP RULE DROP TABLE DROP TABLE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
