[BUGS] plpgsql - variable's names conflict with table field names
Hello When I declare variable with same name as field of table, then I have a problem with insert cmd in plpgsql procedure. I can't use this name of columns list in insert cmd; I get syntax error. When I use equal names in SELECT cmd, I didn't get error msg, but stored prodedure don't work. CREATE TABLE fog2( idx SERIAL PRIMARY KEY, cas TIMESTAMP ); -- work fine CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS ' DECLARE _cas TIMESTAMP; BEGIN SELECT INTO _cas cas FROM fog2 LIMIT 1; RETURN _cas; END; ' LANGUAGE plpgsql; -- don't work CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS ' DECLARE cas TIMESTAMP; BEGIN SELECT INTO cas cas FROM fog2 LIMIT 1; RETURN cas; END; ' LANGUAGE plpgsql; -- works fine CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS ' DECLARE cas TIMESTAMP; BEGIN cas := CURRENT_TIMESTAMP; INSERT INTO fog2 VALUES(DEFAULT, cas); RETURN cas; END; ' LANGUAGE plpgsql; -- don't work - syntax error CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS ' DECLARE cas TIMESTAMP; BEGIN cas := CURRENT_TIMESTAMP; INSERT INTO fog2 (cas) VALUES(cas); RETURN cas; END; ' LANGUAGE plpgsql; intra=# select errdemo(); ERROR: syntax error at or near "$1" at character 20 CONTEXT: PL/pgSQL function "errdemo" line 3 at SQL statement intra=# Is it plpgsql error or my bug? Regards Pavel Stehule ---(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] plpgsql - variable's names conflict with table field names
Pavel Stehule <[EMAIL PROTECTED]> writes: > When I declare variable with same name as field of table, then I > have a problem with insert cmd in plpgsql procedure. Don't do that. > Is it plpgsql error or my bug? You could argue it either way, perhaps, but it's unlikely to get changed. In general plpgsql cannot tell whether a variable name appearing in a SQL command ought to be substituted for or not, and so it just always does it. I think trying to be smart would create as many pitfalls as it'd solve. The best practice is not to use plpgsql variable names that match table or field names you need to access in the same procedure. regards, tom lane ---(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] plpgsql - variable's names conflict with table field
On Tue, 17 Feb 2004, Tom Lane wrote: > Pavel Stehule <[EMAIL PROTECTED]> writes: > > When I declare variable with same name as field of table, then I > > have a problem with insert cmd in plpgsql procedure. > > Don't do that. > > > Is it plpgsql error or my bug? > > You could argue it either way, perhaps, but it's unlikely to get > changed. In general plpgsql cannot tell whether a variable name > appearing in a SQL command ought to be substituted for or not, and so it > just always does it. I think trying to be smart would create as many > pitfalls as it'd solve. The best practice is not to use plpgsql > variable names that match table or field names you need to access in the > same procedure. I haven't problem with it. I found it when I ported procedures from Solid db. Can You add big warning about it to plpgsql documentation? This is very tricky "bug". Insert reports syntax error, ok. But Select reports nothink. Regard Pavel > > regards, tom lane > > ---(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 > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Default Timestamp 'Now' bug with 7.4 on Panther.
Ok, thanks. that's what i get for using a premade package, I guess. -harry On Feb 15, 2004, at 1:31 PM, Tom Lane wrote: Harry Hochheiser <[EMAIL PROTECTED]> writes: Running Postgres 7.4 on Panther, there seems to be a problem with setting a timestamp column to have a default of 'now': There was an intentional change of behavior --- see the release notes. The supported way to do this is to use CURRENT_TIMESTAMP or now() as the column default. 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])
[BUGS] Crash when calling a pl/pgsql function with no row to pass as an argument
(Attempting to send this to pgsql-bugs a second time without Cc:'ing pgsql-patches, since it doesn't appear that my February 15 message got through.) POSTGRESQL BUG REPORT TEMPLATE Your name : Chris Campbell Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : PowerPC G3 Operating System (example: Linux 2.4.18) : Mac OS X 10.3.2 (Darwin 7.2.0) PostgreSQL version (example: PostgreSQL-7.4.1): PostgreSQL-7.4.1 Compiler used (example: gcc 2.95.2) : gcc 3.3 20030304 Please enter a FULL description of your problem: postmaster crashes if it tries to call a pl/plgsql function that requires a table row as an argument, and there is no row produced in the query that can be passed in. There is currently an assertion in the code to guard against this case, but it's not an error case, so it needs to be handled more gracefully than crashing. :) Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- In order to encounter the situation described above, you have to execute a query that calls a pl/pgsql function expecting a table row as an argument, but have the query produce no row that can be passed in. For example, doing a left join between a patient and dentist table where there is no dentist row for a corresponding patient row. And then call a pl/pgsql function, passing in the nonexistent dentist row. CREATE TABLE patient ( patient_id INTEGER, first_name TEXT, last_name TEXT, dentist_id INTEGER ); CREATE TABLE dentist ( dentist_id INTEGER, first_name TEXT, last_name TEXT ); CREATE OR REPLACE FUNCTION full_name(dentist) RETURNS text AS ' DECLARE d ALIAS FOR $1; BEGIN RETURN d.first_name || '' '' || d.last_name; END; ' LANGUAGE 'plpgsql'; -- Note: John Smith has no dentist INSERT INTO patient (patient_id, first_name, last_name) VALUES (1, 'John', 'Smith'); -- Get a list of patient IDs and dentist names SELECT p.patient_id, full_name(d) AS dentist_name FROM patient p LEFT JOIN dentist d ON (p.dentist_id = d.dentist_id); If you know how this problem might be fixed, list the solution below: - Change the assertion protecting against this case in src/pl/plpgsql/src/pl_exec.c to an if statement, so that the row argument is only copied into the function's arguments if the row actually exists. Otherwise, a row with no columns is passed in to the function, which gets NULLs when it tries to access any of the row's columns. I think this is correct behavior -- if there was no row, then there should be no values passed into the function. See the attached file pl_exec.c.patch (against postgresql 7.4.1). pl_exec.c.patch Description: Binary data Thanks! - Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] pg_autovacuum bug with temp tables?
[Apologies to anyone who is seeing this for the second time; the author of pg_autovacuum suggested it belonged here, so I am reposting.] Recently I installed and started pg_autovacuum against my new Pg 7.4.1 installation. We use a fairly large number of temporary tables within an application (that is, several copies of this application may be running, and each creates and drops several temp tables as they cycle through their workload). Here's what I think happened, based on the log (pg_autovacuum's and the postmaster's): pg_autovacuum.log: [2004-02-15 08:10:01 AM] Performing: ANALYZE "pg_temp_13"."tmp_targs" [2004-02-15 08:10:01 AM] Can not refresh statistics information from the database nexcerpt. [2004-02-15 08:10:01 AM] The error is [ERROR: relation "pg_temp_13.tmp_targs" does not exist postmaster.log: 2004-02-15 08:10:01 [31563] ERROR: relation "pg_temp_13.tmp_targs" does not exist 2004-02-15 08:10:01 [31563] LOG: unexpected EOF on client connection It appears that pg_autovacuum collected the name of a temp table, and later tried to analyze it. The table was gone by then, and this caused the daemon to exit. As this happened on a Sunday morning, my weekend experiment to see how pg_autovacuum would maintain our test database was rather spoiled ... 8-( -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] memory does not return back
I'm using freeradius 0.9.3 with postgresql 7.3.2. All I do is just inserting some h323 accounting data into my tables. After a couple of hours the memory reaches to about 1G and then Linux starts using swap file.I lose some of my records and PC becomes very slow. Even if I restart postgresql , the memory does not return back. I've ordered another 1Gb RAM.Is it really necessary? When I reboot Linux and start Postgresql momory usage is about 250 Mb. Even a single select from a large table which lasts more than a minute causes this. I'm using Pentium 4 (2.6 GHZ) , 1 GB RAM, 80Gb HDD. PostgreSQL 7.3.2 on i386-redhat-linux-gnu, compiled by GCC i386-redhat- linux-gcc (GCC) 3.2.2 20030213 (Red Hat Linux 8.0 3.2.2-1). Just a couple of days ago I ordered Intel PC with two Xeon CPUs and 1 GB RAM(soon would be 2GB) and installed Linux(9) on it same as above. Selects are faster But again RAM usage reaches about 1GB and it's using swap , cpu cache.Restarting Postgresql does not help. What am I to do now? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Crash when calling a pl/pgsql function with no row to pass as an argument
POSTGRESQL BUG REPORT TEMPLATE Your name : Chris Campbell Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : PowerPC G3 Operating System (example: Linux 2.4.18) : Mac OS X 10.3.2 (Darwin 7.2.0) PostgreSQL version (example: PostgreSQL-7.4.1): PostgreSQL-7.4.1 Compiler used (example: gcc 2.95.2) : gcc 3.3 20030304 Please enter a FULL description of your problem: postmaster crashes if it tries to call a pl/plgsql function that requires a table row as an argument, and there is no row produced in the query that can be passed in. There is currently an assertion in the code to guard against this case, but it's not an error case, so it needs to be handled more gracefully than crashing. :) Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- In order to encounter the situation described above, you have to execute a query that calls a pl/pgsql function expecting a table row as an argument, but have the query produce no row that can be passed in. For example, doing a left join between a patient and dentist table where there is no dentist row for a corresponding patient row. And then call a pl/pgsql function, passing in the nonexistent dentist row. CREATE TABLE patient ( patient_id INTEGER, first_name TEXT, last_name TEXT, dentist_id INTEGER ); CREATE TABLE dentist ( dentist_id INTEGER, first_name TEXT, last_name TEXT ); CREATE OR REPLACE FUNCTION full_name(dentist) RETURNS text AS ' DECLARE d ALIAS FOR $1; BEGIN RETURN d.first_name || '' '' || d.last_name; END; ' LANGUAGE 'plpgsql'; -- Note: John Smith has no dentist INSERT INTO patient (patient_id, first_name, last_name) VALUES (1, 'John', 'Smith'); -- Get a list of patient IDs and dentist names SELECT p.patient_id, full_name(d) AS dentist_name FROM patient p LEFT JOIN dentist d ON (p.dentist_id = d.dentist_id); If you know how this problem might be fixed, list the solution below: - Change the assertion protecting against this case in src/pl/plpgsql/src/pl_exec.c to an if statement, so that the row argument is only copied into the function's arguments if the row actually exists. Otherwise, a row with no columns is passed in to the function, which gets NULLs when it tries to access any of the row's columns. I think this is correct behavior -- if there was no row, then there should be no values passed into the function. See the attached file pl_exec.c.patch (diffed against postgresql 7.4.1). pl_exec.c.patch Description: Binary data Thanks! - Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
