RE: [BUGS] foreign key check makes a big LOCK
> Short Description > foreign key check makes a big LOCK > > Long Description > in: src/backend/utils/adt/ri_triggers.c > > RI_FKey_check(), RI_FKey_noaction_upd(), RI_FKey_noaction_del(), etc.. > checking the referential with SELECT FOR UPDATE. > > After BEGIN TRANSACTION: the INSERT/DELETE/UPDATE calling > foreign-key checks, and the SELECT FOR UPDATE locking ALL > matched rows in referential table. > > I modify ri_triggers.c (remove "FOR UPDATE"). This working.. > but is correct? It's not. If one transaction inserts FK 1 and another one deletes PK 1 at the same time both will succeed. RI triggers should perform dirty reads (and test if returned tuples alive/dead/being updated by concurrent transaction) instead of SELECT FOR UPDATE but dirty reads are not implemented, yet. Vadim
[BUGS] plpgsql function error after alter table add
NOTE: Please retract similar, earlier entry (hit send by mistake :-)
Synopsis:
If you create a function with a table as the argument, and later alter that
table and add a new column, the function fails saying "incorrect number of
attributes for table _tablename_" Dropping and readding the function does
not fix this problem.
Error:
ERROR: query didn't return correct # of attributes for $1
Reproduce:
Of course you'll need to add plpgsql as a valid language on your database in
order for this to work:
CREATE TABLE car_make(
make text primary key);
CREATE TABLE car(
vin text primary key,
make text references car_make(make));
INSERT INTO car_make VALUES('Toyota');
INSERT INTO car VALUES('mytoyota','Toyota');
CREATE FUNCTION carname(car) RETURNS text AS '
declare
tmp alias for $1;
begin
return tmp.vin || ''_'' || tmp.make;
end;
' LANGUAGE 'plpgsql';
SELECT carname(car) FROM car;
ALTER TABLE car ADD purchase_date timestamp;
SELECT carname(car) FROM car;
DROP FUNCTION carname(car);
CREATE FUNCTION carname(car) RETURNS text AS '
declare
tmp alias for $1;
begin
return tmp.vin || ''_'' || tmp.make;
end;
' LANGUAGE 'plpgsql';
SELECT carname(car) FROM car;
/* above causes error */
Results:
CREATE FUNCTION carname(car) RETURNS text AS '
junk'#declare
junk'#tmp alias for $1;
junk'#begin
junk'#return tmp.vin || ''_'' || tmp.make;
junk'#end;
junk'#' LANGUAGE 'plpgsql';
end;
' LANGUAGE 'plpgsql';
CREATE
junk=#SELECT carname(car) FROM car;
carname
-
mytoyota_Toyota
(1 row)
junk=#ALTER TABLE car ADD purchase_date timestamp;
SELECT carname(car) FROM car;
ALTER
junk=#SELECT carname(car) FROM car;
carname
-
mytoyota_Toyota
(1 row)
junk-#DROP FUNCTION carname(car);
DROP
junk=#CREATE FUNCTION carname(car) RETURNS text AS '
junk'#declare
junk'#tmp alias for $1;
junk'#begin
junk'#return tmp.vin || ''_'' || tmp.make;
junk'#end;
junk'#' LANGUAGE 'plpgsql';
CREATE
junk=#SELECT carname(car) FROM car;
ERROR: query didn't return correct # of attributes for $1
junk=#/* above causes error */
--
Tim Dunnington
Sr. Integration Engineer
Healthcare.com
[BUGS] renaming columns do not update foreign key triggers
Synopsis:
If you create a table with a foreign key reference, and later change the name
of the referenced field in the referenced table, subsequent updates on the
given table will result in an error that the old field name does not exist.
I've also seen the corollary to this, where an update to the field name in
the base table also causes the error.
Error Message:
ERROR: constraint : table _tablename) does not have an attribute
_old-field-name_
Expected behavior:
Alter table should also update the foreign key functions and triggers as
necessary, such that changing the name of any field will work. At the very
least, changing the name of a foreign key constraint an error on the alter
table statement, something along the lines of "you cannot change the name of
a column which is a foreign key reference".
Workaround:
If you have to change the name of a foreign key field, or the field it
references, you have to drop the given table, recreate it, and reload it with
data.
I suppose there is a way to rewrite the functions and triggers too, but I
haven't toyed with that.
Reproduce:
This example demonstrates changing the name of a field on both ends of a
foreign key constraint.
CREATE TABLE car_make (
type text primary key);
CREATE TABLE car (
vin text primary key,
make text references car_make(type));
INSERT INTO car_make VALUES('Toyota');
INSERT INTO car VALUES('yadayada','Toyota');
ALTER TABLE car_make RENAME type TO make;
INSERT INTO car VALUES('anothercarvin','Toyota');
/* the above errors */
INSERT INTO car_make VALUES('Satrun');
ALTER TABLE car RENAME make TO type;
UPDATE car_make SET make='Saturn' WHERE make='Satrun';
/* the above errors */
Results:
INSERT INTO car_make VALUES('Toyota');
INSERT INTO car_make VALUES('Satrun');
ALTER TABLE car RENAME make TO type;
UPDATE car_make SET make='Saturn' WHERE make='Satrun';
/* the above errors */
INSERT 365791 1
junk=#INSERT INTO car VALUES('yadayada','Toyota');
INSERT 365792 1
junk=#ALTER TABLE car_make RENAME type TO make;
ALTER
junk=#INSERT INTO car VALUES('anothercarvin','Toyota');
ERROR: constraint : table car_make does not have an attribute type
junk=#/* the above errors */
junk-#INSERT INTO car_make VALUES('Satrun');
INSERT 365794 1
junk=#
junk=#ALTER TABLE car RENAME make TO type;
ALTER
junk=#UPDATE car_make SET make='Saturn' WHERE make='Satrun';
ERROR: constraint : table car does not have an attribute make
junk=#/* the above errors */
[BUGS] source code of jdbc drivers
this is to inquire whether postgresql provides the source code of jdbc drivers(any type).if so please let me know the url as soon as possible. as i need to customize it to suite my organizations requirements THANKS CHEERS __ Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/
Re: [BUGS] source code of jdbc drivers
Quoting najeeb ahmed <[EMAIL PROTECTED]>: > > > > this is to inquire whether postgresql provides the > source code of jdbc drivers(any type).if so please let > me know the url as soon as possible. > > as i need to customize it to suite my organizations > requirements The sources are included with the main postgresql sources under the src/interfaces/jdbc directory. Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
[BUGS] PostgreSQL 7.0.3 freeze
PostgreSQL 7.0.3 continues freezing after restoring a table with 60.000 rows after about 8.000 rows. 920 pts/0S 0:00 psql -d database -f temp_registration.dump 922 ?D 0:08 /usr/bin/postgres localhost pguser database commit 976 ?S 0:00 /usr/bin/postgres localhost pguser database idle 1040 ?D 0:00 /usr/bin/postgres localhost pguser template1 commit Distribution: RedHat Linux 7.0
[BUGS] 7.1beta1 JDBC bug?
I think that I've found a little bug in the 7.1beta1 JDBC drivers.
Attached is a simple test case which produces the problem on my setup
(LinuxPPC 2000, on Apple PowerMac G3-400Mhz, 512MB). It would seem that
the drivers and/or the DBMS has a problem with nested queries, even
simple ones.
Here's the stacktrace:
Bad Long
at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:284)
at Test.go(Test.java:35)
at Test.main(Test.java:15)
I'm using the 7.0.2 JDBC drivers against the 7.1beta1 backend and
everything seems to be working fine.
Stu.
--
Stu Coates
Chelmsford, England U.K.
http://www.StuCoates.com/
The day Microsoft makes something that doesn't suck is probably the day
they start making vacuum cleaners.
import java.sql.*;
import java.io.*;
public class Test
{
public static void main(String [] args)
{
if(args.length!=3) {
System.err.println("Usage: ");
System.exit(1);
}
Test app=new Test();
try {
app.go(args[0],args[1],args[2]);
}
catch(Exception e) {
System.err.println(e.getMessage());
e.printStackTrace();
}
}
private void go(String url,String uid,String pwd) throws Exception
{
Class.forName("org.postgresql.Driver");
Connection connection=DriverManager.getConnection(url,uid,pwd);
connection.setAutoCommit(false);
PreparedStatement s=connection.prepareStatement("select mykey from mytest");
ResultSet rs=s.executeQuery();
while(rs.next()){
PreparedStatement s2=connection.prepareStatement("select * from mytest
where mykey=?");
s2.setLong(1,rs.getLong(1));
ResultSet rs2=s2.executeQuery();
while(rs2.next()) {
System.out.println(rs2.getLong(1) + " " + rs2.getString(2));
}
rs2.close();
s.close();
}
rs.close();
s.close();
connection.commit();
connection.close();
}
}
drop table mytest;
create table mytest (mykey int8, foo varchar(20));
insert into mytest(mykey,foo) values (0,'stuff');
insert into mytest(mykey,foo) values (1,'stuff');
insert into mytest(mykey,foo) values (2,'stuff');
insert into mytest(mykey,foo) values (3,'stuff');
insert into mytest(mykey,foo) values (4,'stuff');
insert into mytest(mykey,foo) values (5,'stuff');
insert into mytest(mykey,foo) values (6,'stuff');
insert into mytest(mykey,foo) values (7,'stuff');
insert into mytest(mykey,foo) values (8,'stuff');
insert into mytest(mykey,foo) values (9,'stuff');
[BUGS] CREATE FUNCTION distributors_id_max() RETURNS INT4 AS Bad Timestamp Format thrown by jdbc7.0-1.2.jar
I have created a table with a DATETIME column: create table registereduser ( ... registeredDate DATETIME NOT NULL ) I inserted a new record into this table, with the registeredDate = new Timestamp(new Date().getTime())) On retrieving this record, the following stack trace ensued: Exception in thread "main" Bad Timestamp Format at 19 in 2000-12-11 09:43:50.20-05 at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:447) at weblogic.db.jdbc.Value.(Value.java:129) at weblogic.db.jdbc.Record.internal_fetch(Record.java:1376) at weblogic.db.jdbc.DataSet.fetchRecords(DataSet.java:284) at weblogic.db.jdbc.TableDataSet.fetchRecords(TableDataSet.java:173) at weblogic.db.jdbc.DataSet.fetchRecords(DataSet.java:234) at test.database.TInsert_1.run(TInsert_1.java:138) at test.database.TInsert_1.main(TInsert_1.java:26) The weblogic classes are dbKona. I expect that for some reason the JDBC driver is not dealing with the '-5' time zone data (I am in US Central Time). - kevin
[BUGS] obsolete version of python bindings
Hi, in PostgreSQL 7.0.3 is still PyGreSQL 2.4 that version is very old, current version is 3.1 can you include the new version? PygreSQL 3.1 is Python DB-API 2.0 compliant, and it's a big advantage over 2.4 thanks, Miroslav Vasko
[BUGS] important question
Dear sir,
we are finding it difficult to connect to
postgresql database using the postgresql driver i.e.
using jdbc connectivity..trying it on apache web
server on linux
otherwise in ordinary applications it is working fine.
Could u plz suggest us a remedy as soon as possible..
thanking you sir,
najeeb
the pgm is here..
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
public class tester extends HttpServlet
{
public void doGet(HttpServletRequest
req,HttpServletResponse res) throws
ServletException,IOException
{
res.setContentType("text/html");
PrintWriter pw=res.getWriter();
try
{
Driver
d=(Driver)Class.forName("postgresql.Driver").newInstance();
DriverManager.registerDriver(d);
java.sql.Connection
con=DriverManager.getConnection("jdbc:postgresql:najeeb","najeeb","");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select hi from
test");
while(rs.next())
{ String str=rs.getString(1);pw.println(str); }
pw.close();rs.close();st.close();con.close();
}
catch(Exception ex)
{
pw.println(ex);
}
}
}
__
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/
[BUGS] select cash_out('2'); crashes backend on 7.0.2
Hello,
I was just experimenting, trying to see if I could find a function that
would format a numeric value like 'money' with Postgres 7.0.2. Here's
what happened:
##
cascade=> select cash_out(2);
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
##
The same thing happened with Postgres 6.5.3. Here's my full version:
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
I'm sure if what I tried is even valid input, but I'm guessing this is
not a desired result in any case. :)
Thanks for the great software and good luck with this!
A frequent Postgres user,
-mark
personal website } Summersault Website Development
http://mark.stosberg.com/{ http://www.summersault.com/
[BUGS] constrains of array
Hi.
I use version from cvs.
I want to have array of referencies to another table, so I do:
cms=# create table a (a int primary key); create table b (b int[]
references a);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
table 'a'
CREATE
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
But now I can't add anything to table b:
cms=# insert into a values (1);
INSERT 52069 1
cms=# insert into b values ('{1}');
ERROR: Unable to identify an operator '=' for types 'int4' and '_int4'
You will have to retype this query using an explicit cast
I expect either of following:
1) REFERENCIES from array to scalar should create correct trigger (check
every entity of array)
or
2) It should be error in *creation* of table if there is no comparasion
operator for constrain check
or
3) Error in insert should say something about trigger, otherwise user have
to guess what is wrong with his query.
Regards,
ASK
[BUGS] Unable to convert null timestamp to date. Bug?
Strange, this works: select * from users where last_visit > now() + 7; -- last_visit is nullable, of type timestamp But this doesn't select * from users where last_visit + 7 > now(); ERROR: Unable to convert null timestamp to date -- yes, there are users where last_visit IS NULL BTW, this works too: select null+1; -- obviously returns NULL System is FreeBSD 4.1, PgSQL 7.0.2 Anyone got a clue? Yours sincerely, Edmar Wiggers BRASMAP Information Systems +55 48 9960 2752
[BUGS] Undefined symbol: pg_encoding_to_char
After installing:
PostgreSQL version 7
and after starting:
postmaster -D {in task 2}
on a: RedHat 6.2
Linux server
running 486, 32MB
RAM
attempting to issue:
createbd testdb {in task 1}
as user:
postgres
I get the following
message:
/usr/bin/psql: error
in loading shared libraries: /usr/bin/psql: undefined symbol:
pg_encoding_to_char
createdb: database
creation failed
--
Ben Trout III, CNE
ICQ#19524206
mailto:[EMAIL PROTECTED]
Re: [BUGS] select cash_out('2'); crashes backend on 7.0.2
I can confirm this is crashes in 7.1 too.
>
> Hello,
>
> I was just experimenting, trying to see if I could find a function that
> would format a numeric value like 'money' with Postgres 7.0.2. Here's
> what happened:
>
> ##
> cascade=> select cash_out(2);
> pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> We have lost the connection to the backend, so further processing is
> impossible. Terminating.
> ##
>
> The same thing happened with Postgres 6.5.3. Here's my full version:
> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
>
> I'm sure if what I tried is even valid input, but I'm guessing this is
> not a desired result in any case. :)
>
> Thanks for the great software and good luck with this!
>
> A frequent Postgres user,
>
> -mark
>
> personal website } Summersault Website Development
> http://mark.stosberg.com/{ http://www.summersault.com/
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [BUGS] obsolete version of python bindings
PostgreSQL 7.1 will have pygresql 3.1. [ Charset ISO-8859-2 unsupported, converting... ] > Hi, > > in PostgreSQL 7.0.3 is still PyGreSQL 2.4 > that version is very old, current version is 3.1 > > can you include the new version? PygreSQL 3.1 > is Python DB-API 2.0 compliant, and it's a big > advantage over 2.4 > > thanks, > Miroslav Vasko > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [BUGS] constrains of array
> Hi.
>
> I use version from cvs.
>
> I want to have array of referencies to another table, so I do:
> cms=# create table a (a int primary key); create table b (b int[]
> references a);
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
> table 'a'
> CREATE
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
>
> But now I can't add anything to table b:
> cms=# insert into a values (1);
> INSERT 52069 1
> cms=# insert into b values ('{1}');
> ERROR: Unable to identify an operator '=' for types 'int4' and '_int4'
> You will have to retype this query using an explicit cast
>
> I expect either of following:
> 1) REFERENCIES from array to scalar should create correct trigger (check
> every entity of array)
Actually, no. Technically, it should be seeing if the exact same array is
on the other side. AFAIK, There is no definition of the RI constraint
in the spec that works that way. We've talked about implementing such
a beast as an extension, but there are some issues about indexing that
we need to look at before we can do that in general.
> 2) It should be error in *creation* of table if there is no comparasion
> operator for constrain check
Possibly, although it currently doesn't to allow you to add the operator
after you do the references. The benefits of that might be outweighed by
the problems if you don't add the operator.
> 3) Error in insert should say something about trigger, otherwise user have
> to guess what is wrong with his query.
Definately true, but unfortunately i'm not sure if it's possible in the
current implementation (it does another query inside the trigger, and
that's what's erroring -- I don't think it knows it's in the trigger at
that time).
[BUGS] Solaris ODBC - linker flag needs to be set when building shared objects
Mike McDonough ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Solaris ODBC - linker flag needs to be set when building shared objects Long Description Linker flag which needs to be set when building shared objects to prevent a symbol in the executable overriding one in the shared object: '-Bsymbolic' needs to be set in Makefile.shlib. This flag is set for linux builds, but not for solaris. LDFLAGS_ODBC := -Bsymbolic Sample Code Makefile.shlib: CUT --- ifeq ($(PORTNAME), linux) install-shlib-dep := install-shlib shlib := lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) LDFLAGS_SL:= -Bdynamic -shared -soname $(shlib) LDFLAGS_ODBC := -Bsymbolic -lc -lm SHLIB_LINK+= -lc CFLAGS+= $(CFLAGS_SL) endif ifeq ($(PORTNAME), solaris_i386) install-shlib-dep := install-shlib shlib := lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) LDFLAGS_SL:= -G #+++rwhit, 12/12/00 added -Bsymbolic to fix SQLPrimaryKeys() - it's calls # to other SQL*() functions were being resolved by # the driver manager versions rather than the pgsql # versions LDFLAGS_ODBC := -Bsymbolic SHLIB_LINK+= -ldl -lsocket -lresolv -lnsl -lm -lc CFLAGS+= $(CFLAGS_SL) endif ifeq ($(PORTNAME), solaris_sparc) install-shlib-dep := install-shlib shlib := lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) LDFLAGS_SL:= -G #+++rwhit, 12/12/00 added -Bsymbolic to fix SQLPrimaryKeys() - it's calls # to other SQL*() functions were being resolved by # the driver manager versions rather than the pgsql # versions LDFLAGS_ODBC := -Bsymbolic SHLIB_LINK+= -ldl -lsocket -lresolv -lnsl -lm -lc CFLAGS+= $(CFLAGS_SL) endif --- END CUT --- No file was uploaded with this report
[BUGS] case with distinct
This works: select orgid, case when status = 'y' then '1' else '0' end from vend This doesn't: select distinct orgid, case when status = 'y' then '1' else '0' end from vend The only difference is the absence of distinct clause. bug?
Re: [BUGS] constrains of array
Stephan Szabo <[EMAIL PROTECTED]> writes: >> 2) It should be error in *creation* of table if there is no comparasion >> operator for constrain check > Possibly, although it currently doesn't to allow you to add the operator > after you do the references. The benefits of that might be outweighed by > the problems if you don't add the operator. I can't see any good reason not to require the operator to pre-exist. In fact, there's a good argument that we should require the two columns to have the exact same datatype. Otherwise, equality may be a pretty fuzzy concept. Think about varchar vs bpchar comparison, for example --- shall we consider trailing blanks significant? Which column will drive the choice? In any case, it's certainly a bad idea that the system accepted an FK constraint relating int[] to int. regards, tom lane
Re: [BUGS] case with distinct
Merrill Oveson <[EMAIL PROTECTED]> writes: > This doesn't: > select distinct > orgid, > case when status = 'y' then '1' else '0' end > from vend Try select distinct orgid, case when status = 'y' then '1'::text else '0'::text end from vend 7.1 is less picky about unknown-type constants... regards, tom lane
Re: [BUGS] constrains of array
On Tue, 12 Dec 2000, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> 2) It should be error in *creation* of table if there is no comparasion > >> operator for constrain check > > > Possibly, although it currently doesn't to allow you to add the operator > > after you do the references. The benefits of that might be outweighed by > > the problems if you don't add the operator. > > I can't see any good reason not to require the operator to pre-exist. The only case I could see would be if there was some case where you had equality operators that needed to be defined after the table that had the references constraint (not sure if that could ever happen). You could use alter table in these cases though. > In fact, there's a good argument that we should require the two columns > to have the exact same datatype. Otherwise, equality may be a pretty > fuzzy concept. Think about varchar vs bpchar comparison, for example > --- shall we consider trailing blanks significant? Which column will > drive the choice? I think the spec only requires them to be comparable I believe (I'd assume that the match predicate rules would apply), so would an equality operator be sufficient to tell that?
Re: [BUGS] select cash_out('2'); crashes backend on 7.0.2
Added to TODO: * SELECT cash_out(2) crashes because of opaque > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> cascade=> select cash_out(2); > >> pqReadData() -- backend closed the channel unexpectedly. > > > I can confirm this is crashes in 7.1 too. > > You can get this sort of result with almost any input or output function > :-(. The problem is that they're mostly misdeclared to take type > "opaque", which for no good reason is also considered to mean "accepts > any input type whatever", which means you can pass a value of any type > at all to an input or output function. > > There have been some past discussions about introducing a little more > rigor into the type system's handling of I/O functions, but it ain't > gonna happen for 7.1 ... > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [BUGS] plpgsql function error after alter table add
"Tim Dunnington" <[EMAIL PROTECTED]> writes: > If you create a function with a table as the argument, and later alter that > table and add a new column, the function fails saying "incorrect number of > attributes for table _tablename_" Dropping and readding the function does > not fix this problem. I think this is not so much a plpgsql bug as a side effect of the rather shoddy implementation of ALTER TABLE ADD COLUMN. It doesn't go through and alter any actual tuples in the table, it just adds the column to the schema. This works safely only because the heap-tuple access routines will generally return a NULL without complaint when asked to access a column number that's beyond the last column actually present in a tuple. So it *looks* like your new column has NULLs everywhere, when in fact no NULL is actually stored. This breaks down, however, as soon as anyone inquires into the number of attributes actually present in any tuple. If you'd like to live dangerously you could try removing the error check at line 2685 of pl_exec.c (in current sources; not sure about line number in 7.0.*, but look for the quoted error message). I'm not inclined to do that as an official patch however. Someday we're going to bite the bullet and rewrite ALTER TABLE ADD COLUMN anyway. A cruder workaround is to do "UPDATE table SET col = NULL" after adding a new column with ALTER TABLE, so that the "virtual" nulls become real. If you've already added a few real entries, you can still do it safely with "UPDATE table SET col = NULL WHERE col IS NULL". (Man, that's a bizarre-looking command...) regards, tom lane
Re: [BUGS] constrains of array
On Tue, 12 Dec 2000, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> In fact, there's a good argument that we should require the two columns > >> to have the exact same datatype. > > > I think the spec only requires them to be comparable > > Oh, in that case never mind ... but I'd still favor checking for > existence of the comparison operator right away. Okay, will do. :)
Re: [BUGS] constrains of array
Stephan Szabo <[EMAIL PROTECTED]> writes: >> In fact, there's a good argument that we should require the two columns >> to have the exact same datatype. > I think the spec only requires them to be comparable Oh, in that case never mind ... but I'd still favor checking for existence of the comparison operator right away. regards, tom lane
[BUGS] pqReadData() -- backend closed the channel unexpectedly
Christian HUGUES ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description pqReadData() -- backend closed the channel unexpectedly Long Description I've installed the PostgreSQL 7.0.3 on a RedHat 6.1, everything was well during installation. When I want to create a temporary table (with the any user account), I've got an error on the backend. "pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormallu before or while processing the request." I've tried with the Postgres 6.5.3 and everything is good !!! I don't have this error. Why this error on the Postgres 7.0.3 ? Sample Code create temporary table test () inherits (test); No file was uploaded with this report
Re: [BUGS] select cash_out('2'); crashes backend on 7.0.2
Bruce Momjian <[EMAIL PROTECTED]> writes: >> cascade=> select cash_out(2); >> pqReadData() -- backend closed the channel unexpectedly. > I can confirm this is crashes in 7.1 too. You can get this sort of result with almost any input or output function :-(. The problem is that they're mostly misdeclared to take type "opaque", which for no good reason is also considered to mean "accepts any input type whatever", which means you can pass a value of any type at all to an input or output function. There have been some past discussions about introducing a little more rigor into the type system's handling of I/O functions, but it ain't gonna happen for 7.1 ... regards, tom lane
Re: [ADMIN] Re: [BUGS] LockReplace: xid table corrupted
I remember that you cannot open more than 16 sessiones at same time by default, if more than that you have to reset, I believe you can find how from documentation. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 11 Dec 2000, Dronamraju Rajesh wrote: > thanks tomBut strange thing is that my database > has only 15 tables totally...which are very small > too... > > so how would be the way of me reaching to 1000 > tables??? > > and why do i get an error message "sorry too many > clients" ??? > > Regards > Rajesh > --- Tom Lane <[EMAIL PROTECTED]> wrote: > > Dronamraju Rajesh <[EMAIL PROTECTED]> writes: > > > database:- Postgres 6.5.2 ( pl dont suggest to > > upgrade > > > to 7.0.2. once this problem is solved. then i can > > do > > > testing of my app with postgres 7.0.2) > > > > If you update to 7.0.3, you'll probably find this > > problem > > goes away. It sounds to me like you are running > > into a bogus > > debug check in the older code that assumed > > LockRelease should > > never need to release more than a thousand locks. > > > > If you don't want to update, try not touching 1000 > > tables in > > the same transaction. I'm guessing you do a lot of > > large-object > > slinging ... otherwise it's not that easy to get to > > 1000 ... > > > > regards, tom lane > > > __ > Do You Yahoo!? > Yahoo! Shopping - Thousands of Stores. Millions of Products. > http://shopping.yahoo.com/ >
Re: [BUGS] plpgsql function error after alter table add
Tom Lane wrote: > > "Tim Dunnington" <[EMAIL PROTECTED]> writes: > > If you create a function with a table as the argument, and later alter that > > table and add a new column, the function fails saying "incorrect number of > > attributes for table _tablename_" Dropping and readding the function does > > not fix this problem. > > I think this is not so much a plpgsql bug as a side effect of the rather > shoddy implementation of ALTER TABLE ADD COLUMN. Seems a plpgsql's simple bug. > It doesn't go through > and alter any actual tuples in the table, it just adds the column to the > schema. It seems a nice implementation. Regards. Hiroshi Inoue
