compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
Started by Jeff Davisover 15 years ago9 messages
Compiling with RELCACHE_FORCE_RELEASE doesn't pass "make check" on my
machine.
Is it supposed to pass?
Regards,
Jeff Davis
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
Jeff Davis <pgsql@j-davis.com> writes:
Compiling with RELCACHE_FORCE_RELEASE doesn't pass "make check" on my
machine.
What happens exactly?
Is it supposed to pass?
It shouldn't crash, but I'm not certain whether you'd see any
visible diffs in the tests.
regards, tom lane
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
On Wed, 2010-09-01 at 15:31 -0400, Tom Lane wrote:
Jeff Davis <pgsql@j-davis.com> writes:
Compiling with RELCACHE_FORCE_RELEASE doesn't pass "make check" on my
machine.What happens exactly?
Is it supposed to pass?
It shouldn't crash, but I'm not certain whether you'd see any
visible diffs in the tests.
I do:
CFLAGS="-O0 -DRELCACHE_FORCE_RELEASE" ./configure --enable-debug \
--enable-depend --enable-cassert
I have attached regression.diffs after a "make check". The diffs don't
look trivial, and actually look quite strange to me.
It happens deterministically for me, so I assume that anyone on linux
x86-64 would see the same thing.
Regards,
Jeff Davis
Attachments:
regression.diffstext/x-patch; charset=ISO-8859-1; name=regression.diffsDownload
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/create_type.out 2010-09-01 15:27:48.257621838 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_type.out 2010-09-01 15:30:06.538280313 -0700
***************
*** 86,97 ****
CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS '
SELECT * FROM default_test;
' LANGUAGE SQL;
SELECT * FROM get_default_test();
! f1 | f2
! -------+----
! zippo | 42
! (1 row)
!
-- Test comments
COMMENT ON TYPE bad IS 'bad comment';
ERROR: type "bad" does not exist
--- 86,98 ----
CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS '
SELECT * FROM default_test;
' LANGUAGE SQL;
+ ERROR: type default_test_row is not composite
+ CONTEXT: SQL function "get_default_test"
SELECT * FROM get_default_test();
! ERROR: function get_default_test() does not exist
! LINE 1: SELECT * FROM get_default_test();
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- Test comments
COMMENT ON TYPE bad IS 'bad comment';
ERROR: type "bad" does not exist
***************
*** 101,107 ****
CREATE TYPE text_w_default; -- should fail
ERROR: type "text_w_default" already exists
DROP TYPE default_test_row CASCADE;
- NOTICE: drop cascades to function get_default_test()
DROP TABLE default_test;
-- Check usage of typmod with a user-defined type
-- (we have borrowed numeric's typmod functions)
--- 102,107 ----
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/create_function_2.out 2010-09-01 15:29:56.698248597 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_function_2.out 2010-09-01 15:30:06.698872691 -0700
***************
*** 5,14 ****
--- 5,18 ----
RETURNS setof hobbies_r
AS 'select * from hobbies_r where person = $1.name'
LANGUAGE SQL;
+ ERROR: type person is not composite
+ CONTEXT: SQL function "hobbies"
CREATE FUNCTION hobby_construct(text, text)
RETURNS hobbies_r
AS 'select $1 as name, $2 as hobby'
LANGUAGE SQL;
+ ERROR: type hobbies_r is not composite
+ CONTEXT: SQL function "hobby_construct"
CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
RETURNS hobbies_r.person%TYPE
AS 'select person from hobbies_r where name = $1'
***************
*** 19,24 ****
--- 23,30 ----
RETURNS setof equipment_r
AS 'select * from equipment_r where hobby = $1.name'
LANGUAGE SQL;
+ ERROR: type hobbies_r is not composite
+ CONTEXT: SQL function "equipment"
CREATE FUNCTION user_relns()
RETURNS setof name
AS 'select relname
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/create_aggregate.out 2010-09-01 15:27:48.257621838 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/create_aggregate.out 2010-09-01 15:30:07.159497058 -0700
***************
*** 48,61 ****
--- 48,67 ----
create function aggf_trans(aggtype[],integer,integer,text) returns aggtype[]
as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
language sql strict immutable;
+ ERROR: type aggtype is not composite
+ CONTEXT: SQL function "aggf_trans"
create function aggfns_trans(aggtype[],integer,integer,text) returns aggtype[]
as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
language sql immutable;
+ ERROR: type aggtype is not composite
+ CONTEXT: SQL function "aggfns_trans"
create aggregate aggfstr(integer,integer,text) (
sfunc = aggf_trans, stype = aggtype[],
initcond = '{}'
);
+ ERROR: function aggf_trans(aggtype[], integer, integer, text) does not exist
create aggregate aggfns(integer,integer,text) (
sfunc = aggfns_trans, stype = aggtype[],
initcond = '{}'
);
+ ERROR: function aggfns_trans(aggtype[], integer, integer, text) does not exist
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/inherit.out 2010-09-01 15:27:48.267621881 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/inherit.out 2010-09-01 15:30:07.878872110 -0700
***************
*** 687,711 ****
create table derived () inherits (base);
insert into derived (i) values (0);
select derived::base from derived;
! derived
! ---------
! (0)
! (1 row)
!
drop table derived;
drop table base;
create table p1(ff1 int);
create table p2(f1 text);
create function p2text(p2) returns text as 'select $1.f1' language sql;
create table c1(f3 int) inherits(p1,p2);
insert into c1 values(123456789, 'hi', 42);
select p2text(c1.*) from c1;
! p2text
! --------
! hi
! (1 row)
!
drop function p2text(p2);
drop table c1;
drop table p2;
drop table p1;
--- 687,709 ----
create table derived () inherits (base);
insert into derived (i) values (0);
select derived::base from derived;
! ERROR: type base is not composite
drop table derived;
drop table base;
create table p1(ff1 int);
create table p2(f1 text);
create function p2text(p2) returns text as 'select $1.f1' language sql;
+ ERROR: type p2 is not composite
+ CONTEXT: SQL function "p2text"
create table c1(f3 int) inherits(p1,p2);
insert into c1 values(123456789, 'hi', 42);
select p2text(c1.*) from c1;
! ERROR: function p2text(c1) does not exist
! LINE 1: select p2text(c1.*) from c1;
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
drop function p2text(p2);
+ ERROR: function p2text(p2) does not exist
drop table c1;
drop table p2;
drop table p1;
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/typed_table.out 2010-09-01 15:27:48.277621964 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/typed_table.out 2010-09-01 15:30:07.159497058 -0700
***************
*** 2,94 ****
ERROR: type "nothing" does not exist
CREATE TYPE person_type AS (id int, name text);
CREATE TABLE persons OF person_type;
CREATE TABLE IF NOT EXISTS persons OF person_type;
! NOTICE: relation "persons" already exists, skipping
SELECT * FROM persons;
! id | name
! ----+------
! (0 rows)
!
\d persons
- Table "public.persons"
- Column | Type | Modifiers
- --------+---------+-----------
- id | integer |
- name | text |
- Typed table of type: person_type
-
CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
LANGUAGE SQL
AS $$
SELECT * FROM persons;
$$;
SELECT * FROM get_all_persons();
! id | name
! ----+------
! (0 rows)
!
-- certain ALTER TABLE operations on typed tables are not allowed
ALTER TABLE persons ADD COLUMN comment text;
! ERROR: cannot add column to typed table
ALTER TABLE persons DROP COLUMN name;
! ERROR: cannot drop column from typed table
ALTER TABLE persons RENAME COLUMN id TO num;
! ERROR: cannot rename column of typed table
ALTER TABLE persons ALTER COLUMN name TYPE varchar;
! ERROR: cannot alter column type of typed table
CREATE TABLE stuff (id int);
ALTER TABLE persons INHERIT stuff;
! ERROR: cannot change inheritance of typed table
CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error
! ERROR: column "myname" does not exist
CREATE TABLE persons2 OF person_type (
id WITH OPTIONS PRIMARY KEY,
UNIQUE (name)
);
! NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons2_pkey" for table "persons2"
! NOTICE: CREATE TABLE / UNIQUE will create implicit index "persons2_name_key" for table "persons2"
\d persons2
- Table "public.persons2"
- Column | Type | Modifiers
- --------+---------+-----------
- id | integer | not null
- name | text |
- Indexes:
- "persons2_pkey" PRIMARY KEY, btree (id)
- "persons2_name_key" UNIQUE CONSTRAINT, btree (name)
- Typed table of type: person_type
-
CREATE TABLE persons3 OF person_type (
PRIMARY KEY (id),
name WITH OPTIONS DEFAULT ''
);
! NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons3_pkey" for table "persons3"
\d persons3
- Table "public.persons3"
- Column | Type | Modifiers
- --------+---------+------------------
- id | integer | not null
- name | text | default ''::text
- Indexes:
- "persons3_pkey" PRIMARY KEY, btree (id)
- Typed table of type: person_type
-
CREATE TABLE persons4 OF person_type (
name WITH OPTIONS NOT NULL,
name WITH OPTIONS DEFAULT '' -- error, specified more than once
);
! ERROR: column "name" specified more than once
DROP TYPE person_type RESTRICT;
- ERROR: cannot drop type person_type because other objects depend on it
- DETAIL: table persons depends on type person_type
- function get_all_persons() depends on type person_type
- table persons2 depends on type person_type
- table persons3 depends on type person_type
- HINT: Use DROP ... CASCADE to drop the dependent objects too.
DROP TYPE person_type CASCADE;
! NOTICE: drop cascades to 4 other objects
! DETAIL: drop cascades to table persons
! drop cascades to function get_all_persons()
! drop cascades to table persons2
! drop cascades to table persons3
DROP TABLE stuff;
--- 2,60 ----
ERROR: type "nothing" does not exist
CREATE TYPE person_type AS (id int, name text);
CREATE TABLE persons OF person_type;
+ ERROR: type person_type is not composite
CREATE TABLE IF NOT EXISTS persons OF person_type;
! ERROR: type person_type is not composite
SELECT * FROM persons;
! ERROR: relation "persons" does not exist
! LINE 1: SELECT * FROM persons;
! ^
\d persons
CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
LANGUAGE SQL
AS $$
SELECT * FROM persons;
$$;
+ ERROR: relation "persons" does not exist
+ LINE 4: SELECT * FROM persons;
+ ^
SELECT * FROM get_all_persons();
! ERROR: function get_all_persons() does not exist
! LINE 1: SELECT * FROM get_all_persons();
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- certain ALTER TABLE operations on typed tables are not allowed
ALTER TABLE persons ADD COLUMN comment text;
! ERROR: relation "persons" does not exist
ALTER TABLE persons DROP COLUMN name;
! ERROR: relation "persons" does not exist
ALTER TABLE persons RENAME COLUMN id TO num;
! ERROR: relation "persons" does not exist
ALTER TABLE persons ALTER COLUMN name TYPE varchar;
! ERROR: relation "persons" does not exist
CREATE TABLE stuff (id int);
ALTER TABLE persons INHERIT stuff;
! ERROR: relation "persons" does not exist
CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error
! ERROR: type person_type is not composite
CREATE TABLE persons2 OF person_type (
id WITH OPTIONS PRIMARY KEY,
UNIQUE (name)
);
! ERROR: type person_type is not composite
\d persons2
CREATE TABLE persons3 OF person_type (
PRIMARY KEY (id),
name WITH OPTIONS DEFAULT ''
);
! ERROR: type person_type is not composite
\d persons3
CREATE TABLE persons4 OF person_type (
name WITH OPTIONS NOT NULL,
name WITH OPTIONS DEFAULT '' -- error, specified more than once
);
! ERROR: type person_type is not composite
DROP TYPE person_type RESTRICT;
DROP TYPE person_type CASCADE;
! ERROR: type "person_type" does not exist
DROP TABLE stuff;
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/subselect.out 2010-09-01 15:27:48.267621881 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/subselect.out 2010-09-01 15:30:09.317623226 -0700
***************
*** 484,512 ****
insert into table_a values (42);
create temp view view_a as select * from table_a;
select view_a from view_a;
! view_a
! --------
! (42)
! (1 row)
!
select (select view_a) from view_a;
! ?column?
! ----------
! (42)
! (1 row)
!
select (select (select view_a)) from view_a;
! ?column?
! ----------
! (42)
! (1 row)
!
select (select (a.*)::text) from view_a a;
! ?column?
! ----------
! (42)
! (1 row)
!
--
-- Test case for sublinks pushed down into subselects via join alias expansion
--
--- 484,496 ----
insert into table_a values (42);
create temp view view_a as select * from table_a;
select view_a from view_a;
! ERROR: type view_a is not composite
select (select view_a) from view_a;
! ERROR: type view_a is not composite
select (select (select view_a)) from view_a;
! ERROR: type view_a is not composite
select (select (a.*)::text) from view_a a;
! ERROR: type view_a is not composite
--
-- Test case for sublinks pushed down into subselects via join alias expansion
--
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/aggregates.out 2010-09-01 15:27:48.257621838 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/aggregates.out 2010-09-01 15:30:09.267623163 -0700
***************
*** 591,804 ****
-- multi-arg aggs, strict/nonstrict, distinct/order by
select aggfstr(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! aggfstr
! ---------------------------------------
! {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
select aggfns(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! aggfns
! -----------------------------------------------
! {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
select aggfstr(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! aggfstr
! ---------------------------------------
! {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
select aggfns(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! aggfns
! -----------------------------------------------
! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
select aggfstr(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! aggfstr
! ---------------------------------------
! {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
! (1 row)
!
select aggfns(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! aggfns
! -----------------------------------------------
! {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
! (1 row)
!
-- test specific code paths
select aggfns(distinct a,a,c order by c using ~<~,a)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! aggfns
! ------------------------------------------------
! {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
! (1 row)
!
select aggfns(distinct a,a,c order by c using ~<~)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! aggfns
! ------------------------------------------------
! {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
! (1 row)
!
select aggfns(distinct a,a,c order by a)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! aggfns
! ------------------------------------------------
! {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
! (1 row)
!
select aggfns(distinct a,b,c order by a,c using ~<~,b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! aggfns
! -----------------------------------------------
! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
-- check node I/O via view creation and usage, also deparsing logic
create view agg_view1 as
select aggfns(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! select * from agg_view1;
! aggfns
! -----------------------------------------------
! {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
! select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! --------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
! (1 row)
!
create or replace view agg_view1 as
select aggfns(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! select * from agg_view1;
! aggfns
! -----------------------------------------------
! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
! select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
! (1 row)
!
create or replace view agg_view1 as
select aggfns(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! select * from agg_view1;
! aggfns
! -----------------------------------------------
! {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
! (1 row)
!
! select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
! (1 row)
!
create or replace view agg_view1 as
select aggfns(a,b,c order by b+1)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! select * from agg_view1;
! aggfns
! -----------------------------------------------
! {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
! (1 row)
!
! select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
! (1 row)
!
create or replace view agg_view1 as
select aggfns(a,a,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! select * from agg_view1;
! aggfns
! ------------------------------------------------
! {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
! (1 row)
!
! select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
! (1 row)
!
create or replace view agg_view1 as
select aggfns(a,b,c order by c using ~<~)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! select * from agg_view1;
! aggfns
! -----------------------------------------------
! {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
! (1 row)
!
! select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
! (1 row)
!
create or replace view agg_view1 as
select aggfns(distinct a,b,c order by a,c using ~<~,b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! select * from agg_view1;
! aggfns
! -----------------------------------------------
! {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
! (1 row)
!
! select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i);
! (1 row)
!
drop view agg_view1;
-- incorrect DISTINCT usage errors
select aggfns(distinct a,b,c order by i)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,b,c order by i)
! ^
select aggfns(distinct a,b,c order by a,b+1)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,b,c order by a,b+1)
! ^
select aggfns(distinct a,b,c order by a,b,i,c)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
! ^
select aggfns(distinct a,a,c order by a,b)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,a,c order by a,b)
! ^
-- string_agg tests
select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
string_agg
--- 591,799 ----
-- multi-arg aggs, strict/nonstrict, distinct/order by
select aggfstr(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! ERROR: function aggfstr(integer, integer, text) does not exist
! LINE 1: select aggfstr(a,b,c)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfns(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 1: select aggfns(a,b,c)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfstr(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! ERROR: function aggfstr(integer, integer, text) does not exist
! LINE 1: select aggfstr(distinct a,b,c)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfns(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 1: select aggfns(distinct a,b,c)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfstr(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! ERROR: function aggfstr(integer, integer, text) does not exist
! LINE 1: select aggfstr(distinct a,b,c order by b)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfns(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 1: select aggfns(distinct a,b,c order by b)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- test specific code paths
select aggfns(distinct a,a,c order by c using ~<~,a)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 1: select aggfns(distinct a,a,c order by c using ~<~,a)
! ^
! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.
select aggfns(distinct a,a,c order by c using ~<~)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 1: select aggfns(distinct a,a,c order by c using ~<~)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfns(distinct a,a,c order by a)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 1: select aggfns(distinct a,a,c order by a)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfns(distinct a,b,c order by a,c using ~<~,b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 1: select aggfns(distinct a,b,c order by a,c using ~<~,b)
! ^
! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.
-- check node I/O via view creation and usage, also deparsing logic
create view agg_view1 as
select aggfns(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 2: select aggfns(a,b,c)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
! select * from agg_view1;
! ERROR: relation "agg_view1" does not exist
! LINE 1: select * from agg_view1;
! ^
! select pg_get_viewdef('agg_view1'::regclass);
! ERROR: relation "agg_view1" does not exist
! LINE 1: select pg_get_viewdef('agg_view1'::regclass);
! ^
create or replace view agg_view1 as
select aggfns(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 2: select aggfns(distinct a,b,c)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
! select * from agg_view1;
! ERROR: relation "agg_view1" does not exist
! LINE 1: select * from agg_view1;
! ^
! select pg_get_viewdef('agg_view1'::regclass);
! ERROR: relation "agg_view1" does not exist
! LINE 1: select pg_get_viewdef('agg_view1'::regclass);
! ^
create or replace view agg_view1 as
select aggfns(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 2: select aggfns(distinct a,b,c order by b)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
! select * from agg_view1;
! ERROR: relation "agg_view1" does not exist
! LINE 1: select * from agg_view1;
! ^
! select pg_get_viewdef('agg_view1'::regclass);
! ERROR: relation "agg_view1" does not exist
! LINE 1: select pg_get_viewdef('agg_view1'::regclass);
! ^
create or replace view agg_view1 as
select aggfns(a,b,c order by b+1)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 2: select aggfns(a,b,c order by b+1)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
! select * from agg_view1;
! ERROR: relation "agg_view1" does not exist
! LINE 1: select * from agg_view1;
! ^
! select pg_get_viewdef('agg_view1'::regclass);
! ERROR: relation "agg_view1" does not exist
! LINE 1: select pg_get_viewdef('agg_view1'::regclass);
! ^
create or replace view agg_view1 as
select aggfns(a,a,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 2: select aggfns(a,a,c order by b)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
! select * from agg_view1;
! ERROR: relation "agg_view1" does not exist
! LINE 1: select * from agg_view1;
! ^
! select pg_get_viewdef('agg_view1'::regclass);
! ERROR: relation "agg_view1" does not exist
! LINE 1: select pg_get_viewdef('agg_view1'::regclass);
! ^
create or replace view agg_view1 as
select aggfns(a,b,c order by c using ~<~)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 2: select aggfns(a,b,c order by c using ~<~)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
! select * from agg_view1;
! ERROR: relation "agg_view1" does not exist
! LINE 1: select * from agg_view1;
! ^
! select pg_get_viewdef('agg_view1'::regclass);
! ERROR: relation "agg_view1" does not exist
! LINE 1: select pg_get_viewdef('agg_view1'::regclass);
! ^
create or replace view agg_view1 as
select aggfns(distinct a,b,c order by a,c using ~<~,b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
! LINE 2: select aggfns(distinct a,b,c order by a,c using ~<~,b)
! ^
! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.
! select * from agg_view1;
! ERROR: relation "agg_view1" does not exist
! LINE 1: select * from agg_view1;
! ^
! select pg_get_viewdef('agg_view1'::regclass);
! ERROR: relation "agg_view1" does not exist
! LINE 1: select pg_get_viewdef('agg_view1'::regclass);
! ^
drop view agg_view1;
+ ERROR: view "agg_view1" does not exist
-- incorrect DISTINCT usage errors
select aggfns(distinct a,b,c order by i)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
LINE 1: select aggfns(distinct a,b,c order by i)
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
select aggfns(distinct a,b,c order by a,b+1)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
LINE 1: select aggfns(distinct a,b,c order by a,b+1)
! ^
! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.
select aggfns(distinct a,b,c order by a,b,i,c)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
! ^
! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.
select aggfns(distinct a,a,c order by a,b)
from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
! ERROR: function aggfns(integer, integer, text) does not exist
LINE 1: select aggfns(distinct a,a,c order by a,b)
! ^
! HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.
-- string_agg tests
select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
string_agg
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/arrays.out 2010-09-01 15:27:48.257621838 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/arrays.out 2010-09-01 15:30:09.337622960 -0700
***************
*** 923,942 ****
-- XXX would like to not have to specify row() construct types here ...
insert into comptable
values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
-- check that implicitly named array type _comptype isn't a problem
create type _comptype as enum('fooey');
select * from comptable;
! c1 | c2
! ---------+-----------------------
! (1,foo) | {"(2,bar)","(3,baz)"}
! (1 row)
select c2[2].f2 from comptable;
! f2
! -----
! baz
! (1 row)
!
drop type _comptype;
drop table comptable;
drop type comptype;
--- 923,938 ----
-- XXX would like to not have to specify row() construct types here ...
insert into comptable
values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
+ ERROR: type comptype is not composite
-- check that implicitly named array type _comptype isn't a problem
create type _comptype as enum('fooey');
select * from comptable;
! c1 | c2
! ----+----
! (0 rows)
select c2[2].f2 from comptable;
! ERROR: type comptype is not composite
drop type _comptype;
drop table comptable;
drop type comptype;
***************
*** 1273,1288 ****
-- Insert/update on a column that is array of composite
create temp table t1 (f1 int8_tbl[]);
insert into t1 (f1[5].q1) values(42);
select * from t1;
! f1
! -----------------
! [5:5]={"(42,)"}
! (1 row)
update t1 set f1[5].q2 = 43;
select * from t1;
! f1
! -------------------
! [5:5]={"(42,43)"}
! (1 row)
--- 1269,1283 ----
-- Insert/update on a column that is array of composite
create temp table t1 (f1 int8_tbl[]);
insert into t1 (f1[5].q1) values(42);
+ ERROR: type int8_tbl is not composite
select * from t1;
! f1
! ----
! (0 rows)
update t1 set f1[5].q2 = 43;
select * from t1;
! f1
! ----
! (0 rows)
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/misc.out 2010-09-01 15:29:56.698248597 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/misc.out 2010-09-01 15:30:13.858246862 -0700
***************
*** 446,568 ****
-- everyone else does nothing.
--
SELECT p.name, name(p.hobbies) FROM ONLY person p;
! name | name
! -------+-------------
! mike | posthacking
! joe | basketball
! sally | basketball
! (3 rows)
!
--
-- as above, but jeff also does post_hacking.
--
SELECT p.name, name(p.hobbies) FROM person* p;
! name | name
! -------+-------------
! mike | posthacking
! joe | basketball
! sally | basketball
! jeff | posthacking
! (4 rows)
!
--
-- the next two queries demonstrate how functions generate bogus duplicates.
-- this is a "feature" ..
--
SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
ORDER BY 1,2;
! name | name
! -------------+---------------
! basketball | hightops
! posthacking | advil
! posthacking | peet's coffee
! skywalking | guts
! (4 rows)
!
SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
! name | name
! -------------+---------------
! posthacking | advil
! posthacking | peet's coffee
! posthacking | advil
! posthacking | peet's coffee
! basketball | hightops
! basketball | hightops
! skywalking | guts
! (7 rows)
!
--
-- mike needs advil and peet's coffee,
-- joe and sally need hightops, and
-- everyone else is fine.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
! name | name | name
! -------+-------------+---------------
! mike | posthacking | advil
! mike | posthacking | peet's coffee
! joe | basketball | hightops
! sally | basketball | hightops
! (4 rows)
!
--
-- as above, but jeff needs advil and peet's coffee as well.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
! name | name | name
! -------+-------------+---------------
! mike | posthacking | advil
! mike | posthacking | peet's coffee
! joe | basketball | hightops
! sally | basketball | hightops
! jeff | posthacking | advil
! jeff | posthacking | peet's coffee
! (6 rows)
!
--
-- just like the last two, but make sure that the target list fixup and
-- unflattening is being done correctly.
--
SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
! name | name | name
! ---------------+-------+-------------
! advil | mike | posthacking
! peet's coffee | mike | posthacking
! hightops | joe | basketball
! hightops | sally | basketball
! (4 rows)
!
SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
! name | name | name
! ---------------+-------+-------------
! advil | mike | posthacking
! peet's coffee | mike | posthacking
! hightops | joe | basketball
! hightops | sally | basketball
! advil | jeff | posthacking
! peet's coffee | jeff | posthacking
! (6 rows)
!
SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
! name | name | name
! ---------------+-------------+-------
! advil | posthacking | mike
! peet's coffee | posthacking | mike
! hightops | basketball | joe
! hightops | basketball | sally
! (4 rows)
!
SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
! name | name | name
! ---------------+-------------+-------
! advil | posthacking | mike
! peet's coffee | posthacking | mike
! hightops | basketball | joe
! hightops | basketball | sally
! advil | posthacking | jeff
! peet's coffee | posthacking | jeff
! (6 rows)
!
SELECT user_relns() AS user_relns
ORDER BY user_relns;
user_relns
--- 446,510 ----
-- everyone else does nothing.
--
SELECT p.name, name(p.hobbies) FROM ONLY person p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT p.name, name(p.hobbies) FROM ONLY person p;
! ^
--
-- as above, but jeff also does post_hacking.
--
SELECT p.name, name(p.hobbies) FROM person* p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT p.name, name(p.hobbies) FROM person* p;
! ^
--
-- the next two queries demonstrate how functions generate bogus duplicates.
-- this is a "feature" ..
--
SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
ORDER BY 1,2;
! ERROR: column hobbies_r.equipment does not exist
! LINE 1: SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FR...
! ^
SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
! ERROR: column hobbies_r.equipment does not exist
! LINE 1: SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbi...
! ^
--
-- mike needs advil and peet's coffee,
-- joe and sally need hightops, and
-- everyone else is fine.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) F...
! ^
--
-- as above, but jeff needs advil and peet's coffee as well.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) F...
! ^
--
-- just like the last two, but make sure that the target list fixup and
-- unflattening is being done correctly.
--
SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) F...
! ^
SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) F...
! ^
SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name F...
! ^
SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
! ERROR: column p.hobbies does not exist
! LINE 1: SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name F...
! ^
SELECT user_relns() AS user_relns
ORDER BY user_relns;
user_relns
***************
*** 672,682 ****
(102 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
! name
! ------
! guts
! (1 row)
!
SELECT hobbies_by_name('basketball');
hobbies_by_name
-----------------
--- 614,623 ----
(102 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
! ERROR: function hobby_construct(text, text) does not exist
! LINE 1: SELECT name(equipment(hobby_construct(text 'skywalking', tex...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT hobbies_by_name('basketball');
hobbies_by_name
-----------------
***************
*** 698,738 ****
-- Try a few cases with SQL-spec row constructor expressions
--
SELECT * FROM equipment(ROW('skywalking', 'mer'));
! name | hobby
! ------+------------
! guts | skywalking
! (1 row)
!
SELECT name(equipment(ROW('skywalking', 'mer')));
! name
! ------
! guts
! (1 row)
!
SELECT *, name(equipment(h.*)) FROM hobbies_r h;
! name | person | name
! -------------+--------+---------------
! posthacking | mike | advil
! posthacking | mike | peet's coffee
! posthacking | jeff | advil
! posthacking | jeff | peet's coffee
! basketball | joe | hightops
! basketball | sally | hightops
! skywalking | | guts
! (7 rows)
!
SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
! name | person | name
! -------------+--------+---------------
! posthacking | mike | advil
! posthacking | mike | peet's coffee
! posthacking | jeff | advil
! posthacking | jeff | peet's coffee
! basketball | joe | hightops
! basketball | sally | hightops
! skywalking | | guts
! (7 rows)
!
--
-- check that old-style C functions work properly with TOASTed values
--
--- 639,663 ----
-- Try a few cases with SQL-spec row constructor expressions
--
SELECT * FROM equipment(ROW('skywalking', 'mer'));
! ERROR: function equipment(record) does not exist
! LINE 1: SELECT * FROM equipment(ROW('skywalking', 'mer'));
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT name(equipment(ROW('skywalking', 'mer')));
! ERROR: function equipment(record) does not exist
! LINE 1: SELECT name(equipment(ROW('skywalking', 'mer')));
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT *, name(equipment(h.*)) FROM hobbies_r h;
! ERROR: function equipment(hobbies_r) does not exist
! LINE 1: SELECT *, name(equipment(h.*)) FROM hobbies_r h;
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
! ERROR: function equipment(hobbies_r) does not exist
! LINE 1: SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM ho...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
--
-- check that old-style C functions work properly with TOASTed values
--
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/plpgsql.out 2010-09-01 15:27:48.267621881 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/plpgsql.out 2010-09-01 15:30:19.687639012 -0700
***************
*** 1631,1646 ****
RETURN;
END;' language plpgsql;
select * from test_table_func_rec();
! a
! -----
! 2
! 100
! 3
! 4
! 5
! 6
! (6 rows)
!
create function test_table_func_row() returns setof found_test_tbl as '
DECLARE
row found_test_tbl%ROWTYPE;
--- 1631,1637 ----
RETURN;
END;' language plpgsql;
select * from test_table_func_rec();
! ERROR: type found_test_tbl is not composite
create function test_table_func_row() returns setof found_test_tbl as '
DECLARE
row found_test_tbl%ROWTYPE;
***************
*** 1651,1666 ****
RETURN;
END;' language plpgsql;
select * from test_table_func_row();
! a
! -----
! 2
! 100
! 3
! 4
! 5
! 6
! (6 rows)
!
create function test_ret_set_scalar(int,int) returns setof int as '
DECLARE
i int;
--- 1642,1648 ----
RETURN;
END;' language plpgsql;
select * from test_table_func_row();
! ERROR: type found_test_tbl is not composite
create function test_ret_set_scalar(int,int) returns setof int as '
DECLARE
i int;
***************
*** 2429,2442 ****
return _v;
end; $$ language plpgsql;
select execute_into_test('eifoo');
! NOTICE: 10 1
! NOTICE: 10 15
! NOTICE: 10 15 20
! execute_into_test
! -------------------
! (1,2)
! (1 row)
!
drop table eifoo cascade;
drop type eitype cascade;
--
--- 2411,2419 ----
return _v;
end; $$ language plpgsql;
select execute_into_test('eifoo');
! ERROR: type eifoo is not composite
! CONTEXT: SQL statement "select (row).* from (select row(10,1)::eifoo) s"
! PL/pgSQL function "execute_into_test" line 11 at EXECUTE statement
drop table eifoo cascade;
drop type eitype cascade;
--
***************
*** 3182,3200 ****
end;
$$ language plpgsql;
select * from ret_query2(8);
! x | y | z
! ----------------------------------+----+---
! a8d2ec85eaf98407310b72eb73dda247 | -8 | f
! 596a3d04481816330f07e4f97510c28f | -6 | f
! 0267aaf632e87a63288a08331f22c7c3 | -4 | f
! 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
! cfcd208495d565ef66e7dff9f98764da | 0 | f
! c81e728d9d4c2f636f067f89cc14862c | 2 | t
! a87ff679a2f3e71d9181a67b7542122c | 4 | t
! 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t
! c9f0f895fb98ab9159f51fd0297e236d | 8 | t
! (9 rows)
!
-- test EXECUTE USING
create function exc_using(int, text) returns int as $$
declare i int;
--- 3159,3165 ----
end;
$$ language plpgsql;
select * from ret_query2(8);
! ERROR: type record_type is not composite
-- test EXECUTE USING
create function exc_using(int, text) returns int as $$
declare i int;
***************
*** 3439,3482 ****
end;
$$ language plpgsql;
select * from returnqueryf();
! a | b | c | d
! ----+----+----+----
! 10 | 20 | 30 | 40
! 50 | 60 | 70 | 80
! 10 | 20 | 30 | 40
! 50 | 60 | 70 | 80
! (4 rows)
!
alter table tabwithcols drop column b;
select * from returnqueryf();
! a | c | d
! ----+----+----
! 10 | 30 | 40
! 50 | 70 | 80
! 10 | 30 | 40
! 50 | 70 | 80
! (4 rows)
!
alter table tabwithcols drop column d;
select * from returnqueryf();
! a | c
! ----+----
! 10 | 30
! 50 | 70
! 10 | 30
! 50 | 70
! (4 rows)
!
alter table tabwithcols add column d int;
select * from returnqueryf();
! a | c | d
! ----+----+---
! 10 | 30 |
! 50 | 70 |
! 10 | 30 |
! 50 | 70 |
! (4 rows)
!
drop function returnqueryf();
drop table tabwithcols;
-- Tests for 8.4's new RAISE features
--- 3404,3419 ----
end;
$$ language plpgsql;
select * from returnqueryf();
! ERROR: type tabwithcols is not composite
alter table tabwithcols drop column b;
select * from returnqueryf();
! ERROR: type tabwithcols is not composite
alter table tabwithcols drop column d;
select * from returnqueryf();
! ERROR: type tabwithcols is not composite
alter table tabwithcols add column d int;
select * from returnqueryf();
! ERROR: type tabwithcols is not composite
drop function returnqueryf();
drop table tabwithcols;
-- Tests for 8.4's new RAISE features
***************
*** 4130,4141 ****
end;
$$ language plpgsql;
select * from conflict_test();
! ERROR: column reference "q1" is ambiguous
! LINE 1: select q1,q2 from int8_tbl
! ^
! DETAIL: It could refer to either a PL/pgSQL variable or a table column.
! QUERY: select q1,q2 from int8_tbl
! CONTEXT: PL/pgSQL function "conflict_test" line 5 at FOR over SELECT rows
create or replace function conflict_test() returns setof int8_tbl as $$
#variable_conflict use_variable
declare r record;
--- 4067,4073 ----
end;
$$ language plpgsql;
select * from conflict_test();
! ERROR: type int8_tbl is not composite
create or replace function conflict_test() returns setof int8_tbl as $$
#variable_conflict use_variable
declare r record;
***************
*** 4147,4161 ****
end;
$$ language plpgsql;
select * from conflict_test();
! q1 | q2
! ----+-------------------
! 42 | 456
! 42 | 4567890123456789
! 42 | 123
! 42 | 4567890123456789
! 42 | -4567890123456789
! (5 rows)
!
create or replace function conflict_test() returns setof int8_tbl as $$
#variable_conflict use_column
declare r record;
--- 4079,4085 ----
end;
$$ language plpgsql;
select * from conflict_test();
! ERROR: type int8_tbl is not composite
create or replace function conflict_test() returns setof int8_tbl as $$
#variable_conflict use_column
declare r record;
***************
*** 4167,4181 ****
end;
$$ language plpgsql;
select * from conflict_test();
! q1 | q2
! ------------------+-------------------
! 123 | 456
! 123 | 4567890123456789
! 4567890123456789 | 123
! 4567890123456789 | 4567890123456789
! 4567890123456789 | -4567890123456789
! (5 rows)
!
drop function conflict_test();
-- Check that an unreserved keyword can be used as a variable name
create function unreserved_test() returns int as $$
--- 4091,4097 ----
end;
$$ language plpgsql;
select * from conflict_test();
! ERROR: type int8_tbl is not composite
drop function conflict_test();
-- Check that an unreserved keyword can be used as a variable name
create function unreserved_test() returns int as $$
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/domain.out 2010-09-01 15:27:48.257621838 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/domain.out 2010-09-01 15:30:16.827623160 -0700
***************
*** 473,483 ****
--- 473,487 ----
create type ddtest1 as (f1 posint);
create table ddtest2(f1 ddtest1);
insert into ddtest2 values(row(-1));
+ ERROR: type ddtest1 is not composite
alter domain posint add constraint c1 check(value >= 0);
ERROR: cannot alter type "posint" because column "ddtest2"."f1" uses it
drop table ddtest2;
create table ddtest2(f1 ddtest1[]);
insert into ddtest2 values('{(-1)}');
+ ERROR: type ddtest1 is not composite
+ LINE 1: insert into ddtest2 values('{(-1)}');
+ ^
alter domain posint add constraint c1 check(value >= 0);
ERROR: cannot alter type "posint" because column "ddtest2"."f1" uses it
drop table ddtest2;
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/rangefuncs.out 2010-09-01 15:27:48.267621881 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/rangefuncs.out 2010-09-01 15:30:16.827623160 -0700
***************
*** 18,61 ****
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ERROR: function expression in FROM cannot refer to other relations of same query level
LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ^
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
! fooid | f2
! -------+-----
! 1 | 11
! 1 | 111
! 2 | 22
! (3 rows)
!
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
! fooid | f2
! -------+-----
! 1 | 11
! 1 | 111
! (2 rows)
!
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
! fooid | f2
! -------+-----
! 1 | 11
! 1 | 111
! (2 rows)
!
-- nested functions
select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
! fooid | f2
! -------+-----
! 1 | 11
! 1 | 111
! (2 rows)
!
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
INSERT INTO foo VALUES(1,1,'Joe');
--- 18,55 ----
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
+ ERROR: type foo2 is not composite
+ CONTEXT: SQL function "foot"
-- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ERROR: function foot(integer) does not exist
LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
! ERROR: function foot(integer) does not exist
! LINE 1: select * from foo2 where f2 in (select f2 from foot(foo2.foo...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
! ERROR: function foot(integer) does not exist
! LINE 1: select * from foo2 where f2 in (select f2 from foot(1) z whe...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
! ERROR: function foot(integer) does not exist
! LINE 1: select * from foo2 where f2 in (select f2 from foot(foo2.foo...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- nested functions
select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
! ERROR: function foot(integer) does not exist
! LINE 1: select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
INSERT INTO foo VALUES(1,1,'Joe');
***************
*** 118,158 ****
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
! fooid | foosubid | fooname
! -------+----------+---------
! 1 | 1 | Joe
! (1 row)
!
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
! fooid | foosubid | fooname
! -------+----------+---------
! 1 | 1 | Joe
! (1 row)
!
-- sql, proretset = t, prorettype = c
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
! fooid | foosubid | fooname
! -------+----------+---------
! 1 | 1 | Joe
! 1 | 2 | Ed
! (2 rows)
!
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
! fooid | foosubid | fooname
! -------+----------+---------
! 1 | 1 | Joe
! 1 | 2 | Ed
! (2 rows)
!
-- sql, proretset = f, prorettype = record
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
fooid | foosubid | fooname
--- 112,160 ----
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ ERROR: type foo is not composite
+ CONTEXT: SQL function "getfoo"
SELECT * FROM getfoo(1) AS t1;
! ERROR: function getfoo(integer) does not exist
! LINE 1: SELECT * FROM getfoo(1) AS t1;
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ ERROR: function getfoo(integer) does not exist
+ LINE 1: CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM vw_getfoo;
! ERROR: relation "vw_getfoo" does not exist
! LINE 1: SELECT * FROM vw_getfoo;
! ^
-- sql, proretset = t, prorettype = c
DROP VIEW vw_getfoo;
+ ERROR: view "vw_getfoo" does not exist
DROP FUNCTION getfoo(int);
+ ERROR: function getfoo(integer) does not exist
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+ ERROR: type foo is not composite
+ CONTEXT: SQL function "getfoo"
SELECT * FROM getfoo(1) AS t1;
! ERROR: function getfoo(integer) does not exist
! LINE 1: SELECT * FROM getfoo(1) AS t1;
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ ERROR: function getfoo(integer) does not exist
+ LINE 1: CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM vw_getfoo;
! ERROR: relation "vw_getfoo" does not exist
! LINE 1: SELECT * FROM vw_getfoo;
! ^
-- sql, proretset = f, prorettype = record
DROP VIEW vw_getfoo;
+ ERROR: view "vw_getfoo" does not exist
DROP FUNCTION getfoo(int);
+ ERROR: function getfoo(integer) does not exist
CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
fooid | foosubid | fooname
***************
*** 210,230 ****
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE plpgsql;
SELECT * FROM getfoo(1) AS t1;
! fooid | foosubid | fooname
! -------+----------+---------
! 1 | 1 | Joe
! (1 row)
!
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
! fooid | foosubid | fooname
! -------+----------+---------
! 1 | 1 | Joe
! (1 row)
!
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
DROP FUNCTION foot(int);
DROP TABLE foo2;
DROP TABLE foo;
-- Rescan tests --
--- 212,229 ----
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE plpgsql;
SELECT * FROM getfoo(1) AS t1;
! ERROR: type foo is not composite
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ ERROR: type foo is not composite
SELECT * FROM vw_getfoo;
! ERROR: relation "vw_getfoo" does not exist
! LINE 1: SELECT * FROM vw_getfoo;
! ^
DROP VIEW vw_getfoo;
+ ERROR: view "vw_getfoo" does not exist
DROP FUNCTION getfoo(int);
DROP FUNCTION foot(int);
+ ERROR: function foot(integer) does not exist
DROP TABLE foo2;
DROP TABLE foo;
-- Rescan tests --
***************
*** 281,319 ****
INSERT INTO foorescan values(5008,5,'abc.5008.5');
INSERT INTO foorescan values(5009,5,'abc.5009.5');
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
--invokes ExecReScanFunctionScan
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
! fooid | foosubid | fooname
! -------+----------+------------
! 5002 | 1 | abc.5002.1
! 5002 | 2 | abc.5002.2
! 5002 | 3 | abc.5002.3
! 5002 | 4 | abc.5002.4
! 5002 | 5 | abc.5002.5
! 5003 | 1 | abc.5003.1
! 5003 | 2 | abc.5003.2
! 5003 | 3 | abc.5003.3
! 5003 | 4 | abc.5003.4
! 5003 | 5 | abc.5003.5
! (10 rows)
!
CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
--invokes ExecReScanFunctionScan
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
! fooid | foosubid | fooname
! -------+----------+------------
! 5002 | 1 | abc.5002.1
! 5002 | 2 | abc.5002.2
! 5002 | 3 | abc.5002.3
! 5002 | 4 | abc.5002.4
! 5002 | 5 | abc.5002.5
! 5003 | 1 | abc.5003.1
! 5003 | 2 | abc.5003.2
! 5003 | 3 | abc.5003.3
! 5003 | 4 | abc.5003.4
! 5003 | 5 | abc.5003.5
! (10 rows)
!
CREATE TABLE barrescan (fooid int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan"
INSERT INTO barrescan values(5003);
--- 280,303 ----
INSERT INTO foorescan values(5008,5,'abc.5008.5');
INSERT INTO foorescan values(5009,5,'abc.5009.5');
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
+ ERROR: type foorescan is not composite
+ CONTEXT: SQL function "foorescan"
--invokes ExecReScanFunctionScan
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
! ERROR: function foorescan(integer, integer) does not exist
! LINE 1: ...M foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
+ ERROR: function foorescan(integer, integer) does not exist
+ LINE 1: CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,500...
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
--invokes ExecReScanFunctionScan
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
! ERROR: relation "vw_foorescan" does not exist
! LINE 1: ...M foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_fooresc...
! ^
CREATE TABLE barrescan (fooid int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan"
INSERT INTO barrescan values(5003);
***************
*** 323,403 ****
INSERT INTO barrescan values(5007);
INSERT INTO barrescan values(5008);
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
--invokes ExecReScanFunctionScan with chgParam != NULL
SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
! fooid | foosubid | fooname
! -------+----------+------------
! 5003 | 1 | abc.5003.1
! 5003 | 2 | abc.5003.2
! 5003 | 3 | abc.5003.3
! 5003 | 4 | abc.5003.4
! 5003 | 5 | abc.5003.5
! 5004 | 1 | abc.5004.1
! 5004 | 2 | abc.5004.2
! 5004 | 3 | abc.5004.3
! 5004 | 4 | abc.5004.4
! 5004 | 5 | abc.5004.5
! 5005 | 1 | abc.5005.1
! 5005 | 2 | abc.5005.2
! 5005 | 3 | abc.5005.3
! 5005 | 4 | abc.5005.4
! 5005 | 5 | abc.5005.5
! 5006 | 1 | abc.5006.1
! 5006 | 2 | abc.5006.2
! 5006 | 3 | abc.5006.3
! 5006 | 4 | abc.5006.4
! 5006 | 5 | abc.5006.5
! 5007 | 1 | abc.5007.1
! 5007 | 2 | abc.5007.2
! 5007 | 3 | abc.5007.3
! 5007 | 4 | abc.5007.4
! 5007 | 5 | abc.5007.5
! 5008 | 1 | abc.5008.1
! 5008 | 2 | abc.5008.2
! 5008 | 3 | abc.5008.3
! 5008 | 4 | abc.5008.4
! 5008 | 5 | abc.5008.5
! (30 rows)
!
SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
! fooid | max
! -------+-----
! 5003 | 5
! 5004 | 5
! 5005 | 5
! 5006 | 5
! 5007 | 5
! 5008 | 5
! (6 rows)
!
CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
! fooid | foosubid | fooname
! -------+----------+------------
! 5004 | 1 | abc.5004.1
! 5004 | 2 | abc.5004.2
! 5004 | 3 | abc.5004.3
! 5004 | 4 | abc.5004.4
! 5004 | 5 | abc.5004.5
! (5 rows)
!
CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
! fooid | maxsubid
! -------+----------
! 5003 | 5
! 5004 | 5
! 5005 | 5
! 5006 | 5
! 5007 | 5
! 5008 | 5
! (6 rows)
!
DROP VIEW vw_foorescan;
DROP VIEW fooview1;
DROP VIEW fooview2;
DROP FUNCTION foorescan(int,int);
DROP FUNCTION foorescan(int);
DROP TABLE foorescan;
DROP TABLE barrescan;
--
--- 307,353 ----
INSERT INTO barrescan values(5007);
INSERT INTO barrescan values(5008);
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
+ ERROR: type foorescan is not composite
+ CONTEXT: SQL function "foorescan"
--invokes ExecReScanFunctionScan with chgParam != NULL
SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
! ERROR: function foorescan(integer) does not exist
! LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
! ERROR: function foorescan(integer) does not exist
! LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(...
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
+ ERROR: function foorescan(integer) does not exist
+ LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(...
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
! ERROR: relation "fooview1" does not exist
! LINE 1: SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
! ^
CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
+ ERROR: function foorescan(integer) does not exist
+ LINE 1: ...fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(...
+ ^
+ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
! ERROR: relation "fooview2" does not exist
! LINE 1: SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
! ^
DROP VIEW vw_foorescan;
+ ERROR: view "vw_foorescan" does not exist
DROP VIEW fooview1;
+ ERROR: view "fooview1" does not exist
DROP VIEW fooview2;
+ ERROR: view "fooview2" does not exist
DROP FUNCTION foorescan(int,int);
+ ERROR: function foorescan(integer, integer) does not exist
DROP FUNCTION foorescan(int);
+ ERROR: function foorescan(integer) does not exist
DROP TABLE foorescan;
DROP TABLE barrescan;
--
***************
*** 847,883 ****
create or replace function get_first_user() returns users as
$$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
language sql stable;
SELECT get_first_user();
! get_first_user
! ----------------
! (id,email,t)
! (1 row)
!
SELECT * FROM get_first_user();
! userid | email | enabled
! --------+-------+---------
! id | email | t
! (1 row)
!
create or replace function get_users() returns setof users as
$$ SELECT * FROM users ORDER BY userid; $$
language sql stable;
SELECT get_users();
! get_users
! ----------------
! (id,email,t)
! (id2,email2,t)
! (2 rows)
!
SELECT * FROM get_users();
! userid | email | enabled
! --------+--------+---------
! id | email | t
! id2 | email2 | t
! (2 rows)
!
drop function get_first_user();
drop function get_users();
drop table users;
-- this won't get inlined because of type coercion, but it shouldn't fail
create or replace function foobar() returns setof text as
--- 797,833 ----
create or replace function get_first_user() returns users as
$$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
language sql stable;
+ ERROR: type users is not composite
+ CONTEXT: SQL function "get_first_user"
SELECT get_first_user();
! ERROR: function get_first_user() does not exist
! LINE 1: SELECT get_first_user();
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM get_first_user();
! ERROR: function get_first_user() does not exist
! LINE 1: SELECT * FROM get_first_user();
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
create or replace function get_users() returns setof users as
$$ SELECT * FROM users ORDER BY userid; $$
language sql stable;
+ ERROR: type users is not composite
+ CONTEXT: SQL function "get_users"
SELECT get_users();
! ERROR: function get_users() does not exist
! LINE 1: SELECT get_users();
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM get_users();
! ERROR: function get_users() does not exist
! LINE 1: SELECT * FROM get_users();
! ^
! HINT: No function matches the given name and argument types. You might need to add explicit type casts.
drop function get_first_user();
+ ERROR: function get_first_user() does not exist
drop function get_users();
+ ERROR: function get_users() does not exist
drop table users;
-- this won't get inlined because of type coercion, but it shouldn't fail
create or replace function foobar() returns setof text as
======================================================================
*** /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/expected/rowtypes.out 2010-09-01 15:27:48.267621881 -0700
--- /home/jdavis/wd/git/jdavis-postgresql/src/test/regress/results/rowtypes.out 2010-09-01 15:30:16.377623414 -0700
***************
*** 8,86 ****
create type quad as (c1 complex, c2 complex);
-- Some simple tests of I/O conversions and row construction
select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad;
! row | row
! -----------+------------------------
! (1.1,2.2) | ("(3.3,4.4)","(5.5,)")
! (1 row)
!
select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname;
! row | fullname
! ------------+------------
! (Joe,Blow) | (Joe,Blow)
! (1 row)
!
select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
! fullname | fullname
! ------------------+--------------
! (Joe,"von Blow") | (Joe,d'Blow)
! (1 row)
!
select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
! fullname | fullname
! -------------------+-----------------
! (Joe,"von""Blow") | (Joe,"d\\Blow")
! (1 row)
!
select '(Joe,"Blow,Jr")'::fullname;
! fullname
! -----------------
! (Joe,"Blow,Jr")
! (1 row)
!
select '(Joe,)'::fullname; -- ok, null 2nd column
! fullname
! ----------
! (Joe,)
! (1 row)
!
select '(Joe)'::fullname; -- bad
! ERROR: malformed record literal: "(Joe)"
LINE 1: select '(Joe)'::fullname;
^
- DETAIL: Too few columns.
select '(Joe,,)'::fullname; -- bad
! ERROR: malformed record literal: "(Joe,,)"
LINE 1: select '(Joe,,)'::fullname;
^
- DETAIL: Too many columns.
create temp table quadtable(f1 int, q quad);
insert into quadtable values (1, ((3.3,4.4),(5.5,6.6)));
insert into quadtable values (2, ((null,4.4),(5.5,6.6)));
select * from quadtable;
! f1 | q
! ----+---------------------------
! 1 | ("(3.3,4.4)","(5.5,6.6)")
! 2 | ("(,4.4)","(5.5,6.6)")
! (2 rows)
select f1, q.c1 from quadtable; -- fails, q is a table reference
ERROR: missing FROM-clause entry for table "q"
LINE 1: select f1, q.c1 from quadtable;
^
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
! f1 | c1 | i
! ----+-----------+-----
! 1 | (3.3,4.4) | 4.4
! 2 | (,4.4) | 4.4
! (2 rows)
!
create temp table people (fn fullname, bd date);
insert into people values ('(Joe,Blow)', '1984-01-10');
select * from people;
! fn | bd
! ------------+------------
! (Joe,Blow) | 01-10-1984
! (1 row)
-- at the moment this will not work due to ALTER TABLE inadequacy:
alter table fullname add column suffix text default '';
--- 8,65 ----
create type quad as (c1 complex, c2 complex);
-- Some simple tests of I/O conversions and row construction
select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad;
! ERROR: type complex is not composite
select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname;
! ERROR: type fullname is not composite
select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname;
! ERROR: type fullname is not composite
! LINE 1: select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname...
! ^
select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname;
! ERROR: type fullname is not composite
! LINE 1: select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fu...
! ^
select '(Joe,"Blow,Jr")'::fullname;
! ERROR: type fullname is not composite
! LINE 1: select '(Joe,"Blow,Jr")'::fullname;
! ^
select '(Joe,)'::fullname; -- ok, null 2nd column
! ERROR: type fullname is not composite
! LINE 1: select '(Joe,)'::fullname;
! ^
select '(Joe)'::fullname; -- bad
! ERROR: type fullname is not composite
LINE 1: select '(Joe)'::fullname;
^
select '(Joe,,)'::fullname; -- bad
! ERROR: type fullname is not composite
LINE 1: select '(Joe,,)'::fullname;
^
create temp table quadtable(f1 int, q quad);
insert into quadtable values (1, ((3.3,4.4),(5.5,6.6)));
+ ERROR: type quad is not composite
insert into quadtable values (2, ((null,4.4),(5.5,6.6)));
+ ERROR: type quad is not composite
select * from quadtable;
! f1 | q
! ----+---
! (0 rows)
select f1, q.c1 from quadtable; -- fails, q is a table reference
ERROR: missing FROM-clause entry for table "q"
LINE 1: select f1, q.c1 from quadtable;
^
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
! ERROR: type quad is not composite
create temp table people (fn fullname, bd date);
insert into people values ('(Joe,Blow)', '1984-01-10');
+ ERROR: type fullname is not composite
+ LINE 1: insert into people values ('(Joe,Blow)', '1984-01-10');
+ ^
select * from people;
! fn | bd
! ----+----
! (0 rows)
-- at the moment this will not work due to ALTER TABLE inadequacy:
alter table fullname add column suffix text default '';
***************
*** 88,114 ****
-- but this should work:
alter table fullname add column suffix text default null;
select * from people;
! fn | bd
! -------------+------------
! (Joe,Blow,) | 01-10-1984
! (1 row)
-- test insertion/updating of subfields
update people set fn.suffix = 'Jr';
select * from people;
! fn | bd
! ---------------+------------
! (Joe,Blow,Jr) | 01-10-1984
! (1 row)
insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66);
select * from quadtable;
! f1 | q
! ----+---------------------------
! 1 | ("(3.3,4.4)","(5.5,6.6)")
! 2 | ("(,4.4)","(5.5,6.6)")
! 44 | ("(55,)","(,66)")
! (3 rows)
-- The object here is to ensure that toasted references inside
-- composite values don't cause problems. The large f1 value will
--- 67,89 ----
-- but this should work:
alter table fullname add column suffix text default null;
select * from people;
! fn | bd
! ----+----
! (0 rows)
-- test insertion/updating of subfields
update people set fn.suffix = 'Jr';
select * from people;
! fn | bd
! ----+----
! (0 rows)
insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66);
+ ERROR: type quad is not composite
select * from quadtable;
! f1 | q
! ----+---
! (0 rows)
-- The object here is to ensure that toasted references inside
-- composite values don't cause problems. The large f1 value will
***************
*** 116,128 ****
create temp table pp (f1 text);
insert into pp values (repeat('abcdefghijkl', 100000));
insert into people select ('Jim', f1, null)::fullname, current_date from pp;
select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people;
! first | substr | length
! -------+----------------------+---------
! Joe | Blow | 4
! Jim | abcdefghijklabcdefgh | 1200000
! (2 rows)
!
-- Test row comparison semantics. Prior to PG 8.2 we did this in a totally
-- non-spec-compliant way.
select ROW(1,2) < ROW(1,3) as true;
--- 91,99 ----
create temp table pp (f1 text);
insert into pp values (repeat('abcdefghijkl', 100000));
insert into people select ('Jim', f1, null)::fullname, current_date from pp;
+ ERROR: type fullname is not composite
select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people;
! ERROR: type fullname is not composite
-- Test row comparison semantics. Prior to PG 8.2 we did this in a totally
-- non-spec-compliant way.
select ROW(1,2) < ROW(1,3) as true;
======================================================================
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
Jeff Davis <pgsql@j-davis.com> writes:
On Wed, 2010-09-01 at 15:31 -0400, Tom Lane wrote:
Jeff Davis <pgsql@j-davis.com> writes:
Compiling with RELCACHE_FORCE_RELEASE doesn't pass "make check" on my
machine.
What happens exactly?
I have attached regression.diffs after a "make check". The diffs don't
look trivial, and actually look quite strange to me.
Hmm, sorta looks like that breaks something related to checking for
composite types. That would be a bug. Will look.
regards, tom lane
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
I wrote:
Jeff Davis <pgsql@j-davis.com> writes:
I have attached regression.diffs after a "make check". The diffs don't
look trivial, and actually look quite strange to me.
Hmm, sorta looks like that breaks something related to checking for
composite types. That would be a bug. Will look.
So here is the culprit:
#0 0x00000000007c08e7 in flush_rowtype_cache (type_id=49022) at typcache.c:531
#1 0x00000000007b4797 in RelationClearRelation (relation=0x7fc490ccebe0,
rebuild=0 '\000') at relcache.c:1929
#2 0x00000000007b4131 in RelationClose (relation=0x7fc490ccebe0)
at relcache.c:1680
#3 0x0000000000472721 in relation_close (relation=0x7fc490ccebe0, lockmode=1)
at heapam.c:1051
#4 0x00000000007c01fe in lookup_type_cache (type_id=49022, flags=64)
at typcache.c:299
#5 0x00000000007c023f in lookup_rowtype_tupdesc_internal (type_id=49022,
typmod=-1, noError=0 '\000') at typcache.c:321
#6 0x00000000007c03e7 in lookup_rowtype_tupdesc_copy (type_id=49022,
typmod=-1) at typcache.c:395
#7 0x00000000007cd087 in get_expr_result_type (expr=0x1395d08,
resultTypeId=0x0, resultTupleDesc=0x7fffbd6e5830) at funcapi.c:256
lookup_type_cache() opens the relcache entry to copy its tupdesc, does
so, and closes the relcache entry again. But then (if
RELCACHE_FORCE_RELEASE is on) we blow away the relcache entry ...
and RelationClearRelation carefully reaches around and blows away
the tupdesc in the typcache too!
So that's overly aggressive, but I'm not sure what's a simple fix for
it. If we just take out that flush_rowtype_cache call in
RelationClearRelation, I think we run the risk of the typcache not being
flushed when it needs to be, namely when an ALTER TABLE changes the
table's rowtype at an instant when some particular backend doesn't
have an active relcache entry for it.
Probably the best fix would be to make typcache flushing fully
independent of the relcache, but that would mean making sure that all
ALTER TABLE variants that affect the rowtype will issue an explicit
typcache flush. That seems a bit too invasive to be back-patchable.
I'm not entirely sure this sort of failure can occur without
RELCACHE_FORCE_RELEASE, but I'm definitely not sure it can't, so a
backpatchable fix would be nice.
Thoughts?
regards, tom lane
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
Jeff Davis <pgsql@j-davis.com> writes:
I have attached regression.diffs after a "make check". The diffs don't
look trivial, and actually look quite strange to me.
BTW, if I dike out the flush_rowtype_cache call at relcache.c:1929,
the regression tests do pass for me, so it seems there is only one
bug exposed by this test (as of HEAD anyway). I don't find that
to be a credible real fix, though, as stated previously.
Once we do have a fix in place for this, it might be a good idea for
someone to run a buildfarm member with -DRELCACHE_FORCE_RELEASE ...
regards, tom lane
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
I wrote:
Probably the best fix would be to make typcache flushing fully
independent of the relcache, but that would mean making sure that all
ALTER TABLE variants that affect the rowtype will issue an explicit
typcache flush. That seems a bit too invasive to be back-patchable.
I'm not entirely sure this sort of failure can occur without
RELCACHE_FORCE_RELEASE, but I'm definitely not sure it can't, so a
backpatchable fix would be nice.
After a bit more study it seems that there is a reasonably
back-patchable approach to this. We can continue to drive flushing of
composite-type typcache entries off of relcache flush, but it has to
occur when we do RelationCacheInvalidateEntry() or
RelationCacheInvalidate() due to a SI invalidate event, not just
anytime a relcache entry is closed. We can do that by plugging in a
callback function with CacheRegisterRelcacheCallback.
Because the callback will only have the relation OID not the type OID,
it will have to scan the whole TypeCacheHash to see if there's a
matching entry. However, that's not as bad as it sounds, because there
aren't likely to be very many entries in that hashtable. I put in some
quick-hack instrumentation to see how big the table gets during the
regression tests, and find that of the hundred-odd backends launched
during the tests, none get above 26 typcache entries, and only 8 get as
many as 10 entries. Based on those numbers, I'm not sure it'd ever be
worth adding the additional infrastructure to allow a direct hash lookup
instead.
regards, tom lane
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
On Wed, 2010-09-01 at 20:57 -0400, Tom Lane wrote:
I wrote:
Probably the best fix would be to make typcache flushing fully
independent of the relcache, but that would mean making sure that all
ALTER TABLE variants that affect the rowtype will issue an explicit
typcache flush. That seems a bit too invasive to be back-patchable.
I'm not entirely sure this sort of failure can occur without
RELCACHE_FORCE_RELEASE, but I'm definitely not sure it can't, so a
backpatchable fix would be nice.After a bit more study it seems that there is a reasonably
back-patchable approach to this. We can continue to drive flushing of
composite-type typcache entries off of relcache flush, but it has to
occur when we do RelationCacheInvalidateEntry() or
RelationCacheInvalidate() due to a SI invalidate event, not just
anytime a relcache entry is closed. We can do that by plugging in a
callback function with CacheRegisterRelcacheCallback.
I think I see how this fixes the problem, but I still don't completely
understand.
Why can't we just make a real copy of the tuple descriptor for the type
cache entry, rather than sharing it between the relcache and the type
cache?
Thank you for the quick response.
Regards,
Jeff Davis
Re: compiling with RELCACHE_FORCE_RELEASE doesn't pass regression
Jeff Davis <pgsql@j-davis.com> writes:
I think I see how this fixes the problem, but I still don't completely
understand.
Why can't we just make a real copy of the tuple descriptor for the type
cache entry, rather than sharing it between the relcache and the type
cache?
The issue isn't really about whether we're sharing the physical copy of
the tupdesc. The problem the code is trying to deal with is making sure
that the typcache's copy gets thrown away (so it can be refreshed on
next use) when the relation's rowtype changes, due to ALTER TABLE ADD
COLUMN for example. So we need to do that whenever we get a SI inval
event for the rel. We were driving that purely off of relcache
flushes, which meant that discarding a relcache entry had to force a
typcache flush, since nothing would happen if a SI inval arrived at an
instant where we had no relcache entry for the rel. Now the typcache is
wired directly to the SI inval events, so it'll get a call whether there
is a corresponding relcache entry or not.
regards, tom lane