[HACKERS] (No) Autocast in 8.4 with operators = and LIKE
Is that is the wished behavoir? template1=# SELECT 1='1'; ?column? -- t (1 row) *template1=# SELECT 1 LIKE '1'; ERROR: operator does not exist: integer ~~ unknown at character 10* HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. LINE 1: SELECT 1 LIKE '1'; ^ template1=# SELECT version(); version - PostgreSQL 8.4.0, compiled by Visual C++ build 1400, 32-bit (1 row) thanks a lot. -- Daniel Schuchardt /Softwareentwicklung/ www.prodat-sql.de http://www.prodat-sql.de
Re: [HACKERS] Error working with Temporary Sequences in plpgsql in 8.1 (8.0
A nice workaraound because
EXECUTE 'select nextval(''test'')' INTO I;
doesnt work in 8.0 seems to be:
myid:=nextval('stvtrsid_seq'::TEXT);
This seems to work in every case.
Daniel
Jaime Casanova schrieb:
try this way:
CREATE OR REPLACE FUNCTION testseq() RETURNS void AS
$BODY$
BEGIN
EXECUTE 'CREATE TEMP SEQUENCE test';
PERFORM testseq1();
DROP SEQUENCE test;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq() OWNER TO postgres;
CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS
$BODY$
DECLARE I INTEGER;
BEGIN
EXECUTE 'select nextval(''test'')' INTO I;
raise notice '%', I;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq1() OWNER TO postgres;
SELECT testseq();
SELECT testseq();
is the same problem as with temp tables, you must put their creation,
and in this case even the nextval in an execute...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[HACKERS] Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
Hi,
here is a testcase:
CREATE OR REPLACE FUNCTION testseq()
RETURNS void AS
$BODY$
BEGIN
CREATE TEMP SEQUENCE test;
PERFORM testseq1();
DROP SEQUENCE test;
RETURN;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq() OWNER TO postgres;
CREATE OR REPLACE FUNCTION testseq1()
RETURNS void AS
$BODY$
DECLARE I INTEGER;
BEGIN
I:= nextval('test');
RETURN;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testseq1() OWNER TO postgres;
SELECT testseq();
-- this works fine.
SELECT testseq();
ERROR: could not open relation with OID 21152
CONTEXT: PL/pgSQL function testseq1 line 3 at assignment
SQL statement SELECT testseq1()
PL/pgSQL function testseq line 3 at perform
Greetings,
Daniel.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] COALESCE bug
Hy, my PostgreSQL 8.0.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 crashes when SELECT * FROM pg_stat_activity WHERE datname=COALESCE(NULL, NULL); I had an mistake in a stored procedure so COALESCE got 2 NULL values but a crash is not nice here ;-) workaround : SELECT * FROM pg_stat_activity WHERE datname=COALESCE(NULL, NULL, ''); so COALESCE has one nice param to return. Daniel (Windows) ---(end of broadcast)--- TIP 1: 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] Oracle Style packages on postgres
Bruce Momjian schrieb: OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? Is that it? Yeah, that would be great. And don't forget global variables for pl/pgsql. Daniel ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] problem with rules - column values lost
Hi List, there seem to be a bug in the 8.0 Rule System if I update a view and does not give a column an value. example TEST=# \d abzu_ruletest View public.abzu_ruletest Column | Type| Modifiers +---+--- abz_txt| character varying | abz_id | integer | abz_proz | real | abz_betrag | real | View definition: SELECT lang_abzu(abzu.abz_id) AS abz_txt, abzu.abz_id, abzu.abz_proz, abzu.abz_betrag FROM abzu; TEST=# SELECT * FROM abzu_ruletest; abz_txt | abz_id | abz_proz | abz_betrag -++--+ | 9 |6 | 3 (1 row) TEST=# UPDATE abzu_ruletest SET abz_betrag=3, abz_txt='test' WHERE abz_id=9; UPDATE 1 TEST=# SELECT * FROM abzu_ruletest; abz_txt | abz_id | abz_proz | abz_betrag -++--+ test| 9 |6 | 3 (1 row) TEST=# UPDATE abzu_ruletest SET abz_betrag=3 WHERE abz_id=9; UPDATE 1 TEST=# SELECT * FROM abzu_ruletest; abz_txt | abz_id | abz_proz | abz_betrag -++--+ | 9 |6 | 3 As you can see the Value of abz_txt is lost. The reason seems to be the on Update rule, i fully delete the old record of the child table and insert a new record there: (i do not know if a record exists) RULE : UPDATE abzu SET abz_id=new.abz_id, abz_proz=new.abz_proz, abz_betrag=new.abz_betrag WHERE abz_id=old.abz_id; --OK DELETE FROM abzutxt WHERE abzl_abz_id=old.abz_id; HERE INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id, new.abz_txt); = Definitions / Testcase CREATE TABLE abzu (abz_idSERIAL PRIMARY KEY, abz_proz FLOAT4, abz_betragFLOAT4 ); CREATE TABLE abzutxt (abzl_id SERIAL NOT NULL PRIMARY KEY, abzl_abz_id INTEGER NOT NULL REFERENCES abzu ON DELETE CASCADE, /*LANGUAGE CODE VARCHAR*/ abzl_txt VARCHAR(50) ); CREATE OR REPLACE FUNCTION lang_abzu(INTEGER /*, VARACHAR (LANGUAGE CODE)*/) RETURNS VARCHAR AS' BEGIN RETURN abzl_txt FROM abzutxt WHERE abzl_abz_id=$1 /* AND LANUAGE CODE = CURRENT_USER_SETTING*/; END'LANGUAGE plpgsql; /*Normally everywhere actual Language codes*/ CREATE OR REPLACE VIEW abzu_ruletest AS SELECT lang_abzu(abz_id) AS abz_txt, * FROM abzu; CREATE RULE abzu_lang_insert AS ON INSERT TO abzu_ruletest DO INSTEAD (INSERT INTO abzu (abz_id, abz_proz, abz_betrag) VALUES (new.abz_id, new.abz_proz, new.abz_betrag); INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id, new.abz_txt); ); CREATE RULE abzu_lang_update AS ON UPDATE TO abzu_ruletest DO INSTEAD (UPDATE abzu SET abz_id=new.abz_id, abz_proz=new.abz_proz, abz_betrag=new.abz_betrag WHERE abz_id=old.abz_id; DELETE FROM abzutxt WHERE abzl_abz_id=old.abz_id; INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id, new.abz_txt); ); INSERT INTO abzu_ruletest (abz_id, abz_txt, abz_betrag, abz_proz) VALUES (9, 'Test Rule', 5, 6); UPDATE abzu_ruletest SET abz_betrag= 3 WHERE abz_id=9; UPDATE abzu_ruletest SET abz_betrag= 3, abz_txt='Test Rule 2' WHERE abz_id=9; thanks, Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] beta1 beta2 Windows heavy load
Tom Lane schrieb: Had you been running the server for very long before forcing the error, I don't think this would have happened, because the buffer hashtable would have already expanded to its full working size. Yes, you are right - this was a fresh started pgserver. Once we fix subxacts to not hold their XID locks after subcommit, the probability of a problem should go back down to the same low value that's allowed us to ignore this risk for the past many years. I think so, too. Daniel ---(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] beta1 beta2 Windows heavy load
Tom Lane schrieb: Can you provide a concrete test case? houres later I'v located the problem. Its not heavy load but subtransactions in Triggers. It's very easy to recreate: the problem is this Syntax : CREATE OR REPLACE FUNCTION do_standard_mgc() RETURNS TRIGGER AS' BEGIN BEGIN --prob also occurs in this case (empty subtransaction) EXCEPTION WHEN OTHERS THEN PERFORN NULL; END; RETURN new; END'LANGUAGE plpgsql; It seems that this subtransactions allocates mem that is never freed. So create a big table with such a trigger - func (in my case after insert or update), do a begin and a update all over the table and postgres will use more and more memory as long there is memory available and then abort. I can post a sample script but i think it's easy to recreate this prob. Hope that helps, Daniel. BTW : how to ignore only duplicate - unique key exceptions? is there sth like WHEN dup_unique_key THEN? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] beta1 beta2 Windows heavy load
Tom Lane schrieb: Yeah, I ran into that earlier today while trying to reproduce your problem. It's fixed by this pending patch. I didn't see any freeze-up of the system after running out of lock memory, though. It seemed to release the locks just fine. Yeah this error is away now. Now i got the same behavoir as before. Notice that I'v never opened a transaction in the following scenario. I'm unable to do anything with this connection. example: C:\postgres\binpsql TEST Welcome to psql 8.0.0beta2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console codepage (850) differs from windows codepage (1252) 8-bit characters will not work correctly. See PostgreSQL documentation Installation on Windows for details. TEST=# SELECT count(*) FROM art; count --- 13534 (1 row) TEST=# UPDATE art SET ak_res=0 WHERE ak_nr='###'; UPDATE 0 TEST=# UPDATE art SET ak_res=0; WARNING: out of shared memory CONTEXT: PL/pgSQL function do_standard_mgc line 2 at block variables initialization WARNING: StartAbortedSubTransaction while in START state ERROR: out of shared memory HINT: You may need to increase max_locks_per_transaction. CONTEXT: PL/pgSQL function do_standard_mgc line 2 at block variables initialization TEST=# UPDATE art SET ak_res=0 WHERE ak_nr='###'; ERROR: current transaction is aborted, commands ignored until end of transaction block TEST=# SELECT count(*) FROM art; ERROR: current transaction is aborted, commands ignored until end of transaction block TEST=# ROLLBACK; ROLLBACK TEST=# SELECT count(*) FROM art; WARNING: out of shared memory ERROR: out of shared memory TEST=# Daniel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] beta1 beta2 Windows heavy load
Tom Lane schrieb: Can I see a stack trace from that? Or at least the verbose form of the error message? actually i dunno know how to create a stack trace. I will do some research. Verbose output (debug5) TEST=# UPDATE art SET ak_res=0; WARNING: out of shared memory CONTEXT: PL/pgSQL function do_standard_mgc line 2 at block variables initialization WARNING: StartAbortedSubTransaction while in START state ERROR: out of shared memory HINT: You may need to increase max_locks_per_transaction. CONTEXT: PL/pgSQL function do_standard_mgc line 2 at block variables initialization WARNING: 01000: StartAbortedSubTransaction while in START state LOCATION: StartAbortedSubTransaction, xact.c:3207 ERROR: 53200: out of shared memory HINT: You may need to increase max_locks_per_transaction. CONTEXT: PL/pgSQL function do_standard_mgc line 2 at block variables initialization LOCATION: LockAcquire, lock.c:562 STATEMENT: UPDATE art SET ak_res=0; WARNING: 01000: StartAbortedSubTransaction while in START state LOCATION: StartAbortedSubTransaction, xact.c:3207 TEST=# ROLLBACK; ROLLBACK TEST=# SELECT count(*) FROM art; WARNING: out of shared memory ERROR: out of shared memory TEST=# WARNING: 53200: out of shared memory LOCATION: ShmemAlloc, shmem.c:185 STATEMENT: SELECT count(*) FROM art; ERROR: 53200: out of shared memory LOCATION: BufTableInsert, buf_table.c:93 STATEMENT: SELECT count(*) FROM art; Daniel. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] beta1 beta2 Windows heavy load
Hi list, is it is known that postgres 8.0 (beta1 and beta2 both same behavoir) will eat tons of memory on Windows under heavy load? Scenario: I have a big delete with about 56000 rows. (DELETE FROM mytable;) There are triggers (after delete) updating 2 or 3 other tables. (there are triggers on this tables too). first everything seems ok but after a short tine postgres starts eating memory. On my mashine one postgres.exe sometimes eates 300MB or more. prob one : everything becomes slow because windows is swapping... main problem : if there is to less virutal memory postgres will abort because of to less memory. For a 56000 row delete you need 500MB of virtual ram or postgres aborts. I noticed the same behavoir when do lets say 75000 rows in a table (triggers updating other tables also). In both situations without transactions. Is this is a bug? Daniel. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] beta2 and blob's
Hi with beta2 i get
leak:relation pg_largeobject_loid_pn_index has refcnt 1 instead of 0
while running my script
that's an example throwing this message
UPDATE reports SET r_blob=lo_import('../reports.r_id.'|| CAST(r_id AS
VARCHAR) || '.blob') WHERE r_blob IS NOT NULL
PS : everything seem to be done in the right way. My data is ok.
Daniel
---(end of broadcast)---
TIP 8: explain analyze is your friend
[HACKERS] beta 2 crash with unique constraints
Hi List, I have another error now here:
notice that only one backend is dying, all others are up and working.
Error - Message :
duplicate key violates unique constraint ferber_rust_params_pkey
FATAL: block 0 of 1663/19335/476756 is still referenced (local 2)
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while
here is a sample script causing this server-crash (simple cut and paste)
BEGIN;
CREATE SEQUENCE dbridseq;
CREATE TABLE ferber_rust_params
(fbrp_id SERIAL PRIMARY KEY,
fbrp_pos INTEGER,
fbrp_ferber VARCHAR(20),
fbrp_descr VARCHAR(80) NOT NULL,
fbrp_nameVARCHAR(10) NOT NULL,
fbrp_default FLOAT4,
fbrp_formula VARCHAR(100),
dbridVARCHAR UNIQUE DEFAULT nextval('dbridseq')
);
INSERT INTO ferber_rust_params (fbrp_id, fbrp_pos, fbrp_ferber,
fbrp_descr, fbrp_name, fbrp_default, fbrp_formula, dbrid) VALUES (6, 4,
'BSRUND', 'Vorschub aus Tabelle', 'vorschub', 360,
'vorschub-(vorschub/100*40)', '337941');
SELECT * INTO TEMP TABLE ferber_rus_205102 FROM ferber_rust_params WHERE
fbrp_ferber='BSRUND';
UPDATE ferber_rus_205102 SET fbrp_name='BSRUND1';
ALTER TABLE ferber_rus_205102 DROP COLUMN dbrid;
INSERT INTO ferber_rust_params SELECT * FROM ferber_rus_205102;
ROLLBACK;
PS : Running Windows XP Home.
Daniel
BEGIN;
CREATE SEQUENCE dbridseq;
CREATE TABLE ferber_rust_params
(fbrp_id SERIAL PRIMARY KEY,
fbrp_pos INTEGER,
fbrp_ferber VARCHAR(20), --REFERENCES ferber ON UPDATE CASCADE ON DELETE
CASCADE,
fbrp_descr VARCHAR(80) NOT NULL,
fbrp_nameVARCHAR(10) NOT NULL,
fbrp_default FLOAT4,
fbrp_formula VARCHAR(100),
dbridVARCHAR UNIQUE DEFAULT nextval('dbridseq')
);
INSERT INTO ferber_rust_params (fbrp_id, fbrp_pos, fbrp_ferber, fbrp_descr, fbrp_name,
fbrp_default, fbrp_formula, dbrid) VALUES (6, 4, 'BSRUND', 'Vorschub aus Tabelle',
'vorschub', 360, 'vorschub-(vorschub/100*40)', '337941');
SELECT * INTO TEMP TABLE ferber_rus_205102 FROM ferber_rust_params WHERE
fbrp_ferber='BSRUND';
UPDATE ferber_rus_205102 SET fbrp_name='BSRUND1';
ALTER TABLE ferber_rus_205102 DROP COLUMN dbrid;
INSERT INTO ferber_rust_params SELECT * FROM ferber_rus_205102;
ROLLBACK;
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[HACKERS] BEGIN EXCEPTION END - small bug?
Hi list, i tried a bit with errorhandling and found the following : (i want to ignore the dublicate key exception) CREATE OR REPLACE FUNCTION test() RETURNS VARCHAR AS' BEGIN BEGIN INSERT INTO table a dublicate key (primary); EXCEPTION WHEN OTHERS THEN ROLLBACK; END; RETURN ''test''; END'LANGUAGE plpgsql; will result in ERROR: SPI_prepare failed for ROLLBACK: SPI_ERROR_TRANSACTION I noticed the right syntax would be (works fine) CREATE OR REPLACE FUNCTION test() RETURNS VARCHAR AS' BEGIN BEGIN INSERT INTO table a dublicate key (primary); EXCEPTION WHEN OTHERS THEN END; RETURN ''test''; END'LANGUAGE plpgsql; Just a hint Regards Daniel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Delphi
a wrote: Hi, Is it have any tools or control let delphi can direct connet to postgresql not only use odbc? Thank!! Gary take a look at www.zeoslib.net www.microolap.com/dac/postgresdac.htm dbexpress : www.vitavoom.com Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
