Re: Wrong insert before trigger examples
Patch applied back through PG 10. --- On Fri, Mar 13, 2020 at 06:56:37PM -0400, Bruce Momjian wrote: > On Wed, Feb 5, 2020 at 09:36:49AM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/12/ddl-partitioning.html > > Description: > > > > Hi, > > > > I just noticed that the example in 5.11.3.1. Point 5 contains an "before > > insert trigger" which will not work anymore (I think since Postgres 12). > > This trigger is not needed anymore and causes an error message. > > Sorry for the delay in replying. I was able to successfully create the > trigger: > > CREATE TABLE measurement ( > city_id int not null, > logdate date not null, > peaktempint, > unitsales int > ); > > CREATE TABLE measurement_y2006m02 ( > CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE > '2006-03-01' ) > ) INHERITS (measurement); > > CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 > (logdate); > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETURNS TRIGGER AS $$ > BEGIN > INSERT INTO measurement_y2008m01 VALUES (NEW.*); > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > > CREATE TRIGGER insert_measurement_trigger > BEFORE INSERT ON measurement > FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger(); > > There are actually two definitions of 'measurement' in the docs above, > and you might have used the partitioned one: > > CREATE TABLE measurement ( > city_id int not null, > logdate date not null, > peaktempint, > unitsales int > ) PARTITION BY RANGE (logdate); > > I have developed the attached patch to clarify which definition to use. > I am not sure if more extensive changes are needed. > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml > index 8d3a0d1c22..d2588876c0 100644 > --- a/doc/src/sgml/ddl.sgml > +++ b/doc/src/sgml/ddl.sgml > @@ -4103,8 +4103,8 @@ ALTER INDEX measurement_city_id_logdate_key > Example > > > - We use the same measurement table we used > - above. To implement partitioning using inheritance, use > + We use the non-partitioned measurement > + table above. To implement partitioning using inheritance, use >the following steps: > > -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
Patch applied through PG 9.5. Thanks. --- On Tue, Mar 17, 2020 at 05:21:36PM -0400, Bruce Momjian wrote: > On Wed, Feb 12, 2020 at 11:55:51PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/12/pgbuffercache.html > > Description: > > > > The pg_buffercache query example results are misleading. The "group by" uses > > just by relname. It needs to include pg_namespace.nspname, without it, if > > the same object exists in multiple schemas, the buffer count is summed for > > those multiple distinct objects. > > In: https://www.postgresql.org/docs/12/pgbuffercache.html > > Alternative SQL (the count is now correct for tables in multiple schemas): > > SELECT ts.nspname AS schema_name,c.relname, count(*) AS buffers > > FROM pg_buffercache b INNER JOIN pg_class c > > ON b.relfilenode = pg_relation_filenode(c.oid) AND > > b.reldatabase IN (0, (SELECT oid FROM pg_database > > WHERE datname = current_database())) > > JOIN pg_namespace ts ON ts.oid = c.relnamespace > > GROUP BY ts.nspname,c.relname > > ORDER BY buffers DESC > > LIMIT 10; > > > > Example Results: > > Current Query returns 1 row with buffer count summed for 3 tables: > > relname buffers > > tab172401 > > > > Modified Query: > > schema_name relname buffers > > schema1 tab11883 > > schema2 tab169961 > > schema3 tab1557 > > Very good point! Patch attached. > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml > index b5233697c3..2479181c5f 100644 > --- a/doc/src/sgml/pgbuffercache.sgml > +++ b/doc/src/sgml/pgbuffercache.sgml > @@ -148,27 +148,28 @@ >Sample Output > > > -regression=# SELECT c.relname, count(*) AS buffers > +regression=# SELECT n.nspname, c.relname, count(*) AS buffers > FROM pg_buffercache b INNER JOIN pg_class c > ON b.relfilenode = pg_relation_filenode(c.oid) AND > b.reldatabase IN (0, (SELECT oid FROM pg_database >WHERE datname = current_database())) > - GROUP BY c.relname > - ORDER BY 2 DESC > + JOIN pg_namespace n ON n.oid = c.relnamespace > + GROUP BY n.nspname, c.relname > + ORDER BY 3 DESC > LIMIT 10; > > - relname | buffers > --+- > - tenk2 | 345 > - tenk1 | 141 > - pg_proc | 46 > - pg_class| 45 > - pg_attribute| 43 > - pg_class_relname_nsp_index | 30 > - pg_proc_proname_args_nsp_index | 28 > - pg_attribute_relid_attnam_index | 26 > - pg_depend | 22 > - pg_depend_reference_index | 20 > + nspname |relname | buffers > +++- > + public | delete_test_table | 593 > + public | delete_test_table_pkey | 494 > + pg_catalog | pg_attribute | 472 > + public | quad_poly_tbl | 353 > + public | tenk2 | 349 > + public | tenk1 | 349 > + public | gin_test_idx | 306 > + pg_catalog | pg_largeobject | 206 > + public | gin_test_tbl | 188 > + public | spgist_text_tbl| 182 > (10 rows) > > -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: Move description of general lock behaviour out of the "13.3.1. Table-level Locks section"
Patch applied through 9.5. --- On Thu, Mar 12, 2020 at 04:34:20PM -0400, Bruce Momjian wrote: > On Mon, Feb 3, 2020 at 05:04:00PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/9.4/explicit-locking.html > > Description: > > > > Hi > > > > The "13.3.1. Table-level Locks" subsection mentions the following: "Once > > acquired, a lock is normally held till end of transaction." (maybe we should > > also squeeze a "...till the end of a transaction" in there) According to a > > Sorry for the delay in replying. Yes, this wording needs improvement, > which I have done in the attached patch. > > > helpful stranger on IRC, this behavior is also true for row-level locks. > > > > Since this sentence also applies to the row-level locks described in the > > following subsection 13.3.2 I think it would be more fitting to move the > > paragraph containing this sentence to the introduction of the topic in > > section "13.3. Explicit Locking". This would then read something like: > > Uh, we can't move that paragraph up because Page-Level Locks and > Advisory Locks aren't always released on transaction end or rollback. > What I did do was to mention that row-level locks are released in a > similar way to table-level locks. > > Patch attached. > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml > index f8c9655111..95465d581b 100644 > --- a/doc/src/sgml/mvcc.sgml > +++ b/doc/src/sgml/mvcc.sgml > @@ -1039,9 +1039,9 @@ ERROR: could not serialize access due to read/write > dependencies among transact > > > > -Once acquired, a lock is normally held till end of transaction. But if a > +Once acquired, a lock is normally held until the end of the transaction. > But if a > lock is acquired after establishing a savepoint, the lock is released > -immediately if the savepoint is rolled back to. This is consistent with > +immediately if the savepoint is rolled back. This is consistent with > the principle that ROLLBACK cancels all effects of the > commands since the savepoint. The same holds for locks acquired within a > PL/pgSQL exception block: an error escape > from the block > @@ -1178,7 +1178,10 @@ ERROR: could not serialize access due to read/write > dependencies among transact > conflicting locks on the same row, even in different subtransactions; > but other than that, two transactions can never hold conflicting locks > on the same row. Row-level locks do not affect data querying; they > - block only writers and lockers to the same row. > + block only writers and lockers to the same > + row. Row-level locks are released at transaction end or during > + savepoint rollback, just like table-level locks. > + > > > -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: Users/Roles do not align.
Patch applied through 9.5, thanks. --- On Mon, Mar 9, 2020 at 10:50:26PM -0400, Bruce Momjian wrote: > On Thu, Feb 6, 2020 at 11:06:44AM +0100, Jürgen Purtz wrote: > > > > > There's a few things wrong about this part anyway- namely that we've got > > > FDWs now, and there's certainly other cluster-wide things that exist > > > beyond the specific items listed, so I wonder if perhaps we should just > > > stop trying to list everything here. > > > > Inspiring answer! After some inquiry I became aware, that we do not have > > only 2 levels of 'belong-to' but 3: tables, views, operators, and much more > > objects belong to a schema; schemata, extensions (e.g. FDW), and more(?) > > belong to a database; databases, roles, tablespaces, and more belong to a > > cluster. Two aspects of 'belong-to' are: object names are unique within > > their level, and objects are automatically known everywhere within their > > level. > > > > Information about such dependencies and their consequences is spread across > > different chapters of the documentation and the System Catalog. Of course > > the chapter about roles/users is not suitable to explain the details. But > > it's important to know the hierarchy, it shut be summarized somewhere. > > I developed the attached patch to address this suggestion. FYI, you can > list global objects using this query: > > SELECT relname > FROM pg_class JOIN pg_tablespace ON (reltablespace = pg_tablespace.oid) > WHERE relkind = 'r' and spcname = 'pg_global'; > relname > --- >pg_authid >pg_subscription >pg_database >pg_db_role_setting >pg_tablespace >pg_auth_members >pg_shdepend >pg_shdescription >pg_replication_origin >pg_shseclabel > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml > index 8d3a0d1c22..fe5e81cd65 100644 > --- a/doc/src/sgml/ddl.sgml > +++ b/doc/src/sgml/ddl.sgml > @@ -2625,19 +2625,18 @@ SELECT * FROM information WHERE group_id = 2 FOR > UPDATE; > > > > - A PostgreSQL database cluster > - contains one or more named databases. Users and groups of users are > - shared across the entire cluster, but no other data is shared across > - databases. Any given client connection to the server can access > - only the data in a single database, the one specified in the connection > - request. > + A PostgreSQL database cluster contains > + one or more named databases. Roles and a few other object types are > + shared across the entire cluster. A client connection to the server > + can only access data in a single database, the one specified in the > + connection request. > > > > > Users of a cluster do not necessarily have the privilege to access every > -database in the cluster. Sharing of user names means that there > -cannot be different users named, say, joe in two > databases > +database in the cluster. Sharing of role names means that there > +cannot be different roles named, say, joe in two > databases > in the same cluster; but the system can be configured to allow > joe access to only some of the databases. > > diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml > index b1b8539fb3..0510afd818 100644 > --- a/doc/src/sgml/manage-ag.sgml > +++ b/doc/src/sgml/manage-ag.sgml > @@ -22,16 +22,13 @@ > > > > - A database is a named collection of SQL objects > - (database objects). Generally, every database > - object (tables, functions, etc.) belongs to one and only one > - database. (However there are a few system catalogs, for example > - pg_database, that belong to a whole cluster and > - are accessible from each database within the cluster.) More > - accurately, a database is a collection of schemas and the schemas > - contain the tables, functions, etc. So the full hierarchy is: > - server, database, schema, table (or some other kind of object, > - such as a function). > + A small number of objects, like role, database, and tablespace names, > + are stored at the cluster level and use the pg_global > + tablespace. Inside the cluster are multiple databases, which > + are isolated from each other but can access cluster-level objects. > + Inside each database are multiple schemas, which contain objects like > + tables and functions. So the full hierarchy is: cluster, database, > + schema, table (or some other kind of object, such as a function). > > > -- Bruce Momjian https://momjian.us EnterpriseDB https:/
Re: Incomplete or misleading explanation of the data types for mathematical operators
Patch applied through 9.5, thanks. --- On Sat, Mar 21, 2020 at 10:31:32AM -0400, Bruce Momjian wrote: > On Thu, Mar 5, 2020 at 08:03:19PM -0700, Sergei Agalakov wrote: > > On 3/5/2020 7:29 PM, Bruce Momjian wrote: > > > On Wed, Jan 29, 2020 at 07:35:18PM +, PG Doc comments form wrote: > > > > Multiplication preserves data type, exponentiation silently converts > > > > bigint > > > > to double, but preserves numeric data type, square root silently > > > > converts > > > > both int and numeric types to double. > > > > The best would be to explain this behaivior of operators like it was > > > > done > > > > for mathematical functions. > > > Uh, how does this relate to bitwise operators? Why would we mention > > > type changes for things like exponentiation in the bitwise operator > > > documentation section? > > > > > This chapter is named "Mathematical Functions and Operators". The table 9.4. > > is named "Mathematical Operators". I don't see on this page any section > > "Bitwise operators" so I don't really understand your complaint. > > How do you understand the phrase "The bitwise operators work only on > > integral data types, whereas the others are available for all numeric data > > types. " in the context of the table "Mathematical Operators"? > > I understand it that all other mathematical operators except bitwise > > operators do exist for all numeric data type. > > In what place by your opinion documentation should describe that some > > mathematical operators exist only for some numeric data types but not > > others? > > I have given examples of such operators - exponentiation and square root > > aren't defined for all numeric data types and do hidden conversion of the > > data types. > > Ah, I see what you are saying now --- there are operators listed above > that are not supported by all numeric data types, so "the others are > available for all numeric data types" is false. I think the text was > written because non-integer calls to bit-wise functions _fail_, rather > than being silently converted: > > SELECT 1::float4 | 1; > ERROR: operator does not exist: real | integer > LINE 1: SELECT 1::float4 | 1; >^ > HINT: No operator matches the given name and argument types. You might > need to add explicit type casts. > > but I see your point. I guess we could change the word "available" to > "silently converted", but it seems best to just remove that phrase. > Documentation patch attached. > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml > index 464a48ed6a..e1d70c251d 100644 > --- a/doc/src/sgml/func.sgml > +++ b/doc/src/sgml/func.sgml > @@ -732,9 +732,8 @@ > > > > -The bitwise operators work only on integral data types, whereas > -the others are available for all numeric data types. The bitwise > -operators are also available for the bit > +The bitwise operators work only on integral data types, and are also > +available for the bit > string types bit and bit varying, as > shown in . > -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: terminology in example
On Tue, Mar 24, 2020 at 01:41:41PM -0400, Bruce Momjian wrote: > On Tue, Mar 24, 2020 at 03:57:25PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/12/ddl-inherit.html > > Description: > > > > In the example on this page: > > https://www.postgresql.org/docs/current/ddl-inherit.html > > the word 'altitude' should be replaced with 'elevation'. > > Yes, I see your point: > > > https://mapscaping.com/blogs/geo-candy/what-is-the-difference-between-elevation-relief-and-altitude The attached patch fixes the problem. The regression tests also have many mentions of "altitude." Should those be changed too? -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index ae5f3fac75..be14d15b3f 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -585,20 +585,20 @@ SELECT sum(salary) OVER w, avg(salary) OVER w CREATE TABLE capitals ( name text, population real, - altitude int,-- (in ft) + elevation int,-- (in ft) state char(2) ); CREATE TABLE non_capitals ( name text, population real, - altitude int -- (in ft) + elevation int -- (in ft) ); CREATE VIEW cities AS - SELECT name, population, altitude FROM capitals + SELECT name, population, elevation FROM capitals UNION - SELECT name, population, altitude FROM non_capitals; + SELECT name, population, elevation FROM non_capitals; This works OK as far as querying goes, but it gets ugly when you @@ -612,7 +612,7 @@ CREATE VIEW cities AS CREATE TABLE cities ( name text, population real, - altitude int -- (in ft) + elevation int -- (in ft) ); CREATE TABLE capitals ( @@ -624,7 +624,7 @@ CREATE TABLE capitals ( In this case, a row of capitals inherits all columns (name, -population, and altitude) from its +population, and elevation) from its parent, cities. The type of the column name is text, a native PostgreSQL @@ -636,23 +636,23 @@ CREATE TABLE capitals ( For example, the following query finds the names of all cities, -including state capitals, that are located at an altitude +including state capitals, that are located at an elevation over 500 feet: -SELECT name, altitude +SELECT name, elevation FROM cities - WHERE altitude > 500; + WHERE elevation > 500; which returns: - name| altitude +-- - Las Vegas | 2174 - Mariposa | 1953 - Madison | 845 + name| elevation +---+--- + Las Vegas | 2174 + Mariposa | 1953 + Madison | 845 (3 rows) @@ -660,19 +660,19 @@ SELECT name, altitude On the other hand, the following query finds all the cities that are not state capitals and -are situated at an altitude over 500 feet: +are situated at an elevation over 500 feet: -SELECT name, altitude +SELECT name, elevation FROM ONLY cities -WHERE altitude > 500; +WHERE elevation > 500; - name| altitude +-- - Las Vegas | 2174 - Mariposa | 1953 + name| elevation +---+--- + Las Vegas | 2174 + Mariposa | 1953 (2 rows) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index ab27645319..d84214f277 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3157,7 +3157,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; CREATE TABLE cities ( nametext, population float, -altitudeint -- in feet +elevationint -- in feet ); CREATE TABLE capitals ( @@ -3177,40 +3177,40 @@ CREATE TABLE capitals ( rows of a table or all rows of a table plus all of its descendant tables. The latter behavior is the default. For example, the following query finds the names of all cities, - including state capitals, that are located at an altitude over + including state capitals, that are located at an elevation over 500 feet: -SELECT name, altitude +SELECT name, elevation FROM cities -WHERE altitude > 500; +WHERE elevation > 500; Given the sample data from the PostgreSQL tutorial (see ), this returns: - name| altitude +-- - Las Vegas | 2174 - Mariposa | 1953 - Madison | 845 + name| elevation +---+--- + Las Vegas | 2174 + Mariposa | 1953 + Madison | 845 On the other hand, the following query finds all the cities that - are not state capitals and are situated at an altitude over 500 feet: + are not state capit
Re: terminology in example
On Tue, 2020-03-31 at 19:10 -0400, Bruce Momjian wrote: > The attached patch fixes the problem. The regression tests also have > many mentions of "altitude." Should those be changed too? I don't think that is necessary. Names don't matter in the regression tests. > --- a/doc/src/sgml/advanced.sgml > +++ b/doc/src/sgml/advanced.sgml > @@ -585,20 +585,20 @@ SELECT sum(salary) OVER w, avg(salary) OVER w > CREATE TABLE capitals ( >name text, >population real, > - altitude int,-- (in ft) > + elevation int,-- (in ft) >state char(2) > ); > > CREATE TABLE non_capitals ( >name text, >population real, > - altitude int -- (in ft) > + elevation int -- (in ft) > ); > > @@ -612,7 +612,7 @@ CREATE VIEW cities AS > CREATE TABLE cities ( >name text, >population real, > - altitude int -- (in ft) > + elevation int -- (in ft) > ); The "int" should be aligned with the rest (delete one space). Yours, Laurenz Albe
