[BUGS] Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable
The following nonsensical query causes PostgreSQL to fail with ERROR: plan should not reference subplan's variable. (This was stripped down from an 'useful' query that triggered the same bug). First encountered on 8.3.4, reproduced on 8.3.7 BEGIN; CREATE SCHEMA bug_schema; SET SEARCH_PATH='bug_schema'; CREATE FUNCTION AGG_GROUP_CONCAT_SFUNC(IN _state TEXT, IN _str TEXT, IN _sep TEXT) RETURNS TEXT SECURITY INVOKER LANGUAGE PLPGSQL IMMUTABLE CALLED ON NULL INPUT AS $PROC$ BEGIN IF _str IS NULL THEN RETURN _state; END IF; IF _state IS NULL THEN RETURN _str; END IF; RETURN _state || _sep || _str; END; $PROC$; CREATE AGGREGATE GROUP_CONCAT(TEXT, TEXT) ( STYPE = TEXT, SFUNC = AGG_GROUP_CONCAT_SFUNC ); CREATE TABLE foo ( idserialNOT NULL, fnamevarchar(64)NOT NULL, PRIMARY KEY (id) ); -- Fails: ERROR: plan should not reference subplan's variable SELECT (SELECT GROUP_CONCAT((SELECT s2.fname FROM foo AS s2 WHERE s2.id=s.idORDER BY fname), '; ')) AS foolist FROM foo AS s; -- Also fails, same error SELECT (SELECT MAX((SELECT s2.fname FROM foo AS s2 WHERE s2.id=s.id ORDER BY fname))) AS foomaxFROM foo AS s; ROLLBACK;
Re: [BUGS] Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable
On Fri, Apr 24, 2009 at 5:38 PM, Tom Lane wrote: > Daniel Grace writes: > > The following nonsensical query causes PostgreSQL to fail with ERROR: > plan > > should not reference subplan's variable. (This was stripped down from an > > 'useful' query that triggered the same bug). First encountered on 8.3.4, > > reproduced on 8.3.7 > > Hmmm ... I guess something is getting confused about the level of query > nesting. FWIW, you can avoid the bug in these two examples by omitting > the inner "SELECT" keyword, which is useless anyway. Perhaps it is > needed in your real query though ... > >regards, tom lane > It's required in my case to force the aggregate function to evaluate its inputs in a set order. I'm trying to replace MySQL's GROUP_CONCAT function, including the ORDER BY option. I had another variation (that did not use sub-SELECTs, but instead joining something along the lines of joining (SELECT * FROM foo ORDER BY fname) AS foo that partially worked -- however, it had the side effect (due to the nature of the query) of having some duplicate data and not the type that could be fixed simply by adding DISTINCT. I'm not going to spam the list with all of the table definitions for the real query, but I will paste it by itself to give a better idea of what I was originally attempting: SELECT s.fid, c.flags, c.id, c.title, cs.ut_start, cs.ut_end, cr.full_name, cal.title AS cancel_reason, (SELECT GROUP_CONCAT((SELECT s2.fname FROM student AS s2 WHERE s2.id= s.id ORDER BY fname), '; ')) AS students, (SELECT GROUP_CONCAT((SELECT p.gname FROM course_teacher AS ct INNER JOIN person AS p ON ct.tid=p.id WHERE ct.cid=c.id ORDER BY p.gname), '; ')) AS teacher FROM student AS s INNER JOIN student_course_session AS scs ON scs.sid=s.id INNER JOIN course_session AS cs ON cs.id=scs.csid INNER JOIN course AS c ON c.id=cs.cid LEFT JOIN course_room AS cr ON cr.id=c.room_id LEFT JOIN calendar AS cal ON cal.id=cs.cancelled_by GROUP BY s.fid, cs.id, c.flags, c.id, c.title, cs.ut_start, cs.ut_end, cr.full_name, cal.title, cs.tsstart ORDER BY s.fid, cs.tsstart, c.title; -- Daniel Grace
Re: [BUGS] Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable
No luck there either (runs, but with incorrect results), but since I know this isn't a support list and is a bugs list I just would like to point out that: Even though what I was doing that triggered the bug is apparently incorrect and 'silly', it's still possible that some complicated legitimate query might trigger the same problem -- so it may be worth looking into. On Fri, Apr 24, 2009 at 7:19 PM, Tom Lane wrote: > I wrote: > > I'm talking about the underlined SELECT, not the one inside the > > aggregate. AFAICS this one is totally useless. > > Oh, wait. It is useless in the query as written, but now that I think > twice about what you're trying to accomplish, you do need three levels > of SELECT keywords. Just not like that. I think what you actually > want is > > SELECT >... >(SELECT GROUP_CONCAT(t.fname, '; ') FROM > (SELECT s2.fname FROM student AS s2 >WHERE s2.id=s.id ORDER BY fname) AS t) AS students, >... > FROM >student AS s > > What you wrote instead is just wrong --- it would fail if there were > multiple students with the same id (can that actually happen? > Maybe there's more wrong with this query...), because what you > wrote is a scalar sub-SELECT inside an aggregate call that belongs > to the outermost query. > >regards, tom lane > -- Daniel Grace AGE, LLC System Administrator and Software Developer dgr...@wingsnw.com // (425)327-0079 // www.wingsnw.com
Re: [BUGS] Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable
On Sat, Apr 25, 2009 at 9:52 AM, Tom Lane wrote: > Daniel Grace writes: > > The following nonsensical query causes PostgreSQL to fail with ERROR: > plan > > should not reference subplan's variable. (This was stripped down from an > > 'useful' query that triggered the same bug). First encountered on 8.3.4, > > reproduced on 8.3.7 > > Patch is here: > http://archives.postgresql.org/pgsql-committers/2009-04/msg00277.php > > I still think that it won't affect you once you have the query logic > straight, though. > Thanks for the info. I've since fixed the query, so you are right in that regard ;) -- Daniel Grace
[BUGS] BUG #5548: ERROR: invalid attnum ## for rangetable entry on EXPLAIN VERBOSE, not on EXPLAIN
The following bug has been logged online: Bug reference: 5548 Logged by: Daniel Grace Email address: dgr...@wingsnw.com PostgreSQL version: 9.0beta2 Operating system: Windows XP 32-bit Description:ERROR: invalid attnum ## for rangetable entry on EXPLAIN VERBOSE, not on EXPLAIN Details: I apologize for not including detailed schema information. It took a lot to get this to reduce to the point it did, and hopefully this is enough information to find a bug. If not, email me back and I'll see how much schema information I can provide. When using EXPLAIN [ANALYZE] VERBOSE on the below query: SELECT t.* FROM ( SELECT TRUE AS is_enrolled, acal.weight::REAL / (SELECT SUM(acal2.weight) FROM allocation_calendar AS acal2 WHERE acal.year=acal2.year)::REAL AS calc_weight, ( TRUNC(EXTRACT(EPOCH FROM LENGTH( PERIOD( GREATEST(FIRST(acal.daterange), acd.tstime), LEAST(NEXT(acal.daterange), FIRST_VALUE(acd.tstime) OVER nextdate) ) ))) / TRUNC(EXTRACT(EPOCH FROM LENGTH(acal.daterange))) ) AS calc_duration, NULL::integer AS group_id FROM allocation_calculated_dates AS acd INNER JOIN allocation_calendar AS acal ON acd.acalid=acal.id INNER JOIN log_status AS ls ON ls.sid=acd.sid AND ls.tsrange ~ acd.tstime WINDOW nextdate AS ( PARTITION BY acd.sid, acd.acalid ORDER BY acd.tstime ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) ) AS t CROSS JOIN yearinfo AS yi -- ON t.year=yi.year LEFT JOIN group_info AS gi ON gi.id=t.group_id WHERE t.is_enrolled /* AND yi.allocation_lock=0 */ I receive the following result: ERROR: invalid attnum 5 for rangetable entry t This appears to be originating from get_rte_attribute_name() in parse_relation.c (which might need to consider RTE_SUBQUERY?) Stripping the final WHERE clause out (WHERE t.is_enrolled) causes the error to go away, as do most modifications to joined tables. When ran as a regular SELECT, the query runs fine and produces correct outputs. A regular EXPLAIN yields: "Nested Loop (cost=1136.45..1146.96 rows=16 width=17)" " -> Subquery Scan on t (cost=1136.45..1145.71 rows=4 width=17)" "Filter: t.is_enrolled" "-> WindowAgg (cost=1136.45..1145.63 rows=8 width=38)" " -> Sort (cost=1136.45..1136.47 rows=8 width=38)" "Sort Key: s.id, wings_demo.allocation_calendar.id, (GREATEST(first(ls.tsrange), first(CASE WHEN (wings_demo.allocation_calendar.countdate IS NULL) THEN wings_demo.allocation_calendar.daterange ELSE period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone) END)))" "-> Hash Join (cost=1055.63..1136.33 rows=8 width=38)" " Hash Cond: (ls.sid = s.id)" " Join Filter: (ls.tsrange ~ (GREATEST(first(ls.tsrange), first(CASE WHEN (wings_demo.allocation_calendar.countdate IS NULL) THEN wings_demo.allocation_calendar.daterange ELSE period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone) END" " -> Seq Scan on log_status ls (cost=0.00..76.26 rows=1126 width=20)" " -> Hash (cost=1055.56..1055.56 rows=6 width=38)" "-> Hash Join (cost=1055.07..1055.56 rows=6 width=38)" " Hash Cond: (wings_demo.allocation_calendar.id = acal.id)" " -> HashAggregate (cost=1053.93..1054.11 rows=18 width=46)" "-> Append (cost=561.01..1053.75 rows=18 width=46)" " -> Merge Left Join (cost=561.01..596.61 rows=17 width=46)" "Merge Cond: ((s.id = ao.sid) AND (wings_demo.allocation_calendar.year = ao.year))" "Filter: (ao.amount IS NULL)" "-> Sort (cost=472.83..481.28 rows=3378 width=46)" " Sort Key: s.id, wings_demo.allocation_calendar.year" " -> Nested Loop (cost=30.02..274.85 rows=3378 width=46)" "
Re: [BUGS] BUG #5548: ERROR: invalid attnum ## for rangetable entry on EXPLAIN VERBOSE, not on EXPLAIN
On Thu, Jul 8, 2010 at 10:52 PM, Tom Lane wrote: > "Daniel Grace" writes: >> I apologize for not including detailed schema information. It took a lot to >> get this to reduce to the point it did, and hopefully this is enough >> information to find a bug. > > It is not. You haven't provided anywhere near enough information > for someone else to reproduce the failure. We're not going to > guess at the tables or views that underlie your query ... > > regards, tom lane > So I've spent the greater portion of the last two hours trying to slim down the schema and query enough to provide something that can reproduce this. While I can reproduce it 100% of the time with live data, I can't get it to reproduce at all with test data -- though I've included a few schemas below. It seems to be based on what plan ends up being constructed for the query. This doesn't really affect me at this point -- but my concern is that it might cause actual problems when paired with the auto_explain contrib module. Basic stripped-down schema: DROP SCHEMA IF EXISTS test CASCADE; CREATE SCHEMA test; SET SEARCH_PATH=test,public; CREATE TABLE allocation_calendar ( id serial NOT NULL, "year" smallint NOT NULL, "name" character varying(64) NOT NULL, countdate date, availabledate date NOT NULL, weight integer NOT NULL, daterange integer NOT NULL, -- Was a PERIOD, but not required to reproduce CONSTRAINT allocation_calendar_pkey PRIMARY KEY (id), CONSTRAINT allocation_calendar_ux_year UNIQUE (year, name) ); CREATE INDEX allocation_calendar_ix_year_3 ON allocation_calendar (year, countdate); CREATE INDEX allocation_calendar_ix_year_4 ON allocation_calendar (year, availabledate); CREATE TABLE yearinfo ( id serial NOT NULL, year smallint NOT NULL, CONSTRAINT yearinfo_ux_year UNIQUE (year) ); INSERT INTO yearinfo (year) SELECT * FROM GENERATE_SERIES(1000, 2000); INSERT INTO allocation_calendar (year, name, countdate, availabledate, weight, daterange) SELECT f.v, 'Year ' || f.v, NULL, '-infinity', 1, 2 FROM GENERATE_SERIES(1000, 5000) AS f(v); ANALYZE allocation_calendar; REINDEX TABLE yearinfo; REINDEX TABLE allocation_calendar; CREATE OR REPLACE VIEW allocation_calculated_dates AS SELECT acal.id AS acalid, acal.year AS year, null::integer AS tstime FROM allocation_calendar AS acal ; EXPLAIN ANALYZE SELECT t.* FROM ( SELECT FIRST_VALUE(acd.tstime) OVER nextdate AS foo FROM allocation_calculated_dates AS acd INNER JOIN allocation_calendar AS acal ON acd.acalid=acal.id WINDOW nextdate AS ( PARTITION BY acd.acalid ORDER BY acd.tstime ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) ) AS t CROSS JOIN yearinfo AS yi -- ON t.year=yi.year WHERE t.foo IS NULL Plan that fails on EXPLAIN VERBOSE: "Nested Loop (cost=0.00..132.35 rows=24 width=4)" " -> Subquery Scan on t (cost=0.00..131.00 rows=6 width=4)" "Filter: (t.foo IS NULL)" "-> WindowAgg (cost=0.00..119.50 rows=1150 width=4)" " -> Merge Join (cost=0.00..102.25 rows=1150 width=4)" "Merge Cond: (acal.id = acal.id)" "-> Index Scan using allocation_calendar_pkey on allocation_calendar acal (cost=0.00..42.50 rows=1150 width=4)" "-> Index Scan using allocation_calendar_pkey on allocation_calendar acal (cost=0.00..42.50 rows=1150 width=4)" " -> Materialize (cost=0.00..1.06 rows=4 width=0)" "-> Seq Scan on yearinfo yi (cost=0.00..1.04 rows=4 width=0)" --> ERROR: invalid attnum 2 for rangetable entry t Note: The attnum in question always seems to be 1 more than the number of columns in t. Plan that succeeds on EXPLAIN VERBOSE: "Nested Loop (cost=0.00..827.88 rows=20020 width=4) (actual time=0.036..2566.818 rows=4005001 loops=1)" " -> Seq Scan on yearinfo yi (cost=0.00..15.01 rows=1001 width=0) (actual time=0.007..0.429 rows=1001 loops=1)" " -> Materialize (cost=0.00..562.67 rows=20 width=4) (actual time=0.000..0.850 rows=4001 loops=1001)" "-> Subquery Scan on t (cost=0.00..562.57 rows=20 width=4) (actual time=0.026..14.731 rows=4001 loops=1)" " Filter: (t.foo IS NULL)" " -> WindowAgg (cost=0.00..522.56 rows=4001 width=4) (actual time=0.025..12.637 rows=4001 loops=1)" "-> Merge Join (cost=0.00..462.55 rows=4001 width=4) (actual time=0.016..7.715 rows=4001 loops=1)" " Merge Cond: (acal.id = acal.id)" " -> Index Scan using allocation_calendar_pkey on allocation_calendar acal (
[BUGS] BUG #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
The following bug has been logged online:
Bug reference: 5563
Logged by: Daniel Grace
Email address: dgr...@wingsnw.com
PostgreSQL version: 9.0beta3
Operating system: Windows XP 32-bit
Description:Odd behavior with aggregate_func(DISTINCT foo ORDER BY
foo)
Details:
The manual states:
"If DISTINCT is specified in addition to an order_by_clause, then all the
ORDER BY expressions must match regular arguments of the aggregate; that is,
you cannot sort on an expression that is not included in the DISTINCT list.
"
However, in some circumstances Postgres will fail
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
t VARCHAR
);
INSERT INTO foo (t) VALUES ('a'), ('a'), ('b'), ('b'), ('c');
SELECT STRING_AGG(DISTINCT t::TEXT ORDER BY t::TEXT) FROM foo;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5564: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
The following bug has been logged online:
Bug reference: 5564
Logged by: Daniel Grace
Email address: dgr...@wingsnw.com
PostgreSQL version: 9.0beta3
Operating system: Windows XP 32-bit
Description:Odd behavior with aggregate_func(DISTINCT foo ORDER BY
foo)
Details:
(Apologies if there's a duplicate, I may have accidentally submitted too
early. Tab+spacebar is a bad combination on browsers)
The manual states:
"If DISTINCT is specified in addition to an order_by_clause, then all the
ORDER BY expressions must match regular arguments of the aggregate; that is,
you cannot sort on an expression that is not included in the DISTINCT list.
"
However, in some circumstances Postgres will fail with "in an aggregate
with DISTINCT, ORDER BY expressions must appear in argument list" when the
same column is named in both places. It appears to be related to cases when
the aggregate function in question requires implicit typecasts:
This test case fails with the above error:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
t VARCHAR
);
INSERT INTO foo (t) VALUES ('a'), ('a'), ('b'), ('b'), ('c');
SELECT STRING_AGG(DISTINCT t ORDER BY t) FROM foo;
However, if t is cast to text in both halves of the aggregate function, it
works correctly:
SELECT STRING_AGG(DISTINCT t::TEXT ORDER BY t::TEXT) FROM foo;
It also works correctly if t is defined as TEXT instead of VARCHAR in the
table definition.
Note that if t is typecast in the ORDER BY but not the DISTINCT part, the
statement still fails (even though STRING_AGG implicitly casts t to text)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)
On Fri, Jul 23, 2010 at 10:42 AM, Alex Hunsaker wrote: > On Fri, Jul 16, 2010 at 18:04, Daniel Grace wrote: >> However, in some circumstances Postgres will fail > > How exactly? > > Maybe its so obvious I missed it? > Please see BUG #5564 -- I accidentally submitted this one before I was finished typing the details. -- Daniel -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Planner producing 100% duplicate subplans when unneeded
ERE clause altogether. Both plans run the same speed with one parent_id. The first plan starts losing speed gradually as the number of parents increase; the second plan is either all-or-nothing. In the first case, it seems inefficient to duplicate the subplan for each reference -- I'd think the (corrected) plan should look something like this: Seq Scan on wings_sky.parent p (cost=0.00..161113.12 rows=1000 width=4) Output: p.id, (SubPlan 1), ((SubPlan 1))[1], ((SubPlan 1))[2], ((SubPlan 1))[3] SubPlan 1 -> Aggregate (cost=40.26..40.27 rows=1 width=8) Output: ARRAY[sum(c.v2), sum(CASE WHEN (c.v1 > 15) THEN c.v2 ELSE 0 END), sum(CASE WHEN (c.v1 > 5) THEN c.v2 ELSE 0 END)] -> Index Scan using child_pkey on wings_sky.child c (cost=0.00..40.10 rows=20 width=8) Output: c.parent_id, c.v1, c.v2 Index Cond: (c.parent_id = $0) Is there any chance this might be looked at in a future release? -- Daniel Grace AGE, LLC System Administrator and Software Developer dgr...@wingsnw.com // www.wingsnw.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5688: ALTER TABLE ALTER col TYPE newtype fails if col is named in an UPDATE OF col trigger
The following bug has been logged online: Bug reference: 5688 Logged by: Daniel Grace Email address: dgr...@wingsnw.com PostgreSQL version: 9.0.0 Operating system: Windows XP 32-bit Description:ALTER TABLE ALTER col TYPE newtype fails if col is named in an UPDATE OF col trigger Details: Given the following state: CREATE TABLE foo ( bar TEXT, baz TEXT ); CREATE OR REPLACE FUNCTION foo_trigger_proc() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$BEGIN RETURN NEW; END$$; CREATE TRIGGER foo_trigger BEFORE INSERT OR UPDATE OF bar ON foo FOR EACH ROW EXECUTE PROCEDURE foo_trigger_proc(); The following happens: > ALTER TABLE foo ALTER bar TYPE VARCHAR; Fails: ERROR: unexpected object depending on column: trigger foo_trigger on table foo SQL state: XX000 > ALTER TABLE foo ALTER baz TYPE VARCHAR; Succeeds (because baz is not named in the trigger) > ALTER TABLE foo DROP bar; Correctly produces an error message: ERROR: cannot drop table foo column bar because other objects depend on it DETAIL: trigger foo_trigger on table foo depends on table foo column bar > ALTER TABLE foo DROP bar CASCADE; Correctly removes bar and foo_trigger ALTER TABLE foo RENAME bar and other variations of variations of ALTER column seem to function correctly. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Planner producing 100% duplicate subplans when unneeded
As a theoretical question (I'm not savvy on Postgres's code but might be intrigued enough to beat on it anyways), is it feasible to do an additional pass on the query plan that essentially goes: - Are these two subplans identical? - Are they at the same part of the tree? and if both of these conditions are true, discard one subplan and rewrite all references to point to the other one? Assuming it IS possible, are there any particular situations where it wouldn't work? On Mon, Oct 4, 2010 at 11:47 AM, Robert Haas wrote: > On Mon, Sep 27, 2010 at 5:09 PM, Daniel Grace wrote: >> Is there any chance this might be looked at in a future release? > > This is another interesting example of a case where an inlining-type > optimization (which is effectively what's happening here, I think) > turns out to be a negative. We had one a while back that involved > actual function inlining, which is not quite what's happening here, > but it's close. It doesn't seem too hard to figure out whether or not > inlining is a win (non-trivial subexpressions should probably never > get duplicated), but nobody's gotten around to writing the logic to > make it work yet. One useful technique is to stick "OFFSET 0" into > the subquery; that prevents it from being inlined and gives you > something more like the plan you were hoping for. > > Whether or not this will get looked at in a future release is a tough > question to answer. It's possible that someone (most likely, Tom) > will get motivated to fix this out of sheer annoyance with the current > behavior, or will notice a way to improve it incidentally while making > some other change. But of course the only way to make sure it gets > fixed is to do it yourself (or pay someone to do it). > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company > -- Daniel Grace AGE, LLC System Administrator and Software Developer dgr...@wingsnw.com // (425)327-0079 // www.wingsnw.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5985: CLUSTER ... USING can fail with ERROR: index xxx does not belong to table yyy
The following bug has been logged online: Bug reference: 5985 Logged by: Daniel Grace Email address: thisgenericn...@gmail.com PostgreSQL version: 9.1a5 Operating system: Win7 x64, also seen on Debian Description:CLUSTER ... USING can fail with ERROR: index xxx does not belong to table yyy Details: [Apologies if this duplicates. NoScript apparently thought me submitting this was XSS, so I'm not sure if the first report went through or not.] I've had no luck reducing this to a reproducible test case, but here goes anyways: I have a lengthy script that consists of inputting a bunch of SQL files into Postgres in sequence. Essentially the first file is importing a database from MySQL and the subsequent files are doing schema alterations (separated out by table), with a sort of dependency solving mechanism built into the script. One such file (contents listed below, admittably not 100% useful without a full schema and data) makes a bunch of alterations to a table but fails when it reaches CLUSTER: psql:D:/SVN/wings/wings/branches/devpg/db/lib/course.sql:38: ERROR: index 17813 8 does not belong to table 176177 However, this failure only occurs if the file is wrapped in a transaction block. Outside of a transaction block, it works fine. My theory is this has something to do with the new CLUSTER change, in conjunction with the fact that an index with the specified name is being dropped and then created in the same transaction. The same dataset on 9.0 works without any issues. Partial SQL is below. Note that it works when not wrapped with a transaction block. -- @Requires: data, flags -- @Provides: course CREATE TRIGGER _restrict BEFORE UPDATE OF id ON course EXECUTE PROCEDURE update_restricted__tproc(); ALTER TABLE course ALTER credit_designation_inherit TYPE BOOLEAN USING credit_designation_inherit<>0, ALTER credit_designation_inherit SET DEFAULT TRUE, ADD FOREIGN KEY(gid) REFERENCES group_info(id) ON UPDATE CASCADE ON DELETE RESTRICT, ALTER section DROP NOT NULL, ALTER slp_mindays DROP NOT NULL, ALTER credits DROP NOT NULL, DROP COLUMN IF EXISTS lock_token ; UPDATE course SET section=NULLIF(section, 0), credits=NULLIF(credits,0), slp_mindays=NULLIF(slp_mindays,0); --These aren't immutable. --CREATE INDEX course_ix_start ON course ((startdate+starttime)); --CREATE INDEX course_ix_end ON course ((enddate+endtime)); DROP INDEX course_ix_flags; DROP INDEX course_ix_location; DROP INDEX course_ix_credit_designation_inherit; DROP INDEX course_ix_grademin; CREATE INDEX course_ix_grademin ON course(grademin); CREATE INDEX course_ix_grademax ON course(grademax); DROP INDEX course_ix_year; CREATE INDEX course_ix_year ON course(year); DROP INDEX course_ix_origin_course; CREATE INDEX course_ix_origin_course ON course(origin_course) WHERE origin_course IS NOT NULL; DROP INDEX course_ix_origin_op; CREATE INDEX course_ix_origin_op ON course(origin_op) WHERE origin_op IS NOT NULL; CLUSTER VERBOSE course USING course_ix_year; ANALYZE course; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5987: Rows created by WITH (INSERT ... RETURNING) are not visible to the rest of the query
The following bug has been logged online:
Bug reference: 5987
Logged by: Daniel Grace
Email address: dgr...@wingsnw.com
PostgreSQL version: 9.1-alpha5
Operating system: Win7 x64
Description:Rows created by WITH (INSERT ... RETURNING) are not
visible to the rest of the query
Details:
This may not be a bug, but I'll describe it anyways. Apologies if this is a
duplicate -- "WITH" is apparently a stopword and searching the archives
using it produces zero results.
9.1 adds the ability to use WITH in INSERT/UPDATE/DELETE in both
directions.
However, the actual effects of the query in the WITH clause are not visible
to the outer query.
-
DROP TABLE IF EXISTS a;
CREATE TABLE a ( t TEXT );
INSERT INTO a VALUES ('test1') RETURNING *; -- Works as expected
WITH b AS (INSERT INTO a VALUES ('test2') RETURNING *) SELECT * FROM b; --
Works as expected
WITH b AS (INSERT INTO a VALUES ('test3') RETURNING *) SELECT a.* FROM a
INNER JOIN b USING(t); -- Does not see the newly created row.
SELECT * FROM a WHERE t='test3';-- But it was created.
WITH b AS (INSERT INTO a VALUES ('test4') RETURNING *) -- Does not see the
newly created row, thus the update does not happen.
UPDATE a SET t='test5'
FROM b
WHERE a.t=b.t;
SELECT * FROM a;
-
This is also true if the WITH query is a stored procedure that modifies the
database and returns results, i.e. WITH b AS (SELECT * FROM
create_row('test6'))...
Presumably it affects UPDATE and DELETE as well, but I didn't test those
cases.
My actual use case is: I'm calling a function to duplicate+modify some rows.
(Essentially, it does INSERT ... SELECT from the same table, but forcing
the primary key to be reassigned via being a serial column and some other
changes). This function returns the new rows as results (doing RETURN QUERY
INSERT ... SELECT ... RETURNING *). In some situations, I want to further
update the freshly created rows, so the goal was to do this:
WITH newrows AS (SELECT * FROM function_that_creates_rows(...))
UPDATE basetable SET foo=overrides.bar
FROM newrows
LEFT JOIN (VALUES (...)) AS overrides(...)
WHERE ...
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6005: ALTER ROLE ... VALID UNTIL 'infinity' crashes postmaster on a fresh install
The following bug has been logged online: Bug reference: 6005 Logged by: Daniel Grace Email address: dgr...@wingsnw.com PostgreSQL version: 9.1-beta1 Operating system: Win7 x64 (x86 postgres) Description:ALTER ROLE ... VALID UNTIL 'infinity' crashes postmaster on a fresh install Details: While trying to restore a database created under 9.1 alpha (created with pg_dumpall): CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD ''; CREATE ROLE foo; ALTER ROLE foo WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION VALID UNTIL 'infinity'; The final statement causes postmaster to crash. I attempted it again breaking each part of the ALTER ROLE into individual statements, e.g. ALTER ROLE foo WITH NOSUPERUSER; ALTER ROLE foo WITH INHERIT; ... and it was the ALTER ROLE foo VALID UNTIL 'infinity' line causing the crash. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6005: ALTER ROLE ... VALID UNTIL 'infinity' crashes postmaster on a fresh install
I don't have the means to easily compile a PG build, but if there's a place to get nightly builds or such I'd be happy to give it a shot and report back. On Wed, May 11, 2011 at 5:02 PM, Tom Lane wrote: > Robert Haas writes: >> Will commit 2e82d0b396473b595a30f68b37b8dfd41c37dff8 have possibly fixed >> this? > > This should now be fixed as of HEAD, but it wouldn't be a bad idea for > someone to check that it really works on Windows. Look at whether the > timezone_abbreviations GUC has a sane value and you can use timezone > abbreviations, not just whether 'infinity' crashes. > > regards, tom lane > -- Daniel Grace AGE, LLC System Administrator and Software Developer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
