Re: [PATCHES] [BUGS] BUG #1219: pgxs does not work fully
Am Dienstag, 17. August 2004 14:26 schrieb Fabien COELHO: > The patch adds missing the "libpgport.a" file to the installation under > "install-all-headers". It is needed by some contribs. I install the > library in "pkglibdir", but I was wondering whether it should be "libdir"? Yes it should. Please change it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [BUGS] BUG #1219: pgxs does not work fully
> Am Dienstag, 17. August 2004 14:26 schrieb Fabien COELHO: > > The patch adds missing the "libpgport.a" file to the installation under > > "install-all-headers". It is needed by some contribs. I install the > > library in "pkglibdir", but I was wondering whether it should be "libdir"? > > Yes it should. Please change it. Dear Peter, dear patchers, Please find attached a small patch against current CVS head that fixes pgport library installation so that it goes to libdir instead of pkglibdir. It works for me. Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED]*** ./src/Makefile.global.in.orig Mon Aug 23 09:15:09 2004 --- ./src/Makefile.global.inTue Aug 24 15:21:17 2004 *** *** 360,366 LIBS := -lpgport $(LIBS) ifdef PGXS # where libpgport.a is installed ! LDFLAGS := -L$(pkglibdir) $(LDFLAGS) else LDFLAGS := -L$(top_builddir)/src/port $(LDFLAGS) endif --- 360,366 LIBS := -lpgport $(LIBS) ifdef PGXS # where libpgport.a is installed ! LDFLAGS := -L$(libdir) $(LDFLAGS) else LDFLAGS := -L$(top_builddir)/src/port $(LDFLAGS) endif *** ./src/port/Makefile.origMon Aug 23 09:15:10 2004 --- ./src/port/Makefile Tue Aug 24 15:18:08 2004 *** *** 22,31 # libpgport is needed by some contrib install-all-headers: ! $(INSTALL_STLIB) libpgport.a $(DESTDIR)$(pkglibdir) uninstall: ! $(RM) $(DESTDIR)$(pkglibdir)/libpgport.a libpgport.a: $(LIBOBJS) $(AR) $(AROPT) $@ $^ --- 22,31 # libpgport is needed by some contrib install-all-headers: ! $(INSTALL_STLIB) libpgport.a $(DESTDIR)$(libdir) uninstall: ! $(RM) $(DESTDIR)$(libdir)/libpgport.a libpgport.a: $(LIBOBJS) $(AR) $(AROPT) $@ $^ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1228: numeric field from a view from a view does not recognise any where values
The following bug has been logged online: Bug reference: 1228 Logged by: mike Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.3 Operating system: FC3 Description:numeric field from a view from a view does not recognise any where values Details: I have the following view definition Column | Type | Modifiers +---+--- bcode | character varying(15) | subhead| text | sc_description | character varying(60) | Budget | numeric | expenditure| numeric | balance| numeric | head | integer | period | integer | View definition: SELECT CASE WHEN vw_rec_sum.code IS NULL AND vw_pay_sum.sum IS NOT NULL THEN vw_pay_sum.code ELSE vw_rec_sum.code END AS bcode, CASE WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 'Salary Costs'::text WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 'Startup Costs'::text WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 'Running Costs'::text WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 'Training Costs'::text ELSE NULL::text END AS subhead, sc_description, vw_rec_sum.sum AS "Budget", vw_pay_sum.sum AS expenditure, CASE WHEN vw_pay_sum.sum IS NULL THEN vw_rec_sum.sum WHEN vw_pay_sum.sum < 0.01 THEN 0.00 - vw_pay_sum.sum + vw_rec_sum.sum ELSE vw_rec_sum.sum - vw_pay_sum.sum END AS balance, CASE WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 1 WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 2 WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 3 WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 4 ELSE NULL::integer END AS head, CASE WHEN to_number(vw_rec_sum.code::text, '999'::text) > 194::numeric THEN 3 WHEN to_number(vw_rec_sum.code::text, '999'::text) < 195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) > 50::numeric THEN 2 ELSE 1 END AS period FROM vw_rec_sum FULL JOIN vw_pay_sum ON vw_rec_sum.code::text = vw_pay_sum.code::text JOIN vw_ac ON vw_rec_sum.code::text = vw_ac.id::text ORDER BY to_number(vw_rec_sum.code::text, '999'::text); However whenever I try to query it with criteria on the period column I get SELECT * FROM vw_budget HAVING "period"='3'; ERROR: invalid input syntax for type numeric: " " If I try on the head column the query runs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1229: memory leak in backend with COPY and FOREIGN KEY
The following bug has been logged online: Bug reference: 1229 Logged by: Andreas Heiduk Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.3 Operating system: Linux Debian testing (i86) Description:memory leak in backend with COPY and FOREIGN KEY Details: Short version: I'm trying to COPY ~18Mrows into a table which has a foreign key to another table. Memory and swap are exhausted and finaly the postgres.log says: [2354] ERROR: could not write block 104984 of relation "track": Cannot allocate memory CONTEXT: writing block 104984 of relation 252172594/252175859 COPY track, line 10205801: "rocka00e050c2 La De Da\N 38443" If I try to load the same data without the foreign key to the other table, the memory usage is constant and low. If I create the foreign key after the COPY, the memory consumption is also constant and low. Longer Version: Here are the tables in question: Table "public.disc" Column | Type| Modifiers ---+---+--- category | character varying | not null discid| character(8) | not null title | character varying | not null ext | character varying | year | integer | genre | character varying | playtime | integer | not null playorder | character varying | rev | integer | not null processor | character varying | not null submitter | character varying | not null Indexes: "disc_pkey" primary key, btree (category, discid) Table "public.track" Column | Type| Modifiers --+---+--- category | character varying | not null discid | character(8) | not null number | smallint | not null title| character varying | ext | character varying | foffset | integer | not null The foreign key constraint is created with ALTER TABLE track ADD FOREIGN KEY (category, discid) REFERENCES disc(category, discid); The "disc" table is filled with 1.4Mrows, the "track" table should be filled with 18Mrows. Both tables are loaded with psql \copy from text files. ps(1) says, that the memory is consumed by the postgres process handling the COPY. The postgres version is: PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-3) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1229: memory leak in backend with COPY and FOREIGN
On Tue, 24 Aug 2004, PostgreSQL Bugs List wrote: > I'm trying to COPY ~18Mrows into a table which has a foreign key to another > table. Memory and swap are exhausted and finaly the postgres.log says: This is very possibly the space taken up by the trigger queue (which cannot currently spill out to disk). If you load a smaller number of rows does the space go up and then down after the copy ends? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] vm/swap used until exhausted
Different memory usage 7.4.3 vs 8.0.0beta1 client does: begin bulk inserts into single table via PQexecParams (1.2 million records) commit under 7.4.3 memory usage is static under 8.0.0beta1 server used increasing memory untill depletion of vm/swap 7.4.3 last pid: 974; load averages: 1.44, 1.12, 0.68up 0+00:24:08 19:05:08 65 processes: 3 running, 62 sleeping CPU states: 88.0% user, 0.0% nice, 9.7% system, 2.3% interrupt, 0.0% idle Mem: 96M Active, 16M Inact, 31M Wired, 7008K Cache, 28M Buf, 28M Free Swap: 357M Total, 37M Used, 319M Free, 10% Inuse PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 923 pgsql1150 16712K 11792K RUN 8:06 65.82% 65.82% postgres 921 pgsql 960 2292K 1320K RUN 0:01 0.00% 0.00% top 904 pgsql 40 16016K 1204K select 0:00 0.00% 0.00% postgres 877 pgsql 80 916K 0K wait 0:00 0.00% 0.00% 906 pgsql 40 6808K12K select 0:00 0.00% 0.00% postgres 905 pgsql 40 7764K12K select 0:00 0.00% 0.00% postgres 8.0.0 beta1 last pid: 11448; load averages: 1.00, 0.35, 0.23up 0+04:57:28 23:38:28 64 processes: 2 running, 62 sleeping CPU states: 77.0% user, 0.0% nice, 15.6% system, 7.4% interrupt, 0.0% idle Mem: 115M Active, 15M Inact, 42M Wired, 7540K Cache, 28M Buf, 564K Free Swap: 357M Total, 57M Used, 300M Free, 15% Inuse, 932K Out PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 11448 pgsql1300 83564K 78732K RUN 0:58 72.08% 70.56% postgres 11438 pgsql 960 13960K 10156K select 0:00 0.00% 0.00% postgres 11436 pgsql 960 13952K 0K select 0:00 0.00% 0.00% 877 pgsql 50 920K 0K ttyin0:00 0.00% 0.00% 11440 pgsql 40 4552K 0K select 0:00 0.00% 0.00% 11439 pgsql 960 5480K 2028K select 0:00 0.00% 0.00% postgres ---(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
[BUGS] Inheritance and constraints duplicate values
After doing some research, this is apparently a
known long-standing issue with inheritance in Postgres. Nobody calls it a
"bug" outright, though. Just a limitation. It makes me wonder how
others have tackled problems of a similar nature.
I've since devised a different way
(http://archives.postgresql.org/pgsql-general/2003-05/msg00585.php), but if you
guys have any other suggestions for how you've handled this sort of thing in
PostgreSQL, let me know.
To grossly simplify, here's what I was trying to do.
CREATE TABLE products (
id serial NOT NULL PRIMARY KEY,
name varchar(64) NOT NULL,
price numeric(6,2)
);
CREATE TABLE pants (
waist smallint,
length smallint,
colour varchar(12)
) inherits (products);
CREATE TABLE computers (
cpu
varchar(12),
mhz smallint,
) inherits (products);
INSERT INTO pants (name,price,waist,length,colour)
VALUES ('Brand-X Cargo Pants', 49.95, 32, 34, 'khaki');
INSERT INTO computers (name,price,cpu,mhz)
VALUES ('Flower Power iMac', $666.66, 'Motorola
750', 500);
SELECT id,name FROM products;
--> 1 Brand-X Cargo Pants
--> 2 Flower Power iMac
INSERT INTO computers (id, name,price,cpu,mhz)
VALUES (1, 'Mac
Plus', $5.00, 'Motorola 68000', 8);
SELECT id,name FROM products;
--> 1 Brand-X Cargo Pants
--> 1 Mac Plus
--> 2 Flower Power iMac
Huh? But products.id is a primary key! The "Mac Plus" screwed it up
by inserting duplicate values!
Thanks for all
John Luise
[BUGS] server crash in very big transaction [postgresql 8.0beta1]
BEGIN; ... ... ... END; PANIC: invalid xlog record length 236052 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> ---(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
[BUGS] Postgres 8.0/Windows 2000 Load testing
While performing Load testing using DOTS http://ltp.sourceforge.net/dotshowto.php , during the tests, an update to a specific table starts hanging. The test name is called BTCJ2 http://ltp.sourceforge.net/dotshowto.php#SEC26, "This test case mainly uses SQL commands to execute database operations such as insert, update, select and delete. This test case uses tables BASIC1, BASIC2, BASIC3. For details about the tables, refer to Appendix A." Postgres itself was running fine and accepting new connections and able to perform queries on the same table. Only the UPDATE was hanging. There was no interesting messages in the log file. I was running with debug level 2. I can see the last line which prints the UPDATE statement. Using debug level 3 the file grows very large and it very slow. Let me know if you want anymore information. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] replacing a function called "isnull" reports an error
Hi, I created a function called "isnull" for test purposes and when trying to replace it returns the message belows : -- Function: public.isnull(anyelement) -- DROP FUNCTION public.isnull(anyelement); CREATE OR REPLACE FUNCTION public.isnull(anyelement) RETURNS bool AS ' SELECT $1 is null; ' LANGUAGE 'sql' VOLATILE; ERROR: syntax error at or near "isnull" at character 120 Thanks. ___ Yahoo! Acesso Grátis - navegue de graça com conexão de qualidade! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] ecpg / libecpg.dll
dear ladies and gentlemen, I indicate, that in Vers 8.0 Beta1 / Window the DLL libecpg.dll is missing. All programs, being precompiled by ecpg are lacking in that DLL after being started. with kind regards G. Abraham -- Guenter Abraham | Tel. 030-35 10 58 97 | Fax. 030-35 10 58 98 Informations-Technologie Babelsberg GmbH http://www.itbb.de -- ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] Memory leak
I have found a memory leak in the libpq library for postrgesql 7.4.3. The code sample in the attached file will produce the error. I used Valgrind(http://valgrind.kde.org/) which is an open source memory profiler application in order to find the problem. Machine: Pentium 4 OS: Linux Fedora Core1 According to Valgrind if an application attempts to make 2 or more connections to the database then memory will be lost for every connection except the first. Here is the error that I see from Valgrind for 2 separate connections: ==8980== 56 bytes in 2 blocks are definitely lost in loss record 1 of 1 ==8980== at 0x38E68E: malloc (vg_replace_malloc.c:153) ==8980== by 0x13DE9B: __libc_res_nsend (in /lib/libresolv-2.3.2.so) ==8980== by 0x13CC12: __libc_res_nquery (in /lib/libresolv-2.3.2.so) ==8980== by 0x13D309: __libc_res_nquerydomain (in /lib/libresolv-2.3.2.so) ==8980== by 0x13CF0F: __libc_res_nsearch (in /lib/libresolv-2.3.2.so) ==8980== by 0x37705D: ??? ==8980== by 0x62AD25: gaih_inet (in /lib/libc-2.3.2.so) ==8980== by 0x62B923: __GI_getaddrinfo (in /lib/libc-2.3.2.so) ==8980== by 0xDF3661: getaddrinfo_all (in /usr/lib/libpq.so.3.1) ==8980== by 0xDE4EBB: (within /usr/lib/libpq.so.3.1) ==8980== by 0xDE4469: PQconnectStart (in /usr/lib/libpq.so.3.1) ==8980== by 0xDE43F1: PQconnectdb (in /usr/lib/libpq.so.3.1) ==8980== by 0x8048684: main (in /home/squin/yo) ==8980== by 0x579BBE: __libc_start_main (in /lib/libc-2.3.2.so) ==8980== by 0x804854C: (within /home/squin/yo) Any help on this issue is greatly appreciated. Thanks Spencer Quin Web Software Developer Reseach In Motion ' (519) 888-7465 x2596 * [EMAIL PROTECTED] test3.c Description: test3.c ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1228: numeric field from a view from a view does not recognise any where values
On Tue, Aug 24, 2004 at 01:33:46PM -0300, PostgreSQL Bugs List wrote: > CASE > WHEN to_number(vw_rec_sum.code::text, '999'::text) > > 194::numeric THEN 3 > WHEN to_number(vw_rec_sum.code::text, '999'::text) < > 195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) > > 50::numeric THEN 2 > ELSE 1 > END AS period > However whenever I try to query it with criteria on the period column I > get SELECT * FROM vw_budget HAVING "period"='3'; > ERROR: invalid input syntax for type numeric: " " Maybe there's a row somewhere in the vw_rec_sum relation that has a " " value in the code field ... or a similar weirdness in some other column. That's expected when you store numbers in text columns. Don't do that. -- Alvaro Herrera () "Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?" ---(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: [BUGS] replacing a function called "isnull" reports an error
César Arnold wrote: > Hi, I created a function called "isnull" for test > purposes and when trying to replace it returns the > message belows : ISNULL is a reserved word. You need to quote the name or pick a different one. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Inconsistent pg_ctl behaviour: start vs. runservice
[PostgreSQL 8.0beta1 on Windows 2000 Professional] In case of a leftover postmaster.pid, pg_ctl start tries to start anyway, but pg_ctl runservice (as installer by the PostgreSQL Windows installer) will not start the service, requiring a manual removal of postmaster.pid. Apparently no proper error code is returned on the service startup failure - I couldn't get the service recovery options to delete postmaster.pid (Tried batchfile and executable). Regards, Steffen ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] 8.0.0b1 cygwin problems
I haven't tested this with mingw or MSVC but the default cygwin layout needs the attached header changes. pending problems: src/timezone has a configure problem pgtz.c:105:2: #error No way to determine TZ? Can this happen? PGAC_VAR_INT_TIMEZONE return no for cygwin, but our time.h has this: extern __IMPORT time_t _timezone; which is from newlib. I truly mistrust PGAC_VAR_INT_TIMEZONE in config/c-library.m4 Right now the gcc-3.4.1 build for cygwin is in experimental status, we will see what will change then. So far we only have gcc-3.3. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ --- postgresql-8.0.0beta1/src/port/dirmod.c.orig2004-08-08 07:44:36.0 +0100 +++ postgresql-8.0.0beta1/src/port/dirmod.c 2004-08-24 19:20:56.557435000 +0100 @@ -33,16 +33,28 @@ #include "miscadmin.h" + +#ifdef __CYGWIN__ +#include +#include +#else #include +#undef unlink +#endif #undef rename -#undef unlink +/* 2004-08-24 20:20:54 rurban: There are no pgport_palloc versions yet */ +#if 0 #ifndef FRONTEND +#undef palloc +#undef pstrdup +#undef pfree #define palloc(sz) pgport_palloc(sz) #define pstrdup(str) pgport_pstrdup(str) #define pfree(pointer) pgport_pfree(pointer) #endif +#endif /* ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Inheritance and constraints duplicate values
On Sat, 2004-08-21 at 12:44, Marian Lojka wrote:
> After doing some research, this is apparently a known long-standing
> issue with inheritance in Postgres. Nobody calls it a "bug" outright,
> though. Just a limitation. It makes me wonder how others have tackled
> problems of a similar nature.
> CREATE TABLE products (
> id serial NOT NULL PRIMARY KEY,
> name varchar(64) NOT NULL,
> price numeric(6,2)
> );
>
> CREATE TABLE pants (
> waist smallint,
> length smallint,
> colour varchar(12)
> ) inherits (products);
>
> CREATE TABLE computers (
> cpu varchar(12),
> mhz smallint,
> ) inherits (products);
>
> INSERT INTO pants (name,price,waist,length,colour)
> VALUES ('Brand-X Cargo Pants', 49.95, 32, 34, 'khaki');
>
> INSERT INTO computers (name,price,cpu,mhz)
> VALUES ('Flower Power iMac', $666.66, 'Motorola 750', 500);
> INSERT INTO computers (id, name,price,cpu,mhz)
> VALUES (1, 'Mac Plus', $5.00, 'Motorola 68000', 8);
>
> SELECT id,name FROM products;
>
> --> 1 Brand-X Cargo Pants
> --> 1 Mac Plus
> --> 2 Flower Power iMac
>
>
> Huh? But products.id is a primary key! The "Mac Plus" screwed it up by
> inserting duplicate values!
Unfortunately it's only a primary key in the parent table; that
constraint is not inherited.
To implement foreign keys on an inheritance hierarchy would require
indexes that could index multiple tables. As far as I know, that is not
currently possible.
The work-around is to create another table
CREATE TABLE product_xref (
id serial PRIMARY KEY
);
then
ALTER TABLE products ADD CONSTRAINT FOREIGN KEY (id) references
product_xref(id);
(and id in products should not be a serial field, or the default should
be explicitly set to nextval(product_xref_id_seq))
and the same for each child table
Then create a trigger function to create a record in product_xref on
insertion and delete it on deletion.
--
Oliver Elphick [EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
"I saw in the night visions, and, behold, one like the
Son of man came with the clouds of heaven, and came to
the Ancient of days, and they brought him near before
him. And there was given him dominion, and glory, and
a kingdom, that all people, nations, and languages,
should serve him; his dominion is an everlasting
dominion, which shall not pass away, and his kingdom
that which shall not be destroyed."
Daniel 7:13,14
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] server crash in very big transaction [postgresql 8.0beta1]
=?UTF-8?Q?=E5=A7=9C?= =?UTF-8?Q?=E7=BB=B4?= <[EMAIL PROTECTED]> writes: > BEGIN; > ... > ... > ... > END; > PANIC: invalid xlog record length 236052 > server closed the connection unexpectedly This is quite unhelpful, if you're not going to show us what you did to cause it. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] vm/swap used until exhausted
Zane <[EMAIL PROTECTED]> writes: > client does: > begin > bulk inserts into single table via PQexecParams (1.2 million records) > commit Could we see a concrete test case? I really don't have time to guess about what contributing factors might be involved ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] replacing a function called "isnull" reports an error
Peter Eisentraut <[EMAIL PROTECTED]> writes: > César Arnold wrote: >> Hi, I created a function called "isnull" for test >> purposes and when trying to replace it returns the >> message belows : > ISNULL is a reserved word. You need to quote the name or pick a > different one. I think what he's unhappy about is this inconsistency: regression=# create function isnull(int) returns bool as 'select $1 is null' language sql; CREATE FUNCTION regression=# drop function isnull(int); DROP FUNCTION regression=# create function public.isnull(int) returns bool as 'select $1 is null' language sql; ERROR: syntax error at or near "isnull" at character 24 LINE 1: create function public.isnull(int) returns bool as 'select $... ^ regression=# ISNULL is a func_name_keyword, so it's legal standing on its own, but we only allow ColId in a qualified name. Possibly that could be relaxed. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] server crash in very big transaction [postgresql 8.0beta1]
On Sun, Aug 22, 2004 at 09:39:07AM +0800, ?? wrote: > BEGIN; > ... > ... > ... > END; > > PANIC: invalid xlog record length 236052 Huh, so what kind of operations did you execute within the transaction? -- Alvaro Herrera () Voy a acabar con todos los humanos / con los humanos yo acabaré voy a acabar con todos / con todos los humanos acabaré (Bender) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Memory leak
"Spencer Quin" <[EMAIL PROTECTED]> writes: > I have found a memory leak in the libpq library for postrgesql 7.4.3. > The code sample in the attached file will produce the error. The traceback says that the leak is in libresolv, not libpq. I'm not sure it's really a leak at all --- I'd expect libresolv to do some internal caching, and this looks like it could be data that's just being held onto for possible reuse. But in any case you want to file this report with somebody else. 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
[BUGS] BUG #1230: mirror problem
The following bug has been logged online: Bug reference: 1230 Logged by: jeff putnam Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: linux Description:mirror problem Details: Website problem. nslookup does not seem to know the address of any of your mirrors : $nslookup ftp22.us.postgresql.org ... ** server can't find ftp22.us.postgresql.org: NXDOMAIN $whois postgresql.org ... Name Server:NS-A.LERCTR.ORG ... $nslookup ... > server ns-a.lerctr.org Default server: ns-a.lerctr.org Address: 192.147.25.11#53 > ftp22.us.postgresql.org Server: ns-a.lerctr.org Address:192.147.25.11#53 ** server can't find ftp22.us.postgresql.org: NXDOMAIN ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] server crash in very big transaction [postgresql 8.0beta1]
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Sun, Aug 22, 2004 at 09:39:07AM +0800, ?? wrote: >> PANIC: invalid xlog record length 236052 > Huh, so what kind of operations did you execute within the transaction? I found one possible explanation, though I don't know if it's the submitter's problem or not. Create a SQL file that generates a whole lot of subtransactions, like more than 16000. I used begin; create table foo(d1 int); drop table foo; savepoint x; release x; -- repeat above 2 lines 2 times commit; First try gave me WARNING: out of shared memory ERROR: out of shared memory HINT: You may need to increase max_locks_per_transaction. WARNING: StartAbortedSubTransaction while in START state ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ... etc ... which is fine except for the StartAbortedSubTransaction warning; that may indicate a problem. (What do you think about it, Alvaro?) I bumped up max_locks_per_transaction to 1000 and tried again, and got psql:zzbig.sql:40004: PANIC: invalid xlog record length 80024 server closed the connection unexpectedly What is happening of course is that more than 16K subtransaction IDs won't fit in a commit record (since XLOG records have a 16-bit length field). We're gonna have to rethink the representation of subxact commit in XLOG. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] Backward compatibility issue with 8.0b1
Guys, Beg pardon if this is a repeat: Summary: \d option on 8.0b1 PSQL not backward compatible Severity: Annoyance Affects: 8.0b1 connecting to 7.4.3 Platform: SuSE Linux 8.2 Description: I get: jwnet_test=# \d elbs_matter ERROR: column "reltablespace" does not exist jwnet_test=# \d cases ERROR: column "reltablespace" does not exist jwnet_test=# I thought that we were going to be better about backward compatibility in this version? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Backward compatibility issue with 8.0b1
On Tue, Aug 24, 2004 at 06:56:42PM -0700, Josh Berkus wrote: > Guys, > > Beg pardon if this is a repeat: > > Summary: \d option on 8.0b1 PSQL not backward compatible > Severity: Annoyance > Affects: 8.0b1 connecting to 7.4.3 Apparently this was fixed after beta 1 ... works for me here. -- Alvaro Herrera () "Now I have my system running, not a byte was off the shelf; It rarely breaks and when it does I fix the code myself. It's stable, clean and elegant, and lightning fast as well, And it doesn't cost a nickel, so Bill Gates can go to hell." ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] server crash in very big transaction [postgresql 8.0beta1]
On Tue, Aug 24, 2004 at 06:10:40PM -0400, Tom Lane wrote: > WARNING: out of shared memory > ERROR: out of shared memory > HINT: You may need to increase max_locks_per_transaction. > WARNING: StartAbortedSubTransaction while in START state > ERROR: current transaction is aborted, commands ignored until end of transaction > block > ERROR: current transaction is aborted, commands ignored until end of transaction > block > ... etc ... > > which is fine except for the StartAbortedSubTransaction warning; that > may indicate a problem. (What do you think about it, Alvaro?) I think the problem here is that we can't "safely" call StartAbortedSubTransaction when in TRANS_START state. This is because we could have some subsystems already initialized, and we will be initializing them again. For example, AtSubStart_Memory() will be called twice, which will lead to the loss of an (empty) memory context. It doesn't seem a big problem, only a memory leak. (We also lose a ResourceOwner, but since it doesn't own anything yet, it isn't a problem.) Fortunately I think we are good regarding other subsystems --- for example if we happened to do something with sinval.c list-of-lists, it could get out of sync with the transaction stack and it could get ugly. But we don't. I don't think we should get rid of the warning. It shows that there's a problem, but it's not critical. We could set a flag indicating that memory and resource owner are already initialized (another TRANS state? a static bool?), but I don't know if it's worth the trouble. -- Alvaro Herrera () "The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do." (Samuel P. Huntington) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Backward compatibility issue with 8.0b1
Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Summary: \d option on 8.0b1 PSQL not backward compatible > Apparently this was fixed after beta 1 ... works for me here. Yeah, Greg Mullane fixed that, see http://archives.postgresql.org/pgsql-committers/2004-08/msg00312.php regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] TOAST error in 7.4.2 on frequently truncated tables
People, Here's another: Summary: TOAST error on frequently truncated tables Version: 7.4.2 Severity: Abort Platform: Red Hat 9.0 When running a script against a reporting database, I'm getting the following: DBD::Pg::st execute failed: ERROR: expected both swapped tables to have TOAST tables at sql_data_xfer.pl line 120, line 2. Process Failed: from step SELECT count(*) FROM elbs_client;Process Failed: from step SELECT count(*) FROM elbs_client; 1 at sql_data_xfer.pl line 120, line 2. What's happening in the scipt is that it's checking for the presence of data in load tables and truncating them if data is found. I'm not absolutely certain whether it is the COUNT(*) query, or the TRUNCATE step which is producing the above error; TRUNCATE would seem more reasonable given the nature of the error. I've searched the source code, and the only reference to the error I can find is for CLUSTER, which is confusing because none of the tables involved are clustered. Is this related to the index problem fixed in 7.4.x? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] TOAST error in 7.4.2 on frequently truncated tables
Josh Berkus <[EMAIL PROTECTED]> writes: > DBD::Pg::st execute failed: ERROR: expected both swapped tables to have TOAST > tables at sql_data_xfer.pl line 120, line 2. Have you done more to this table than TRUNCATE it? We recognized relatively recently that ALTER DROP COLUMN could drop the last/only variable-width column, in which case there is no longer any need for a TOAST table. The 8.0 code for CLUSTER/TRUNCATE copes with this possibility but 7.4 doesn't. I'm quite sure you couldn't get this error from anything except CLUSTER or TRUNCATE in 7.4 though. Seems like your client-side code is in error to be fingering a plain SELECT as the cause. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] TOAST error in 7.4.2 on frequently truncated tables
Tom, > > DBD::Pg::st execute failed: ERROR: expected both swapped tables to have > > TOAST tables at sql_data_xfer.pl line 120, line 2. > > Have you done more to this table than TRUNCATE it? > > We recognized relatively recently that ALTER DROP COLUMN could drop the > last/only variable-width column, in which case there is no longer any > need for a TOAST table. The 8.0 code for CLUSTER/TRUNCATE copes with > this possibility but 7.4 doesn't. Aha! Yes, the problem is that I dropped the last VARCHAR column, not in that table but in one that came after it. Any workaround to fix? > I'm quite sure you couldn't get this error from anything except CLUSTER > or TRUNCATE in 7.4 though. Seems like your client-side code is in error > to be fingering a plain SELECT as the cause. Yeah, well, I can't get the client to install Exception module. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] TOAST error in 7.4.2 on frequently truncated tables
Josh Berkus <[EMAIL PROTECTED]> writes: > Aha! Yes, the problem is that I dropped the last VARCHAR column, not in that > table but in one that came after it. Any workaround to fix? Easiest is to add back a useless varchar column ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
