Re: [PATCHES] pg_regress in C
Per discussion at the conference: In order to run the regression tests on Windows without msys, pg_regress needs to be reimplemnted in C. This has some minor portability issues (macros with ... aren't portable, for instance) but I think it's something we need to do. Barring objections I'm going to clean up and apply it. Thanks for this, including all the followup patches :-) //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
Am Donnerstag, 17. August 2006 20:05 schrieb Chris Mair: \gc sounds like a good idea to me :) Strictly speaking, in the randomly defined grammer of psql, \gc is \g with an argument of 'c' (try it, it works). I'm not sure what use case you envision for this feature. Obviously, this is for queries with large result sets. I'd guess that people will not normally look at those result sets interactively. If the target audience is instead psql scripting, you don't really need the most convenient command possible. A \set variable would make sense to me. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] Information_schema fixes for sequences and temporary tables
More to come, but these two are probably worth backpatching. Sequences
were not being shown due to the use of lowercase 's' instead of 'S', and
the views were not checking for table visibility with regards to
temporary tables and sequences.
--
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200608181942
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Index: information_schema.sql
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/information_schema.sql,v
retrieving revision 1.33
diff -u -c -r1.33 information_schema.sql
*** information_schema.sql 2 Apr 2006 17:38:13 - 1.33
--- information_schema.sql 18 Aug 2006 23:26:22 -
***
*** 644,650
WHERE a.attrelid = c.oid
AND a.atttypid = t.oid
AND nc.oid = c.relnamespace
!
AND a.attnum 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
AND (pg_has_role(c.relowner, 'USAGE')
--- 644,650
WHERE a.attrelid = c.oid
AND a.atttypid = t.oid
AND nc.oid = c.relnamespace
! AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
AND a.attnum 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
AND (pg_has_role(c.relowner, 'USAGE')
***
*** 933,938
--- 933,939
AND nc.oid = c.connamespace
AND c.contype IN ('p', 'u', 'f')
AND r.relkind = 'r'
+ AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
AND (pg_has_role(r.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
***
*** 1459,1465
CAST(null AS character_data) AS cycle_option-- FIXME
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
! AND c.relkind = 's'
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'UPDATE') );
--- 1460,1467
CAST(null AS character_data) AS cycle_option-- FIXME
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
! AND c.relkind = 'S'
! AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'UPDATE') );
***
*** 1690,1695
--- 1692,1698
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
AND c.conrelid = r.oid
AND r.relkind = 'r'
+ AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
AND (pg_has_role(r.relowner, 'USAGE')
-- SELECT privilege omitted, per SQL standard
OR has_table_privilege(r.oid, 'INSERT')
***
*** 1723,1728
--- 1726,1732
AND a.attnum 0
AND NOT a.attisdropped
AND r.relkind = 'r'
+ AND (nr.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(r.oid))
AND (pg_has_role(r.relowner, 'USAGE')
OR has_table_privilege(r.oid, 'SELECT')
OR has_table_privilege(r.oid, 'INSERT')
***
*** 1824,1829
--- 1828,1834
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r', 'v')
+ AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
***
*** 1945,1950
--- 1950,1956
AND c.oid = t.tgrelid
AND t.tgtype em.num 0
AND NOT t.tgisconstraint
+ AND (n.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
AND (pg_has_role(c.relowner, 'USAGE')
-- SELECT privilege omitted, per SQL standard
OR has_table_privilege(c.oid, 'INSERT')
***
*** 2143,2148
--- 2149,2155
WHERE c.relnamespace = nc.oid
AND c.relkind = 'v'
+ AND (nc.nspname NOT LIKE 'pg!_temp!_%' ESCAPE '!' OR pg_catalog.pg_table_is_visible(c.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
signature.asc
Description: This is a digitally signed message part
[PATCHES] Information schema - finalize key_column_usage
Correctly populates the position_in_unique_constraint column in the information_schema.key_column_usage view. -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200608182231 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 Index: information_schema.sql === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/information_schema.sql,v retrieving revision 1.33 diff -u -c -r1.33 information_schema.sql *** information_schema.sql 2 Apr 2006 17:38:13 - 1.33 --- information_schema.sql 19 Aug 2006 02:28:51 - *** *** 921,931 CAST(relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name, CAST((ss.x).n AS cardinal_number) AS ordinal_position, !CAST(null AS cardinal_number) AS position_in_unique_constraint -- FIXME FROM pg_attribute a, ! (SELECT r.oid, nc.nspname AS nc_nspname, c.conname, nr.nspname AS nr_nspname, r.relname, ! _pg_expandarray(c.conkey) AS x FROM pg_namespace nr, pg_class r, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace --- 921,937 CAST(relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name, CAST((ss.x).n AS cardinal_number) AS ordinal_position, !( ! SELECT CAST(a AS cardinal_number) ! FROM pg_constraint, !(SELECT a FROM generate_series(1,(SELECT array_upper(ss.confkey,1))) f(a)) AS foo ! WHERE conrelid = ss.confrelid ! AND conkey[foo.a] = ss.confkey[(ss.x).n] !) AS position_in_unique_constraint FROM pg_attribute a, ! (SELECT r.oid, nc.nspname AS nc_nspname, c.conname, c.confkey, nr.nspname AS nr_nspname, r.relname, ! _pg_expandarray(c.conkey) AS x FROM pg_namespace nr, pg_class r, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace signature.asc Description: This is a digitally signed message part
Re: [PATCHES] Information_schema fixes for sequences and temporary tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'd be interested to see you cite chapter and verse in the SQL spec where it says that information_schema should hide temp tables of other backends. That change seems pretty dubious to me. (More likely, we should be adding tests on whether the caller has USAGE privilege on the table's containing schema.) Fair point: what brought this about was someone wondering why a SELECT 1 FROM information_schema.tables WHERE table_name = 'mytemptable' returned true but a subsequent DROP TABLE mytemptable; failed. Another subtle difference between \d and i_s.tables I suppose. It all depends on how one interprets accessible here: quote Function: [of information_schema.tables] Identify the tables defined in this catalog that are accessible to a given user or role. /quote While I might extend accessible to schemas outside of a user's search path, I'm not sure that should include the pg_temp_ ones. Seems confusing for the user to see other temp tables, even if the schema is returned, as one does not specify a schema when creating temp tables. +1 on the USAGE idea. As for all that ESCAPE junk, consider using regexps instead; they play nicer with underscores in patterns. Hmph. I was just copying the surrounding code, in the theory that it increases the chance of my patches being accepted. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200608182237 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFE5n7EvJuQZxSWSsgRAr9UAKDSXYExsVwsYazS1ygaOCmsudGVpwCeKPHj 1g/fpDkpDdfOr9eGQzr3M9U= =9QRx -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
