Re: [BUGS] BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT
On Thursday 11 March 2004 06:45, PostgreSQL Bugs List wrote: > The following bug has been logged online: > > Bug reference: 1098 > Logged by: Tim Burgess > Email address: [EMAIL PROTECTED] > Description:Multiple ON INSERT rules not applied properly in the > case of INSERT...SELECT > Rules: > quips_transfer_from AS ON INSERT TO quips_transactions DO UPDATE > users_quips SET balance = (users_quips.balance - new.amount) WHERE > ((users_quips.username)::text = (new.user_from)::text) > quips_transfer_to AS ON INSERT TO quips_transactions DO UPDATE > users_quips SET balance = (users_quips.balance + new.amount) WHERE > ((users_quips.username)::text = (new.user_to)::text) > insert into quips_transactions select 'frontoffice_quips', member_username, > 10, now(), 'Free Print Credit' from group_members where groupname = > 'freshers_04'; > > And all the transactions are added, however the rules do not execute > properly. In our case, the quips_transfer_to rule worked fine - all the > students had their balances credited. However, the quips_transfer_from > rule was only applied once (the frontoffice_quips user had their balance > lowered by $10, not $2180 as they should have). The rule should only be applied once. The rule system basically rewrites the insert you supply into two other insert queries (from/to). It will not generate one query for each row in quips_transactions (which is what you want). You probably need a trigger on the transactions table that issues separate queries for each row inserted. -- Richard Huxton Archonet Ltd ---(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
[BUGS] BUG #1099: bad syntax error localisation
The following bug has been logged online: Bug reference: 1099 Logged by: Fabien Coelho Email address: [EMAIL PROTECTED] PostgreSQL version: 7.5 Dev Operating system: linux debian Description:bad syntax error localisation Details: The example is in the regression tests:-) In src/test/regress/output/create_function_1.source on line 55: CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'not even SQL'; ERROR: syntax error at or near "not" at character 1 You may notice that word "not" is not at character 1, where there is "CREATE". ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1099: bad syntax error localisation
PostgreSQL Bugs List wrote: > CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql > AS 'not even SQL'; > ERROR: syntax error at or near "not" at character 1 > > You may notice that word "not" is not at character 1, > where there is "CREATE". The error message refers to the function body, which is parsed separately and therefore starts the count at 1. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT
Richard Huxton <[EMAIL PROTECTED]> writes: > The rule should only be applied once. The rule system basically rewrites the > insert you supply into two other insert queries (from/to). It will not > generate one query for each row in quips_transactions (which is what you > want). You probably need a trigger on the transactions table that issues > separate queries for each row inserted. The real issue is that since the multi-row insert command generates multi-row update commands, user rows that are mentioned multiple times in the "from" or "to" columns of the insert would have to be updated multiple times in the same UPDATE command. We do not support that --- an UPDATE can only update a given row once, because it does not see its own output row versions as input candidates. This is annoying in this scenario but it is correct and necessary in most other scenarios. As an example, without this rule something like "UPDATE foo SET x = x + 1" would likely go into an infinite loop, repeatedly seeing the new row version it just created as fodder for another UPDATE cycle. I concur with the suggestion that triggers would probably be the most intuitive solution to the problem. I tried to think of a way to make it work as a rule by aggregating all the updates affecting a single user row into one row operation. However, since the UPDATE syntax has no provision for GROUP BY there doesn't seem to be any good way to do that. 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] BUG #1099: bad syntax error localisation
Peter Eisentraut <[EMAIL PROTECTED]> writes: > PostgreSQL Bugs List wrote: >> CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql >> AS 'not even SQL'; >> ERROR: syntax error at or near "not" at character 1 > The error message refers to the function body, which is parsed > separately and therefore starts the count at 1. I'm surprised there is no CONTEXT line here though. You would have gotten one for a syntax error in plpgsql; I must have overlooked doing it for SQL. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Glitch: cannot use Arrays with Raise Notice
Josh Berkus wrote: Bug: Cannot Use Arrays with Raise Notice in PL/pgSQL. Version Tested: 7.4.1 Severity: Annoyance Description: Attempting to pass an array element to Raise Notice in PL/pgSQL will produce a parse error: I can reproduce this with cvs tip -- I'll check into it. Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1100: pd_dump doesn't work with upper case table names
The following bug has been logged online: Bug reference: 1100 Logged by: Bill Erickson Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4 Operating system: Solaris 9 Description:pd_dump doesn't work with upper case table names Details: I have found that in PG 7.4.1 the dump_pg command no longer seems to work with upper case table names. Here is how I am using the command: pg_dump -h pglocalhost -U scncraft -a -d -t SA_SSN; If I change the table name to lower case the command will work. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Glitch: cannot use Arrays with Raise Notice
Joe Conway <[EMAIL PROTECTED]> writes: > I can reproduce this with cvs tip -- I'll check into it. It's no surprise --- plpgsql's RAISE doesn't take anything but a string literal for the format, and unadorned variable names for the additional parameters. It ought to be generalized some time ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1100: pd_dump doesn't work with upper case table names
"PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes:
> I have found that in PG 7.4.1 the dump_pg command
> no longer seems to work with upper case table names.
Works fine for me:
regression=# create table "SA_SSN" ("F1" int);
CREATE TABLE
regression=# \q
$ pg_dump -t SA_SSN regression
-- (produces dump of table)
$ pg_dump -t sa_ssn regression
pg_dump: specified table "sa_ssn" does not exist
Are you quite sure you created the table with an upper-case name?
Also: some time back, we had versions of pg_dump that behaved
differently in this respect; perhaps you are mistakenly invoking an
old pg_dump. (Though I'd think that a pg_dump that old would fail
completely against a 7.4 server anyway ...)
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] BUG #1096: pg_restore cannot restore large objects with
I have found the bug. Unfortunately, I have a table called public.pg_proc within my database with the same structure of pg_catalog.pg_proc. I did a test with it and forgot to drop it. Within postgresql-7.4.1/src/interfaces/libpq/fe-lobj.c near line 555 is a query call as res = PQexec(conn, "select proname, oid from pg_proc \ where proname = 'lo_open' \ or proname = 'lo_close' \ or proname = 'lo_creat' \ or proname = 'lo_unlink' \ or proname = 'lo_lseek' \ or proname = 'lo_tell' \ or proname = 'loread'\ or proname = 'lowrite'"); without a given schema. In my opinion, it must be: res = PQexec(conn, "select proname, oid from pg_catalog.pg_proc \ ... I have changed it in my fe-lobj.c . Now pg_restore works well. BTW, I have droped the my test table public.pg_proc too ;-) Regards, Janko Tom Lane wrote: "PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes: When I restore the dumped database, pg_restore tries to restore the functions of tsearch2 as a large object. It does what? I don't think you're describing this accurately. Please show the actual problem and not your interpretation of it. regards, tom lane -- Janko Richter ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1096: pg_restore cannot restore large objects with other oid columns
Janko Richter <[EMAIL PROTECTED]> writes: > I have found the bug. > Unfortunately, I have a table called public.pg_proc within my database > with the same structure of pg_catalog.pg_proc. I did a test with it and > forgot to drop it. > Within postgresql-7.4.1/src/interfaces/libpq/fe-lobj.c near line 555 is > a query call as Now that's a coincidence. I just fixed that problem about a week ago --- it's fixed in 7.4.2 ... 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] Function is called multiple times in subselect
pgsql-bugs: I wrote a plpgsql function that does a fairly expensive calculation on its input, and want to peform a query that: 1. Selects certain rows from a table, then 2. Calls my function on the selected rows So I wrote a query and used a subselect to first select the rows, and then used the outer select to call my function on each of the selected rows and return the result. I referenced the result of my inner select's calculation multiple times in the outer select, and found that my function is called once for *each reference* to its result in the outer select, rather than once for each row of the inner select. Here's a simple example: CREATE FUNCTION square_it(INTEGER) RETURNS INTEGER AS ' DECLARE i ALIAS FOR $1; BEGIN RAISE NOTICE ''square_it(%)'', i; RETURN (i * i); END; ' LANGUAGE 'plpgsql'; CREATE TABLE foo (i INTEGER); INSERT INTO foo (i) VALUES (1); INSERT INTO foo (i) VALUES (2); INSERT INTO foo (i) VALUES (3); INSERT INTO foo (i) VALUES (4); SELECT query.i, query.squared AS test1, query.squared + 1 AS test2, query.squared + 2 AS test3, query.squared + 3 AS test4 FROM ( SELECT i, square_it(i) AS squared FROM foo ) query; When I run it, I expect to see 4 lines of output, and I expect that square_it() will have been called 4 times (once for each row). However, it is actually called *4 times for each row* because I reference "query.squared" 4 times in the outer select. NOTICE: square_it(1) NOTICE: square_it(1) NOTICE: square_it(1) NOTICE: square_it(1) NOTICE: square_it(2) NOTICE: square_it(2) NOTICE: square_it(2) NOTICE: square_it(2) NOTICE: square_it(3) NOTICE: square_it(3) NOTICE: square_it(3) NOTICE: square_it(3) NOTICE: square_it(4) NOTICE: square_it(4) NOTICE: square_it(4) NOTICE: square_it(4) i | test1 | test2 | test3 | test4 ---+---+---+---+--- 1 | 1 | 2 | 3 | 4 2 | 4 | 5 | 6 | 7 3 | 9 |10 |11 |12 4 |16 |17 |18 |19 (4 rows) I don't think this should be happening (PostgreSQL 7.4.1). I think it should be saving the result of the calculation in the resulting rows from the innery query. In my case, that means my query takes 4 times longer than it should. And when it's a query that takes a nontrivial amount of time to execute, that's harsh. Any ideas? Here's the query plan: QUERY PLAN - Seq Scan on foo (cost=0.00..37.50 rows=1000 width=4) (actual time=3.203..4.384 rows=4 loops=1) Total runtime: 4.742 ms Thanks! - Chris smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #1096: pg_restore cannot restore large objects with
7.4.X CVS has:
if (conn->sversion >= 70300)
query = "select proname, oid from pg_catalog.pg_proc "
"where proname in ("
"'lo_open', "
"'lo_close', "
"'lo_creat', "
"'lo_unlink', "
"'lo_lseek', "
"'lo_tell', "
"'loread', "
"'lowrite') "
"and pronamespace = (select oid from pg_catalog.pg_namespace
"
"where nspname = 'pg_catalog')";
so I think this fix is in 7.4.2. Please upgrade.
---
Janko Richter wrote:
> I have found the bug.
>
> Unfortunately, I have a table called public.pg_proc within my database
> with the same structure of pg_catalog.pg_proc. I did a test with it and
> forgot to drop it.
>
> Within postgresql-7.4.1/src/interfaces/libpq/fe-lobj.c near line 555 is
> a query call as
>
> res = PQexec(conn, "select proname, oid from pg_proc \
> where proname = 'lo_open' \
>or proname = 'lo_close' \
>or proname = 'lo_creat' \
>or proname = 'lo_unlink' \
>or proname = 'lo_lseek' \
>or proname = 'lo_tell' \
>or proname = 'loread'\
>or proname = 'lowrite'");
>
> without a given schema. In my opinion, it must be:
>
> res = PQexec(conn, "select proname, oid from pg_catalog.pg_proc \
> ...
>
> I have changed it in my fe-lobj.c . Now pg_restore works well.
>
> BTW, I have droped the my test table public.pg_proc too ;-)
>
> Regards, Janko
>
>
> Tom Lane wrote:
>
> >"PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes:
> >
> >
> >>When I restore the dumped database, pg_restore tries to restore the
> >>functions of tsearch2 as a large object.
> >>
> >>
> >
> >It does what? I don't think you're describing this accurately. Please
> >show the actual problem and not your interpretation of it.
> >
> > regards, tom lane
> >
> >
> >
> --
> Janko Richter
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 8: explain analyze is your friend
