Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
"Mikheev, Vadim" wrote: > > > > >> I assume this is not possible in 7.1? > > > > > > > >Just looked in heapam.c - I can fix it in two hours. > > > >The question is - should we do this now? > > > >Comments? > > > > > > It's a bug; how confident are you of the fix? > > 95% -:) > > > I doubt if it's a bug of SELECT. Well what > > 'concurrent UPDATE then SELECT FOR UPDATE + > > SELECT' return ? > > I'm going to add additional check to heapgettup and > heap_fetch: > SELECT seems to be able to return a different result from that of preceding SELECT FOR UPDATE even after applying your change. SELECT doesn't seem guilty but the result is far from intuitive. It seems impossoble for all queires inside such a function to use a common snapshot. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] 'Include' function in SQL scripts
I was wondering if PostgreSQL supports some kind of #include between SQL script files, to split a long script in different files. -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Max Size of a text field
Hi there I just recieved this error: 'Warning: PostgreSQL query failed: ERROR: Tuple is too big: size 13872, max size 8140 ' Is there anyting I can do about that other than tell my users just not typing so much text into the field? Kind regards, Koen Antonissen ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] serial type; race conditions
Thus spake postgresql > How does currval work if you are not inside a transaction. I have > been experimenting with inserting into a table that has a sequence. > If the insert fails (not using a transaction) because of bad client input > then the next insert gets the proper next number in the sequence. > > given sequence 1,2,3,4,5 exists > insert into table date 1/111/01 (obviously wrong) insert fails... > try again with good data, insert succeeds and gets number 6 in the > sequence. > > i'm getting what I want. A sequence number that does not increment > on a failed insert. However, how do I get the assigned sequence > number with currval when I am not using a transaction? What > happens when multiple users are inserting at the same time? > > I am trying to create a sequence with out any "missing" numbers. If > there is a failure to insert, and a sequence number is "taken". I want > the empty row. Why is it a requirement to not use transactions? That's the proper way to maintain data consistency in a relational database. I have suggested a method before to create a sequence with out any missing numbers but let me explain it further. I have never used this method with PostgreSQL but I am sure it has the features needed to make it work. Create a table called numbers with two fields, seq_name and seq_num. Pick a maximum number of concurrent users for a specific serial number. Seed the table with the sequence name and number for each sequence name and number from 1 to the maximum you picked. So, if you had 3 sequences and 10 as the max the table should have 30 entries in it. To get the next number you start a transaction. You then search for the lowest number in the table for the required sequence name that is not locked by another process (this is the part I am fuzzy on for PostgreSQL - how do you exclude locked records from your search just based on them being locked?) and lock it. You then use that number in your transaction and add your max to it. In the above example of 10 then the first time you use it you will get 1 and the number will be increased to 11. If your transaction is rolled back then you may have a hole if someone else used a number but it will be filled the next time that someone requests a number from that sequence. They aren't strictly speaking consecutive but they are complete. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Function x returns opaque in error typeidTypeRelid
Hello, I am a bit at a loss here. If I create a function which returns something it works fine. If it returns opaque I get the following: ERROR: typeidTypeRelid: Invalid type - oid = 0 What am I doing wrong ? Thanks in advance. Best regards, Roelof Sondaar dhcp=# drop function lalala(); DROP dhcp=# create function lalala() returns opaque as ' dhcp'# declare dhcp'# sname text; dhcp'# begin dhcp'# sname:= ''lalala''; dhcp'# end; dhcp'# ' language 'plpgsql'; CREATE dhcp=# dhcp=# select lalala(); ERROR: typeidTypeRelid: Invalid type - oid = 0 dhcp=# dhcp=# drop function lalala2(); DROP dhcp=# create function lalala2() returns text as ' dhcp'# declare dhcp'# sname text; dhcp'# begin dhcp'# sname:= ''lalala''; dhcp'# return sname; dhcp'# end; dhcp'# ' language 'plpgsql'; CREATE dhcp=# dhcp=# select lalala2(); lalala2 - lalala (1 row) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Help with 'now', now(), timestamp 'now', ...
Hi all,
I´m developing some procedures in my db and i want know how much time my
procedures take to execute. So, i write my first procedure (to test) as
follows:
CREATE FUNCTION TEST(INTEGER) RETURNS INTEGER AS '
DECLARE
SEQ RECORD;
BEGIN
SELECT NEXTVAL(''TIMES_ID_SEQ'') AS ID INTO SEQ;
INSERT INTO TIMES (ID, START) VALUES (SEQ.ID, NOW());
FOR I IN 1..$1 LOOP ^
INSERT INTO TEST(ANUMBER) VALUES (RANDOM()*$1);
END LOOP;
UPDATE TIMES SET END = NOW() WHERE ID = SEQ.ID;
RETURN SEQ.ID; ^
END;'
LANGUAGE 'PLPGSQL';
and executed using "SELECT TEST(1);". When it finish, i do a "SELECT
* FROM TIMES" and got:
test=> SELECT * FROM TIMES;
start | end| id
--+--+
10:27:55 | 10:27:55 | 12
10:27:55 | 10:27:55 | 13
10:30:29 | 10:30:29 | 14
10:31:29 | 10:31:29 | 15
(4 rows)
In id = 12 and id = 13, i runned two times. Then I changed the function
and run, at id = 14. Change again at id = 15.
Where is underlined (), i tried to put, 'now', timestamp 'now', etc,
and always get the same time. What i'm doing wrong?
obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)
-> TABLE TIMES (ID SERIAL, START TIME, END TIME);
-> PostgreSQL 7.0.2 under Conectiva Linux
Thanks,
Edipo Elder
[[EMAIL PROTECTED]]
_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Killing Postmaster
It's not a machine I look at often, and I realiased that it's database had never been vacuumed and had grown to about 2 gigs It is a dual PII 450, with 384 megs and U2W disks... but I started the vacuum about 10 days ago.. and when I checked it hadn't finished... I guess I'll kill it and try in verbose mode! or perhaps kill it and upgrade to 7! On Wed, 28 Mar 2001, Poul L. Christiansen wrote: > A week? That sounds much too long to me (assuming that you have > PostgresSQL funning on fairly good hardware). > > There could be something wrong with at specific table. Try to vacuum a > table one at the time using "vacuum verbose MyTable", and report any error > you may find. > > I don't understand the output of a vacuum verbose, but I'm sure other > people on this list do :-) > > Poul L. Christiansen > > On Wed, 28 Mar 2001, A James Lewis wrote: > > > > > I have a vacuum which has been running for about a week, is that too long? > > > > On Wed, 28 Mar 2001, Poul L. Christiansen wrote: > > > > > On Wed, 28 Mar 2001, Graham Vickrage wrote: > > > > > > > Hi All, > > > > > > > > What is the correct way of killing postgres 7.0 on redhat linux. > > > > > > "man pg_ctl". > > > > > > > > > > > Is there a reason why vacuum hangs on a DB with about 1.5 million rows? > > > > > > Vacuum can take a long time with 1.5M records. How long have you > > > waited? Is there any error message? > > > > > > Poul L. Christiansen > > > > > > > > > ---(end of broadcast)--- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > A. James Lewis ([EMAIL PROTECTED]) > > If your OS needs a virus detector... RUN!!! > > ...Out and get Linux! > > > A. James Lewis ([EMAIL PROTECTED]) If your OS needs a virus detector... RUN!!! ...Out and get Linux! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Killing Postmaster
I have a vacuum which has been running for about a week, is that too long? On Wed, 28 Mar 2001, Poul L. Christiansen wrote: > On Wed, 28 Mar 2001, Graham Vickrage wrote: > > > Hi All, > > > > What is the correct way of killing postgres 7.0 on redhat linux. > > "man pg_ctl". > > > > > Is there a reason why vacuum hangs on a DB with about 1.5 million rows? > > Vacuum can take a long time with 1.5M records. How long have you > waited? Is there any error message? > > Poul L. Christiansen > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > A. James Lewis ([EMAIL PROTECTED]) If your OS needs a virus detector... RUN!!! ...Out and get Linux! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
Hi Regarding my previous post, I just successfully created a unique index on pg_shadow. DON'T DO THIS!!! --- CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename) --- I couldn't create at pg_shadow_index as the pg prefix is reserved for system tables. This BROKE the database. At least I can't connect anymore with a: --- template1=# \c statements FATAL 1: Index 'pg_shadow_name_index' does not exist Previous connection kept template1=# --- If I look at the error log I get : --- ERROR: Illegal class name 'pg_shadow_index' The 'pg_' name prefix is reserved for system catalogs ERROR: Index 'pg_shadow_name_index' does not exist ERROR: SearchSysCache: recursive use of cache 23 ERROR: SearchSysCache: recursive use of cache 23 ERROR: SearchSysCache: recursive use of cache 23 ERROR: SearchSysCache: recursive use of cache 23 <-- quite psql here FATAL 1: Index 'pg_shadow_name_index' does not exist <-- restarted again FATAL 1: Index 'pg_shadow_name_index' does not exist FATAL 1: Index 'pg_shadow_name_index' does not exist --- What can I do??? I've got a non-trivial amount of data that I cannot afford to lose!! HELP!.. Regards MArCin - Thanks ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RE: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> >Reported problem is caused by bug (only one tuple version must be > >returned by SELECT) and this is way to fix it. > > > > I assume this is not possible in 7.1? Just looked in heapam.c - I can fix it in two hours. The question is - should we do this now? Comments? Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Untitled
"Md. Intekhab Alam" wrote: > Has anyone tried setting up Postgres as a linked server under Microsofts SQL > Server 7 to connect with SQL 6.5 > > I am able to create the link correctly (see below) and see all the tables > available in Postgres, but if I try querying anything in them I get the > following error > > Server: Msg 7313, Level 16, State 1 > Invalid schema or catalog specified for provider 'MSDASQL'. > > how should I correct this problem. I am in very critical > position because of this problem. > > please help me out ASAP i will be thankfull for that. > > waiting eagarly for the reply with helpful suggetion. > > regards, > > Intekhab Alam > > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) You need to specify the table with the fully qualified format: SERVER.DATABASE.OWNER.TABLE or whatever format the ODBC driver is expecting. The DATABASE portion is what they refer to as CATALOG or SCHEMA. I usually end up trying any combination I can think of until I reach the right one. I can tell you it is expecting three dots, and it can accept blanks in between if it can use a default. For example, in MS SQL Server, owner defaults to 'dbo' if left blank. The docs in MS SQL Server are not too clear on this subject. Good luck. BTW, you will want to alias the fully qualified name in any queries since you can't use it to specify a field. SERVER.DATABASE.OWNER.TABLE.FIELD is not valid. It's SELECT T.FIELD FROM SERVER.DATABASE.OWNER.TABLE T WHERE... Ian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] primary key scans in sequence
hey i have the following table def (834.000 rows, vaccum analyze'd): dl_online=# \d mitglied Table "mitglied" Attribute| Type | Modifier +--+ mitgliedid | bigint | not null dlnummer | varchar(30) | vorname| varchar(50) | zuname | varchar(50) | not null geburtsdatum | varchar(20) | aktiv | boolean | not null default 't'::bool strasse| varchar(255) | plz| varchar(25) | ort| varchar(255) | telefon| varchar(255) | eintrittsdatum | varchar(20) | geschlechtid | bigint | not null default 3 treuelevelid | bigint | not null default 1 clubmitglied | boolean | not null default 'f'::bool bemerkungen| text | mid| bigint | Indices: mitglied_dlnummer_idx, [on dlnummer] mitglied_pkey [on mitgliedid] ok; i use 2 querys: 1) get one row over dlnummer: dl_online=# explain select * from mitglied where dlnummer = '098765432'; NOTICE: QUERY PLAN: Index Scan using mitglied_dlnummer_idx on mitglied (cost=0.00..4.77 rows=1 width=154) EXPLAIN 2) get one row over the primatry key (mitgliedid): dl_online=# explain select * from mitglied where mitgliedid = 833228; NOTICE: QUERY PLAN: Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154) EXPLAIN why doesn't use postrges in (2) the primary-key-index?? take a look at the cost! and both queries returns only ONE row (the optimizer knows that fact). and the worst. in(2) the query take ~3sec. in this time the cpu works on 99.9% (rh-linux 7 on a compac dc10 -alpha). the other works fine (no time to calculate, "no" use of the cpu!). PS: i tried also "set ENABLE_SEQSCAN to OFF". no chance, (2) will work with Seq Scan. thx to ANY hint!! bernd. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Calling Java from psql (was Re: [SQL] requesting help)
Before I go investigating this, is it possible to trigger an arbitrary program from the SQL, say a shell script? Also, why am I getting "not subscribed messages", I am subscribed since I'm replying to a message that was sent to me!! On Thu, 29 Mar 2001, Peter Mount wrote: > At 21:37 26/03/01 +0200, Mathijs Brands wrote: > >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote: > > > Mathijs Brands writes: > > > > > > > Has anybody ever tried calling Java code from a pgsql trigger written > > > > in C? Shouldn't this be possible using JNI? > > > > > > I have, and given the current Java implementations it's a desaster. > > > >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to > >work stable either :( > > Not having looked at the PHP-Java link, are they working as one process or > is it some IPC type link? > > PeterM > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > A. James Lewis ([EMAIL PROTECTED]) If your OS needs a virus detector... RUN!!! ...Out and get Linux! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 'Include' function in SQL scripts
Alessio Bragadini writes: > I was wondering if PostgreSQL supports some kind of #include between SQL > script files, to split a long script in different files. If you're using the psql client then \i filename can be used. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Max Size of a text field
Hi there Warning: PostgreSQL query failed: ERROR: Tuple is too big: size 13872, max size 8140 Is there anyting we can do about that other than just not typing so much text into the field? Kind regards, Koen Antonissen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [SQL] Help with 'now', now(), timestamp 'now', ...
Title: RE: [SQL] Help with 'now', now(), timestamp 'now', ...
try timeofday(), now() gives starttime of transaction :-{.
Regards
Ben
PS this has just been discussed in this mailing list or I would have been clueless
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: 27 March 2001 19:02
> To: [EMAIL PROTECTED]
> Subject: [SQL] Help with 'now', now(), timestamp 'now', ...
>
>
> Hi all,
>
>
>
> I´m developing some procedures in my db and i want know
> how much time my
>
>
> procedures take to execute. So, i write my first procedure
> (to test) as
>
> follows:
>
>
>
> CREATE FUNCTION TEST(INTEGER) RETURNS INTEGER AS '
>
> DECLARE
>
> SEQ RECORD;
>
> BEGIN
>
> SELECT NEXTVAL(''TIMES_ID_SEQ'') AS ID INTO SEQ;
>
> INSERT INTO TIMES (ID, START) VALUES (SEQ.ID, NOW());
>
> FOR I IN 1..$1 LOOP ^
>
> INSERT INTO TEST(ANUMBER) VALUES (RANDOM()*$1);
>
> END LOOP;
>
> UPDATE TIMES SET END = NOW() WHERE ID = SEQ.ID;
>
> RETURN SEQ.ID; ^
>
> END;'
>
> LANGUAGE 'PLPGSQL';
>
>
>
> and executed using "SELECT TEST(1);". When it finish,
> i do a "SELECT
>
>
> * FROM TIMES" and got:
>
>
>
> test=> SELECT * FROM TIMES;
>
> start | end | id
>
> --+--+
>
> 10:27:55 | 10:27:55 | 12
>
> 10:27:55 | 10:27:55 | 13
>
> 10:30:29 | 10:30:29 | 14
>
> 10:31:29 | 10:31:29 | 15
>
> (4 rows)
>
>
>
> In id = 12 and id = 13, i runned two times. Then I
> changed the function
>
>
> and run, at id = 14. Change again at id = 15.
>
>
>
> Where is underlined (), i tried to put, 'now',
> timestamp 'now', etc,
>
>
> and always get the same time. What i'm doing wrong?
>
>
>
> obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)
>
> -> TABLE TIMES (ID SERIAL, START TIME, END TIME);
>
> -> PostgreSQL 7.0.2 under Conectiva Linux
>
>
>
>
>
> Thanks,
>
>
>
> Edipo Elder
>
> [[EMAIL PROTECTED]]
>
> _
> Oi! Você quer um iG-mail gratuito?
> Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html
>
>
> ---(end of
> broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
*
This email and any attachments transmitted with it are confidential
and intended solely for the use of the individual or entity to whom
they are addressed. If you have received this email in error please
notify the sender and do not store, copy or disclose the content
to any other person.
It is the responsibility of the recipient to ensure that opening this
message and/or any of its attachments will not adversely affect
its systems. No responsibility is accepted by the Company.
*
[SQL] Help with 'now', now(), timestamp 'now', ...
> "edipoelder" == edipoelder <[EMAIL PROTECTED]> writes:
edipoelder> times. Then I changed the function
and run, at id =
edipoelder> 14. Change again at id = 15.
Where is underlined
edipoelder> (), i tried to put, 'now', timestamp 'now', etc,
edipoelder> and always get the same time. What i'm doing wrong?
edipoelder>
obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)
-> TABLE TIMES (ID SERIAL, START TIME, END TIME);
PostgreSQL
-> 7.0.2 under Conectiva Linux
now() returns the time at the start of a transaction. Since pl/psql
functions are implicit transactions, now() will not change inside a
function call.
I've used something like the following:
create function bm(integer) returns text as '
declare
cnt alias for $1;
startt text;
endt text;
begin
startt := timeofday();
for i in 1..cnt LOOP
-- insert statement you want to time here
end loop;
endt := timeofday();
return delta_time_msecs(startt,endt);
end;' language 'plpgsql';
create function delta_time_msecs(text,text) returns float8 as '
declare
startt alias for $1;
endtalias for $2;
spaninterval;
daysfloat8;
hours float8;
minutes float8;
msecondsfloat8;
begin
span := endt::timestamp - startt::timestamp;
mseconds := date_part(''milliseconds'',span)::float8;
minutes := date_part(''minutes'',span)::float8;
hours:= date_part(''hours'',span)::float8;
days := date_part(''days'',span)::float8;
return abs(mseconds + minutes*60.0*1000.0 + hours*3600.0*1000.0 +
days*24.0*3600.0*1000.0);
end;' language 'plpgsql';
select bm(1000)::float8/1000.0;
This will give you the average time, averaged over a thousand queries.
-Dan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
RE: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> > >> I assume this is not possible in 7.1?
> > >
> > >Just looked in heapam.c - I can fix it in two hours.
> > >The question is - should we do this now?
> > >Comments?
> >
> > It's a bug; how confident are you of the fix?
95% -:)
> I doubt if it's a bug of SELECT. Well what
> 'concurrent UPDATE then SELECT FOR UPDATE +
> SELECT' return ?
I'm going to add additional check to heapgettup and
heap_fetch:
HeapTupleSatisfies(T) is TRUE:
IF XactIsoLevel is READ_COMMITTED
and snapshot != SnapshotDirty
and !(T->t_data->t_infomask & HEAP_XMAX_INVALID)
and T->t_data->t_infomask & HEAP_XMAX_COMMITTED
and T->t_self != T->t_data->t_ctid
{
FOR ( ; ; )
{
fetch tuple->t_data->t_ctid tuple
IF t_infomask & (HEAP_XMAX_INVALID | HEAP_MARKED_FOR_UPDATE)
break; -- and return T
IF t_infomask & HEAP_XMAX_COMMITTED
{
IF t_self != ctid -- updated
continue;
break; -- deleted, return T
}
-- uncommitted update/delete
IF t_xmax != CurrentTransactionID
break; -- and return T
-- changed by current TX!
IF changed *BEFORE* this query began
{
-- DELETE + SELECT: nothing to return
-- UPDATE + SELECT: newer tuple version
-- will be/was returned by query
return NULL;
}
continue;
}
}
Vadim
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Max Size of a text field
Koen Antonissen writes: > I just recieved this error: > 'Warning: PostgreSQL query failed: ERROR: Tuple is too big: size 13872, > max size 8140 ' > > Is there anyting I can do about that other than tell my users just not > typing so much text into the field? Option 1: increase BLCKSZ in src/include/config.h Option 1.5: use lztext (7.0* only) Option 2: upgrade to 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [SQL] Max Size of a text field
Title: RE: [SQL] Max Size of a text field You can edit config.h and recompile 7.0.3 to allow for larger tuples (up to 32K - Info used to be in the FAQ but that has changed so try the mailing list archives for info) or you can try 7.1 RC1 or the CVS snapshot as both can deal with almost unlimited text fields ;-) Regards Ben > -Original Message- > From: Koen Antonissen [mailto:[EMAIL PROTECTED]] > Sent: 30 March 2001 12:52 > To: [EMAIL PROTECTED] > Subject: [SQL] Max Size of a text field > > > Hi there > > I just recieved this error: > 'Warning: PostgreSQL query failed: ERROR: Tuple is too big: > size 13872, > max size 8140 ' > > Is there anyting I can do about that other than tell my users just not > typing so much text into the field? > > Kind regards, > Koen Antonissen > > ---(end of > broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > * This email and any attachments transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender and do not store, copy or disclose the content to any other person. It is the responsibility of the recipient to ensure that opening this message and/or any of its attachments will not adversely affect its systems. No responsibility is accepted by the Company. *
[SQL] Error on Drop table
Hi, I am trying to drop a table and I get the following error: drop TABLE links; ERROR: RelationBuildTriggers: 1 record(s) not found for rel links mondo=# Could someone explain this to me. I would really appreciate the help. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] primary key scans in sequence
On Thu, Mar 29, 2001 at 03:47:58PM +0200, bernd wrote: > hey i have the following table def (834.000 rows, vaccum analyze'd): > dl_online=# \d mitglied > Table "mitglied" >Attribute| Type | Modifier > +--+ > mitgliedid | bigint | not null > dlnummer | varchar(30) | > vorname| varchar(50) | > zuname | varchar(50) | not null > geburtsdatum | varchar(20) | > aktiv | boolean | not null default 't'::bool > strasse| varchar(255) | > plz| varchar(25) | > ort| varchar(255) | > telefon| varchar(255) | > eintrittsdatum | varchar(20) | > geschlechtid | bigint | not null default 3 > treuelevelid | bigint | not null default 1 > clubmitglied | boolean | not null default 'f'::bool > bemerkungen| text | > mid| bigint | > > Indices: mitglied_dlnummer_idx, [on dlnummer] > mitglied_pkey [on mitgliedid] > > ok; i use 2 querys: > > 1) get one row over dlnummer: > dl_online=# explain select * from mitglied where dlnummer = '098765432'; > NOTICE: QUERY PLAN: > Index Scan using mitglied_dlnummer_idx on mitglied (cost=0.00..4.77 rows=1 > width=154) > EXPLAIN > > 2) get one row over the primatry key (mitgliedid): > dl_online=# explain select * from mitglied where mitgliedid = 833228; > NOTICE: QUERY PLAN: > Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154) > EXPLAIN > > why doesn't use postrges in (2) the primary-key-index?? take a look at the > cost! and both queries returns only ONE row (the optimizer knows that fact). Because the type of the "mitgliedid" is "bigint", but the type of the constant "833228" is "integer" (I think; certainly it isn't "bigint"). Postgres doesn't realise that it can use an index on a bigint to do comparisons to an integer. If you explicitly cast the constant to a bigint, it should be willing to do an index scan, like so: select * from mitglied where mitgliedid = 833228::bigint Yes, this is a bit unpleasant to have to in your client code, and no, I don't know if there's a neater way to let Postgres know it can use this index for this query. But what I've just described does work. Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] TypeCreate: type links already defined
HI all, I just posted a message about droping a table. I droped it using pgaccess from the pg_class table. Now I want to recreate the table and I get the following message: TypeCreate: type links already defined I need to fix this problem in hurry please someone could reply to it. Thanks alot in advance for your help My guess is Tom is not checking his email today :) Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] primary key scans in sequence
bernd writes: > hey i have the following table def (834.000 rows, vaccum analyze'd): > dl_online=# \d mitglied > Table "mitglied" >Attribute| Type | Modifier > +--+ > mitgliedid | bigint | not null > dlnummer | varchar(30) | > vorname| varchar(50) | > zuname | varchar(50) | not null > geburtsdatum | varchar(20) | > aktiv | boolean | not null default 't'::bool > strasse| varchar(255) | > plz| varchar(25) | > ort| varchar(255) | > telefon| varchar(255) | > eintrittsdatum | varchar(20) | > geschlechtid | bigint | not null default 3 > treuelevelid | bigint | not null default 1 > clubmitglied | boolean | not null default 'f'::bool > bemerkungen| text | > mid| bigint | > > Indices: mitglied_dlnummer_idx, [on dlnummer] > mitglied_pkey [on mitgliedid] > > 2) get one row over the primatry key (mitgliedid): > dl_online=# explain select * from mitglied where mitgliedid = 833228; > NOTICE: QUERY PLAN: > Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154) > EXPLAIN This is because the system is not smart enough to match up a bigint = integer comparison with an index scan on a bigint column. Quoting the number, '833228', should fool it sufficiently to make this work. Incidentally, it seems unlikely that you need to use bigint for membership ids, unless you plan on more than 2*10^9 members. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Possible 7.1RC1 bug
Kyle <[EMAIL PROTECTED]> writes: > It appears that sum(int4) returns type numeric. Shouldn't it return > int4? This is not a bug. It's a deliberate change in behavior: sum() and avg() on integer datatypes now return numeric, to avoid problems with overflow. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Possible 7.1RC1 bug
Tom, > This is not a bug. It's a deliberate change in behavior: sum() and > avg() on integer datatypes now return numeric, to avoid problems with > overflow. That could throw off some of my already-written views & functions. Is there a setting that can change this back to the "regular" behavior of sum(integer) = integer? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Error in the date field (with NULL value...).Thanks!
Subject: Importing data from Informix to PostgreSQL. Error in the date field (WITH NULL value) Hello! I'll try to explain my little problem. Well, I have this table create table mytable ( codice char(16) not null, dt_inizio date, dt_finedate, tipo_operazione char(1), causa_operazione integer ); ok! I find out that pgsql: In my example '' is the NULL value exported from Informix... ! (an ASCII file) INFORMIX PostgreSQL char(16) '' --> blank string (I think it's ok! ) char(1)'' --> blank string (I think it's ok) integer'' --> 0 (is it an error? ) date ''--> ERROR! Bad date external representation '' >> select * from mytable ; codice | dt_inizio | dt_fine | tipo_operazione | causa_operazione ABCEDEF | 2001-03-28 | | |0 XXXYYYAAA23C957Y | 2001-03-28 | | |0 clinica=# insert into mytable values ( '','03/28/2001', '' , '' , '' ); ERROR: Bad date external representation '' ^^^ PostgreSQL doesn't want '' as an input of a date with NULL value: it's necessary to use this kind of insert: >> insert into mytable values ( '','03/28/2001',null,'',''); ^^ Now there is a new line in the table: | 2001-03-28 | | |0 My question: How can I resolv my problem? I have a big data file to import where in the 2nd date field there is '' instead of null How can I "binds" PostgreSQL to consider '' as null ? Many thanks for any suggestions! CIAO! MAURIZIO *** ** Happy surfing on THE NET !! ** ** Ciao by ** ** C R I X 98 ** *** AntiSpam: rimuovere il trattino basso dall'indirizzo per scrivermi... (delete the underscore from the e-mail address to reply) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Vacuum Error
We recently upgraded sourceforge to 7.1b6 from our nice old stable november cvs snapshot and we hit this error last nite: NOTICE: FlushRelationBuffers(groups, 2140): block 2057 is referenced (private 0, global 1) Error: ERROR: VACUUM (repair_frag): FlushRelationBuffers returned -2 I tend to fear any error message that says "Frag" in it ;-) Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Error in the date field (with NULL value...).Thanks!
Perhaps you can run your file through a filter to replace '' to null. Any of sed, perl, python and other scripting languages would do nicely. e.g. sed -e "s/''/null/g" < myfile > myfile2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Maurizio Ortolan Sent: Thursday, March 29, 2001 6:50 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SQL] Error in the date field (with NULL value...).Thanks! ... My question: How can I resolv my problem? I have a big data file to import where in the 2nd date field there is '' instead of null How can I "binds" PostgreSQL to consider '' as null ? Many thanks for any suggestions! CIAO! MAURIZIO *** ** Happy surfing on THE NET !! ** ** Ciao by ** ** C R I X 98 ** *** AntiSpam: rimuovere il trattino basso dall'indirizzo per scrivermi... (delete the underscore from the e-mail address to reply) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [PATCHES] Error in the date field (with NULL value...).Thanks!
> How can I "binds" PostgreSQL to consider '' as null ?
You can modify src/backend/utils/{datetime,date,timestamp}.c to accept
an empty string as a null value. But imho it is better to fix this in
your input file, perhaps using sed:
sed "s/''/NULL/g" < informix.dump > pg.dump
- Thomas
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Error in the date field (with NULL value...).Thanks!
Subject: Importing data from Informix to PostgreSQL. Error in the date field (WITH NULL value) Hello! I'll try to explain my little problem. Well, I have this table create table mytable ( codice char(16) not null, dt_inizio date, dt_finedate, tipo_operazione char(1), causa_operazione integer ); ok! I find out that pgsql: In my example '' is the NULL value exported from Informix... ! (an ASCII file) INFORMIX PostgreSQL char(16) '' --> blank string (I think it's ok! ) char(1)'' --> blank string (I think it's ok) integer'' --> 0 (is it an error? ) date ''--> ERROR! Bad date external representation '' >> select * from mytable ; codice | dt_inizio | dt_fine | tipo_operazione | causa_operazione ABCEDEF | 2001-03-28 | | |0 XXXYYYAAA23C957Y | 2001-03-28 | | |0 clinica=# insert into mytable values ( '','03/28/2001', '' , '' , '' ); ERROR: Bad date external representation '' ^^^ PostgreSQL doesn't want '' as an input of a date with NULL value: it's necessary to use this kind of insert: >> insert into mytable values ( '','03/28/2001',null,'',''); ^^ Now there is a new line in the table: | 2001-03-28 | | |0 My question: How can I resolv my problem? I have a big data file to import where in the 2nd date field there is '' instead of null How can I "binds" PostgreSQL to consider '' as null ? Many thanks for any suggestions! CIAO! MAURIZIO *** ** Happy surfing on THE NET !! ** ** Ciao by ** ** C R I X 98 ** *** AntiSpam: rimuovere il trattino basso dall'indirizzo per scrivermi... (delete the underscore from the e-mail address to reply) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
On Thursday 29 March 2001 22:15, Tom Lane wrote: > > Just looked in heapam.c - I can fix it in two hours. > > The question is - should we do this now? > > This scares the hell out of me. > > I do NOT think we should be making quick-hack changes in fundamental > system semantics at this point of the release cycle. Although I'm the one who is being bit by this bug, I tend to agree. > The problem went unnoticed for two full release cycles I first reported the problem on 25 September 2000, on the pgsql-sql list, message subject "SQL functions not locking properly?" I was using 7.0.2 at the time. Also, I seem to remember that a problem of this nature bit me in 6.5.x as well. > it can wait another cycle for a fix that has been considered, reviewed, > and tested. Let's not risk making things worse by releasing a new > behavior we might find out is also wrong. Good point. How long is the next cycle likely to take? Forest ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Forest Wilkinson <[EMAIL PROTECTED]> writes: > Good point. How long is the next cycle likely to take? Good question. I'd like to say 4 to 6 months, but that was how long 7.1 was supposed to take, and we're pushing a year now. What might make the most sense is to develop and test a fix in the early part of the 7.2 development cycle, and then back-patch it into a 7.1.x release perhaps 2 or 3 months from now. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Possible 7.1RC1 bug
"Josh Berkus" <[EMAIL PROTECTED]> writes: >> This is not a bug. It's a deliberate change in behavior: sum() and >> avg() on integer datatypes now return numeric, to avoid problems with >> overflow. > That could throw off some of my already-written views & functions. Is > there a setting that can change this back to the "regular" behavior of > sum(integer) = integer? Sorry, no. You can coerce the result back to int4 if you care to risk overflow: "select sum(int4field)::int4" or some such. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Library Management System
Hi Everyone, Please bear with me.I am a final year student from India-Goa, and +developing a library management system which can be adopted by any University +(Hopefully). We are developing this project using PHP and PostgreSql.The good +thing that we appreciate is that this project is based on the ISO format 2709 +adopted by UGC. We would like to know from all the intrested people for this +project , that what are their expectations from this project like information +on statistics. Right now we are developing the html and PHP pages.We have written a +perl script to import the data from ISO format into PostgreSql database so that +any library right now running on the CDISIS/WWWISIS package can easily export +to ISO format that will be imported into PostgreSql. Due to non existing record +for Serials and Thesis we have not been able to test the perl script for +Serials and Thesis records althought the books has been completed throughly. We +would be very thankful if anyone could give us a record or two of Thesis and +Serials in ISO 2709 format. Hoping for a best, Yours sincerly, Hiren & Sharmad -- The secret of the universe is @*&í!'ñ^#+ NO CARRIER ___ _ _ _ |_|_||_||_||\/||_|| \ _|| || || \| || ||_/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
